Posts tagged MySQL
A buddy at work sent out the following link which proved to be an interesting read and should be read by any developer supporting a high traffic website.
I had a situation arise where I needed to insert data into a linking table where one column was made up of ids from one table, but the other column was set to a default value (1). Never did this type of query before so had a bit of a struggle with it, but finally figured it out.
INSERT INTO linking_book_category (book_id, category_id) SELECT book.id, 1 FROM books AS book;
Notice the 1 after SELECT book.id.
This morning I had an issue where my transactions weren’t being committed and needed to figure out why. When Googling for a way to see what transactions were conducted, but not active I came across a great ‘Show INNODB Status’ post from MySQL Performance Blog.
If you have the correct admin privileges; log into your MySQL DB server and type the following:
show innodb status
You will be presented a wealth of information, including transaction history (or snapshot if there are many). The aforementioned link will give you a section by section explanation, which proved to be quite helpful in debugging my specific issue.
Add a column with no regard for order of entry among other columns:
ALTER TABLE `table_name` ADD COLUMN `column_name` int(11) unsigned not null default 0;
Add a column, but specify that it should be the first column of the table:
ALTER TABLE `table_name` ADD COLUMN `column_name` int(11) unsigned not null default 0 FIRST;
Add a column, but specify it's location other than first:
ALTER TABLE `table_name` ADD COLUMN `column_name` int(11) unsigned not null default 0 AFTER `some_other_column`;
MySQL provides a way to get a snapshot of your schema via mysqldump. Care needs to be taken though, if your tables are large you are better off with a SELECT INTO OUTFILE, or implement replication to a low traffic server because the mysqldump command will lock your tables for as long as it takes to grab the data.
mysqldump -u <user_name> -h <db_host> --opt <database_name> -d --single-transaction > backup.sql
The -d flag tells mysqldump that you don’t want any data, only the schema.
If you are only dumping schema and you wish to remove the auto_increment references from the resulting file, there is a how-to here: http://melikedev.com/2011/06/01/mysql-remove-auto_increment-from-schema-dumps-mysqldump/
Needed to do this to setup a MySQL server on my local machine to facilitate unit test testing… I used XAMPP and started the MySQL app. After it starts open your terminal and type the following:
mysql -u root
mysql: GRANT ALL ON *.* TO '<some_user>'@'%' IDENTIFIED BY '<some_password>' mysql: FLUSH PRIVILEGES; mysql: EXIT
mysql -u <some_user> -p<some_password>
You should now be able to connect to your local mysql server from any host using the supplied name and password.