belle’s sql musings

Generating Random Data for SQL Server and MySQL

Posted in DBA Toolbox / T-SQL Scripts, MySQL, T-SQL Tips and Tricks by belle on October 5, 2008

SQL Server – uses NEWID() in ORDER BY

   1: -- using the AdventureWorks Database
   2: SELECT
   3:     TOP 10
   4:     DepartmentID,
   5:     [Name]
   6: FROM
   7:     HumanResources.Department
   8: ORDER BY
   9:     NEWID()
  10:

MySQL – uses RAND() in ORDER BY

   1: SELECT
   2:   `Code`,
   3:   `Name`
   4: FROM
   5:   `Country`
   6: ORDER BY
   7:   RAND()
   8: LIMIT 10;
Tagged with:

MySQL EXPLAIN Cheat Sheet

Posted in MySQL by belle on August 19, 2008

Came across this handy cheat sheet for MySQL EXPLAIN:

http://www.beberlei.de/mysql_explain.html

EXPLAIN is a clause in MySQL that explains how a SELECT statement will be executed, and allows one to determine whether the indexing scheme is effective or not.

Tagged with:

Indispensible MySQL Resources

Posted in MySQL by belle on July 14, 2008

Doug Bromley of Straw Dogs have posted 20 Indispensible MySQL Resources, which include

  • SQLYog
  • phpMyAdmin

In addition, the following tools are in my MySQL toolbox if I am wearing my MySQL DBA hat on:

Tagged with:

How to increase an InnoDB log file size

Posted in MySQL by belle on November 21, 2007

(MySQL 5.0)
Had a question from a student:

Problem:
He is trying to increase the innodb log file size by altering his .ini file.
After he makes the changes to the .ini file, he attempts to restart the server but fails.

Error messages indicate:
-ib_logfile0 has a different size
-can’t initialize database

Solution:
The solution was posted by Jay Pipes in the MySQL forum (http://forums.mysql.com/read.php?22,32004,32014#msg-32014). Essentially MySQL tries to look for the log file that had the original size, doesn’t find it, aborts the restart.

Solution is to rename the log files. MySQL will recreate the appropriate log files with the appropriate sizes.

#assuming you have a Linux system and you installed MySQL using an RPM#otherwise your MySQL install might be in /usr/local#if you can't figure out where your install folder it, "find" is your friend $/etc/init.d/mysql stop$mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak$mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak$/etc/init.d/mysql start$