Writing to server on NYC3

May 1, 2019 422 views
Ubuntu 16.04 Drupal

Our site is having issues with writing to the server and to the db

3 comments
  • @support350187

    Please you need to share more information with us in order to help you.

    What db are you using? Is the disk full? Do you use instance or block storage?

    Once you share more info we can help.

  • It’s a drupal site with a CiviCRM integration. MySQL db. The disk is not full. Here are the error logs:

    2019-05-01 09:31:16 0x7fd98023e700  InnoDB: Assertion failure in thread 140572134467328 in file row0mysql.cc line 4536
    InnoDB: Failing assertion: table->n_rec_locks == 0
    InnoDB: We intentionally generate a memory trap.
    InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
    InnoDB: If you get repeated assertion failures or crashes, even
    InnoDB: immediately after the mysqld startup, there may be
    InnoDB: corruption in the InnoDB tablespace. Please refer to
    InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
    InnoDB: about forcing recovery.
    09:31:16 UTC - mysqld got signal 6 ;
    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.
    Attempting to collect some information that could help diagnose the problem.
    As this is a crash and something is definitely wrong, the information
    collection process might fail.
    
    key_buffer_size=16777216
    read_buffer_size=131072
    max_used_connections=12
    max_threads=214
    thread_count=2
    connection_count=2
    It is possible that mysqld could use up to 
    key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 101423 K  bytes of memory
    Hope that's ok; if not, decrease some variables in the equation.
    
    Thread pointer: 0x7fd8e008d8a0
    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...
    stack_bottom = 7fd98023de70 thread_stack 0x30000
    /usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xe9510b]
    /usr/sbin/mysqld(handle_fatal_signal+0x489)[0x78ad29]
    /lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7fe1ee232390]
    /lib/x86_64-linux-gnu/libc.so.6(gsignal+0x38)[0x7fe1ed5eb428]
    /lib/x86_64-linux-gnu/libc.so.6(gsignal+0x38)[0x7fe1ed5eb428]
    /lib/x86_64-linux-gnu/libc.so.6(abort+0x16a)[0x7fe1ed5ed02a]
    /usr/sbin/mysqld[0x7605dc]
    /usr/sbin/mysqld(_Z24row_drop_table_for_mysqlPKcP5trx_tbbP12dict_table_t+0x111a)[0xfc5d5a]
    /usr/sbin/mysqld(_ZN11ha_innobase12delete_tableEPKc+0x3bb)[0xedc16b]
    /usr/sbin/mysqld(_Z18rm_temporary_tableP10handlertonPKc+0xe1)[0xbf7aa1]
    /usr/sbin/mysqld(_Z15close_temporaryP5TABLEbb+0x82)[0xbf7b92]
    /usr/sbin/mysqld(_Z20drop_temporary_tableP3THDP10TABLE_LISTPb+0x9d)[0xbf7cfd]
    /usr/sbin/mysqld(_Z23mysql_rm_table_no_locksP3THDP10TABLE_LISTbbbb+0x2ba)[0xcc2d9a]
    /usr/sbin/mysqld(_Z14mysql_rm_tableP3THDP10TABLE_LISTcc+0x260)[0xcc43f0]
    /usr/sbin/mysqld(_Z21mysql_execute_commandP3THDb+0x28d7)[0xc50867]
    /usr/sbin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x3ad)[0xc54d0d]
    /usr/sbin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0x102a)[0xc55e4a]
    /usr/sbin/mysqld(_Z10do_commandP3THD+0x1c7)[0xc57307]
    /usr/sbin/mysqld(handle_connection+0x290)[0xd19880]
    /usr/sbin/mysqld(pfs_spawn_thread+0x1b4)[0x11b4be4]
    /lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7fe1ee2286ba]
    /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fe1ed6bd41d]
    
    Trying to get some variables.
    Some pointers may be invalid and cause the dump to abort.
    Query (7fd8e009a6a0): is an invalid pointer
    Connection ID (thread ID): 17043
    Status: NOT_KILLED
    
    The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
    information that should help you find out what is causing the crash.
    2019-05-01T09:31:17.441758Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
    2019-05-01T09:31:17.441819Z 0 [Warning] Changed limits: max_connections: 214 (requested 600)
    2019-05-01T09:31:17.441824Z 0 [Warning] Changed limits: table_open_cache: 400 (requested 2000)
    2019-05-01T09:31:17.618392Z 0 [Warning] option 'max_allowed_packet': unsigned value 104857600000 adjusted to 1073741824
    2019-05-01T09:31:17.618450Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
    2019-05-01T09:31:17.620726Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.26-0ubuntu0.16.04.1) starting as process 26973 ...
    2019-05-01T09:31:17.626689Z 0 [Note] InnoDB: PUNCH HOLE support available
    2019-05-01T09:31:17.626712Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    2019-05-01T09:31:17.626723Z 0 [Note] InnoDB: Uses event mutexes
    2019-05-01T09:31:17.626730Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
    2019-05-01T09:31:17.626736Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
    2019-05-01T09:31:17.626752Z 0 [Note] InnoDB: Using Linux native AIO
    2019-05-01T09:31:17.627136Z 0 [Note] InnoDB: Number of pools: 1
    2019-05-01T09:31:17.627286Z 0 [Note] InnoDB: Using CPU crc32 instructions
    2019-05-01T09:31:17.629588Z 0 [Note] InnoDB: Initializing buffer pool, total size = 32G, instances = 8, chunk size = 128M
    2019-05-01T09:31:20.452548Z 0 [Note] InnoDB: Completed initialization of buffer pool
    2019-05-01T09:31:21.019893Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
    2019-05-01T09:31:21.032387Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
    2019-05-01T09:31:21.470986Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 2525015194905
    2019-05-01T09:31:21.491046Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2525016775564
    2019-05-01T09:31:21.491603Z 0 [Note] InnoDB: Database was not shutdown normally!
    2019-05-01T09:31:21.491628Z 0 [Note] InnoDB: Starting crash recovery.
    2019-05-01T09:31:21.604529Z 0 [Note] InnoDB: 1 transaction(s) which must be rolled back or cleaned up in total 3 row operations to undo
    2019-05-01T09:31:21.604565Z 0 [Note] InnoDB: Trx id counter is 1485434368
    2019-05-01T09:31:21.627951Z 0 [Note] InnoDB: Starting an apply batch of log records to the database...
    InnoDB: Progress in percent: 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
    2019-05-01T09:31:24.254228Z 0 [Note] InnoDB: Apply batch completed
    2019-05-01T09:31:25.119176Z 0 [Note] InnoDB: Starting in background the rollback of uncommitted transactions
    2019-05-01T09:31:25.119255Z 0 [Note] InnoDB: Rolling back trx with id 1485433997, 3 rows to undo
    2019-05-01T09:31:25.122520Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
    2019-05-01T09:31:25.122538Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
    2019-05-01T09:31:25.122602Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
    2019-05-01T09:31:25.122953Z 0 [Note] InnoDB: Rollback of trx with id 1485433997 completed
    2019-05-01T09:31:25.122975Z 0 [Note] InnoDB: Rollback of non-prepared transactions completed
    2019-05-01T09:31:25.144212Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
    2019-05-01T09:31:25.145457Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
    2019-05-01T09:31:25.145473Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
    2019-05-01T09:31:25.146205Z 0 [Note] InnoDB: 5.7.26 started; log sequence number 2525016775564
    2019-05-01T09:31:25.146211Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4127ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
    2019-05-01T09:31:25.146668Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
    2019-05-01T09:31:25.146800Z 0 [Note] Plugin 'FEDERATED' is disabled.
    2019-05-01T09:31:25.154755Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
    2019-05-01T09:31:25.154793Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
    2019-05-01T09:31:25.154816Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
    2019-05-01T09:31:25.154871Z 0 [Note] Server socket created on IP: '127.0.0.1'.
    2019-05-01T09:31:25.167106Z 0 [Note] Event Scheduler: Loaded 0 events
    2019-05-01T09:31:25.167356Z 0 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.7.26-0ubuntu0.16.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
    
  • my.cnf

    #
    # The MySQL database server configuration file.
    #
    # You can copy this to one of:
    # - "/etc/mysql/my.cnf" to set global options,
    # - "~/.my.cnf" to set user-specific options.
    #
    # One can use all long options that the program supports.
    # Run program with --help to get a list of available options and with
    # --print-defaults to see which it would actually understand and use.
    #
    # For explanations see
    # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
    
    # This will be passed to all mysql clients
    # It has been reported that passwords should be enclosed with ticks/quotes
    # escpecially if they contain "#" chars...
    # Remember to edit /etc/mysql/debian.cnf when changing the socket location.
    
    # Here is entries for some specific programs
    # The following values assume you have at least 32M ram
    
    [mysqld_safe]
    socket          = /var/run/mysqld/mysqld.sock
    nice            = 0
    
    [mysqld]
    #
    # * Basic Settings
    #
    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
    skip-external-locking
    #
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    bind-address            = 127.0.0.1
    #
    # * Fine Tuning
    #
    key_buffer_size         = 16M
    max_allowed_packet      = 100000M
    thread_stack            = 192K
    thread_cache_size       = 8
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    myisam-recover-options  = BACKUP
    
    max_connections        = 200
    sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    
    #table_cache            = 64
    #thread_concurrency     = 10
    #
    # * Query Cache Configuration
    #
    query_cache_limit       = 1M
    query_cache_size        = 16M
    #
    # * Logging and Replication
    #
    # Both location gets rotated by the cronjob.
    # Be aware that this log type is a performance killer.
    # As of 5.1 you can enable the log at runtime!
    #general_log_file        = /var/log/mysql/mysql.log
    #general_log             = 1
    #
    # Error log - should be very few entries.
    #
    log_error = /var/log/mysql/error.log
    #
    # Here you can see queries with especially long duration
    #log_slow_queries       = /var/log/mysql/mysql-slow.log
    #long_query_time = 10
    #log-queries-not-using-indexes
    #x
    # The following can be used as easy to replay backup logs or for replication.
    # note: if you are setting up a replication slave, see README.Debian about
    #       other settings you may need to change.
    #server-id              = 1
    #log_bin                = /var/log/mysql/mysql-bin.log
    expire_logs_days        = 10
    max_binlog_size        = 100M
    #binlog_do_db           = include_database_name
    #binlog_ignore_db       = include_database_name
    #
    # * InnoDB
    #
    # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
    # Read the manual for more InnoDB related options. There are many!
    #
    # * Security Features
    #
    # Read the manual, too, if you want chroot!
    # chroot = /var/lib/mysql/
    #
    # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
    #
    # ssl-ca=/etc/mysql/cacert.pem
    # ssl-cert=/etc/mysql/server-cert.pem
    # ssl-key=/etc/mysql/server-key.pem
    
    innodb_buffer_pool_size = 32G
    
Be the first one to answer this question.