Bug #36461 In an embedded server create trigger reports an error about missing mysql databa

• Submitted: 2 May 2008 0:28
• Modified: 5 May 2008 12:13
• Reporter: Barry Leslie
• Status: Can't repeat
• Category: MySQL Server: Embedded Library ( libmysqld )
• Severity: S3 (Non-critical)
• Version: 5.1
• OS: Any
• CPU Architecture: Any
• Tags: triggers

[2 May 2008 0:28] Barry Leslie

Description:
Embedded servers do not use mysql privileges so they do not need the mysql database. (I think.)

It looks like the is an "#ifdef" missing in the create trigger code to prevent it from checking to see if the user has privileges to create the trigger when being executed from an embedded server.

How to repeat:
Remove the mysql database from an embedded server's data dir and then try creating a trigger.

[5 May 2008 12:13] MySQL Verification Team

Thank you for the bug report. I tested with current source server and I could
not repeat:

miguel@hegel:~/dbs/mysql-5.1/libmysqld/examples> ./mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 0
Server version: 5.1.25-rc-embedded-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database dbtest;
Query OK, 1 row affected (0.01 sec)

mysql> use dbtest
Database changed
mysql> CREATE TABLE test1(a1 INT);
Query OK, 0 rows affected (0.12 sec)

mysql> CREATE TABLE test2(a2 INT);
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE test4(
    ->   a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    ->   b4 INT DEFAULT 0
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> DELIMITER |
mysql> 
mysql> CREATE TRIGGER testref BEFORE INSERT ON test1
    ->   FOR EACH ROW BEGIN
    ->     INSERT INTO test2 SET a2 = NEW.a1;
    ->     DELETE FROM test3 WHERE a3 = NEW.a1;  
    ->     UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
    ->   END;
    -> |
Query OK, 0 rows affected (0.07 sec)

mysql> delimiter ;
mysql> show create trigger testref\G
*************************** 1. row ***************************
               Trigger: testref
              sql_mode: 
SQL Original Statement: CREATE DEFINER=`miguel`@`localhost` TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;  
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END
  character_set_client: latin1
  collation_connection: latin1_swedish_ci
    Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql> select * from mysql.user;
ERROR 1146 (42S02): Table 'mysql.user' doesn't exist
mysql> 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| dbtest             | 
| mysqltest1         | 
| test               | 
| x                  | 
| y                  | 
+--------------------+
6 rows in set (0.01 sec)