30 votes

How to grant super privilege to the user?

I had the exact same problem. What I did was: GRANT SUPER ON *.* TO user1@localhost And problem solved. CAVEAT: Is there any danger in granting SUPER privileges to a user?
carla's user avatar
  • 593
26 votes
Accepted

Why isn't INFORMATION_SCHEMA displayed in the MySQL Workbench UI by default?

Click on Edit -> Preferences -> SQL Editor and then check the box "Show Metadata and Internal Schemas" Click on Refresh icon on the right side of Schemas in the left side bar. I am using 6....
Allen King's user avatar
23 votes

MySQL Workbench: "ssl is required but the server doesn't support it"

At least in MySQL Workbench community edition 8.0.27 no Use SSL option is not available. You could define useSSL parameter described in previous answer, in the Advance tab:
Antonio Maria Sanchez Berrocal's user avatar
21 votes
Accepted

MySQL Workbench: "ssl is required but the server doesn't support it"

Configure MySQL Workbench to not require SSL encryption If you get the MySQL Workbench error of "SSL connection error: SSL is required but the server doesn't support it" then you likely just ...
IT Thug Ninja's user avatar
17 votes

MySQL Workbench: "ssl is required but the server doesn't support it"

You can bring back the old SSL options in 8.0.27: Close MySQL Workbench Go to you MySQL Workbench install folder (p.e. C:\Program Files\MySQL\MySQL Workbench 8.0 CE) open the file modules/data/...
fehrlich's user avatar
  • 273
14 votes

How to make MySQL table name case insensitive in Ubuntu?

If you change lower_case_table_names in a DB with existing tables Stack Overflow: MySQL > Table doesn't exist. But it does (or it should) can happen. The comment to this answer helped me in this ...
Gerold Broser's user avatar
13 votes
Accepted

Mysql Workbench crashing when connecting

Looks like a known issue with it not handling an exception error properly and also the wrong version: https://bugs.mysql.com/bug.php?id=79364 Description: WB 6.3.5 (and also older release like 6.1.7) ...
Ali Razeghi - AWS's user avatar
10 votes
Accepted

Delete Slow Query Log File

As of 5.5.3, FLUSH LOGS will close and reopen the slowlog. (In old versions, FLUSH had no effect on the slowlog.) So, on *nix OS, this should work without restarting the server: rm (to delete) or ...
Rick James's user avatar
  • 77.6k
8 votes

What is the difference between threads connected and threads running

Threads connected means the total number of client processes (threads) connected to the database server. This includes the count for threads running. Thread running means the total number of client ...
Robert's user avatar
  • 121
8 votes
Accepted

InnoDB: What does Data free mean?

What does "Data_free" mean? Not much. Seriously, there are several areas in a table where there is "free space"; Data_free refers to only one of them: An InnoDB block is exactly 16KB; there is ...
Rick James's user avatar
  • 77.6k
7 votes

Check (health) status of MySQL schema table

To monitor your database's health in overall you could simply use: mysqlcheck --all-databases --auto-repair or on weekly basis (if you prefer, due to optimize option resource usage) mysqlcheck --...
Rogerlr's user avatar
  • 441
7 votes
Accepted

Normalizing/combing mutliple tables with similar data

Functional dependencies and normalization In order to carry out a normalization exercise involving second and further normal forms —as per the relational model of data by Dr. E. F Codd—, one first has ...
MDCCL's user avatar
  • 8,500
6 votes

How to search for a specific column name in all the tables in MySQL Workbench?

In MySQL Workbench (v6.3) (Windows): Right-click any table. Left-click "Table Maintenance ..." after a delay... Left-click "Columns" tab. This shows a sortable grid of Table, Column, ... Left-...
Andrew Dennison's user avatar
6 votes

How to make MySQL table name case insensitive in Ubuntu?

This problem was causing pain for me, where Doctrine generated capital/CamelCase table names and MySQL stored them as lowercase! It was solved by changing my.cnf and adding lower_case_table_names = ...
Doried Abd-Allah's user avatar
6 votes
Accepted

I need to change the format of 'date' from yyyy-dd-mm to mm-dd-yyyy

If your column date_ordered is of type DATE, MySQL will store it in its own way. To display it in the format you want, you should use the function date_format(): https://dev.mysql.com/doc/refman/5.5/...
Andrew Brennan's user avatar
6 votes

What is an equivalent of EXCEPT(in PostgreSQL) in MySQL?

You can use a NOT EXISTS predicate. Assume you want to mimic: select a.c1, a.c2 from a except select b.c1, b.c2 from b This can be expressed as: select distinct a.c1, a.c2 from a where not exists (...
Lennart - Slava Ukraini's user avatar
6 votes

MySQL Workbench: "ssl is required but the server doesn't support it"

In workbench 8.0.27 if under the SSL tab you don't see the option No, edit file $HOME/.mysql/workbench/connections.xml, look for the connection that you want to disable ssl, and find <value type=&...
ManKino's user avatar
  • 61
6 votes

INSERT gives Error Code: 1366. Incorrect string value: '\xF0\x9F\x98\x80' for column

The problem appears to be in the Client. When the client connects to the MySQL server, it needs to announce that the bytes in the client are utf8mb4. This can be done in several ways: In the ...
Rick James's user avatar
  • 77.6k
6 votes

Unable to connect to remote server, error message references "localhost"

I've just run into this issue myself. In my case, the version of the MySQL Server (5.1) is too old for MySQL Workbench (8.0) to connect to and results in the completely misleading error 'Unable to ...
Jason Musgrove's user avatar
5 votes

Visual Explain Not working in MySQL Workbench

The user raising this question got an answer in the MySQL Workbench Forum: Visual Explain is only available if your server supports the JSON explain output (MySQL 5.6.5 or newer). The reason ...
wbob's user avatar
  • 151
5 votes

How to select mutiple values with like clause

This case is a good candidate for using REGEXP instead of LIKE. SELECT * from table WHERE column REGEXP '^(AAA|BBB|CCC)' REGEXP is case insensitive. For case sensitive searches use REGEXP BINARY. ...
Ezequiel Tolnay's user avatar
5 votes
Accepted

Searching all database schemas - how to use wildcards?

You can try this: SELECT table_schema, table_name, column_name, data_type, ordinal_position FROM INFORMATION_SCHEMA.COLUMNS WHERE column_name LIKE 'activated_a%' ; When you want to use wildcard, you ...
Filip Holub's user avatar
5 votes

MySQL Workbench: "ssl is required but the server doesn't support it"

add useSSL=0 in advanced tab this worked for me in win 10
Mo Zakaria's user avatar
5 votes
Accepted

MySQL Workbench, Windows Server 2019, MySQL not showing Performance, Information of MySQL Schemas!

A similar issue, Information_Schema and mysql databases not shown, was submitted as a bug report to the MySQL Bug System nine years ago. The report was resolved as Not a Bug as the issue turned out to ...
Andriy M's user avatar
  • 22.9k
4 votes

How to grant super privilege to the user?

"Access denied" error during the import process might be due to the privileges mismatch for the DEFINERs between the database exported and the database going to be imported. I faced the same situation ...
Muniraj's user avatar
  • 141
4 votes
Accepted

Key column 'FK_UserQuestion' doesn't exist in table error

Your foreign keys have the wrong format. The syntax should be: FOREIGN KEY <name> (column_1, ..., column_n) REFERENCES <parent table> (column_1, ..., columns_n) In your case: FOREIGN ...
Lennart - Slava Ukraini's user avatar
4 votes

Searching all database schemas - how to use wildcards?

You need to use LIKE instead of =. So, something like this: SELECT table_schema, table_name, column_name, data_type, ordinal_position FROM INFORMATION_SCHEMA.COLUMNS WHERE column_name LIKE '...
Hannah Vernon's user avatar
  • 70k
4 votes
Accepted

row n was truncated; it contained more data than there were input columns: only some rows

Try opening your file with a text editor, and going to the 66th line (header + 65). You'll see the movie_title is: "The Chronicles of Narnia: The Lion, the Witch and the Wardrobe " This title ...
RDFozz's user avatar
  • 11.6k
4 votes

How do I test if MySQL "require secure transport" is working?

Just try to connect in a non-secure way and be sure it doesn't work. If you are not sure wether you are using SSL or not to connect, then yes, the simplest way is using the command line client: ...
Federico Razzoli's user avatar
4 votes
Accepted

getting innodb buffer usage

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_free' and SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_total' will enable you to derive this percentage. Keep in mind a page_free of 0 isn't ...
danblack's user avatar
  • 7,699

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