How to grant super privilege to the user?

In a politically correct sense, what you just asked for is impossible. Why ?
The SUPER privilege is a global privilege, not a database level privilege.
When you created the user with
grant all privileges on db1.* to user1@'%' with grant option;

you populated the table mysql.user with user='user1' and host='%'. All other columns (global  privileges) were defaulted to 'N'. One of those columns is Super_priv. Here is the table:
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| x509_issuer            | blob                              | NO   |     | NULL    |       |
| x509_subject           | blob                              | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0       |       |
| plugin                 | char(64)                          | YES  |     |         |       |
| authentication_string  | text                              | YES  |     | NULL    |       |
| password_expired       | enum('N','Y')                     | NO   |     | N       |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
43 rows in set (0.00 sec)

mysql>

Super_priv appears right after Show_db_priv.
The database level privileges got populated into mysql.db. Here is it:
mysql> desc mysql.db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(16)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)

mysql>

Notice that Super_priv does not exist in mysql.db.
To visualize this in pure SQL terms, login as user1 and run SHOW GRANTS; The output will have two lines:

GRANT USAGE ON *.* TO user1@'%' ...
GRANT ALL PRIVILEGES ON db1.* TO user1@'%' ...

There is a hack you can try but I normally would not recommend it.
STEP 01) Login to mysql as root@localhost (should have all privs)
STEP 02) Run this query
UPDATE mysql.user SET Super_Priv='Y' WHERE user='user1' AND host='%';

STEP 03) Run this query
FLUSH PRIVILEGES;

That theoretically should work. Then, user1 may work (I make no guarantees).
UPDATE 2014-12-19 15:24 EST
Metafaniel just asked

Great explanation, thanks. However if you do not reccomend that way to solve the problem, then what other way is the best one to gran a user this Super_priv?? Thanks! –  Metafaniel

Since a user with only DB access cannot have SUPER, the only thing one can do is change the DEFINER manually in the dump. The basic idea would be to mysqldump the routines alone to a text file. Then, edit the definer to user1@'%'. Then, you should be able to reload.

Oct 02, 2011 : Can mysqldump dump triggers and procedures?
Jul 25, 2011 : DROP PROCEDURE IF EXISTS not included in mysqldump
Jul 25, 2011 : Dump only the Stored Procedures in MySQL

Same Thing for Views

Mar 23, 2012 : MySQL Views Authorization
Jul 26, 2011 : Modify DEFINER on Many Views


Access write conflicts with views and insteadof triggers?

Access uses the values of all columns to decide if a row has been updated by some other process between Access reading the row and subsequently writing to that row.

You can prevent that behavior by adding a TIMESTAMP column to the table(s) in question.  Access will then use the single TIMESTAMP value to determine if the row has been modified by someone else.

The only way to prevent that error message is to not update the row prior to Access attempting its own update.  I would make Access modify the two fields currently being modified by the trigger.


AWS RDS MySQL database created but cannot create indexes or change collation

Solution:

a. create a user-defined parameter group and use it in the existing database instance instead of the default parameter group.

b. change the character set to utf-8.

I can now create indexes and change the table and database collation.

So the issue was the lack of the user-created parameter group and a parameter group with the correct character set (i.e. utf-8).


All of your employee numbers are the same. So right now every row will join to every row (1 * 3 * 3 = 9)  and nothing ties tables 2 and 3 together (for example a foreign key to show that FZG belongs to Fitzgerald).  Adding the foreign key will fix the problem you are seeing.


Derive Date Spans from Start and End Dates in SQL Server table

I think I understand what you're trying to do. You're trying to get the start date and end date of a patient at a provider, as long as there is no gap between the start and end dates of the periodes. I've created a test table with the data you sampled.
Create table test (Provider int, Patient int, startdate date, enddate date)
insert into test (Provider, Patient, StartDate, EndDate)

SELECT * FROM
(SELECT 1922157 as Provider  , 12345 as Patient         , '2019-12-01' as StartDate , '2019-12-31' as EndDate
union all SELECT 1904176  , 12345         , '2019-11-01'  , '2019-12-01'
union all SELECT 1904176  , 12345         , '2019-10-01'  , '2019-11-01'
union all SELECT 1904176  , 12345         , '2019-09-01'  , '2019-10-01'
union all SELECT 1904176  , 12345         , '2019-08-01'  , '2019-09-01'
union all SELECT 1904176  , 12345         , '2019-07-01'  , '2019-08-01'
union all SELECT 1904176  , 12345         , '2019-06-01'  , '2019-07-01'
union all SELECT 1904176  , 12345         , '2019-05-01'  , '2019-06-01'
union all SELECT 1904176  , 12345         , '2019-04-01'  , '2019-05-01'
union all SELECT 1904176  , 12345         , '2019-03-01'  , '2019-04-01'
union all SELECT 1904176  , 12345         , '2019-02-01'  , '2019-03-01'
union all SELECT 1922157  , 12345         , '2019-01-01'  , '2019-02-01'
union all SELECT 1922157  , 56789         , '2019-01-01'  , '2019-02-01' )t

The Idea is to start by ordering data and trying to get those that start date and end dates match, in order to detect a hole in the dates. I do that with the "ROW_NUMBER" function. I then find all the rows that match and take the first StartDate and max EndDate for those who match, and then I add all the rows that are "alone" and have no match.
I think it works wit the data you provided. I didn't get to test it with other data.
Recursivity is another option to find the Min/Max dates of different values but I didn't go with recursivity in this case. (feel free to give better names, I went a little fast)
;With RowsWithNum AS
(
SELECT Provider, Patient, StartDate, EndDate, ROW_NUMBER() OVER (ORDER BY Provider, patient, StartDate) as RowNum
FROM test
)
,BeforeAndAfterDates AS
(
SELECT a.Provider, a.Patient, a.StartDate, a.RowNum, a.EndDate, b.StartDate EndStartDate, DATEPART(DAYOFYEAR, b.StartDate)-DATEPART(DAYOFYEAR,a.EndDate) as DateDiffInDays, b.EndDate as EndEndDate, b.RowNum as EndRowNum
FROM RowsWithNum a
LEFT JOIN RowsWithNum b ON b.Provider=a.Provider and b.Patient=a.Patient and b.StartDate=a.EndDate
)
SELECT Provider, Patient, Min(StartDate) as StartDate, Max(EndEndDate) as EndDate, Min(RowNum) as RowNum
FROM BeforeAndAfterDates
WHERE DateDiffInDays=0
GROUP BY Provider, Patient
UNION
SELECT a.Provider, a.Patient, a.StartDate, a.EndDate, a.RowNum
FROM BeforeAndAfterDates a
LEFT JOIN BeforeAndAfterDates b ON b.EndEndDate=a.enddate
WHERE a.DateDiffInDays IS NULL AND b.RowNum IS NULL

And here is my result.


When to use a "middle table" instead of storing a JSON with IDs?

As nbk mentions, the middle table is known as a bridge table or linking table. JSON is best for structureless or variable structure situations, but not so good when needed to be used in predicates within the context of a relational system.
By stuffing a bunch of values in a single JSON field and then using the IN clause, you're telling MySQL to treat the values of that JSON field as a predicate which is very similar to as if you wrote a bunch of OR clauses with each value in it. Having a large number of predicates in this form is generally sub-performant.
Furthermore, you're still dealing with roughly the same amount of data regardless if you store it in a JSON column or in a bridge table, because your templates table will still be the same number of rows times the same number of item_ids in the one-to-many relationship they naturally share. (I know there's some data redundancy in practice, but the optimizations of the database system under the hood with normalized data probably make that a moot point from a performance perspective.)
Joining multiple tables together is not a bad thing for performance, especially only a few tables as you mentioned and especially with small tables of only a few hundred thousand rows at most. It's more so an issue if you over-normalize the data and always need to rejoin all the tables back together in every query. You probably wouldn't see any issues even in that case until your data grows significantly more and/or you're joining around 10+ tables together every query (rough estimates).
So at the end of the day you'd be at best ending up with the same performance on average by leveraging a JSON column, and potentially worse off when querying it as a predicate, by sidestepping one of the key things a relational database is meant to do - join tables. :)
Regarding the purpose of a JSON column, it's better used for data points that are variable in nature and structure, that generally need to be returned as part of the SELECT list (i.e. are commonly returned as part of your queries' data lookups) as opposed to as being part of the JOIN, WHERE, or HAVING clause predicates.


PROPOSED QUERY

SELECT * FROM test WHERE MATCH(tags)
AGAINST ('"+proj_7" "(invoice report)"' IN BOOLEAN MODE);

YOUR SAMPLE DATA LOADED

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS test;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `test` (
    ->   `tags` text NOT NULL,
    ->   FULLTEXT KEY `tags` (`tags`)
    -> );
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO test (tags) VALUES ("proj_7 type_invdtl year_15 month_04");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (tags) VALUES ("proj_8 type_invdtl year_15 month_04");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (tags) VALUES ("proj_10 type_invdtl year_15 month_04");
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM test;
+--------------------------------------+
| tags                                 |
+--------------------------------------+
| proj_7 type_invdtl year_15 month_04  |
| proj_8 type_invdtl year_15 month_04  |
| proj_10 type_invdtl year_15 month_04 |
+--------------------------------------+
3 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT * FROM test WHERE MATCH(tags)
    -> AGAINST ('"+proj_7" "(invoice report)"' IN BOOLEAN MODE);
+-------------------------------------+
| tags                                |
+-------------------------------------+
| proj_7 type_invdtl year_15 month_04 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql>

OTHER EXAMPLES

mysql> SELECT * FROM test WHERE MATCH(tags)
    -> AGAINST ('"+proj_7" "(type_invdtl year_15)"' IN BOOLEAN MODE);
+--------------------------------------+
| tags                                 |
+--------------------------------------+
| proj_7 type_invdtl year_15 month_04  |
| proj_8 type_invdtl year_15 month_04  |
| proj_10 type_invdtl year_15 month_04 |
+--------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM test WHERE MATCH(tags)
    -> AGAINST ('"+proj_7" "(type_invdt2 year_15)"' IN BOOLEAN MODE);
+-------------------------------------+
| tags                                |
+-------------------------------------+
| proj_7 type_invdtl year_15 month_04 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE MATCH(tags) AGAINST ('"+proj_7 +type_invdtl" "+proj_7 +year15"' IN BOOLEAN MODE);
+-------------------------------------+
| tags                                |
+-------------------------------------+
| proj_7 type_invdtl year_15 month_04 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE MATCH(tags) AGAINST ('"+proj_7 +type_invdtl" "+proj_7 +year16"' IN BOOLEAN MODE);
+-------------------------------------+
| tags                                |
+-------------------------------------+
| proj_7 type_invdtl year_15 month_04 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE MATCH(tags) AGAINST ('"+proj_7 +type_invdt2" "+proj_7 +year16"' IN BOOLEAN MODE);
Empty set (0.00 sec)

mysql>

GIVE IT A TRY !!!


Connecting to SQL Server instance through C#

You may need to use SQL Server Configuration Manager to enable TCP/IP connections.

The default is disabled, as below.  You must enable TCP/IP to allow remote connections to the SQL Server machine.


The syntax for IN(...) is:
test_expression [ NOT ] IN
    ( subquery | expression [ ,...n ]    )

With:

test_expression
Is any valid expression.
subquery
Is a subquery that has a result set of one column. This column must have the same data type as test_expression.
expression[ ,... n ]
Is a list of expressions to test for a match. All expressions must be of the same type as test_expression.

This means that only 1 column is allowed on both side. Even a list of expressions is considered as 1 dummy table with 1 column for expression values similar to:
SELECT exp FROM (values(exp1), (exp2), ...) as X(exp)

With 2 columns you can use a subquery with EXISTS:
Select VendorName, VendorCity, VendorState
FROM Vendors AS V1
WHERE NOT EXISTS (
    SELECT 1
    FROM Vendors AS V2
    WHERE V2.VendorID <> V1.VendorID
        AND VendorCity = VendorCity
        AND VendorState = VendorState
)

Overall this subquery always return 0 row or 1 or more rows with 1 because it only needs to know if there is 1 or more rows with the same City and State.
The value is not important because it only looks at the number of row (=> if 1 or more rows EXISTS), hence the SELECT 1. The test has already been done in the inner WHERE clause.
I guess that VendorId is the PK. An index on VendorCity and VendorState would help.
Since you are learning and if you don't already know it, you can also look at the usage of APPLY (CROSS APPLY and OUTER APPLY). I let you try it and play with it.


AFAIK, the target table must exist, though the documentation is not explicit and probably should be. I can't see anything in the syntax diagram that would support any type of extension to create the output table at the same time.


Exception when calling Postgresql function returning a local numeric from C#

It seems like you misunderstood the RETURNING clause. It should list the columns the statement should return, not the names of the variables the returned values should go into. You need an additional INTO clause for the latter.
CREATE
 OR REPLACE FUNCTION public.func_insert_item(_name character varying)
                     RETURNS bigint
                     LANGUAGE plpgsql
AS
$$
DECLARE
    _item_id bigint;
BEGIN
    INSERT INTO public.items
                (name)
                VALUES (_name)
                RETURNING <name of the id column>
                          INTO _item_id;

    RETURN _item_id;
END;
$$

Unfortunately you didn't provide the table's DDL, so I don't know what column could be the ID you want to return. Replace <name of the id column> with its name.


Crash Safety

PostgreSQL is crash-safe within certain limits. It guarantees to always preserve committed data if the database system crashes or the host it's on reboots/loses power/crashes unexpectedly. That's what the D in ACID means - atomicity, consistency, isolation, durability.

The critical feature in PostgreSQL's crash safety is the write-ahead log (WAL). The documentation explains it much better than I ever could.

This crash safety is not a substitute for good backups.

PostgreSQL cannot protect you against file system corruption or hard drive failure. The storage system it uses needs to be reliable.
Hard drives and SSDs will fail, so RAID is necessary but not sufficient for protection. You need backups and/or replication to protect against multi-drive failures, file system corruption, etc.
There are certain settings documented in PostgreSQL that weaken the crash-safety guarantees it makes. For example, if you set fsync=off you give PostgreSQL permission to destroy your data if it crashes in exchange for going a bit faster.
PostgreSQL guarantees that it will not save uncommitted data, it will be lost if the system crashes before a transaction COMMITs. Statements not surrounded by BEGIN and COMMIT for this purpose commit when the statement returns.

There's more, but the short version is that Pg is crash-safe and durable, but you still need to look after the other layers in your infrastructure, which is why you need:

Backups

Don't just do a weekly pg_dump where you overwrite the same dump file over and over, that'll end in tears. Use more than one backup strategy, and keep historical backups not just a most recent backup.

For example, I keep both dumps and a continuous archiving/PITR replica. I keep daily pg_dumps for a week, then weekly dumps for a month and monthly dumps for a year to give me some timeline protection. For the PITR setup I refresh from a base backup every week; it's mostly there to give me the ability to recover to the nearest 5-minutes from any failure or catastrophic mistake. Nightly dumps won't help me recover the last 23 hours of data when I accidentally drop a table; PITR will.

There's lots more here:

Pg manual - backup and restore
Pg manual - streaming replication
Pg manual - high availability
Pg wiki - Backup category
Pg wiki - high availability

Don't be one of the people who turns up on the Pg list saying "my hard drive crashed, how can I recover my database?". Be like this guy, who asked the question for fun because he had backups but was curious if his DB could be recovered. (Answer: probably not).

Repair or recovery of corrupt databases

If you're reading this because you found it in a search and you suspect you have a corrupt Pg database, read this before attempting any repair.

If by crash recovery you mean a repair tool to fix a damaged database, no, there isn't anything much. pg_resetxlog may save you from corrupt/lost transaction logs, but will probably corrupt your database in the process, so you usually need to dump and reload afterwards. There isn't any kind of fsck tool, nor is there anything like myisamchk because unlike MyISAM, Pg's tables are designed not to get corrupted as a routine part of normal operation. It'd actually be quite nice to have some sort of pg_dbcheck, but it would probably not be able to tell you much more than "Yup, there's some corruption here, go find a backup".

The database just doesn't get damaged in the first place unless:

The underlying hardware fails
The OS or file system has a bug
PostgreSQL has a bug
PostgreSQL is explicitly configured not to be durable
The user did something silly like went around deleting things from the database directory

In all of these cases automated repair would be impractical anyway.

Keep good backups of any database, not just Pg. Don't rely on being able to repair corruption, because you probably won't be able to.


MySql Replication Failure on primary secondary

In the master server, you don't need to issue either RESET MASTER nor PURGE BINARY LOGS to extract a DB copy... when you need to extract a master copy to create a slave, just do it with the current binary log file and position.

Since you will need the current binary log file and position to start replication on the slave, just include the --master-data (more info) option to mysqldump which automatically appends the CHANGE MASTER TO statement required on the slave to start the replication process.
(Just open the mydb-dump.sql file after backup is completed and copy the "CHANGE MASTER TO" info.

In slave is perfectly safe to use RESET SLAVE after a backup restoration to start the replication.


You can do it with Native dynamic SQL ( http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS011 ) using the EXECUTE IMMEDIATE statement.
Here's an example:

DECLARE
 v_sql varchar2(128);
 v_username varchar(30);
 cursor c1 is
    select username from dba_users where username like ('PATT%');
BEGIN
    for REC in c1 loop
        v_sql := 'UPDATE ' || REC.username || '.table1 SET field1 = ''new_value'' WHERE field2 = ''filter'''  ;
        EXECUTE IMMEDIATE v_sql;
    end loop;
end;
/

Otherwise, you can make a script with each needed statement with a simple query like

SELECT '''UPDATE ' || username || '.table1 SET field1 = ''new_value'' WHERE field2 = ''filter'';' from dba_users where username in ( 'PATT_%' );


ORA-01017: invalid username/password; logon denied

PL/SQL Developer will remember your most recent windows. If you close the application without closing your windows, it will attempt to authenticate to restart your old sessions. This can become a nuisance if you work in several environments. Try making it habit to close everything before closing the application. You may also try upgrading the application as user21081 suggested.


Storing dart throws in relation database (JSON vs multiple tables)

When I data plan for a table or system, I think in terms of how much data do I realistically see being stored in 1 year, 5 years, 10 years and 20 years. I don't know how many games you expect in a given day and over a year's time, but accumulating millions of records in a single table in a year is not uncommon and definitely reasonable to support in most RDBMS. Obviously there's no concrete answer to exactly how many rows a table can hold at a given time and still be performant, because it's depend on the server hardware and database design, but again millions of rows in one table is not something to be scared of by any means. I've worked with tables with 10s of billions of rows that were performant running on servers with pretty standard hardware (8 Core CPU, 32 GB of RAM, SSD).
That being said, I think your database design is pretty good and I would definitely recommend going with SDT_GAME_2 because of the better normalization. The aggregations for all games data by user is something you should easily be able to calculate on the fly as needed (e.g. with a view or procedure) instead of storing the aggregation in a table.


MySQLI stored procedure return PK/AI insertID

Add OUT the_id to the argument list.

Just before the END, add this statement:  SELECT INSERT_ID() INTO the_id;

Then decide how you can retrieve the id from whatever variable you pass into the proc.


Why am I getting an email size error with SQL Server database mail?

You mentioned that you're using Mailgun as your mail server. Mailgun's terms page say they limit email sizes:

An individual email message may not exceed the per-message size limit
(including attachments) of 25MB or it may be permanently lost.

You'll want to reduce your email sizes to match, or consider using a different SMTP server.


how can i write a trigger that compare between two tables in phpmyadmin mysql

Here's one example. You should make deptor and mypin
columns not null, or you will have to deal with possible nulls there:

delimiter //
create trigger before_insert_users
before insert on users
for each row
begin
    IF ( EXISTS (
            SELECT 1 FROM debtors
            WHERE debtors.myPIN = NEW.myPIN
         )
    )
    THEN
        SET NEW.debtor = 1;
    ELSE
        SET NEW.debtor = 0;
    END IF;
end //

Note that you will need a delete trigger on debtor as well (if myPIN can change you need an update trigger in each table as well.


How do you directly modify the system catalogs in SQL Server 2017?

!!   BE SURE TO READ THE WARNING, IN BOLD, AT THE BOTTOM   !!

The server configuration option of allow updates was rendered non-functional starting in SQL Server 2005. The documentation states that it does not error, but won't actually allow for any direct updates to system catalog tables.

The only way to achieve this now, BEING FULLY AWARE THAT THIS IS DANGEROUS AND IS NOT RECOMMENDED, is to use a Dedicated Admin Console (DAC) connection.

First you will need the actual Table name that you want to update. The name that you SELECT from is just a system catalog View, not the real Table. I usually use the following:

EXEC sp_helptext N'sys.{some name here}';

Then, do the following:

Restart the instance in Single-User mode with

sudo systemctl stop mssql-server
sudo -u mssql /opt/mssql/bin/sqlservr -m

Just to be clear: this step is to allow for system catalog updates. Connecting to the DAC itself for doing other operations does not require Single-User mode.
Connect via Dedicated Admin Console connection with sa or some other sysadmin Login. You can do this in a SQLCMD interactive session by running the following in a Command Prompt window:

sqlcmd -S admin:localhost -U sa

If the Linux SQLCMD doesn't support this for some reason, you can enable remote DAC connections and then later use a Windows machine to dug that DAC is working. You can enable DAC on Linux with,:

EXEC sp_configure 'remote admin connections', 1;
RECONFIGURE;

In that DB, try something like the following:

UPDATE sys.{whatever_name_you_found} {enter}
SET [some_column] = {some_value} {enter}
WHERE [some_other_column] = {the_better_be_correct_or_else_ID}; {enter}
GO {enter}

It won't execute the statement until you put in the GO {enter}.

DANGER WILL ROBINSON!!

Please be cautious when directly editing system catalog tables, and do not get too comfortable with the idea of doing it. This is something that should be done only if there is absolutely no other way of fixing a problem (such as the case here). There are probably several reasons to avoid making direct edits, but the two that initially come to mind are:

Much like the data-models that we create, there are likely rules and workflows for how things work that we are unaware of (e.g. denormalization, "business" rules governing the state of data across various tables, etc)
Quite likely making direct edits voids Microsoft's responsibility to assist you if you run into problems and have a support contract (I have not seen the terms of the Support agreement(s), but I have a hard time believing that such language would not be in there).

@Paul Randal confirmed in a comment: "manually editing a system table irrevocably sets a flag in the boot page of the database that marks your database as having been edited in this way, and CSS might decide to not help you if you subsequently have problems with that database."


How to measure the performance improvement that NUMA brings?

You can't on a host with only 1 node. Your query on sys.dm_os_memory_clerks shows you only have 1 physical node (0). The other node (64) is a logical node for Dedicated Admin Access (DAC).

In older versions of SQL Server that were NUMA supported instead of NUMA aware/optimized, it is possible to tank performance when running on large NUMA hosts. Consider this: you have a large server with 4-NUMA nodes (say HPDL980). Query executed from node-0 retrieves a large amount of data which gets cached in node-2. To access that data, you now do at least 2 hops across the CPUs and perform cache-to-cache transfers. They're REALLY fast compared to disk but still a LOT slower than pulling directly from node-0 where the query originated.

Versions of SQL Server that are NUMA aware/optimized will try to keep memory usage within the same local NUMA node to avoid the hops. Even if soft NUMA is further layered on top of this, SQLOS memory node still works with the physical layout presented by the OS. That way, you SQL Server will still optimize for physical local nodes even when they look like different nodes logically. You can also track remote node access from perfmon using the SQL Server:Buffer Node\Remote node page lockups/sec counter.

These are very highly descriptions plus it assumes existing knowledge of NUMA, SQLOS, SQL Server memory management and query execution details. Lots of good write-ups on these areas in the CSS blogs and if you're keen on how all this started way back in 2005, read up Slava's blog (http://blogs.msdn.com/b/slavao/). It's really old but that work laid the foundation for what you see today.


Select columns by file containing primary keys

This assumes you are on a shell and have the tr command in the PATH:
psql -c "SELECT * FROM mytable WHERE id IN (`tr '\n' , <file`NULL)"

Every newline character is replaced with ,, and the NULL in the end takes care of the final comma.


ERROR 1226 (42000): User 'root' has exceeded the 'max_user_connections' resource (current value: -1)

The quick and dirty fix is FLUSH USER_RESOURCES

  USER_RESOURCES

  Resets all per-hour user resources to zero. This enables clients that have reached their hourly connection, query, or update limits to resume activity immediately. FLUSH USER_RESOURCES does not apply to the limit on maximum simultaneous connections. See Section 6.3.4, “Setting Account Resource Limits”.

Since you did not set any user recsource limits, I would suspect a misalignment of columns in the mysql.user table, especially if you performed mysql_upgrade with fixing the grant tables.

To check this run this query

select ordinal_position from information_schema.columns
where table_schema='mysql' and table_name='user' and column_name='max_user_connections';

I get 40 in MySQL 5.6.

If I upgraded MySQL 5.6 and I don't get 40 from this query, I would just run

mysql_upgrade --upgrade-system-tables

There is a 6-month-old bug on a similar issue : MySQL Forums :: Newbie :: ERROR 1226 (42000) at line 1129: User 'root' has exceeded the 'max_updates' resource (current value: 100)


And why don't you simply create b-tree index on created_at column?
Or compound (created_at+kpi) ? In the second case all the information will be taken during the index scan and no need to read blocks from the table itself.
But if the time range between X and Y is rather big, then the optimizer might think that full table scan is cheaper anyway.
In some particular situations the clustered index may be a good solution, too. For example by days or months, so when you select values, all blocks are read from the same area that belongs to that cluster.


Pull valid row from table even when joining table has no valid data

Try rearranging your conditions like so:

...
LEFT OUTER JOIN TableB B ON A.KeyField1 = B.KeyField1
AND A.KeyField2 = B.KeyField2
AND B.Status <> 'D'
WHERE A.KeyField = '11111'
AND A.Status <> 'D'
...

When you put an outer table in the WHERE clause, you force SQL Server to filter only to rows that exist and where that value can be explicitly evaluated, in other words you change your outer join to an inner join.


Mirroring between two Ms-sql servers for Migrating server from one Datacenter to other Datacenter

This is perfectly viable (and is how I moved several databases before). The outline of the process is this:

Make sure the source database is in full recovery mode
Configure the source to no NO_TRUNCATE transaction log backup (so the log is preserved)
Full Backup the source. Preferably use backup compression
Take your full backup (on a USB stick if it takes to long to copy over the network) to
the target
Restore full backup on target
Take a log backup of the source
Move (using your fastest method as in 4) the log backup to the target an apply it there
Configure mirroring in async mode (source and target can now mirror)
Start regular transaction log backups again on the primary
Wait for mirror to catch up (use the mirror admin tools to monitor)
Set mirror to sync mode
Move the app pointer and manually fail over the mirror

A thing you have to be careful about is that you have enough space to hold the transaction log while the move is happening. But a 40GB database is tiny, so I assume you wont have a lot of tlog traffic on it.

Another thing to watch out for is that you have the same security set up on the target as on the source. If not, you may end up with orphaned users when you swing over the server. This is less of an issue if you only use Windows logins.

Getting this right takes a bit of practise. I would advise to test it out on a development system first to make sure you have all the steps scripted nicely.


In the directory that you ran mongo.exe you should also see the executable mongod.exe.

mongo.exe is the shell that connects to the server/daemon that allows you to issue commands and query your databases.
mongod.exe is the actaul mongo server/daemon.

First run mongod.exe to start the server then in a new command window run mongo.exe. The shell should now connect to the running server. For production you will want to look at setting mongod as Windows Service instead of just running in a separate command window.


Blocking caused by Query Store. Unable to clear or disable

Based on your explanation, I believe this is related to KB4461562, and was resolved in the latest Cumulative Update 5.

  FIX: Transactions and log truncation may be blocked when you use Query Store in SQL Server 2016 and 2017


How do I determine which IP I should use for my MariaDB Galera Cluster?

First, you should have 3 nodes in your cluster (or an odd number), not "3-4", so to always know which is the majority group and avoid the split brain problem.

You can specify any of the three node IPs as the database is replicated in all nodes.  However, to make the system resilient you should have a Virtual IP address that allows you to reach any of the nodes.

In my system, I have a S9S ClusterControl with two HAProxy load balancers associated to the same Virtual IP address.  This VIP is the address that clients use to contact the cluster.


Check the result of a math expression in mysql

Because SQL differentiates code and data reasonably well in the syntax function like the following is required:
CREATE OR REPLACE FUNCTION test(a int, op char(1), b int)
   RETURNS INT DETERMINISTIC RETURN
CASE op
WHEN '+' THEN a + b
WHEN '*' THEN a * b
WHEN '/' THEN a / b
WHEN '-' THEN a - b
END

once you have that you can use a query:
select a, op, b, c
from maths
where test(a,op,b) = c

ref: fiddle


Why storing product fees in another table is preferred?

If the range of fees is guaranteed to be fix - as well as the fees themselves, you might be fine with having them as columns in your product table rather than as rows in additional tables. Otherwise, you will need to adjust the schema and your code in order to add a new type of fee.
Once, you have separate tables, maintaining, e.g., periods, a particular fee value is valid for, is simple (even though, it might require one more table and one more join each). Just imagine trying to do this using columns.
As always in (database) design: You need to balance (application) requirements, efficiency/effectiveness of your solution, flexibility down the road, etc. based on your domain('s characteristics as valid over the lifetime of your system).

In general, this is an example of data (de)normalization…


How to encrypt/secure single columns ins SQL Server?

Although I am not an expert, I will suggest reading the following article on the c-sharp corner:

https://www.c-sharpcorner.com/article/implement-column-level-encryption-decryption-in-sql-server-2016/

This details the process fairly well and has some working examples posted in a way that i feel might suit your application.


MariaDB innodb bufferpool load taking long time

Assuming the buffer pool was completely full, this would mean 25G (based on innodb_buffer_pool_dump_pct) would be loading.
The loading of the innodb buffer pool is throttled by the setting of innodb_io_capacity.
The loading is also throttled heavily if there is other server activity in innodb.
Reading this amount from disk, especially in a random read pattern that would happen with innodb loading can take some time.
The loading thread currently is also single threaded. It is also blocking that one (space,page) is loaded at a time.
MDEV-9930 was written a while ago however without enough details to action.
Looking at the code now, there a significant performance enhancements possible. If increasing the innodb_io_capacity significantly doesn't improve this to your desired level, please write a bug report or include details in MDEV-9930 to be improved.
Edit:
MariaDB now documented this (MDEV-25366 document innodb buffer pool loading dependency on innodb_io_capacity).
MariaDB is looking for better solutions on reducing the throttling time based on your question here (MDEV-25417 reduce InnoDB buffer pool load time).


SSMS (2012) limit # of characters on editor window line

SSMS isn't as full-featured as some other editors out there when it comes to visual formatting. I think the closest you'll get is using word wrap from Tools -> Options  and then manually sizing your SSMS window to  force it to a smaller column width.

The bottom row of SSMS will tell you what column number your cursor is on, which may be helpful to size your window to approximately 100 columns wide.


PHP & Ruby drivers are put on hold - they will receive only critical bug fixes but not the new functionality. You should be able to compile existing code for PHP versions beyond 7.1, but DataStax doesn't provide pre-built versions.


Trouble Creating AG Listener: The WSFC Cluster could not bring the Network Name resource

I was able to resolve this issue in my lab.

I setup an Availability Group between two virtual machines in my lab.  I was not able to add the listener as part of the initial setup and also couldn't add it afterwards.

Each server had two network adapters on different sub-nets.  When I disabled the 2nd network adapter on each server, I was able to add the listener successfully. I then re-enabled the 2nd network adapters successfully with no adverse effect on the listener.


SQL Server: How can I read server-side traces with limited permission?

To view the trace file contents in SMSS, I am using the following function: FN_TRACE_GETTABLE. To run FN_TRACE_GETTABLE, the users don't need any permissions on the server's file system. Only the account that is running the service must have the permissions to access the trace file, but it already has them, because it has created the trace file in the first place.

I have an automated job that uploads a trace file into a table every weekend, and neither me nor the account running the job have any permissions on the server's file system. This is running 2008 R2.

When I was developing that job, I had run FN_TRACE_GETTABLE from SSMS quite a few times.


Does modifying an access file copy affect original file?

No, modifying a backup file will not affect the original. Access databases are self-contained in the MDB file.

Your statement "Since both files will be pointing to the same database" is incorrect. While both files are identical at the time of copying, they are (for all intents and purposes) separate databases.

If you're pointing an Access application to an external data source (e.g. SQL Server), this does not hold true.


How to restore a MySQL file to SQL Server 2008

You should download and install MySQL MSI on the Windows machine

By default, root@localhost has no password

Once mysqld.exe appears in the Taks Manager's process list, run the following

C:\> net start mysql
C:\> mysql -uroot -e"CREATE DATABASE apple"
C:\> mysql -uroot -Dapple < apple.sql

Database will be loaded in MySQL

You can check everything afterwards. First, login to mysql

C:\> mysql -uroot

Then run these client commands

mysql> use apple
mysql> show tables

You can set up DTS or any other mechanism for migrating from MySQL to SQL Server


Do I relate entities that are indirectly related when creating a student-course ERD?

This is a great question!  You have a great start on the ERD because you took notes on how the world of the Tutoring Center works.  You are right in that all of these entities are related.  The difficult part is in developing a sound model of what those relationships are.  Before you get into relationships though, you first want to uncover if any of the entities are really the same thing.  In this case, employee, teacher, director, and section manager are really the same thing - a person working for the tutoring center.  So I would say you really have a single entity there - Employee.  Now that employee may be a contract worker or be a full time employee.  The other words - teacher, director - identify roles those employees play.  In one of the sentences from your notes you identify the employee, but the role is really say analyst.  The employee analyzes the section's data.

The best way to handle these employee roles is to realize that each employee is really holding a position in the tutoring center.  Each position would be of a given position type, and in this case those types are Teacher, Director, Analyst, and Manager.  Now that you've identified the entity types, you begin to look for the relationships.  For example, your question "where students didn't actually contact the teacher but they will be taught by the teacher so do you establish a many to one relationship between these two entities?"  Absolutely there is a relationship because the student is related to the teacher through the class.  But there a subtle distinction here that will cause you to introduce more entity types to properly model what is going on.

A thing type is distinct from a thing.  We know we have a student and we know we have a class.  But really, there is the kind of class - say "Into to Math" - and the offering of that class on a given date and time.  This is a very common modeling pattern you will find everywhere.  In fact you have already seen it with the position type - a kind of job an employee can do, and the position - where an employee is performing that position type at a moment in time.  There are two ways to handle this from a naming standpoint.  The first is to use the words class type and class.  The second is to come up with words whose natural meaning is associated with a type or an instance.  In this case I like the word course to represent the kind of class being offered, and the word offering to represent the scheduling of a teaching team to actually deliver that course to students.  So while it isn't in the notes directly, I would say that since each section is broken down by academic area, each section likely offers one or more courses, and each offering may be taught by many teachers, and many teachers may teach one offering.

This now leads into your second question:  "I ended up with many to many relationships and I don't know what kind of associate entity I should add between them."  If your ERD is a pure conceptual model with no attributes being shown, then you don't need to add an associative entity.  You can use the Many to Many relationship to represent this.  Only when you add the attributes do you need to resolve M to M relationships with associative entities so they may hold the attributes.  But, if the relationship itself will then be associated to new entities, you have to introduce the associative entity.  That is the case with offering, as we realize that a student must register for an offering that is being taught cooperatively by many teachers.  Registration becomes an associative entity to show a student registers for one or more offerings, and an offering can be registered by one or more students.  It isn't the case though with the cooperative teaching, as we can simply show a M to M between offering and teacher as there are no further associations.  Then, when you add attributes (even if there are none there are still the foreign keys), you would add an associative entity perhaps called instruction team member.  We say member as in ER modeling each entity type is always named for a single occurrence - exactly as you have done.  I bring this up only because its very easy to think you are naming an occurrence but have really named the set - in this case to call the associative entity instruction team.

To finish up, you note that a class may offer a test at the end.  I would say the relationship you have is actually 1 to M from class to test, as I would suspect each course has its own test type that it uses that is only for that course and no other.  Now if we needed to show that students in the class actually take the test and make a grade on it, then we again have test type and test, where test is a particular student taking the test on a given date and making a given grade.  Along these lines, you note that a student submits an evaluation.  This is modeled by showing that for a registration, which is a student taking a given offering, an evaluation is performed.

Here is an example ERD I prepared to answer this using the Oracle Data Modeler which is a great free tool you can download that uses what I consider to be the best notation for conceptual models - Barker-Ellis notation.

There is so much more we could discuss about the model and various patterns to accurately depict the tutoring center.  One important addition would be prepositional phrases that precisely describe each relationship.  A really good book to check out when it comes to doing this kind of conceptual modeling is Enterprise Model Patterns by David Hay.  You can also pick up the original text on the Barker-Ellis notation for just the cost of shipping!  Good luck with the project and I hope my comments have helped answer your questions!


Tested - see picture below (renamed table in the second example to test_uq_2)

Not much difference really, but - in my opinion, second example is better, because it gives you the ability to name the constraint/index, while in the first example SQL Server names it automatically


Does changing a Postgresql table's tablespace compact it like "vacuum full" or "cluster"?

The comments so far are roughly correct, but to give an authoritative answer from looking at src/backend/tablecmds.c:

If you're only performing ALTER TABLE ... SET TABLESPACE ... ;, then ATExecSetTableSpace() will be invoked to handle the SET TABLESPACE, and it uses copy_relation_data() to perform a WAL-logged block-by-block copy of the table. However, if you were to specify additional actions to the ALTER TABLE command which require table rewriting, then the new copy of the table should be built (and compacted) in the new tablespace via ATRewriteTable().


How can I perform a full-text CONTAINS call across a list of phrases?

Try this:

go
create function dbo.fn_GetCountForPhrase (@phrase varchar(500))
    returns  int
as
begin
    declare @count int
    declare @localphrase varchar(8000)
    set @localphrase='"' + @phrase + '"'
    SELECT @count=COUNT(*) FROM table  a WHERE CONTAINS(table.description,   @localphrase)
    return @count
end
go
SELECT
    phrase,
    dbo.fn_GetCountForPhrase(phrase)
FROM phrases


SSAS Cube in Excel - Show only applicable rows with KPI?

Thanks to @ConcernedOfTunbridgeWells for pointing me in the right direction on this.

The issue was with the status expression.

Since it ALWAYS returns a 1, 0 or -1 it didn't account for NULLs.

I added a check at the beginning of the expression:

CASE WHEN IsEmpty(KpiValue("MyKPI")) THEN NULL

...which correctly leaves  those fields empty.


Partition trigger and serial column concurrence

Your code is safe, because according to the documentation:

  Because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did.

But why don't you do the natural thing and

EXECUTE format(
           'insert into %I (id_beat, created_at) values ($1, $2)',
           partition_name)
USING NEW.id_beat, NEW.created_at;


MySQL won't use an index on an int, if I provide a string

From the comments dcaswell,

  Is the fact that you're comparing a string and int the issue

He suggests you check out

http://stackoverflow.com/questions/16786063/mysql-comparison-of-integer-value-and-string-field-with-index


Is there a way to calculate yesterday / today of last month / year in SQL?

Shouldn't really need any hacks to handle leap years, but it depends on what results you expect. Typically you just subtract the larger component first, so subtract a month before you subtract a day, instead of the other way around.

Do any of these produce results you don't expect? If so, which ones are "wrong" to you, and why?

DECLARE @d TABLE(d DATE);

INSERT @d(d) VALUES('20150330'),('20150331'),
      ('20150430'),('20150531'),('20150526'),

      ('20160229'), -- Leap day: test "Today of last year" and "Yesterday of last year"

      -- Month after leap day: test "Today of last month" and "Yesterday of last month"
      ('20160329'),('20160330'),('20160331'),

      -- March 1st in possible leap year: test "Yesterday"
      ('20160301'), -- test regular leap year
      ('20000301'), -- test century divisible by 400
      ('19000301'), -- test century not divisible by 400 (not a leap year)

      -- March 1st in year AFTER possible leap year: test "Yesterday of last year"
      ('20170301'), -- test regular leap year
      ('20010301'), -- test century divisible by 400
      ('19010301'); -- test century not divisible by 400 (not a leap year)

SELECT

  [Today] = d,
  [Today of last year] = DATEADD(YEAR, -1, d),
  [Today of last month] = DATEADD(MONTH, -1, d),

  [Yesterday] = DATEADD(DAY, -1, d),
  [Yesterday of last year]  = DATEADD(DAY, -1, DATEADD(YEAR, -1, d)),
  [Yesterday of last month] = DATEADD(DAY, -1, DATEADD(MONTH, -1, d))

FROM @d;

You could also consider using a calendar table if you want to handle leap years, holidays, weekends, etc. differently. What you really need to do is fully understand and explain your requirement. With many months having 31 days, and with leap years especially, each possible combination of "today or yesterday last month or year" can be open to multiple interpretations.


PostgreSQL equivalent of mysqld --bootstrap or mysqld --init-file

You probably want the single user mode: --single, as described in the documentation:

  When invoked in single-user mode from the shell, the user can enter
  queries and the results will be printed to the screen, but in a form
  that is more useful for developers than end users. In the single-user
  mode, the session user will be set to the user with ID 1, and implicit
  superuser powers are granted to this user. This user does not actually
  have to exist, so the single-user mode can be used to manually recover
  from certain kinds of accidental damage to the system catalogs

Note that initdb has be run to initialize an empty data directory before creating databases or users. That would be the equivalent of mysql_install_db if you prefer to look at  this from the MySQL analogy angle.


Why is VACUUM FULL locking ALL databases on the cluster?

I'm considering below is the reason from PostgreSQL v10 documentation:(https://www.postgresql.org/docs/10/sql-vacuum.html)

The FULL option is not recommended for routine use, but might be useful in special cases. An example is when you have deleted or updated most of the rows in a table and would like the table to physically shrink to occupy less disk space and allow faster table scans. VACUUM FULL will usually shrink the table more than a plain VACUUM would.

VACUUM causes a substantial increase in I/O traffic, which might cause poor performance for other active sessions. Therefore, it is sometimes advisable to use the cost-based vacuum delay feature. See Section 19.4.4 for details.

https://www.postgresql.org/docs/10/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST

In your case, it might have happened because all databases in the cluster shares common resource, if it's on the same server.


Cannot connect to SQL Server database regardless of connection string

If you have access to SQL Server Management Studio, then attempt a logon to MIKE-LAPTOP with your provided credentials.  Can you connect?  Any errors thrown?  That will eliminate the possibility of password/permissions.

You mentioned that there could be multiple SQL instances.  Depending on configurations, you may need to specify the instance name and/or port number.

Such as:

MIKE-LAPTOP\sql2008
MIKE-LAPTOP\sql2008,1433


You can do this much simpler using a CTE:

;WITH x AS
(
  SELECT
    Field1, Field2,
    c = COUNT(*) OVER(PARTITION BY Field1, Field2)
  FROM dbo.Table1
  WHERE Field2 <> ''
)
UPDATE x SET Field2 = '' WHERE c > 1;

Or with a more cumbersome (and potentially less efficient, did not test) self-join:

UPDATE t SET Field2 = ''
FROM dbo.Table1 AS t
INNER JOIN (SELECT Field1, Field2, c = COUNT(Field2)
            FROM dbo.Table1
            WHERE Field2 <> ''
            GROUP BY Field1, Field2
            HAVING COUNT(Field2) > 1) AS x
 ON x.Field1 = t.Field1
 AND x.Field2 = t.Field2;


MySQL max_connections setting - per client or global?

The option max_connections is a global setting.

You are limited to a total of 10 connections, no matter how many users are authenticating.

If you want to limit a specific user, such as myuser@localhost, to 10 connections, you would specify that with the GRANT command

GRANT USAGE ON *.* TO `myuser@localhost` MAX_USER_CONNECTIONS 10;

or hack it in like this

UPDATE mysql.user SET max_user_connections = 10
WHERE user='myuser' AND host='localhost';
FLUSH PRIVILEGES;

If you want 200 connections total, just set max_connections to 200.

You don't even have to restart mysqld.

Just do the following

STEP 01 : Add max_connections=200 to my.cnf under [mysqld] group header

STEP 02 : Login as root@localhost and run SET GLOBAL max_connections=200;


Database Mirroring login attempt failed with error: 'Connection handshake failed. The handshake verification failed. State 36.'

This is an issue with the encryption method used on the database mirroring endpoint. In SQL Server 2014 the default encryption type was RC4, but in SQL Server 2016 the new default is AES and it looks like the CU2 update may have removed support for RC4.

To detect and fix this you can run the following on your nodes:

-- Looking at encryption_algorithm - RC4 is deprecated in 2016 CU2+
Select * From sys.database_mirroring_endpoints

Alter Endpoint Hadr_endpoint
 STATE=STARTED
 AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
 FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
 , ENCRYPTION = REQUIRED ALGORITHM AES RC4)
 GO

After which replication should start working again and you can continue with the rolling upgrade.


OK, i figured it out:

initialise the db as user postgres

name: "Create a new PostgreSQL database cluster"
become: postgres
command: "/usr/pgsql-{{ postgres_version.major }}/bin/pg_ctl initdb --pgdata={{ postgres_data }}"
ignore_errors: yes

override PGDATA env variable through systemd

name: "Override PGDATA pathname (1/3)"
become: yes
file: path="/etc/systemd/system/postgresql-{{ postgres_version.major }}.service" state=touch owner=root group=root mode=0644
when: dev.platform == "baremetal"

name: "Override PGDATA pathname (2/3)"
become: yes
lineinfile: path="/etc/systemd/system/postgresql-{{ postgres_version.major }}.service" line=".include /lib/systemd/system/postgresql-{{ postgres_version.major }}.service" state=present
when: dev.platform == "baremetal"

name: "Override PGDATA pathname (3/3)"
become: yes
blockinfile:
  path: "/etc/systemd/system/postgresql-{{ postgres_version.major }}.service"
  state: present
  block: |
    [Service]
    Environment=PGDATA={{ postgres_data }}
  when: dev.platform == "baremetal"
  notify:
    reload-systemd


PostgreSQL compare a timestamp with timezone with a timestamp


Strange Issue with SQL Server to Oracle Linked Server Select Query

Just for anyone facing this mysterious issue in the future...

This particular issue was resolved by removing packet inspection rules on TCP Port 1521 (Oracle default) within the VMWare NSX. This was causing a bottleneck of data coming out of our Oracle infrastructure (IBM AIX) and back into our NSX infrastructure.


We maintain this very same setup. In order to handle read only on the replicas, we have a cron job with SUPER permissions that synchronizes their password with the one on the database.

Alternatively, you could handle passwords on an external authentication mechanism, such as LDAP, so passwords are not handled on MySQL. We don't do it like this because there are MySQL-specific changes (different grants) that we still need to handle on the database. To facilitate such changes, we use roles so that global permissions can be handled easily.

Don't let the users execute commands to the database as SUPER, even if wrapped appropriately, or that could be exploited. Have an API at least in between which simplifies the executable actions. If you don't want to handle external code, you could even have stored procedures with definer permissions, although those are prone to mistakes/SQL injection.


Oracle SQL Throwing "ORA-00934: group function is not allowed here" error for simple Select w/AVG

For that version of AVG(), you need to GROUP BY on the columns, but that way your average value would be incorrect. Sure you can use a subquery (where you do not reference any other column, and because of that, you do not need a GROUP BY inside), for example:

SELECT COL1, COL2
FROM TABLE1
WHERE COL3 > (SELECT AVG(COL3) * 3 FROM TABLE1)
ORDER BY COL1;

But this will perform 2 index/table scans, that work can be reduced by using the analytic version of AVG():

SELECT * FROM
(
  SELECT COL1, COL2, COL3, AVG(COL3) OVER () * 3 AS COL3_AVG3
  FROM TABLE1
)
WHERE COL3 > COL3_AVG3;

Edit: forgot about the greater than zero condition, including that:

SELECT COL1, COL2
FROM TABLE1
WHERE COL3 > (SELECT AVG(COL3) * 3 FROM TABLE1 WHERE COL3 > 0)
ORDER BY COL1;

And:

SELECT * FROM
(
  SELECT COL1, COL2, COL3, SUM(CASE WHEN COL3 > 0 THEN COL3 ELSE 0 END) OVER () / SUM(CASE WHEN COL3 > 0 THEN 1 ELSE 0 END) OVER () * 3 AS COL3_AVG3
  FROM TABLE1
)
WHERE COL3 > COL3_AVG3;


In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?

Since Postgres functions (CREATE FUNCTION) only run in a (single) transaction context, several important commands cannot be executed inside a function body. Like CREATE DATABASE or CREATE INDEX CONCURRENTLY or VACUUM. The manual:

  VACUUM cannot be executed inside a transaction block.

Functions are often called "stored procedures", which has always been a misleading term - probably carried over from other RDBMS. With the arrival of SQL procedures (CREATE PROCEDURE) in Postgres 11 that misnomer should be avoided completely.

SQL procedures can begin and end transactions. But the commands mentioned above are not allowed inside any transaction block at all, so those cannot be included in SQL procedures, either (yet).

Multiple result sets are planned for the future, but not implemented, yet.

Related:

When to use stored procedure / user-defined function?
Function Performance


I would suggest that you are 12ms away (latency) from the AWS datacenter.  You are stuck with that overhead.  Conclusion:  Beware of the timings.
The "<1ms" is realistic only if client and server are in the same datacenter and the disk is SSD.  What is "magnetic storage"?  Perhaps that is an old, spinning, disk.  In that case 10ms is a reasonable guess for one write.  And it is hard to determine from the available numbers what the latency really is.  (Presumably less than 12ms.)  Conclusion:  Be sure to use SSDs.
What does Amazon say about interference from other instances on the same hardware?  You have a "small" instance; there are probably many others sharing the same I/O.  If one of them has a spike, you might be slowed down.  With "large", you might have fewer competing customers.  And it might be SSD.  Conclusion:  Focus on average or 95th percentile, not spikes.
innodb_flush_log_at_trx_commit=1 requires at least 1 disk write per transaction.  =0 delays and combines writes, hence the noticeable speedup.  One way to soften it is to batch multiple queries into a single BEGIN...COMMIT.  (Only do such where the logic makes sense.)  Conclusion:  Bigger transactions.  (However, you will still be stuck with 12+ms per transaction instead of per insert.)
Updates to indexes (except for UNIQUE indexes) are delayed and can usually be treated as zero impact.
char(21) COLLATE utf8_unicode_ci -- Is it always 21 characters?  If not, use VARCHAR.  Perhaps it is always ascii, not utf8?  If so, use CHARACTER SET ascii.
PRIMARY KEY (seqid),  UNIQUE KEYid(id) -- Why have both?  As already mentioned, it causes overhead included in the INSERT time, namely to verify uniqueness before committing.
The activity seems to be very low.  Normally people ask questions like yours when they are having trouble with hundreds or thousands of queries per second.
Here's a way to get a 16-byte, chronologically ordered, UUID:  http://mysql.rjweb.org/doc.php/uuid .  (But not as easily displayed.)


Have you tried a simple IN clause other than the one in your code?  If not, I would write a couple simple ones and try them to see if they run.  If they run successfully, it's either you copied it incorrectly, OR it's got to be something specific with how your code is using the IN statement.

If no IN statements run, something was changed, either by design or it's a bug.


SQL Server Express: difference between connecting to localhost and localhost\sqlexpress

Hypothesis one: You have installed a named instance called SQLExpress. This instance was configured to listen on the default SQL Server port 1433. Therefore wether you connect to 'localhost' or to 'localhost\SQLExpress', you are connecting to the same instance.

Hypothesis two: You have installed two different SQL Server instances, one named SQLExpress and one default, unnamed. Both connections succeed, but you are not connecting to the same instance.


Will I need more system resources to run DB2 instead of MySQL?

I have administered both MySQL and DB2 database systems. In general, I'd say that DB2 requires more memory and more administration. But sometimes, you can use DB2's sophisticated features to perform queries which are much more efficient.

About memory and administration: DB2 is not very good at making good use of RAM. Either you spend a lot of time fine-tuning its memory config, or you use its automatic memory configuration ("STMM") and accept that it will waste a depressingly high percentage of your RAM. And DB2 has a bunch of other small annoyances which add up to a need for significant need for administration. Example: If you create a database, it will not be automatically "activated" at boot time, meaning that the first connection to it will take forever (a script can help you, but why-oh-why is that needed).

About sophisticated features: If you take the (potentially long) time to study some DB2 features such as index-only scans, OLAP queries, semantic query optimizations (where the optimizer can sometimes take advantage of constraints defined in the tables), materialized views, etc, then there are situations where you can make things go orders of magnitude faster compared to a simpler database system. And if/when you make something run much faster through design and queries, then you can cut down on CPU, RAM and I/O requirements.

If you have large amounts of data, then you also need to think about backups: Do you need to back up your data? If so, what options do the database systems provide? - There is not much fun about an otherwise fast system which is often held back due to long running and I/O-saturating backups. DB2's backup+restore has many annoying characteristics, but it does also have interesting features such as the ability to split data into hot+cold data (via separation into tablespaces), incremental or delta backups, etc. Last time I looked at MySQL, the backup options were less sophisticated.


One generic mapping table VS many specific mapping tables

You don't want option # 1 because:

You might as well just place ContextId and Type into the Files table.
You would be better off with a TypeID TINYINT field (and the associated Type lookup table) so that you didn't need to do an inefficient string comparison each time.

What is probably best is a modified option # 2. Yes, start with the context-specific file map tables (e.g. ExpenseFiles, PetFiles, etc) but also add a ContextTypeID TINYINT column in the existing Files table. There should also be a new ContextType lookup table with ContextTypeID TINYINT NOT NULL as the Primary Key (but do not make it an IDENTITY column). You can add a Name column to the lookup table, and a Foreign Key from Files to ContextType on ContextTypeID. Having this new column in the Files table will make it much easier to determine which context-specific file map / property table the associated record is in.

The reason for going with option # 2, even if there are 15 different contexts, is that, inevitably, at least some of them will evolve and diverge over time. So you might have:

ExpenseFiles (ExpenseId, FileId, Date, Total)
PetFiles (PetId, FileId, Name, Age, AnimalType)


Slow Linked Server INSERTs on SQL Server 2014

"My local and remote servers were all using SQL Server 2008 R2 but the
  local ones were recently upgraded to SQL Server 2014 and the
  performance of Remote Queries has been crippled (literally from
  minutes to hours)."

When Sql Server performance degrades substantially, my first response is usually "what has changed?"

Well - you just upgraded the local server to Sql Server 2014 which introduces a new Cardinality Estimator (CE).

As quoted in the blog (Careful Testing the 2014 Cardinality Estimator with Trace Flags):

  When you migrate to SQL Server 2014, rather than turning on the new
  cardinality estimator right away, it's recommended to stick with the old
  cardinality estimator initially by leaving your database in SQL Server
  2012 compatibility mode. Let the database settle down for a few weeks,
  let folks air out their grievances, and give yourself some time to
  make sure the system is working at least as well as the old system.

You say that "the DBA (for the local server) claims that the database configuration is consistent with the old setup".  So, how can you tell if you are being impacted by the new CE?  There are a few ways.

First,
run this query against the local instance.

select name, compatibility_level from sys.databases

If the compatibility level is 120 or higher, you certainly 'might' be using the new CE (assuming it has not been overridden by using session or global trace flags, or QUERYTRACEON hints on the query that force the use of the older CE.)

Another way to see if the new CE is being used is to look at the execution plan XML for a 'local' query and locate the string 'CardinalityEstimationModelVersion'.

If it's 70, you're using the old CE.  If it's 120, you're using the new CE.

My suggestion is to 'first' figure out whether you are being bitten by the new Sql Server 2014 CE.

There is quite a bit of information on the Internet about this topic.

Another helpful link is The SQL 2014 Cardinality Estimator Eats Bad TSQL for Breakfast.


Scheduling a procedure with DBMS_SCHEDULER. PLS-00103: Encountered the symbol “NULL” when expecting one of the following: ;

Most probably you are creating objects in different schemas,verify created jobs and job details in current schema with these views user_scheduler_jobs and user_scheduler_job_run_details
Simple procedure scheduled to run every 2 minutes
  SQL>create or replace procedure test_print
      2  is
      3  begin
      4  dbms_output.put_line('Current date and time is : ' || to_char(sysdate,'DD-MON-YYYY HH:MI:SS AM'));
      5* end;
    SQL> /

    Procedure created.

    SQL> exec test_print();
    Current date and time is : 20-08-2020 09:22:59 PM

    PL/SQL procedure successfully completed.

    SQL> BEGIN
      2             DBMS_SCHEDULER.CREATE_JOB (
      3              job_name => 'JOB_TEST',
      4              job_type => 'STORED_PROCEDURE',
      5              job_action => 'TEST_PRINT',
      6              number_of_arguments => 0,
      7              start_date => NULL,
      8              repeat_interval => 'FREQ=MINUTELY;INTERVAL=2;',
      9              end_date => NULL,
     10              enabled => FALSE,
     11              auto_drop => FALSE,
     12              comments => 'Testing job runs');
     13
     14
     15
     16
     17
     18              DBMS_SCHEDULER.enable(
     19               name => 'JOB_TEST');
     20  END;
     21  /

  SQL> select job_name,output,status from user_scheduler_job_run_details
       2  where  job_name='JOB_TEST';

JOB_NAME   OUTPUT                                             STATUS
---------- -------------------------------------------------- ---------------
JOB_TEST   Current date and time is :20-08-2020 09:30:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:32:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:34:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:36:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:38:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:40:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:42:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:44:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:46:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:48:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-AUG-2020 09:50:05 PM  SUCCEEDED -- Modified date format in procedure
JOB_TEST   Current date and time is :20-AUG-2020 09:52:05 PM  SUCCEEDED
JOB_TEST   Current date and time is :20-AUG-2020 09:54:05 PM  SUCCEEDED
JOB_TEST   Current date and time is :20-AUG-2020 09:58:05 PM  SUCCEEDED
JOB_TEST   Current date and time is :20-AUG-2020 10:02:05 PM  SUCCEEDED
JOB_TEST   Current date and time is :20-AUG-2020 10:04:05 PM  SUCCEEDED
JOB_TEST   Current date and time is :20-AUG-2020 09:56:05 PM  SUCCEEDED
JOB_TEST   Current date and time is :20-AUG-2020 10:00:05 PM  SUCCEEDED

18 rows selected.

SQL> select job_name,last_start_date,last_run_duration from user_scheduler_jobs
  2  where job_name='JOB_TEST';

JOB_NAME   LAST_START_DATE                                    LAST_RUN_DURATION
---------- -------------------------------------------------- ------------------------------
JOB_TEST   20-AUG-20 10.08.05.070000 PM US/CENTRAL            +000000000 00:00:00.009000

Edit:- Just observation you were able to run job manually but not running from scheduler implies job and procedure are not in the same schema.


MongoDB : How to estimate max number of connections supported?

Each connection eats up about 1MB of RAM on the server side. That includes the connections to the config servers and the replica set members. Please keep in mind that those communicate with each other.

So, let's say you want to use 1/3 of the RAM of your shards for connections, that'd be 5GB or 5000MB.

Then, each server connects to two members of it's replica set. These are actually 3 physical connections each. Furthermore, we have connections for managing the sharded cluster, which are 3*3=9. So we have 5000 - 6 (for the replica set) - 9 (for the management) = 4985 connections available. Since we want each mongos be relatively equaling terms of connections, we divide this by 10 (the maximum number of mongos instances we can expect) and get 498, rounded down. That is a relatively normal value, but this of course depends on the use case. If you have a lot of or very big indices, your 15GB of RAM may actually be a bit small.

As per the rule that there should be at least as many database connections available as application threads, your AppServers should be configured accordingly.


Set SA to weak Password in SQL Server 2012

Try

 ALTER LOGIN sa
 WITH PASSWORD = 'password',
  CHECK_POLICY = OFF

(I feel dirty now)

In order to mitigate some of the potential horrors this allows, I'd recommend renaming the built-in sa account to something else, then creating a new login called sa with the rights the app actually requires.


The important part of that log is

InnoDB: mmap(137363456 bytes) failed; errno 12 - ENOMEM 12 /* Out of memory */

If you check system log you will see OOM killer doing its work too.

It means your browser sends multiple requests when you hold F5, they probably are executing slowly so they stack, the web server eats more and more memory for each new request and after some time there is just not enough so some process has to be killed - that victim is the mysqld server process which holds some big buffers (key buffer and/or innodb buffer pool).

Then it cannot start anymore until some memory is freed (for example by restarting the system or the web server service).

Check this guide to set your MySQL server to use less memory or separate web and db servers.


Deleted rows but database size is still large

Do all tables have clustered indexes? Without this, rebuilding indexes won't help you.

If you haven't and don't want to add them, your only option is to use DBCC SHRINKFILE with NOTRUNCATE to force the data pages to compact


If you are simply interested in adding a column Users_ID to the Content table, then the following will likely do the trick.  (Assuming this is indeed SQL Server - see the 2nd code block for the MySQL version).

/* add the Users_ID column to the table */
ALTER TABLE dbo.Content ADD Users_ID INT NULL
    CONSTRAINT FK_Content_Users_ID
    FOREIGN KEY (Users_ID) REFERENCES dbo.Users(ID);
GO

/* Update the Users_ID column */
UPDATE dbo.Content
SET Users_ID = Containers.Users_ID
FROM dbo.Content
    INNER JOIN dbo.Containers ON Content.Container_ID = Containers.ID
WHERE Content.Users_ID IS NULL;
GO

/* Make the column not nullable to prevent rows being inserted without
   a Users_ID value present
*/
ALTER TABLE dbo.Content ALTER COLUMN Users_ID INT NOT NULL;

As an aside from the actual question at hand, I would recommend against naming three separate columns ID, since down the road it becomes a nightmare to quickly identify what the ID actually means.  If it is called UserID in the Users table, then call it UserID in the Content table as well.

The code - with minor changes - to work in MySQL:

/* add the Users_ID column to the table */
ALTER TABLE Content ADD Users_ID INT NULL,
    ADD CONSTRAINT FK_Content_Users_ID
    FOREIGN KEY (Users_ID) REFERENCES Users(ID);

/* Update the Users_ID column */
UPDATE Content
    INNER JOIN Containers ON Content.Container_ID = Containers.ID
SET Content.Users_ID = Containers.Users_ID
WHERE Content.Users_ID IS NULL;

/* Make the column not nullable to prevent rows being inserted without
   a Users_ID value present
*/
ALTER TABLE Content MODIFY COLUMN Users_ID INT NOT NULL;


change journalCompressor to none - not default snappy

What do you think about journalCompressor not snappy? Is this a valid config for DBaaS provider and best for most use cases?

In general I would leave MongoDB settings at the default unless variations have proven to be beneficial for your common workloads (i.e. through actual testing and measurement). Snappy compression/decompression typically does not have much overhead and I/O is often more of a resource constraint than CPU. The default settings are intended to be "best for most use cases" and are more widely tested/used in MongoDB deployments and continuous integration tests.

The size limit on WiredTiger journal files is 100MB; when the journal file size limit is reached WiredTiger creates a new journal file and syncs the previous journal file to disk. With the default journalCompressor setting of snappy individual journal files can contain more data than with no compression. Without compression, more journal files will be created and there will be more journal I/O.

The right tradeoff for CPU vs I/O usage will vary based on your deployments and workloads. If your deployments have CPU to spare, you could also consider testing zlib compression (expecting better compression but more CPU usage).

If you are concerned about I/O contention between the journal and data files in your dbPath or want to do finer tuning given journal writes are sequential rather than random, another option would be to symlink the journal directory to a separate mount point. This approach may affect your backup strategy (e.g. if you are relying on filesystem snapshots), however since you are already using the directoryForIndexes option I suspect you are already aware of this caveat.

For more configuration suggestions for production deployments, see the Production Notes in the MongoDB manual.


Storing multiple settings for multiple users in a SQL Server table

Already maintainability will ask for a structure like shown below.
An additional set of templates would support fast capture of new data (Structure like UserSettings with a templateid instead of userid)

CREATE TABLE [dbo].[Settings](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [ValueType] [int] NOT NULL,
 CONSTRAINT [PK_Settings] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[UserSettings](
    [userid] [int] NOT NULL,
    [settingsid] [int] NOT NULL,
    [value] [sql_variant] NULL,
 CONSTRAINT [PK_UserSettings] PRIMARY KEY CLUSTERED
(
    [userid] ASC,
    [settingsid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


I would suggest not logging the "latest activity" but rather keeping a full audit trail. In order to minimize space requirements, you might want three tables:

CREATE TABLE dbo.Users
(
  UserID TINYINT IDENTITY(1,1) PRIMARY KEY, -- assuming <= 255 users
  Username NVARCHAR(128) NOT NULL UNIQUE,
  /* , other columns */
);

CREATE TABLE dbo.Tables
(
  TableID TINYINT IDENTITY(1,1) PRIMARY KEY, -- assuming <= 255 tables
  Name NVARCHAR(128) NOT NULL UNIQUE
  /* , other columns */
);

This auditing table assumes that all of the audited tables have an INT primary key (or a PK that will fit into INT). This will obviously be more complex if you have different data types, or compound primary keys, in which case you may just consider different auditing tables - still more valuable, IMHO, than only keeping the last modification for any one row.

CREATE TABLE dbo.AuditLog
(
  TableID       TINYINT NOT NULL
                FOREIGN KEY REFERENCES dbo.Tables(TableID),
  ID            INT, -- loose reference to entity table's PK
  Action        CHAR(1) CHECK (Action IN ('I', 'U', 'D')),
  UserID        TINYINT NULL -- just in case
                FOREIGN KEY REFERENCES dbo.Users(UserID),
  EventDateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

(Note that I intentionally did not dictate what the clustered index should be, since I don't know all of your query patterns aside from the one mentioned in the question. It is quite likely that you will want to cluster leading on EventDateTime, especially if you are going to query for events that happened recently, and/or you are going to purge data periodically, which is never a bad idea. This will at least ensure that new rows are added to the "end" of the table instead of page splits happening all over the place if you choose something like TableID as the leading column.)

Now your trigger can check the user name (not sure about your authentication method, but this may be via SUSER_SNAME()), it knows what table it's dealing with, it can determine the action, so it just has to add the ID(s) from inserted/deleted. Hokey example that assumes a table named dbo.foo with a primary key called FooID:

INSERT dbo.tables(Name) SELECT N'dbo.foo';
GO

CREATE TRIGGER dbo.AuditFoo
ON dbo.foo
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @UserID TINYINT, @TableID TINYINT, @now DATETIME;

  SELECT @UserID = UserID -- could be NULL
    FROM dbo.Users WHERE Username = SUSER_SNAME();

  SELECT @TableID = TableID
    FROM dbo.Tables WHERE Name = N'dbo.foo';

  -- inserts
  INSERT dbo.AuditLog(TableID, ID, Action, UserID)
  SELECT @TableID, FooID, 'I', @UserID
    FROM inserted AS i WHERE NOT EXISTS
    (SELECT 1 FROM deleted AS d WHERE d.FooID = i.FooID);

  -- updates
  INSERT dbo.AuditLog(TableID, ID, Action, UserID)
  SELECT @TableID, FooID, 'U', @UserID
    FROM inserted AS i WHERE EXISTS
    (SELECT 1 FROM deleted AS d WHERE d.FooID = i.FooID);

  -- deletes
  INSERT dbo.AuditLog(TableID, ID, Action, UserID)
  SELECT @TableID, FooID, 'D', @UserID
    FROM deleted AS d WHERE NOT EXISTS
    (SELECT 1 FROM inserted AS i WHERE i.FooID = d.FooID);
END
GO

Only one of those inserts will fire per statement / trigger invocation (well, to be pedantic, the trigger itself will fire multiple times in the event of MERGE).

If you don't want to keep a list of users / tables you can always populate this dynamically (though if you're not strictly controlling the list you may seriously reconsider the TINYINT suggestion above). E.g. to capture users you're seeing for the first time and haven't manually inventoried:

  SELECT @UserID = UserID FROM dbo.Users WHERE Username = SUSER_SNAME();

  IF @UserID IS NULL
  BEGIN
    INSERT dbo.Users(Username) SELECT SUSER_SNAME();
    SELECT @UserID = SCOPE_IDENTITY();
  END

But for the table this doesn't make much sense. Just add the row when you create the trigger, and you can even hard-code the TableID instead of deriving it at runtime.

Once you are collecting data in dbo.AuditLog, you should be able to easily derive information about the last action of any specific type, restricted to a table, user, or even individual entity. If you need help constructing any of those queries, please start a new question with the schema, some sample rows and desired / expected results. Using SQLFiddle can be quite helpful.


Why does LIKE not work with CONCAT but works with STRING value

Can't understand what's the difference between '%me%' and CONCAT('%', 'me', '%').

The first is an untyped string literal. The second is type text since the function concat() returns text. Not exactly the same thing for Operator Type Resolution! Quoting the manual there:

  Look for the best match.

  a. Discard candidate operators for which the input types do not match
  and cannot be converted (using an implicit conversion) to match.
  unknown literals are assumed to be convertible to anything for this
  purpose. If only one candidate remains, use it; else continue to the
  next step.

And there is no implicit conversion for text -> bytea in standard Postgres.
And there is no variant of LIKE for bytea / text, only for:

bytea / bytea
name / text
text / text

db<>fiddle here (testing in Postgres 11)

To solve, add an explicit cast to bytea (text can be cast to any type):

SELECT CONVERT_FROM(data, 'utf8')
FROM   binary_data
WHERE  "data" LIKE ('%' || 'me' || '%')::bytea  --!
LIMIT  10;


Error in WSFC Role while applying SQL 2016 CU7 in Multiple SQL instance environment

Is it normal while updating 2nd instance of SQL Server the 1st instance get interrupted while server involved in Availability Groups, or should we follow any particular method in this case to avoid any error as such.

No, it's not expected. The error, though, says it tried to bring the resource online and failed at least 3 times (by default, unless you changed this value). This leads me to believe that the instance was patched while it was a primary, which would of course fail while the service is offline for patching. If it wasn't a primary then you'll need to look into the SQL Server errorlog and the cluster log to understand what had happened.


You do not mention what language your application is in but if you are connecting on every page requested you could add a lot of speed simply by using a database connection pool. That way you application will not need so many connections and will not have to connect on every request, giving you extra speed simply because you don't reconnect every time and also reducing drastically the number of concurrent connections it uses, which seems to be your problem.

I have only seen problems like this you mention on Windows machines. Most of this problems are also highly attenuated simply by moving your db to GNU/Linux (or BSD).


PostgreSQL functions inside where clause

For every table that is created a type with the same name is created. So you you can define your function to accept the table's type.

Such a function is also better written as a SQL function to avoid the overhead of PL/pgSQL.

create function my_function(val a) returns boolean as
 $$
 select val.p1 is not null
    and val.p2 is not null
    and val.p3 is not null;
 $$
 language sql
 stable;

The a in (val a) is the name of your table.

You can use that function in the following way:

select *
from a
where my_function(a);


After some trial and error, I've learned how and where to apply COLLATE:

Converted lines like:

SELECT SOMETHING
FROM SOMEWHERE
WHERE table_schema = given_database
AND   table_name   = given_table
AND   index_name   = given_index;

To:

SELECT SOMETHING
FROM SOMEWHERE
WHERE table_schema COLLATE utf8_unicode_ci = given_database
AND   table_name   COLLATE utf8_unicode_ci = given_table
AND   index_name   COLLATE utf8_unicode_ci = given_index;


Are individual queries faster than joins?

Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient?

In any performance scenario, you have to test and measure the solutions to see which is faster.

That said, it's almost always the case that a joined result set from a properly tuned database will be faster and scale better than returning the source rows to the client and then joining them there. In particular, if the input sets are large and the result set is small -- think about the following query in the context of both strategies: join together two tables that are 5 GB each, with a result set of 100 rows. That's an extreme, but you see my point.

  I have noticed that when I have to get information from multiple tables, it is "often" faster to get this information via multiple queries on individual tables (maybe containing a simple inner join) and patch the data together on the client side that to try to write a (complex) joined query where I can get all the data in one query.

It's highly likely that the database schema or indexes could be improved to better serve the queries you're throwing at it.

  A joined query always has to return more data than the individual queries that receive the same amount of information.

Usually this is not the case. Most of the time even if the input sets are large, the result set will be much smaller than the sum of the inputs.

Depending on the application, very large query result sets being returned to the client are an immediate red flag: what is the client doing with such a large set of data that can't be done closer to the database? Displaying 1,000,000 rows to a user is highly suspect to say the least. Network bandwidth is also a finite resource.

  Since the database has to cobble together the data, for large datasets one can assume that the database has to do more work on a single joined query than on the individual ones, since (at least) it has to return more data to the client.

Not necessarily. If the data is indexed correctly, the join operation is more likely to be done more efficiently at the database without needing to scan a large quantity of data. Moreover, relational database engines are specially optimized at a low level for joining; client stacks are not.

  Would it follow from this, that when I observe that splitting a client side query into multiple queries yield better performance, this is just the way to go, or would it rather mean that I messed up the joined query?

Since you said you're inexperienced when it comes to databases, I would suggest learning more about database design and performance tuning. I'm pretty sure that's where the problem lies here. Inefficiently-written SQL queries are possible, too, but with a simple schema that's less likely to be a problem.

Now, that's not to say there aren't other ways to improve performance. There are scenarios where you might choose to scan a medium-to-large set of data and return it to the client if the intention is to use some sort of caching mechanism. Caching can be great, but it introduces complexity in your design. Caching may not even be appropriate for your application.

One thing that hasn't been mentioned anywhere is maintaining consistency in the data that's returned from the database. If separate queries are used, it's more likely (due to many factors) to have inconsistent data returned, unless a form of snapshot isolation is used for every set of queries.


Commands like CREATE, GRANT, DROP always return "0 rows affected"

The way mysql works is this ...

Performing INSERTs, UPDATEs, DELETEs explicitly names tables

SQL Commands that do not explicitly name tables have 0 row(s) affected

Such commands that echo 0 row(s) affected include

CREATE USER
GRANT/REVOKE
CHANGE MASTER TO (to Setup MySQL Replication)

I suppose this has always been the case.


As Mihai said, use a LEFT JOIN on feat_attrib_type_4 and feat_attrib_type_5.  Either old style (as you have) or ANSI joins style would work.  For the former add (+) after every reference to a column in the where clause from those two aliases.  For the latter, convert the statement to ANSI join syntax and add LEFT before the word JOIN for those two tables.


you need to cast the string '2018-08-24 06:42:16' to one of the allowed types

TO_UNIXTIME(cast ('2018-08-24 06:42:16' as timestamp) ) new_year_ut

or

TO_UNIXTIME( timestamp '2018-08-24 06:42:16' )


But still returns the record where resonance = ''

Select *
FROM @T1
WHERE userid LIKE '%[0-9]%'
    AND resonance not in ('DEL','Samit')

You should define it as:

Select *
FROM @T1
WHERE userid LIKE '%[0-9]%'
    AND resonance not in ('DEL','Samit','')


You could look for ProductIds contained in a subselect

declare @supplier int = 1
declare @category int = 1
declare @shippostal nvarchar(50) = '01307'

select * from products p
WHERE (@supplier  = '' or SupplierId = @supplier) AND (@category = '' or CategoryId = @category)
AND ProductId in
(
Select od.ProductId from [Order Details]
inner join Orders o on o.OrderId = od.OrderId
Where (@shippostal = '' or ShipPostalCode = @shippostal)
)


How much RAM should you allocate a MySQL replication slave?

The amount of memory should be the same, the config should be the same (except config that pertain to Master / Slave only).

Reason is when you fail over, you don't want to spend time trying to find out what value should be there or not. I would even create 2 my.cnf files: my.cnf and my_failover.cnf (prepare my.cnf on Slave in case master fails. File can contain binlog=1 (turn on bin log).

All you should need to do is:

mv my.cnf mybk.cnf, my my_failover.cnf my.cnf
Restart Mysql.

Then your slave will start login.

Regarding caching queries, well, all Select statement running on Master won't replicate to Slave. If your Master is 80% Select / 20% write, it should not be a problem.

If you want to cache your Select from Master, turn on Slow Query log for query > 1 sec then apply that to Slave. You could do that once a day (in morning).

Chances are, soon you'll be using your slave for reporting.

You could also use Percona Playback.


Why first_lsn of full backup and 1st transaction log backup are same?

Because Full backups never break the log chain.  The log backups contain a contiguous sequence of log records regardless of how many Full backups were taken.
The log records in the Full are only there to enable the database to be restored and recovered to the point-in-time at which the backup completed.


When one of masters is unreachable all other replica sets work too slow in mongodb 2.6.11 and web application throws error 71

Replica sets are individual entities unless you are sharing resources among them like CPU,RAM,Disk... running multiple mongods in a single server.

I believe your case is that on a sharded cluster scatter-gather queries speed is determined by the slowest shard, so when one shard gets slow all scatter-gather queries will turn slow.


How using many tables and JOINS affect performance vs. using limited number of tables and lesser JOINS?

You are obviously familiar with data normalization and joining tables.  Not to dismiss your question, but I would focus more on a solid design that captures the information in the simplest way and without putting undo constraints on your application.  I think you'll find then the performance works itself out.  I'm wondering what's wrong with this:

TABLE events
  id | shop_id | title | desc | from (DATETIME) | to (DATETIME)

To figure out a day's schedule, you then have to do some work on those DATETIME fields to figure out whether they were on Mon, Tue, etc.  But that's pretty easy,  and now there's all sorts of other ways you could slice this data.  You could even create 7 VIEWS (S,M,T,W,T,F,S) that return only events on each day of the week, if that's so important.


SSRS 2008 - How to Total values from an expression

You actually just put a SUM around the outside:

=Sum(IIF(Fields!Role.Value = "Contractor", Fields!actualwork.Value * 100,
Fields!actualwork.Value * 77))

It will evaluate that row by row and will provide the correct sum.


Is a process created in Oracle for every SQL statement?

A process is created per connection, which can then execute SQL statements for the duration of the connection.

A SQL statement may use more than one Oracle background process (for things like Parallel Query) during its execution, but these aren't forked specifically for the SQL statement.

Note that I'm ignoring things like connection pooling (DRCP) and shared servers (MTS) to keep the answer simple.