Moving Joomla! mySQL DataBase

jleavy

Hi All,
This is a SQL iaaue I have.
I have exported a mySQL database from an old server with all tables and data.

During the import to the new server I get the following error:

ERROR: 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 38

Query = --
-- Dumping
data for table `opticsforum_smilies`
--

INSERT DELAYED IGNORE
INTO `opticsforum_smilies` (`smilies_id`, `code`, `smile_url`,
`emoticon`) VALUES
(1, ':D', 'icon_biggrin.gif', 'Very
Happy'),
(2, ':-D', 'icon_biggrin.gif', 'Very Happy'),
(3,
'', 'icon_biggrin.gif', 'Very Happy'),
(4, ':)', 'icon_smile.gif',
'Smile'),
(5, ':-)', 'icon_smile.gif', 'Smile'),
(6,
'', 'icon_smile.gif', 'Smile'),
(7, ':(', 'icon_sad.gif',
'Sad'),
(8, ':-(', 'icon_sad.gif', 'Sad'),
(9,
':sad:', 'icon_sad.gif', 'Sad'),
(10, ':o', 'icon_surprised.gif',
'Surprised'),
(11, ':-o', 'icon_surprised.gif', 'Surprised'),
(12,
':eek:', 'icon_surprised.gif', 'Surprised'),
(13,
':shock:', 'icon_eek.gif', 'Shocked'),
(14, ':?', 'icon_confused.gif',
'Confused'),
(15, ':-?', 'icon_confused.gif',
'Confused'),
(16, ':???:', 'icon_confused.gif', 'Confused'),
(17,
'8)', 'icon_cool.gif', 'Cool'),
(18, '8-)', 'icon_cool.gif',
'Cool'),
(19, ':cool:', 'icon_cool.gif', 'Cool'),
(20,
'', 'icon_lol.gif', 'Laughing'),
(21, ':x', 'icon_mad.gif',
'Mad'),
(22, ':-x', 'icon_mad.gif', 'Mad'),
(23, ':mad:',
'icon_mad.gif', 'Mad'),
(24, ':P', 'icon_razz.gif', 'Razz'),
(25,
':-P', 'icon_razz.gif', 'Razz'),
(26, ':razz:', 'icon_razz.gif',
'Razz'),
(27, ':oops:', 'icon_redface.gif',
'Embarassed'),
(28, '', 'icon_cry.gif', 'Crying or Very
sad'),
(29, ':evil:', 'icon_evil.gif', 'Evil or Very Mad'),
(30,
':twisted:', 'icon_twisted.gif', 'Twisted Evil'),
(31,
':roll:', 'icon_rolleyes.gif', 'Rolling Eyes'),
(32, '',
'icon_wink.gif', 'Wink'),
(33, '
=============================


The full import for this table reads:



CREATE TABLE IF NOT EXISTS `opticsforum_smilies` (
`smilies_id` smallint(5) unsigned NOT NULL auto_increment,
`code` varchar(50) default NULL,
`smile_url` varchar(100) default NULL,
`emoticon` varchar(75) default NULL,
PRIMARY KEY (`smilies_id`)
) TYPE=MyISAM AUTO_INCREMENT=43 ;

--
-- Dumping data for table `opticsforum_smilies`
--

INSERT DELAYED IGNORE INTO `opticsforum_smilies` (`smilies_id`, `code`, `smile_url`, `emoticon`) VALUES
(1, ':D', 'icon_biggrin.gif', 'Very Happy'),
(2, ':-D', 'icon_biggrin.gif', 'Very Happy'),
(3, '', 'icon_biggrin.gif', 'Very Happy'),
(4, ':)', 'icon_smile.gif', 'Smile'),
(5, ':-)', 'icon_smile.gif', 'Smile'),
(6, '', 'icon_smile.gif', 'Smile'),
(7, ':(', 'icon_sad.gif', 'Sad'),
(8, ':-(', 'icon_sad.gif', 'Sad'),
(9, ':sad:', 'icon_sad.gif', 'Sad'),
(10, ':o', 'icon_surprised.gif', 'Surprised'),
(11, ':-o', 'icon_surprised.gif', 'Surprised'),
(12, ':eek:', 'icon_surprised.gif', 'Surprised'),
(13, ':shock:', 'icon_eek.gif', 'Shocked'),
(14, ':?', 'icon_confused.gif', 'Confused'),
(15, ':-?', 'icon_confused.gif', 'Confused'),
(16, ':???:', 'icon_confused.gif', 'Confused'),
(17, '8)', 'icon_cool.gif', 'Cool'),
(18, '8-)', 'icon_cool.gif', 'Cool'),
(19, ':cool:', 'icon_cool.gif', 'Cool'),
(20, '', 'icon_lol.gif', 'Laughing'),
(21, ':x', 'icon_mad.gif', 'Mad'),
(22, ':-x', 'icon_mad.gif', 'Mad'),
(23, ':mad:', 'icon_mad.gif', 'Mad'),
(24, ':P', 'icon_razz.gif', 'Razz'),
(25, ':-P', 'icon_razz.gif', 'Razz'),
(26, ':razz:', 'icon_razz.gif', 'Razz'),
(27, ':oops:', 'icon_redface.gif', 'Embarassed'),
(28, '', 'icon_cry.gif', 'Crying or Very sad'),
(29, ':evil:', 'icon_evil.gif', 'Evil or Very Mad'),
(30, ':twisted:', 'icon_twisted.gif', 'Twisted Evil'),
(31, ':roll:', 'icon_rolleyes.gif', 'Rolling Eyes'),
(32, '', 'icon_wink.gif', 'Wink'),
(33, ';)', 'icon_wink.gif', 'Wink'),
(34, ';-)', 'icon_wink.gif', 'Wink'),
(35, ':!:', 'icon_exclaim.gif', 'Exclamation'),
(36, ':?:', 'icon_question.gif', 'Question'),
(37, ':idea:', 'icon_idea.gif', 'Idea'),
(38, ':arrow:', 'icon_arrow.gif', 'Arrow'),
(39, ':|', 'icon_neutral.gif', 'Neutral'),
(40, ':-|', 'icon_neutral.gif', 'Neutral'),
(41, '', 'icon_neutral.gif', 'Neutral'),
(42, '', 'icon_mrgreen.gif', 'Mr. Green');

--

---

Now, value 34 in the smilies has a ";" and I think this is a reservered char. Any ideas how to get around this.

There is over 150 tables, it's stopped at about table 40 or so, the ones before have all imported fine.

I'm using "RazorSQL" run locally on my XP box connecting into my remote mySQL server.

I can run the export again in a different compatibility mode if needed. I've tried all of them and this is the only one that actually seems to be working.

Joe Leavy

forbairt

Seems slightly strange ...

Could you remove this table from the import and see how you get on with the rest of your tables ?

jleavy

Hi,
With that table removed I get another break in the chain, this one is harder to explain as it involves some time offset.
There are a lot of tables that have now imported OK between last break and this break: (i.e. is not breaking again at the next table)

ERROR: 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 ''s:1:"0"' at line 19

Query = --
-- Dumping
data for table `web_bsq_conf`
--

INSERT DELAYED IGNORE
INTO `web_bsq_conf` (`application`, `section`, `param`,
`conftype`, `value`) VALUES
('bsq_sitestats', 'hittracking',
'trackHits', 'yesno', '1'),
('bsq_sitestats', 'hittracking',
'doKeywordSniffing', 'yesno', '1'),
('bsq_sitestats', 'hittracking',
'debugQueries', 'yesno', '0'),
('bsq_sitestats',
'hittracking', 'doIpToCountry', 'yesno', '1'),
('bsq_sitestats',
'hittracking', 'trackRS****s', 'yesno', '1'),
('bsq_sitestats',
'reporting', 'cacheTime', 'integer', '30'),
('bsq_sitestats',
'reporting', 'rowLimit', 'integer', '20'),
('bsq_sitestats',
'reporting', 'cssPrepend', 'string', 'bsq_'),
('bsq_sitestats',
'reporting', 'dateFormat', 'string', 'n/j G:i'),
('bsq_sitestats',
'reporting', 'useInternalCSS', 'yesno',
'0'),
('bsq_sitestats', 'reporting', 'useDayBoundary', 'yesno',
'1'),
('bsq_sitestats', 'reporting', 'urlsAsSEF', 'yesno',
'1'),
('bsq_sitestats', 'reporting', 'baseSiteUrl', 'string',
'http://www.xxx.com'),
('bsq_sitestats', 'reporting',
'reportHoursOffset', 'enum', 's:1:"0"
====

The full table on this one is:



CREATE TABLE IF NOT EXISTS `web_bsq_conf` (
`application` varchar(64) NOT NULL default '',
`section` varchar(64) NOT NULL default '',
`param` varchar(64) NOT NULL default '',
`conftype` varchar(64) NOT NULL default '',
`value` text NOT NULL,
PRIMARY KEY (`application`,`section`,`param`)
) TYPE=MyISAM;

--
-- Dumping data for table `web_bsq_conf`
--

INSERT DELAYED IGNORE INTO `web_bsq_conf` (`application`, `section`, `param`, `conftype`, `value`) VALUES
('bsq_sitestats', 'hittracking', 'trackHits', 'yesno', '1'),
('bsq_sitestats', 'hittracking', 'doKeywordSniffing', 'yesno', '1'),
('bsq_sitestats', 'hittracking', 'debugQueries', 'yesno', '0'),
('bsq_sitestats', 'hittracking', 'doIpToCountry', 'yesno', '1'),
('bsq_sitestats', 'hittracking', 'trackRS****s', 'yesno', '1'),
('bsq_sitestats', 'reporting', 'cacheTime', 'integer', '30'),
('bsq_sitestats', 'reporting', 'rowLimit', 'integer', '20'),
('bsq_sitestats', 'reporting', 'cssPrepend', 'string', 'bsq_'),
('bsq_sitestats', 'reporting', 'dateFormat', 'string', 'n/j G:i'),
('bsq_sitestats', 'reporting', 'useInternalCSS', 'yesno', '0'),
('bsq_sitestats', 'reporting', 'useDayBoundary', 'yesno', '1'),
('bsq_sitestats', 'reporting', 'urlsAsSEF', 'yesno', '1'),
('bsq_sitestats', 'reporting', 'baseSiteUrl', 'string', 'http://www.xxxx.com'),
('bsq_sitestats', 'reporting', 'reportHoursOffset', 'enum', 's:1:"0";'),
('bsq_sitestats', 'reporting', 'showUsersAs', 'enum', 's:1:"2";'),
('bsq_sitestats', 'rss', 'enableRSS', 'yesno', '1'),
('bsq_sitestats', 'rss', 'rssPassword', 'string', ''),
('bsq_sitestats', 'rss', 'rssFormat', 'enum', 's:6:"RSS2.0";'),
('bsq_sitestats', 'rss', 'rssNumItems', 'integer', '15'),
('bsq_sitestats', 'compression', 'hoursBeforeCompress', 'enum', 's:3:"168";'),
('bsq_sitestats', 'compression', 'rowsPerCompress', 'enum', 's:3:"500";'),
('bsq_sitestats', 'graphing', 'useJpGraph', 'yesno', '1'),
('bsq_sitestats', 'graphing', 'graphForComponent', 'yesno', '1'),
('bsq_sitestats', 'graphing', 'graphTimeFormat', 'string', 'G:i'),
('bsq_sitestats', 'graphing', 'graphDateFormat', 'string', 'n/j'),
('bsq_sitestats', 'graphing', 'graphWidth', 'integer', '500'),
('bsq_sitestats', 'graphing', 'graphHeight', 'integer', '500'),
('bsq_sitestats', 'graphing', 'graphCacheTime', 'integer', '500'),
('bsq_sitestats', 'graphing', 'visitorsGraphInterval', 'enum', 's:5:"30min";'),
('bsq_sitestats', 'graphing', 'barChartValueColor', 'string', '#880000'),
('bsq_sitestats', 'graphing', 'barChartFillColor', 'string', '#FF8888'),
('bsq_sitestats', 'frontend', 'feshowSSSummary', 'yesno', '1'),
('bsq_sitestats', 'frontend', 'feshowVisitorGraph', 'yesno', '1'),
('bsq_sitestats', 'frontend', 'feshowLatestVisitors', 'yesno', '1'),
('bsq_sitestats', 'frontend', 'feshowUserFreq', 'yesno', '1'),
('bsq_sitestats', 'frontend', 'feshowResourceFreq', 'yesno', '1'),
('bsq_sitestats', 'frontend', 'feshowBrowserFreq', 'yesno', '1'),
('bsq_sitestats', 'frontend', 'feshowRecentReferers', 'yesno', '1'),
('bsq_sitestats', 'frontend', 'feshowRefererFreq', 'yesno', '1'),
('bsq_sitestats', 'frontend', 'feshowDomainFreq', 'yesno', '1'),
('bsq_sitestats', 'frontend', 'feshowLanguageFreq', 'yesno', '1'),
('bsq_sitestats', 'frontend', 'feshowKeywordFreq', 'yesno', '1');

--

---

axer

Is the old joomla site still active?

If so then use this component to move your joomla website to a new server. It supposedly does everything you need to do automatically.

jleavy

Hi,
The old site is still "live" and I don't have acces to change anything on it sadly - I don't even have a logon to the Joomla backend.

Anyway, I am making very slow progress now again:
I figured out that the ASCII codes seem to be breaking the imports:

Eg:
Joseph o 'Leavy

This normally reads: Joseph O'Leavy, but is stored in the database as ASCII. However, even if I manually change the ASCII is still breaks as the " ' " is a reserved char in itself.

So Annoying.

axer

jleavy wrote: »

Hi,
The old site is still "live" and I don't have acces to change anything on it sadly - I don't even have a logon to the Joomla backend.

Anyway, I am making very slow progress now again:
I figured out that the ASCII codes seem to be breaking the imports:

Eg:
Joseph o 'Leavy

This normally reads: Joseph O'Leavy, but is stored in the database as ASCII. However, even if I manually change the ASCII is still breaks as the " ' " is a reserved char in itself.

So Annoying.

Have you access to the database? i.e. to make changes to the database. If so then you could either create an account there or reset the admin password. You enter this as the hashed password:
21232f297a57a5a743894a0e4a801fc3
and then login using the word "admin" as your password.

Have you access to phpmyadmin and can you enable "Complete inserts" and "Enclose table and field names with backquotes" when exporting?

jleavy

axer wrote: »

Have you access to the database? i.e. to make changes to the database. If so then you could either create an account there or reset the admin password. You enter this as the hashed password:
21232f297a57a5a743894a0e4a801fc3
and then login using the word "admin" as your password.

Have you access to phpmyadmin and can you enable "Complete inserts" and "Enclose table and field names with backquotes" when exporting?

Hi,
I've read-only access to the MySQL end and not the cpanel. So, I'm able to run an export again only.

Yup, I got the "Complete inserts" and "Enclose table and field names with backquotes" and set compatability mode to MYSQL40.

The current Server version: 4.1.22-standard
Protocol version: 10

And the new one:
Server version: 4.0.27-standard
Protocol version: 10

Hense my initial problems with the initial export.........

Joe

axer

Try exporting the old database and and importing to the new server using MySQL's free GUI Tools and see how you get on.

Webmonkey

Are you exporting to file or simply copying pasting into a file yourself. If so be careful as word can add strange charactors, do it in notepad or something. Just something to think about as it happened to me lately.

jleavy

@ Mt. Webmonkey:
I'm doing a "dump" to a single file enclosed in a transaction from the old server. Formatting looks fne in TextPad.

@ Sir. axer:
For GUI I'm using RazorSQL i think, but i will try the others you suggested..

Going by your replies to date it appears that what i got is not overly common, and certainly no-one has replied on the Joomla fourm, so I'm starting to thi that maybe i'm hitting a limit or sometihng with my new server.

I might try and log a call with them and ee if they offer any insights....

forbairt

To me it sounds like differences in the server version. Its not very clear to me why the import is failing at the points you mentioned though.

I would have assumed ';anything' would import ok ...

Normally for imports between databases I log into the machines via ssh and run mysql from the command line and do the import like that with a simple

 \. mysqlfile.sql

It normally gets rid of most problems I have with timeouts and so on.

I did come across some problems with imports of binary data contained within the files but it seemed to work eventually.

Normally if it doesn't work I do like I mentioned ... reduce the file and import what imports then take a look at what isn't working for problems.

Also check for differences with the server versions and it might be easier to trace down.

EKRIUQ

I've done this successfully in the last week so its still fresh in my mind,

I first did what you were trying to do export(dump) then import into a data base, but because I'm on shared hosting I wasn't allowed to configure my new database the same as the old.

So here's how I went about it.

On new host.

1) Created new database called "MY_NEW_DATABASE"

2)Uploaded joomla files and instaled new joomla files

3)Then configured new joomla with "MY_NEW_DATABASE"

So now I have a default joomla instalation set up on my new host

So I export(dump) from Mysql database non compressed then edited the xml file with textpad(opensourse) and removed the text "create data base OLDNAME)

Went into my new host's mysql phpadmin and imported the files to MY_NEW_DATABASE database.

And they went in fine

Then went into my template I'm using and uploaded the files I have for my template and then everything went in fine.

That worked for me, but Its just from memory as I'm not at home now



I haven't moved hosting yet so the website is hosted on two different hosters