If you are trying to drop/delete or empty/truncate a mysql table and you keep getting an error that you are violating a foreign key constraint, you may want to temporarily ignore that constraint. The code below demonstrates how you can tell mysql to ignore foreign key checks, delete/truncate the tables you need to, then re-enable foreign key checks
SET FOREIGN_KEY_CHECKS = 0;
SET FOREIGN_KEY_CHECKS = 1;
If you are getting the error: user is not allowed to connect to this MySQL serverConnection closed by foreign host
Or : Access denied for user ‘root’@’somehost.com’ (using password: YES))
Then you probably have not created your user properly in MySQL
Here is how to create a new MySQL user and give him access to connect to the database from any location (local host or remote host).
Execute the following 4 queries – probably easiest to just run them from within the SQL section of phpMyAdmin (use the same username and password in queries 2 and 4 – these are the username and password you want to give to the new user you are creating)
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;
(note, make sure you have told MySQL to listen for connections from all hosts and not just localhost)
If you’re trying to understand how an existing database has been setup it can be very useful to see all of the tables/columns that foreign key into some other table/column.
REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from KEY_COLUMN_USAGE where
REFERENCED_TABLE_NAME = 'your_table_name'
AND REFERENCED_COLUMN_NAME = 'your_column_name'
Here is a simple tutorial showing how to implement transactions in Ruby with MySQL.
The code uses The MySQL2 Gem. We are showing the insert of 2 records into the customers table of a database. If either insert fails, both will be rolled back.
client_mysql = Mysql2::Client.new(:host =>; "localhost", :username =>; "my_user", :password =>; "my_password")
mysql_query_insert_customer1 = 'INSERT INTO MyDatabase.CustomersTable (name, phoneNumber, address) VALUES ("FSB", "888-555-5555", "322 Firstsearch Way")'
result_insert_mysql = client_mysql.query mysql_query_insert_customer1
mysql_query_insert_customer2 = 'INSERT INTO MyDatabase.CustomersTable (name, phoneNumber, address) VALUES ("FSG", "887-555-5555", "322 Firstsearch Road")'
result_insert_mysql = client_mysql.query mysql_query_insert_customer2
rescue Exception => e
puts "+++++++ DB ERROR - ROLLING BACK ++++++++"
puts e #print the MySQL Error
client_mysql.query('rollback') #rollback both of our insert statements
exit #exit the script/app (you might want a softer touch)
client_mysql.query('commit') #commit the changes to the DB
This simple guide demonstrates adding a new user to mysql and giving that user full access rights to a particular db within MySQL. This is really useful when you want to create a new database user for a new wordpress isntall.
You can do this from the command line of your server if you login with:
mysql -u root -p
Or you can do it from within phpmyadmin (phpmyadmin is probably the best option if you are using a web hosting account)
Before you create the new user, you will want to create a new database (you can do that from the main page of phpmyadmin)
Use the following 2 lines to create a new user named sammy with password strong_password and give him full access rights for the database called MY_DB
CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON MY_DB.* TO 'sammy'@'localhost' WITH GRANT OPTION;