- discuss - lists.mariadb.org

[Maria-discuss] The future of Dynamic Columns
by Tom Worster 01 Feb '15

01 Feb '15

I've been excited about the potential uses of Maria's Dynamic Columns (MDC) since I first read about the them. Now I am starting a new application development and want to use MDC. It's a server-side PHP webapp using the Yii 2.0 MVC framework. These days, for typical webapp pages, I seldom write SQL and use the ORM instead. So I started writing an extension to the Yii 2.0 Active Record ORM to provide an abstract interface to read and write structured dynamic attributes as well as use them in queries. I'm trying to do it so that I can make the same API work using PostgreSQL jsonb and perhaps other things. While this is very interesting work, I have developed a vague uncomfortable feeling about MDC. I have the sense that this wicked powerful feature is suffering from atrophy and possibly apathy. Contributing to this sense: 1. I haven't been able to discuss MDC with anyone who uses it. (If you do, contacts below). 2. In at least two places (one was here <https://mariadb.com/kb/en/mariadb/indexoptimizations-in-dynamic-columns/>) I've read text with the implied meaning, "we may improve such-and-such about MDC if and when people start actually using it." 3. The MDC syntax is so clunky I worry that people who might find it useful are turned away. 4. Did the 5.3 version with numbers for column names turn people off MDC for good? 5. The requirement to specify datatype when querying a dynamic column is agonizing. The manual says it is a limitation of SQL itself (presumably immutable) but PostgreSQL manages to avoid it when querying jsonb doc properties. 6. The lack of indexing of dynamic columns is a severe limitation but PostgreSQL manages to index jsonb document properties. And then... 7. I head a rumor that some kind of JSON features are coming to Maria. So, do Maria's Dynamic Columns have a future? Are my fears that MDC will be neglected as some better solution to the same problem receives all the attention? Basically, I'm trying to gauge the risk vs. return in my investment in developing the ORM extension I mentioned at the top. Tom fsb(a)thefsb.org tom[] on Freenode

8 35

Re: [Maria-discuss] JSON and binary strings
by Tom Worster 01 Feb '15

01 Feb '15

I could have been more direct. What should be the expected behavior in the following? SELECT COLUMN_JSON(COLUMN_CREATE('name', UNHEX('C1C2C3C4C5C6'))); From: Tom Worster <fsb(a)thefsb.org> Date: Friday, January 30, 2015 at 9:05 PM To: maria-discuss email list <maria-discuss(a)lists.launchpad.net> Subject: [Maria-discuss] JSON and binary strings BINARY is one of the datatypes you can use with dynamic columns. If you try saving binary string and then retrieve it with COLUMN_JSON() then you get an invalid JSON document back. All the JSON decoders I tested reject such documents. It doesn't make sense to me that COLUMN_JSON() would return a document that's invalid according to the JSON spec and can't be decoded. But I don't know what it should do. It seems that BINARY in dynamic columns means that COLUMN_JSON() is in an impossible position. [On a related note, if you save a valid utf8 string that includes control characters (e.g. "I went down to the river,\nI set down on the bank.") then COLUMN_JSON() fails to escape them. While similar, this I think is a bug.] Tom _______________________________________________ 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

3 11

[Maria-discuss] JSON and binary strings
by Tom Worster 31 Jan '15

31 Jan '15

BINARY is one of the datatypes you can use with dynamic columns. If you try saving binary string and then retrieve it with COLUMN_JSON() then you get an invalid JSON document back. All the JSON decoders I tested reject such documents. It doesn't make sense to me that COLUMN_JSON() would return a document that's invalid according to the JSON spec and can't be decoded. But I don't know what it should do. It seems that BINARY in dynamic columns means that COLUMN_JSON() is in an impossible position. [On a related note, if you save a valid utf8 string that includes control characters (e.g. "I went down to the river,\nI set down on the bank.") then COLUMN_JSON() fails to escape them. While similar, this I think is a bug.] Tom

1 0

[Maria-discuss] New Question: Why are almost all ROWS_EXAMINED values 0 in events_statements_* tables (performance_schema) !?
by AskMonty KB 30 Jan '15

30 Jan '15

Hello, A new question has been asked in "MariaDB Community" by fabian-atrada. Please answer it at http://mariadb.com/kb/en/why-are-almost-all-rows_examined-values-0-in-event… as the person asking the question may not be subscribed to the mailing list. -------------------------------- In the last days I started to explore the new possibilities of the perofmance_schema in 10.0.x. In this process I discovered the valuable event_statemens_* tables, which I could imagine using for some new monitoring approaches. While studying the content of the event_statemens_* on our production DB server, I have noticed, that almost all ROWS_EXAMINED values were always equal 0! Only on one server, in the events_statements_summary_by_digest table could I find a few entries with ROWS_EXAMINED > 0 (but in those cases, both DIGEST and DIGEST_TEXT were NULL ???) .... which is a real pity, because this value seems to be the best one to be used as some kind of "load" indicator (and it can show very quickly, if a query needs an unreasonable amount of rows to be examined ... etc. ..). Now the questions is, why is this happening? As far as I could see, all other values in the event_statemens_* tables seemed to have reasonable values. Only ROWS_EXAMINED was missing notoriously. Is there special reason for this? Is there some instrumentation setting needed to make this work? Is there some server variable which influences this? or is there some very special technical reason - from the servers logic - which prohibits to gather this value most of the time? By the way, I have also observed - and this might coincide with this issue - that neither do I ever see EXAMINED_ROWS > 0 in `information_schema`.`PROCESSLIST`! Even if I fire a lung running query, which uses a table scan. By the way, the only place where I always - and reliably - see ROWS_EXAMINED is in the slow_query_log! I am really curious about the background (and possible reasons) for this - seemingly - strange behavior. Thank you very much! -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/why-are-almost-all-rows_examined-values-0-in-event…

1 0

[Maria-discuss] New Question: What's the chance of a true Boolean data type in MariaDB?
by AskMonty KB 30 Jan '15

30 Jan '15

Hello, A new question has been asked in "MariaDB Community" by RobLewis. Please answer it at http://mariadb.com/kb/en/whats-the-chance-of-a-true-boolean-data-type-in-ma… as the person asking the question may not be subscribed to the mailing list. -------------------------------- As a MySQL user, I've often been bothered by the lack of a true Boolean data type in MySQL. Is there any chance this omission will be filled in MariaDB? And yes, I know there are workarounds. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/whats-the-chance-of-a-true-boolean-data-type-in-ma…

1 0

[Maria-discuss] MariaDB Galera Cluster 10.0.16 now available
by Daniel Bartholomew 30 Jan '15

30 Jan '15

The MariaDB project is pleased to announce the immediate availability of MariaDB Galera Cluster 10.0.16 This is a Stable (GA) release. See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB Galera Cluster 10.0.16 - Release Notes: https://mariadb.com/kb/en/mariadb-galera-cluster-10016-release-notes - Changelog: https://mariadb.com/kb/en/mariadb-galera-cluster-10016-changelog - Downloads: https://downloads.mariadb.org/mariadb-galera/10.0.16 About MariaDB Galera Cluster: - https://mariadb.com/kb/en/what-is-mariadb-galera-cluster/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ - - MariaDB Webinars - - - - - - - - - - - - - - - - - - - - - - - - - There are several upcoming MariaDB-focused webinars and many previously held ones available to watch on an on-demand basis at: - https://mariadb.com/news-events/webinars - - 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://mariadb.org/jira - - Support MariaDB - - - - - - - - - - - - - - - - - - - - - - - - - - If you would like to contribute to MariaDB, please see the "contributing" page in the Knowledge Base. - https://mariadb.com/kb/en/contributing We hope you enjoy MariaDB! -- MariaDB Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledge Base - http://mariadb.com/kb

1 0

[Maria-discuss] Fwd: Re: The future of Dynamic Columns
by Oleksandr Byelkin 29 Jan '15

29 Jan '15

Hi! Sorry I forgot to put in in the list. -------- Forwarded Message -------- Тема: Re: [Maria-discuss] The future of Dynamic Columns Дата: Wed, 28 Jan 2015 23:44:39 +0100 Від: Oleksandr Byelkin <sanja(a)montyprogram.com> Кому: Roberto Spadim <roberto(a)spadim.com.br> Hi, Roberto! On 28.01.15 23:13, Roberto Spadim wrote: > Hi Peter :) i will talk about what i used and what i consider > interesting to have, > > > 2015-01-28 19:25 GMT-02:00 Peter Laursen <peter_laursen(a)webyog.com > <mailto:peter_laursen@webyog.com>>: > > @Roberto. A comment to "i think it's a nice human readable format > for arrays/objects, nothing less nothing more". > > But why will you then need to **store** as JSON? > > about "store": > 1) it's nice to read data without any tool (function), it's easy to > read a object or a array, maybe could use YAML or any other format (i > don't like XML cause it consume too many space) > 2) saving json could optimize cpu use at php/application server > without decode data every read (if you use json a lot, not my case) > 3) could be nice to export/import data? i don't know, today i use > table dump when i need, or a small script or pre defined data format > > about "json internally implemented at database / store": > 1)seach with index, jpath/xpath or others functions, consume less > space when possible json/xml specific field type > 2)today when i use json i save using a blob column and execute a > compress() function when possible, sometimes i don't care about > datasize, and only save at blob columns (or maybe varchar when data is > small) Actually saving JSON as is looks so inefficient that even MongoDB store BSON AFAIK. [skip]

1 0

Re: [Maria-discuss] Unable to DROP table, Data Dict problem
by Martin Langhoff 28 Jan '15

28 Jan '15

Hi Jan, the root cause for the corruption, we know: NFSv4 bugs. The "dump/restore-to-new-db" path we know as well. My question here is: is there a way to get rid of this table _without restoring to a new DB_. thanks, martin On Wed, Jan 28, 2015 at 2:05 AM, Jan Lindström <jan.lindstrom(a)mariadb.com> wrote: > Hi, > > Yes I see the assertion, it is a consistency check. Inside a tablespace > (table in this case) every page has space_id stored. This assertion tells us > that one page has different space_id than on tablespace header i.e. there is > one page that based on metadata does not belong to this table. Furthermore, > that means that tablespace could be corrupted. If the instructions on that > web page do not help, you need to export all tables, delete everything, > import tables to new database. Can you send me full, unedited error log, I > will try to see if there is something that could help to identify the real > problem. Also if you could send me dmesg output. > > R: Jan > > On Tue, Jan 27, 2015 at 4:19 PM, Martin Langhoff <martin.langhoff(a)gmail.com> > wrote: >> >> Hi Jan, >> >> thanks for your notes. I have gone through the instructions in that >> doc link, but they do not help. In the prior emails I have included >> stack dumps and straces of what happens when I follow those >> instructions. >> >> There is something else going on, or perhaps a regression that >> prevents the procedure you mention from working right. >> >> We are definitely NOT hitting ENOSPC, nor any FS or storage side >> issue. I have double checked on that. >> >> cheers, >> >> >> m >> >> On Tue, Jan 27, 2015 at 12:37 AM, Jan Lindström >> <jan.lindstrom(a)mariadb.com> wrote: >> > Hi, >> > >> > Failure you see on >> > >> > InnoDB: Error: tablespace id is 47073 in the data dictionary InnoDB: but >> > in >> > file ./moodle_sand/mdl_tag_correlation.ibd it is 48463! >> > 150119 17:14:45 InnoDB: Assertion failure in thread 140045886629632 in >> > file >> > fil0fil.c line 778 >> > >> > indicates database corruption that is caused most likely disk failure or >> > a >> > bug on a software. With this information not easy to say which one. If >> > drop >> > table crashes while the operation is done, deleting .ibd file is not >> > enough >> > to re-create the table because table definition is on innodb data >> > dictionary >> > while it is not on MySQL/MariaDB data dictionary. Please refer to >> > >> > http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html >> > how to resolve the issue. >> > >> > R: Jan >> > >> > On Tue, Jan 20, 2015 at 5:00 PM, Martin Langhoff >> > <martin.langhoff(a)gmail.com> >> > wrote: >> >> >> >> Hi Folks, >> >> >> >> We run a very large infra on MariaDB/RHEL6.x/RHEV/NetApp; thousands of >> >> VMs, each running MariaDB and very busy. MariaDB is 5.5.32. >> >> >> >> We recently had some NFS problems which led to minor DB corruption. >> >> Luckily, the corruption was isolated to a table holding cache data, >> >> and in fact one that is empty in most of our instances. That's the >> >> good news. >> >> >> >> The bad news is that the data dict is really unhappy about that table. >> >> >> >> - Cannot drop it, we get a nasty stack trace at [1] >> >> >> >> - Stopping MariaDB, removing the table by hand, restarting MariaDB, >> >> attempting to re-create the table hits a data dict error [2]. On disk >> >> I can see it created an ibd file, but no frm file. Following the >> >> instructions at >> >> >> >> >> >> http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting-datadict.html >> >> does not help. >> >> >> >> I have straced the process, and it does not seem to even try to create >> >> the frm file, so it really looks like it's internal state (data dict), >> >> not errors from further down the stack. OS / kernel logs are clean. >> >> >> >> Is there any reasonable method to cure this "in place" with minimal >> >> disruption? (These are large DBs, dump restore is more than awkward.) >> >> >> >> Oh and careful with NFSv4 :-/ >> >> >> >> thank you! >> >> >> >> >> >> martin >> >> >> >> 1 - Stack trace when trying to drop table >> >> >> >> Version: '5.5.32-MariaDB-log' socket: '/var/lib/mysql/mysql.sock' >> >> port: 3306 MariaDB Server >> >> InnoDB: Error: tablespace id is 47073 in the data dictionary >> >> InnoDB: but in file ./moodle_sand/mdl_tag_correlation.ibd it is 48463! >> >> 150119 17:14:45 InnoDB: Assertion failure in thread 140045886629632 >> >> in file fil0fil.c line 778 >> >> InnoDB: We intentionally generate a memory trap. >> >> InnoDB: Submit a detailed bug report to http://bugs.mysql.com. >> >> InnoDB: If you get repeated assertion failures or crashes, even >> >> InnoDB: immediately after the mysqld startup, there may be >> >> InnoDB: corruption in the InnoDB tablespace. Please refer to >> >> InnoDB: >> >> http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html >> >> InnoDB: about forcing recovery. >> >> 150119 17:14:45 [ERROR] mysqld got signal 6 ; >> >> This could be because you hit a bug. It is also possible that this >> >> binary >> >> or one of the libraries it was linked against is corrupt, improperly >> >> built, >> >> or misconfigured. This error can also be caused by malfunctioning >> >> hardware. >> >> >> >> To report this bug, see http://kb.askmonty.org/en/reporting-bugs >> >> >> >> We will try our best to scrape up some info that will hopefully help >> >> diagnose the problem, but since we have already crashed, >> >> something is definitely wrong and this may fail. >> >> >> >> Server version: 5.5.32-MariaDB-log >> >> key_buffer_size=134217728 >> >> read_buffer_size=131072 >> >> max_used_connections=1 >> >> max_threads=187 >> >> thread_count=1 >> >> It is possible that mysqld could use up to >> >> key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = >> >> 182265 K bytes of memory >> >> Hope that's ok; if not, decrease some variables in the equation. >> >> >> >> Thread pointer: 0x0xa92ee60 >> >> Attempting backtrace. You can use the following information to find out >> >> where mysqld died. If you see no messages after this, something went >> >> terribly wrong... >> >> stack_bottom = 0x7f5ef9531d78 thread_stack 0x48000 >> >> ??:0(my_print_stacktrace)[0xa8631e] >> >> ??:0(handle_fatal_signal)[0x6cdd0b] >> >> ??:0(??)[0x30a7c0f500] >> >> ??:0(??)[0x30a78328a5] >> >> ??:0(??)[0x30a7834085] >> >> ??:0(fil_node_open_file)[0x8f3add] >> >> ??:0(fil_node_prepare_for_io)[0x8f3be5] >> >> ??:0(fil_space_get_flags)[0x8f7420] >> >> ??:0(fil_space_get_zip_size)[0x8f7529] >> >> ??:0(dict_drop_index_tree)[0x8d64e6] >> >> ??:0(row_upd_clust_step)[0x86d84d] >> >> ??:0(row_upd_step)[0x86e22e] >> >> ??:0(que_run_threads)[0x9577a8] >> >> ??:0(que_eval_sql)[0x957ea6] >> >> ??:0(row_drop_table_for_mysql)[0x85ae6a] >> >> ??:0(ha_innobase::delete_table(char const*))[0x8424e3] >> >> ??:0(ha_delete_table(THD*, handlerton*, char const*, char const*, char >> >> const*, bool))[0x6d5233] >> >> ??:0(mysql_rm_table_no_locks(THD*, TABLE_LIST*, bool, bool, bool, >> >> bool))[0x5ff928] >> >> ??:0(mysql_rm_table(THD*, TABLE_LIST*, char, char))[0x60001d] >> >> ??:0(mysql_execute_command(THD*))[0x58ad90] >> >> ??:0(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x58e369] >> >> ??:0(dispatch_command(enum_server_command, THD*, char*, unsigned >> >> int))[0x58f790] >> >> ??:0(do_handle_one_connection(THD*))[0x647cdf] >> >> ??:0(handle_one_connection)[0x647d7c] >> >> ??:0(??)[0x30a7c07851] >> >> ??:0(??)[0x30a78e811d] >> >> >> >> Trying to get some variables. >> >> Some pointers may be invalid and cause the dump to abort. >> >> Query (0x7f5db800afa8): is an invalid pointer >> >> Connection ID (thread ID): 2 >> >> Status: NOT_KILLED >> >> >> >> Optimizer switch: >> >> >> >> >> >> index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off >> >> >> >> >> >> >> >> 2 - Error when attempting to recreate table >> >> 150119 17:15:22 InnoDB: Operating system error number 2 in a file >> >> operation. >> >> InnoDB: The error means the system cannot find the path specified. >> >> InnoDB: If you are installing InnoDB, remember that you must create >> >> InnoDB: directories yourself, InnoDB does not create them. >> >> 150119 17:15:22 InnoDB: Error: trying to open a table, but could not >> >> InnoDB: open the tablespace file >> >> './moodle_sand/mdl_tag_correlation.ibd'! >> >> InnoDB: Have you moved InnoDB .ibd files around without using the >> >> InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE? >> >> InnoDB: It is also possible that this is a temporary table #sql..., >> >> InnoDB: and MySQL removed the .ibd file for this. >> >> InnoDB: Please refer to >> >> InnoDB: >> >> >> >> http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html >> >> InnoDB: for how to resolve the issue. >> >> >> >> >> >> -- >> >> martin.langhoff(a)gmail.com >> >> - ask interesting questions >> >> - don't get distracted with shiny stuff - working code first >> >> ~ http://docs.moodle.org/en/User:Martin_Langhoff >> >> >> >> _______________________________________________ >> >> 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 >> > >> > >> >> >> >> -- >> martin.langhoff(a)gmail.com >> - ask interesting questions >> - don't get distracted with shiny stuff - working code first >> ~ http://docs.moodle.org/en/User:Martin_Langhoff > > -- martin.langhoff(a)gmail.com - ask interesting questions - don't get distracted with shiny stuff - working code first ~ http://docs.moodle.org/en/User:Martin_Langhoff

1 0

[Maria-discuss] Can't create account on MariaDB bugtracker
by Brian Sammon 28 Jan '15

28 Jan '15

I'm having trouble creating an account on the bugtracker. I can get it to send me the email-address-verification email, but when I use the link from the email to create my account I get a "unexpected error has occurred" message. P.S. I use atlassian-contact@ as my email address for the bugtracker.

2 2

[Maria-discuss] Presentations, interviews, news articles etc. needed
by Ian Gilfillan 27 Jan '15

27 Jan '15

I am dusting some of the neglected corners of the Knowledge Base, and there are some areas that haven't been updated in a while and that are in need of some love. If anyone has presentations, videos, news articles, interviews, suggestions for interesting mailing list threads etc that could be added to these pages, feel free to either add them to the page yourself, or send them to me to add. These are some of the pages I'm looking at: https://mariadb.com/kb/en/mariadb/presentations/ https://mariadb.com/kb/en/mariadb/videos-related-to-mariadb/ https://mariadb.com/kb/en/mariadb/news-reports-related-to-mariadb/ https://mariadb.com/kb/en/mariadb/interviews-related-to-mariadb/ https://mariadb.com/kb/en/mariadb/blog-posts-relevant-to-mariadb/ https://mariadb.com/kb/en/mariadb/interesting-mailing-list-threads/ https://mariadb.com/kb/en/mariadb/articles-related-to-mariadb/ I will probably merge some of these pages as, for example, "Articles Related to MariaDB" overlaps with some of the other pages, but in the meantime any newer content would be great. thanks, ian

1 0