test site to new server issue

[lennon]

Hi I'm having a bit of an install problem.

I've a site on test server. Its finished and I'm moving it over to the live server.

I can install and get an old version of my site from a database. Any of the new backups I've made
return the following in phpmyadmin when I upload.

It weird that an old version of my site works fine but not the recent back ups.

Code: Select all

SQL query:

-- phpMyAdmin SQL Dump -- version 2.7.0-pl2 -- http://www.phpmyadmin.net -- -- Host: 82.195.146.249 -- Generation Time: Mar 30, 2006 at 04:20 PM -- Server version: 4.0.24 -- PHP Version: 4.4.0 -- -- Database: `galwayr_database` -- -- -------------------------------------------------------- -- -- Table structure for table `cms_additional_htmlblob_users` -- CREATE TABLE `cms_additional_htmlblob_users` ( `additional_htmlblob_users_id` int(11) NOT NULL default '0', `user_id` int(11) default NULL, `htmlblob_id` int(11) default NULL, PRIMARY KEY (`additional_htmlblob_users_id`) ) TYPE=MyISAM;

MySQL said: Documentation
#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 '

CREATE TABLE `cms_additional_htmlblob_users` (
  `additio

This is driving me mad and the deadline looms. The client thinks this is finished....and I suppose it is bar this.

If anyonehas any ideas then please please let me know

[Dr.CSS]

Found this on Documentation page,,

Move site to another directory

Sometimes you may wish to move your site to another directory. Either because you have developed the site in a sub-directory while another site has been the live site. Or perhaps you have developed the site on your local computer and now want to move it to your host.

Then follow this procedure:

  1. Move all your files from the previous directory to your new directory.
  2. Make sure that all permissions are set correct after the move (see step 4 of the Quick Install Guide for the necessary permissions.)
  3. Edit the config.php file in your root directory and change all the URLs and paths that are pointing to the old location.
  4. If you have any hard links in your site, you may have to change them also.

It should be as easy as that!

we hope so 

  HTH
          mark

[Dee]

Hmm, tough one. I've experienced trouble with SQL syntax errors when restoring phpmyadmin backups, but think that was before "Enclose table and field names with backquotes" was turned on by default in phpmyadmin and it was not with a CMSMS backup.

Do you get the same error if you fill a db with the backup on your test server? If so it's probably easier to try get your backup/sql file working on the test server first.

Installing the tables by hand to see what happens can sometimes lighten things up:

Code: Select all

mysql -u [username] -p [dbname] < [backupfilename.sql]

When you get an error message, try to find what mysql is complaining about, then look at the end of the message, it'll end with  "at line ...". Check that line of your backupfile for things that could be disturbing mysql (strange characters for example) and remove them. 
The problem seems to be in the inserts of some of the user htmlblobs. Removing those (or that) insert(s) from the backup and doing those inserts (one by one) afterwards is an option.

Another thing to try is create new backups with some extra options on (not sure what "Complete inserts" and "Extended inserts" does exactly, but maybe it helps).

It would help if you post the line of code in your backupfile that causes the error (and a few before and after) if you don't/didn't get it working.

Good luck, hope you get it up in time!

[Ted]

I've seen this issue with some editors on windows.  It's making junk in the first few characters of a text file, and you can't really see them in notepad.  I have NO idea where it comes from or what the reason is.

Your best hope is to open the file in something like textpad or notepad2 and see if you can see those nasty characters at the front.  If so, delete them.  If not, let me know.  Unix edtiors seem to have no problem finding and removing them.  I could easily fix it for you and send the file back.

[lennon]

Thanks for the response guys.

Maksbud - its not a directory move its a server move. Wish it was that simple.

Dee - I've tried using the extra options in the export - no joy there Im afraid.

This is the complete error I get on import:

Code: Select all

There is a chance that you may have found a bug in the SQL parser. Please examine your query closely, and check that the quotes are correct and not mis-matched. Other possible failure causes may be that you are uploading a file with binary outside of a quoted text area. You can also try your query on the MySQL command line interface. The MySQL server error output below, if there is any, may also help you in diagnosing the problem. If you still have problems or if the parser fails where the command line interface succeeds, please reduce your SQL query input to the single query that causes problems, and submit a bug report with the data chunk in the CUT section below:
----BEGIN CUT----
eNqdUs1um0AYvO9TzCFSbAkwYOzaW/VAHeRYBZsCSeVe7HXY1FSACSxx/Gw59JH6Ct21Qn8OTaWu
tHuYb76Zbz6tF0WriGJmYWbD95YUFmyMrSGJE4k/EzK7jSkuFilF85BXrG54beTZzqj2lfYI23Bs
wzZkk2mOBpY1sCewJtQeUsdGmX3lObynChckOMUffQrHMA3b0RvBypTVKbmJI6xiDe7cWyYabj1p
+ikrEaw+L3zfxcgwSRi4VBq9MUy9ym0SXoeKp61iJScF4Wdl+0R8dzmn4KWeNQd9MhlNdYucPb8/
f9N1yHmDk5sWUlyiuGqLikj4kddNdih/05fgXoiKDgbH41HFLE5MtRklF6qo7vWhERQTGXw6Mixn
LDNNFT7nJa+ZUIJJVnCKgNUYmppazxhMwHSobSIMFDnmtXTvJuh2oyrniL9gWeiMr5hgO9ZI5e0X
lh/Zqd6kL9D2zFGP/p+HdC4J2+UcjajbO9HWHPeHGuKMbe+KZsPSNFMZWb7ZiyLf5YfdppX/onkZ
YRZ5buIhcd/73r860CPA9q/1TZZukZWiZ1l9LFcJlje+j5TfszYXuDQvNdWuqH8wO4Jinxk/VV9j
hdEicKM1PnhroPf6UH3SR7IOvXfBaRG7wdsf3CXzBA==
----END CUT----
----BEGIN RAW----

ERROR: C1 C2 LEN: 1 2 613
STR: »

CVS: $Id: sqlparser.lib.php,v 2.42.2.1 2005/11/28 18:23:42 nijel Exp $
MySQL: 4.0.24-standard
USR OS, AGENT, VER: Win MOZILLA 5.0
PMA: 2.7.0-pl2
PHP VER,OS: 4.4.0 Linux
LANG: en-iso-8859-1
SQL: -- phpMyAdmin SQL Dump
-- version 2.7.0-pl2
-- http://www.phpmyadmin.net
-- 
-- Host: 82.195.146.249
-- Generation Time: Mar 30, 2006 at 04:20 PM
-- Server version: 4.0.24
-- PHP Version: 4.4.0
-- 
-- Database: `galwayr_database`
-- 

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

-- 
-- Table structure for table `cms_additional_htmlblob_users`
-- 

CREATE TABLE `cms_additional_htmlblob_users` (
  `additional_htmlblob_users_id` int(11) NOT NULL default '0',
  `user_id` int(11) default NULL,
  `htmlblob_id` int(11) default NULL,
  PRIMARY KEY  (`additional_htmlblob_users_id`)
) TYPE=MyISAM;

----END RAW----

SQL query:

-- phpMyAdmin SQL Dump -- version 2.7.0-pl2 -- http://www.phpmyadmin.net -- -- Host: 82.195.146.249 -- Generation Time: Mar 30, 2006 at 04:20 PM -- Server version: 4.0.24 -- PHP Version: 4.4.0 -- -- Database: `galwayr_database` -- -- -------------------------------------------------------- -- -- Table structure for table `cms_additional_htmlblob_users` -- CREATE TABLE `cms_additional_htmlblob_users` ( `additional_htmlblob_users_id` int(11) NOT NULL default '0', `user_id` int(11) default NULL, `htmlblob_id` int(11) default NULL, PRIMARY KEY (`additional_htmlblob_users_id`) ) TYPE=MyISAM;

MySQL said: Documentation
#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 '

CREATE TABLE `cms_additional_htmlblob_users` (
  `additio

I wonder is there a way to copy over just the content and page structure. I may end up doing this manually. At least its a way out of this.

Ted - I tried looking in notepad2 for anything strange - again no joy.

I might start the manual copry and paste soon. Will take all day though...

Thanks guys

[Dee]

Somehow I'm pretty convinced there's some strange characters in the user added htmlblobs.
The syntax to use near ' message also makes it looks like so.

There's no linenumber at the end of the errormessage (this is the full error?)?
The error message doesn't explain much, need to see the concerning lines from the backup file.

If you open the backupfile in a texteditor and start a textsearch, starting from "CREATE TABLE `cms_additional_htmlblob_users`" and search for ï, do you find a insert command/line with  in it  somewhere? If so then remove those characters and try again.

I guess I would remove the part of the backup sql where the cms_additional_htmlblob_users table is filled/entries into that table are inserted. That is, empty the table (from a copy of course) before you make a backup, or remove from the current backup all the lines starting with:

Code: Select all

INSERT INTO `cms_additional_htmlblob_users`

.
Then paste those insert commands one by one in phpmyadmin to see which one is giving the error and fix it.

To be sure you could also remove the table creation command (the line starting with

Code: Select all

CREATE TABLE `cms_additional_htmlblob_users`

) and create the table manually by pasting the line in phpmyadmin (although theoretically there can be no errors in that command and things must be going wrong with the inserts).

I think that if you (make a copy of the site and then) remove or empty all user htmlblobs from the admin panel and then make a backup, you won't get any errors when restoring (and 'll only have to create and copy/paste the html blobs into the new admin panel).

[Ted]

Well, as a last resort, if you want to pm any relavant login information, I can take a stab at it...

[lennon]

Cheers guys for your very kind offers. This is a great forum.

I was running out of time and so I hand copyied the lot. Got it done in time. Had to make the decision to either keep looking for a fix or to do it manually.

Only took 5 hours!!!

The sites up now I'll post on the show if your interested...

and thanks once again everyone.

[thorntma]

I had this same thing happen to me.  I opened my sql dump in MS Word and viewed it with each of the encoding types.  In UTF-8, I could not see the extra characters at the top of the file.  But, when I switched to MS-DOS encoding, I could see and remove it.  All worked well after that.