MySQL Fatal Error Maximum Execution Time Exceeded

I was trying to backup a database containing +/- 2,2 millions record from one host (Ubuntu 7.10 running MySQL 5.0.45) and restore to another host (Windows XP running MySQL 5.0.0alpha).

I ran "mysqldump -user=wahyu -p aida32wyu > aida32wyu.sql", where aida32wyu being the database name, copy the result (aida32wyu.sql) to target machine and execute "source d:\aida32wyu.sql" from mysql console just to get "Fatal Error: Maximum execution time 300 seconds exceeded".

I thought something was wrong with the mysql in the target machine, like too short execution time or whatever option it has right inside my.cnf or elsewhere. But I found none of them. Googling also yielded things people refer to as problem inherent with PHP setting itself, but I didn't use any PHP so it must have been something else.

Still, I insisted the problem was on the restoring process, until I found out that the restoring process always halt after the same records and finally confirmed by using "tail aida32wyu.sql" that it was the mysqldump containing exactly the line "Fatal Error ..." that breaks the SQL syntax.

Then I focused on mysqldump. I wanted to know if it has some command-line options to indicate maximum execution time but none found. An interesting thing happen suddenly when I figured out from the "mysqldump --help" that we should disable --opt option (by --skip-opt) to dump SQL to be restored to older version of MySQL.

Voila! I ran
"mysqldump --skip-opt --port=3306 -p user=wahyu -v aida32wyu > aida32wyu.sql"
and didn't find any "Fatal Error" anywhere in the result. Restoring the SQL to my target machine was normal. No sweat.

Modifying File Security Context

When you are Fedora user like me, and occasionaly download some scripts to try on your own web server, make sure you have changed the security context of the files to "httpd_sys_content_t" by applying:
chcon -R -h -t httpd_sys_content_t *
after you copy to your webserver directory, otherwise you will get "Forbidden Access" result.

Importing SQL Server CSV to MySQL

I have another situation here. I want to import SQL Server data (SQL Server 2000 running onWindows 2003), a subset of it, into MySQL database (v5.0.22 running on Fedora Core 5). I have phpMyAdmin 2.9.1-rc2 up and running on my Linux box. This is how I get it done:

1. Run the desired query using SQL Server Query Analyzer and wait until the result show up in the data grid.
2. Right-click on the data grid, select "Select All", and then "Save As". A "Save Grid Results" dialog box appears.
3. Choose the file name you want, the file type ("Export Files (.CSV)"), the file format ("ANSI"), and the column delimiter ("Comma Separated (CSV)") then click save. You have to save it anywhere accessible from your phpMyAdmin.
   
4. Login to phpMyAdmin, create the target database if not exists yet, and create the target table within that database. Make sure the destination table has as many columns using the proper data types as the data being exported. Otherwise I do not guarantee it will work.
   
5. From the phpMyAdmin SQL menu make sure you have replace the ";" in the Delimiter input text with "," as we use comma separated value for each fields, and type this query:
   

   LOAD DATA INFILE '/path/to/file_name.CSV' INTO TABLE table_name
   FIELDS TERMINATED BY ',' ENCLOSED BY '"'
   LINES TERMINATED BY '\n';
   

6. Don't forget to click "Go" when you're finished and you're done with it.
   

MySQL to ColdFusion Date/DateTime Conversion

If you work with Coldfusion and MySQL database, and would like to retrieve and display data of date/datetime type, be sure to format it first with DateFormat() function.

For example: DateFormat(#date_vars#, "dd mmm yyyy") to make it like "23 Dec 2006".

Otherwise you would get output like this:
{ts 'the_date in yyyy-mm-dd hh:mm:ss'}

ByteArray objects cannot be converted to strings

Here's the situation.

I have a "tb_customer" table which has a column named "address" of type Text in a MySQL database. I use tag named "getCustomer" to invoke simple SELECT query from within Coldfusion page. When I list all query result within a construct, I got this error:

"ByteArray objects cannot be converted to strings."

when the variables #address# that contains the value of "address" column is processed. My first response to it was to look around and see what ByteArray object is in Coldfusion. Trying with no luck, I then thought it must have something to do with typecasting. Simply enclosing #address# variable as an argument of ToString function, like this one

#ToString(address)#

would do the trick.