PDA

View Full Version : Web Design SQL problems



Stephen Coates
2nd September 2007, 12:44
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

FOL
2nd September 2007, 13:01
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, :).

v85rawdeal
2nd September 2007, 13:55
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 :)

FOL
2nd September 2007, 14:49
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.

Stephen Coates
2nd September 2007, 17:35
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! :)

Harrison
3rd September 2007, 13:08
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.

FOL
3rd September 2007, 18:48
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, :).

Stephen Coates
4th September 2007, 07:58
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.

Harrison
4th September 2007, 09:56
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.

Stephen Coates
12th September 2007, 14:59
Do you know how I could get access to the SQL server?

Harrison
12th September 2007, 16:13
Depends what access rights you have to the hosting server. Do you have SSH shell access? That is the best way. Otherwise you will be stuck with phpMyAdmin which as you have encountered can cause problems.

v85rawdeal
12th September 2007, 16:21
Would would be the best source materials and software to use in order to learn php/sql programming?

I have an old website design I want to update, and that means getting a database system working on it... eventually. I would appreciate any advise or help given, thanks.

Harrison
12th September 2007, 16:28
Many people tell everyone these days to just learn from websites and not to bother with books, but personally I find there is no better way than to have a book in front of you with some examples that you can take your time learning and experimenting with.

I highly recommend the books published by Wrox. If you are completely new to PHP then I recommend you have a look at one titled "Beginning PHP". They also have some others such as "Beginning PHP, Apache, MySQL Web Development" which is also a good beginners guide, but as it lumps everything in together it doesn't quite cover as much on actual PHP and everything you can do with it. You can easily identify all books by Wrox as they are completely red with yellow writing.

If you also need to brush up on your HTML, with XHTML and CSS, then I have always used "HTML, The Complete Reference" by Powell and published by McGrawHill.

Stephen Coates
15th September 2007, 14:00
Depends what access rights you have to the hosting server. Do you have SSH shell access? That is the best way. Otherwise you will be stuck with phpMyAdmin which as you have encountered can cause problems.

I don't have shell access.

Does that mean i am going to have to stick with phpMyAdmin?

FOL
15th September 2007, 20:07
Depends what access rights you have to the hosting server. Do you have SSH shell access? That is the best way. Otherwise you will be stuck with phpMyAdmin which as you have encountered can cause problems.

I don't have shell access.

Does that mean i am going to have to stick with phpMyAdmin?

I would believe so. I have to admit, I have never really bothered with shell, always used myphpadmin, and it has never failed me once, aslong as you select the correct export options.

Stephen Coates
15th September 2007, 21:05
I'm pretty sure I did select the correct export options, but I will probably find out later that I didn't. I exported the database and then restored it using the same method last year and it worked perfectly, but I was following some instructions on a web page that I am no longer able to find.

Harrison
17th September 2007, 10:10
You will be stuck with phpmyadmin without any shell access. If used correctly and the backup was exported correctly then it should restore perfectly well.


I have to admit, I have never really bothered with shell, always used myphpadmin, and it has never failed me once

phpmyadmin is fine but you don't realise how slow it is until you have used a shell session. Directly being able to use Linux commands to backup and restore a database is very fast. For example if I backup the main classicamiga database via the shell you type the command, hit return, and the backup is sitting waiting for you on the server before you can blink. It also makes it possible to automate these backup commands using a cron job, so you don't even need to remember to do the backups.

But the big speed advantage you will notice is in restoring a database. Using phpmyadmin this can take a long time as the program processes through all of the SQL commands from the backup, checking for any errors, and building the new database tables and then populates them with data. Via the shell you type the command to create the database and then restore from the backup and 2 seconds later the restored database is sitting there waiting to be used. The first time I did this I couldn't believe it had actually done anything and restored it. I had to load up the database in phpmyadmin just to check. :lol:

Another great use for a shell session is file backups. Backing up a server file structure via ftp is a very slow and tedious job as you have to wait for each file to download before the next starts so it slows the whole process down. Using the shell it is easy to backup the whole file structure into a single gzipped file which you can then download. This has two big advantages. The file is compressed so the download is smaller, and as the whole file structure is contained within the one big file so you know you will not miss any files in the backup, and the file integrity is better.

Stephen Coates
17th September 2007, 14:13
I added loads of \ to the ' and ", and now have this error:


MySQL said:
#1064 - 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 'Worldwide Developers Conference keynote address exclusively in QuickTime.\r\n\r\' at line 1

And it is in this part of the database:

INSERT INTO `phpbb_posts_text` VALUES (3361, 'bcacceda26', 'Watch the Worldwide Developers Conference keynote address', 'Tune-in following the event on Monday afternoon June 23, 2003 to see Steve Jobs' Worldwide Developers Conference keynote address exclusively in QuickTime.\r\n\r\nFor optimal viewing experience, viewers should download QuickTime 6.3.\r\n\r\nhttp://stream.apple.akadns.net/');

I'm guessing I'd just need another \ or similar, but thought I'd better check before I go and mess it up even more.

Harrison
17th September 2007, 14:37
Looking at the code you posted you have Steve Jobs'. Did you escape the ' on the end of Jobs'?

Stephen Coates
17th September 2007, 15:29
That was the problem. Sorted.

Just got lots more to do now.

BTW, how big is the classicamiga database?

Harrison
17th September 2007, 16:35
Site database is about 5MB and forum database is about 33MB.

Stephen Coates
18th September 2007, 12:34
Got another error which I'm not sure about now. I'm begining to wonder if this database will ever work.

error:
#1064 - 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 'd90c350459', '', 'What are you laughing at? There are many ways to do it, and Ap' at line 1

database:
INSERT INTO `phpbb_posts_text` VALUES (19936, 'd90c350459', '', 'What are you laughing at? There are many ways to do it, and Apple has done far harder before. \r\n\r\nApple used to make MacOS install CDs that would only install on Macintosh Performa models, the same MacOS that would run any 68k, but would not install on anything other than the machine Apple had designed that particular CD from.

Any ideas?

Harrison
18th September 2007, 12:42
Umm... not sure about this one. About the only thing I noticed is the , '', part. I'm assuming that is the subject line text and as it is a reply within a thread that the post has no subject. Maybe try placing a space in there and see if that fixes it, so it would look like:

'd90c350459', ' ', 'What are you lau

The only other thing in that part that could cause any problems is the question mark, but that is unlikely.

BTW, what settings do you have ticked when you export your DB from PHPMyAdmin? Just want to see if you had anything selected that has caused you all of these problems.

Stephen Coates
18th September 2007, 15:14
It is a reply to a topic, and that is a subject line. There are lots of posts like it.

I just tried to connect using SSH and it said that SSH access is not enabled on the account, so it is possible that I might be able to get access. I will try and do that.

I think I left all the phpMyAdmin at all the default settings. I seem to remember chaning some settings when I did it last year, but i couldn;t remember what.
http://www.emaculation.com/steve/export.jpg

Harrison
18th September 2007, 15:26
The settings look fine from that screenshot.

Are you using the backups to restore a database to a new database (ie no data in the database before you restore it), or are you importing the backup into an existing database that has data already in it?

Stephen Coates
18th September 2007, 16:05
There is no data in the database when I restore it. If I try and restore it into a database that already has data I get an error saying that it is trying to restore things that already exist.

I now have SSH access. I got the address after I saw something about SSH in CPanel, and the video tutorial for it showed how to connect using a built in java client, that wasn't avaliable, so using the address I found, i tried to connect to it in PuTTY and it said that SSH is not enabled. The hosts help page said that I needed to email them with the server IP address and user name and they would enable it. So I did and 10 minutes later, I got SSH access.

Now I just need to know how to go about restoring the database using SSH. A quick bit of googling found the required command, but I'm not sure whether the database needs to be on my computer or not. The original database without any of the fixes that I did is already on the server at http://www.emaculation.com/dump/dump.sql, and the dump.sql which had all the backslashes added to it is on my desktop.

Harrison
18th September 2007, 16:34
The sql backup file needs to be on the same server as you wish to restore the database too.

To restore the database you just need to run a mysql command in the shell


mysql -h[database server] -u[username] -p[password] databasename < backupfile.sql

So if your database server is called db01.domain.co.uk, the database is called db1001, username is fred, password is cheese, and the database backup is called backup.sql, then the command would be:


mysql -hdb01.domain.co.uk -ufred -pcheese db1001 < backup.sql

Stephen Coates
18th September 2007, 17:56
Done that. Now I get this error:

mysql -hlocalhost -uclockwis_phpBB -p******** clockwis_phpBB < public_html/dump/dump.sql

ERROR 1045 (28000) at line 82321: Access denied for user 'clockwis_phpBB'@'gaius.asmallorange.com' (using password: YES)

Any ideas as to what that means?

Harrison
18th September 2007, 18:29
Are you sure you have the right username and password? Normally that error is because you have the root username or password wrong. It isn't always identical to the ftp access details.

Stephen Coates
18th September 2007, 18:48
I don't know anything about root user names.

All I know is that the database name is clockwis_phpBB, the username for that database is clockwis_phpBB and that the password is ********. It does restore some of the database before the error appears.

FOL
18th September 2007, 21:28
I don't know anything about root user names.

All I know is that the database name is clockwis_phpBB, the username for that database is clockwis_phpBB and that the password is ********. It does restore some of the database before the error appears.

By root user, it means your main account user and pass. Thats why its saying incorrect, i used to get this a couple of times, as i kept forgetting to use my root account name and pass.

Stephen Coates
24th September 2007, 11:39
Success at last!

I have finally managed to fix all the errors thanks to you lot and the guys at the hosting companies technical support.

The database now seems to work, and I now know to always make sure I have the correct settings when exporting a database.

Harrison
24th September 2007, 13:11
Great to hear you finally got everything back and working again. It definitely took some work on your part to get it all to import.

Most important thing. Have you now made another backup from the newly restored database?

Stephen Coates
24th September 2007, 15:54
Nope, but I will do.

I also should make a complete backup of the server and stick it on a CD. Over the last two years two of my favourite forums got messed up due to server crashes and missing backups and I don't want a site that i am in charge of to be the next.

Harrison
24th September 2007, 16:57
Easiest way for you to make a complete backup now that you have access is through the shell. I back mine up by creating a single gzipped file of the whole server and then just download that one file.