Hot answers tagged

17

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


13

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


11

You will get this message if you're trying to add a constraint with a name that's already used somewhere else. According to this post you can check your constraint like this: If the table you're trying to create includes a foreign key constraint, and you've provided your own name for that constraint, remember that it must be unique within the database. Run ...


9

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


9

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


8

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


8

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


8

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


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.


7

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


7

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


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.


6

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


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

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


5

Yes, it could be normal. With a "large table", you are likely needing to do a lot of I/O (unless you're using InnoDB and you've got a huge buffer pool allocated.) Unless you're paying for Amazon Provisioned IOPS, your I/O rate is going to be somewhat constrained on shared volumes. http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html ...


5

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


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.


5

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


5

The icon shows that that side of the relationship is not mandatory, meaning the field can be NULL. You can change that if you click on the relationship line, go to Foreign Key tab and select the Mandatory check box.


5

To be frank, if you are using a GUI tool as your backup strategy, you are doing it wrong. Backups need to be scripted, automated, and regularly tested or else you might as well not have them. If your database is very small (a few gigabytes), there is no point in doing incremental backups: backup the entire database with mysqldump and use binary logs in ...


5

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


5

You many need to take a look at the comprehensive list of all Server Variables. Some of them you can change for the duration of your session, some you may change with the SET GLOBAL command, and other options may require a restart of MySQL. Here is the list of all options. This will also show which options can be used in option files.


5

Believe it or not, although the products went EOL December 18, 2009, the last versions of MySQL Query Browser and MySQL Administrator are still available for download for free. Hey, I still use them today. Click here and get it while Oracle isn't looking ... QUICK !!!


4

You will need to give root@localhost a password Since you just installed mysql, you are running with default settings, this means you have no my.cnf. You will have to create one. Run the following at a DIS Prompt cd C:\Program Files\MySQL\MySQL Server 5.5 dir *.ini There, you will see sample .ini files. Let's pick my-small.ini net stop mysql cd ...


4

http://code-remind.blogspot.com.au/2011/12/on-macbookpro-10.html NOTE: Where the author suggests using the path '/Applications/MAMP/bin/stoptMysql.sh' at the end... there's a typo. Should be '/Applications/MAMP/bin/stopMysql.sh'


4

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


4

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


4

What idexes do you have on this table? If the dbms engine is doing a full table scan, it will completely load a cpu and take a long time. If you have a 2 cpu system and all your data is in RAM, then you'll see 50% cpu usage. Please create and index, such as CREATE INDEX BlueBoxEngineDB.TransferIdx1 ON BlueBoxEngineDB.Transfer(Amount,FromUserId,Status); ...


4

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



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