61

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


44

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 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

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.


13

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 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 ...


12

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 ...


10

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.


10

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 ...


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

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 =...


7

You don't state what OS you are using, but under Linux/OSX and other Unix-a-likes you can use the mysql command line tools with the pretty standard watch utility to do this. Something like: watch -n 10 'mysql --database=dbname --user=username --password=$(cat /path/to/file/with/mysql/password) --execute="select *, (select count(*) from domains) as count, (...


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 ...


7

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 processes (threads) currently executing on the database server. The server is holding these connections while the client is waiting for a reply. These thread may ...


7

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 almost always some wasted space in a block. A block in a BTree is normally between 50% and 100% full. Groups of blocks ("extents") are allocated in chunks of 8MB(?)....


7

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 ...


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

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 = 1 under the [mysqld] section my.cnf can be found: under LAMPP/XAMPP... : /opt/lampp/etc/my.cnf stand alone mysql server : /etc/mysql/my.cnf Afterwards ...


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

What you need is a better visual representation of the InnoDB Storage Engine Think about the kind of things that trigger writes that can be monitored Number of Write Operations Innodb_dblwr_writes to the Double Write Buffer Redo Logs Innodb_os_log_writes and Innodb_os_log_pending_writes : Logical Innodb_os_log_fsyncs and Innodb_os_log_pending_fsyncs : ...


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

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/en/date-and-time-functions.html#function_date-format e.g. For DD-MM-YYYY format, you want to use the format string '%d-%m-%Y' select date_format (...


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

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) can connect to MariaDB. > How to repeat: > 1. Download WB 6.3.5 CE 64 bit (32 bit has the same issue) > 2. Download MariaDB 10.0.22 64 bit windows &...


6

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 --all-databases --auto-repair --optimize To verify tables: mysql \u mydb CHECK TABLE mytable FAST QUICK; The first command will check the integrity for all ...


5

I recommend to do this migration in two steps. Use MySQL Workbench to migrate most of the db objects and data to MySQL (table data will be copied by this process too). See Using the MySQL Workbench Migration Wizard for details. This will leave you with manual work for specific objects like stored procedures. No tool can do an automatic migration as stored ...


5

MySQL doesn't support true booleans as per standard SQL. It uses integer values 1 and 0 respectively for true and false. It does support the keywords true and false, but these are mapped to the integer values. mysql> SELECT true, false; +------+-------+ | TRUE | FALSE | +------+-------+ | 1 | 0 | +------+-------+ They really are mapped to ...


5

The maintenance feature is a bit hidden in MySQL Workbench. You can reach it by opening your connection as usual and right clicking on your schema to open the schema inspector: You will get a new tab in the editor area with many pages for inspection/maintenance. Click on the Maintenance button at the bottom to open this page: From there it should be clear ...


5

There's no perfect layout tool. What you get is the best you can get automatically. After that fine tune your layout manually. Btw, make sure you have enough space for all objects by adding more pages if necessary (see menu -> Model -> Diagram Properties and Size...) before you run the auto layout.


5

That's actually a bug. MySQL Workbench saves the tabs on close of the connection. To get rid of the unwanted tabs you could temporarily switch off the snapshot feature: If you like to get that bug fixed file a bug report at http://bugs.mysql.com.


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