- discuss - lists.mariadb.org

[Maria-discuss] MariaDB version of Function Name Parsing and Resolution
by mariadb＠biblestuph.com 25 Mar '19

25 Mar '19

Is there anywhere a MariaDB specific version of the document here: https://dev.mysql.com/doc/refman/8.0/en/function-resolution.html Or can someone just tell me which functions are affected by IGNORE_SPACE for MariaDB 10.3? I'm guessing that at least DATE_FORMAT has been added to that list at some point though it doesn't appear as such in any of the MySQL docs. (On a test version of 10.3 I'm getting a DATE_FORMAT error that I didn't have on 5.5). I can correct my spacey statements easily enough, but it would be helpful to know what functions I'm looking for. Thank you!

2 2

[Maria-discuss] mysqld --initialize
by Felipe Gasper 22 Mar '19

22 Mar '19

Hi all, Does MariaDB intend to support “mysqld --initialize”? Thank you! -Felipe Gasper Mississauga, Ontario

3 3

[Maria-discuss] OT Advice Sought: Migrating from to MySQL replication to Maria cluster
by Richard Reina 20 Mar '19

20 Mar '19

Since 2001 I have been running MySQL for a database application that runs on a private LAN. There all only a few users but the data is important to me so I have had replication with a handful of slave servers -- I also backup daily with mysqldump. I recently found out that my slaves were broken -- in that not all update quieres replicate becuase I configured them wrong using replicate-ignore-db. https://stackoverflow.com/questions/55151424/update-database-table-set-name… My master server is also pretty old and running an old version of MySQL. It is definitely time to upgrade. My question is should I try to migrate to MariaDB replication or MariaDB/Galera Cluster? How much harder is configuring and maintaining a DB cluster vs replication? Does MariaDB have any paid support option where I can hire someone to help me set up a DB cluster? Thanks, Richard

2 2

[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] how to file a problem / bug ?
by Norbert Zawodsky 14 Mar '19

14 Mar '19

Hello to everybody! I hope that this mailing list is the right place to ask this question... I have been using mysql, and since it came out mariadb, for years now without any problem. Now, something serious (a bug ?) suddendly happened. (mariadb server crashed and a whole table was gone afterwards. So I wonder where would be the right place to communicate / discuss this problem? Hope that someone here can point me in the right direction. regards, Norbert

4 4

[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

[Maria-discuss] MariaDB-10.4 / Galera and GTIDs - aka we now have two different GTID implementations
by Daniel Black 04 Mar '19

04 Mar '19

Hi, I was excited to come across the Galera-4 release notes and blog. http://galeracluster.com/2019/02/galera-cluster-4-available-for-use-in-the-… It was good to see causal read functions that I'd hoped for in https://jira.mariadb.org/browse/MDEV-10715. WSREP_LAST_WRITTEN_GTID/WSREP_LAST_SEEN_GTID/ WSREP_SYNC_WAIT_UPTO_GTID functions which seemed to mirror existing functions like MASTER_GTID_WAIT and GTID variables. The reason these are different names is because a MariaDB GTID is different from a Galera one. *Having two GTID implementations is insane*. To state the obvious from a user point of view, we've now got two Global Transaction Identification implementations. Application and framework writers like Django/Drupal now have to handle now try to handle consistently with 00000000-0000-0000-0000-000000000000:25 for Galera and 0-1-33 for MariaDB async gtid replication. This is not just a format string difference. The an implementation requires a disparate set of functions to implement when they could of been consolidated. A mixed topology setup could be hidden from the application implementation if the same functions where used. There is/was also a option/proposal that had MariaDB gtid in the protocol save a round trip retrieval of a GTID - quite useful. There is also a bunch of documentation that refers to GTID and its going to absolutely horrible to try to keep the concepts separated as you try to configure a system. An issue like MDEV-10715, the most voted for and watched open issue in JIRA, (and related MDEV-14153), should of had a very priority than this when the implementation from Codership was been merged. This shoehorned in implementation from Codership is a very poor fit. Giving MDEV-10715 a critical priority and a 10.5 target is fiction when there isn't going to be a new galera wsrep api implementation for a number of years. An extra field in the protocol is all it would of taken (comment in MDEV-10715 from 8 months ago). 10.4.3 is out (really an RC?), the galera code got merge in 10.4.2, and the source code of galera-4 isn't even released. Hopefully the tight relationship between MariaDB Corporation and Codership is enough to get this fixed because from an architectural point of view, because the current implementation shouldn't of been merged (contractual arrangement or not). Developers, MariaDB and Codership you've lost track of your user base because the feedback, that is readily available, is ignored. Sadly, Daniel

2 1

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