Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6789

segfault in Item_func_from_unixtime::get_date on updating table with virtual columns

Details

    Description

      Hey,
      since i added two virtual columns to a table querying that table cause some random segfaults.

      The table looks like that:

      CREATE TABLE

      CREATE TABLE `tl_astars_reservation_group` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `tstamp` int(10) unsigned NOT NULL DEFAULT '0',
        `pid` int(10) unsigned NOT NULL DEFAULT '0',
        `resid` int(10) unsigned NOT NULL DEFAULT '0',
        `time` int(10) unsigned NOT NULL DEFAULT '0',
        `time_hr` int(10) unsigned AS (`time`/3600) VIRTUAL,
        `repeatEach` varchar(64) NOT NULL DEFAULT '',
        `recurrDayWise` varchar(64) NOT NULL DEFAULT '',
        `duration` int(10) unsigned NOT NULL DEFAULT '0',
        `seriesBegin` int(10) unsigned NOT NULL DEFAULT '0',
        `seriesBeginWeekdayHr` varchar(50) AS (DATE_FORMAT(FROM_UNIXTIME(`seriesBegin`), '%W')) VIRTUAL,
        `seriesEnd` int(10) unsigned NOT NULL DEFAULT '0',
        `onhold` char(1) NOT NULL DEFAULT '',
        `locked` char(1) NOT NULL DEFAULT '',
        `season` varchar(64) NOT NULL DEFAULT '',
        `specialprice` decimal(20,4) DEFAULT NULL,
        `paid` char(1) NOT NULL DEFAULT '',
        `weeks` varchar(64) NOT NULL DEFAULT '',
        `day` varchar(7) NOT NULL DEFAULT '0',
        `downpayment` decimal(20,4) DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `pid` (`pid`),
        KEY `resid` (`resid`),
        KEY `locked` (`locked`)
      ) ENGINE=MyISAM AUTO_INCREMENT=9885 DEFAULT CHARSET=utf8

      The new colums are the following ones:

      `time_hr` int(10) unsigned AS (`time`/3600) VIRTUAL,
      `seriesBeginWeekdayHr` varchar(50) AS (DATE_FORMAT(FROM_UNIXTIME(`seriesBegin`), '%W')) VIRTUAL,

      You can find the entire stacktrace as an attachment. The IRC user `tanj` recommended to try the following command to fix the issue:

      mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

      But this did not solve the problem at all.
      If you need more data, please contact me. I am happy to provide them.

      Greetings
      Leo

      Attachments

        Issue Links

          Activity

            leo.unglaub Leo Unglaub added a comment -

            So basicly, you are not going to fix the segfault, you are just forbidding to use some functions in virtual columns? In my opinion this is not the best solution, because it already works most of the time. It just crashes sometims. Also not having a FROM_UNIXTIME in virtual columns renders them pretty much useless for everyone who has to deal with a lot of PHP inserted timestamps.

            leo.unglaub Leo Unglaub added a comment - So basicly, you are not going to fix the segfault, you are just forbidding to use some functions in virtual columns? In my opinion this is not the best solution, because it already works most of the time. It just crashes sometims. Also not having a FROM_UNIXTIME in virtual columns renders them pretty much useless for everyone who has to deal with a lot of PHP inserted timestamps.

            well, "crashes sometimes" is a total show stopper .

            leo.unglaub, can you shed more light on your use case? FROM_UNIXTIME implies a timezone, which timezone is used at your system? Will it be ok for you if it was GMT? (or some other timezone that you would have to specify as an extra parameter to FROM_UNIXTIME).

            Are there any reasons to use a virtual column and not define a VIEW?

            psergei Sergei Petrunia added a comment - well, "crashes sometimes" is a total show stopper . leo.unglaub , can you shed more light on your use case? FROM_UNIXTIME implies a timezone, which timezone is used at your system? Will it be ok for you if it was GMT? (or some other timezone that you would have to specify as an extra parameter to FROM_UNIXTIME). Are there any reasons to use a virtual column and not define a VIEW?
            leo.unglaub Leo Unglaub added a comment -

            well, "crashes sometimes" is a total show stopper .

            You are absolutly right. What i meant was, that it is currently already possible, it just needs a bugfix instead of a removal.

            can you shed more light on your use case? FROM_UNIXTIME implies a timezone, which timezone is used at your system? Will it be ok for you if it was GMT?

            The server itself runs on UTC (GMT), the client is forced to set it's timezone according to it's needs. In my opinion this is also the time zone that should be used in virtual columns. As you explained it to me in IRC the thd pointer already contains the current connection state. This should also contain the timezone set by the user.

            I am personally against adding some new functions that take a timezone as a param. This would only confuse most of the people, because they now have two functions doing the same. This would look to me like a Microsoft VB solution. (Lets translate method names to make it more simple, but in reallity they screwed it up bad)

            Are there any reasons to use a virtual column and not define a VIEW?

            Personally i am against using views for small manipulations on a table. Because if you manipulate the original table, you also have to update the view. Now you have two places you have to manipulate if you change the schema.

            Greetings
            Leo

            leo.unglaub Leo Unglaub added a comment - well, "crashes sometimes" is a total show stopper . You are absolutly right. What i meant was, that it is currently already possible, it just needs a bugfix instead of a removal. can you shed more light on your use case? FROM_UNIXTIME implies a timezone, which timezone is used at your system? Will it be ok for you if it was GMT? The server itself runs on UTC (GMT), the client is forced to set it's timezone according to it's needs. In my opinion this is also the time zone that should be used in virtual columns. As you explained it to me in IRC the thd pointer already contains the current connection state. This should also contain the timezone set by the user. I am personally against adding some new functions that take a timezone as a param. This would only confuse most of the people, because they now have two functions doing the same. This would look to me like a Microsoft VB solution. (Lets translate method names to make it more simple, but in reallity they screwed it up bad) Are there any reasons to use a virtual column and not define a VIEW? Personally i am against using views for small manipulations on a table. Because if you manipulate the original table, you also have to update the view. Now you have two places you have to manipulate if you change the schema. Greetings Leo
            leo.unglaub Leo Unglaub added a comment -

            If you need me to explain my special use case, ping me on IRC. That should be easier

            leo.unglaub Leo Unglaub added a comment - If you need me to explain my special use case, ping me on IRC. That should be easier

            I just ran into the same issue with one of my systems. Having the same use case, so "converting" PHP timestamps to human readable date/time values.

            FROM_UNIXTIME implies a timezone, which timezone is used at your system? Will it be ok for you if it was GMT? (or some other timezone that you would have to specify as an extra parameter to FROM_UNIXTIME).

            My server is running UTC+1, which is also what I'd expect FROM_UNIXTIME to use. But having to specify the timezone explicitly seems to be good option as well.

            pprkut Heinz Wiesinger added a comment - I just ran into the same issue with one of my systems. Having the same use case, so "converting" PHP timestamps to human readable date/time values. FROM_UNIXTIME implies a timezone, which timezone is used at your system? Will it be ok for you if it was GMT? (or some other timezone that you would have to specify as an extra parameter to FROM_UNIXTIME). My server is running UTC+1, which is also what I'd expect FROM_UNIXTIME to use. But having to specify the timezone explicitly seems to be good option as well.

            People

              serg Sergei Golubchik
              leo.unglaub Leo Unglaub
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.