Unknown or incorrect time zone error

Sometimes I get this error on replication slaves. I had created an index on the master and the command replicated to the slave. However, replication broken on the slave and the error from "show slave status" was the following:

Error 'Unknown or incorrect time zone: 'America/Denver'' on query. Default database: ''. Query: 'CREATE INDEX `my_index`  ON `myDatabase`.`my_table` (column1, column2) COMMENT '' ALGORITHM DEFAULT LOCK DEFAULT'

For whatever reason this MySQL server didn't have the time zone information. You can add the time zone information like this:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

Here is a reference to this on stack exchange:

https://dba.stackexchange.com/questions/120945/how-do-i-resolve-this-error-error-1298-hy000-unknown-or-incorrect-time-zone

Using alias in WHERE clause in MySQL - understanding how queries are parsed

I had written a very long query where I was using an alias and needed to be able to filter the query on the alias. Turns out you cannot use an alias in a WHERE clause. BUT you can use a HAVING clause which can be made to mimic certain aspects of a WHERE clause.

https://stackoverflow.com/questions/200200/can-you-use-an-alias-in-the-where-clause-in-mysql

In relation to this, it can be important to understand how queries are parsed:

https://stackoverflow.com/questions/24127932/mysql-query-clause-execution-order

From the above post on stack overflow:

Order that queries are executed, although I think HAVING and GROUP BY could come after SELECT:

FROM clause
WHERE clause
SELECT clause
GROUP BY clause
HAVING clause
ORDER BY clause

This is important for understanding how queries are parsed. You cannot use a column alias defined in a SELECT in the WHERE clause, for instance, because the WHERE is parsed before the SELECT. On the other hand, such an alias can be in the ORDER BY clause.

And another blog post on SQL Query Order of Operations:

https://www.bennadel.com/blog/70-sql-query-order-of-operations.htm

Query to find which users have priv on a specific database

SET @db_name = 'db_name';
SELECT -- the list of global privileges
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mu.host `host`,
mu.user `user`,
(case when `mu`.`password` ='' then "**Yes**" ELSE 'No' end) as `Empty Password?`,
mu.password as 'password',
'ALL' as `Database`,
'-' as `Tables`,
'-' as `Views`,
'-' as `Tables Columns`,
'-' as `Views Columns`,
'-' as `Procedures`,
'-' as `Functions`,
TRIM(TRAILING ',' FROM(RTRIM(CONCAT(
IF(mu.Select_priv = 'Y', 'Select, ', ''),
IF(mu.Insert_priv = 'Y', 'Insert, ', ''),
IF(mu.Update_priv = 'Y', 'Update, ', ''),
IF(mu.Delete_priv = 'Y', 'Delete, ', ''),
IF(mu.Create_priv = 'Y', 'Create, ', ''),
IF(mu.Drop_priv = 'Y', 'Drop, ', ''),
IF(mu.Reload_priv = 'Y', 'Reload, ', ''),
IF(mu.Shutdown_priv = 'Y', 'Shutdown, ', ''),
IF(mu.Process_priv = 'Y', 'Process, ', ''),
IF(mu.File_priv = 'Y', 'File, ', ''),
IF(mu.Grant_priv = 'Y', 'Grant, ', ''),
IF(mu.References_priv = 'Y', 'References, ', ''),
IF(mu.Index_priv = 'Y', 'Index, ', ''),
IF(mu.Alter_priv = 'Y', 'Alter, ', ''),
IF(mu.Show_db_priv = 'Y', 'SHOW DATABASES, ', ''),
IF(mu.Super_priv = 'Y', 'Super, ', ''),
IF(mu.Create_tmp_table_priv = 'Y', 'CREATE TEMPORARY TABLES, ', ''),
IF(mu.Lock_tables_priv = 'Y', 'LOCK TABLES, ', ''),
IF(mu.Execute_priv = 'Y', 'Execute, ', ''),
IF(mu.Repl_slave_priv = 'Y', 'REPLICATION SLAVE, ', ''),
IF(mu.Repl_client_priv = 'Y', 'REPLICATION CLIENT, ', ''),
IF(mu.Create_view_priv = 'Y', 'CREATE VIEW, ', ''),
IF(mu.Show_view_priv = 'Y', 'SHOW VIEW, ', ''),
IF(mu.Create_routine_priv = 'Y', 'CREATE ROUTINE, ', ''),
IF(mu.Alter_routine_priv = 'Y', 'ALTER ROUTINE, ', ''),
IF(mu.Create_user_priv = 'Y', 'CREATE USER, ', ''),
IF(mu.Event_priv = 'Y', 'Event, ', ''),
IF(mu.Trigger_priv = 'Y', 'Trigger, ', '')
)))) AS `Privileges`
FROM
mysql.user mu
HAVING Privileges <> ''
UNION
SELECT -- the list of privileges for a database
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
md.host `host`,
md.user `user`,
'-' as `Empty Password?`,
'-' as 'password',
md.db `Database`,
'-' as `Tables`,
'-' as `Views`,
'-' as `Tables Columns`,
'-' as `Views Columns`,
'-' as `Procedures`,
'-' as `Functions`,
TRIM(TRAILING ',' FROM(RTRIM(CONCAT(
IF(md.Select_priv = 'Y', 'Select, ', ''),
IF(md.Insert_priv = 'Y', 'Insert, ', ''),
IF(md.Update_priv = 'Y', 'Update, ', ''),
IF(md.Delete_priv = 'Y', 'Delete, ', ''),
IF(md.Create_priv = 'Y', 'Create, ', ''),
IF(md.Drop_priv = 'Y', 'Drop, ', ''),
IF(md.Grant_priv = 'Y', 'Grant, ', ''),
IF(md.References_priv = 'Y', 'References, ', ''),
IF(md.Index_priv = 'Y', 'Index, ', ''),
IF(md.Alter_priv = 'Y', 'Alter, ', ''),
IF(md.Create_tmp_table_priv = 'Y', 'CREATE TEMPORARY TABLES, ', ''),
IF(md.Lock_tables_priv = 'Y', 'LOCK TABLES, ', ''),
IF(md.Create_view_priv = 'Y', 'CREATE VIEW, ', ''),
IF(md.Show_view_priv = 'Y', 'SHOW VIEW, ', ''),
IF(md.Create_routine_priv = 'Y', 'CREATE ROUTINE, ', ''),
IF(md.Alter_routine_priv = 'Y', 'ALTER ROUTINE, ', ''),
IF(md.Execute_priv = 'Y', 'Execute, ', ''),
IF(md.Event_priv = 'Y', 'Event, ', ''),
IF(md.Trigger_priv = 'Y', 'Trigger, ', '')
))))  AS `Privileges`
FROM
mysql.db md
WHERE md.Db = @db_name
UNION
SELECT -- the list of privileges for tables
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mt.host `host`,
mt.user `user`,
'-' as `Empty Password?`,
'-' as 'password',
'-' as `Database`,
CONCAT(mt.Db, '.', mt.Table_name) `Tables`,
'-' as `Views`,
'-' as `Tables Columns`,
'-' as `Views Columns`,
'-' as `Procedures`,
'-' as `Functions`,
REPLACE(mt.Table_priv, ',', ', ') AS `Privileges`
FROM
mysql.tables_priv mt
WHERE mt.Db = @db_name
AND
mt.Table_name IN
(SELECT
DISTINCT
t.table_name `tables`
FROM
information_schema.tables AS t
WHERE
t.table_type IN
('BASE TABLE', 'SYSTEM VIEW', 'TEMPORARY', '') OR
t.table_type <> 'VIEW' AND
t.create_options IS NOT NULL
)
UNION
SELECT -- the list of privileges for views
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mv.host `host`,
mv.user `user`,
'-' as `Empty Password?`,
'-' as 'password',
'-' as `Database`,
'-' as `Tables`,
CONCAT(mv.Db, '.', mv.Table_name) `Views`,
'-' as `Tables Columns`,
'-' as `Views Columns`,
'-' as `Procedures`,
'-' as `Functions`,
REPLACE(mv.Table_priv, ',', ', ') AS `Privileges`
FROM
mysql.tables_priv mv
WHERE
 mv.Db = @db_name
AND
mv.Table_name IN
(SELECT
DISTINCT
v.table_name `views`
FROM
information_schema.views AS v
)
UNION
SELECT -- the list of privileges for table columns
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mtc.host `host`,
mtc.user `user`,
'-' as `Empty Password?`,
'-' as 'password',
'-' as `Database`,
'-' as `Tables`,
'-' as `Views`,
CONCAT(mtc.Db, '.', mtc.Table_name, '.', mtc.Column_name) `Tables Columns`,
'-' as `Views Columns`,
'-' as `Procedures`,
'-' as `Functions`,
REPLACE(mtc.Column_priv, ',', ', ') AS `Privileges`
FROM
mysql.columns_priv mtc
WHERE
 mtc.Db = @db_name
AND mtc.Table_name IN
(SELECT
DISTINCT
t.table_name `tables`
FROM
information_schema.tables AS t
WHERE
t.table_type IN
('BASE TABLE', 'SYSTEM VIEW', 'TEMPORARY', '') OR
t.table_type <> 'VIEW' AND
t.create_options IS NOT NULL
)
UNION
SELECT -- the list of privileges for view columns
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mvc.host `host`,
mvc.user `user`,
'-' as `Empty Password?`,
'-' as 'password',
'-' as `Database`,
'-' as `Tables`,
'-' as `Views`,
'-' as `Tables Columns`,
CONCAT(mvc.Db, '.', mvc.Table_name, '.', mvc.Column_name) `Views Columns`,
'-' as `Procedures`,
'-' as `Functions`,
REPLACE(mvc.Column_priv, ',', ', ') AS `Privileges`
FROM
mysql.columns_priv mvc
WHERE
 mvc.Db = @db_name
AND mvc.Table_name IN
(SELECT
DISTINCT
v.table_name `views`
FROM
information_schema.views AS v
)
UNION
SELECT -- the list of privileges for procedures
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mp.host `host`,
mp.user `user`,
'-' as `Empty Password?`,
'-' as 'password',
'-' as `Database`,
'-' as `Tables`,
'-' as `Views`,
'-' as `Tables Columns`,
'-' as `Views Columns`,
CONCAT(mp.Db, '.', mp.Routine_name) `Procedures`,
'-' as `Functions`,
REPLACE(mp.Proc_priv, ',', ', ') AS `Privileges`
FROM
mysql.procs_priv mp
WHERE
mp.Routine_type = 'PROCEDURE'
AND mp.Db = @db_name
UNION
SELECT -- the list of privileges for functions
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mf.host `host`,
mf.user `user`,
'-' as `Empty Password?`,
'-' as 'password',
'-' as `Database`,
'-' as `Tables`,
'-' as `Views`,
'-' as `Tables Columns`,
'-' as `Views Columns`,
'-' as `Procedures`,
CONCAT(mf.Db, '.', mf.Routine_name) `Functions`,
REPLACE(mf.Proc_priv, ',', ', ') AS `Privileges`
FROM
mysql.procs_priv mf
WHERE
mf.Routine_type = 'FUNCTION'
AND mf.Db = @db_name

Use if exists to run a different query on different version of MySQL

I wrote a simple query for checking daily if there are any users with blank passwords for a client which uses the IF EXISTS syntax.

 Client has a mix of MySQL 5.1, 5.5, 5.6, 5.7, Aurora, and RDS.

The need for this query was because the mysql.user table is different in MySQL 5.7. The password column was removed and the hash was changed to be stored in the authentication_String column. I needed to be able to run a different query depending on the version. This query gets run on thousands of servers and is rolled up as part of a report.


SELECT IF (EXISTS(
                  SELECT @@version
                  FROM DUAL
                  WHERE @@version LIKE '5.1%'
                  OR @@version LIKE '5.5%'
                  OR @@version LIKE '5.6%'
                  )
         ,concat('SELECT user, host, \'users\' FROM mysql.user WHERE password = \'\';')
         ,concat('SELECT user, host, \'users\' FROM mysql.user WHERE authentication_String = \'\';')) into @a;
         SELECT @a;
PREPARE stmt1 FROM @a;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

Moving data from MySQL into Aurora/RDS

Migrating databases from your data center to Amazon RDS or Amazon Aurora can be fun. Here are some tips to help.

If you data set is larger than a few GB, use mydumper and myloader instead of a normal MySQL dump. It will go a lot faster because mydumper and myloader is multi-threaded. Using these tools does introduce some caveats though.

Aurora will not let the user doing the restore create definers for other users. If you try this you will get this misleading error:

ERROR 1227 (42000) at line 1902: Access denied; you need (at least one of) the SUPER privilege(s) for this

If you are migrating a schema with routines (functions or stored procedures), events, triggers, or views then you are going to have definers for the code in each of those. You can either change all the definers in your schemas before you dump so they are standardized or change them after the dump but before importing into AWS.

This is the process I am using to dump and restore from a client's data center to Aurora. My client has a long running query killer on their master servers so I generally perform the dumps on a read-only replica (slave). If I must perform the dump on the master, then I must disable the event that performs the query killing during the duration of the dump.

# 1. Create a dump of objects with no data excluding stored procedures, functions, events of views.
# 2. Restore dump on new server.
# 3. Re-create the users on new server but not the grants for any stored procedures, functions, or events. Remove/replace any grants which are not compatible with RDS/Aurora.
# 4. Create a dump of the the objects without data (a second time) but this time include stored procedures, functions, events and  views.
# 5. Get a list of the unique definers on the source system. Do a find and replace of all the unique definer users and replace with standard definer.
# 6. Restore the dump on the new server as the same user which is the definer.
# 7. Dump the grants from the source.
# 8. Remove/replace any grants which are not compatible with RDS/Aurora.
# 9. Apply all the grants including stored procedures, functions, events.
# 10. Do a full dump and restore of all data and all objects

The reason for this multi stop process is you cannot create objects which have a definer if the user has not been created yet. You cannot grant permissions to objects if the object has not been created yet. This is why you cannot always do a straight dump on a new machine. Restoring to RDS adds in more steps because the definers must be the same user which is doing the restore.

Here is a great white paper written by the Aurora team lead at Amazon:

https://d0.awsstatic.com/whitepapers/Migration/amazon-aurora-migration-handbook.pdf

What happened to deep db?

When I was at Percona Live last year, there seemed to be a lot of excitement around what DeepDB could do for MySQL users. Their sales people kept hounding me to try it out but I didn't have time. I saw some news articles that they have open sourced the technology and now are being called Deep Software Foundation (http://www.deepis.org).

They had a lot of good blog posts and I really want to see how this technology progresses. Here are some interesting blog posts:

http://dev.deepis.com.473elmp01.blackmesh.com/blog/innodb-writing-challenges
http://dev.deepis.com.473elmp01.blackmesh.com/insights/blog/understanding-science-databases
http://dev.deepis.com.473elmp01.blackmesh.com/insights/blog/reimaging-science-databases
http://dev.deepis.com.473elmp01.blackmesh.com/blog/mysql-keep-grumbling

References to news articles:

https://finance.yahoo.com/news/deep-information-sciences-goes-open-130000559.html

https://www.businesswire.com/news/home/20170502005138/en/Deep-Information-Sciences-Open-Source-Relaunches-Deep

Disabling selinux on CentOS

If you try to run MySQL with selinux running on CentOS, you will get vague permission denied error like the following. It is frustaring to figure out why MySQL will not start because it does not point to SElinux being the problem.

/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13 - Permission denied)
2017-04-20 21:01:43 20164 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create

Starting MySQL (Percona Server). ERROR! The server quit without updating PID file


rm: cannot remove `/var/lib/mysql/SERVERNAME.pid': Permission denied
 ERROR! The server quit without updating PID file (/var/lib/SERVERNAME.pid).

Check your log file to see what errors MySQL is generating during the start up process.

You can turn it off like this:

echo 0 >/selinux/enforce

Credit to:

https://www.cyberciti.biz/faq/howto-turn-off-selinux/