Skip to main content

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
149 votes
4 answers
248k 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
41 votes
5 answers
41k 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: ...
Frosty's user avatar
  • 411
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
30 votes
8 answers
49k 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 ...
Sam Ivichuk's user avatar
26 votes
3 answers
58k 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
  • 63.6k
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
24 votes
6 answers
111k 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
39k 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
105k 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
16 votes
3 answers
37k 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 (...
Question Overflow's user avatar
15 votes
2 answers
28k 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?
Evan Carroll's user avatar
  • 63.6k
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
14 votes
4 answers
127k 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
  • 437
14 votes
2 answers
90k 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 ...
Sithu's user avatar
  • 367
14 votes
4 answers
142k 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,774
14 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
  • 357
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
13 votes
2 answers
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 &...
mpen's user avatar
  • 427
12 votes
3 answers
34k 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 ...
Ninj's user avatar
  • 223
12 votes
1 answer
44k 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 ...
Evan Carroll's user avatar
  • 63.6k
12 votes
2 answers
13k 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 ...
Schmuddi's user avatar
  • 223
11 votes
2 answers
20k 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 ...
dbdemon's user avatar
  • 6,381
11 votes
4 answers
15k 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?...
dbdemon's user avatar
  • 6,381
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
10 votes
2 answers
30k 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 ...
dr_'s user avatar
  • 1,314
10 votes
2 answers
6k 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 ...
John's user avatar
  • 402
9 votes
3 answers
3k views

In MySQL/MariaDB, do Indexes' performance degrade as they become larger and larger?

I'm currently exploring the use of PARTITION, for a specific use case I have. I use InnoDB, file per table. MariaDB 10.8. I was reading Rick's PARTITION Maintenance in MySQL webpage. I'd like to ...
Nuno's user avatar
  • 829
9 votes
3 answers
25k views

How to update Column related to Row Number

Is there any easy way to update Column by Row number not a PK ex: UPDATE contact m SET ContactNumber = sub.rn + 500 FROM (SELECT Id, row_number() OVER (ORDER BY Id) AS rn FROM contact) sub ...
Mokh Akh's user avatar
  • 203
9 votes
1 answer
20k views

How to reset MariaDB into a "fresh install" state?

I had InnoDB corruption and managed to start the server in read only mode and perform a fresh backup using innodb_force_recovery=5. This way of starting the service puts the databases in read only ...
Sebastián Grignoli's user avatar
9 votes
1 answer
34k views

What exactly is INTERVAL 1 HOUR checking?

MariaDB 10.1.x Let's say I have a query like: select FOO from db.BLAH where STAMPFOO > NOW() - INTERVAL 1 HOUR If I run that query at 12:15 PM, will I only get records that have a date value ...
Mike B's user avatar
  • 617
9 votes
1 answer
8k 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(...
Max's user avatar
  • 349
8 votes
4 answers
6k views

MariaDb - How to ensure a (LEFT) JOIN only if a single right side record exists

I have two tables I need to join together. However, I only want to join records when there are unqiue matches rather than picking one of several for the join. Versioning: Using MariaDB version 10.3.34 ...
Martin's user avatar
  • 225
8 votes
1 answer
9k views

How do you tune innodb_read_io_threads?

The default value of innodb_read_io_threads and innodb_write_io_threads is 4. How do you check if your server load needs a higher number of threads? As I regularly check show engine innodb status \G, ...
Googlebot's user avatar
  • 4,531
8 votes
2 answers
382 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 ...
Tom's user avatar
  • 205
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
  • 63.6k
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
34k views

How to recover MariaDB Galera Cluster after full crash?

I crashed all my 3 nodes. After all nodes have been started I noticed that mariadb is dead. An I couldn't run it again. I am using CentOS 7 on all servers I tried to start first node and then others ...
Oleksandr's user avatar
  • 393
8 votes
1 answer
11k 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 ...
jcho360's user avatar
  • 1,999
8 votes
1 answer
2k views

There is no index in the referenced table where the referenced columns appear as the first column

I have two EXISTING mysql/mariadb tables such as: CREATE TABLE `ice_product` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `long_description` text DEFAULT NULL, `long_summary_descr` text DEFAULT ...
lrkwz's user avatar
  • 191
8 votes
2 answers
17k views

Change default collation for character set utf8mb4 to utf8mb4_unicode_ci

When creating a database without specifying a character set or collation the servers defaults are used (as expected). MariaDB [(none)]> SHOW VARIABLES LIKE '%_server' ; +----------------------+----...
Peter van der Wal's user avatar
8 votes
1 answer
11k 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 ...
leafonsword's user avatar
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
3 answers
8k 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 ...
Matt James's user avatar
8 votes
1 answer
19k 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 ...
david's user avatar
  • 183
8 votes
3 answers
5k 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 ...
travisbotello's user avatar
8 votes
3 answers
17k 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 ...
user avatar
7 votes
3 answers
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 ...
Bucky's user avatar
  • 173
7 votes
3 answers
13k 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 <[email protected]> &...
user119791's user avatar
7 votes
2 answers
24k 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 ...
Roman K's user avatar
  • 231

1
2 3 4 5
55