Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

0
votes
0answers
6 views

How to optimize SQL query which involves GROUP_CONCAT function with ORDER BY

I wrote the following SQL query to group results and then sort results in each group by date: SELECT origin, provider, SUBSTRING_INDEX(GROUP_CONCAT(price ORDER BY departure_date ...
-2
votes
0answers
28 views

How would you do this simple query in the most optimized way MYSQL and also MARIADB

pkid clientname location 1 Jones NY 2 Jones NJ 3 Jones TX 4 Jones FL 5 Jones IA 6 Jones NC 7 Jones SC 8 Jones VA 9 Smith FL 10 Smith CO ...
2
votes
2answers
13 views

First order by importance then group by section

In this table scheme http://sqlfiddle.com/#!9/101a9c/9/0 id section rev importance 2 1 b 6 1 1 a 5 3 2 c 9 5 3 e 6 4 ...
0
votes
0answers
8 views

MariaDB crash after / while table check on small VPS

I'm facing an issue with my MariaDB server on a Debian Linux inside a small LXC container. (A bigger one with dual core and more memory has got the same issue, another identical LXC container is ...
1
vote
1answer
11 views

Why would a file generated by mysqldump be substantially larger than the data directory

I have a MariaDB server - 5.5.60 I have a database xyz that has both MyISAM and InnoDB tables The majority of the tables are MyISAM but in this database in particular, the data is mainly in InnoDB ...
0
votes
0answers
23 views

Unable to call PASSWORD() with VARCHAR() parameter inside a procedure

DELIMITER // CREATE PROCEDURE custUpdateInfo(IN customer_id varchar(20), IN passwordd VARCHAR(20), IN named varchar(20), IN addressd VARCHAR(60), IN phone_number DECIMAL(10) UNSIGNED, IN email_id ...
-1
votes
0answers
48 views

Mariadb docker container SQLSTATE[HY000] [2002] No such file or directory error [on hold]

I have developed an web application that consists of a app, a website and a mariadb database. I put them together in a docker-compose file and tested it on my local computer and it works fine. I tried ...
0
votes
0answers
18 views

How do I debug/set up MariaDB SSL connection?

[ I originally posted this on ServerFault but then was pointed at this community ] Since moving from MySQL to a newer MariaDB installation on Debian 9, I a struggling with SSL connections. I have ...
0
votes
0answers
30 views

init_connect not executing

From this question I was able to make my init_connect = "CALL mydb.Warnings()", but a user login in does not triggers the SP. the SP is like: CREATE DEFINER=`root`@`localhost` PROCEDURE `Warnings`...
0
votes
1answer
16 views

How to use 'mysql -uroot -pXXXX' on Debain 9.8?

My apologies for this lame question. Debian 9.8 changed some things and mysql is broke but I can't seem to fix it. I installed MySQL on Debian 9.8, which now installs MariaDB 10.1. After install I ...
0
votes
1answer
23 views

Access denied for user 'root'@'localhost' What proces is causing this?

I got this connections constantly in logs, how to trace what app is trying to connect? root@localhost is allowed to connect and working for some apps. I think there is some app without changed ...
0
votes
1answer
14 views

Mariadb times out on startup after system reboot

This is continued from: https://stackoverflow.com/q/55071021/8644833 Mariadb shuts down after running for a minute or two. If I remove Mariadb and reinstall (keeping my databases), everything works ...
0
votes
1answer
24 views

Mysql replication rebuild dump database lock

I need to rebuild master-slave replication of whole instance of mariadb on innodb. What I often see is that people do read lock then dump master then unlock, like: FLUSH TABLES WITH READ LOCK; SHOW ...
0
votes
0answers
23 views

Encrypting a Database (mariadb/mysql) With Veracrypt

as explained in this question I created a mysql (mariadb) database in a separate volume. In my case it is a veracrypt container. Even after changing ownership of the mounted folder to mysql, and ...
1
vote
1answer
29 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 (...
1
vote
1answer
40 views

Show a Warning on logon in MariaDB

I pretend to show a personalized message at login in MariaDB ver. 10.1.37 , in accordance with the data in one table. My idea was to use a stored procedure as shown in fromdual.com but i can't find ...
1
vote
2answers
41 views

User rank based on cumulative rows

UPDATE: Full table schema and data: https://www.db-fiddle.com/f/jT3Mfwd1BJ8GrsBxxPDC8M/1 Table structure (some fields are hidden): +---------+---------+------------+-------------+-------+-------+ | ...
1
vote
1answer
26 views

How to fix the sys.host_summary table in MariaDB 10.3.13

using mysqldump while doing a backup gives the following error: mysqldump: Got error: 1356: "View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view ...
0
votes
1answer
26 views

Upgrading from mysql 5.7 on FreeBSD to mariadb or MySQL8

I am looking into upgrading some major components of a webserver (php, apache, mysql). I've done the update to 5.7 from 5.6. I've updated to php7.3. What I wonder now is: I see that a mysql ...
0
votes
0answers
24 views

Determine cause of “MySQL server has gone away”

I've got a long running process on a headless server. It is a C program that monitors a modem for Caller Id and hangs up on telemarketers. On startup the program opens a connection to a MariaDB 10.3 ...
1
vote
2answers
35 views

Expand compact data

For the sake of simplicity, I decided to store a value only when it changes. +------------+--------+-------+ | date | entity | value | +------------+--------+-------+ | 2019-01-01 | 1 | ...
0
votes
1answer
19 views

How to use Unicode chars on PHPMyAdmin?

On a WordPress site I want to replace " !" and " ?" by " !" and " ?". The challenge is that the replacement string not uses a common space char but a thin space char (U+2009). PHPMyAdmin is used for ...
0
votes
2answers
60 views

mariadb 10.3.13 table_open_cache problems

Just upgraded mysql 5.6 to mariadb 10.3.13 - now when the server hits open_tables = 2000, my php queries stop working - if I do a flush tables it starts working correctly again. This never happened ...
0
votes
1answer
34 views

Why does MariaDB choose different index use for the same data?

I have this table (this is the output from show create table on both servers, ie. using diff to compare the output shows only the auto increment value, because one has moved on slightly from the ...
0
votes
1answer
21 views

Cannot import table due to zombie constraint

I tried to refresh my copy of the production database by emptying and importing a SQL file. I've done it hundreds of times, only this time the import failed. I tried dropping & recreating the ...
0
votes
2answers
54 views

Mysqldump multiple tables with where clauses in single transaction

Suppose, we have 2 tables: t1,t2 in database db: Expected the below code to dump tables based on conditions specified in the where clauses: mysqldump --login-path=login_path \ --single-...
-1
votes
0answers
23 views

Sync MySQL (MaraDB) databases through github

Is there a way to be able to sync multiple database through text files on github? This is for an open source project I am considering and need a way to have local databases to sync on demand. I ...
1
vote
1answer
81 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 ...
0
votes
2answers
44 views

MySql | How to compute this [closed]

I have this table Grade Lec Lab 1.5 3 0 1.5 3 0 1.5 2 1 The formula will be like this 1.5 * 3 1.5 * 3 1.5 * (2+1) = 13.5 / ...
0
votes
2answers
45 views

MySql | How to compute every row in same table

I have this table Grade Lec Lab 1.50 3 0 1.50 3 0 1.50 2 1 The formula will be 1.50 * 3 1.50 * 3 1.50 * (2 + 1) = 13.5 The results will divide to total number ...
0
votes
1answer
26 views

csv engine and mysql start

my question is about speed when perform service mysql start. with many tables (some not often used). what engines consumes more or less start time ? example: innodb --> the more tables, the more ...
0
votes
1answer
18 views

Batch query a lot of MySQLdump files

I have a website for an event. I set up a cron job to back up the database every hour. The backup command is mysqldump -u"$DB_USER" -p"$DB_PASS" --database "$DB_NAME" >"${OUTPUT_DIR}/${DB_NAME}.$(...
0
votes
1answer
18 views

Does any MariaDB engine have the option to declaratively or configuratively abstract highly-redundant data?

I have an application which is architected in a "NoSQL" style around one fully denormalized "main table" which currently just holds a primary key and one JSON-valued column. For reasons which are ...
0
votes
1answer
23 views

Question about encrypting multiple MariaDB schemas on the same server

I have 1 server running 1 installation of MariaDB In that installation of MariaDB, I have a CustomerService database and WorkFlow database. Can I encrypt CustomerService with one key and WorkFlow ...
0
votes
0answers
12 views

MariaDB Galera journalctl

I've noticed something weird. I have 3 nodes galera cluster, installed using mariadb. I'm pretty sure that I did all three nodes installations exactly the same. All three nodes display their --...
2
votes
2answers
114 views

Unexpected RAM usage on MySQL

We have a server which hosts about 10 customers in a multi-tenant web application, each customer has its own database which is cloned from a template, and each database is a little less than 3000 ...
-1
votes
1answer
23 views

MariaDB - Epoch date comparisons

On MariaDB 10, I'm trying to work with dates, particularly whether the current date is within 7 days of a date stored in the database. A longtext column holds an Epoch datetime, which I convert into a ...
0
votes
0answers
18 views

Returning a set datatype from MariaDB function

Given below is the syntax for the MariaDB's CREATE FUNCTION: CREATE [OR REPLACE] [DEFINER = {user | CURRENT_USER | role | CURRENT_ROLE }] [AGGREGATE] FUNCTION [IF NOT EXISTS] func_name ([...
0
votes
1answer
26 views

MySQL workbench can't deal with MariaDB's new Invisible columns

Recently upgraded to MariaDB 10.3 which has a new feature whereby we can create an invisible column like so: ALTER TABLE MyTable ADD COLUMN `TestA` INT INVISIBLE; But once I create this column, ...
1
vote
1answer
50 views

MariaDB 10.3: InnoDB much slower than MyISAM

I'm using MariaDB 10.3.12 on Arch Linux and haven't modified any of the default settings under /etc/mysql. The following script takes 0.75 s to run on average using the default InnoDB engine: drop ...
1
vote
1answer
29 views

Unexpected results while using HAVING without GROUP BY

Given below is the snapshot of the SQL queries: MariaDB [university]> select * from department; +------------+----------+-----------+ | dept_name | building | budget | +------------+----------+...
1
vote
1answer
26 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
0answers
16 views

Azure MariaDB log_bin_trust_function_creators

I'm testing AzureDB for MariaDB and for my DB to work properly I must change log_bin_trust_function_creators variabl. However, it isn't available in Azure Portal Server Parameters. I've checked and ...
0
votes
1answer
28 views

Safe online dumping of an InnoDB database in the presence of DDL changes

When dumping an InnoDB database using mysqldump, it is often recommended to use the --single-transaction and --skip-table-locks options. This allows for minimal blocking of other concurrent reads + ...
0
votes
0answers
30 views

SELECT … INTO OUTFILE “Read-only file system” error

I'm using MariaDB 10.3.12 on CentOS 7, using the MariaDB-server RPM package from MariaDB's yum repository. I'm trying to do a simple SELECT ... INTO OUTFILE into a different directory than /var/lib/...
0
votes
2answers
41 views

What kind of SSD to speed up querying a lot of data?

For research purposes I am using a big MariaDB database (~500 GB) with 20 Tables (engine: myISAM) and around 9 Billion rows. Due to missing free storage on my Laptop (16 GB RAM), I put the data on an ...
0
votes
1answer
16 views

10.3.9-MariaDB can not query equal operator with special character

I have a MariaDB database that has a filed TagName with value ~!@#$%^&*()_+|}{":?><./';[]\=-` I had set mode SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',NO_BACKSLASH_ESCAPES'); When I try to ...
0
votes
0answers
13 views

Is the result of ST_Union defined?

With MariaDB 10.1, I get a simplified LINESTRING when I provide two lines can be joined. SELECT ST_AsText(geom) FROM ( SELECT ST_Union( LineString(Point(0,0),Point(1,1)), LineString(Point(1,...
0
votes
1answer
16 views

Does MySQL or MariaDB support a LineString constructor that takes points?

It seems with MySQL and MariaDB I'm getting the following when I try to use the LineString constructor. In PostGIS I can do this with ST_MakeLine(pt1,pt2) > SELECT ST_MakeLine(Point(0,0), Point(0,...
1
vote
2answers
59 views

Overly eager explicit row locking causes deadlock

I'm running into deadlock problems on MariaDB using the INNODB engine. In my use case, I've got a number of threads working on a table. Each thread first reads a number of rows and then updates those....