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
8 votes
2 answers
59k 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 ...
user avatar
  • 307
7 votes
4 answers
1k views

Why the query optimizer choose this bad execution plan?

We have a MariaDB table (stories) with more than 1TB of data, periodically running a query that fetches recently added rows for indexing somewhere else. innodb_version: 5.6.36-82.1 version : 10....
user avatar
6 votes
2 answers
3k 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, ...
user avatar
  • 55.7k
5 votes
2 answers
32k 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 ...
user avatar
  • 343
5 votes
1 answer
7k 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 ...
user avatar
5 votes
2 answers
25k 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 ...
user avatar
  • 343
4 votes
2 answers
18k 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 ...
user avatar
  • 637
4 votes
3 answers
5k 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) ...
user avatar
  • 55.7k
4 votes
2 answers
39k 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 ...
user avatar
  • 173
4 votes
2 answers
3k 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 ...
user avatar
3 votes
1 answer
4k 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 `...
user avatar
  • 599
3 votes
2 answers
6k views

Max Connections Maria DB

Every time I reboot Mariadb places me the max_connections in 151 and in the my.cnf I have it with 300. Why does this happen?
user avatar
3 votes
1 answer
3k 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 ...
user avatar
  • 2,178
3 votes
1 answer
978 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 = ...
user avatar
3 votes
1 answer
89 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), ...
user avatar
3 votes
1 answer
13k 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 /...
user avatar
3 votes
1 answer
87 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 ...
user avatar
  • 599
3 votes
2 answers
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, ...
user avatar
2 votes
2 answers
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 ...
user avatar
2 votes
3 answers
10k 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 = ...
user avatar
2 votes
2 answers
2k 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 ...
user avatar
  • 2,178
2 votes
1 answer
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 ,...
user avatar
2 votes
1 answer
2k 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 ...
user avatar
2 votes
2 answers
202 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....
user avatar
  • 147
2 votes
1 answer
522 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 ...
user avatar
  • 131
2 votes
1 answer
4k 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 ...
user avatar
  • 41
2 votes
2 answers
373 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 ...
user avatar
2 votes
1 answer
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 ...
user avatar
2 votes
2 answers
813 views

Unable to run mariadb in safe mode

When I run following command mysqld_safe --skip-grant-tables & I get message myuser@myvm:~$ 200523 08:24:41 mysqld_safe Logging to syslog. 200523 08:24:41 mysqld_safe Starting mysqld daemon ...
user avatar
2 votes
1 answer
233 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 ...
user avatar
2 votes
0 answers
3k 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 ...
user avatar
  • 21
1 vote
3 answers
313 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 ...
user avatar
  • 111
1 vote
2 answers
28 views

Updating log table in an effective manner

There is a table with compound key: t_count + pageType VARCHAR(32) ascii_bin + pageId VARCHAR(255) utf8_bin + count INT PRIMARY(pageType, pageId) Its purpose is to track how many times each page ...
user avatar
1 vote
1 answer
153 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 ...
user avatar
  • 55.7k
1 vote
1 answer
611 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/...
user avatar
  • 2,178
1 vote
1 answer
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-...
user avatar
  • 2,178
1 vote
2 answers
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 ...
user avatar
1 vote
1 answer
2k 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 ...
user avatar
  • 1,690
1 vote
1 answer
660 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-...
user avatar
  • 111
1 vote
1 answer
739 views

MariaDB freezes with no visible reason

MariaDB server experiences some sort of freezes happening sporadically with no visible reason or connection to outside factors (load, etc). After a thorough investigation the common picture for those ...
user avatar
1 vote
1 answer
287 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 }}" ...
user avatar
1 vote
1 answer
73 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), .* ...
user avatar
  • 45
1 vote
1 answer
2k views

Exclude rows with unique values in one column from grouped mysql query

The following table represents interactions form users with different projects: id user_id project_id 17373 6336 1001 17374 6336 1001 17375 6336 1001 ...
user avatar
  • 115
1 vote
1 answer
156 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 ...
user avatar
  • 122
1 vote
2 answers
336 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): +---------+---------+------------+-------------+-------+-------+ | ...
user avatar
  • 13
1 vote
2 answers
2k views

DELETE inside INSERT Trigger

I wanted to make rotation inside my logs table to store last 100 rows only, and decided to use triggers, as in create trigger rotate_log after insert on logs for each row begin declare cnt int; ...
user avatar
  • 113
1 vote
1 answer
448 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 ...
user avatar
1 vote
1 answer
497 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 ...
user avatar
  • 122
1 vote
1 answer
253 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....
user avatar
  • 2,178
1 vote
1 answer
2k 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 ...
user avatar