Tag: mysql error

  • MySQL ERROR 1114 (HY000) at line 2137: The table ‘X’ is full

    MySQL ERROR 1114 (HY000) at line 2137: The table ‘X’ is full

    When restoring a MySQL database, I get the following error

    root@localhost:~# mysql -u sok_user -p'serverok123' sok_db < parkingcupid.sql
ERROR 1114 (HY000) at line 2137: The table 'field_data_field_monthly_price' is full
root@localhost:~

    How to fix ERROR 1114 (HY000) table is full?

    First, check if the disk on the server is full. 

    df -h

    In my case server disk was not full. Next try increasing the value for variables tmp_table_size and max_heap_table_size.

    Edit file

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

    Under [mysqld] section, add

    tmp_table_size=256M
max_heap_table_size=256M

    Restart MySQL

    systemctl restart mysql

    After this change MySQL database restore worked without giving any error.

    If the above solution did not fix your problem, check the following.

    Check Mysql variables innodb_data_file_path

    MariaDB [(none)]> select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend  |
+-------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]>

    If innodb_data_file_path variable have a max value set, you should update it. In this case, we don't have a max value.

    Verify innodb_file_per_table is set to 1.

    MariaDB [(none)]> select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]>

    See MySQL

  • ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

    ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

    When deleting a table in a MySQL database, I get the following error message

    MariaDB [thrkhztbpt]> drop table users;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
MariaDB [thrkhztbpt]>

    To fix the error, run

    SET FOREIGN_KEY_CHECKS=0;

    Now the drop table SQL command will work. After you dropped the table, re-enable foreign key check with

    SET FOREIGN_KEY_CHECKS=1;

    Example

    MariaDB [thrkhztbpt]> drop table users;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
MariaDB [thrkhztbpt]> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [thrkhztbpt]> drop table users;
Query OK, 0 rows affected (0.00 sec)

MariaDB [thrkhztbpt]> 
MariaDB [thrkhztbpt]> SET FOREIGN_KEY_CHECKS=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [thrkhztbpt]>

  • 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

    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 this is not possible, you can set sql_require_primary_key to OFF.

    To set sql_require_primary_key to OFF, edit file

    vi /etc/mysql/mysql.conf.d/mysqld.cnf

    Under [mysqld] section, add

    sql_require_primary_key=0

    Now restart MySQL server.

    systemctl restart mysql

    In the case of DigitalOcean, we can't edit MySQL configuration as it is Managed MySQL Database service, What you can do is edit the MySQL backup file. Add following code to top of the file.

    SET sql_require_primary_key=0;

    This should be added as first SQL statement. Now do restore again, it will work.

  • MySQL Recovering after a crash using tc.log

    MySQL Recovering after a crash using tc.log

    After MySQL upgrade from MraiaDB 10.1 to 10.3, MySQL failed to start. On checking log, found

    root@localhost:/var/log/mysql# cat error.log
2019-09-23  6:30:10 0 [Note] InnoDB: Using Linux native AIO
2019-09-23  6:30:10 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-09-23  6:30:10 0 [Note] InnoDB: Uses event mutexes
2019-09-23  6:30:10 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-09-23  6:30:10 0 [Note] InnoDB: Number of pools: 1
2019-09-23  6:30:10 0 [Note] InnoDB: Using SSE2 crc32 instructions
2019-09-23  6:30:10 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-09-23  6:30:10 0 [Note] InnoDB: Completed initialization of buffer pool
2019-09-23  6:30:10 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-09-23  6:30:10 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2019-09-23  6:30:10 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-09-23  6:30:10 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-09-23  6:30:10 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-09-23  6:30:10 0 [Note] InnoDB: Waiting for purge to start
2019-09-23  6:30:10 0 [Note] InnoDB: 10.3.17 started; log sequence number 682398229; transaction id 1563655
2019-09-23  6:30:10 0 [Note] Plugin 'FEEDBACK' is disabled.
2019-09-23  6:30:10 0 [Note] Recovering after a crash using tc.log
2019-09-23  6:30:10 0 [ERROR] Can't init tc log
2019-09-23  6:30:10 0 [ERROR] Aborting

root@localhost:/var/log/mysql#

    On /var/lib/mysql folder, there is a tc.log file with 0 size. The error is solved by removing this file.

    mv /var/lib/mysql/tc.log /root/