Database Administrators

Questions tagged [mariadb]

Ask Question

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

1,948 questions
Newest
Active
Bountied
Unanswered
Filter by
Sorted by
Tagged with
1
vote
1answer
24 views

UPDATE SET N + 1 equal to a row (with iteration)

Question: How would you iterate over the previous result (referenced below) over for each of the values in column b3? MariaDB Fiddle (this includes prior fiddle information to solve the previous ...
3
votes
2answers
45 views

UPDATE SET N + 1 equal to a row

Problem: I need help implementing N+1 WHEN a2 = "Z" then check if the next row number (a1 + 1) has a2 = "Z" ...if this is the case THEN replace the (a1+1) row --> a2 = "Y&...
2
votes
1answer
22 views

Slow query when multiple users connected to MariaDB

I have a query that runs very fast on test environment (0.4 seg) but in production it is taking about 5-7 seconds. The database for test environment is very similar to production, the difference is ...
0
votes
0answers
8 views

“Error opening column file” in MariaDB Columnstore data cpimport

I'm trying to import a large file, around 2GB, into MariaDB Columnstore. Other files that are smaller worked fine, so I suspect it's a size issue. I create the table with engine Columnstore with no ...
0
votes
1answer
19 views

Finding rows where totals do not match

MariaDB MySQL Trying to do what seems like a basic query (join) on two tables. Receipts id date supplier total Parts id receipt_id partType subtotal Trying to get the row from the first table when ...
1
vote
0answers
17 views

Mariadb malloc configuration- tcmalloc vs tcmalloc_minimal

I am using mariadb 10.4. Initial sysbench test showing promising result with tcmalloc. But much better result(TPS & Latency) is obtained using tcmalloc-minimal which basically removes heap checker ...
0
votes
0answers
27 views

Mariadb Error 1808 Schema mismatch in IMPORT TABLESPACE

I am trying to restore a single database from a backup taken last night using the instructions in this MariaDB blog entry, but I am getting the following error on IMPORT TABLESPACE: Error in query (...
2
votes
1answer
94 views

Can not drop virtual column | ERROR 1054 (42S22): Unknown column in 'GENERATED ALWAYS'

I'm just running a command: optimize table some_table_name_here; But it results with: Table | Op | Msg_type | Msg_text db.some_table_name_here | optimize | note | Table does not support optimize, ...
0
votes
1answer
17 views

Recovering InnoDB database from backed up “data” folder in XAMP in Windows 10

I copied the data directory from mysql folder and uninstalled the XAMPP as the MariaDB was not starting. I installed XAMPP same version and backed up the data folder into mysql data directory. At ...
1
vote
1answer
31 views

Set crontab backup job for Mariadb Databases from 1 remote server to another [closed]

I'm connecting to a Ubuntu Server remotely using SSH, where are x number of MariaDB databases. Now I want to set a crontab job to automatically create backup for all of them to another server where ...
0
votes
2answers
16 views

MariaDB JSON functions

I am using MariaDB 10.5, I have a JSON field in a table which typical contains something like: [{"Filename":"C:/Users/simon/Documents/file.ext"},{"Soil":"Sand"},...
0
votes
1answer
29 views

Query taking much longer on production server than test server

We have a couple servers, one test server, and another production server. The table schemas, indexes, and data are identical on both. Here is the table schema: create table ...
-1
votes
1answer
20 views

Ruby on Rails cannot access production RDS on AWS. Part 2 [closed]

I can connect with Squirrel to my DB, which is created with rake db:create. When I run rake db:migrate I get the error: Caused by: Mysql2::Error: Can't create table `gmrdb`.`comments` (errno: 150 &...
1
vote
2answers
116 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 ...
0
votes
1answer
25 views

Enforcing same type for two columns

Is there any method of constraining column types between tables in MariaDB? When creating foreign keys column types have to match. I would like to make sure two or more columns always stay the same ...
0
votes
1answer
73 views

Queries freezes in MariaDB

I have a problem where queries freezes sporadically during the day. I made a script monitoring the processlist and have detected a pattern where a slow query is running (different slow queries) and ...
0
votes
0answers
18 views

Mariadb galera cluster on asymmetric IP addresses fails IST/SST

I have a 3-node WAN cluster. The nodes are connected to each-other by VPN without a central server so as to avoid a single point of failure. Instead, each node is an openvpn server with another as ...
0
votes
0answers
13 views

How to configure non-mysql user for datadir in MySql/MariaDB?

Using it for testing purposes and the NFS datadir will be accessed by two or more non-concurrent independent mariadb-servers. So far, mariadb only permits if its this way: chown -R mysql:mysql /data/...
0
votes
0answers
20 views

MySQL scheduled events not working on newly-added data

I can get a scheduled event to perform calculations on a current table. But it won't perform calculations on any new rows that are added to the table. How can I get the event to use the current ...
0
votes
1answer
22 views

Rank calculation using variables working only sometimes

I have the following code in MariaDB that was working perfectly well yesterday. It calculates a rank and orders by the data by another column: SET @c = (SELECT COUNT(*) FROM myTable); SET @rownum = 0; ...
1
vote
1answer
58 views

Manage billions lines of sensors data in mariaDB [closed]

I'm the new "IT guy" in a small company that managed ecological footprint reduction in industries, for contracts reasons they need to keep every data changes of each sensors(some changed ...
0
votes
1answer
18 views

How do I tell if a long running update won't lock the database?

There is a update query I need to run on a table in production. The update would touch millions of rows and I would take a couple of minutes. The database is Mysql. I was wondering if the table would ...
0
votes
1answer
21 views

Is this atomic set and get in MariaDB / MySQL: `UPDATE t SET col = @my_var := col + 1 WHERE id = 123`?

I want to atomically update a single row and get its value. I discovered today that MariaDB / MySQL has this additional way of setting user variables using the assignment operator :=, described e.g. ...
0
votes
0answers
10 views

Checksum difference between table in MariaDb 10.4 and Mysql 8

We have a script that transfers databases between clusters. To make sure the data is still valid, we run a checksum on some of the tables. If those are ok, we mark transfer succesful. The problem is ...
0
votes
0answers
63 views

Mariadb Galera [ERROR] mysqld got signal 11 ;

I have a galera mariadb cluster on 3 centos 8 linux servers. Each server crashes several times a week. I have 16 Go RAM on one server and 60 Go ram on other servers. Each wsrep recovery takes about 12 ...
0
votes
2answers
68 views

How to improve performance to 12million rows table?

I have performance issues on a query in a big table (12 million records) based on Geonames, that's a read-only database so NO DELETE, UPDATE or INSERT only SELECT. There are queries I make every now ...
3
votes
2answers
92 views

Correct composite indexing indexing order

I have MySQL (MariaDB) database. I have a table 'sensors' ,which collects data from IoT devices. Each device may have 4-6 parameters that it records, like temperature, humidity, air quality, etc. Each ...
1
vote
1answer
55 views

MariaDB freezes with no visible reason

MariaDB server experiences some sort of freezes happening sporadically with no visible reason or connection to outside factors (load, etc). After a thorough investigation the common picture for those ...
1
vote
2answers
24 views

Updating log table in an effective manner

There is a table with compound key: t_count + pageType VARCHAR(32) ascii_bin + pageId VARCHAR(255) utf8_bin + count INT PRIMARY(pageType, pageId) Its purpose is to track how many times each page ...
1
vote
1answer
33 views

How do I stop a master from being a master in mariadb

I have a database which used to be setup as a master in a topology. Now I want to remove it as master, and make it a replica. How do I do this? Is there something similar to how to stop slave but for ...
1
vote
1answer
24 views

MariaDB Galera Arbitrator failing to sync with SSL

So the scenario is this. I have already setup a 4 node MariaDB Galera cluster (10.5). I'm also adding on top a 5th Galera Arbitrator and everything syncs and connects just fine. However when I enable ...
2
votes
1answer
31 views

MaridDB 10.3.29 prevents me from deleting a foreign key

This is a minimal example that triggers the error CREATE TABLE A ( id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY ); CREATE TABLE B ( what tinyint NOT NULL, a bigint NOT NULL, CONSTRAINT x ...
0
votes
2answers
49 views

Subquery into join issue

I'm trying hard as I can to transform my query to get rid of subqueries. I know its better to not use subqueries, but my first reason to change this query is because my ORM (Doctrine) isn't able to ...
0
votes
1answer
29 views

MariaDB 10.5 How to Prevent High Memory Usage on Windows

I am running MariaDB 10.5 on I am having trouble with MariaDB using too much memory on my Windows 10 machine. I have 16GB of RAM, and MariaDB ends up using around 8GB. SHOW VARIABLES; https://pastebin....
0
votes
1answer
20 views

Do unused columns in MySQL/MariaDB notably affect performance?

In the WordPress MySQL/MariaDB database there is a table with some columns that in my own uses of WordPress are never used. The exact columns I am talking about are: comment_status varchar(20) ...
0
votes
1answer
23 views

Select data from a table to insert/update another similar table

I have two tables (in two databases): create table catalog_product_entity_text ( value_id int auto_increment comment 'Value ID' primary key, attribute_id smallint unsigned default 0 ...
0
votes
1answer
58 views

MariaDB remote access ERROR 1045 (28000): Access denied

MariaDB 10.3.27. Debian 10 Buster. I've followed the instructions to grant remote access to a user to my remote MariaDB server: Edited /etc/mysql/my.cnf: [mysqld] #skip-networking #bind-address = &...
2
votes
1answer
33 views

Unable to enter characters in MariaDB

I have just switched from MySQL to MariaDB and am running into a very silly problem: I cannot enter any extended characters in the database (for example, ö ä or å). The system is utf8, and I tried ...
0
votes
1answer
16 views

Merging one SQL database into another

Currently in my CI I'm completely wiping production db with development db. That was fine for testing but I'm coming to a stage where I need to start merging instead of completely wiping. Right now ...
1
vote
2answers
40 views

Get all rows where specific column value occurs more than once (filter out single occurences)

I am trying to filter all sessionIds that occur once out of an existing result set. This query is being used in a web application and runs on a big dataset (~ 35 million rows), so I want to prevent ...
1
vote
0answers
63 views

Same query taking different amounts of time to execute

Table Details SHOW CREATE TABLE foldertable CREATE TABLE `foldertable` ( `serverToken` bigint(1) NOT NULL, `folderName` varchar(255) NOT NULL, `folderid` varchar(255) NOT NULL, `RootFolderPath` ...
1
vote
2answers
39 views

LEFT JOIN not joining the second table

I'm having a problem with my SQL command running on MariaDB. I have the following tables: reservations: id child_id time_from time_to user_id 1 1 2021-05-17 13:20:00 2021-05-17 14:20:00 NULL 2 2 ...
2
votes
2answers
48 views

Best order of execution of mysql maintenance tools

After a change of data type or index for exemple. Given some queries to do maintenance tasks. What's the best order of doing these tasks? ANALYSE TABLE foo; CHECK TABLE foo; OPTIMIZE TABLE foo; REPAIR ...
1
vote
1answer
30 views

MySQL (MariaDB) bound to 0.0.0.0, but doesn't appear to be listening to all IPs?

My MySQL (actually MariaDB) server accepts connections at port 43210 without problems on the public IP address of, we'll say, 123.123.123.123 on eth0. This is on an Ubuntu 18.04 server that also has ...
0
votes
2answers
37 views

MariaDB event to automatically move data older than a specific time to another table without temporary table?

I am trying to setup an event, that automatically moves data older than a specified amount of time from a heavily used table to some "archive" table to improve performance. So far I've only ...
1
vote
1answer
41 views

slow disk with InnoDB page compression

I have a write-intensive MariaDB with both NVMe SSD and HDD disks. I recently enabled page compression (innodb_compression_default=ON). I encountered two problems: The database gets slow after a ...
2
votes
0answers
30 views

MariaDB suddenly requiring database name prefixed to table name

I'm new to MariaDB (and databases in general). I'm using MariaDB 10.5.9 on Windows 10 from terminal. I'm not sure how/why, but for some reason MariaDB is requiring my SQL statements using table names ...
0
votes
1answer
30 views

Rebuilding a single corrupt table with dual master replication

I have a replication setup with two MariaDB 10.2 instances set up in a dual-master configuration. On host A, a table has been corrupted somehow; whenever I try to read from it (including CHECK TABLE ...
0
votes
0answers
34 views

HeidiSQL, how to export user definition

I am using MariaDB version 10.5.9.0 on Windows version 10.0.19041.928 with HeidiSQL version 11.0.0.5919 I have exported the database structure which includes the tables, stored procedures and data. I ...
0
votes
3answers
50 views

Find Max() updated time for a group of users

Database is MariaDB 10.3.25 I have 2 columns that are relevant: UserID and LastUpdate UserID consists of user@domain LastUpdate is a date field. Here is my issue - I have the current query: select a....

1
2 3 4 5
…
39

Your privacy

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.