MariaDB DEFAULT NULL ERROR 1064 (42000)
When trying to restore a MySQL databse bakcup taken on MySQL 5.7 server to MairaDB 10.1 server i get following error.
|
1 2 3 4 5 6 |
root@server:~# mysql -u vcon_user -p vcon_sbc < vcon_sbc.sql Enter password: ERROR 1064 (42000) at line 287: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'json DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` ti' at line 3 root@server:~# |
Source server
|
1 2 3 |
root@ip-172-31-16-209:~# mysql --version mysql Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using EditLine wrapper root@ip-172-31-16-209:~# |
Destination server
|
1 2 3 |
root@server:~# mysql --version mysql Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 root@server:~# |
I opend the SQL file in editor and checked line 287, i run the SQL in mysql, that resulted in error
|
1 2 3 4 5 6 7 8 9 10 11 |
MariaDB [test]> CREATE TABLE `current_job_status_reports` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `jobs` json DEFAULT NULL, -> `created_at` timestamp NULL DEFAULT NULL, -> `updated_at` timestamp NULL DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'json DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` ti' at line 3 MariaDB [test]> |
I run the command on a MariaDB 10.2 server and it worked.
To fix the problem, upgrade MariaDB to version 10.2 or above. In this case, i went with MariaDB 10.04 and it worked fine.