0

Edit: Apparently a duplicate of this https://stackoverflow.com/questions/15585602/change-limit-for-mysql-row-size-too-large which I discovered when doing an import of only the table that was giving me problems. Mods please mark as duplicate. The remaining errors are fixed by using mysql_upgrade: https://bugs.mageia.org/show_bug.cgi?id=17579

Setting up a new database server running 10.1.17-MariaDB-1~xenial. When I import our existing production database to the new server via the source command I am noticing data missing. I am not getting any errors, just missing data, entire rows from a table. I suspect this is a server setting but I am not sure what setting it could be. These tables are all InnoDB and I constantly pull data down from production to my local without problems.

This particular table is a fat table, with lots of text and longtext columns. I do see a number of errors in the log file, but nothing jumps out.

2016-09-19 22:49:50 7f7f9a7f7700 InnoDB: Error: Column last_update in table "mysql"."innodb_table_stats" is INT UNSIGNED NOT NULL but should be BINARY(4) NOT NULL (type mismatch).

2016-09-19 22:38:40 7f804706d700 InnoDB: Error: Fetch of persistent statistics requested for table "crs_staging"."#sql-634b_3" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

Here is the config file:

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[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
skip-name-resolve
tmp_table_size= 64M
max_heap_table_size= 64M

# 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         = 128M
max_allowed_packet      = 64M
thread_stack            = 192K
thread_cache_size       = 16
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10

innodb_buffer_pool_size = 256M
innodb_log_buffer_size  = 8M
innodb_file_per_table   = 1
innodb_open_files       = 400
innodb_io_capacity      = 400
innodb_flush_method     = O_DIRECT
innodb_buffer_pool_size = 2000MB

#
# * 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
#
# Enable the slow query log to see queries with especially long duration
#slow_query_log_file    = /var/log/mysql/mariadb-slow.log
#long_query_time = 10
#log_slow_rate_limit    = 1000
#log_slow_verbosity     = query_plan
#log-queries-not-using-indexes
#
# 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!
innodb_file_per_table=1

#
# * 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

#
# * Character sets
#
# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
#
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

#
# * Unix socket authentication plugin is built-in since 10.0.22-6
#
# Needed so the root database user can authenticate without a password but
# only when running as the unix root user.
#
# Also available for other users if required.
# See https://mariadb.com/kb/en/unix_socket-authentication-plugin/
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.0 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.0]

Update with locale:

LANG=en_US.UTF-8
LANGUAGE=
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=
5
  • This looks like a (failed) MySQL upgrade, not a new installation. – Michael Hampton Sep 20 '16 at 5:23
  • I see that you tried to restore all databases include mysql system database. Reinstall mariadb to reassign again mysql database. Then import only your database. Check that system locale is utf8mb4 and importing database has the same locale. – Mikhail Khirgiy Sep 20 '16 at 5:24
  • I'm seeing en_US.UTF-8 for locale on both servers. Updated question with full locale output. – cnizzardini Sep 20 '16 at 5:29
  • Just wondering if a reinstall is really necessary here. I did upgrade from the stock mariadb version Ubuntu 16.04 ships with, but would rather fix what I have then a reinstall given that some of our internal applications have already been migrated to this new server, our core customer facing application is all that remains. – cnizzardini Sep 20 '16 at 5:32
  • But my.cnf config file has other locale settings character-set-server = utf8mb4 – Mikhail Khirgiy Sep 20 '16 at 21:31
0

Edit: Apparently a duplicate of this https://stackoverflow.com/questions/15585602/change-limit-for-mysql-row-size-too-large which I discovered when doing an import of only the table that was giving me problems. Mods please mark as duplicate. The remaining errors are fixed by using mysql_upgrade: https://bugs.mageia.org/show_bug.cgi?id=17579

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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