- discuss - lists.mariadb.org

[Maria-discuss] INSERT IGNORE and triggers
by Federico Razzoli 19 Jul '15

19 Jul '15

Hello. As we all know, IGNORE "catches" some errors and turns them into warnings. But I've noticed that it does not catch the same errors if they were issued by a trigger (using SIGNAL). * Is this by design? * Is this standard SQL behaior? Here is a trigger example to show what I mean: CREATE TRIGGER bi_t BEFORE INSERT ON t FOR EACH ROW BEGIN SIGNAL SQLSTATE '23000' SET MYSQL_ERRNO = 1048; END Regards Federico

2 1

[Maria-discuss] mysqlbinlog
by Rhys.Campbell＠swisscom.com 18 Jul '15

18 Jul '15

Hello All, I've been playing with mysqlbinlog today. I'm Looking at performing backups of the binary logs. The MariaDB<https://mariadb.com/kb/en/mariadb/mysqlbinlog-options/> mysqlbinlog lags behind the MySQL<https://mariadb.com/kb/en/mariadb/mysqlbinlog-options/> version in the sense it's missing a few features. Namely the ability to 'tail' a live binary log... mysqlbinlog --read-from-remote-server --host=host_name --raw --stop-never binlog.000999 I haven't fully tested this but running the MySQL version against MariaDB doesn't appear viable. I'm probably going to fall back to a FLUSH LOGS / file copy solution for the moment. What would be the chances of back-porting these features into the MariaDB version? Cheers, Rhys

4 13

[Maria-discuss] New Question: Ubuntu vivid package missing virtual-mysql-server-core
by AskMonty KB 17 Jul '15

17 Jul '15

Hello, A new question has been asked in "MariaDB Galera Cluster" by wolfgang-baron. Please answer it at http://mariadb.com/kb/en/ubuntu-vivid-package-missing-virtual-mysql-server-… as the person asking the question may not be subscribed to the mailing list. -------------------------------- On ubuntu vivid, the current mariadb-galera-server-10.0 package does not provide virtual package virtual-mysql-server-core (it only provides virtual-mysql-server), which disables the installation of akonadi-backend-mysql, which is obligatory for akonadi-server and thus all akonadi using programs. Is there a way to fake this provision? How do I report a bug like this? Thanks for any help! -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/ubuntu-vivid-package-missing-virtual-mysql-server-…

1 0

[Maria-discuss] Getting PAM auth to work with JDBC
by Troels Arvin 16 Jul '15

16 Jul '15

I've got the PAM authentication to work, as long as I use a plain command line MariaDB client or HeidiSQL. But I need things to work with JDBC as well. I've found some instructions what seem to indicate that I need to somehow make the JDBC connection use SQL and plain-text authentication. But I have not been able to find specific instructions on doing that. (The instructions, I've seen seem to mainly point to other documents in a circular/byzantine way.) Has someone got the combination of a JDBC client and an PAM-enabled MariaDB server to work? If so: Can you shed some light on the steps you went through? Thanks in advance. -- Regards, Troels Arvin http://troels.arvin.dk

3 2

[Maria-discuss] New Question: ERROR 1932 on a table with recently added VIRTUAL column
by AskMonty KB 16 Jul '15

16 Jul '15

Hello, A new question has been asked in "MariaDB Community" by Bytesmiths. Please answer it at http://mariadb.com/kb/en/error-1932-on-a-table-with-recently-added-virtual-… as the person asking the question may not be subscribed to the mailing list. -------------------------------- Using version "10.1.0-MariaDB-log - Source distribution" on MacOS X Server 10.6.8 via phpMyAdmin 4.1.8 using raw SQL, I recently used ALTER TABLE to add a VIRTUAL column to a table that was the simple difference between two columns: "ALTER TABLE s_vehicle_log ADD COLUMN v_distance INT(5) AS (odometer - begin) AFTER odometer". This first appeared to work; I could see the new column in phpMyAdmin and it appeared to have the proper values. But within an hour, I could no longer access that table. Going into the mysql CLI, executing "SELECT count(*) FROM s_vehicle_log;" yields "ERROR 1932 (42S02): Table 'EcoReality.s_vehicle_log' doesn't exist in engine" (I do the same on other tables in the same database successfully.) Going into the shell (bash), I can see the proper files, and they have the same perms as other tables that work properly: # ls -l s_vehicle_* -rw-rw---- 1 _mysql _mysql 1264 Feb 12 18:40 s_vehicle_cost.frm -rw-rw---- 1 _mysql _mysql 98304 Feb 12 18:40 s_vehicle_cost.ibd -rw-rw---- 1 _mysql _mysql 3796 Jul 15 15:56 s_vehicle_log.frm -rw-rw---- 1 _mysql _mysql 688128 Jul 15 15:56 s_vehicle_log.ibd -rw-rw---- 1 _mysql _mysql 2583 Feb 12 18:40 s_vehicle_monthly_query.frm It is interesting that, though ERROR 1932 says the table doesn't exist, phpMyAdmin shows it in the list of tables, showing "in use" as its table type: s_vehicle_cost Browse Browse Structure Structure Search Search Insert Insert Empty Empty Drop Drop ~11 InnoDB latin1_swedish_ci 16 KiB - s_vehicle_log Browse Browse Structure Structure Search Search Insert Insert Empty Empty Drop Drop in use mysql SHOW TABLE STATUS isn't very helpful, either: MariaDB [EcoReality]> show table status like 's_vehicle_%' -> ; +-------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+----------------------------------------------------------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+----------------------------------------------------------+ | s_vehicle_cost | InnoDB | 10 | Compact | 11 | 1489 | 16384 | 0 | 0 | 0 | NULL | 2015-02-12 18:40:32 | NULL | NULL | latin1_swedish_ci | NULL | | Cost per km of vehicles over time. | | s_vehicle_log | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Table 'EcoReality.s_vehicle_log' doesn't exist in engine | | s_vehicle_monthly_query | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Table 'EcoReality.s_vehicle_log' doesn't exist in engine | +-------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+----------------------------------------------------------+ 3 rows in set, 2 warnings (0.00 sec) Looking at the most recent file that seems to be a binlog reveals nothing of interest; just phpMyAdmin logging queries about the damaged table: # mysqlbinlog ../mysql-bin.000127 | fgrep s_vehicle_log REPLACE INTO `phpmyadmin`.`pma__table_uiprefs` VALUES ('root', 'EcoReality', 's_vehicle_log', '[]', NULL) REPLACE INTO `phpmyadmin`.`pma__recent` (`username`, `tables`) VALUES ('root', '[{"db":"EcoReality","table":"s_where_heard"},{"db":"EcoReality","table":"s_product_sales_log"},{"db":"EcoReality","table":"s_animal_notes"},{"db":"EcoReality","table":"s_product_harvest"},{"db":"EcoReality","table":"s_timelog"},{"db":"EcoReality","table":"s_project"},{"db":"EcoReality","table":"s_product"},{"db":"Personal","table":"Weight"},{"db":"EcoReality","table":"s_vehicle_log"},{"db":"EcoReality","table":"s_shares"}]') REPLACE INTO `phpmyadmin`.`pma__recent` (`username`, `tables`) VALUES ('root', '[{"db":"EcoReality","table":"s_timelog"},{"db":"EcoReality","table":"s_where_heard"},{"db":"EcoReality","table":"s_product_sales_log"},{"db":"EcoReality","table":"s_animal_notes"},{"db":"EcoReality","table":"s_product_harvest"},{"db":"EcoReality","table":"s_project"},{"db":"EcoReality","table":"s_product"},{"db":"Personal","table":"Weight"},{"db":"EcoReality","table":"s_vehicle_log"},{"db":"EcoReality","table":"s_shares"}]') REPLACE INTO `phpmyadmin`.`pma__recent` (`username`, `tables`) VALUES ('root', '[{"db":"EcoReality","table":"s_product_sales_log"},{"db":"EcoReality","table":"s_timelog"},{"db":"EcoReality","table":"s_where_heard"},{"db":"EcoReality","table":"s_animal_notes"},{"db":"EcoReality","table":"s_product_harvest"},{"db":"EcoReality","table":"s_project"},{"db":"EcoReality","table":"s_product"},{"db":"Personal","table":"Weight"},{"db":"EcoReality","table":"s_vehicle_log"},{"db":"EcoReality","table":"s_shares"}]') repair table s_vehicle_log REPLACE INTO `phpmyadmin`.`pma__recent` (`username`, `tables`) VALUES ('root', '[{"db":"EcoReality","table":"s_timelog"},{"db":"EcoReality","table":"s_product_sales_log"},{"db":"EcoReality","table":"s_where_heard"},{"db":"EcoReality","table":"s_animal_notes"},{"db":"EcoReality","table":"s_product_harvest"},{"db":"EcoReality","table":"s_project"},{"db":"EcoReality","table":"s_product"},{"db":"Personal","table":"Weight"},{"db":"EcoReality","table":"s_vehicle_log"},{"db":"EcoReality","table":"s_shares"}]') REPLACE INTO `phpmyadmin`.`pma__recent` (`username`, `tables`) VALUES ('root', '[{"db":"EcoReality","table":"s_vehicle"},{"db":"EcoReality","table":"s_timelog"},{"db":"EcoReality","table":"s_product_sales_log"},{"db":"EcoReality","table":"s_where_heard"},{"db":"EcoReality","table":"s_animal_notes"},{"db":"EcoReality","table":"s_product_harvest"},{"db":"EcoReality","table":"s_project"},{"db":"EcoReality","table":"s_product"},{"db":"Personal","table":"Weight"},{"db":"EcoReality","table":"s_vehicle_log"}]') ---------------- Anyone know what's going on, and what to do about it? (Besides "restore from backup," which is a bit old.) -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/error-1932-on-a-table-with-recently-added-virtual-…

1 0

[Maria-discuss] doubt - replication at same mysqld process
by Roberto Spadim 15 Jul '15

15 Jul '15

hi guys, i have a doubt about replciation on same machine, i never did this before i have a table running many writes and few reads, and another process start reading a lot, my today solution is replication on two servers (on same machine or other machine), the point is... could i replicate in same server (with only one server running / only one mysqld process) ? something like change table A, and a background process replicate to table B? "many writes" will write at table A, reads will read table B (read can be out of sync) innodb is locking a lot of rows, and myisam/aria is locking table a lot, both engines i have problem with lock, i consider replication to another mysqld process as the only solution, but i'm considering running only one mysqld process (if possible) i was thinking about something like HA in spider, but i didn't tested, maybe with flexviewcdc i could have a materialized view "B" of table A? any idea/help is wellcome -- Roberto Spadim

5 16

[Maria-discuss] ssd and hdd
by Roberto Spadim 12 Jul '15

12 Jul '15

hi guys i`m with a doubt... i have a server with ssd raid 10 array and hdd raid10 array ssd is faster for non sequencial read that's a fact when i change a table from one device to another and check query response histograms my doubt is... today i have myisam, aria, innodb and sometimes tokudb and spider at same server how could i easily select what table should be stored at ssd or hdd array? there's a "table space" to select if i want to use ssd or hdd instead of create table, and create symbolic links, or create table with data directory/index directory? i was thinking about something more easily understood like tablespace, but used by all engines something like create table blabla table space ssd create table blabla table space hdd what i'm doing today... i have many tables that i use normally and don't drop, these tables i can symbolic link to ssd or hdd, but all new tables i use hdd (they normally are temporary tables), i'm using innodb per file, tokudb i'm not using too much but i don't care about where it's today anyone with experience to help or ideas? -- Roberto Spadim

4 3

[Maria-discuss] New Question: Joomla 1.5 and unicode databse
by AskMonty KB 12 Jul '15

12 Jul '15

Hello, A new question has been asked in "Moving to MariaDB" by alijam. Please answer it at http://mariadb.com/kb/en/joomla-15-and-unicode-databse/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- I have a server which (unfortunately) have tens of joomla 1.5 websites, After upgrade from Mysql to Mariadb 10 , all of the unicode texts are being displayed as question marks (i.e. ?????? ????), I looked into the databases , the data seems to be intact, even I moved some of them to another server with mariadb 5.6 which "seems" ok. It seems to be a driver/configuration issue. It works on later versions of joomla (2.5& 3.x) versions. It seems mariadb should also be compatible with joomla 1.5 In the joomla system information it says: Database Version: 10.0.20-MariaDB Database Collation: N/A (mySQL < 4.1.2) PHP Version: 5.4.41 Web Server: Apache Web Server to PHP interface: cgi-fcgi Joomla! Version: Joomla! 1.5.26 Stable [ senu takaa ama busani ] 27-March-2012 18:00 GMT User Agent: Mozilla/5.0 (Windows NT 6.3; WOW64; rv:39.0) Gecko/20100101 Firefox/39.0 Any help would be greatly appreciated , as I'm going to upgrade my other servers to mariadb. Best -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/joomla-15-and-unicode-databse/

1 0

Re: [Maria-discuss] New Question: backup
by Reindl Harald 10 Jul '15

10 Jul '15

Am 10.07.2015 um 14:03 schrieb Pantelis Theodosiou: > Is rsync a valid backup option for a running server? no > Without stopping it or at least (flushing and) locking tables first? no there are files in use and you can't get a safe, consistent snapshot of the datadir when the server is running, what you can do is one hot rsync, then stop mysqld and make a second rsync which should be fast because it only needs to copy the recent changes and fire up the daemon after that but that's just a workaround losing caches and buffers for nothing there is no problem start a replication-slave instance even on the same host while it's prferred to have it on a different because that brings you a "realtime backup" if the master host crashs unclean and refuses to start mysqld because damage of datafiles > On Wed, Jul 8, 2015 at 3:53 PM, Reindl Harald <h.reindl(a)thelounge.net > <mailto:h.reindl@thelounge.net>> wrote: > > > > Am 08.07.2015 um 16:40 schrieb AskMonty KB: > > A new question has been asked in "MariaDB Documentation" by > gtrymore. Please answer it at http://mariadb.com/kb/en/backup/ > as the person asking the question may not be subscribed to the > mailing list. > > -------------------------------- > How do you back up a mariadb server and what is /var/lib/mysql > directory > -------------------------------- > > power down the replication slave and rsync /var/lib/mysql to the > backup-location or whatever folder is configured as datadir - to be > honest you could have typed the same in a google search

1 0

[Maria-discuss] New Question: backup
by AskMonty KB 10 Jul '15

10 Jul '15

Hello, A new question has been asked in "MariaDB Documentation" by gtrymore. Please answer it at http://mariadb.com/kb/en/backup/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- How do you back up a mariadb server and what is /var/lib/mysql directory -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/backup/

5 8