By lanexa on
I've found only a few posts in the forum on this topic, so please bare with me if I've missed something...
I'm trying to import a database from a server running MySQL 3.23.58 to a server running 4.1.21 - both Drupal 4.7. I've checked and both MySQL versions appear to be using Latin1 as the default character set. It appears as if the import works, but there are no tables in the new database on the new server. I tried this from both the command line and from phpMyAdmin....both with no luck.
Any experiences, suggestions are greatly appreciated...I need to migrate in the coming week, else my site will go down. Thanks in advance,
Chris
Comments
Since your both MySQL
Since your both MySQL version too far (there are too many changes from 3.23 to 4.1) then I suggest you to export 3.23 table to SQL command file then execute this SQL file on 4.1.
You can try SQLyog freeware which have easy tool to export any table to SQL command file.
Tried mysql import...
Thanks for the response.
If I understand your suggestion correctly, I should export the database to a .sql file and import the file in 4.1. Correct interpretation? If so, I did that already and the import looked like it ran, but still no tables in the database....
Is there something about SQLyog that I'm missing by using the command line or by using phpMyAdmin? Also, I don't have a Windows PC....so I don't think I can use it.
in SQLyog, by Default the
in SQLyog, by Default the Export task will not create any table. So, you must click Structure and Data.
you can open the .sql file and must be contain at the top lines something like: CREATE TABLE .....
I think I understand what
I think I understand what you are suggesting I do, but again, it isn't working. Here is a snip from my mysqldump output, which shows that the file is does in fact contain the CREATE TABLE commands.
See the following:
---------------------
#
# Table structure for table `access`
#
DROP TABLE IF EXISTS `access`;
CREATE TABLE `access` (
`aid` tinyint(10) NOT NULL auto_increment,
`mask` varchar(255) NOT NULL default '',
`type` varchar(255) NOT NULL default '',
`status` tinyint(2) NOT NULL default '0',
PRIMARY KEY (`aid`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
#
# Dumping data for table `access`
#
----------------------
I tried executing the SQL file within the SQL window in phpMyAdmin and the tables did populate on the second try. However, now when I try via a URL to see the site, this is some of the output:
Warning: Unknown column 'referer' in 'field list' query: INSERT INTO watchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, 'php', 'Unknown column 'weight' in 'order clause'\nquery: SELECT name, filename, throttle, bootstrap FROM system WHERE type =
I'm not sure what my next steps should be, but I think I've successfully be able to import the tables...now, it seems there are some missing columns??
Thanks again. I appreciate your thoughts on this,
cc
I don't have the answers, I
I don't have the answers, I posted just to watch this thread. The upgrade to MySQL 4.1 is perhaps the most challenging one because the UTF-8 character encoding was fully supported for the first time. Going to 5.0 is easier than this.
If you take a look at Drupal's database initial setup files under /database, there are two separate files, database.4.0.mysql and database.4.1.mysql. An obvious difference I can see is that in the second case after each CREATE TABLE statement there is a DEFAULT CHARACTER SET utf8; line. I am not sure if you need to add that after your CREATE TABLE statements, or if character conversion is required first for the non-latin character (such as typographic quotes, long dashes etc).
About your specific problem, does the 'referer' field actually exist in the 'watchdog' table in your database dump? Did you run the same Drupal version in your first site? (just in case it didn't have that field). Can you check with phpMyAdmin whether the 'referer' field existed there, to narrow down at which point it was lost?
--------
Edit to add: Before you use the uploaded database truncate the 'cache' table just in case some old values cause confusion.
I think I've made some
I think I've made some progress, though the results are not what I had hoped for. As you may know, there are variations in the number of fields in the database structure between older versions of Drupal running on MySQL 3.23 and the current version running on MySQL 4.1.x - herein lies my issue.
As an example, I checked the NODE table in both the "old" installation and my "new" installation, and here are the differences:
w/ MySQL 3.23
CREATE TABLE `node` (
`nid` int(10) unsigned NOT NULL auto_increment,
`type` varchar(16) NOT NULL default '',
`title` varchar(128) NOT NULL default '',
`uid` int(10) NOT NULL default '0',
`status` int(4) NOT NULL default '1',
`created` int(11) NOT NULL default '0',
`changed` int(11) NOT NULL default '0',
`comment` int(2) NOT NULL default '0',
`promote` int(2) NOT NULL default '0',
`moderate` int(2) NOT NULL default '0',
`teaser` longtext NOT NULL,
`body` longtext NOT NULL,
`revisions` longtext NOT NULL,
`sticky` int(2) NOT NULL default '0',
`format` int(4) NOT NULL default '0',
PRIMARY KEY (`nid`),
KEY `node_type` (`type`(4)),
KEY `node_title_type` (`title`,`type`(4)),
KEY `status` (`status`),
KEY `uid` (`uid`),
KEY `node_moderate` (`moderate`),
KEY `node_promote_status` (`promote`,`status`),
KEY `node_created` (`created`),
KEY `node_changed` (`changed`),
KEY `node_status_type` (`status`,`type`,`nid`)
) TYPE=MyISAM;
w/MySQL 4.1.21
CREATE TABLE node (
nid int(10) unsigned NOT NULL auto_increment,
vid int(10) unsigned NOT NULL default '0',
type varchar(32) NOT NULL default '',
title varchar(128) NOT NULL default '',
uid int(10) NOT NULL default '0',
status int(4) NOT NULL default '1',
created int(11) NOT NULL default '0',
changed int(11) NOT NULL default '0',
comment int(2) NOT NULL default '0',
promote int(2) NOT NULL default '0',
moderate int(2) NOT NULL default '0',
sticky int(2) NOT NULL default '0',
PRIMARY KEY (nid, vid),
UNIQUE KEY vid (vid),
KEY node_type (type(4)),
KEY node_title_type (title, type(4)),
KEY status (status),
KEY uid (uid),
KEY node_moderate (moderate),
KEY node_promote_status (promote, status),
KEY node_created (created),
KEY node_changed (changed),
KEY node_status_type (status, type, nid),
KEY nid (nid)
)
DEFAULT CHARACTER SET utf8;
You will note that, among other differences, there is no VID field in the "old" installation. I'm focusing on the NODE table, as for my site, that is the critical table that needs to be imported. Is the only way around this to manually edit each line of the dumpfile to account for changes in the "new" db structure? Ugh........
Thanks to everyone who has responded to me with ideas....much appreciated.
mcspoon
I see now, I think
I see now, I think
The problem is not that you are moving between MySQL versions.
Within a single Drupal installation, tables for different MySQL versions all have the same structure.
So, the problem is that you are moving between different Drupal versions!
Before I can say anything more specific, what was you old Drupal's version and what is the new one? You can find it in the first entry of their respective CHANGELOG.txt files.
Your old installation is
Your old installation is probably 4.7.0.
The revisions table (with the 'vid' key) was first introduce in 4.7.1 in May 2006
The current version is 4.7.4
If the above is correct about your versions, you have two routes:
a) install the same old version (4.7.0 if that was your version
b) update to 4.7.4 or even better drupal-4.7.x-dev which you can find on the right sidebar here and contains several bug fixes. In this case, be prepared to have to update some of your additional modules too, if they complain.
In the second case, after uploading the new files and your database and before doing anything else, you must run the update.php script to fix the tables and make them current. Te script also contains code for conversion to UTF-8.
Log in your new site as user #1 and run http://www.your-site-name.com/update.php
If you cannot log in, open the update.php file with a text editor and remove its security by changing it to
$access_check = FALSE;and then run it. After finishing the update remember to put it back to TRUE because someone can ruin your site by playing back and forth with this script.Please check your fields in both Drupal installation
1. it's look like you have different table structure on both Drupal installation
2. avoid using DROP TABLE table_name, otherwise use: CREATE TABLE IF NOT EXISTS table_name. This will give you any warning if both table have different structure.
That's exactly it.....any
That's exactly it.....any suggestions for how to proceed? Am I going to have to manually edit the dump file in order to perform the inserts?
mcspoon
There are 2 solutions
At least there are 2 solutions:
1. Easy
If server 1 contain Drupal 4.7.0 and server 2 contain Drupal 4.7.4 then you can should upgrade server 1 to Drupal 4.7.4 then do the Export of .sql file
2. Difficult
You can manually edit the .sql file to meet any field name, field type, field length, etc.
IMPORTANT: always do BACKUP before do anything. You must backup: Drupal files and Database! BACKUP FIRST!!!
How I solved the upgrade issue
I thought I would follow up with some general comments about how I ultimately addressed the issue of upgrading several different Drupal installations (different 4.x versions) from MySQL 3.23.x to MySQL 4.1.x on Fedora.
The first thing I did was backup all existing Drupal databases and dumped to a .sql file for archive purposes. Next, I installed the new 4.7.4 Drupal code base on the web server and edited the settings.php file to point to the existing database. Then, edited the update.php file according to instructions within. Then, performed the update on the existing database. Once completed, I dumped a new .sql file that includes all update changes (new fields, etc) for the 4.7.4 installation.
Next, I installed 4.7.4 on the new server and created a new, clean database using the database/database.4.1.mysql script. Then, using phpMyAdmin, I selectively ran certain INSERT statements that contained the content I wanted in my new database.
ISSUES:
The 'node' and 'node_revisions' .sql dump needed to be manually edited after the upgrade script was run, as the field order was changed. I'm not sure if I should report this somewhere or not, but it was by far the most time consuming part of this upgrade and I'm still recovering from it. I'm seeing some "duplicate" entry errors when I try and create a new node. Once I do this enough times, I'm free and clear.
THANK YOU to everyone to contributed ideas and thoughts on this. I appreciate you all taking the time.