Mysql Error Tablespace Size Stored In Header Is
Email Updates: Status: Verified Impact on me: None Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical) Version:5.0, 5.1, 5.5, 5.6.3 OS:Any Assigned to: Triage: Needs Triage: D3 (Medium) View Add Comment Files Developer Edit Submission View Progress Log Contributions [28 Apr 2011 19:38] Chris Calender Description: If you add 2 extra ibdata files to innodb_data_file_path, but the 2nd has permissions issue, the 1st new ibdata file is created, but not the second. The problem comes in because the 1st is created fine, but the tablespace size stored in the header is not updated. So when you fix the permissions problem for the 2nd ibdata file, then mysqld starts up properly, but you will see such error messages as below: InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! InnoDB: Error: tablespace size stored in header is 23040000 pages, but InnoDB: the sum of data file sizes is 26240000 pages How to repeat: Have working instance with following options: innodb_data_dir_home = innodb_data_file_path =/data/mysql/ibdata1:10M:autoextend Stop MySQL. Create new directory (and subdirectory) /data1/mysql/ that is only owned/accessible by the root user. Now add the 2 new ibdata files to the config. Point the first one to the working directory. Point the second to the non-working directory. innodb_data_file_path =/data/mysql/ibdata1:10M;/data/mysql/ibdata2:10M;/data1/mysql/ibdata3:10M;:autoextend Start mysqld. Will fail to start, OS error 13 when trying to create ibdata3. But note that ibdata2 is created. Now, change the path for ibdata3 to match that of ibdata2: innodb_data_file_path =/data/mysql/ibdata1:10M;/data/mysql/ibdata2:10M;/data/mysql/ibdata3:10M;:autoextend Now start mysqld again. It should start up fine, and create ibdata3. However, you will now see errors such as these: InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence nu
View as plain text Ian, are you sure that you copied the complete ibdata1 file to the new place? It is strange how 7 MB can be missing from the file end. What does the old .err log contain? Any message about disk space running out? The error below probably has not corrupted your tablespace. Best to run CHECK TABLE on some of your tables, though. When ibdata files are created, they are initially written full of zeros. You can probably fix the error by using another mysqld installation (or any method that makes a 7 MB zero-filled https://bugs.mysql.com/bug.php?id=60997 file) to create a new ibdata2 file that is 7 MB in size, and add that to the tablespace. Look from http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html how to edit my.cnf then. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support http://lists.mysql.com/mysql/177995 from https://order.mysql.com/ ----- Original Message ----- From: "Ian Grant"
2016-02-26 16 https://bugs.launchpad.net/bugs/1550322 This bug affects 2 people Affects Status Importance https://www.mail-archive.com/search?l=mysql@lists.mysql.com&q=subject:%22InnoDB%3A+Error%3A+tablespace+size+stored+in+header%22&o=newest&f=1 Assigned to Milestone Percona XtraBackup Edit Status tracked in 2.4 2.4 Fix Released High Sergei Glushchenko Edit Percona XtraBackup 2.4.4 You need to log in to change this bug's status. Affecting: Percona mysql error XtraBackup 2.4 Filed here by: Shahriyar Rzayev When: 2016-02-26 Confirmed: 2016-02-26 Assigned: 2016-07-10 Started work: 2016-07-10 Completed: 2016-07-11 Status Importance Milestone Fix Released High Percona XtraBackup 2.4.4 Assigned to Me Sergei Glushchenko (sergei.glushchenko) Comment on this change (optional) Email me about changes mysql error tablespace to this bug report Also affects project (?) Also affects distribution/package Nominate for series Bug Description Here are full steps: CREATE TABLE `sbtest1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Running: sysbench --test=/usr/share/doc/sysbench/tests/db/insert.lua --oltp-test-mode=complex --num-threads=200 --mysql-db=dbtest --mysql-user=root --mysql-password=Baku12345# --db-driver=mysql --max-requests=0 run Taking backups: xtrabackup --defaults-file=/etc/my.cnf --backup --datadir=/var/lib/mysql/ --target-dir=/home/backup_dir/full/ --user=root --password=Baku12345# --no-version-check xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/home/backup_dir/inc/inc1 --incremental-basedir=/home/backup_dir/full --datadir=/var/lib/mysql/ --user=root --password=Baku12345# --no-version-check xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/home/backup_dir/inc/inc2 --incremental-basedir=/home/backup_dir/inc/inc1 --datadir=/var/lib/mysql/ --user=root --password=Baku12345# --no-version-check xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/home/backup_dir/inc/inc3 --incremental-basedir=/home/backup_dir/inc/inc2 --datadir=/var/lib/mysql/ --user=root --password=Baku12345# --no-version-check Preparing backups: xtrabackup --defaults-file=/home/backup_dir/full/backup-my.cnf --prepare --apply-log-only --target-dir=/home/backup_dir/full xtrabackup --defaults-file=/home/backup_dir/full/backup-my.cnf --prepare --apply-log-only --target-dir=/home/backup_dir/full --incremental-dir=/home/backup_dir/inc/inc1 xtrabackup --defaults-file=/home/backup_dir/fu
size stored in header is 877184 pages, but InnoDB: the sum of data file sizes is 953856 pages And Mr. Heikki tell me to do these steps: (953856 - 877184) / 64 = 1198 MB 1) Stop the mysqld server. 2) Add a new 1198M ibdata file at the end of innodb_data_file_path. 3) When you start mysqld, InnoDB will write that new ibdata file full of zeros, and increment the tablespace size stored in the tablespace header by 1198M. 4) Then stop the mysqld server, remove the extra 1198M ibdata file from innodb_data_file_path and delete that extra ibdata file. 5) Start mysqld again. 6) Voila! Now the tablespace size stored in the tablespace header agrees with the combined size of the files in innodb_data_file_path! BUT With my case (1056000-64) / 64 = 6500 Mbyte It's bigger than a single file in my Linux (2 Gbyte). My InnoDB data path is: innodb_data_file_path = /rsv1/ibdata1:2000M;/rsv1/ibdata2:2000M;/rsv1/ibdata3:2000M;/rsv1/ibdata4:2000M;/rsv1/ibdata5:500M;/r sv3/ibdata6:2000M;/rsv3/ibdata7:2000M;/rsv3/ibdata8:2000M;/rsv3/ibdata9:2000M; Any solutions? | |-- Regards, Ady Wicaksono HP: +628562208680 | Re: InnoDB: Error: tablespace size stored in header is 640000 pages, but the sum of data file sizes is 1056000 pages 2005-08-26 Thread Ady Wicaksono Reply by me. How stupid i'm :( I've finished this warning error problem by adding 4 files -- 3 file each 3 Gbyte and 1 file 500Mbyte Thx mr. heiiki :D Sorry Ady Wicaksono wrote: |Dear All, As subject, Actually i've been ever meet this case when i see : InnoDB: Error: tablespace size stored in header is 877184 pages, but InnoDB: the sum of data file sizes is 953856 pages And Mr. Heikki tell me to do these steps: (953856 - 877184) / 64 = 1198 MB 1) Stop the mysqld server. 2) Add a new 1198M ibdata file at the end of innodb_data_file_path. 3) When you start mysqld, InnoDB will write that new ibdata file full of zeros, and increment the tablespace size stored in the tablespace header by 1198M. 4) Then stop the mysqld server, remove the extra 1198M ibdata file from innodb_data_file_path and delete that extra ibdata file. 5) Start mysqld again. 6) Voila! Now the tablespace size stored in the tablespace header agrees with the combined size of the files in innod