3

I'm designing a comments MySQL db, and my comments table has fields:

  • id primary key, auto-incr
  • thread int, not null
  • content

All replies to the same comment, in addition to that root comment must share the same thread. This is simple when a user is replying to a comment but what about when a new root comment is posted? I figured I would set thread=id for root comments.

Problem is, I don't know how to write a query that will reuse the just created id value within the same query when filling thread. Is this even possible?

I've tried

INSERT INTO `comments`
VALUES (NULL, LAST_INSERT_ID(), 'hi there')

This gives me the id from the previous insert, not the current one. Do I have to use 2 queries?

BeetleJuice
  • 39,516
  • 19
  • 105
  • 165
  • 1
    are you talking about mysql_insert_id () – sumit Nov 29 '16 at 02:18
  • Your question is unclear. There is no ID for current query, since the row wasn't inserted yet. You can only fetch the ID generated on the last `INSERT`. You can try to figure out what the ID will be by doing `SELECT MAX(id) + 1 FROM table`, but that is actually inaccurate, the auto increment counter is kept elsewhere. – Havenard Nov 29 '16 at 02:22
  • @sumit Is that a MySQL function or a PHP function? If I use it within the query, I get an error. If it's a PHP function it won't be helpful because PHP cannot know the insert ID at the time it sends the query to MySQL because the insert ID hasn't been created yet at that time – BeetleJuice Nov 29 '16 at 02:23
  • @Havenard I think you understood the Q. I would like whatever `id` is created to be automatically used for `thread` unless I have also specified a `thread` value. I know I can do it in 2 queries. My question is: can I do it in one? – BeetleJuice Nov 29 '16 at 02:25
  • `mysql_insert_id()` is a PHP/C function, that probably applies to other languages and have equivalent versions for MySQLi and other libraries. – Havenard Nov 29 '16 at 02:25

3 Answers3

3

Do I have to use 2 queries?

Yes. As you discovered, the id value hasn't been generated yet in a BEFORE INSERT trigger. But you can't change your NEW.thread value in an AFTER INSERT trigger.

You can't rely on reading the INFORMATION_SCHEMA, because you can cause a race condition.

You'll just have to do the INSERT, and then immediately execute:

UPDATE comments SET thread=id WHERE id=LAST_INSERT_ID() AND thread IS NULL;

If it's a root comment.

See also my past answers on the similar topic:

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Hey Bill thanks for taking the time. How is your suggestion not vulnerable to a race condition as well? In other words, could there not be another `INSERT` between the previous one and the triggered `UPDATE`? – BeetleJuice Dec 01 '16 at 07:18
  • Yes there could be concurrent inserts, but each insert will generate its own unique id. That part is not at risk. Then each thread will have its own respective id value. Keep in mind that LAST_INSERT_ID() only returns the most recent id value generated in the same session. So two concurrent sessions can generate their own id values without affecting each other. – Bill Karwin Dec 01 '16 at 08:18
  • Cool; that explains why I would run queries from my PHP code, but the result of `LAST_INSERT_ID()` in my DB management software wouldn't change. Since I'm running 2 queries after all, I captured the insert ID value returned by the first, and just used it in the second, instead of calling `LAST_INSERT_ID()`. – BeetleJuice Dec 01 '16 at 08:35
  • @BillKarwin does the unavailability of the auto-increment value stem from the fact that the storage engine actually assigns the value after the `BEFORE INSERT` trigger runs, thus the SQL layer doesn't have access to it (since it literally has not yet been assigned)? Or is this a design defect that could have been done differently? Or is the auto-increment undefined here, even in "standard SQL?" – Michael - sqlbot Dec 01 '16 at 11:18
  • 1
    @Michael-sqlbot I believe the former. The storage engine is responsible for generating AI values. Generating an AI value requires a kind of table lock to protect against a race condition, even in the case of InnoDB. It's desirable to delay allocation of an AI value until after the trigger runs, in case the trigger causes a signal to abort. There's no sense in allocating a value that won't be used. – Bill Karwin Dec 01 '16 at 16:53
  • 1
    @Michael-sqlbot, and yes, auto-increment mechanisms were not part of standard SQL for a long time. I think maybe in 2006 there was finally an definition for auto-incrementation syntax in the language (though no brand of SQL database complies with that yet), but the SQL standard doesn't define implementation details anyway. – Bill Karwin Dec 01 '16 at 16:55
0

Thanks to Michael's answer I started looking into triggers; basically event-handlers that run some code when something happens. Michael's trigger didn't work for me, but the following does:

USE `my_db_name`;
DELIMITER $$
CREATE TRIGGER comments_bi 
BEFORE INSERT ON `comments`
FOR EACH ROW
BEGIN
  DECLARE nextID INT DEFAULT 0;

  SELECT AUTO_INCREMENT INTO nextID FROM information_schema.tables
  WHERE table_name = 'comments' AND table_schema = DATABASE();

  IF NEW.`thread` IS NULL OR NEW.`thread` = 0 THEN
    SET NEW.`thread` = nextID;
  END IF;

END $$
DELIMITER ;

One big caveat: because this trigger requires access to the information_schema, only the root account could define it.

Thanks to this answer for inspiration

Community
  • 1
  • 1
BeetleJuice
  • 39,516
  • 19
  • 105
  • 165
  • 1
    I don't recommend this solution, because it is susceptible to race conditions. Two concurrent inserts could read from the INFORMATION_SCHEMA simultaneously, and try to use the same value. – Bill Karwin Dec 01 '16 at 06:43
  • 1
    I've taken your recommendation; I'm now running two queries. – BeetleJuice Dec 01 '16 at 08:35
-1

You can simply set thread to NULL to signify that the comment is a root comment rather than attached to a thread.

Connor Gurney
  • 597
  • 2
  • 6
  • 16
  • That's what I did with my previous design. It made other queries more complex though by adding this `thread=null` corner case; for instance if I want to group all comments by thread and sort by postTime, it's no longer simple. If I want to tally up all votes (comments can be voted up or down) by thread, etc... So this time around I am hoping for a simpler implementation – BeetleJuice Nov 29 '16 at 02:19