Transfer problems

They have: 17 posts

Joined: Oct 2005

I've moved servers, so i need to transfer my database for my site and forums to my new host. I have made back-ups of both and have tried to use PHPMyAdmin on my new host to upload the SQL back-ups. When ever i try to do this is get an error. I have tried it with both databases and get the same error. I have checked the SQL version on the old host which is 4.1.13 and on the new host it's 4.0.25. If this is the problem is there any other way i can upload my database without having to start all over again?

They have: 17 posts

Joined: Oct 2005

<?php
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 '-- phpMyAdmin SQL Dump
-- version 2.6.3-pl1
-- <a href=\"http://www\" class=\"bb-url\">http://www</a>.
?>

He has: 698 posts

Joined: Jul 2005

It looks like, by your error, you have some odd characters in your code. What did you use to export your SQL? If it wasn't phpMyAdmin, I'd suggest using that. That way it will be readable when it imports it into the new database. Wink

Kurtis

Mark Hensler's picture

He has: 4,048 posts

Joined: Aug 2000

mysqldump --opt -p -uUSER DBNAME > DBNAME.20051028.sql'
mysql -p -uUSER DBNAME < DBNAME.20051028.sql'
might want to compress to transfer...
gzip -S .gz DBNAME.20051028.sql'
gunzip DBNAME.20051028.sql.gz'

Mark Hensler
If there is no answer on Google, then there is no question.

Greg K's picture

He has: 2,145 posts

Joined: Nov 2003

Man I just ran into this problem yesterday. We had purchased a dedicated server though our hosting company a few months ago. Last month they went through and upgraded all their shared servers to the latest 4.x version of mysql. Well on their dedicated servers, mysql is running on your own machine, so they either only upgrade it if your request it, or maybe they just havent gotten to upgrading the dedicated servers yet.

I did an export of all my tables in phpMyAdmin, switched over the our dedicated server, went to run the sql script, and errors out the wazzoo (sp?). Called them up and found out that the dedicated still was using the older version.

Well now that v5.x has a "stable" version (or whatever), they gave me that option as well. As soon as they did the upgrade, the sql script ran no problems at all (other than the delay that it took to upload the 3meg file).

So now I have v5 and can play around witht he new features. I found out that for $50 fee they will switch me over to PHP 5 if I want. Have to investigate to see if it has anything to offer to make it worth it.

-Greg

JeevesBond's picture

He has: 3,955 posts

Joined: Jun 2002

Oooooh, I wish we were on MySQL 5 (sorry just found this thread Smiling ), what're the new features like?

I read they have triggers, stored procedures and all sorts of other whizzo gumpf. Smiling
Have you played with any of the new stuff yet?

And PHP 5 has totally re-written OOP support, mmmmm OOP support. Although a nasty bug has reared it's ugly head and there isn't a fix for PHP 5 yet, but there is one for 4. Bit strange IMO, although I suppose most people are using 4.

a Padded Cell our articles site!

Greg K's picture

He has: 2,145 posts

Joined: Nov 2003

The big thing I am lookingforward to is the stored procedures and triggers. In our program, any time you change any one of 5 pages, it needs to recalculate some items. Right now every page that does a SAVE VALUES function calls an included file that loops though all the data and updates it all.

Will be nice to have that separate from the main program.

-Greg

JeevesBond's picture

He has: 3,955 posts

Joined: Jun 2002

Ah, cool. So in this case it would just call a stored procedure and MySQL will do it itself.

Wonder if there'll be a performance increase from it doing that? Suppose it wont make much difference, SQL is SQL.

a Padded Cell our articles site!

chrishirst's picture

He has: 379 posts

Joined: Apr 2005

There should be, using stored procedures (provided you use them correctly) can give a noticeable performance boost especially for examples as Greg cited.

Not tried MySQL 5 as yet though.

Chris

Indifference will be the downfall of mankind, but who cares?
Venue Capacity Monitoring
Code Samples

Greg K's picture

He has: 2,145 posts

Joined: Nov 2003

With triggers, if they work the same as with MS SQL and oracle, are handled by the database directly. On the programming end (the php program), you do not have to call anything. Basically the server itself looks for any update, and when it occurs executes the stored procedure itself. The one benefit for me is it can do an update only on the records being adjusted. We were in a slight hurry to get the program up and running, so I didn't code it to look to see which data was updated in my included file, it jsut goes and recalcs everything (yes i know, poor design, but i have 50 "need to do first" things here and I'm the only one to do them).

Part of the preformance increase will be that there will be no need to transfer the data back and forth from mySQL to PHP. [for my project] If you have 20 employees and adjust one of the tax rates, it has to get a list of all employees, then each employee get their data accross from 3 tables, and then calculate it all, then update 2 tables. As with a lot of programming "preformance" issues, it may seem small, but it all adds up.

-Greg

Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.