Ignore Foreign Key Constraints During Truncate or Drop

mysql

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;
TRUNCATE MY_TABLE;
DROP HIS_TABLE;
SET FOREIGN_KEY_CHECKS = 1;

Create new MySQL user and allow them to connect from any IP

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

MySQL See all columns and tables that foreign key to a table or column

mysql foreign keyIf 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.

use INFORMATION_SCHEMA;
select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from KEY_COLUMN_USAGE where
REFERENCED_TABLE_NAME = 'your_table_name'
AND REFERENCED_COLUMN_NAME = 'your_column_name'

Ruby MySQL Transaction Example

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

require "mysql2"
client_mysql = Mysql2::Client.new(:host =>; "localhost", :username =>; "my_user", :password =>; "my_password")
client_mysql.query('begin')
begin
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)
end
client_mysql.query('commit') #commit the changes to the DB
client_mysql.close

Add MySQL User | Give them Permissions On a Database

create mysql user
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;