Re: Administrative Tools
Hi Arjan, > Any plans to port Database Workbench to linux? No, there will be no native Linux Database Workbench. From what I've heard, it works fine under Wine though. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle & MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
best third party backup/restore software for mysql
Hi All, Can you please suggest any good third party software for backup and restore of mysql db. Right now i am looking at ZMANDA. Has any one worked on this, and used for their production db, please let me know how it is and can we use the same for our production 24/7 mysql db. regards anandkl
Re: mysql creating lots of processes (not threads, linux processes)
Hi and thanks for the answers. As I understand from what I've read in the comments here: http://www.xaprb.com/blog/2006/07/16/how-we-enabled-threading-in-mysql/ This might be normal behaviour. Apparently the different types of threading used on the boxes (LinuxThreads vs NPTL) can result, for the case of LinuxThreads, in seeing the mysql threads as processes, when in fact they're not. I have no clue how boxes prepared and compiled the same way could end up with different setups, but it seems this is the reason from what i get when running "getconf GNU_LIBPTHREAD_VERSION". It seems the LinuxThreads builds are much less efficient than the NPTL ones, which for the same traffic and mysql config consume much less memory and resources (not sure how normal that is). It still sems like there's a problem here because the number of threads on the linuxthreads boxes seem to never decrease, and some need to be manually killed at shutdown. Anyway, I'll switch all boxes to NPTL and see if there's any more issues or if that does the trick. Cheers, Quentin On 5/31/07, Scott Tanner <[EMAIL PROTECTED]> wrote: I know I've run into this before, and it was a build issue. We build from source on redhat linux, so it may not be related to your issue. Anyways, I just did a test and built mysql from source with the flag "--with-mysqld-ldflags=-all-static". I end up with the process spawning mysqld: >ps -eaf | grep -c my 12 I removed that flag and rebuilt again, and now I have 2 processes. Just for reference: /configure --prefix=/usr/pkg/mysql5_test \ --with-libwrap=/usr/pkg/tcpd \ --with-mysqld-user=mydaemon \ --without-debug \ --with-unix-socket-path=/var/mysql/mysql.sock \ --with-client-ldflags=-all-static \ --enable-shared \ --enable-thread-safe-client \ --with-extra-charsets=none Scott On Wed, 2007-05-30 at 18:29 +0200, Quentin Gouedard wrote: > Oh and by the way mysql works just fine on that machine. You can run > queries without any problems. > Only it keeps spawning new processes over again. It takes about 3h > before the machine starts having problems due to memory getting full > of useless mysql processes. > I just got a new machine for my site, and it's happenning there too > (although on none of my 5 other boxes), which is good cause i can do > some more extensive testing before i actually use it. > Another thing is, mysql won't stop. > /etc/init.d/mysql stop just hangs. > There are some processes that need to be killed manually in the end. > I'm guessing this could have to do with the process owner being root. > > Some similar problems are reported here though: > http://forums.gentoo.org//viewtopic-t-544730-highlight-mysql.html > > > On 5/30/07, Quentin Gouedard <[EMAIL PROTECTED]> wrote: > Merci Geoffroy, > starting from the command prompt shows: > > # /usr/sbin/mysqld --console --verbose --basedir=/usr > --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid > --socket=/var/run/mysqld/mysqld.sock > 070530 18:01:28 [Note] /usr/sbin/mysqld: ready for > connections. > Version: '5.0.38-log' socket: '/var/run/mysqld/mysqld.sock' > port: 3306 Gentoo Linux mysql-5.0.38 > > > However running a ps already shows multiple mysqld processes. > Something i had not noticed so far: > > # ps -ef | grep mysqld > mysql25752 27831 0 18:01 pts/2 > 00:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr > --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid > --socket=/var/run/mysqld/mysqld.sock > root 16560 25752 0 18:01 pts/2 > 00:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr > --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid > --socket=/var/run/mysqld/mysqld.sock > root 16560 25752 0 18:01 pts/2 > 00:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr > --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid > --socket=/var/run/mysqld/mysqld.sock > root 23390 16560 0 18:01 pts/2 > 00:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr > --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid > --socket=/var/run/mysqld/mysqld.sock(etc.) > > the first process created is the only one with "mysql" owner. > All the subsequent processes are own by root, and have as > parent one of the previously created processes (not always the > first one). Not sure if that helps in understanding this > though. > > Thanks anyway for your help. > > > On 5/30/07, Geoffroy Cogniaux <[EMAIL PROTECTED]> > wrote: > Try to start it with mysql_safe instead or try to > start mysqld manually > within a command prompt, without fork, to see what > happen. > ./mysqld --consol
Re: Administrative Tools
Hi Martijn, Any plans to port Database Workbench to linux? Regards, Arjan. Martijn Tonies wrote: Hello I am looking for the tools that I would use to be able to do administrative duties and to be able to create and update databases (tables, indexes and so forth). I would rather do this than do it via a command line. I would appreciate any links available. If you're on Windows, you might take a look at Database Workbench - www.upscene.com Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle & MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com
MySQL 5, stored procedure, mysql-slow.log
Hi, Is there any configuration to be setup to get each query of a stored procedure, called by a client, in the mysql-slow.log or mysql.log? For the moment, I just have the stored procedure call in the log file, but I need to trace every SQL statement of this stored procedure. # Time: 070605 21:59:01 # [EMAIL PROTECTED]: dbo[dbo] @ localhost [] # Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 1 CALL generate_random_battles(); Thanks,
Re: Sorting by a list of possible results in a column....
Mike I have a query that selects a list of results, ordering them by the status field. However, I want to further sort that by the type of status, that is: Undefined Ready for Review Top Priority Priority Completed ORDER BY FIELD( columnname, 'Undefined', 'Ready for Review', ... ) PB Mike Morton wrote: I am sure that this has been asked - but in searching through google and lists for about an hour - hopefully someone will indulge me a repeat question here. I have a query that selects a list of results, ordering them by the status field. However, I want to further sort that by the type of status, that is: Undefined Ready for Review Top Priority Priority Completed Etc... Every sort that I try, of course, sorts alphabetically. Is there a way to define how the sort function works in the order by? I know that I could do this in PHP after populating the results into an array, but that is (in my opinion) an unnecessary step that could be handled at the database query Thanks! Sample query: Select * from development order by status -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sorting by a list of possible results in a column....
Hi, You could use a case statement to implicitly convert your column to everything you want: select status, case when status = 'undefined' then 4 when status = 'Top Priority' then 1 ... End as ord_status from development order by ord_status Bye Geoffroy -Message d'origine- De : Mike Morton [mailto:[EMAIL PROTECTED] Envoyé : mardi 5 juin 2007 23:26 À : mysql@lists.mysql.com Objet : Sorting by a list of possible results in a column I am sure that this has been asked - but in searching through google and lists for about an hour - hopefully someone will indulge me a repeat question here. I have a query that selects a list of results, ordering them by the status field. However, I want to further sort that by the type of status, that is: Undefined Ready for Review Top Priority Priority Completed Etc... Every sort that I try, of course, sorts alphabetically. Is there a way to define how the sort function works in the order by? I know that I could do this in PHP after populating the results into an array, but that is (in my opinion) an unnecessary step that could be handled at the database query Thanks! Sample query: Select * from development order by status -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sorting by a list of possible results in a column....
[snip] I have a query that selects a list of results, ordering them by the status field. However, I want to further sort that by the type of status, that is: Undefined Ready for Review Top Priority Priority Completed Etc... Every sort that I try, of course, sorts alphabetically. Is there a way to define how the sort function works in the order by? [/snip] You can specify ORDER BY foo DESC or ASC and you can do multiple ORDER BY's SELECT * FROM table ORDER BY foo, bar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sorting by a list of possible results in a column....
I am sure that this has been asked - but in searching through google and lists for about an hour - hopefully someone will indulge me a repeat question here. I have a query that selects a list of results, ordering them by the status field. However, I want to further sort that by the type of status, that is: Undefined Ready for Review Top Priority Priority Completed Etc... Every sort that I try, of course, sorts alphabetically. Is there a way to define how the sort function works in the order by? I know that I could do this in PHP after populating the results into an array, but that is (in my opinion) an unnecessary step that could be handled at the database query Thanks! Sample query: Select * from development order by status -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error after system setup/replication
I have resolved the issue. As Heikki said in another posting, basically injecting a large amount of data will cause it to catch up. I made a new DB on the server and am importing a 4 GB table into it. At some point during the process the error quit getting logged. After it is done importing I can just drop the table/DB and be done with it. I am going to look into this some more as I feel this is a definite "bug" and even though this work-around fixed it - it is very kludgy. And we actually did move the log files and have it recreate them on startup. - Original Message - From: "Juan Eduardo Moreno" <[EMAIL PROTECTED]> To: "Keith Murphy" <[EMAIL PROTECTED]> Cc: mysql@lists.mysql.com Sent: Tuesday, June 5, 2007 4:24:22 PM (GMT-0500) America/New_York Subject: Re: error after system setup/replication Keith, When you copied the database, you need recreate de logfiles and the sequences. Then, startup the database. 1) clean your actually log files ( backup to another side) 2) When the database startup automatically recreate the log files and sequence. Send feedback about it. Regards, Juan On 6/5/07, Keith Murphy < [EMAIL PROTECTED] > wrote: This: 070605 15:07:27 InnoDB: Error: page 170144 log sequence number 71 2823728422 InnoDB: is in the future! Current system log sequence number 71 452597657. InnoDB: Your database may be corrupt. 070605 15:07:28 InnoDB: Error: page 61253 log sequence number 71 910331446 InnoDB: is in the future! Current system log sequence number 71 452599188. InnoDB: Your database may be corrupt. 070605 15:07:29 InnoDB: Error: page 56830 log sequence number 71 3280625836 InnoDB: is in the future! Current system log sequence number 71 452601958. InnoDB: Your database may be corrupt. shows up in the error log on a new server we are setting up. This system had the data copied to it from another db server. On the old server we locked the tables (flush tables with read lock), did a "show slave status" to get the bin log position and copied the relevant directories (50gigs) to the new server. Then we started up the new server. Everything appears to be going fine. Replication status showed that it was roughly 5000 seconds behind, but it synced up so that it is now 0 seconds behind. However, the same error message is still being written to the error log. I am not familiar with it at all. There is some mention of it in google but the answers really don't make any sense to me. Any ideas? Thanks, Keith -- Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 910-433-0786 (c) 850-637-3877 -- Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 910-433-0786 (c) 850-637-3877
Re: error after system setup/replication
Keith, When you copied the database, you need recreate de logfiles and the sequences. Then, startup the database. 1) clean your actually log files ( backup to another side) 2) When the database startup automatically recreate the log files and sequence. Send feedback about it. Regards, Juan On 6/5/07, Keith Murphy <[EMAIL PROTECTED]> wrote: This: 070605 15:07:27 InnoDB: Error: page 170144 log sequence number 71 2823728422 InnoDB: is in the future! Current system log sequence number 71 452597657. InnoDB: Your database may be corrupt. 070605 15:07:28 InnoDB: Error: page 61253 log sequence number 71 910331446 InnoDB: is in the future! Current system log sequence number 71 452599188. InnoDB: Your database may be corrupt. 070605 15:07:29 InnoDB: Error: page 56830 log sequence number 71 3280625836 InnoDB: is in the future! Current system log sequence number 71 452601958. InnoDB: Your database may be corrupt. shows up in the error log on a new server we are setting up. This system had the data copied to it from another db server. On the old server we locked the tables (flush tables with read lock), did a "show slave status" to get the bin log position and copied the relevant directories (50gigs) to the new server. Then we started up the new server. Everything appears to be going fine. Replication status showed that it was roughly 5000 seconds behind, but it synced up so that it is now 0 seconds behind. However, the same error message is still being written to the error log. I am not familiar with it at all. There is some mention of it in google but the answers really don't make any sense to me. Any ideas? Thanks, Keith -- Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 910-433-0786 (c) 850-637-3877
Re: jdbc with a UDF
Thanks for the reply. I tracked the problem down. Simple problem really, I was using an old mysql-connector-java jar. I updated that to the correct version for my database and it all worked. What version of of the JDBC driver are you using? Good question to ask. I asked myself that very question shortly after emailing the list. Thanks David Godsey > Perhaps you could show us the Java code you are using to invoke this > UDF? What are you expecting to get back form it? a result set? a > return code from a function? What version of of the JDBC driver are > you using? > > - michael > > > On 6/5/07, Dave G <[EMAIL PROTECTED]> wrote: >> I'm getting what appears to be a java reference back from values I >> return >> from a UDF. The values appear find using php, and in the mysql client. >> The result looks something like: >> >> [EMAIL PROTECTED] >> >> The result should be a string (some representing floats, others as >> integers). >> >> Any help will be appreciated. I searched on google and only found where >> another person had the same problem, but no solution. >> >> Dave G. >> >> >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >> >> > > > -- > - michael dykman > - [EMAIL PROTECTED] > > - All models are wrong. Some models are useful. > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error after system setup/replication
This: 070605 15:07:27 InnoDB: Error: page 170144 log sequence number 71 2823728422 InnoDB: is in the future! Current system log sequence number 71 452597657. InnoDB: Your database may be corrupt. 070605 15:07:28 InnoDB: Error: page 61253 log sequence number 71 910331446 InnoDB: is in the future! Current system log sequence number 71 452599188. InnoDB: Your database may be corrupt. 070605 15:07:29 InnoDB: Error: page 56830 log sequence number 71 3280625836 InnoDB: is in the future! Current system log sequence number 71 452601958. InnoDB: Your database may be corrupt. shows up in the error log on a new server we are setting up. This system had the data copied to it from another db server. On the old server we locked the tables (flush tables with read lock), did a "show slave status" to get the bin log position and copied the relevant directories (50gigs) to the new server. Then we started up the new server. Everything appears to be going fine. Replication status showed that it was roughly 5000 seconds behind, but it synced up so that it is now 0 seconds behind. However, the same error message is still being written to the error log. I am not familiar with it at all. There is some mention of it in google but the answers really don't make any sense to me. Any ideas? Thanks, Keith -- Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 910-433-0786 (c) 850-637-3877
Re: Lock Tables Question
Hi David, On Jun 5, 2007, at 3:55 PM, David T. Ashley wrote: My only concern with GET_LOCK() is that lock is server-global rather than database-global. This makes attacks possible in a shared setting (some bad person could disable your database code by going after your lock). My solution is just to lock all tables in one statement. The only question I have (and nobody has answered this) is how many tables I can include in a single LOCK TABLE statement. I thinking anything up to a few thousand shouldn't be a problem. What is the limit? The size of a query is limited by the value of the "max_allowed_packet" system parameter. So if you want to add more tables to your LOCK TABLE statement, you should just be able to increase max_allowed_packet until it works. Regards, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_init segmentation fault
On 6/5/07, S Nassar <[EMAIL PROTECTED]> wrote: I didn't build libmysql.so myself. This was installed with mysql. The application code that calls mysql_init is built statically. I see this error when building on one machine and trying to use the code on a different machine. Knowing the operating system would help. Are the build machine and the target machine identical? Building portable binaries is an art unto itself and is way out of the scope of this list.. You may have built your program staically, but the stack dump tells me that libmysql has some dynamic dependancies. Thanks for all the help, Sam On 6/5/07, Michael Dykman <[EMAIL PROTECTED]> wrote: > It looks like you application bailed while trying to resuolve the > server address, but the nature of the dump looks to me like some kind > of build error..did you build libmysql.so yourself or is this a > binary download from a trusted source? > > - michael > > > On 6/5/07, S Nassar <[EMAIL PROTECTED]> wrote: > > Hi, > > > > I am running an application that interfaces with MySQL from c++. When > > I try to run the application, I get a segmentation fault at the line > > where I call mysql_init. Here is part of the output from gdb > > > > #0 0x081a0e78 in elf_machine_rel.0 () at ../sysdeps/i386/dl-machine.h:333 > > #1 0x081a113a in elf_dynamic_do_rel.4 () at do-rel.h:100 > > #2 0x081a12db in _dl_relocate_object (l=0x821ec40, scope=0x821ed84, lazy=1, > > consider_profiling=0) at dl-reloc.c:158 > > #3 0x081984ee in dl_open_worker (a=0xbfa25380) at dl-open.c:294 > > #4 0x0817c4a4 in _dl_catch_error (objname=0xbfa2537c, errstring=0xbfa25378, > > operate=0x8198040 , args=0xbfa25380) at dl-error.c:152 > > #5 0x08198606 in _dl_open (file=0xbfa254fc "libnss_files.so.2", mode=1, > > caller=0x0) at dl-open.c:411 > > #6 0x0817cf4a in do_dlopen (ptr=0x1) at dl-libc.c:78 > > #7 0x0817c4a4 in _dl_catch_error (objname=0xbfa254d0, errstring=0xbfa254cc, > > operate=0x817cf30 , args=0xbfa254e0) at dl-error.c:152 > > #8 0x0817cf02 in dlerror_run (operate=0, args=0x40057930) at dl-libc.c:42 > > #9 0x0817cfd9 in __libc_dlopen ( > > __name=0x829eff0 ) at dl-libc.c:105 > > #10 0x0816e8ee in __nss_lookup_function (ni=0x821e798, > > fct_name=0x81cb6db "getservbyname_r") at nsswitch.c:340 > > #11 0x0816e4f0 in __nss_lookup (ni=0x0, fct_name=0x82194ec "", fctp=0xbfa254fc) > > at nsswitch.c:147 > > #12 0x0816fb04 in __getservbyname_r (name=0x81b2f3b "mysql", > > proto=0x81aa9d6 "tcp", resbuf=0x821a218, buffer=0x821e0e0 "", buflen=1024, > > result=0x829eff0) at ../nss/getXXbyYY_r.c:168 > > #13 0x0816f965 in getservbyname (name=0x81b2f3b "mysql", proto=0x81aa9d6 "tcp") > > at ../nss/getXXbyYY.c:109 > > #14 0x080d3e80 in mysql_server_init (argc=0, argv=0x0, groups=0x0) > > at libmysql.c:138 > > #15 0x080e65f4 in mysql_init (mysql=0x0) at client.c:1418 > > #16 0x08048534 in DBMonitor (this=0x821c300, > > ---Type to continue, or q to quit--- > > fileName=0xbfa258bd "monitor/parameters") at monitor/DBMonitor.cpp:10 > > > > Any help is greatly appreciated. > > > > Cheers, > > Sam > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > -- > - michael dykman > - [EMAIL PROTECTED] > > - All models are wrong. Some models are useful. > -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_init segmentation fault
Hi, I am running an application that interfaces with MySQL from c++. When I try to run the application, I get a segmentation fault at the line where I call mysql_init. Here is part of the output from gdb #0 0x081a0e78 in elf_machine_rel.0 () at ../sysdeps/i386/dl-machine.h:333 #1 0x081a113a in elf_dynamic_do_rel.4 () at do-rel.h:100 #2 0x081a12db in _dl_relocate_object (l=0x821ec40, scope=0x821ed84, lazy=1, consider_profiling=0) at dl-reloc.c:158 #3 0x081984ee in dl_open_worker (a=0xbfa25380) at dl-open.c:294 #4 0x0817c4a4 in _dl_catch_error (objname=0xbfa2537c, errstring=0xbfa25378, operate=0x8198040 , args=0xbfa25380) at dl-error.c:152 #5 0x08198606 in _dl_open (file=0xbfa254fc "libnss_files.so.2", mode=1, caller=0x0) at dl-open.c:411 #6 0x0817cf4a in do_dlopen (ptr=0x1) at dl-libc.c:78 #7 0x0817c4a4 in _dl_catch_error (objname=0xbfa254d0, errstring=0xbfa254cc, operate=0x817cf30 , args=0xbfa254e0) at dl-error.c:152 #8 0x0817cf02 in dlerror_run (operate=0, args=0x40057930) at dl-libc.c:42 #9 0x0817cfd9 in __libc_dlopen ( __name=0x829eff0 ) at dl-libc.c:105 #10 0x0816e8ee in __nss_lookup_function (ni=0x821e798, fct_name=0x81cb6db "getservbyname_r") at nsswitch.c:340 #11 0x0816e4f0 in __nss_lookup (ni=0x0, fct_name=0x82194ec "", fctp=0xbfa254fc) at nsswitch.c:147 #12 0x0816fb04 in __getservbyname_r (name=0x81b2f3b "mysql", proto=0x81aa9d6 "tcp", resbuf=0x821a218, buffer=0x821e0e0 "", buflen=1024, result=0x829eff0) at ../nss/getXXbyYY_r.c:168 #13 0x0816f965 in getservbyname (name=0x81b2f3b "mysql", proto=0x81aa9d6 "tcp") at ../nss/getXXbyYY.c:109 #14 0x080d3e80 in mysql_server_init (argc=0, argv=0x0, groups=0x0) at libmysql.c:138 #15 0x080e65f4 in mysql_init (mysql=0x0) at client.c:1418 #16 0x08048534 in DBMonitor (this=0x821c300, ---Type to continue, or q to quit--- fileName=0xbfa258bd "monitor/parameters") at monitor/DBMonitor.cpp:10 Any help is greatly appreciated. Cheers, Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Diff between restore from tar and recover from power outage
mos wrote: At 09:59 PM 6/4/2007, you wrote: If mysql has been shut down, and given enough time for the tables to be flushed to disk, then backing up the data dir using tar is fine. You can tar the data dir without shutting down, providing all transactions have been stopped and the tables have been flushed to disk. This is how we backup our servers, and we haven't had an issue yet (restoring daily to a testing environment). Scott, What if you stop the transactions and updates to the tables, and flush the cache to disk, BUT leave the tables open for reading? Can you then do a backup of the files during when only Select statements are used on them? Then when the backup is over, re-allow updates to the tables? I'm thinking of doing this for a webserver so I don't have to shut it down for reading, but just for updates for a few minutes while the backup is underway. I haven't seen you say what storage engine you are using. If you're using InnoDB, the answer is no. There are background threads that do things even when the database is not actively being updated. Peter Zaitsev wrote a good article on hot backups with a snapshot-capable filesystem like LVM. I personally think this is probably the best way to take hot backups at the moment. Another alternative is to backup from a slave, but you need to make sure the slave has the same data as the master. (shameless plug: mysql-table-checksum does this). Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Diff between restore from tar and recover from power outage
At 09:59 PM 6/4/2007, you wrote: If mysql has been shut down, and given enough time for the tables to be flushed to disk, then backing up the data dir using tar is fine. You can tar the data dir without shutting down, providing all transactions have been stopped and the tables have been flushed to disk. This is how we backup our servers, and we haven't had an issue yet (restoring daily to a testing environment). Scott, What if you stop the transactions and updates to the tables, and flush the cache to disk, BUT leave the tables open for reading? Can you then do a backup of the files during when only Select statements are used on them? Then when the backup is over, re-allow updates to the tables? I'm thinking of doing this for a webserver so I don't have to shut it down for reading, but just for updates for a few minutes while the backup is underway. TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
jdbc with a UDF
I'm getting what appears to be a java reference back from values I return from a UDF. The values appear find using php, and in the mysql client. The result looks something like: [EMAIL PROTECTED] The result should be a string (some representing floats, others as integers). Any help will be appreciated. I searched on google and only found where another person had the same problem, but no solution. Dave G. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock Tables Question
On 6/5/07, Baron Schwartz <[EMAIL PROTECTED]> wrote:
David T. Ashley wrote:
> There is no concept that I'm missing. I understand what a transaction
is.
> But I just don't want to bothered. My application is simple enough that
> bogarting the database until all necessary modifications have been made
and
> the tables are again consistent is good enough.
>
> Collisions are handled by serialization. Period. Somebody
wins. Everyone
> else waits. Works for me.
Then the simplest possible thing to do (besides using transactions, which
IMO would
actually be a LOT less bother!) is use GET_LOCK('database_name'). That
should handle
your requirement to make locks 'database-local.'
In my experience, using LOCK TABLES becomes a spaghetti problem that
begins to involve
more and more things until you are going through *serious* contortions. I
would avoid
it at all costs.
My only concern with GET_LOCK() is that lock is server-global rather than
database-global. This makes attacks possible in a shared setting (some bad
person could disable your database code by going after your lock).
My solution is just to lock all tables in one statement.
The only question I have (and nobody has answered this) is how many tables I
can include in a single LOCK TABLE statement. I thinking anything up to a
few thousand shouldn't be a problem. What is the limit?
Thanks, Dave.
Re: Lock Tables Question
David T. Ashley wrote:
There is no concept that I'm missing. I understand what a transaction is.
But I just don't want to bothered. My application is simple enough that
bogarting the database until all necessary modifications have been made and
the tables are again consistent is good enough.
Collisions are handled by serialization. Period. Somebody wins. Everyone
else waits. Works for me.
Then the simplest possible thing to do (besides using transactions, which IMO would
actually be a LOT less bother!) is use GET_LOCK('database_name'). That should handle
your requirement to make locks 'database-local.'
In my experience, using LOCK TABLES becomes a spaghetti problem that begins to involve
more and more things until you are going through *serious* contortions. I would avoid
it at all costs.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock Tables Question
On 6/5/07, Brent Baisley <[EMAIL PROTECTED]> wrote: I think you're missing the concept of a transaction in the database sense. The idea behind a transaction is that you can perform multiple steps and if you don't complete all steps, any changes are reversed. The reversal process is handled by the database. A good example is moving money from bank account A to B. You specify how much to move and that amount is debited from account A and credited to account B, 2 steps. If the first step happens, but not the second, then the first step needs to be reversed. Until the transaction is complete, anything querying the data needs to see bank account in it's state before any transaction started, a type of versioning. You seem to be trying implement all this manually, which you would need to do if you are using MyISAM based tables. But you may not need to use transactions at all if your data does not have real time dependencies. I knew somebody was going to say this. Here is the relevant prose from my original post. One more note: I'm sure that many of the skilled users on this list will be tempted to advocate more sophisticated methods. I appreciate all advice, but I'm just looking for an easy way to serialize access to my database and guarantee mutual exclusion. Each operation I want to do would take at most half a second, so another web process waiting that long won't make a difference. Simpler is easier for me. There is no concept that I'm missing. I understand what a transaction is. But I just don't want to bothered. My application is simple enough that bogarting the database until all necessary modifications have been made and the tables are again consistent is good enough. Collisions are handled by serialization. Period. Somebody wins. Everyone else waits. Works for me.
Re: Lock Tables Question
I think you're missing the concept of a transaction in the database sense. The idea behind a transaction is that you can perform multiple steps and if you don't complete all steps, any changes are reversed. The reversal process is handled by the database. A good example is moving money from bank account A to B. You specify how much to move and that amount is debited from account A and credited to account B, 2 steps. If the first step happens, but not the second, then the first step needs to be reversed. Until the transaction is complete, anything querying the data needs to see bank account in it's state before any transaction started, a type of versioning. You seem to be trying implement all this manually, which you would need to do if you are using MyISAM based tables. But you may not need to use transactions at all if your data does not have real time dependencies. On 6/4/07, David T. Ashley <[EMAIL PROTECTED]> wrote: I decided to go with a simple paradigm for my web-based database. Rather than transactions, each process locks the entire database while it is changing something, then unlocks it. This just serializes access (all other processes will block until the one modifying the database has finished). The method I was using is something like: LOCK TABLE thistable, thattable, theothertable, goshthislistcangetlongtable; Do whatever is needed; UNLOCK TABLES; I probably botched the syntax above. Rather than enumerate every table in the database (which could get to be a long list), I wondered if it is possible to just lock one table, with the gentleman's agreement that in order to modify the database, every process must first lock that particular table. For example: #1)LOCK TABLE x; #2)Make modifications to tables x, y, and z; #3)UNLOCK TABLES; Are there any race conditions in just using one table for this purpose? For example, SQL guarantees that a given SQL statement is atomic. But it is guaranteed that #2 will complete before #3 above? If every process uses the same rule, can anything unexpected happen? One more note: I'm sure that many of the skilled users on this list will be tempted to advocate more sophisticated methods. I appreciate all advice, but I'm just looking for an easy way to serialize access to my database and guarantee mutual exclusion. Each operation I want to do would take at most half a second, so another web process waiting that long won't make a difference. Simpler is easier for me. Thanks.
Re: Data migration Verification
Hi !
paulizaz wrote:
What do you mean by "same output" ?
I have too much data to go through and check if all the data is the same.
This is my problem. Sampling would speed this up, but I need something more
accurate.
All data is important.
In Unix / Linux, you would generate similar plain-text output before and
after the migration ("select *" to file, any report, ...) and then
compare them, typically using "diff".
If the format differs on intention (more/fewer columns, sequence of
columns, ...), you would include "sed" or some other filter to align the
two.
As a last resort, you would write some small script (Perl, shell, ...)
to compare them.
For me, it is a very common task to take the output of two different
versions of something (a build log, a trace from a run, the log of a
backup run, ...) and compare them via such a tool chain.
As you mentioned C#, I take it you are on Windows - pick your equivalent
available.
HTH,
Joerg
--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
