Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

#1 2008-12-02 01:02:00

rooon
New Member
From: Melbourne, Australia
Registered: 2008-12-01
Posts: 7

Incorrect datetime value: '0' for column 'Expires' error in Txp 4.0.7

I’m receiving a ‘Incorrect datetime value: ‘0’ for column ‘Expires’ at row 1’ error message when attempting to update and existing article.

Here are the steps that lead to this error:

1. In the ‘write’ section of the TXP Admin, create an article – enter text in title and body and excerpt sections.
2. Without filling in any other fields, publish the article. (You can select a section if required.)
3. Go to the ‘articles’ section of the Admin and click on the title of the article you just added to edit it.
4. Change a word or two anywhere in the content you’ve entered then click ‘Save’.

Result: The changes are not saved and the following error message is received:

// ——————————————————————————————— //
Warning: Incorrect datetime value: ‘0’ for column ‘Expires’ at row 1 update textpattern set Title = ‘blah’, Body = ‘blah’, Body_html = ‘
blah’, Excerpt = ‘blah’, Excerpt_html = ‘blah’, Keywords = ‘’, Image = ‘’, Status = 4, LastMod = now(), LastModID = ‘name’, Section = ‘blog’, Category1 = ‘’, Category2 = ‘’, Annotate = 0, textile_body = 1, textile_excerpt = 1, override_form = ‘’, url_title = ‘blah’, AnnotateInvite = ‘Comments’, custom_1 = ‘’, custom_2 = ‘’, custom_3 = ‘’, custom_4 = ‘’, custom_5 = ‘’, custom_6 = ‘’, custom_7 = ‘’, custom_8 = ‘’, custom_9 = ‘’, custom_10 = ‘’, Posted=from_unixtime(1228179036), Expires=‘0’ where ID = 7 in C:\websites\sitename\textpattern\lib\txplib_db.php on line 82
// ——————————————————————————————— //

I’ve noticed that entering in a date/time for the Expires fields seems to fix this problem. It errors only when they are left blank.

I’m using Textpattern 4.0.7 and MySQL 5.0.27 on a Windows box running Apache.

Any help greatly appreciated!

Offline

#2 2008-12-02 02:53:48

wet
Developer Emeritus
From: Schoerfling, Austria
Registered: 2005-06-06
Posts: 3,288
Website Mastodon

Re: Incorrect datetime value: '0' for column 'Expires' error in Txp 4.0.7

Please issue these SQL queries and report back:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

Alternatively, you can visit the “Show MySQL system variables” page in phpMyAdmin and read this setting from the row starting with “sql mode” (see pic)

Ref: [1], [2]

Last edited by wet (2008-12-02 02:54:26)

Online

#3 2008-12-02 03:00:24

rooon
New Member
From: Melbourne, Australia
Registered: 2008-12-01
Posts: 7

Re: Incorrect datetime value: '0' for column 'Expires' error in Txp 4.0.7

They both return:
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’

Offline

#4 2008-12-02 03:01:26

wet
Developer Emeritus
From: Schoerfling, Austria
Registered: 2005-06-06
Posts: 3,288
Website Mastodon

Re: Incorrect datetime value: '0' for column 'Expires' error in Txp 4.0.7

Any reason why you use STRICT_TRANS_TABLES?

Online

#5 2008-12-02 03:05:37

rooon
New Member
From: Melbourne, Australia
Registered: 2008-12-01
Posts: 7

Re: Incorrect datetime value: '0' for column 'Expires' error in Txp 4.0.7

None at all. It’s not something I’ve manually changed or set up intentionally. I just followed the installation instructions for TXP… perhaps it’s a default setting for my version of MySQL??

Offline

#6 2008-12-02 03:08:52

wet
Developer Emeritus
From: Schoerfling, Austria
Registered: 2005-06-06
Posts: 3,288
Website Mastodon

Re: Incorrect datetime value: '0' for column 'Expires' error in Txp 4.0.7

So, this is not a self-hosted environment on your own box but rented webspace?

Online

#7 2008-12-02 03:13:15

rooon
New Member
From: Melbourne, Australia
Registered: 2008-12-01
Posts: 7

Re: Incorrect datetime value: '0' for column 'Expires' error in Txp 4.0.7

No, I’m developing locally, so it is self-hosted. I created the db using a local version of MySQL Administrator.

I’m not entirely across what STRICT_TRANS_TABLES is about, so it quite possibly could be a setting I missed when creating the database.

Offline

#8 2008-12-02 03:20:26

wet
Developer Emeritus
From: Schoerfling, Austria
Registered: 2005-06-06
Posts: 3,288
Website Mastodon

Re: Incorrect datetime value: '0' for column 'Expires' error in Txp 4.0.7

  1. See docs.
  2. SQL mode is determined upon MySQL server’s startup and not changed for a single database.
  3. Try to locate MySQL’s configuration file my.ini or my.cnf. For XAMPP, this file would live somewhere around c:\xampp\myqsl\bin\.
  4. Disable STRICT_TRANS_TABLES by removing a line similar to sql_mode=STRICT_TRANS_TABLES from this file (you can prepend a # to change it into an inoperational comment)
  5. Stop and restart MySQL server
  6. Report back

Online

#9 2008-12-02 03:28:20

rooon
New Member
From: Melbourne, Australia
Registered: 2008-12-01
Posts: 7

Re: Incorrect datetime value: '0' for column 'Expires' error in Txp 4.0.7

That fixed it. Many thanks wet!

Offline

#10 2008-12-02 12:35:48

wet
Developer Emeritus
From: Schoerfling, Austria
Registered: 2005-06-06
Posts: 3,288
Website Mastodon

Re: Incorrect datetime value: '0' for column 'Expires' error in Txp 4.0.7

In case you do not have the opportunity to switch MySQL into non-strict mode by using the method sketched above, try this plugin:

# txp_mysql5strict v0.1
# Set MySQL 5 into non-strict mode.

# ......................................................................
# This is a plugin for Textpattern - http://textpattern.com/
# To install: textpattern > admin > plugins
# Paste the following text into the 'Install plugin' box:
# ......................................................................

H4sIAAAAAAAAA81U72vbMBD9HP8VhwdzC63t/G6cNN+6UuhgWwP9UgiKfLFFbdmRznW80v99
khOzMBgE9mUG45P03und3cMs6ofRu46mkfuGSotCunMdDSM39Ps2mkQuqygtlF30zepHsUFF
8Iz0M8tZc9gPO9S6UsLuDEyGlKiMgoBwTyUjQiV9XuRBS+hHboyaK1FSd6VhPCHB1+bp+yOM
QUgqQBbyWpMSnCAvYvQtcBS51JTozkU0PCwly7GTR/tynTd6l40PvCMjxay04Tg0mN6iXH4p
FKx+K4ORH/rTKyhKVIyETI46hIST+4HJGDR7s+dMAlMkeIZQC0rtGvOSGsB9KVQDMSOEXCQp
QYLSZjV0qJmSlp2j1izBaBGUS8fpLTZZwV93VUG4dHpW3/MBGMGD5IVSaBTYjCRyhDeWVRjB
5083g/5kHrbf6Ry2piReZFUuu6M7KwV1B2AEqqihD1XZqjuZDGjT+pWgDC14GM5bYb1FcCrM
scIepCaWZUCp0FBmVWJaVKcooSkqWzsqgZKjCRmnrDngtKCK2Un7XcHlctUluDYZTKgwQ6Yx
hkrGqAwPYcEgVbi97axU17WfyMovVBJkgqPUqIOkzHzak7u8lxXct63O4Fu1MQB4tCCOi4At
25uPbuBmlq1hhsaHjsJEaNOENTd1bRh/vfCsi0xHWiPZwXtX4B2nbUPzkqrwcu4420pyWxj8
Sbm4dN4dMI9mW1zvKlTNhfd0twJzvraA2xfvxfNMjg/Hark5mHStWN2qDM3Gf2JSx9nsfDjD
kV7onbrQO/rPO895vu87zj/ay3HOsJX7d6e40RlW84+/yDwet9EgcidDHmM8Gc0G0+FsOtuE
480NYzzk49kUw3jkzj9+AW5lRSVqBQAA

Online

#11 2008-12-02 12:49:24

ruud
Developer Emeritus
From: a galaxy far far away
Registered: 2006-06-04
Posts: 5,068
Website

Re: Incorrect datetime value: '0' for column 'Expires' error in Txp 4.0.7

^^ might be a good idea to put that plugin code in the TXP FAQ as a temporary fix until 4.0.8.

Offline

#12 2008-12-02 13:33:28

wet
Developer Emeritus
From: Schoerfling, Austria
Registered: 2005-06-06
Posts: 3,288
Website Mastodon

Re: Incorrect datetime value: '0' for column 'Expires' error in Txp 4.0.7

Will do so, but I’d hope for anyone confirming its healing powers first.

Online

Board footer

Powered by FluxBB