[MDEV-8666] create table /*!50100 PARTITION BY HASH (compid) PARTITIONS 400 */ AUTO_INCREMENT=61308 ; failes Created: 2015-08-23  Updated: 2015-10-20  Resolved: 2015-10-20

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.0.21
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Daniel Black Assignee: Unassigned
Resolution: Cannot Reproduce Votes: 1
Labels: create_table, need_feedback


 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)
 
 



 Comments   
Comment by Arash Shams [ 2015-08-23 ]

Hello
I tested on a Debian jessie server with mariadb Ver 15.1 Distrib 10.0.21-MariaDB problem exist

Comment by Arash Shams [ 2015-08-23 ]

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

Comment by Arash Shams [ 2015-08-23 ]

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

Comment by Elena Stepanova [ 2015-08-24 ]

Ara4Sh
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?

Comment by Arash Shams [ 2015-08-24 ] 

+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| 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 */ |
 
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
 

Comment by Elena Stepanova [ 2015-08-24 ]

Ara4Sh,

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.

Comment by Arash Shams [ 2015-08-25 ]

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

Comment by Elena Stepanova [ 2015-09-22 ]

I still don't see how it can happen, mysqldump seems to execute the same simple show create table statement.

Ara4Sh,
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 .

Comment by Elena Stepanova [ 2015-10-20 ]

Closing as "Cannot reproduce" for now, but if you have any additional information, please comment and the report will be re-opened; it would be very interesting to discover how it could have happened.

Generated at Thu Feb 08 07:28:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.