I got a very simple select statement, for example:
SELECT id, some_more_fields_that_do_not_matter FROM account WHERE status = '0' LIMIT 2
Keep in mind that the above returns the following identifiers: 1,2
The next thing I do is loop these lines in for each and update some entries:
UPDATE account SET connection = $_SESSION['account_id'], status = '1' WHERE id = $row_id
Now the rows in the table with identifier 1,2 have the status "1" (which I check to make sure that the rows are updated correctly). If this fails, I will cancel everything. As soon as everything is in order, I have a counter in the first place, which in this case is 2, so 2 lines should be updated, and I check this with a simple COUNT (*). This information will also be sent by e-mail, for example, with the following data (which means that everything was updated correctly):
- Time of update: 2013-09-30 16:30:02 - Total rows to be updated (selected) = 2 - Total rows successfully updated after completing queries = 2 The following id should have been updated (these where returned by the SELECT statement): 1,2
So far so good. Now comes the weird part. However, the very next query made by another user sometimes returns, for example, the identifier 1,2 (but this is not possible because they should never be returned by the SELECT statement, because they no longer contain the status “0.” The following: Now I get an electronic a letter, for example:
- Time of update: 2013-09-30 16:30:39 - Total rows to be updated (selected) = 10 - Total rows successfully updated after completing queries = 8 The following id should have been updated (these where returned by the SELECT statement): 1,2,3,4,5,6,7,8,9,10
Now it’s really strange that 1 and 2 are selected by the update. In most cases, this goes well, but very rarely it simply does not return and returns the same ID that has already been updated with the status "1".
Pay attention to the time between these updates. This is not the same time. At first I thought that it would be something like this, that these requests would be executed at the same time (which is impossible correctly?). Or is it possible? Or maybe the request has been cached and I need to change some parameters in my mysql.conf file?
I never had this problem, I tried every update method, but it seems to be happening. Is it possible to somehow combine these 2 requests into one huge update request? All data is the same and does nothing strange. I hope someone knows that this can cause a problem and why this happens by accident (rarely).
EDIT:
I updated the script, added microtime to check how long SELECT, UPDATE and CHECK-(SELECT) .
The first member (with ID 20468) makes a call to the address: 2013-10-01 08:30:10
2/2 lines were correctly updated from the following 2 identifiers: 33412,33395
Requests taken together 0.878005027771 seconds
The second member (with identifier 10123) makes a call at: 2013-10-01 08:30:14
20/22 lines were correctly updated from the following 22 identifiers: 33392,33412,33395,33396,41489,13011,12555,27971,22811 and a few more, but not important
Requests taken together 3.3440849781036 seconds
Now you see that 33412 and 33395 return SELECT again.
The third member (with ID 20951) makes a call to the address: 2013-10-01 08:30:16
9/9 lines were correctly updated from the following 9 identifiers: 33392,33412,33395,33396,41489,13011,12555,27971,22811
Requests taken together Did not return anything that bothers me a bit too
Since we don’t know how long the last requests lasted, we only know that the first and second should work correctly without problems, because if you look, there are 4 seconds between them. and execution time is 3.34 seconds. In addition, the first one started from 2013-10-01 08:30:17 , because the time that is recorded for the call (when sending it by e-mail) is at the end of the script. Check to find out how much time it took to start the first request and stop right after the last request, and this is before I send the letter (of course).
Could there be something in my.cnf file that mysql does is weird? However, I do not understand why id did not return any runtime for the last (third) call. The solution for this would be a Queue of these actions, first saving them to a table and executing them one at a time due to the cron job. But this is not exactly what I want, it should be instantly when a member makes a call. Thanks for the help so far.
In any case, here is my my.cnf , if anyone has suggestions for me (the server has 16 GB of RAM installed):
[client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 myisam-recover = BACKUP max_connections = 20 query_cache_type = 1 query_cache_limit = 1M query_cache_size = 4M log_error = /var/log/mysql/error.log expire_logs_days = 10 max_binlog_size = 100M innodb_buffer_pool_size = 333M join_buffer_size = 128K tmp_table_size = 16M max_heap_table_size = 16M table_cache = 200 [mysqldump] quick quote-names max_allowed_packet = 16M [mysql]
EDIT 2:
$recycle_available = $this->Account->Membership->query(" SELECT Account.id, (SELECT COUNT(last_clicks.id) FROM last_clicks WHERE last_clicks.account = Account.id AND last_clicks.roulette = '0' AND last_clicks.date BETWEEN '".$seven_days_back."' AND '".$tomorrow."') AS total, (SELECT COUNT(last_clicks.id)/7 FROM last_clicks WHERE last_clicks.account = Account.id AND last_clicks.roulette = '0' AND last_clicks.date BETWEEN '".$seven_days_back."' AND '".$tomorrow."') AS avg FROM membership AS Membership INNER JOIN account AS Account ON Account.id = Membership.account WHERE Account.membership = '0' AND Account.referrer = '0' AND Membership.membership = '1' HAVING avg > 0.9 ORDER BY total DESC"); foreach($referrals as $key => $value) { $this->Account->query("UPDATE account SET referrer='".$account_id."', since='".$since_date."', expires='".$value['Account']['expires']."', marker='0', kind='1', auction_amount='".$value['Account']['auction_amount']."' WHERE id='".$recycle_available[$key]['Account']['id']."'"); $new_referral_id[] = $recycle_available[$key]['Account']['id']; $counter++; } $total_updated = $this->Account->find('count',array('conditions'=>array('Account.id'=>$new_referral_id, 'Account.referrer'=>$account_id, 'Account.kind'=>1)));