0

I have a MySQL table mes_mes that contains ~12 000 000 rows. This is the structure:

CREATE TABLE `mes_mes` (
  `id` bigint(20) NOT NULL,
  `sent_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `to_id` int(11) NOT NULL,
  `from_id` int(11) NOT NULL,
  `msg_txt` text CHARACTER SET latin1 NOT NULL,
  `read_t` varchar(20) NOT NULL DEFAULT '0',
  `img_id` varchar(11) CHARACTER SET latin1 NOT NULL,
  `em_id` tinyint(1) NOT NULL,
  `is_received` tinyint(1) NOT NULL DEFAULT '1',
  `is_copy` bigint(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;


DELIMITER $$
CREATE TRIGGER `instant_messages_d` BEFORE DELETE ON `mes_mes` FOR EACH ROW BEGIN


UPDATE users_table SET inbox_msg_total=inbox_msg_total-1 WHERE id=OLD.to_id;


UPDATE mes_fol set msg_total=msg_total-1 where sender=OLD.from_id and receiver=OLD.to_id;
END
$$
DELIMITER ;


ALTER TABLE `mes_mes`
  ADD PRIMARY KEY (`id`) USING BTREE,
  ADD KEY `to_id` (`to_id`,`is_received`,`id`),
  ADD KEY `to_id_2` (`to_id`,`from_id`,`id`),
  ADD KEY `from_id` (`from_id`,`id`),
  ADD KEY `is_copy` (`is_copy`);

ALTER TABLE `mes_mes`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;

Problem: The number of rows is growing and now I am not able to update the table because the request hangs for 1-2 minutes and then the server just crashes. I don't understand where is the problem because the where clause is primary key.

MySQL query:

"UPDATE mes_mes set msg_txt='Spam', img_id='', em_id='10' WHERE id IN (". implode(', ', $_POST['msg_id']).")"

$_POST['msg_id'] does not contain more than 20 values.

P.S. The problem occurs on production server. I ran into this twice and unfortunately didn't look in the logs. I don't want to repeat anymore because the server becomes unavailable. I suspect the server runs out of memory. As long as there were fewer records in the database, there was no problem. Just wonder how such a simple query can cause problems.

SanMiguel
  • 5
  • 2
  • 1
    12 million rows are not an issue for a database server like MySQL. So you will have to start digging what the _actual_ issue is. You say "the server is crashing" ... but what does that actually mean? What do you see in the system logs? What does the memory consumption show? What does the "explain" feature mysql offers reveal? Is that key _really_ used? – arkascha Oct 14 '22 at 08:52
  • What actual error message do you get? – ADyson Oct 14 '22 at 09:20
  • 1
    P.S. **Warning:** Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli / PDO etc. **Never** insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. – ADyson Oct 14 '22 at 09:21
  • 1
    See also: [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) . Parameterising your queries will also greatly reduce the risk of accidental syntax errors as a result of un-escaped or incorrectly quoted input values. If you learnt your current technique from a tutorial or book, please don't use it again. – ADyson Oct 14 '22 at 09:21
  • 1
    Warning: Using MyISAM is **extremely risky**, there's no journal to rollback if the server crashes. You should be using InnoDB. – tadman Oct 14 '22 at 10:11
  • 1
    If your server crashes, it should log why in the MySQL error log. You should investigate that *immediately* since MySQL hardly ever crashes for no reason. – tadman Oct 14 '22 at 10:12
  • Try to all an index to `id` : "ALTER TABLE `mes_mes` ADD INDEX `id` (`id`);". – D. Dimopoulos Oct 14 '22 at 10:15
  • ADyson, of course I check Post values before running the query. – SanMiguel Oct 14 '22 at 10:42
  • Check them how, exactly? According to what you've shown us you're inserting the raw POST data directly into the SQL string. That's unsafe and vulnerable to SQL injection. The **only** reliable solution to that is to use prepared statements and parameters, as described by all those resources I linked you to. Whatever you're doing in your code to "check" the POST values, it is not and cannot be relevant to preventing SQL injection. – ADyson Oct 14 '22 at 10:46
  • `I don't want to repeat anymore because the server becomes unavailable`...you're going to need to, unless you can reproduce it in a test environment. Get agreement to do it when the system is quiet, look in the logs and get any info you need, then restart. Or if you keep logs for a period of time, surely you can just look back in the files to the last time you know it happened? Check both PHP logs and mysql logs, would be my suggestion. – ADyson Oct 14 '22 at 10:57

0 Answers0