Category: MySQL – Page 3

MySQL Can’t create new tempfile

Posted on December 5, 2020 by ServerOk

When i try to repair a crashed database table, i get error

mysql> repair table visitorstats_sessions;
+-------------------------------------+--------+----------+------------------------------------------------------------------------+
| Table                               | Op     | Msg_type | Msg_text                                                               |
+-------------------------------------+--------+----------+------------------------------------------------------------------------+
| centovacastdb.visitorstats_sessions | repair | error    | Can't create new tempfile: './centovacastdb/visitorstats_sessions.TMD' |
| centovacastdb.visitorstats_sessions | repair | status   | Operation failed                                                       |
+-------------------------------------+--------+----------+------------------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql>

mysql> repair table visitorstats_sessions;

+-------------------------------------+--------+----------+------------------------------------------------------------------------+

+-------------------------------------+--------+----------+------------------------------------------------------------------------+

+-------------------------------------+--------+----------+------------------------------------------------------------------------+

2 rows in set (0.01 sec)

mysql>

Check the table with myisamcheck

[root@centos7 ~]# myisamchk -a /var/lib/mysql/centovacastdb/visitorstats_sessions
Checking MyISAM file: /var/lib/mysql/centovacastdb/visitorstats_sessions
Data records:  704545   Deleted blocks:       0
myisamchk: warning: Table is marked as crashed and last repair failed
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check data record references index: 3
- check data record references index: 4
- check record links
MyISAM-table '/var/lib/mysql/centovacastdb/visitorstats_sessions' is usable but should be fixed
[root@centos7 ~]#

[root@centos7 ~]# myisamchk -a /var/lib/mysql/centovacastdb/visitorstats_sessions

Checking MyISAM file: /var/lib/mysql/centovacastdb/visitorstats_sessions

Data records: 704545 Deleted blocks: 0

myisamchk: warning: Table is marked as crashed and last repair failed

- check file-size

- check record delete-chain

- check key delete-chain

- check index reference

- check data record references index: 1

- check data record references index: 2

- check data record references index: 3

- check data record references index: 4

- check record links

MyISAM-table '/var/lib/mysql/centovacastdb/visitorstats_sessions' is usable but should be fixed

[root@centos7 ~]#

It reported some errros. To fix, run

myisamchk -r /var/lib/mysql/centovacastdb/visitorstats_sessions

1	myisamchk -r /var/lib/mysql/centovacastdb/visitorstats_sessions

When i run, i get following error

[root@centos7 ~]# myisamchk -r /var/lib/mysql/centovacastdb/visitorstats_sessions
- recovering (with sort) MyISAM-table '/var/lib/mysql/centovacastdb/visitorstats_sessions'
Data records: 704545
myisamchk: error: Can't create new tempfile: '/var/lib/mysql/centovacastdb/visitorstats_sessions.TMD'
MyISAM-table '/var/lib/mysql/centovacastdb/visitorstats_sessions' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag
[root@centos7 ~]#

[root@centos7 ~]# myisamchk -r /var/lib/mysql/centovacastdb/visitorstats_sessions

- recovering (with sort) MyISAM-table '/var/lib/mysql/centovacastdb/visitorstats_sessions'

Data records: 704545

myisamchk: error: Can't create new tempfile: '/var/lib/mysql/centovacastdb/visitorstats_sessions.TMD'

MyISAM-table '/var/lib/mysql/centovacastdb/visitorstats_sessions' is not fixed because of errors

Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag

[root@centos7 ~]#

Since the error said “Can’t create new tempfile”, i checked disk usage to make sure disk is not full. Since […]

Split mysqldump backup file into tables

Posted on December 4, 2020 (December 4, 2020) by ServerOk

I had to restore a large MySQL backup file. When restoring one of the table resulted in error. To debug the error, i wanted to split the MySQL backup taken using mysqldump into tables. You can use csplit command to do this

csplit -s -ftable MYSQLDUMP_BACKUP_FILE_HERE "/-- Table structure for table/" {*}

1	csplit -s -ftable MYSQLDUMP_BACKUP_FILE_HERE "/-- Table structure for table/" {*}

This will generate files with name tableXX. First file table00 contains SQL […]

Auto Restart MySQL if Crashed

Posted on December 4, 2020 (June 17, 2021) by ServerOk

This bash script is used to auto-restart MySQL or MariaDB database if it crashes or stops for any reason. Create file

mkdir /usr/serverok
vi /usr/serverok/mysql_monitor.sh

1 2	mkdir /usr/serverok vi /usr/serverok/mysql_monitor.sh

Add

#!/bin/bash
# Author: ServerOK
# Web: https://serverok.in/mysql-restart-bash

MYSQL_REPLY="$(mysqladmin ping)"
TIME_STAMP="$(date "+%Y-%m-%d %H:%M:%S")"

if [[ ! "$MYSQL_REPLY" =~ "mysqld is alive" ]]
then
    systemctl restart mariadb
    echo -e "${TIME_STAMP} MySQL Down\n"
fi

#!/bin/bash

# Author: ServerOK

# Web: https://serverok.in/mysql-restart-bash

MYSQL_REPLY="$(mysqladmin ping)"

TIME_STAMP="$(date "+%Y-%m-%d %H:%M:%S")"

if [[ ! "$MYSQL_REPLY" =~ "mysqld is alive" ]]

then

systemctl restart mariadb

echo -e "${TIME_STAMP} MySQL Down\n"

In the code, replace YOUR_ROOT_PW_HERE with your actual root password. If you are not using “root”, replace root with whatever username you use. Make it executable

chmod 755 /usr/serverok/mysql_monitor.sh

1	chmod 755 /usr/serverok/mysql_monitor.sh

Create cronjob to run […]

Find MySQL Database and Table Size

Posted on November 24, 2020 (November 24, 2020) by ServerOk

To find size of database using SQL command, run following SQL in MySQL promt.

SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;

1	SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;

Example FInd disk usage by tables To find disk usage by tables in database, run

SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) as "size (MB)" FROM information_schema.TABLES WHERE table_schema = "DB_NAME_HERE"  ORDER BY data_length DESC;

1	SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) as "size (MB)" FROM information_schema.TABLES WHERE table_schema = "DB_NAME_HERE" ORDER BY data_length DESC;

In above SQL, replace DB_NAME_HERE with actual name of the database. Example

SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) as "size (MB)" FROM information_schema.TABLES WHERE table_schema = "xogifts_db"  ORDER BY data_length DESC;

1	SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) as "size (MB)" FROM information_schema.TABLES WHERE table_schema = "xogifts_db" ORDER BY data_length DESC;

See MySQL […]

MySQL ERROR Unable to create or change a table without a primary key

Posted on October 22, 2020 by ServerOk

When trying to restore a database backup to Managed DigitialOcean MySQL 8 database, i get following error

root@ocp:~# mysql -u doadmin -p'BKwsQcqEGbSV3w' -h db-sevrerok-do-user-8606188-0.b.db.ondigitalocean.com -P 25060 serverok_db < serverok_db.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3750 (HY000) at line 223: Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
root@ocp:~#

root@ocp:~# mysql -u doadmin -p'BKwsQcqEGbSV3w' -h db-sevrerok-do-user-8606188-0.b.db.ondigitalocean.com -P 25060 serverok_db < serverok_db.sql

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 3750 (HY000) at line 223: Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.

root@ocp:~#

This is because one of the tables in your MySQL backup don’t have primary key defined. Solution There are few ways you can fix this. Proper way is to define a primary key for the table. If […]

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

Posted on July 27, 2020 by ServerOk

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:~#

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]>

MariaDB [adrymmls]> show engines;

+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+

+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+

| 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]>

MariaDB [production]> show engines;

+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+

+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+

| 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 […]

MariaDB ERROR 1524 Plugin unix_socket is not loaded

Posted on July 27, 2020 by ServerOk

After upgrading MariaDB on Ubuntu server, i got following error

root@server:~# mysql
ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded
root@server:~#

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

1	vi /etc/mysql/mariadb.conf.d/50-server.cnf

Find

[mysqld]

[mysqld]

Add below

plugin-load-add = auth_socket.so

1	plugin-load-add = auth_socket.so

Resatrt MariaDB

systemctl restart mysqld

1	systemctl restart mysqld

See MySQL […]

MySQL Initialize Data Directory

Posted on July 14, 2020 by ServerOk

To initialize MySQL data directory, run

mysql_install_db --user=mysql

1	mysql_install_db --user=mysql

This will create /var/lib/mysql dirrectory. For some version of MySQL you can use

mysqld --initialize --user=mysql

1	mysqld --initialize --user=mysql

See MySQL […]

MySQL Database Character Set and Collation

Posted on June 19, 2020 by ServerOk

To change MySQL database char set and collation, run

ALTER DATABASE DB_NAME_HERE CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

1	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;

1 2	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;

1	CREATE DATABASE DB_NAME_HERE CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

[…]

MySQL cannot connect via localhost

Posted on May 4, 2020 (May 5, 2020) by ServerOk

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 […]