Jump to content


Photo

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


  • Please log in to reply
18 replies to this topic

#1 seinstein

seinstein
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 15 July 2006 - 12:46 AM

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! :)

#2 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 15 July 2006 - 04:34 AM

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

Maybe that'll help. I doubt there's a "conversion utility", just changing the queries manually.
Legend has it that reading the manual never killed anyone.
My site

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 15 July 2006 - 03:42 PM

Seems strange.. there aren't that many incompatible changes from 4 -> 5, especially syntax related with parens.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 seinstein

seinstein
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 16 July 2006 - 12:11 AM

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

#5 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 16 July 2006 - 12:56 AM

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

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.
Legend has it that reading the manual never killed anyone.
My site

#6 seinstein

seinstein
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 16 July 2006 - 02:03 AM

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:

<?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)';

?>

The error I get during installtime is as follows:

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

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!

:)

#7 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 16 July 2006 - 04:26 AM

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 any version of MySQL.

http://www.htmlite.com/mysql002a.php
Legend has it that reading the manual never killed anyone.
My site

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 16 July 2006 - 06:14 AM

Yeah, must be a reserved keyword issue somewhere.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 seinstein

seinstein
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 16 July 2006 - 12:37 PM

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?  ???

#10 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 16 July 2006 - 01:16 PM

backticks .... `sql`
Legend has it that reading the manual never killed anyone.
My site

#11 seinstein

seinstein
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 17 July 2006 - 01:33 AM

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! :)

#12 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 17 July 2006 - 05:19 AM

You'll have to backtick every reference to this column, assuming that is what's causing the problem you're experiencing.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#13 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 17 July 2006 - 01:20 PM

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.
Legend has it that reading the manual never killed anyone.
My site

#14 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 17 July 2006 - 06:10 PM

All excellent questions....
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#15 seinstein

seinstein
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 17 July 2006 - 11:42 PM

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 think 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. :)

#16 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 18 July 2006 - 07:59 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#17 seinstein

seinstein
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 19 July 2006 - 11:15 PM

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

#18 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 20 July 2006 - 12:35 AM

That's what reserved MEANS... you can't use it, and tell it to ignore keywords.  Otherwise, there's no L in SQL.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#19 seinstein

seinstein
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 21 July 2006 - 03:53 PM

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. :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users