MySQL shows warning: Using unique option is deprecated and will be removed in a future release

Error when exporting a dump in plesk databases (See:https://docs.plesk.com/en-US/onyx/reseller-guide/website-management/website-databases/exporting-and-importing-database-dumps.69538/): Warning: Using unique option prefix database instead of databases is deprecated and will be removed in a future release. Please use the full name instead. Cause key_buffer and myisam-recover are deprecated in Mysql 5.5. myisam-recover option is renamed as of MySQL 5.5.3 to myisam-recover-options . Resolution […]

Read More

Disable ONLY_FULL_GROUP_BY in Mysql on Ubuntu 16

Recently I ran a command in mysql that resulted in this error: mysql: [ERROR] unknown variable ‘sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’ So I had to add a line to the mysql config file. In Ubuntu 16: # nano /etc/mysql/mysql.conf.d/mysqld.cnf Add the following line under mysqld [mysqld] sql_mode = “STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION” Restart mysql: # systemctl restart mysql

Read More

MariaDB cnf files

Here is the original /etc/my.cnf file for a default mariadb installation: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file […]

Read More

How do you calculate mysql max_connections variable

The basic formulas are: Available RAM = Global Buffers + (Thread Buffers x max_connections) max_connections = (Available RAM – Global Buffers) / Thread Buffers To get the list of buffers and their values: SHOW VARIABLES LIKE ‘%buffer%’; Here’s a list of the buffers and whether they’re Global or Thread: Global Buffers: key_buffer_size, innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size, […]

Read More

List sizes of MySQL databases

Do you wonder which databases are actually taking up how much space but only have one huge ibdata1 in your /var/lib/mysql and the directories inside your mysql data directory don’t represent the actual database sizes? Run from a mysql root console: mysql> SELECT table_schema AS “Database name”, SUM(data_length + index_length) / 1024 / 1024 AS […]

Read More

How do I disable MySQL strict mode on the server?

Please follow the under mentioned instructions to turn off the MySQL strict mode. Make the following changes in the “my.ini/my.cnf”: Look for the following line: sql-mode = “STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION” Change it to: sql_mode=”” Restart the MySQL service. or Look for the following line , this line will set MySQL strict mode sql-mode = “STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION” To disable […]

Read More