January 2016 - discuss - lists.mariadb.org

[Maria-discuss] New Question: Can't optimize database in cluster?
by AskMonty KB 23 Jan '16

23 Jan '16

Hello, A new question has been asked in "MariaDB Galera Cluster" by gijs. Please answer it at http://mariadb.com/kb/en/cant-optimize-database-in-cluster/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- I'm using a cluster of 3 MariaDB 10.1.10 servers on Ubuntu. The servers are located in different datacenters and have a network latency of ~6 ms. Previously I found that I couldn't do a database dump or optimize, because the job would hang and took the cluster down with it. This issue was caused by flow control, it has been fixed by increasing the gcs.fc_limit. I currently have the problem that I can't run a optimize job for databases. For example: mysqlcheck --auto-repair --optimize --databases joomla The optimize job runs fine on the server I execute the job on and finishes in about 10 seconds. Let's call the server that executes this job the "master server". According to syslog the job runs on the other servers as well, I see entries like: Jan 23 16:01:32 Netherlands mysqld[1064]: 2016-01-23 16:01:32 140116180166400 [Note] InnoDB: Online DDL : Start reading clustered index of the table and create temporary files Jan 23 16:01:32 Netherlands mysqld[1064]: 2016-01-23 16:01:32 140116180166400 [Note] InnoDB: Online DDL : End of reading clustered index of the table and create temporary files Jan 23 16:01:32 Netherlands mysqld[1064]: 2016-01-23 16:01:32 140116180166400 [Note] InnoDB: Online DDL : Start merge-sorting index `PRIMARY` (1 / 3), estimated cost : 10.0000 Jan 23 16:01:32 Netherlands mysqld[1064]: 2016-01-23 16:01:32 140116180166400 [Note] InnoDB: Online DDL : End of merge-sorting index `PRIMARY` (1 / 3) Jan 23 16:01:32 Netherlands mysqld[1064]: 2016-01-23 16:01:32 140116180166400 [Note] InnoDB: Online DDL : Start building index `PRIMARY` (1 / 3), estimated cost : 15.0000 Jan 23 16:01:32 Netherlands mysqld[1064]: 2016-01-23 16:01:32 140116180166400 [Note] InnoDB: Online DDL : End of building index `PRIMARY` (1 / 3) Jan 23 16:01:32 Netherlands mysqld[1064]: 2016-01-23 16:01:32 140116180166400 [Note] InnoDB: Online DDL : Completed These other servers go down silently, they stop responding to query's from the users/applications any more but there are no error messages in the syslog. This doesn't happen on the "master" server, which continues to work like nothing happened. I've found that this issue doesn't happen when I run a optimize job for one single table. I believe the issue is that the operation for a entire database is to resource intensive or takes to long to finish. I use the following wsrep_provider_options = gcache.size = 32G;evs.keepalive_period = PT3S;evs.suspect_timeout = PT30S;evs.inactive_timeout = PT1M;evs.install_timeout = PT1M;evs.send_window = 512;evs.user_send_window = 512;gcs.fc_limit=2048 ;gcs.fc_master_slave=YES; gcs.fc_factor=1.0 -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/cant-optimize-database-in-cluster/

1 0

[Maria-discuss] New Question: Use of UPDATE & SELECT operators
by AskMonty KB 22 Jan '16

22 Jan '16

Hello, A new question has been asked in "MariaDB Documentation" by jon_lethbridge_g. Please answer it at http://mariadb.com/kb/en/use-of-update-select-operators/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi - I'm sure this has been asked before but I've tried looking! It's been 20 years since I last used SQL (DB2 and an IBM Mainframe), but all I'm trying to do is change a string of text within a cell. Background - I'm just changing URL address on a wordpress related DB. So in MYSQL it's UPDATE wp_posts SET post_content = replace(post_content, 'http://www.example.com', 'http://localhost/test-site'); Using MariaDB syntax I've tried: UPDATE `wp_posts` SET `post_content`= REPLACE (`post_content`,'http://www.holidaycottagesloirevalley.com','http://localhost/hclv'); and after some reading I've tried REPLACE INTO `wp_posts` VALUE ('http://www.holidaycottagesloirevalley.com','http://localhost/hclv'); No joy and I've run out of ideas (strength!) Can anyone point me in the right direction please? Thanks in advance, Jon. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/use-of-update-select-operators/

1 0

[Maria-discuss] New Question: Request details about MariaDB Cluster behaviour in certain situations
by AskMonty KB 22 Jan '16

22 Jan '16

Hello, A new question has been asked in "MariaDB Community" by noghan_odedra_g. Please answer it at http://mariadb.com/kb/en/request-details-about-mariadb-cluster-behaviour-in… as the person asking the question may not be subscribed to the mailing list. -------------------------------- We are evaluating different database cluster solutions. We would like to get information on some of situations. Assumption: We have 2 sites with cluster each running 3 nodes as master-master. Synchronous replication is configured between clusters. So all the nodes in both clusters remains in sync. There are multiple applications running on both sites and uses their site cluster continuously. Meaning that continuous read/write is perform. 1. When one node reboots in one of the cluster, what would be the order of operations, data sync -> replication or replication -> data sync ? 2. When network connectivity of one of the node of the cluster is lost, when network connectivity is restored, will data sync happen correctly ? 3. As replication is configured in both clusters and they replicate to each other, is there any chance of getting stuck in indefinite replication ? I mean cluster1 replicates to cluster2 and cluster2 replicates to cluster1. 4. In case link between cluster goes down for days, when link is restored, will the data sync happened correctly ? 5. In case one of the node goes into split-brain, will it be recovered correctly ? -noghan -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/request-details-about-mariadb-cluster-behaviour-in…

1 0

[Maria-discuss] C header files in binary distribution on Windows
by Peter Laursen 21 Jan '16

21 Jan '16

I have an almost fresh Win10 installation with MariaDB 10.1.9 installed with .msi installer from this link (I believe) https://downloads.mariadb.org/interstitial/mariadb-10.0.21/win32-packages/m…. No older version has been installed on this system ever. The folder C:\Program Files\MariaDB 10.1\include\mysql and its subfolders have a large number of ".h" files. Isn't this a build script bug? -- Peter -- Webyog

2 3

[Maria-discuss] Fromdual training review
by Lukas Lehner 21 Jan '16

21 Jan '16

Hi I am searching class room training (Galera, Advanced DBA topics) in Europe and found: http://www.fromdual.com/mysql-mariadb-training-modules Anyone can give a short review of their training offering? Feedback? Is Fromdual trust worthy? Lukas

2 1

[Maria-discuss] Select optimizations
by Roberto Spadim 20 Jan '16

20 Jan '16

Hi guys! There's a "easy/fast" way to rewrite a query from this: Select datetime,key,value from table where ... To this: Select datetime, value1 as key1, value2 as key2, .... From table where ... I don't remember the right name in R/python (pandas) language but it's something like vector/matrix merge The ideia is, instead of one line of date key vale, use a single date, and "put" values as columns, and the columns name is the key name The table structure is something like: Create table ( Datetime datetime not null, Key varchar(255) not null, Value double not null, Primary key(datetime,key) ) Any idea is wellcome I know there's many solutions, if i have >100 columns, if datetime is sparse (only one key have values per datetime value), etc etc -- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

2 3

[Maria-discuss] New Question: Using BitNami WordPress with Amazon RDS MariaDB
by AskMonty KB 19 Jan '16

19 Jan '16

Hello, A new question has been asked in "MariaDB Community" by brasilmorar. Please answer it at http://mariadb.com/kb/en/using-bitnami-wordpress-with-amazon-rds-mariadb/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Installed BitNami WordPress which is already configured and updated in an instance EC2 Amazon. Launched a database RDS MariaDB, efetuei up the MySQL database that is already inserted in BitNami stack and then sent the backup.sql for instance RDS MaridaDB. I changed the database access settings in wp-config.php file by inserting the data from the database RDS MariaDB created. Everything is working fine, but I have a doubt about the performance of the bank I have not deleted the Mysql the BitNami stack. If I'm using InnoDB tables and not the standard MariaDB tables. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/using-bitnami-wordpress-with-amazon-rds-mariadb/

1 0

[Maria-discuss] New Question: mysql_install_db
by AskMonty KB 14 Jan '16

14 Jan '16

Hello, A new question has been asked in "MariaDB Community" by skuns_35717. Please answer it at http://mariadb.com/kb/en/mariadb-community-mysql_install_db/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- I'm new to maria, coming from mysql, and I'm trying to run the mysql_install_db program to create the new databases. This is what i'm executing as root: /usr/bin/mysql_install_db --user=mysql --datadir=/var/lib/mysql --basedir=/usr --force I've tried it with and without the force option. I always get the same error: ERROR: 1005 Can't create table `mysql`.`innodb_table_stats` (errno: 165 "Table is read only") 160114 11:35:10 [ERROR] Aborting Any help will be greatly appreciated. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/mariadb-community-mysql_install_db/

1 0

[Maria-discuss] Are lock/unlock tables replicated in ROW / STATEMENT / MIXED r. mode
by pslawek83 13 Jan '16

13 Jan '16

Hi Guys, quick question about lock tables with replication... I basically use it to achieve atomicity of some database writes, but it seems that queries like LOCK TABLES ___tsc WRITE UNLOCK TABLES are not replicated to slave. Eg. i can lock table on master, for write, but still read from it on the slave, which could lead to issues... We're reading some data from slaves, and writing data to it atomically using LOCK TABLES, it seems that this way will lead to race conditions and inconsistent reads from the slave. Any way of having aria/myisam writes to be atomic on the slaves too? I thought to copy the table to random temporary name, do the changes and then rename... but it seems like not very optimal. Thanks.

3 2

[Maria-discuss] New Question: SQL Server replication to Maria DB
by AskMonty KB 13 Jan '16

13 Jan '16

Hello, A new question has been asked in "MariaDB Community" by zeeshan_amin. Please answer it at http://mariadb.com/kb/en/sql-server-replication-to-maria-db/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi, I am new to Maria DB and have no past experience with MySQL. I am trying to create replication between SQL Server 2005 and Maria DB 10.0.14 Publication has 2 tables with close to 24 million rows, and taking a long time to load data into Maria DB. There is no other user connected to MariaDB, Looking at the workbench, i see InnoDB Buffer usage close to 87%, key Efficiency 100%, and Traffic 35KB/sec. as per current speed 2million rows an hour, its going to take 12 hours to load 24 million rows. Any help in making this load faster? Regards Zee -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/sql-server-replication-to-maria-db/

2 1