Skellywag

Friday, October 07, 2005

MySQL truncat [es data]

I really want to like MySQL. I really do. It's an integral part of the LAMP stack, and it now has Stored Procedures! and ACID!

But wait...

Although you can copy a DB from one machine to another with ease (just copy the files), there is a sneaky problem, at least when moving from MySQL 3.x to 4.x: all VARCHAR columns are truncated by a factor of three. Yes VARCHAR(100) becomes VARCHAR(33). VARCHAR(255) becomes VARCHAR(85). What the fuck?

Simple SELECT or SET statements in 4.x simply do not work in 3.x:

-- DOES NOT WORK WITH MySQL 3.* 
    -- SET @MaxUserId := 
(SELECT MAX(user_id)
 FROM phpbb_users); 

-- Instead we need this: 
    SELECT (@MaxUserId := 
MAX(user_id)) 
FROM phpbb_users;

Add to this basic things like almost complete lack of feedback or pinpointing of errors. All you get for a SQL expression error is:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARCHAR(255) = ''' at line 1

Thanks a bunch, MySQL! Reminds me of debugging the VME boxes under Linux: the odd println statement which may or may not contain a useful line number.

My enthusiasm for LAMP has definately been truncated...


4 Comments:

  • Heh, get this: MySQL doesn't have an IF statement which can be used outside of Stored Procedures of functions.

    What. The. Fuck.

    By Blogger Skellywag, at 4:58 pm  

  • Oh, and get this. Putting extra parentheses in a statement screws up the parsing.

    WHAT? THE? FUCK?

    Surely in any decent declaritive language 1+2 is equal to (1+2) or even ((1+2))

    By Blogger Skellywag, at 5:07 pm  

  • Yes, you'd think, but it similar to *king bash in it's arseness, I mean you can't string compare vars which are based on assignments if the assignment comes out as empty the conditional test does not encapsulate the $VAR inside "" to do if [ "string" = "string" ] ; then it just fails as it sees and invalid conditional, but returns an error about unary operator expected, this confvused the hell out of me, I fettled it in a non-optimal way by using a var stuffing technique.

    Bleh, programming .

    By Blogger Pondskater, at 10:43 am  

  • This doesn't even work in MySQL:

    SELECT 'yo' WHERE EXISTS (SELECT 'wibble');

    What is going on here?

    By Blogger Skellywag, at 8:42 am  

Post a Comment

<< Home