March 2018 - discuss - lists.mariadb.org

[Maria-discuss] why stdout? -> [Note] /usr/libexec/mysqld (mysqld 10.0.20-MariaDB) starting as process 115096
by Reindl Harald 05 Jul '20

05 Jul '20

Aug 6 12:24:24 testserver mysqld: 150806 12:24:24 [Note] /usr/libexec/mysqld (mysqld 10.0.20-MariaDB) starting as process 115096 ... why is this now stdout instead mysql logs? on systemd based distributions that spits now to the global syslog and finally on machines restarting 2 instances every hour for backup reasons it annoys until 10.0.19 or 10.0.20 that was not this way

5 13

[Maria-discuss] MariaDB-10.2.11 Long Time To Start
by Michael Caplan 15 Mar '19

15 Mar '19

Hi, I'm setting up a replication slave based off of MySQL 5.6. There are a large number of tables (400K). The MySQL 5.6 master takes around 10 minutes to start up, where the new MariaDB 10.2 slave takes close to 30 minutes. What is making the startup take soo long? In the below mysql log, you can see in the startup sequence between "Highest supported file format is Barracuda." and "InnoDB: 128 out of 128 rollback segments are active." the startup process takes the majority of its time: 2018-01-07 9:38:15 139980432365760 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2018-01-07 9:38:15 139980432365760 [Note] InnoDB: Uses event mutexes 2018-01-07 9:38:15 139980432365760 [Note] InnoDB: Compressed tables use zlib 1.2.8 2018-01-07 9:38:15 139980432365760 [Note] InnoDB: Using Linux native AIO 2018-01-07 9:38:15 139980432365760 [Note] InnoDB: Number of pools: 1 2018-01-07 9:38:15 139980432365760 [Note] InnoDB: Using SSE2 crc32 instructions 2018-01-07 9:38:15 139980432365760 [Note] InnoDB: Initializing buffer pool, total size = 54G, instances = 8, chunk size = 128M 2018-01-07 9:38:18 139980432365760 [Note] InnoDB: Completed initialization of buffer pool 2018-01-07 9:38:18 139918113195776 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2018-01-07 9:38:18 139980432365760 [Note] InnoDB: Highest supported file format is Barracuda. 2018-01-07 10:11:48 139980432365760 [Note] InnoDB: 128 out of 128 rollback segments are active. 2018-01-07 10:11:48 139980432365760 [Note] InnoDB: Creating shared tablespace for temporary tables 2018-01-07 10:11:48 139980432365760 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2018-01-07 10:11:48 139980432365760 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2018-01-07 10:11:48 139980432365760 [Note] InnoDB: Waiting for purge to start 2018-01-07 10:11:48 139980432365760 [Note] InnoDB: 5.7.20 started; log sequence number 1394303471760 2018-01-07 10:11:48 139919461500672 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool 2018-01-07 10:11:48 139980432365760 [Note] Server socket created on IP: '::'. 2018-01-07 10:11:49 139980432365760 [Note] Reading of all Master_info entries succeded 2018-01-07 10:11:49 139980432365760 [Note] Added new Master_info '' to hash table 2018-01-07 10:11:49 139980432365760 [Note] /usr/sbin/mysqld: ready for connections. Version: '10.2.11-MariaDB-10.2.11+maria~xenial-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 mariadb.org binary distribution Any ideas why? I've included my my.conf file below. Thanks, Mike # Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208 # Configuration name usaddxprdsqlpm3 generated formcaplan(a)labnet.net at 2017-12-22 13:49:58 [mysql] # CLIENT # port = 3306 socket = /var/lib/mysql/mysql.sock [mysqladmin] # CLIENT # port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] # GENERAL # user = mysql default-storage-engine = InnoDB socket = /var/lib/mysql/mysql.sock pid-file = /var/lib/mysql/mysql.pid character-set-server = utf8 collation_server = utf8_general_ci performance_schema = 0 # MyISAM # key-buffer-size = 32M myisam-recover = FORCE,BACKUP # SAFETY # max-allowed-packet = 20M max-connect-errors = 1000000 skip-name-resolve sysdate-is-now = 1 innodb = FORCE # DATA STORAGE # datadir = /var/lib/mysql/ # BINARY LOGGING # log-bin = /var/lib/mysql/mysql-bin binlog_format = MIXED server-id = 3 expire-logs-days = 7 sync-binlog = 1 # REPLICATION # read-only = 1 skip-slave-start = 1 log-slave-updates = 1 relay-log = /var/lib/mysql/relay-bin slave-net-timeout = 60 sync-master-info = 1 sync-relay-log = 1 sync-relay-log-info = 1 # CACHES AND LIMITS # tmp-table-size = 32M max-heap-table-size = 32M query-cache-type = 0 query-cache-size = 0 max-connections = 500 thread-cache-size = 50 open-files-limit = 65535 table-definition-cache = 4096 table-open-cache = 10240 # INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 512M innodb-flush-log-at-trx-commit = 1 innodb-file-per-table = 1 innodb-buffer-pool-size = 54G # LOGGING # log-error = /var/lib/mysql/mysql-error.log log-queries-not-using-indexes = 0 slow-query-log = 0 slow-query-log-file = /var/lib/mysql/mysql-slow.log [mariadb] plugin-load-add=file_key_management file_key_management_filename=/var/lib/mysql/keys.enc file_key_management_filekey=FILE:/var/lib/mysql/mysql.key file_key_management_encryption_algorithm=AES_CTR encrypt_binlog=0

3 19

[Maria-discuss] Charsets and collations
by Peter Laursen 20 Apr '18

20 Apr '18

Does anybody know if there are any plans to bring collations added in MySQL 8 - refer http://mysqlserverteam.com/new-collations-in-mysql-8-0-0/ - to MariaDB and are there any plans to change the default charset to a unicode-based one? -- Peter -- Webyog

2 2

[Maria-discuss] galera & restore a database
by lejeczek 28 Mar '18

28 Mar '18

hi guys I have mariadb in a three nodes galera cluster, when I restore a database on a node: $ mysql wordpress_local < db.sql it works well on that node but the remaining two nodes database tables are empty. Tables structure is there, but no records. What am I missing, doing wrong? many thanks, L.

1 0

[Maria-discuss] MDEV-10286 - Adjustment of table_open_cache according to system limits does not work when open-files-limit option is provided
by Sergey Vojtovich 21 Mar '18

21 Mar '18

Hi Monty, > diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl > index 55543e529a6..54dc0aa17cc 100755 > --- a/mysql-test/mysql-test-run.pl > +++ b/mysql-test/mysql-test-run.pl > @@ -4469,6 +4469,7 @@ sub extract_warning_lines ($$) { > qr/InnoDB: See also */, > qr/InnoDB: Cannot open .*ib_buffer_pool.* for reading: No such file or directory*/, > qr/InnoDB: Table .*mysql.*innodb_table_stats.* not found./, > + qr/Changed limits: max_open_files/, I wonder how did you get this warning? > qr/InnoDB: User stopword table .* does not exist./ > > ); > diff --git a/sql/mysqld.cc b/sql/mysqld.cc > index b21d50a20fe..b3414279ac9 100644 > --- a/sql/mysqld.cc > +++ b/sql/mysqld.cc > @@ -4446,11 +4446,18 @@ static int init_common_variables() > > /* connections and databases needs lots of files */ > { > - uint files, wanted_files, max_open_files; > + uint files, wanted_files, max_open_files, min_tc_size, extra_files, > + min_connections; > + ulong org_max_connections= max_connections, org_tc_size= tc_size; > > + /* Number of files reserved for temporary files */ > + extra_files= 30; > + min_connections= 10; > /* MyISAM requires two file handles per table. */ > - wanted_files= (10 + max_connections + extra_max_connections + > + wanted_files= (extra_files + max_connections + extra_max_connections + > tc_size * 2); > + min_tc_size= MY_MIN(tc_size, TABLE_OPEN_CACHE_MIN); > + > /* > We are trying to allocate no less than max_connections*5 file > handles (i.e. we are trying to set the limit so that they will I believe the following is easier to read: ulong org_max_connections= max_connections; ulong org_tc_size= tc_size; ulong min_tc_size= MY_MIN(tc_size, TABLE_OPEN_CACHE_MIN); /* Number of files reserved for temporary files */ uint extra_files= 30; uint min_connections= 10; /* MyISAM requires two file handles per table. */ uint wanted_files= extra_files + max_connections + extra_max_connections + tc_size * 2; /* We are trying to allocate no less than max_connections*5 file handles (i.e. we are trying to set the limit so that they will be available). In addition, we allocate no less than how much was already allocated. However below we report a warning and recompute values only if we got less file handles than were explicitly requested. No warning and re-computation occur if we can't get max_connections*5 but still got no less than was requested (value of wanted_files). */ uint max_open_files= MY_MAX(MY_MAX(wanted_files, (max_connections + extra_max_connections)*5), open_files_limit); uint files= my_set_max_open_files(max_open_files); > @@ -4463,43 +4470,43 @@ static int init_common_variables() > */ > max_open_files= MY_MAX(MY_MAX(wanted_files, > (max_connections + extra_max_connections)*5), > - open_files_limit); > + open_files_limit); > files= my_set_max_open_files(max_open_files); > > - if (files < wanted_files) > - { > - if (!open_files_limit || IS_SYSVAR_AUTOSIZE(&open_files_limit)) > - { > - /* > - If we have requested too much file handles than we bring > - max_connections in supported bounds. > - */ > - SYSVAR_AUTOSIZE(max_connections, > - (ulong) MY_MIN(files-10-TABLE_OPEN_CACHE_MIN*2, max_connections)); > - /* > - Decrease tc_size according to max_connections, but > - not below TABLE_OPEN_CACHE_MIN. Outer MY_MIN() ensures that we > - never increase tc_size automatically (that could > - happen if max_connections is decreased above). > - */ > - SYSVAR_AUTOSIZE(tc_size, > - (ulong) MY_MIN(MY_MAX((files - 10 - max_connections) / 2, > - TABLE_OPEN_CACHE_MIN), tc_size)); > - DBUG_PRINT("warning", > - ("Changed limits: max_open_files: %u max_connections: %ld table_cache: %ld", > - files, max_connections, tc_size)); > - if (global_system_variables.log_warnings > 1) > - sql_print_warning("Changed limits: max_open_files: %u max_connections: %ld table_cache: %ld", > - files, max_connections, tc_size); > - } > - else if (global_system_variables.log_warnings) > - sql_print_warning("Could not increase number of max_open_files to more than %u (request: %u)", files, wanted_files); > - } > - SYSVAR_AUTOSIZE(open_files_limit, files); > + if (files < wanted_files && global_system_variables.log_warnings) > + sql_print_warning("Could not increase number of max_open_files to more than %u (request: %u)", files, wanted_files); > + > + /* > + If we have requested too much file handles than we bring > + max_connections in supported bounds. Still leave at least > + 'min_connections' connections > + */ > + SYSVAR_AUTOSIZE(max_connections, > + (ulong) MY_MAX(MY_MIN(files- extra_files- min_tc_size*2, > + max_connections), min_connections)); > + /* > + Decrease tc_size according to max_connections, but > + not below min_tc_size. Outer MY_MIN() ensures that we > + never increase tc_size automatically (that could > + happen if max_connections is decreased above). > + */ > + SYSVAR_AUTOSIZE(tc_size, > + (ulong) MY_MIN(MY_MAX((files - extra_files - > + max_connections) / 2, min_tc_size), > + tc_size)); > + DBUG_PRINT("warning", > + ("Current limits: max_open_files: %u max_connections: %ld table_cache: %ld", > + files, max_connections, tc_size)); > + if (global_system_variables.log_warnings > 1 && > + (max_connections < org_max_connections || > + tc_size < org_tc_size)) > + sql_print_warning("Changed limits: max_open_files: %u max_connections: %lu (%lu) table_cache: %lu (%lu)", I'd say this format is quite confusing. Probably add something like "was %lu" in brackets? > + files, max_connections, org_max_connections, > + tc_size, org_tc_size); I think the above code must be executed under "if (files < wanted_files)". We don't really want to "autosize" max_connections and tc_size if we got enough file descriptors. Also you missed to "autosize" open_files_limit. I think it should be done "if (open_files_limit != files) SYSVAR_AUTOSIZE(open_files_limit, files);". > } > > /* > - Max_connections is now set. > + Max_connections and tc_cache is now set. s/is/are/ > Now we can fix other variables depending on this variable. > */ > > diff --git a/sql/sql_const.h b/sql/sql_const.h > index 65742235bee..e28a0649f04 100644 > --- a/sql/sql_const.h > +++ b/sql/sql_const.h > @@ -127,7 +127,7 @@ > #define MAX_FIELDS_BEFORE_HASH 32 > #define USER_VARS_HASH_SIZE 16 > #define SEQUENCES_HASH_SIZE 16 > -#define TABLE_OPEN_CACHE_MIN 400 > +#define TABLE_OPEN_CACHE_MIN 200 > #define TABLE_OPEN_CACHE_DEFAULT 2000 > #define TABLE_DEF_CACHE_DEFAULT 400 > /** > diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc > index de9806ab289..41317a5c259 100644 > --- a/sql/sys_vars.cc > +++ b/sql/sys_vars.cc > @@ -3477,7 +3477,7 @@ static bool fix_table_open_cache(sys_var *, THD *, enum_var_type) > static Sys_var_ulong Sys_table_cache_size( > "table_open_cache", "The number of cached open tables", > GLOBAL_VAR(tc_size), CMD_LINE(REQUIRED_ARG), > - VALID_RANGE(1, 1024*1024), DEFAULT(TABLE_OPEN_CACHE_DEFAULT), > + VALID_RANGE(10, 1024*1024), DEFAULT(TABLE_OPEN_CACHE_DEFAULT), Why? There're at least a few test cases that do table_open_cache=1: partition_open_files_limit, sp, myisam. > BLOCK_SIZE(1), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0), > ON_UPDATE(fix_table_open_cache)); > Regards, Sergey

2 1

[Maria-discuss] columns compression
by Roberto Spadim 19 Mar '18

19 Mar '18

hi guys! anyone testing column compression and have problems when changing form one table format to other? i created a new table with blob compressed column, and i'm trying to insert select from old table, but i got a problem: MariaDB [test]> insert into new_table select * from old_table; ERROR 1406 (22001): Data too long for column 'data' at row 1 using aria or innodb engine give the same error, it's a BLOB columns tahnks -- Roberto Spadim

2 8

[Maria-discuss] SQL_MODE=ORACLE documentation
by Federico Razzoli 05 Mar '18

05 Mar '18

With SQL_MODE='ORACLE' a completely new syntax has been introduced. Basic syntax constructs of MariaDB become unavailable, but not all Oracle syntax seems to be supported. Is there any plan to document the supported syntax? Current documentation looks like an unfinished draft and doesn't save much time for the poor user. Regards,Federico

4 5

[Maria-discuss] 10.3.5 tokudb rpm missing for Opensuse
by Reinis Rozitis 04 Mar '18

04 Mar '18

Hello, It seems that the Mariadb 10.3.5 Tokudb rpm package hasn't been built (and replicated on mirrors) for Opensuse (though all other like SLES, Rhel, Centos do have it). rr

2 1

[Maria-discuss] Backing up a single database with mariabackup?
by Kevin Poitra 01 Mar '18

01 Mar '18

I've been working on redoing our database backup routines, and have looked into mariabackup as mysqldump doesn't cut it anymore given the size of our schemas. We have multiple schemas that need to be backed up - they all vary in size, and they also vary in backup frequency (some need to be backed up on a six hour period, others can go a day in between backups). On top of that, our database consists of two database servers, running in a master-slave configuration. To start, I ran this command against one of our schemas: mariabackup --backup --user root --password <pass> --databases db1 That generated a copy of the 'db1' schema's data files, along with some files used by MariaDB itself (aria_log, ib_buffer_pool, ib_logfile, ibdata, so on). This works perfectly - we can create separate backups of each of our schemas. Restoring seems to be another story however. After '--prepare'ing the backup and issuing the mariabackup --copy-back command, it complains that the data directory must be empty. If I have separated backups, would it be possible for me to restore those schemas under one database (or rather, one MariaDB instance)? To be even clearer, if I've made backups of schemas 'db1', 'db2', and 'db3', I don't seem to be able to restore them all under the same database instance - is there something I'm not understanding?

2 1