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… Continue reading Ignore Foreign Key Constraints During Truncate or Drop
Tag: mysql
Create new MySQL user and allow them to connect from any IP
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… Continue reading Create new MySQL user and allow them to connect from any IP
MySQL See all columns and tables that foreign key to a table or column
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. 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
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. require “mysql2” client_mysql = Mysql2::Client.new(:host =>; “localhost”, :username =>; “my_user”, :password… Continue reading Ruby MySQL Transaction Example
Add MySQL User | Give them Permissions On a Database
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… Continue reading Add MySQL User | Give them Permissions On a Database