Questions tagged [mariadb-10.2]
MariaDB 10.2 was released as stable (GA) with version 10.2.6 on 23 May 2017. Please also tag with mariadb for search purposes.
61
questions
0
votes
1
answer
126
views
Unable to translate Unicode character \\uDCC9 at index 5 to specified code page
I'm using MariaDB (and Adminer)
MySQL version: 5.5.5-10.10.2-MariaDB-1:10.10.2+maria~ubu2204 through PHP extension MySQLi
And this is the show create table Pages:
CREATE TABLE `Pages` (
`Id` ...
- 1,327
1
vote
0
answers
135
views
How can I prevent overload of Mariadb server?
I'm running MariaDB 10.2.27 on Ubuntu 16.04 (10 cores, 40 GB RAM).
We have a queued message handling system (using Symfony Messenger) that normally has 20 workers consuming messages from the queue. ...
- 101
0
votes
1
answer
1k
views
Mysql service down with [ERROR] InnoDB: Page log sequence number is in the future
I have a Centos 7.9 server with MariaDB version 10.2.43. All of a sudden on May 2 2022, I have started receiving mysql down alerts. On checking I could see below log information in logs.
Error 2:- ...
- 140
0
votes
4
answers
825
views
Practical way of finding last update/insert/delete of any table in a database?
OS: W10.
This question is specifically about what's possible in MariaDB, specially version "Ver 10.16 Distrib 10.2.11-MariaDB".
Edit
In light of the answer by Rick James I have now upgraded ...
- 111
0
votes
1
answer
576
views
Mysql service stops with [ERROR] InnoDB: CORRUPT LOG RECORD FOUND
In my Plesk Obsidian version 18.0.42 running on CentOS Linux release 7.9.2009 with MySQL version 10.2.43-MariaDB. All of a sudden database stopped working. Mysql service never comes up on start ...
- 140
1
vote
0
answers
160
views
mariaDB 10.2: sql mode has changed
The Upgrading from MariaDB 10.1 to MariaDB 10.2 states:
SQL_MODE has been changed; in particular, NOT NULL fields with no
default will no longer fall back to a dummy value for inserts which do
not ...
- 151
0
votes
0
answers
32
views
Unable to DROP or MODIFY a column because row size is too large
I've tried to ALTER the table to DROP the unused columns or to change the data type of some VARCHAR columns to TEXT, but I always get this error:
ERROR 1118 (42000) at line 1: Row size too large (>...
- 101
1
vote
1
answer
114
views
Inconsistent results between MySQL and MariaDB when using variables and order
I'm building an accounting table and I need to show the current balance on each transaction.
Everything is working fine in my local environment (Windows 10 + MySQL 5.7.19), but the same query gives me ...
- 113
1
vote
2
answers
558
views
Missing an inserted row
I am having a strange heisenbug when a set of queries in a transaction (in MariaDB 10.2.38) somehow results in missing rows afterward. I mean that there are rows inserted within the transaction, but ...
- 131
13
votes
4
answers
3k
views
In the same query INSERT, can two VALUES being NOW() return a different time?
I met a query similar to
INSERT INTO mytable (id, Created, Updated) VALUES (null, NOW(), NOW())
having column definitions
| Created | datetime |
| Updated | datetime |
Will in this case MySQL ...
- 441
0
votes
1
answer
180
views
Can large data deletion affect Innodb buffer pool performance? [duplicate]
At the beginning of this year we lunched a lot of massive data deletions (millions of rows per week) in our production database. Also, there is an automatic integration of new data (insert/updates) of ...
- 798
0
votes
2
answers
355
views
How to find unused columns in MariaDB tables
I have a lot of tables in my DB that have a number of columns which might not be getting used as they slowly lost their relevance with time, I need to find such columns and delete those from the ...
- 69
1
vote
0
answers
47
views
JOIN bug in MariaDB 10.2 [closed]
This is less a question than just a "beware." Thankfully MariaDB 10.2 is an old version, but it does some funky things when joining to nested derived tables. I updated my database from ...
- 123
0
votes
1
answer
221
views
Connection aborts is happening in MariaDB 10.2 but not in 10.1 even with log_warnings level set to 2
I am noticing different behavior in mariadb 10.1 & 10.2 reg.connection aborts.
I noticed connection getting aborted after 7 minutes(wait_timeout) of any query execution. There are few random ...
0
votes
1
answer
2k
views
How to use FIRST_VALUE window function in MySQL/MariaDB to get one row per group?
I'm pleased to see window functions landed in MariaDB 10.2. I thought they would be good for first-of-group problems, but I'm struggling to see how they're efficient. I have something like this:
...
- 417
1
vote
0
answers
393
views
Why is PDO::ATTR_EMULATE_PREPARES = false causing "MySQL server has gone away" errors?
I've been using PHP PDO with the default setting of ATTR_EMULATE_PREPARES = true and it was working fine.
However, I wanted to be able to select database values as integers instead of having them ...
- 101
1
vote
1
answer
101
views
SLAVE off when loading data into the MASTER from another database
I have an active MASTER/SLAVE replication (Mariadb 10.2) with a single database (see scenario 1)
I decided to add in IMPORT database to populate the MASTER database as described in scenario 2. ...
- 798
3
votes
2
answers
688
views
Dropping a CHECK in mariadb?
I have a schema migration plan to add json column to mariadb 10.2.
But I am required to have a reverse plan.
Here's the forward plan:
ALTER TABLE `mydb`.`table1`
ADD COLUMN jsonf JSON DEFAULT NULL;
...
- 131
0
votes
1
answer
21
views
Why is it not possible to "SELECT" Data as parameters for certain clauses like LIMIT?
EXAMPLE:
CREATE TABLE t (A INT);
INSERT INTO t VALUES(1),(2),(3),(4),(5);
This works:
SELECT A FROM t WHERE A=(SELECT 1)
This does not:
SELECT A FROM t LIMIT (SELECT 1);
This works:
SELECT A ...
0
votes
1
answer
117
views
MySQL Fast Database Data Validation Master/Slave
I'm moving a MySQL database from the US to Australia and have a requirement where downtime is kept to an absolute minimum.
The database is 400GB is size, replication is working without any issues. ...
1
vote
1
answer
748
views
Does a MySQL dump that contains triggers double data on import?
I have a quick and simple question to clarify. I have medium sized database (400 Gigs) running in MariaDB 10.2 with lots of tables and lots of triggers as well. I am planning to take a mysqldump and ...
- 187
-1
votes
1
answer
558
views
Mariadb encryption
I have a requirement to encrypt certain column of DB tables. Going through the links, have followed the below steps:
In /etc/my.cnf
[mysqld]
plugin-load-add = file_key_management
...
- 1
0
votes
0
answers
28
views
MariaDB Replication - Missing ^M Characters on Slave
I've got a simple single master->slave replication setup with MIXED binlog format for mariadb 10.2.22-1 on RHEL7.
I believe I'm having an issue where '^M' characters in a longtext column are not ...
0
votes
1
answer
3k
views
Mysql_upgrade fails, table is corrupted
I have a mariaDB with a table that is corrupted. It's useless data to me, so I've tried to drop the schema but it won't drop. Instead, it gives me this error when trying to drop it:
"Error Code: 1805....
- 21
3
votes
1
answer
6k
views
How to clear the InnoDB redo log before starting mysqld for crash recovery
System: Windows Server 2012 R2, MariaDB 10.2.8
We are currently trying to recover from a quasi-crash (it was a normal shutdown, but mysqld won't start up again)
and are having problems with the redo ...
- 133
0
votes
1
answer
298
views
Can XtraDB be enabled on MariaDB 10.2
Can we create a table in MariaDB 10.2 with XtraDB engine?
I have installed MariaDB 10.2 and I am trying to create a table with storage engine XtraDB and I am getting error
*mysql> CREATE TABLE T1(ID ...
0
votes
0
answers
151
views
Fulltext search in a CTE result - missing fulltext index
My question applies to MariaDB 10.2.7
The query
WITH prefiltered AS (
SELECT id, fulltext_indexed_column
FROM fulltext_table
WHERE id < 30
)
SELECT *
FROM prefiltered
WHERE MATCH(...
- 101
0
votes
1
answer
260
views
MariaDB 10.2.22 Galera Cluster Using Bad Indexes After Analyze
I'm using Drupal on a MariaDB Galera 10.2.22 3 node cluster and I'm having some problems with one query. I have a table (InnoDB) called field_data_field_department which has a bunch of indexes. If I ...
0
votes
0
answers
357
views
MariaDB 10.2 crash on innodb table corruption - trying to restore the database
I'm running MariaDB 10.2 on Windows and had some hard drive corruption on my server that messed up some innodb tables. Unfortunately this also affected backups, so I'm trying to dump the tables and ...
- 1
0
votes
1
answer
205
views
perform non locking select when updating another table
We need to perform the following (periodic) query in mariadb:
update A set status='INACTIVE' where status='ACTIVE' and a.b_id in
(select id from B where ... LOCK IN SHARE MODE);
Unfortunately it ...
- 101
1
vote
1
answer
582
views
Prepared statement too slow
have an issue with MariaDB 10.2.12 running in aws rds (single master, no slave)
when I run prepared statement using a variable substitution
PREPARE stmt1 FROM
"select ...quite complex select from ...
- 98
2
votes
2
answers
3k
views
Mysql: set variable in mysqldump
I recently added a global variable in mmy.cnf max_statement_time=120 ; to stop long running queries and to throw an exception. Its working fine. Then I am unable to run mysqldump for backing up all my ...
- 187
1
vote
1
answer
100
views
Join against two tables to get single combined result set
tl;dr I made a SQLFiddle here http://sqlfiddle.com/#!9/031e71/1 with a minimal, complete, reproducible test case (though very specific to the problem domain of orders and products).
I have a Zen Cart ...
- 113
-1
votes
1
answer
43
views
MySQL - Classic Date Functions
Version: 10.2.18-MariaDB
I'm attempting to find the first available room within December (already included in the sample table) that will be free for 3 consecutive days.
The expected output should ...
- 1
1
vote
1
answer
3k
views
mysql : ordering a table in the order of the primary key
I have a table with a primary key consisting of two integer columns.
I store ~200 millions lines in this table.
My program needs to get the full table ordered by the columns of the primary key in the ...
- 121
2
votes
1
answer
368
views
Finding temporary table name in MariaDB
As I reported some days ago to MariaDB bug tracker the following bug: https://jira.mariadb.org/browse/MDEV-17420, slave replicas with version 10.2.x are leaking temporary tables on MyISAM files.
Our ...
1
vote
0
answers
1k
views
MariaDB 10.3 slave fails to replicate from a MariaDB 10.2 master
We have a 10.2.18 master and 4 slaves with 10.2.18. Because of this bug that I've reported last week (https://jira.mariadb.org/browse/MDEV-17420) we tried to upgrade one of the replicas to 10.3.10 to ...
1
vote
0
answers
48
views
MySQL InnoDB stop locking when large sql is executed
We face a issue that the InnoDB table is locked, when a sql file that is executed is large than a certain file size.
The problem does not appear when the file size is much lower, for example 25MB. ...
- 111
0
votes
1
answer
2k
views
Add a New Column with ALIASES on 'date' by automation based on search results
I am trying to create a new column based on date search result.
I have written a code in the link SQL fiddle as I am looking for date automation on each column based on the date format. the date ...
- 139
5
votes
2
answers
2k
views
Why am I connecting securely to MySQL when I don't have client certificates?
I don't understand the MySQL documentation for encrypted connections. The server is configured with certificates, and unless I am misreading it, the documentation says that the client must have ...
- 153
6
votes
1
answer
2k
views
Allow only unique combination of values for a composite primary key
I created the following table called books:
CREATE TABLE IF NOT EXISTS `books` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` TEXT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=...
- 187
0
votes
2
answers
212
views
Which way should be selected when move from mysql to mariadb? [closed]
Now, the mysql version is 5.1.I plan to move to mariadb 10.2.Use mysqldump and source to update databases.
Based on MySQL Upgrade Strategies,
Upgrading to the latest release is recommended before ...
- 143
1
vote
1
answer
2k
views
table_open_cache increase issue - MariaDB 10.2
I'm trying to increase to 9000 but MariaDB sets it at 4895 automatically.
Here is my simple config
https://pastebin.com/raw/4eFiuFSv
MySQLTuner output:
Control warning line(s) into /var/lib/mysql/...
- 13
0
votes
1
answer
419
views
Bypass truncated data type error in `CREATE TABLE AS`
I am attempting to recreate the code from this excellent tutorial in MariaDB 10.2 to try and build nested sets from an existing adjacency list. (The goal being to ingest changes to the adjacency list ...
- 510
1
vote
1
answer
291
views
Setup replication between two Galera nodes
I've been using MariaDB 10.2 Galera with 3 nodes successfully on local servers. Now, I have a second 3-nodes Galera cluster on another location, joined by an Site-to-Site IPSec Tunnel.
I can't really ...
- 11
2
votes
1
answer
926
views
Partitioning increases CPU usage in mariadb
I'm using mariadb 10.2, and I had a table with a structure similar to the one below, currently with 15gb, where I insert a few thousand records per minute all the time, each batch for a specific ...
- 167
1
vote
1
answer
2k
views
MariaDB 10.2: Why does Count(*) work, but not Count(t.*)
I have a table containing transaction details which I am importing from several vendor-provided daily reports.. I want to be sure that I am not skipping any of the files (one file per day) as I do ...
- 113
2
votes
1
answer
594
views
GSSAPI on MariaDB
I'm using Maria 10.2.13 on Windows Server 2012 and I want to use GSSAPI authentication with it so that we can continue using windows auth.
I have followed this tutorial:
https://mariadb.com/kb/en/...
- 131
1
vote
1
answer
492
views
Can sst_state_transfer be set to rsync with innodb as storage engine in Galera cluster?
Galera cluster wsrep-sst-method docs states:
You can only use rsync when anode is starting. You cannot use it with
a running InnoDB storage engine.
I do have InnoDB storage engine configured in ...
- 173
1
vote
1
answer
1k
views
What are some practical use cases for memory tables?
Trying to learn mysql and having tons of other material to learn, I'm considering skipping memory tables, since they have many special characteristics to consider especially that they vanish, after ...
- 117