Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

#1 2023-06-04 22:42:09

nabrown78
Member
From: Northampton, MA, USA
Registered: 2006-10-04
Posts: 294
Website

Long shot! Looking for help with MySQL Syntax error

Hi All,

This is a super long shot cry for help. I have a client site on a very old (It looks like it’s Textpattern 4.2.0.) and customized (read: hacked up) version of Textpattern. I did not create the site, so am not super familiar with what has been customized. I am also far from a MySQL expert.

The front of the site looks as it should. But when I try to log in I get a few errors (the MySQL version is 8, I believe):

Warning: 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 'window = '1'' at line 1 (SELECT COUNT(*) FROM txp_window WHERE user = '[username is here]' AND window = '1') textpattern/lib/txplib_db.php:1321 getRow() in /home/rogfin2/harrietyoung.com/textpattern/lib/txplib_db.php on line 326
Warning: Incorrect DATETIME value: '' (SELECT t.ID, t.ParentID, t.ParentName, t.Path, t.Alias, t.ImageID, t.FileID, t.`Type`, t.Trash, t.Level, t.AuthorID, t.Title, t.Title_html, t.Name, t.Status, t.Class AS ClassName, UNIX_TIMESTAMP(t.Posted) AS Posted, UNIX_TIMESTAMP(t.LastMod) AS LastMod, IF(t.Position <= 0,0,FLOOR(t.Position)) AS Position, CONCAT(i.Name,i.ext) AS Image, i.transparency, t.Categories, NULL AS custom_fields, (SELECT f.ext FROM txp_file AS f WHERE t.FileID = f.ID), /* Maximum Position */ (SELECT Position FROM textpattern AS mp WHERE t.ParentID = mp.ParentID ORDER BY Position DESC LIMIT 1) AS maxpos, IF(t.Posted IS NULL OR t.Posted = '',1,0) AS isnull, IF(t.ID = '3',1,0) AS istrash, /* Child Count */ (SELECT COUNT(ID) FROM textpattern AS ch WHERE ch.ParentID = t.ID AND 1=1 AND ch.Trash = 0) AS child_count, /* Alias Child Count */ IF(t.Alias,(SELECT COUNT(ID) FROM textpattern AS ch WHERE ch.ParentID = t.Alias AND 1=1 AND ch.Trash = 0),0) AS alias_child_count FROM textpattern AS t LEFT JOIN txp_image AS i ON t.ImageID = i.ID WHERE 1=1 AND t.ParentID = '2' AND t.Trash = 0 ORDER BY istrash ASC, isnull ASC, t.Posted DESC LIMIT 1000) textpattern/lib/txplib_db.php:1121 getRows() in /home/rogfin2/harrietyoung.com/textpattern/lib/txplib_db.php on line 326
Fatal error: Maximum execution time of 120 seconds exceeded in /home/rogfin2/harrietyoung.com/textpattern/lib/txplib_db.php on line 2530

The clients wants to just patch well enough to be able to log in and manage content, but I think it might be a lost cause. If any generous MySQL expert out there has ideas, I’m all ears.

Last edited by nabrown78 (2023-06-04 22:53:19)

Offline

#2 2023-06-04 23:11:51

nabrown78
Member
From: Northampton, MA, USA
Registered: 2006-10-04
Posts: 294
Website

Re: Long shot! Looking for help with MySQL Syntax error

Update! I just figured out that WINDOW is a reserved word in MySQL 8. So I got over that little hurdle, anyway. :-) (For any other clueless folk: use backticks around reserved words! Or, um…don’t use reserved words).

All the remaining errors have to do with the DATETIME / TIMESTAMP:

Fatal error: Uncaught TypeError: getdate(): Argument #1 ($timestamp) must be of type ?int, string given in /home/rogfin2/harrietyoung.com/textpattern/lib/txplib_misc.php:1471 Stack trace: #0 /home/rogfin2/harrietyoung.com/textpattern/lib/txplib_misc.php(1471): getdate(''0000-00-00 00:...') #1 /home/rogfin2/harrietyoung.com/textpattern/lib/txplib_misc.php(1515): tz_offset(''0000-00-00 00:...') #2 /home/rogfin2/harrietyoung.com/textpattern/lib/txplib_forms.php(387): safe_strftime('%Y', ''0000-00-00 00:...') #3 /home/rogfin2/harrietyoung.com/textpattern/include/lib/txp_lib_ContentEdit.php(873): tsi('exp_year', '%Y', ''0000-00-00 00:...') #4 /home/rogfin2/harrietyoung.com/textpattern/include/txp_content_article.php(133): content_edit('', false) #5 /home/rogfin2/harrietyoung.com/textpattern/admin.php(382): article_edit() #6 /home/rogfin2/harrietyoung.com/textpattern/index.php(391): include('/home/rogfin2/h...') #7 {main} thrown in /home/rogfin2/harrietyoung.com/textpattern/lib/txplib_misc.php on line 1471

No doubt the way dates are stored in MySQL changed at some point.

Last edited by nabrown78 (2023-06-04 23:15:54)

Offline

#3 2023-06-05 07:38:08

Dragondz
Moderator
From: Algérie
Registered: 2005-06-12
Posts: 1,529
Website GitHub Twitter

Re: Long shot! Looking for help with MySQL Syntax error

Hi

What i can say is that txp_window is not part of a txp standard installation, it should have been added by the dev site.

I dont know if a 4.2 version is compatible with recents versions of Mysql and PHP.

May be a txp dev can confirm that and give advices.

If you can make a backup of the website (files and DB) and try to upgrade to latest version can solve problems (i dont remember but it advised to upgrade in 2 phases when there is a very old version, first to 4.6 and then to 4.8).

Cheers.

Offline

#4 2023-06-06 13:50:02

nabrown78
Member
From: Northampton, MA, USA
Registered: 2006-10-04
Posts: 294
Website

Re: Long shot! Looking for help with MySQL Syntax error

Thanks @Dragondz. Yes, the original developers made an unwise decision to customize and hack around the core Textpattern files and database. Their changes were just too intermingled to try to upgrade, so I’ve made numerous patches over the years for PHP and MySQL compatibility. Just leaving a note here for any other unfortunate souls who encounter these types of errors in the future:

  • if you have a MySQL syntax error you can’t figure out, check the list of reserved words.
  • you can’t compare a date time with a string, even an empty one
  • Regex pattern syntax has changed over the years. Check if more characters need to be escaped (like hyphens)

Offline

Board footer

Powered by FluxBB