mariadb interview questions
Top mariadb frequently asked interview questions
If I replace a MySQL 5.1 server with a MariaDB Server (Maria & XtraDB storages) instead of MySQL (MyISAM & InnoDB), will most of MySQL client software (incl. applications made with PHP 5.2 and Java SE 1.6) ...
- just remain working without any changes (with minor regressions maybe)?
- Or will I have to replace/reconfigure client drivers (like use another JDBC driver class and connection string)?
- Or will I have even to change application code?
Source: (StackOverflow)
Generic Build instructions:
https://mariadb.com/kb/en/mariadb/generic-build-instructions/
$ cmake --version
cmake version 3.4.0-rc3
CMake suite maintained and supported by Kitware (kitware.com/cmake).
$ cmake . -DBUILD_CONFIG=mysql_release
$ make --version
GNU Make 3.81
Copyright (C) 2006 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.
There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A
PARTICULAR PURPOSE.
This program built for i386-apple-darwin11.3.0
$ make
...
...
Users/7stud/Downloads/mariadb-10.1.8/storage/mroonga/vendor/groonga/lib/dat.cpp:191:3: error:
cannot use 'try' with exceptions disabled
try {
^
/Users/7stud/Downloads/mariadb-10.1.8/storage/mroonga/vendor/groonga/lib/dat.cpp:222:3: error:
cannot use 'try' with exceptions disabled
try {
^
/Users/7stud/Downloads/mariadb-10.1.8/storage/mroonga/vendor/groonga/lib/dat.cpp:423:3: error:
cannot use 'try' with exceptions disabled
try {
^
/Users/7stud/Downloads/mariadb-10.1.8/storage/mroonga/vendor/groonga/lib/dat.cpp:452:5: error:
cannot use 'try' with exceptions disabled
try {
^
/Users/7stud/Downloads/mariadb-10.1.8/storage/mroonga/vendor/groonga/lib/dat.cpp:465:3: error:
cannot use 'try' with exceptions disabled
try {
^
/Users/7stud/Downloads/mariadb-10.1.8/storage/mroonga/vendor/groonga/lib/dat.cpp:552:3: error:
cannot use 'try' with exceptions disabled
try {
^
/Users/7stud/Downloads/mariadb-10.1.8/storage/mroonga/vendor/groonga/lib/dat.cpp:576:5: error:
cannot use 'try' with exceptions disabled
try {
^
/Users/7stud/Downloads/mariadb-10.1.8/storage/mroonga/vendor/groonga/lib/dat.cpp:592:3: error:
cannot use 'try' with exceptions disabled
try {
^
/Users/7stud/Downloads/mariadb-10.1.8/storage/mroonga/vendor/groonga/lib/dat.cpp:617:5: error:
cannot use 'try' with exceptions disabled
try {
^
/Users/7stud/Downloads/mariadb-10.1.8/storage/mroonga/vendor/groonga/lib/dat.cpp:616:3: error:
cannot use 'try' with exceptions disabled
try {
^
/Users/7stud/Downloads/mariadb-10.1.8/storage/mroonga/vendor/groonga/lib/dat.cpp:652:5: error:
cannot use 'try' with exceptions disabled
try {
^
/Users/7stud/Downloads/mariadb-10.1.8/storage/mroonga/vendor/groonga/lib/dat.cpp:651:3: error:
cannot use 'try' with exceptions disabled
try {
^
/Users/7stud/Downloads/mariadb-10.1.8/storage/mroonga/vendor/groonga/lib/dat.cpp:703:3: error:
cannot use 'try' with exceptions disabled
try {
^
/Users/7stud/Downloads/mariadb-10.1.8/storage/mroonga/vendor/groonga/lib/dat.cpp:811:3: error:
cannot use 'try' with exceptions disabled
try {
^
/Users/7stud/Downloads/mariadb-10.1.8/storage/mroonga/vendor/groonga/lib/dat.cpp:864:3: error:
cannot use 'try' with exceptions disabled
try {
^
/Users/7stud/Downloads/mariadb-10.1.8/storage/mroonga/vendor/groonga/lib/dat.cpp:921:3: error:
cannot use 'try' with exceptions disabled
try {
^
/Users/7stud/Downloads/mariadb-10.1.8/storage/mroonga/vendor/groonga/lib/dat.cpp:969:3: error:
cannot use 'try' with exceptions disabled
try {
^
/Users/7stud/Downloads/mariadb-10.1.8/storage/mroonga/vendor/groonga/lib/dat.cpp:1007:3: error:
cannot use 'try' with exceptions disabled
try {
^
/Users/7stud/Downloads/mariadb-10.1.8/storage/mroonga/vendor/groonga/lib/dat.cpp:1101:3: error:
cannot use 'try' with exceptions disabled
try {
^
fatal error: too many errors emitted, stopping now [-ferror-limit=]
20 errors generated.
make[2]: *** [storage/mroonga/vendor/groonga/lib/CMakeFiles/libgroonga.dir/dat.cpp.o] Error 1
make[1]: *** [storage/mroonga/vendor/groonga/lib/CMakeFiles/libgroonga.dir/all] Error 2
make: *** [all] Error 2
~/Downloads/mariadb-10.1.8$
Source: (StackOverflow)
I installed mysql through yum just now and the OS fedora installed mariadb for me.
I know mariadb is a new branch of mysql, but I can't understand why it does not ask me for setting the password.
I have tried for 123456 and so on, but I failed.
My fedora is new, and this is the first time to install mysql/mariadb.
What should I do for it?
Source: (StackOverflow)
Just installed MariaDB (with homebrew). Everything looks like it's working, but I can't figure out how to have it automatically startup on boot on my Mac. I can't find any Mac-specific docs for this.
The installation output says:
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
I guess I don't know where the right place is.
Source: (StackOverflow)
I have a query that runs in about 20 seconds on a MySQL 5.1 server but takes almost 15 minutes on a MariaDB 5.5 server.
Usual suspects like key_buffer_size and tmp_table_size and max_heap_table_size are all equal (128M). Most settings are equal as far as I can see (query_cache,etc)
The query:
SELECT products.id,
concat(publications.company_name,' [',publications.quote,'] ', products.name) as n,
products.impressions,
products.contacts,
is_channel,
sl.i,
count(*)
FROM products
LEFT JOIN publications ON products.publications_id = publications.id
LEFT OUTER JOIN (
SELECT adspace.id AS i,
slots.products_id FROM adspace
LEFT JOIN slots ON adspace.slots_id = slots.id
AND adspace.end > '2016-01-25 10:28:49'
WHERE adspace.active = 1) AS sl
ON sl.products_id = products.id
WHERE 1 = 1
AND publications.active=1
GROUP BY products.id
ORDER BY n ASC;
The only difference is in the explain fase:
Old server (MySQL 5.1)
+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
| 1 | PRIMARY | products | ALL | NULL | NULL | NULL | NULL | 6568 | Using temporary; Using filesort |
| 1 | PRIMARY | publications | eq_ref | PRIMARY | PRIMARY | 4 | db.products.publications_id | 1 | Using where |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 94478 | |
| 2 | DERIVED | adspace | ALL | NULL | NULL | NULL | NULL | 101454 | Using where |
| 2 | DERIVED | slots | eq_ref | PRIMARY | PRIMARY | 4 | db.adspace.slots_id | 1 | |
+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
New server (MariaDB 5.5)
+------+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
| 1 | SIMPLE | products | ALL | test_idx | NULL | NULL | NULL | 6557 | Using temporary; Using filesort |
| 1 | SIMPLE | publications | eq_ref | PRIMARY | PRIMARY | 4 | db.products.publications_id | 1 | Using where |
| 1 | SIMPLE | adspace | ALL | NULL | NULL | NULL | NULL | 100938 | Using where |
| 1 | SIMPLE | slots | eq_ref | PRIMARY | PRIMARY | 4 | db.adspace.slots_id | 1 | Using where |
+------+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
An index was added to the products table on the new server to speed things up, to no avail.
Engine variables:
Old server:
mysql> show variables like '%engine%';
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| engine_condition_pushdown | ON |
| storage_engine | MyISAM |
+---------------------------+--------+
mysql> show variables like '%buffer_pool%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| innodb_buffer_pool_size | 8388608 |
+-------------------------+---------+
New server:
MariaDB [db]> show variables like '%engine%';
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| default_storage_engine | InnoDB |
| engine_condition_pushdown | OFF |
| storage_engine | InnoDB |
+---------------------------+--------+
MariaDB [db]> show variables like '%buffer_pool%';
+---------------------------------------+-----------+
| Variable_name | Value |
+---------------------------------------+-----------+
| innodb_blocking_buffer_pool_restore | OFF |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_populate | OFF |
| innodb_buffer_pool_restore_at_startup | 0 |
| innodb_buffer_pool_shm_checksum | ON |
| innodb_buffer_pool_shm_key | 0 |
| innodb_buffer_pool_size | 134217728 |
+---------------------------------------+-----------+
All tables used in the query are MyISAM (both old and new server)
Profiling showed that the old query spend around 16 seconds in 'copying to tmp table' and the new server around 800 seconds in this fase.
New server all has SSD disks for storage and old servers have normal disks.
Edit: I also have a MySQL 5.5 server and there the query only take around 10 seconds. Also with all the same settings as far as I can see.
I tried to summarise it in a table:
Location: Customer Own Customer
MySQL Type: MySQL MySQL MariaDB
Mysql Version: 5.1.56-community-log 5.5.39-1-log (Debian) 5.5.44-MariaDB-log
HDD: Normal Normal SSD
Type: Virtual Real Virtual
Query time: ~15s ~10s ~15min
DB engine: MyISAM InnoDB InnoDB
Table Engine: MyISAM MyISAM MyISAM
I don't want to rewrite the query (although it could use some work) but I want to find the difference between the 2 machines, my guess is a setting that isn't ideal in MariaDB but I can't find it.
Source: (StackOverflow)
I've created database, for example 'mydb'.
CREATE DATABASE mydb CHARACTER SET utf8 COLLATE utf8_bin;
CREATE USER 'myuser'@'%' IDENTIFIED BY PASSWORD '*HASH';
GRANT ALL ON mydb.* TO 'myuser'@'%';
GRANT ALL ON mydb TO 'myuser'@'%';
GRANT CREATE ON mydb TO 'myuser'@'%';
FLUSH PRIVILEGES;
Now i can login to database from everywhere, but can't create tables.
How to grant all privileges on that database and (in the future) tables. I can't create tables in 'mydb' database. I always get:
CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);
ERROR 1142 (42000): CREATE command denied to user 'myuser'@'...' for table 't'
Source: (StackOverflow)
I'm attempting to install MariaDB on Ubuntu 12.04 LTS.
I've followed the instructions provided at http://askubuntu.com/questions/64772/how-to-install-mariadb and from MariaDB.org that appear when you choose the download.
The last step is sudo apt-get install mariadb-server which returns:
Reading package lists... Done
Building dependency tree
Reading state information... Done
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:
The following packages have unmet dependencies:
mariadb-server : Depends: mariadb-server-5.5 but it is not going to be installed
E: Unable to correct problems, you have held broken packages.
The dependency issue is an acknowledge issue (https://mariadb.atlassian.net/browse/MDEV-3882) but I believe the broken package prevents me from working around this.
If I try to install libmariadbclient18 I get the following:
Reading package lists... Done
Building dependency tree
Reading state information... Done
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:
The following packages have unmet dependencies:
libmariadbclient18 : Depends: libmysqlclient18 (= 5.5.30-mariadb1~precise) but 5.5.31-0ubuntu0.12.04.1 is to be installed
E: Unable to correct problems, you have held broken packages.
I've tried to correct the broken package using sudo apt-get install -f, but I still can't install mariadb-server or libmariadbclient18.
Source: (StackOverflow)
How can I use MariaDB instead of MySQL in my Rails project?
When I try to install mysql2 gem it returns error,because mysqlclient was not found.
Here some solution, but I didn't found any libmariadbd-dev package on my openSUSE 12.3.
Source: (StackOverflow)
We are encountering an error on Node 1 of a 5-node cluster. Queries to Node 1 seem to succeed from a client perspective but are failing to insert. We are seeing a lot of autoinc errors even though autoinc shouldn't be involved in the update queries. Also, this seems to cause performance issues until a higher priority transaction occurs knocking the node offline to perform transaction replay. Below are some of the entries in error.log with debug on and a walkthrough of setup. We are at a loss of how to troubleshoot further. The only way to cause transactions to continue is for all the clients to drop and rebuild connection pool.
Some details of the setup:
- 5 Nodes all acting as master to their local server
- All connected via WAN
- Node 1 also has outside SQL connections for website access
- Each node is running inside of docker on the physical machine
Here are some of the errors:
150703 5:56:27 [Note] WSREP: DUPKEY error for autoinc
THD 5041, value 133622, off 2 inc 5
150703 5:56:27 [Note] WSREP: retrying insert: INSERT INTO `server_live` (server_id, performance_30, performance_120, performance_300, performance_600, players_online, staff_online, staff_last_seen, uptime, worlds_loaded, chunks_loaded, entities_loaded, tileEntities_loaded) VALUES (79, 100, 100, 99, 99, 2, '{}', staff_last_seen, 15568, 13, 789, 384, 1101) ON DUPLICATE KEY UPDATE performance_30 = 100, performance_120 = 100, performance_300 = 99, performance_600 = 99, players_online = 2, staff_online = '{}', staff_last_seen = staff_last_seen, uptime = 15568, worlds_loaded = 13, chunks_loaded = 789, entities_loaded = 384, tileEntities_loaded = 1101
150703 5:56:27 [Note] WSREP: innobase_commit, abort INSERT INTO `server_live` (server_id, performance_30, performance_120, performance_300, performance_600, players_online, staff_online, staff_last_seen, uptime, worlds_loaded, chunks_loaded, entities_loaded, tileEntities_loaded) VALUES (79, 100, 100, 99, 99, 2, '{}', staff_last_seen, 15568, 13, 789, 384, 1101) ON DUPLICATE KEY UPDATE performance_30 = 100, performance_120 = 100, performance_300 = 99, performance_600 = 99, players_online = 2, staff_online = '{}', staff_last_seen = staff_last_seen, uptime = 15568, worlds_loaded = 13, chunks_loaded = 789, entities_loaded = 384, tileEntities_loaded = 1101
150703 5:56:27 [Note] WSREP: cleanup transaction for LOCAL_STATE: INSERT INTO `server_live` (server_id, performance_30, performance_120, performance_300, performance_600, players_online, staff_online, staff_last_seen, uptime, worlds_loaded, chunks_loaded, entities_loaded, tileEntities_loaded) VALUES (79, 100, 100, 99, 99, 2, '{}', staff_last_seen, 15568, 13, 789, 384, 1101) ON DUPLICATE KEY UPDATE performance_30 = 100, performance_120 = 100, performance_300 = 99, performance_600 = 99, players_online = 2, staff_online = '{}', staff_last_seen = staff_last_seen, uptime = 15568, worlds_loaded = 13, chunks_loaded = 789, entities_loaded = 384, tileEntities_loaded = 1101
150703 5:56:27 [Note] WSREP: wsrep retrying AC query: INSERT INTO `server_live` (server_id, performance_30, performance_120, performance_300, performance_600, players_online, staff_online, staff_last_seen, uptime, worlds_loaded, chunks_loaded, entities_loaded, tileEntities_loaded) VALUES (79, 100, 100, 99, 99, 2, '{}', staff_last_seen, 15568, 13, 789, 384, 1101) ON DUPLICATE KEY UPDATE performance_30 = 100, performance_120 = 100, performance_300 = 99, performance_600 = 99, players_online = 2, staff_online = '{}', staff_last_seen = staff_last_seen, uptime = 15568, worlds_loaded = 13, chunks_loaded = 789, entities_loaded = 384, tileEntities_loaded = 1101
150703 5:56:27 [Note] WSREP: DUPKEY error for autoinc
THD 5041, value 133627, off 2 inc 5
150703 5:56:27 [Note] WSREP: releasing retry_query: conf 0 sent 0 kill 0 errno 0 SQL INSERT INTO `server_live` (server_id, performance_30, performance_120, performance_300, performance_600, players_online, staff_online, staff_last_seen, uptime, worlds_loaded, chunks_loaded, entities_loaded, tileEntities_loaded) VALUES (79, 100, 100, 99, 99, 2, '{}', staff_last_seen, 15568, 13, 789, 384, 1101) ON DUPLICATE KEY UPDATE performance_30 = 100, performance_120 = 100, performance_300 = 99, performance_600 = 99, players_online = 2, staff_online = '{}', staff_last_seen = staff_last_seen, uptime = 15568, worlds_loaded = 13, chunks_loaded = 789, entities_loaded = 384, tileEntities_loaded = 1101
Our config:
[MYSQLD]
datadir=/data
log-error=/data/error.log
query_cache_size=0
binlog_format=ROW
query_cache_type=0
bind-address=0.0.0.0
port=3304
innodb_buffer_pool_size=2048M
innodb_flush_log_at_trx_commit=0
innodb_read_io_threads=4
innodb_write_io_threads=4
innodb_io_capacity=200
innodb_doublewrite=1
innodb_log_file_size=512M
innodb_log_buffer_size=64M
innodb_buffer_pool_instances=4
innodb_log_files_in_group=2
innodb_thread_concurrency=64
innodb_flush_method = O_DIRECT
innodb_autoinc_lock_mode=2
innodb_stats_on_metadata=0
default_storage_engine=innodb
binlog_format=ROW
key_buffer_size = 24M
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 512M
skip_name_resolve
memlock=0
sysdate_is_now=1
max_connections=512
thread_cache_size=512
query_cache_type = 0
query_cache_size = 0
table_open_cache=1024
lower_case_table_names=0
wait_timeout = 28800
explicit_defaults_for_timestamp=1
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=2048M; evs.keepalive_period=PT3S; evs.inactive_check_period=PT10S; evs.suspect_timeout=PT30S; evs.inactive_timeout=PT1M; evs.install_timeout=PT1M; evs.send_window=1024; evs.user_send_window=512;"
wsrep_cluster_name="<removed>"
wsrep_cluster_address="<removed>"
wsrep_slave_threads=4
wsrep_certify_nonPK=1
wsrep_max_ws_rows=131072
wsrep_max_ws_size=1073741824
wsrep_debug=1
wsrep_convert_LOCK_to_trx=0
wsrep_retry_autocommit=10
wsrep_auto_increment_control=1
wsrep_replicate_myisam=1
wsrep_drupal_282555_workaround=1
wsrep_causal_reads=0
wsrep_sst_method=rsync
wsrep_log_conflicts=1
UPDATE:
Per request in comments:
mysql> SHOW CREATE TABLE server_live;
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| server_live | CREATE TABLE `server_live` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`server_id` int(11) NOT NULL,
`performance_30` int(11) NOT NULL,
`performance_120` int(11) NOT NULL,
`performance_300` int(11) NOT NULL,
`performance_600` int(11) NOT NULL,
`players_online` int(11) NOT NULL,
`staff_online` varchar(255) NOT NULL DEFAULT '{}',
`staff_last_seen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`uptime` int(11) NOT NULL,
`worlds_loaded` int(11) NOT NULL,
`chunks_loaded` int(11) NOT NULL,
`entities_loaded` int(11) NOT NULL,
`tileEntities_loaded` int(11) NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `server_id_2` (`server_id`),
CONSTRAINT `server_live_ibfk_1` FOREIGN KEY (`server_id`) REFERENCES `server` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=720312 DEFAULT CHARSET=utf8 |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
mysql> SHOW VARIABLES LIKE 'auto%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 5 |
| auto_increment_offset | 3 |
| autocommit | ON |
| automatic_sp_privileges | ON |
+--------------------------+-------+
Source: (StackOverflow)
Have a simple question here.
I've a database with around 1 billion records, a server with 200GB of ram to handle it.
What do you suggest for best performances? Mysql 5, Mysql 6 or MariaDB?
Source: (StackOverflow)
Why do queries like SELECT (@sum:=(@var:=@sum)+some_table.val)... automatically cast (@var:=@sum) to integer in MySQL ≤5.5 for DECIMAL-type some_table.val and floor it for DOUBLE/FLOAT? What feature was changed to allow for the expected behaviour in 5.6?
Context & Elaboration:
Consider the following table:
CREATE TABLE t (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
sum_component FLOAT
);
INSERT INTO t (sum_component) VALUES (0.5), (0.6), (0.4), (0.5);
I am designing a cumulative sum query, which fetches the id of the entry where the cumulative sum is greater than some value. Normally, this query would fit the bill:
SELECT t.id,
@cumulative_sum
FROM t
CROSS JOIN (SELECT @cumulative_sum:=0) a
WHERE (@cumulative_sum:=@cumulative_sum+t.sum_component) > 1.3
ORDER BY id ASC LIMIT 1;
...but I happen to also need to store the cumulative sum before the selected entry as well for later calculations, and cumulative_sum from this query does not return the expected result, double-counting the last entry. In this case, I would want this query to set a variable that stores the value 1.1 (0.5 + 0.6) without having to do additional math.
If I assign the old value of @cumulative_sum to @another_variable in the incrementation step, I should be able to do precisely this.
SELECT t.id,
@cumulative_sum
FROM t
CROSS JOIN (SELECT @cumulative_sum:=0) a
WHERE (@cumulative_sum:=(@another_variable:=@cumulative_sum)+t.sum_component) > 1.3
ORDER BY id ASC LIMIT 1;
On two of my machines — one running MySQL 5.6 and another running MariaDB 10.0.7 — the above query performs as expected:
MariaDB [a51]> SELECT t.id, @cumulative_sum
FROM t
CROSS JOIN
( SELECT @cumulative_sum:=0) a
WHERE (@cumulative_sum:=(@another_variable:=@cumulative_sum)
+t.sum_component) > 1.3
ORDER BY id ASC
LIMIT 1;
+----+--------------------+
| id | @cumulative_sum |
+----+--------------------+
| 3 | 1.5000000298023224 |
+----+--------------------+
1 row in set (0.00 sec)
MariaDB [a51]> SELECT @another_variable;
+-------------------+
| @another_variable |
+-------------------+
| 1.100000023841858 |
+-------------------+
1 row in set (0.01 sec)
but on MySQL 5.5, it doesn't:
mysql> SELECT t.id, @cumulative_sum
FROM t
CROSS JOIN
( SELECT @cumulative_sum:=0) a
WHERE (@cumulative_sum:=(@another_variable:=@cumulative_sum)+t.sum_component) > 1.3
ORDER BY id ASC
LIMIT 1;
Empty set (0.18 sec)
mysql> SELECT @another_variable;
+-------------------+
| @another_variable |
+-------------------+
| 0 |
+-------------------+
1 row in set (0.01 sec)
Observing how the query increments, we can see the underlying problem. Here are the results with a FLOAT sum_component:
mysql> SELECT t.id, (@cumulative_sum := (@another_variable:=@cumulative_sum)
+ t.sum_component) AS cumulative_sum,
sum_component
FROM t
CROSS JOIN
( SELECT @cumulative_sum:=0) a
ORDER BY id ASC;
+----+----------------+---------------+
| id | cumulative_sum | sum_component |
+----+----------------+---------------+
| 1 | 0.5 | 0.5 |
| 2 | 0.6 | 0.6 |
| 3 | 0.4 | 0.4 |
| 4 | 0.5 | 0.5 |
+---+----------------+----------------+
4 rows in set (0.04 sec)
and here's what having a DECIMAL sum_component looks like:
mysql> ALTER TABLE t MODIFY sum_component DECIMAL(4,2);
Query OK, 4 rows affected, 2 warnings (0.16 sec)
Records: 4 Duplicates: 0 Warnings: 2
mysql> SELECT t.id, (@cumulative_sum := (@another_variable:=@cumulative_sum)
+ t.sum_component) AS cumulative_sum,
sum_component
FROM t
CROSS JOIN
( SELECT @cumulative_sum:=0) a
ORDER BY id ASC;
+----+----------------+---------------+
| id | cumulative_sum | sum_component |
+----+----------------+---------------+
| 1 | 0.50 | 0.50 |
| 2 | 1.60 | 0.60 |
| 3 | 2.40 | 0.40 |
| 4 | 2.50 | 0.50 |
+----+----------------+---------------+
4 rows in set (0.18 sec)
SQL Fiddle
Source: (StackOverflow)
I've been using PDO in PHP for a while now utilizing MySQL.
However, recent developments have made me think that MySQL will start fading out in replacement of MariaDB especially since MariaDB:
- Consider themselves many developer years ahead of MySQL without putting new developments into paid areas (clustering for example).
- The majority of the main MySQL developers moved to MariaDB after Oracle took over.
- Is becoming the default database to replace MySQL on various Linux distributions.
- Is a drop in replacement of MySQL and large companies are starting to adopt MariaDB such as Wikipedia (Read the blog post here).
So my question is, since MariaDB doesn't appear to be listed in the PDO drivers and seeing as MariaDB is designed to be a "drop in replacement" and could potentially phase out MySQL in the future.
Can I use the MySQL PDO driver with a MariaDB database, at least until an official MariaDB driver becomes available?
Links
Source: (StackOverflow)
MySQL is awesome! I am currently involved in a major server migration and previously, our small database used to be hosted on the same server as the client.
So we used to do this : SELECT * INTO OUTFILE .... LOAD DATA INFILE ....
Now, we moved the database to a different server and SELECT * INTO OUTFILE .... no longer works, understandable - security reasons I believe.
But, interestingly LOAD DATA INFILE .... can be changed to LOAD DATA LOCAL INFILE .... and bam, it works.
I am not complaining nor am I expressing disgust towards MySQL. The alternative to that added 2 lines of extra code and a system call form a .sql script. All I wanted to know is why LOAD DATA LOCAL INFILE works and why is there no such thing as SELECT INTO OUTFILE LOCAL?
I did my homework, couldn't find a direct answer to my questions above. I couldn't find a feature request @ MySQL either. If someone can clear that up, that had be awesome!
Is MariaDB capable of handling this problem?
Source: (StackOverflow)
I was asked to check Mariadb as Centos does not provider MySQL 5.5 for the moment. I have read that xtradb servers as a drop in for innodb.
What are the advantages of using one or the other because if they were equal, they would not have been called the same name?
Do you think that I should switch to Mariadb? What kind of problems I might face in the future because of updates if any.
I know that the founder of MySQL is behind Mariadb, and Oracle is managing MySQL now. It seems a bit tricky as a tricky decision.
Thank you in advance for your opinion,
Update, I asked the question here, because google did not display any recent updates. Only some old comparisons published prior to 2012
Source: (StackOverflow)