Questions tagged [mariadb]

Open source RDBMS that forked from MySQL. Add a version-specific tag like mariadb-10.5 if that context is important.

Filter by
Sorted by
Tagged with
26 votes
2 answers
10k views

How do I properly perform a MySQL bake-off?

I want to performance test (aka bake-off) MySQL server rpm against some other forks such as Percona server, MariaDB, and possibly some others. I'm hoping that by asking this question I can better ...
randomx's user avatar
  • 3,934
5 votes
3 answers
6k views

How do I determine how much data is being written per day through insert, update and delete operations?

The longevity of SSDs is largely determined by the amount of bytes written by insert, update and delete operations. What is the best way to accurately determine how much data is being written by ...
Craig Sefton's user avatar
3 votes
1 answer
2k views

How does MariaDB handle ENUM types used in FOREIGN KEY constraints?

Off the heels of this question, How does MySQL handle joins on ENUMs? How does MySQL work if the referencing table has more and less values in the ENUM than the table being referenced? Is it ...
Evan Carroll's user avatar
  • 62.7k
147 votes
4 answers
241k views

Is it safe to delete mysql-bin files?

I have MM Replication in mysql, and I want to squeeze some free space in the box be deleting unnecessary files, I came across these mysql-bin files inside /var/db/mysql/ There are hundreds of those ...
user avatar
5 votes
1 answer
9k views

How do I create a point with an SRID in MySQL?

MySQL supports using ST_PointFromText to construct a point with an SRID, ST_PointFromText(wkt[, srid [, options]]) Constructs a Point value using its WKT representation and SRID. This ...
Evan Carroll's user avatar
  • 62.7k
6 votes
1 answer
2k views

Does MySQL or MariaDB support GIS KNN Index Queries (k-nearest neighbors)?

Can you do k-nearest neighbors algorithm (k-NN) queries on MySQL for example, to find the nearest point in PostgreSQL with PostGIS, I can run a kNN query on the spatial index with <-> SELECT ...
Evan Carroll's user avatar
  • 62.7k
13 votes
4 answers
122k views

Error 1236 - "Could not find first log file name in binary log index file"

Our setup: Master: MariaDB 10.0.21 Slave: MariaDB 10.0.17 Replication was working fine until recently at which point the slave's DBs had to be restored from a dump. I performed all of the necessary ...
rinogo's user avatar
  • 427
6 votes
1 answer
9k views

MariaDB - "ERROR 1205 ... Lock wait timeout exceeded; ..." when doing multiple parallel UPDATEs affecting different rows

We are using MariaDB 10.1.19 hosting a legacy database. We have an lpr table (on InnoDB) where we want to move from a text column gate to a normalized foreign key gate_id. Our code is doing the UPDATE ...
Marco Pantaleoni's user avatar
6 votes
3 answers
6k views

MySQL INSERT INTO SELECT vs BULK INSERTs performance

Currently, I am trying to copy data from TABLE1 to TABLE2. In terms of insertions performance, would it be the same or faster if I would to do BULK INSERT manually (i.e BULK insert every 10K records ...
CloudNineHorizon's user avatar
5 votes
1 answer
3k views

MariaDB 10.1.22 to use more RAM rather than CPU [closed]

Im using: Server version: 10.1.22-MariaDB - Source distribution server spec: 128GB RAM CPU: E5-2600 we have around 80 tables with moderate data below 10k record per table, all of them are InnoDB. ...
Teddybugs's user avatar
  • 177
1 vote
1 answer
948 views

How does MariaDB handle joins on ENUMs?

I'm just wondering because MySQL ENUMs are essentially tied to the table, how do they handle JOINs if two tables have the same ENUM? Are they join based on the ENUM value's or the string-key (input)? ...
Evan Carroll's user avatar
  • 62.7k
15 votes
2 answers
10k views

What are the drawbacks of using Galera Cluster instead of Master/Slave Replication?

What are the drawbacks of using Galera Cluster instead of regular Master/Slave Replication? Galera's 0 slave lag time, Synchronous replication and no Single point of failure seem very appealing then ...
Sam's user avatar
  • 334
10 votes
7 answers
36k views

TRUNCATE TABLE statement sometimes hangs

Why does the TRUNCATE TABLE statement hang sometimes? What are the reasons for this type of issue? I am migrating from MySQL to MariaDB. This problem doesn't happen with MySQL, only with MariaDB. ...
Haseena's user avatar
  • 571
8 votes
3 answers
7k views

How can I improve my SQL statement with weekly results with week starting on Thursday or any other day of the week?

I am a total newb, and I couldn't find a good way to do this anywhere. I have a database table that contains statistics that are recorded at various time throughout the week. The reporting week ...
James Lee Vann's user avatar
8 votes
2 answers
19k views

MySQL - Autoincrement does not increment sequentally if last row was deleted

I have a table which contains an auto incremented primary key id. If I delete the last row (highest id, for exampe id = 6) and insert a new row, the new id starts at 7. Which paramater I have to ...
giordano's user avatar
  • 257
8 votes
2 answers
7k views

Does MariaDB or MySQL implement the VALUES (expression) table value constructor?

I am just wondering if MariaDB or MySQL implements the <table value constructor> in the SQL Spec. In SQL Server and PostgreSQL this is done with standardized VALUES (expression)? SELECT * FROM (...
Evan Carroll's user avatar
  • 62.7k
4 votes
1 answer
5k views

When to disable MySQL Query Cache

I have a high volume MariaDB 10 server thousands of databases of with over a million tables. Currently we are only hitting around 20% query cache hits. We are already employing Memcache and other ...
jozwikjp's user avatar
  • 346
4 votes
3 answers
15k views

The table is full - MariaDB

I am getting the following error whist trying to execute a long running query. The table 'C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp#sql1664_349_19be' is full The C drive (NTFS) on the server ...
SkelDave's user avatar
  • 143
3 votes
1 answer
13k views

How to start mysqld with read-only data directory?

I'm trying to start MariaDB in a docker container, using a data directory from the host mounted read-only. MySQLd is of course shut down on the host. Unfortunately, even with adding the read_only flag ...
Thomas Johnson's user avatar
3 votes
1 answer
19k views

InnoDB: Error: Table “mysql”.“innodb_table_stats” not found

I've got the same error, although I'm running galera cluster with 3 nodes. I tried following the instructions from InnoDB: Error: Table "mysql"."innodb_table_stats" not found after ...
Aaron's user avatar
  • 151
3 votes
4 answers
12k views

Purge old records from mysql ibdata1 file

Is it possible to purge old records from an ibdata1 file without taking down the database attached to it? Records that have been deleted from the database are still present when I scan ibdata1 for ...
Andrew Clinton's user avatar
3 votes
1 answer
4k views

mariadb Unknown system variable 'server_uuid'

Im trying to setup replication between mariadb(10.1.31-MariaDB - MASTER) and mysql(5.7.12-log - SLAVE). After executed the start slave, I got the below error. Fatal error: The slave I/O thread ...
TheDataGuy's user avatar
  • 1,920
3 votes
5 answers
17k views

You do not have privileges to manipulate with the users!

Error message: You do not have privileges to manipulate with the users! Appears in PhpMyAdmin using Mariadb 10.3.7 on MacOs High Sierra installation from Homebrew I'm logged in as root installed ...
Danny F's user avatar
  • 133
2 votes
1 answer
5k views

'kill <thread id>' gives ERROR 1095 (HY000): You are not owner of thread ... even though I am

I did an ALTER TABLE on a huge table on a MariaDB Galera Cluster 10.2, but after a while of waiting I changed my mind and tried to kill the connection. However, MariaDB said ERROR 1095 (HY000): You ...
dbdemon's user avatar
  • 6,301
2 votes
2 answers
4k views

json functions: double quotes added when going through a variable

Im stuck with what it seems simple question, when I create a JSON using the MariaDB/MySQL json functions going through variables, it adds double quotes which is annoying. However, if I don't go ...
Tarcaird's user avatar
2 votes
2 answers
2k views

Is there any performance difference among MariaDB (xtradb plugin) and XtraDB Server for InnoDB? [closed]

Title says it all. However i have to enter at least 30 characters.
cedivad's user avatar
  • 131
1 vote
1 answer
2k views

MySQL/MariaDB stored procedure inserts even with syntax error

createdAt has not been defined, but the query still runs when I call this proc and inserts a "0" date. Why, shouldn't it fail? DELIMITER $$ USE `MobiFit_Dev` $$ DROP PROCEDURE IF EXISTS `...
Lee's user avatar
  • 171
1 vote
2 answers
3k views

MySQL/MariaDB Limitations

we're running MariaDB on our DB server. We have to install multiple databases on each DB instance (in fact each DB server runs multiple MariaDB instances) because each database must be separated. We'...
Unkl Benz's user avatar
1 vote
3 answers
5k views

MariaDB: Missing FILE_CHECKPOINT

Running MariaDB v10.9.3 on Arch Linux. I've hosted a file storage cloud with it for years. Recently decided to create a database for another project, and am very new to MySQL interactions, so it's ...
Kal's user avatar
  • 41
1 vote
1 answer
2k views

Point in time recovery with mariabackup

I have a full backup dir and a bunch of hourly (incremental) backup directories taken with mariabackup, which is a port of the XtraBackup. How does one restore the MariaDB up to a specific minute (...
Slawomir's user avatar
  • 453
0 votes
1 answer
46 views

Connecting to “foreign” database on the same DBMS

I am somewhat surprised to see that JDBC allows me to transparently SELECT from a table in a "foreign" database, on the same DBMS, for which I have the requisite privileges, without ...
Xin's user avatar
  • 3
0 votes
1 answer
327 views

Do the type-specific WKT- constructors function differently from ST_GeomFromText?

From the docs on MariaDB, for ST_LineStringFromText Constructs a LINESTRING value using its WKT representation and SRID. ST_LineFromText(), ST_LineStringFromText(), ST_LineFromText() and ...
Evan Carroll's user avatar
  • 62.7k
39 votes
5 answers
6k views

When is the right time to use MariaDB instead of MySQL, and Why?

What we earn and what we lost with this migration? What should I expect as drawbacks after the migration? Is it really unnecessary to change the applications in any situation?
Maniero's user avatar
  • 2,728
26 votes
3 answers
57k views

How do I resolve this error, "ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'"?

When I run the following command I get an error, however one of my scripts requires it. SET time_zone = 'UTC'; ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'
Evan Carroll's user avatar
  • 62.7k
23 votes
6 answers
107k views

MySQL FOREIGN KEY constraint is incorrectly formed

I have the following table definition: CREATE TABLE `async_task` ( `idasync_task` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `idasync_type` int(10) unsigned NOT NULL, `priority` tinyint(3) ...
Daniel Gray's user avatar
18 votes
3 answers
12k views

Why does importing a 12 GB .sql file take more than 36 hours?

I've been waiting now for 36 hours for a 12 GB .sql file to be imported with a simple type site.sql | mysql command. I can see the ibdata1 is growing still, currently nearly 40 GB. Considering the ...
servermanfail's user avatar
17 votes
2 answers
38k views

Does MariaDB support native JSON column data type?

I’m not talking about dynamic columns, I’m asking about native column JSON data type. In simple words, can I run the following code on any MariaDB version? CREATE TABLE example (names JSON); As far ...
Omranic's user avatar
  • 271
16 votes
5 answers
102k views

How to determine the optimal sort_buffer_size?

I read from a sample configuration file which says the following: # Sort buffer is used to perform sorts for some ORDER BY and GROUP BY # queries. If sorted data does not fit into the sort buffer, a ...
Question Overflow's user avatar
14 votes
4 answers
136k views

Datatype for phone number: VARCHAR, INT or BIGINT?

So this will be the dummy question of the year but I need to ask since is not the first time I pass through this. Take a look to the following table definition: Take a look at the column from_number ...
ReynierPM's user avatar
  • 1,764
13 votes
2 answers
8k views

To what extent is MariaDB Java client a perfect replacement for MySQL JDBC (Connector/J)?

Premise: I ship a Java application which will make a JDBC connection to a MySQL database. (At the time of this writing it could be 5.1 or 5.5, but hopefully the question and answer don't rely heavily ...
mdahlman's user avatar
  • 347
13 votes
1 answer
30k views

MariaDB 10.1.38 - Specified key was too long; max key length is 767 bytes

I have a table that has 3 columns: id(int 11) | user_id(int 4) | title(varchar 512) ____________|________________|___________________ 1 | 3 | Thing X 2 | 3 ...
Emanuel Ones's user avatar
7 votes
1 answer
19k views

Corrupt InnoDB: Start mysqld only innodb_force_recovery=6

I am working with version 10.0.19-MariaDB-1~trusty-log and I'm only able to restart mysqld with innodb_force_recovery=6 and I don't know why. The output of the /usr/sbin/mysqld is the following: ...
betonorius's user avatar
6 votes
1 answer
21k views

What is the HY number (HY000) in MySQL Error messages?

When I get an error some of them have an HY code, ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory") What is HY000 ...
Evan Carroll's user avatar
  • 62.7k
6 votes
3 answers
14k views

Maximum overall size of MariaDB binary logs

I am looking for a way to prevent MariaDB binary logs from taking a huge size when they grow faster than usual. variable max_binlog_size set the maximum size of ONE binary logfile. variable ...
Nicolas Payart's user avatar
6 votes
2 answers
4k views

How to insert values into a highly normalized database without excessive duplicate checking?

The situation: The lead for our project has decided to use a highly normalized database as our database design. Meaning that literally every field of a big table is an ID instead of the actual value....
Joe's user avatar
  • 269
5 votes
2 answers
8k views

How to make a FULLTEXT search with ORDER BY fast?

I'm trying to get a simple FULLTEXT match to be faster when using order by on another column on a table with over 100 million rows. The basis is one table with a fulltext on two columns and I want to ...
JJJ's user avatar
  • 187
5 votes
5 answers
15k views

Install MariaDB without password prompt in Ubuntu

I need to install MariaDB server in Ubuntu without password prompt. For this purpose, I execute the following commands, but it doesn't gave me any progress. It again shows password prompt. I used the ...
Haseena's user avatar
  • 571
5 votes
4 answers
4k views

Which columns should be indexed when all may be used in different search queries?

Background I'm working on a website for a movie theather chain currently located in four different cities (might expand in the future). They use the same, single-database website for all cities, ...
Visual Vincent's user avatar
5 votes
2 answers
17k views

grastate.dat with seqno -1 on a healthy cluster. Why?

We use those versions: # rpm -qa | egrep '(galera|maria)' mariadb-libs-5.5.41-2.el7_0.x86_64 mariadb-galera-common-5.5.40-6.el7ost.x86_64 mariadb-galera-server-5.5.40-6.el7ost.x86_64 mariadb-5.5.41-2....
Sybil's user avatar
  • 2,488
4 votes
2 answers
20k views

Mariadb - Variable 'innodb_log_file_size' is a read only variable

Im using mariadb 10.0.29, I need to change the innodb file size to 256M, default size 50M, My config file is located on /etc/mysql/mariadb.conf.d/50-server.cnf I have added the below line, but it ...
TheDataGuy's user avatar
  • 1,920