I decided to use the trigger in the trigger

SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tname');

did not work. The phpMyAdmin was able to find out the following:

  1. select database() returns the name of the database

  2. select * from information_schema.tables where TABLE_SCHEMA=имя_базы works correctly

  3. select * from information_schema.tables where TABLE_SCHEMA=database() returns fields with TABLE_SCHEMA equal information_schema

MySql 5.5, if that

What am I doing wrong?

  • Well, not actually convinced that with the condition TABLE_NAME='tname' selects exactly one entry. And it’s very interesting why the trigger might need such a construction. For getting an id not through get_last_id () will lead to potential problems. - Mike
  • 1) made sure. 2) it is necessary to initialize another field, if it is not filled, with this value. in the trigger after it can not be updated, in the trigger before it is not yet filled - splash58
  • to cope - I coped by complicating requests, but judging by Google, it should work - splash58

1 answer 1

Trigger worker, most likely a problem in the context of the call. Perhaps somewhere a query is called with a fully qualified table name, indicating the database. In this case, the database is not selected and the DATABASE() function returns NULL . In general, the functions DATABASE() and USER() very insidious, since they can produce different values ​​under different conditions, breaking the database logic if it is tied to them (this is especially hard, it makes itself felt during replication).

In request

 SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA=DATABASE()

You explicitly specify the database name information_schema , which becomes the current one and is returned by the DATABASE() function. In the circle of SQL programmers, this function is notorious, especially if you then have to scale replication to multiple servers.

  • that's the problem, I plan to drag the base, it means that I will have to climb the trigger and correct it :( The most surprising thing for me is that the result in phpMyAdmin is so strange - splash58
  • In general, I complicated the requests and threw this place out of the trigger, but did not add clarity :) - splash58
  • See, here in this query select * from information_schema.tables where TABLE_SCHEMA = database () you explicitly requested inforfation_schema to from. Maybe here the dog rummaged? - cheops
  • it draws 40 columns in which the TABLE_SCHEMA column should be equal to the base, not 'information_schema'. And they turn out exactly - splash58
  • Yes, because information_schema is also a database and you chose it with the help of information_schema.tables - now it is current - cheops