Hot answers tagged

4

MySQL (and the derivative) MariaDB have not implemented FULL JOIN. So you need either 2 queries or a UNION of 2 NOT EXISTS subqueries. To find the not matched IDs using NOT EXISTS: SELECT t1.id, 'IDs_in_t1_but_not_in_t2' AS mismatch FROM table1 AS t1 WHERE NOT EXISTS ( SELECT * FROM table2 AS t2 WHERE t2.id = t1.id ) UNION ALL ...


3

This is taken from the MySQL documentation: Changes in MySQL 5.7.1 (2013-04-23, Milestone 11) ... If a column is declared as NOT NULL, it is not permitted to insert NULL into the column or update it to NULL. However, this constraint was enforced even if there was a BEFORE INSERT (or BEFORE UPDATE trigger) that set the column to a non-NULL value. ...


2

While I do use NULL columns, there is overhead. The Oracle documentation you retrieved this short list should explain if you read further. There are case where NULL indicates issues with data types and/or just having the column. Consider PHONE_NUMBER NUMBER(15): This will likely have a formatted phone number column, and may be null for numbers like 1-...


2

In a Galera cluster, any node can play the part of a Master node. Such a node can execute DML (INSERTs, UPDATEs, and DELETEs). If DML statements are spread across multiple Master nodes and are targeting the same tables, you must expect log conflicts. Why ? Galera wsrep libraries were designed to commit DML changes if all nodes in a cluster are ready to do ...


2

This information may not be of interest to the DBA. If the application is handling conflicts correctly, there is no need to log them. It would be expected behaviour. You would only enable this level of logging if you were debugging a specific issue. Otherwise it would generate a lot of spam in the logs.


1

Avoiding NULLs is very low on my list of optimizations. I prefer to say "use NOT NULL wherever appropriate". That implies that if you need NULL, go ahead and use it. I do find in my own tables that I rarely have a use case for NULL. See Rick's RoTs for a longer list of recommendations; they are aimed at MySQL, and come from years of optimization ...


1

There's a lot of choices out there: Percona Monitoring plugins for Zabbix, Cacti, Nagios Percona Monitoring and Management Monyog Severalnines Clustercontrol Equally capable but not on-premise (afaik) is Vividcortex.


1

Get MonYog through MariaDB. https://www.webyog.com/product/monyog Note: anything that works with MySQL, MariaDB, or Percona, will probably work well with the others. I looked at lots of tools, but came away thinking that most graphs were measuring either stuff that never changes. My favorite tool metric is the number of slowlog entries. If that goes up, ...


1

MySQL and MariaDB handle character sets the same. However... Different versions of MySQL have different defaults. Ditto for MariaDB. SET NAMES declares what encoding is in the client. This is independent of the CHARACTER SET on a column or table. (There are, of course, characters that are not representable in latin1, so there can be conversion problems....


1

You've got a few issues here. When using the UNION or UNION ALL operator, each query must return the same number of columns. The first query in your list has three columns in the SELECT but the queries that come later only have two. You can create a static value for these if you need to, something like this (using the second query as an example); SELECT ...


1

If I understand your requirement correctly, You can try something like this. This will get count per ID from table1 and table2 and then return only those records where T1.ID count matches the second table SELECT T1.ID FROM ( SELECT ID,Count(*) c From Table1 GROUP BY ID ) T1 INNER JOIN ( SELECT ID,Count(*) c From Table2 GROUP BY ID ) T2 ...


1

Please, check the following configuration files and variables: File: /etc/mysql/my.cnf [client] ... ssl-ca=/var/lib/mysql/ca.pem ssl-cert=/var/lib/mysql/client-cert.pem ssl-key=/var/lib/mysql/client-key.pem ssl-verify-server-cert ... [mysqld] ... ssl ssl-ca=/var/lib/mysql/ca.pem ssl-cert=/var/lib/mysql/server-cert.pem ssl-key=/var/lib/mysql/server-key.pem ...



Only top voted, non community-wiki answers of a minimum length are eligible