- discuss - lists.mariadb.org

[Maria-discuss] Step-by-Step Tutorial: How to Setup Your Own e-Commerce Online Store using WooCommerce 3.4.5, Wordpress 4.9.8, and CentOS 1805 (LAMP) in Amazon AWS Cloud
by Turritopsis Dohrnii Teo En Ming 29 Sep '18

29 Sep '18

Good morning from Singapore, You can read my step-by-step tutorial on How to Setup Your Own e-Commerce Online Store using WooCommerce 3.4.5, Wordpress 4.9.8, and CentOS 1805 (LAMP) in Amazon AWS Cloud at any one of my two redundant blogs. My blogs were configured in RAID 1 mirroring array. https://tdtemcerts.wordpress.com/2018/09/29/step-by-step-tutorial-how-to-se… https://tdtemcerts.blogspot.com/2018/09/step-by-step-tutorial-how-to-setup-… Thanks for reading! If there are any mistakes, please do let me know! ===BEGIN SIGNATURE=== Turritopsis Dohrnii Teo En Ming's Academic Qualifications as at 30 Oct 2017 [1] https://tdtemcerts.wordpress.com/ <https://tdtemcerts.wordpress.com/>[2] http://tdtemcerts.blogspot.sg/ <http://tdtemcerts.blogspot.sg/>[3] https://www.scribd.com/user/270125049/Teo-En-Ming <https://www.scribd.com/user/270125049/Teo-En-Ming>===END SIGNATURE===

1 0

Re: [Maria-discuss] MariaDB 10.3 and largepages on Linux
by Kenneth Penza 26 Sep '18

26 Sep '18

Good morning, Thanks for the clarification. Yes sure, below please find the requested output. If you prefer I can open a JIRA ticket to facilitate tracking. $ cat /proc/sys/vm/nr_hugepages 512 $ uname -a Linux srv1 3.10.0-862.11.6.el7.x86_64 #1 SMP Fri Aug 10 16:55:11 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux $ grep ^Huge /proc/meminfo HugePages_Total: 512 HugePages_Free: 467 HugePages_Rsvd: 167 HugePages_Surp: 0 Hugepagesize: 2048 kB $ grep mysql /etc/security/limits.conf mysql soft memlock 2621440 mysql hard memlock 2621440 $ grep AnonHugePages /proc/meminfo AnonHugePages: 6144 kB $ cat /etc/sysctl.d/99-mysql.conf vm.hugetlb_shm_group = 27 vm.nr_hugepages = 512 vm.swappiness = 10 fs.aio-max-nr = 500000 $ I have also added, "LimitMEMLOCK=2147483648" in systemd file. Kenneth On Wed, Sep 26, 2018 at 1:01 AM Daniel Black <daniel(a)linux.ibm.com> wrote: > On Tue, 25 Sep 2018 10:48:01 +0200 > Kenneth Penza <kpenza(a)gmail.com> wrote: > > > Good morning, > > > Good morning, > > Thanks for the detailed bug report. As the contributor of this feature > I'm happy to try to work this out. First I'll reassure you that the > WARNING is simply that, there's no negative impacts form this apart > from any unexpected core dump will include the large pages as it failed > to to mark them as don't dump. > > A couple of small clarifying questions to start: > > Do you have THP enabled? > $ cat /proc/sys/vm/nr_hugepages > > What kernel version / architecture? > $ uname -a > > What huge page size / current allocation is available? > > $ grep ^Huge /proc/meminfo > > > > I am currently running RHEL 7.5 and MariaDB 10.3.9. After setting > > "large_pages=on" I noticed that MariaDB is reporting an error on > > startup and shutdown as per below output. > > > > To use large pages I > > modified /etc/sysctl.conf, /etc/security/limits.conf and the systemd > > unit file. By monitoring /proc/meminfo I verified that largepage are > > being used. > > > > Configuration: > > > > mysqld would have been started with the following arguments: > > --port=3306 --socket=/var/lib/mysql/mysql.sock --thread_stack=256K > > --large_pages=on --key_buffer_size=16K --max_allowed_packet=512M > > --table_open_cache=32 --sort_buffer_size=512K --net_buffer_length=8K > > --read_buffer_size=256K --read_rnd_buffer_size=256K --slow_query_log=1 > > --slow_query_log_file=mysql-slow.log --long_query_time=0.1 > > --log-queries-not-using-indexes=on --thread_handling=pool-of-threads > > --thread_pool_idle_timeout=60 --thread_pool_max_threads=1000 > > --thread_pool_size=8 --innodb_buffer_pool_size=256M > > --innodb_strict_mode=1 --innodb_file_per_table=1 > > --innodb_flush_method=O_DIRECT --innodb_log_files_in_group=4 > > --innodb_log_file_size=50331648 --innodb_undo_logs=128 > > --innodb_undo_tablespaces=8 --slow_query_log=1 > > --slow_query_log_file=mysql-slow.log --long_query_time=30 > > --log-queries-not-using-indexes=off --log_bin=mariadblog > > --binlog_format=ROW --binlog_row_image=MINIMAL > > --max_binlog_size=10240000 --expire_logs_days=1 --server-id=1 > > --innodb_flush_log_at_trx_commit=1 --sync_binlog=1 > > --relay-log=mariadb-relay-bin --performance_schema=off > > --histogram_size=255 --histogram_type=SINGLE_PREC_HB > > --use_stat_tables=preferably --optimizer_use_condition_selectivity=4 > > > > Startup messages: > > > > Sep 25 10:40:53 srv1 mysqld: 2018-09-25 10:40:52 0 [Note] InnoDB: > > Opened 8 undo tablespaces > > Sep 25 10:40:53 srv1 mysqld: 2018-09-25 10:40:53 0 [Warning] InnoDB: > > Failed to set memory to DODUMP: Invalid argument ptr 0x2aaac5400000 > > size 2097152 Sep 25 10:40:53 srv1 mysqld: 2018-09-25 10:40:53 0 > > [Note] InnoDB: 128 out of 128 rollback segments in 8 undo tablespaces > > are active. > > > > Shutdown messages: > > > > Sep 25 10:41:18 srv1 mysqld: 2018-09-25 10:41:18 0 [Note] InnoDB: > > Buffer pool(s) dump completed at 180925 10:41:18 > > Sep 25 10:41:19 srv1 mysqld: 2018-09-25 10:41:19 0 [Warning] InnoDB: > > Failed to set memory to DODUMP: Invalid argument ptr 0x2aaac3400000 > > size 33554432 Sep 25 10:41:19 srv1 mysqld: 2018-09-25 10:41:19 0 > > [Note] InnoDB: Shutdown completed; log sequence number 1522725; > > transaction id 22 > > > > Do you have ideas on what is causing "InnoDB: Failed to set memory to > > DODUMP: Invalid argument ptr .. " warning? > > > > > > > Kenneth > >

1 0

[Maria-discuss] pam / ldap auth
by mourik jan c heupink 25 Sep '18

25 Sep '18

Hi, I installed mariadb using the stock debian packages, configured pam authentication, and configured a mysql pam module like this: > /etc/pam.d/mysql > auth required pam_winbind.so > account required pam_winbind.so As I also added the server to my AD domain, this basically gives my samba winbind users access to mysql using their windows credentials: > root@mysqlserver:~# mysql -udomain_username -p > Enter password: <windows_domain_password> > Welcome to the MariaDB monitor. Commands end with ; or \g. > Your MariaDB connection id is 6 > Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1 > > Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. > > Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. > > MariaDB [(none)]> So I was happy. Initially. :-) Until I found out this works only from the localhost mariadb is running on. Trying this from a remote mysql client gives: > user@e7470 ~ $ mysql -udomain_user -p -h mysqlserver.full.address > Enter password: ERROR 2059 (HY000): Authentication plugin 'dialog' cannot be loaded: /usr/lib/mysql/plugin/dialog.so: cannot open shared object file: No such file or directory and searching on that remote server for /usr/lib/mysql/plugin/dialog.so gives no results: > user@e7470 ~ $ dpkg --search /usr/lib/mysql/plugin/dialog.so > dpkg-query: no path found matching pattern /usr/lib/mysql/plugin/dialog.so Anyone here with a good suggestion how to make this work from a 'regular' remote mysql client? But more fundamentally: is it possible to have mariadb ldap auth on a server, *without* requiring anything special on the client side? MJ

4 6

[Maria-discuss] MariaDB + RocksDB: MemTables Compactation
by Juan Telleria Ruiz de Aguirre 24 Sep '18

24 Sep '18

Dear MariaDB Developers, I have an issue with RocksDB, as it takes a lot of Memory after performing a Bulk Load, taking almost 30GB of RAM Memory after data ingestion. My question is: ¿How can I flush RocksDB Compactation MemTables after the Data Bulk Load? ¿Is there any FLUSH TABLES ROCKSDB command sort of? Thank you, Juan Here is my.ini Configuration: ######################################################################## ######################################################################## ########### MARIADB: TORNADO24 CONFIGURATION FILE - ROCKSDB ############ ######################################################################## ######################################################################## ######################################################################### ############################# client #################################### ######################################################################### # The following options will be passed to all MariaDB clients [client] port = 3305 #socket = /tmp/mysql.sock socket = C:/Program Files/MariaDB 10.3/data/mysql.sock #pid-file = C:/Program Files/MariaDB 10.3/data/mysql.pid default-character-set = latin1 # Here follows entries for some specific programs ######################################################################### ############################ mariadb #################################### ######################################################################### [mariadb] #local_infile = 1 # ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| # # |||||||||||||||||||||||||||||| GENERAL |||||||||||||||||||||||||||||| # # ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| # # The MariaDB server [mysqld] port = 3305 #socket = /tmp/mysql.sock socket = C:/Program Files/MariaDB 10.3/data/mysql.sock #pid-file = C:/Program Files/MariaDB 10.3/data/mysql.pid tmpdir = F:/MariaDB 10.3/temp/ # Table type which is used by default when creating new tables, if not # specified differently during the CREATE TABLE statement. # The default storage engine that will be used when new tables are created. default-storage-engine = rocksdb # Default storage engine that will be used for tables created with # CREATE TEMPORARY TABLE (Whichever this Engine, Aria Engine will always # be used for internal temporary tables, as materialized tables in subqueries, # or other internal tables). RocksDB does not perform well for Temporary Tables, # which require fast INSERT INTO...VALUES in Loops (For example: Cursors). default_tmp_storage_engine = aria # The default character set that will be used when a new schema or table is # created and no character set is defined character-set-server = latin1_bin collation-server = latin1_spanish_ci # Set the SQL mode to strict # Default: sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" sql_mode = "" # *************************** OTHER *********************************** # group_concat_max_len = 1048576 event_scheduler = ON net_write_timeout = 3600 net_read_timeout = 3600 #local_infile = 1 # ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| # # ||||||||||||||||||||||||||| DATA STORAGE |||||||||||||||||||||||||||| # # ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| # # Path to the database root datadir = F:/MariaDB 10.3/data # ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| # # |||||||||||||||||||||||||||||| SAFETY ||||||||||||||||||||||||||||||| # # ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| # # The maximum size of a query packet the server can handle as well as # maximum query size server can process (Important when working with # large BLOBs). enlarged dynamically, for each connection. max_allowed_packet = 512M # Maximum amount of errors allowed per host. If this limit is reached, # the host will be blocked from connecting to the MariaDB server until # "FLUSH HOSTS" has been run or the server was restarted. Invalid # passwords and other errors during the connect phase result in # increasing this value. See the "Aborted_connects" status variable for # global counter. max_connect_errors = 1000 # Secure File Priv. # Disabled: secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/" # ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| # # |||||||||||||||||||||||| CACHES AND LIMITS |||||||||||||||||||||||||| # # ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| # # Maximum size for internal (in-memory) temporary tables. If a table # grows larger than this value, it is automatically converted to disk # based table This limitation is for a single table. There can be many # of them. tmp_table_size = 512M # Maximum allowed size for a single HEAP (in memory) table. This option # is a protection against the accidential creation of a very large HEAP # table which could otherwise use up all memory resources. max_heap_table_size = 1024M # Query cache is used to cache SELECT results and later return them # without actual executing the same query once again. Having the query # cache enabled may result in significant speed improvements, if your # have a lot of identical queries and rarely changing tables. See the # "Qcache_lowmem_prunes" status variable to check if the current value # is high enough for your load. # Note: In case your tables change very often or if your queries are # textually different every time, the query cache may result in a # slowdown instead of a performance improvement. query_cache_size = 1M # How many threads we should keep in a cache for reuse. When a client # disconnects, the client's threads are put in the cache if there aren't # more than thread_cache_size threads from before. This greatly reduces # the amount of thread creations needed if you have a lot of new # connections. (Normally this doesn't give a notable performance # improvement if you have a good thread implementation.) thread_cache_size = 256 # The maximum amount of concurrent sessions the MariaDB server will # allow. One of these connections will be reserved for a user with # SUPER privileges to allow the administrator to login even if the # connection limit has been reached. # Low value choosen is order to reduce RAM Memory Allocation. max_connections = 50 # The number of open tables for all threads. Increasing this value # increases the number of file descriptors that mysqld requires. # Therefore you have to make sure to set the amount of open files # allowed to at least 4096 in the variable "open-files-limit" in # section [mysqld_safe] table_open_cache = 4096 # ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| # # ||||||||||||||||||||||||||||| LOGGING ||||||||||||||||||||||||||||||| # # ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| # # General and Slow logging. log-output='FILE,TABLE' general-log=0 general_log_file="NGALARRETA-P700.log" # Error Logging. log-error = C:/Program Files/MariaDB 10.3/data/NGALARRETA-P700-error.log log_queries_not_using_indexes = 0 slow-query-log = 0 slow-query-log-file = C:/Program Files/MariaDB 10.3/data/NGALARRETA-P700-slow.log long_query_time = 1800 # Binary Logging. # log-bin # ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| # # ||||||||||||||||||||||||||| ALL ENGINES ||||||||||||||||||||||||||||| # # ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| # # Sort buffer is used to perform sorts for some ORDER BY and GROUP BY # queries. If sorted data does not fit into the sort buffer, a disk # based merge sort is used instead - See the "Sort_merge_passes" # status variable. Allocated per thread if sort is needed. # If you see many sort_merge_passes per second in SHOW GLOBAL STATUS # output, you can consider increasing the sort_buffer_size value to # speed up ORDER BY or GROUP BY operations that cannot be improved with # query optimization or improved indexing. # Commented for choosing default values. sort_buffer_size = 256K # This buffer is used for the optimization of full JOINs (JOINs without # indexes). Such JOINs are very bad for performance in most cases # anyway, but setting this variable to a large value reduces the # performance impact. See the "Select_full_join" status variable for a # count of full JOINs. Allocated per thread if full join is found # The minimum size of the buffer that is used for plain index scans, range # index scans, and joins that do not use indexes and thus perform full # table scans. # Commented for Default Value. join_buffer_size = 256K # ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| # # |||||||||||||||||||||||||||||| OTHERS ||||||||||||||||||||||||||||||| # # ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| # # back_log is the number of connections the operating system can keep in # the listen queue, before the MariaDB connection manager thread has # processed them. If you have a very high connection rate and experience # "connection refused" errors, you might need to increase this value. # Check your OS documentation for the maximum value of this parameter. # Attempting to set back_log higher than your operating system limit # will have no effect. # You need to increase this only if you expect a large number of # connections in a short period of time. back_log = 150 # Only cache result sets that are smaller than this limit. This is to # protect the query cache of a very large result set overwriting all # other query results. query_cache_limit = 2M # Minimum word length to be indexed by the full text search index. # You might wish to decrease it if you need to search for shorter words. # Note that you need to rebuild your FULLTEXT index, after you have # modified this value. ft_min_word_len = 3 # Thread stack size to use. This amount of memory is always reserved at # connection time. MariaDB itself usually needs no more than 64K of # memory, while if you use your own stack hungry UDF functions or your # OS requires more stack for some operations, you might need to set this # to a higher value. thread_stack = 297K # Set the default transaction isolation level. Levels available are: # READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE # This variable's name might change to tx_isolation in future versions # of MariaDB transaction-isolation = REPEATABLE-READ # Set the query cache type. 0 for OFF, 1 for ON and 2 for DEMAND. query_cache_type=0 # The number of table definitions (from .frm files) that can be stored # in the definition cache. If you use a large number of tables, you can # create a large table definition cache to speed up opening of tables. # The table definition cache takes less space and does not use file # descriptors, unlike the normal table cache. # The minimum and default values are both 400. table_definition_cache=1400 # If the value of this variable is greater than 0, a replication slave # synchronizes its master.info file to disk. # (using fdatasync()) after every sync_master_info events. sync_master_info=10000 # If the value of this variable is greater than 0, the MySQL server # synchronizes its relay log to disk. # (using fdatasync()) after every sync_relay_log writes to the relay log. sync_relay_log=10000 # If the value of this variable is greater than 0, a replication # slave synchronizes its relay-log.info file to disk. # (using fdatasync()) after every sync_relay_log_info transactions. sync_relay_log_info=10000 ######################################################################### ########################## REPLICATION ################################## ######################################################################### # Server Id. # Used to identify master and slave servers in replication. The server_id # must be unique for each server in the replicating group. If left at 0, # the default, a slave will not connect to a master, and a master will # refuse all slave connections. # server_id=0 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - # the syntax is: # # CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>, # MASTER_USER=<user>, MASTER_PASSWORD=<password> ; # # where you replace <host>, <user>, <password> by quoted strings and # <port> by the master's port number (3306 by default). # # Example: # # CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, # MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then # start replication for the first time (even unsuccessfully, for example # if you mistyped the password in master-password and the slave fails to # connect), the slave will create a master.info file, and any later # change in this file to the variables' values below will be ignored and # overridden by the content of the master.info file, unless you shutdown # the slave server, delete master.info and restart the slaver server. # For that reason, you may want to leave the lines below untouched # (commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host = <hostname> # # The username the slave will use for authentication when connecting # to the master - required #master-user = <username> # # The password the slave will authenticate with when connecting to # the master - required #master-password = <password> # # The port the master is listening on. # optional - defaults to 3306 #master-port = <port> # # binary logging - not required for slaves, but recommended #log-bin=mysql-bin # # binary logging format - mixed recommended #binlog_format=mixed # *** Replication related settings # Unique server identification number between 1 and 2^32-1. This value # is required for both master and slave hosts. It defaults to 1 if # "master-host" is not set, but will MariaDB will not function as a master # if it is omitted. # server-id = 1 # # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - # the syntax is: # # CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>, # MASTER_USER=<user>, MASTER_PASSWORD=<password> ; # # where you replace <host>, <user>, <password> by quoted strings and # <port> by the master's port number (3306 by default). # # Example: # # CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, # MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then # start replication for the first time (even unsuccessfully, for example # if you mistyped the password in master-password and the slave fails to # connect), the slave will create a master.info file, and any later # changes in this file to the variable values below will be ignored and # overridden by the content of the master.info file, unless you shutdown # the slave server, delete master.info and restart the slaver server. # For that reason, you may want to leave the lines below untouched # (commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host = <hostname> # # The username the slave will use for authentication when connecting # to the master - required #master-user = <username> # # The password the slave will authenticate with when connecting to # the master - required #master-password = <password> # # The port the master is listening on. # optional - defaults to 3306 #master-port = <port> # # Make the slave read-only. Only users with the SUPER privilege and the # replication slave thread will be able to modify data on it. You can # use this to ensure that no applications will accidently modify data on # the slave instead of the master #read_only ######################################################################### ######################### innodb (Skipped) ############################## ######################################################################### skip-innodb ############################################################################################################################ ############################################################################################################################ ############################################################################################################################ # MariaDB ROCKSDB Engine Server Variables Performance-Tuning (Optimized 50/50 Snappy) ############################################################################################################################ ############################################################################################################################ ############################################################################################################################ ############################################################################################################################# # rocksdb_block_size ############################################################################################################################# # NOTES: # a) I/O Unit (Not fully Aligned). # b) Default is 4KB. 16 KB gives better space savings, but needs extra CPU for decompression. # c) Measure trade-offs between 4K, 8K, 16K and 32 K. # d) Check the size of data of the block cache in DB_BLOCK_CACHE_USAGE Column of the INFORMATION_SCHEMA.ROCKSDB_DBSTATS table. rocksdb_block_size = 8192 # Default Value: 4096 (4K) ############################################################################################################################# # rocksdb_block_cache_size ############################################################################################################################# # DESCRIPTION: Block_cache size for RocksDB. # NOTES: RocksDB’s Internal Cache. Similar to innodb_buffer_pool_size, but less important since RocksDB relies on OS cache too. # Set Variable depending on objective "Commit(kB)" to be set in RAM Memory by the "mysqld" process. rocksdb_block_cache_size = 2G # Default: 512 MB # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ # Index & Filter Blocks # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ # IMPORTANT: Both Options MUST always be set to ON (1) in order to limit resources to rocksdb_block_cache_size Memory to the # objective "Commit" Load. # rocksdb_cache_index_and_filter_blocks: If you set cache_index_and_filter_blocks to true, index and filter blocks # will be stored in block cache, together with all other data blocks. # # This allows to effectively limit allocated resources. # # rocksdb_pin_l0_filter_and_index_blocks_in_cache: Minimizes performance impact of rocksdb_cache_index_and_filter_blocks = ON, # in order to effectively limit resources without a decrease in performance. rocksdb_cache_index_and_filter_blocks = 1 rocksdb_pin_l0_filter_and_index_blocks_in_cache = 1 ############################################################################################################################# # ROCKSDB WAL Configuration Settings ############################################################################################################################# # NOTES: # Control Maximum WAL Size. # Setting as large as total InnoDB Log Size would be fine. rocksdb_max_total_wal_size = 4G # Default: 0 rocksdb_wal_dir = F:/MariaDB 10.3/data # rocksdb_wal_dir: Recommended to set the WAL path on a different SSD (Whenever its possible). ############################################################################################################################# # rocksdb_max_background_jobs ############################################################################################################################# # DEFINITION: Maximum number of concurrent background jobs, including flushes and compactions. RocksDB will automatically # decide how to allocate the available job slots to flushes and compactions. # VALUE RANGE: -1 to 64. # DEFAULT: 2. # WARNING: This option must be used with MariaDB 10.3.8 as “rocksdb_max_background_compactions” # and “rocksdb_max_background_flushes” variables have been DEPRECATED. rocksdb_max_background_jobs = 36 ############################################################################################################################# # rocksdb_lock_wait_timeout ############################################################################################################################# # DESCRIPTION: Number of seconds to wait for lock. rocksdb_lock_wait_timeout = 2 # Default: 1 ############################################################################################################################# # rocksdb_max_open_files ############################################################################################################################# # NOTE: # a) Increase file descriptor limit for mysqld process (Increase nofile in /etc/security/limits.conf) # b) If setting greater than 0, RocksDB still uses table_cache, which will lock a mutex every time you access the file. # I think you'll see much greater benefit with -1 because then you will not need to go through LRUCache to get the table you # need. # # IMPORTANT: Keep all files Open! (-1) # rocksdb_max_open_files = -1 # Default Value: -1 ############################################################################################################################# # rocksdb_db_write_buffer_size ############################################################################################################################# # DESCRIPTION: This is the maximum size of all Write Buffers across all Collumn Families in the database. # It represents the amount of data to build up in memory (backed by an unsorted log on disk) across all # column families before writing to a sorted on-disk file. # # "Total size of memtables across column families". This can be used to manage the total memory used by memtables. # # This is distinct from write_buffer_size, which enforces a limit for a single memtable. # # By default this feature is disabled (by being set to 0). You should not need to change it. rocksdb_db_write_buffer_size = 4G # Default Value: 0 (Disabled). ############################################################################################################################# # Other Configurations (CF Options) ############################################################################################################################# rocksdb_table_cache_numshardbits = 6 # Default: 6. Optimal 50/50 Snappy: 6. # ----------------------------------------------------------------------------------- # Options to Enable or Disable # ----------------------------------------------------------------------------------- # NOTE: "allow_mmap_reads" cannot be used with "use_direct_reads" or "use_direct_io_for_flush_and_compaction". Otherwise Server # CRASHES. rocksdb_allow_mmap_reads = 1 # Default: 0 # Useful for SSD Disks. Do not set with Spinning Disks. rocksdb_allow_mmap_writes = 0 # Default: 0 rocksdb_use_fsync = 0 # Default: 0 rocksdb_use_adaptive_mutex = 0 # Default: 0 # ----------------------------------------------------------------------------------- # Rate Limiter # ----------------------------------------------------------------------------------- rocksdb_bytes_per_sync = 512000 # Default: 0. Optimal: 512 KB. rocksdb_wal_bytes_per_sync = 4194304 # Default: 0. Set to 4MB. rocksdb_rate_limiter_bytes_per_sec = 419430400 # Default: 0. Set to 400 MB/s. Increase if you're running on higher. # ----------------------------------------------------------------------------------- # Triggering Compaction if there are many Sequential Deletes # ----------------------------------------------------------------------------------- # NOTE: DESCRIPTION: "RocksDB will trigger compaction for the file if it has more than the specified number # of sequential deletes per window". rocksdb_compaction_sequential_deletes_count_sd=1 # Default: 0 rocksdb_compaction_sequential_deletes=199999 # Default: 0 rocksdb_compaction_sequential_deletes_window=200000 # Default: 0 # ----------------------------------------------------------------------------------- # Max Subcompactations # ----------------------------------------------------------------------------------- # DESCRIPTION: This value represents the maximum number of threads that will concurrently perform a compaction job by breaking it into multiple, # smaller ones that are run simultaneously. # DEFAULT: 1 (i.e. no subcompactions) # # NOTES: L0 to L1 compaction cannot be parallelized. In some cases, it may become a bottleneck that limit the total # compaction speed. In this case, users can set max_subcompactions to more than 1. In this case, we'll try to partition the # range and use multiple threads to execute it. # # Set up compression more aggressively and allocate more threads for flush and compaction. rocksdb_max_subcompactions = 2 # Default: 1 ############################################################################################################################# # Override cf options for RocksDB. ############################################################################################################################# rocksdb_default_cf_options="write_buffer_size=512m;max_bytes_for_level_base=2048m;target_file_size_base=256m;max_bytes_for_level_multiplier=10;max_write_buffer_number=12;min_write_buffer_number_to_merge=4;target_file_size_multiplier=1;level0_file_num_compaction_trigger=1;level0_slowdown_writes_trigger=36;level0_stop_writes_trigger=42;source_compaction_factor=2;max_grandparent_overlap_factor=10" #------------------------------------------------------------- # MOST IMPORTANT SETTINGS: Optimal 50/50 Snappy #------------------------------------------------------------- # write_buffer_size: Sets the size of a single memtable. Once memtable exceeds this size, it is marked immutable and # a new one is created.It represents the amount of data to build up in memory (backed by an unsorted # log on disk) before converting to a sorted on-disk file. The default is 64 MB. # You need to budget for 2 x your worst case memory use. If you don't have enough memory for this, # you should reduce this value. # # "You can think of memtables as in-memory write buffers. Each new key-value pair is first written # to the memtable. Memtable size is controlled by the option write_buffer_size. It's usually not a # big memory consumer. However, memtable size is inversely proportional to write amplification # -- the more memory you give to the memtable, the less the write amplification is. # # If you increase your memtable size, be sure to also increase your L1 size! L1 size is controlled by # the option *"max_bytes_for_level_base"*. # # Baseline: 256 MB. Optimized 50/50 Snappy: 512MB # # max_bytes_for_level_base: Write amplification. *"max_bytes_for_level_base"* is total size of level 1. As mentioned, we recommend # that this be around the size of level 0. Each subsequent level is *"max_bytes_for_level_multiplier"* # larger than previous one. The default is 10 and we do not recommend changing that. # It could be Set Same Value than "Cache Size". # # target_file_size_base: "Default Value is 64MB". Increasing "*target_file_size_base*" will reduce total number of database files, # which is generally a good thing. We recommend setting target_file_size_base to be # *"max_bytes_for_level_base*" / 10, so that there are 10 files in level 1". # # max_bytes_for_level_multiplier: Default: 10 (Optimal Value). # # target_file_size_multiplier: Optimal Value: 1 # # compression: Default: Snappy (Optimal). # #------------------------------------------------------------- # OTHER SETTINGS: #------------------------------------------------------------- # min_write_buffer_number_to_merge: "Minimum number of memtables to be merged before flushing to storage. # # For example: # * If this option is set to 2, immutable memtables are only flushed when there are 2 of them # - a single immutable memtable will never be flushed. # * If multiple memtables are merged together, less data may be written to storage since two updates # are merged to a single key. # # Recommended Value: 4 (Optimal 50/50 Snappy). # level0_file_num_compaction_trigger: "Number of files to trigger level-0 compaction. A value <0 means that level-0 compaction will # not be triggered by number of files at all". Default: 4. # # For example, if level0_file_num_compaction_trigger = 8 and every flushed file is 100MB. # Then as soon as there is 8 files, they are compacted to one 800MB file. And after we have 8 # new 100MB files, they are compacted in the second 800MB, and so on. Eventually we'll have a list # of 800MB files and no more than 8 100MB files. # # Optimal Value: 1. # max_write_buffer_number: "When the number of memtables waiting to flush is greater or equal to max_write_buffer_number, writes are # fully stopped to wait for flush finishes. # # The number of in-memory memtables. Each memtable is of size write_buffer_size. # # Default: 2. Recommended Value: Same as "Total Physical CPU" # level0_slowdown_writes_trigger: If level0_slowdown_writes_trigger, a limited write rate to DB is activated. # # Recommended Value: Same as "Total Physical CPU" x3 # # level0_stop_writes_trigger: If level0_stop_writes_trigger, writes to DB are stopped. # # Recommended Value: Same as "Total Physical CPU" x3.5 # # source_compaction_factor: Maximum number of bytes in all source files to be compacted in a single compaction run. # # Default: 1. Optimal 50/50 Snappy: 2 # # max_grandparent_overlap_factor: Only for level-based compactions. Default: 10. Optimal 50/50 Snappy: 10 ############################################################################################################################# # Options to Use with Spinning Disks ############################################################################################################################# # WARNING: Do not use the Options in this Section ("rocksdb_compaction_readahead_size", "rocksdb_use_direct_reads", # "rocksdb_use_direct_io_for_flush_and_compaction"), as they do not work with "rocksdb_allow_mmap_reads", resulting # in Server's Data Corruption. # Throughput gap between random read vs. sequential read is much higher in spinning disks. Suggestions: #--------------------------------------------------------- # Compaction Readahead Size #--------------------------------------------------------- #If non-zero, we perform bigger reads when doing compaction. If you're running RocksDB on spinning disks, you should set this to # at least 2MB (e.g: 16MB). We enforce it to be 2MB if you don't set it with direct I/O. rocksdb_compaction_readahead_size=0 # Default: 0 #--------------------------------------------------------- # Direct Reads #--------------------------------------------------------- # NOTE: * "allow_mmap_reads" cannot be used with "use_direct_reads" or "use_direct_io_for_flush_and_compaction" # * "allow_mmap_writes" cannot be used with "use_direct_io_for_flush_and_compaction", i.e., they cannot be set to true at # the same time. rocksdb_use_direct_reads=0 # Default: 0 rocksdb_use_direct_io_for_flush_and_compaction=0 # Default: 0 ############################################################################################################################# # Data Loading: Bulk Load ############################################################################################################################# # DESCRIPTION: Commit rows implicitly every rocksdb_bulk_load_size, on bulk load/insert, update and delete. # NOTE: Session Variables. rocksdb_commit_in_the_middle = 1 # Default: 0 # Maximum number of locks a transaction can have: rocksdb_max_row_locks = 1048576 ############################################################################################################################# # MyRocks Crash Recovery ############################################################################################################################# # MyRocks supports crash recovery. Crash recovery is done by replaying transaction logs from WAL (Write Ahead Log) files. # MyRocks has a system variable rocksdb_wal_recovery_mode to control how to apply logs, if any of the WAL entries is corrupted. # This variable can be any of the following options: # # 0: If a corrupted WAL entry is detected as the last entry in the WAL, truncate the entry and start up normally; otherwise, # refuse to start. # # 1 (default): If a corrupted WAL entry is detected, fail to start. This is the most conservative recovery mode. # # 2: If a corrupted WAL entry is detected in the middle of the WAL, truncate all of WAL entries after that (even though there # may be uncorrupted entries) and then start up normally. For Replication Slaves, this option is fine, since the slave # instance can recover any lost data from the master without breaking consistency. For Replication Masters, this option # may end up losing data if you do not run failover. For example, if the master crashed and was restarted (by mysqld_safe, # or auto restart after OS reboot) with this mode and it silently truncated several WAL entries, the master would lose some # data which may be present on one or more slaves. # # 3: If a corrupted WAL entry is detected in the middle of the WAL, skip the WAL entry and continue to apply as many healthy WAL # entries as possible. This is the most dangerous recovery option and it is not generally recommended. rocksdb_wal_recovery_mode = 1 # Default: 1 ######################################################################### ###### myisam (Performance_Schema Variables: Joins not necessary) ####### ######################################################################### # If set, external locking for MyISAM tables is disabled. # skip_external_locking = 1 # Size of the Key Buffer, used to cache index blocks for MyISAM tables. # Do not set it larger than 30% of your available memory, as some memory # is also required by the OS to cache rows. Even if you're not using # MyISAM tables, you should still set it to 8-64M as it will also be # used for internal temporary disk tables. # If you don't use MyISAM tables explicitly you can set key_buffer_size # to a very low value, 64K for example. # 32 [GB] (Tornado24 Half RAM)· 0,25 = 8[GB] key_buffer_size = 64K # This buffer is allocated when MariaDB needs to rebuild the index in # REPAIR, OPTIMIZE, ALTER table statements as well as in LOAD DATA INFILE # into an empty table. It is allocated per thread so be careful with # large settings. # myisam_sort_buffer_size = 128M # The maximum size of the temporary file MariaDB is allowed to use while # recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE. # If the file-size would be bigger than this, the index will be created # through the key cache (which is slower). # myisam_max_sort_file_size = 50G # If a table has more than one index, MyISAM can use more than one # thread to repair them by sorting in parallel. This makes sense if you # have multiple CPUs and plenty of memory. # myisam_repair_threads = 6 # Automatically check and repair not properly closed MyISAM tables. # myisam_recover_options = FORCE,BACKUP # When reading rows in sorted order after a sort, the rows are read # through this buffer to avoid disk seeks. You can improve ORDER BY # performance a lot, if set this to a high value. # Allocated per thread, when needed. # read_rnd_buffer_size = 265K ######################################################################### ################# MyISAM & Aria System Variables ######################## ######################################################################### # Values in [MB] range turn out into a performance decrease # Size of the buffer used for doing full table scans. # Allocated per thread, if a full scan is needed. read_buffer_size = 256K # MyISAM uses special tree-like cache to make bulk inserts (that is, # INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA # INFILE) faster. This variable limits the size of the cache tree in # bytes per thread. Setting it to 0 will disable this optimisation. Do # not set it larger than "key_buffer_size" for optimal performance. # This buffer is allocated when a bulk insert is detected. # Values of 1/4 key_buffer_size make sense (Percona). It is per connection, # so a 1/16 ratio is choosen in order not to use excesive resources: # 8[GB] (key_buffer_size) / 16 = 512[MB] (bulk_insert_buffer_size) # Same value has been choosen as tmp_table_size (For those cases in which # Temporary Tables exceed 512M and use MyISAM instead of Memory Engine). # MyISAM uses a special tree-like cache to make bulk # inserts faster for INSERT ... SELECT, INSERT ... # VALUES (...), (...), ..., and LOAD DATA INFILE when # adding data to NON-EMPTY TABLES. # limits the size of the cache tree in bytes per thread. bulk_insert_buffer_size=256M ######################################################################### ############# Aria System Variables (Disk Temporary Tables) ############# ######################################################################### # The maximum size of the temporary file MariaDB is allowed to use while # recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE. # If the file-size would be bigger than this, the index will be created # through the key cache (which is slower). aria_max_sort_file_size = 100G # Size of the Key Buffer, used to cache index blocks for Aria tables. # Do not set it larger than 30% of your available memory, as some memory # is also required by the OS to cache rows. Even if you're not using # Aria tables, you should still set it to 8-64M as it will also be # used for internal temporary disk tables. aria_pagecache_buffer_size = 4G # If a table has more than one index, MyISAM can use more than one # thread to repair them by sorting in parallel. This makes sense if you # have multiple CPUs and plenty of memory. aria_repair_threads = 49 # This buffer is allocated when MariaDB needs to rebuild the index in # REPAIR, OPTIMIZE, ALTER table statements as well as in LOAD DATA INFILE # into an empty table. It is allocated per thread so be careful with # large settings. aria_sort_buffer_size = 128M # Automatically check and repair not properly closed MyISAM tables. # In MariaDB 10.3.0 aria_recover is renamed to aria_recover_options. aria_recover_options = FORCE,BACKUP # Path to the directory where to store transactional log: aria_log_dir_path=C:/Program Files/MariaDB 10.3/data ######################################################################### ########################### mysqldump ################################### ######################################################################### [mysqldump] # Do not buffer the whole result set in memory before writing it to # file. Required for dumping very large tables quick quick # The maximum size of one packet or any generated or intermediate string, # or any parameter sent by the mysql_stmt_send_long_data() C API function. max_allowed_packet = 16M ######################################################################### ############################# mysql ##################################### ######################################################################### [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL # sql_safe_updates = 0 #local_infile = 1 ######################################################################### ############################## mysqld_safe ############################## ######################################################################### # ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| # # |||||||||||||||||||||||| CACHES AND LIMITS |||||||||||||||||||||||||| # # ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| # [mysqld_safe] # Increase the amount of open files allowed per process. Warning: Make # sure you have set the global system limit high enough! The high value # is required for a large number of opened tables # Changes the number of file descriptors available to mysqld. # You should try increasing the value of this option if mysqld gives you # the error "Too many open files". # Default Value: Autosized # open_files_limit = 65535 # Number of table definitions that can be cached. table_definition_cache = 4096 ######################################################################### ############################## myisamchk ################################ ######################################################################### [myisamchk] key_buffer_size = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M ######################################################################### ############################## mysqlhotcopy ############################# ######################################################################### [mysqlhotcopy] interactive-timeout ######################################################################### ############################ mysqld ##################################### ######################################################################### [mysqld] character-set-server = latin1 collation-server = latin1_spanish_ci #local_infile = 1 [client-server] #local_infile = 1

2 4

[Maria-discuss] Fulltext in Galera - Index is corrupt?
by Antonio García Domínguez 17 Sep '18

17 Sep '18

Hello everyone, We are evaluating various approaches for horizontal scalabilty of fulltext searching, and we thought about trying out Galera. We set up a 3-node cluster in Google Cloud Compute, and we tried loading a collection of 90k synthetic documents into a table like this one: CREATE TABLE `documents` ( `id` int(11) NOT NULL AUTO_INCREMENT, `filename` varchar(255) DEFAULT NULL, `text` longtext DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=282232 DEFAULT CHARSET=latin1 We followed the recommendation in the MariaDB docs of inserting everything, then creating the FULLTEXT index by running: ALTER TABLE documents ADD FULLTEXT (text); While this works fine with a regular single node configuration (without Galera), it is crashing in our Galera setup with a message like this: Exception in thread "main" java.sql.SQLException: (conn=41) Index for table 'documents' is corrupt; try to repair it at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:198) at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:110) at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:228) at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:334) at org.mariadb.jdbc.MariaDbStatement.executeUpdate(MariaDbStatement.java:520) at uk.ac.aston.erdf.mariadb.MariaDBIndexer.indexFiles(MariaDBIndexer.java:211) at uk.ac.aston.erdf.mariadb.MariaDBIndexer.runIteration(MariaDBIndexer.java:142) at uk.ac.aston.erdf.mariadb.MariaDBIndexer.run(MariaDBIndexer.java:117) at uk.ac.aston.erdf.mariadb.MariaDBIndexer.main(MariaDBIndexer.java:106) Caused by: java.sql.SQLException: Index for table 'documents' is corrupt; try to repair it Query is: ALTER TABLE documents ADD FULLTEXT (text); at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:119) at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:200) at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:328) ... 5 more We have tried dropping and recreating the database, and SHOW STATUS lists the 3 nodes just fine: MariaDB [(none)]> show status like 'wsrep_cluster%'; +--------------------------+--------------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------------+ | wsrep_cluster_conf_id | 12 | | wsrep_cluster_size | 3 | | wsrep_cluster_state_uuid | 258a45dd-b9e0-11e8-bdd1-e260a7908d0f | | wsrep_cluster_status | Primary | +--------------------------+--------------------------------------+ 4 rows in set (0.001 sec) Our Galera section is pretty bog standard: we followed most of the defaults in the Ubuntu MariaDB 10.3 packages, except for disabling bin logs (they take up too much space for our 40GB cloud VMs). Here is our [galera] section: [galera] # Mandatory settings wsrep_on=ON wsrep_provider=/usr/lib/libgalera_smm.so wsrep_cluster_address=gcomm://mariadb-g1,mariadb-g2,mariadb-g3 binlog_format=row default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 # # Allow server to accept connections on all interfaces. # bind-address=0.0.0.0 # # Optional setting #wsrep_slave_threads=1 innodb_flush_log_at_trx_commit=0 Are we missing something obvious, or is InnoDB FULLTEXT not yet fully supported on Galera? Should we try Mroonga instead? Thank you for your help, Antonio

1 1

[Maria-discuss] How does InnoDB delete rows?
by Arlindo Neto 13 Sep '18

13 Sep '18

Hi everyone. I have some questions regarding InnoDB deletion process. As far as I know, when we issue a DELETE command to a row stored in a InnoDB table, the row contents are stored in the UNDO Log, and the contents of the row are only deleted when the last transaction after the DELETE transaction has finished. But what actually happens to the row that was deleted? Is any disk space ever released to the OS, or are the contents simply erased? Defragmenting a table is only done through OPTIMIZE TABLE, or are there any automatic threads responsible for rearranging the rows? And if the are no such threads, how do we maintain tables which suffer from many deletions? I hope I made myself clear. Thanks in advance, Arlindo Neto.

5 7

[Maria-discuss] dump optimization on update
by Roberto Spadim 12 Sep '18

12 Sep '18

Hi guys, i'm checking a dump optimization, probably a problem: EXPLAIN SELECT * FROM table WHERE pk1=1001 AND pk2='v' AND pk3=19808671980883 pk3 = bigint, pk1=integer, pkg2=enum('v','i','c') this return "Impossible WHERE noticed after reading const tables", my max(pk3) = 1923425, pk1 and pk2 exists but: EXPLAIN UPDATE table SET field1=1234 WHERE pk1=1001 AND pk2='v' AND pk3=19808671980883 shows: index=PRIMARY - rows = 1.055.901 - Using where maybe update didn't check max value? it's a INNODB engine this execute a full table read, and slowquery # Query_time: 15.440127 Lock_time: 0.000079 Rows_sent: 0 Rows_examined: 1.914.183 (more than table rows) -- thanks

1 0

[Maria-discuss] Aria Crash
by Roberto Spadim 30 Aug '18

30 Aug '18

Hi guys I was using mariadb and database crashed recovering give me this error and crash any idea what could be done? mariadb 10.1.5 recovered pages: 0% 3631826282% 0%180830 12:01:21 [ERROR] mysqld got signal 11 ; -- Roberto Spadim

1 2

[Maria-discuss] Publish / Subscribe
by Roberto Spadim 30 Aug '18

30 Aug '18

Hi guys, we have nosql connections with memcache/handlersocket Could be possible integrate a publish subscribe system? I'm thinking about something like redis infrastructure, there's some jira related MDEV https://jira.mariadb.org/browse/MDEV-5263?jql=text%20~%20%22publish%22 any interest or ideas in this area? -- Roberto Spadim

1 0

[Maria-discuss] mariadb CONNECT XML table type question
by Björn Hayer 29 Aug '18

29 Aug '18

Hi, I am using CONNECT for some XML files, but lately came to a problem I was unable to solve so far. The XML has the following structure: <PhysicalLocation> <Name>Address</Name> <Area type="countrycode"> <Value>DE</Value> </Area> <Area type="postalcode"> <Value>70173</Value> </Area> <Area type="municipality"> <Value>Stuttgart</Value> </Area> </PhysicalLocation> Is there a way to get a column for the value in e.g. area type=„countrycode"? Any help is very much appreciated! Thanks! Björn

1 0

[Maria-discuss] Looking for mariadb-plugin-aws-key-management package on Ubuntu 18.04
by James Colannino 27 Aug '18

27 Aug '18

Hi everyone, I'm new to the list. Please forgive me if this is a dumb question, but I've been following this document: https://mariadb.com/kb/en/library/aws-key-management-encryption-plugin-setu… I'm using Ubuntu 18.04, and after adding the MariaDB repos and installing MariaDB, I get to the point where I need to install the mariadb-plugin-aws-key-management package and am told that this package isn't found. I see that other plugin packages exist, but not this one. I've done a ton of Googling, and the only clue I've been able to find is that an old cached package list of a mirror used to have this package, while the current package list for the same mirror doesn't. Was this package removed at some point? If so, I can't find any documentation online that says this occurred. I'm really confused and frustrated and hoping to get this plugin installed. Thanks in advance! :)

1 1

[Maria-discuss] Is disabling doublewrite safe on ZFS?
by Gionatan Danti 25 Aug '18

25 Aug '18

Hi all, as by subject: is disabling doublewrite safe on ZFS (and/or other CoW filesystems as BTRFS)? Background information: ZFS is a CoW/transactional filesystem, meaning that writes are atomic: they fully commit or are rolled backup to latest "stable" version. This lend many peoples to claim not only that disabling doublewrite is safe when InnoDB runs on top of a ZFS storage, but even that it is the *right* thing to do for increase InnoDB write performance. The reason is that when ZFS recordsize is set the same as InnoDB page/record size, no partial page write can happen. Some evidence: http://assets.en.oreilly.com/1/event/21/Optimizing%20MySQL%20Performance%20… However, I am not fully committed (pun intended!) to this idea. While I surely appreciate ZFS write atomicity, and how it *does* protect from system-wide crash (ie: powerloss), I fear that an InnoDB/MariaDB crash *can* lead to partial page writes. If, for example, the mysqld process crashes (or it is killed) when copying an internal buffer during a write() call, I can imagine the filesystem will receive wrong/partial data, which it will happily write to the main storage pool (as it know nothing of internal data consistency from InnoDB point of view). I understand that this failure scenario should be *really* rare, as the critical operation (buffer copy from mysqld to system pagecache/ARC via write()) is extremely fast compared to the real data flush to stable storage (meaning that the "vulnerable time window" is very small). However, it remain different from 100% safety. Moreover, it really backfired in the past: https://www.percona.com/blog/2015/06/17/update-on-the-innodb-double-write-b… From my understanding, disabling doublebuffer is really 100% safe only when enabling atomic writes on *a supported hardware* (https://mariadb.com/kb/en/library/atomic-write-support/) Am I missing something? Am I over-thinking it, maybe? Thanks. -- Danti Gionatan Supporto Tecnico Assyoma S.r.l. - www.assyoma.it email: g.danti(a)assyoma.it - info(a)assyoma.it GPG public key ID: FF5F32A8

5 15

[Maria-discuss] Fwd: CVE list for connectors
by Michal Schorm 21 Aug '18

21 Aug '18

Hello, On this page: https://mariadb.com/kb/en/library/security/ I can find CVEs that affect MariaDB server and releases they were fixed in. Other useful page is this one: https://mariadb.com/kb/en/library/security-vulnerabilities-in-oracle- mysql-that-did-not-exist-in-mariadb/ -- I'd like to ask you to also list CVEs applicable for connectors and versions of connectors (CONC, CONJ, ODBC) they were fixed in, since there is not direct relationship between: 1) server nad CONC 2) ODBC and CONC Server is (or atleast should be) built on top of released version of the CONC, not the latest Git content. Atleast downstreams starts to use this scheme (separate server and client library - the CONC), so they depend only on the released versions of the CONC. -- For example CVE-2018-3081 should be fixed in CONC 3.0.5, however since nor git commit, nor CONC release notes says it explicitly, I can only guess. -- Michal Schorm Associate Software Engineer Core Services - Databases Team Red Hat

1 0

[Maria-discuss] Drop user not replicating in 10.2.16/17 galera cluster with NO_AUTO_CREATE_USER in mysql_mode
by Vincent Hoffman-Kazlauskas 15 Aug '18

15 Aug '18

Hi all, not sure if this is expected or not. I am running a test cluster in preparation for upgrading from 10.0 to 10.2 and noticed that DROP user commands were not being propagated to other members of the galera cluster. The only user management changes of note I know of was the addition of NO_AUTO_CREATE_USER to sql_mode and indeed removing that from sql_mode appears to have resolved it. My question is, is that expected behavior and if so whats the modern way of removing mysql users from a mariadb galera cluster. If not is it worthy of a bug report? Thanks, Vince

1 0

Re: [Maria-discuss] [EXTERNAL] Re: Copying database files
by Reindl Harald 14 Aug '18

14 Aug '18

Am 13.08.2018 um 11:57 schrieb Ling, Andy: >>> I'm using MariaDB 10.2.13 on Windows 7. All tables are using the Aria >>> engine. >>> >>> I am trying to initialise a slave database by copying the files between >>> PCs, but when I try and use the copied files I get the error >>> >>> Table is from another system and must be zerofilled or repaired to be >>> usable on this system >>> >>> Repairing the tables fixes this, but I'd rather not have to so that. >>> >>> I have tried various things suggested to stop this, but nothing seems to >>> help. On the source server I have run aria_chk -zerofill on all the >>> tables. Before copying the tables I run FLUSH TABLES <table list> FOR >>> EXPORT, but I still get the error. >> jesus don't copy tbale files around while the server is running >> no idea why you repsond off-list > Does that mean this is not valid then... did you read and understand it > https://mariadb.com/kb/en/library/copying-tables-between-different-mariadb-… how make you sure "The server is not accessing the tables during the copy process" "The advantage of FLUSH TABLES table_name FOR EXPORT is that the table is read locked until UNLOCK TABLES is executed" is not really true because that's only valid for the running connection "Warning: If you do the above live copy, you are doing this on your own risk" should be clear in short: nobody right in his mind copies database tables hot

3 9

[Maria-discuss] New testsuite option
by Michal Schorm 13 Aug '18

13 Aug '18

Hello, I take care of MariaDB in Fedora & RHEL. We run the testsuite there. Every time, in every build, with --big-test. Time to time it happens that some of the tests fail. For example during a rebuild. For such cases, I'd use a way to tell MTR what is a unstable test treshold, which means "how many tests can fail to still count the testsuite run as a success". Most likely, I'd use it with value from 1-5. Any larger group of failing tests needs to get my attention. Anyway I still could spare myself of the unstable tests. -- (Asking before filling any JIRA feature request) 1) Is there already some way to do that? 2) Do you find such feature usefull? 3) Any ideas how to call such option? --max-test-fail is already used -- Michal Schorm Associate Software Engineer Core Services - Databases Team Red Hat

2 3

[Maria-discuss] Copying database files
by Ling, Andy 13 Aug '18

13 Aug '18

I'm using MariaDB 10.2.13 on Windows 7. All tables are using the Aria engine. I am trying to initialise a slave database by copying the files between PCs, but when I try and use the copied files I get the error Table is from another system and must be zerofilled or repaired to be usable on this system Repairing the tables fixes this, but I'd rather not have to so that. I have tried various things suggested to stop this, but nothing seems to help. On the source server I have run aria_chk -zerofill on all the tables. Before copying the tables I run FLUSH TABLES <table list> FOR EXPORT, but I still get the error. What do I need to do to make this work? Thanks for any help. Andy Ling ********************************************************************** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You.

2 1

[Maria-discuss] Best way to fetch user’s password hash?
by Felipe Gasper 10 Aug '18

10 Aug '18

Hi all, What is the best way to fetch a user’s password hash? I’d ideally like something that works in both MySQL and MariaDB, as many versions as possible. Thank you! -Felipe Gasper Mississauga, Ontario

2 2

[Maria-discuss] Disable Stric Mode MariaDB 10.4
by Wilmer Arambula 04 Aug '18

04 Aug '18

Hi, i need disable strict mode Maria DB 10.4, Thks, Wilmer.

2 1

[Maria-discuss] MariaDB Zulip
by Ian Gilfillan 04 Aug '18

04 Aug '18

A reminder that MariaDB is trialling Zulip, which provides both Slack-like and IRC-like functionality. Zulip is an open-source tool that can also provide native integrations with various other tools, including GitHub. At the moment the MariaDB Foundation has been using it internally for most communications, and there's not been much public activity, but we'd like to invite developers and others interested in MariaDB to come try it out as well. So far the responses from those using it have been positive. Visit it at https://mariadb.zulipchat.com.

1 0

[Maria-discuss] How to get rid of foreign keys to non existant temporary tables?
by Bernhard Weller 30 Jul '18

30 Jul '18

Hello, I have a server running MariaDB 10.1.29-MariaDB-6+b1 (on Debian). I recently tried upgrading a concrete5 installation from 8.3.2 to 8.4.1 which failed running a SQL statement: ALTER TABLE AreaLayoutsUsingPresets ADD CONSTRAINT FK_7A9049A1385521EA FOREIGN KEY (arLayoutID) REFERENCES AreaLayouts (arLayoutID) ON UPDATE CASCADE ON DELETE CASCADE With an error: SQLSTATE[HY000]: General error: 1005 Can't create table `concrete5`.`#sql-215_264a4` (errno: 121 "Duplicate key on write or update") Now after investigating that command should probably be done with foreign key checks disabled - but I'm not sure on that one. The problem which I'm having now is, that I have two foreign key entries in INNODB_SYS_FOREIGN which are for temporary tables which don't exist, so I have no idea how to remove them: ID FOR_NAME REF_NAME N_COLS TYPE concrete5/FK_3322FA75FD71026C concrete5/#sql-2a01_48f4 concrete5/Users 1 1 concrete5/FK_7A9049A1385521EA concrete5/#sql-215_26264 concrete5/AreaLayouts 1 5 I've tried creating a table with the corresponding name (#mysql50##sql-2a01_48f4) and referenced colums (uID, int(10)) for the first one. But executing ALTER TABLE `#mysql50##sql-2a01_48f4` DROP FOREIGN KEY FK_3322FA75FD71026C Results in error #1091. I'm a bit lost now - the update will certainly not work while these foreign keys are in the system blocking the unique name. Any ideas? Bernhard Weller

1 0

[Maria-discuss] Galera partitioning question
by Ryan Delgrosso 29 Jul '18

29 Jul '18

Greetings all, I am running MariaDB 10.2.16 on CentOS in AWS and am seeing a sporadic cluster partitioning and rejoining issue with seemingly no explicable cause. * I have elements in 3 different AWS availability zones in a single galera cluster * Monitoring logs I see this message: /Jul 29 05:33:53 server01 mysqld: 2018-07-29 5:33:53 139633883080448 [Note] WSREP: (eabb848a, 'tcp://0.0.0.0:4567') connection to peer 392b9516 with addr tcp://172.31.17.60:4567 timed out, no messages seen in PT3S/ * I have tried forcing a 1500byte MTU as some others sources mentioned jumbo framing could negatively impact galera replication. * Running prolonged packet captures between nodes i cannot seem to find anything else wrong, network connectivity isn't interrupted and no service restarts occur. * These partition events happen multiple times per day. Has anyone seem this sporadic cluster disconnect and re-join issue in a similar env? I did not previously note this behavior on 10.1. Any help is much appreciated. -Ryan

1 0

[Maria-discuss] Mysql replication into galera cluster Errors
by Ton Wittenberg 27 Jul '18

27 Jul '18

Hello All, currently i have one master ( Mariadb 10.0.15) most of oure databases and tables are still myisam. i made a galera cluster with 3 nodes on mariadb 10.3.8 i made a dump the master, imported it into galera cluster, and mysql replication is running but im encoutering Alot of errors: Jul 28 00:30:32 db100 mysqld[7615]: 2018-07-28 0:30:32 3807 [Warning] WSREP: SQL statement was ineffective thd: 3807 buf: 401 Jul 28 00:30:32 db100 mysqld[7615]: schema: mytable Jul 28 00:30:32 db100 mysqld[7615]: QUERY: COMMIT Jul 28 00:30:32 db100 mysqld[7615]: => Skipping replication the idea is to discontinue the 10.0.15 machine and replace it with a 3 node galera cluster. and to setup mysql replication into galera. then start inserting in galera. and then shutdown the old master to provide a smooth migration. what could this be or how can i further debug this and find out why i receive this errors? Kind regards Ton Wittenberg

1 0

[Maria-discuss] community contribution measurement page
by Daniel Black 22 Jul '18

22 Jul '18

Folks, I've prepared a community contribution measurement page: http://measure-mariadb.ozlabs.org/repo/mariadb/server-outside-org.html This is largely the code base of https://github.com/MeasureOSS/Measure known errors: * I've yet to create the organisation aspects of this site. * some top page may be distorted by non-mariadb respositories Insights and suggested improvements welcome (especially with code) :-)

3 4

[Maria-discuss] MariaDB 10.3.8 does not install on Debian 8 and 9?
by Vimal Kumar V. 19 Jul '18

19 Jul '18

Dear Friends, I am trying to install MariaDB 10.3.8 on Debian 8&9. When I try to add following repo key, apt-key adv --recv-keys --keyserver SKS key server <http://keyserver.ubuntu.com/> 0xF1656F24C74CD1D8 I got following error message, Executing: /tmp/apt-key-gpghome.YltUOLRxyc/gpg.1.sh --recv-keys --keyserver SKS key server <http://keyserver.ubuntu.com/> 0xF1656F24C74CD1D8 gpg: keyserver receive failed: Server indicated a failure Please guide me to how to bypass the problem. -- Vimal Kumar V. Technical Assistant Mahatma Gandhi University Kottayam, Kerala, India-686 560 My Home Page: http://www.vimalkumar.info My Blogs: http://linuxhalwa.blogspot.com http://kohageek.blogspot.in http://dspacegeek.blogspot.in http://moovandan.blogspot.in --------------------------------------------------------------------------- "I forget what I was taught. I only remember what I have learnt" -Patrick White

2 1

Re: [Maria-discuss] Replication Problem
by Kristian Nielsen 05 Jul '18

05 Jul '18

Thomas Plant <thomas(a)plant.systems> writes: > Your tip with 'MASTER_USE_GTID=slave_pos' fixed it.Will have to look > better in the documentation next time, never found the 'slave_pos' > mentioned or 'SET sql_log_bin=0'. > > Thank you very much for your help. Welcome, glad that you solved your problem. The slave_pos/current_pos is confusing a lot of users. It would probably have been better if current_pos had never been introduced. > so you mean that disabling the binary log on the slave would be > indicated? Can I do it while it is online? SET sql_log_bin=0 disables binlogging only for the following queries done in that connection (not in general). So yes, it can be done online. You would do it for queries that you will not want replicated to other servers. For example, if you later make this slave the master, and put the old master to replicate from the old slave (now new master), you probably do not want to replicate your earlier slave-fixup-query. Hence the suggestion to SET sql_log_bin=0 to avoid having this query in the slave binlog. But if using MASTER_USE_GTID=slave_pos, in most cases it won't matter one way or the other. - Kristian.

1 0

[Maria-discuss] Replication Problem
by Thomas Plant 04 Jul '18

04 Jul '18

Hello, I'd have a question about MariaDB 10.1 replication, which has been interrupted by a power outage in our datacenter. We started the master server and had a duplicate id in a table on the slave, so replication stopped. We did not have time to adjust this at the moment, there were a lot of other systems to start and check if all was alright. Today I had time to look at the error, removed the duplicate ID from the table and started the slave thread again using 'start slave;'. But now I get another error: Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Error: connecting slave requested to start from GTID 0-2-2948175468, which is not in the master's binlog. Since the master's binlog contains GTIDs with higher sequence numbers, it probably means that the slave has diverged due to executing extra erroneous transactions' Can this be resolved? Or will I have to start replication from scratch? Greetings, Thomas

5 4

[Maria-discuss] MariaDB embedded, prepared statements
by Julien Muchembled 02 Jul '18

02 Jul '18

Hello, In our application (NEO - https://neo.nexedi.com/ ), we have 2 backends: SQLite and MySQL/MariaDB We also have a benchmarking tool working with a quite small dataset and showing that SQLite is ~30% than MariaDB. For years, we supposed that SQLite performed better in some cases for 2 reasons: - everything processed in same thread (i.e. no socket or context switches) - precompiled queries And by combining both, skip any kind of string serialization (i.e. do not format a query on one side, and parse on the other side). Now, we are trying to optimize our MariaDB backend by trying to do like SQLite. = MariaDB embedded I could make it work, but only with InnoDB for the moment. I kept using the same binding, MySQLdb, which I rebuilt against libmariadbd-dev_10.1.29-6+b1_amd64.deb Currently, we only use TokuDB in production and I have the same issue as: https://forums.mysql.com/read.php?94,629158,629158 ("embedded server cannot accept a plugin engine") This user solved it by embedding his engine inside libmysqld. I wonder if it will work with TokuDB. Anyway, I haven't seen any speed improvement. = Prepared statements I couldn't find anything else that's closer to SQLite precompiled queries. Which means that there's still some formatting/parsing. | Com_stmt_execute | 96528 | | Com_stmt_prepare | 40 | But again, not faster. Well, I do my best to have stable results, like having the DB in tmpfs or disabling C-states, but it's hard to measure small changes of performance. With 10.1.29, prepared statements look slightly slower. I also tried the new EXECUTE syntax with 10.2.16 to avoid SET queries, and it may be 1% faster (< error margin). I only turned the most often used queries into prepared statements. Here is one of the most complex queries we have: SELECT tid, compression, data.hash, value, value_tid FROM obj FORCE INDEX(PRIMARY) LEFT JOIN data ON (obj.data_id = data.id) WHERE `partition`=? AND oid=? AND tid < ? ORDER BY tid DESC LIMIT 1 https://mariadb.com/kb/en/library/prepared-statements/ should explain the purpose of prepared statements. The "In addition to using prepared statements from the libmysqld" clause seems to tell there's a specific kind of prepared statements within MariaDB embedded but I haven't found anything about that. We're disappointed by all these results. Did I miss something ? If not, I'll have to do some profiling of MariaDB. Julien

1 0

Re: [Maria-discuss] [MariaDB Announce] MariaDB 10.2.16 now available
by Reindl Harald 27 Jun '18

27 Jun '18

is there any QA on your side? mysql-test-run: *** ERROR: Could not find any of /usr/sql/mysql_tzinfo_to_sql /usr/bin/mysql_tzinfo_to_sql /usr/sql/mysql_tzinfo_to_sql where the hell is "mysql_tzinfo_to_sql" gone? that it's here in "mariadb-manpages" is a packaging detail but in 10.2.16 it seems to be missing completly [root@testserver:~]$ rpm -q --file /usr/bin/mysql_tzinfo_to_sql mariadb-manpages-10.2.15-1.0.fc27.20180613.rh.sandybridge.x86_64 Am 26.06.2018 um 17:45 schrieb MariaDB Announce List: > The MariaDB project is pleased to announce the availability > of MariaDB 10.2.16, the latest stable release in the > MariaDB 10.2 series. > > See the Release Notes and Changelogs for details. > > > - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - > > MariaDB 10.2.16 > - Release Notes: https://mariadb.com/kb/en/mdb-10216-rn/ > - Changelog: https://mariadb.com/kb/en/mdb-10216-cl/ > - Downloads: https://downloads.mariadb.org/mariadb/10.2.16 > > About MariaDB 10.2: > - https://mariadb.com/kb/en/what-is-mariadb-102/ > > APT and YUM Repository Configuration Generator: > - https://downloads.mariadb.org/mariadb/repositories/ > > > - - MariaDB Books - - - - - - - - - - - - - - - - - - - - - - - - - - > > There is an ever-growing library of MariaDB books available to help > you get the most out of MariaDB. See the MariaDB Books page for > details and links: > > - https://mariadb.com/kb/en/mariadb/books/ > > > - - User Feedback plugin - - - - - - - - - - - - - - - - - - - - - - - > > MariaDB includes a User Feedback plugin. This plugin is disabled by > default. If enabled, it submits basic, completely anonymous MariaDB > usage information. This information is used by the developers to > track trends in MariaDB usage to better guide development efforts. > > If you would like to help make MariaDB better, please add > "feedback=ON" to your my.cnf or my.ini file! > > See http://mariadb.com/kb/en/user-feedback-plugin for more > information. > > > - - Quality - - - - - - - - - - - - - - - - - - - - - - - - - - - - - > > The project always strives for quality, but in reality, nothing is > perfect. Please take time to report any issues you encounter at: > > - http://jira.mariadb.org > > > - - Support MariaDB - - - - - - - - - - - - - - - - - - - - - - - - - > > If you would like to contribute to the MariaDB Foundation, please see > the "contributing" and "donations" pages. We also have merchandise > available in a cafepress store. All proceeds go to support the > MariaDB Foundation. > > - https://mariadb.com/kb/en/contributing > > - https://mariadb.org/donate/ > > > We hope you enjoy MariaDB!

2 3

[Maria-discuss] MariaDB crashes because of "long semaphore wait" after migrating from 10.1 to 10.3
by TD 22 Jun '18

22 Jun '18

Hi, I have a Windows server that has been running MariaDB 10.1 successfully for over a year. The server remains mostly idle for long times with some read access, but occasionally there are transactions that add data (about 500k rows per commit). There can be up to 10 such transactions (one per database) at the same time and during those times the server is under quite some load (the code processing the data resides on the same server as the database). We're using an 8 core AMD Ryzen, 32 GB RAM and a Samsung SSD. Like I said, this worked fine with MariaDB 10.1 all the time. We're currently in the process of setting up a new server (specs similar to the ones above) and because MariaDB 10.3 has been released recently, I was curious and installed it. The database now constantly shuts down because of "[FATAL] InnoDB: Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung." whenever there is one of the load situations described above. If I install MariaDB 10.1 on the new server, it runs as fine as the old one. The crashes also happen when I start with a clean database (no data migrated from 10.1 databases). I'm not a huge MariaDB config pro, as it's only a tool to store the data, it just needs to run with reasonable performance and has always worked until now. As far as I know and can judge from comments in the file, the my.ini in use is (mostly?) based on my-innodb-heavy-4G.cnf, but coworkers might have made modifications. I've attached the contents below. I don't know how I can debug this issue further. Windows event viewer contains several log warnings with the text "InnoDB: A long semaphore wait:" but no further information why this wait happens. I couldn't find any other error logs. I guess it is some configuration error (I read about MariaDB switching from XtraDB to InnoDB, so I guess that the issue might be related). Migrating to 10.3 is not an absolute must, but it would be nice to be able to fix the problem. Any help would be greatly appreciated and if you need more information, I'll gladly try to provide it. Thanks, Tom. my.ini: [mysqld] max_connections = 100 max_allowed_packet = 16M binlog_cache_size = 1M max_heap_table_size = 64M read_buffer_size = 2M read_rnd_buffer_size = 16M sort_buffer_size = 8M join_buffer_size = 8M thread_cache_size = 10 query_cache_size = 64M query_cache_limit = 2M thread_stack = 240K tmp_table_size = 64M slow_query_log expire_logs_days = 14 log-bin = mysql-bin character-set-server=utf8 collation-server=utf8_general_ci #*** MyISAM Specific options key_buffer_size = 32M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover # *** INNODB Specific options *** #innodb_fatal_semaphore_wait_threshold = 6000 #tried that, didn't help #innodb_adaptive_hash_index = 0 #tried that (suggested in some stack overflow post, didn't help) innodb_buffer_pool_size = 2G innodb_data_file_path = ibdata1:10M:autoextend innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_thread_concurrency = 16 innodb_log_buffer_size = 8M innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 10 innodb_io_capacity = 2000 innodb_lock_wait_timeout = 120 [mysql] no-auto-rehash [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqld_safe] open-files-limit = 8192

4 4

[Maria-discuss] MariaDB Developers Unconference next week
by Otto Kekäläinen 22 Jun '18

22 Jun '18

Hello everybody! If you plan to attend the MariaDB Developers Unconference and related activities next week,* make sure you have signed up <https://www.meetup.com/MariaDB-Developers/events/249024232/> to the meetup* . We will soon be posting important information to attendees and only people who have signed up will receive it. Info: - www.meetup.com/MariaDB-Developers/events/249024232/ <https://www.meetup.com/MariaDB-Developers/events/249024232/> - mariadb.org/2018-2-developers-unconference-in-finland/ - mariadb.org/travel-tips-to-tampere-finland/

1 0

[Maria-discuss] MariaDB files wiped out after reboot
by Allan Mendoza 20 Jun '18

20 Jun '18

Hi, Good Day! I have a weird experience on one of our mysql servers. After a reboot all the database files have been wiped out. May I know if someone had expereienced the same, My OS is Centos 6.9 and MariaDB version is 5.5.35 Attaching is the error log that we have salvaged after the reboot. Thank you lanz_s99zx :)

1 0

Re: [Maria-discuss] Chewing Through Swap - Swappiness = 0
by Michael Caplan 15 Jun '18

15 Jun '18

Just found this interesting article on the "The MySQL 'swap insanity' problem": https://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architect… Has anyone else tried the mentioned solutions? - Forcing interleaved allocation with numactl --interleave=all. - Flushing Linux’s buffer caches just before mysqld startup with sysctl -q -w vm.drop_caches=3. - Forcing the OS to allocate InnoDB’s buffer pool immediately upon startup, using MAP_POPULATE where supported (Linux 2.6.23+), and falling back to memset otherwise. One of the solutions seems dependent on the Twitter fork that was absorbed by the now removed XtraDB. Thanks, Mike On 2018-06-15 08:24 AM, Michael Caplan wrote: > Hi, > > I'm trying to figure out why my recently put into production MariaDB > is so swap hungry. > > I'm running 10.2.14, with roughly 300GB data (1000K +/- tables). 95% > tables are innodb. I have 64GB RAM, with INNODB buffer pool size set > to 50GB (full my.cnf below). The OS is Ubuntu 16.04.4. This is a > dedicated MariaDB server. > > I have swappiness set to 0. > > I started with available swap set to just shy of 1GB. When swap got > to 85% used, I bumped swap to 3GB. A day later, swap again was 85% > used, and I bumped it to 5GB. A day later, swap again was 85% used, > so I bumped it to 15GB. 2 days later 50% of the available swap was > used. I restarted the DB moments ago, freeing up all 15GB but 128MB > of swap: > > total used free shared buff/cache > available > Mem: 62G 20G 4.7G 266M 37G 41G > Swap: 14G 128M 14G > > My understanding is that the OS should never allocate swap when > swappiness is 0, so that has me confused. > > > I've had similar issues with MySQL 5.6 (what I upgraded from) dipping > into swap, but that instance had swappiness set to 1, and never > consumed more than 5GB of swap. > > > Questions: > > 1) How much of this is an OS issue vs MariaDB issue? > > 2) What MariaDB config vars should I be looking at to fix this issue? > > 3) What linux config vars should I be looking at to fix this issue? > > > Thanks, > > Mike > > > > [mysqld] > > # GENERAL # > user = mysql > default-storage-engine = InnoDB > character-set-server = utf8 > collation_server = utf8_general_ci > performance_schema = 0 > max_allowed_packet = 16777216 > sql_mode = > "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" > > # MyISAM # > key-buffer-size = 32M > myisam-recover = FORCE,BACKUP > > # SAFETY # > max-allowed-packet = 16777216 > max-connect-errors = 1000000 > skip-name-resolve > sysdate-is-now = 1 > innodb = FORCE > local_infile = 0 > secure_auth = 1 > safe_user_create = 1 > skip_symbolic_links = 1 > wait_timeout = 28800 > > # DATA STORAGE # > datadir = /var/lib/mysql/ > > # BINARY LOGGING # > log-bin = /var/lib/mysql/mysql-bin > binlog_format = MIXED > server-id = 3 > expire-logs-days = 7 > sync-binlog = 1 > > # REPLICATION # > read-only = 1 > skip-slave-start = 1 > log-slave-updates = 1 > relay-log = /var/lib/mysql/relay-bin > slave-net-timeout = 60 > sync-master-info = 1 > sync-relay-log = 1 > sync-relay-log-info = 1 > > # CACHES AND LIMITS # > tmp-table-size = 32M > max-heap-table-size = 32M > query-cache-type = 0 > query-cache-size = 0 > max-connections = 500 > thread-cache-size = 50 > open-files-limit = 65535 > table-definition-cache = 4096 > table-open-cache = 10240 > > # INNODB # > innodb-flush-method = O_DIRECT > innodb-log-files-in-group = 2 > innodb-log-file-size = 512M > innodb-flush-log-at-trx-commit = 2 > innodb-file-per-table = 1 > innodb-buffer-pool-size = 50G > >

1 0

[Maria-discuss] Timezones Table
by Kurniadi Asrigo 12 Jun '18

12 Jun '18

Hi, I am following the instruction to update the timezone table from MySQL: https://mariadb.com/kb/en/library/time-zones/ And the file is downloaded from MySQL page. I got MariaDB from XAMPP, running on Window10. I downloaded the "timezone_2018e_posix_sql.zip<https://downloads.mysql.com/general/timezone_2018e_posix_sql.zip> - POSIX standard" https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html Once downloaded, I extracted the package into a directory. From the instruction, I executed the command: .\mysql_tzinfo_to_sql.exe C:\dev\xampp_download\timezone\timezone_posix.sql | .\mysql.exe -D mysql -u root -p Now, the timezone Table is all empty. I am wondering if I am missing a step or an option. Thanks, Ti.

2 1

[Maria-discuss] MariaDB Developers Unconference in a few weeks!
by Otto Kekäläinen 09 Jun '18

09 Jun '18

Hello everybody! The next developers unconference will be in a few weeks: https://mariadb.org/2018-2-developers-unconference-in-finland/ There are still open slots so we encourage you to sign up now. https://www.meetup.com/MariaDB-Developers/events/249024232/ We have plenty of break-out rooms this time and there is space, so we also encourage you to suggest sessions/discussions you would like to have to ian(a)mariadb.org. Around mid-summer is a great time to visit Finland. The nights that don't get dark are worth experiencing! Getting to the venue in Tampere., Finlnd is also very easy. https://mariadb.org/travel-tips-to-tampere-finland/#why-visit Hope to see you there! - Otto

1 0

[Maria-discuss] Upgrade paths through more major versions
by Michal Schorm 06 Jun '18

06 Jun '18

Hello, MariaDB changed its release cycle to 1 major version / year. However the upgrades remained supported only from one major version to the very next. I see a raise of users (of RH products - RHEL and RHSCL mainly) that would like to jump several versions. They had an application build on, let's say, 5.5 (RHEL7 default) and would like to get the features from 10.2 (available in RH software collections - we provide plenty of versions this way). So far, they have to upgrade one by one 5.5->10.0->10.1->10.2; and solve the conflicts at each stage. Lately, we discontinued support fo 10.0 in RHSCL, which means, the upgrade path metioned above won't be as easy anymore. Also mysqldump can't be used for upgrade to 10.3, which is IMHO frequently used. For other RH products, there is an ongoing discussions, whether support all major releases or if to skip some. Which implies the questions about the upgrades through versions we wouldn't pack. However skipping several versions is IMHO quite normal for products with slow release cycle, like RHEL, so I'd expect other companies providing similar systems / products with slow release cycle to face the same problems. Are there any plans to extend the upgrade paths through more versions? Have you encountered simmilar needs of the users? -- Michal Schorm Associate Software Engineer Core Services - Databases Team Red Hat

4 6

[Maria-discuss] Why MyISAM as default engine for system tables ?
by Julien Muchembled 06 Jun '18

06 Jun '18

Hello, A few weeks ago, we got the following fatal error when starting mariadb: [Note] Starting crash recovery... [Note] Crash recovery finished. [ERROR] mysqld: Table './mysql/db' is marked as crashed and should be repaired [Warning] Checking table: './mysql/db' [ERROR] mysql.db: 1 client is using or hasn't closed the table properly tch value count at row 1 [ERROR] Aborting Unfortunately, what led to this is unclear. We only rebooted the machine. Maybe a hw raid issue. We did a copy at that time. I tried recently if it could be repaired but I don't have the a fatal error anymore. We also don't know why system tables were touched during the reboot. Anyway, given the description of Aria ("Crash-safe tables with MyISAM heritage"), I understand that MyISAM is not crash-safe. Then, why keep using MyISAM as default engine for system tables, when MariaDB has Aria or InnoDB ? Not sure a crash-safe engine would have helped in the above case (there's no miracle for data corruption). But we'd like at least that all our mariadb setups are crash-safe (e.g. power cuts) and we use InnoDB partly for that. And I don't know if a InnoDB DB could be always recoverable if the 'mysql' DB is lost but we don't want to do manually (and learn how to) what the machine can do automatically. Julien

6 8

[Maria-discuss] Python 3 Handler Socket
by Roberto Spadim 28 May '18

28 May '18

Hi guys! Any link to a working handler socket python module? pyhs is old and working only with python2, we need to update the C lib or use plain python with python3 (i did some changes, don't know if there's a running job on this) -- Roberto Spadim

2 4

[Maria-discuss] MariaDB Compile Size
by Tom Jay 21 May '18

21 May '18

Hello, Has anyone got any idea how to reduce the MariaDB install size, when compiling from source? I've had multiple attempts, all with different options such as 'WITHOUT_EXAMPLE_STORAGE_ENGINE=1' and 'FEATURE_SET:STRING=large', for example, but with no effect. The installation size is usually around 2.1 GB, whereas the official Arch package, which also compiles from source, is about 231.78 MB. The official Arch build script is here [https://git.archlinux.org/svntogit/packages.git/tree/trunk/PKGBUILD?h=packa…] and the only difference I can notice is that they delete a lot of stuff afterwards. Is there any reference for stuff that can be deleted after MariaDB has been installed? Thanks.

4 5

[Maria-discuss] R: MariaDB Compile Size
by Federico Razzoli 19 May '18

19 May '18

You maybe interested in this article: http://datacharmer.blogspot.nl/2016/02/mysql-sandboxes-in-docker.html?m=1 Federico -------------------------------------------- Sab 19/5/18, Tom Jay <tom_jay(a)hotmail.com> ha scritto: Oggetto: [Maria-discuss] MariaDB Compile Size A: "maria-discuss(a)lists.launchpad.net" <maria-discuss(a)lists.launchpad.net> Data: Sabato 19 maggio 2018, 07:33 Hello, Has anyone got any idea how to reduce the MariaDB install size, when compiling from source? I've had multiple attempts, all with different options such as 'WITHOUT_EXAMPLE_STORAGE_ENGINE=1' and 'FEATURE_SET:STRING=large', for example, but with no effect. The installation size is usually around 2.1 GB, whereas the official Arch package, which also compiles from source, is about 231.78 MB. The official Arch build script is here [https://git.archlinux.org/svntogit/packages.git/tree/trunk/PKGBUILD?h=packa…] and the only difference I can notice is that they delete a lot of stuff afterwards. Is there any reference for stuff that can be deleted after MariaDB has been installed? Thanks. _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss(a)lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

1 0

[Maria-discuss] Query ambiguity
by Dev C 17 May '18

17 May '18

Hello I have query ambiguity as attached. Please help me on how to solve that.

4 6

[Maria-discuss] Interval in MYsql query
by Dev C 16 May '18

16 May '18

Hello If I use below query (metrics_5.created_at)>'5/10/2018'- 28 ) it gives me some result but if i use below query (metrics_5.created_at)>'5/10/2018'- INTERVAL 28 DAY ) It does not give me any result, any one know why ?

3 2

[Maria-discuss] MySQL Quries and Error
by Dev C 16 May '18

16 May '18

Below is my queries SELECT AdData4w.`Campaign Name`, AdData4w.`Adgroup Name`, AdData4w.`4w Clicks`, AdData1w.`1w Clicks`, AdData4w.`4w Orders`, AdData1w.`1w Orders`, AdData4w.`4w Spend`, AdData1w.`1w Spend`, AdData4w.`4w Sales`, AdData1w.`1w Sales`, AdData4w.`4w Conv Rate`, AdData1w.`1w Conv Rate`, AdData4w.`4w ACOS`, AdData1w.`1w ACOS`, AdData4w.`4w CPC`, AdData1w.`1w CPC`,AdData4w.`4w Bid`, AdData1w.`1w Bid` FROM AdData4w INNER JOIN AdData1w ON (AdData4w.`Adgroup Name` = AdData1w.`Adgroup Name`) AND (AdData4w.`Campaign Name` = AdData1w.`Campaign Name`) GROUP BY AdData4w.`Campaign Name`, AdData4w.`Adgroup Name`, AdData4w.`4w Clicks`, AdData1w.`1w Clicks`, AdData4w.`4w Orders`, AdData1w.`1w Orders`, AdData4w.`4w Spend`, AdData1w.`1w Spend`, AdData4w.`4w Sales`, AdData1w.`1w Sales`, AdData4w.`4w Conv Rate`, AdData1w.`1w Conv Rate`, AdData4w.`4w ACOS`, AdData1w.`1w ACOS`, AdData4w.`4w CPC`, AdData1w.`1w CPC`, AdData4w.`4w Bid`, AdData1w.`1w Bid` From (SELECT campaigns_5.name AS `Campaign Name`, adgroups_5.name AS `Adgroup Name`, Sum(metrics_5.clicks) AS `1w Clicks`, Sum(metrics_5.orders) AS `1w Orders`, Sum(metrics_5.costs) AS `1w Spend`, Sum(metrics_5.sales) AS `1w Sales`, If(Sum(clicks)=0,0,Sum(orders)/Sum(clicks)) AS `1w Conv Rate`, If(Sum(metrics_5.sales)=0,0,Sum(metrics_5.costs)/Sum(metrics_5.sales)) AS `1w ACOS`, IF (Sum(clicks)=0,0,Sum(costs)/Sum(clicks)) AS `1w CPC`, Avg(bid_info_5.value) AS `1w Bid` FROM ((adgroups_5 INNER JOIN campaigns_5 ON adgroups_5.campaignId = campaigns_5.campaignId) INNER JOIN metrics_5 ON adgroups_5.adGroupId = metrics_5.type_id) INNER JOIN bid_info_5 ON adgroups_5.adGroupId = bid_info_5.type_id WHERE (((metrics_5.created_at)>'5/10/2018'-7)) GROUP BY campaigns_5.name, adgroups_5.name, campaigns_5.user_market_id, RIGHT(campaigns_5.name,4) HAVING (((campaigns_5.user_market_id)=12) AND ((Right(campaigns_5.name,4))="PFUS") AND ((Sum(metrics_5.impressions))>0)) ORDER BY campaigns_5.name, adgroups_5.name) AS AdData1w INNER JOIN (SELECT campaigns_5.name AS `Campaign Name`, adgroups_5.name AS `Adgroup Name`, Sum(metrics_5.clicks) AS `4w Clicks`, Sum(metrics_5.orders) AS `4w Orders`, Sum(metrics_5.costs) AS `4w Spend`, Sum(metrics_5.sales) AS `4w Sales`, If(Sum(clicks)=0,0,Sum(orders)/Sum(clicks)) AS `4w Conv Rate`, If(Sum(metrics_5.sales)=0,0,Sum(metrics_5.costs)/Sum(metrics_5.sales)) AS `4w ACOS`, If(Sum(clicks)=0,0,Sum(costs)/Sum(clicks)) AS `4w CPC`, Avg(bid_info_5.value) AS `4w Bid` FROM ((adgroups_5 INNER JOIN campaigns_5 ON adgroups_5.campaignId = campaigns_5.campaignId) INNER JOIN metrics_5 ON adgroups_5.adGroupId = metrics_5.type_id) INNER JOIN bid_info_5 ON adgroups_5.adGroupId = bid_info_5.type_id WHERE (((metrics_5.created_at)>'5/10/2018'-28)) GROUP BY campaigns_5.name, adgroups_5.name, campaigns_5.user_market_id, RIGHT(campaigns_5.name,4) HAVING (((campaigns_5.user_market_id)=12) AND ((Right(campaigns_5.name,4))="PFUS") AND ((Sum(metrics_5.impressions))>0)) ORDER BY campaigns_5.name, adgroups_5.name) AS AdData4w it gives error on as below 4 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'From (SELECT campaigns_5.name AS `Campaign Name`, adgroups_5.name AS `Adgroup N' at line 4 Query12.sql 1 1 Please assist.

2 1

[Maria-discuss] [question] password input length
by Michal Schorm 16 May '18

16 May '18

Hello everyone, 1) I found out, that passwords longer than 79 characters aren't accepted via password prompt. Is that expected? Morie info and reproducer in RH bugzilla: https://bugzilla.redhat.com/show_bug.cgi?id=1566179 -- 2) Another password length cap at 213 characters can be found when using sha256_password authentification plugin. Is that expected too? Morie info and reproducer in RH bugzilla: https://bugzilla.redhat.com/show_bug.cgi?id=1566219 -- 3) Different theme: why aren't tests for mariadb-connector-odbc, that can be found on GitHub, packged to the source tarball ? -- Michal Schorm Associate Software Engineer Core Services - Databases Team Red Hat

2 2

[Maria-discuss] Installing MariaDB Alongside MySQL
by Muhammad Bashir Al-Noimi 15 May '18

15 May '18

4 27

[Maria-discuss] Charsets and collations
by Peter Laursen 20 Apr '18

20 Apr '18

Does anybody know if there are any plans to bring collations added in MySQL 8 - refer http://mysqlserverteam.com/new-collations-in-mysql-8-0-0/ - to MariaDB and are there any plans to change the default charset to a unicode-based one? -- Peter -- Webyog

2 2

[Maria-discuss] MariaDB Developers Unconference in June
by Ian Gilfillan 10 Apr '18

10 Apr '18

The next MariaDB Developers Unconference is going to be held in Tampera, Finland, from 26 June to 29 June. This will be a four-day event; the first day focusing on new contributors, the 2nd and 3rd the regular Unconference sessions and discussions, and the 4th day on getting patches reviewed and implemented, taking advantage of the chance to have in-person discussions. Details, as well as where to sign up, are at https://mariadb.org/2018-2-developers-unconference-in-finland/

1 0

[Maria-discuss] MDEV-15765 BETWEEN not working in certain cases
by Michael Caplan 08 Apr '18

08 Apr '18

Hello, I've just stalled a rollout to production of 10.2.14 because of this what I think is rather huge SQL break (https://jira.mariadb.org/browse/MDEV-15765) Anyone else holding 10.2.14 from production? Anyone running 10.2.14 in production? Trying to get some additional perspective on this issue. Thanks, Mike

1 0

[Maria-discuss] Replication New to Old
by Michael Caplan 08 Apr '18

08 Apr '18

Hello, I realize that in general replication from a newer master to an older slave is typically not recommended. This said, does anyone have an experience replicating from MariaDB 10.2 to MySQL 5.6? The compatibility matrix found here: https://mariadb.com/kb/en/library/mariadb-vs-mysql-compatibility/ Seems to be missing data on this type of setup. Thanks, Mike

5 6

[Maria-discuss] galera & restore a database
by lejeczek 28 Mar '18

28 Mar '18

hi guys I have mariadb in a three nodes galera cluster, when I restore a database on a node: $ mysql wordpress_local < db.sql it works well on that node but the remaining two nodes database tables are empty. Tables structure is there, but no records. What am I missing, doing wrong? many thanks, L.

1 0

[Maria-discuss] MDEV-10286 - Adjustment of table_open_cache according to system limits does not work when open-files-limit option is provided
by Sergey Vojtovich 21 Mar '18

21 Mar '18

Hi Monty, > diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl > index 55543e529a6..54dc0aa17cc 100755 > --- a/mysql-test/mysql-test-run.pl > +++ b/mysql-test/mysql-test-run.pl > @@ -4469,6 +4469,7 @@ sub extract_warning_lines ($$) { > qr/InnoDB: See also */, > qr/InnoDB: Cannot open .*ib_buffer_pool.* for reading: No such file or directory*/, > qr/InnoDB: Table .*mysql.*innodb_table_stats.* not found./, > + qr/Changed limits: max_open_files/, I wonder how did you get this warning? > qr/InnoDB: User stopword table .* does not exist./ > > ); > diff --git a/sql/mysqld.cc b/sql/mysqld.cc > index b21d50a20fe..b3414279ac9 100644 > --- a/sql/mysqld.cc > +++ b/sql/mysqld.cc > @@ -4446,11 +4446,18 @@ static int init_common_variables() > > /* connections and databases needs lots of files */ > { > - uint files, wanted_files, max_open_files; > + uint files, wanted_files, max_open_files, min_tc_size, extra_files, > + min_connections; > + ulong org_max_connections= max_connections, org_tc_size= tc_size; > > + /* Number of files reserved for temporary files */ > + extra_files= 30; > + min_connections= 10; > /* MyISAM requires two file handles per table. */ > - wanted_files= (10 + max_connections + extra_max_connections + > + wanted_files= (extra_files + max_connections + extra_max_connections + > tc_size * 2); > + min_tc_size= MY_MIN(tc_size, TABLE_OPEN_CACHE_MIN); > + > /* > We are trying to allocate no less than max_connections*5 file > handles (i.e. we are trying to set the limit so that they will I believe the following is easier to read: ulong org_max_connections= max_connections; ulong org_tc_size= tc_size; ulong min_tc_size= MY_MIN(tc_size, TABLE_OPEN_CACHE_MIN); /* Number of files reserved for temporary files */ uint extra_files= 30; uint min_connections= 10; /* MyISAM requires two file handles per table. */ uint wanted_files= extra_files + max_connections + extra_max_connections + tc_size * 2; /* We are trying to allocate no less than max_connections*5 file handles (i.e. we are trying to set the limit so that they will be available). In addition, we allocate no less than how much was already allocated. However below we report a warning and recompute values only if we got less file handles than were explicitly requested. No warning and re-computation occur if we can't get max_connections*5 but still got no less than was requested (value of wanted_files). */ uint max_open_files= MY_MAX(MY_MAX(wanted_files, (max_connections + extra_max_connections)*5), open_files_limit); uint files= my_set_max_open_files(max_open_files); > @@ -4463,43 +4470,43 @@ static int init_common_variables() > */ > max_open_files= MY_MAX(MY_MAX(wanted_files, > (max_connections + extra_max_connections)*5), > - open_files_limit); > + open_files_limit); > files= my_set_max_open_files(max_open_files); > > - if (files < wanted_files) > - { > - if (!open_files_limit || IS_SYSVAR_AUTOSIZE(&open_files_limit)) > - { > - /* > - If we have requested too much file handles than we bring > - max_connections in supported bounds. > - */ > - SYSVAR_AUTOSIZE(max_connections, > - (ulong) MY_MIN(files-10-TABLE_OPEN_CACHE_MIN*2, max_connections)); > - /* > - Decrease tc_size according to max_connections, but > - not below TABLE_OPEN_CACHE_MIN. Outer MY_MIN() ensures that we > - never increase tc_size automatically (that could > - happen if max_connections is decreased above). > - */ > - SYSVAR_AUTOSIZE(tc_size, > - (ulong) MY_MIN(MY_MAX((files - 10 - max_connections) / 2, > - TABLE_OPEN_CACHE_MIN), tc_size)); > - DBUG_PRINT("warning", > - ("Changed limits: max_open_files: %u max_connections: %ld table_cache: %ld", > - files, max_connections, tc_size)); > - if (global_system_variables.log_warnings > 1) > - sql_print_warning("Changed limits: max_open_files: %u max_connections: %ld table_cache: %ld", > - files, max_connections, tc_size); > - } > - else if (global_system_variables.log_warnings) > - sql_print_warning("Could not increase number of max_open_files to more than %u (request: %u)", files, wanted_files); > - } > - SYSVAR_AUTOSIZE(open_files_limit, files); > + if (files < wanted_files && global_system_variables.log_warnings) > + sql_print_warning("Could not increase number of max_open_files to more than %u (request: %u)", files, wanted_files); > + > + /* > + If we have requested too much file handles than we bring > + max_connections in supported bounds. Still leave at least > + 'min_connections' connections > + */ > + SYSVAR_AUTOSIZE(max_connections, > + (ulong) MY_MAX(MY_MIN(files- extra_files- min_tc_size*2, > + max_connections), min_connections)); > + /* > + Decrease tc_size according to max_connections, but > + not below min_tc_size. Outer MY_MIN() ensures that we > + never increase tc_size automatically (that could > + happen if max_connections is decreased above). > + */ > + SYSVAR_AUTOSIZE(tc_size, > + (ulong) MY_MIN(MY_MAX((files - extra_files - > + max_connections) / 2, min_tc_size), > + tc_size)); > + DBUG_PRINT("warning", > + ("Current limits: max_open_files: %u max_connections: %ld table_cache: %ld", > + files, max_connections, tc_size)); > + if (global_system_variables.log_warnings > 1 && > + (max_connections < org_max_connections || > + tc_size < org_tc_size)) > + sql_print_warning("Changed limits: max_open_files: %u max_connections: %lu (%lu) table_cache: %lu (%lu)", I'd say this format is quite confusing. Probably add something like "was %lu" in brackets? > + files, max_connections, org_max_connections, > + tc_size, org_tc_size); I think the above code must be executed under "if (files < wanted_files)". We don't really want to "autosize" max_connections and tc_size if we got enough file descriptors. Also you missed to "autosize" open_files_limit. I think it should be done "if (open_files_limit != files) SYSVAR_AUTOSIZE(open_files_limit, files);". > } > > /* > - Max_connections is now set. > + Max_connections and tc_cache is now set. s/is/are/ > Now we can fix other variables depending on this variable. > */ > > diff --git a/sql/sql_const.h b/sql/sql_const.h > index 65742235bee..e28a0649f04 100644 > --- a/sql/sql_const.h > +++ b/sql/sql_const.h > @@ -127,7 +127,7 @@ > #define MAX_FIELDS_BEFORE_HASH 32 > #define USER_VARS_HASH_SIZE 16 > #define SEQUENCES_HASH_SIZE 16 > -#define TABLE_OPEN_CACHE_MIN 400 > +#define TABLE_OPEN_CACHE_MIN 200 > #define TABLE_OPEN_CACHE_DEFAULT 2000 > #define TABLE_DEF_CACHE_DEFAULT 400 > /** > diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc > index de9806ab289..41317a5c259 100644 > --- a/sql/sys_vars.cc > +++ b/sql/sys_vars.cc > @@ -3477,7 +3477,7 @@ static bool fix_table_open_cache(sys_var *, THD *, enum_var_type) > static Sys_var_ulong Sys_table_cache_size( > "table_open_cache", "The number of cached open tables", > GLOBAL_VAR(tc_size), CMD_LINE(REQUIRED_ARG), > - VALID_RANGE(1, 1024*1024), DEFAULT(TABLE_OPEN_CACHE_DEFAULT), > + VALID_RANGE(10, 1024*1024), DEFAULT(TABLE_OPEN_CACHE_DEFAULT), Why? There're at least a few test cases that do table_open_cache=1: partition_open_files_limit, sp, myisam. > BLOCK_SIZE(1), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0), > ON_UPDATE(fix_table_open_cache)); > Regards, Sergey

2 1

[Maria-discuss] columns compression
by Roberto Spadim 19 Mar '18

19 Mar '18

hi guys! anyone testing column compression and have problems when changing form one table format to other? i created a new table with blob compressed column, and i'm trying to insert select from old table, but i got a problem: MariaDB [test]> insert into new_table select * from old_table; ERROR 1406 (22001): Data too long for column 'data' at row 1 using aria or innodb engine give the same error, it's a BLOB columns tahnks -- Roberto Spadim

2 8

[Maria-discuss] SQL_MODE=ORACLE documentation
by Federico Razzoli 05 Mar '18

05 Mar '18

With SQL_MODE='ORACLE' a completely new syntax has been introduced. Basic syntax constructs of MariaDB become unavailable, but not all Oracle syntax seems to be supported. Is there any plan to document the supported syntax? Current documentation looks like an unfinished draft and doesn't save much time for the poor user. Regards,Federico

4 5

[Maria-discuss] 10.3.5 tokudb rpm missing for Opensuse
by Reinis Rozitis 04 Mar '18

04 Mar '18

Hello, It seems that the Mariadb 10.3.5 Tokudb rpm package hasn't been built (and replicated on mirrors) for Opensuse (though all other like SLES, Rhel, Centos do have it). rr

2 1

[Maria-discuss] Backing up a single database with mariabackup?
by Kevin Poitra 01 Mar '18

01 Mar '18

I've been working on redoing our database backup routines, and have looked into mariabackup as mysqldump doesn't cut it anymore given the size of our schemas. We have multiple schemas that need to be backed up - they all vary in size, and they also vary in backup frequency (some need to be backed up on a six hour period, others can go a day in between backups). On top of that, our database consists of two database servers, running in a master-slave configuration. To start, I ran this command against one of our schemas: mariabackup --backup --user root --password <pass> --databases db1 That generated a copy of the 'db1' schema's data files, along with some files used by MariaDB itself (aria_log, ib_buffer_pool, ib_logfile, ibdata, so on). This works perfectly - we can create separate backups of each of our schemas. Restoring seems to be another story however. After '--prepare'ing the backup and issuing the mariabackup --copy-back command, it complains that the data directory must be empty. If I have separated backups, would it be possible for me to restore those schemas under one database (or rather, one MariaDB instance)? To be even clearer, if I've made backups of schemas 'db1', 'db2', and 'db3', I don't seem to be able to restore them all under the same database instance - is there something I'm not understanding?

2 1

[Maria-discuss] procedure to change database encryption with file_key_management plugin?
by obsa＠mm.st 27 Feb '18

27 Feb '18

I've built mysqld -V mysqld Ver 10.2.14-MariaDB-log for Linux on x86_64 (Source distribution) I'm setting up encryption, following https://mariadb.com/kb/en/library/encryption/ https://mariadb.com/kb/en/library/data-at-rest-encryption/ I created my key file openssl rand -hex 32 b650adbc0c5df1bc3e766b4b65f26dc76c76ed81b955bbedaf50e1d4e16fc732 /etc/mariadb/keys.txt 1;b650adbc0c5df1bc3e766b4b65f26dc76c76ed81b955bbedaf50e1d4e16fc732 encrypted it openssl enc -aes-256-cbc -k 'test_passphrase' -md sha1 -in /etc/mariadb/keys.txt -out /etc/mariadb/keys.enc verified it openssl aes-256-cbc -d -md sha1 -k 'test_passphrase' -in /etc/mariadb/keys.enc 1;b650adbc0c5df1bc3e766b4b65f26dc76c76ed81b955bbedaf50e1d4e16fc732 I've enabled "everything" encryption using that keyfile [mysqld] plugin_dir=/opt/mariadb/lib/plugin plugin-load-add=file_key_management file-key-management file_key_management_encryption_algorithm=aes_ctr file_key_management_filekey = 'test_filekey' file_key_management_filename = /etc/mariadb/enc/keys.enc aria-encrypt-tables = 1 encrypt-binlog = 1 encrypt-tmp-disk-tables = 1 encrypt-tmp-files = 1 innodb_default_encryption_key_id = 1 innodb-encrypt-log = off innodb-encrypt-tables = on innodb-encryption-threads = 4 innodb-tablespaces-encryption = 1 verified the plugin loads mysql -e "show plugins;" | grep ENC INNODB_TABLESPACES_ENCRYPTION ACTIVE INFORMATION SCHEMA NULL BSD file_key_management ACTIVE ENCRYPTION file_key_management.so GPL on startup it looks like it starts up ok 2018-02-21 13:01:29 139729003899072 [Note] InnoDB: 5.7.21 started; log sequence number 7206290786 2018-02-21 13:01:29 139729003899072 [Note] InnoDB: Creating #1 encryption thread id 139727810316032 total threads 4. 2018-02-21 13:01:29 139729003899072 [Note] InnoDB: Creating #2 encryption thread id 139727801923328 total threads 4. 2018-02-21 13:01:29 139727818708736 [Note] InnoDB: Loading buffer pool(s) from /home/data/db/ib_buffer_pool 2018-02-21 13:01:29 139729003899072 [Note] InnoDB: Creating #3 encryption thread id 139727793530624 total threads 4. 2018-02-21 13:01:29 139729003899072 [Note] InnoDB: Creating #4 encryption thread id 139727785137920 total threads 4. 2018-02-21 13:01:29 139727818708736 [Note] InnoDB: Buffer pool(s) load completed at 180222 13:01:29 2018-02-21 13:01:29 139729003899072 [Note] Using encryption key id 1 for temporary files 2018-02-21 13:01:29 139729003899072 [Note] Server socket created on IP: '127.0.0.1'. 2018-02-21 13:01:29 139729003899072 [Note] Reading of all Master_info entries succeded 2018-02-21 13:01:29 139729003899072 [Note] Added new Master_info '' to hash table 2018-02-21 13:01:29 139729003899072 [Note] /opt/mariadb/bin/mysqld: ready for connections. Version: '10.2.14-MariaDB-log' socket: '/var/cache/mariadb/mariadb.sock' port: 3306 Source distribution and verified table encryption mysql -e "SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION;" +-------+-------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+ | SPACE | NAME | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER | CURRENT_KEY_ID | ROTATING_OR_FLUSHING | +-------+-------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+ | 1375 | mysql/gtid_slave_pos | 1 | 1 | 1 | 1 | NULL | NULL | 1 | 0 | | 1465 | mysql/innodb_index_stats | 1 | 1 | 1 | 1 | NULL | NULL | 1 | 0 | | 1466 | mysql/innodb_table_stats | 1 | 1 | 1 | 1 | NULL | NULL | 1 | 0 | | 18999 | testdata/table0001 | 1 | 0 | 1 | 1 | NULL | NULL | 1 | 0 | ... ... ... | 0 | innodb_system | 1 | 1 | 1 | 1 | NULL | NULL | 1 | 0 | +-------+-------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+ reading Encryption key management MariaDB encryption supports multiple encryption keys, they are identified by a key identifier — a 32-bit integer. To support automatic key rotation every key additionally might have different versions. XtraDB and InnoDB can automatically re-encrypt the data from an older to a newer version of the same key. But how different keys are stored and rotated depends on the key management solution that you choose. but for this plugin file_key_management This plugin does not support key rotation — all keys always have the version 1. So I understand that I can't rotate the keys similar to what the AWS plugin provides. But if I need to change the key at any time, either just its encrypted form /etc/mariadb/keys.enc &/or the 'master' /etc/mariadb/keys.txt What's the procedure to re-key all the encrypted tables? Do I need to (1) stop the server (2) manually decrypt each table with its old key (3) reencrypt each table with the new key (4) restart the server ? True also for having used multiple keys for global/default, temp tables, and per-table? Is there any tool/procedure that automates that? I suppose that the AWS plugin takes care of that automated-rotation. Is there another non-commercial/open-source plugin with similar rotation capability?

3 4

[Maria-discuss] JSON column type
by Brent Clark 22 Feb '18

22 Feb '18

Good day Guys I have a client that is looking for JSON support, or better 'JSON column type'. He is asking for MySQL/5.7.8 (Currently on MySQL 5.6). And his comment is : "/This is so that we can use the JSON column type introduced in this version." We don't use MySQL, well we shipping away to MariaDB. The question I would like to ask is. Based on reading : https://mariadb.com/resources/blog/best-both-worlds-relational-json Is it fair to say, the clients needs are met? If anyone has any comment or further thought on this matter or any tips to share it would gratefully be appreciated. Regards and many thanks. Brent Clark

2 2

[Maria-discuss] mysqld_safe
by Ruben Safir 21 Feb '18

21 Feb '18

I did an update on my package manager and it seems that mysqld_safe was removed from the system, and I have that integrated into my openrc init system. Has it stopped being supported? ruben -- So many immigrant groups have swept through our town that Brooklyn, like Atlantis, reaches mythological proportions in the mind of the world - RI Safir 1998 http://www.mrbrklyn.com DRM is THEFT - We are the STAKEHOLDERS - RI Safir 2002 http://www.nylxs.com - Leadership Development in Free Software http://www2.mrbrklyn.com/resources - Unpublished Archive http://www.coinhangout.com - coins! http://www.brooklyn-living.com Being so tracked is for FARM ANIMALS and and extermination camps, but incompatible with living as a free human being. -RI Safir 2013

2 1

[Maria-discuss] build broken again -> Fwd: [MariaDB Announce] MariaDB 10.2.13, MariaDB Connector/ODBC 3.0.3 and MariaDB Connector/ODBC 2.0.16 now available
by Reindl Harald 14 Feb '18

14 Feb '18

can we have a release which simply builds? see my complaint about 10.2.12 10.2.13 is also broken different place would you guys mind sent out a link to the users list *before* GA annoucements so that such likely easy to fix bugs can be fixed *before* the announcement? and no it's not a option build with partition support and disable it in my.cnf because the next build won't start because the unknown option and i danced these dance now often enough while MariaDB is the only project in the whole opensource-ecosystem where can place a bet that the next release won't build with the same options Scanning dependencies of target mysqld Scanning dependencies of target explain_filename-t [ 99%] Building CXX object sql/CMakeFiles/mysqld.dir/main.cc.o [100%] Building CXX object unittest/sql/CMakeFiles/explain_filename-t.dir/explain_filename-t.cc.o [100%] Linking CXX executable mysqld [100%] Linking CXX executable explain_filename-t libsql.a(sql_yacc.cc.o): In function `MYSQLparse(THD*)': /home/buildbot/git/sql/sql_yacc.yy:5211: undefined reference to `partition_info::error_if_requires_values() const' collect2: error: ld returned 1 exit status make[2]: *** [sql/CMakeFiles/mysqld.dir/build.make:115: sql/mysqld] Error 1 make[1]: *** [CMakeFiles/Makefile2:8465: sql/CMakeFiles/mysqld.dir/all] Error 2 make[1]: *** Waiting for unfinished jobs.... ../../sql/libsql.a(sql_yacc.cc.o): In function `MYSQLparse(THD*)': /home/buildbot/git/sql/sql_yacc.yy:5211: undefined reference to `partition_info::error_if_requires_values() const' collect2: error: ld returned 1 exit status make[2]: *** [unittest/sql/CMakeFiles/explain_filename-t.dir/build.make:116: unittest/sql/explain_filename-t] Error 1 make[1]: *** [CMakeFiles/Makefile2:1336: unittest/sql/CMakeFiles/explain_filename-t.dir/all] Error 2 make: *** [Makefile:163: all] Error 2 Fehler: Fehler-Status beim Beenden von /var/tmp/rpm-tmp.Xg5GXO (%build) -------- Weitergeleitete Nachricht -------- Betreff: [Maria-discuss] Fwd: [MariaDB Announce] MariaDB 10.2.12 now available Datum: Thu, 4 Jan 2018 19:29:36 +0100 Von: Reindl Harald <h.reindl(a)thelounge.net> An: Mailing-List mariadb <maria-discuss(a)lists.launchpad.net> and once again a update don't build with the same rpm-spec as before - frankly how often should i offer that someone sends out a mail to the ML with a link to a proposed release tarball to get such things fixed *before* release? + cmake . -DFEATURE_SET=large -DCMAKE_INSTALL_PREFIX=/usr -DINSTALL_INCLUDEDIR=include/mysql -DINSTALL_LAYOUT=RPM -DDAEMON_NAME=mysqld -DDAEMON_NO_PREFIX=mysqld -DNICE_PROJECT_NAME=MariaDB -DINSTALL_LIBDIR=lib64/mysql -DINSTALL_MANDIR=share/man -DINSTALL_MYSQLSHAREDIR=share/mysql -DINSTALL_MYSQLTESTDIR=share/mysql-test -DINSTALL_PLUGINDIR=lib64/mysql/plugin -DINSTALL_SBINDIR=libexec -DINSTALL_SCRIPTDIR=bin -DINSTALL_SQLBENCHDIR= -DINSTALL_SUPPORTFILESDIR=share/mysql -DMYSQL_DATADIR=/var/lib/mysql -DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock -DENABLED_PROFILING=OFF -DENABLE_DEBUG_SYNC=OFF -DENABLE_DTRACE=OFF -DPLUGIN_ARIA=YES -DPLUGIN_CSV=YES -DPLUGIN_MYISAM=YES -DPLUGIN_ARCHIVE=NO -DPLUGIN_BLACKHOLE=NO -DPLUGIN_CASSANDRA=NO -DPLUGIN_CONNECT=NO -DPLUGIN_EXAMPLE=NO -DPLUGIN_FEDERATED=NO -DPLUGIN_FEDERATEDX=NO -DPLUGIN_FEEDBACK=NO -DPLUGIN_MROONGA=NO -DPLUGIN_MYISAMMRG=NO -DPLUGIN_OQGRAPH=NO -DPLUGIN_PARTITION=NO -DPLUGIN_PERFSCHEMA=NO -DPLUGIN_ROCKSDB=NO -DPLUGIN_SEMISYNC=NO -DPLUGIN_SEQUENCE=NO -DPLUGIN_SPHINX=NO -DPLUGIN_SPIDER=NO -DPLUGIN_TOKUDB=NO -DPLUGIN_XTRADB=NO -DWITHOUT_DYNAMIC_PLUGINS=ON -DWITH_ATOMIC_OPS=smp -DWITH_EMBEDDED_SERVER=OFF -DWITH_INNODB_DISALLOW_WRITES=OFF -DWITH_INNODB_BZIP2=OFF -DWITH_INNODB_LZ4=OFF -DWITH_INNODB_LZMA=OFF -DWITH_INNODB_LZO=OFF -DWITH_INNODB_SNAPPY=OFF -DWITH_MYSQLCOMPAT=ON -DSECURITY_HARDENED=OFF -DWITH_LIBARCHIVE=OFF -DWITH_LIBWRAP=OFF -DWITH_MARIABACKUP=OFF -DWITH_PIC=NO -DWITH_READLINE=OFF -DWITH_SAFEMALLOC=OFF -DWITH_VALGRIND=OFF -DWITH_WSREP=OFF -DWITH_JEMALLOC=OFF -DWITH_SSL=system -DWITH_ZLIB=system -DCOMPILATION_COMMENT=thelounge [ 99%] Built target sql Scanning dependencies of target explain_filename-t Scanning dependencies of target mysqld [ 99%] Building CXX object sql/CMakeFiles/mysqld.dir/main.cc.o [100%] Linking CXX executable mysqld [100%] Building CXX object unittest/sql/CMakeFiles/explain_filename-t.dir/explain_filename-t.cc.o [100%] Linking CXX executable explain_filename-t libsql.a(sql_yacc.cc.o): In function .`MYSQLparse.(/THD.*.)/'sql:/ libsql.a/(homesql_yacc.cc.o/)buildbot:/ Ingit /functionsql /`sql_yacc.yyMYSQLparse(:THD*5211):' :undefined /referencehome /tobuildbot /`gitpartition_info/sql:/:sql_yacc.yyerror_if_requires_values:5211(: )undefined referenceconst 'to `partition_info::error_if_requires_values() const' collect2: error: ld returned 1 exit status collect2: error: ld returned 1 exit status make[2]: *** [sql/CMakeFiles/mysqld.dir/build.make:115: sql/mysqld] Error 1 make[2]: *** [unittest/sql/CMakeFiles/explain_filename-t.dir/build.make:116: unittest/sql/explain_filename-t] Error 1 make[1]: *** [CMakeFiles/Makefile2:8464: sql/CMakeFiles/mysqld.dir/all] Error 2 make[1]: *** Waiting for unfinished jobs.... make[1]: *** [CMakeFiles/Makefile2:1336: unittest/sql/CMakeFiles/explain_filename-t.dir/all] Error 2 -------- Weitergeleitete Nachricht -------- Betreff: [MariaDB Announce] MariaDB 10.2.13, MariaDB Connector/ODBC 3.0.3 and MariaDB Connector/ODBC 2.0.16 now available Datum: Tue, 13 Feb 2018 21:03:01 +0200 Von: MariaDB Announce List <announce(a)mariadb.org> An: announce(a)mariadb.org The MariaDB project is pleased to announce the availability of MariaDB 10.2.13, the latest stable release in the MariaDB 10.2 series, MariaDB Connector/ODBC 3.0.3, the latest stable release in the MariaDB Connector/ODBC 3.0 series, and MariaDB Connector/ODBC 2.0.16, the latest stable release in the MariaDB Connector/ODBC 2.0 series. See the Release Notes and Changelogs for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.2.13 - Release Notes: https://mariadb.com/kb/en/mdb-10213-rn/ - Changelog: https://mariadb.com/kb/en/mdb-10213-cl/ - Downloads: https://downloads.mariadb.org/mariadb/10.2.13 About MariaDB 10.2: - https://mariadb.com/kb/en/what-is-mariadb-102/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ MariaDB Connector/ODBC 3.0.3 - Release Notes: https://mariadb.com/kb/en/mco-303-rn/ - Changelog: https://mariadb.com/kb/en/mco-303-cl/ - Downloads: https://downloads.mariadb.org/connector-odbc/3.0.3/ MariaDB Connector/C 2.0.16 - Release Notes: https://mariadb.com/kb/en/mco-2016-rn/ - Changelog: https://mariadb.com/kb/en/mco-2016-cl/ - Downloads: https://downloads.mariadb.org/connector-odbc/2.0.16/ About MariaDB Connector/C: - https://mariadb.com/kb/en/about-mariadb-connector-c/ - - MariaDB Books - - - - - - - - - - - - - - - - - - - - - - - - - - There is an ever-growing library of MariaDB books available to help you get the most out of MariaDB. See the MariaDB Books page for details and links: - https://mariadb.com/kb/en/mariadb/books/ - - User Feedback plugin - - - - - - - - - - - - - - - - - - - - - - - MariaDB includes a User Feedback plugin. This plugin is disabled by default. If enabled, it submits basic, completely anonymous MariaDB usage information. This information is used by the developers to track trends in MariaDB usage to better guide development efforts. If you would like to help make MariaDB better, please add "feedback=ON" to your my.cnf or my.ini file! See http://mariadb.com/kb/en/user-feedback-plugin for more information. - - Quality - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The project always strives for quality, but in reality, nothing is perfect. Please take time to report any issues you encounter at: - http://jira.mariadb.org - - Support MariaDB - - - - - - - - - - - - - - - - - - - - - - - - - If you would like to contribute to the MariaDB Foundation, please see the "contributing" and "donations" pages. We also have merchandise available in a cafepress store. All proceeds go to support the MariaDB Foundation. - https://mariadb.com/kb/en/contributing - https://mariadb.org/donate/ We hope you enjoy MariaDB! -- MariaDB Website - http://mariadb.org Twitter - http://twitter.com/mariadbfdn Knowledge Base - http://mariadb.com/kb

2 2

Re: [Maria-discuss] mariadb-backup meta deb package
by Reindl Harald 14 Feb '18

14 Feb '18

Am 14.02.2018 um 12:56 schrieb Florent B: > On 14/02/2018 12:49, Sergei Golubchik wrote: >> Hi, Florent! >> >> On Feb 14, Florent B wrote: >>> Hi, >>> >>> Wouldn't be nice to have a metapackage mariadb-backup in Debian >>> repository, that depends on the current best version >>> (mariadb-backup-10.2, etc..) ? >>> >>> Like "mariadb-server" package in fact. >> Yes, I guess. >> It'll be consistent with what we do with the server. >> >> Of course, if you also have a solution how to reduce the number of >> packages, that'd be perfect :) >> >> Hmm, how about that? >> We rename the following packages to remove "-10.1" at the end. >> >> mariadb-connect-engine-10.1 >> mariadb-oqgraph-engine-10.1 >> mariadb-cracklib-password-check-10.1 >> mariadb-gssapi-server-10.1 >> mariadb-gssapi-client-10.1 >> mariadb-backup-10.1 >> >> This way the server package will specify the version (either >> "mariadb-server-10.1" for 10.1 or "mariadb-server" for the latest) >> and everything else will follow. >> >> Looks logical and doesn't increase the number of packages (!!!). >> But I need to try whether it'll actually work in Debian :) > > Wow ! Someone understood what I mean. Thank you a lot Sergei, I though I > was crazy seeing other people's answers well, i don't get why there needs to be a metapackage at all http://yum.mariadb.org/10.1/rhel6-x86/rpms/ http://yum.mariadb.org/10.2/rhel6-x86/rpms/ these are different repos and you have either 10.1 or 10.2 enabled whatever packages in whatever versions are there, the update manager without mention a version explicit takes the one with the highest number independent of a new install or update %package server Summary: The MariaDB server and related files Requires: %{name} = %{epoch}:%{version}-%{release}, %{name}-libs = %{epoch}:%{version}-%{release} Requires: sh-utils Requires(pre): shadow-utils Provides: mysql-server = %{epoch}:%{version}-%{release}, mysql-server%{?_isa} = %{epoch}:%{version}-%{release} Obsoletes: mysql-server, community-mysql-server %description server "dnf install mysql-server" == "dnf install mariadb-server" == "dnf install community-mysql-server" why? because the Provides/Obsoletes in my spec say so

1 0

Re: [Maria-discuss] mariadb-backup meta deb package
by Reindl Harald 14 Feb '18

14 Feb '18

Am 14.02.2018 um 10:51 schrieb Florent B: > Wouldn't be nice to have a metapackage mariadb-backup in Debian > repository, that depends on the current best version > (mariadb-backup-10.2, etc..) ? > > Like "mariadb-server" package in fact you missed to describe the problem you want to solve by step in with a solution which is always the wrong start because in case you didn't understand the real problem any solution is wrong

2 1

Re: [Maria-discuss] mariadb-backup meta deb package
by Sergei Golubchik 14 Feb '18

14 Feb '18

Hi, Florent! On Feb 14, Florent B wrote: > Hi, > > Wouldn't be nice to have a metapackage mariadb-backup in Debian > repository, that depends on the current best version > (mariadb-backup-10.2, etc..) ? > > Like "mariadb-server" package in fact. Yes, I guess. It'll be consistent with what we do with the server. Of course, if you also have a solution how to reduce the number of packages, that'd be perfect :) Hmm, how about that? We rename the following packages to remove "-10.1" at the end. mariadb-connect-engine-10.1 mariadb-oqgraph-engine-10.1 mariadb-cracklib-password-check-10.1 mariadb-gssapi-server-10.1 mariadb-gssapi-client-10.1 mariadb-backup-10.1 This way the server package will specify the version (either "mariadb-server-10.1" for 10.1 or "mariadb-server" for the latest) and everything else will follow. Looks logical and doesn't increase the number of packages (!!!). But I need to try whether it'll actually work in Debian :) Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

Re: [Maria-discuss] mariadb-backup meta deb package
by Reindl Harald 14 Feb '18

14 Feb '18

Am 14.02.2018 um 12:22 schrieb Florent B: > I'm sorry but that's what not I mean. > > My message was just a suggestion, to have a "meta-package" for Debian > repository, like we have a meta-package "mariadb-server" that points to > "mariadb-server-10.2" (for example, if you installed 10.2 repository). > > In my deployment script, I just install "mariadb-server", without > specifying version. I would like to do the same for "mariadb-backup". > For now, I have to specify version of package : mariadb-backup-10.2. It > could be nice to have a "mariadb-backup" meta-package to point > automatically to the latest version available. stop talking about metapackages DAMNED! you mean "mariadb-backup" in it's last recent point-version should be in the 10.2 or 10.1 repository - why don't you say it then? don't get me wrong but it's hard when people fail to say what they mean and every response is a new riddle > On 14/02/2018 12:08, Vladislav Vaintroub wrote: >> >> The latest backup will not work with your 10.1 server. Mariabackup >> version should match server version that you have on the same box. >> >> mariabackup from 10.1 appears to work with 5.5 and 10.0, but this is >> merely by luck and by accident. >> >> *From: *Florent B <mailto:florent@coppint.com> >> *Sent: *Wednesday, February 14, 2018 11:58 AM >> *To: *maria-discuss(a)lists.launchpad.net >> <mailto:maria-discuss@lists.launchpad.net> >> *Subject: *Re: [Maria-discuss] mariadb-backup meta deb package >> >> On 14/02/2018 11:16, Reindl Harald wrote: >> >> > Am 14.02.2018 um 10:51 schrieb Florent B: >> >> >> Wouldn't be nice to have a metapackage mariadb-backup in Debian >> >> >> repository, that depends on the current best version >> >> >> (mariadb-backup-10.2, etc..) ? >> >> >> Like "mariadb-server" package in fact >> >> > you missed to describe the problem you want to solve by step in with a >> >> > solution which is always the wrong start because in case you didn't >> >> > understand the real problem any solution is wrong >> >> I just want to install the latest mariadb-backup package, without >> >> checking version by myself (10.1, 10.2...). >> >> Exactly the same as the "mariadb-server" metapackage for Debian, which >> >> points to latest version possible.

1 1

Re: [Maria-discuss] mariadb-backup meta deb package
by Federico Razzoli 14 Feb '18

14 Feb '18

I am not sure this is a good idea. Having a meta-package for the server and not one for mariabackup may seem inconsistent, but users are different. A lot of people just want MariaDB and don't (need to) care about versions. Maybe they barely know SQL and just want to run a simple script. For backups, it's different. If you need mariabackup you manage a MariaDB instance, and you need to care about versions. Federico Da: Florent B <florent(a)coppint.com> A: Vladislav Vaintroub <vvaintroub(a)gmail.com>; "maria-discuss(a)lists.launchpad.net" <maria-discuss(a)lists.launchpad.net> Inviato: Mercoledì 14 Febbraio 2018 12:23 Oggetto: Re: [Maria-discuss] mariadb-backup meta deb package I'm sorry but that's what not I mean. My message was just a suggestion, to have a "meta-package" for Debian repository, like we have a meta-package "mariadb-server" that points to "mariadb-server-10.2" (for example, if you installed 10.2 repository). In my deployment script, I just install "mariadb-server", without specifying version. I would like to do the same for "mariadb-backup". For now, I have to specify version of package : mariadb-backup-10.2. It could be nice to have a "mariadb-backup" meta-package to point automatically to the latest version available. On 14/02/2018 12:08, Vladislav Vaintroub wrote: #yiv8725764717 #yiv8725764717 -- _filtered #yiv8725764717 {panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv8725764717 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}#yiv8725764717 #yiv8725764717 p.yiv8725764717MsoNormal, #yiv8725764717 li.yiv8725764717MsoNormal, #yiv8725764717 div.yiv8725764717MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:11.0pt;}#yiv8725764717 a:link, #yiv8725764717 span.yiv8725764717MsoHyperlink {color:blue;text-decoration:underline;}#yiv8725764717 a:visited, #yiv8725764717 span.yiv8725764717MsoHyperlinkFollowed {color:#954F72;text-decoration:underline;}#yiv8725764717 .yiv8725764717MsoChpDefault {} _filtered #yiv8725764717 {margin:70.85pt 70.85pt 56.7pt 70.85pt;}#yiv8725764717 div.yiv8725764717WordSection1 {}#yiv8725764717 The latest backup will not work with your 10.1 server. Mariabackup version should match server version that you have on the same box. mariabackup from 10.1 appears to work with 5.5 and 10.0, but this is merely by luck and by accident. From: Florent B Sent: Wednesday, February 14, 2018 11:58 AM To: maria-discuss(a)lists.launchpad.net Subject: Re: [Maria-discuss] mariadb-backup meta deb package On 14/02/2018 11:16, Reindl Harald wrote: > > > Am 14.02.2018 um 10:51 schrieb Florent B: >> Wouldn't be nice to have a metapackage mariadb-backup in Debian >> repository, that depends on the current best version >> (mariadb-backup-10.2, etc..) ? >> >> Like "mariadb-server" package in fact > > you missed to describe the problem you want to solve by step in with a > solution which is always the wrong start because in case you didn't > understand the real problem any solution is wrong I just want to install the latest mariadb-backup package, without checking version by myself (10.1, 10.2...). Exactly the same as the "mariadb-server" metapackage for Debian, which points to latest version possible. _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss(a)lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss(a)lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

1 0

Re: [Maria-discuss] mariadb-backup meta deb package
by Reindl Harald 14 Feb '18

14 Feb '18

Am 14.02.2018 um 11:58 schrieb Florent B: > On 14/02/2018 11:16, Reindl Harald wrote: >> >> Am 14.02.2018 um 10:51 schrieb Florent B: >>> Wouldn't be nice to have a metapackage mariadb-backup in Debian >>> repository, that depends on the current best version >>> (mariadb-backup-10.2, etc..) ? >>> >>> Like "mariadb-server" package in fact >> >> you missed to describe the problem you want to solve by step in with a >> solution which is always the wrong start because in case you didn't >> understand the real problem any solution is wrong > > I just want to install the latest mariadb-backup package, without > checking version by myself (10.1, 10.2...). > Exactly the same as the "mariadb-server" metapackage for Debian, which > points to latest version possible besides that "mariadb-server" for sure is not a metapackage i doubt and hope for debian users that you don't get *major upgrade* and 10.1 to 10.2 *is* a major upgrade nobody upgrades just by the way you heavily confuse point updates and major upgrades 10.1 versus 10.2 is practically the same as 10 versus 11 just with different numbers but must not be confused with 10.1.10 to 10.1.11 and when you are at it learn what a meta-package is because in communication it's important to know what you are talking about https://askubuntu.com/questions/66257/what-is-the-difference-between-a-meta…

1 0

Re: [Maria-discuss] mariadb-backup meta deb package
by Vladislav Vaintroub 14 Feb '18

14 Feb '18

The latest backup will not work with your 10.1 server. Mariabackup version should match server version that you have on the same box. mariabackup from 10.1 appears to work with 5.5 and 10.0, but this is merely by luck and by accident. From: Florent B Sent: Wednesday, February 14, 2018 11:58 AM To: maria-discuss(a)lists.launchpad.net Subject: Re: [Maria-discuss] mariadb-backup meta deb package On 14/02/2018 11:16, Reindl Harald wrote: > > > Am 14.02.2018 um 10:51 schrieb Florent B: >> Wouldn't be nice to have a metapackage mariadb-backup in Debian >> repository, that depends on the current best version >> (mariadb-backup-10.2, etc..) ? >> >> Like "mariadb-server" package in fact > > you missed to describe the problem you want to solve by step in with a > solution which is always the wrong start because in case you didn't > understand the real problem any solution is wrong I just want to install the latest mariadb-backup package, without checking version by myself (10.1, 10.2...). Exactly the same as the "mariadb-server" metapackage for Debian, which points to latest version possible. _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss(a)lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

1 0

[Maria-discuss] [GSoC Idea Discussion] "mcsapi needs a new read API" project
by Udit Juneja 14 Feb '18

14 Feb '18

Hi, I am Udit Juneja, a Compter Science undergraduate student at Thapar Institute of Engineering and Technology, India. I am interested in contributing to MariaDB. A brief introduction about me: I am familiar with programming languages (C, C++, Python, PyCUDA, MySQL). I have won many hackathons. I am interested in "mcsapi needs a new read API" project. I have already started exploring MariaDB, and the APIs which are currently published on the website. I would like to discuss the idea further and maybe submit a proposal in GSoC later. Regards, Udit Juneja

3 2

[Maria-discuss] Google Summer of Code 2018 - MariaDB Foundation accepted
by Colin Charles 14 Feb '18

14 Feb '18

Hi, I just noticed this piece of good news — MariaDB Foundation is accepted to participate in the Google Summer of Code 2018 again — https://summerofcode.withgoogle.com/ There is an already healthy list of ideas: https://mariadb.com/kb/en/library/google-summer-of-code-2018/ Many already have mentors from MariaDB Corporation or MariaDB Foundation. Some are looking for mentors. Happy coding this summer! cheers, -colin -- Colin Charles, http://bytebot.net/blog/ twitter: @bytebot | skype: colincharles "First they ignore you, then they laugh at you, then they fight you, then you win." -- Mohandas Gandhi

1 0

Re: [Maria-discuss] System versioned tables inquiry
by serg＠mariadb.org 12 Feb '18

12 Feb '18

Hi, Benjamin! First: please send questions like this to maria-discuss(a)lists.launchpad.net it's a public mailing list dedicated to MariaDB. I am subscribed, so I'll see you mail there, and you may be sure I will, because it won't be accidentally catched by my spam filter, or sorted out in some obscure folder. Furthermore other subscribers will see your question and could reply if I will be not available (e.g. I could be travelling). Thank you. On Feb 05, bartels.benjamin(a)tutanota.de wrote: > Hello Mr. Golubchik, > I have a couple of questions on MariaDB's implementation of the system > versioned tables if you are not busy. > 1. Is there any mechanism in the current implementation to insert rows > with custom values for ROW START and ROW END? I know that since they > are automatically implemented as auto generated columns that they are > not allowed to be modified. But I feel like this would make backup > migration very hard, since you cannot insert into these generated > columns. Maybe I am missing something but from what I can tell it is > not possible at the moment.MSSQL from my understanding doesn't > restrict the ROW START and ROW END columns to generated columns so it > is possible to insert some data into those columns and then alter the > table to make it system versioned.See (Listing > 1): http://www.itprotoday.com/microsoft-sql-server/first-look-system-versioned-… No, there is none. It is intentional, to protect the integrity of the history. SQL Standard, as far as I know, does not provide any way of falsifying the history of system versioned tables. There is another feature in the standard: application-time period tables, in there one can provide arbitrary values for start and end timestamps. But MariaDB 10.3 doesn't support application-time period tables. As for backups, yes, I agree, it's a problem. Binary backups will preserve the history just fine, but mysqldump backups will not, they'll reset the history. > 2. What is supposed to be done in 2038 when NOW() will surpass the ROW > END column? Will there be a way to have a bigger underlying datatype > to represent the historic columns? That's the common issue for any TIMESTAMP columns. We don't have any solution specific to system versioned tables. I could *guess* that closer to that date TIMESTAMP width will be increased and, perhaps, there will be some migration procedure in mysql_uprade. There are no specific plans yet. Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

[Maria-discuss] [MARIADB 5.5] Profiling truncated
by Fabien CLERC 12 Feb '18

12 Feb '18

Hello, I'm trying to profile a very long query in production (MariaDB 5.5) using "set session profiling = 1" but when I run the command "show profile for query 1", there is only the only 100 last steps of the query. It's the same checking in "INFORMATION_SCHEMA.PROFILING" I understand the parameter "profiling_history_size" is the maximum number of queries and not the maximum steps stored fora query. => How can I change the number of steps available in the result of the profiling ? Thanks and Regards *Fabien CLERC* <https://www.facebook.com/Yieloo-184624595039051/?fref=ts> <https://twitter.com/intraknow> <https://plus.google.com/+Intraknow?hl=fr> fabien.clerc(a)yieloo.com ____________________________________________________________ *Editeur de la solution intra'know* Espace performance 3 - Bâtiment Z - 35769 Saint-Grégoire www.intraknow.com <https://www.intra-know.com/yieloo/admin/cciaux/modif/www.intraknow.com> 02 99 55 15 03

2 1

[Maria-discuss] Clarification about have_ssl and have_openssl variables
by Peter Laursen 11 Feb '18

11 Feb '18

MySQL documentation states https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar… simply states "This variable is an alias for have_ssl.". I checked with a few Oracle/MySQL installations that the two variables have identical values. But this is not the case on MariaDB (I checked my local Windows installs of 10.1 and 10.2). have_openssl is NO - have_ssl is YES|DISABLED. We believe that this is the reason for a newly reported problem to us: SSL-connections is not possibe to MariaDB on Amazon RDS. We verified the problem with a fresh RDS instance with MariaDB 10.2 and running with SSL enabled. The 3 different 'mysql' CLI's give results: *MariaDB 10.2.6 version:* - without ssl, gives 'Access denied error' - with ssl, it gives Error No. 2026: SSl Connection error: Certificate signature check failed *using MySQL 5.7.x version:* it connects to the server irrespective of the ssl parameter passed or not (but we don't know if SSL is actually used. It may silently connect without SSL if SSL is not possible) *MariaDB 10.1.22 version:* without ssl: it gives 'Access denied error' with ssl (Works as expected) Any thoughts on this? What can we do? We are affected as we use MariaDB Connector/C 2.33 currently -- Peter -- Webyog

2 1

[Maria-discuss] MariaDB server 10.1.31 a little question ?
by Ljr Yang 08 Feb '18

08 Feb '18

Hi, There two little qustion in MariaDB 10.1.31, please confirm, thanks. 1) After install MariaDB 10.1.31 , in error log show xtradb version 5.6.36-83.0 ? it should 5.6.38-83.0 2018-02-07 19:07:47 140196300970016 [Note] InnoDB: Percona XtraDB ( http://www.percona.com) 5.6.36-83.0 started; log seque nce number 6162730 2) in the web page , show " Download MariaDB Server 10.2.10" , it should "10.0.34 Galera cluster", https://mariadb.com/resources/blog/mariadb-server-10131-and-mariadb-galera-…

2 1

[Maria-discuss] MariaDB threads optimalization
by Brano Gege 07 Feb '18

07 Feb '18

Hi All, I got problem with mariadb performance, NEW (pcie) server: Intel(R) Xeon(R) CPU E5-2637 v4 @ 3.50GHz, 1TB NVMe disks, 128 GB RAM, installed Debian 4.9.65-3+deb9u1, Ver 15.1 Distrib 10.1.26-MariaDB OLD server: Intel(R) Xeon(R) CPU E5-1630 v3 @ 3.70GHz, SSD disk, 64 GB RAM, FreeBSD 11.0-STABLE, 10.1.21-MariaDB On servers is running just mysql, I copy my.ini file, config files are same. mysql slap results for example employees db: #less /root/slap/select_query.sql SELECT emp_no, first_name, last_name, gender FROM employees LIMIT 10; SELECT emp_no, first_name, last_name, gender FROM employees ORDER BY last_name ASC LIMIT 10; SELECT COUNT(emp_no) FROM employees WHERE last_name = 'Aamodt'; SELECT last_name, COUNT(emp_no) AS num_emp FROM employees GROUP BY last_name ORDER BY num_emp DESC LIMIT 10; SELECT employees.* FROM employees LEFT JOIN dept_emp ON ( dept_emp.emp_no = employees.emp_no ) LEFT JOIN salaries ON ( salaries.emp_no = salaries.emp_no ) WHERE employees.first_name LIKE '%Jo%' AND salaries.from_date > '1993-01-21' AND salaries.to_date < '1998-01-01' LIMIT 0, 100; root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=1 Average number of seconds to run all queries: 0.459 seconds DB1: Average number of seconds to run all queries: 0.627 seconds root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=2 Benchmark Average number of seconds to run all queries: 0.473 seconds DB1: Average number of seconds to run all queries: 0.626 seconds root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=4 Average number of seconds to run all queries: 0.486 seconds DB1: Average number of seconds to run all queries: 0.656 seconds root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=8 Average number of seconds to run all queries: 0.569 seconds DB1: Average number of seconds to run all queries: 1.136 seconds root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=16 Benchmark Average number of seconds to run all queries: 0.948 seconds DB1: Average number of seconds to run all queries: 1.750 seconds root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=32 Average number of seconds to run all queries: 1.650 seconds DB1: Average number of seconds to run all queries: 2.455 seconds root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=64 Average number of seconds to run all queries: 3.306 seconds DB1: Average number of seconds to run all queries: 3.176 seconds root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=128 Average number of seconds to run all queries: 6.744 seconds DB1: Average number of seconds to run all queries: 5.737 seconds root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=256 Average number of seconds to run all queries: 13.474 seconds (verified 2nd run: 12.883 seconds) DB1: Average number of seconds to run all queries: 3.451 seconds (verified 2nd run: 4.935 seconds) root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=512 Average number of seconds to run all queries: 26.085 seconds (verified 2nd run: 26.307 seconds) DB1: Average number of seconds to run all queries: 15.862 seconds (verified 2nd run: 11.280 seconds) as you can see with raising threads PCIE performance is lowering rapidly. Any idea what to check? Thank you. I post this question couple of days with more information also here: https://serverfault.com/questions/895506/mariadb-threads-optimalization

4 7

[Maria-discuss] Triangular FKs - Cascade delete leaves DB in state with broken referential integrity
by Oliver Schonrock 05 Feb '18

05 Feb '18

I have come across a very strange symptom with triangular FKs (see below) where a cascade delete of the central (member) record leaves the DB in a state with broken referential integrity. The FK structure is a little bit demanding and somewhat "triangular/circular", but has been in operation in our application for many years without problems. After recent upgrade from 10.1 to 10.2.12, we promptly got broken FKs. (I have not yet retested with 10.1). How to reproduce: (example reduced to the bare essentials with 3 tables with 1 record each). SET FOREIGN_KEY_CHECKS=0; CREATE TABLE member ( id int(11) NOT NULL AUTO_INCREMENT, default_address_id int(11) DEFAULT NULL, default_card_id int(11) DEFAULT NULL, PRIMARY KEY (id), KEY member_FI_2 (default_address_id), KEY member_FI_3 (default_card_id), CONSTRAINT member_FK_2 FOREIGN KEY (default_address_id) REFERENCES address (id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT member_FK_3 FOREIGN KEY (default_card_id) REFERENCES payment_method (id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB; INSERT INTO member VALUES (1,2,3); CREATE TABLE address ( id int(11) NOT NULL AUTO_INCREMENT, member_id int(11) NOT NULL, PRIMARY KEY (id), KEY address_FI_1 (member_id), CONSTRAINT address_FK_1 FOREIGN KEY (member_id) REFERENCES member (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB; INSERT INTO address VALUES (2,1); CREATE TABLE payment_method ( id int(11) NOT NULL AUTO_INCREMENT, member_id int(11) NOT NULL, cardholder_address_id int(11) DEFAULT NULL, PRIMARY KEY (id), KEY payment_method_FI_1 (member_id), KEY payment_method_FI_2 (cardholder_address_id), CONSTRAINT payment_method_FK_1 FOREIGN KEY (member_id) REFERENCES member (id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT payment_method_FK_2 FOREIGN KEY (cardholder_address_id) REFERENCES address (id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB; INSERT INTO payment_method VALUES (3,1,2); SET FOREIGN_KEY_CHECKS=1; SELECT * FROM member; +----+--------------------+-----------------+ | id | default_address_id | default_card_id | +----+--------------------+-----------------+ | 1 | 2 | 3 | +----+--------------------+-----------------+ SELECT * FROM address; +----+-----------+ | id | member_id | +----+-----------+ | 2 | 1 | +----+-----------+ SELECT * FROM payment_method; +----+-----------+-----------------------+ | id | member_id | cardholder_address_id | +----+-----------+-----------------------+ | 3 | 1 | 2 | +----+-----------+-----------------------+ DELETE FROM member WHERE id = 1; SELECT * FROM member; [ empty ] SELECT * FROM address; [ empty ] SELECT * FROM payment_method; +----+-----------+-----------------------+ | id | member_id | cardholder_address_id | +----+-----------+-----------------------+ | 3 | 1 | NULL | +----+-----------+-----------------------+ ------------ DB is now in an inconsistent state: payment_method.member_id=1 references a non-existent member record. Am I missing something, or should this never happen? Thanks -- Oliver Schönrock

1 2

[Maria-discuss] Aurora 'rds.amazonaws.com' string detection
by Evan Burke 31 Jan '18

31 Jan '18

Hi, I recently resolved an issue with an application using the MariaDB JDBC driver being unable to connect to an Amazon Aurora MySQL-compatible database (single instance, not cluster). This turned out to be caused by using 'dblink.[examplehostname].com' for my Aurora connection string, rather than the ...rds.amazonaws.com string checked for in UrlParser.java - https://github.com/MariaDB/mariadb-connector-j/blob/386819936ffad08d0ac2134… I see the code comment indicating that there are problems with detecting Aurora via other methods, as well as compatibility issues preventing the ability to connect to Aurora without a special case, so to some extent I can understand the decision to use hostname or haMode detection. But I wanted to point out that this approach comes with downsides - particularly when it is not documented anywhere that I can see. We had to review the code itself on Github in order to identify the solution, and that's a problem. While I don't expect my experience to be the same as most users, I can't be the only one using an alternate hostname or an IP address to connect, or the only one making the reasonable assumption that the driver is hostname-agnostic. The other consideration - which I am admittedly making somewhat naively - is that the MySQL JDBC driver is able to connect to this Aurora instance without any problems, regardless of hostname. From the perspective of an end user who doesn't care too much about implementation details, I'm probably going to prefer the option that doesn't exhibit unexpected behavior. In any case - despite the frustrating experience I had - I am grateful to everyone who's contributed to the MariaDB project, and I hope my feedback is helpful. -Evan

1 0

[Maria-discuss] Fwd: mysql_insert_id() for INSERT...SELECT statement
by Yuki . 30 Jan '18

30 Jan '18

Hi, I made a post at https://stackoverflow.com/questions/48306962/mysql- insert-id-for-insert-select-statement, but have not received any helpful answer, may be you could help me. I am using MaridDB but I read MySQL documentation because it has much more elaborated documentation than the same page for MariaDB. I am doing similar INSERT...SELECT query to this INSERT INTO table (value1, value2) SELECT 'stuff for value1', 'stuff for value2' FROM DUAL WHERE NOT EXISTS (SELECT * FROM table WHERE value1='stuff for value1' AND value2='stuff for value2') LIMIT 1 , where table has auto-genrated id. And I would like to know if it was inserted or not, of course. I assume the way to do that is to use `mysql_insert_id()`. It returns 0 if no insertions happen and 1 if insertions happen. Looking more details [here]( https://dev.mysql.com/doc/refman/5.7/en/mysql-insert-id.html) > If an INSERT ... SELECT statement is executed, and **NO** automatically > generated value is successfully inserted, mysql_insert_id() **RETURNS** > the ID of the last inserted row. What does it return if no auto-generated ID was successfully inserted? Is this a doc typo? So far I did testing in C and `mysql_insert_id()` returns always 0 if insertion did not happen even if the last insertion succeeded and `mysql_insert_id()` returned non-zero result. A paragraphs in the same manual, mentioned above, confirms this behavior by saying: > mysql_insert_id() returns 0 if the previous statement does not use an AUTO_INCREMENT value. .... > > The value of mysql_insert_id() is affected only by statements issued within the current client connection. It is not affected by statements issued by other clients. > > The LAST_INSERT_ID() SQL function will contain the value of the first automatically generated value that was successfully inserted. LAST_INSERT_ID() is not reset between statements because the value of that function is maintained in the server. .... And that feels kind of logical otherwise `INSERT...SELECT` would be useless in many cases, if you cannot know within the code if your insertion worked or not. But it totally contradicts to the statement above. Did anyone have experience with this? >From MariaDB manual, also suggests that the value should be zero in case of insertion did not happen: > The mysql_insert_id() function returns the ID generated by a query on > a table with a column having the AUTO_INCREMENT attribute or the value > for the last usage of LAST_INSERT_ID(expr). If the last query wasn't > an INSERT or UPDATE statement or if the modified table does not have a > column with the AUTO_INCREMENT attribute and LAST_INSERT_ID was not > used, this function will return zero. Does `mysql_insert_id()` of MariaDB returns 0 for `INSERT...SELECT` if an insertion did not happen, or it might return some other non-zero value? Do you think it is a typo at MySQL documentation? Best, Yuki

2 3

[Maria-discuss] Replication set up to ignore Delete statements
by Furlong, John 25 Jan '18

25 Jan '18

I am new to MariaDB and am wondering if it's possible to set up replication to only replicate INSERTS & UPDATES? Can Deletes be ignored? The purpose is to keep the primary DB small and the secondary DB would be a data warehouse. Does --binlog-format=STATEMENT mean all writes? I've been reading the documention but haven't been able to find a definitive answer. If Delete statements are executed by a different user is it possible to ignore them that way such as through the CURRENT_USER()<https://mariadb.com/kb/en/current_user/> function? Thanks. John

1 0

[Maria-discuss] ALTER TABLE ENIGNE=InnoDB performance
by Conor Murphy 25 Jan '18

25 Jan '18

Hi, We're looking at changing an existing database from MyISAM to InnoDB. The existing system is using MariaDB 5.5.56 on RHEL 7.4 The plan is to - create a replication slave on the same host - use ALTER TABLE .. ENGINE=InnoDB on the slave, with 4 ALTERs running concurrently - replace the master with the "coverted" slave Doing this with a small database (~ 3.5GB) works okay with the largest table taking ~ 7 minutes to convert. However, while trying to figure out how to replicate from the new master, we found that we need "FLUSH TABLES .. FOR EXPORT" which isn't available in MariaDB 5.5. So we updated to MariaDB 10.2.12 and started again. Now when we run the "ALTER TABLE .. ENIGNE=InnoDB", it takes hours from the ALTERs to complete and we're getting the following log entries 2018-01-09 12:36:03 140132036818688 [ERROR] InnoDB: Cannot save table statistics for table `statsdb`.`#sql-6d1a_12#P#P20160101` /* Partition `P20160101` */: Lock wait timeout 2018-01-09 12:36:54 140132036515584 [ERROR] InnoDB: Cannot save table statistics for table `statsdb`.`#sql-6d1a_13#P#P20160101` /* Partition `P20160101` */: Lock wait timeout 2018-01-09 12:37:45 140132036818688 [ERROR] InnoDB: Cannot save table statistics for table `statsdb`.`#sql-6d1a_12#P#P20160601` /* Partition `P20160601` */: Lock wait timeout 2018-01-09 12:38:36 140132036818688 [ERROR] InnoDB: Cannot save table statistics for table `statsdb`.`#sql-6d1a_12#P#P20160701` /* Partition `P20160701` */: Lock wait timeout 2018-01-09 12:39:27 140132037728000 [ERROR] InnoDB: Cannot save table statistics for table `statsdb`.`#sql-6d1a_11#P#P20160701` /* Partition `P20160701` */: Lock wait timeout ... Any ideas on what's wrong here? Thanks, Conor

3 8

[Maria-discuss] MariaDB 10.3.4 consumes extremely much CPU on Windows.
by Peter Laursen 21 Jan '18

21 Jan '18

On a 4-core (4 physical, 8 logical cores), 3.5 Ghz box, MariaDB 10.3.4 consumes typically 35-40% of available CPU even when completely idle according to Task Manager. Please see image. I cannot tell if previous 10.3 versions also did, as I don't have 10.3 running constantly in the backgorund. I just observed this now. This does not happen with MariaDB 10.1, MariaDB 10.2, MySQL 5.6, MySQL 5.7 and MySQL 8.0 (all those I have starting with Windows on this box), where the CPU-load is neglible unless the server is "doing real work". As a result, the CPU clock frequency is always highest possible 3.5 Ghz (this CPU model is a 4th generation Intel with the option to scale down CPU frequency to around 40-50% of max. when there is no need for more) when MariaDB 10.3.4 is running. -- Peter -- Webyog

2 7

[Maria-discuss] OpenLDAP & PAM authentication
by Adam Balgach 19 Jan '18

19 Jan '18

Hello – I am having an issue configuring openLDAP to authenticate users on an instance in RHEL (CentOS 7) MariaDB server configured with plugin-load=auth_pam.so in my /etc/pam.d/mysql: auth required pam_ldap.so account required pam_ldap.so when I try and access the server from either the localhost, or any server inside my internal network, I see the LDAP authentication happen without issue and login is fine. When I try and access the server from outside the network, on the mariadb server in /var/log/secure I see the following: Jan 19 08:32:35 mysqld: pam_ldap(mysql:auth): unexpected response from failed conversation function Jan 19 08:32:35 mysqld: pam_ldap(mysql:auth): conversation failed Jan 19 08:32:35 mysqld: pam_ldap(mysql:auth): failed to get password: Authentication token manipulation error And in my client (Cygwin) I get the following error: ERROR 2059 (HY000): Authentication plugin 'mysql_clear_password' cannot be loaded: No such file or directory However I see this plugin in the /usr/lib64/mysql/plugin directory Any idea what is going wrong with remote logins? Thanks-

2 1

[Maria-discuss] MariaDB 10.3.4 installation blocked ny MS "Windows Defender" malware protection
by Peter Laursen 19 Jan '18

19 Jan '18

Please see image. (you can clik "more info" adn the option to "install anyway" will appear. Did you forget to sign the installer this time or has some certificate expired? -- Peter -- Webog

2 5

Re: [Maria-discuss] Parallel Databases and network security
by Reindl Harald 16 Jan '18

16 Jan '18

Am 16.01.2018 um 05:10 schrieb Jan Steinman: >> On 2018-01-15, at 17:15, Reindl Harald <h.reindl(a)thelounge.net> wrote: >> >> Am 16.01.2018 um 01:58 schrieb Jan Steinman: >>>> On 2018-01-15, at 16:37, Ruben Safir <ruben(a)mrbrklyn.com> wrote: >>>> >>>> Because that is how I want it. The longer explanation is, because that >>>> is how I want to do it, period. >>> You have the freedom to be foolish. And I have the freedom to not help you be foolish >> >> forget it - that fool obviously never wants answers to his questions nor tries to understand what people tell him > > He doesn’t understand how TCP/IP and routed works, either: “I'd like it to only listen on a specific interface altogether.” > > That’s okay. It’s been cheap entertainment for me! :-) :-) however, it's heartwarming that someone who don't want anserws from humans types his questions not in a search engine like https://www.google.at/search?q=mysql+listen gicen that the first hit is not too bad... https://www.howtogeek.com/howto/mysql/switch-mysql-to-listen-on-tcp/

3 8

[Maria-discuss] Parallel Databases and network security
by Ruben Safir 16 Jan '18

16 Jan '18

Is there a way to hot copy a database from one machine to another and restrict network access to only between those two machines without using a firewall? Ruben -- So many immigrant groups have swept through our town that Brooklyn, like Atlantis, reaches mythological proportions in the mind of the world - RI Safir 1998 http://www.mrbrklyn.com DRM is THEFT - We are the STAKEHOLDERS - RI Safir 2002 http://www.nylxs.com - Leadership Development in Free Software http://www2.mrbrklyn.com/resources - Unpublished Archive http://www.coinhangout.com - coins! http://www.brooklyn-living.com Being so tracked is for FARM ANIMALS and and extermination camps, but incompatible with living as a free human being. -RI Safir 2013

3 6

Re: [Maria-discuss] Parallel Databases and network security
by Reindl Harald 16 Jan '18

16 Jan '18

Am 16.01.2018 um 01:58 schrieb Jan Steinman: >> On 2018-01-15, at 16:37, Ruben Safir <ruben(a)mrbrklyn.com> wrote: >> >> Because that is how I want it. The longer explanation is, because that >> is how I want to do it, period. > > You have the freedom to be foolish. And I have the freedom to not help you be foolish forget it - that fool obviously never wants answers to his questions nor tries to understand what people tell him - leaves the questions why someone is aksing at all isntead just RTFM in silent in the thread below i did not say anything about that he should use systemd but how systemd does create the folder and why it should go to a subfolder and why that subfolder needs to created at every boot before try to start mysqld and his best answer was "I've seen already far too many security breakins of systemd" which i call a uneducated lie based on FUD https://www.mail-archive.com/maria-discuss@lists.launchpad.net/msg04760.html

1 0

[Maria-discuss] Submitting talks for Percona Live Santa Clara?
by Colin Charles 09 Jan '18

09 Jan '18

Hi all, As part of the program committee, I’m writing in to say we’ve seen only a handful of talks mention MariaDB Server out of the several hundred submissions so far. With all the amazing use cases of MariaDB Server out there, I would expect a lot more. The CFP ends January 12 2018, so please feel free to get ideas and submissions in: https://www.percona.com/live/18/ Kind Regards, Colin Charles -- Colin Charles, http://bytebot.net/blog/ twitter: @bytebot | skype: colincharles "First they ignore you, then they laugh at you, then they fight you, then you win." -- Mohandas Gandhi

3 2

[Maria-discuss] Problem with bit operations in certain expressions
by Mats Eklund 09 Jan '18

09 Jan '18

Hi, Need some help here. I have the following expression: SELECT IF(1=1, 0x2, 0x4) | 0x1 which in MySQL evaluates to: 3 but in MariaDB it evaluates to: 1 MySQL's result seems correct to me. Would appreciate any advice on this. Many thanks! Mats

3 2

[Maria-discuss] mariadb-connector-c mariadb_config: Faulty output with static gssapi plugin
by Stefan Scherfke 08 Jan '18

08 Jan '18

Hi all, the output of "mariadb_config --libs[_r]" is faulty if the AUTH_GSSAPI plugin is configured as static: I built mariadb-connector-c 3.0.2 on Fedora 26 with this command: mkdir build cd build cmake -DAUTH_GSSAPI=STATIC .. make The output of "mariadb_config" then looks like this: $ mariadb_config/mariadb_config --libs_r -L/usr/local/lib/mariadb/ -lmariadb -lpthread -ldl -lm -lssl -lcrypto -l-lgssapi_krb5 -lkrb5 -lk5crypto -lcom_err All libs should be named like "-l<name>" but gssapi_krb5 has a double "-l-l" prefix. This problems becomes more apparent when I build mariadb-connector-c as a Python Conda package. Without the "AUTH_GSSAPI=STATIC" flag, I get the following libs: -L/opt/emsconda/conda-bld/mariadb-connector-c/env/lib/ -lmariadb -lpthread -ldl -lm -lssl -lcrypto With the "AUTH_GSSAPI=STATIC" flag, I get the following libs: -L/opt/emsconda/conda-bld/mariadb-connector-c/env/lib/ -lmariadb -lpthread -ldl -lm -lssl -lcrypto -l-L/opt/emsconda/conda-bld/mariadb-connector-c/envplaceho/lib -Wl,--enable-new-dtags -Wl,-rpath -Wl,/opt/emsconda/conda-bld/mariadb-connector-c/envplaceho/lib -lgssapi_krb5 -lkrb5 -lk5crypto -lcom_err The problematic part there is the "-l-L/opt/emsconda/.../lib" part which confuses other tools like Mydumper when they are compiled against mariadb-connector-c. Is this an issue with mariadb-connector-c or can I fix it in my build process somehow? Cheers, Stefan

2 2

[Maria-discuss] Fwd: [MariaDB Announce] MariaDB 10.2.12 now available
by Reindl Harald 04 Jan '18

04 Jan '18

and once again a update don't build with the same rpm-spec as before - frankly how often should i offer that someone sends out a mail to the ML with a link to a proposed release tarball to get such things fixed *before* release? + cmake . -DFEATURE_SET=large -DCMAKE_INSTALL_PREFIX=/usr -DINSTALL_INCLUDEDIR=include/mysql -DINSTALL_LAYOUT=RPM -DDAEMON_NAME=mysqld -DDAEMON_NO_PREFIX=mysqld -DNICE_PROJECT_NAME=MariaDB -DINSTALL_LIBDIR=lib64/mysql -DINSTALL_MANDIR=share/man -DINSTALL_MYSQLSHAREDIR=share/mysql -DINSTALL_MYSQLTESTDIR=share/mysql-test -DINSTALL_PLUGINDIR=lib64/mysql/plugin -DINSTALL_SBINDIR=libexec -DINSTALL_SCRIPTDIR=bin -DINSTALL_SQLBENCHDIR= -DINSTALL_SUPPORTFILESDIR=share/mysql -DMYSQL_DATADIR=/var/lib/mysql -DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock -DENABLED_PROFILING=OFF -DENABLE_DEBUG_SYNC=OFF -DENABLE_DTRACE=OFF -DPLUGIN_ARIA=YES -DPLUGIN_CSV=YES -DPLUGIN_MYISAM=YES -DPLUGIN_ARCHIVE=NO -DPLUGIN_BLACKHOLE=NO -DPLUGIN_CASSANDRA=NO -DPLUGIN_CONNECT=NO -DPLUGIN_EXAMPLE=NO -DPLUGIN_FEDERATED=NO -DPLUGIN_FEDERATEDX=NO -DPLUGIN_FEEDBACK=NO -DPLUGIN_MROONGA=NO -DPLUGIN_MYISAMMRG=NO -DPLUGIN_OQGRAPH=NO -DPLUGIN_PARTITION=NO -DPLUGIN_PERFSCHEMA=NO -DPLUGIN_ROCKSDB=NO -DPLUGIN_SEMISYNC=NO -DPLUGIN_SEQUENCE=NO -DPLUGIN_SPHINX=NO -DPLUGIN_SPIDER=NO -DPLUGIN_TOKUDB=NO -DPLUGIN_XTRADB=NO -DWITHOUT_DYNAMIC_PLUGINS=ON -DWITH_ATOMIC_OPS=smp -DWITH_EMBEDDED_SERVER=OFF -DWITH_INNODB_DISALLOW_WRITES=OFF -DWITH_INNODB_BZIP2=OFF -DWITH_INNODB_LZ4=OFF -DWITH_INNODB_LZMA=OFF -DWITH_INNODB_LZO=OFF -DWITH_INNODB_SNAPPY=OFF -DWITH_MYSQLCOMPAT=ON -DSECURITY_HARDENED=OFF -DWITH_LIBARCHIVE=OFF -DWITH_LIBWRAP=OFF -DWITH_MARIABACKUP=OFF -DWITH_PIC=NO -DWITH_READLINE=OFF -DWITH_SAFEMALLOC=OFF -DWITH_VALGRIND=OFF -DWITH_WSREP=OFF -DWITH_JEMALLOC=OFF -DWITH_SSL=system -DWITH_ZLIB=system -DCOMPILATION_COMMENT=thelounge [ 99%] Built target sql Scanning dependencies of target explain_filename-t Scanning dependencies of target mysqld [ 99%] Building CXX object sql/CMakeFiles/mysqld.dir/main.cc.o [100%] Linking CXX executable mysqld [100%] Building CXX object unittest/sql/CMakeFiles/explain_filename-t.dir/explain_filename-t.cc.o [100%] Linking CXX executable explain_filename-t libsql.a(sql_yacc.cc.o): In function .`MYSQLparse.(/THD.*.)/'sql:/ libsql.a/(homesql_yacc.cc.o/)buildbot:/ Ingit /functionsql /`sql_yacc.yyMYSQLparse(:THD*5211):' :undefined /referencehome /tobuildbot /`gitpartition_info/sql:/:sql_yacc.yyerror_if_requires_values:5211(: )undefined referenceconst 'to `partition_info::error_if_requires_values() const' collect2: error: ld returned 1 exit status collect2: error: ld returned 1 exit status make[2]: *** [sql/CMakeFiles/mysqld.dir/build.make:115: sql/mysqld] Error 1 make[2]: *** [unittest/sql/CMakeFiles/explain_filename-t.dir/build.make:116: unittest/sql/explain_filename-t] Error 1 make[1]: *** [CMakeFiles/Makefile2:8464: sql/CMakeFiles/mysqld.dir/all] Error 2 make[1]: *** Waiting for unfinished jobs.... make[1]: *** [CMakeFiles/Makefile2:1336: unittest/sql/CMakeFiles/explain_filename-t.dir/all] Error 2 -------- Weitergeleitete Nachricht -------- Betreff: [MariaDB Announce] MariaDB 10.2.12 now available Datum: Thu, 4 Jan 2018 19:20:40 +0200 Von: MariaDB Announce List <announce(a)mariadb.org> An: announce(a)mariadb.org The MariaDB project is pleased to announce the availability of MariaDB 10.2.12, the latest stable release in the MariaDB 10.2 series. See the Release Notes and Changelogs for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.2.12 - Release Notes: https://mariadb.com/kb/en/mdb-10212-rn/ - Changelog: https://mariadb.com/kb/en/mdb-10212-cl/ - Downloads: https://downloads.mariadb.org/mariadb/10.2.12 About MariaDB 10.2: - https://mariadb.com/kb/en/what-is-mariadb-102/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ - - MariaDB Books - - - - - - - - - - - - - - - - - - - - - - - - - - There is an ever-growing library of MariaDB books available to help you get the most out of MariaDB. See the MariaDB Books page for details and links: - https://mariadb.com/kb/en/mariadb/books/ - - User Feedback plugin - - - - - - - - - - - - - - - - - - - - - - - MariaDB includes a User Feedback plugin. This plugin is disabled by default. If enabled, it submits basic, completely anonymous MariaDB usage information. This information is used by the developers to track trends in MariaDB usage to better guide development efforts. If you would like to help make MariaDB better, please add "feedback=ON" to your my.cnf or my.ini file! See http://mariadb.com/kb/en/user-feedback-plugin for more information. - - Quality - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The project always strives for quality, but in reality, nothing is perfect. Please take time to report any issues you encounter at: - http://jira.mariadb.org - - Support MariaDB - - - - - - - - - - - - - - - - - - - - - - - - - If you would like to contribute to the MariaDB Foundation, please see the "contributing" and "donations" pages. We also have merchandise available in a cafepress store. All proceeds go to support the MariaDB Foundation. - https://mariadb.com/kb/en/contributing - https://mariadb.org/donate/ We hope you enjoy MariaDB! -- MariaDB Website - http://mariadb.org Twitter - http://twitter.com/mariadbfdn Knowledge Base - http://mariadb.com/kb

1 0

[Maria-discuss] Is this normal behaviour?
by Karthick Subramanian 04 Jan '18

04 Jan '18

All, When I tried to connect in centos 7 mariadb 10.2.11 server using root user, by mistake I gave wrong user name, but it connected successfully. I am not sure why. When I check show databases, it didnt list all DBs, but when I connect with root again, I can see all valid Dbs. Is this normal that even if I give wrong user, it authenticate into DB server from root. [root@ipaddr ~]# mysql -u rot Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 22 Server version: 10.2.11-MariaDB MariaDB Server Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.00 sec) MariaDB [(none)]> exit Bye [root@ipaddr~]# mysql -u root Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 24 Server version: 10.2.11-MariaDB MariaDB Server Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | mydb01 | | information_schema | | mydb02 | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec)

4 4

[Maria-discuss] Replication Upgrade Strategy MySQL 5.6 to MariaDB 10.2
by Michael Caplan 29 Dec '17

29 Dec '17

Hello, Am hoping to get some feedback on an upgrade strategy to go from MySQL 5.6 (two separate servers, one replication slave) to MariaDB 10.2 (installed on new servers). I'm looking at an rsync process to seed the two new instances on MariaDB, and am unsure if this is sound (https://www.stephenrlang.com/2016/08/setting-up-mysql-master-slave-replicat…) Roughly, the process would look like this: - rsync data from MySQL 5.6 Master to MariaDB 10.2 Slave - Lock master - Get master status - Refresh rsync - Remove lock - Run upgrade process on MariaDB 10.2 Slave. - Change MASTER.... on MariaDB 10.2 Slave - Create second MariaDB 10.2 Slave from first MariaDB 10.2 Slave - Promote MariaDB 10.2 Slave to Master (redirect all DB traffic at this point) - Redirect other MariaDB 10.2 Slave to new Master - Shutdown MySQL 5.6 servers. What I'm most concerned about is the viability of rsync as a method to seed the new MariaDB server from MySQL 5.6. I've come across some chatter that there are risks using rsync instead of a proper dump and restore. Can anyone comment on this? Thanks, Mike

2 3

[Maria-discuss] WSREP: BF lock wait long
by Jesus 22 Dec '17

22 Dec '17

Hi I'm getting this message almost daily or even more and I must restart the server that generates it: WSREP: BF lock wait long How can I solve this? Thanks for your help

2 3

Re: [Maria-discuss] About data encryption
by Sergei Golubchik 15 Dec '17

15 Dec '17

Hi, Cesar! On Dec 15, Cesar Hernandez wrote: > > Thanks Sergei! > > So the database is encrypted by using AES-128-CBC or AES-128-CRT, right? > Can I use AES-256-CBC? No. Each tablespace is encrypted with a different per-tablespace key. This key is 128 bit. It's generated by encrypting a random 128-bit nonce with the user-specified key. So, if you specify a 256-bit key in the file_key_manager plugin, this 256-bit key will be used to generate the 128-bit tablespace key. The tablespace itself will be encrypted using the 128-bit key. Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

[Maria-discuss] About data encryption
by Cesar Hernandez 14 Dec '17

14 Dec '17

Hi Can someone tell me which algorithm uses MariaDB when using table encryption? Looking at https://mariadb.com/kb/en/library/data-at-rest-encryption/ <https://mariadb.com/kb/en/library/data-at-rest-encryption/> Seems to talk only about encryption of the key file, but not on the database data. I've been looking for a while and haven't found complete documentation about it. I've even taken a look at the MariaDB source code, in the file /storage/innobase/fil/fil0crypt.cc <http://fil0crypt.cc/> Seems the encryption is done by AES algorithm, but I'm not sure, and I don't know the key size neither the mode of operation. Thanks Cesar

2 1

[Maria-discuss] Debian 9.2 - Kytea
by Guillermo Cespedes 29 Nov '17

29 Nov '17

Hi, I'm compiling MariaDb in Debian 9.2. Is dependence on Kytea necessary? I need to compile Kytea previously. As you know, the Kytea package is no longer available in the Debian repositories. http://www.phontron.com/kytea/ <http://www.phontron.com/kytea/> https://github.com/dertin/lemp-stack-debian/blob/develop/install.sh#L105 <https://github.com/dertin/lemp-stack-debian/blob/develop/install.sh#L105> regards Guillermo Céspedes Full Stack Developer

3 2

[Maria-discuss] Mariadb-10.2.11 missing tokudb rpm
by Reinis Rozitis 29 Nov '17

29 Nov '17

Hi, the mariadb-10.2.11 release (all distros) is missing *-tokudb-engine.rpm package. Is this a build/packaging error or something more (though nothing in the Release notes)? rr

2 1

[Maria-discuss] MariaDB 10.2 now available on CentOS 6 &7 via SCL !!
by Michal Schorm 29 Nov '17

29 Nov '17

Hello everybody, In case you missed it, I just want to share an announce of availability of MariaDB 10.2 on CentOS 6 & 7 as a Software collections. Here are the announce emails from CentOS mailing list, which contains most basic howto: https://lists.centos.org/pipermail/centos-announce/2017-November/022640.html https://lists.centos.org/pipermail/centos-announce/2017-November/022642.html -- This made MariaDB 10.2 a big step closer to a new mass of users, because the standard MDB version in CentOS & RHEL is 5.5. It took quite an effort to fine-tune and perk-up the MDB 10.2 since its first attemps in Fedora, however it is always pleasure to cooperate with developers like you :) -- Michal Schorm Associate Software Engineer Core Services - Databases Team Red Hat

1 0

[Maria-discuss] installing mariadb using rpm
by Mark Edwards 28 Nov '17

28 Nov '17

hello - i was following an earlier thread titled: [Maria-discuss] Install MariaDB repo official Centos-7 this is the first time i have attempted an RPM install. so i decided to try it myself, using 10.2 on a new Centos7 installation created in google-console. the installation went perfectly, but now i have a question. i let mariadb generate my repo using this link: https://downloads.mariadb.org/mariadb/repositories/#mirror=jaleco&distro=Ce… and the following repo was generated: # MariaDB 10.2 CentOS repository list - created 2017-11-28 17:28 UTC # http://downloads.mariadb.org/mariadb/repositories/ [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.2/centos7-amd64 gpgkey= https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1 my question: when i cat /proc/cpuinfo, i see the following: vendor_id : GenuineIntel model name : Intel(R) Xeon(R) CPU @ 2.30GHz and yet the repo specifies "centos7-amd64". does it matter that amd64 is being specified rather than Intel? Thank you, Mark Edwards

2 2

[Maria-discuss] Install MariaDB repo official Centos-7
by Wilmer Arambula 28 Nov '17

28 Nov '17

Hi, install MariaDB from the official repository [mariadb.org] for Centos-7: 1.- yum install MariaDB-server MariaDB-client When executing: 2.- systemctl start mariadb.service Error: Failed to start mariadb.service: Unit not found. Thks,

4 4

[Maria-discuss] MariaDB won't start after updating Fedora 26 to Fedora 27
by Bill Damage 23 Nov '17

23 Nov '17

After checking the logs I see mysqld: Can't create/write to file '/var/run/mariadb/mariadb.pid' (Errcode: 2 "No such file or directory")If I manually create this dir I can then start it ... until the next reboot. What is the correct fix to ensure its works as it did before the update please?

3 4

[Maria-discuss] ALTER TABLE removes check constraint
by Peter Laursen 22 Nov '17

22 Nov '17

Test case: CREATE TABLE checks (a INT CHECK (a>2), b INT CHECK (b>2), CONSTRAINT a_greater CHECK (a>b)); SHOW CREATE TABLE checks; ALTER TABLE `test`.`checks` CHANGE `a` `a` INT(11) NULL; SHOW CREATE TABLE checks; /' -- and now one constraint is gone! CREATE TABLE `checks` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL CHECK (`b` > 2), CONSTRAINT `a_greater` CHECK (`a` > `b`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 */ This docs page https://mariadb.com/kb/en/library/constraint/ does not say that (unnamed) CHECK constraints (ie. defined "column-wise") may be removed like this. In this case the ALTER changes one column to what it already was and still the CONSTRAINT is removed. A more realistic scenario is that someone has an INT column that is about to overflow and wants to change it to a BIGINT. I am not sure if this is an oversight/a bug or not. But if it is not, I think the docs page should clearly state that "an unnamed CHECK constraint is droppped implicitly when an ALTER TABLE statement is executed and this ALTER TABLE statement references the particular column and the ALTER TABLE statement does not recreate the CONSTRAINT". There will be other cases where such ALTER TABLE would violate the CONSTRAINT due to already existing data. Wasn't it better if the ALTER TABLE failed then, rather than dropping the CONSTRAINT? What do standards say? How do other RDBMS behave with this? Does somebody know more about this? What say? -- Peter -- Webyog

3 5