Want to subscribe to topics you're interested in?
Become a Member

MariaDB Error importing sql database: Row size too large (> 8126)

Discussion in 'Install & Upgrades or Pre-Install Questions' started by 1121, Nov 29, 2018.

  1. 1121

    1121 New Member

    13
    1
    3
    Nov 29, 2018
    Ratings:
    +2
    Local Time:
    6:09 PM
    1.15.7
    MariaDB 10.1.37
    It's a clean database with no data yet, correct, so I assume that's why it shows 0 rows.
    I just tried to backup and import the database with the previous Centminmod my.cnf set to
    innodb_log_file_size = 2G and I am back to the same ERROR 1118 (42000). Used commands from docs here:
    backup

    Code (Text):
    mysqldump -u mysqlusername -p mysqldatabasename > /path/to/mysqldatabasename_backup_date.sql

    restore

    Code (Text):
    mysql -u mysqlusername -p mysqldatabasename < /path/to/mysqldatabasename_backup_date.sql
     
  2. eva2000

    eva2000 Administrator Staff Member

    37,261
    8,140
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,532
    Local Time:
    9:09 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
  3. 1121

    1121 New Member

    13
    1
    3
    Nov 29, 2018
    Ratings:
    +2
    Local Time:
    6:09 PM
    1.15.7
    MariaDB 10.1.37
    Added as follows:

    Code (Text):
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

    right under
    Code (Text):
    socket=/var/lib/mysql/mysql.sock


    OUTPUT
    Code (Text):
    mysqladmin var | egrep 'default_storage_engine|sql_mode' | tr -s ' '
    | default_storage_engine | InnoDB |
    | sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
    


    still the same error on import.
     
  4. eva2000

    eva2000 Administrator Staff Member

    37,261
    8,140
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,532
    Local Time:
    9:09 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    can you share the mysql database's entire table structure via this command to dump only the database structure with no data
    Code (Text):
    mysqldump --no-data databasename > databasename-schema-only.sql
    

    then post the contents of databasename-schema-only.sql to pastebin.com or gist.github.com and share the link...

    it is still a limit for innodb though not sure why the other my.cnf works MySQL :: MySQL Restrictions and Limitations :: 8.4 Limits on Table Column Count and Row Size
     
    • Like Like x 1
  5. eva2000

    eva2000 Administrator Staff Member

    37,261
    8,140
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,532
    Local Time:
    9:09 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    Oh I think I found the the option that is causing problems in Centmin Mod 123.09beta01 MariaDB 10 default /etc/my.cnf it's related to
    Code (Text):
    innodb_default_row_format = dynamic
    

    from MySQL :: MySQL Restrictions and Limitations :: 8.4 Limits on Table Column Count and Row Size
    So difference is warning and succeed (innodb_default_row_format compact) versus error and fail (innodb_default_row_format dynamic). Both instances though still mean the table(s) have hit InnoDB column limits - so should be something FunnelFlux needs to fix.

    Centmin Mod changed the defaults from compact to dynamic for innodb_default_row_format to workaround fix large prefix issues MariaDB - Index column size too large. The maximum column size is 767 bytes. see specifically here.
    But if you database is not optimally designed as in case of FunnelFlux database table, then it has run up against InnoDB's column limit so you'd be in catch 22 potentially running up against index key prefix length limit or run into innodb column limit. Ideal solution is FunnelFlux restructuring their database table to adhere to InnoDB column limits.

    But for your situation, you can for now change /etc/my.cnf

    from
    Code (Text):
    innodb_default_row_format = dynamic
    

    to
    Code (Text):
    innodb_default_row_format = compact
    

    then restart mariadb mysql
    Code (Text):
    mysqlrestart
    
     
    Last edited: Dec 4, 2018
    • Like Like x 1
  6. 1121

    1121 New Member

    13
    1
    3
    Nov 29, 2018
    Ratings:
    +2
    Local Time:
    6:09 PM
    1.15.7
    MariaDB 10.1.37
    Thanks, George. This has not removed the error.
    I have spent almost a week trying to get it working and now it is finally working. At this point if it works, it works, and maybe later I can do line by line add/remove to see which settings break it.
    I put back the original centminmod my.cnf and Funnelflux seems to be working ok. Maybe when Funnelflux updates are issued I can temporarily switch back to the funnelflux my.cnf, but in the meantime I want to make sure the my.cnf is as optimized as possible with centminmod version. Thanks so much for your time and effort troubleshooting with me!
     
    • Like Like x 1
..