Your questions, with my responses:

  Does event_config need a reference to event via event_id? It makes sense to know to which event the config belongs to, but at the same time it feels redundant since it's implicit from the relationship with dates.

Adding the event_id to the event_config table allows you to easily design a user-interface for modifying the event_config table, without needing a complex reference to an actual event instance.  This might be useful for "default" configs.

This also allows an easier code path for presenting data to the user prior to the user choosing a date.

  Isn't event_config an unneeded complexity? Its sole purpose is to provide shared fields to event_date and event_recurrence. Wouldn't it be better just to have the price and capacity columns both in date and recurrence?

event_config looks like a good optimization to me.  I see it as a way to manage configurations independently of specific events in time.  For instance, you might have a "holiday" config, a config for each weekday, and a config for "saturday" and "sunday", each of which might be exactly the same for each and every event that occurs on those days.  Why duplicate that data by storing the info in event_date and event_recurrence?


When populating the data there are a number of issues you may run in to. The data size itself is not huge, but more inconvenient... you could choose any of the mechnisms for populating it including regular selects.

The right way to be doing it is with the TRUNCATE followed by the LOAD. If you are having problems with indexes then you should take that up with the MySQL folk, it is a bug, and it should be working.

If you temporarily need a work around for it then I recommend that you add a column to your table which will be unique for each LOAD cycle. A date may be a good candidate. You have already chosen a column 'update', which is a fine column to use.... but, instead of changing the data back to 0 each time, just increment it for each load, or use a number that matches the date like 20140110 for example. Then, after you LOAD the data you can:

DELETE from TABLE where update <> 20140110


Getting error while starting mysql by just typing in mysql terminal

That is not how you start mysql from the command line.

When you type mysql in the command line it will launch the mysql-client not the mysql-server, which looks for the user/password options to be provided as arguments, if they aren't provided it will expect them to be specified in ~/.my.cnf or /etc/mysql/my.cnf. If it still can't find the details there then it will then try the your current TTY user, which is what has happened to you.

If you want to login from the command line you can provide the username and password like so:

mysql -u <username> -p

If you don't want to have to do that every time then you can set the details in your ~/.my.cnf file.

[client]
user=<username>
password="<password>"

If you do actually want to start the mysql-server then it would depend on your operating system. For instance on Ubuntu you would do that with:

sudo service mysql start

Generally speaking you should be able to do

/etc/init.d/mysql start


mysqld_safe version different than mysqld?

As we all know, mysqld_safe and mysqld are very different

mysqld : The database server instance daemon

mysqld_safe : Control program that examines and sets the environment for mysqld to execute. The mysqld executable is actually launched in a loop. When mysqld terminates, the mysqld_safe program will examine the return results and decide whether

mysqld terminated normally (intentional shutdown), leaves mysqld_safe
mysqld terminated abnormally (crash or kill -9 of mysqld)

Loop back, mysqld fails on retry, leaves mysqld_safe
Loop back, mysqld starts up, stays in the mysqld_safe loop

Why is it important to have mysqld and mysqld_safe using the same MySQL version?

Let me illustrate it this way: Percona Server sometimes has additional features in mysqld_safe for manipulating the OS. For example, I have seen numactl --interleave=all in a Percona Server mysqld_safe. If that line was not there, the mysqld for Percona Server may run into issues with memory and swapping.

The same scenario could possibly be the case for Oracle's (ugh, still hate saying that) mysqld and mysqld_safe. There could be improvements from one major release to another that would be removed if the mysqld_safe was older.

Rather than exploring the possibilities of using a old mysqld_safe and a new mysqld (or vica versa), please make your life simple and reinstall MySQL 5.5.30 from scratch.

Before doing so, please run

updatedb
locate mysqld_safe

in Linux and see if there are two lingering. If there are, get the paths straightened out. Otherwise, you may have to reinstall MySQL 5.5.30.


to ensure that physically corrupt data is not read

I imagine that physically corrupt data means that whatever physical container holds the data - typically blocks or pages - always deliver a sound and well formatted structure, no matter what failures happen.
The lowest storage unit of a relational database is traditionally called a page or a block. A page is usually formed by multiple disk I/O units (disk blocks or sectors). The I/O subsystem guarantees that a sector is always written as an atomic I/O unit: it either succeeds or fails.
However, if (as is pretty much always the case) a page is formed by multiple sectors, then changes to that page need to be written in multiple sectors, as a consistent unit: either all sectors are written - or none is written. The OS I/O subsystem does not automatically guarantee that:
the lower level of the RDBMS (the I/O manager generally) must guarantee that instead.
This is what that sentence means: even when I use the read uncommitted isolation level, there is still a minimal consistency level that guarantees that the physical storage units of the database (the pages) are always consistent at the physical level.


Slower running Query with better Filter?

There are 3 steps in SELECTing from a PARTITIONed table.

"Partition pruning" -- deciding which partition(s) might have the data
Running the SELECT on each of those partitions
Combining the results.

Your first query could do no pruning since nothing in the WHERE clause limited which partitions to look at.

A quoted number will convert to an INT or BIGINT without trouble.  (The other direction causes trouble:  WHERE varchar_col = 123.)

The one index you have -- (itemid,clock) -- works even better without partitioning.  Do you have other queries for which you think partitioning is necessary?

You have no explicit PRIMARY KEY on that InnoDB table.  That generally hurts performance.

As for your 0sec vs 40sec, please provide EXPLAIN FORMAT=JSON SELECT ... for each of the queries.  There may be extra clues.


SQL Server - Check for opened Activity Monitor

The Activity Monitor shows up as a standard user session, "sleeping" in tempdb (database_id = 2, generally), with the program_name of "Microsoft SQL Server Management Studio".

It only queries at the rate of it's refresh window, but those identifiers, plus the total_elapsed_time, should make it feasible to find your idling monitors.


Numerous failed authentication attempts in error log

My initial thought was a logon trigger but, sadly, those only fire after a successful login.

The documentation for the Audit Login Failed Event Class says that failed login audits will include the application name, but I haven't observed that so far (I only tested failed logins from changing connections within Management Studio, so maybe it is exposed in other scenarios). Anyway, create this audit and audit specification:

USE master;
GO

CREATE SERVER AUDIT failed_logins
  TO FILE (FILEPATH = 'C:\temp')
GO

ALTER SERVER AUDIT failed_logins
  WITH (STATE = ON);
GO

CREATE SERVER AUDIT SPECIFICATION failed_logins_spec
  FOR SERVER AUDIT failed_logins
  ADD (FAILED_LOGIN_GROUP)
  WITH (STATE=ON);
GO

Now you should be able to right-click the audit (in Object Explorer under Security > Audits)  and choose View Audit Logs. There may be more information there for you, that might help you narrow this down better, but I am not 100% certain. The application trying to connect would have to pass a valid application name, Citrix can't be blocking any of that information from passing through (or changing it on the way), etc.

Note that this may be a no-op depending on your version and edition, too. If so, your next option may be Extended Events, as Max pointed out below.

CREATE EVENT SESSION FailedLogins
ON SERVER
 ADD EVENT sqlserver.error_reported
 (
   ACTION
   (
     sqlserver.client_app_name,
     sqlserver.client_hostname,
     sqlserver.nt_username
    )
    WHERE severity = 14
      --AND error_number = 18456 -- login failed - 2012+ only!
      AND state > 1 -- seems to always be a redundant state 1 event
  )
  ADD TARGET package0.asynchronous_file_target
  (
    SET FILENAME = N'C:\temp\FailedLogins.xel',
    METADATAFILE = N'C:\temp\FailedLogins.xem'
  );
GO

ALTER EVENT SESSION FailedLogins ON SERVER
  STATE = START;
GO

You can write really ugly XQuery to parse the results, or get Jonathan Kehayias' SSMS add-in.

In 2012 you can just open a Watch Live Data window by right-clicking the session in Object Explorer: Management > Extended Events > Sessions and wait for it to happen again. You might get noise from other "real" failed logins, password typos, other severity 14 errors, etc. In 2012+ you will be able to bind error_number instead, but it is not valid on 2008. Anyway, here is what I saw in 2014 with the 18456 error filter:

And while I have no interest in going the extra length to craft the XQuery you need to see query results in a nice tabular format, I did verify that in 2008 the above session does record the application name in the .xel file target by opening it in Notepad (it ain't pretty).

If that doesn't work (e.g. the app name does not get populated for some reason), next would be some kind of network/packet sniffer, like Wireshark. You could also:

Look at Control Panel > Administrative Tools > Services to see if there are any services running that are set to start up using that account.
Look at the Event Logs on both the SQL Server machine and an offending Citrix machine for any other clues around the time of a login failure.
Change the hosts file on that machine to override the SQL Server DNS name to point elsewhere (e.g. to an IP that is unreachable), and see if anything breaks in a different way if the app or service can't connect to SQL Server at all. Who knows, it may actually log something or pop up a dialog in that case. This will only work, of course, if the app currently reaches the SQL Server by name resolution and not by IP - you may need to try both the Windows name and the FQDN. This should work to stop the login attempts at least, even if it doesn't yield any more clues about the source; if so, then at the very least this could be a permanent way to stop filling the SQL Server's logs with failed login attempts. If other apps on that machine do need to connect to SQL Server, you could have them connect by IP instead, or (probably more future-proof) you could set up a different alias and change their connection strings to use the new name.

I also have a pretty elaborate blog post detailing what the state means for all the 18456 errors.


mysql load data infile takes too long and db file grows too large

24 hours is a very long time- the most important factor when running LOAD DATA is the buffer pool, and yours (22GB) should be enough to accommodate the imported 3.5G file. Something else must be going on, please share more data like the SHOW CREATE TABLE output for the used table and SHOW PROCESSLIST- maybe there is something blocking the table and you are waiting for the process to start. You can check at any time the state of the load by checking the GLOBAL STATUS for innodb%' as well as theHandler_writes`.

To give you an idea of how much I would expect this to take, a 3.7 GB file should not take much more than 200 seconds to safely load on an SSD desktop-grade machine. See these tests I did not a long time ago on all recent MariaDB and MySQL versions on a non-server grade machine, even on a default (bad) config: https://dbahire.com/testing-again-load-data-on-mysql-5-6-5-7-8-0-non-ga-and-mariadb-10-0-10-1-and-10-2-non-ga/


Inserting data from one table, then taking the primary key of that insert and put it into another table

Consider using the RETURNING clause on an INSERT or UPDATE statement to insert the foreign key.  From the PostgreSQL docs:
WITH upd AS (
  UPDATE employees
  SET sales_count = sales_count + 1
  WHERE id =
    (
       SELECT sales_person
       FROM accounts
       WHERE name = 'Acme Corporation'
    )
    RETURNING *
)
INSERT INTO employees_log
SELECT *, current_timestamp FROM upd;

So, your statement would need to do the initial insert in common-table-expression, which returns the id to allow it to be inserted into the users_login table:
CREATE TABLE users (
      user_id serial
    , first_name varchar(30)
    , last_name varchar(30)
);

CREATE TABLE users_login (
      user_login_id serial
    , email varchar(30)
    , user_id int
);

WITH ins AS (
    INSERT INTO users (first_name, last_name)
                VALUES ('hannah', 'vernon')

    RETURNING user_id
)
INSERT INTO users_login(email, user_id)
SELECT 'us@them.com', user_id
FROM ins

Querying the two tables:
SELECT *
FROM users u
    INNER JOIN users_login ul ON u.user_id = ul.user_id;

╔═════════╦════════════╦═══════════╦═══════════════╦═════════════╦═════════╗
║ user_id ║ first_name ║ last_name ║ user_login_id ║    email    ║ user_id ║
╠═════════╬════════════╬═══════════╬═══════════════╬═════════════╬═════════╣
║       1 ║ hannah     ║ vernon    ║             1 ║ us@them.com ║       1 ║
╚═════════╩════════════╩═══════════╩═══════════════╩═════════════╩═════════╝


Quoting AWS support which I think was really disappointing:

"Thanks so much for your patience. I'm one of the RDS Support Engineers looking at your instance issue.

I've escalated this CPU utilisation issue of your instance to our RDS team and they are aware of an issue that affects a very small number of db.t2.medium instances where CPU spikes to 50% despite little to no actual database activity happening on your instance. This issue is outside of the database engine and in the underlying operating system. What this does is eat through the available CPU credits available to you, and when you get to zero CPU credits, your CPU starts being throttled, impacting the ability for your database to do any actual work. There's no remediation we can apply and we encourage you to scale your instance to either a db.t2.small or db.m3.medium or large which are not impacted by this issue. Our RDS team will be rolling out a proactive operating system fix to all impacted db.t2.medium instances over the coming months, although we don't have a timeline for this roll out.

Please let us know if we can help you further on this support case.

Best regards,

MJ A. | Sydney Support Centre
Amazon Web Services
We value your feedback. Please rate my response using the link below."


Check out this article by the folks at Percona regarding duplicate UUIDs, it may be worth checking how the slave was created originally and checking out the auto.cnf settings on each server.
https://www.percona.com/blog/2014/01/21/beware-mysql-5-6-server-uuid-cloning-slaves/


How to monitor or do vacuum without stop all?

VACUUM FULL takes an ACCESS EXCLUSIVE lock on the table, then rewrites the whole table. This will take a long time for a large table, especially since it must also rebuild all indexes. You haven't said anything about the table size or the server's I/O subsystem so it's hard to say if 5 hours is unexpected or not.

You shouldn't generally need to use VACUUM FULL. Just use ordinary VACUUM - and turn autovacuum up so it works harder if you need to do a manual VACUUM more than very occasionally. It is safe to cancel the VACUUM FULL; doing so will undo all it's work, though.

You generally only need to run VACUUM FULL if you're trying to recover from severe table bloat caused by a long-running <IDLE> in transaction connection, an abaondoned prepared transaction, insufficiently aggressive autovacuum, etc. If you can't afford the downtime required for this you can look into pg_reorg as an online, reduced-locking alternative.


SQL Server 2005 error while starting service

It looks like your [model] database has become corrupted in some or another way, in particular, SQL is reporting that the log scan failed.

If you had a customised model database, you should probably have a backup of it too.

You would need your installation media to recreate the system databases, and thereafter (if you have one) restore the latest backup of your [model] database.

You can achieve this by running the following command line :

start /wait <CD or DVD Drive>\setup.exe /qn INSTANCENAME=<Instance> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword>

This will set you up with an instance without your user databases and all default settings.  You can then proceed with the restore of your [master], [msdb], and [model], and also the rest of your databases.


Using views with no insert table VS Audit tables

what database is this, make sure to check your DB if it has some inbuilt native solution.
But still if you want to develop Audit part do consider

Insert and Select are difference and independent in relational databases.
Try to trigger only the important columns you want to observer / monitor.
Make sure you have a purge algorithm ready for stale data to keep the tables size in check.
Make sure you properly partition the tables so that the selects are always efficient.


A relation is in BCNF if every determinant of the non-trivial dependencies is a superkey.

If G is the key of R2, we know that G → F and G → H.

Since DE in R1 is a subset of FG in R2, and since G → F, we can easily prove that E → D in R1.

If no other constraint holds on R1, then we can calculate the candidate keys of R1 starting from the two dependencies CD → E and E → D, and see if R1 is in BCNF or not.

Finally note that, if no other constraint is known on R2 a part from the key, it is very easy to decide if it in BCNF or not.


I am counfused about when Oracle database won't do parsing

Execute To Parse is "a ratio between the number of times a sql [statement] is executed versus the number of times it is parsed" as noted in this reference. In this context, parsed means hard parsed (which is the full parsing process that's inclusive of generating an execution plan).
The reasoning this metric exists is because whenever a new query is ran in Oracle, it needs to be parsed so that the Oracle SQL Engine can determine the best execution plan to serve the data for the query. After it has been parsed, and an execution plan is generated, that execution plan is typically cached and therefore the next time that same exact query is executed, it doesn't need to be parsed again, rather it will use the existing cached execution plan.
You can read more information on execution plans and Oracle's cache in the Oracle documentation. You can also find more information on the Execute To Parse metric here.
To answer your question "how applications "can reduce the number of parses"?": The application can do so in two somewhat obvious ways (which is why that statement in the Oracle documentation may be confusing). The first way is the application can reduce the number of parses by reducing the number of queries it executes in the database (e.g. if a query is converted to application code instead). Obviously this is a silly answer, but it's true. The second, more relevant way, is it can reduce the number of unique queries it executes in the database.
Because a cached execution plan is based on the exact query text that was sent to the database server, even the most minor change such as a comment or a different value in the WHERE clause predicates could cause Oracle to parse and re-generate a new execution plan for a very similar query to one that was previously ran. If the application is building unique query strings for the WHERE clause predicates instead of passing them as actual parameters to the database server, then that query will be parsed every time. If instead, parameters were used, then every subsequent execution of that query would result in the cached execution plan to be used and that query wouldn't get re-parsed, therefore resulting in a positive number for the metric Execute To Parse ratio.


LSN (Log Sequence Number), which incremental backup is based on, is specific to an instance. That's why knowing full copy LSN means nothing on other node.


Enable sysadmin user to restart SQL server from management studio

If you connect to SQL Server from a Machine M which has SSMS installed but your account is not local admin on Machine M you would find the option of start/stop to be disabled or greyed out. OTOH if you are local admin on the Machine M you would find the option to be enabled. When you do this from server on which SQL Server is installed, you are administrator there and hence you see the option. It does not matter whether your account is admin in SQL Server or not when you connect from machine where you do not have local admin privileges you would see the option disabled, however you would still be able to perform all sysadmin activities in SQL Server. This is not a complete statement their could be other reasons as well, this is what mostly I have observed, please check and let me know. Please also refer to SQL Server agent start stop and restart disabled blog to know more about reasons why this could happen


Part of parallel execution plan has serial portion

In answer to the question being asked (why is this portion of the query serial), I would ask another question: what would you gain from that part of the query being parallel?

Zooming in a little on one of the small serial portions of the plan that you mentioned:

There is only one row coming out of the "Merge Interval" operator.  Having that one row flow in to a parallel nested loops join would result in essentially the same behavior as the current plan: a serial nested loops join (with the added negative repercussion of there being unnecessary thread reservations).

There will be at most 2 rows coming out of the "Merge Interval" operator, depending on the specific values of the parameters being used in the query (manifested as the separate "Constant Scan" operators see this question and answer for a general explanation of what's going on there).  In the 2-row scenario, you're still unlikely to get a lot of benefit from parallelism here.

Overall: even if you were able to manage to get the NL join to be parallel, it doesn't seem like it would help you very much.

For a more general treatment of why parallelism might be inhibited in portions of a query plan, or the entire thing, see SQL not engaging parallelism for extremely large query.


having problem in insering date in mysql database

Is this Perl code?

I think the problem is that SQL doesn't recognize the date format. Just before doing the query, insert a print statement to see what exactly is in $rlsdate.

MySQL expects date in this format: '2019-03-13 16:35:42'

If your date is in unix timestamp format, you can use FROM_UNIXTIME() in your query:

$query = "UPDATE `latestmoviesdetails` SET `budget` = $budget,
                 `releasedate` = FROM_UNIXTIME($rlsdate)
          WHERE movieid = $id";

If it's in a date format that is not YYYY-MM-DD, use STR_TO_DATE() function. Example:

mysql> SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
        -> '2013-05-01'
mysql> SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');
        -> '2013-05-01'
mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
        -> '2004-04-31'


Unpredictable performance of queries against snapshot

PAGEIOLATCH_SH means waits for reading database pages.  When you create a database snapshot, none of the database pages are in the cache, and you will have to fetch them from disk.

  disk IO at these times is low (2-5mb/sec),

The query plans may be driving small, random IO, and 2-5mb/sec is all your disk can supply.

  Would it help for the snapshot to be on a different disk from the master?

A Database Snapshot is a NTFS copy-on-write snapshot of the target database.  There is only one copy of the unchanged pages shared between the database and its snapshot.  If you use a backup/restore instead of a Database Snapshot, then you could put it on a different disk.


Efficiently write SELECT queries for tables with lots of columns (not SELECT *)

We can use the ALL_TAB_COLS view along with LISTAGG to generate the queries that you need. This strategy should work as long as your tables don't go above 125 columns or so. If you have too many very long column names LISTAGG won't return the full value because the return value is limited to a VARCHAR2(4000). Below is one implementation:

SELECT 'SELECT ' || COL_LIST || ' FROM SCOTT.' || TABLE_NAME || ';' QUERY_TO_RUN
FROM
(
  SELECT DISTINCT TABLE_NAME
  ,  LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_ID)  OVER (PARTITION BY TABLE_NAME) COL_LIST
  from ALL_TAB_COLS
  WHERE OWNER = 'SCOTT'
  -- AND TABLE_NAME IN (...)
  AND COLUMN_NAME <> 'SHAPE'
) t;

In that example I'm using the built-in SCOTT schema (not sure if this is present in your database). Here are the results for me:

SELECT ENAME, JOB, SAL, COMM FROM SCOTT.BONUS;
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM SCOTT.EMP;
SELECT GRADE, LOSAL, HISAL FROM SCOTT.SALGRADE;
SELECT DEPTNO, DNAME, LOC FROM SCOTT.DEPT;


SQL Server - select column_name from (multiple tables_name stored in a variable)

Assuming your table is "similar" to next one and table names are stored there:

CREATE TABLE [MyTable]
(
    [Type] nvarchar(100),
    [Check_column] nvarchar(10)
);

INSERT INTO [MyTable] ([Type]) VALUES
    ('Starter'), ('Meal'), ('Dessert'), ('Drink');

You can dynamically build and execute UPDATE sentences using a CURSOR.

DECLARE @cmd nvarchar(max);
DECLARE @type nvarchar(10);

DECLARE cur CURSOR FOR
    SELECT [Type] FROM [MyTable];

OPEN cur;

FETCH NEXT FROM cur INTO @Type
WHILE(@@FETCH_STATUS = 0)
BEGIN
        SET @cmd = 'update [MyTable]
            set [Check_column] =
                case when exists(select 1
                                 from   ' + @Type
                              + ' where left([TVF],4) = ''M006'' or left([RPPS],4) = ''1009'')'
             + ' then ''FAILED'' else ''OK'' end'
           + ' where [Tabs] = ''' + @Type + '''';

    EXEC sp_executesql @cmd;

    FETCH NEXT FROM cur INTO @Type
END

CLOSE cur;
DEALLOCATE cur;

db<>fiddle here


DB2: how to export data file and own by db2inst1 via ADMIN_CMD

This is by design; ADMIN_CMD and other fenced routines execute using the fenced user authority in order to protect ("fence") the instance from buggy or malicious code.

Some people set the fenced user to the instance owner ID during the instance creation, which would cause fenced routines to run with the instance owner authority. However, this creates a serious security vulnerability.

It's better to ask your system administrator to allow the instance owner limited sudo or runuser rights to execute the necessary commands as db2fenc1.


Logging errors with extended events excluding some users

Since you are using sql 2008R2, you cannot use like predicate comparisons as they were introduced with SQL Sever 2012.

If you are on SQL 2012, then you can use below query to find what is available :

select xp.name, xo.name, xo.description, xo.type_name, xo.type_size from
sys.dm_xe_objects xo
inner join sys.dm_xe_packages xp on xo.package_guid=xp.guid
where object_type like 'pred_compare'

SQL 2008 R2 does not have like_i_sql_unicode_string vs SQL 2012 has it.

So, below should work on SQL 2012

CREATE EVENT SESSION [ErrorCapture]
ON SERVER
ADD EVENT sqlserver.error_reported
(
    ACTION
    (
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.sql_text,
        sqlserver.username
    )
    WHERE (
            [severity] >= (11) AND
            (NOT sqlserver.like_i_sql_unicode_string(sqlserver.username,N'test%'))

    )

)
ADD TARGET package0.asynchronous_file_target
(
    SET filename='C:\ErrorCapture_Extended_Event.xel'
)
WITH
(
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=10 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
);

Refer: Connect item Extended events predicate - NOT LIKE required


Help with combining two queries into one

Assuming your first query has error_flag and date in one of the tables then something like this would probably work:

SELECT ROW_NUMBER() OVER ( ORDER BY s.Date DESC ) AS Rownum ,
       s.cname ,
       c.HName
FROM   c
       INNER JOIN s ON (   c.pid = s.pid
                           AND c.cid = s.cid )
WHERE s.error_Flag = 'Y' AND Rownum = 1;


You can't combine them in one statement but you can wrap them into one transaction.

BEGIN TRANSACTION
  update a
  set a.value = a.value + b.value
  from Table1 a
  join Table2 b on a.ID = b.ID;

  DELETE FROM Table1 WHERE Table1.Value > @Constant;
COMMIT TRANSACTION ;


MySQL Master-Slave-Slave Configuration -error 1236

If you want to replicate from the existing 5.6 slave to another 5.6 slave, you need to setup the first 5.6 slave to behave like a master

STEP 01

Add these three(3) lines to my.cnf (under [mysqld] group header) on the first 5.6 slave

[mysqld]
log-bin=mysql-bin
log-slave-updates
skip-slave-start

STEP 02

Restart mysql on the first 5.6 slave

service mysql restart

Now, the first 5.6 slave has its own binary logs

STEP 03

Reset Binary Logs on the first 5.6 slave

mysql> RESET MASTER;

STEP 04

On the second 5.6 Slave, setup replication to the first 5.6 slave as follows:

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='existing slave',
MASTER_USER='xxx',
MASTER_PASSWORD='xxx',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=4;
START SLAVE;

STEP 5

Start replication on the first 5.6 slave

mysql> START SLAVE;

STEP 6

Verify that Replication is running on both 5.6 Slaves

mysql> SHOW SLAVE STATUS\G

If you see Slave_IO_Running: Yes, Slave_SQL_Running: Yes, and Seconds_Behind_Master is a number (preferably 0), CONGRATULATIONS !!!

GIVE IT A TRY !!!


Is Oracle 12g free to download for learning purposes?

Yes, for learning, it is free.

Software Downloads

  All software downloads are free, and most come with a Developer
  License that allows you to use full versions of the products at no
  charge while developing and prototyping your applications, or for
  strictly self-educational purposes. (Unless otherwise specified, our
  technical support organization will not provide technical support,
  phone support, or updates to you for the programs licensed under this
  agreement.) You can buy products with full-use licenses at any time
  from the online Store or from your sales representative.

Filling the registration form:

Terms of Use

  Security, Passwords and Means of Accessing the Site and Content

  If any of the Services require you to open an account, you must
  complete the registration process by providing us with current,
  complete and accurate information as required by the applicable
  registration form.


Restricting mail sending from SQL Server 2008

You just need to ensure the profile isn't public when you assign permissions and access to it.  The stored procedure in the msdb database is msdb.dbo.sysmail_add_principalprofile_sp, and this stored proc is what grants permissions for an msdb database role or user.

Excerpt taken from BOL on the subject:

  To make a profile public, specify a @principal_id of 0 or a @principal_name of public. A public profile is available to all users in the msdb database, though users must also be a member of DatabaseMailUserRole to execute sp_send_dbmail.

BOL reference on Creating a Database Mail Private Profile.


MySQL After Insert trigger - how to handle case where no rows were inserted

the After Insert trigger fires for every insert call, even in cases where the insert fails and no rows are inserted

That's not right.

Single query is a transaction, trigger action is a part of it, if fail the whole transaction is rollbacked, including changes made by trigger.

create table test (id int not null primary key, val int);

✓

create table log(id int, val int, cnt int not null auto_increment primary key);

✓

create trigger tr after insert on test for each row
insert into log (id, val) values (new.id, new.val);

✓

insert into test values (1,1), (2,2);

✓

select * from test;

id | val
-: | --:
 1 |   1
 2 |   2

select * from log;

id | val | cnt
-: | --: | --:
 1 |   1 |   1
 2 |   2 |   2

insert into test
select 3,3 union all select 2,4 union all select 5,5 order by 2;

Duplicate entry '2' for key 'PRIMARY'

select * from test;

id | val
-: | --:
 1 |   1
 2 |   2

select * from log;

id | val | cnt
-: | --: | --:
 1 |   1 |   1
 2 |   2 |   2

insert into log (id, val) values (6,6);

✓

select * from log;

id | val | cnt
-: | --: | --:
 1 |   1 |   1
 2 |   2 |   2
 6 |   6 |   4

db<>fiddle here

Pay attention to the hole in log.cnt values. Autoincrement value 3 is absent because it was used during 2nd insertion and then it was removed during rollback caused by duplicate error.


How does the transaction log record a transaction?

I could not put this as comment so here it is for you. It is not compete answer to your question because your question can either be correctly answered by person working in Microsoft or Paul Randal(I guess). All I can say for all paractical purposes every information is logged.

You can read the contents of log file using undocumented command

select * from fn_dblog(Null,Null)

If you run it you can see lot of information related to database, pages,extents,locks ect. But it would be difficult for you to extract information from it as it requires a level of expertise to decipher the output.

If you read Books Online document SQL Server Transaction Log architecture and Management it says

Many types of operations are recorded in the transaction log. These
 operations include:

  •The start and end of each transaction.

  •Every data modification (insert, update, or delete). This includes
  changes by system stored procedures or data definition language (DDL)
  statements to any table, including system tables.

  •Every extent and page allocation or deallocation.

  •Creating or dropping a table or index.

Log records for data modifications record either the logical operation performed or they record the before and after images of the modified data. The before image is a copy of the data before the operation is performed; the after image is a copy of the data after the operation has been performed.

AFAIK there is no information about query which was fired but the changes which query made is written in transaction log. Changes made to page, extents , locks that were taken, resources that were locked.


How should I setup a table of temporary/transactional items that I only need for a few days or weeks?

First, check execution plan for DELETE - may be you'll get additional ideas.

Second, I suggest you to choose from two standard patterns for such tables.

Use DELETE TOP(N) to remove data. N should be chosen
experimentally.
Use set of tables (e.g. 10 tables) to store data by day or week and "rotate" it periodically. In this case you can TRUNCATE "old" tables which is much faster than DELETE.


Can SQL Server's crash resilience be improved?

I am aware that SQL Server is not designed for such scenarios and the correct solution is to fix the cause of the power loss […]

Actually it's designed to deal with power loss, that's why there are things like write ahead logging (WAL) and crash recovery upon startup (or whatever you want to call it). One of the ways this is done is by choosing to not cache writes which it seems is what the tablet is doing, hence the corruption.

  Nevertheless, are there any tuning options in SQL Server that I can set to reduce the risk of database corruption on power loss?

No, SQL Server is doing what it should. You should look either outside SQL Server (windows settings for drive caching [which SQL wants to be off but we can't force you], hardware/firmware updates, etc.) or as Eric has said, buy an external power supply for relatively cheap which could solve the symptoms (the actual issue is probably some type of caching or battery backed write that isn't actually backed).


Query hangs in preparing state and blocks others in waiting for table flush

As @jkavalik wrote you're executing the update with a dependent subquery which effectively runs it 3861 times making it highly ineffective even if the query itself runs fast.

An updated version of the query could be:

UPDATE products
JOIN feeds_processing_data ON
   (product.source_code = feeds_processing_data.source_code
    AND ?.processing_id = ?.in_processing_id)
SET products.visible = 1
WHERE merchant_id = in_merchant_id

You didn't attach the table schemas so I just assume this would work. You might need to fully qualify the field names (using tablename.fieldname). Also feeds_processing_data could benefit from a composite index covering the involved fields.


Receiving error Error: 18059, Severity: 20, State: 1 in the SQL Logs

The post that Roni Vered referred to in 2013 did not say simply say: "change the connection pooling in the application's connection string."

The text said to that there was a problem with using "an Application Role ... with connection pooling."  The answer was: Turn Off Connection Pooling.

This could be done in the application's connection string by including "; Pooling=False".

This worked back in the mid-2000's. Likely it will still work for you if you are still using SQL Server 2008.

The no longer correct path to the original post was: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=473777&SiteID=1

A suggested workaround from 2007: On each call if the approle is already in use, then skip the setting of the approle again. Sample code:

IF NOT EXISTS (SELECT * FROM sysusers
    WHERE name = USER_NAME()
     AND isapprole = 1)
 EXEC sp_setapprole ...


pt-table-checksum and row-based replication

I do not understand why an application requires the MIXED replication format, as MIXED is equal to STATEMENT-based, but changing to ROW format when executing "unsafe" queries. So, a MIXED-compatible application should be compatible with both STATEMENT and ROW.

As you can see here, if the the format detected (server-side) is ROW or MIXED, it tries to change it dynamically for the session only. In your case, it complains about one of the slaves here (it requires SUPER privileges).

Assuming you do not have a 3-node chain replication, or you use the slave's binlog for anything, you can ignore the warning with --no-check-binlog-format. No need to change the my.cnf. The question is, will the session-only change to statement affect your "application that requires MIXED"? pt-table-checksum requires its own calculations to be done as statements, as a hash has to be calculated independently on the master and the slave independently and synchronously (not in time, but in relation with other queries).


How to send alert email to multiple recipients whenever SQL SERVER authenticated user is login in SSMS?

Here I got the answer of this question:

First i made on Table for storing history of SqlServerLoginHistory:

CREATE TABLE AuditDetail
(
ServerName VARCHAR(255),
DatabaseName VARCHAR(255),
LoginName VARCHAR(255),
HostName VARCHAR(255),
ClientNetAddress VARCHAR(255),
ConnectionTime DATETIME2
)

After that made one logon trigger.

CREATE TRIGGER [connection_limit_trigger]
ON ALL SERVER
FOR LOGON
AS
BEGIN
INSERT INTO AuditDetail
SELECT
       DISTINCT
        @@SERVERNAME ServerName ,
        DB_NAME(database_id) DatabaseName ,
        s.login_name ,
        s.[host_name] ,
        c.client_net_address ,
        c.connect_time
FROM    sys.dm_exec_connections AS c
        INNER JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
WHERE   c.auth_scheme = 'SQL'
        AND program_name IN ( 'Microsoft SQL Server Management Studio',
                              'Microsoft SQL Server Management Studio - Query' )
        AND c.connect_time >= DATEADD(DAY, -1, GETDATE())
        AND login_name <> 'prcommon' and not exists (select 1 from Auditdetail where auditdetail.connectiontime = c.connect_time);

END;
GO
ENABLE TRIGGER [connection_limit_trigger] ON ALL SERVER
GO

After that made on Simple trigger (after insert) on AuditDetail table to send alert mail .

CREATE TRIGGER auditdetailtrigger
ON AuditDetail
AFTER INSERT
AS
BEGIN
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL Admin',
                            @recipients = 'abhalala@stackoverflow.com',
                            @body = 'Customise your code',
                            @subject = 'New User Login With Sql Server Authentication';
END


It sounds like you are talking about creating a report that allows the users to choose the grouping of the data, e.g. there is a report parameter that allows the user to select "Employee", "Department" etc. which will then drive the grouping in the report data (presumably a table).

You have a couple of options to achieve this:

1) Create a dataset that uses dynamic SQL to build the query selected columns and GROUP BY clause based on the user's selection. This is probably the best performing option but in my experience is more trouble than it's worth due to the inherent difficulties of debugging or changing dynamically built SQL statements.

2) Create a dataset that contains all the possible grouping columns using a simple select statement, and then design the report to dynamically group the results based on the user selection. In your case this is the simplest choice since your data is already in a single table.

e.g. if you have a report parameter called "GroupBy", then you can set the grouping expression in the report data region (e.g. a table) to:

=Fields(Parameters!GroupBy.Value).Value

So if one of the dataset columns is called "Employee" then you need a parameter option with a value of "Employee". If the user selects this parameter option then the table grouping is set to employee. You also need to use a similar expression to set the display value in the grouping column (e.g. if the grouping is by Employee, then you need to display the Employee name).


Are the index here will suffice to make the query faster?

1,2,3,4,9,10 -- all the same pattern; all good.  The PRIMARY KEY qualifies as an INDEX (and UNIQUE).

The PK is often faster than a secondary key.  So, do not add INDEX(id) to a table that already has PRIMARY KEY(id).  There can be only one PK on a table.

6 - good

7,11 - Needs improvement:  Composite INDEX(category, date), INDEX(user_id, date)

8,12 - handled by indexes for 7,11

14,16 - OK as you state.

edit 15 - INDEX(msg_url, date)

13 - discussed in the other thread

To summarize:

users_table:

PRIMARY KEY(id),
INDEX(user_email),
INDEX(user_link),
INDEX(user_recover_link)

post_table:

PRIMARY KEY(id),
INDEX(category, date),
INDEX(post_url),
INDEX(user_url),
INDEX(user_id, date),
INDEX(msg_receiver_id, msg_sender_id, date)

msg_table

PRIMARY KEY(id),  ??
INDEX(msg_receiver_id, msg_status),
INDEX(msg_url, date)


Set the primary key for many to many relationship

In a practical system (as opposed to a textbook problem) you would want to have some kind of transaction identifier on any transaction like hiring one or more pieces of equipment.  Assuming you are running an equipment hiring business, your transactions would probably have to account for letting out multiple pieces of equipment at once.  Therefore you need to add an order table as follows:

Table Order
order_id
order_date

and your Hire_Equipment table would change as follows:

Table Hire_Equipment
customer_id (PK)
order_id (PK)
equip_id (PK)
expected_return_date

Note that the hiring_date has been normalised up to the Order level, but expected_return_date has been left at the item level, since you might want to allow a single order to contain a mixture of rental periods.

This design allows a customer to hire a piece of equipment more than once on a given date but it prevents hiring the same equipment more than once on the same order - which is what you would expect.


Best way to model a database structure to store multidimensional variables?

So you have some basic data.

SELECT timestamp::timestamp, level, lat, long
FROM ( VALUES
  (now(), 7, 1, 9)
)
  AS t(timestamp, level, lat, long);
         timestamp          | level | lat | long
----------------------------+-------+-----+------
 2017-02-08 15:38:54.903155 |     7 |   1 |    9

What you want to do is create a point with lat, and long...

SELECT timestamp::timestamp, level, ST_AsText(point::geography)
FROM ( VALUES
  (now(), 7, 1, 9)
)
  AS t(timestamp, level, lat, long)
CROSS JOIN LATERAL ST_SetSRID( ST_MakePoint(long,lat), 4326 )
  AS point;
         timestamp          | level | st_astext
----------------------------+-------+------------
 2017-02-08 15:38:00.892956 |     7 | POINT(9 1)
(1 row)

Now, we just store that into a table..

CREATE TEMP TABLE foo
AS
SELECT timestamp::timestamp, level, point::geography
FROM ( VALUES
  (now(), 7, 1, 9)
)
  AS t(timestamp, level, lat, long)
CROSS JOIN LATERAL ST_SetSRID( ST_MakePoint(long,lat), 4326 )
  AS point;

  Give me all the rows for variable SO2 that fall between latitude X and longitude Y at time T and level L.

This is not how GIS works, because between changes on a sphere. So instead, just use distance. So instead what we do

SELECT *
FROM foo
WHERE ST_DWithin(
  (ST_MakePoint(long,lat),4326)::geography,
  point,
  1000 -- distance in meters
)
FROM foo;

  What is the average temperature for time T at level L?

For this we would have had to store the temperature, assuming we did -- which we didn't. It'd look like this.

SELECT avg(temp)
FROM foo
WHERE level = l
  AND time BETWEEN start AND finish;

As a side note ST_DWithin will use an index.. (as will level and timestamp if you create the indexes). However, if you're new to GIS you create GIS indexes with GIST and not btree.

CREATE INDEX ON foo USING gist ( point );

For a similar question see this answer.

UPDATE

This file is a complex proprietary raster,

gdalinfo file.nc
Driver: netCDF/Network Common Data Format
Files: file.nc
Size is 512, 512
Coordinate System is `'
Metadata:
  NC_GLOBAL#Conventions=CF-1.6
  NC_GLOBAL#history=2017-02-08 12:30:09 GMT by grib_to_netcdf-2.0.2: grib_to_netcdf /data/data01/scratch/_mars-atls17-95e2cf679cd58ee9b4db4dd119a05a8d-mEMhIr.grib -o /data/data01/scratch/_grib2netcdf-atls00-95e2cf679cd58ee9b4db4dd119a05a8d-lrYLIt.nc -utime
Subdatasets:
  SUBDATASET_1_NAME=NETCDF:"file.nc":aermr01
  SUBDATASET_1_DESC=[45x2x451x900] aermr01 (16-bit integer)
  SUBDATASET_2_NAME=NETCDF:"file.nc":aermr02
  SUBDATASET_2_DESC=[45x2x451x900] aermr02 (16-bit integer)
  SUBDATASET_3_NAME=NETCDF:"file.nc":aermr03
  SUBDATASET_3_DESC=[45x2x451x900] aermr03 (16-bit integer)
  SUBDATASET_4_NAME=NETCDF:"file.nc":aermr04
  SUBDATASET_4_DESC=[45x2x451x900] aermr04 (16-bit integer)
  SUBDATASET_5_NAME=NETCDF:"file.nc":aermr05
  SUBDATASET_5_DESC=[45x2x451x900] aermr05 (16-bit integer)
  SUBDATASET_6_NAME=NETCDF:"file.nc":aermr06
  SUBDATASET_6_DESC=[45x2x451x900] aermr06 (16-bit integer)
  SUBDATASET_7_NAME=NETCDF:"file.nc":aermr11
  SUBDATASET_7_DESC=[45x2x451x900] aermr11 (16-bit integer)
  SUBDATASET_8_NAME=NETCDF:"file.nc":so2
  SUBDATASET_8_DESC=[45x2x451x900] so2 (16-bit integer)
  SUBDATASET_9_NAME=NETCDF:"file.nc":z
  SUBDATASET_9_DESC=[45x2x451x900] geopotential (16-bit integer)
  SUBDATASET_10_NAME=NETCDF:"file.nc":t
  SUBDATASET_10_DESC=[45x2x451x900] air_temperature (16-bit integer)
  SUBDATASET_11_NAME=NETCDF:"file.nc":q
  SUBDATASET_11_DESC=[45x2x451x900] specific_humidity (16-bit integer)
  SUBDATASET_12_NAME=NETCDF:"file.nc":r
  SUBDATASET_12_DESC=[45x2x451x900] relative_humidity (16-bit integer)
Corner Coordinates:
Upper Left  (    0.0,    0.0)
Lower Left  (    0.0,  512.0)
Upper Right (  512.0,    0.0)
Lower Right (  512.0,  512.0)
Center      (  256.0,  256.0)

You can import it by generating PAM files.

gdal_translate -sds file.nc file

Them remove the .aux.xml files with rm *.aux.xml; And, run this to import them all into the database

raster2pgsql file_* mySchema.mytable | psql -d database

This gets some kind of raster into the database. Now you have to start learning the PostGIS GDAL raster functions to see what and how you can work with it.. Using ST_MetaData we can see you have a 12 rasters with 90 bands (with ST_BandMetaData we can see they're 16bit unsigned).

SELECT rid, (metadata).* FROM foo.foo1 CROSS JOIN LATERAL st_metadata(rast) AS metadata;
 rid |     upperleftx     | upperlefty | width | height |      scalex       | scaley | skewx | skewy | srid | numbands
-----+--------------------+------------+-------+--------+-------------------+--------+-------+-------+------+----------
   1 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90
   2 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90
   3 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90
   4 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90
   5 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90
   6 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90
   7 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90
   8 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90
   9 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90
  10 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90
  11 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90
  12 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90


Removing duplicate-ish paths generated from recursive CTE

You need NOT EXISTS and the contains operator reversed:

select
    q1.*
from
    common_ancestors q1
where not exists
    ( select 1
      from   common_ancestors q2
      where  q1.id_path_array @> q2.id_path_array
        and  q1.item_id <> q2.item_id
    ) ;

Test in dbfiddle.uk


In both cases for the attempted queries in the question, the problems are simply syntactical.

In the first case: You have an IF condition, but do not group the statements after it together, so only the statement immediately following the IF is conditional. The second ALTER DATABASE statement always executes. To fix, do this:

EXECUTE master.sys.sp_MSforeachdb N'USE [?];
IF DB_ID() >= 5
BEGIN
  ALTER DATABASE [?] SET QUERY_STORE = ON;
  ALTER DATABASE [?] SET QUERY_STORE
   (OPERATION_MODE = READ_WRITE,
    INTERVAL_LENGTH_MINUTES = 30,
    MAX_STORAGE_SIZE_MB = 1000,
    QUERY_CAPTURE_MODE = AUTO);
END;
';

You probably also no not need the USE statement. It could be removed and then the DB_ID() could be changed into DB_ID(N''?'').

In the second case: You did not escape the embedded single-quotes. You also had an extraneous EXEC, and you did not include the msdb system database. To fix all of that, do the following:

EXECUTE master.sys.sp_MSforeachdb N'
IF (N''?'' NOT IN (N''tempdb'', N''master'', N''model'', N''msdb''))
BEGIN
   ALTER DATABASE [?] SET QUERY_STORE = ON;
   ALTER DATABASE [?] SET QUERY_STORE
    (OPERATION_MODE = READ_WRITE,
     INTERVAL_LENGTH_MINUTES = 30,
     MAX_STORAGE_SIZE_MB = 1000,
     QUERY_CAPTURE_MODE = AUTO);
END;
';

PLEASE NOTE: @Kris is not incorrect in saying that "sp_MSForeachDB is undocumented and unsupported", and the advice to use another mechanism to cycle through DBs is good advice. It might not be worth the trouble if this is a one-time task, but if this code is to be used repeatedly, then yes, you should probably heed that warning.


As you didn't provide us with any detailed information, may I suggest you watching the following videos from Brent Ozar (Very smart MVP).

  http://www.toadworld.com/platforms/sql-server/w/wiki/10063.find-missing-indexes.aspx

  http://www.toadworld.com/platforms/sql-server/w/wiki/10062.find-indexes-not-in-use.aspx

These videos will give you a basic idea of how indexing works and give you scripts to run against your database and check for missing indexes and indexes that are not used.

I hope these links can help you, as they did for me.


FOR XML could not serialize the data because it contains character (0x0000)

The line:

...
SELECT
    [CodFuncionario],
    STUFF
    (
        (
            SELECT
                ' / ' +
                CAST
                (
                    [DescFuncao] + '-' +
                    [DescTempoExperiencia]
                    AS VARCHAR(MAX)
                )...

Should be:

...
SELECT
    [CodFuncionario],
    STUFF
    (
        (
            SELECT
                ' / ' +
                CAST
                (
                    replace -- *** NEW! ***
                    (
                        [DescFuncao] + '-' +
                        [DescTempoExperiencia],
                        char(0),
                        ''
                    )
                    AS VARCHAR(MAX)
                )...


Oracle11.2g post-installation problem on Oracle Linux kernel

Make libclntsh executable:

chmod +rx /u01/app/oracle/product/11/lib/libclntsh*

Then check the above commented ldd command again.


Efficient table structure for application logging

1) The first thing you would need to figure out, what recovery model you should go with , i.e. decide accordingly as per the need between , FULL , BULK and SIMPLE recovery model.

Read here for configuring the same: Choosing the Recovery Model for a Database

2) When you create a new database in SQL Server 2008 and SQL Server 2005, the data and log files have the following characteristics by default:

  Data: initial size 2MB; Autogrowth: by 1MB, unrestricted growth

  Log:  initial size 1MB; Autogrowth: by 10 percent, unrestricted
  growth

Therefore the first thing you will need to look upon what is the default value you will like to give while setting up for first time.

Its better to test the activity first on test env, come up with a minimal amount of capacity planning to help define your initial size and a maximum size.

3) Once you have come up with a default values for log file, its recommended to monitor the usage of log file or track its growth:

There are various ways of doing that.

You can use below query and collect the results storing in a table over a period of time via SQL agent job:

SELECT name,
  size, -- in 8-KB pages
  max_size, -- in 8-KB pages
  growth,
  is_percent_growth
FROM sys.database_files
WHERE type_desc = 'LOG'

or using DBCC SQLPERF(LOGSPACE)

4) If a database is configured with the Full or Bulk Logged recovery model, you should back up the transaction log regularly so it can be truncated to free up inactive log space. The backup can also be used (along with the database backups) to restore the database in the event of failure.

Therefore its better to choose you're defaults correctly in order to avoid lot of event changes in log that may hamper the performance.


Script to find the list of stored procedures in all databases

You have to iterate through all databases to do that. If you're adventurous enough, you could use the undocumented stored procedure sp_MSForEachDB, which is buggy and unreliable.

Another possibility is using one of its replacements: Aaron Bertrand's or my humble attempt.

Yet another possibility is using a cursor to loop through all databases:

USE master;

DECLARE @name sysname;
DECLARE @sql nvarchar(max) = '
    SELECT
        DB_NAME() AS [database_name],
        OBJECT_SCHEMA_NAME(object_id) AS [schema_name],
        name AS [procedure_name]
    FROM sys.procedures
';
DECLARE @theSQL nvarchar(max);

DECLARE @results TABLE (
    [database_name] sysname,
    [schema_name] sysname,
    [procedure_name] sysname
);

DECLARE dbs CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT name
FROM sys.databases;
-- you may want to exclude system databases here
-- WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'distribution')

OPEN dbs;
FETCH NEXT FROM dbs INTO @name;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @theSQL = 'EXEC ' + QUOTENAME(@name) + '.sys.sp_executesql @sql';

    INSERT @results
    EXEC sys.sp_executesql @theSQL, N'@sql nvarchar(max)', @sql

    FETCH NEXT FROM dbs INTO @name;
END

CLOSE dbs;
DEALLOCATE dbs;

SELECT *
FROM @results;


How to find all invalid document based on jsonSchema validator?

You can use the $jsonSchema query operator to find relevant documents.
For example, using the mongo shell:
// Retrieve the current validator for the "students" collection
var schema = db.getCollectionInfos({ name: "students" })[0].options.validator

// Find any documents not matching the schema
db.students.find({ $nor: [schema] })

Similarly, you can use the $jsonSchema query operator to confirm expected matches before adding or updating the validator for a collection:
var schema = {
  $jsonSchema: {
    properties: {
      name: {
        bsonType: "string",
        description: "name",
      },
    },
    required: ["name"],
    bsonType: "object",
  },
}

// Find documents matching schema
db.students.find(schema)


How to Select all child elements of a given Node in SqlHierarchyId?

A worked example of what Mark's talking about, using IsDescendantOf:

DECLARE @t TABLE ( hId hierarchyId PRIMARY KEY )

INSERT INTO @t VALUES
    ( '/1/' ),
    ( '/1/2/' ),
    ( '/1/2/1/' ),
    ( '/1/2/2/1/' ),
    ( '/3/' ),
    ( '/3/4/' )

DECLARE @hId hierarchyId = '/1/2/'

SELECT *, hId.ToString() hId_string
FROM @t
WHERE hId.IsDescendantOf(@hId) = 1


That is the MySQL documentation, not the Oracle Database documentation.
There is no such clause in Oracle Datababase.
Use a trigger. Here is an example: link


MySQL Finding index size for compound unique indexes

For MySQL (not Percona), see if this table gives you what you want:

mysql.INNODB_INDEX_STATS

Sorry, but it is not easy to tease out the specific data you asked about.


Yep, that's basically right. FYI, the ON_ERROR_STOP business is just a hint to psql that you want it to stop (and, in your case, effectively rollback by closing the connection) immediately upon an error, instead of flooding you with a bunch of messages like "ERROR:  current transaction is aborted" for each subsequent command in the transaction.

There are a few exceptions, see some hints in this answer. But if you try to stick a command in your script.sql which is not allowed to run inside a transaction block, PostgreSQL should give you an error like "... cannot run inside a transaction block" and your transaction should get rolled back.

Of course, you'd be well advised to try your migration script on a test/backup instance (you do test your backups somewhere, right?) first.


Database as a service (DbaaS) over dimensions--performance, scalability, & reliability?


How to access row value from UPDATE sub query function?

When using a query of this form: UPDATE a SET columns=values FROM b WHERE ..., the b subquery is evaluated independently of a, and it's the WHERE block at the end of the query that is supposed to join the rows of a and b.

The query in the question attempts to join inside the subquery rather than in a WHERE clause at the uppermost level (in fact it doesn't have such a WHERE clause), but it's not allowed and that's what the error message indicates.

The SQL-standard way of updating each row with a correlated subquery would be:

UPDATE a SET (col1,col2) =
   (SELECT val1,val2 FROM b WHERE expression-needing-values-from-a)

but in older versions of PostgreSQL (9.4 or before), the manual explicitly tells that this form is not implemented. Starting with 9.5, it is implemented.

The typical way to emulate it with postgres 9.4 is to use a copy of table a in the subquery and join back to the updated table in the upper level's WHERE clause, like this:

 UPDATE a SET (col1,col2)=(s.col1,s.col2)
  FROM (SELECT a2.ctid,col1,col2 AS ctid_of_row FROM b, a AS a2 WHERE...) AS s
   WHERE s.ctid_of_row = a.ctid;

knowing that ctid is the system pseudo-column that indicates the physical location of the row version within its table. Alternatively, the primary key can be used if there is one.

In your case, the transformation is not obvious because your subquery is meant to produce only one row with its ORDER BY... LIMIT 1 at the end. It should be redesigned to produce all the target rows in a single resultset, presumably (verticeid,cost,gid) where cost and gid are determined per offer.verticeid.


How large will a MySQL database be relative to the dump file?

Just from the words in your question I suspect the following: You most likely have innodb_file_per_table disabled.

NOTE : The following info is based on innodb_file_per_table being disabled

When insert data into InnoDB tables, everything and its grandmother lands in the system tablespace file, better known as ibdata1. What does ibdata1 actually contain?

Table Data
Table Indexes
Metadata
MVCC Info

Table Data and Indexes while initially bloat ibdata1. The metadata is just simply the data dictionary + the list of tablespace_ids assign on a per-table basis.

What about MVCC (Multiversioning Concurrency Control)? This represents the systrem objects designed to support transaction isolation, rollbacks, undo logs, insert buffers for secondayr indexes, and the double- write buffer.

You need to clean up the InnoDB infrastructure. I already wrote StackExchange posts on how and why to do this:

Why does InnoDB store all databases in one file?
Is innodb_file_per_table advisable?
https://stackoverflow.com/a/4056261/491757
https://serverfault.com/a/231400/69271

Getting back to your original question, the only way to estimate the size of the ibdata1 on reload would have been to run this query before the mysqldump:

SELECT
    data_length/power(1024,3) InnoDBData,
    index_length/power(1024,3) InnoDBIndexes,
    (data_length+index_length)/power(1024,3) InnoDBSize
FROM
    information_schema.tables
WHERE
    engine='InnoDB';

This will report the size of the data in GB. Upon a fresh reload of the ibdata1 (with innodb_file_per_table disabled in your case), this would have been the rule of thumb for the size estimation.

From the dump file size, it is hard to judge because the combined total size of data pages and index pages maybe far less that the size of ibdata1 the dump was created from. That difference would be have leftover space from the bloating of MVCC system objects (rollback segments, undo logs, double-write buffer, secondary index insert buffer). From another perspective, data pages could outnumber index pages, and vice versa. This could be due to too many indexes, bad design, or just a judicious amount of data.


Use flag on each row to mark record as primary or use foreign key on parent table

Which solution you chose is largely a matter of personal preference. I have a strong preference for the foreign key approach, and think I can provide good reasons why.

First of all, check that your requirement is strictly to capture a single "primary supplier", and not (potentially multiple) "preferred suppliers". If you can't guarantee that you'll never have more than 1, the foreign key approach will break and you're better off going with the flag approach from day 1.

If, however, a part always has a single primary supplier I would use the foreign key approach. If a part must have a primary supplier, then this is the only approach where the database constraints guarantee that the data is always correct -- make the foreign key column mandatory (not null) and you will have to provide the primary supplier when adding the part. There is simply no way that the data cannot be correct.

With the flag approach, the primary supplier information can be missing from parts -- unless you want to rely on your application's logic. If that's potentially a problem, the mandatory foreign key approach is the easiest way to ensure this can never happen.

Think also about updates. With the flag approach, you need to clear the flag from other "part supplier" records when you change preferred suppliers.


When used to start the instance, pg_ctl accepts options to the postgres executable, passed with -o

From its manpage:

  -o options
       Specifies options to be passed directly to the postgres command.

       The options should usually be surrounded by single or double quotes
       to ensure that they are passed through as a group.

So your last command in the question should be amended as:

/usr/lib/postgresql/9.3/bin/pg_ctl start \
   "-D" "/var/lib/postgresql/9.3/main" \
   "-o -c config_file=/etc/postgresql/9.3/main/postgresql.conf"


How do I swap tables atomically in Oracle?

Create a public synonym that points to the new table.

CREATE PUBLIC SYNONYM foo FOR foo_new;
RENAME foo TO foo_tmp;

-- At this point in time, any DMLs for foo will operate on foo_new, via the synonym

RENAME foo_new TO foo;
DROP PUBLIC SYNONYM foo;

Things will get a bit more complicated if you're accessing the tables involved with a user other than the schema owner, but this works if you're only accessing the table with the schema owner.


Row inserted to DB in main app is not available in the different process. Can MySQL defer some queries?

Use Innodb.

Do not use LOCK TABLES.

Do

BEGIN;
-- some SQL statements that need to be treated atomically:
INSERT INTO my_table ...;
INSERT INTO my_queue ...;
COMMIT;

By using BEGIN and COMMIT, you can ignore the setting of autocommit, which may be tripping you up.

There may be some tricky business going on in the queue; please show us the SQL for enqueuing and dequeuing, pluse SHOW CREATE TABLE.


delete database and configure database options in DBCA greyed out (disabled)

DBCA relies on /etc/oratab. If that file is empty, or the entry for the database you want to delete is missing, the option(s) will be disabled.


What is the correct way to map relationships between structurally similar comments tables and commentable entities?

I think you should really look into using inheritance to manage this particular issue.

Inheritance

Table inheritance does many things in PostgreSQL, and I'd highly recommend you refer to the online documentation about inheritance to get a feel for what it does and how it might work for you.

Since you stated that your comments are all structurally similar, then you can make a parent comments table as

CREATE TABLE comments (
/* columns here */
);

Then you can create the child comment tables as (for example)

CREATE TABLE posts_comments (
/* any columns unique to this table here */
) INHERITS(comments);

Now, posts_comments will have all columns from comments, plus any additional column fields which you believe are necessary for that particular table.  Rinse and repeat to make other tables which are children of the comments table.

Further, you can then query for all comments which match a particular condition

SELECT * FROM comments
WHERE condition = true;

and all comments meeting you condition will be returned.  That is, every comment from the comments table (if there are any entries, since this might just be an empty parent table), plus all comments from the child tables posts_comments, profile_coments, etc. will be returned by the SELECT.

You can also have ALTER TABLE propagate changes to all child tables through this structure.

Does this meet your needs?


If a job has to run in an interval, the job needs to be in the state 'SCHEDULED' in between. In case of a job chain, make sure that it actually has an ending, otherwise it will remain running forever.

DBA_RUNNING_JOBS only shows the jobs that are in state 'RUNNING'
make sure the chain has an END action.


SQL Server 2014 schema changes in 24/7 multi user environment

Below will require a bit of more planning and testing.

Blue-Green Concept :

The gist of Blue-Green Concept is to divide your production into 2 environments and they are identical all times (data synchronization) wherein

The Blue (Current) will have the current version of the schema/build or product and will be your "LIVE" environment.
At the same time Green will be your staging/testing environment wherein you will upgrade your schema/build or product to the NEXT release, do a full regression test and get signed off by your business users. Once happy, during a cut-over period, you will promote the Green to be your "LIVE" environment and demote the Blue to be a preprod/staging or testing for the next release.

This way, you have a very less downtime and the risk of deployment failure on a live system (which is in maintenance window, since you are doing upgrade) will be highly minimized. Also, following the Blue-Green approach, you will be oscillating between LIVE and PREVIOUS version which will be staging for the next version.

Again, this will require more hardware/licensing as well as planning and testing.

Most of the steps can be automated using DACPACs and PowerShell. Also, if you are installing multiple instances on one server, make sure to re-balance the Memory settings when switching between Blue and Green. The LIVE environment gets more memory than the Passive environment.

In my current environment, we have implemented Blue/Green Model for Agile Code Deployment that allows us to promote code every 2 weeks with ample amount of time for testing and business sign-off. Also, its a breeze to rollback in case something goes horribly wrong. We have automated majority of the deployment stuff using Dacpacs and PowerShell.

(Image source)

Also refer to Grant Fritchey's article on Rollback and Recovery Troubleshooting; Challenges and Strategies


Apparently this is a MySQL extension, the SQL 2011 spec defines the insert grammar as,

<insert statement> ::=
INSERT INTO <insertion target> <insert columns and source>
14

<insertion target> ::=
<table name>

<insert columns and source> ::=
<from subquery>
| <from constructor>
| <from default>

<from subquery> ::=
[ <left paren> <insert column list> <right paren> ]
[ <override clause> ]
<query expression>

<from constructor> ::=
[ <left paren> <insert column list> <right paren> ]
[ <override clause> ]
<contextually typed table value constructor>

<override clause> ::=
OVERRIDING USER VALUE
| OVERRIDING SYSTEM VALUE

<from default> ::=
DEFAULT VALUES

<insert column list> ::=
<column name list>

You can see this talked about on the Pg Lists


What is MSrepl_tran_version? (MS SQL Server)

what, exactly, is msrepl_tran_version? We have it in all our tables (a rather large number), but I don't believe we're using replication. I guess we did at some point in the past, maybe.

when you have configured Transactional replication with updatable subscriptions, replication adds the column msrepl_tran_version to every table. If the table is dropped from the publication, the msrepl_tran_version column is not removed.

Check with your DBA to see if you still have replication or not.

If you are not using Replication, then to see how this column gets updated, I would suggest you to use profiler.

IF this column is not being used anymore, you should be good to remove/drop it. Refer to my answer with script here.


Oracle 11g alternative options for oracle 12c with function

Create the functions within a PACKAGE.

Beyond that, you are using a feature that was introduced in 12c.


What is a practical use of FOR STATEMENT triggers?

I cannot speak to all database platforms that support statement level triggers, but DB2 LUW also supports both row level triggers AND statement level triggers.

Referencing the documentation for DB2 LUW (highlighting mine) - Specifying what makes a trigger fire (triggering statement or event)

  When a trigger is activated, it runs according to its level of
  granularity as follows:

  FOR EACH ROW

  It runs as many times as the number of rows in the set of affected rows.

  If you need to refer to the specific rows affected by the
  triggered action, use FOR EACH ROW granularity. An example of this is
  the comparison of the new and old values of an updated row in an AFTER
  UPDATE trigger.

  FOR EACH STATEMENT

  It runs once for the entire trigger event.

  If the set of affected rows is empty (that is, in the case of a
  searched UPDATE or DELETE in which the WHERE clause did not qualify
  any rows), a FOR EACH ROW trigger does not run. But a FOR EACH
  STATEMENT trigger still runs once.

For example, keeping a count of number of employees can be done using FOR EACH ROW.

CREATE TRIGGER NEW_HIRED
  AFTER INSERT ON EMPLOYEE
  FOR EACH ROW
  UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1

You can achieve the same affect with one update by using a granularity of FOR EACH STATEMENT.

CREATE TRIGGER NEW_HIRED
  AFTER INSERT ON EMPLOYEE
  REFERENCING NEW_TABLE AS NEWEMPS
  FOR EACH STATEMENT
  UPDATE COMPANY_STATS
  SET NBEMP = NBEMP + (SELECT COUNT(*) FROM NEWEMPS)

Some examples of why you might want to use a statement level trigger over a row level trigger:

You want to record how many rows were updated, but don't necessarily
care about which rows were updated
You want to execute an action when a statement is made against a
table, even if no rows are updated
Your table has 40 Billion rows and sometime updates are made against
10% of the table. Recording an action for 4 Billion rows, one at a
time, is not feasible due to resource constraints
You want to record any time any person makes an update to a table,
but you don't care what the update was, and you don't care how many
rows were affected.


designing multiple tables while eliminating duplication?

My recommendation would be to have the subject table with just subject_name and subject_id, have the grades table with just the grade_name and grade_id. Then create a third table called subject_grades that has the columns subject_id and grade_id, on this table create foreign keys to the subject.subject_id column and the grades.grades_id column. I'd highly recommend putting a primary key over both of the columns in this table or at least a unique key.

I hope this helps you.


Windows native backups makes SQL Server think that databases backup has been done

This KB isn't directly related but the quote in it is:

MSKB2615182: Windows Server Backup May Fail Because of the SQL VSS Writer:

  When Windows Server Backup attempts to backup a disk volume, a Volume Shadow Copy Snapshot is created for the volume. When the snapshot is created any VSS writer associated with the volume is called.

If there are SQL Server VSS writer backups being taken then you will see records in the MSDB.dbo.BackupMediaFamily table in the physical_device_name column. You might want to look into disabling the SQL Writer Service.

TechNet: SQL Writer Service


Pull the latest Employee and Manager Comments Based on Modified date

Thank you for the schemas. I did modify the employee table to include a manager bit flag which makes the query a bit easier. This doesn't exactly reproduce your desired results, but it is very close. If there is no manager comment on the last plan do you need to include the manager ID? Also, I wasn't sure on the date to include so the date is there for each comment.

--Load base tables
CREATE TABLE #Comments
(EmpFK INT
, PlanFK INT
, Comments VARCHAR(10)
, ModifiedDate DATE)

CREATE TABLE #Plan
(PlanFK INT
, PlanName VARCHAR(10))

CREATE TABLE #Employee
(EmpPK INT
, MgrPk INT NULL
, Mgr BIT
, EmpName VARCHAR(10))

INSERT INTO #Comments VALUES (60,7,'empCmnt1','2016-04-13')
INSERT INTO #Comments VALUES (61,7,'mgrCmnt1','2016-04-14')
INSERT INTO #Comments VALUES (60,7,'empCmnt2','2016-04-18')
INSERT INTO #Comments VALUES (61,7,'mgrCmnt2','2016-04-19')
INSERT INTO #Comments VALUES (60,8,'empCmnt1','2016-03-01')

INSERT INTO #Plan VALUES (7,'Name1')
INSERT INTO #Plan VALUES (8,'Name2')

INSERT INTO #Employee VALUES (60,61,0,'user1')
INSERT INTO #Employee VALUES (61,null,1,'mgr1')

--Load Temp table with the last comment per employee per plan

CREATE TABLE #LastComment (EmpFK INT, PlanFK INT, Comments VARCHAR(10), ModifiedDate DATE, Mgr BIT)

INSERT INTO #LastComment
    SELECT Comment.EmpFK, Comment.PlanFK, Comment.Comments, Comment.ModifiedDate, Emp.Mgr
    FROM #Comments as Comment
    JOIN (SELECT c.EmpFK, c.PlanFK, MAX(c.ModifiedDate) as MaxCommentDate
        FROM #Comments as c
        GROUP BY EmpFk, PlanFk
        ) as LastComment on Comment.EmpFK = LastComment.EmpFK AND Comment.PlanFK = LastComment.PlanFK AND Comment.ModifiedDate = LastComment.MaxCommentDate
    JOIN #Employee AS Emp on Comment.EmpFK = Emp.EmpPK

SELECT EmpLC.EmpFK, MgrLC.EmpFK, EmpLC.Comments, MgrLC.Comments, EmpLC.ModifiedDate, MgrLC.ModifiedDate, P.PlanFK
FROM #Plan AS P
LEFT JOIN #LastComment AS EmpLC ON P.PlanFK = EmpLC.PlanFK AND EmpLC.Mgr = 0
LEFT JOIN #LastComment AS MgrLC ON P.PlanFK = MgrLC.PlanFK AND MgrLC.Mgr = 1


What are possible ways of handling multiple users accessing the same database via custom web API?

Don't connect as root/super user

Read this: Multi-Tenant Data Architecture

Since it's a web app, you're going to want to use connection pooling, for performance

This means you need to use impersonation, i.e., login as one user then switch to a different user. Because connection pooling requires that everyone in the pool connect with the same connection string.

For Maria/MySQL, read up on https://mariadb.com/kb/en/mariadb/grant/#proxy-privileges

MySQL/Maria doesn't yet do row security as far as I know, so you'd need to do your access control (via SQL) in the application layer (your web app)

In terms of multiple people editing the same rows at the same time, read up on Optimistic Concurrency Control

Personally, I'd consider Postgres, as it does row security


MySQL - Small table popping up in slowlog, refuses to use index

By default, MySQL logs ALL queries not using an index, but you can change that by setting a nonzero value for min_examined_row_limit - set it to 100 and these tables won't pollute your log anymore.


SQL to update table with records from second table (but only those with distinct value in a given column)

Use NOT EXISTS to check that no matching record already exists in t1 and the row_number() window function to pick only one row per t2.d.

INSERT INTO t1
            (a,
             b)
            SELECT x.d,
                   x.e
                   FROM (SELECT t21.d,
                                t21.e,
                                row_number() OVER (PARTITION BY t21.d) rn
                                FROM t2 t21
                                WHERE NOT EXISTS (SELECT *
                                                         FROM t1 t11
                                                         WHERE t11.a = t21.d)) x
                   WHERE x.rn = 1;

I don't know how t1.c should be calculated, that's not clear from your post. So I left this part out. But maybe this helps to solve the main problem.


Consider the following odetails data:

ono   pno
---------
1     1
1     2

If you join it on itself by ono, like

SELECT pno
FROM odetails d1 JOIN odetails d2 ON d1.ono = d2.ono;

then you will get four rows instead of two.  So the two queries are different in their meaning even though they return the same data (thanks to the DISTINCT keyword).

(There is a possibility for similar problem with joining the orders table twice.  As you did not mention the exact requirements for this query, one can't be sure if even the first query is correct.  (Acknowledgment to ypercube for mentioning this.))


Should I turn to simple recovery model and truncate?

That would be the worst thing to do it would break away mirroring and if you have big database you would again have to follow the whole process of creating mirroring again that could well be a tedious task.

  Should I stop mirroring, do what I should do to principal database and then enable mirroring?

Stopping mirroring is not a solution here and not going to help you. You need to find why your database log file grew so much my guess is huge DML operation is scheduled or ETL process taking place or rebuild of all indexes followed by update stats.

  Should I create a new log file to another disk?

I dont know where you read this but this is no where related to your problem. Writing to log file is sequential and only after completely utilizing first log file SQL Server will move to second so this is again not going to help

  My database is in Full Recovery Model. Backups for both database and log file taken every day

How often you take log backup, is it just once in a day. In full recovery model only transaction log backup would truncate log and allow other transactions to use log file and thus limiting the size of log so I would first suggest you to increase the frequency of transaction log backup.

At last you must read why transaction log keeps growing and run out of space


Pass Parameters to trigger function to execute dynamic SQL

The error msg says relation "model_cd201707" does not exist. I suppose there is no table of that name in the search_path then? There is a confusion of table names in your question. modelsample..., model_cd..., model... tb_moldsummary. Something is not right here ...

That aside, after assigning passed parameter values to the variables model_cd and processdate you are not using them at all (yet).

Assuming your 2 passed parameters are supposed to be used to build the table name, it might work like this:

CREATE OR REPLACE FUNCTION trg_insert_data_func()
  RETURNS TRIGGER AS
$func$
BEGIN
   EXECUTE format(
   $x$INSERT INTO tb_moldsummary AS t
      SELECT $1.machine
           , $1.model
           , split_part($1.lot, '_', 1)
           , right($1.lot, position('_' IN lot) * -1)
           , COUNT(*)  -- never null in this query
           , $1.datetimestamp
      FROM   %I
      WHERE  lot = $1.lot
      AND    machine = $1.machine
      GROUP  BY machine, model, lot
      ON     CONFLICT ON CONSTRAINT tb_summary_unique  -- constraint has same name?
      DO     UPDATE
      SET    machine = $1.machine
           , totalshots = t.totalshots + 1
           , datetimestamp = $1.datetimestamp
   $x$, TG_ARGV[0]
   )
   USING NEW;
   RETURN NEW;
END
$func$
LANGUAGE plpgsql;

Trigger (passing a single concatenated parameter as table name):

DO
$$
BEGIN
   EXECUTE format(
     'CREATE TRIGGER insert_data_trigger
      AFTER INSERT ON %1$s
      FOR EACH ROW EXECUTE PROCEDURE trg_insert_data_func(%1$L)'
    , 'modelsample' || to_char(now(), 'YYYYMM')
   );
END
$$;

Note how I use %1$s for the (unquoted) table name in the SQL statement and %1$L for the parameter (quoted as string literal). %I is not needed in this case for a known legal identifier. See the manual on format() for details.

You may want to schema-qualify function and table names to be unambiguous. Like public.modelsample. More:

Special syntax sth.name() in the CREATE FUNCTION statement, what does it mean?

Be aware that the date component built into the trigger definition is immutable once created. Only the creating statement itself (and the trigger function) are "dynamic".

Related:

Trigger function taking column names as parameters to modify the row

But that seems like needless complication. No need to pass the name of the table for which the trigger is called. Some special variables are available automatically in plpgsql trigger functions. Among others: TG_TABLE_SCHEMA and TG_TABLE_NAME. So use dynamic SQL in the trigger function, but the trigger itself can be simple and static:

CREATE OR REPLACE FUNCTION trg_insert_data_func()
  RETURNS TRIGGER AS
$func$
BEGIN
   EXECUTE format(
   $x$INSERT INTO tb_moldsummary AS t
      SELECT $1.machine
           , $1.model
           , split_part($1.lot, '_', 1)
           , right($1.lot, position('_' IN lot) * -1)
           , COUNT(*)  -- never null in this query
           , $1.datetimestamp
      FROM   %I.%I     -- !!
      WHERE  lot = $1.lot
      AND    machine = $1.machine
      GROUP  BY machine, model, lot
      ON     CONFLICT ON CONSTRAINT tb_summary_unique  -- constraint has same name?
      DO     UPDATE
      SET    machine = $1.machine
           , totalshots = t.totalshots + 1
           , datetimestamp = $1.datetimestamp
   $x$, TG_TABLE_SCHEMA, TG_TABLE_NAME   -- !!
   )
   USING NEW;
   RETURN NEW;
END
$func$
LANGUAGE plpgsql;

And no parameters in the trigger itself.


How can I import a database dump (.sql) using pgAdmin without the PSQL Console plugin?

I have used this pl/pgsql function to create one .csv file per table:

CREATE OR REPLACE FUNCTION db_to_csv(path TEXT) RETURNS void AS $$
declare
  tables RECORD;
  statement TEXT;
begin
  FOR tables IN
    SELECT (table_schema || '.' || table_name) AS schema_table
    FROM information_schema.tables t INNER JOIN information_schema.schemata s
    ON s.schema_name = t.table_schema
    WHERE t.table_schema NOT IN ('pg_catalog', 'information_schema', 'configuration')
    ORDER BY schema_table
  LOOP
    statement := 'COPY ' || tables.schema_table || ' TO ''' || path || '/' || tables.schema_table || '.csv' ||''' DELIMITER '';'' CSV HEADER';
    EXECUTE statement;
  END LOOP;
  return;
end;
$$ LANGUAGE plpgsql;

And I use it this way:

SELECT db_to_csv('/home/user/dir');
-- this will create one csv file per table, in /home/user/dir/


to_tsquery - Does it always accept quert text?

If the values can be arbitrary strings, you can get errors:
SELECT to_tsquery('a&');
ERROR:  no operand in tsquery: "a&"
SELECT to_tsquery('''q');
ERROR:  syntax error in tsquery: "'q"

Use plainto_tsquery, which is designed to accept plain text strings.


Security setting for SQL Server services

The engine provisioning Setup makes them sysadmin members, as documented at Configure Windows Service Accounts and Permissions:

  SQL Server Per-service SID Login and Privileges The per-service SID of
  the SQL Server service is provisioned as a Database Engine login. The
  per-service SID login is a member of the sysadmin fixed server role.

  SQL Server Agent Login and Privileges The per-service SID of the SQL
  Server Agent service is provisioned as a Database Engine login. The
  per-service SID login is a member of the sysadmin fixed server role.

If the product Setup makes them sysadmin is better you follow the same practice.


Is xp_create_subdir supported on Linux SQL Server

According to the latest official release notes, no it is not supported.

The following features and services are not available on Linux at this time. The support of these features will be increasingly enabled during the monthly updates cadence of the preview program.
...
System extended stored procedures (XP_CMDSHELL, etc.)

It does say that there will be increasingly available but does not make any commitment toward a time or viability at release.


Problem has been fixed. So I'll try to decribe solution to this problem and maybe for someone it'll be useful and save time. In the beginning we have an error message from wizard:

  "Failed to deploy the project. Try again later. (Microsoft SQL Server,
  Error: 27118)"

Let's see detailed error, to do this just need to click on "Show technical details button". In the section "Additional data" we can find object wich is responsible for this error. In my case it was procedure prepare_deploy. This is an internal store procedure of database SSISDB. To catch an error I used statement RAISERROR in deferent places, because this error appears in different places. Thus my problem was there:

IF EXISTS (SELECT [project_id] FROM [internal].[projects]
            WHERE [folder_id] = @folder_id AND [name] = @project_name)
    BEGIN
        RAISERROR(27118, 16, 1) WITH NOWAIT
        RETURN 1
    END

But to see this error you should have true in the following condition: IF(@project_id IS NULL) See whole statement which appears earlier in the store procedure:

    SET @project_id = (SELECT [project_id] FROM [catalog].[projects]
                   WHERE [folder_id] = @folder_id AND [name] = @project_name)

    IF(@project_id IS NULL)

    BEGIN
    ...

So we have almost identical SELECT statements except schema, but results are different: first statement gives us nothing although second gives some result.
The schema [catalog] says that we have a deal with view. Take a look on the view:

CREATE VIEW [catalog].[projects]
AS
SELECT     ...

FROM       [internal].[object_versions] ver INNER JOIN
           [internal].[projects] proj ON (ver.[object_id] = proj.[project_id]
           AND ver.[object_version_lsn] = proj.[object_version_lsn]) INNER JOIN
           [internal].[folders] ON proj.[folder_id] = [internal].[folders].[folder_id]
WHERE      (ver.[object_status] = 'C')
           AND (ver.[object_type]= 20)
           AND (
                  proj.[project_id] IN (SELECT [id] FROM [internal].[current_user_readable_projects])
                  OR (IS_MEMBER('ssis_admin') = 1)
                  OR (IS_SRVROLEMEMBER('sysadmin') = 1)
                )

Obviously, to see some result from this view user should have the permission either ssis_admin or sysadmin. Let's see who executes procedure internal.prepare_deploy

ALTER PROCEDURE [internal].[prepare_deploy]
    ...
WITH EXECUTE AS 'AllSchemaOwner'
AS

Finally, checking permission for user AllSchemaOwner in database SSISDB:

Bingo, we don't have appropriate permissions to see data from view [catalog].[projects]. Just adding user AllSchemaOwner to role ssis_admin and problem is being gone.


How to use array variable in query in PostgreSQL

You manage to get it wrong in just too many ways. To begin with, there is no need for dynamic SQL at all.

The best way to "solve this problem" is probably to start with the basics. Read the excellent manual about CREATE FUNCTION, PL/pgSQL and SQL functions.

A PL/pgSQL function could look like this (one of several variants):

CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[], OUT xrc) AS
$func$
BEGIN
SELECT INTO xrc name FROM t1 WHERE xcheck @> xch;
END
$func$ LANGUAGE plpgsql

Or, simpler as plain SQL function:

CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[])
  RETURNS text AS
$func$
SELECT name FROM t1 WHERE xcheck @> $1;
$func$ LANGUAGE sql;

Or study some of the plpgsql examples that have been posted here.


Updating a SQL Server Extended Event session to add data storage - is it possible?

To keep the history just stop the session and copy all the event files for that session to another place. You can query them like the example provided by Dan Guzman's answer. You can query those files even if the event session that created them no longer exists.
About the files never reaching the specified size, it might be related to the chosen size, as you can see on the Event File Target doc:

When max_file_size is set to a size larger than the size of the
session buffers, it may be rounded down to the nearest multiple of the
session buffer size. This may create a target file that is smaller
than the specified value of max_file_size. For example, if the buffer
size is 100MB and max_file_size is set to 150MB, the resultant file
size is rounded down to 100MB because a second buffer would not fit in
the remaining 50MB of space.

Also, by stopping and starting a session a new file is created even though the limit size has not been filled on the previous file.
To alter the max_file_size of an existing XE Session (let's say the one from the answer I linked above) it would be something like this:
ALTER EVENT SESSION [errors] ON SERVER
DROP TARGET package0.event_file;

ALTER EVENT SESSION [errors] ON SERVER
ADD TARGET package0.event_file(
    SET
        filename=N'C:\TraceFiles\errors',
        max_rollover_files=(5),
        max_file_size=(512)
    );


Slony not replicating changes from a dump file

Make sure you are not using the --disable-triggers option of pg_dump since slony replication is based on triggers. Check the dump file for ALTER TABLE <table_name> DISABLE TRIGGER ALL statements.


Can I prove my database does not have memory issues?

The only way to prove is not a memory issue is by prooving is something else. And this requires you to identify the root cause of your performance problems. I recommend you follow a methodology like Waits and Queues. The SQLCAT team has also published a Troubleshooting Flowchart poster that you can follow.

As a general side comment: if someone is offering you to buy more memory on the server, just say YES, and then go ahead and do the root cause analysis. It doesn't matter that if the root cause of the problems are table scans, SQL Server can always use more memory.


You shouldn't need to uninstall/install: your data and log files should be on separate disk arrays/SAN Luns from the binaries.

Saying that, 64k NFTS cluster is highly recommended all over the place.
SQL Server does IO in extents which is 8x8k pages = 64k, basically.

For actual numbers on performance differences, I can only find this http://tk.azurewebsites.net/2012/08/ (Azure, but still SQL Server)


MySQL database cannot accept SSL connections (Certs are UTD)

The issue was not with the database, but with the connector. As of v8.0.8 of the MySQL connector, they made it default to use SSL. If you want to not use SSL you just have to add "SslMode=none" to the end of your connection string.


How to Ensure of Data MariaDB Galera Cluster's Safety?

Well... It seems that there is no way to implement auth unless I use mysqldump but finally I found an alternative way to secure my SST using certificate and private key.

Just self-sign a CA, and create a server private key and certificate, add them to wsrep_provider_options and everything is done. The attackers cannot retrieve a copy of SST as he cannot provide the same key pair for data signature and encryption.

The configuration file should be something like this:

[mysqld]
...
wsrep_provider_options="socket.ssl_key=/path/to/private.key; socket.ssl_cert=/path/to/certificate/file.crt; socket.ssl_ca=/path/to/CA.crt; socket.ssl_compression=YES";

I have written an article about how to implement this idea in my blog, though it is in Simplified Chinese.


Microsoft recoomends as an option if you are experiencing buffer pool paging (http://support.microsoft.com/kb/918483).


SQL Server 2012 - sp_executesql multiple parameter error

You can't parameterize column and table names, sorry. You'll need to do this the old way:

SET @tableCreateSQL = N'SELECT ' + QUOTENAME(@ID1) + N'
               INTO   dbo.' + QUOTENAME(@StageTable1) + N'
               FROM   dbo.' + QUOTENAME(@StageTable2) + N';';

Also see:

Protecting Yourself from SQL Injection in SQL Server - Part 1
Protecting Yourself from SQL Injection in SQL Server - Part 2


As Aaron Bertrand says in the comments, having a .mdf file as the method for adding a database to a server is unusual.  Normally you should have a .bak backup file.  If at all possible, contact your source and ask for backup to restore.

If you have to use an .mdf You need to copy it to the folder on the server, where it will become the .mdf of the database when it is live. Normally you would do this with an RDP or share connection to the folder. Be aware there are several issue you may encounter doing this.

See these related posts for the answer to questions you have not asked yet, but probably will.

Attaching MDF without LDF
How to attach an .mdf file that was not properly detached?
Is is possible to recover a database from the 'raw' files (.mdf and .ldf) of the old server to a new SQL Server instance on a new server?


Try adding Id as one of the key columns of ix_HighSchoolCourseGrade_CourseId_includes. That may help with the additional sort needs as well as back the foreign key.
If you can change the foreign key, remove the cascading actions and do your deletes from the referencing tables first. Cascading foreign keys use serializable locks behind the scenes, and when deleting large amounts of data can cause bad blocking scenarios.
If you can change code, do your deletes via stored proc. That's easier than creating plan guides for app code. It gives you many tuning options to help tune down the memory grants as well. You can use MAX_GRANT_PERCENT among other things (recompile, etc.) to get a more appropriate plan.

Making the index change should be your first effort, though.


Perhaps it is order of doing things. IMHO a combination of SERIALIZABLE isolation and every other transaction test may be the problem.

For starters, what does SERIALIZABLE mean?

According to MySQL 5.0 Certification Study Guide

page 421 Bulletpoint 4 says

  SERIALIZABLE completely isolates the effects of one transaction from
  others. It is similar to REPEATABLE READ with the additional
  restriction that rows selected by one transaction cannot be changed by
  another until the first transaction finishes.

The next paragraph says that with SERIALIZABLE, one transaction cannot modify rows if another has merely read them.

SUGGESTIONS

You should use the default REPEATABLE READ isolation level
You should check the sequence of events and make sure you did not miss a ROLLBACK in between tests


SQL Server Permission Denied Error When I'm DBOwner?

From what I see you created a server login with server roles (DBCreator and SecurityAdmin are server roles). But this doesn't mean it already has privileges on an existing database.

What you need to do is to create a user in that database and map it to the login:

use your_db
create user x for login y
exec sp_addrolemember 'db_owner', 'x'

This will assign your new user the database role db_owner to the database. Now you should be able to create tables with the login. You can try also other roles, if you don't need to be the db owner.


How can I select rows from a hierarchical query with the lowest level?

If you have an hierarchical query that produces the whole tree under the root node, that also has a level column computed, you can wrap it in a derived table or cte and use the window aggregate:

WITH query AS
  ( SELECT <columns list>, level
  -- your query here
  ) ,
cte AS
  ( SELECT <columns list>, level,
           MIN(the_level) OVER () AS min_level
    FROM query
    WHERE <conditions>
  )
SELECT *
FROM cte
WHERE min_level = level ;