PHP: 5.2.17 (CGI - not FastCGI)
Apache: 2.2.20
OS: linux
MySQL: 5.1.58-community-log
MODx: 1.0.5
Greetings,
Well, this is a first for me in a couple of dozen Evo installs. A content maintainer got a MySQL syntax error on a resource save. I can't re-create the issue on my end.
Steps -- the best I could extract them -- the maintainer took (twice - same result):
- Duplicated an existing resource - one that had a (past) publish date and an unpublish date that was also in the past
- Edited the duplicate
- Set a future unpublish date
- Left the publish date blank
- Checked the "publish" checkbox
- Attempted to save the resource. System Events log shows a MySql syntax error on UPDATE of the document's row in the modx_site_content table
The SQL error in the log is:
« Execution of a query to the database failed - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' unpub_date=1318441500, parent=26, template=14, menuindex='0', searchable=1,' at line 2 »
The SQL statement shown in the log is:
UPDATE `oshkoshr_modx`.`modx_site_content` SET introtext='', content='
Art Wigchers will join us from Milwaukee and will describe recent travels to Pakistan on behalf of Catholic Relief Services, an international service organization.
', pagetitle='Weekly Meeting: Art Wigchers, \"Education in Pakistan\"', longtitle='', type='document', description='Art will discuss recent travels to Pakistani', alias='2011-09-05', link_attributes='', isfolder=0, richtext=1, published=1, pub_date=, unpub_date=1318441500, parent=26, template=14, menuindex='0', searchable=1, cacheable=1, editedby=6, editedon=1315241090, publishedon=1315241090, publishedby=6, contentType='text/html', content_dispo='0', donthit='0', menutitle='', hidemenu='1' WHERE id=196;
A couple of things about the SQL statement seem odd to me:
- There is no HTML in the content field, even though TinyMCE is installed and the page in question is marked rich text
- The SQL syntax error seems to be related to the absence of a value for pub_date
[li]I tried that SQL statement directly -- without MODX -- on (a copy of) the database; same error, plus more verbage pointing at
pub_date=[/code
] being followed directly by a comma.[/li]
- I tried it again on an older version of MySQL (5.02.43) for the heck of it; same error
- I changed the UPDATE to use 'pub_date=DEFAULT,' (without the quotes; can't nest a code block in a list here); statement worked
- I changed the UPDATE to use 'pub_date=0,' (which is the default for that field); statement worked
Here's an odd thought -- Is it possible that the date picker for the pub and unpub dates is supposed to generate the default value used in the SQL? The absence of HTML in the content field (implying that TinyMCE wasn't working) and empty value for pub_date (if generated by the date selector) might imply at least some JavaScript failure on the content maintainer's machine?
I don't know the content maintainer's browser and version -- trying to find out.
I'm hoping someone who knows the internals will recognize this pattern and shout out. It's a puzzler, and a little bit worrying -- I'd not expect regular page edits to generate SQL syntax errors <smile>
I've left it here instead of using the issue tracker, not knowing if it's truly a bug. I can always pop it in there if it's best.
Thanks in advance for any shared brain-power on this odd issue.
[ed. note: clareoconsulting last edited this post 15 years, 1 month ago.]
☆ A M B ☆