1

Prehistory:

I wanted to see how mysql handles in tables with millions of rows. So, I started to insert bunches of rows in one table. After ~80 millions mysql server has gone away and didn't start after. Then I installed mariadb and later removed it. Somehow installed mysql-server again. Lost any pieces of previous config files. After that, I realized that I need one database which engine is innodb. I have old data files. So I need to start mysqld with old data or dump that particular database somehow. Can't find a way to do last thing.

My config is Ubuntu 16.4 i686 and mysql-server 5.7.

To start mysqld I tried innodb_force_recovery of 4,5 and 6. No result. Here is /var/log/mysql/error.log:

2017-02-28T09:29:14.103113Z 0 [Warning] Changed limits:     max_open_files: 1024 (requested 6161)
2017-02-28T09:29:14.103225Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 3000)
2017-02-28T09:29:14.280958Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-02-28T09:29:14.283429Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.17-0ubuntu0.16.04.1) starting as process 30221 ...
2017-02-28T09:29:14.289321Z 0 [Note] InnoDB: PUNCH HOLE support available
2017-02-28T09:29:14.289355Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-02-28T09:29:14.289361Z 0 [Note] InnoDB: Uses event mutexes
2017-02-28T09:29:14.289366Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2017-02-28T09:29:14.289372Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2017-02-28T09:29:14.289377Z 0 [Note] InnoDB: Using Linux native AIO
2017-02-28T09:29:14.289625Z 0 [Note] InnoDB: Number of pools: 1
2017-02-28T09:29:14.289725Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2017-02-28T09:29:14.291134Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-02-28T09:29:14.302212Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-02-28T09:29:14.304956Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-02-28T09:29:14.318160Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-02-28T09:29:14.319899Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 14888319000
2017-02-28T09:29:14.809862Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 14893561856
2017-02-28T09:29:15.317674Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 14890153984
2017-02-28T09:29:15.839489Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 14895396864
2017-02-28T09:29:16.147646Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 14898570815
2017-02-28T09:29:16.150245Z 0 [Note] InnoDB: Database was not shutdown normally!
2017-02-28T09:29:16.150258Z 0 [Note] InnoDB: Starting crash recovery.
2017-02-28T09:29:16.150355Z 0 [Warning] InnoDB: Page 0 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318633]
2017-02-28T09:29:16.150363Z 0 [Warning] InnoDB: Page 1 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318634]
2017-02-28T09:29:16.150369Z 0 [Warning] InnoDB: Page 2 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318635]
2017-02-28T09:29:16.150380Z 0 [Warning] InnoDB: Page 3 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318636]
2017-02-28T09:29:16.150386Z 0 [Warning] InnoDB: Page 4 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318637]
2017-02-28T09:29:16.150392Z 0 [Warning] InnoDB: Page 5 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318638]
2017-02-28T09:29:16.150397Z 0 [Warning] InnoDB: Page 6 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318639]
2017-02-28T09:29:16.150403Z 0 [Warning] InnoDB: Page 7 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318640]
2017-02-28T09:29:16.150409Z 0 [Warning] InnoDB: Page 8 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318641]
2017-02-28T09:29:16.150415Z 0 [Warning] InnoDB: Page 9 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318642]
2017-02-28T09:29:16.150420Z 0 [Warning] InnoDB: Page 10 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318643]
2017-02-28T09:29:16.150426Z 0 [Warning] InnoDB: Page 11 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318644]
2017-02-28T09:29:16.150444Z 0 [Warning] InnoDB: Page 12 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318645]
2017-02-28T09:29:16.150451Z 0 [Warning] InnoDB: Page 13 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318646]
2017-02-28T09:29:16.150456Z 0 [Warning] InnoDB: Page 14 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318647]
2017-02-28T09:29:16.150462Z 0 [Warning] InnoDB: Page 15 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318648]
2017-02-28T09:29:16.150468Z 0 [Warning] InnoDB: Page 16 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318649]
2017-02-28T09:29:16.150474Z 0 [Warning] InnoDB: Page 17 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318650]
2017-02-28T09:29:16.150479Z 0 [Warning] InnoDB: Page 18 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318651]
2017-02-28T09:29:16.150485Z 0 [Warning] InnoDB: Page 19 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318652]
2017-02-28T09:29:16.150491Z 0 [Warning] InnoDB: Page 20 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318653]
2017-02-28T09:29:16.150496Z 0 [Warning] InnoDB: Page 21 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318654]
2017-02-28T09:29:16.150502Z 0 [Warning] InnoDB: Page 22 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318655]
2017-02-28T09:29:16.150565Z 0 [Warning] InnoDB: Page 24 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318720]
2017-02-28T09:29:16.150571Z 0 [Warning] InnoDB: Page 25 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318721]
2017-02-28T09:29:16.150577Z 0 [Warning] InnoDB: Page 26 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318722]
2017-02-28T09:29:16.150583Z 0 [Warning] InnoDB: Page 27 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318723]
2017-02-28T09:29:16.150589Z 0 [Warning] InnoDB: Page 28 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318724]
2017-02-28T09:29:16.150594Z 0 [Warning] InnoDB: Page 29 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318684]
2017-02-28T09:29:16.150600Z 0 [Warning] InnoDB: Page 30 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318685]
2017-02-28T09:29:16.150606Z 0 [Warning] InnoDB: Page 31 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318686]
2017-02-28T09:29:16.150612Z 0 [Warning] InnoDB: Page 32 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318687]
2017-02-28T09:29:16.150617Z 0 [Warning] InnoDB: Page 33 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318688]
2017-02-28T09:29:16.150623Z 0 [Warning] InnoDB: Page 34 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318689]
2017-02-28T09:29:16.150629Z 0 [Warning] InnoDB: Page 35 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318690]
2017-02-28T09:29:16.150635Z 0 [Warning] InnoDB: Page 36 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318691]
2017-02-28T09:29:16.150640Z 0 [Warning] InnoDB: Page 37 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318692]
2017-02-28T09:29:16.150646Z 0 [Warning] InnoDB: Page 38 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318693]
2017-02-28T09:29:16.150656Z 0 [Warning] InnoDB: Page 39 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318694]
2017-02-28T09:29:16.150662Z 0 [Warning] InnoDB: Page 40 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318695]
2017-02-28T09:29:16.150668Z 0 [Warning] InnoDB: Page 41 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318696]
2017-02-28T09:29:16.150673Z 0 [Warning] InnoDB: Page 42 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318697]
2017-02-28T09:29:16.150679Z 0 [Warning] InnoDB: Page 43 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318698]
2017-02-28T09:29:16.150685Z 0 [Warning] InnoDB: Page 44 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318699]
2017-02-28T09:29:16.150691Z 0 [Warning] InnoDB: Page 45 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318700]
2017-02-28T09:29:16.150696Z 0 [Warning] InnoDB: Page 46 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318701]
2017-02-28T09:29:16.150702Z 0 [Warning] InnoDB: Page 47 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318702]
2017-02-28T09:29:16.150708Z 0 [Warning] InnoDB: Page 48 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318703]
2017-02-28T09:29:16.150714Z 0 [Warning] InnoDB: Page 49 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318704]
2017-02-28T09:29:16.150719Z 0 [Warning] InnoDB: Page 50 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318705]
2017-02-28T09:29:16.150725Z 0 [Warning] InnoDB: Page 51 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318706]
2017-02-28T09:29:16.150731Z 0 [Warning] InnoDB: Page 52 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318707]
2017-02-28T09:29:16.150737Z 0 [Warning] InnoDB: Page 53 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318708]
2017-02-28T09:29:16.150742Z 0 [Warning] InnoDB: Page 54 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318709]
2017-02-28T09:29:16.150748Z 0 [Warning] InnoDB: Page 55 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318710]
2017-02-28T09:29:16.150754Z 0 [Warning] InnoDB: Page 56 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318711]
2017-02-28T09:29:16.150760Z 0 [Warning] InnoDB: Page 57 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318712]
2017-02-28T09:29:16.150765Z 0 [Warning] InnoDB: Page 58 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318713]
2017-02-28T09:29:16.150771Z 0 [Warning] InnoDB: Page 59 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318714]
2017-02-28T09:29:16.150777Z 0 [Warning] InnoDB: Page 60 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318715]
2017-02-28T09:29:16.150783Z 0 [Warning] InnoDB: Page 61 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318716]
2017-02-28T09:29:16.150788Z 0 [Warning] InnoDB: Page 62 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318717]
2017-02-28T09:29:16.150794Z 0 [Warning] InnoDB: Page 63 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318718]
2017-02-28T09:29:16.150800Z 0 [Warning] InnoDB: Page 64 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318719]
2017-02-28T09:29:16.150809Z 0 [Warning] InnoDB: Page 65 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318623]
2017-02-28T09:29:16.150815Z 0 [Warning] InnoDB: Page 66 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318624]
2017-02-28T09:29:16.150821Z 0 [Warning] InnoDB: Page 67 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318625]
2017-02-28T09:29:16.150826Z 0 [Warning] InnoDB: Page 68 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318626]
2017-02-28T09:29:16.150832Z 0 [Warning] InnoDB: Page 69 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318627]
2017-02-28T09:29:16.150838Z 0 [Warning] InnoDB: Page 70 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318628]
2017-02-28T09:29:16.150843Z 0 [Warning] InnoDB: Page 71 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318629]
2017-02-28T09:29:16.150853Z 0 [Warning] InnoDB: Page 72 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318630]
2017-02-28T09:29:16.150859Z 0 [Warning] InnoDB: Page 73 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318631]
2017-02-28T09:29:16.150865Z 0 [Warning] InnoDB: Page 74 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318632]
2017-02-28T09:29:16.150871Z 0 [Warning] InnoDB: Page 75 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318149]
2017-02-28T09:29:16.150876Z 0 [Warning] InnoDB: Page 76 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318150]
2017-02-28T09:29:16.150882Z 0 [Warning] InnoDB: Page 77 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318151]
2017-02-28T09:29:16.150888Z 0 [Warning] InnoDB: Page 78 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=318152]
2017-02-28T09:29:16.150894Z 0 [Warning] InnoDB: Page 79 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=234050]
2017-02-28T09:29:16.150900Z 0 [Warning] InnoDB: Page 80 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=234051]
2017-02-28T09:29:16.150906Z 0 [Warning] InnoDB: Page 81 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=234052]
2017-02-28T09:29:16.150911Z 0 [Warning] InnoDB: Page 82 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=234053]
2017-02-28T09:29:16.150917Z 0 [Warning] InnoDB: Page 83 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=234054]
2017-02-28T09:29:16.150923Z 0 [Warning] InnoDB: Page 84 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=317530]
2017-02-28T09:29:16.150929Z 0 [Warning] InnoDB: Page 85 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=317503]
2017-02-28T09:29:16.151243Z 0 [Warning] InnoDB: Page 92 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=315793]
2017-02-28T09:29:16.151252Z 0 [Warning] InnoDB: Page 93 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=315794]
2017-02-28T09:29:16.151258Z 0 [Warning] InnoDB: Page 94 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=315795]
2017-02-28T09:29:16.151264Z 0 [Warning] InnoDB: Page 95 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=263390]
2017-02-28T09:29:16.151270Z 0 [Warning] InnoDB: Page 96 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=262144]
2017-02-28T09:29:16.151281Z 0 [Warning] InnoDB: Page 97 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=285943]
2017-02-28T09:29:16.151288Z 0 [Warning] InnoDB: Page 98 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=285944]
2017-02-28T09:29:16.151293Z 0 [Warning] InnoDB: Page 99 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=285945]
2017-02-28T09:29:16.151299Z 0 [Warning] InnoDB: Page 100 in the doublewrite buffer is not within space bounds: page [page id: space=1201, page number=285946]
2017-02-28T09:29:16.205193Z 0 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 2017-02-28T09:29:16.205262Z 0 [ERROR] InnoDB: Trying to access page number 234400 in space 1201, space name insert_test/tbl0, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.
2017-02-28T09:29:16.205270Z 0 [ERROR] InnoDB: Server exits.
  • you can start with adding innodb_force_recovery tag in my.ini/my.cnf(MySQL Configuration) file and setting its values starting from 1 to 6 based on severity. This will force start the MySQL service. you can refer dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html Before you start this I recommend to copy whole data folder to some safe place. – Shashank Tiwari Mar 6 '17 at 9:49
  • One more quick question did you remove ib log files from the data/default directory? – Shashank Tiwari Mar 6 '17 at 9:52
  • I didn't remove any file from data directory. – edwvee Mar 6 '17 at 10:35
  • I tried innodb_force_recovery from 4 to 6 - no result. And even can't remember any significant change in log with this option. – edwvee Mar 6 '17 at 10:36
1

The error message at the end of the logs you posted, points to a possible mismatch of ibdata file name mismatch.

Have a look at the old data files that you have (/var/lib/mysql is the default directory unless you changed it). Check name of ibdata file (it's usually called ibdata1). Match this name with what's in /etc/mysql/my.cnf or /etc/my.cnf. Start MySQL and see if it comes up.

If this doesn't work:

  • copy old data files to a directory of your choice
  • change owner & group of this folder and all sub-folders and files to mysql (chown -R mysql:mysql [your new directory])
  • edit your my.cnf and set datadir to the new directory where you copied the MySQL data files)
  • Start MySQL server and see what you get in the mysqld.log file

  • If you get MySQL running, run mysql_upgrade (https://dev.mysql.com/doc/refman/5.7/en/mysql-upgrade.html) to upgrade old data to MySQL 5.7 if your old data was from another version of MySQL.

  • Have searched through /etc/mysql for "ibdata" - nothing found. Can add this folder here if it can help. – edwvee Mar 6 '17 at 13:13
  • 1
    @edwvee ibdata file is usually in /var/lib/mysql/ – thatsaru Mar 6 '17 at 14:09
  • I meant that there are no settings pointing on any ibdata file. – edwvee Mar 7 '17 at 9:38
  • @edwvee what is size of your old/original ibdata1 file and what's the size of the same file in /var/lib/mysql directory? – thatsaru Mar 7 '17 at 10:23
  • In folder with old files (which I currently use to run mysqld) ibdata1 is 146800640 bytes. In folder that came with installation (and with which mysqld is able to start) ibdata1 is 12582912 bytes (little strange: i didn't add any database in fresh installation files). – edwvee Mar 7 '17 at 11:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.