0

What I'm doing is I'm trying to restore MySQL tables from frm and ibd files. Most of the tables have been restored successfully. I do it like this:

mysqlfrm --diagnostic "C:/<path_to_frm>/DB:Users.frm" > "C:/<path_to_sql>/Users.sql"

source c:/<path_to_sql>/Users.sql;

alter table DB.Users discard tablespace;

Then I copy the ibd file into the MySQL data folder and

alter table DB.Users import tablespace;

When I go to PhpMyAdmin then and click on the imported table I get this error:

#2006 MySQL server has gone away

Then I restart MySQL and when I'm trying to log in to the PhpMyAdmin I get error 200 and simply can't log in until I drop the table I imported.

I tried changing max_allowed_packet, wait_timeout, interactive_timeout in the [mysqld] section of my.inifile but it did not help.

I'm very confused and would appreciate any help.

Here's the error log:

2021-06-08 16:26:21 8256 [Note] Plugin 'FEDERATED' is disabled.

2021-06-08 16:26:21 8256 [Note] InnoDB: Using atomics to ref count buffer pool pages

2021-06-08 16:26:21 8256 [Note] InnoDB: The InnoDB memory heap is disabled

2021-06-08 16:26:21 8256 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions

2021-06-08 16:26:21 8256 [Note] InnoDB: Memory barrier is not used

2021-06-08 16:26:21 8256 [Note] InnoDB: Compressed tables use zlib 1.2.11

2021-06-08 16:26:21 8256 [Note] InnoDB: Not using CPU crc32 instructions

2021-06-08 16:26:21 8256 [Note] InnoDB: Initializing buffer pool, size = 128.0M

2021-06-08 16:26:21 8256 [Note] InnoDB: Completed initialization of buffer pool

2021-06-08 16:26:21 8256 [Note] InnoDB: Highest supported file format is Barracuda.

2021-06-08 16:26:22 8256 [Note] InnoDB: 128 rollback segment(s) are active.

2021-06-08 16:26:22 8256 [Note] InnoDB: Waiting for purge to start

2021-06-08 16:26:22 8256 [Note] InnoDB: 5.6.46 started; log sequence number 18105434

2021-06-08 16:26:22 8256 [Note] RSA private key file not found: C:\MyData\private_key.pem. Some authentication plugins will not work.

2021-06-08 16:26:22 8256 [Note] RSA public key file not found: C:\MyData\public_key.pem. Some authentication plugins will not work.

2021-06-08 16:26:22 8256 [Note] Server hostname (bind-address): '*'; port: 3306

2021-06-08 16:26:22 8256 [Note] IPv6 is available.

2021-06-08 16:26:22 8256 [Note] - '::' resolves to '::';

2021-06-08 16:26:22 8256 [Note] Server socket created on IP: '::'.

2021-06-08 16:26:22 8256 [Note] Event Scheduler: Loaded 0 events

2021-06-08 16:26:22 8256 [Note] C:\MySQL\bin\mysqld: ready for connections.

Version: '5.6.46' socket: '' port: 3306 MySQL Community Server (GPL)

2021-06-08 16:27:42 8256 [Note] InnoDB: Sync to disk

2021-06-08 16:27:42 8256 [Note] InnoDB: Sync to disk - done!

2021-06-08 16:27:42 8256 [Note] InnoDB: Phase I - Update all pages

2021-06-08 16:27:42 8256 [Note] InnoDB: Sync to disk

2021-06-08 16:27:42 8256 [Note] InnoDB: Sync to disk - done!

2021-06-08 16:27:42 8256 [Note] InnoDB: Phase III - Flush changes to disk

2021-06-08 16:27:42 8256 [Note] InnoDB: Phase IV - Flush complete

Here's the screenshot of what I got in the PhpMyAdmin:

enter image description here

The structure of the table has been restored but when I click the Browse button or click on the table itself the aforementioned error pops up and I can't log in to PhpMyAdmin anymore until I drop the table.

Here's the result of the check: enter image description here

Pastebin link to the full error log.

Improve this question
8
  • 1
    see manual dev.mysql.com/doc/refman/5.6/en/check-table.html
    – nbk
    Jun 8, 2021 at 13:45
  • try an repair "REPAIR TABLE tbl_name"
    – nbk
    Jun 8, 2021 at 13:59
  • 1
    Apparently corruption exists within indexes for table ab.user. Primary and other indexes are named as suspect in your error log. I am not sure how you will correct this trouble, anyone else have a corrective suggestion? How much data is in your tables? innodb_buffer_pool_size is default of 128M and likely is too small to be reasonable. If you could post from your system to pastebin.com TEXT results of A) SHOW GLOBAL VARIABLES; and B) SHOW GLOBAL STATUS; we will probably be able to suggest configuration changes to avoid 'server has gone away'.
    – Wilson Hauck
    Jun 10, 2021 at 13:02
  • 1
    @WilsonHauck Here's the link to what you've asked for: pastebin.com
    – JConstantine
    Jun 11, 2021 at 11:44
  • 1
    A) 16 Gb RAM B) AMD Phenom(tm) II X4 B45 Processor 3.00 GHz C) HDDs only D) Not sure what Appx # tables are. My DB consists of InnoDB tables and one MyISAM table. E) Not sure. The estimated amount of space used by my DB is 100 MBs.
    – JConstantine
    Jun 18, 2021 at 7:28

1 Answer 1

Reset to default
1

Suggestions to consider for your instance my.ini [mysqld] section

interactive_timeout=7200  # from 300 - seconds of idle tolerance
wait_timeout=7200  # from 90 - seconds of idle tolerance

other suggestions you will likely find reasonable for your 16G server

thread_cache_size=100  # from 9 to avoid thread creation overhead, when you get busy
innodb_io_capacity=500  # from 200 unless your HDD is 10 years old
log_warnings=2  # from 1 for error log to include aborted connects
innodb_fast_shutdown=0  # from 1 for clean stop and usually avoid recovery on start.

Many other tuning suggestions would improve performance when you have more significant number of concurrent connections.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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