Hot answers tagged

2

Yes your idx_tpydjournaltrans_ie_BUSINESSUNIT index is being used (I assume length 139 corresponds the the length of the three index components). Other single element index elements may be assisting other queries. For each table reference in a query, a single index is used. The idx_tpydjournaltrans_ie_BUSINESSUNIT index corresponds to the 3 elements of the ...


1

I've encountered a similar problem, and the issue seems to have to do with the MariaDB version. Namely, that as of MariaDB 10.4, the mysql.user table has been replaced with a view, while the real data is in the new mysql.global_priv table. This causes the DROP TABLE to fail, which, in turn, causes the CREATE TABLE to fail. Adding --ignore-table=mysql.user to ...


1

its really hard to be able to truly optimize this without knowing the sizes of the tables, but I would recommend reorganizing queries like this with common table expressions (CTEs). A CTE works like a sub query, but allows you to throw results in memory and refer back to it multiple times. Since you go back to the same tables multiple times within your sub ...


1

All(?) INSERTs, including IODKU and REPLACE can burn ids, leading to gaps. This happens with the statement is rolled back. And for INSERT IGNORE when some UNIQUE triggers "ignore". Even without multi-master, you can get AUTO_INCREMENT values out of order. This is because the auto_inc is grabbed when needed, but not replicated until COMMITted. ...


1

aria_s3_copy is in the mariadb-plugin-s3 package which isn't installed by the mariadb docker library container image. Extending the docker container and installing this plugin package is possible.


1

AND DATE_ADD(ord.paid_on, INTERVAL ord.extended_duration HOUR) is a DATETIME, which might be in the middle of today. BETWEEN (CURDATE() - INTERVAL 5 DAY) AND CURDATE() excludes anything after midnight this morning. Please provide the order that is miscategorized.


1

\u0395 is just 6 characters to MariaDB. Searching for the 5-character "word" u0395 would succeed. You need to change the JSON strings to have characters, not unicode codepoint encoding. In PHP, I would consider write a 1-time script to bring in each line, decode it, and write it back out. Meanwhile, the writing should be changed to json_encode($...


1

Using index means that an index was "covering" and was used. Or, at least, when the EXPLAIN was run, it decided that. I have looked at thousands of EXPLAINs and slowlog entries. In theory, they always match. However, there is nothing that forces the EXPLAIN to actually match what happens and is recorded in the slowlog. I have seen as many as 6 ...


1

Solution: We need to add login_unix_socket: /var/run/mysqld/mysqld.sock to the task: - name: update mysql root password for root account mysql_user: name: root login_unix_socket: /var/run/mysqld/mysqld.sock host: 'localhost' password: '{{ mysql_root_password }}' priv: "*.*:ALL,GRANT" check_implicit_admin: true Explanation:...


1

Local testing on toy test instance: show global status like 'memory_used' 276360624 show session status like 'memory_used'; 69608 Looking the code the "global memory used" is the total of all the per connection memory allocations, but exclude actual global allocations like key_buffer, innodb_buffer_pool etc.


1

another fiddle test So both MariaDB on tx_read_only and mysql on transaction_read_only have the session variable set to the current session value. The "next transaction access mode" remains hidden. Catching insert errors seems like the only option.


Only top voted, non community-wiki answers of a minimum length are eligible