miniBB ® 

miniBB

®
Support Forums
  
 | Start | Register | Search | Statistics | File Bank | Manual |
Bugs miniBB Support Forums / Bugs /  
 

When posting a new topic: Incorrect datetime value: '0000-00-00 00:00:00'

 
Author flyup
Partaker
#1 | Posted: 17 Feb 2018 04:28 
Hello. I'm testing a new forum install and new topic post. I am receiving the following error when trying to post a new topic:

Incorrect datetime value: '0000-00-00 00:00:00' for column 'topic_last_post_time' at row 1. Try another name or value.

Can somebody help me? Thanks!

Author flyup
Partaker
#2 | Posted: 17 Feb 2018 17:00 
I figured this out:

add to my.cnf

[mysqld]
sql_mode = "NO_ZERO_IN_DATE,NO_ZERO_DATE"

Author Paul
Lead Developer 
#3 | Posted: 17 Feb 2018 20:40 
Thanks for this report; what mySQL version do you use? It appears sort of mySQL related version, possibly we could fix it by default for future releases...

Author Paul
Lead Developer 
#4 | Posted: 20 Feb 2018 18:54 
I've analyzed this more deeply and could conclude you're using mySQL at least of the version 5.7, in which '0000-00-00 00:00:00' is not a valid DATETIME value anymore. Since then, this also needs to be updated in the current stable and developed version of miniBB.

The steps to complete for the newer mySQL version (this will also work for miniBB 3.2.2):

1) In case you are installing the fresh forum, modify _install_mysql.sql and remove NOT NULL default '0000-00-00 00:00:00' for the following fields: post_time, topic_time, topic_last_post_time, user_regdate. NULL values for these fields are not truly "wanted", but they all will be formed in/recorded from the script itself, so the older structure could be removed.

2) In case you already have an existing forum, and like to migrate it to/make it compatible with the newer mySQL 5.7+, you need to update your database with the following commands (substitute your own custom field names in the case you have them):

ALTER TABLE minibbtable_posts MODIFY post_time datetime;
ALTER TABLE minibbtable_topics MODIFY topic_time datetime;
ALTER TABLE minibbtable_topics MODIFY topic_last_post_time datetime;
ALTER TABLE minibbtable_users MODIFY user_regdate datetime;
The same could be done for whatever add-ons you may have installed and which previously contained the '0000-00-00 00:00:00' value for the datetype field as default.

If you still experience Incorrect datetime value error while trying to execute the commands above, before running these commands try to execute the following in the command line:

SET SQL_MODE='ALLOW_INVALID_DATES';
3) Edit bb_func_ptopic.php and after the line which is `$topic_time=date('Y-m-d H:i:s');` add the new line: `$topic_last_post_time=$topic_time;` so it looks like:

$topic_time=date('Y-m-d H:i:s');
$topic_last_post_time=$topic_time;
This all is currently fixed in miniBB 3.3 v2.

Author tom322
Active Member
#5 | Posted: 20 Feb 2018 20:29 
Regarding point 2) above - are these full commands there (shouldn't be there something like: SET after the MODIFY) - currently it seems to modify but not sure if it knows to what it should modify?

Author Paul
Lead Developer 
#6 | Posted: 21 Feb 2018 21:47 
tom322:
are these full commands there
Yes, they are, I've tested them on this forum and on minibb.org, both worked.

MODIFY means the change of DB field attributes; in our case, we remove 'not null' i.e. they could be NULL, and remove the default value.

Author flyup
Partaker
#7 | Posted: 23 Feb 2018 20:33 
Hello Paul.

Are you still offering quotes for miniBB integrations?

Author Paul
Lead Developer 
#8 | Posted: 24 Feb 2018 22:19 
flyup:
Are you still offering quotes for miniBB integrations?
Yes, I provide such support in terms of our Paid Service.

Author Paul
Lead Developer 
#9 | Posted: 23 Feb 2020 19:16 
UPDATE: while upgrading the truly old forums of miniBB version 2.3, I've checked the code updates provided above in #4 post, and if you have mySQL 5.7+ (mine was 5.7.29) then you could experience sort of mySQL error, if you work out the 'minibb_topics' table with these codes:

ALTER TABLE minibbtable_topics MODIFY topic_time datetime;
ALTER TABLE minibbtable_topics MODIFY topic_last_post_time datetime;
If you execute the first code string, then mySQL would say there is a wrong default value for 'topic_last_post_time'; and vice versa, if you execute the 2nd string, then it will say the default value is wrong for 'topic_time'. I.e. mySQL doesn't let you to upgrade one default value, taking into attention also other possibly incorrect values.

For this case, you have to manually create a new table named, let's say, 'minibbtable_topics2'. Create it with the syntax provided in _install_mysql.sql of miniBB package, for example:

CREATE TABLE minibbtable_topics2 (
topic_id int(10) NOT NULL auto_increment,
topic_title varchar(700) NOT NULL default '',
topic_poster int(10) NOT NULL default '0',
topic_poster_name varchar(255) NOT NULL default 'Anonymous',
topic_time datetime,
topic_views int(10) default '0' not null,
forum_id int(10) NOT NULL default '1',
topic_status tinyint(1) NOT NULL default '0',
topic_last_post_id int(10) NOT NULL default '1',
posts_count int(10) NOT NULL default '0',
sticky int(1) NOT NULL default '0',
topic_last_post_time datetime,
topic_last_poster VARCHAR(255) default '' NOT NULL,
PRIMARY KEY(topic_id),
KEY forum_id (forum_id),
KEY topic_last_post_id (topic_last_post_id),
KEY sticky (sticky),
KEY posts_count (posts_count),
KEY topic_last_post_time (topic_last_post_time),
KEY topic_views (topic_views)
) ENGINE=MYISAM;
I've taken the code above from miniBB 3.4.2 and I hope it's the most recent version to work with mySQL 5.7+. When upgrading your own forum, you should take this code from the most recent miniBB package.

After you created an empty table, copy all values from the older table to the newer table (substitute your own table names):

INSERT INTO minibbtable_topics2 SELECT * FROM minibbtable_topics;
Then rename the older table to something like:

RENAME TABLE minibbtable_topics TO minibbtable_topics_tmp;
Rename the new table to the default name you have:

RENAME TABLE minibbtable_topics2 TO minibbtable_topics;
Finally, delete the unnecessary table:

DROP TABLE minibbtable_topics_tmp;

Bugs miniBB Support Forums / Bugs /
 When posting a new topic: Incorrect datetime value: '0000-00-00 00:00:00'
 Share Topic's Link

Your Reply Click this icon to move up to the quoted message


  ?
Post as a Guest, leaving the Password field blank. You could also enter a Guest name, if it's not taken by a member yet. Sign-in and post at once, or just sign-in, bypassing the message's text.


Before posting, make sure your message is compliant with forum rules; otherwise it could be locked or removed with no explanation.

 

 
miniBB Support Forums Powered by Forum Software miniBB ® Home  Features  Requirements  Demo  Download  Showcase  Gallery of Arts
Compiler  Premium Extensions  Premium Support  License  Contact Us
Try the Captcha add-on: protect your miniBB-forums from the automated spam and flood.


  ⇑