how to convert .sql file to support latest version of mysql?

[seinstein]

Hi all,

I have a bunch of code in a .sql file (for MySQL server) that when run on the latest (5 beta) version of MySQL gives a syntax error, 'check for the correct syntax to use near ) ' etc etc.
I was wondering if it is possible to convert this file so that it works with MySQL 5, as I tried it with a version 4 of MySQL and it gave no problems!

Thanx in advance! :)

[AndyB]

http://lists.mysql.com/mysql/198945

Maybe that'll help. I doubt there's a "conversion utility", just changing the queries manually.

[fenway]

Seems strange.. there aren't that many incompatible changes from 4 -> 5, especially syntax related with parens.

[seinstein]

If I posted the .sql file would that help?  ???

[AndyB]

[quote author=seinstein link=topic=100622.msg397947#msg397947 date=1153008700]
If I posted the .sql file would that help?  ???
[/quote]
On behalf of whoever might solve this for you, I'd say yes.  If it's "huge" it might be better uploaded to your web site and providing a link to it.

[seinstein]

Well, it's actually an error I get on installing program, ironically by invision but not supported any more. I am using it for educational purposes (or want to anyway!).

The sql code that gives the problem is as follows, in a php lib file that is used to perform SQL queries dynamically during install time:

[code]<?php

/* -----------------------------------------
                Define queries
  ----------------------------------------- */
 
$queries["drop_table"] = 'DROP TABLE IF EXISTS {TABLE}';

// Table creation
$queries["create_groups"] = 'CREATE TABLE {PREFIX}_groups (gid int(11) NOT NULL auto_increment, groupname char(25) NOT NULL default \'\', add_notes tinyint(1) NOT NULL default \'0\', del_admin_msg tinyint(1) NOT NULL default \'0\', show_url tinyint(1) NOT NULL default \'0\', use_bookmarks tinyint(1) NOT NULL default \'0\', lock_dir tinyint(1) NOT NULL default \'0\', access_ucp tinyint(1) NOT NULL default \'0\', stats tinyint(1) NOT NULL default \'0\', access_acp tinyint(1) NOT NULL default \'0\', chmod tinyint(1) NOT NULL default \'0\', upload tinyint(1) NOT NULL default \'0\', download tinyint(1) NOT NULL default \'0\', editor tinyint(1) NOT NULL default \'0\', view_source tinyint(1) NOT NULL default \'0\', view_img tinyint(1) NOT NULL default \'0\', mp3 tinyint(1) NOT NULL default \'0\', `delete` tinyint(1) NOT NULL default \'0\', `rename` tinyint(1) NOT NULL default \'0\', copy tinyint(1) NOT NULL default \'0\', move tinyint(1) NOT NULL default \'0\', newfolder tinyint(1) NOT NULL default \'0\', newfile tinyint(1) NOT NULL default \'0\', overwrite tinyint(1) NOT NULL default \'0\', create_archive tinyint(1) NOT NULL default \'0\', view_archive tinyint(1) NOT NULL default \'0\', archive_save_options tinyint(1) NOT NULL default \'0\', make_symlink tinyint(1) NOT NULL default \'0\', verify_file tinyint(1) NOT NULL default \'0\', group_space decimal(15,0) NOT NULL default \'0\', show_hidden_files tinyint(1) NOT NULL default \'0\', access_type tinyint(1) NOT NULL default \'0\', file_types char(100) default NULL, allow_no_extension tinyint(1) NOT NULL default \'0\', offline tinyint(1) NOT NULL default \'0\', PRIMARY KEY  (gid), KEY uid (gid)) TYPE=MyISAM';
$queries["create_users"] = 'CREATE TABLE {PREFIX}_users (uid int(11) NOT NULL auto_increment, username varchar(32) NOT NULL default \'\', password varchar(32) NOT NULL default \'\', email varchar(75) NOT NULL default \'\', offset varchar(6) NOT NULL default \'0\', dst tinyint(1) NOT NULL default \'0\', gid tinyint(3) NOT NULL default \'0\', sql tinyint(1) default NULL, dbhost varchar(75) default NULL, dbuser varchar(75) default NULL, dbpass varchar(75) default NULL, dbname varchar(100) default NULL, root_url varchar(255) default NULL, root_path varchar(255) default NULL, user_space decimal(20,0) default NULL, language tinyint(4) NOT NULL default \'0\', PRIMARY KEY (uid), KEY uid (uid)) TYPE=MyISAM';
$queries["create_antispam"] = 'CREATE TABLE {PREFIX}_reg_antispam (regid varchar(32) NOT NULL default \'\', regcode varchar(8) NOT NULL default \'\', regtime int(10) default NULL, PRIMARY KEY (regid)) TYPE=MyISAM';
$queries["create_validating"] = 'CREATE TABLE {PREFIX}_validating (vid varchar(32) NOT NULL default \'\', uid int(11) NOT NULL default \'0\', vtime int(10) NOT NULL default \'0\', ip_address varchar(16) NOT NULL default \'0\', type tinyint(1) NOT NULL default \'0\', PRIMARY KEY (vid)) TYPE=MyISAM';

// Insert values
$queries["admin_group"] = 'INSERT INTO {PREFIX}_groups (gid, groupname, add_notes, del_admin_msg, show_url, use_bookmarks, lock_dir, access_ucp, stats, access_acp, chmod, upload, download, editor, view_source, view_img, mp3, `delete`, `rename`, copy, move, newfolder, newfile, overwrite, create_archive, view_archive, archive_save_options, make_symlink, verify_file, group_space, show_hidden_files, access_type, file_types, allow_no_extension, offline) VALUES (1, \'Administrators\', 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 3, 1, 1, 52428800, 0, 0, \'\', 1, 1)';
$queries["guest_group"] = 'INSERT INTO {PREFIX}_groups (gid, groupname, add_notes, del_admin_msg, show_url, use_bookmarks, lock_dir, access_ucp, stats, access_acp, chmod, upload, download, editor, view_source, view_img, mp3, `delete`, `rename`, copy, move, newfolder, newfile, overwrite, create_archive, view_archive, archive_save_options, make_symlink, verify_file, group_space, show_hidden_files, access_type, file_types, allow_no_extension, offline) VALUES (2, \'Guests\', 0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 1, 0, 1, 1, 0, 0, 1, 0, 0, 1, 1, 0, 1, 0, 2, 0, 0, 52428800, 0, 1, \'zip tar gif jpg jpeg png txt\', 0, 0)';
$queries["member_group"] = 'INSERT INTO {PREFIX}_groups (gid, groupname, add_notes, del_admin_msg, show_url, use_bookmarks, lock_dir, access_ucp, stats, access_acp, chmod, upload, download, editor, view_source, view_img, mp3, `delete`, `rename`, copy, move, newfolder, newfile, overwrite, create_archive, view_archive, archive_save_options, make_symlink, verify_file, group_space, show_hidden_files, access_type, file_types, allow_no_extension, offline) VALUES (3, \'Members\', 1, 1, 1, 1, 1, 1, 0, 0, 1, 1, 1, 1, 1, 1, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 52428800, 0, 0, \'php cgi pl asp php3 aspx phtml php4 htaccess htpasswd\', 1, 0)';
$queries["admin_user"] = 'INSERT INTO {PREFIX}_users (uid, username, password, email, offset, dst, gid, sql, dbhost, dbuser, dbpass, dbname, root_url, root_path, user_space, language) VALUES (1, \'{USERNAME}\', \'{PASSWORD}\', \'{EMAIL}\', \'\', 0, 1, 0, NULL, NULL, NULL, NULL, \'{URL}\', \'{PATH}\', NULL, 0)';
$queries["guest_user"] = 'INSERT INTO {PREFIX}_users (uid, username, password, email, offset, dst, gid, sql, dbhost, dbuser, dbpass, dbname, root_url, root_path, user_space, language) VALUES (2, \'Guest\', \'5f4dcc3b5aa765d61d8327deb882cf99\', \'nobody@localhost.localdomain\', \'\', 0, 2, 0, NULL, NULL, NULL, NULL, \'\', \'{PATH}users/Guests/Guest/\', NULL, 0)';

?>[/code]

The error I get during installtime is as follows:

[code]There was an error creating the SQL tables, the error returned was: 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 'sql, dbhost, dbuser, dbpass, dbname, root_url, root_path, user_space, language) ' at line 1[/code]

I don't really see anything wrong with the code myself. The install works fine with MySQL 4.1.12a, but for the newest version MySQL 5 beta this error occurs.

Hope somebody can help! Also apologies for spamming this site of all sites with Invision-related code, but it's just me, don't worry!

:)

[AndyB]

That's odd. SQL is a reserved word so I'm surprised to see it used as a fieldname. I'd have expected problems with that with [i]any[/i] version of MySQL.

[url=http://www.htmlite.com/mysql002a.php]http://www.htmlite.com/mysql002a.php[/url]

[fenway]

Yeah, must be a reserved keyword issue somewhere.

[seinstein]

If I'm reading you correctly, I think the word SQL is generically used, as in you could in theory inject the 'SQL' code into a database engine of your choice such as MSSQL/MySQL/Oracle and so on, as opposed to MySQL alone... ;)

If I was to change this what would I put around the word sql - quotes or something else?  ???

[AndyB]

backticks .... `sql`

[seinstein]

okay, that sort of worked - i got thru the installation ok, but when i try to login the page just empties. i think we can both assume that i now have to rummage through all the code in the other php files that call the 'sql' part of the tables or whatever they are, and change them accordingly. how i do this, i have no idea; how about it, anyone?

p.s. thanx for the help already, it's great! :)

[fenway]

You'll have to backtick every reference to this column, assuming that is what's causing the problem you're experiencing.

[AndyB]

If changing each instance of sql to `sql` is what's needed, then a good text editor should help.  Since the string sql occurs in places like mysql_query, etc. you'll need to exercise care before doing any global change and replace. I'd think of doing something like a global replace of mysql with myxyz, then a global replace of sql with `sql`, then a global replace of myxyz with mysql.

But I'm still wondering why you get a blank page (rather than an error), and still wondering why this worked with earlier versions of MySQL (same reserved word sql), and then wondering even more why someone wrote parts of a script that were doomed to fail and hasn't offered a working replacement.

[fenway]

All excellent questions....

[seinstein]

The reason they are doomed to fail is that the program is currently discontinued, but I was using it for personal pleasure. I was hoping i could replace the `sql` with something else like `sql_query` and then change these variables like you said in the other php files. At least that is what I [i]think[/i] I need to do, as I can't think of any alternative if I want the program to continue to work with later versions of MySQL.  ::)

Also to follow up on what AndyB said about search and replace, I think that I already have a function for searching whole words only, so there's no worries there. Problem is understanding when and where the sql column itself is called. :)

Upon searching all the files *.php,*.tpl,*.js and so on with Macromedia Dreamweaver, do i need to worry about all these filetypes? Because not all of them seem to be calling the 'sql' column in question. The problem is identifying when the column in the MySQL table is called, because there is a <form name="sql"... also and in the .js files there are things like 'function run_sql(sql)' and so on. Also I don't know when php is calling a html form input or otherwise, when commands like $result = db_query($input["sql"], $dbman_link_id); or $page["sql"] = htmlspecialchars($input["sql"]); occur - are these commands updating the 'sql' column i want to change the value of to say 'sql_query' for compatibility, or are they calling on the data entered in a <form>?
There really isn't all that much to change if somebody with php programming experience wants to volunteer doing it for me, I'll be eternally grateful, let me know if I'll upload the script. :)

[fenway]

Well, any reference to column would include the queries themselves, and the output from said queries in PHP (like from a mysql_fetch_assoc() call).  Everything else -- JS function parameters, form names, etc. -- could, in theory, be changed, but who knows how these values might be used.  Search and replace would work, but you'd have to visually inspect each match.

[seinstein]

Then perhaps is there a way to make MySQL ignore the input of reserved values, instead of using backticks?  :(

[fenway]

That's what reserved MEANS... you can't use it, and tell it to ignore keywords.  Otherwise, there's no L in SQL.

[seinstein]

darn - looks like i'm stuck with an older version of mysql until i can figure out the script... Thanx for all your help, this site is great. :)