4
It depends on what storage engine you use. The default storage engine has been InnoDB since MySQL 5.5 (circa 2010), so I will assume that's what you use, since you did not specify.
OPTIMIZE TABLE <name> does the same as ALTER TABLE <name> FORCE or ALTER TABLE <name> ENGINE=InnoDB. This should be done with care, because it rebuilds the ...
3
After clarification in the fiddle you provided, the only other way I can interpret your issue is you're saying you're not seeing the columns for the users table even though you joined to it. That's because you didn't list any columns from the users table in the SELECT list.
You can add any or all columns from your users subquery you want to the SELECT list ...
2
You say:
but we have the issue that, if in theory, the insert query takes up to
5 minutes, there will be a difference in the result between the INSERT
INTO and the DELETE statement, leading to 5 minutes of lost data.
No, you don't have to risk losing data! That's what TRANSACTIONs are for!
InnoDB uses (Multi-Version Concurrency Control - MVCC). You should ...
2
insert into x set id = -1, s = 'apple'; blocks the row with id=-1 until then end of the transaction. Hence the other process delays without deadlocking.
This is why you should worry about max(id)+1
PROCESS 1 PROCESS 2
set autocommit = 0;
start transaction;
insert into x set id = -1, s = 'apple';
select @m := max(id) + 1 from x;
--...
2
MariaDB-10.2.2 InnoDB merged from 5.7.14
MariaDB-10.2.35 Merge new release of InnoDB 5.7.32
Lots of miscellany features have been merged at random times.
MySQL 8.0 and MariaDB 10.5 are quite different. Still, you may not notice any differences unless you reach into rarely-used features.
The two play leapfrog. For example.
MariaDB had CTEs and Windowing ...
1
If I understand your requirement correctly, this is the kind of problem where window aggregation shines. Use the window version of the COUNT(*) function on the filtered dataset to obtain the counts alongside the other columns. Then filter on the count results to get only the rows you want. Your output can include any or all of the columns your table has:
...
1
The first approach is to create an index by sessionId. But its datatype is MEDIUMTEXT...
So the approach is: normalize your data, move sessionId values into separate table and refer to it by foreign key. The reference column will be compact (4 or 8 bytes) and indexed, so its usage in the query as GROUP BY expression will improve.
The columns clientID and ...
1
This can be significantly sped up (if you have INDEX(datum)):
WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH)))
< 0 LIMIT
-->
WHERE datum > NOW() - INTERVAL 6 MONTH
Partitioning is the ultimate speedup: http://mysql.rjweb.org/doc.php/partitionmaint
Is your table have some id or timestamp that is guaranteed to be unique and ...
1
Answer provided by mustaccio in a comment:
COUNT(uniqueid) Total_Dialed_Calls,
COUNT(CASE disposition WHEN 'ANSWERED' THEN 1 END) Total_Connected_Calls...
1
Mariadb 10.5 seems to store passwords in another table mysql.global_priv as well as in mysql.user. I didn't have my users in global_priv which was causing the login issues. Setting the password again fixed the issue.
It seems that global_priv is not being replicated, from master and it contains old values before the database was turned into a replica.
I've ...
1
Ensure host B is your active host in the dual master
Rest of instructions are on host A
As reading from A.tablename will probably crash it, try this with another tablename to be familiar with what its doing. pt-table-sync --dry-run --print --execute --sync-to-master h=hostB,D=db,t=sometablename
look closely at the above.
SET GLOBAL innodb_file_per_table=1 (...
1
The best way to do this is probably the following. This solution makes use of the GENERATED COLUMN functionality of MariaDB >= 10.3 (also in MySQL >= 5.7) - it's really very handy for queries like this - it also makes the query much more readable.
All the code below is available on the fiddle here:
CREATE TABLE login
(
user_id VARCHAR (255) NOT NULL ...
1
See if its what you want
-- this main query retrieve all users under the subquery domain
SELECT a.UserID
, substring_index(a.UserID, '@', -1)
, MAX(a.lastupdate)
FROM MyTable a
WHERE substring_index(a.UserID, '@', -1) IN (
-- this subquery search all domain inactive in last 12 months
SELECT substring_index(a.UserID, '@', -1) AS domain
...
1
Doh, I looked at numerous examples online, and these were pretty much the same as I used in my example, however I did think I was missing something, then I found another example which proved that I was missing:
EXECUTE stmt;
So the complete prepared statement looks like:
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
1
You may need to perform a FLUSH PRIVILEGES; before trying to create an account that was recently removed. This is the result of a very old bug in MySQL server. By flushing the privileges the system will properly “forget” that simon@localhost existed.
1
This is bordering on an opinion-based question, because of course anyone can have their own justification for either choice.
I would not choose to add the intermediary table, for a few reasons.
One is that as you know, the intermediary table cannot have a foreign key reference to a table on another MySQL instance. Foreign keys can span schemas, but not ...
1
OR is inefficient. It can be turned into UNION. An example: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#or
Why use INET6_ATON on IPV4 values?
Finding something in a range is poorly optimized, primarily because the Optimizer does not know whether there are overlapping ranges. One solution is given here: http://mysql.rjweb.org/doc.php/ipranges
1
This is what I say about MySQL/MariaDB without columnstore:
Not good: "sum up to 1 TB" versus "1,25 TB of SSD storage". As a Rule of Thumb, you should have half your disk free for maintenance and growth. As a minimum, there should be enough room for an extra copy of the largest table -- data+indexes. This allows any ALTER to run ...
Only top voted, non community-wiki answers of a minimum length are eligible
