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

Cannot insert value into a column of type YEAR in a CONNECTed MariaDB table

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 10.9.3, 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL)
    • 10.4.29
    • None
    • Windows 10. Problem occurs both with local installation and with server installation (Windows Server).

    Description

      I have a CONNECT to a MariaDB server table which contains a column of type YEAR.
      Trying to insert a value into this column always fails. (I have had no problem at all inserting values into other data types that I tried.)

      Minimal example using a locally installed MariaDB 10.9.3 server: (Problem came up originally when CONNECTing to a MySQL 5.7.x server – same effect.)

      USE test;
      CREATE TABLE t0 (jahr year(4) not null);
      CREATE TABLE t0_conn ENGINE=CONNECT CONNECTION='mysql://USERNAME:PASSWORD@localhost/test/t0' `TABLE_TYPE`='MYSQL';
      INSERT INTO t0 VALUES (2022); -- inserting into underlying table directly works OK
      SELECT * FROM t0_conn; -- works OK, shows inserted value 2022
      INSERT INTO t0_conn VALUES (2023);
      -- fails with error message:
      ERROR 1296 (HY000): Got error 122 '(1265) Data truncated for column 'jahr' at row 1 [INSERT INTO `t0` (`jahr`) VALUES ('2023-01-01 00:00:00')]' from CONNECT
      

      It seems that the CONNECT engine over-eagerly converts the value-to-insert from its simple form (2022) into datetime form before sending it over the CONNECTion.
      On the receiving end, trying to insert a datetime value into a year field fails. (This latter is expected: trying to insert a datetime directly into the underlying table t0 will also fail, and correctly so.)

      Thus, the problem seems to be the over-eager data type conversion performed by the CONNECT engine.

      Attachments

        Activity

          People

            TheLinuxJedi Andrew Hutchings
            gwselke Gisbert W. Selke
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.