Converting DB from OSC2.2 to OSCMAX2.2

[Twizted]

Hello, I am sorry to post this yet again but the thread I found about converting a DM using MySQLdiff was closed and little to no good info can actually be found on this.

I am attempting to convert my production store which is a modified version of OSC2.2 to OSCMAX

I have made two new databases to do this and imported the old (OSC2.2) db and in the other imported the new OSCMAX (stock) db.

I ran mysqldiff and left all the options as is.. and got the file to convert.. I copied and pasted it into an SQL query in phpMyAdmin for the OLD db... when I run it I get this error right off the bat

#
# DDL START
#
CREATE TABLE admin( admin_id int( 11 ) NOT NULL DEFAULT 0 COMMENT '' AUTO_INCREMENT ,
admin_groups_id int( 11 ) NULL DEFAULT NULL COMMENT '',
admin_firstname varchar( 32 ) NOT NULL DEFAULT '' COMMENT '' COLLATE latin1_swedish_ci,
admin_lastname varchar( 32 ) NULL DEFAULT NULL COMMENT '' COLLATE latin1_swedish_ci,
admin_email_address varchar( 96 ) NOT NULL DEFAULT '' COMMENT '' COLLATE latin1_swedish_ci,
admin_password varchar( 40 ) NOT NULL DEFAULT '' COMMENT '' COLLATE latin1_swedish_ci,
admin_created datetime NULL DEFAULT NULL COMMENT '',
admin_modified datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '',
admin_logdate datetime NULL DEFAULT NULL COMMENT '',
admin_lognum int( 11 ) NOT NULL DEFAULT '0' COMMENT '',
PRIMARY KEY ( admin_id ) ,
UNIQUE admin_email_address( admin_email_address ) 
) DEFAULT CHARSET = latin1 COLLATE = latin1_swedish_ci;
 
 
 
MySQL said:  
#1067 - Invalid default value for 'admin_id' 

I tried removing that section and it would error again on the very next section.. Could you please tell me what I am doing wrong or possibly point me to a thread explaining how to convert from a old osCommerce MS2.2 to OSCMAX2.. I have read a few and not really getting anywhere

thank you for your time...

**reposted from PM for others benifit**

[michael_s]

Change the default value from zero to 1:

DEFAULT 1

Do a search and replace in the entire sql and change any zero default values to 1 or drop the default value altogther. I think it is due to the age of the mysqldiff application, it is not 100% compatible with the current mysql version as it was developed years ago and has not been updated.

[Twizted]

Uh oh... I just tried it and no go..

Error

SQL query: 

#
# DDL START
#
CREATE TABLE admin( admin_id int( 11 ) NOT NULL DEFAULT 1 COMMENT '' AUTO_INCREMENT ,
admin_groups_id int( 11 ) NULL DEFAULT NULL COMMENT '',
admin_firstname varchar( 32 ) NOT NULL DEFAULT '' COMMENT '' COLLATE latin1_swedish_ci,
admin_lastname varchar( 32 ) NULL DEFAULT NULL COMMENT '' COLLATE latin1_swedish_ci,
admin_email_address varchar( 96 ) NOT NULL DEFAULT '' COMMENT '' COLLATE latin1_swedish_ci,
admin_password varchar( 40 ) NOT NULL DEFAULT '' COMMENT '' COLLATE latin1_swedish_ci,
admin_created datetime NULL DEFAULT NULL COMMENT '',
admin_modified datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '',
admin_logdate datetime NULL DEFAULT NULL COMMENT '',
admin_lognum int( 11 ) NOT NULL DEFAULT '0' COMMENT '',
PRIMARY KEY ( admin_id ) ,
UNIQUE admin_email_address( admin_email_address ) 
) DEFAULT CHARSET = latin1 COLLATE = latin1_swedish_ci;


MySQL said:  
#1067 - Invalid default value for 'admin_id'

Appears to be the same error...

[Twizted]

as an experiment I tried setting the default ID to null and it worked... and got past the first three sections and on the 4th errored because I had not set it to null.. What will setting them to null do?

**EDIT**
Yep, I changed the Default ID (first line of all the CREATE TABLE calls) down to Affiliate_Sales and BAM... Right after that one..

Error

SQL query: 

CREATE TABLE article_reviews( reviews_id int( 11 ) NOT NULL DEFAULT 0 COMMENT '' AUTO_INCREMENT ,
articles_id int( 11 ) NOT NULL DEFAULT '0' COMMENT '',
customers_id int( 11 ) NULL DEFAULT NULL COMMENT '',
customers_name varchar( 64 ) NOT NULL DEFAULT '' COMMENT '' COLLATE latin1_swedish_ci,
reviews_rating int( 1 ) NULL DEFAULT NULL COMMENT '',
date_added datetime NULL DEFAULT NULL COMMENT '',
last_modified datetime NULL DEFAULT NULL COMMENT '',
reviews_read int( 5 ) NOT NULL DEFAULT '0' COMMENT '',
approved tinyint( 3 ) unsigned NULL DEFAULT '0' COMMENT '',
PRIMARY KEY ( reviews_id ) 
) DEFAULT CHARSET = latin1 COLLATE = latin1_swedish_ci;



MySQL said:  
#1067 - Invalid default value for 'reviews_id'

Now I just need to know how to get past that... I mean... Will using null hurt or what? I am a bit of a n00b in this respect.

[michael_s]

No, default values only tell what to enter in for the field if there is no input for it. You should be fine.

[Twizted]

OMG.. I am about to go nuts... I had set them all to NULL

DEFAULT 0 -> DEFAULT NULL
and
DEFAULT '0' -> DEFAULT 'NULL'

so then I get an error one saying it cant have a default value... :'(

OMG I wish this was easier.. I dont really know enough to edit SQL files.... Would anyone be willing to help me.. Its not a monsterous database.. the original (OSC2.2) is only 1mb.... I just do not want to loose my customers nor am I very keen on having to reload all the products again (I have started this process a few other times to no avail and in frustration usually throw my hands up and quit but this time I "need" to get this working)

Any help would be awesome.. Please...

[michael_s]

Just remove the default value parameter entirely and I think you will be fine. I think I ran into this a while back on an update I did and that is how I worked around it. It was a while ago so my memory is a bit foggy, but it only took a couple of minutes to work around.

[Twizted]

I tried that as I stated it come up saying something couldnt have a default value or something.. I am not sure..

[oscjww103]

i am having exactly the same problem as this - does anyone have a solution?

[tcshadow]

also having the same problem... trying to find a workaround I'll post it here if I find one