Page 1 of 4 1234 LastLast
Results 1 to 10 of 35

Thread: SQL problems

  1. #1
    RetroSteve! My location

    Stephen Coates's Avatar
    Join Date
    Mar 2003
    Location
    Rotherham
    Posts
    2,187
    Downloads
    0
    Uploads
    0

    SQL problems

    Does this SQL error mean anything to anyone?

    http://www.emaculation.com/steve/error.jpg

    I'm trying to restore a MySQL database which I got from phpMyAdmin using the 'Export' thing.

    The database is for a phpBB forum, and some of the text in the error is from a topic posted a few years ago.

    Opening the database in MS Word, the error would appear to be located somewhere here:

    http://www.emaculation.com/steve/sql.txt

    Does anyone know what the error is and how to fix it?

    Thanks
    Steve

  2. #2
    Competent My location

    FOL's Avatar
    Join Date
    Aug 2007
    Location
    Wales, UK
    Posts
    325
    Downloads
    0
    Uploads
    0
    Quote Originally Posted by Stephen Coates View Post
    Does this SQL error mean anything to anyone?

    http://www.emaculation.com/steve/error.jpg

    I'm trying to restore a MySQL database which I got from phpMyAdmin using the 'Export' thing.

    The database is for a phpBB forum, and some of the text in the error is from a topic posted a few years ago.

    Opening the database in MS Word, the error would appear to be located somewhere here:

    http://www.emaculation.com/steve/sql.txt

    Does anyone know what the error is and how to fix it?

    Thanks
    Steve
    If you look at the error message its pointing you to the error.

    Its taking some of the text as functions, i.e. today AND tommorow IS sunny. AND and IS are being taken as functions.

    EDIT:- after a closer look, look at the (tm) bit, as that is causing problem, remove the ( ) and leave the tm. This is because the brackets open, then that tm bit opens another bracket and then closed straight after tm, making it think its a function thats ended, so everything after it is seen as functions. Hence the AND, IS, etc etc are in capitals.

    It looks like it will error again by the phone no. listed in that post.


    I know, I must sound like a right n00b, but im not, if the terminology is wrong, its because i have taught myself PHP and SQL. So I know what I mean, just hard to try and explain what I mean, .
    Last edited by FOL; 2nd September 2007 at 13:14.
    On news of PSPUAE update on DCEMU came this reply, lol.

    [quote=djk21108;]this will sure make that horace guy happy....[/quote]

  3. #3
    ELITE v85rawdeal's Avatar
    Join Date
    May 2006
    Location
    Portsmouth, UK
    Posts
    1,334
    Downloads
    1
    Uploads
    0
    As a non-coder I found that explanation easy to understand, FOL, which seems to indicate that you have a good understanding of the subject and a knack for passing that knowledge on to others.

    I just wish my teachers at school were that good
    This is not a signature

  4. #4
    Competent My location

    FOL's Avatar
    Join Date
    Aug 2007
    Location
    Wales, UK
    Posts
    325
    Downloads
    0
    Uploads
    0
    Quote Originally Posted by v85rawdeal View Post
    As a non-coder I found that explanation easy to understand, FOL, which seems to indicate that you have a good understanding of the subject and a knack for passing that knowledge on to others.

    I just wish my teachers at school were that good
    Good good, lets hope that solves problem. I aint touched much PHP & SQL since I started messing with PSPUAE. Im sure thats what is causing problem though.
    On news of PSPUAE update on DCEMU came this reply, lol.

    [quote=djk21108;]this will sure make that horace guy happy....[/quote]

  5. #5
    RetroSteve! My location

    Stephen Coates's Avatar
    Join Date
    Mar 2003
    Location
    Rotherham
    Posts
    2,187
    Downloads
    0
    Uploads
    0
    That all made sense to me. I don't know much about SQL. Although for some reason I did wonder if the (tm) bit might be a problem.

    I will see if that fixes it tommorow.

    Thanks!

  6. #6
    Retro Addict Administrator
    My location

    Burger Time Champion, Sonic Champion Harrison's Avatar
    Join Date
    Dec 2002
    Location
    UK
    Posts
    16,654
    Blog Entries
    1
    Downloads
    6
    Uploads
    14
    The 1064 MySQL error is a general coding syntax error that appears when anything is wrongly formatted in the query you are trying to run. This is commonly if an end of line ; has been missed from PHP code or if a character hasn't been escaped correctly, confusing the script.

    There are a few things that could be wrong with the syntax of the query you are trying to run. But on looking at your SQL query the first thing I noticed that I believe is the problem is that characters haven't been escaped correctly within the query. The use of single quotes ' and double quotes " within the SQL query's strings is what is causing the problems.

    The (TM) mentioned by FOL is only a by-product of this error and not the cause.

    If you look at your statement you can easily see what is happening. Look down to the line below the purple highlighted AND in the screenshot. The word Transitive's has been split at the 's point. This is because PHP is interpreting the single quote ' as the end of a string within the query and is therefore leaving and ending the string at this point and taking the next part as a new part of the code, and this is why the text past this point is then being highlighted as though it were php.

    To get around this problem you need to escape all php characters that can cause this issue within your code's strings. To do this you escape characters by adding a backslash \ before all characters that need to have quotes in the database query.

    So I recommend you try this. Go through your query and within the strings containing the forum text add a backslash before all instances of single quotes ('), double quotes ("), backslashes (\) and NUL.

    So for example you would change Transitive's to Transitive\'s. This tells the PHP interpreter to ignore that single quote and continue. Try doing that and then rerunning the query.

    I hope that fixes it for you.

    Info regarding why this may of happened: The forum when saving this post into the database was probably coded to add and remove the backslash escape characters as it processes the data. But normally I would expect to see the backslash escape characters present within the SQL and the forum software to remove them using a "stripslashes" function when it is retrieving the data from the database. I'm not sure why it isn't coded like this. Unless the forum was using the old magic quotes method which is bad coding practice.

    If you haven't played a classic game in years, it's never too late to start!


  7. #7
    Competent My location

    FOL's Avatar
    Join Date
    Aug 2007
    Location
    Wales, UK
    Posts
    325
    Downloads
    0
    Uploads
    0
    Quote Originally Posted by Harrison View Post
    The 1064 MySQL error is a general coding syntax error that appears when anything is wrongly formatted in the query you are trying to run. This is commonly if an end of line ; has been missed from PHP code or if a character hasn't been escaped correctly, confusing the script.

    There are a few things that could be wrong with the syntax of the query you are trying to run. But on looking at your SQL query the first thing I noticed that I believe is the problem is that characters haven't been escaped correctly within the query. The use of single quotes ' and double quotes " within the SQL query's strings is what is causing the problems.

    The (TM) mentioned by FOL is only a by-product of this error and not the cause.

    If you look at your statement you can easily see what is happening. Look down to the line below the purple highlighted AND in the screenshot. The word Transitive's has been split at the 's point. This is because PHP is interpreting the single quote ' as the end of a string within the query and is therefore leaving and ending the string at this point and taking the next part as a new part of the code, and this is why the text past this point is then being highlighted as though it were php.

    To get around this problem you need to escape all php characters that can cause this issue within your code's strings. To do this you escape characters by adding a backslash \ before all characters that need to have quotes in the database query.

    So I recommend you try this. Go through your query and within the strings containing the forum text add a backslash before all instances of single quotes ('), double quotes ("), backslashes (\) and NUL.

    So for example you would change Transitive's to Transitive\'s. This tells the PHP interpreter to ignore that single quote and continue. Try doing that and then rerunning the query.

    I hope that fixes it for you.

    Info regarding why this may of happened: The forum when saving this post into the database was probably coded to add and remove the backslash escape characters as it processes the data. But normally I would expect to see the backslash escape characters present within the SQL and the forum software to remove them using a "stripslashes" function when it is retrieving the data from the database. I'm not sure why it isn't coded like this. Unless the forum was using the old magic quotes method which is bad coding practice.

    FFS, Harrison, I said it was ' first of all, but then edited cause i though it wasnt. So I should have gone with my original instint, .
    On news of PSPUAE update on DCEMU came this reply, lol.

    [quote=djk21108;]this will sure make that horace guy happy....[/quote]

  8. #8
    RetroSteve! My location

    Stephen Coates's Avatar
    Join Date
    Mar 2003
    Location
    Rotherham
    Posts
    2,187
    Downloads
    0
    Uploads
    0
    I can try putting a \ behind the ' and ", but it would be a little tricky doing that with all the posts on the forum (assuming that they all have this problem - it could just be this post).

    I don't understand why it has done this though. Last time I got the database from phpMyAdmin it uploaded without problems.

  9. #9
    Retro Addict Administrator
    My location

    Burger Time Champion, Sonic Champion Harrison's Avatar
    Join Date
    Dec 2002
    Location
    UK
    Posts
    16,654
    Blog Entries
    1
    Downloads
    6
    Uploads
    14
    How did you export the database? Why are you needing to restore it?

    Do you have SSH telnet shell access to the server you are restoring the database to? If so it would probably be better to bypass phpMyAdm completely and directly restore the database to MySQL via the commandline.

    Doing this would be much faster and most likely without any of the errors you are experiencing. For example when I moved the main classicamiga site to the new server I did some local tests first to make sure it would restore correctly. First I created a new database and used phpMyAdmin to restore the database to the new one. It worked but took a long time and required me to answer some selection boxes along the way. In contrast I then tested it using the commandline and direct MySQL commands to restore the database directly to MySQL. This worked perfectly and finished in seconds! So when I actually moved the site, I uploaded the database backup to the server, then logged into the server using a SSH telnet shell session and restored this local backup directly to MySQL using the commandline. It was completed in seconds and worked without error.

    If you haven't played a classic game in years, it's never too late to start!


  10. #10
    RetroSteve! My location

    Stephen Coates's Avatar
    Join Date
    Mar 2003
    Location
    Rotherham
    Posts
    2,187
    Downloads
    0
    Uploads
    0
    Do you know how I could get access to the SQL server?

Similar Threads

  1. Problems with having photos processed
    By Stephen Coates in forum General Chat
    Replies: 11
    Last Post: 21st August 2007, 08:29
  2. MySpace problems
    By Submeg in forum General Chat
    Replies: 12
    Last Post: 13th February 2007, 00:30

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Copyright classicamiga.com