Posted by Jeffrey Roberson
We just finished the updated layout programming for Sam Moore. They wanted the site to look more like the existing www.hookerfurniture.com parent site while adding updated meta data, social networking, fabrics, and product information. The site was originally in ASP/HTML and didn't match their new marketing. It's been completely redone with Coldfusion, mySQL, jQuery, and YUI grids.
Posted by Jeffrey Roberson
This post is more for my notes so I remember it, but once again Ben Nadel's blog saved the day.
My problem was similar, but I was getting "Data Truncated for Column" errors using MYSQL import. To stop this error, basically just let mysql truncate teh fields without throwing the error. Follow Ben's instructions for whichever version of the driver you are using. Except you want to add jdbcCompliantTruncation=false to the driver. If you have multiple parameters then you separate them with an &.
Posted by Jeffrey Roberson
I can never seem to remember the mySQL syntax for JOINED updates when I need them and the mysql documentation on their site doesn't help a whole lot. Then I always forget what the exact phase to google for. (Joined updates, multi-table updates, updating from one table to another, etc..) I found some great examples over on the electictoolbox that makes it dead simple.
So I am posting it here as a reminder later on , and in case someone else was wondering how to do it and couldn't find it.
Posted by Jeffrey Roberson
This is just a little note on settings the max allowed packet in mysql without rebooting it. I always forget how to do it when I neet to change it quickly for something.
set global max_allowed_packet = 32 * 1024 * 1024;
This will set it for 32 megs. Change the 32 to have it larger or smaller.
Posted by Jeffrey Roberson
I finally found the correct information today to figure out why some kinds of subqueries take forever to come back in mysql. If you are using a subquery with a MYSQL IN() like this:
select o.ack,o.product
from orders o
where o.ack in (
select distinct ack
from orders
where status in ('Shipped','Cancelled')
group by ack
having DATE_ADD( DATE_SUB( now( ) , INTERVAL DAYOFMONTH( now( ) ) - 1 DAY ) , INTERVAL -3 MONTH ) max(status_date)))
order by o.ack, o.line
It takes a few minutes to return a result. If you take the same query and reorder it to put the subquery in the from with a join it comes back almost instanty :
select o.ack,o.product
from orders o
inner join (select distinct ack
from orders
where status in ('Shipped','Cancelled')
group by ack
having DATE_ADD( DATE_SUB( now( ) , INTERVAL DAYOFMONTH( now( ) ) - 1 DAY ) , INTERVAL -3 MONTH ) > max(status_date)) old_o
on o.ack = old_o.ack
order by o.ack, o.line
It's interesting on MSSQL that the IN() subquery syntax is much faster.
Here's a link to the bug report:
bugs.mysql.com/bug.php ( Thanks Jeremy Pointer )