49

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 here: mysqladmin -u root -p variables


32

In a politically correct sense, what you just asked for is impossible. Why ? The SUPER privilege is a global privilege, not a database level privilege. When you created the user with grant all privileges on db1.* to user1@'%' with grant option; you populated the table mysql.user with user=user1 and host='%'. All other columns (global privileges) were ...


27

I stumbled upon this question when I myself had encountered this error. I could finally figure out the configuration. I didn't touch anything in /etc/mysql/my.cnf which already has bind_address = 127.0.0.1. So only localhost can connect. I use OpenSSH server. So in its config file /etc/ssh/sshd_config I changed from no to yes the param responsible for TCP ...


22

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?


21

You can use the INFORMATION_SCHEMA database and the COLUMNS table in particular Example of use: SELECT table_name, column_name, data_type, ordinal_position FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = 'myDatabase' --- the database you want to search AND column_name = 'name' ; --- or: column_name LIKE '%name%'


16

SET @given_id = 1; SELECT name FROM table1 WHERE id = @given_id ;


12

I have some queries you can run against the INFORMATION_SCHEMA Run this to get the Total MySQL Data and Index Usage By Storage Engine SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ', SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE( FORMAT(B.ISize/POWER(1024,pw),3),',','...


9

BOOL and BOOLEAN are synonyms of TINYINT in MySQL. See MySQL docs: Numeric Type Overview You probably want to use the BIT datatype.


9

If you change lower_case_table_names in a DB with existing tables MySQL > Table doesn't exist. But it does (or it should) can happen. The comment to this answer helped me in this case: I reverted the value, restarted the database, exported the tables, set the value back to 1, restarted the database, re-imported the tables and everything worked again.


9

Assuming that your dataset is larger than your buffer pool, having it at 95% usage is not only normal, but a desired state. You want as much information as possible on memory- hardware and resources are there to be used- so that both next reads and writes can be done faster than having to access disk. A different thing is if that memory usage is being used ...


9

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 error message has python.exe as it's title.


9

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 by following these instructions: Run the mysql server. (Change the password admin to anything you want) docker run -e MYSQL_ROOT_PASSWORD=admin --name mysql -...


8

I think the TCP/IP over SSH approach works by establishing a "normal" SSH connection underlying the MySQL connection (in the same way as you would tunnel using -L with the OpenSSH command-line client). Therefore, you'd need to specify a connection to an SSH server on server via which you're establishing the tunnel. Here, you seem to be using mysql.myhost....


8

It is a very poor practice to expect to maintain the PK/FK relationships from the application. In any db that is nontrivial, data has close to a 100% chance of being changed from other sources including ad hoc queries, data imports, etc. It is irresponsible to think the data in the database is protected because the application has protections. Sure you think ...


8

After you create a connection to your database, execute the following two commands: USE <DATABASE NAME>; SELECT * FROM <TABLE NAME>; Then MySQL Workbench will show another pane with the results. This will be the entire contents of the table.


8

To expand on @ypercube's answer (He gets a +1), if you do not know which database the table resides, do this: SELECT table_schema, table_name, column_name, data_type, ordinal_position FROM INFORMATION_SCHEMA.COLUMNS WHERE column_name = 'name' ; --- or: column_name LIKE '%name%'


8

See File -> Export -> Forward Engineer SQL Create Script...


8

Try temporary disabling foreign keys (make sure no ones allowed to update the db meanwhile): create table t1 (id int not null primary key) engine = innodb; create table t2 (id int not null primary key ,t1_id int not null , constraint abc foreign key (t1_id) references t1 (id) ) engine =...


8

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.3CE. Older versions may have something similar.


7

You may need to check the users in the mysql.user table. Run this query: SELECT user,host FROM mysql.user; You should see something like this: mysql> SELECT user,host,password FROM mysql.user; +------------------+-------------+-------------------------------------------+ | user | host | password | +--...


7

MySQL Workbench does not have the capability to "infer" the relationships from table column names, or MyISAM tables. It requires that the constraints be defined in the database or SQL script being reverse engineered.


7

Thanks Mika, I tried your solution in workbench (5.2.41). It worked after I cleared the passwords in the vault (click Manage Connections) and restarted the WorkBench. To clear passwords stored in the vault, I did the following: Open MySQL WorkBench the you will see the home page Click Manage Connection at bottom left corner. In Manage DB Connections, click ...


7

From the MySQL Workbench 5.2 window, select Database -> Synchronize model which will allow you to synchronize the model (EER diagram) with the database (you need to create a saved connection) for it. I recommend this method over just reverse engineering the model from the database since it maintains the layout of the EER diagram. One caveat is that you ...


7

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 reference, or delete a row that is referenced. You also get it when you are trying to create a foreign key constraint on a column that is already populated and ...


6

If you just want to see the data in a table then do a select for that table in a query editor. That's the most basic operation wrt SQL. Using the table search with some "mysterious query" certainly does not help to accomplish that task. Search is, as the name implies, a search. Put the value you are looking for in the search field and let MySQL Workbench do ...


6

You can take a backup from the live MySQL with Xtrabackup. Percona provides deb repository for Ubuntu. To install the repo for Ubuntu 12.04 follow instructions: Install the key: # apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A Add the source file: # cat /etc/apt/sources.list.d/percona.list deb http://repo.percona.com/apt precise ...


6

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 database. mysql_upgrade moves Password column values to the authentication_string column and removes the Password column. For nonupgraded installations that have ...


6

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 mv the slowlog to another name. (Note: mysqld will continue to write to the file, even though you changed the name.) FLUSH LOGS;. As of 5.5.3, you can limit ...


6

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 ( select 1 from b where b.c1 = a.c1 and b.c2 = a.c2 ) Other options are to use a left join and check for null: select distinct a.c1, a.c2 from ...


6

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 to know what are the relevant functional dependencies (FDs for brevity) between the attributes (usually depicted as columns) of an adapted mathematical ...


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