Skip to main content
Share Your Experience: Take the 2024 Developer Survey
22 votes
Accepted

Cannot drop column : needed in a foreign key constraint

Having a look at MySql docs I've found a warning about foreign_key_keys: Warning With foreign_key_checks=0, dropping an index required by a foreign key constraint places the table in an ...
McNets's user avatar
  • 23.8k
15 votes
Accepted

How to restart MariaDB Galera cluster?

MariaDB Galera Cluster: Solution 1: 1) I've changed safe_to_bootstrap parameter to 1 on one of the node in the file /var/lib/mysql/grastate.dat: safe_to_bootstrap: 1 2) After that I killed all mysql ...
Oleksandr's user avatar
  • 393
12 votes
Accepted

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

The arch wiki helped me solve this pretty quickly. Install mariadb, afterwards run the following command before starting the mariadb.service: mysql_install_db --user=mysql --basedir=/usr --...
nelaaro's user avatar
  • 727
12 votes
Accepted

MariaDB - "ERROR 1205 ... Lock wait timeout exceeded; ..." when doing multiple parallel UPDATEs affecting different rows

What are we doing wrong? Is there a table-level lock even if using InnoDB? Your problem is the lack of a proper index to use. InnoDB does next-key locking, meaning it will lock only the rows that it ...
jynus's user avatar
  • 14.9k
8 votes
Accepted

How to recover MariaDB Galera Cluster after full crash?

Pre-Recovery Settings: Ensure that the MYSQL_HOME path is being exported in the .profile. If the MySQL install is in a different location, then make that change to the MYSQL_HOME.(Example: MYSQL_HOME=...
Siero Sierikas's user avatar
6 votes
Accepted

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

There was a bug in galera_recovery.sh script. https://jira.mariadb.org/browse/MDEV-10396
Nirbhay Choubey's user avatar
5 votes
Accepted

UPDATE does not use INDEX, but SELECT does

To address the question "UPDATE does not use INDEX, but SELECT does"... Until very recently, many UPDATEs were processed by different code than SELECTs. Recently, there was a unification in the ...
Rick James's user avatar
  • 78.4k
5 votes

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

For those running MariaDB and MySQL (If using 5.7, show_compatibility_56 enabled) SELECT Q / T Queries_per_second_avg FROM (SELECT variable_value Q FROM information_schema.global_status WHERE ...
RolandoMySQLDBA's user avatar
5 votes
Accepted

Select only the last record for multiple values

Given your example data this should do it. SELECT name, max(f_time) AS f_time FROM sessions WHERE name IN ('abc','def','xyz') GROUP BY name ORDER BY name; If you need to pick the f_time according ...
Jasen's user avatar
  • 3,593
5 votes

Max Connections Maria DB

This depend if you set max_connections under the correct section inside my.cnf. what do you need to check(with examples) go to MySQL Directory cd /opt/mysql edit my.cnf using any text editor sudo vi ...
Ahmad Abuhasna's user avatar
4 votes

How do I find mismatches in two tables?

MySQL (and the derivative) MariaDB have not implemented FULL JOIN. So you need either 2 queries or a UNION of 2 NOT EXISTS subqueries. To find the not matched IDs using NOT EXISTS: SELECT t1.id, '...
ypercubeᵀᴹ's user avatar
4 votes

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

This is currently impossible. MySQL doesn't have a real BOOLEAN type, (or a real array type.. or a real JSON type). It has an alias for TINYINT. Any condition returns an integer. This is the fastest ...
Evan Carroll's user avatar
  • 63.6k
4 votes

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

Silently? Let's try: CREATE TABLE g AS SELECT CAST('2147483699' AS int); MariaDB 10.1.32, default sql_mode (non-strict) Query OK, 1 row affected, 1 warning (0.19 sec) Records: 1 Duplicates: ...
dbdemon's user avatar
  • 6,371
4 votes
Accepted

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

I agree that the split between _ALL_ and _TRANS_ needs clarification. Earlier on that page it says With TRADITIONAL mode enabled, an INSERT or UPDATE aborts as soon as an error occurs. If you are ...
Rick James's user avatar
  • 78.4k
4 votes

How can I speed up this query?

Because your WHERE clasue referencing timestamp has the column wrapped in a function, it is not going to be able to use an index efficiently to may currently be scanning the whole table. An index on ...
David Spillett's user avatar
3 votes
Accepted

Optimize finding newest x rows from category

Community Wiki answer compiled from comments on the question You should probably check the InnoDB settings. You may have given very little RAM for MySQL to use. How much RAM do you have, and what's ...
3 votes
Accepted

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

With autocommit=1, each INSERT will be committed before moving on to the next. There is less overhead. But, if the server crashes, the table will be half built because some INSERTs will be committed;...
Rick James's user avatar
  • 78.4k
3 votes
Accepted

MariaDB: Disable the GSSAPI plugin

File /etc/my.cnf.d/auth_gssapi.cnf contains the line plugin-load-add=auth_gssapi.so Comment it out and restart the service. The server should stop loading the plugin, it will make the errors go ...
elenst's user avatar
  • 224
3 votes
Accepted

MariaDB audit plugin logs all queries instead of DML only

As of version 1.4.4 of the plugin, there is now a QUERY_DML_NO_SELECT type that should give you what you are expecting. Same as QUERY_DML, but doesn't log SELECT queries. (since version 1.4.4) (DO, ...
nextgentech's user avatar
3 votes
Accepted

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

Short Answer: None of those is "wrong". How are those settings used? There are (if I am not mistaken) exactly 2 uses for those two settings. They are, as alluded to in some of what you quoted: ...
Rick James's user avatar
  • 78.4k
3 votes

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

I had a similar problem but rectified it by adding the following line to /etc/mysql/my.cnf !include /etc/mysql/mariadb.cnf This seemed to fix the issue of the mariadb config file not being read.
Hermann von Bargen's user avatar
2 votes

Issues using pt-kill to kill log queries

Try this one: pt-kill --busy-time 15 --print -u root -p password --match-command Query --victims all Then add --kill-query if you think this one is what you intend to use. Edit Changed --match-all ...
jerichorivera's user avatar
2 votes
Accepted

mysqld_safe not run on RHEL7, while it runs on RHEL6 when starting mariadb

It is stated in the mariadb and even mysql docs: https://mariadb.com/kb/en/mariadb/systemd/ systemd services are packaged on RPM and Debian based Linux distributions. When systemd is used, ...
jerichorivera's user avatar
2 votes
Accepted

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

"If the NO_AUTO_CREATE_USER SQL_MODE is set, users can only be created with a CREATE USER statement. In this case GRANT will produce an error when the specified user does not exist." -- https://...
Rick James's user avatar
  • 78.4k
2 votes

MySQL Master-Slave replication foreign key constraint error

I see that you have used a multi-master configuration for m1 and m2 and a master-slave config for m1-s1 and m2-s2. Currently, the multi-master configuration does not support write actions on the ...
Vignesh Kothapalli's user avatar
2 votes

How to recover MariaDB Galera Cluster after full crash?

If you are in the position where your seqno is -1, and you need to bootstrap that node, you can recovery the correct seqno by running: # galera_recovery Which will output the correct seqno plus it ...
ctrlbrk's user avatar
  • 121
2 votes

MySQL Instance Crashing on Selecting From a DB

It mean You have a problem with InnoDB table You can check few methods for repair: for all cases - stop MySQL, backup data folder run mysqlcheck command line utility if it not fix add tomy.ini row ...
a_vlad's user avatar
  • 3,655
2 votes

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

I faced the similar issue, I fixed it by the following steps: Uninstalled the MySQL server. Removed data directory, log directory and configuration files. Killed all the running mysqld process. ...
Rathish Kumar B's user avatar
2 votes
Accepted

InnoDB Write Log efficiency above 100%

That seems to be computed from abs(Innodb_log_write_requests - Innodb_log_writes) hits / Innodb_log_write_requests total where the values come from SHOW GLOBAL STATUS. "..._write_requests" ...
Rick James's user avatar
  • 78.4k
2 votes

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

Check your config files /etc/my.cnf and /etc/my.cnf.d/server.cnf and look for the socket parameter. Make sure it's the same for client and server. A good place for the socket parameter is in the [...
dbdemon's user avatar
  • 6,371

Only top scored, non community-wiki answers of a minimum length are eligible