Menu ▾ ▴

#4787 If mysql server default timezone is not set(by default), Phpmyadmin export wrong timestamp and timezone combination

Latest_Git
out-of-date
Madhura Jayaratne
timestamp (1)
High
2015-04-09
2015-03-04
aaronyu6
No

Bug Report-
Brief, in debian 6/7: if mysql server default timezone is not set, Phpmyadmin export wrong timestamp and timezone combination in sql result.

The problem can be recreated,below is steps to recreate it

Env:
phpmyadmin Version information: 4.3.11 (up to date)
debian 6/7 64bit
Brief: If mysql server default timezone is not set, Phpmyadmin export wrong timestamp(offset) in sql result.

How to recreate problem as below

php time zone is set on asia/shanghai(+08:00)
Php script to store date and time to mysql timestamp columne: What is recorded as '2015-03-04 23:59:37' in timestamp col (you can also manually input the value from phpmyadmin, the field must be defined timestamp format)
and it is stored correctly in mysql with correct timezone.
The problem is after exporting from phpmyadmin(pma)

Problematic-Senario-1: if not set default_time_zone of mysql my.cnf(use default)
if dun set default_time_zone of mysql my.cnf, mysql timezone by default equals to debian SYSTEM timezone(+08:00),
Display: The Pma displayed record as '2015-03-04 23:59:37': correct. it use default debian systemtimezone to display time correctly.

If use mysqldump to export: the mysqldump result, /!40103 SET TIME_ZONE='+00:00' /;'2015-03-04 15:59:37', so mysqldump result is correct also,

but if pma export sql, it contains 'SET time_zone = "+00:00"'; and '2015-03-04 23:59:37' : which is wrong exported output.

the correct exported content shall be 'SET time_zone = "+00:00"'; and '2015-03-04 15:59:37',
or "SET time_zone = "+08:00"; and '2015-03-04 23:59:37'"
if import back this wrongly exported sql(quoted again 'SET time_zone = "+00:00"'; and '2015-03-04 23:59:37'), the stored record in pma will be displayed totally differently from the beginning. because it treate 23:59:37 as timezone of +00:00, not +08:00
it seems the phpmyadmin does not catch the default time zone of mysql which is transferred from debian System.

Test-Senario 2(as reference only,to troubleshoot where maybe wrong):
A: If mysql server set default_time_zone: +00:00 in my.cnf , pma export '2015-03-04 15:59:37', correct.

B: If mysql my.cnf is set default_time_zone +08:00, pma display '2015-03-04 23:59:37', correct.
If phpmyadmin export, it contains "set time_zone +00:00, '2015-03-04 15:59:37', that is also correct, because mysql default_time_zone is +08:00, but phpmyadmin all use +00:00, to do the export, anyway, it is still right, even though it only export with +00:00 format

Analysis: The problem seems only occured when mysql my.cnf does not set default_time_zone(using default setting in debian)
it seems during exporting, the phpmyadmin does not catch the default time zone of mysql which is transferred from debian System.
recap The problem: in debian 6/7: if mysql server default timezone is not set, Phpmyadmin export wrong timestamp and timezone combination in sql result.

Temp walk around: set default_time_zone in mysql my.cnf

Related

Bugs: #4787

Discussion

  • aaronyu6

    aaronyu6 - 2015-03-04

    Temperary walkaround,add below in /etc/mysql/my.cnf ,
    under [mysqld] section

    default-time-zone='+08:00'
    restart mysql server

     

    Last edit: aaronyu6 2015-03-04

  • Madhura Jayaratne

    Madhura Jayaratne - 2015-04-08
    • Description has changed:

    Diff:

    --- old
+++ new
@@ -1,4 +1,3 @@
-
 Bug Report-
 Brief, in debian 6/7: if mysql server default timezone is not set, Phpmyadmin export wrong timestamp and timezone combination in sql result.
    • assigned_to: Madhura Jayaratne
     

  • Madhura Jayaratne

    Madhura Jayaratne - 2015-04-08
    • status: open --> pending
     

    • aaronyu6

      aaronyu6 - 2015-04-08

      Hi phpmyadmin people,

      I went further to troubleshoot this problem, I found where is the root of
      the problem.

      This problem seems only occurred with phpmyadmin installed by debian's
      package repositories (apt-get)

      as phpmyadmin install doc mentioned

      Debian
      <file: D:="" Users="" mic="" Downloads="" php="" phpMyAdmin-4.4.1.1-english="" phpMyAdmin-4.4.1.1-english="" doc="" html="" setup.html#debian="">

      Debian’s package repositories include a phpMyAdmin package, but be aware
      that the configuration file is maintained in /etc/phpmyadmin and may differ
      in some ways from the official phpMyAdmin documentation.

      =====

      but phpmyadmin in standard debian's package seems not update to day
      I use
      "apt-get update"
      "apt-get upgrade"
      "apt-get install phpmyadmin"
      it shows it is the latest version.
      But when I go to view the export file, it shows that phpMyAdmin version is
      3.3.7deb7 ,and there is not set timezone="+00:00" in this exported version,
      which cause the timezone problem.

      Then I manually upload files and install latest pma version 4.4.1.1, the
      problem is gone with the latest version.

      Do you have an official Debian package repository which always at first
      time follow to the latest version of phpmyadmin?
      If not, the standard debian package using apt-get install seems does not
      provide the correct latest package for us.(the installation doc need to
      change)

      Therefore, if no suitable latest apt-get sources, the Solution is: do not
      use apt-get install to install phpmyadmin, manually upload files is the way.

      my sources.list is as below for your reference, all standard sources

      please use local debian mirror, see http://www.debian.org/mirror/list

      deb http://mirror.sg.gs/debian squeeze main contrib non-free
      deb http://security.debian.org/ squeeze/updates main contrib non-free

      qeneral

      deb http://packages.dotdeb.org squeeze all
      deb-src http://packages.dotdeb.org squeeze all

      php5.4

      deb http://packages.dotdeb.org squeeze-php54 all
      deb-src http://packages.dotdeb.org squeeze-php54 all

      deb http://download.webmin.com/download/repository sarge contrib
      deb http://webmin.mirror.somersettechsolutions.co.uk/repository sarge
      contrib

      deb http://ftp.us.debian.org/debian unstable main contrib non-free

      ========

      Aaron Yu

      On Wed, Apr 8, 2015 at 9:25 PM, Madhura Jayaratne madhuracj@users.sf.net
      wrote:

      • status: open --> pending
      • Comment:

      Thank you for your detailed report.

      However, I am unable to reproduce this on both my local environment as
      well as on our demo server (http://demo.phpmyadmin.net/QA_4_4/).

      In the 'Variables' page what are the values you see for time_zone and
      system_time_zone.

      Status: pending
      Group: Latest_Git
      Labels: timestamp
      Created: Wed Mar 04, 2015 02:55 PM UTC by aaronyu6
      Last Updated: Wed Apr 08, 2015 11:17 AM UTC
      Owner: Madhura Jayaratne

      Bug Report-
      Brief, in debian 6/7: if mysql server default timezone is not set,
      Phpmyadmin export wrong timestamp and timezone combination in sql result.

      The problem can be recreated,below is steps to recreate it

      Env:
      phpmyadmin Version information: 4.3.11 (up to date)
      debian 6/7 64bit
      Brief: If mysql server default timezone is not set, Phpmyadmin export
      wrong timestamp(offset) in sql result.
      How to recreate problem as below

      php time zone is set on asia/shanghai(+08:00)
      Php script to store date and time to mysql timestamp columne: What is
      recorded as '2015-03-04 23:59:37' in timestamp col (you can also manually
      input the value from phpmyadmin, the field must be defined timestamp
      format)
      and it is stored correctly in mysql with correct timezone.
      The problem is after exporting from phpmyadmin(pma)

      Problematic-Senario-1: if not set default_time_zone of mysql my.cnf(use
      default)
      if dun set default_time_zone of mysql my.cnf, mysql timezone by default
      equals to debian SYSTEM timezone(+08:00),
      Display: The Pma displayed record as '2015-03-04 23:59:37': correct. it
      use default debian systemtimezone to display time correctly.

      If use mysqldump to export: the mysqldump result, /!40103 SET
      TIME_ZONE='+00:00'       /;'2015-03-04 15:59:37', so mysqldump result is
      correct also,

      but if pma export sql, it contains 'SET time_zone = "+00:00"'; and
      '2015-03-04 23:59:37' : which is wrong exported output.

      the correct exported content shall be 'SET time_zone = "+00:00"'; and
      '2015-03-04 15:59:37',
      or "SET time_zone = "+08:00"; and '2015-03-04 23:59:37'"
      if import back this wrongly exported sql(quoted again 'SET time_zone =
      "+00:00"'; and '2015-03-04 23:59:37'), the stored record in pma will be
      displayed totally differently from the beginning. because it treate
      23:59:37 as timezone of +00:00, not +08:00
      it seems the phpmyadmin does not catch the default time zone of mysql
      which is transferred from debian System.

      Test-Senario 2(as reference only,to troubleshoot where maybe wrong):
      A: If mysql server set default_time_zone: +00:00 in my.cnf , pma export
      '2015-03-04 15:59:37', correct.

      B: If mysql my.cnf is set default_time_zone +08:00, pma display
      '2015-03-04 23:59:37', correct.
      If phpmyadmin export, it contains "set time_zone +00:00, '2015-03-04
      15:59:37', that is also correct, because mysql default_time_zone is +08:00,
      but phpmyadmin all use +00:00, to do the export, anyway, it is still right,
      even though it only export with +00:00 format

      Analysis: The problem seems only occured when mysql my.cnf does not set
      default_time_zone(using default setting in debian)
      it seems during exporting, the phpmyadmin does not catch the default time
      zone of mysql which is transferred from debian System.
      recap The problem: in debian 6/7: if mysql server default timezone is not
      set, Phpmyadmin export wrong timestamp and timezone combination in sql
      result.

      Temp walk around: set default_time_zone in mysql my.cnf

      Sent from sourceforge.net because you indicated interest in
      https://sourceforge.net/p/phpmyadmin/bugs/4787/

      To unsubscribe from further messages, please visit
      https://sourceforge.net/auth/subscriptions/

      --

      新加坡临床催眠学院
      Tel / Fax: +65 66562106 * Mobile: (Singapore) +65 91856478 (Malaysia)
      013-6688169
      Website:       www.sghypno.com http://www.sghypno.com*
      Facebook: https://www.facebook.com/lifegrowthteam

      *重写内心程序，创造崭新生命；温暖社会人心，实现人生价值 *

       

      Related

      Bugs: #4787

      • aaronyu6

        aaronyu6 - 2015-04-08

        latest version reply please refer to link
        https://sourceforge.net/p/phpmyadmin/bugs/4787/

        On Thu, Apr 9, 2015 at 3:46 AM, Service Team Academy of Clinical Hypnosis
        Pte Ltd service@sghypno.com wrote:

        Hi phpmyadmin people,

        I went further to troubleshoot this problem, I found where is the root of
        the problem.

        This problem seems only occurred with phpmyadmin installed by debian's
        package repositories (apt-get)

        as phpmyadmin install doc mentioned

        Debian

        Debian’s package repositories include a phpMyAdmin package, but be aware
        that the configuration file is maintained in /etc/phpmyadmin and may
        differ in some ways from the official phpMyAdmin documentation.

        =====

        but phpmyadmin in standard debian's package seems not update to day
        I use
        "apt-get update"
        "apt-get upgrade"
        "apt-get install phpmyadmin"
        it shows it is the latest version.
        But when I go to view the export file, it shows that phpMyAdmin version is
        3.3.7deb7 ,and there is not set timezone="+00:00" in this exported version,
        which cause the timezone problem.

        Then I manually upload files and install latest pma version 4.4.1.1, the
        problem is gone with the latest version.

        Do you have an official Debian package repository which always at first
        time follow to the latest version of phpmyadmin?
        If not, the standard debian package using apt-get install seems does not
        provide the correct latest package for us.(the installation doc need to
        change)

        Therefore, if no suitable latest apt-get sources, the Solution is: do not
        use apt-get install to install phpmyadmin, manually upload files is the way.

        my sources.list is as below for your reference, all standard sources

        please use local debian mirror, see http://www.debian.org/mirror/list

        deb http://mirror.sg.gs/debian squeeze main contrib non-free
        deb http://security.debian.org/ squeeze/updates main contrib non-free

        qeneral

        deb http://packages.dotdeb.org squeeze all
        deb-src http://packages.dotdeb.org squeeze all

        php5.4

        deb http://packages.dotdeb.org squeeze-php54 all
        deb-src http://packages.dotdeb.org squeeze-php54 all

        deb http://download.webmin.com/download/repository sarge contrib
        deb http://webmin.mirror.somersettechsolutions.co.uk/repository sarge
        contrib

        deb http://ftp.us.debian.org/debian unstable main contrib non-free

        ========

        Aaron Yu

        On Wed, Apr 8, 2015 at 9:25 PM, Madhura Jayaratne madhuracj@users.sf.net
        wrote:

        • status: open --> pending
        • Comment:

        Thank you for your detailed report.

        However, I am unable to reproduce this on both my local environment as
        well as on our demo server (http://demo.phpmyadmin.net/QA_4_4/).

        In the 'Variables' page what are the values you see for time_zone and
        system_time_zone.

        Status: pending
        Group: Latest_Git
        Labels: timestamp
        Created: Wed Mar 04, 2015 02:55 PM UTC by aaronyu6
        Last Updated: Wed Apr 08, 2015 11:17 AM UTC
        Owner: Madhura Jayaratne

        Bug Report-
        Brief, in debian 6/7: if mysql server default timezone is not set,
        Phpmyadmin export wrong timestamp and timezone combination in sql result.

        The problem can be recreated,below is steps to recreate it

        Env:
        phpmyadmin Version information: 4.3.11 (up to date)
        debian 6/7 64bit
        Brief: If mysql server default timezone is not set, Phpmyadmin export
        wrong timestamp(offset) in sql result.
        How to recreate problem as below

        php time zone is set on asia/shanghai(+08:00)
        Php script to store date and time to mysql timestamp columne: What is
        recorded as '2015-03-04 23:59:37' in timestamp col (you can also manually
        input the value from phpmyadmin, the field must be defined timestamp
        format)
        and it is stored correctly in mysql with correct timezone.
        The problem is after exporting from phpmyadmin(pma)

        Problematic-Senario-1: if not set default_time_zone of mysql my.cnf(use
        default)
        if dun set default_time_zone of mysql my.cnf, mysql timezone by default
        equals to debian SYSTEM timezone(+08:00),
        Display: The Pma displayed record as '2015-03-04 23:59:37': correct. it
        use default debian systemtimezone to display time correctly.

        If use mysqldump to export: the mysqldump result, /!40103 SET
        TIME_ZONE='+00:00'         /;'2015-03-04 15:59:37', so mysqldump result is
        correct also,

        but if pma export sql, it contains 'SET time_zone = "+00:00"'; and
        '2015-03-04 23:59:37' : which is wrong exported output.

        the correct exported content shall be 'SET time_zone = "+00:00"'; and
        '2015-03-04 15:59:37',
        or "SET time_zone = "+08:00"; and '2015-03-04 23:59:37'"
        if import back this wrongly exported sql(quoted again 'SET time_zone =
        "+00:00"'; and '2015-03-04 23:59:37'), the stored record in pma will be
        displayed totally differently from the beginning. because it treate
        23:59:37 as timezone of +00:00, not +08:00
        it seems the phpmyadmin does not catch the default time zone of mysql
        which is transferred from debian System.

        Test-Senario 2(as reference only,to troubleshoot where maybe wrong):
        A: If mysql server set default_time_zone: +00:00 in my.cnf , pma export
        '2015-03-04 15:59:37', correct.

        B: If mysql my.cnf is set default_time_zone +08:00, pma display
        '2015-03-04 23:59:37', correct.
        If phpmyadmin export, it contains "set time_zone +00:00, '2015-03-04
        15:59:37', that is also correct, because mysql default_time_zone is +08:00,
        but phpmyadmin all use +00:00, to do the export, anyway, it is still right,
        even though it only export with +00:00 format

        Analysis: The problem seems only occured when mysql my.cnf does not set
        default_time_zone(using default setting in debian)
        it seems during exporting, the phpmyadmin does not catch the default time
        zone of mysql which is transferred from debian System.
        recap The problem: in debian 6/7: if mysql server default timezone is not
        set, Phpmyadmin export wrong timestamp and timezone combination in sql
        result.

        Temp walk around: set default_time_zone in mysql my.cnf

        Sent from sourceforge.net because you indicated interest in
        https://sourceforge.net/p/phpmyadmin/bugs/4787/

        To unsubscribe from further messages, please visit
        https://sourceforge.net/auth/subscriptions/

        --

        新加坡临床催眠学院
        Tel / Fax: +65 66562106 <%2B65%2066562106> * Mobile: (Singapore) +65
        91856478 <%2B65%2091856478> (Malaysia) 013-6688169
        Website:         www.sghypno.com http://www.sghypno.com*
        Facebook: https://www.facebook.com/lifegrowthteam

        *重写内心程序，创造崭新生命；温暖社会人心，实现人生价值 *

        --

        新加坡临床催眠学院
        Tel / Fax: +65 66562106 * Mobile: (Singapore) +65 91856478 (Malaysia)
        013-6688169
        Website:         www.sghypno.com http://www.sghypno.com*
        Facebook: https://www.facebook.com/lifegrowthteam

        *重写内心程序，创造崭新生命；温暖社会人心，实现人生价值 *

         

        Related

        Bugs: #4787

  • Madhura Jayaratne

    Madhura Jayaratne - 2015-04-08

    Thank you for your detailed report.

    However, I am unable to reproduce this on both my local environment as well as on our demo server (http://demo.phpmyadmin.net/QA_4_4/).

    In the 'Variables' page what are the values you see for time_zone and system_time_zone.

     

  • aaronyu6

    aaronyu6 - 2015-04-08

    Hi,

    I went further to troubleshoot this problem, I found out how to trigger the problem.

    This problem seems only occurred with phpmyadmin installed by debian's package repositories (apt-get), not occurred with manually uploaded and installed ones.

    as phpmyadmin install doc mentioned

    Debian
    Debian’s package repositories include a phpMyAdmin package, but be aware that the configuration file is maintained in /etc/phpmyadmin and may differ in some ways from the official phpMyAdmin documentation.
    =====

    but phpmyadmin in standard debian's repository package seems is not up to date

    I use
    "apt-get update"
    "apt-get upgrade"
    "apt-get install phpmyadmin"
    it shows it is the latest version.
    But when I go to view the exported file, it shows that phpMyAdmin version is 3.3.7deb7 ,and there is no line mentioning "set timezone='+00:00'"(which is shown in latest version), in this exported version, which cause the timezone problem.

    Then I manually upload files and install latest pma version 4.4.1.1, the problem is gone with the latest version.

    Question: Do you have an official Debian package repository which always at first time follow to the latest version of phpmyadmin?
    If not, the standard debian package using apt-get install seems does not provide the correct latest package for us.(the installation doc need to change)

    Therefore, if no suitable up-to-date debian apt-get sources provided,
    suggestion 1: do not use apt-get install to install phpmyadmin, manually upload files is the way.
    suggestion 2:old version phpmyadmin may work well with previous version mysql, if mysql update even smaller version, phpmyadmin remain the same old version, it may cause problem.

    My sources.list is as below for your reference, using all standard sources

    please use local debian mirror, see http://www.debian.org/mirror/list

    deb http://mirror.sg.gs/debian squeeze main contrib non-free
    deb http://security.debian.org/ squeeze/updates main contrib non-free

    qeneral

    deb http://packages.dotdeb.org squeeze all
    deb-src http://packages.dotdeb.org squeeze all

    php5.4

    deb http://packages.dotdeb.org squeeze-php54 all
    deb-src http://packages.dotdeb.org squeeze-php54 all

    deb http://download.webmin.com/download/repository sarge contrib
    deb http://webmin.mirror.somersettechsolutions.co.uk/repository sarge contrib

     

    Last edit: aaronyu6 2015-04-08

  • Madhura Jayaratne

    Madhura Jayaratne - 2015-04-09

    When exporting there is an option to 'Dump TIMESTAMP columns in UTC' (In export page select 'Custom', this is the last option), which is checked by default. It is this option that adds "set timezone='+00:00'" and convert the value to UTC. I don't think this option was available in version 3.3.7

    I'm glad that the problem is resolved with latest version 4.4.1

     

  • Madhura Jayaratne

    Madhura Jayaratne - 2015-04-09
    • status: pending --> out-of-date
     

  • aaronyu6

    aaronyu6 - 2015-04-09

    It is a bit strange, because at the beginning, the problem occurred in both local and production machine.
    and the phpmyadmin Version information is quite up to date: 4.3.11 (up to date)
    yesterday, the problem only occur with production machine.

    Any way, with version 4.4.1.1, this problem is not able to be recreated.
    so this issue can be closed I think

     

  • aaronyu6

    aaronyu6 - 2015-04-09

    Final finding:
    May be useful to some one who face similar problems(I saw some one reported similiar issues online about running apt-get install phpmyadmin)

    run "apt-show-versions phpmyadmin" result:
    phpmyadmin/squeeze uptodate 4:3.3.7-7 (not up to date still)

    But if login phpmyadmin: it shows the version on the screen is 3.3.7deb7 (very old)
    that is conflicted with version ablove

    if run apt-get install phpmyadmin result shows:
    it is latest phpmyadmin

    Below is the default config file being included in apache2.conf, by apt-get installed phpmyadmin (the uri point to the 3.3.7deb7 version one also, and it is confirmed the only phpmyadmin config file included apache2.conf)

    /etc/phpmyadmin/apache.conf

    phpMyAdmin default Apache configuration

    Alias /phpmyadmin /usr/share/phpmyadmin

    <Directory usr="" share="" phpmyadmin="">
    AuthName "Restricted Area"
    AuthType Basic
    require valid-user
    AuthUserFile "/htps/.htpasswd"

    Options FollowSymLinks
DirectoryIndex index.php

<IfModule mod_php5.c>
    AddType application/x-httpd-php .php

    php_flag magic_quotes_gpc Off
    php_flag track_vars On
    php_flag register_globals Off
    php_value include_path .
</IfModule>

    </Directory>

    Authorize for setup

    <Directory usr="" share="" phpmyadmin="" setup="">
    <IfModule mod_authn_file.c="">
    AuthType Basic
    AuthName "phpMyAdmin Setup"
    AuthUserFile /etc/phpmyadmin/htpasswd.setup
    </IfModule>
    Require valid-user
    </Directory>

    Disallow web access to directories that don't need it

    <Directory usr="" share="" phpmyadmin="" libraries="">
    Order Deny,Allow
    Deny from All
    </Directory>
    <Directory usr="" share="" phpmyadmin="" setup="" lib="">
    Order Deny,Allow
    Deny from All
    </Directory>
    =========

    Conclusion: somewhere is wrong with "apt-get install phpmyadmin", because it does not install the latest repository, do not use it. use manual way to have the latest phpmyadmin