Hot answers tagged

355 votes
Accepted

Create a MySQL database with charset UTF-8

Update in 2019-10-29 As mentions by @Manuel Jordan in comments, utf8mb4_0900_ai_ci is the new default in MySQL 8.0, so the following is now again a better practice: CREATE DATABASE mydatabase ...
user avatar
  • 3,704
215 votes

'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

use --no-tablespaces see mysqldump-Documentation mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES if the --...
user avatar
  • 2,251
148 votes
Accepted

MySQL any way to import a huge (32 GB) sql dump faster?

Percona's Vadim Tkachenko made this fine Pictorial Representation of InnoDB You definitely need to change the following innodb_buffer_pool_size = 4G innodb_log_buffer_size = 256M ...
user avatar
128 votes
Accepted

Difference between On Delete Cascade & On Update Cascade in mysql

A very good thread on this subject is to be found here and also here. The definitive guide for MySQL is, of course, the documentation, to be found here. In the SQL 2003 standard there are 5 different ...
user avatar
96 votes
Accepted

How does ORDER BY FIELD() in MySQL work internally

For the record SELECT * FROM mytable WHERE id IN (1,2,3,4) ORDER BY FIELD(id,3,2,1,4); should work as well because you do not have to order the list in the WHERE clause As for how it works, FIELD()...
user avatar
92 votes

What is faster, one big query or many small queries?

What would address your question is the subject JOIN DECOMPOSITION. According to Page 209 of the Book You can decompose a join by running multiple single-table queries instead of a multitable join, ...
user avatar
90 votes

'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

tl;dr Breaking change introduced in minor MySQL update, use --no-tablespaces option in mysqldump from now on (recommended) or add the global PROCESS privilege to the user running the command. I ...
user avatar
80 votes

Create a MySQL database with charset UTF-8

You should use: CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; Note that utf8_general_ci is no longer recommended best practice. See the related Q & A: What's the ...
user avatar
78 votes
Accepted

Disable MySQL binary logging with log_bin variable

This is an old question, but it came up in a search while I was trying to jog my memory about the correct option name and so now that I've figured it out I'm adding the details here. The key part of ...
user avatar
77 votes
Accepted

How to check foreign keys related to a table

Firstly, find out your FOREIGN KEY constraint name in this way: SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, -- <<-- the one you want! REFERENCED_TABLE_NAME, ...
user avatar
66 votes
Accepted

What is the meaning of filtered in MySQL explain?

To filter here means to apply a condition on a set of rows that were selected by a type-search as potential rows, and to only keep rows that fulfill the condition: MySQL will first try to use an ...
user avatar
  • 1,051
64 votes

Cannot locally import UTF-8 encoded SQL database on Mac

Based on this blog post http://ilikekillnerds.com/2014/08/fixing-sequel-pro-sql-encoding-error-for-imported-sql-files/ When importing your database dump, you need to select Western (Mac OS Roman) ...
user avatar
  • 741
61 votes

mysql dump import incredibly slow on my developer's machine

This answer speeded up everything a lot: https://stackoverflow.com/a/2167641/292408 I simply SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0; at the beginning, and COMMIT; SET ...
user avatar
  • 1,083
61 votes

How to make MySQL table name case insensitive in Ubuntu?

Open terminal and edit /etc/mysql/my.cnf sudo nano /etc/mysql/my.cnf Underneath the [mysqld] section.add: lower_case_table_names = 1 Restart mysql sudo /etc/init.d/mysql restart Then check it ...
user avatar
60 votes

How to easily convert utf8 tables to utf8mb4 in MySQL 5.5

This solution will generate and then run queries needed to convert databases, tables and columns. It converts all columns of the type varchar, text, tinytext, mediumtext, longtext, char. You should ...
user avatar
56 votes
Accepted

EXISTS (SELECT 1 ...) vs EXISTS (SELECT * ...) One or the other?

No, there is no difference in efficiency between (NOT) EXISTS (SELECT 1 ...) and (NOT) EXISTS (SELECT * ...) in all major DBMS. I've often seen (NOT) EXISTS (SELECT NULL ...) being used as well. In ...
user avatar
50 votes
Accepted

Query to compare the structure of two tables in MySQL

TWO TABLES IN THE CURRENT DATABASE If you want to know if two tables are different, run this SELECT IF(COUNT(1)>0,'Differences','No Differences') Comparison FROM ( SELECT column_name,...
user avatar
48 votes
Accepted

MySQL - varchar length and performance

This is a very common "exam/interview question". I will answer as good as I can: In the standard row formats for InnoDB and MyISAM (dynamic/compact) a VARCHAR(50) and a VARCHAR(255) will store the ...
user avatar
  • 14.2k
46 votes

MySQL relay log corrupted, how do I fix it? Tried but failed

Error: Last_SQL_Errno: 1594 Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. This error message means that the copy of the MySQL replication log could not be read on ...
user avatar
  • 561
45 votes
Accepted

MySQL Count rows from another table for each record in table

You are just missing the GROUP BY YOUR QUERY WITH GROUP BY SELECT student.StudentID, student.`Name`, COUNT(attendance.AttendanceID) AS Total FROM student LEFT JOIN attendance ON student....
user avatar
44 votes
Accepted

How do I enforce a write-once then read only database table in SQL?

I see at least two ways of accomplishing this. The first approach is to not grant DELETE and UPDATE privileges on these write-once tables, or, for that matter, any privileges apart from INSERT and ...
user avatar
  • 22k
43 votes
Accepted

How can I mysqldump all databases except the mysql schema?

You need to collect all the database names into a space delimited list. Use that for mysqldump MYSQL_USER=root MYSQL_PASS=rootpassword MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}" # # Collect all ...
user avatar
41 votes
Accepted

mysql Lock wait timeout exceeded; try restarting transaction

You can set variable innodb_lock_wait_timeout=100 for lock time to 100 sec. mysql> set innodb_lock_wait_timeout=100; Query OK, 0 rows affected (0.02 sec) mysql> show variables like '...
user avatar
41 votes

What are the main differences between InnoDB and MyISAM?

A bit late to the game...but here's a quite comprehensive post I wrote a few months back, detailing the major differences between MYISAM and InnoDB. Grab a cuppa (and maybe a biscuit), and enjoy. The ...
user avatar
  • 893
40 votes

What is faster, one big query or many small queries?

In Postgres (and probably any RDBMS to a similar extent, MySQL to a lesser extent), fewer queries are almost always much faster. The overhead of parsing and planning multiple queries is already more ...
user avatar
39 votes

Why do we use Group by 1 and Group by 1,2,3 in SQL query?

This is actually a really bad thing to do IMHO, and it's not supported in most other database platforms. The reasons people do it: they're lazy - I don't know why people think their productivity is ...
user avatar
38 votes

Why query_cache_type is disabled by default start from MySQL 5.6?

You need the history of InnoDB to understand why. Here it goes: WAR STORY InnoDB and the query cache are in a constant state of war. InnoDB tends to be very heavy-handed when inspecting changes in the ...
user avatar
37 votes
Accepted

Why is the default page size for databases so small?

I'm running Linux (Fedora 34, 64 bit, two cores, four CPUs, 32 GB RAM - PostgreSQL 13.3). If I run stat -f some_random_file as follows: [pol@fedora inst]$ stat -f blah.txt File: "blah.txt" ...
user avatar
36 votes
Accepted

MySQL - Difference between using count(*) and information_schema.tables for counting rows

There are various ways to "count" rows in a table. What is best depends on the requirements (accuracy of the count, how often is performed, whether we need count of the whole table or with variable ...
user avatar
35 votes
Accepted

How do I resolve this error, "ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'"?

Simply run mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p $ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p Enter password: Warning: Unable to load '/usr/share/...
user avatar
  • 56.7k

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