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

Oracle Comp.: Sql-Error on "SELECT name, comment FROM mysql.proc"

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.3.7
    • 10.3.8
    • Parser
    • None

    Description

      After

      SET sql_mode = 'oracle';

      an error occurs with the following sql:

      SELECT name, comment FROM mysql.proc

      SQL Error [1064] [42000]: (conn:8) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'comment FROM mysql.proc
      LIMIT 0, 200' at line 1
      Query is : SELECT name, comment FROM mysql.proc

      The problem is that this is used by several database IDEs (e.g. DBeaver, dbForge, etc.). They cannot list any packages with this error.

      Attachments

        Activity

          Apparently comment is a reserved word in SQL mode, so it has to be quoted.

          MariaDB [test]> set sql_mode='oracle';
          Query OK, 0 rows affected (0.00 sec)
           
          MariaDB [test]> SELECT name, `comment` FROM mysql.proc;
          +--------------------+---------+
          | name               | comment |
          +--------------------+---------+
          | AddGeometryColumn  |         |
          | DropGeometryColumn |         |
          +--------------------+---------+
          2 rows in set (0.00 sec)
          

          Assigning to bar to confirm whether it's intentional.

          elenst Elena Stepanova added a comment - Apparently comment is a reserved word in SQL mode, so it has to be quoted. MariaDB [test]> set sql_mode= 'oracle' ; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> SELECT name , `comment` FROM mysql.proc; + --------------------+---------+ | name | comment | + --------------------+---------+ | AddGeometryColumn | | | DropGeometryColumn | | + --------------------+---------+ 2 rows in set (0.00 sec) Assigning to bar to confirm whether it's intentional.

          People

            bar Alexander Barkov
            Estabillo Mebo
            Votes:
            1 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.