- discuss - lists.mariadb.org

[Maria-discuss] Monitoring InnoDB tables?
by Erik Cederstrand 13 Apr '16

13 Apr '16

Hi list, I'm developing a fast data synchronization tool and need to monitor some tables in a customer's MariaDB (10.1) server for changes. I only have access to the server as a MariaDB client (no filesystem access). Ideally, I would keep a connection open and poll every few seconds - AFAIK, it's not possible for e.g. a stored procedure to notify a listener external to MariaDB. The tables have no 'timestamp' or other columns I can do MAX() on to check for updates. The database is used as a backend for some proprietary software, so I can't just ask the customer to add an autoincrement column. With MyISAM, I could look at UPDATE_TIME in information_schema.tables (and live with the full-second resolution) or ask the customer to "alter table t1 checksum=1;" so I can use "checksum table t1 quick;". None of these work with InnoDB, and "checksum table t1;" is too slow for large tables. I've seen suggestions to use NUM_ROWS and MODIFIED_COUNTER in information_schema.innodb_sys_tablestats, but it's unreliable for me in a number of cases, e.g. if the number of rows is the same but contents are different. Here's an example: MariaDB [test]> create table t1 (i int primary key, j int); Query OK, 0 rows affected (0.02 sec) MariaDB [test]> insert into t1 values (1, 2); Query OK, 1 row affected (0.00 sec) MariaDB [test]> select * from information_schema.innodb_sys_tablestats where name = 'test/t1'; +----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+ | TABLE_ID | NAME | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT | +----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+ | 15000 | test/t1 | Initialized | 1 | 1 | 0 | 1 | 0 | 1 | +----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+ 1 row in set (0.00 sec) MariaDB [test]> delete from t1; Query OK, 1 row affected (0.00 sec) MariaDB [test]> insert into t1 values (3, 4); Query OK, 1 row affected (0.00 sec) MariaDB [test]> select * from information_schema.innodb_sys_tablestats where name = 'test/t1'; +----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+ | TABLE_ID | NAME | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT | +----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+ | 15000 | test/t1 | Initialized | 1 | 1 | 0 | 1 | 0 | 1 | +----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+ 1 row in set (0.00 sec) How can I monitor an InnoDB table for changes efficiently? I could *possibly* convince the customer to monitor the binlog or full query log, but then I don't even know which database was changed. The server has hundreds of databases. Erik

3 4

[Maria-discuss] R: mariadb/contrib?
by Federico Razzoli 13 Apr '16

13 Apr '16

I would really appreciate. I believe that some great tools exist but are not used because are not in a default MariaDB/MySQL installation. A contrib directory could advertise good tools and encourage people to try them. Regards Federico -------------------------------------------- Mar 12/4/16, Colin Charles <byte(a)aeon.com.my> ha scritto: Oggetto: [Maria-discuss] mariadb/contrib? A: "Maria Discuss" <maria-discuss(a)lists.launchpad.net> Data: Martedì 12 Aprile 2016, 16:55 Hi, I was speaking to Hartmut a few days ago and he mentioned that he really did like the idea of postgresql’s /contrib feature. From postgresql/contrib: This subtree contains porting tools, analysis utilities, and plug-in features that are not part of the core PostgreSQL system, mainly because they address a limited audience or are too experimental to be part of the main source tree. This does not preclude their usefulness. (https://github.com/postgres/postgres/tree/master/contrib) Should we consider doing this for MariaDB Server? Have more tools that people might benefit from, but not spend much time on it? Just a thought cheers, -colin -- Colin Charles, http://bytebot.net/blog/ twitter: @bytebot | skype: colincharles "First they ignore you, then they laugh at you, then they fight you, then you win." -- Mohandas Gandhi -----Segue allegato----- _______________________________________________ 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

1 0

[Maria-discuss] New Question: MariaDB Quarterly patch schedules with all Bug Fixes availability
by AskMonty KB 12 Apr '16

12 Apr '16

Hello, A new question has been asked in "MariaDB Community" by Nagaraj B. Please answer it at http://mariadb.com/kb/en/mariadb-quarterly-patch-schedules-with-all-bug-fix… as the person asking the question may not be subscribed to the mailing list. -------------------------------- Will there be a Quarterly or half yearly release of Mariadb Patches, which includes all the fixes for a quarter. Is there a pre scheduled release dates for the same. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/mariadb-quarterly-patch-schedules-with-all-bug-fix…

1 0

[Maria-discuss] New Question: Fresh Installation of Mariadb
by AskMonty KB 09 Apr '16

09 Apr '16

Hello, A new question has been asked in "MariaDB Binary Packages" by francis_adekunle_45706. Please answer it at http://mariadb.com/kb/en/fresh-installation-of-mariadb/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- We are in the process of setting up our Developments env on Cloud Foundry and Mariadb is the DB of choice. I have Oracle background. I m trying to download and install the Mariadb software binaries as well as setup the database in our development environment. OS is RHEL 6.5 and I tried to use yum to install but got error message. I also obtain the tar.zip file and uncompressed the file on the linux server but most of the files are zero bytes.. Questions: a) What binaries do I need to download for Mariadb installation? b) What OS username and group id etc..would I be installing Mariadb as? c) Where do I get Mariadb files to download since I m having issue with yum.. I m trying to get a Maria db book but need the above basic info to get started. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/fresh-installation-of-mariadb/

1 0

[Maria-discuss] systemctl startup script gives “WSREP: Failed to recover position” - manual startup works
by Lukas Lehner 08 Apr '16

08 Apr '16

Hi please post your ideas http://dba.stackexchange.com/questions/134762/systemctl-startup-script-give… best Lukas

1 0

[Maria-discuss] New Question: Galera errors in mysqld.log but works OK
by AskMonty KB 08 Apr '16

08 Apr '16

Hello, A new question has been asked in "MariaDB Community" by aferrett. Please answer it at http://mariadb.com/kb/en/galera-errors-in-mysqldlog-but-works-ok/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- I have a 3 node Galera cluster that I am testing. It generally works OK, and I have been doing various failure / recovery tests as part of a certification project. I continuously get 3 error messages even though it is working OK. I'd like to eliminate any unnecessary error messages so that DBAs would not taking time to resolve this in a production situation. Here is a sampling of the three errors: I included a bit before and after the error for context: 2016-04-06 11:01:22 140287919777536 [Note] WSREP: Quorum results: version = 3, component = PRIMARY, conf_id = 26, members = 3/3 (joined/total), act_id = 8017357, last_appl. = 0, protocols = 0/7/3 (gcs/repl/appl), group UUID = db762c6b-e246-11e5-90df-4adee5e6b24e 2016-04-06 11:01:22 140287919777536 [Note] WSREP: Flow-control interval: [28, 28] 2016-04-06 11:01:22 140290187433728 [Note] WSREP: New cluster view: global state: db762c6b-e246-11e5-90df-4adee5e6b24e:8017357, view# 27: Primary, number of nodes: 3, my index: 2, protocol version 3 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) 2016-04-06 11:01:22 140290187433728 [ERROR] WSREP: Process completed with error: /opt/app/mysql/product/mariadb-10.1.12-linux-x86_64/support-files/wsrep_notify --status Synced --uuid db762c6b-e246-11e5-90df-4adee5e6b24e --primary yes --index 2 --members 06e16ae0-fb9b-11e5-8ae2-76e1f955bae1/zlx70604.vci.att.com/130.9.221.48:3306…: 1 (Operation not permitted) 2016-04-06 11:01:22 140290187433728 [ERROR] WSREP: Notification command failed: 1 (Operation not permitted): "/opt/app/mysql/product/mariadb-10.1.12-linux-x86_64/support-files/wsrep_notify --status Synced --uuid db762c6b-e246-11e5-90df-4adee5e6b24e --primary yes --index 2 --members 06e16ae0-fb9b-11e5-8ae2-76e1f955bae1/zlx70604.vci.att.com/130.9.221.48:3306…" 2016-04-06 11:01:22 140290187433728 [Note] WSREP: REPL Protocols: 7 (3, 2) 2016-04-06 11:01:22 140287985837824 [Note] WSREP: Service thread queue flushed. 2016-04-06 11:01:22 140290187433728 [Note] WSREP: Assign initial position for certification: 8017357, protocol version: 3 2016-04-06 11:01:22 140287985837824 [Note] WSREP: Service thread queue flushed. 2016-04-06 16:18:22 140287930267392 [Note] WSREP: (8180e0ba, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://10.242.60.235:4567 I grepped for just one of the errors so you can get an idea of the frequency: 2016-04-06 1:58:15 140290151147264 [ERROR] WSREP: Notification command failed: 1 (Operation not permitted): "/opt/app/mysql/product/mariadb-10.1.12-linux-x86_64/support-files/wsrep_notify --status Synced --uuid db762c6b-e246-11e5-90df-4adee5e6b24e --primary yes --index 1 --members 06e16ae0-fb9b-11e5-8ae2-76e1f955bae1/zlx70604.vci.att.com/130.9.221.48:3306…" 2016-04-06 2:15:49 140290151147264 [ERROR] WSREP: Notification command failed: 1 (Operation not permitted): "/opt/app/mysql/product/mariadb-10.1.12-linux-x86_64/support-files/wsrep_notify --status Synced --uuid db762c6b-e246-11e5-90df-4adee5e6b24e --primary yes --index 2 --members 06e16ae0-fb9b-11e5-8ae2-76e1f955bae1/zlx70604.vci.att.com/130.9.221.48:3306…" 2016-04-06 2:15:49 140290151147264 [ERROR] WSREP: Notification command failed: 1 (Operation not permitted): "/opt/app/mysql/product/mariadb-10.1.12-linux-x86_64/support-files/wsrep_notify --status Donor" 2016-04-06 3:40:41 140287908230912 [ERROR] WSREP: Notification command failed: 1 (Operation not permitted): "/opt/app/mysql/product/mariadb-10.1.12-linux-x86_64/support-files/wsrep_notify --status Synced" 2016-04-06 9:28:48 140290187433728 [ERROR] WSREP: Notification command failed: 1 (Operation not permitted): "/opt/app/mysql/product/mariadb-10.1.12-linux-x86_64/support-files/wsrep_notify --status Synced --uuid db762c6b-e246-11e5-90df-4adee5e6b24e --primary yes --index 1 --members 06e16ae0-fb9b-11e5-8ae2-76e1f955bae1/zlx70604.vci.att.com/130.9.221.48:3306…" 2016-04-06 9:29:00 140290187433728 [ERROR] WSREP: Notification command failed: 1 (Operation not permitted): "/opt/app/mysql/product/mariadb-10.1.12-linux-x86_64/support-files/wsrep_notify --status Synced --uuid db762c6b-e246-11e5-90df-4adee5e6b24e --primary yes --index 2 --members 06e16ae0-fb9b-11e5-8ae2-76e1f955bae1/zlx70604.vci.att.com/130.9.221.48:3306…" 2016-04-06 10:05:09 140290187433728 [ERROR] WSREP: Notification command failed: 1 (Operation not permitted): "/opt/app/mysql/product/mariadb-10.1.12-linux-x86_64/support-files/wsrep_notify --status Synced --uuid db762c6b-e246-11e5-90df-4adee5e6b24e --primary yes --index 1 --members 06e16ae0-fb9b-11e5-8ae2-76e1f955bae1/zlx70604.vci.att.com/130.9.221.48:3306…" 2016-04-06 10:07:34 140290187433728 [ERROR] WSREP: Notification command failed: 1 (Operation not permitted): "/opt/app/mysql/product/mariadb-10.1.12-linux-x86_64/support-files/wsrep_notify --status Synced --uuid db762c6b-e246-11e5-90df-4adee5e6b24e --primary yes --index 2 --members 06e16ae0-fb9b-11e5-8ae2-76e1f955bae1/zlx70604.vci.att.com/130.9.221.48:3306…" 2016-04-06 11:01:10 140290187433728 [ERROR] WSREP: Notification command failed: 1 (Operation not permitted): "/opt/app/mysql/product/mariadb-10.1.12-linux-x86_64/support-files/wsrep_notify --status Synced --uuid db762c6b-e246-11e5-90df-4adee5e6b24e --primary yes --index 1 --members 06e16ae0-fb9b-11e5-8ae2-76e1f955bae1/zlx70604.vci.att.com/130.9.221.48:3306…" 2016-04-06 11:01:22 140290187433728 [ERROR] WSREP: Notification command failed: 1 (Operation not permitted): "/opt/app/mysql/product/mariadb-10.1.12-linux-x86_64/support-files/wsrep_notify --status Synced --uuid db762c6b-e246-11e5-90df-4adee5e6b24e --primary yes --index 2 --members 06e16ae0-fb9b-11e5-8ae2-76e1f955bae1/zlx70604.vci.att.com/130.9.221.48:3306…" mysql@zlx70605 /opt/app/mysql -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/galera-errors-in-mysqldlog-but-works-ok/

1 0

[Maria-discuss] New Question: Java Client 1.4.0 Release Notes
by AskMonty KB 08 Apr '16

08 Apr '16

Hello, A new question has been asked in "MariaDB Connector/J" by Sebastian_S. Please answer it at http://mariadb.com/kb/en/java-client-140-release-notes/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi, Why Jira CONJ-260: "Add jdbc nString, nCharacterStream, nClob implementation" (https://jira.mariadb.org/browse/CONJ-260) is on BugFix list of Java Client 1.4.0 Release Notes since it is not implemented and already moved to release 1.5.0? Sebastian Stroz -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/java-client-140-release-notes/

1 0

[Maria-discuss] expire_logs_days
by Reindl Harald 07 Apr '16

07 Apr '16

i find a config variable with "days" just annoying - why - because in case of thin-provisioned virtual servers it wastes space in environment where the slave is for sure not down longer then a view hours and if it is longer sown it's just my problem to act http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_…

2 2

[Maria-discuss] First MariaDB Berlin Meetup
by Colin Charles 06 Apr '16

06 Apr '16

Hi! Many of the core server developers for MariaDB Server and MariaDB MaxScale will be having a meetup in Berlin, on April 12 2016 at Wikimedia’s offices. Feel free to drop by, have a drink, say hi, and listen to the upcoming plans http://www.meetup.com/MariaDB-Developers-Berlin-Meetup/events/230026151/ Looking forward to seeing you if you’re local to Berlin, and if you have friends that would be interested, please feel free to forward this note to them Thanks Kind Regards, Colin Charles -- Colin Charles, http://bytebot.net/blog/ twitter: @bytebot | skype: colincharles "First they ignore you, then they laugh at you, then they fight you, then you win." -- Mohandas Gandhi

1 0

[Maria-discuss] New Question: can maxscal support router like this?
by AskMonty KB 06 Apr '16

06 Apr '16

Hello, A new question has been asked in "MaxScale Routers" by hei_yingzi_(darkz)_g. Please answer it at http://mariadb.com/kb/en/can-maxscal-support-router-like-this/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- I have some server storage some records,like this server_id -> column value server1 -> s1 server2 -> s2 server3 -> s3 server4 -> s4 table table_name1 struce like column1,column2,sid a,1,s1 when I query select * from table_name1 where sid=s1, then route the query to server1 ... select * from table_name1 where sid=s2, then route the query to server2 -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/can-maxscal-support-router-like-this/

1 0