New answers tagged

1

I also had the same issue using Mariadb 10.4 master-slave replication. I was using the setting innodb_thread_concurrency in the config file with a non-zero value. I updated it to: innodb_thread_concurrency = 0 And now it's working again.


0

SOLVED: here is the link to the original answer in other post, is a step by step guide to solve the problem, thank you. https://dba.stackexchange.com/a/244610/187038


0

I really appreciate Ecd. What worked for me: 1.- I had a backup of the base a few months ago this helped me to lift this backup in xampp in Windows 10 and create the tables to have the structure (configuration: Windows 10, xampp-windows-x64-7.1.30-5-VC14) mysql my.ini configuration file at the end NOTE: Some tables did not have ROW_FORMAT =...


0

If you are on version 8.x (or above), you can take advantage of window functions like LAG/LEAD. This functions return a value of previous or next next row inside a partition, following an specific order. Using your data: SELECT userID, tStamp, status, CASE WHEN COALESCE(LAG(status) OVER (PARTITION BY userID ORDER BY tStamp), '') = status ...


0

Simon's answer is a good one. He effectively suggests splitting the range of possible ids into 2. This can also be done in a more flexible manner by using a bitshift right operator. You can then decide how many ranges you want in the same 64(?) bit number space to have by deciding on how many shifts right you move the number. I don't know about MySQL but a ...


0

Create a view on each of the tables, and on one, replace Id with Id * 2 so it will always be an even number. In the other, replace Id with Id * 2 + 1, so it will always be odd. The numbers will be unique across the two tables, and it is always possible to go from original Id to the view's one and then back to the original. Or just add the calculated ...


0

Plan A: Have a 3rd table that contains an AUTO_INCREMENT plus any common columns between the two existing tables. Plan B: Build a "sequence" table which has 2 columns: an AUTO_INCREMENT and another column to key into the table. Probably REPLACE is the command to get a new id without growing the table. REPLACE is performed in 2 steps: DELETE any row(s) ...


1

Let it go to completion! InnoDB does a lot of things to speed up execution. One of them is to delay DELETEs via the Change Buffer (research that). It essentially writes changes (inserts, updates, deletes) to the Change Buffer, then eventually gathers those changes (in a more I/O efficient way!) together to write to disk. The effect is two-fold: Your ...


1

I found solution the issue: 1.It's neccessary to configure and validate every instance with dba.checkInstanceConfiguration() and dba.configureInstance(). 2.Specify port number of instance which we'll add to the cluster. Example in my script: cluster.addInstance({user: "admin", host: "srv-mysql-02", port:3306, password: dbPass})


0

It's possible it could still grow, although it would be slowly if you aren't inserting and you aren't making changes. If it is being changed and you are logging changes with your recovery option, it will still grow the log. You may want to check all the jobs that touch that database as well and switch it to simple recovery if you haven't already done so.


0

(too messy for Comment) If you change and (`t1`.`user_id`, `t2`.`node_id`) in ( ('825773502556700160', '825773839074099712')) to and (`t1`.`user_id` = 825773502556700160 AND `t2`.`node_id` = 825773839074099712) does 8.0.17 work? Note the difference: You are touching two different tables. Was that intentional?


0

Have you tried a simple IN clause other than the one in your code? If not, I would write a couple simple ones and try them to see if they run. If they run successfully, it's either you copied it incorrectly, OR it's got to be something specific with how your code is using the IN statement. If no IN statements run, something was changed, either by design ...


1

Did you have innodb_file_per_table = ON? Regardless of the above setting, ibdata1 will have some stuff in it. Without more specifics (size of that file, the above setting, number of tables, number of .ibd files, etc.), I cannot be more specific. In my opinion, "tiny" tables may as well be built with file_per_table = OFF.


0

Curious to see if this speeds up the query; tried to utilize row goals by doing joins with a few where clauses first, then using top to count the rows for accurate estimates moving forward, then apply the rest of the where clauses. Also replaced the IN statement with a BETWEEN statement. SELECT * FROM ( SELECT TOP (2000000000) FROM `contents` AS `n` ...


0

And also you have to do the "explain" command for your queries. If that query doesn't use any indices then that also increase the execution time. Compare your local dataset size and the actual prod data size. and optimize your query.


Top 50 recent answers are included