StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMysql Innodb does not return the last row on select with desc order
text
Body
copied!<p>We have a table scheme like following:</p> <pre><code>CREATE TABLE IF NOT EXISTS `offers` ( `id` int(11) NOT NULL AUTO_INCREMENT, `campaign_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `price` double NOT NULL, `ip` varchar(15) NOT NULL, `cdate` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `campaign_id` (`campaign_id`,`price`) ) ENGINE=InnoDB DEFAULT CHARSET=latin5 AUTO_INCREMENT=190514 ; </code></pre> <p>On each new offer by the user we check if the last order is given by the same user with:</p> <pre><code>"select user_id from offers where campaign_id='".$campaign['id']."' order by id desc limit 1" </code></pre> <p>if the user_id is same we prevent the new offer to protect the user from accidental double clicks.</p> <p>if there is not any problem with the offer we insert the offer with:</p> <pre><code>"insert into offers(campaign_id,user_id,price,ip,cdate) values (".$campaign['id'].",".$user['id'].",'".$price."','".$_SERVER['REMOTE_ADDR']."',".time().")" </code></pre> <p>But the problem is the select only returns the last inserted row after about 1 second. That means the user can insert multiple offers if they click the button too fast.</p> <p>We use 5.5.30-30.2-log Percona Server as our database server. Following is our my.cnf file:</p> <pre><code>[mysqld] datadir = /var/lib/mysql tmpdir = /var/lib/mysqltmp socket = /var/lib/mysql/mysql.sock skip-external-locking = 1 skip-name-resolve open-files-limit = 40000 max_heap_table_size = 64M tmp_table_size = 64M log-error = /var/log/mysqld.log thread-cache-size = 50 table-cache = 4096 table-open-cache = 4096 table-definition-cache = 512 query-cache-size = 0 query-cache-limit = 16M query-cache-type = 0 sort-buffer-size = 1M read-buffer-size = 1M read-rnd-buffer-size = 8M join-buffer-size = 1M tmp-table-size = 64M max-heap-table-size = 64M back-log = 100 max-connections = 10000 max-connect-errors = 10000 max-allowed-packet = 256M interactive-timeout = 360 wait-timeout = 360 innodb = FORCE key-buffer-size = 32M myisam-sort-buffer-size = 4M innodb-buffer-pool-size = 60G innodb-log-file-size = 256M innodb_log_files_in_group = 2 innodb-log-buffer-size = 4M innodb-file-per-table = 1 innodb-thread-concurrency = 8 innodb-flush-log-at-trx-commit =2 server-id = 1 slow-query-log = 1 slow-query-log-file = /var/lib/mysqllogs/slow-log </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload