Not the sexiest blog title in the world but I thought I’d knock up a little post on the behaviour of MySQL and SQL Server with integer subtraction. How would you expect a database system to behave with positive and negative data types? Microsoft SQL Server doesn’t really have unsigned data types. All integer types [...]

I usually use SQLYog to write any stored procedures for MySQL. Whenever you alter a procedure the editor essentially generates SQL to drop and then recreate it. ?View Code MYSQLDELIMITER $$   USE `db`$$   DROP PROCEDURE IF EXISTS `my_proc`$$   CREATE DEFINER=`root`@`%` PROCEDURE `my proc`() MODIFIES SQL DATA SQL SECURITY INVOKER COMMENT ‘Just an [...]

Colleague: “Can you have foreign keys referencing other databases?” Me: “Erm… I don’t know, but it I’ll find out.” I’ve always thought tables with these relationships should exist in the same database so I’ve never attempted this. But I don’t like not knowing things so I set to find out. The answer, as it often [...]

I’ve been building utilities with PHP and MySQL command-line tools to clone databases. I ran into an issue when exporting data from multi-gigabyte tables using the mysql client program. mysql: Out of memory (Needed 4179968 bytes) ERROR 2008 (HY000) at line 1: MySQL client ran out of memory The fix for this is easy; just use [...]

It’s reasonably easy to run multiple instances of MySQL with the mysqld_multi bash script. This can be really useful in development environments where you need to give several developers their own instance. To install multiple Microsoft SQL Server instances we have to get the install DVD and go through a laborious series of wizards. MySQL [...]

I ran across this error today whilst upgrading to an instance of MySQL 5.4. Unknown table engine ‘InnoDB’‏ I executed the following command at the MySQL client to see the available storage engines. ?View Code MYSQLSHOW ENGINES; This only listed the following table types; MRG_MYISAM MyISAM BLACKHOLE CSV MEMORY FEDERATED ARCHIVE Sure enough, No Innodb [...]

I’ve recently needed to script out the create sql for various MySQL database objects. No Powershell or SMO to help with this so I’ve quickly rolled a PHP script to get this done. This will script out all tables, views, triggers, stored procedures and functions from the specified database. One file per object in your [...]

Now I’m back working with MySQL on Linux I’m starting to learn Bash scripting to automate various tasks. Here’s a very simple script demonstrating how to interact with MySQL from Bash. Just set the localhost, user and pwd (password) variables to something appropriate for the MySQL server you want to query. The script will use [...]

Getting data out of MySQL with SSIS is a snap. Putting data into MySQL has been a different matter. I’ve always done this in the past with a hodgepodge of ODBC, Linked Servers, OPENQUERY and the Script Task. All of these work well but they’re just not as convenient as loading files with the OLED [...]

Things have been rather quiet on this blog lately as I’ve just been finalizing the details for my new role as a MySQL DBA \ Developer. This may seem a little odd, considering TSQL is mentioned in my blog url, but I frequently blog about MySQL and have worked with it previously in a professional [...]