- discuss - lists.mariadb.org

[Maria-discuss] Decimal operations
by Oleg Tsarev 23 Aug '10

23 Aug '10

Hello, I don't understand how right work with decimal type. As you talk, i change column types from CHAR(...) to DECIMAL in my patch: http://www.percona.com/docs/wiki/patches:response_time_distribution Now i have trouble with decimal precision/scale. Here is my definition of system table INFORMATION_SCHEMA.QUERY_RESPONSE_TIME: ST_FIELD_INFO query_response_time_fields_info[] = { {"TIME", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, MY_I_S_UNSIGNED, "time", SKIP_OPEN_TABLE }, {"COUNT", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, MY_I_S_UNSIGNED, "count", SKIP_OPEN_TABLE }, {"TOTAL", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, MY_I_S_UNSIGNED, "total", SKIP_OPEN_TABLE }, {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE } }; So, i want use DECIMAL(20,6) for columns time and total, and DECIMAL(14,0) for columns count. 1) Does above definiton correct, or should include some information about precision/scale? I collect time of query execution in: 1) Column total - in int64, microseconds count 2) Column count - in int64, just count Here is source code, for fill this table: for(int i= 0, count= m_utility.bound_count() + 1 /* with overflow */; count > i; ++i) { const uint64 time = m_utility.bound(i); const int64 count = m_time.count(i); const int64 all = m_time.all(i); my_decimal time_d; my_decimal count_d; my_decimal all_d; my_decimal million_d; my_decimal temp_d; ulonglong2decimal(MILLION,&million_d); ulonglong2decimal(time, &temp_d); my_decimal_div(E_DEC_OK,&time_d,&temp_d,&million_d,SCALE); longlong2decimal(count,&count_d); longlong2decimal(all, &temp_d); my_decimal_div(E_DEC_OK,&all_d,&temp_d,&million_d,SCALE); fields[0]->store_decimal(&time_d); fields[1]->store_decimal(&count_d); fields[2]->store_decimal(&all_d); if (schema_table_store_record(thd, table)) { DBUG_RETURN(1); } } I want store some value to decimal type, than divide by MILLION: microsecond_count => convert to decimal => / 1000000 = result. But as result i get: SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; TIME COUNT TOTAL 0 0 0 0 0 0 0 1 0 0 0 0 0 1 0 0 0 0 1 11 4 10 8 13 100 0 0 1000 0 0 10000 0 0 ...................... Instead of wanted: SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; TIME COUNT TOTAL 0.000001 0 0 0.00001 0 0 0.0001 1 0 0.001 0 0 0.01 1 0 0.1 0 0 1 11 4 10 8 13 100 0 0 1000 0 0 10000 0 0 ...................... 2) What i do incorrect? How fix it? Best regards, Oleg

2 1

Re: [Maria-discuss] Full Replacement of MySQL (innoDB, mysql Workbench, data, etc)
by Daniel Bartholomew 17 Aug '10

17 Aug '10

On Mon, 16 Aug 2010 16:25:00 -0000 Enrique Daniel <enridp(a)yahoo.com.ar> wrote: Enrique> Hi !! Hello! I'm CC'ing this message to the maria-discuss mailing list in case anyone else wants to comment. I recommend you join the list. It's a good resource. Enrique> I'm really new with MariaDB, I have discovered it yesterday at Enrique> really :S And I have not tested it yet, but it looks really Enrique> promising. Thanks! We've done lots of testing to make sure MariaDB works as seamlessly as possible for those moving from MySQL to MariaDB. As with any upgrade, I would definitely recommend making a backup and testing MariaDB on your end to ensure everything works with your applications and use-cases before putting it into production. Enrique> Anyway, there are a few things that are braking me to make the Enrique> change. I hope somebody can help me: Enrique> Enrique> 1) I'm using mySQL Workbench to design my DB, how can we use Enrique> it with MariaDB? (Aria, XtraDB engine, etc.) I haven't used MySQL Workbench much, but using it with MariaDB should be exactly the same as using it with MySQL. Enrique> 2) My tables ar InnoDB, but I was reading about XtraDB and it Enrique> looks much better, how can I make the change from innoDB to Enrique> XtraDB? if I have a SQL file, is enough changing ENGINE=INNODB Enrique> with ENGINE=XTRADB (or something like that). Enrique> I want to preserve my FK, with cascades, and comments. For compatibility purposes, XtraDB identifies itself to the server as InnoDB, so there's no need to change ENGINE= in any SQL files. Any existing InnoDB tables will start using XtraDB automatically after the upgrade. Enrique> 3) What happens if I have my DB with data and working with Enrique> MySQL and InnoDB right now? can I change to MariaDB (and Enrique> XtraDB) preserving all my scheme, tables, data, relations, Enrique> comments, indexes, etc? Yes. All of our testing indicates you can upgrade from MySQL to MariaDB and all of your schemas, tables, data, relations, comments, indexes, and so on will "just work" like they did with MySQL without you having to change anything. Of course, as with any upgrade, I would recommend testing on a non-production server before putting MariaDB into production. One last note. If you do find anything that doesn't work, please file a bug report: https://bugs.launchpad.net/maria/+filebug Thanks. -- Daniel Bartholomew Monty Program - http://askmonty.org

4 5

Re: [Maria-discuss] Full Replacement of MySQL (innoDB, mysql Workbench, data, etc)
by Henrik Ingo 16 Aug '10

16 Aug '10

I'm forwarding this to the mailing list since of all the places it was sent to, lp did not send it to the list... Please take care of this, nice MariaDB people: henrik On Mon, Aug 16, 2010 at 7:25 PM, Enrique Daniel <enridp(a)yahoo.com.ar> wrote: > Hi !! > I'm really new with MariaDB, I have discovered it yesterday at really :S > And I have not tested it yet, but it looks really promising. > > Anyway, there are a few things that are braking me to make the change. > I hope somebody can help me: > > 1) I'm using mySQL Workbench to design my DB, how can we use it with > MariaDB? (Aria, XtraDB engine, etc.) > > 2) My tables ar InnoDB, but I was reading about XtraDB and it looks much > better, how can I make the change from innoDB to XtraDB? if I have a SQL > file, is enough changing ENGINE=INNODB with ENGINE=XTRADB (or something > like that). > I want to preserve my FK, with cascades, and comments. > > 3) What happens if I have my DB with data and working with MySQL and > InnoDB right now? can I change to MariaDB (and XtraDB) preserving all my > scheme, tables, data, relations, comments, indexes, etc? > > Thanks !!! > Enrique. > -- > This message was sent from Launchpad by the user > Enrique Daniel (https://launchpad.net/~enridp) > using the "Contact this team" link on the Maria Discuss > team page to each member directly. > For more information see > https://help.launchpad.net/YourAccount/ContactingPeople > -- email: henrik.ingo(a)avoinelama.fi tel: +358-40-5697354 www: www.avoinelama.fi/~hingo book: www.openlife.cc

1 0

Re: [Maria-discuss] Locale settings and fractal number
by Oleg Tsarev 16 Aug '10

16 Aug '10

Hi, Sergey! I use string column, because i need fixed-point number (time column): 14_digit_to_second.6_digit_to_microsecond 123456.654321 as example. Big number, because i have not only "time" column and also "sum" column too. I didn't find any type what good for this, and i use string. Typically this columns use only by hand or in scripts, and don't used in any another queries. When i prepare output, i should print number and i don't know what the delimiter should i use. What the internal function should i call for get correct delimeter? Best regards, Oleg. 2010/8/16 Sergei Golubchik <serg(a)askmonty.org>: > Hi, Oleg! > > On Aug 15, Oleg Tsarev wrote: >> >> Hello all, >> >> I have feature >> http://www.percona.com/docs/wiki/patches:response_time_distribution >> Now i want respect locale settings, and use in "time" column >> locale-depended delimiter instead of current hardcoded ".". >> I investigate source code about that, and also bugzilla, >> documentation, but not found answer to my question - where this >> delimieter setup. >> >> What the "official" mysql policy? Use only "."? Or use native-locale? >> Or use some internal mysql function? >> Please, resolve my confuse. >> I want understand how this questions solved in mysql. > > As far as I understand, MySQL typically does not uses system locale > information for formatting numbers or dates. > > Anyway, you shouldn't care about locale - as you have two columns > of numbers, you need to have two numeric columns, say, DOUBLE and > BIGINT. > > Regards, > Sergei >

3 2

[Maria-discuss] Locale settings and fractal number
by Oleg Tsarev 15 Aug '10

15 Aug '10

Hello all, I have feature http://www.percona.com/docs/wiki/patches:response_time_distribution Now i want respect locale settings, and use in "time" column locale-depended delimiter instead of current hardcoded ".". I investigate source code about that, and also bugzilla, documentation, but not found answer to my question - where this delimieter setup. What the "official" mysql policy? Use only "."? Or use native-locale? Or use some internal mysql function? Please, resolve my confuse. I want understand how this questions solved in mysql. Thank you very much. --- Oleg Tsarev, Software Engineer, Percona Inc.

2 1

[Maria-discuss] Google docs now allows collaborative graphics and schemas
by Henrik Ingo 14 Aug '10

14 Aug '10

Hi I didn't try it yet, but Google docs just advertised the addition of a collaborative graphics app and schema drawing app. It's not open source of course, but it's better than nothing. Especially if they turn out to support ODF fileformats. henrik (This was a topic discussed at the devmeeting in Iceland.) -- email: henrik.ingo(a)avoinelama.fi tel: +358-40-5697354 www: www.avoinelama.fi/~hingo book: www.openlife.cc

1 0

[Maria-discuss] Wiki to Knowledgebase Move
by Daniel Bartholomew 14 Aug '10

14 Aug '10

All, I have started (and over the course of the next week I will continue) moving several pages from http://askmonty.org/wiki to http://kb.askmonty.org With the Knowledgebase now in beta it is time to move the content over. The procedure I plan on using for this is as follows: 1. Copy the content of a page from the wiki into the Knowledgebase. 2. Add a link to the top of the wiki page notifying of the move and giving a link to the new location of the page on the KB. 3. Add a redirect to the webserver config which automatically sends you to the KB page if you click on a link to the wiki page. I wanted to let you know about it now so that you are not surprised when you click on a link in the wiki and end up in the Knowledgebase. After most of the pages which are slated to be moved have been moved I will start deleting the pages from the wiki. The pages won't actually be gone (because of MediaWiki's undelete functionality) but having them officially deleted brings closure to the page and prevents it being accidentally viewed and/or edited. The pages slated to be moved include all of the ones in the "Manual:" namespace and several of the ones in the "MariaDB:" section. An easy way to think of it is that all documentation-type pages on the wiki are going to move to the Knowledgebase. Pages the developers use for planning releases (such as the 5.2 TODO page) will not move. Pages developers use to document new features will be moved when the developer indicates the page has become stable and ready to be used as documentation. In the future, some features will likely be documented first on the KB, but I'm sure there will always be some which begin life on the wiki. The redirects will stay in place indefinitely since keeping them around is easy and painless. The wiki will not go away, it is going to continue to be a site the MariaDB devs can use for notes, plans, and so on. The look will also change to better fit this mission. The corporate info, for example, has already been moved to http://montyprogram.com so the navigation links at the top of every page (About Us, Business, Partners, etc...) are no-longer necessary. Let me know if you have any questions or suggestions. Thanks. -- Daniel Bartholomew Monty Program - http://askmonty.org

1 0

[Maria-discuss] What is the log_bin and log_bin_index config for?
by Joshua Partogi 10 Aug '10

10 Aug '10

Hi all, Can anyone advise what is the log_bin and log_bin_index configuration in my.cnf for? When I enable both of this config and did and alter table on a table with PBXT engine, I encounter this error: MariaDB [bar]> alter table foo rename to tmp; ERROR 1598 (HY000): Binary logging not possible. Message: Statement-based format required for this statement, but not allowed by this combination of engines I am using MariaDB 5.1.47. Kind regards, Joshua. -- http://twitter.com/scrum8

2 1

[Maria-discuss] MySQL error code - forward compatible
by Oleg Tsarev 05 Aug '10

05 Aug '10

Dear MariaDB, Both Percona-Server/XtraDB and MariaDB experience problems concerning forward compatible error codes. 1) Both servers have a lot of features backported from mysql 5.5. Some features add new error (new error codes from MySQL 5.5). 2) Some Percona-Server/XtraDB and MariaDB features add new error code. In the first case we should save error code. For example, Percona has patch query_cache_totally_disable.patch (http://www.percona.com/docs/wiki/patches:query_cache_totally_disable) This patch adds error ER_QUERY_CACHE_DISABLED - error code number 1651 in mysql 5.5 (http://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html#error_er_…) We must be consistent with error code numbers, because they may be used in scripts. After migrating PerconaServer/XtraDB and MariaDB to mysql 5.5, users might experience troubles because of this. In the second case we should add error code numbers, unique for our errors, as we cannot use any numbers from mysql 5.5. For example: http://www.percona.com/docs/wiki/patches:slave_type_conversions_error_on_tr… MySQL has a tool "comp_err" that generates 1) errmsg.sys files 2) header file include/mysqld_error.h 3) header file include/mysqld_ername.h from the file errmsg.txt To keep error numbers consistent, we should add some fictive errors to errmsg.txt, because comp_err assigns error code numbers sequentially, without gaps. I propose patch to comp_err (see attachment error_pad.patch). This patch allows usage of a new syntax, with prefix "PADD_", for example: PADD_QUERY_CACHE_DISABLED 1651 eng "ER_QUERY_CACHE_DISABLED padding to 1651 error" ER_QUERY_CACHE_DISABLED eng "Query cache is disabled; restart the server with query_cache_type=1 to enable it" comp_err with my patch padds empty intervals (from last error code number to 1651) by error message "ER_QUERY_CACHE_DISABLED padding to 1651 error", i.e. and ER_QUERY_CACHE_DISABLED now has error code 1651 (as desired). I propose to use this patch for Percona's and Maria's errors, for example: PADD_PERCONA_NEW_ERROR_CODE 4000 end "Padd empty space to error code number 4000 (Percona error codes)" ...some percona error codes... PADD_MARIA_DB_NEW_ERROR_CODE 5000 eng "Padd empty space to error code number 5000 (MariaDB error codes)" ...some maria error codes... Patch only adds prefix "PADD_" and padds error in sys files. All other mysql code (load*.sys files, my_error, etc) works as old one. Please, let me know what you think of it. Best regards, Oleg.

1 0

[Maria-discuss] Full text search in MariaDB
by Joshua Partogi 04 Aug '10

04 Aug '10

Hi all, We are currently using MariaDB in one of our products. One thing that we are wondering, does Maria engine currently supports Fulltext search like MyISAM? If currently not implemented yet, will there be any plan to implement fulltext support in MariaDB? Thank you very much in advance for your assistance. Kind regards, Joshua. -- http://twitter.com/scrum8

6 8