MariaDB is an open source RDBMS that is designed to be a "drop-in" replacement for MySQL.
0
votes
0answers
5 views
Copying MySQL MariaDB databases MyISAM vs Aria
One of the main advantages that MyISAM continues to have over InnoDB is the ability to copy databases directly between servers using just tar files of the database directories.
InnoDB has ...
2
votes
1answer
36 views
what's practical example / use case SELECT SLEEP() for DBA
I don't know how to use SELECT SLEEP(). It looks like a dirty hack.
I found an interesting question How and when to use SLEEP() correctly in MySQL?. The answer is developer oriented:
If you don't ...
1
vote
1answer
29 views
I'm seeing inconsisntecies in table data after exporting from one server and importing to another, why is this?
I'm quite new to the process of importing/exporting databases.
I'm using Maria DB, and HeidiSQL to run queries, etc.
I exported an entire database from our dev server, and loaded it into our live ...
1
vote
0answers
10 views
galera cluster in simple failover mode
Can mariadb-galera cluster be configured without data replication but simply in failover mode? I envision such configuration as one active master server and one backup server, both using the same ...
1
vote
0answers
14 views
1 Node crashed of 3 node mariadb galera cluster
Heading
I have three nodes Galera cluster MariaDB, a node in June 4, 2016 suddenly crash, the following is log error
Red Hat Enterprise Linux Server release 6.5
Server version: ...
2
votes
1answer
29 views
guessing unused junk space in table. (innodb mysql/mariadb )
We have in every site (a dozen) one big database with lots of tables and record, using innodb as default and with innodb_file_per_table. from time to time we have space issue on some site and I am ...
2
votes
2answers
36 views
2 fast SQL queries get slow when bunched as subquery on a very large table
I'm trying to use a subquery to speed up a query on a very large table right now 30 million row it most likely going to end up close to 1 billion rows.
My table lnmntr is as follow
Lnmntr_ndx: ...
1
vote
1answer
54 views
Querying dates with different timezones at db and application level
There is a created_at column of datetime type in the order table. In my application I'm storing dates to the db as datetime in UTC. When I bring them back from the db I'm presenting/formating them to ...
1
vote
1answer
23 views
Contradictory reports from MySQL that a binlog file exists
Our replication recently stopped working. I submitted a question but was unsuccessful in fixing the problem (Error 1236 - "Could not find first log file name in binary log index file"). ...
1
vote
0answers
15 views
mysql/mariadb - prepared statement to create schema for users
I have the following scenario:
mariaDB (mysql Ver 15.1 Distrib 5.5.49-MariaDB to be exact - I could bug the sysadmins to update if necessary)
I want users to be able to create their own, distinct ...
1
vote
0answers
55 views
LEAST with 'if' condition brings timestamp instead of date
I was testing some new code in my server 1 and everything was just fine. When I sent my code to my server2 [Ver 15.1 Distrib 10.0.22-MariaDB, for Linux (x86_64)] , which is almost the same as server1 ...
0
votes
0answers
9 views
WSREP: Requesting state transfer failed: -11(Resource temporarily unavailable)
We have a 3 node Galera cluster. Only one node has "valid" data (gal-1).
The other nodes need to sync from this node.
I delete datadir and start MariaDB.
160527 14:28:20 [Note] WSREP: discarding ...
1
vote
1answer
24 views
MySQL trigger to update a key if record exists
I'm trying to build a trigger in MariaDB to follow this logic:
1. If the primary key val exists, increment the counter column
2. Else, insert the primary key into the table
Here is my prosed ...
0
votes
0answers
22 views
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'
I try to use this on my vps with mariadb:
# Optimized MySQL configuration for cPanel servers by Fotis Evangelou - Updated Feb 2016
#
# The settings provided below are a starting point for a 4GB-8GB ...
0
votes
3answers
65 views
Combine WHERE NOT EXISTS with ON DUPLICATE KEY UPDATE
I have 2 tables params and params_exceptions where params is supposed to be updated frequently by an external script.
INSERT INTO params (extension, type, subtype, paramvalue)
VALUES ('12345', ...
1
vote
0answers
22 views
'mysqlcheck' hangs over a database
Sometime ago I noticed my WordPress sites wont load. I found the problem with MySQL.
The server I am running on is a VPS server with:
Debian 8.1
MariaDB - Ver 15.1 Distrib 10.1.14-MariaDB
2048 MB ...
0
votes
0answers
48 views
how to optimize individual insert-if-not-exist on large table with unique varchar
I am in the process of building a bunch of tables and pivot tables from a single large table containing 1.4m rows (from the OMDB project's monthly dump)
The dump contains rows that include things ...
1
vote
1answer
68 views
howto read/interpret “show engine innodb mutex” output?
We use a 3 node Galera cluster with version 5.5.41-MariaDB-wsrep
MariaDB [(none)]> show engine innodb mutex;
+--------+---------------------------------+------------------+
| Type | Name ...
1
vote
2answers
91 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 ...
1
vote
1answer
45 views
Building MySQL InnoDB clustered indexes
Assuming that you have a table like
CREATE TABLE `t1` (
`search_id` int(11) NOT NULL,
`provider_id` bigint(20) unsigned NOT NULL,
`status` text NOT NULL,
`imported_at` int(11) NOT ...
0
votes
0answers
14 views
What is the 'auth_string' in 'GRANT' 'ON' SQL command in MySQL/MariaDB?
In the typical command to grant privileges:
GRANT ALL PRIVILEGES ON databasename.* TO "wordpressusername"@"hostname" IDENTIFIED BY "password";
What is the "password" part? The documentation says ...
-1
votes
0answers
452 views
How to check whether index splits in clustered index are causing freezes
Reading http://dba.stackexchange.com/a/18699 and http://www.sqlskills.com/blogs/kimberly/ever-increasing-clustering-key-the-clustered-index-debate-again/, I believe my database may be experiencing ...
1
vote
1answer
42 views
varchar datatype accepting integer values
below is the test table created. As data type is set to varchar it shouldn’t accept int values.
desc c\G
*************************** 1. row ***************************
Field: t
Type: int(6)
...
0
votes
1answer
48 views
Unable to change innodb_log_file_size in MariaDB Galera Cluster 10.0
I have a MariaDB Galera cluster (v. 10.0.19) with 3 nodes. I want to increase the innodb_log_file_size (i.e. the size of the InnoDB redo log files) on one of the nodes to match what it is on the other ...
1
vote
1answer
41 views
Display last date grouped by warehouse and product
I have a table called daily_stock with the following fields:
+-----------+----------+------------+----------+
| warehouse | product | date | stock |
...
1
vote
0answers
15 views
MariaDB strange user privileges issue
Server version: 5.5.47-MariaDB-log MariaDB Server / CentOS Linux 7 x86_64
I have two users: user@% and user@localhost with the same password but apparently different privileges (some privileges the ...
0
votes
1answer
56 views
Cant initialize MySQL. “mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended”
I am doing a fresh install of MySQL on Fedora 22.
After I run mysql_install_db I try to start the daemon using mysqld_safe but it does not work. The system says:
mysqld_safe Logging to ...
0
votes
0answers
39 views
Table data is not getting dumped
This is just for test purpose. I'm taking backup (INNODB DB) with --single-transaction and from another terminal I'm running alter table on one of the table of same DB on which dump command is ...
0
votes
0answers
64 views
MySql Error : Database page corruption on disk
Recently Ive setup a xampp in my store location.
on Windows 7 32 bit, 2GB RAM - Pentium PC. (XAMPP)
xampp-win32-5.5.19-0-VC11
PHP application is Point of Sale.
With latest mysql server and PHP 5.5. ...
0
votes
1answer
70 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).
...
0
votes
0answers
23 views
Completely reset innodb logs and files
So long story short, we've been dealing with a corrupt innodb installation on a MariaDB 10.0 server after a power outage, specifically a log in the future error.
Rather than try and fake the log ...
0
votes
0answers
31 views
Create a not unique FK in MySQL Workbench
I'm trying to model a database using MySQL Workbench (version 6.3.6, 64 bits, using linux mint). After syncing my model with a MariaDB (running in localhost, version 10.0.25), I discovered that the ...
2
votes
2answers
48 views
Improve query “JOIN … WHERE NOT EXISTS (SELECT 1 FROM … WHERE … )”
I'm trying to optimize a query because the plan generates a full table scan for a subquery.
Data
The (simplified) table structure:
CREATE TABLE RECORD (
ID INTEGER PRIMARY KEY AUTO_INCREMENT
);
...
0
votes
0answers
23 views
pt-duplicate-key-checker - DBD::mysql::db selectall_arrayref failed: MySQL server has gone away
I run pt-duplicate-key-checker a few months ago successfully. Now I receive on the same database (Galera, RHEL7) those errors DBD::mysql::db do failed: MySQL server has gone away
On CLI I can do use ...
0
votes
0answers
30 views
MariaDB shows crashed and PID/Program Name shows -
I have setup alpine linux 3.3 on ubuntu 16.04 as a container using lxc/lxd and setting up mariadb. I am done with the installation and all.
When I check for the status of mariadb, it shows crashed ...
1
vote
1answer
49 views
import TSV file into MySQL table with SET datatype column
Is it possible to use LOAD DATA INFILE to load data into a MySQL/MariaDB table, where one column uses the SET datatype? My columns are separated by tab, my set members by comma.
If so, how do I ...
4
votes
1answer
97 views
How can I optimize a query with multiple derived tables containing `IN` and `GROUP BY`?
I'm collecting nmap data every five minutes and storing it in a database. Information about each scan (e.g. start and end time) is stored in the scans table:
CREATE TABLE `scans` (
`scan_id` ...
0
votes
0answers
35 views
Silent install of Maria DB on Ubuntu 14.04 is not working
I am using the following commands for the silent installation of Maria DB on ubuntu 14.04.
export DEBIAN_FRONTEND=noninteractive
sudo debconf-set-selections <<< 'mariadb-server-10.0 ...
1
vote
1answer
61 views
Is the SQL syntax for InnoDB gauranteed to be the same between MySQL, MariaDB and Percona?
I develop an application that uses MySQL and explicitly uses InnoDB table engine. In a few cases we have clients using this application with MariaDB or Percona.
I understand that MariaDB and Percona ...
1
vote
1answer
69 views
When to disable MySQL Query Cache
I have a high volume MariaDB 10 server thousands of databases of with over a million tables. Currently we are only hitting around 20% query cache hits. We are already employing Memcache and other ...
1
vote
0answers
28 views
problem with column oriented dbms features
I am currently looking for a free(or at least low cost) column oriented DBMS with really good compression abilities (at least 10:1 ratio) and fast response time. Having evaluated multiple open source ...
1
vote
1answer
55 views
Run multiple MySQL instances in Windows
Which is a better way to install multiple instances of MySQL / MariaDB / etc on a single Windows box?
Manually setting different ports?
Docker?
Virtual Box?
Something else? (Sandbox, mysql_multi, ...
1
vote
1answer
43 views
Migrate from innodb_file_per_table to off in MySQL ALTER TABLE removing ibd files
Trying to migrate individual tablespaces back into the shared ibdata tablespace.
MariaDB version 10.0.16
SET innodb_file_per_table to off in MySQL, restated mysql and new tables are in fact going ...
2
votes
1answer
44 views
Possible causes that a MySQL/MariaDB query is slow under some table conditions
I am having a strange issue with a slow query under certain conditions:
I have made a few tests and was able to isolate the issue to one single table called products_description (all the tables are ...
1
vote
1answer
74 views
Why is MySQL MariaDB GREATEST(timestamp,NOW()) padding appending results with zeros?
Everything works as expected on my development machine, but when deploying to quality assurance environment, tests are failing because MariaDB is padding the result set with extra precision zeros ...
1
vote
1answer
33 views
How to optimize MariaDB database when it is serving queries
We would like to run an optimization on our MariaDB database while still allowing it to serve queries.
I see that for MySQL this is possible since 5.7.4 (from this link)
For InnoDB tables prior ...
3
votes
1answer
37 views
Performance schema: 'stage/sql/Sending data'
Background
I am using MariaDB 10.0 but MySQL 5.6 answers are also of interest.
Two machines cloned from the same image, same MariaDB configuration as far as I could tell, same dataset. One machine ...
1
vote
2answers
35 views
InnoDB file per tablespace
I am using following syntax to create an InnoDB table:
create table InnoDB_Test(a INT)
DATA DIRECTORY='/var/log/storage/MySQL'
INDEX DIRECTORY='/var/log/storage/MySQL' engine=InnoDB;`
I want to ...
0
votes
1answer
15 views
Does MariaDB require aliases for subquery table locks?
MySQL suffers from a bug (which they claim is not a bug) that prevents a table lock from working on a sub-query unless you create a table alias for each mention of the table name.
See: ...
0
votes
1answer
16 views
How to Ensure of Data MariaDB Galera Cluster's Safety?
MariaDB will send a copy of SST to every server which request for it even without checking whether it is in the gcomm:// list. So I can easily get other people's data by simply install a fresh copy of ...
