February 2013 - discuss - lists.mariadb.org

[Maria-discuss] Using 10.0s multi-source replication for database consolidation
by Markus "Shorty" Uckelmann 22 Feb '13

22 Feb '13

Hi all, I want to consolidate the databases on several servers onto a single server. And all this without or only minimal downtime. After reading an article[1] from the Percona folks I was thinking of using the multi-source replication feature of 10.0. But there are a few things I'm not sure about. Maybe someone can help me out. What MySQL/MariaDB versions are needed before and behind the MariaDB 10 server? My plan/idea/dream was the following: 1. Stop the server on a slave and start it with skip-slave and skip-networking. Write down replication data. 2. Dump the databases I want to move with "mysqldump <db-name>" 3. Load the dump into MariaDB 10.0 and configure replication. This is done for all the databases I want to put onto a single server. The rest should be business as usual. BTW: We use similar grants on all servers. So the MariaDB 10.0 will be setup with the same statements and I don't have to care about the "mysql"-database. Does this sound realistic? Or have I missed something? [1] http://www.mysqlperformanceblog.com/2012/12/18/migrating-several-single-sta… Thanks a lot, Shorty

2 4

[Maria-discuss] KB Question: Low level access 8Bit MCU
by Daniel Bartholomew 19 Feb '13

19 Feb '13

The following question has been posted in the Knowledgebase: https://kb.askmonty.org/en/low-level-access-8bit-mcu/ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Low level access 8Bit MCU are there any possibilities for a low level access via TCP/IP or UDP with an 8Bit MCU, for the basic functions, read record, edit record, add record. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Thanks. -- Daniel Bartholomew Google+ - http://gplus.to/dbart Twitter - http://twitter.com/daniel_bart MariaDB: An Enhanced Drop-in Replacement for MySQL Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledgebase - http://kb.askmonty.org Monty Program - http://montyprogram.com

1 0

[Maria-discuss] Configuration tool
by Joerg Stephan 19 Feb '13

19 Feb '13

Hi there, if you follow the instructions regarding Ubuntu 12.04 LTS, the instructions will nit lead to an succesfull installation. apt-get will run into an unresolved dependency state, doing it with aptitude you can resolve the dependency in an second step. Maybe the issue should be fixed, or the docu should be updated. Regards Jörg

2 1

[Maria-discuss] KB Question: What Join?
by Daniel Bartholomew 18 Feb '13

18 Feb '13

The following question has been posted to the Knowledgebase: https://kb.askmonty.org/en/what-join/ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - I have a table called called ContenidosDeModelos with fields +------------+-------------+------+-----+ | Field | Type | Null | Key | +------------+-------------+------+-----+ | CdMID | int(5) | NO | PRI | | ModeloID | int(5) | NO | MUL | | ArticuloID | int(5) | NO | MUL | | Cantidad | float(10,3) | NO | | +------------+-------------+------+-----+ which in turn is related on the field ArticuloID to a table called CatArticulos with fields +-----------------------+-------------------------------------- | Field | Type +------------------------+------------------------------------- | ArticuloID | int(5) | ArticuloCodigo | char(7) | ArticuloNombre | varchar(45) | ArticuloDesc | varchar(75) | ArticuloDeshabilitado | char(1) | ArticuloUnidad | enum('pz','mts','kg','ton','m3','lts') +-----------------------+-------------------------------------- I want a listing of ALL of table CatArticulos (1300+ records) with the value of the field Cantidad from the table ContenidosDeModelos for a specified ModeloID (3 - 10 records) when there is a common ArticuloID otherwise Cantidad should be NULL when there is no corresponding ArticuloID in ContenidosDeModelos. I have written at least 100 different joins that don't work. Can someone give me a clue? - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Thanks. -- Daniel Bartholomew Google+ - http://gplus.to/dbart Twitter - http://twitter.com/daniel_bart MariaDB: An Enhanced Drop-in Replacement for MySQL Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledgebase - http://kb.askmonty.org Monty Program - http://montyprogram.com

2 1

[Maria-discuss] CentOS 6 RPMS - do give them a test
by Colin Charles 18 Feb '13

18 Feb '13

Hi! If you're using CentOS, do consider checking out the testing packages available: http://lists.centos.org/pipermail/centos-devel/2013-February/009029.html Quick start: drop http://dev.centos.org/centos/6/mariadb/mariadb.repo into /etc/yum.repos.d/ and yum list mariadb\* Please let us know how it goes and report bugs to http://bugs.centos.org/ cheers, -colin -- Colin Charles, http://bytebot.net/blog/ | twitter: @bytebot | skype: colincharles MariaDB: Community developed. Feature enhanced. Backward compatible. Download it at: http://www.mariadb.org/ Open MariaDB/MySQL documentation at the Knowledgebase: http://kb.askmonty.org/

1 0

[Maria-discuss] testing Galera
by Jan Kirchhoff 12 Feb '13

12 Feb '13

Hi, I'd like to evaluate mariadb-galera on some test databases. Our load is at least 90% writes coming from a datastream and I would try to set up a real-world situation and test with real data. Most of the work load is "insert ... on duplicate key update" mass-inserts (actually updates) of about 4MB/10000 rows each. 99% of these run onto 3-4 tables (each around 10GB/7 million rows of size). Total database size is around 800 GB. We have a traditional statement-based master-slave replication, but all these mass-inserts run over a proxy with SET SQL_LOG_BIN=0 in parallel on all systems - no way to process this amount of data single-threaded on the slaves. I was hoping galera would make this easier and make our proxies obsolete. I have some servers I could use for galera as a first step. I wanted to do this for 4 months now but never got into it, I read your request to test the RC and think I should start now hoping to be able to give some valuable feedback: After reading through the documentation and some blogs I have some doubts, but my idea was to do the following: I'd clone the database of an existing slave to a new machine with mariadb-galera (name the host "testgalera1" and rsync the database or use mysqldump). I'd then set up this one as a "normal" statement based slave connected to one of our masters? Afterwards, the next host "testgalera2" would have to be added to the galera-cluster? And after that, I'd set up one of our older database servers and add that to the cluster, too. My idea was to take an older, slower system to see how much this slowes the cluster down and get an idea how much headroom I'd have when I leave those old machines out (the old machines have only 64GB of RAM and slower CPUs). I'd then modify my proxy for all the incoming inserts and add a writer-thread to one of the hosts in the cluster, that data should be replicated by galera to all other galera hosts. All other data should be coming in via the "traditional" replication thread of testgalera1. This should give me a 95-99% real world write situation on the cluster. Adding a host to a running cluster would mean cloning the whole database (of course), but this also means the host I copy from would have to block write access to its tables and buffer all modifications in memory (at least that's how I read the documentation) during the copy process. Looking at the database size and amount of inserts, I am afraid adding a host to the cluster without taking everything down would be impossible - or even if it works fine now it could cause trouble once the database gets just a little bit larger or my load increases a bit. before I start with this - is galera an insane idea regarding my load? Or is it worth a try? Jan some numbers of some of the old databases: innodb_buffer_pool_size between 60-120 GB depending on server memory configuration. db5: Uptime: 8052248 Threads: 44 Questions: 1413881794 Slow queries: 160049 Opens: 683779 Flush tables: 2 Open tables: 1024 Queries per second avg: 175.588 db6: Uptime: 8070552 Threads: 6 Questions: 873413218 Slow queries: 3208 Opens: 336584 Flush tables: 1 Open tables: 578 Queries per second avg: 108.222 db7: Uptime: 4175032 Threads: 28 Questions: 639627677 Slow queries: 139337 Opens: 522673 Flush tables: 1 Open tables: 2016 Queries per second avg: 153.203 db7# mysql -e 'show global status like "%com\_%" ' |grep -v '\s0$' Variable_name Value Com_admin_commands 289127 Com_alter_table 6269 Com_analyze 725 Com_begin 3712141 Com_change_db 80112 Com_commit 3999309 Com_create_db 1 Com_create_table 253849 Com_create_trigger 10 Com_create_view 68 Com_delete 12365037 Com_delete_multi 7 Com_drop_table 310771 Com_drop_trigger 10 Com_drop_view 65 Com_empty_query 509 Com_flush 48 Com_grant 36 Com_insert 314280963 Com_insert_select 613565 Com_kill 214 Com_lock_tables 85 Com_optimize 1 Com_purge 2320 Com_rename_table 11 Com_repair 1 Com_replace 14240825 Com_replace_select 29920 Com_revoke 8 Com_rollback 13 Com_select 178540996 Com_set_option 6886915 Com_show_binlogs 69960 Com_show_charsets 1542 Com_show_collations 1544 Com_show_create_db 28 Com_show_create_func 195 Com_show_create_table 53282 Com_show_create_trigger 1 Com_show_databases 43584 Com_show_events 3 Com_show_fields 131874 Com_show_function_status 45 Com_show_grants 335 Com_show_keys 2582 Com_show_master_status 142 Com_show_plugins 7224 Com_show_procedure_status 45 Com_show_processlist 116918 Com_show_slave_status 83193 Com_show_status 155732 Com_show_storage_engines 25 Com_show_table_status 7439 Com_show_tables 1973739 Com_show_triggers 538 Com_show_variables 85034 Com_show_warnings 766 Com_stmt_close 363 Com_stmt_execute 363 Com_stmt_prepare 363 Com_truncate 452 Com_unlock_tables 79 Com_update 34496744 Com_update_multi 408212

5 10

[Maria-discuss] Problem in replication with NOW('2012-03-07 17:32:03')
by Jan Kirchhoff 12 Feb '13

12 Feb '13

Hi, I set up 2 hosts as a galera cluster, one of them is a slave of my main database. The galera hosts are 5.5.28a-MariaDB-a1~precise-log, all other databases are 5.2.12-MariaDB-mariadb115~squeeze-log. The 5.5 slave stops with 1064 errors. I figured this is because of updates using NOW() on timestamp columns, I think they come from a phpmyadmin frontend. In the error log the queries look like this: UPDATE ..., some_timestamp_field = NOW('2012-03-07 17:32:03') WHERE ...primary keys... when I manually execute the query and just remove the NOW so it is a simple update some_timestamp_field = '2012-03-07 17:32:03' and then execute set global sql_slave_skip_counter=1;slave start; everything is fine again. All 5.2 hosts replicate just fine. is this a bug in 5.5? Thanks Jan

2 4

[Maria-discuss] service on port 4567 is not starting
by Kőszegi Dániel 12 Feb '13

12 Feb '13

Hi All, I would appreciate a little help. I have done everything how it is in the installation steps, but the galera service on the port 4567 is not starting. No errors in mysql or syslog :( Tried on debian and centos as well. Right now server version: 5.5.28a-MariaDB-a1~squeeze-log mariadb.org binary distribution, wsrep_23.7rc1.rXXXX but I have tried with relevant mysql binaries as well. Do you have any idea why is it not starting the cluster service? wsrep.conf on node1: root@mariaclu01:/etc/mysql# cat /etc/mysql/conf.d/wsrep.cnf [wsrep] wsrep_provider=/usr/lib/galera/libgalera_smm.so wsrep_cluster_address="gcomm://" wsrep_sst_auth=sst:sstpass123 wsrep.conf on node2: root@mariaclu02:~# cat /etc/mysql/conf.d/wsrep.cnf [wsrep] wsrep_provider=/usr/lib/galera/libgalera_smm.so wsrep_cluster_address="gcomm://192.168.1.30:4567" wsrep_sst_auth=sst:sstpass123 MariaDB [(none)]> show status like 'wsrep%'; +--------------------------+----------------------+ | Variable_name | Value | +--------------------------+----------------------+ | wsrep_cluster_conf_id | 18446744073709551615 | | wsrep_cluster_size | 0 | | wsrep_cluster_state_uuid | | | wsrep_cluster_status | Disconnected | | wsrep_connected | OFF | | wsrep_local_index | 18446744073709551615 | | wsrep_provider_name | | | wsrep_provider_vendor | | | wsrep_provider_version | | | wsrep_ready | ON | +--------------------------+----------------------+ root@mariaclu01:/etc/mysql# netstat -tulpn | grep -e 4567 -e 3306 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3387/mysqld Thank you in advance! Cheers, Daniel dudu(a)ihd.hu

2 2

[Maria-discuss] MariaDB 10.0.1-alpha now available
by Daniel Bartholomew 06 Feb '13

06 Feb '13

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.0.1. This is an alpha release. MariaDB 10.0 is the development version of MariaDB. It is built on the MariaDB 5.5 series with backported features from MySQL 5.6 and entirely new features not found anywhere else. This is the second 10.0-based release, and we are releasing it now to get it into the hands of any who might want to test it. Not all features planned for the MariaDB 10.0 series are included in this release. Additional features will be pushed in future releases. See the release notes and changelog for details on what is new in this release. Do not use alpha releases on production systems. - - MariaDB 10.0 Links - - - - - - - - - - - - - - - - - - - - - - - - - Release Notes: http://kb.askmonty.org/en/mariadb-1001-release-notes Changelog: http://kb.askmonty.org/en/mariadb-1001-changelog Downloads: http://downloads.mariadb.org/mariadb/10.0.1 APT and YUM Repository Configuration Generator: http://downloads.mariadb.org/mariadb/repositories/ Overview of MariaDB 10.0: http://kb.askmonty.org/en/what-is-mariadb-100 - - New Packages - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.0.1 includes packages for Fedora 18 "Spherical Cow" and Ubuntu 12.10 "Quantal Quetzal". Visit the Repository Configurator to generate the necessary commands to easily install MariaDB on these and several other distributions. https://downloads.mariadb.org/mariadb/repositories/ - - Discontinued Builds - - - - - - - - - - - - - - - - - - - - - - - Along with the news of new package builds is the news that some old distributions have been deprecated. The MariaDB project tries to support as many different operating systems and Linux distributions as possible. However, when a distribution or OS stops receiving upstream security and other updates it becomes difficult to provide packages for that platform. In such cases, our policy is to deprecate that platform and stop building binary packages for it in our build system. As of 1 Feb 2013, we stopped building and testing packages for the following: - Fedora 16 "Verne" - Debian 5 "Lenny" - Ubuntu 10.10 "Maverick" - Ubuntu 11.04 "Natty" Even after your chosen Linux distribution is deprecated, packages and support are still available. Companies such as SkySQL and Monty Program (among others) provide paid support for all versions of MariaDB and back to even very old MySQL versions. This includes packaged binaries. More information on our deprecation policy can be found at: https://kb.askmonty.org/en/mariadb-deprecation-policy/ - - User Feedback plugin - - - - - - - - - - - - - - - - - - - - - - - MariaDB includes a User Feedback plugin. This plugin is disabled by default. If enabled, it submits basic, completely anonymous MariaDB usage information. This information is used by the developers to track trends in MariaDB usage to better guide development efforts. If you would like to help make MariaDB better, please add "feedback=ON" to your my.cnf (my.ini on Windows) file! See http://kb.askmonty.org/en/user-feedback-plugin for more information. - - Quality - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The project always strives for quality, but in reality, nothing is perfect. Please take time to report any issues you encounter at: http://mariadb.org/jira - - Support MariaDB - - - - - - - - - - - - - - - - - - - - - - - - - - If you would like to contribute to MariaDB, please see our "contributing" and "donations" pages. We also have merchandise available in a cafepress store. - https://kb.askmonty.org/en/contributing - https://mariadb.org/en/donate/ - http://www.cafepress.com/mariadb We hope you enjoy MariaDB! -- MariaDB: An Enhanced Drop-in Replacement for MySQL Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledgebase - http://kb.askmonty.org

1 0

[Maria-discuss] mariasql client question (zappajs, coffeescript, nodejs)
by klrumpf 05 Feb '13

05 Feb '13

Hi, I have switched from mysql to mariadb using it with the node-mysql client and this has been runing in prod. for some time. I now want to switch to the mariasql client... inspect = require('util').inspect mdbClient = require('mariasql') ... To do the many queries and simplify the source I wanted to write a principal function for queries that simply returns an object (or array) with the query result and then I could just re-use the existing functionality for the render. So I copied this from the examples doSql = (sqlstring) -> c = new mdbClient() c.connect host: 'localhost' db: 'xyz' # NOT database user: 'abc' password: 'defghi' c.on("connect", -> console.log "Client connected" ).on("error", (err) -> console.log "Client error: " + err ).on "close", (hadError) -> console.log "Client closed" c.query(sqlstring).on("result", (res) -> res.on("row", (row) -> console.log "Result row: " + inspect(row) ).on("error", (err) -> console.log "Result error: " + inspect(err) ).on "end", (info) -> console.log "Result finished successfully, number of rows: " + info.numRows ).on "end", -> console.log "Done with all results" c.end() .... and then @get '/': (req,res) -> # LANDING PAGE) result = doSql("select pakz from pkt where pktnr=10001") console.log "919, result = ",result .... @view.... Console log in the doSql functions shows the results correctly but I have been playing around for a while and I can't get the function to return the result for further processing to the @get routine. Seems like I have not understood the scope issues here and probably a few more things, I am still green with this. My questions - what is the correct approach to do pass the query object from the function to the calling program if this is possible at all? - how do I access the individual rows the way I do it with node-mysql like so row[1].myfield ...? - should I create the instance of the mdbClient in the function or rather once at the very beginning of the program, my principal worry being stability even if the db disconnects here and there for some reason? Thanks, hope this is the right list for this, Karl -- Karl-L. Rumpf klrumpf(a)gmail.com Málaga, Spain

2 2