December 2014 - discuss - lists.mariadb.org

[Maria-discuss] Converting the whole database and tables from one charset+collation to another
by Honza Horak 10 Dec '14

10 Dec '14

Google does not provide any source I would be sure of, many different approaches.. so rather asking here.. Given I have a database with latin1 and some latin* collation, the same for tables and I want to convert database, tables and data to utf8. Are the following two commands really all I need to do? I mean if that is really enough to change the data as well.. ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE db_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; Thanks ahead for clarification. Honza

2 1

[Maria-discuss] New Question: MariaDB client/server (TCP) protocol
by AskMonty KB 07 Dec '14

07 Dec '14

Hello, A new question has been asked in "Geographic Features" by massimo.manca.pn. Please answer it at http://mariadb.com/kb/en/mariadb-clientserver-tcp-protocol/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi, I am interested to interface embedded boards to MariaDB data bases. Actually I made some trials with Mysql and a LPC1769 based board. I am quite satisfied, the results are very good but there is a problem with the license of the Mysql TCP protocol that is released under GPL so... there is a problem to work with closed source applications. 1. I would like to know if client/server protocol is the same for MariaDB and MySql 2. I would like to know if client/server protocol for MariaDB is under GPL or LGPL 3. Is there any documentation about client/server protocol for MariaDB? Thank you very much and thank you also to fork MariaDB from MySQL, it is a better product. Massimo -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/mariadb-clientserver-tcp-protocol/

2 1

[Maria-discuss] apparent master shutdown
by Bruce Carlson 07 Dec '14

07 Dec '14

HI Folks, For the last several years, actually ever since MySQL went to pieces (I mean went to Oracle) I have been running five MariaDB databases in five Australian cities all replicating via a daisy chain. This system has never let me down. I have had times when hardware or network failures have stopped or broken the replication but recovery has always been simple easy and quick. Over the years our IT Infrastructure team have replaced and upgraded several of the servers and I've simply installed MariaDB on the new servers and set the ini files and copied the data and that's all I've had to do. Today the Infrastructure team replaced one of the servers. The old server was running WIN SERVER 2008 and the new server is running WIN SERVER 2012. I've just installed Maria DB on the new server and reset the ini file, loaded the data and restarted the replication. Everything went perfect as expected. (I've learnt to expect nothing less from MariaDB) The databases are replicating perfectly and all user interfaces are connecting and running perfectly. HOWEVER. (AND HERE IS THE BAD NEWS):- Every six seconds I'm getting the following two log entries on the new server. 141206 21:56:30 [Note] Slave: received end packet from server, apparent master shutdown: 141206 21:56:30 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'repbin.000071' at position 12487 Six seconds later ... 141206 21:56:36 [Note] Slave: received end packet from server, apparent master shutdown: 141206 21:56:36 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'repbin.000071' at position 12487 And so on ... There are no log entries on this slave's master indicating any outage or stoppage. Note:- the rep_log_pos changes whenever there has been a replication activity as you would expect. Now I know I'm running an old version of MariaDB (version 5.3.4) but I've never had a problem since I first installed and I suppose I'm a victim of the "if it ain't broken don't fix it" philosophy but I haven't, until now been able to justify the expense and time of upgrading what is working perfectly. OK, for that, shoot me down in flames if you like but has anyone got any ideas what may be causing this? The database size is 8.6 GB with 156 tables (all are Innodb), 32 views, 98 functions, 292 procedures and 3 triggers. It drives around 100 users across the five replicated sites. Kind Regards, Bruce Carlson.

3 4

[Maria-discuss] MariaDB for OpenSuSE 12.3
by Peter Laursen 06 Dec '14

06 Dec '14

I have OpenSuSE 12.3. It ships with MariaDB 1.0.13 - and with no TokuDB and no Galera options. I would like to upgrade to 10.0.14 and also enable TokuDB. And it does not seem that an upgrade will be available from SuSE software repositories. There are a lot of RPMs in the yum repository http://mirror.23media.de/mariadb/mariadb-10.0.14/yum/. But does anyone know if they will work with SuSE 12.3? Runtime environment (kernel, glibc etc.) should be compatible of course, and also RPMs for Redhat/Fedora/CentOS systems will not alwyas work on SuSE, as SuSE has some specific requirements for the SPECS. When I used SuSE 10.x many years ago, the generic "glibc23" RPMs available worked perfectly. Note: I *only* want to install a server that can be handled by SuSE's YaST package manager! So suggestons for any other solution (such as using the tarball) is not an option. Or it would be last resort. I would not mind give it a try if someone can advise what I should try. I have SuSE running in a VM and I will be able to return to a snapshot of the system easily if something goes wrong. -- Peter

5 23

[Maria-discuss] thread_cache_size and buffer pool
by Federico Razzoli 03 Dec '14

03 Dec '14

Hi all Would someone kindly explain (or document) how @@thread_cache_size affects the pool-of-threads? Do values > 0 make sense? Regards Federico

3 3

[Maria-discuss] new mdev about better explain
by Roberto Spadim 02 Dec '14

02 Dec '14

Hi guys, any help is wellcome here the problem: why optimizer chose a "bad" index? an example about this problem is MDEV-7125 i have this EXPLAIN output: id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra 1|SIMPLE|c|const|PRIMARY,NewIndex|PRIMARY|265|const,const|1|Distinct; Using temporary 1|SIMPLE|b|range|PRIMARY,id,plano_conta_numero,cod_busca|cod_busca|265|(NULL)|49|Using index condition; Distinct *1|SIMPLE|a|ref|cfop,item,transferencias,rendimento,estoque,giro|item|6|const,19_org.b.plano_conta_id_red|3347|Using where; Distinct* 1|SIMPLE|d|eq_ref|PRIMARY,NewIndex|PRIMARY|265|19_org.a.estoque_entrada_org,const|1|Using index condition; Using where; Distinct --- check the row from table "a" 1|SIMPLE|a|ref|*cfop,item,transferencias,rendimento,estoque,giro*|item|6|const,19_org.b.plano_conta_id_red|3347|Using where; Distinct optimizer found 5 possible index, and chose the "item" index, instead of "rendimento" index, it's not the best index, but i don't know why optimizer prefer "item" instead of "rendimento" to "solve" (explain) this optimizer choise i execute force index in each index and get output from explain... example EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (cfop) ... EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (item) ... EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (transferencias) ... EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (rendimento) ... EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (estoque) ... EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (giro) ... and compare each output to know why it chose the index 'item' as 'best' option could be possible a better explain from optimizer? something like { 'item': {'key_len':1234, 'ref rows':'blablalba', 'filtered' 99, 'extra':'blabla'}, 'transferencias': {'key_len':1234, 'ref rows':'blablalba', 'filtered' 99, 'extra':'blabla'}, 'rendimento': {'key_len':1234, 'ref rows':'blablalba', 'filtered' 99, 'extra':'blabla'}, 'giro': {'key_len':1234, 'ref rows':'blablalba', 'filtered' 99, 'extra':'blabla'} } i don't know if optimizer have this information, but it's relevant? could we implement a better explain? what others users (dba/developer) do when they have this problem (optimizer selecting the wrong index)? thanks guys -- Roberto Spadim SPAEmpresarial Eng. Automação e Controle

4 6