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
5
votes
2answers
42k 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 ...
5
votes
1answer
6k 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 ...
5
votes
2answers
21k 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 ...
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, ...
4
votes
2answers
15k 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 ...
4
votes
1answer
26k views

How to restart MariaDB Galera cluster?

How to restart MariaDB Galera cluster? After all node have been crashed I try to recover the cluster but without success. I have only 2 nodes. As documentation says I set a parameter on one of the ...
4
votes
3answers
3k 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) ...
4
votes
2answers
37k 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 ...
4
votes
2answers
2k views

Unexpected RAM usage on MySQL

We have a server which hosts about 10 customers in a multi-tenant web application, each customer has its own database which is cloned from a template, and each database is a little less than 3000 ...
3
votes
1answer
2k views

how to get 'Queries per second avg' using SQL (not 'mysqladmin status')

When I start mysqladmin, it prints out several statistics, including "Queries per second avg": # mysqladmin --defaults-file=../mylogin.cnf status Uptime: 4568115 Threads: 859 Questions: 3703806462 ...
3
votes
1answer
891 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 = ...
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), ...
3
votes
1answer
9k 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 /...
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 ...
3
votes
2answers
1k 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, ...
2
votes
2answers
8k views

MariaDB 10.1.16 on Ubuntu fails to start: WSREP Failed to recover position

I'm using 10.1.16-MariaDB-1~xenial from the official MariaDB apt repository for 10.1 [stable], via the University of Texas mirror. I had a perfectly functioning MariaDB Galera cluster setup on 3 ...
2
votes
3answers
8k views

Which file do I need to modify my.cnf MariaDB on Ubuntu 18.04

I want to modify these values: [mysqld] key_buffer_size = 256M max_allowed_packet = 128M thread_stack = 256K query_cache_limit = 64M query_cache_size = 384M innodb_buffer_pool_size = ...
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
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 ...
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 ,...
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 ...
2
votes
2answers
184 views

how to improve this query performance

I have an application that's running this following query SELECT SQL_NO_CACHE p.*, product_shop.*, stock.out_of_stock, IFNULL(stock....
2
votes
1answer
479 views

Using mariabackup/xtrabackup vs block level snapshots

I am running a mariadb 10.1 instance with all innodb tables and a replication slave in a k8s cluster on Google Cloud's GKE. I have a scheduled mysqldump that runs every night. But for additional ...
2
votes
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://' ...
2
votes
1answer
3k 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 ...
2
votes
1answer
148 views

How to optimize the execution of query that takes too long

I am working with a query that is taking around 53 minutes to execute, which I feel is excessive. Maria DB version: Server version: 10.1.38-MariaDB-1~stretch mariadb.org The declaration of the ...
2
votes
0answers
2k views

Mariadb connection fails through tunnel when bind address = localhost

I have a MariaDB 10.1.26 virtual server running on Debian 9. It is connected to via ssh tunnel and the bind address = localhost. It has been working without fail untill recently. Now when I connect ...
1
vote
3answers
169 views

How can I speed up this query?

Here is the definition of the relevant table: CREATE TABLE IF NOT EXISTS pricehistory ( id INTEGER(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, price INTEGER NOT NULL, region ...
1
vote
1answer
121 views

How does STRICT_ALL_TABLES work with multi-row statements and partial updates?

In the docs for STRICT_ALL_TABLES it states, For STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. However, because the earlier rows have been inserted or updated, the ...
1
vote
1answer
441 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
3k 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
2answers
3k views

MySQL Instance Crashing on Selecting From a DB

I have a MySQL database table which crashes whenever I select from the table or perform any operation no matter how little it is. It crashes the whole MySQL instance. The table is of the following ...
1
vote
1answer
1k views

Can't grant privileges to “root” when it has no password in MariaDB

I am setting up a small development local server (is a VM running CentOS 7) and because of that I didn't add any password to MySQL after run the command: mysql_secure_installation. Now I need to ...
1
vote
1answer
628 views

Issues using pt-kill to kill log queries

I'm trying to get pt-kill to work to kill queries running longer than n. The command as I'm running now is; pt-kill --busy-time 15 --print -u root -p `cat mysqlpw` --victims all --log /var/log/pt-...
1
vote
1answer
62 views

Optimize SELECT statement for mysql database

I have this query: SELECT COUNT(*) AS "Other" FROM events JOIN triggers ON events.objectid = triggers.triggerid WHERE events.name REGEXP "No data from SAM0056(.FTG|.REC|.LEFT|.RIGHT|.BACK), .* ...
1
vote
1answer
97 views

migrate users from mariadb10.1 to mysql5.7

My company is moving our databases from self-hosted mariadb 10.1 to Google CloudSQL (which is essentially MySQL 5.7). Dumping / restoring our application schemas looks straightforward. However I'm ...
1
vote
1answer
354 views

greatest n per group with a 3 tables join

Working on a project with following objects: People id Vehicles id model: text and a link table for owners which passes through owners changes Owner_Changes old_person_id new_person_id ...
1
vote
1answer
384 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 ...
1
vote
1answer
169 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 ...
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
1answer
186 views

Using “OR” against two indexed columns doesn't use indexes

In the query below, from and tid are indexes of the replies table. SELECT * FROM `replies` WHERE `from`="<userId>" OR `tid` IN (SELECT `tid` FROM `posts` WHERE `from`="<userId>") By ...
1
vote
0answers
59 views

MariaDB root password change using Ansible

I had tried to change MariaDB root password using following Ansible script. - name: Dump MySQL root Password debug: msg: "MySQL new Password : {{ mysql_pass }}" ...
1
vote
0answers
1k views

Mariadb is not starting

Please help me with this issue MariaDB not starting. Its a production server so not able to reinstall and clean the data at all. (This command executed by me) systemctl -l status mariadb.service ...
1
vote
0answers
436 views

Connect to MariaDB server fails although port seems to be open

Hello I am trying to connect to an external MariaDB server. The configurations files on this installations are in a different place but I managed to find my way around. The installation im using is: ...
1
vote
1answer
728 views

Cannot remove orphaned InnoDB Temp Table - missing .frm file

For the second time, I have encountered an issue, where our MySQL server (MariaDB v10.1) suddenly threw an error - Could not remove temporary table: './database_name/#sql-4593_791', error: 120. ...
1
vote
2answers
228 views

User rank based on cumulative rows

UPDATE: Full table schema and data: https://www.db-fiddle.com/f/jT3Mfwd1BJ8GrsBxxPDC8M/1 Table structure (some fields are hidden): +---------+---------+------------+-------------+-------+-------+ | ...
1
vote
0answers
67 views

MariaDB Cluster Cannot Join new Node

This is status on Main node. it's show some seqno as 17210843 when new empty node is join. i'm found rsync process is ok. but i see difference seqno in linux process after finish this process my ...
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 ...
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 ...