News:

Join the Facebook Fan Page.

Main Menu

Updating problem

Started by PD3, May 09, 2020, 08:00:41 AM

Previous topic - Next topic

PD3

Hi all.

I'm in the process of trying to update from SMF 1 to 2. I've got as far as running the update, but then ran cross a problem. The updater was returning a error, saying smf_log_errors was missing.

After searching around I found this, and uploaded it as a .sql into the database:

CREATE TABLE smf_log_errors (
  id_error mediumint(8) unsigned NOT NULL auto_increment,
  log_time int(10) unsigned NOT NULL default '0',
  id_member mediumint(8) unsigned NOT NULL default '0',
  ip char(16) NOT NULL default '                ',
  url text NOT NULL,
  message text NOT NULL,
  session char(32) NOT NULL default '                                ',
  error_type char(15) NOT NULL default 'general',
  file varchar(255) NOT NULL default '',
  line mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY (id_error),
  KEY log_time (log_time),
  KEY id_member (id_member),
  KEY ip (ip(16))
) ENGINE=MyISAM;


Ran the updater again and got this message: Incorrect date value: '0000-00-00' for column 'eventDate' at row 1. I've searched around and found the problem is with the database not liking the value '0000-00-00' and it preferring 'null' or a random date. The problem is that I'm a real newbie when it comes to this stuff. Please could somebody give me some pointers on how to proceed.

Sorry if this is in the wrong section -- wasn't sure where to put it.

vbgamer45

Did you change all the '0000-00-00'  values? Or need help with it?
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

PD3

Quote from: vbgamer45 on May 09, 2020, 08:24:51 AM
Did you change all the '0000-00-00'  values? Or need help with it?

Thanks for the reply, vbgamer45. I need a bit of help, please. I don't know where to edit it, or how.

vbgamer45

Run this query on the database

UPDATE smf_calendar_holidays  SET eventDate = '0001-01-01' WHERE eventDate = '0000-00-00'
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

PD3

Thanks. Sorry to be so thick -- I don't think I'm doing this right. Here's a screen shot attached


vbgamer45

Get rid of the whole line SELECT * FROM
Then try again
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

PD3

Did it (see attachment) but the SMF update is still sticking on the same fault:

Sir Osis of Liver

Which 1.1 version are you updating from?
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

Doug Heffernan

I think that this could be related to the sql mode which can be strict mode. If strict mode is not enabled, '0000-00-00' format is allowed without errors. If strict mode is enabled, '0000-00-00' is not allowed. Please see the below link.

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict

For that format to be allowed without errors, the STRICT_TRANS_TABLES mode in mysql config needs to be disabled.

Sir Osis of Liver

eventDate is a valid column name in 1.1, not in 2.0.  The table structure for _log_errors is different in 1.1 and 2.0, the code posted above is the 2.0 version, was it imported to the original 1.1 database?  Do you have an unaltered dump of the 1.1 database?
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

PD3

Quote from: doug_ips on May 10, 2020, 06:04:37 PM
I think that this could be related to the sql mode which can be strict mode. If strict mode is not enabled, '0000-00-00' format is allowed without errors. If strict mode is enabled, '0000-00-00' is not allowed. Please see the below link.

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict

For that format to be allowed without errors, the STRICT_TRANS_TABLES mode in mysql config needs to be disabled.

Thanks for the reply. I think you're right. Unfortunately, I don't think I have access to change that on the server. I'll try asking the host. Cheers

PD3

Quote from: Sir Osis of Liver on May 10, 2020, 04:30:39 PM
Which 1.1 version are you updating from?

Thanks for the reply. I don't know exactly, I think it was 1.1.21 with TP.

QuoteeventDate is a valid column name in 1.1, not in 2.0.  The table structure for _log_errors is different in 1.1 and 2.0, the code posted above is the 2.0 version, was it imported to the original 1.1 database?

I just ran update.php and whatever that does led to where we are now. It looks like it creates a backup file of each part of the database before automatically deleting it at the end.

QuoteDo you have an unaltered dump of the 1.1 database?

Yes, I took a full backup of the database before I started the upgrade.

PD3

I've just had this reply from my host's tech support:

QuoteHello,

There isn't strict mode in effect on the server. Please note this is a shared hosting server where your account is hosted and for the same reason no custom changes can be done as it all user accounts. Please check following snippets and let us know if you need any further assistance.

+------------+
| @@sql_mode |
+------------+
| |
+------------+

+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
| |
+-------------------+


Thank You!

Best Regards,
---------------------------------------------
Steve
Technical Support

Frankly, I don't have a clue what he means by checking those snippets.  ::)

vbgamer45

That means strict mode is not in affect. So shouldn't be the cause of the 0000-00-00 error.
Lets just try to remove that table we can add it back later...
Run this mysql code

DROP TABLE smf_calendar_holidays;





Then run sql

CREATE TABLE smf_calendar_holidays (
  id_holiday smallint(5) unsigned NOT NULL auto_increment,
  event_date date NOT NULL default '0001-01-01',
  title varchar(255) NOT NULL default '',
  PRIMARY KEY (id_holiday),
  KEY event_date (event_date)
) ENGINE=MyISAM;
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

PD3

Thanks. Tried that. Now on the updater I'm geting:


Executing: "Updating holidays and calendar..." (5 of 19 - of this script)
Error: Unknown column 'eventDate' in 'where clause'

vbgamer45

Ok lets try it again

DROP TABLE smf_calendar_holidays;

then run

CREATE TABLE smf_calendar_holidays (
  ID_HOLIDAY smallint(5) unsigned NOT NULL auto_increment,
  eventDate date NOT NULL default '0001-01-01',
  title varchar(30) NOT NULL default '',
  PRIMARY KEY (ID_HOLIDAY),
  KEY eventDate (eventDate)
) ENGINE=MyISAM;
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

PD3

Urgh :o

Executing upgrade script 1 of 3.
Executing: "Updating holidays and calendar..." (5 of 19 - of this script)
Error!
Incorrect date value: '0000-00-00' for column 'eventDate' at row 1

vbgamer45

Very very strange.....Attach .sql file. for upgrade..
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Arantor

0000-00-00 stopped being a legal date in MySQL so,e versions ago, cf all the changes in 2.1 to make it 1004-01-01.

vbgamer45

Do you have an idea why? Even after dropping that whole they would get the same error?
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Advertisement: