Re: Moving / Exporting Joomla's mySql DB to another host or server via phpmyadmi

guilliam · Post by **guilliam** » Wed Feb 21, 2007 5:46 am

have you choosed the box in your host1 the option of mysql version> you should select mysql4 for compatibility.

plus select this options:

Add DROP TABLE
Add IF NOT EXISTS
Add AUTO_INCREMENT value

see screenshot below:

its the dump the db to a compress file method not the copy to clipboard.

- g

PROXIMO · Post by **PROXIMO** » Wed Feb 21, 2007 6:03 am

yeah , I just tried them all after you told me, still same error message ,my old hosting company is using very old version of mysql and new company I am going to use now ,is using very new version and the new company's mysql doesn't recognise the old version.

if you have time and if it is not too much for the ask, can I give you my info and could you please take a look at it?

ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=3' at line 19

Post by **infograf768** » Wed Feb 21, 2007 8:51 am

PROXIMO wrote:
) ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=3' at line 19

so I dont know what to do, can you give me a hand please? thanks very much

When query to a UTF-8 db, take off the DEFAULT CHARSET=latin1 from the dump.
Obviously, the whole dump has to go through a text editor and saved as UTF8 NO BOM before the query.

PROXIMO · Post by **PROXIMO** » Wed Feb 21, 2007 9:34 am

I just did the way you told me Sir.I went in to database and opened the database without saaving then I selected them all copy them and paste them to notepad then save as utf8 then I went to my new host database and exported them. and this time I have gotten different error I believe that.Thanks for trying. ( hold on ,I didn't do that part of take off the charset=latin1 part himm ?) let me try that one sorry)
Error
SQL query:

-- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 21, 2007 at 11:28 AM
-- Server version: 4.1.21
-- PHP Version: 4.4.2
--
-- Database: `aydinkoy_aydinkoyu`
--
-- --------------------------------------------------------
--
-- Table structure for table `jos_banner`
--
CREATE TABLE `jos_banner` (

`bid` int( 11 ) NOT NULL AUTO_INCREMENT ,
`cid` int( 11 ) NOT NULL default '0',
`type` varchar( 10 ) NOT NULL default 'banner',
`name` varchar( 50 ) NOT NULL default '',
`imptotal` int( 11 ) NOT NULL default '0',
`impmade` int( 11 ) NOT NULL default '0',
`clicks` int( 11 ) NOT NULL default '0',
`imageurl` varchar( 100 ) NOT NULL default '',
`clickurl` varchar( 200 ) NOT NULL default '',
`date` datetime default NULL ,
`showBanner` tinyint( 1 ) NOT NULL default '0',
`checked_out` tinyint( 1 ) NOT NULL default '0',
`checked_out_time` datetime NOT NULL default '0000-00-00 00:00:00',
`editor` varchar( 50 ) default NULL ,
`custombannercode` text,
PRIMARY KEY ( `bid` ) ,
KEY `viewbanner` ( `showBanner` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =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 'ï»¿

CREATE TABLE `jos_banner` (
`bid` int(11) NOT NULL

PROXIMO · Post by **PROXIMO** » Wed Feb 21, 2007 9:52 am

I just took off all the charset latin=1 from db and got this error
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 'ï»¿

CREATE TABLE `jos_banner` (
`bid` int(11) NOT NULL

I guess I should go jump to the cold river maybe I can do that then

or get a cold shower lol

PROXIMO · Post by **PROXIMO** » Wed Feb 21, 2007 10:23 am

Evraka Evrakaaaaa, I did it , actualy I didn't do it you guys did, I am glad to I am here and also you guys are here,
I would like to special thanks to infograf768 and guilliam , thanks to you guys,
I changed the MYSQL Compatibility MOde to = MYSQL323 and then I save it ,and thats it.
Thanks a lot for your help. I will be around here and I am going to help to other people whoever need help on my knowledge.
Thanks to everyone.

guilliam · Post by **guilliam** » Wed Feb 21, 2007 5:48 pm

@PROXIMO, warmly welcome. that did the trick. missed to ask what version of mysql you have on your host1.

happy to hear youre one merry joomla user!

thanks for posting this solution, will need to update the tutorial to include this as a NOTE for those using mysql 3.x

- g

trgraglia · Post by **trgraglia** » Fri Feb 23, 2007 4:24 pm

Ok... I develop sites on my own server and transfer to the clients.

I am transfering a DB from MySQL client version: 4.1.21, phpMyAdmin - 2.9.0.2

-to-

MySQL 4.0.21-nt, phpMyAdmin 2.5.0

I have read this forum back to front and front to back trying everything you have thrown out there. When i try to import or run the query, it merely says "No SQL query!"

Sorry to sound so lame... but anyone?

guilliam · Post by **guilliam** » Fri Feb 23, 2007 4:57 pm

looks like youre clicking a wrong button and missed something in the tutorial along the process.
can you do it again, im sure you missed one in the instructions given(the one with visuals and screenshots).
if it says "no sql query" you are probably "importing" a dump without a insert or update in the file.

- g

trgraglia · Post by **trgraglia** » Fri Feb 23, 2007 6:17 pm

ill try again right now... thx

trgraglia · Post by **trgraglia** » Fri Feb 23, 2007 6:24 pm

same thing...

-- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 23, 2007 at 11:20 AM
-- Server version: 4.1.21
-- PHP Version: 4.4.2
--
-- Database: `nsanegra_gusdfund`
--

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

--
-- Table structure for table `jos_banner`
--

CREATE TABLE `jos_banner` (
`bid` int(11) NOT NULL auto_increment,
`cid` int(11) NOT NULL default '0',
`type` varchar(10) NOT NULL default 'banner',
`name` varchar(50) NOT NULL default '',
`imptotal` int(11) NOT NULL default '0',
`impmade` int(11) NOT NULL default '0',
`clicks` int(11) NOT NULL default '0',
`imageurl` varchar(100) NOT NULL default '',
`clickurl` varchar(200) NOT NULL default '',
`date` datetime default NULL,
`showBanner` tinyint(1) NOT NULL default '0',
`checked_out` tinyint(1) NOT NULL default '0',
`checked_out_time` datetime NOT NULL default '0000-00-00 00:00:00',
`editor` varchar(50) default NULL,
`custombannercode` text,
PRIMARY KEY (`bid`),
KEY `viewbanner` (`showBanner`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `jos_banner`
--

INSERT INTO `jos_banner` VALUES (1, 1, 'banner', 'OSM 1', 0, 42, 0, 'osmbanner1.png', 'http://www.opensourcematters.org', '2004-07-07 15:31:29', 1, 0, '0000-00-00 00:00:00', NULL, NULL);
INSERT INTO `jos_banner` VALUES (2, 1, 'banner', 'OSM 2', 0, 49, 0, 'osmbanner2.png', 'http://www.opensourcematters.org', '2004-07-07 15:31:29', 1, 0, '0000-00-00 00:00:00', NULL, NULL);

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

guilliam · Post by **guilliam** » Fri Feb 23, 2007 6:42 pm

what do you mean same thing? that dump you posted above is a good dump. maybe you interchange "export" from "import" in the 2nd data base.

- g

trgraglia · Post by **trgraglia** » Fri Feb 23, 2007 6:47 pm

there is no import... i just click on the SQL tab and upload the sql file for the query... am i wrong? sorry for the problem...

guilliam · Post by **guilliam** » Fri Feb 23, 2007 6:51 pm

trgraglia wrote: there is no import... i just click on the SQL tab and upload the sql file for the query... am i wrong? sorry for the problem...

ahh.. okies.. when youre viewing that DUMP in a text editor or from the result page of db1, select ALL(ctrl + a) then copy.

in the SQL link in the 2nd database, paste or ctrl+p,

click the run or "go" button in phpmyadmin of your 2nd db.

- g

trgraglia · Post by **trgraglia** » Fri Feb 23, 2007 6:54 pm

well its a step forward...

Error

SQL-query :

CREATE TABLE `jos_banner` (

`bid` int( 11 ) NOT NULL AUTO_INCREMENT ,
`cid` int( 11 ) NOT NULL default '0',
`type` varchar( 10 ) NOT NULL default 'banner',
`name` varchar( 50 ) NOT NULL default '',
`imptotal` int( 11 ) NOT NULL default '0',
`impmade` int( 11 ) NOT NULL default '0',
`clicks` int( 11 ) NOT NULL default '0',
`imageurl` varchar( 100 ) NOT NULL default '',
`clickurl` varchar( 200 ) NOT NULL default '',
`date` datetime default NULL ,
`showBanner` tinyint( 1 ) NOT NULL default '0',
`checked_out` tinyint( 1 ) NOT NULL default '0',
`checked_out_time` datetime NOT NULL default '0000-00-00 00:00:00',
`editor` varchar( 50 ) default NULL ,
`custombannercode` text,
PRIMARY KEY ( `bid` ) ,
KEY `viewbanner` ( `showBanner` )
)ENGINE = MYISAM DEFAULTCHARSET = latin1 AUTO_INCREMENT = 3

MySQL said:

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=latin1 AUTO_INCREMENT=3' at line 19
Back

ill try a few options like before with other people... unless you notice something about this paste

trgraglia · Post by **trgraglia** » Fri Feb 23, 2007 6:56 pm

GOT IT BY CHANGING compatibility... THANKS MAN! YOU HAVE SAVED THE DAY!

trgraglia · Post by **trgraglia** » Fri Feb 23, 2007 7:05 pm

but the site does not work... another issue i know but at the bottom of the DB page it says:

Error
The additional Features for working with linked Tables have been deactivated. To find out why click here.

guilliam · Post by **guilliam** » Fri Feb 23, 2007 7:22 pm

trgraglia wrote: but the site does not work... another issue i know but at the bottom of the DB page it says:

Error
The additional Features for working with linked Tables have been deactivated. To find out why click here.

thats another issue not related anymore with this thread "Moving / Exporting Joomla's mySql DB to another host or server via phpmyadmi"

happy to know you got it finally working.
on your issue though, search on the forum and im sure a solution will come in handy

- g

haktanir · Post by **haktanir** » Tue Mar 06, 2007 7:10 pm

Guys can someone please help me with something, it's been driving me nuts.

I followed the method the create a mysql dump word by word, but all it gives me in the end is a 0 byte file.

How can I solve this?

guilliam · Post by **guilliam** » Thu Mar 08, 2007 6:51 pm

haktanir wrote: Guys can someone please help me with something, it's been driving me nuts.

I followed the method the create a mysql dump word by word, but all it gives me in the end is a 0 byte file.

How can I solve this?

looks like you havent selected any tables to be created for the dump.

- g

haktanir · Post by **haktanir** » Thu Mar 08, 2007 7:52 pm

Nope the problem was that Yahoo Webhosting simply sucks. Although I selected the tables, it had no effect.

Anyway, I managed to find a script online to get my dump without having to use Yahoo's phpadmin addon.

boonetp · Post by **boonetp** » Sat Mar 17, 2007 9:01 pm

Hello Folks I have an interesting problem that I haven't found a topic that answers my problem. I have successfully moved my Joomla site from ISP-FROM to ISP-DESTINATION, except for jos_users. As I look at the jos_users table on ISP-DESTINATION I see my Joomla users. If I look within the Joomla Administrator I do not see the users. I only see the Administrator user created with the new installation of Joomla on ISP-DESTINATION.

From within Joomla Administrator I attempted to create a user with the same username of one found in the jos_users table on ISP-DESTINATION, but I get an error that the username already exists.

I've also checked the auto-increment value on the jos_users table on ISP-DESTINATION, which is also correct.

Any ideas what is wrong?

TIA!

guilliam · Post by **guilliam** » Sun Mar 18, 2007 4:38 am

boonetp wrote: Hello Folks I have an interesting problem that I haven't found a topic that answers my problem. I have successfully moved my Joomla site from ISP-FROM to ISP-DESTINATION, except for jos_users. As I look at the jos_users table on ISP-DESTINATION I see my Joomla users. If I look within the Joomla Administrator I do not see the users. I only see the Administrator user created with the new installation of Joomla on ISP-DESTINATION.

From within Joomla Administrator I attempted to create a user with the same username of one found in the jos_users table on ISP-DESTINATION, but I get an error that the username already exists.

I've also checked the auto-increment value on the jos_users table on ISP-DESTINATION, which is also correct.

Any ideas what is wrong?

TIA!

seems to me that the table jos_users was not transfered correctly. if the contents, menus, other components works and only the user is the prob; you may retransfer again the users tables and the other ACL tables(that can be found in this thread, earlier replies).

- g

WindWarrior · Post by **WindWarrior** » Tue Mar 20, 2007 12:25 am

Hi guys
I am pulling my hair out with this one so would love some help if possible
Through reading some of th posts above i worked out i had a compatibility issue with mysql v5.X on my server and the mysql v4.X that comes with Ravenswood.
I selected the mysql 4.0 compatibility and exported the data.... but when I attempt to import into database into the local server I get the following error message
Error

SQL query:

-- phpMyAdmin SQL Dump
-- version
-- http://www.phpmyadmin.net
--
-- Host: mysql5.ilisys.com.au
-- Generation Time: Mar 20, 2007 at 11:09 AM
-- Server version: 5.0.27
-- PHP Version: 4.4.2
--
-- Database: `sphinq_db`
--
-- --------------------------------------------------------
--
-- Table structure for table `jos_banner`
--
DROP TABLE IF EXISTS `jos_banner` ;

MySQL said: Documentation
#1046 - No Database Selected

I know I am doing this back to front (moving a built site from a live server onto a local server) but the plan is to make any further mods and updates locally and then upload the update database !

Any words of wisdom ?

WindWarrior · Post by **WindWarrior** » Tue Mar 20, 2007 1:53 am

Bumbling my way through I managed to get the database and tables to import... here's what i did or more to the point what I was doing wrong

From within phpMyAdmin where i was trying to import the database to my local server i had not selected mysql in the drop down on the left hand side (it lists different databases)
I had assumed* it would simply import in the default mode... but when I gave this alittle more thought it was pretty simple to see that i had not really told the phpMyAdmin where to dump/install/import the database to !

Well my data base is now in place and I am prepairing to do battle with the config files !

PS great work by guiliam and other Joomla Heros on this topic and others

WindWarrior

WindWarrior · Post by **WindWarrior** » Tue Mar 20, 2007 3:04 am

OK database is in place and site is running on local server all good EXCEPT -

Failed to backup database on local server before importing db from live server (no biggy as I had only made minor modifications but is something to think about !)

I can log into the admin section no problems but can not view 2 modules I have installed... the modules are there in the modules folder but do not appear on the module list within the joomla admin... I can not perform a new install as joomla admin says the folders are already there and am I trying to install the same module twice ?

Question - How can I get the module to work or be recognised within the admin interface ?

Broken links... I suspect I may not have set up my config file correctly as none of the menus work correctly.... by this I mean if I click on a link/menu i get the following error

Not Found

The requested URL /content/view/1/1/ was not found on this server.

Question - How do I repair this ?

Please help as this is seriously doing my head in !

WindWarrior · Post by **WindWarrior** » Thu Mar 22, 2007 1:55 am

IS ANYBODY ABLE TO PROVIDE ADVICE OR EVEN READING THIS ?

Fixed the unaligned module issues be re-importing the modules via ftp and then doing a new database import... still no luck getting the menu links to work on the local server.....

anything ?

any body ?

So much for the so called 'open source' for answers and solutions

WindWarrior · Post by **WindWarrior** » Thu Mar 22, 2007 7:14 am

SO WHO DO YOU HAVE TO PAY TO GET A REPLY ON THIS DAMN FORUM ?
3 DAYS WITHOUT SO MUCH AS A HINT IS PRETTY PITIFUL... THIS WOULD BE FUNNY IF IT WASN'T TOUTED AS THE ULTIMATE OPEN SOURCE COLABERRATION !
WHAT A HOAX

howdwegethere · Post by **howdwegethere** » Thu Mar 22, 2007 11:26 am

Hi WindWarrior

I know it can be frustrating. I remember once I got a reply six months after posting a question. Sometimes, nobody is watching and sometimes nobody knows the answer either.

I'm not sure I can help but I'm happy to try. I'm no expert etc etc. Somebody else might be able to spot what your problem is. I can't but I could help you try to work it out.

Perhaps you could post your config.php and somebody might spot your problem. But I would have thought if you can get to your home page, your config should be okay.

Have you tried making a new menu item to see if that link works?
Can you see all your content items in the admin area ie. did your database import work okay.

Adam

Steve · Post by **Steve** » Thu Mar 22, 2007 9:34 pm

The requested URL /content/view/1/1/ was not found on this server.

You appear to have SEF on try turning it off under "global configurations->SEO tab" to see if your links work.

The Joomla! Forum™