import sql-file to MySQL 4.0.21 (coming from MySQL 5.0.51)

2,314 Posts

anonymized-26931 Reply #1, 17 years, 2 months ago

Hi there,

a clients host is running MySQL 4.0.21 / phpMyAdmin 2.6.2 and importing a MODx database-dump from my computer (MySQL-Client-Version: 5.0.51a / phpMyAdmin 3.1.3.1) results in this 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 ’DEFAULT CHARSET=utf8 COMMENT=’Contains data about active users.

i asked them to update, but they can’t give me a time frame when it will happen. Is this a really bad mismatch of MySQL versions, or is there an easy workaround?
...i guess a hosting company running such old versions, and not being able to guarantee an update aren’t the best choice anyways...

thanks, j

2,310 Posts

bunk58 Reply #2, 17 years, 2 months ago

Have you tried the export from MySQL 5 using the "SQL compatibility mode" feature set to 4?
I’ve done it a couple of times without problems.

2,314 Posts

anonymized-26931 Reply #3, 17 years, 2 months ago

Hi bunk,

thanks for the quick answer...

Have you tried the export from MySQL 5 using the "SQL compatibility mode" feature set to 4?
I’ve done it a couple of times without problems.

no, i’ll try that...can you tell me which checkboxes to check, or tick-off when i export the db via phpmyadmin, or just the default (besides the compatibility mode)?

thanks, j

2,314 Posts

anonymized-26931 Reply #4, 17 years, 2 months ago

okay, i could import it, got one error though:

--
-- Dumping data for table `modx_web_user_settings`
--

#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 ’--’ at line

and it seems that special characters are not written correctly into the database. When i compare the 2 sql-files, there seems to be a difference in the "Create Table" statement...MySQL 5 uses "DEFAULT CHARSET=utf8", whereas the MySQL 4 file has no statement concerning the Charset...

2,310 Posts

bunk58 Reply #5, 17 years, 2 months ago

What does that part of the dump file look like?

2,314 Posts

anonymized-26931 Reply #6, 17 years, 2 months ago

-- phpMyAdmin SQL Dump
-- version 3.1.3.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 26, 2009 at 10:41 AM
-- Server version: 5.1.33
-- PHP Version: 5.2.9


/*!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 */;

--
-- Database: `dbtest`
--

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

--
-- Table structure for table `modx_active_users`
--

CREATE TABLE IF NOT EXISTS `modx_active_users` (
  `internalKey` int(9) NOT NULL DEFAULT '0',
  `username` varchar(50) NOT NULL DEFAULT '',
  `lasthit` int(20) NOT NULL DEFAULT '0',
  `id` int(10) DEFAULT NULL,
  `action` varchar(10) NOT NULL DEFAULT '',
  `ip` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`internalKey`)
) TYPE=MyISAM COMMENT='Contains data about active users.';

error should be at line 3

2,310 Posts

bunk58 Reply #7, 17 years, 2 months ago

According to the error, shouldn’t it be the ’modx_web_user_settings’ table?

2,314 Posts

anonymized-26931 Reply #8, 17 years, 2 months ago

ah, sorry...

--
-- Table structure for table `modx_web_user_settings`
--

CREATE TABLE IF NOT EXISTS `modx_web_user_settings` (
  `webuser` int(11) NOT NULL,
  `setting_name` varchar(50) NOT NULL DEFAULT '',
  `setting_value` text,
  KEY `setting_name` (`setting_name`),
  KEY `webuserid` (`webuser`)
) TYPE=MyISAM COMMENT='Contains web user settings.';

thought the error would be at line 3 of the sql-file

☆ A M B ☆
24,524 Posts

sottwell Reply #9, 17 years, 2 months ago

It looks like it isn’t happy with those -- comment delimiters. Could be the charset of the original doesn’t match the charset of the new one. You can try replacing those -- markers with a # to indicate a comment.

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

2,314 Posts

anonymized-26931 Reply #10, 17 years, 2 months ago

thanks sottwell! no errors importing the sql-file!

since this is an old version of phpmyadmin, and the hosting company is creating a blank database for me, how could i define the Database CharacterSet & Collation before importing the sql-file?

ALTER DATABASE `mydbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

doesn’t work, gives me this error again:

#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 ’DATABASE `mydbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_gen

"ß" is written as "ÃŸ" in the db

j