This article collect all of the things that surprise me when using MySQL/MariaDB. I should have write this long time ago, so the future me can learn from this.
You cannot rename a database
Use case: We finish migrating GBs of databases from different environment. Turns out some of databases name should be X instead Y (for any reasons).
What would you do?
There are two solutions for this: rename each table to use new database name,
RENAME TABLE olddb.table1 TO newdb.table1; RENAME TABLE olddb.table2 TO newdb.table2; ... RENAME TABLE olddb.tablen TO newdb.tablen;
or dump the old database and then restore into new database [1].
sql_mode=STRICT_TRANS_TABLES
Use case: we have two environments: X and Y. When we run the application on X, all functions are working well. But when we run it on Y, some functions failed because we did not fill the non-null columns.
What happened?
After inspecting the error and do some research, turns out on the environment
Y, they enable STRICT_TRANS_TABLES on sql_mode. [2]
Import fail due to DEFINER
Use case: we want to migrate the database from one environment to Google Cloud SQL. We run mysqldump on the source database and got couple of GBs dump file. Then we copy it into work area in the destination environment, import it using mysql command. Turns out after waiting couple of minutes you got the following error,
ERROR 1227 (42000) at line 8673: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
What would you do?
Since we cannot have SUPER privilege on Google Cloud SQL, we need to replace any string "DEFINER='x’@'y’" in the GBs dump file using sed or vi manually. There is no option to disable this during export nor importing the database.
Select on integer column using string does not return an error
Given the following table,
CREATE TABLE test ( col_int int );
and the following data,
INSERT INTO test(col_int) VALUES (1), (2);
The following query will return success,
SELECT * FROM test WHERE col_int = '1abc';
col_int|
-------+
1|
You cannot use function as default value, but…
Affected on MySQL version 5.7 or lower.
Let say we want to create column created_at as INT that store the UNIX Epoch
by default using UNIX_TIMESTAMP.
The idea for using UNIX_TIMESTAMP is that the value is always in UTC.
CREATE TABLE xyz ( ... created_at INT DEFAULT UNIX_TIMESTAMP() );
Somehow, there is an exception to the rule. You can store CURRENT_TIMESTAMP() to TIMESTAMP type. Note that, CURRENT_TIMESTAMP is depends on the server time zone.
Addendum
Messy configuration location
If you install MariaDB 10 on debian, the directory structure for configuration is,
10:36:06 /etc/mysql (ins) 0 # tree -a . ├── conf.d │ ├── mysql.cnf │ └── mysqldump.cnf ├── debian.cnf ├── debian-start ├── mariadb.cnf ├── mariadb.conf.d │ ├── 50-client.cnf │ ├── 50-mysql-clients.cnf │ ├── 50-mysqld_safe.cnf │ ├── 50-server.cnf │ └── 60-galera.cnf ├── my.cnf -> /etc/alternatives/my.cnf └── my.cnf.fallback
There are four possibilites where someone can add the server configuration: debian.cnf (but deprecated), my.cnf, mariadb.cnf, any files under conf.d, and any files under mariadb.conf.d. It is documented at mariadb.cnf file itself,
# The MariaDB/MySQL tools read configuration files in the following order: # 0. "/etc/mysql/my.cnf" symlinks to this file, reason why all the rest is read. # 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults, # 2. "/etc/mysql/conf.d/*.cnf" to set global options. # 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
Inconsistent system column names
If we look at the schema for mysql.user table,
MariaDB [mysql]> desc user; +------------------------+---------------------+------+-----+----------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+---------------------+------+-----+----------+-------+ | Host | char(60) | NO | | | | | User | char(80) | NO | | | | | Password | longtext | YES | | NULL | | | Select_priv | varchar(1) | YES | | NULL | | | Insert_priv | varchar(1) | YES | | NULL | | | Update_priv | varchar(1) | YES | | NULL | | | Delete_priv | varchar(1) | YES | | NULL | | | Create_priv | varchar(1) | YES | | NULL | | ... 20 more columns | Create_user_priv | varchar(1) | YES | | NULL | | | Event_priv | varchar(1) | YES | | NULL | | | Trigger_priv | varchar(1) | YES | | NULL | | | Create_tablespace_priv | varchar(1) | YES | | NULL | | | Delete_history_priv | varchar(1) | YES | | NULL | | | ssl_type | varchar(9) | YES | | NULL | | | ssl_cipher | longtext | NO | | | | | x509_issuer | longtext | NO | | | | | x509_subject | longtext | NO | | | | | max_questions | bigint(20) unsigned | NO | | 0 | | | max_updates | bigint(20) unsigned | NO | | 0 | | | max_connections | bigint(20) unsigned | NO | | 0 | | | max_user_connections | bigint(21) | NO | | 0 | | | plugin | longtext | NO | | | | | authentication_string | longtext | NO | | | | | password_expired | varchar(1) | NO | | | | | is_role | varchar(1) | YES | | NULL | | | default_role | longtext | NO | | | | | max_statement_time | decimal(12,6) | NO | | 0.000000 | | +------------------------+---------------------+------+-----+----------+-------+
You will find some columns start with uppercase and then later without uppercase.