Category: MySQL

ERROR 1118 Row size too large Changing some columns to TEXT or BLOB may help.

When restoring a MySQL database, i get error

root@server:~# mysql production < db.sql
ERROR 1118 (42000) at line 733: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
root@server:~#

I installed exactly same MraiaDB version on both source and destination server. But still restring failed.

This is due to default Engine. On old server, i had

MariaDB [adrymmls]>  show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                            | NO           | NO   | NO         |
| MyISAM             | DEFAULT | Non-transactional engine with good performance and small data footprint          | NO           | NO   | NO         |
| CSV                | YES     | Stores tables as CSV files                                                       | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                        | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                           | NO           | NO   | NO         |
| InnoDB             | YES     | Supports transactions, row-level locking, foreign keys and encryption for tables | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                               | NO           | NO   | NO         |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                   | YES          | NO   | YES        |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.000 sec)

MariaDB [adrymmls]>

On new server, it had InnoDB was set as default.

MariaDB [production]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                            | NO           | NO   | NO         |
| CSV                | YES     | Stores tables as CSV files                                                       | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                        | NO           | NO   | NO         |
| MyISAM             | YES     | Non-transactional engine with good performance and small data footprint          | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                           | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                               | NO           | NO   | NO         |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                   | YES          | NO   | YES        |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.000 sec)

MariaDB [production]>

Solution

Edit the SQL file in a text editor. Go to the line in the error message. In this case line 733, you will see a create table statement. Go to end of this table create statement. You will see ENGINE=InnoDB, replace it with ENGINE=MyISAM.

July 27, 2020

MariaDB ERROR 1524 Plugin unix_socket is not loaded
After upgrading MariaDB on Ubuntu server, i got following error
```
root@server:~# mysql
ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded
root@server:~# 
```
Users created worked fine. Only root user had this error.

To fix, you need to enable auth_socket.so plugin.

Edit file
```
vi /etc/mysql/mariadb.conf.d/50-server.cnf
```
Find
```
[mysqld]
```
Add below
```
plugin-load-add = auth_socket.so
```
Resatrt MariaDB
```
systemctl restart mysqld
```
See MySQL
July 27, 2020

MySQL Initialize Data Directory

To initialize the MySQL data directory, run

mysql_install_db --user=mysql

This will create /var/lib/mysql directory. For some versions of MySQL you can use

mysqld --initialize --user=mysql

Example

[root@panel lib]# mysql --version
mysql  Ver 15.1 Distrib 10.2.31-MariaDB, for Linux (x86_64) using readline 5.1
[root@panel lib]# mysql_install_db --user=mysql
Installing MariaDB/MySQL system tables in '/var/lib/mysql' ...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system


PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:

'/usr/bin/mysqladmin' -u root password 'new-password'
'/usr/bin/mysqladmin' -u root -h panel.topnews.us password 'new-password'

Alternatively you can run:
'/usr/bin/mysql_secure_installation'

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd '/usr' ; /usr/bin/mysqld_safe --datadir='/var/lib/mysql'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/mysql-test' ; perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
Get Involved


[root@panel lib]#

See MySQL

July 14, 2020

MySQL Database Character Set and Collation

To change MySQL database char set and collation, run

ALTER DATABASE DB_NAME_HERE CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

To usee current char set and collation, run

USE DB_NAME_HERE;
SELECT @@character_set_database, @@collation_database;

You can specify char set when creating database with following syntax

CREATE DATABASE DB_NAME_HERE CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

June 19, 2020

MySQL cannot connect via localhost

On an Apache server, MySQL can’t connect when you use localhost, but it work when you chane to IP address 127.0.0.1

When you use “localhost”, it use socket for connecting to MySQL server, this is faster than using TCP/IP connection, that is used when you use IP address to connect to MySQL server.

First find out socket path. To do this login to MySQL server, run

show variables like "socket";

See if you can connect using this socket with command

mysql -S /var/lib/mysql/mysql.sock -u root -p

In this cause, login to MySQL using socket worked.

I created a simple test PHP script to verify MySQL connection, it was able to connect to MySQL server using “localhost”.

Same script did not work when i try access it using web server. So the problem is web server user not able to connect to MySQL socket. You need to check permission for socket and parrent folders. In this case problem is fixed by running

chmod 755 /var/lib/mysql/

You can verify enabling SSH access for web server user, then connect to MySQL using command line or try access socket file as apache user.

MySQL Socket Path in php.ini

When a PHP application use localhost to connect, PHP find location of socket from php.ini, you need to verify this path set in php.ini is same as the socket path used by MySQL server.

# cat /etc/php.ini  | grep socket
; Default timeout for socket based streams (seconds)
; http://php.net/default-socket-timeout
default_socket_timeout = 60
;extension=php_sockets.dll
; Default socket name for local MySQL connects.  If empty, uses the built-in
; http://php.net/pdo_mysql.default-socket
pdo_mysql.default_socket= /var/lib/mysql/mysql.sock
; Default socket name for local MySQL connects.  If empty, uses the built-in
; http://php.net/mysql.default-socket
mysql.default_socket = /var/lib/mysql/mysql.sock
; Default socket name for local MySQL connects.  If empty, uses the built-in
; http://php.net/mysqli.default-socket
mysqli.default_socket = /var/lib/mysql/mysql.sock
#

If path is differnt, you need to make it same. You can either modify php.ini or MySQL server config file.

May 4, 2020

MySQL ERROR 1193 Unknown system variable GTID_PURGED
When restoring MySQL backup taken on Amazon RDS, i get error
```
root@PRD-50ml:~# mysql sok_db2 < db.sql
ERROR 1193 (HY000) at line 24: Unknown system variable 'GTID_PURGED'
root@PRD-50ml:~# 
```
To fix this, open db.sql in text editor, remove the line
```
SET @@GLOBAL.GTID_PURGED='';
```
Another solution is take a new MySQL backup with --set-gtid-purged=OFF option and restore it.
```
mysqldump -u DB_USER -p --set-gtid-purged=OFF --triggers --routines --events DB_NAME > DB_NAME.sql
```
March 25, 2020

MySQL create database

To create a database, you can use

create database DB_NAME;

Example

To create a database with specific charset use

create database DB_NAME character set utf8mb4 collate utf8mb4_bin

create database DB_NAME character set utf8mb4 collate utf8mb4_unicode_ci;

You can use whatever character set you wish instead of utf8mb4.

Example

create database serverok_db2 character set utf8mb4 collate utf8mb4_unicode_ci;

mysql create database

To delete a database, use

drop database DB_NAME;

March 25, 2020

MySQL root password in VestaCP
In VestaCP server, MySQL root password is stored in file /usr/local/vesta/conf/mysql.conf, to find MySQL root password, run
```
cat /usr/local/vesta/conf/mysql.conf
```
March 23, 2020
MySQL 8
MySQL 8 use caching_sha2_password as the default authentication method. Many MySQL clients still do not support this method. If you need to use the old method, you can set the authentication plugin as mysql_native_password. You can also set this as the default method by editing my.cnf file.
```
[mysqld]
default_authentication_plugin=mysql_native_password
```
To change a user to use mysql_native_password, run
```
ALTER USER 'USERNAME'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'PASSWORD_HERE';
```
March 2, 2020

Disable MySQL bin log on Bitnami

if you have cloud sevrer with less disk space, it is better disable MySQL bin log as it take approx 3 GB of disk space on bitnami server.

To disable MySQL binlog, edit mysql config file.

vi /opt/bitnami/mysql/my.cnf

Find

[mysqld]

Add below

disable_log_bin

Here is what i have in the my.cnf

root@wordpress-vm:~# cat /opt/bitnami/mysql/my.cnf

[mysqladmin]
user=root

[mysqld]
disable_log_bin
basedir=/opt/bitnami/mysql
datadir="/opt/bitnami/mysql/data"
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
tmpdir=/opt/bitnami/mysql/tmp
max_allowed_packet=32M
bind-address=127.0.0.1
skip-name-resolve=1
default-authentication-plugin=mysql_native_password

character-set-server=UTF8
collation-server=utf8_general_ci
[client]
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock

default-character-set=UTF8
[manager]
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
pid-file=/opt/bitnami/mysql/tmp/manager.pid
default-mysqld-path=/opt/bitnami/mysql/bin/mysqld.bin


!include /opt/bitnami/mysql/bitnami/my.cnf
root@wordpress-vm:~#

Now restart MySQL

/opt/bitnami/ctlscript.sh stop mysql
/opt/bitnami/ctlscript.sh start mysql

You can now remove the file starting with binlog from folder /opt/bitnami/mysql/data

rm -f /opt/bitnami/mysql/data/binlog.*

February 26, 2020

MySQL server has gone away
When restoring a MySQL database, i get error “MySQL server has gone away”.
```
# mysql -u root -p'serverok123' sok_wp < backup.sql
ERROR 2006 (HY000) at line 5095: MySQL server has gone away
# 
```
To fix this, edit MySQL configuration file. Add following line under [mysqld] section.
```
max_allowed_packet = 256M
```
Restart MySQL
```
systemctl restart mysql
```
If you use MariaDB
```
systemctl restart mariadb
```
Related Posts

MySQL
February 5, 2020

Aria engine is not enabled or did not start

When starting MaraDB, i got error

root@localhost:/var/log/mysql# cat error.log
2020-01-19  6:18:46 0 [ERROR] mysqld: Can't lock aria control file '/var/lib/mysql/aria_log_control' for exclusive use, error: 11. Will retry for 30 seconds
2020-01-19  6:19:17 0 [ERROR] mysqld: Got error 'Could not get an exclusive lock; file is probably in use by another process' when trying to use aria control file '/var/lib/mysql/aria_log_control'
2020-01-19  6:19:17 0 [ERROR] Plugin 'Aria' init function returned error.
2020-01-19  6:19:17 0 [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.
2020-01-19  6:19:17 0 [Note] InnoDB: Using Linux native AIO
2020-01-19  6:19:17 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-01-19  6:19:17 0 [Note] InnoDB: Uses event mutexes
2020-01-19  6:19:17 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-01-19  6:19:17 0 [Note] InnoDB: Number of pools: 1
2020-01-19  6:19:17 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-01-19  6:19:17 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-01-19  6:19:17 0 [Note] InnoDB: Completed initialization of buffer pool
2020-01-19  6:19:17 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-01-19  6:19:17 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=2137316301
2020-01-19  6:19:17 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-01-19  6:19:17 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2020-01-19  6:19:17 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-01-19  6:19:17 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-01-19  6:19:17 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-01-19  6:19:17 0 [Note] InnoDB: Waiting for purge to start
2020-01-19  6:19:17 0 [Note] InnoDB: 10.3.18 started; log sequence number 2137316310; transaction id 2275296
2020-01-19  6:19:17 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-01-19  6:19:17 0 [ERROR] Aria engine is not enabled or did not start. The Aria engine must be enabled to continue as mysqld was configured with --with-aria-tmp-tables
2020-01-19  6:19:17 0 [ERROR] Aborting

root@localhost:/var/log/mysql#

To fix error, remove the file

mv /var/lib/mysql/aria_log_control /root/

January 19, 2020