- discuss - lists.mariadb.org

[Maria-discuss] Query exceeding 16384 bytes, skipping. Probably a bulk insert query.
by Umarzuki Mochlis 02 Apr '15

02 Apr '15

When I'm restoring dump of all database to one of my MariaDB 10.0 nodes (2 nodes galera cluster), I saw "Query exceeding 16384 bytes, skipping. Probably a bulk insert query.". So I ran again mysqldump for all database and diff with its original dump. There's a lot of differences. Where should I configure on my.cnf to allow each query from dump running successfully? Thanks.

1 0

[Maria-discuss] New Question: Strange issue with a complex stored procedure and Union Query
by AskMonty KB 01 Apr '15

01 Apr '15

Hello, A new question has been asked in "MariaDB Community" by stewartpage. Please answer it at http://mariadb.com/kb/en/strange-issue-with-a-complex-stored-procedure-and-… as the person asking the question may not be subscribed to the mailing list. -------------------------------- I have a complex stored procedure that executes on a schedule. It runs a union query and inserts the results into a table. I recently discovered that it needed to have a LIMIT added to both queries so I enclosed the two selects in brackets, and immediately the query error'd. Removing the brackets allowed the query to run correctly. Are there any issues I should be aware of? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/strange-issue-with-a-complex-stored-procedure-and-…

1 0

[Maria-discuss] One node out of cluster keeps crashing
by Momin Rashid 31 Mar '15

31 Mar '15

Hello everyone, MariaDB-Galera-server-10.0.17-1.el7.centos.x86_64 MariaDB-client-10.0.17-1.el7.centos.x86_64 galera-25.3.9-1.rhel7.el7.centos.x86_64 We have a three node MariaDB-galera cluster, and one of our nodes keeps crashing. The other two nodes have been running fine for weeks without issues. They all have the exact same specifications. I have even rebuilt this node from ground up, and the new node still crashed. Could someone take a look at the following logs and help me figure out what is wrong, and how can we avoid this in future? ===================================== 2015-03-30 10:13:05 7f5be57fc700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 19 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 116110 srv_active, 0 srv_shutdown, 343879 srv_idle srv_master_thread log flush and writes: 459986 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56843 OS WAIT ARRAY INFO: signal count 804274 Mutex spin waits 1039838, rounds 335818, OS waits 4493 RW-shared spins 393078, rounds 1863774, OS waits 46608 RW-excl spins 34511, rounds 1424782, OS waits 5051 Spin rounds per wait: 0.32 mutex, 4.74 RW-shared, 41.28 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 3156054 Purge done for trx's n:o < 3155992 undo n:o < 0 state: running but idle History list length 814 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 3155294, not started MySQL thread id 246025, OS thread handle 0x7f5d84124700, query id 32716796 ha-proxy.prod.lan 10.0.2.11 appl_01 cleaning up ---TRANSACTION 3154332, not started MySQL thread id 245589, OS thread handle 0x7f5d4c963700, query id 32642939 ha-proxy.prod.lan 10.0.2.11 appl_01 cleaning up ---TRANSACTION 3150705, not started MySQL thread id 245568, OS thread handle 0x7f5d840db700, query id 32777452 ha-proxy.prod.lan 10.0.2.11 appl_01 cleaning up ---TRANSACTION 3152515, not started MySQL thread id 245295, OS thread handle 0x7f5d851ff700, query id 32560015 ha-proxy.prod.lan 10.0.2.11 appl_01 cleaning up ---TRANSACTION 3155566, not started MySQL thread id 245281, OS thread handle 0x7f5d86d23700, query id 32774720 ha-proxy.prod.lan 10.0.2.11 appl_01 cleaning up ---TRANSACTION 3156038, not started TOO MANY LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 294306 OS file reads, 2485633 OS file writes, 532545 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 63, seg size 65, 28112 merges merged operations: insert 42583, delete mark 939, delete 29 discarded operations: insert 0, delete mark 0, delete 0 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 121964244699 Log flushed up to 121964244699 Pages flushed up to 121964244699 Last checkpoint at 121964244699 Max checkpoint age 216721613 Checkpoint age target 209949063 Modified age 0 Checkpoint age 0 0 pending log writes, 0 pending chkp writes 153001 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 6263013376; in additional pool allocated 0 Total memory allocated by read views 3288 Internal hash tables (constant factor + variable factor) Adaptive hash index 423323504 (96884488 + 326439016) Page hash 757784 (buffer pool 0 only) Dictionary cache 25112304 (24222544 + 889760) File system 854680 (812272 + 42408) Lock system 15172408 (15139192 + 33216) Recovery system 0 (0 + 0) Dictionary memory allocated 889760 Buffer pool size 373496 Buffer pool size, bytes 6119358464 Free buffers 34210 Database pages 319362 Old database pages 118034 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 1891, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 294208, created 25154, written 2272207 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 319362, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 46687 Buffer pool size, bytes 764919808 Free buffers 4184 Database pages 40015 Old database pages 14791 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 237, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 36928, created 3087, written 294016 LRU len: 40015, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 46687 Buffer pool size, bytes 764919808 Free buffers 4881 Database pages 39304 Old database pages 14528 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 232, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 36418, created 2886, written 189961 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 39304, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 46687 Buffer pool size, bytes 764919808 Free buffers 4278 Database pages 39906 Old database pages 14748 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 215, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 36782, created 3124, written 154398 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 39906, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 46687 Buffer pool size, bytes 764919808 Free buffers 4286 Database pages 39917 Old database pages 14754 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 247, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 36327, created 3590, written 392683 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 39917, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 46687 Buffer pool size, bytes 764919808 Free buffers 4215 Database pages 39956 Old database pages 14769 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 276, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 36833, created 3123, written 258687 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 39956, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 46687 Buffer pool size, bytes 764919808 Free buffers 4082 Database pages 40127 Old database pages 14831 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 243, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 36928, created 3199, written 252207 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 40127, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 46687 Buffer pool size, bytes 764919808 Free buffers 4010 Database pages 40224 Old database pages 14860 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 223, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 37120, created 3104, written 527443 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 40224, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 46687 Buffer pool size, bytes 764919808 Free buffers 4274 Database pages 39913 Old database pages 14753 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 218, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 36872, created 3041, written 202812 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 39913, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 2 read views open inside InnoDB 8 RW transactions active inside InnoDB 0 RO transactions active inside InnoDB 8 out of 1000 descriptors used ---OLDEST VIEW--- Normal read view Read view low limit trx n:o 3156042 Read view up limit trx id 3155989 Read view low limit trx id 3156042 Read view individually stored trx ids: Read view trx id 3155989 Read view trx id 3156029 Read view trx id 3156038 ----------------- Main thread process no. 2488, id 140032660715264, state: sleeping Number of rows inserted 1182044, updated 1504189, deleted 4658, read 2328285511 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long

1 0

[Maria-discuss] New Question: Please list OS requirements.
by AskMonty KB 31 Mar '15

31 Mar '15

Hello, A new question has been asked in "Graphical Clients" by openiduser56. Please answer it at http://mariadb.com/kb/en/please-list-os-requirements/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- It' quite annoying to have to click around the destination website to try to work out what it takes to actually run the tool. I suspect most people looking for a GUI tool already have an operating system they want it to run on, and sometimes even restrictions on how much extra stuff they can install to get it working. So saying "runs on MacOS... you just need Parallels and a Windows licence" is unlikely to make sense. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/please-list-os-requirements/

1 0

[Maria-discuss] mariadb-galera-server : Depends: mariadb-galera-server-10.0 (= 10.0.17+maria-1~trusty) but it is not going to be installed
by Umarzuki Mochlis 27 Mar '15

27 Mar '15

Hi, Error as title happened when I'm following http://www.severalnines.com/blog/howto-offline-upgrade-galera-cluster-mysql… to upgrade from 5.5 to 10.0 Repos /etc/apt/sources.list.d/MariaDB.list deb http://ftp.osuosl.org/pub/mariadb/repo/10.0/ubuntu trusty main /etc/apt/sources.list deb http://my.archive.ubuntu.com/ubuntu/ trusty main restricted deb-src http://my.archive.ubuntu.com/ubuntu/ trusty main restricted deb http://my.archive.ubuntu.com/ubuntu/ trusty-updates main restricted deb-src http://my.archive.ubuntu.com/ubuntu/ trusty-updates main restricted deb http://my.archive.ubuntu.com/ubuntu/ trusty multiverse deb-src http://my.archive.ubuntu.com/ubuntu/ trusty multiverse deb http://my.archive.ubuntu.com/ubuntu/ trusty-updates multiverse deb-src http://my.archive.ubuntu.com/ubuntu/ trusty-updates multiverse deb http://my.archive.ubuntu.com/ubuntu/ trusty-backports main restricted multiverse deb http://security.ubuntu.com/ubuntu trusty-security main restricted deb-src http://security.ubuntu.com/ubuntu trusty-security main restricted deb http://security.ubuntu.com/ubuntu trusty-security multiverse deb-src http://security.ubuntu.com/ubuntu trusty-security multiverse deb http://repo.percona.com/apt trusty main deb-src http://repo.percona.com/apt trusty main Command used $ sudo LC_ALL=en_US.utf8 DEBIAN_FRONTEND=noninteractive apt-get -o Dpkg::Options::='--force-confnew' -y install mariadb-galera-server galer $ sudo dpkg --get-selections | grep maria libmariadbclient18 install mariadb-client-5.5 deinstall mariadb-client-core-5.5 install mariadb-common install mariadb-galera-server-5.5 deinstall $ sudo apt-get remove libmariadbclient18 mariadb-client-5.5 mariadb-client-core-5.5 mariadb-common mariadb-galera-server-5.5 Even after purging those packages I'm still having same error. How to solve this? Any help is appreciated. Ubuntu 14.04

2 2

[Maria-discuss] Source package for Ubuntu Precise is missing
by Kouhei Sutou 25 Mar '15

25 Mar '15

Hi, It seems that source package of MariaDB 10.0.17 for Ubuntu Precise Pangolin (12.04 LTS) isn't provided. Binary packages for Precise Pangolin are provided. mariadb-10.0_10.0.17+maria-1~trusty.dsc (for Trusty Tahr) and mariadb-10.0_10.0.17+maria-1~utopic.dsc (for Utopic Unicorn) exist at: http://mirror.jmu.edu/pub/mariadb/repo/10.0/ubuntu/pool/main/m/mariadb-10.0/ But for Precise doesn't exist at there. Is Precise support finished? Could you confirm it? FYI: Source package of MariaDB 5.5.42 for Ubuntu Precise Pangolin (12.04 LTS) is provided. http://mirror.jmu.edu/pub/mariadb/repo/5.5/ubuntu/pool/main/m/mariadb-5.5/ Thanks, -- kou

2 2

[Maria-discuss] Inconsistent engine typecode for TokuDB
by Jan Hejl 23 Mar '15

23 Mar '15

Hello, i moved from TokuDB 7.5.x MySQL (5.5.39) fork to MariaDB 10.0.16. Since then mysqld.err show error like this: 150315 22:30:29 [Warning] ./imperial@002bkv@002bcz773/scanner.frm is inconsistent: engine typecode 42, engine name TokuDB (43) for each TokuDB table access. Editing 4th byte of FRM file didn't helped, it gets overwriten to 0x2A value again. Does anyone know how to get rid of these warnings? Thanks Jan

2 3

[Maria-discuss] Reason for changing the thread_pool_size default value?
by Ian Gilfillan 19 Mar '15

19 Mar '15

Hi What was the reason for changing the default thread_pool_size from the number of CPU's to 8? If I look at the history of sql_vars.cc - https://github.com/MariaDB/server/commits/10.1/sql/sys_vars.cc?page=2 - the commit made on the 17th of November still has it as the number of CPU's, but the next commit on the 2nd of December has it at 8. This doesn't appear as a change in the summary. ian

2 1

[Maria-discuss] MariaDB 10: how to convert Aria table to InnoDB?
by Reindl Harald 19 Mar '15

19 Mar '15

how do someone convert a Aria table to another engine? MyISAM and InnoDB results in the same error ___________________________________________________________ MariaDB [dbmail]> ALTER TABLE `systemevents` ENGINE = InnoDB; ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1' MariaDB [dbmail]> ALTER TABLE `systemevents` TRANSACTIONAL=0; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [dbmail]> ALTER TABLE `systemevents` ENGINE = InnoDB; ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1' ___________________________________________________________ CREATE TABLE `systemevents` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `DeviceReportedTime` datetime DEFAULT NULL, `FromHost` varchar(30) CHARACTER SET latin1 COLLATE latin1_german1_ci NOT NULL, `Message` text, `SysLogTag` varchar(60) DEFAULT NULL, PRIMARY KEY (`ID`), KEY `FromHost` (`FromHost`), KEY `SysLogTag` (`SysLogTag`), KEY `DeviceReportedTime` (`DeviceReportedTime`) ) ENGINE=Aria AUTO_INCREMENT=698044 DEFAULT CHARSET=utf8 PACK_KEYS=0 PAGE_CHECKSUM=0 DELAY_KEY_WRITE=1 ROW_FORMAT=PAGE TRANSACTIONAL=0

6 14

[Maria-discuss] New Question: MS-SQL Server Change tracking
by AskMonty KB 18 Mar '15

18 Mar '15

Hello, A new question has been asked in "Moving to MariaDB" by peterhorwood. Please answer it at http://mariadb.com/kb/en/ms-sql-server-change-tracking/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- I'm looking at having our product add MariaDB as a database we support. Currently we only support MS-SQL. One issue I'm wondering about is change tracking. Does MariaSQL have anything like the SQL Server (2008 and newer) Server change tracking? Or do I need to 'roll my own' using insert/update/delete triggers? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/ms-sql-server-change-tracking/

1 0