Details
-
Type:
Bug
-
Status: Open
-
Priority:
Major
-
Resolution: Unresolved
-
Affects Version/s: 10.0.21
-
Fix Version/s: None
-
Component/s: Data Definition - Alter Table
-
Labels:
Description
as reported by @Ara4Sh on #mariab
16:50 < Ara4Sh> Hello i dump my backup in a mariadb server (mysql Ver 15.1 Distrib 10.0.14-MariaDB) and import in another mariadb server (mysql Ver 15.1 Distrib 10.0.21-MariaDB) and here is
the problem
16:51 < Ara4Sh> ERROR 1064 (42000) at line 4185: 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
'AUTO_INCREMENT=61308' at line 10
MariaDB [test]> CREATE TABLE IF NOT EXISTS `arash` (
-> `call_id` int(11) NOT NULL AUTO_INCREMENT,
-> `number` varchar(15) CHARACTER SET utf8 COLLATE utf8_persian_ci DEFAULT NULL,
-> `date` datetime DEFAULT '0000-00-00 00:00:00',
-> `sms_call` int(1) DEFAULT NULL,
-> `compid` int(11) NOT NULL DEFAULT '0',
-> PRIMARY KEY (`call_id`,`compid`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-> /*!50100 PARTITION BY HASH (compid)
-> PARTITIONS 400 */ AUTO_INCREMENT=61308 ;
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 'AUTO_INCREMENT=61308' at line 10
swapping parition and auto_increment table options is successful:
MariaDB [test]> CREATE TABLE IF NOT EXISTS `arash` ( `call_id` int(11) NOT NULL AUTO_INCREMENT, `number` varchar(15) CHARACTER SET utf8 COLLATE utf8_persian_ci DEFAULT NULL, `date` datetime DEFAULT '0000-00-00 00:00:00', `sms_call` int(1) DEFAULT NULL, `compid` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`call_id`,`compid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=61308 /*!50100 PARTITION BY HASH (compid) PARTITIONS 400 */; Query OK, 0 rows affected (1.06 sec)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
testing on another instance same error :
mysql Ver 15.1 Distrib 10.0.14-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
I export structures again and remove all lines contains
/*!50100 PARTITION BY HASH (compid) PARTITIONS 400 */ AUTO_INCREMENT=61308 ;
Problem solved but if you have any insertion you have to delete them completely
Arash Shams
I don't quite understand – how exactly was this table created at the first place? When you run SHOW CREATE TABLE on this table on your 10.0.14, what does it show, could you please provide the output?
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | callservice | CREATE TABLE `callservice` ( `call_id` int(11) NOT NULL AUTO_INCREMENT, `number` varchar(15) CHARACTER SET utf8 COLLATE utf8_persian_ci DEFAULT NULL, `date` datetime DEFAULT '0000-00-00 00:00:00', `sms_call` int(1) DEFAULT NULL, `compid` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`call_id`,`compid`) ) ENGINE=InnoDB AUTO_INCREMENT=61662 DEFAULT CHARSET=utf8 /*!50100 PARTITION BY HASH (compid) PARTITIONS 400 */ | +-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Are you saying that when you run SHOW CREATE TABLE from the client, you see this (valid) table structure, but when you are running mysqldump on the same table, you get PARTITION and AUTO_INCREMENT clauses reverted? This is mysterious, as mysqldump itself runs SHOW CREATE queries.
Please paste or attach
- the exact command line you use to create the backup,
- mysqldump --version output (maybe it's some old or upstream or 3rd-party one),
- your cnf file(s).
Frankly I don't know yet if this will necessarily help to understand what is happening, but it's really strange, so the more information the better.
my.cnf
[client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc_messages_dir = /usr/share/mysql lc_messages = en_US skip-external-locking bind-address = 172.20.8.2 max_connections = 100 connect_timeout = 5 wait_timeout = 600 max_allowed_packet = 16M thread_cache_size = 128 sort_buffer_size = 4M bulk_insert_buffer_size = 16M tmp_table_size = 32M max_heap_table_size = 32M myisam_recover = BACKUP key_buffer_size = 128M table_open_cache = 400 myisam_sort_buffer_size = 512M concurrent_insert = 2 read_buffer_size = 2M read_rnd_buffer_size = 1M query_cache_limit = 128K query_cache_size = 64M log_warnings = 2 slow_query_log_file = /var/log/mysql/mariadb-slow.log long_query_time = 10 log_slow_verbosity = query_plan auto_increment_increment = 2 auto_increment_offset = 1 log_bin = /var/log/mysql/mariadb-bin log_bin_index = /var/log/mysql/mariadb-bin.index expire_logs_days = 10 max_binlog_size = 100M default_storage_engine = InnoDB innodb_buffer_pool_size = 256M innodb_log_buffer_size = 8M innodb_file_per_table = 1 innodb_open_files = 400 innodb_io_capacity = 400 innodb_flush_method = O_DIRECT server-id=1411972157 log_bin = /var/log/mysql/mysql-bin.log binlog_do_db = netband [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 16M !includedir /etc/mysql/conf.d/ [client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc_messages_dir = /usr/share/mysql lc_messages = en_US skip-external-locking bind-address = 172.20.8.2 max_connections = 100 connect_timeout = 5 wait_timeout = 600 max_allowed_packet = 16M thread_cache_size = 128 sort_buffer_size = 4M bulk_insert_buffer_size = 16M tmp_table_size = 32M max_heap_table_size = 32M myisam_recover = BACKUP key_buffer_size = 128M table_open_cache = 400 myisam_sort_buffer_size = 512M concurrent_insert = 2 read_buffer_size = 2M read_rnd_buffer_size = 1M query_cache_limit = 128K query_cache_size = 64M log_warnings = 2 slow_query_log_file = /var/log/mysql/mariadb-slow.log long_query_time = 10 log_slow_verbosity = query_plan auto_increment_increment = 2 auto_increment_offset = 1 log_bin = /var/log/mysql/mariadb-bin log_bin_index = /var/log/mysql/mariadb-bin.index expire_logs_days = 10 max_binlog_size = 100M default_storage_engine = InnoDB innodb_buffer_pool_size = 256M innodb_log_buffer_size = 8M innodb_file_per_table = 1 innodb_open_files = 400 innodb_io_capacity = 400 innodb_flush_method = O_DIRECT server-id=1411972157 log_bin = /var/log/mysql/mysql-bin.log binlog_do_db = arash [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 16M !includedir /etc/mysql/conf.d/
mysqldump version :
mysqldump Ver 10.15 Distrib 10.0.14-MariaDB, for debian-linux-gnu (x86_64)
Exact command :
mysqldump -u arash -p --all-databases --routines| gzip > /root/MySQLDB_`date '+%H-%m-%d-%Y'`.sql.gz
I still don't see how it can happen, mysqldump seems to execute the same simple show create table statement.
Arash Shams,
Can you provide your datadir, or at least your .frm file for the table – maybe we can figure out something from it?
Also, if it's easily reproducible for you, can you enable general_log, run mysqldump again, and send the contents of the general log, so that we see what exactly mysqldump is doing there?
The error log might also be useful.
Any data that you don't want to share publicly can be uploaded to our ftp.askmonty.org/private .
Hello
I tested on a Debian jessie server with mariadb Ver 15.1 Distrib 10.0.21-MariaDB problem exist