MySQL4 compatible imports into v5...

RobertK · 01-15-2008, 08:19 PM

I'm trying to write an installer script, but when it comes down to the actual "drop if exists" statements it dies on the following line. I have MySQL 5.0.50 and the SQL export (that I'm importing with the script) is in MySQL40 compatibility mode.

Can anyone tell me why a simple statement like the following is dying with the given error? When I import this script by phpMyAdmin, I get to specify MySQL40 compatibility mode though, this whole thing works.

sql Code:

DROP TABLE IF EXISTS `jag_categories`;
CREATE TABLE `jag_categories` (
  `cat_id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent` mediumint(8) UNSIGNED NOT NULL,
  `title` tinytext NOT NULL,
  PRIMARY KEY  (`cat_id`),
  FULLTEXT KEY `title` (`title`)
) TYPE=MyISAM;

Quote:

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 '; CREATE TABLE `jag_categories` ( `cat_id` mediumint(8) unsigned NOT NULL au' at line 20

Alan @ CIT · 01-15-2008, 08:54 PM

That's a strange one to say the least. The good news is that I can replicate the problem using your export here running MySQL 5.0.45 so I'll play about some more and let you know if I can get it working.

Alan.

RobertK · 01-15-2008, 09:00 PM

Okay thanks, it's really a strange one.

I'm just doing a "file_get_contents" and running it as my query, in case that method is bad.

Maybe I should format it differently, and do one statement at a time?

Alan @ CIT · 01-15-2008, 09:15 PM

Problem solved - turns out that mysql_query() doesn't support multiple queries (PHP: mysql_query - Manual - "mysql_query() sends an unique query (multiple queries are not supported) to the currently active database..."). The MySQL C API supports a flag to allow multiple queries but I couldn't find one for PHP.

Supposedly some sort of security measure to prevent SQL injection :/

A workaround could be to explode the big query by ';' and run them seperately perhaps.

Alan.

RobertK · 01-15-2008, 09:17 PM

...ugh. I had worried about that. I don't have mysqli available on my server either, which is slightly odd. Thanks for all your help Alan.

Alan @ CIT · 01-15-2008, 09:20 PM

Before you go about installing mysqli, that doesn't support it either :/

And np's - glad I found this out myself, I'd always assumed it could handle multiple queries.

Alan.

RobertK · 01-15-2008, 09:40 PM

I thought I heard mysqli did!

So much for that hope.

Looks like I might have to write my own "multi-query" member for my database. At the moment this work around is successful, though there isn't any aggregation of the results.

PHP Code:


			
        foreach(explode(";\r\n",$query) as $q) {

          if(!$q{0}) {

            continue;

          }

          if(mysql_query($q) == false) {

            $str = mysql_error($pDb).'<br/><tt>'.htmlspecialchars($q).'</tt>';

            if(!empty($error) && is_string($error)) {

              $error = array($error, $str);

            } elseif(is_array($error)) {

              $error[] = $str;

            } else {

              $error = $str;

            }

          }

        }

As you can see with a little adaptation you can tweak it for Linux compatibility, but for WAMP it works just fine. For now.

SOCK · 01-16-2008, 01:08 AM

Quote:

Originally Posted by Alan @ CIT

Before you go about installing mysqli, that doesn't support it either :/

And np's - glad I found this out myself, I'd always assumed it could handle multiple queries.

mysqli_multi_query()

Alan @ CIT · 01-16-2008, 10:16 AM

Ahh, good call - dammed if I saw that originally

Alan.