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?
- 583
25
votes
Accepted
Why isn't INFORMATION_SCHEMA not 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....
- 420
22
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:
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 ...
- 2,248
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/...
- 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 ...
- 241
12
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) ...
- 7,468
10
votes
Missing "MSVCR120.dll" during the Installation of MySQL Workbench 6.3 CE
I am getting the same error and I installed using MSI.
Downloaded the VC redist packages for x64 and it fixed the issue:
Visual C++ Redistributable Packages for Visual Studio 2013
Strange that the ...
- 101
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 ...
- 74.8k
9
votes
Connect MySQL workbench with MySQL container
It looks like you ran the container without setting the -p 3306:3306 parameter. Also, it seems that you mistyped the IP address in of the Hostname in MySQL Workbench. I recommend starting from scratch ...
- 91
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 ...
- 121
7
votes
Accepted
Alter ON DELETE CASCADE in 1 table
The error indicates that you have invalid references in the parentId column.
This error shows when you are trying to insert a row with an invalid reference, update an existing row with an invalid ...
- 22.5k
7
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 ...
- 74.8k
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 ...
- 8,370
6
votes
MySQL unknown column 'password_last_changed'
From the 5.7.6 release notes:
If you upgrade to this release of MySQL from an earlier version, you
must run mysql_upgrade (and restart the server) to incorporate the
changes to the mysql ...
- 14.5k
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-...
- 161
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 = ...
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/...
- 2,078
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 (...
- 22.8k
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=&...
- 61
6
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 --...
- 421
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 ...
- 74.8k
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 ...
- 161
5
votes
Accepted
How to generate the E-R Diagram of Wordpress using mySQL Workbench
The EER diagram is correct. There should be no relationships because WordPress does not have any constraints. The EER diagram shown in the codex is only for reference (it's a dummy EER diagram). You ...
- 165
5
votes
Accepted
Correct 'lines terminated by' for query
See the mydata.csv file at bottom of the answer.
I created a table xy
CREATE TABLE `xy`
(
`fred` int(11) DEFAULT NULL,
`mary` int(11) DEFAULT NULL,
`billy` varchar(50) DEFAULT NULL
);
From ...
- 28.9k
5
votes
Is it possible to change ER diagram style in MySQL Workbench? How?
You can change the look of the diagrams a bit, evn though not exactly as shown in your sshot. Under the model menu there are 2 submenus for object and relationship notations:
The result looks then ...
- 1,543
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.
...
- 4,918
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 ...
- 169
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
- 51
4
votes
Accepted
Optimizing MySQL query with left joins
First, get rid of the subquery. It is not needed and it can interfere with optimization. Second, you don't need a left join, because the having clause is requiring matches.
SELECT p .*,
IF(...
- 2,222
Only top scored, non community-wiki answers of a minimum length are eligible