- discuss - lists.mariadb.org

Re: [Maria-discuss] [Maria-developers] Problem with MariaDb as a Windows service
by Alex Budovski 24 Nov '10

24 Nov '10

> Right, this was subsequently fixed in MySQL in > http://lists.mysql.com/commits/118824 > Yes, I noticed that too late.

1 0

[Maria-discuss] Problem with MariaDb as a Windows service
by Peter Laursen 20 Nov '10

20 Nov '10

Some person claimed here around one week ago that MariaDB would not run as service after "mysqld --install". I replied that it worked for me - but it does not with the latest stable version 5.2.3 (it did with the 5.2.3 RC) (so my apology to this person!) Windows console output: C:\maria52\bin>sc delete maria52 [SC] DeleteService LYKKEDES C:\maria52\bin>mysqld --install maria52 Service successfully installed. C:\maria52\bin>net start maria52 Tjenesten maria52 starter... Tjenesten maria52 kunne ikke startes. Der opstod en systemfejl. Systemfejlen 1067 opstod. (From Danish --> service could not start --> the system error 1067 occurred) I have tried with both old datadir and the empty one shipped with mariaDB and also my old my.ini as well as a fresh one. Also I tried to add --defaults-dir to the registry (just in case I had overlooked a my.ini copy in C:\ or C:\Windows) and adding doublequotes key manually like "C:\maria52\bin\mysqld" --defaults-file="C:\maria52\my.ini" maria52 I double checked there are no conflicts wit all settings. The command "C:\maria52\bin\mysqld" --defaults-file="C:\maria52\my.ini" .. starts the server (also when --defaults-file is omitted) .. but not as a service of course. Was it tested with the 5.2.3 GA release that it will run as a Windows service? The RC worked as a charm - but the GA will not. Any idea? (I am using Win7, 64 bit, Home Premium) Peter (Webyog)

2 6

[Maria-discuss] virtual columns
by Federico Razzoli 13 Nov '10

13 Nov '10

Hello I've read in the documentation that any legal SQL expression SHOULD be allowed in the Virtual Columns definition. I'm trying to use a (deterministic) stored function, but I get an error: ERROR 1642 (HY000): Function or expression is not allowed for column 'vc1' Here's my create table: CREATE TABLE t1 (c1 int, c2 int, vc1 INT AS (c1+functest()) VIRTUAL); Here's how functest() is defined: CREATE FUNCTION functest() RETURNS TINYINT DETERMINISTIC BEGIN UPDATE t2 SET c1 = c1 + 1; RETURN 0; END I think this clearly explains what I am trying to do: a function which logs accesses to a certain column. Why do I get error 1642? Is the bug in the documentation or in the software? Will future MariaDB versions allow us to create virtual columns which call stored functions? Federico

5 6

[Maria-discuss] Install as service on Windows 7 64x
by Francisco Ernesto Teixeira 11 Nov '10

11 Nov '10

Hello, First I have to say that it's a great idea. I looked for replace my MySQL servers to MariaDB, but I can't loose everything. First, when I run the downloaded MariaDB 5.2.3 as "C:\Program Files (x86)\MariaDB 5.2.3\bin\mysqld.exe" --standalone everything is allrigth. When I follow these steps on Prompt: 1. "C:\Program Files (x86)\MariaDB 5.2.3\bin\mysqld.exe" --install 2. net start mysql Says: O serviço de MySQL está sendo iniciado..... O serviço de MySQL não pôde ser iniciado. Erro de sistema. Erro de sistema 1067. O processo foi finalizado de forma inesperada. BUT, don't exists error log on Data folder or log!!! Thank you.

2 1

[Maria-discuss] Non-root install of MariaDB
by Arun Jagatheesan 05 Nov '10

05 Nov '10

Hi, Is it mandatory to be a privileged user for MariaDB? (hope not so). Is there an existing documentation or any suggestions for a non-root instal of MariaDB on Linux (CentOS)? Thanks, Arun

2 2

Re: [Maria-discuss] How to lock individual tables in 1-db-per-file binlogging (Was: Re: Per-db binlogging)
by Weldon Whipple 28 Oct '10

28 Oct '10

Dmitry, Thank you very much for your response! (I apologize for the slowness of my reply. I've been fighting a cold for several days.) See below: On Thu, Oct 21, 2010 at 11:15 PM, Dmitry Lenev <Dmitry.Lenev(a)oracle.com> wrote: > Hello Weldon! > > * Weldon Whipple <weldon(a)whipple.org> [10/10/20 00:26]: >> Thanks for the great feedback. It got me looking at the capabilities >> of MySQL 5.5. >> >> It's POSSIBLE that the new syntax: >> >> FLUSH TABLES t1, t2, t3, ... , tn WITH READ LOCK >> >> is PRECISELY the functionality we need! >> >> Our (perl) migration script MIGHT (for example) do the following: >> >> Issue the following command (implemented by me ...): >> >> 1. START BINLOG FOR DATABASE <db>; >> >> (for each database a given user owns. [Hmmm... Maybe my implementation >> allows multiple <db> arguments. I need to check.] Each database would >> start binlogging to its own file. [We will configure--in my.cnf--the >> 1-database-per-file binlogs to be stored on a very fast solid-state >> drive.]) >> >> Then the script creates a list of all tables in all the user's >> databases being migrated to another server and issues the command (new >> to MySQL 5.5): >> >> 2. FLUSH TABLES t1, t2, t3, ..., tn WITH READ LOCK; >> >> (where the tables t1, t2, ..., tn are the tables being migrated.) >> >> Then issue the following command (implemented by me ...): > > AFAIU you also want to prohibit creation of tables in this database > after this point as otherwise you can't guarantee binlog stability. > Also what about non-table objects like stored routines - don't you > want to block their modification as well? The above statement won't > provide this as it locks only *existing* *tables*. > > OTOH in my opinion it should be fairly easy to implement functionality > that you need (i.e. blocking of all DDL and DML that changes data in > database) on the basis of FLUSH TABLES <table_list> WITH READ LOCK > implementation. You just need to take database-scope S metadata lock > in addition to locking and flushing all tables in database. Such lock > will block creation of new tables and any DDL on stored routines in > the database. Maybe I need to implement a new command (something like FLUSH DATABASE db WITH READ LOCK) that--conceptually, at least--read locks all the tables in the database (like FLUSH TABLES ... WITH READ LOCK) *as well as* blocks all DDL and DML that changes data in database. I'll look through the code and find where FLUSH TABLES ... WITH READ LOCK is implemented. If I can't figure out how to "take database-scope S metadata lock," I'll get back with you... (I suppose I'll need a corresponding UNLOCK DATABASES command as well.) I've observed (and read) that the read locks are held as long as the client connection remains open, and that the UNLOCK applies to all the locks created during the same connection that created the read locks. I guess that means that our migration tool will need to maintain the connection with mysqld while it dumps (or scp's, in the case of MyISAM tables) the database snapshot. (That shouldn't necessarily be a problem--it's just something we need to be aware of. It makes sense for it to work that way.) > >> >> BUG IDENTIFIED. >> <snip/> > I have taken the liberty to report this issue as bug #57649 > (http://bugs.mysql.com/bug.php?id=57649) > > Thanks for pointing it out! Thanks for reporting it. > >> >> QUESTION: >> I read that Larry Ellison stated (at a conference) that 5.5 would be >> out by year end. How likely is that? Our company generally waits for >> some point releases before changing our customer boxes to the next >> release. >> >> Should I try to back port those locks to 5.1 in the interim? ... or >> leave well enough alone? > > I don't think it makes sense to backport metadata locks to 5.1 as it is > likely to require back-porting of too much other changes from 5.5. > > OTOH implementing what you need on top of infrastructure already present > in 5.1 is likely to require significant efforts as well. > > So I think it is better to base your work on 5.5 tree. I agree. There are quite a few major changes in 5.5. I haven't noticed meta-data-locks (MDL) before. The PSI additions to the mutex and conditions also look new. (I'm sure I'm missing lots of other changes.) (Regarding the PSI_mutex_key, PSI_rwlock_key and similar additions: I've tried to follow the patterns in 5.5 as I added my own mutexes. I'm assuming [hoping?] that they work when multiple instances of an object all refer to the same-named PSI_xxx-key ??? ...) (Is there any externally available documentation on MySQL's use of PSI?) > > Best regards, > -- > Dmitry Lenev, Software Developer > Oracle Development SPB/MySQL, www.mysql.com > > Are you MySQL certified? http://www.mysql.com/certification Not that I know of. Weldon Whipple weldon(a)whipple.org

2 2

Re: [Maria-discuss] italian translation
by Bryan Alsdorf 25 Oct '10

25 Oct '10

Hi, On 09/14/2010 11:24 PM, Colin Charles wrote: > Hi! > > On 12 Sep 2010, at 07:51, Federico Razzoli wrote: > >> 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. > > > You are absolutely allowed to do that. You can translate it to Italian with no issue at all > > Currently (and I think, by design) it seems that kb.askmonty.org will be in the English language. Bryan (CC'ed in this email) is the right person to help setup an instance for the Italian language (something like it.kb.askmonty.org) if you were planning on making a larger translation project. It would be great if you could harness a group of volunteers We actually want to setup a structure to support translations inside our KB, I just haven't had the time to design or implement it. My two concerns are how to properly display content in another language and how to keep translated articles up to date when the English version is updated (or vice versa). If you have any ideas, please let me know. > In the meantime however, as you'd like to just translate interesting documents, just go ahead, translate them, and it would rock if you'd point folk to the English version at the askmonty KB > > Good luck with your translation work and tell us if you need any help! Yes please let us know if there is anything we can do to help. I will try to work on supporting other languages soon since people are actually translating articles. Best Regards, -- Bryan Alsdorf, Lead Web Developer Monty Program AB. http://montyprogram.com

4 4

Re: [Maria-discuss] How to lock individual tables in 1-db-per-file binlogging (Was: Re: Per-db binlogging)
by Weldon Whipple 19 Oct '10

19 Oct '10

Davi, Sergei (and everyone else), Thanks for the great feedback. It got me looking at the capabilities of MySQL 5.5. It's POSSIBLE that the new syntax: FLUSH TABLES t1, t2, t3, ... , tn WITH READ LOCK is PRECISELY the functionality we need! Our (perl) migration script MIGHT (for example) do the following: Issue the following command (implemented by me ...): 1. START BINLOG FOR DATABASE <db>; (for each database a given user owns. [Hmmm... Maybe my implementation allows multiple <db> arguments. I need to check.] Each database would start binlogging to its own file. [We will configure--in my.cnf--the 1-database-per-file binlogs to be stored on a very fast solid-state drive.]) Then the script creates a list of all tables in all the user's databases being migrated to another server and issues the command (new to MySQL 5.5): 2. FLUSH TABLES t1, t2, t3, ..., tn WITH READ LOCK; (where the tables t1, t2, ..., tn are the tables being migrated.) Then issue the following command (implemented by me ...): 3. SHOW DATABASE BINLOG STATUS; which shows--for each database being binlogged to its own file: a. the database name b. whether binlogging is active c. the name of the current binlog file d. the binlog position in that file. The script captures the coordinates, then 4. Creates a copy of the database(s) using mysqldump or scp/tar/gzip, etc. (MyISAM databases will probably just be copied "raw," because it is much faster. Innodb will probably have to use mysqldump or something similar.) 5. UNLOCK TABLES: On the new server, the copied/mysqldump'ed files will be used to bring up the mysql on the new server. At some future time--hopefully SOON--the tables could be locked again. For any where the coordinates have changed: The binlog file(s) would be copied to the new server, "played" by mysqlbinlog, and the new server would be brought online on the new box and taken offline on the old. When the migration is determined to be complete, issue this command (implemented by me) on the old box: 6. STOP BINLOG FOR DATABASE <db>; for all the user's databases ... and the migration is done. QUESTIONS/ASSUMPTIONS: 1. In playing with the new FLUSH TABLES t1, t2, ..., tn command, I've noticed that if the connection drops, the locks are all unlocked. ... So our scripts will have to make sure the connection stays up. (ANY SUGGESTIONS?) 2. The UNLOCK TABLES command unlocks all the tables locked earlier in the same session. (So it doesn't need the variable argument list of tables to unlock ...) A post on dev.mysql.com shows the following possible sequence of commands: FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; SYSTEM xfs_freeze -f /var/lib/mysql; SYSTEM YOUR_SCRIPT_TO_CREATE_SNAPSHOT.sh; SYSTEM xfs_freeze -u /var/lib/mysql; UNLOCK TABLES; EXIT; (The FLUSH TABLES command would correspond to #2 above. SHOW MASTER STATUS would correspond to #3 above. UNLOCK TABLES would correspond to #5 above. The SYSTEM commands would have to be replaced by our own commands/scripts.) Would running (sub-)scripts from SYSTEM commands inside MySQL be sufficient to hold the locks? BUG IDENTIFIED. I noticed the following mysqld-crashing bug--in 5.5.6 rc as well as the one I modified: 1. FLUSH TABLES t1, t2, t3, ..., tn WITH READ LOCK; 2. FLUSH TABLES; The second FLUSH TABLES (with no added tokens) crashes at mdl.cc:1563 : DBUG_ASSERT(mdl_request->type != MDL_EXCLUSIVE || is_lock_owner(MDL_key::GLOBAL, "", "", MDL_INTENTION_EXCLUSIVE)); because is_lock_owner() returns NULL when FLUSH TABLES is called immediately after FLUSH TABLES [with additional args]; QUESTION: I read that Larry Ellison stated (at a conference) that 5.5 would be out by year end. How likely is that? Our company generally waits for some point releases before changing our customer boxes to the next release. Should I try to back port those locks to 5.1 in the interim? ... or leave well enough alone? Thanks in advance for your feedback/help/advice!! We.don Whipple On Mon, Oct 18, 2010 at 12:48 PM, Davi Arnaut <davi.arnaut(a)oracle.com> wrote: > On 10/18/10 3:16 PM, Weldon Whipple wrote: >> >> Well, I finished a working prototype that does everything except the >> locking and unlocking of databases. Someone mentioned that it's now >> possible to lock individual tables. Would you--anyone--be willing to >> point me toward places in the 5.5 code that use that >> locking/unlocking? >> > > Do you want to lock the database, or individual tables, or both? Anyway, > take a look at mysql_rm_db as the locking is all very similar. Roughly, the > algorithm of a DROP DATABASE is: > > // Protect against other schema operations > lock_schema_name("db1"); > // Build list of tables in the db1 directory > table_list = .. > // Initialize metadata lock request > table_list->mdl_request.init(MDL_key::TABLE, > table_list->db, > table_list->table_name, MDL_EXCLUSIVE); > // Lock tables > lock_table_names(thd, tables, NULL ...) > // Remove from cache and drop the table... > > The best thing at this point is to study (and debug) a similar statement and > understand how metadata locks work (mdl prefix) or how tables are > open/locked. > > Regards, > > Davi > > -- > MySQL Internals Mailing List > For list archives: http://lists.mysql.com/internals > To unsubscribe: http://lists.mysql.com/internals?unsub=weldon@whipple.org > >

1 0

Re: [Maria-discuss] How to lock individual tables in 1-db-per-file binlogging (Was: Re: Per-db binlogging)
by Weldon Whipple 18 Oct '10

18 Oct '10

Davi, Thanks for the pointers! I'd like to lock an entire database. (I guess I can iterate through the tables in database if that's the only way. [I suppose there might be possible locking issues to deal with ...]) I'll look at the source you recommend ... Weldon weldon(a)whipple.org On Mon, Oct 18, 2010 at 12:48 PM, Davi Arnaut <davi.arnaut(a)oracle.com> wrote: > On 10/18/10 3:16 PM, Weldon Whipple wrote: >> >> Well, I finished a working prototype that does everything except the >> locking and unlocking of databases. Someone mentioned that it's now >> possible to lock individual tables. Would you--anyone--be willing to >> point me toward places in the 5.5 code that use that >> locking/unlocking? >> > > Do you want to lock the database, or individual tables, or both? Anyway, > take a look at mysql_rm_db as the locking is all very similar. Roughly, the > algorithm of a DROP DATABASE is: > > // Protect against other schema operations > lock_schema_name("db1"); > // Build list of tables in the db1 directory > table_list = .. > // Initialize metadata lock request > table_list->mdl_request.init(MDL_key::TABLE, > table_list->db, > table_list->table_name, MDL_EXCLUSIVE); > // Lock tables > lock_table_names(thd, tables, NULL ...) > // Remove from cache and drop the table... > > The best thing at this point is to study (and debug) a similar statement and > understand how metadata locks work (mdl prefix) or how tables are > open/locked. > > Regards, > > Davi > > -- > MySQL Internals Mailing List > For list archives: http://lists.mysql.com/internals > To unsubscribe: http://lists.mysql.com/internals?unsub=weldon@whipple.org > >

1 0

[Maria-discuss] How to lock individual tables in 1-db-per-file binlogging (Was: Re: Per-db binlogging)
by Weldon Whipple 18 Oct '10

18 Oct '10

Thanks, Sergei and everyone else, for your feedback/ideas! Well, I finished a working prototype that does everything except the locking and unlocking of databases. Someone mentioned that it's now possible to lock individual tables. Would you--anyone--be willing to point me toward places in the 5.5 code that use that locking/unlocking? I'm interested in sharing the changes with the community if there is any interest. (Any suggestions about whether I should prefer launchpad versus forge.mysql.com or some other place to share? [I once owned a project on SourceForge--I think it's still there, but not very active ...]. :-) A few comments in response to your comments: On Thu, Sep 16, 2010 at 6:34 AM, Sergei Golubchik <serg(a)askmonty.org> wrote: > Hi, Weldon! > > On Sep 15, Weldon Whipple wrote: >> >> > See where mysql_bin_log.write is called. >> >> Excellent information! (I was hoping it might be that easy ???). If I >> succeed in making a DB_BIN_LOG class derive from MYSQL_BIN_LOG (with an >> instance of DB_BIN_LOG for each database that is currently binlogging), >> I could--in theory, at least--just call something like >> db_binlog->write () at those places. > > Better make this write method virtual and implement it in your derived > class. I did implement it as a derived class, but it was a bit more work than just reimplementing the ::write() method. (I use separate instances of DB_BINLOG--the derived class--for each database that is binlogging to its separate binlog file [sequence].) I had to have a few additional methods (extremely small) to set up for opening files named <database>-bin.<seq#>, <database>-bin.index, etc. I also maintain a db_binlog.info file that remembers the databases being binlogged across restarts. One major change was in the way the global MYSQL_BIN_LOG class is referenced. In the original code, it comes into existence at the top of binlog.cc. Before I started making my modifications, I changed MYSQL_BIN_LOG to be a pointer to MYSQL_BIN_LOG, declared in mysqld.cc (and extern'ed in mysqld.h). At the point in mysqld.cc where mysql_bin_log is first referenced I "new" the global instance of MYSQL_BIN_LOG*. I went through all the files that reference mysql_bin_log and made the necessary changes so that mysql_bin_log works as a pointer. (Example: Change "mysql_bin_log.is_active()" to "mysql_bin_log->is_active().) Also, before, referencing mysql_bin_log*, I always make sure it is non-NULL ... Then I made my changes for 1 db-per-file binlogging, using #ifdef HAVE_DB_BINLOG/#endif blocks to allow building with or without 1-db-per-file binlogging. As I'm typing this post, I'm running mysql-test-run.pl on a build without HAVE_DB_BINLOG defined and it seems to be passing all the tests. I need to write additional test cases for when HAVE_DB_BINLOG is defined ... Thanks in advance for ideas/suggestions (as well as for the ideas in the past). Weldon Whipple weldon(a)whipple.org (I'll leave the conversations below to help refresh your memory on this thread's past.) > >> >> 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?) >> > >> > depends. >> > binlog needs to be declated a plugin, to be able to pretent being a >> > storage engine, to be able to force two phase commit (2PC). >> > >> > 2PC is used to commit or rollback a transaction atomically in more >> > than one engine. And MySQL only uses 2PC when a transaction spans >> > more than one engine. But we want a transaction to be committed in >> > the engine and written to a binlog - and it should be done >> > atomically too. So, binlog pretends to be an engine, pretends to >> > participate in a transaction, which forces MySQL to use 2PC which >> > can guarantee that a transaction is either committed and written to >> > a binlog - or not committed and not written. Reliably, even if MySQL >> > crashes in the middle. >> > >> > So, if you don't use innodb, or don't use >> > innodb_flush_log_at_trx_commit=1 or don't use sync_binlog=1 you >> > probably don't need to support 2PC as your setup is not crash proof >> > anyway. >> > >> > If you care about innodb<->binlog consistency in the presence of >> > crashes, you should support 2PC in your binlog, as explained above. >> >> We DO have a small percentage of innodb databases. If 2PC commits >> (rolls back, etc.) atomically to more than one engine, does that mean >> TWO (only)? ... or could it happen for three (specifically, innodb, >> MYSQL_BIN_LOG's binlog, and DB_BIN_LOG's binlog)? > > See http://en.wikipedia.org/wiki/Two-phase_commit_protocol > > It's two phases, not two engines, any number of engines can participate > in a transaction. > > But! All of them but one have to support two-phase commit. Binlog does > not support it. Apparently, DB_BIN_LOG will not support it either - > adding two-phase commit support to binlog will surely be beyond the > scope of your work. > > It means, you cannot have InnoDB, binlog, and DB_BIN_LOG to participate > in a two-phase commit. But you can disable MYSQL_BIN_LOG altogether and > only use DB_BIN_LOG - in that case you can have a two-phase commit > between DB_BIN_LOG and InnoDB. > >> The two kinds of binlog files would have the same format (except that >> one is for only one database). Are they considered two storage >> engines/plugins? > > If you want to write to both - yes. A transaction will need to be > written to both independently, so you want them to be in sync, and MySQL > uses 2PC to synchronize different data storages. > > You can solve that by > > 1) not using MYSQL_BIN_LOG. In this case there's only InnoDB and > DB_BIN_LOG to keep in sync, and InnoDB can do 2PC. > > 2) not making DB_BIN_LOG independent from MYSQL_BIN_LOG. For example, > everything is written to binlog only. Then you have a background > thread (or even a separate process) that reads binlog and writes your > per-db binlogs. In that case you have only InnoDB and MYSQL_BIN_LOG > to keep in sync, and MySQL already does that. Your background thread > is only catching up, it is allowed to fall behind. > >> (I notice that the "mysql_declare_plugin(binlog)" declaration is at >> the bottom of log.cc. Most of the other storage engines' corresponding >> declarations are in files in the storage subdirectories. If I were to >> need a "mysql_declare_plugin(db_binlog)" declaration, could it also go >> at the bottom of log.cc? ... Or (since a DB_BIN_LOG is AKO [a kind of] >> MYSQL_BIN_LOG [in the OO sense]), are MYSQL_BIN_LOGs and DB_BIN_LOGs >> both kinds of binlog plugins ...? > > if you want to have two plugins in one file you still need to use one > mysql_declare_plugin(), not two. See example at the end of > http://www.packtpub.com/article/look-inside-mysql-daemon-plugin > > Regards, > Sergei >

1 0