New answers tagged mariadb
0
votes
Error with SQL DELETE statement with MariaDB
The syntax for MariaDB does not allow an alias in a DELETE
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name [PARTITION (partition_list)]
...
Also note the following
DISTINCT in an IN or ...
- 8,940
0
votes
Error with SQL DELETE statement with MariaDB
In MariaDB, you can use table aliases only if you use the multi-table DELETE syntax.
https://mariadb.com/kb/en/delete/ shows the syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name [...
- 12.2k
0
votes
Error with SQL DELETE statement with MariaDB
I am not comfortable with NOT UT.ld_tag IN. Syntactically, it looks weird to me.
Try flipping the NOT UT.ld_tag IN to UT.ld_tag IN NOT
DELETE FROM ld_uniquetag UT
WHERE UT.ld_tag NOT IN (SELECT ...
- 178k
1
vote
Accepted
Listing Entries from Table1 which do not appear in Table 2
Seems you just want a NOT EXISTS, and a parameter for customer
SELECT *
FROM Table1 AS t1
WHERE customer = ?
AND NOT EXISTS (SELECT 1
FROM Table2 AS t2
WHERE T2.invoiceNo = T1.invoiceNo
)
- 8,940
0
votes
RAM recommedation for Mysql DB server
You are mixing two things; first, let me separate them:
Disk space:
database size is 400 GB
Disk space: 600 GB & Tmp space: 110 GB
Largest table size exists in database approx 25 GB size.
So...
...
- 73.8k
2
votes
Accepted
MariaDB upgrade requires table rebuild. Rebuild takes too long. Options?
You can use an online schema change tool such as pt-online-schema-change or gh-ost to perform a table rebuild without incurring downtime.
At my last job, we ran hundreds of ALTER TABLE commands every ...
- 12.2k
1
vote
MariaDB: Using composite primary key and index on the same columns gives strange results
MySQL is deprecating the assigning of @variables in SELECTs.
MariaDB will probably follow suit at some point.
See http://mysql.rjweb.org/doc.php/myisam2innodb#index_issue_2_column_pk
for workarounds.
- 73.8k
0
votes
Mysql/Mariadb: can you format a date+time to show the time zone at a given location?
Perhaps you are approaching the problem from the wrong direction.
MySQL and MariaDB have a paltry set of tools to help you.
The "Locale" is a global setting.
Two (only two) datatypes: ...
- 73.8k
1
vote
What cons should I consider if I put index on every UUID column in my database
When you create a foreign key, the referencing column should be of the same type as the referenced column
Absolutely. They should both be INT or both be UUID strings or otherwise compatible ...
- 73.8k
2
votes
Accepted
MariaDB: Large INSERT SELECT query failed without generating an error
Why might this query have failed? (The error log files were silent
during the relevant time window.)
It's possible that you might've hit the statement level timeout that's controlled by ...
- 261
1
vote
Mariadb upgrade problem
and after updating the tables, mariadb will no longer start
It sounds as if you may have run the update with the wrong user or otherwise somehow made the data directory unreadable and/or unwritable ...
- 6,091
1
vote
Performance for query filtered by user preference
My query to db is something like this:
author_id IN ('1,5,3,7,10') OR topic_id IN('3,6,9')
Hopefully only something like that.
Double check you "IN" clause syntax - I doubt you have an ...
- 7,601
0
votes
How to detect a trigger set up on database INFORMATION_SCHEMA?
You can dump the triggers to an external file.
See my old post Can mysqldump dump triggers and procedures?
You can also execute this to see the trigger names and their definitions
SELECT
...
- 178k
1
vote
Accepted
MySQL upgrade 5.7 to 8.0 with Keyring encrytption failed to complete upgrade
[SOLVED]
The problem happened when the mysql_upgrade step failed to decrypt mysql.plugin table, so to avoid this I just rolled back to 5.7, decrypted all MySQL db tables and ran the upgrade again.
...
- 476
1
vote
Accepted
Can't get MariaDB docker to work without password
The code from the entrypoint is:
if [ -n "$MARIADB_PASSWORD" ] || [ -n "$MARIADB_PASSWORD_HASH" ] && [ -n "$MARIADB_USER" ]; then
mysql_note "...
- 6,998
5
votes
Accepted
Does simple key value storage on MySQL/MariaDB requires index on key?
Without an index, you will get a full table scan every time. Glad you indexed the table.
I would recommend you make a compound index for every better performance as the table grows:
ALTER TABLE cache ...
- 178k
2
votes
Accepted
This query gives error. right syntax to use near 'on vehicles.school_id = schools.id left join drivers on drivers.school_id = ...' at line 8
The error is in the following line:
left join (select count(id) total_vehicles from vehicles)
on vehicles.school_id = schools.id
Each query in brackets must be given an alias, which can then be ...
- 3,059
0
votes
Mysql/Mariadb: can you format a date+time to show the time zone at a given location?
A small comment from a long long time ago (over 19 years) in the code:
Now we don't use abbreviations in server but we will do this in future.
What is the case, is the mysql.time_zone_transition_type ...
- 6,998
0
votes
Optimize MariaDB 10.6
innodb_buffer_pool_size=1024M --> 30G
query_cache_size=64M --> 0
If queries are slow, provide SHOW CREATE TABLE and EXPLAIN SELECT .... Indexing and/or reformulation is the likely ...
- 73.8k
0
votes
MariaDB: defective MyISAM and ARIA Indexes after errorless import of big data file
Will you be having a mixture of MyISAM and InnoDB? I see a big buffer_pool (for InnoDB), but no setting for key_buffer_size, which is needed for MyISAM.
How much RAM do you have?
Advice on memory ...
- 73.8k
2
votes
Accepted
Pulling the postcode area from the full postcode
You should be able to use the same regex in MariaDB as you did in the JavaScript by using the REGEXP_SUBSTR function, like:
SELECT REGEXP_SUBSTR(Postcode, '^[A-Z]+') AS PostalArea From ...
- 136
0
votes
Are the MariaDB and MySql drivers interchangeable when connecting to a MySQL backend Aurora cluster?
As of 2023, maria jdbc driver should not be used to connect to aurora mysql instances.
From https://mariadb.com/kb/en/about-mariadb-connector-j/
driver 3.0 is a complete rewrite of the connector. ...
- 101
-1
votes
On MariaDB event scheduler doesn't run
For me said it too, that event_scheduler are on if I check show processlist
But it doesnt work correctly.
One Event has stop on 28 Feb and other on 22 Feb.
Have to set event_scheduler on in my.cnf
...
- 1
3
votes
Accepted
Avoid writing on a slave MariaDB server
When I have managed replicas, I always set this on the replica:
SET GLOBAL read_only=ON;
Of course the replication thread gets to ignore this. But it ensures that all user requests (even superuser ...
- 12.2k
2
votes
Accepted
Data retention in Mariadb
Staring with MariaDB-10.9.1, System Versioned tables can auto create partitions and have them expire.
So even for a table without timestamps in an explicit column:
alter table t1 WITH SYSTEM ...
- 6,998
3
votes
Data retention in Mariadb
You could PARTITION BY RANGE(TO_DAYS(...)) each table. However, it is somewhat strange to have more than one table that is big and in need of purging periodically.
I would recommend weekly partitions....
- 73.8k
Top 50 recent answers are included