Over a period of time we have accumulated a large number of SQL tips and tricks. This is a collection of those quirks. Generic SQL, MSSQL, MySQL & PostgreSQL are included.
To find all the rows in a table where a certain value is empty
select productid,title_tag,weight,length,width,height,meta_description
from xcart_products where LENGTH(meta_description) != 0"
queries of this sort can be useful while using within XML files.
To find all the values when a certain value is NULL. Note that NULL and empty are different
select productid,title_tag,weight,length,width,height,meta_description from xcart_products where meta_description IS NOT NULL;
The diamond operator <> is an interesting equivalent of <= =>
Error after upgrading to MySQL 5.7
If you have run across errors after upgrading to MySQL 5.7, you are not alone and its a well documented error.
A typical error is given below:
Setting up mysql-server-5.7 (5.7.20-0ubuntu0.16.04.1) ...
/var/lib/dpkg/info/mysql-server-5.7.postinst: line 143: /usr/share/mysql-common/configure-symlinks: No such file or directory
dpkg: error processing package mysql-server-5.7 (--configure):
subprocess installed post-installation script returned error exit status 1
No apport report written because the error message indicates its a followup error from a previous failure.
dpkg: dependency problems prevent configuration of mysql-server:
mysql-server depends on mysql-server-5.7; however:
Package mysql-server-5.7 is not configured yet.
dpkg: error processing package mysql-server (–configure):
dependency problems – leaving unconfigured
Errors were encountered while processing:
mysql-server-5.7
mysql-server
make changes as below
You will have to replace couple of variables from your configuration files
grep -Er ‘key.buffer|
will list the variables & make changes as per the following document:
https://bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/1571865
You may still get the error and you can remove a file to proceed.
delete
rm -f /var/lib/dpkg/info/mysql-server-5.7.postinst