- discuss - lists.mariadb.org

[Maria-discuss] MariaDB Knowledgebase typo
by Yousif Masoud 16 Sep '10

16 Sep '10

Hi, I'd like to thank everyone working on this project. MariaDB is currently installed on one of my Dev environments, I am looking forward to switching to it. I noticed there was a minor typo on: http://kb.askmonty.org/v/compiling-with-the-innodb-plugin-from-oracle First sentence reads: Normally MariaDB comes with XtraDB <http://kb.askmonty.org/v/xtradb-storage-engine>, an enhanced version **if** the InnoDB plugin that comes from Oracle. I believe it should be: Normally MariaDB comes with XtraDB <http://kb.askmonty.org/v/xtradb-storage-engine>, an enhanced version **of** the InnoDB plugin that comes from Oracle. Many thanks, Yousif PS. I don't know if there is a formal method for reporting this. Please accept my apologies if this is not the best way to do it.

2 1

Re: [Maria-discuss] Seeking command syntax feedback (Still Re: Per-db binlogging)
by Weldon Whipple 16 Sep '10

16 Sep '10

On Wed, Sep 15, 2010 at 2:00 PM, Sergei Golubchik <serg(a)askmonty.org> wrote: > Hi, Weldon! Hi, Sergei! > > On Sep 15, Weldon Whipple wrote: <snip/> >> 2. FLUSH TABLES WITH READ LOCK FOR DATABASE dbname; >> >> (I just added an optional 'FOR DATABASE dbname' to the already >> existing command--which still works, BTW. Is this a misuse of the word >> "FOR"?) >> This is the command that locks the tables before someone (some >> program) dumps/copies the tables that will be moved to another server. >> It flushes and locks only one database. > > This would be difficult to implement. > > FLUSH TABLES WITH READ LOCK is not implemented in a way that can be > generalized to support per-database locks :( I had guessed as much. (The method in my DB_BINLOG_MGR method that implements that functionality consists of a mutex lock, a comment about some sort of miracle happening, then a mutex unlock.) I wonder if there might be a way of inhibiting some user database (temporarily) from changing ... Alternately, I suppose we could do a global FLUSH TABLE WITH READ LOCK/ UNLOCK TABLES. Or ... (???) > Besides, I personally wouldn't worry much about the syntax. It's > something you can easily change anytime - e.g. replace BINLOG with > BINLOGGING or FOR with something else. Yeah, every time I write a new prototype I tweak the syntax a bit. At the moment I'm just trying to settle on an implementation that would work for our company (and still be of some sort of general use). > > Regards, > Sergei > Thanks, Weldon

1 0

Re: [Maria-discuss] Per-db binlogging
by MARK CALLAGHAN 16 Sep '10

16 Sep '10

+maria-discuss as they are working in replication too +robert.hodges as tungsten replicator has features that could be used here I am very interested in a subset of this. I want one SQL thread per database on a slave (stream per database on the slave). However I don't need to split the binlog on the master to get that and writing a binlog file per database on the master might complicate the sync_binlog option that keeps the binlog and InnoDB in sync during crash recovery. How will this work in that case? Note that a stream (SQL slave thread) per database on the slave will break rpl_transaction_enabled. Some of us rely on that to keep slaves consistent during crash recovery. I assume you don't use that feature. MySQL has worklogs to use db tables in place of relay-log.info and when that code is production ready, then this is not a problem. On Mon, Sep 13, 2010 at 1:52 PM, Weldon Whipple <weldon(a)whipple.org> wrote: > Greetings! > > My employer has asked me to implement per-DB binlogging for > MySQL. I've been working on prototypes for 3-4 weeks now, and decided > it's time to post to this list to ask for comments, suggestions, > condolences, etc. (I've been a "lurker" for quite awhile. I hope this > post isn't too far off the mark) The assignment is *only* for master-side > binlogging. (It doesn't require a slave/replication-side implementation.) > > > Why I Want to Do It. > ------------------- > > I work for an ISP that hosts (last I heard--I HOPE I'm not lying) over > 2 million domains. Customers are allowed to have (almost) unlimited > MySQL databases. Migrating an account from one physical server to > another is a daunting task. (A typical server has easily 2000+ > [sometimes far more] databases.) Global mysqld binlogging isn't > feasible when we want to migrate (for example) a single account to > another server. > > Our proposed scenario goes something like this: > > 1. START USER BINLOG FOR DATABASE <db> > 2. FLUSH USER TABLES WITH READ LOCK FOR DATABASE <db> > 3. SHOW USER BINLOG STATUS [WHERE DATABASE LIKE <pattern>] > > SHOW output might look something like: > ================================= > mysql> show user binlog status; > +----------------------+----------+---------------------------------+------------+ > | Database | User | Binlog_file | > Binlog_pos | > +----------------------+----------+---------------------------------+------------+ > | booklibs | wwhipple | booklibs-bin.000003 | > 248 | > | charmins_gravestones | charmins | charmins_gravestones-bin.000001 | > 4 | > +----------------------+----------+---------------------------------+------------+ > 2 rows in set (0.01 sec) > > The "where" clause also works: > > mysql> show user binlog status where user like 'charmins'; > +----------------------+----------+---------------------------------+------------+ > | Database | User | Binlog_file | > Binlog_pos | > +----------------------+----------+---------------------------------+------------+ > | charmins_gravestones | charmins | charmins_gravestones-bin.000001 | > 4 | > +----------------------+----------+---------------------------------+------------+ > 1 row in set (0.01 sec) > ================================= > > At this point the migration tools note the binlog coordinates and > start copying the databases to the new server. (For myisam tables, the > tools writers want to just copy [scp?] the files. For innodb, they > will use something like mysqldump.) > > (Note: We might want to "lose" the "User" column in the above table] > in our recent designs.) > > Then the sequence of MySQL commands resumes: > > 4. UNLOCK USER BINLOG FOR DATABASE <db> > > Finally, when migration is complete: > > 5. STOP USER BINLOG FOR DATABASE <db> > > A high percentage of users (infrequent bloggers, etc.) go several days > (sometimes even weeks or months) without updating their databases. For > them--after migrating the files to the new server, a final check of > the binlog coordinates on the old box can verify that nothing has > changed. > > If it HAS changed, migration can copy the binlog file(s) to the new > box and use the mysqlbinlog command (specifying the starting > coordinates) to bring the new database in sync with the old one. > > (This scenario is probably oversimplified, ignoring things like DNS > caching, TTL, etc., etc. Hopefully it doesn't include too many lies.) > > > How I Plan to Do It. > ------------------- > > I've noticed that: > > 1. The class MYSQL_BIN_LOG seems to be involved in most binlogging > 2. MYSQL_BIN_LOG inherits from TC_LOG and MYSQL_LOG. It is created in > mysqld.cc at startup. > > My current partial implementation has: > > 1. Class DB_BIN_LOG inheriting from MYSQL_BIN_LOG, adding members like > db_name > db_binlog_name > db_index_name > and methods to access them, etc. > > 2. Singleton class USER_BIN_LOG_MGR. (Should probably be DB_BIN_LOG_MGR), > which maintains a db_binlog_hash of information about each DB being > binlogged. A DB_BIN_LOG_ENTRY (in the hash) contains: > dbname > locked (bool)--has it been locked by FLUSH ... TABLES WITH READ LOCK? > binlog file > binlog_pos > is_active > a pointer to a DB_BIN_LOG instance > > USER_BINLOG_MGR has a rw lock to protect access to its hash of > currently binlogging db's. (Most accesses will probably be > read-only?) > > Is it plausible to identify all places where a binlog addition might > occur? In those places, if the singleton USER_BIN_LOG_MRG exists, ask > it if the current DB is being binlogged. If so, send the request to > the DB_BIN_LOG instance. > > > Questions I Have. > ---------------- > > 1. I notice that binlog is in the list of engines and plugins > (displayed by SHOW ...). The bottom of log.cc has > "mysql_declare_plugin(binlog)". Do I need one for db_plugin? > (probably not?) > 2. I've diagrammed the binary log event taxonomy--the 24 classes that > inherit from Log_event (in log_event.cc/h). That was my main > motivation for wanting to inherit from MYSQL_BIN_LOG. > 3. On the other hand, I wonder if inheriting from MYSQL_BIN_LOG might > be overkill for "master only" binlogging--without the > slave/replication part of it. > 4. What version should I be working on? (We're currently running > 5.1.47.) What should I check out of bzr/launchpad? (Is that still > what we're doing with the changes in ownership?) > 5. I see that THD is involved in binlogging. (Several THD methods are > defined in log.cc.) Are there any caveats here? > 6. What files should I use? A new h/cc pair? ... or just add to the > hierarchy in log.h/cc? > > In an ideal world, it would be nice to inherit polymorphically from > MYSQL_BIN_LOG, overriding methods that set the names of the binlog > file and index file (etc.) then just call the methods and have it do > the "right thing." On the other hand, I get the impression that I > might be oversimplifying. > > I welcome your advice!! > > What I've Implemented So Far (about 13 Prototypes). > -------------------------------------------------- > > (PRELIMINARY NOTE:) > > Our initial plan was to call it "Per-User Binlogging". The user here > is a cPanel [control panel] user. cPanel's database naming convention > is to have all of a user's database names start with the username, > followed by an underscore, followed by a distinctive database > name. Thus all the commands above start with > > <verb> USER ... > > Originally most of them could end either "DATABASE <db>" or "USER > <user>" (with user meaning cPanel user). Thus, in the original plan > the first command above was: > > START USER BINLOG FOR (USER <user> | DATABASE <db>) > > We have realized that per-database binlogging is all we really need. > We imagine that others in our situation might want something similar. > Since not everyone uses cPanel, we should forget per[cPanel]-user > binlogging, focusing on per-database binlogging. > > I have several versions of lex and bison/yacc files (sql_yacc.yy, > lex.h) to implement the above (and earlier variants). The recent > versions avoid increasing the %expect'ed 169 shift/reduce conflicts > specified in sql_yacc.yy. > > (END OF PRELIMINARY NOTE) > > I currently have 13 different Subversion branches with different > (partial) implementations. > > 1. All have some lex/bison grammer defined. (I'm not completely > satisfied with any of them.) > > 2. Per-db binlogging is enabled by two my.cnf (or command-line) > options: > > user_binlog_dir=/full/directory/path > user_binlog_persist_file=user_binlog.info > > Our servers have fast solid-state drives that we will use for per-db > binlogging. We want to specify a separate directory to store all the > per-db binlogs > > The persist_file stores information about the current state of > db-binlogging--to survive a restart. > > Just about every instance of "user_binlog" in this note should > probably be changed to "db_binlog" ... (???) > > 3. I have added cases in sql_parse.cc for > > SQLCOM_START_USER_BINLOG_DB: > SQLCOM_FLUSH_AND_LOCK_DB: > SQLCOM_SHOW_USER_BINLOG_STATUS: > SQLCOM_UNLOCK_DB: > SQLCOM_STOP_USER_BINLOG_DB: > > (as well as some "USER" variants like SQLCOM_START_USER_BINLOG_USER:, > which should probably abandoned.) > > They should probably be changed to something like: > > SQLCOM_START_DB_BINLOG: > SQLCOM_FLUSH_AND_LOCK_DB: > SQLCOM_SHOW_DB_BINLOG_STATUS: > SQLCOM_UNLOCK_DB: > SQLCOM_STOP_DB_BINLOG: > > ----------------- > > Now that you've read it, please feel free to throw darts, etc. At this > point I'm willing to discard everytning and start over. (In the worst > case--if everyone on this list thinks per-db binlogging is a terrible > idea--I guess I'll be forced to create our own patch [sigh].) > > On the other hand, I would love to help contribute to this effort. (If > per-db binlogging is already underway, let me know, and I'll likely help.) > Thanks in advance for feedback/suggestions/help. Feel free to contact > me privately if you want to. > > Weldon Whipple > weldon(a)whipple.org > > -- > MySQL Internals Mailing List > For list archives: http://lists.mysql.com/internals > To unsubscribe: http://lists.mysql.com/internals?unsub=mdcallag@gmail.com > > -- Mark Callaghan mdcallag(a)gmail.com

3 2

[Maria-discuss] italian translation
by Federico Razzoli 12 Sep '10

12 Sep '10

Hello. I appreciate so much your new Knowledge Base. The pages that are already on line are very clear and complete, as the MySQL docs. But, unlike the MySQL docs, the Knowledge Base has free licenses. For these reasons, I would like to translate into italian - which is my natural language. Am I allowed to do that? As far as I understand, both the licenses used (CC BY-SA and GNU FDL) allow translations, as long as they have the same licenses used by the original work. But I want to be sure before starting. I can't translate all the articles, so I'll only translate some of the most interesting documents. But, as the documentation grows, maybe I may try to set up a group of volounteers. We'll see. Regards, Federico

2 1

[Maria-discuss] Mac package (was Re: Contact Form - []: jeremy ellison)
by Hakan Kuecuekyilmaz 26 Aug '10

26 Aug '10

Hi Jeremy, all, On 16.08.2010, at 12:47, Bo Thorsen wrote: > Den 14-08-2010 05:25, Contact Form skrev: >> Looks interesting, why do you not have a mariadb package for Apple >> Mac OS X? This is the basis for my blog, bb, and server backend. If >> you have no Mac support it's not a drop in replacement for MYSQL. > > Hi Jeremy, > > The reason is that no one has offered to do it yet. MariaDB isn't a product sold by Monty Program, but an open source community project. Us Monty Program guys do a lot of the work, though. > > If someone will contribute the necessary work to get Mac packages, they will be included. Until then, it's source code only. Should you be interested in doing this, or know someone who will, then you should ask about the necessary work on the mailing list > maria-discuss(a)lists.launchpad.net. I read about your request for a MariaDB package for Mac OS X on my vacation and did some research. Back from vacation I have an alpha version of MariaDB package for Mac OS X for you and for our community to test. Caution: this is the first installer I wrote on Mac, so do not use it on a production system. Please test the installer and provide us with feedback. Known issues in the MariaDB installer: * The Preferences Pane app for starting/stopping the server instance is missing * The installer for setting up MariaDB as a Startup Item is missing. Side node: while digging into the Mac installer I found two bugs in the MySQL Mac OS X installer. * http://bugs.mysql.com/bug.php?id=56279 Mac installer does not work as documented * http://bugs.mysql.com/bug.php?id=56280 Mac installer's postflight script does not work all the time You can grab the package from here: http://lisas.de/~hakan/file/mariadb-5.1.49-osx10.6-x86.pkg Best, Hakan -- Hakan Küçükyılmaz, QA/Benchmark Engineer, Stuttgart/Germany Monty Program Ab, http://askmonty.org/ Skype: hakank_ Phone: +49 171 1919839

1 0

[Maria-discuss] Clustering with Maria/Aria engine
by Joshua Partogi 26 Aug '10

26 Aug '10

Hi all, Does Maria/Aria engine supports clustering? Or do we still have to use NDB for this? Thanks for your help. Kind regards, Joshua. -- http://twitter.com/scrum8

3 4

[Maria-discuss] mariadb-mysql command-line problem on CentOS 5.5 i386
by Daniel Bartholomew 24 Aug '10

24 Aug '10

I saw a comment today from Eric Koh on the Knowledgebase article for the mysql Command-line Client: http://kb.askmonty.org/v/mysql-command-line-client --------------------------------------------------------------------- mariadb-mysql command-line problem on CentOS 5.5 i386 I tried these 2 RPMs MariaDB-client-5.1.42-68.el5.i386.rpm MariaDB-client-5.1.49-82.el5.i386.rpm I get this message when starting the mysql client mysql: symbol mysql_get_server_name, version libmysqlclient_16 not defined in file libmysqlclient.so.16 with link time reference running ldconfig/rebooting doesnt help. The RPMs for the server are fine though. I am thus using MariaDB-server and MariaDB-shared together with MySQL-client-community --------------------------------------------------------------------- Anyone know what might be going on here? Thanks. -- Daniel Bartholomew Monty Program - http://askmonty.org

4 6

[Maria-discuss] my_atomic_add64
by Oleg Tsarev 24 Aug '10

24 Aug '10

Hello, I need my_atomic_add64 in mysql. Can i simple add following macros, or i need more advanced tricks? tsarev@main:/storage/project/percona/rtd_2$ diff -Nur ../rtd/c/include/my_atomic.h c/include/my_atomic.h --- ../rtd/c/include/my_atomic.h 2010-07-09 16:35:11.000000000 +0400 +++ c/include/my_atomic.h 2010-08-17 18:57:07.648819066 +0400 @@ -96,25 +96,30 @@ make_atomic_cas( 8) make_atomic_cas(16) make_atomic_cas(32) +make_atomic_cas(64) make_atomic_cas(ptr) make_atomic_add( 8) make_atomic_add(16) make_atomic_add(32) +make_atomic_add(64) make_atomic_load( 8) make_atomic_load(16) make_atomic_load(32) +make_atomic_load(64) make_atomic_load(ptr) make_atomic_store( 8) make_atomic_store(16) make_atomic_store(32) +make_atomic_store(64) make_atomic_store(ptr) make_atomic_swap( 8) make_atomic_swap(16) make_atomic_swap(32) +make_atomic_swap(64) make_atomic_swap(ptr) #undef make_atomic_add --- Oleg Tsarev, Software Engineer, Percona Inc.

3 4

[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