mysql --innodb_file_per_table error

Discussion in 'Server Operation' started by ptroms, Dec 17, 2020.

  1. ptroms

    ptroms Member

    I am not sure when the error started, but recently i have got a problem with additional db added into the system independently (not via ispconfig) and I just realize that mysql server is not actin as it should.
    When i try to access it as a root form command line
    I have got this:
    Code:
    mysql: unknown option '--innodb_file_per_table'
    
    server status:
    Code:
    â mariadb.service - MariaDB 10.3.27 database server
       Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
       Active: active (running) since Thu 2020-12-17 08:46:52 GMT; 57min ago
         Docs: man:mysqld(8)
               https://mariadb.com/kb/en/library/systemd/
      Process: 29439 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
      Process: 29440 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
      Process: 29441 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && sys
      Process: 29520 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
      Process: 29521 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
     Main PID: 29489 (mysqld)
       Status: "Taking your SQL requests now..."
        Tasks: 44 (limit: 4915)
       Memory: 110.9M
       CGroup: /system.slice/mariadb.service
               ââ29489 /usr/sbin/mysqld
    
    Dec 17 08:46:52 aspuk systemd[1]: Starting MariaDB 10.3.27 database server...
    Dec 17 08:46:52 aspuk mysqld[29489]: 2020-12-17  8:46:52 0 [Note] /usr/sbin/mysqld (mysqld 10.3.27-MariaDB-0+deb10u1) starting as process 29489 ...
    Dec 17 08:46:52 aspuk mysqld[29489]: 2020-12-17  8:46:52 0 [Warning] Could not increase number of max_open_files to more than 16384 (request: 32186)
    Dec 17 08:46:52 aspuk systemd[1]: Started MariaDB 10.3.27 database server.
    Dec 17 08:46:52 aspuk /etc/mysql/debian-start[29523]: Upgrading MySQL tables if necessary.
    How is the obvious question. How to deal with it.
     
    Last edited: Dec 17, 2020
  2. Jesse Norell

    Jesse Norell ISPConfig Developer Staff Member ISPConfig Developer

    Search for a innodb_file_per_table setting in your mysql config files and comment it out, or move it from the mysql section to the mysqld section.
     
  3. ptroms

    ptroms Member

    Great It works. Thank you
    I knew what kind of db trigger the error so I have remove it.
    when I tried to made a fresh copy form the original source by running a command:
    I have got:
    Code:
    mysql: unknown option '--innodb_file_per_table'
    
    I am afraid in square one. :)
    Shall I disable "innodb_file_per_table" in my.cnf for good.

    I am sure there is a tiny conf somewhere.
     
    Last edited: Dec 17, 2020
  4. ptroms

    ptroms Member

    I have check the databases they are healthy, and delete unnecessary users and databases.
    But mariadb still have some issues.
    error.log
    Code:
    2020-12-17 15:52:43 0 [Note] InnoDB: Using Linux native AIO
    2020-12-17 15:52:43 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    2020-12-17 15:52:43 0 [Note] InnoDB: Uses event mutexes
    2020-12-17 15:52:43 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
    2020-12-17 15:52:43 0 [Note] InnoDB: Number of pools: 1
    2020-12-17 15:52:43 0 [Note] InnoDB: Using SSE2 crc32 instructions
    2020-12-17 15:52:43 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
    2020-12-17 15:52:43 0 [Note] InnoDB: Completed initialization of buffer pool
    2020-12-17 15:52:43 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
    2020-12-17 15:52:43 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
    2020-12-17 15:52:43 0 [Note] InnoDB: Creating shared tablespace for temporary tables
    2020-12-17 15:52:43 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
    2020-12-17 15:52:43 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
    2020-12-17 15:52:43 0 [Note] InnoDB: 10.3.27 started; log sequence number 8443056180; transaction id 12628048
    2020-12-17 15:52:43 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
    2020-12-17 15:52:43 0 [Note] Plugin 'FEEDBACK' is disabled.
    2020-12-17 15:52:43 0 [Note] Server socket created on IP: '127.0.0.1'.
    2020-12-17 15:52:43 0 [Note] Reading of all Master_info entries succeeded
    2020-12-17 15:52:43 0 [Note] Added new Master_info '' to hash table
    2020-12-17 15:52:43 0 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '10.3.27-MariaDB-0+deb10u1'  socket: '/run/mysqld/mysqld.sock'  port: 3306  Debian 10
    2020-12-17 15:52:43 0 [Note] InnoDB: Buffer pool(s) load completed at 201217 15:52:43
    
    When "innodb_file_per_table" is enable in the mysql.cnf no access form the command line.
    Disable does work but the the errors.
     
    Last edited: Dec 17, 2020
  5. Jesse Norell

    Jesse Norell ISPConfig Developer Staff Member ISPConfig Developer

    Does your example.sql include a command to set the innodb_file_per_table option?
     
  6. ptroms

    ptroms Member

    It says only:
    BUT ...
    Some other support buys are saying that my.cnf fiel should be
    Code:
    innodb_file_per_table = 1
    innodb_file_format = barracuda
    Which I am willing to test.
    Will that affect ispconfig?
     
    Last edited: Dec 17, 2020
  7. Jesse Norell

    Jesse Norell ISPConfig Developer Staff Member ISPConfig Developer

    I don't know; I would suspect not, but not familiar with it.
     
  8. ptroms

    ptroms Member

     
  9. ptroms

    ptroms Member

    Chmm.
    It seems like the database that I tried to import the other day corrupt mysql.
    No I have three major error directly from mariadb.
    command:
    comes up with:
    Code:
    ...
    Error    : Table 'mysql.innodb_index_stats' doesn't exist in engine
    status   : Operation failed
    mysql.innodb_table_stats
    Error    : Table 'mysql.innodb_table_stats' doesn't exist in engine
    status   : Operation failed
    ...
    ...
    Error    : Table 'mysql.transaction_registry' doesn't exist in engine
    status   : Operation failed
    ...
    
    On: Ver 15.1 Distrib 10.3.27-MariaDB
     

Share This Page