Questions tagged [mariadb-10.1]

MariaDB 10.1 was released as stable (GA) with version 10.1.8 on 17 Oct 2015.

Filter by
Sorted by
Tagged with
0
votes
2answers
134 views

WARN max_connections from pt-variable-advisor - what to configure for DBaaS?

Our VM configuration (hosted on Vmware) # cat /proc/cpuinfo |grep "cpu cores" | awk -F: '{ num+=$2 } END{ print "cpu cores", num }' cpu cores 32 # free -h total used free ...
0
votes
1answer
3k views

different settings for max_heap_table_size and tmp_table_size (MariaDB 10.1.x)

I run pt-variable-advisor and got a note about different settings for max_heap_table_size and tmp_table_size. With a web search I found only old articles (around 2007). pt-variable-advisor h=...
1
vote
1answer
386 views

pt-query-digest --type binlog (percona-toolkit) gives empty output with MariaDB/Galera 10.1

My installed versions of MariaDB and Percona Toolkit on MacBook: brew info percona-toolkit percona-toolkit: stable 3.0.10 (bottled), HEAD Percona Toolkit for MySQL https://www.percona.com/software/...
1
vote
1answer
337 views

Mariadb slow_query_log keeps disabling

I'm trying to enable slow_query_log on a Mariadb 10.1.32 server. When slow_query_log is enabled & flushed, logging is successful (slow queries are recorded and written to the file) for a minute or ...
5
votes
1answer
5k views

MariaDB - “ERROR 1205 … Lock wait timeout exceeded; …” when doing multiple parallel UPDATEs affecting different rows

We are using MariaDB 10.1.19 hosting a legacy database. We have an lpr table (on InnoDB) where we want to move from a text column gate to a normalized foreign key gate_id. Our code is doing the UPDATE ...
3
votes
3answers
2k views

Can MariaDB or MySQL cast as bigint, getting an error: … right syntax to use near 'bigint)'?

If I want to take a string and create a table (this fails in strict mode), CREATE TABLE g AS SELECT CAST('2147483699' AS int); How do I tell it that I want a bigint type? I'll also get an int(10) ...
5
votes
2answers
2k views

Hex and Binary Literals in MySQL / MariaDB?

Another question I have pertains to just the & over string types. This question is over the binary-string literals as constructed with B'' or X''. MariaDB supports binary and hex literals, ...
1
vote
1answer
141 views

does the gcache.size matter during SST? What if more than gcache.size comes in during SST?

During SST my data dir looks like this (there is a hidden dir .sst with xtrabackup): -rw-rw---- 1 vcap vcap 113 Apr 16 08:13 grastate.dat -rw-rw---- 1 vcap vcap 265 Apr 16 08:13 gvwstate....
1
vote
1answer
1k views

What are the options for archiving old data of mariadb tables if partitioning can not be implemented due to a restriction

We are planning to archive some old data of our MariaDB tables to some other volume(or mount point) of the linux server apart from the one the tables are on. We are using partitioning for this and ...
2
votes
2answers
1k views

why I see only COMMIT or commit in SHOW FULL PROCESSLIST - No SQL statement

I see in lots of output from SHOW FULL PROCESSLIST only COMMIT or commit (lowercase and upercase mix). What are those transactions? Why no SQL statement? We are running MariaDB 10.1.x and Galera ...
0
votes
1answer
334 views

how to install a second server of mariadb [closed]

I have a server where cpanel is installed (it includes mysql 5, php). What I need is to install a second database server mariadb 10.1.20. I tried through: yum install MariaDB-10.1.20-centos6-x86_64-...
3
votes
1answer
836 views

MariaDB audit plugin logs all queries instead of DML only

I was testing the "server_audit" plugin of MariaDB, which I configured like this: plugin-load-add = server_audit server_audit_logging = ON server_audit_events = QUERY_DML server_audit_output_type = ...
0
votes
1answer
66 views

I can't add extra node after reset root password mariadb 10.1.20

I have a mariadb 10.1.20 cluster with 5 nodes. I don't know root password so I did the following steps: On second node, I stopped mysql service I set wsrep = OFF on /etc/my.cnf.d/server.cnf I ...
3
votes
2answers
950 views

Changing master for channel gives syntax error

I'm trying to set up a multi-source replication When trying to set a master for a specific channel using this query CHANGE MASTER TO MASTER_HOST='192.168.0.203', MASTER_USER='rep', MASTER_PORT=3306, ...
4
votes
2answers
35k views

Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 “No such file or directory”) when configuring cluster

I have 3 MariaDB nodes. Want to configure cluster. Specified in gcomm:// in my.cnf ips of all members in cluster according to this. And added ?pc.wait_prim=no at then of gcomm://ip1,ip2,ip3 Then ...
0
votes
1answer
280 views

I can't add an extra node to mariadb cluster 10.1

I was installing a test mariadb cluster but I have a problem when I want to add an extra node. Both nodes use Red Hat Enterprise Linux Server release 7.3 (Maipo) These were the following steps: On ...
1
vote
0answers
26 views

Best setting of MadiaDB database Server (version: 10.1.29-MariaDB) for data about 400GB

I have a single server with 128GB and 64 core CPU. I need to compare multiple tables (about 40 tables) having ~400GB data. The maximum size of a table has ~60GB data for upload as CSV file. With the ...
0
votes
1answer
2k views

InnoDB Write Log efficiency above 100%

After running mysqltuner.pl, I get an Innodb write log efficiency way above 100 %. Why? What does it mean? There is no specific reccomendation from mysqltuner. DB is MariaDB 10.1.26 on Debian 9.x ...
0
votes
0answers
105 views

Move a node from cluster to be a slave of the cluster in Galera

I have 5 Node in Galera cluster. MariaDB Server 10.1.21-MariaDB-1~trusty. I will remove 2 nodes from it but one of them will be a slave. So from 5 nodes 5th one will be completely removed, 4th one ...
2
votes
1answer
2k views

Define calculated column of BOOLEAN/TINYINT(1) type in a View? [duplicate]

I'm configuring a View which contains calculated columns that should be returning Boolean results. Here's an anonymised version of the view: CREATE VIEW `ItemView` AS SELECT Item.ID ,...
3
votes
1answer
56 views

Add Primary Key column at the end of an Index

If I have a table with the following primary key: PRIMARY KEY (id) And I want to query documents written by someone (e.g. display a preview of the latest 20), sorted by their "id", so I created an ...
0
votes
1answer
1k views

Internal MariaDB error code: 1927 ('Connection was killed')

Edit: filed a bug report at https://jira.mariadb.org/browse/MDEV-14493 Mariadb nodes freeze occasionally with the following error in the logs, any clues? (drupal app, db freezes on clearing ...
-1
votes
2answers
2k views

Mariadb minimum CPU requirements (Edit: Still crashing, new question with log linked)

EDIT3: Read-write splitting seems the answer as pointed by out the user dbdemon in the linked question. Internal MariaDB error code: 1927 ('Connection was killed') EDIT2: https://drupal....
0
votes
1answer
86 views

Questions for 'Tips on Converting to Galera' on MariaDB KB [closed]

Some questions about the excellent writing Tips on Converting to Galera. Is "cross-colo writing" cross connect datacenters write? Also, you should use "START TRANSACTION READONLY" wherever ...
0
votes
1answer
193 views

mysqldump to a hostname which is proxy/router (not MySQL server)

I do a mysqldump with those options --single-transaction --routines --events --max_allowed_packet=1G --flush-logs --master-data=2. The desination hostname is not a MySQL server, it's a proxy/router. I ...
0
votes
1answer
427 views

Deadlocks appeared after moving to MariaDB10.2 from MySQL5.5

After moving to mariadb-server-10.2 from mysql5.5 I am facing deadlock issues. Please see the output of 'show full processlist'. Can someone give some suggestion? | 162506 | catalog | ...
1
vote
1answer
2k views

When to use mysqldump with '--no-autocommit'? My benchmark shows slower restore

I tested quick and dirty --no-autocommit vs. without this option on MacBook (installed with Homebrew latest MariaDB). I restored to an empty MariaDB a 4.6 GB *.sql file. The dump made without --no-...
1
vote
1answer
1k views

MariaDB 10.1 Galera cluster error

I'm trying to install MariaDB Galera Cluster with 2 nodes : node 1/ 172.23.0.2 : wsrep_on=ON wsrep_provider=/usr/lib64/galera/libgalera_smm.so binlog_format=ROW wsrep_cluster_address='gcomm://' ...
0
votes
1answer
41 views

mysqltuner.pl shows [!!] 2 different collations for database

mysqltuner.pl shows me result [!!] 2 different collations for database ccdb I don't see two different COLLATE on DB level. Is this even possible? How to verify? > show create database ccdb; +---...
1
vote
1answer
3k views

Node unable to join a MariaDB Galera Cluster

I'm trying to setup a third node for my MariaDB cluster. I copied over the server.cnf file from my second node (which joins the cluster and can start its own cluster). The first two work fine using a ...
1
vote
1answer
1k views

'Out of memory' Error for MariaDB on RDS

We have a t2.medium instance on AWS' RDS platform (4gb, 2xCpu). We're using MariaDB 10.1.19 with a single database, ~120 tables and ~2gb of data in total. After running for about a week we get this ...
1
vote
2answers
963 views

Java application unable to connect to MariaDB Galera cluster

I was debating whether to post this question here or on Stack Overflow but through my own examination, I believe this to be a server-side issue over Java code being wrong. My goal for this project is ...
1
vote
0answers
21 views

Database service likely hang at some moments

Our team adopted MariaDB to replace SQL Server 2008 last year end. There is no issue for DB service hanging, and last week and yesterday I met 2 services hang issues. The following are configurations ...
0
votes
2answers
508 views

reduce number of innodb pages read by a query

In my mariadb I have a large innodb table (600 millions rows). Simple queries on the table using a secondary index perform slowly so I wanted to check the number of innodb pages read to fulfill one of ...
1
vote
1answer
50 views

How come website be random slow? Open sleep processes?

My website is randomly very slow by high MYSQL CPU, and I have no idea what the problem is. Hopefully I will come closer to the solution with this post. If I check the mysql processes, I see a lot of ...
0
votes
1answer
165 views

Partitioned mariadb table files(the .ibd ones) placed outside of data directory return back to data directory itself after altering the table in use

While Partitioning large tables in Mariadb we want some partitions to be placed in another storage device with different speed and capacity. That is our requirement. For that we are trying to perform ...
4
votes
2answers
14k views

MySQL fails to start on arch Linux. Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

MySQL fails to start on arch Linux. mysqld[3440]: [Note] /usr/bin/mysqld (mysqld 10.1.22-MariaDB) starting as process 3440 ... mysqld[3440]: [Note] InnoDB: Using mutexes to ref count buffer pool ...
5
votes
2answers
35k views

Cannot drop column : needed in a foreign key constraint

I have a table with two foreign key constraints as below: mysql> SHOW CREATE TABLE `user`; CREATE TABLE `user` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `region_id` int(11) unsigned ...
3
votes
1answer
84 views

Optimize finding newest x rows from category

The query for this problem is fairly simple and has been answered many times before, I however have problems with optimizing it. Say I have a table with the columns timestamp(bigint), category(int), ...
0
votes
1answer
193 views

Disabling MariaDB cluster multi master mode

Is it possible to configure replication with one master node and 2 slaves in MariaDB like replication works in mongodb? I noticed that the default is multi master mode. Thanks in advance.
0
votes
1answer
247 views

How can I prevent mysqldump from prepending the database name in CREATE VIEW?

When I execute the following: mysqldump -u root -n mydatabase > mydatabase.sql The tables are all dumped without the database name, but the views oddly get the database name prepended: /*!50001 ...
2
votes
1answer
8k views

MariaDB: Disable the GSSAPI plugin

How can i disable the GSSAPI plugin? I do not need authentication via Kerberos on my server. This is my environment: Centos 7.3 with SELinux mode enforcing MariaDB Server: 10.1.19 In my log file /...
2
votes
1answer
2k views

“No valid checkpoint found” when restoring xtrabackup

I have a cluster of MariaDB 10.1 servers, master/master with one node acting as arbitrator and incremental backups with xtrabackup. This is how I've pieced it together from the docs. Master01 does ...
1
vote
1answer
3k views

ERROR! MySQL is running but PID file could not be found - MariaDB 10.1 - CentOS 7 - Fresh VM

So I'm having a little trouble with a BRAND new VM I just created today to test a Galera Cluster. What I have done so far is... Download and install the latest CentOS 7 in a VM. Install open-vm-...
1
vote
1answer
608 views

Galera Cluster - 3rd Quoroum Only Node - No Replication - Possible?HowTo?

I want to setup a 2 node cluster for MariaDB Galera cluster. What I read says it "requires" 3 nodes minimum. This is not correct, I am familiar with HA through using pacemaker, and basically, with ...
0
votes
2answers
681 views

Replication error from Mariadb 10.1 to Mysql 5.1/5.0/5/5 when master's logging format is set to row based

While replicating from Mariadb 10.1 to MySQL (5.0, 5.1, 5.5) or Mariadb (5.2, 5.5) lower versions, if master's binlog_format is set to row, the replication failure occurs with the following message ...
5
votes
2answers
19k views

How to recover MariaDB Galera Cluster after full crash?

I crashed all my 3 nodes. After all nodes have been started I noticed that mariadb is dead. An I couldn't run it again. I am using CentOS 7 on all servers I tried to start first node and then others ...
1
vote
0answers
101 views

MariaDB MySQL Won't Start

I am in a bind, today our server with the MySQL Server went down and we are having trouble getting it back up. Only change to the system is that we upgraded the ram from 2gb to 4gb. Everything else on ...
2
votes
1answer
3k views

UPDATE does not use INDEX, but SELECT does

I'm actually struggling understanding the problem here. I read everywhere saying that UPDATE benefits from indexes on the WHERE clause. However, this query, UPDATE `documents` SET `read`="1" WHERE `...
2
votes
1answer
1k views

MySQL Master-Slave replication foreign key constraint error

This is the cluster setup: ------ ------ | m1 | --- | m2 | ------ ------ || || ------ ------ | s1 | | s2 | ------ ------ m1 and m2 run mariadb-server-10.1 ...