Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

#1 2021-04-12 09:30:40

gaekwad
Server grease monkey
From: People's Republic of Cornwall
Registered: 2005-11-19
Posts: 4,272
GitHub

Request for testers: MySQL leap second issue investigation

Some time ago, when MySQL 5.3 was the new kid on the block, we had some issues with handling of leap second handling and the linked issue below was opened.

It appears this issue may be resolved. Testing with Percona Server (aka MySQL off-brand replacement) seems to work as expected. This indicates that either enough has changed in Textpattern internally since the days of MySQL 5.3 that we’ve worked around it in some way, or it has been addressed somewhere in the MySQL development process.

Here’s the issue:

github.com/textpattern/textpattern/issues/100

We need to determine whether this issue is still valid with current and supported MySQL environments, and we need your help to test something on existing Textpattern sites with an established database. The more wide-ranging the coverage for different versions of Textpattern and MySQL, the better.

Here’s the preflight list:

  • You will need to create a new article, and delete it when you’re done.
  • You will need access to your database tables to check a timestamp associated with the article.
  • You will need to report back with information from Diagnostics (private or anonymised is just fine).

Here’s what you need to do:

  1. Create an article with the publish time stamp of: 2010-12-15 11:24:45, as per the linked issue.
  2. Refer to your database and cross-check the article ID’s row in the textpattern table. We need to know the value in the Posted column.
  3. Post your low detail diagnostics here, along with the time stamp reported in your database row.
  4. Delete the article you created.

Like this:

Database Posted time stamp: 2010-12-15 10:24:45

Textpattern version: 4.8.5 (fe6df0487f1a92cf562eafddc1260096)
Last update: 2021-04-12 09:00:02
Textpattern path: __TXP-ROOT/textpattern
Article URL pattern: section_title
Production status: testing
Temporary directory path: __TXP-ROOT/textpattern/tmp
PHP version: 7.4.16
GD Graphics Library: 2.2.5; Supported formats: GIF, JPEG, PNG, WebP.
Server timezone: Europe/London
Server local time: 2021-04-12 10:27:34
Daylight Saving Time enabled?: 0
Automatically adjust Daylight Saving Time setting?: 0
Time zone (GMT offset in seconds):  (+3600)
MySQL: 8.0.22-13 (Percona Server (GPL), Release '13', Revision '6f7822f') 
Database server time: 2021-04-12 09:27:34
Database server time offset: 1 s
Database server timezone: SYSTEM
Database session timezone: SYSTEM
Locale: en_US.UTF-8
Site / Admin language: en / en
Web server: nginx/1.19.8
PHP server API: fpm-fcgi
PHP SSL version: OpenSSL 1.1.1f  31 Mar 2020
RFC 2616 headers: 0
Server OS: Linux 5.4.0-70-generic
Admin-side theme: hive 4.8.5

From here, we can sift through responses and figure out whether this is still an issue.

Thank you!

Offline

#2 2021-04-12 10:25:26

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,459
Website GitHub

Re: Request for testers: MySQL leap second issue investigation

Here’s a few of mine:

Online server: Database Posted timestamp: 2010-12-15 11:24:45

Textpattern version: 4.8.6-dev (0ddfb97b1a553e13470ee0a5bc35db9a)
Last update: 2021-04-08 13:19:34/2021-04-08 13:17:57
Textpattern path: __TXP-ROOT/textpattern
Article URL pattern: section_title
Production status: debug
Temporary directory path: __TXP-ROOT/textpattern/tmp
PHP version: 7.3.18-1+ubuntu16.04.1+deb.sury.org+1
GD Graphics Library: 2.3.0; Supported formats: GIF, JPEG, PNG, WebP.
Server timezone: UTC
Server local time: 2021-04-12 09:40:11
Daylight Saving Time enabled?: 1
Automatically adjust Daylight Saving Time setting?: 1
Time zone (GMT offset in seconds): Europe/London (0)
MySQL: 5.6.47-87.0 (Percona Server (GPL), Release 87.0, Revision 9ad342b) 
Database server time: 2021-04-12 10:40:11
Database server time offset: 1 s
Database server timezone: SYSTEM
Database session timezone: SYSTEM
Locale: C
Site / Admin language: en / en
Web server: Apache/2.4.18 (Ubuntu)
Apache version: Apache/2.4.18 (Ubuntu)
PHP server API: apache2handler
PHP SSL version: OpenSSL 1.1.1g  21 Apr 2020
RFC 2616 headers: 
Server OS: Linux 4.4.0-206-generic
Admin-side theme: hive 4.8.5

Localhost (MAMP) Database posted timestamp: 2010-12-15 10:24:45

Textpattern version: 4.7.3 (7c46d1f4c8ac79e62a7d5e54a9ddac53)
Last update: 1970-01-01 00:00:00/2019-02-27 21:47:01
Textpattern path: __TXP-ROOT/textpattern
Article URL pattern: section_title
Production status: testing
upload_tmp_dir: /Applications/MAMP/tmp/php
Temporary directory path: /private/var/folders/q0/m11cryvs68b19v4c7f582s9r0000gn/T
PHP version: 5.6.40
GD Graphics Library: bundled (2.1.0 compatible); Supported formats: GIF, JPEG, PNG.
Server timezone: Europe/Berlin
Server local time: 2021-04-12 11:53:37
Daylight Saving Time enabled?: 0
Automatically adjust Daylight Saving Time setting?: 0
Time zone (GMT offset in seconds): Europe/Berlin (+3600)
MySQL: 5.7.32 (MySQL Community Server (GPL)) 
Database server time: 2021-04-12 10:53:37
Database server time offset: 0 s
Database server timezone: SYSTEM
Database session timezone: SYSTEM
Locale: C
Site / Admin language: en / en
Web server: Apache/2.4.46 (Unix) OpenSSL/1.0.2u PHP/5.6.40 mod_wsgi/3.5 Python/2.7.13 mod_fastcgi/mod_fastcgi-SNAP-0910052141 mod_perl/2.0.11 Perl/v5.30.1
Apache version: Apache/2.4.46 (Unix) OpenSSL/1.0.2u PHP/5.6.40 mod_wsgi/3.5 Python/2.7.13 mod_fastcgi/mod_fastcgi-SNAP-0910052141 mod_perl/2.0.11 Perl/v5.30.1
PHP server API: apache2handler
RFC 2616 headers: 
Server OS: Darwin 18.7.0
Admin-side theme: hive 4.7.3

Localhost (MAMP) Database posted timestamp: 2010-12-15 11:24:45

Textpattern version: 4.8.5 (fe6df0487f1a92cf562eafddc1260096)
Last update: 2021-04-09 14:38:39/2021-04-09 23:07:15
Textpattern path: __TXP-ROOT/textpattern
Article URL pattern: section_title
Production status: debug
upload_tmp_dir: /Applications/MAMP/tmp/php
Temporary directory path: __TXP-ROOT/textpattern/tmp
PHP version: 8.0.0
GD Graphics Library: Unavailable
Server timezone: UTC
Server local time: 2021-04-12 10:24:10
Daylight Saving Time enabled?: 1
Automatically adjust Daylight Saving Time setting?: 1
Time zone (GMT offset in seconds): Europe/London (0)
MySQL: 5.7.32 (MySQL Community Server (GPL)) 
Database server time: 2021-04-12 11:24:10
Database server time offset: 0 s
Database server timezone: SYSTEM
Database session timezone: SYSTEM
Locale: en_US.UTF-8
Site / Admin language: en / en
Web server: Apache/2.4.46 (Unix) OpenSSL/1.0.2u PHP/8.0.0 mod_wsgi/3.5 Python/2.7.13 mod_fastcgi/mod_fastcgi-SNAP-0910052141 mod_perl/2.0.11 Perl/v5.30.1
Apache version: Apache/2.4.46 (Unix) OpenSSL/1.0.2u PHP/8.0.0 mod_wsgi/3.5 Python/2.7.13 mod_fastcgi/mod_fastcgi-SNAP-0910052141 mod_perl/2.0.11 Perl/v5.30.1
PHP server API: apache2handler
PHP SSL version: OpenSSL 1.0.2u  20 Dec 2019
RFC 2616 headers: 
Server OS: Darwin 18.7.0
Admin-side theme: hive 4.8.5

The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

#3 2021-04-12 10:56:18

gaekwad
Server grease monkey
From: People's Republic of Cornwall
Registered: 2005-11-19
Posts: 4,272
GitHub

Re: Request for testers: MySQL leap second issue investigation

Thanks, Bloke – looks encouraging for MySQL 5.6 & 5.7!

Offline

#4 2021-04-12 16:48:21

Vienuolis
Member
From: Vilnius, Lithuania
Registered: 2009-06-14
Posts: 310
Website GitHub GitLab Mastodon Twitter

Re: Request for testers: MySQL leap second issue investigation

Database Posted timestamp: 2010-12-15 10:24:45. Diagnostics:

Textpattern version: 4.8.5 (fe6df0487f1a92cf562eafddc1260096)
Last update: 2021-03-31 09:58:43/2021-03-31 09:44:51
Site URL: az.on.lt
Admin URL: az.on.lt/textpattern
Document root: /srv/az
$path_to_site: /srv/az
Textpattern path: /srv/az/textpattern
Article URL pattern: title_only
Production status: testing
Temporary directory path: /tmp
PHP version: 8.0.3
GD Graphics Library: 2.2.5; Supported formats: GIF, JPEG, PNG, WebP.
Server timezone: Europe/Helsinki
Server local time: 2021-04-12 19:46:46
Daylight Saving Time enabled?: 1
Automatically adjust Daylight Saving Time setting?: 1
Time zone (GMT offset in seconds): Europe/Vilnius (7200)
MySQL: 5.5.5-10.1.48-MariaDB-0+deb9u2 (Debian 9.13) 
Database server time: 2021-04-12 19:46:46
Database server time offset: 0 s
Database server timezone: SYSTEM
Database session timezone: SYSTEM
Locale: C
Site / Admin language: en / en
Web server: Hiawatha v10.12
PHP server API: fpm-fcgi
PHP SSL version: OpenSSL 1.1.1j  16 Feb 2021
RFC 2616 headers: 0
Server OS: Linux 4.9.0-15-686-pae
Admin-side theme: hive 4.8.5

Last edited by Vienuolis (2021-04-12 16:49:02)

Offline

#5 2021-04-12 16:54:44

gaekwad
Server grease monkey
From: People's Republic of Cornwall
Registered: 2005-11-19
Posts: 4,272
GitHub

Re: Request for testers: MySQL leap second issue investigation

Thank you, Vienuolis!

Edit: it looks like MariaDB 10.1 works fine, too.

Last edited by gaekwad (2021-04-12 16:58:35)

Offline

#6 2021-04-13 07:40:20

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

Re: Request for testers: MySQL leap second issue investigation

Here a test :

Posted on DB : 2010-12-15 05:24:45

Textpattern version: 4.8.1 (8e4e0ea0982f48cbe19bbb00c4c260a2)
Last update: 2020-06-20 14:18:10/2020-06-20 14:14:31
Article URL pattern: section_title
Production status: live
Temporary directory path: /tmp
PHP version: 7.2.34
GD Graphics Library: bundled (2.1.0 compatible); Supported formats: GIF, JPEG, PNG, WebP.
Server timezone: UTC
Server local time: 2021-04-13 07:38:53
Daylight Saving Time enabled?: 0
Automatically adjust Daylight Saving Time setting?: 0
Time zone (GMT offset in seconds): Africa/Algiers (3600)
MySQL: 5.5.5-10.2.37-MariaDB-log-cll-lve (MariaDB Server) 
Database server time: 2021-04-13 03:38:53
Database server time offset: 1 s
Database server timezone: SYSTEM
Database session timezone: SYSTEM
Locale: fr_FR.UTF-8
Site / Admin language: fr / fr
Web server: LiteSpeed
PHP server API: litespeed
RFC 2616 headers: 
Server OS: Linux 3.10.0-962.3.2.lve1.5.44.3.el7.x86_64
Admin-side theme: hive 4.8.1
Active plugins: 
	com_connect-4.6.0
	pap_comconnect-0.1.2
	rah_sitemap-1.2
	rss_admin_db_manager-4.6.0
	upm_insert_tab-0.2.2
	zem_nth-0.1
Pre-flight check: 
------------------------
New Textpattern version 4.8.5 available for download.
------------------------

Offline

#7 2021-04-13 09:02:40

gaekwad
Server grease monkey
From: People's Republic of Cornwall
Registered: 2005-11-19
Posts: 4,272
GitHub

Re: Request for testers: MySQL leap second issue investigation

Merci beaucoup, Rabah!

That looks like MariaDB 10.2 is OK.

Offline

#8 2021-04-13 09:46:36

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,459
Website GitHub

Re: Request for testers: MySQL leap second issue investigation

An interesting test that one, Rabah:

Database server time: 2021-04-13 03:38:53
Server local time: 2021-04-13 07:38:53
Daylight Saving Time enabled?: 0
Server timezone: UTC
Time zone (GMT offset in seconds): Africa/Algiers (3600)

A 4-hour time difference between server and database server (boxes in different geographical regions?), plus 1-hour timezone shift on the server, with no DST compensation = 5-hour difference.

Although the timezone/DST settings on the database server are unknown to Diagnostics (just SYSTEM whatever that means), we can surmise it’s a further hour out (either due to TZ or DST) because there’s a 6-hour difference between the test datestamp 11:24:45 and the stored database datestamp 05:24:45! Uhhh, right?

Talk about brain-taxing, which is why I’m glad Txp takes (some of) this headache away, even if it’s not perfect.

Last edited by Bloke (2021-04-13 09:49:08)


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

#9 2021-04-13 09:50:23

gaekwad
Server grease monkey
From: People's Republic of Cornwall
Registered: 2005-11-19
Posts: 4,272
GitHub

Re: Request for testers: MySQL leap second issue investigation

Bloke wrote #329802:

An interesting test that one, Rabah:

Database server time: 2021-04-13 03:38:53...

A 4-hour time difference between server and database server (boxes in different geographical regions?), plus 1-hour timezone shift on the server, with no DST compensation = 5 hour difference.

I’m so glad it wasn’t me that struggled to get my head around that…I ended up with pen and paper and I still wasn’t 100% sure my sums were correct!

Offline

#10 2021-04-14 05:16:20

colak
Admin
From: Cyprus
Registered: 2004-11-20
Posts: 9,091
Website GitHub Mastodon Twitter

Re: Request for testers: MySQL leap second issue investigation

Database Posted time stamp: 2010-12-15 10:24:45

Textpattern version: 4.8.5 (af57d74ce90fc754218d77dcfb1fb508)
Last update: 2021-03-31 15:13:32/2021-03-31 15:08:35
Textpattern path: __TXP-ROOT/textpattern
Article URL pattern: section_category_title
Production status: live
Temporary directory path: /home/tmp
PHP version: 7.4.14
GD Graphics Library: bundled (2.1.0 compatible); Supported formats: GIF, JPEG, PNG, WebP.
Server timezone: America/Los_Angeles
Server local time: 2021-04-13 22:14:50
Daylight Saving Time enabled?: 0
Automatically adjust Daylight Saving Time setting?: 0
Time zone (GMT offset in seconds): Asia/Nicosia (7200)
MySQL: 5.7.28-log (Source distribution) 
Database server time: 2021-04-13 22:14:50
Database server time offset: 1 s
Database server timezone: SYSTEM
Database session timezone: SYSTEM
Locale: en_GB.UTF-8
Site / Admin language: en-gb / en-gb
Web server: Apache
PHP server API: cgi-fcgi
PHP SSL version: OpenSSL 1.1.1d  10 Sep 2019
RFC 2616 headers: 0
Server OS: Linux 4.9.159-vs2.3.9.8-vs+
Admin-side theme: hive 4.8.5
Active plugins: 
	act_if_mobile-0.1.2 (modified)
	adi_matrix-2.1beta5
	adi_notes-1.4
	com_connect-4.7.0 (modified)
	etc_query-1.3.7
	ext_com_connect_verify-0.1 (modified)
	ext_file_attach-1.0.5
	fha_time_diff-0.23 (modified)
	ied_plugin_composer-1.2.1
	mem_postmaster-1.0.20 (modified)
	oui_cookie-1.0.0-BETA4
	pap_comconnect-0.1.2 (modified)
	pat_speeder-1.1
	rah_external_output-1.0.4
	rss_admin_db_manager-4.6.0
	smd_where_used-0.30
	wet_opensearch-0.1

Pre-flight check: 
------------------------
Some Textpattern files have been modified: 
	__TXP-ROOT/textpattern/publish.php
------------------------

.htaccess file contents: 
------------------------

------------------------

Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.

Offline

#11 2021-04-14 09:15:39

phiw13
Plugin Author
From: Japan
Registered: 2004-02-27
Posts: 3,210
Website

Re: Request for testers: MySQL leap second issue investigation

I was going to post one on DreamHost servers, but I see Yiannis already did.

On localhost, Homebrew set up (Apache + PHP8 + TXP 4.9 dev)

Posted: 2010-12-25 11:24:45

diagnostics

extpattern version: 4.9.0-dev (d2c5f3a966325b817c37199657a01b40)
Last update: 2021-04-08 05:41:33/2021-02-20 06:27:31
Textpattern path: __TXP-ROOT/textpattern
Article URL pattern: section_title
Production status: debug
Temporary directory path: /private/var/tmp
PHP version: 8.0.3
GD Graphics Library: 2.3.1; Supported formats: GIF, JPEG, PNG, WebP.
Server timezone: Asia/Tokyo
Server local time: 2021-04-14 18:08:04
Daylight Saving Time enabled?: 0
Automatically adjust Daylight Saving Time setting?: 0
Time zone (GMT offset in seconds): Asia/Tokyo (32400)
MySQL: 8.0.22 (MySQL Community Server - GPL) 
Database server time: 2021-04-14 18:08:04
Database server time offset: 5 s
Database server timezone: SYSTEM
Database session timezone: SYSTEM
Locale: en_GB.UTF-8
Site / Admin language: en-gb / en-gb
Web server: Apache/2.4.46 (Unix) PHP/8.0.3
Apache version: Apache/2.4.46 (Unix) PHP/8.0.3
PHP server API: apache2handler
PHP SSL version: OpenSSL 1.1.1j  16 Feb 2021
RFC 2616 headers: 
Server OS: Darwin 20.3.0

Where is that emoji for a solar powered submarine when you need it ?
Sand space – admin theme for Textpattern

Offline

#12 2021-04-14 10:26:14

gaekwad
Server grease monkey
From: People's Republic of Cornwall
Registered: 2005-11-19
Posts: 4,272
GitHub

Re: Request for testers: MySQL leap second issue investigation

Thanks very much, Yiannis & Philippe – very useful.

My gut feeling says we can close the OP-linked issue as no longer relevant with modern MySQL and its cousins.

Offline

Board footer

Powered by FluxBB