February 2019 - discuss - lists.mariadb.org

[Maria-discuss] explain for insert/update/delete
by MARK CALLAGHAN 08 Jul '25

08 Jul '25

Does MariaDB have any plans to support explain for insert/update/delete/replace/CTAS? There have been feature requests open for a very long time on official MySQL without any progress. I have had a few problems in production from 'bad' delete statements that had a very different plan than the select statement with the same where clause. The only way to check for a bad plan today is to use SHOW INNODB STATUS to determine whether the statement locks all rows in a table. http://bugs.mysql.com/bug.php?id=14745 http://bugs.mysql.com/bug.php?id=35355 -- Mark Callaghan mdcallag(a)gmail.com

3 3

[Maria-discuss] Galera 23.2.7 mariadb 5.5.33a msvc 1600 binaries available
by Yann Nayn 21 May '25

21 May '25

Binaries have been uploaded for both x86 and amd64 platforms. due to github file size limitations , the directories have been uploaded unarchived. for Win33 binaries , please checkout https://github.com/YannNayn/galera-mariadb-win32.git and for Win64(amd64) binaries (being uploaded/pushed): https://github.com/YannNayn/galera-mariadb-win64.git they are not recommended for a production environment. in the my.cnf file: _________ wsrep_provider=galera_smm.dll wsrep_sst_method=msqldump # percona xtrabackup will be tested soon _________ libevent and openssl(the provided binaries use respectively 2.1.3-alpha and 1.1.0-dev versions ) will be added next on github some limitations are stated on openssl website copied here for convenience: ____________ from http://www.openssl.org/ ____________ This software package uses strong cryptography, so even if it is created, maintained and distributed from liberal countries in Europe (where it is legal to do this), it falls under certain export/import and/or use restrictions in some other parts of the world. PLEASE REMEMBER THAT EXPORT/IMPORT AND/OR USE OF STRONG CRYPTOGRAPHY SOFTWARE, PROVIDING CRYPTOGRAPHY HOOKS OR EVEN JUST COMMUNICATING TECHNICAL DETAILS ABOUT CRYPTOGRAPHY SOFTWARE IS ILLEGAL IN SOME PARTS OF THE WORLD. SO, WHEN YOU IMPORT THIS PACKAGE TO YOUR COUNTRY, RE-DISTRIBUTE IT FROM THERE OR EVEN JUST EMAIL TECHNICAL SUGGESTIONS OR EVEN SOURCE PATCHES TO THE AUTHOR OR OTHER PEOPLE YOU ARE STRONGLY ADVISED TO PAY CLOSE ATTENTION TO ANY EXPORT/IMPORT AND/OR USE LAWS WHICH APPLY TO YOU. THE AUTHORS OF OPENSSL ARE NOT LIABLE FOR ANY VIOLATIONS YOU MAKE HERE. SO BE CAREFUL, IT IS YOUR RESPONSIBILITY. ________________________________________________ msvc 1600 redistributables SP1 need to be installed/available on the target machine: vcredist_x86.exe : http://www.microsoft.com/en-us/download/details.aspx?id=8328 vcredist_x64.exe : http://www.microsoft.com/en-us/download/details.aspx?id=13523 MSys binaries are provided and msqld.bat should then be used instead of mysqld.exe (e.g. mysld.bat --console --wsrep_cluster_address=gcomm:// ) in order to have a working sh shell(cygwin has been proved to do it as well) Please note that oqgraph is disabled (boost trunk is used , that is 1.55) Comments and suggestions are welcome.

5 4

[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] Chewing Through Swap - Swappiness = 0
by Michael Caplan 09 Mar '19

09 Mar '19

Hi, I'm trying to figure out why my recently put into production MariaDB is so swap hungry. I'm running 10.2.14, with roughly 300GB data (1000K +/- tables). 95% tables are innodb. I have 64GB RAM, with INNODB buffer pool size set to 50GB (full my.cnf below). The OS is Ubuntu 16.04.4. This is a dedicated MariaDB server. I have swappiness set to 0. I started with available swap set to just shy of 1GB. When swap got to 85% used, I bumped swap to 3GB. A day later, swap again was 85% used, and I bumped it to 5GB. A day later, swap again was 85% used, so I bumped it to 15GB. 2 days later 50% of the available swap was used. I restarted the DB moments ago, freeing up all 15GB but 128MB of swap: total used free shared buff/cache available Mem: 62G 20G 4.7G 266M 37G 41G Swap: 14G 128M 14G My understanding is that the OS should never allocate swap when swappiness is 0, so that has me confused. I've had similar issues with MySQL 5.6 (what I upgraded from) dipping into swap, but that instance had swappiness set to 1, and never consumed more than 5GB of swap. Questions: 1) How much of this is an OS issue vs MariaDB issue? 2) What MariaDB config vars should I be looking at to fix this issue? 3) What linux config vars should I be looking at to fix this issue? Thanks, Mike [mysqld] # GENERAL # user = mysql default-storage-engine = InnoDB character-set-server = utf8 collation_server = utf8_general_ci performance_schema = 0 max_allowed_packet = 16777216 sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" # MyISAM # key-buffer-size = 32M myisam-recover = FORCE,BACKUP # SAFETY # max-allowed-packet = 16777216 max-connect-errors = 1000000 skip-name-resolve sysdate-is-now = 1 innodb = FORCE local_infile = 0 secure_auth = 1 safe_user_create = 1 skip_symbolic_links = 1 wait_timeout = 28800 # 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 = 2 innodb-file-per-table = 1 innodb-buffer-pool-size = 50G

3 8

Re: [Maria-discuss] Server system variables which description I haven't found
by Michal Schorm 28 Feb '19

28 Feb '19

> https://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-and-s… This is the list I was looking for! Thank you. > Whether the description of each is good enough, is a separate question. I would be in favor of more verbose description. Stack trace: Does that mean without "--stack-trace" I won't get the trace? gdb: I understand it as "--gdb" is meant for runtime debugging and has no effect to the coredump files? does that mean "disable_gdb" option does not exists? (anymore?) -- Michal Schorm Software Engineer Core Services - Databases Team Red Hat -- On Wed, Feb 27, 2019 at 4:16 PM Elena Stepanova <elenst(a)mariadb.com> wrote: > > Hi Michal, > > On 02/27/2019 05:07 PM, Michal Schorm wrote: > > Hello everybody, > > > > In the article about "Enabling Core Dumps" [1] there is an example, > > how to properly configure the server for such cases: > > [mariadb] > > stack-trace > > core-file > > disable-gdb > > > > However, the page listing server system variables [2] does not > > describe two of them ("stack-trace", "disable-gdb") and I would like > > That's because they're not system variables, they are command-line > options. I recommend this page as a starting point, it's more universal: > > https://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-and-s… > > Then, for the first one it would send you to > > https://mariadb.com/kb/en/library/mysqld-options/#-stack-trace > > for the other one, you would have to guess that you need to search for > "gdb", not "disable-gdb": > > https://mariadb.com/kb/en/library/mysqld-options/#-gdb > > Whether the description of each is good enough, is a separate question. > > /E > > > > to know what those options do. In which cases I should use each one or > > some combination. Or, since I haven't found their meaning, if there > > aren't long obsoleted. or if the are only applicable to specific > > MariaDB versions. > > Or / and I'm just seeking help on where to find simmilar variables not > > listed on the page [2]. > > > > > > [1] https://mariadb.com/kb/en/library/enabling-core-dumps/ > > [2] https://mariadb.com/kb/en/library/server-system-variables/ > > > > -- > > > > Michal Schorm > > Software Engineer > > Core Services - Databases Team > > Red Hat > > > > -- > > > > _______________________________________________ > > Mailing list: https://launchpad.net/~maria-discuss > > Post to : maria-discuss(a)lists.launchpad.net > > Unsubscribe : https://launchpad.net/~maria-discuss > > More help : https://help.launchpad.net/ListHelp > >

2 2

[Maria-discuss] Server system variables which description I haven't found
by Michal Schorm 27 Feb '19

27 Feb '19

Hello everybody, In the article about "Enabling Core Dumps" [1] there is an example, how to properly configure the server for such cases: [mariadb] stack-trace core-file disable-gdb However, the page listing server system variables [2] does not describe two of them ("stack-trace", "disable-gdb") and I would like to know what those options do. In which cases I should use each one or some combination. Or, since I haven't found their meaning, if there aren't long obsoleted. or if the are only applicable to specific MariaDB versions. Or / and I'm just seeking help on where to find simmilar variables not listed on the page [2]. [1] https://mariadb.com/kb/en/library/enabling-core-dumps/ [2] https://mariadb.com/kb/en/library/server-system-variables/ -- Michal Schorm Software Engineer Core Services - Databases Team Red Hat --

2 1

[Maria-discuss] MariaDB 10.1: binlog stop updating
by Benoit Plessis 22 Feb '19

22 Feb '19

Hi, I have a few mysql cluster, previously on 5.5 (debian jessie) that we are upgrading in mariadb 10.1 (debian stretch) for now. We are experiencing two major issues with the production trafic: * We saw a huge increase in on disk tmp table space used (serveurs with 4Gb of free space in / (including /tmp) had to be added an additionnal 20Gb /tmp volume and even that is no enough everytime * If the replication process has to write a tmp file to execute the query, and log slave updates is active then in the even of the /tmp volume being full, the update of the binlog will also fail erroneouslly with a "No space" error and will stop every following binlog write. 2019-02-20 15:24:12 140618945124096 [ERROR] Slave SQL: Could not execute Write_rows_v1 event on table xxx.yyyy; Error writing file '/tmp/MLxClNzW' (Errcode: 28 "No space left on device"), Error_code: 3; Error writing file '/var/lib/mysql/mysql-bin' (errno: 28 "No space left on device"), Error_code: 1026; Writing one row to the row-based binary log failed, Error_code: 1534; handler error HA_ERR_RBR_LOGGING_FAILED; the event's master log mysql-bin.006843, end_log_pos 42848762, Gtid 0-70-467485, Internal MariaDB error code: 3 2019-02-20 15:24:12 140618945124096 [Warning] Slave: Error writing file '/tmp/MLxClNzW' (Errcode: 28 "No space left on device") Error_code: 3 2019-02-20 15:24:12 140618945124096 [Warning] Slave: Error writing file '/var/lib/mysql/mysql-bin' (errno: 28 "No space left on device") Error_code: 1026 2019-02-20 15:24:12 140618945124096 [Warning] Slave: Writing one row to the row-based binary log failed Error_code: 15342019-02-20 15:24:12 140618945124096 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.006843' position 42814887 Does someone know if they have been improvement in 10.2/10.3 on theses issues ?

4 8

[Maria-discuss] “system user” in SHOW PROCESSLIST
by Felipe Gasper 15 Feb '19

15 Feb '19

Hi all, https://mariadb.com/kb/en/library/show-processlist/ ^^ The above doesn’t describe “system user” in the result of SHOW PROCESSLIST, but that’s what I observe in 10.2.21. What is the meaning of “system user” in the output of this command? Is there a list of these “special” values for the User column? Are these reserved names (i.e., no DB user should be named “system user”)? Thank you! -FG

4 4

[Maria-discuss] Lock Tables and InnoDB
by mariadb＠Biblestuph.com 06 Feb '19

06 Feb '19

I'm missing something in regard to LOCK TABLES when used with InnoDB. Per the documentation here: https://mariadb.com/kb/en/library/lock-tables-and-unlock-tables/ "LOCK TABLES works on XtraDB/InnoDB tables only if the innodb_table_locks system variable is set to 1 (the default) and autocommit is set to 0 (1 is default). Please note that no error message will be returned on LOCK TABLES with innodb_table_locks = 0." But it appears to me that LOCK TABLES *does* work even when autocommit is set to 1, thus I'm unclear as to what is meant by the statement above. Example: MariaDB [MyDB]> show variables like '%version%'; +-------------------------+----------------------+ | Variable_name | Value | +-------------------------+----------------------+ | innodb_version | 5.5.61-MariaDB-38.13 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.5.62-MariaDB | | version_comment | MariaDB Server | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+----------------------+ 7 rows in set (0.00 sec) MariaDB [MyDB]> CREATE TABLE MyInnoDBTest ( -> some_number smallint(5) unsigned not null, -> some_text varchar(20) not null, -> primary key (some_number) -> ) Engine=InnoDB; Query OK, 0 rows affected (0.10 sec) MariaDB [MyDB]> INSERT INTO MyInnoDBTest ( some_number, some_text ) VALUES ( 1, 'One' ), ( 2, 'Two' ), ( 3, 'Three' ); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [MyDB]> SELECT * FROM MyInnoDBTest; +-------------+-----------+ | some_number | some_text | +-------------+-----------+ | 1 | One | | 2 | Two | | 3 | Three | +-------------+-----------+ 3 rows in set (0.00 sec) MariaDB [MyDB]> SELECT @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ MariaDB [MyDB]> SHOW VARIABLES LIKE 'innodb_table_locks'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | innodb_table_locks | ON | +--------------------+-------+ 1 row in set (0.00 sec) MariaDB [MyDB]> LOCK TABLE MyInnoDBTest WRITE; Query OK, 0 rows affected (0.00 sec) ---------------------- Now, in another session, I am unable to do anything with the table, which is what I would suspect if the LOCK TABLES *did* work (I had to CTRL-C out of each statement): MariaDB [MyDB]> SELECT * FROM MyInnoDBTest; ^CCtrl-C -- query killed. Continuing normally. ERROR 1317 (70100): Query execution was interrupted MariaDB [MyDB]> UPDATE MyInnoDBTest SET some_text = 'Four' WHERE some_number = 3; ^CCtrl-C -- query killed. Continuing normally. ERROR 1317 (70100): Query execution was interrupted MariaDB [MyDB]> UPDATE MyInnoDBTest SET some_text = 'Four' WHERE some_number = 4; ^CCtrl-C -- query killed. Continuing normally. ERROR 1317 (70100): Query execution was interrupted MariaDB [MyDB]> INSERT INTO MyInnoDBTest ( some_number, some_text ) VALUES ( 4, 'Four' ); ^CCtrl-C -- query killed. Continuing normally. ERROR 1317 (70100): Query execution was interrupted MariaDB [MyDB]> DELETE FROM MyInnoDBTest WHERE some_number = 3; ^CCtrl-C -- query killed. Continuing normally. ERROR 1317 (70100): Query execution was interrupted ----------------------- What am I missing? Thanks, Dan

3 3