New answers tagged

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 ...
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 ...
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. ...
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 ...
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 ...
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