Questions tagged [mariadb]

Open source RDBMS designed to be a "drop-in" replacement for MySQL. Add a version-specific tag like mariadb-10.2 if that context is important.

Filter by
Sorted by
Tagged with
39
votes
5answers
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?
27
votes
2answers
9k 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 ...
24
votes
8answers
33k views

MariaDB Can't init tc log

I've tried every solution on the Internet but my MariaDb server continue to fail, continue to betray me, continue to destroy my tiny DevOps world. My attempts to smooth the situation included all ...
20
votes
3answers
35k 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'
17
votes
3answers
11k 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 ...
15
votes
2answers
29k 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 ...
14
votes
2answers
7k 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 ...
13
votes
4answers
76k 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 ...
13
votes
2answers
20k views

Why does auto increment jumps by more than the number of rows inserted?

I am very perturbed by this weird behaviour I am seeing in the auto_increment value recorded in the bidID of a Bids table after performing bulk insertion using a stored procedure: INSERT INTO Bids (...
12
votes
5answers
64k 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 ...
12
votes
2answers
11k views

Install MariaDB 10 on Ubuntu without prompt and no root password

Pretty much the same as this question but I don't want to have a root password (this is just a dev machine). Here's what I've got: export DEBIAN_FRONTEND=noninteractive sudo debconf-set-selections &...
12
votes
2answers
7k 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 ...
10
votes
4answers
66k 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 ...
9
votes
1answer
14k 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 ...
9
votes
4answers
7k views

Length of varchar column - is there an advantage to using 255? [duplicate]

I often come across columns that are varchar(255), so presumably this is a convention of sorts. 255 is 2^8 - 1. So is there something 'magical' about lengths that are 2^n - 1? Or just specifically 255?...
8
votes
6answers
23k 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. ...
8
votes
2answers
344 views

Covering index used despite missing column

I have the following query, using MariaDB 10 / InnoDB: SELECT id, sender_id, receiver_id, thread_id, date_created, content FROM user_message WHERE thread_id = 12345 AND placeholder = FALSE ORDER ...
8
votes
2answers
17k 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 ...
8
votes
2answers
16k views

MariaDB: disable foreign key checks

On MySql, in order to truncate a table or delete rows when it's normally impossible because of foreign keys (InnoDB only), we use this command: SET FOREIGN_KEY_CHECKS=0; On MariaDB, while this ...
8
votes
1answer
10k views

Do you still use MyISAM or prefer Aria storage engine?

If Aria storage engine (previously called Maria) is the "new" MyISAM, which supports transaction and automatic crash recovery: Why still use MyISAM ? Should changing storage engine from MyISAM to ...
8
votes
2answers
5k views

Which characters are allowed in MySQL/MariaDB user account names?

It's not clear to me which characters can occur in MySQL/MariaDB user account names, and which are illegal. I've read section 6.3.1 "User Names and Passwords" in the MySQL documentation. This section ...
8
votes
1answer
8k views

What's the advantage of thread pool in MySQL/MariaDB?

MariaDB thread pool MySQL thread pool I've read official document of MySQL/MariaDB thread pool,but I'm still confused about it; As I understand, thread pool has two advantages: 1. limit too many ...
8
votes
3answers
6k 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 ...
8
votes
1answer
5k views

Substantial difference between database size under MySQL and actual size on disk

When I check the size of my databases under MySQL I get this: MariaDB [(none)]> SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB", sum(...
8
votes
2answers
4k views

Innodb: after 48 hours of optimizing 10mb/sec write speed

I run this on a 8 core server with NVME storage (2TB) and 64 GB ram. The disks are hell fast, 1.1 GB/sec seq and 70-100k IOPS full duplex. Because I had so horrible performance with mysql 5.7 I ...
7
votes
2answers
10k views

What is the optimal solution for storing real time time-series in MariaDB / MySQL?

Use case: A measurement creates a given number of images. For each image we need to store a small set of quality indicators (floats, doubles) along with an image integer [1 ...N], a timestamp and one ...
7
votes
3answers
4k views

Is MariaDB a suitable drop in replacement for MySQL on a standard LAMP stack?

I've been wondering lately what (if any) are the improvements available in MariaDB over 'conventional' MySQL? I understand that where platform interoperability and/or backwards compatibility may be ...
7
votes
3answers
10k views

Mariadb MySQL Tuner report confusing

I'd like to ask you to clarify the report from mysqltuner regarding MariaDB database. The mysqltuner was called with --nogood flag! >> MySQLTuner 1.7.1 - Major Hayden <major@mhtx.net> &...
7
votes
1answer
19k views

How do you index a text column in MySQL?

If I create a table with a text column CREATE TABLE foo ( bar text ); I can't index the bar column. MariaDB [test]> CREATE INDEX foo_bar_idx ON foo ( bar ); ERROR 1170 (42000): BLOB/TEXT column ...
7
votes
3answers
4k views

Occasionally simple queries are slow with InnoDB caused by slow query end [closed]

We are having serious issues with one of our productions servers. The setup in question is CentOS 7 64-bit server with 16 GB of ram and 250GB SSD (so the setup should be very fast). The LEMP stack is ...
7
votes
4answers
668 views

Why the query optimizer choose this bad execution plan?

We have a mariadb table (stories) with above 1TB of data, periodically running a query that fetches recently added rows for indexing somewhere else. innodb_version: 5.6.36-82.1 version : 10.1....
6
votes
2answers
749 views

Does the spec permit column-count mismatch on INSERT?

I'm just wondering if the spec permits excluding the column-list clause in an INSERT statement if you're inserting in fewer columns than the table provides. I see PostgreSQL does, and MySQL and SQL ...
6
votes
2answers
14k views

How do I disable MySQL on Linux from starting on boot or statup?

After adding MySQL to Ubuntu, it automatically starts up on server boot. I would like it to not start up with the system. How can I disable it and manually start it?
6
votes
2answers
2k views

After I kill my clients, MySQL continues to process queries for hours

Hi I have a MySQL db with 10 million rows with lots of triggers. A php client connects to mysql and INSERT's hundreds of rows per second. When the client has been running for hours, and I terminate ...
6
votes
1answer
19k views

MariaDB - cannot set max_connections and wait_timeout through my.cnf

I am struggling to set max_connections and wait_timeout parameters in /etc/my.cnf but MariaDB does not seem to read the parameters from the file (it reads some of the others, I haven't checked all). ...
6
votes
2answers
16k views

Add UNIQUE index fails with duplicate entry error, but no duplicates found

Given table: CREATE TABLE mytable ( field_a CHAR(15) NOT NULL DEFAULT '', field_b MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, field_c SMALLINT UNSIGNED NOT NULL DEFAULT 0, field_d SMALLINT ...
6
votes
1answer
12k 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: ...
6
votes
2answers
10k views

Does ALTER TABLE - ADD COLUMN lock the table?

I have a question about the ALTER TABLE ... ADD COLUMN DDL statement. On a Amazon RDS instance with MariaDB v10.2, I've noticed that INSERT statements complete and the rows are correctly inserted in ...
6
votes
2answers
5k views

SQL_SLAVE_SKIP_COUNTER = 1 fails, setting @@gtid_slave_pos used to skip a given GTID position

I recently broke replication and when I tried to get past the one incorrect transaction. I got the following. MariaDB [(none)]> STOP SLAVE; Query OK, 0 rows affected (0.05 sec) MariaDB [(none)]&...
6
votes
1answer
120 views

Can I do a conditional JOIN or conditional SELECT based on the data in a table?

I think I have a problem where EAV is the best choice. I am planning my tables as follows. motherboard motherboard_id | model_name int | varchar Example: motherboard_id | model_name ---...
6
votes
3answers
6k views

Behavior of transactions on multiple connections

I have a MariaDB database with InnoDB tables. I have two connections to the same database. One connection is using a transaction around a large chunk of work. The other connection is tasked with ...
6
votes
2answers
926 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....
6
votes
1answer
9k views

Is using timestamp as primary key good idea - mysql/mariadb?

We just argued that storing timestamp in sqlserver in microsoft does guarantee uniqueness, but im a little bit doubful about mysql/mariadb, because we are using parallel execution when inserting to ...
6
votes
2answers
26k views

How to enable queries logs in MariaDB

I'm running mysql Ver 15.1 Distrib 5.5.46-MariaDB in CentOS 6.7 server. I'm trying to get queries logged and I'm doing by redirecting log to files as shown below: #/etc/my.cnf.d/server.cnf # this is ...
6
votes
3answers
8k views

MySQL is too slow

I have a project with PHP/Laravel/MariaDB, the DB is getting bigger every day now some tables has 4kk+ records already Server is 4GB RAM + Ubuntu 14.04 + Apache (DigitalOcean) when I only start the ...
6
votes
3answers
2k views

Update old database with SQL dump

I had to recover a database with a some months old full backup. I have however in my possession a most recent but unfortunately incomplete SQL dump for this same database done with mysqldump. Is ...
6
votes
0answers
3k views

mysql to mariadb: unknown collation utf8mb4_0900_ai_ci

I have a mysql 8.0 that I exported using mysqldump. I am trying to import it onto a Mariadb 10.4 database with phpmyadmin, both are the most current versions. Each time I do it though, I get: Error: ...
5
votes
2answers
39k views

Cannot drop column : needed in a foreign key constraint

I have a table with two foreign key constraints as below: mysql> SHOW CREATE TABLE `user`; CREATE TABLE `user` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `region_id` int(11) unsigned ...
5
votes
4answers
2k 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, ...
5
votes
2answers
27k 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) ...

1
2 3 4 5
37