#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    103

    Crash of MySQL databas after import of data


    Background: We are using Solaris 10 on Sun Zones, and I stood up a MySQL 5.6.12 Advanced server edition database. Now currently we are using MySQL 5.0.51a Community edition, also on Solaris 10 Sun Zones. So the only thing that is new is the MySQL 5.6.12 database.

    So I brought the new database up on a new configured Zone and it worked just fine. I even used Mysqldump to imported the DDL for (12) different company based parts (I would refer to them as Schemas in the Oracle DB world, but MySQL refers to them as Databases). Now I should say that my company is not doing or using anything complicated at all in MySQL (pretty simple in structures). Tables, Indexes, Views...we are NOT using Partitioning, MYSQL DB replication, or they using any of the other less popular storage engines types. Actually most of our tables are using INNODB with a few still using MyISAM (we use no other storage engine type).

    So I had all of these "schemas" within the database and all was fine, we even stopped the database a number of times and even bounced the Zone a couple of times too. The database always came up with no issues. We also installed and configured MySQL Monitor 2,13 and started testing and watching all using it.

    Now I had heard that there were issues with Migrating from a 5.0 to a 5,1 and the from a 5.1 to 5.5 MySQL Database, but since we are using a new 5.6.12 MySQL database, and recreated all the objects for the different schemas, we were just importing the data back into the database.

    So using Mysqldump and trying to import the data caused the MySQL database to crash with the following error:

    Version: '5.6.12-enterprise-commercial-advanced' socket: '/tmp/mysql.sock' port: 3306 MySQL Enterprise Server - Advanced Edition (Commercial)
    2013-07-31 10:23:32 445 [Note] /opt/mysql/mysql/bin/mysqld: Normal shutdown

    2013-07-31 10:23:32 445 [Note] Giving 1 client threads a chance to die gracefully
    2013-07-31 10:23:32 445 [Note] Event Scheduler: Purging the queue. 0 events
    2013-07-31 10:23:32 445 [Note] Shutting down slave threads
    2013-07-31 10:23:34 445 [Note] Forcefully disconnecting 1 remaining clients
    2013-07-31 10:23:34 445 [Warning] /opt/mysql/mysql/bin/mysqld: Forcing close of thread 1 user: 'qedadm_db'

    2013-07-31 10:23:34 445 [Note] Binlog end
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'partition'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'ARCHIVE'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'BLACKHOLE'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_FT_DELETED'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_METRICS'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_CMPMEM'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_CMP_RESET'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_CMP'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_LOCKS'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'INNODB_TRX'
    2013-07-31 10:23:34 445 [Note] Shutting down plugin 'InnoDB'
    2013-07-31 10:23:34 445 [Note] InnoDB: FTS optimize thread exiting.
    2013-07-31 10:23:34 445 [Note] InnoDB: Starting shutdown...
    2013-07-31 10:23:35 445 [Note] InnoDB: Shutdown completed; log sequence number 17369991
    2013-07-31 10:23:35 445 [Note] Shutting down plugin 'MRG_MYISAM'
    2013-07-31 10:23:35 445 [Note] Shutting down plugin 'CSV'
    2013-07-31 10:23:35 445 [Note] Shutting down plugin 'MyISAM'
    2013-07-31 10:23:35 445 [Note] Shutting down plugin 'MEMORY'
    2013-07-31 10:23:35 445 [Note] Shutting down plugin 'sha256_password'
    2013-07-31 10:23:35 445 [Note] Shutting down plugin 'mysql_old_password'
    2013-07-31 10:23:35 445 [Note] Shutting down plugin 'mysql_native_password'
    2013-07-31 10:23:35 445 [Note] Shutting down plugin 'binlog'
    2013-07-31 10:23:35 445 [Note] /opt/mysql/mysql/bin/mysqld: Shutdown complete

    130731 10:23:35 mysqld_safe mysqld from pid file /var/lib/mysql/panama.pid ended
    130731 10:24:36 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    2013-07-31 10:24:37 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
    2013-07-31 10:24:37 5370 [Note] Plugin 'FEDERATED' is disabled.
    2013-07-31 10:24:37 5370 [Note] InnoDB: The InnoDB memory heap is disabled
    2013-07-31 10:24:37 5370 [Note] InnoDB: Mutexes and rw_locks use Solaris atomic functions
    2013-07-31 10:24:37 5370 [Note] InnoDB: Compressed tables use zlib 1.2.3
    2013-07-31 10:24:37 5370 [Note] InnoDB: Not using CPU crc32 instructions
    2013-07-31 10:24:37 5370 [Note] InnoDB: Initializing buffer pool, size = 128.0M
    2013-07-31 10:24:37 5370 [Note] InnoDB: Completed initialization of buffer pool
    2013-07-31 10:24:37 5370 [Note] InnoDB: Highest supported file format is Barracuda.
    2013-07-31 10:24:37 5370 [Note] InnoDB: 128 rollback segment(s) are active.
    2013-07-31 10:24:37 5370 [Note] InnoDB: Waiting for purge to start
    2013-07-31 10:24:37 5370 [Note] InnoDB: 5.6.12 started; log sequence number 17369991
    2013-07-31 10:24:37 5370 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
    2013-07-31 10:24:37 5370 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
    2013-07-31 10:24:37 5370 [Note] Server hostname (bind-address): '*'; port: 3306
    2013-07-31 10:24:37 5370 [Note] IPv6 is available.
    2013-07-31 10:24:37 5370 [Note] - '::' resolves to '::';
    2013-07-31 10:24:37 5370 [Note] Server socket created on IP: '::'.
    2013-07-31 10:24:37 5370 [Note] Event Scheduler: Loaded 0 events
    2013-07-31 10:24:37 5370 [Note] /opt/mysql/mysql/bin/mysqld: ready for connections.
    Version: '5.6.12-enterprise-commercial-advanced' socket: '/tmp/mysql.sock' port: 3306 MySQL Enterprise Server - Advanced Edition (Commercial)
    17:20:27 UTC - mysqld got signal 11 ;
    This could be because you hit a bug. It is also possible that this binary
    or one of the libraries it was linked against is corrupt, improperly built,
    or misconfigured. This error can also be caused by malfunctioning hardware.
    We will try our best to scrape up some info that will hopefully help
    diagnose the problem, but since we have already crashed,
    something is definitely wrong and this may fail.

    key_buffer_size=8388608
    read_buffer_size=131072
    max_used_connections=4
    max_threads=151
    thread_count=3
    connection_count=3
    It is possible that mysqld could use up to
    key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68182 K bytes of memory
    Hope that's ok; if not, decrease some variables in the equation.

    Thread pointer: 0x101a9c990
    Attempting backtrace. You can use the following information to find out
    where mysqld died. If you see no messages after this, something went
    terribly wrong...
    /opt/mysql/mysql/bin/mysqld:my_print_stacktrace+0x2c
    /opt/mysql/mysql/bin/mysqld:handle_fatal_signal+0x32c
    /lib/sparcv9/libc.so.1:0xd8684
    /lib/sparcv9/libc.so.1:0xcc1f8
    /lib/sparcv9/libc.so.1:0xcc404
    /opt/mysql/mysql/bin/mysqld:0x42d7cc [ Signal 69819032 (?)]
    /opt/mysql/mysql/bin/mysqld:__1cLmysql_grant6FpnDTHD_pkcrnEList4nLst_lex_user___Lbb_b_+0x694
    /opt/mysql/mysql/bin/mysqld:__1cVmysql_execute_command6FpnDTHD__i_+0x408c
    /opt/mysql/mysql/bin/mysqld:__1cLmysql_parse6FpnDTHD_pcIpnMParser_state__v_+0x384
    /opt/mysql/mysql/bin/mysqld:__1cQdispatch_command6FnTenum_server_command_pnDTHD_pcI_b_+0xa40
    /opt/mysql/mysql/bin/mysqld:__1cKdo_command6FpnDTHD__b_+0x18c
    /opt/mysql/mysql/bin/mysqld:__1cYdo_handle_one_connection6FpnDTHD__v_+0x1c8
    /opt/mysql/mysql/bin/mysqld:handle_one_connection+0x54
    /opt/mysql/mysql/bin/mysqldfs_spawn_thread+0xc0
    /lib/sparcv9/libc.so.1:0xd8558
    Please read http://dev.mysql.com/doc/refman/5.1/en/resolve-stack-dump.html
    and follow instructions on how to resolve the stack trace.
    Resolved stack trace is much more helpful in diagnosing the
    problem, so please do resolve it

    Trying to get some variables.
    Some pointers may be invalid and cause the dump to abort.
    Query (122ce1c18): grant all on *.* to qedadm_db@'aruba' identified by "Op123xR5" with Grant option
    Connection ID (thread ID): 31
    Status: NOT_KILLED


    ******So after it finished we found that via MySQL Monitor 2.13 the server was green and not up, we tried to start it and it failed to start. the system at the OS level still showed that the process was up but you could not log into it. So we bounced the whole server and brought up everything, and still the MySQL database would crash and not let one log in.

    getting the following error:

    ~$ mysql -u root -p
    Enter password:
    ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (146)


    ****** So why would importing data cause this?

    Any advice here? I have tried to look up this via the net and it would seem to point to either of these two bugs #48726 and 61351 or a permissions issue with /tmp being able to write to /tmp/MySQL.sock which is blown open to the wold so that shouldn't be a problem, and as I have said this all worked before I attempted to import data into these schemas.
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    103
    Ok so what if I were to roll back a snapshot I have of the Zone just prior to the change of adding the data,, and were to use mysqldump and export just the RAW data and then import the data...would that work then.

    I am thinking that since there is a know bug from going to 5.0 to 5.1 and 5.1 to 5.5 and continues in 5.6 maybe taking the RAW data might work.

    thoughts anyone?
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    103
    Ok so what I will try is to rollback the snapshot to before I did the import of only data. I will do a mysqldump of just one schema as follows:

    mysqldump -uroot -p (pwd) --no-create-db --no-create-info mydb_schema > mydb_schema.sql

    then SFTP it from the one server to the new one

    then import it into the new database that already has the DDL for that schema already installed in the database.

    MySQL -uroot -p (pwd) mydb_schema < mydb_schema.sql

    I will see if that crashes the database...then I will work through the 12 schemas on at a time
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    103
    Ok I figured it out, when we did the mysqldump we did all the schemas which means the MYSQL schema which is like the SYSTEM schema in Oracle was over written and in the MySQL 5.6.12 database was confused...the my.cnf thought it was a 5.6 and the structures and internals from the import were from a 5.0.51a MySQL database....thus it was in a confused state.

    I rolled back to a snapshot taken prior to the import and the database is backup and working correctly. I then did a mysqldump of just one schema and imported the raw data (the schema objects are already in the database) and it works.

    So it would appear the work around might be that we stand up a new 5.6.12 MySQL database and then recreate the individual schema and import the Raw data each one individually.


IMN logo majestic logo threadwatch logo seochat tools logo