MySQL Can’t create new tempfile

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

Check the table with myisamcheck

It reported some errros. To fix, run

When i run, i get following error

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

Read More…

Split mysqldump backup file into tables

MySQL

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

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

Read More…

Auto Restart MySQL if Crashed

MySQL

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

Add

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

Create cronjob to run […]

Read More…

Find MySQL Database and Table Size

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

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

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

See MySQL […]

Read More…

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

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

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

Read More…

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

When restoring a MySQL database, i get error

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

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

Solution Edit the SQL file in a […]

Read More…

MariaDB ERROR 1524 Plugin unix_socket is not loaded

After upgrading MariaDB on Ubuntu server, i got following error

Users created worked fine. Only root user had this error. To fix, you need to enable auth_socket.so plugin. Edit file

Find

Add below

Resatrt MariaDB

See MySQL […]

Read More…

MySQL Database Character Set and Collation

MySQL

To change MySQL database char set and collation, run

To usee current char set and collation, run

You can specify char set when creating database with following syntax

[…]

Read More…