MySql Version Issue

d4funky1 · March 3, 2007

Hi,

I am in the process of switching host and i'm at the stage of importing my backup sql file to the new database.

Problem is - according to the new host their version of mysql is 4.0 and my current hosts is 5.0

They tell me that this is what is causing the problem of not being able to import my database.

This would now lead me to 2 questions:

1, Is there anything out there that would be able to convert my sql file to a 4.0 version?

2, Is there another way of going about this?

If anyone could shed any light on this for me it would, as usual, be highly appreciated.

Many Thanks

D4

GemRock · March 3, 2007

What's the error, if any? I just did one today from my local server which uses mysql 5, to a live server which uses mysql 4. Nothing special, just spent extra time to get rid of the part that caused the errors.

Ken

d4funky1 · March 3, 2007

not actually getting an error message.

If i upload the no compression version it just says: No Sql Query

and if i upload the bzipper version the page just takes a while to load and then goes all white?

Any ideas? I could post a snippet of the file if required.

Many Thanks

D4

GemRock · March 3, 2007

Doesnt look like a mysql problem - mysql always (in my own experience - however the error msg may be unclear) gives out error msgs. What about trying to upload a sql file which you know for sure is from version 5 or same version as your new mysql server?

Ken

not actually getting an error message.

If i upload the no compression version it just says: No Sql Query

and if i upload the bzipper version the page just takes a while to load and then goes all white?

Any ideas? I could post a snippet of the file if required.

Many Thanks

D4

d4funky1 · March 3, 2007

ok i'll take another look

are you still online for a couple of mins?

Thanks

D4

d4funky1 · March 3, 2007

ok

from the top

1,

ERROR: Unknown Punctuation String @ 5
STR: />
SQL: <br />
<b>Fatal error</b>:  Allowed memory size of 16777216 bytes exhausted (tried to allocate 10184912 bytes) in <b>/websites/LinuxPackage01/ph/pm/ya/phpmyadmin.myserverworld.net/phpMyAdmin-2.8.1/export.php</b> on line <b>80</b><br />

2,

SQL query: 

<br /> <b>Fatal error</b>: Allowed memory size of 16777216 bytes exhausted (tried to allocate 10184912 bytes) in <b>/websites/LinuxPackage01/ph/pm/ya/phpmyadmin.myserverworld.net/phpMyAdmin-2.8.1/export.php</b> on line <b>80</b><br />

3,

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 '<br />
<b>Fatal error</b>:  Allowed memory size of 16777216 byt

Phew.

Any Ideas?

Many Thanks

GemRock · March 3, 2007

Its not clear to me that in your post, are the text in 1 & 2 actually in your sql file? if yes, then your exportation of your old db has been done OK. Whatever, its an indication of it being not a mysql issue.

Ken

ok

from the top

1,

ERROR: Unknown Punctuation String @ 5
STR: />
SQL: <br />
<b>Fatal error</b>:  Allowed memory size of 16777216 bytes exhausted (tried to allocate 10184912 bytes) in <b>/websites/LinuxPackage01/ph/pm/ya/phpmyadmin.myserverworld.net/phpMyAdmin-2.8.1/export.php</b> on line <b>80</b><br />

2,

SQL query: 

<br /> <b>Fatal error</b>: Allowed memory size of 16777216 bytes exhausted (tried to allocate 10184912 bytes) in <b>/websites/LinuxPackage01/ph/pm/ya/phpmyadmin.myserverworld.net/phpMyAdmin-2.8.1/export.php</b> on line <b>80</b><br />

3,

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 '<br />
<b>Fatal error</b>:  Allowed memory size of 16777216 byt

Phew.

Any Ideas?

Many Thanks

d4funky1 · March 3, 2007

not sure i follow?

i'm confident that the export of my file using my current hosts 5.0.21 version of mysql is fine.

The issue is uploading to my new host that use 4.0 version of mysql

They say there is an incompatiability with the two sql versions and the data being passed from one to another?

Could this be true if so the new host is going to have to get the boot.

GemRock · March 3, 2007

Neither do I. What about post part (first 100 lines or so) of your sql scripts?

Ken

d4funky1 · March 4, 2007

Hi,

Here are the first few lines of my backup up until we get to sensitive information (customer details etc)

-- MySQL dump 10.10
--
-- Host: mysql01	Database: fancydressretailcouk_66188_db1
-- ------------------------------------------------------
-- Server version	5.0.21-standard-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `address_book`
--

DROP TABLE IF EXISTS `address_book`;
CREATE TABLE `address_book` (
 `address_book_id` int(11) NOT NULL auto_increment,
 `customers_id` int(11) NOT NULL,
 `entry_gender` char(1) collate latin1_general_ci NOT NULL,
 `entry_company` varchar(32) collate latin1_general_ci default NULL,
 `entry_company_tax_id` varchar(32) collate latin1_general_ci default NULL,
 `entry_firstname` varchar(32) collate latin1_general_ci NOT NULL,
 `entry_lastname` varchar(32) collate latin1_general_ci NOT NULL,
 `entry_street_address` varchar(64) collate latin1_general_ci NOT NULL,
 `entry_suburb` varchar(32) collate latin1_general_ci default NULL,
 `entry_postcode` varchar(10) collate latin1_general_ci NOT NULL,
 `entry_city` varchar(32) collate latin1_general_ci NOT NULL,
 `entry_state` varchar(32) collate latin1_general_ci default NULL,
 `entry_country_id` int(11) NOT NULL default '0',
 `entry_zone_id` int(11) NOT NULL default '0',
 PRIMARY KEY  (`address_book_id`),
 KEY `idx_address_book_customers_id` (`customers_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--
-- Dumping data for table `address_book`
--


/*!40000 ALTER TABLE `address_book` DISABLE KEYS */;
LOCK TABLES `address_book` WRITE;
INSERT INTO `address_book` VALUES

Thanks

D4

d4funky1 · March 4, 2007

Just to add

current server: PHP Version 5.0.4 & MySQL 5.0.21-standard-log

new server: PHP Version 4.4.4 & MySQL 4.0.27-standard-log

first few lines of sql above.

Thanks

D4

d4funky1 · March 4, 2007

Just an update:

It appears that via phpmyadmin my raw sql file is too large.

Instead we are now uploading the file to the server and using SSH (putty) in which to dump the file.

When we do this we ARE presented with an error message

Here it is:

ERROR 1064 (00000) at line 23: 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 'collate latin1_general_ci NOT NULL,
 `entry_company` varchar(3

Any ideas?

Many Thanks

D4

ladym · March 4, 2007

since you have phpmyadmin you could export the table chosing the specific mysql version. I use diferent mysql version for online and offline what works best for me is open both phpmyadmin export chosing the specific version but just to sceen, then copy and past into sql tab of the other phpmyadmin. if I save the file and import it gives me an error for some reason.

March 4, 2007

at the end of the mysql command change the , to a ; and that shoud fix it. I had the same problem a few times with php5.

ERROR 1064 (00000) at line 23: 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 'collate latin1_general_ci NOT NULL,
 `entry_company` varchar(3

d4funky1 · March 4, 2007

since you have phpmyadmin you could export the table chosing the specific mysql version. I use diferent mysql version for online and offline what works best for me is open both phpmyadmin export chosing the specific version but just to sceen, then copy and past into sql tab of the other phpmyadmin. if I save the file and import it gives me an error for some reason.

Whereabouts can i choose which version to export the file in phpmyadmin?

I have been looking for this but probably can't see the wood for the trees

at the end of the mysql command change the , to a ; and that shoud fix it. I had the same problem a few times with php5.

Do you mean change ALL , to ; or just specific entries?

Thanks to you both

D4

March 4, 2007

JUST THE ONE CAUSING THE PROBLEMS.

Now see i hate the php5 system if it was easy I would tell you to regress to php4 but it is a pain.

Whereabouts can i choose which version to export the file in phpmyadmin?

I have been looking for this but probably can't see the wood for the trees

Do you mean change ALL , to ; or just specific entries?

Thanks to you both

D4

d4funky1 · March 4, 2007

so without sounding stupid,

looking at the error and the 1st few lines of my script posted previously, which line would you say is causing the error, or which line would be classed as line 23

Thanks

D4

ladym · March 4, 2007

go to export chose sql then on the right side under sql options there should be a drop down box called:

SQL export compatibility:

mybe not all phpmyadmin versions have that I'm runnign phpMyAdmin - 2.8.2.4

d4funky1 · March 4, 2007

okay,

have now exported a version using the SQL export compatibility: MYSQL40

Have uploaded the file and i am presented with the following error:

ERROR 1064 (00000) at line 12: 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 'DEFAULT CHARACTER SET latin1 COLLATE latin1_g
eneral_ci' at line

Here are the first few lines of the new sql file:

-- phpMyAdmin SQL Dump
-- version 2.8.1
-- http://www.phpmyadmin.net
-- 
-- Host: mysql01
-- Generation Time: Mar 04, 2007 at 08:04 PM
-- Server version: 5.0.21
-- PHP Version: 5.0.4
-- 
-- Database: `fancydressretailcouk_66188_db1`
-- 
CREATE DATABASE `fancydressretailcouk_66188_db1` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;
USE fancydressretailcouk_66188_db1;

-- --------------------------------------------------------

-- 
-- Table structure for table `address_book`
-- 

CREATE TABLE `address_book` (
 `address_book_id` int(11) NOT NULL,
 `customers_id` int(11) NOT NULL,
 `entry_gender` char(1) NOT NULL,
 `entry_company` varchar(32) default NULL,
 `entry_company_tax_id` varchar(32) default NULL,
 `entry_firstname` varchar(32) NOT NULL,
 `entry_lastname` varchar(32) NOT NULL,
 `entry_street_address` varchar(64) NOT NULL,
 `entry_suburb` varchar(32) default NULL,
 `entry_postcode` varchar(10) NOT NULL,
 `entry_city` varchar(32) NOT NULL,
 `entry_state` varchar(32) default NULL,
 `entry_country_id` int(11) NOT NULL default '0',
 `entry_zone_id` int(11) NOT NULL default '0',
 PRIMARY KEY  (`address_book_id`),
 KEY `idx_address_book_customers_id` (`customers_id`)
) TYPE=MyISAM

Any ideas?

Many Thanks

D4

d4funky1 · March 4, 2007

Could it have something to do with the fact that the new sql file is refering to the previous database name of:

fancydressretailcouk_66188_db1

whereas my new database is called something different?

d4funky1 · March 4, 2007

okay,

i've renamed the 3 instances that mention my previous database and changed them to the current database name.

After running the dump this is the error:

ERROR 1064 (00000) at line 1: 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 'ï»¿
CREATE DATABASE `db198681683` DEFAULT CHARACTER SET latin1

We'll get there i'm sure.

D4

MySql Version Issue

Recommended Posts

d4funky1

GemRock

d4funky1

GemRock

d4funky1

d4funky1

GemRock

d4funky1

GemRock

d4funky1

d4funky1

d4funky1

ladym

Guest

d4funky1

Guest

d4funky1

ladym

d4funky1

d4funky1

d4funky1

Archived

Browse

Activity