Re: BDB transaction isolation levels?
On Mon, Sep 08, 2003 at 03:26:34PM -0700, Bill Todd wrote: > What transaction isolation level(s) does BDB support? I can't find any > information on BDB transactions section 7.6 of the manual. BDB only does READ COMMITTED. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 1 days, processed 43,048,848 queries (401/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BDB or InnoDB
Thanks Edward, Thank you that confirms what I found. I also found that BDB is not available on Mac and Linux (alpha) architecture. Best regards Nils Valentin Tokyo/Japan 2003年 6月 12日 木曜日 16:02、Becoming Digital さんは書きました: > According to Sams Publishing (April 2002): > The Berkeley DB table type is a usable, transaction-safe table type, > but it is not the most optimized table type in the mix. BDB tables support > the basic elements of transactions as well as the AUTOCOMMIT variable, but > are not as popular or as developed as the InnoDB or Gemini table types. > > Edward Dudlik > Becoming Digital > www.becomingdigital.com > > > - Original Message - > From: "Nils Valentin" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Thursday, 12 June, 2003 02:19 > Subject: BDB or InnoDB > > > Hi MySQL Fans, > > I am sure this question was asked before, but I did not find sufficient > info, so please allow me to ask this question again. > > When comparing BDB and InnoDB which one would be (generally speaking) a > better choice for a certain purpose ? > > I understood that BDB and InnoDB have basically similar features. > > Except BDB uses-page-level locking and InnoDB uses row-level-locking. > > There are some things which I am not so sure about. > > Does BDB support foreign keys ? > > Any response much appreciated. I searched through the archive back to June > 2002 and in the info manual , but unfortunately I did not find what I was > looking for. > > Did I miss something ? > > > -- > --- > Valentin Nils > Internet Technology > > E-Mail: [EMAIL PROTECTED] > URL: http://www.knowd.co.jp > Personal URL: http://www.knowd.co.jp/staff/nils > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BDB or InnoDB
According to Sams Publishing (April 2002): The Berkeley DB table type is a usable, transaction-safe table type, but it is not the most optimized table type in the mix. BDB tables support the basic elements of transactions as well as the AUTOCOMMIT variable, but are not as popular or as developed as the InnoDB or Gemini table types. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: "Nils Valentin" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, 12 June, 2003 02:19 Subject: BDB or InnoDB Hi MySQL Fans, I am sure this question was asked before, but I did not find sufficient info, so please allow me to ask this question again. When comparing BDB and InnoDB which one would be (generally speaking) a better choice for a certain purpose ? I understood that BDB and InnoDB have basically similar features. Except BDB uses-page-level locking and InnoDB uses row-level-locking. There are some things which I am not so sure about. Does BDB support foreign keys ? Any response much appreciated. I searched through the archive back to June 2002 and in the info manual , but unfortunately I did not find what I was looking for. Did I miss something ? -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- 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: BDB wrapped transaction id's
On Thu, May 09, 2002 at 09:31:06AM +0100, Robert Cross wrote: > > BTW, a big "thank you" to whomever wrote "mysqlbinlog" as it saved the day! > (my database was well and truely broken) That's Sasha. He wrote most (all?) of the replication code in MySQL. -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.51: up 0 days, processed 18,792,940 queries (295/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: bdb table data deleted
Hi! We shall investigate your test case as soon as we find some time. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: bdb table crashes?
Kalok Lo writes: > I've had many problems with BDB table crashes. > Up until now, I haven't really cared, running safe_mysqld, the database just > restarts, and things proceed as normal. > > But recently I've had some strange behaviour, where table data is deleted > after an "alter table" statement. > Upon looking through the error logs, I found many cases much like yours, but > NOT due to delete queries, mine were just select queries. > I don't know if the 2 problems are related at all, "the crashes, and the > data deletion" Hi! Please try a latest MySQL-Max binary from our site and see if the problem reccurs.> -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: bdb table crashes?
I've had many problems with BDB table crashes. Up until now, I haven't really cared, running safe_mysqld, the database just restarts, and things proceed as normal. But recently I've had some strange behaviour, where table data is deleted after an "alter table" statement. Upon looking through the error logs, I found many cases much like yours, but NOT due to delete queries, mine were just select queries. I don't know if the 2 problems are related at all, "the crashes, and the data deletion" I'm runing MYSQL server version: 3.23.36 Here is 1 sample of what I'm getting. -- mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died. Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Stack range sanity check OK, backtrace follows: 0x4007d0fe 0x810afb0 0x81554ca 0x819bc49 0x819108a 0x818e9ea 0x81699d6 0x81659f0 0x8109119 0x80e2776 0x80e252b 0x80d9bcf 0x80c0cd3 0x80c3d8b 0x80c008f 0x80bf599 Stack trace successful, trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x82a9338 = Select l.name l_name , s.name s_name, description, shopa_notes, not_allowed, year from list l, school s, grade g where list_id = 50 and s.school_id=l.school_id and g.grade_id=l.grade_id thd->thread_id = 10283 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 10283 did to cause the crash. In some cases of really bad corruption, this value may be invalid Please use the information above to create a repeatable test case for the crash, and send it to [EMAIL PROTECTED] Number of processes running now: 0 011217 13:17:06 mysqld restarted - Original Message - From: "Geoffrey Soh" <[EMAIL PROTECTED]> To: "Mysql@Lists. Mysql. Com" <[EMAIL PROTECTED]> Sent: Monday, January 21, 2002 10:27 PM Subject: bdb table crashes? > Hi, > > Having some problems with a particular BDB table, which crashes once in a > while. Just wondering if anyone has experienced such BDB table crashes? > The relevant info is included below for troubleshooting. Let me know if I > need to send anything else. Appreciate any help. Thanks! > > >Description: > BDB table crashes on delete query, such delete queries execute flawlessly > but once in a while, the table crashes on a delete, and causes a restart of > MySQL > > >How-To-Repeat: > Not very repeatable, happens intermittently and at random. > > >Fix: > No fix yet. > > >Submitter-Id: > >Originator: > >Organization: Ufinity Pte Ltd > >MySQL support: none > >Synopsis: bdb table crashes > >Severity: serious > >Priority: medium > >Category: mysql > >Class: sw-bug > >Release: mysql-3.23.42 (Source distribution) > > >Environment: > > System: Linux axle 2.4.2-2 #1 Sun Apr 8 20:41:30 EDT 2001 i686 unknown > Architecture: i686 > > Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc > /usr/ > bin/cc > GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs > gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-81) > Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' > LIBC: > lrwxrwxrwx1 root root 13 Sep 12 02:51 /lib/libc.so.6 -> > libc-2 > .2.2.so > -rwxr-xr-x2 root root 1236396 Apr 7 2001 /lib/libc-2.2.2.so > -rw-r--r--1 root root 26350254 Apr 7 2001 /usr/lib/libc.a > -rw-r--r--1 root root 178 Apr 7 2001 /usr/lib/libc.so > Configure command: > ./configure --prefix=/home/mysql/mysql-lite --localstatedir= > /home/mysql/data/mysql-lite --with-berkeley-db --with-raid --enable-assemble > r --with-mysqld-ldflags=-all-static > > Stack Trace : > > 0x806c666 handle_segfault__Fi + 258 > 0x818d3ee btr_cur_pessimistic_update + 386 > 0x80b4c6e delete_row__12ha_myisammrgPCc + 14 > 0x80feb28 txn_abort + 184 > 0x81429c2 __bam_repl_recover + 566 > 0x8138859 __bam_pgout + 41 > 0x8135204 __qam_c_get + 1008 > 0x8111a66 __db_rename + 926 > 0x80b6b65 update_row__11ha_berkeleyPCcPc + 1153 > 0x80b6d5a update_row__11ha_berkeleyPCcPc + 1654 > 0x809b2e1 generate_table__FP3THDP13st_table_listP8st_table + 673 > 0x8074115 mysql_execute_command__Fv + 5705 > 0x8075a73 my_yyoverflow__FPPsPP7YYSTYPEPi + 31 > 0x8072082 do_command__FP3THD + 862 > 0x80715a4 handle_one_connection__FPv + 180 > > Schema from table : > > mysql> desc user_status; > +---+-+--+-+-+---+ > | Field | Type| Null | Key | Default | Extra | > +---+-+--+-+-+---+ > | userid| varchar(255)| | PRI | | | > | domain| varchar(150)| | PRI | | | > | password |
Re: BDB table handler crashing during select
On Tuesday 04 December 2001 12:00 pm, [EMAIL PROTECTED] wrote: > The concept of a transaction is important to my app because the number of > queries and the number of tables > to be modified during a particular transaction is not easy to calculate. It is > obtained from the result of > the queries to the myISAM tables. If for some reason, one query cannot be > fulfilled, all queries of the transaction > are undone by calling ROLLBACK. The transaction model makes my app much > simlper and more flexible. For transaction support, the recommended table handler is InnoDB. For BDB, though, we will fix all bug for which we have a test case. Please send a full test case including your tables to [EMAIL PROTECTED] If your tables are reasonably large, upload them to ftp://support.mysql.com/pub/mysql/secret. We do, however, recommend that you switch to InnoDB since there is currently no active development on the BDB handler. -- MySQL Development Team For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA <___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB recovery and replication?
On Thu, Oct 25, 2001 at 04:22:33PM +0800, Geoffrey Soh wrote: > Hi All, > > I'm new to this list and have some questions about BDB tables and > replication. Hope some of the more enlightened ones on this list > might shed some light on the following : > > 1. I've a master-slave replication setup with BDB tables. If for some > reason the BDB tables on the slave go out of sync (e.g. failover > master->slave, then failback slave->master), what's the best way to sync > these tables and restart the replication on the slave? Have a look at mysqlsnapshot: http://jeremy.zawodny.com/mysql/ It's designed for MyISAM files, but it could be modified for BDB pretty easily (I'd love the patch). > 2. How would I be able to get a decent snapshot of the BDB (or other MyISAM) > tables in (1) without stopping the master (the master is running a live 24x7 > service hence cannot stop the master without affecting uptime)? Same answer. :-) > 3. Related to (1), what's the best way to do recovery on BDB tables using > the MySQL binary logs + BDB table snapshot + BDB logs? I've read the manual > but could not find such info :) Look at the mysqlbinlog command. It turns a binary log into something you can pipe back thru the mysql command-line tool. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB table : different results on adjacent queries
Try running "ANALYZE TABLE x;" and redoing the query. If this fixes your query, there is some index corruption going on. Are there any other queries going through the system during these tests, or is it only your 2 threads? There were some BDB index bugs fixed in the last few mysql releases, but 3.23.41 should be fine. Im using .42 and having no more problems. dpk - Original Message - From: <[EMAIL PROTECTED]> To: "'Dana Powers'" <[EMAIL PROTECTED]> Cc: "mysql mailing list (E-Mail)" <[EMAIL PROTECTED]> Sent: Thursday, September 20, 2001 7:23 AM Subject: RE: BDB table : different results on adjacent queries > Hi Dana > > > -Original Message- > > From: Dana Powers [mailto:[EMAIL PROTECTED]] > > Sent: Wednesday, September 19, 2001 7:19 PM > > To: [EMAIL PROTECTED] > > Cc: mysql mailing list (E-Mail) > > Subject: Re: BDB table : different results on adjacent queries > > > > > > What is the query you are running + what version of mysql are > > you using? > > dpk > > It was a simple 'select * from where a= and b='. > Version: 3.23.41, compiled with BDB and INNODB support, > running on Linux (SuSE 7.0). > > The table we used had the following structure: > a varchar(36) not null, pri > b varchar(50) null > c varchar(4) null > d varchar(50) null > e decimal (10,0) null > f varchar(36) null, mul > g varchar(36) null, mul > h varchar(50) not null > i varchar(50) null > > there are 3 non_unique keys defined: > f+b > f+d > g > > column f is filled with all the same values (262 records) > > the last test was performed with the command line tool mysql. > query: > select count(*) from where f=''; > > count(*) = 262 -> ok. > > (the value in column f is a GUID which uses the full > length of the field.) > > I repeated the query several times, with always the same > positive results. > > Then I opened another connection (as a different user), > using the same database, applied my query, > and ... OOPS ... count(*) = 0 ! > > I turned back to the other client and again performed the > same query (using the commandline buffer). > Now the result of the count was 0 also there !!! > > Next I tried another application: mysql_navigator (1.2.4), > the same query, count was 262, ok. > next try: added " and b = '4004'" to the where clause so that > the count should be 1 as there is only one record with '4004' > in column 'b'. > result: count was 0 ! > another try on the previous select : count is 2 instead of 262 ! > > so what ??? > > replaced 'count(*)' by '*': > one of the resulting records is the one with '4004' in column 'b', > the other one's 'b' value is 400403. > There is another record in the table beginning with 4004 in > column 'b' : value '40040307', but this one wasn't selected. > > I hope you have an idea about this very weird behaviour. > > Thanx in advance > Andy > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: BDB table : different results on adjacent queries
Hi Dana > -Original Message- > From: Dana Powers [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, September 19, 2001 7:19 PM > To: [EMAIL PROTECTED] > Cc: mysql mailing list (E-Mail) > Subject: Re: BDB table : different results on adjacent queries > > > What is the query you are running + what version of mysql are > you using? > dpk It was a simple 'select * from where a= and b='. Version: 3.23.41, compiled with BDB and INNODB support, running on Linux (SuSE 7.0). The table we used had the following structure: a varchar(36) not null, pri b varchar(50) null c varchar(4) null d varchar(50) null e decimal (10,0) null f varchar(36) null, mul g varchar(36) null, mul h varchar(50) not null i varchar(50) null there are 3 non_unique keys defined: f+b f+d g column f is filled with all the same values (262 records) the last test was performed with the command line tool mysql. query: select count(*) from where f=''; count(*) = 262 -> ok. (the value in column f is a GUID which uses the full length of the field.) I repeated the query several times, with always the same positive results. Then I opened another connection (as a different user), using the same database, applied my query, and ... OOPS ... count(*) = 0 ! I turned back to the other client and again performed the same query (using the commandline buffer). Now the result of the count was 0 also there !!! Next I tried another application: mysql_navigator (1.2.4), the same query, count was 262, ok. next try: added " and b = '4004'" to the where clause so that the count should be 1 as there is only one record with '4004' in column 'b'. result: count was 0 ! another try on the previous select : count is 2 instead of 262 ! so what ??? replaced 'count(*)' by '*': one of the resulting records is the one with '4004' in column 'b', the other one's 'b' value is 400403. There is another record in the table beginning with 4004 in column 'b' : value '40040307', but this one wasn't selected. I hope you have an idea about this very weird behaviour. Thanx in advance Andy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB table : different results on adjacent queries
What is the query you are running + what version of mysql are you using? dpk - Original Message - From: <[EMAIL PROTECTED]> To: "mysql mailing list (E-Mail)" <[EMAIL PROTECTED]> Sent: Wednesday, September 19, 2001 2:12 AM Subject: BDB table : different results on adjacent queries > hi all > > we encountered the following: > - altered table type to 'BDB' > - did 2 identical queries > - got different result sets ! > > after restart of MySQL the very next query > gave the correct results, the next did NOT. > > any ideas? > > Thanx in advance > Andreas Schoelver > -- > Andreas Schoelver (AS) > mailto: [EMAIL PROTECTED] > mailto: [EMAIL PROTECTED] > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB table error
Hi!
> "Dana" == Dana Powers <[EMAIL PROTECTED]> writes:
Dana> Thanks for the reply! I can understand the situation with muti statement
Dana> transactions, but I'm not using transactions ( at least from my
Dana> perspective ); Im only issuing single 'atomic' statements. It would make
Dana> much more sense if mysql internally detected the deadlock, released the
Dana> locks and tried to process the statement again. It just seems so
Dana> counter-intuitive that i can connect, issue a single, isolated select
Dana> statement and get a deadlock. I dont know if this is possible, but I would
Dana> love something like this in the mysql engine:
Dana> retry-deadlock:
Dana> error = do_query();
Dana> if ( ( auto-commit == on ) && ( error == DEADLOCK ) && ( ++tries <
Dana> threshold ) ) {
Dana> warn('Deadlock detected. Retrying query.');
Dana> goto retry-deadlock;
Dana> }
Dana> Of course this could go into the application code, but it would have to go
Dana> around every single query, transactional or not, and that seems so
Dana> cumbersome given that the solution is the same in every case.
Dana> dpk
Currenty the only way we can affect how locks works with BDB tables is
to use the --bdb-lock option.
This is passed to BDB with db_env->set_lk_detect().
We have actually a variable to do retry of locks:
In ha_berkeley.cc, line 82:
ulong berkeley_trans_retry=1;
Could you try to change the above to 10 and see if this helps.
This retry count is only used for insert/delete/update but it may
still help solve some of your problems (If it does, we will make this
variable available from the command line).
One problem in doing the lock detection in the server is that the you
may get a lock after you have already sent some data to the client.
It would on the other hand not that hard to do retries on the client
side if something goes wrong. I shall put this on our TODO.
Regards,
Monty
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB table error
Thanks for the reply! I can understand the situation with muti statement
transactions, but I'm not using transactions ( at least from my
perspective ); Im only issuing single 'atomic' statements. It would make
much more sense if mysql internally detected the deadlock, released the
locks and tried to process the statement again. It just seems so
counter-intuitive that i can connect, issue a single, isolated select
statement and get a deadlock. I dont know if this is possible, but I would
love something like this in the mysql engine:
retry-deadlock:
error = do_query();
if ( ( auto-commit == on ) && ( error == DEADLOCK ) && ( ++tries <
threshold ) ) {
warn('Deadlock detected. Retrying query.');
goto retry-deadlock;
}
Of course this could go into the application code, but it would have to go
around every single query, transactional or not, and that seems so
cumbersome given that the solution is the same in every case.
dpk
- Original Message -
From: "Michael Widenius" <[EMAIL PROTECTED]>
To: "Dana Powers" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Monday, September 17, 2001 2:19 PM
Subject: Re: BDB table error
>
> Hi!
>
> >>>>> "Dana" == Dana Powers <[EMAIL PROTECTED]> writes:
>
> Dana> Certainly I understand how the deadlock can occur, but the question
is why
> Dana> is this acceptable behaviour? Is the application developer expected
to
> Dana> handle this case? If so, how? and shouldnt this be included in the
manual
> Dana> under BDB problems? If Im supposed to wrap _every_ sql call I make
to a BDB
> Dana> table with something like: 'do { query } while ( !deadlock );' why
would I
> Dana> even bother trying to use BDB tables? This seems to me like a
_fundamental_
> Dana> weakness of the BDB table type, and should at least be documented as
such...
>
> We shall will add som more documentation about this ASAP.
>
> In MySQL 3.23.43 you will get the following error:
>
> Error: 1213 (ER_LOCK_DEADLOCK)
> "Deadlock found when trying to get lock; Try restarting transaction",
>
> Anyway, the problem you are experiencing is something that is normal
> with all transactional databases. When you have many clients that is
> reading/updating the same table there is always cases when you will
> get deadlocks or other failure in the transaction and you have to redo
> the whole transaction in the application.
>
> Regards,
> Monty
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail [EMAIL PROTECTED]
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
>
>
>
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB table error
Hi!
> "Dana" == Dana Powers <[EMAIL PROTECTED]> writes:
Dana> Certainly I understand how the deadlock can occur, but the question is why
Dana> is this acceptable behaviour? Is the application developer expected to
Dana> handle this case? If so, how? and shouldnt this be included in the manual
Dana> under BDB problems? If Im supposed to wrap _every_ sql call I make to a BDB
Dana> table with something like: 'do { query } while ( !deadlock );' why would I
Dana> even bother trying to use BDB tables? This seems to me like a _fundamental_
Dana> weakness of the BDB table type, and should at least be documented as such...
We shall will add som more documentation about this ASAP.
In MySQL 3.23.43 you will get the following error:
Error: 1213 (ER_LOCK_DEADLOCK)
"Deadlock found when trying to get lock; Try restarting transaction",
Anyway, the problem you are experiencing is something that is normal
with all transactional databases. When you have many clients that is
reading/updating the same table there is always cases when you will
get deadlocks or other failure in the transaction and you have to redo
the whole transaction in the application.
Regards,
Monty
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB table error
Certainly I understand how the deadlock can occur, but the question is why
is this acceptable behaviour? Is the application developer expected to
handle this case? If so, how? and shouldnt this be included in the manual
under BDB problems? If Im supposed to wrap _every_ sql call I make to a BDB
table with something like: 'do { query } while ( !deadlock );' why would I
even bother trying to use BDB tables? This seems to me like a _fundamental_
weakness of the BDB table type, and should at least be documented as such...
dpk
- Original Message -
From: "Heikki Tuuri" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Sunday, September 16, 2001 2:14 AM
Subject: Re: BDB table error
> Dana,
>
> if you have page level locking, you can easily
> get a deadlock from an atomic insert done as
> a single transaction, and another user doing
> an atomic select in another single transaction.
>
> It happens this way:
>
> User A User B
>
> insert recordselect record from secondary
> to primary index index, get S-lock on page 2
> get X-lock on page 1
>
> select record from primary index
> get X-lock on page 1 -> MUST WAIT
> insert record to
> secondary index
> get X-lock on page 2 ->MUST WAIT -> DEADLOCK
>
>
> Regards,
>
> Heikki
> http://www.innodb.com
>
> >Ok, so the problem Im seeing is that any table that has an index other
than
> >the primary key is prone to deadlocks.
> >I cant think of any reason that this is acceptable behaviour ( i.e. not a
> >bug ), because I am doing 4 atomic operations on a single table with no
user
> >level locks.Here is a test:
> >CREATE TABLE bdb_test ( id int unsigned not null auto_increment primary
key,
> >key1 char(32) not null, val1 char(32), unique (key1) ) TYPE=BDB;
> >( replacing unique(key1) with index(key1) also results in deadlocks ).
> >Now run the following 4 queries in parallel:
> >## Insert a new row with 2 key values - 1 auto_incremented and 1
specified
> >INSERT INTO bdb_test (key1) VALUES(CONCAT(CONNECTION_ID(),' - some
> extratext'));
> >## Update a non key column in row
> >UPDATE bdb_test SET val1='test val' WHERE id=LAST_INSERT_ID();
> >## Select based on secondary key
> >SELECT * FROM bdb_test WHERE key1=CONCAT(CONNECTION_ID(),' - some
extratext');
> >## Select based on primary keySELECT * FROM bdb_test WHERE
id=LAST_INSERT_ID();
> >I get deadlocks on my machine ( Intel w/ Dual 400 + 512MB RAM, running
> >linux ) with 64 simultaneous threads.
> >It appears that as the table gets larger ( and more pages created for
> >locking, hehe ), the probability that we get a deadlock grows.
>
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail [EMAIL PROTECTED]
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
>
>
>
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB table error
Dana, if you have page level locking, you can easily get a deadlock from an atomic insert done as a single transaction, and another user doing an atomic select in another single transaction. It happens this way: User A User B insert recordselect record from secondary to primary index index, get S-lock on page 2 get X-lock on page 1 select record from primary index get X-lock on page 1 -> MUST WAIT insert record to secondary index get X-lock on page 2 ->MUST WAIT -> DEADLOCK Regards, Heikki http://www.innodb.com >Ok, so the problem Im seeing is that any table that has an index other than >the primary key is prone to deadlocks. >I cant think of any reason that this is acceptable behaviour ( i.e. not a >bug ), because I am doing 4 atomic operations on a single table with no user >level locks.Here is a test: >CREATE TABLE bdb_test ( id int unsigned not null auto_increment primary key, >key1 char(32) not null, val1 char(32), unique (key1) ) TYPE=BDB; >( replacing unique(key1) with index(key1) also results in deadlocks ). >Now run the following 4 queries in parallel: >## Insert a new row with 2 key values - 1 auto_incremented and 1 specified >INSERT INTO bdb_test (key1) VALUES(CONCAT(CONNECTION_ID(),' - some extratext')); >## Update a non key column in row >UPDATE bdb_test SET val1='test val' WHERE id=LAST_INSERT_ID(); >## Select based on secondary key >SELECT * FROM bdb_test WHERE key1=CONCAT(CONNECTION_ID(),' - some extratext'); >## Select based on primary keySELECT * FROM bdb_test WHERE id=LAST_INSERT_ID(); >I get deadlocks on my machine ( Intel w/ Dual 400 + 512MB RAM, running >linux ) with 64 simultaneous threads. >It appears that as the table gets larger ( and more pages created for >locking, hehe ), the probability that we get a deadlock grows. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB table error
I can totally understand that, but the problem is, my queries are only interested in one row each ( accessed by primary key ). Perhaps it has to do with the extra unique index? The flow is this: insert row. grab newly created primary key ( autoincrement ) + generate a unique 32 byte char string based on the primary key. update row to set unique char string. commit | rollback Could it be that bdb needs to grab a page lock on the index as well? hmm, that might explain it. dpk - Original Message - From: "Christian Sage" <[EMAIL PROTECTED]> To: "Dana Powers" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Saturday, September 15, 2001 3:08 PM Subject: AW: BDB table error Dana, a deadlock can easily occur on a single table with row-level (or page-level) locking. What happens is something like the following: connectionhas lock on wants lock on alpha object Aobject B beta object Bobject A With page-level locking this would obviously be possible to happen only if the objects resided in different pages. Anyway, this type of situation cannot be resolved by the connections on their own, because they both see only their own context and end up sitting there indefinitely waiting for the object they want to be freed. Therefore, it must be handled by either the application code or the rdbms itself. Some of the other database systems I know detect this situation on their own. Oracle, for example, will roll back one of the contending connections and write a trace file plus an entry in its alert log (for an ORA-00060 error). Sadly, I don't know anything about BDB, so I can't really help you. Generally speaking I've not yet met a situation where it was necessary to sequentially lock several objects on the same table, though. I may be wrong, but as far as I can see this would seem to point at either loose design (not fully normalized - if the data is normalized you simply go and lock the (single) parent object, then all child objects of this parent are implicitly locked if all connections behave in the same way) or at sub-optimal coding (atomicity of operations should have been preserved). No offense intended, as I say, I may be totally off the beam here. Cheers, Christian Sage - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB table error
I've found the answer to my problem in the bdb source... >From bdb/include/db.src: #defineDB_LOCK_DEADLOCK(-30996)/* Deadlock. */ So the question is: how can a bunch of simultaneous connections that only work on 1 table create a deadlock. And how can I write my code to avoid this problem? Is this an intrinsic problem with BDB, a problem with the way mysql handles BDB, or a problem with my application code? Additionaly, it would be nice if either the mysql engine returned a string-ified version of the error code or if perror could handle these error codes. dpk - Original Message - From: "Dana Powers" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Saturday, September 15, 2001 1:52 PM Subject: BDB table error > Im getting this error from attempted inserts into a BDB table using > mysql-3.23.42 ( same error but more often in 3.23.38 ): > > Could not insert new row into SESSION_DATA: Got error -30996 from table > handler > > First - I have not been able to find what this error means in either the > source code, online, or with tools like perror ( doesnt like negative > numbers ). Does anyone have an idea what this is, or how to find out? > > Info on the situation: > I've got X simultaneous connections inserting and updating rows, and this > error consistently pops up if X > 16. If X = 32, more than half of the > inserts are errors. > > Here is the table: > > CREATE TABLE SESSION_DATA ( > SESSION_ID int(10) unsigned NOT NULL auto_increment, > SESSION_KEY varchar(32) default NULL, > TS_ACCESS timestamp(14) NOT NULL, > DATA text NOT NULL, > PRIMARY KEY (SESSION_ID), > UNIQUE KEY SESSION_DATA___SESSION_KEY (SESSION_KEY) > ) TYPE=BerkeleyDB; > > Here are the sql statements used: > > ## Create a Session > SET AUTOCOMMIT=0; > INSERT INTO SESSION_DATA (SESSION_KEY,TS_ACCESS,DATA) VALUES (NULL,NULL,''); > UPDATE SESSION_DATA SET SESSION_KEY='32bytesofblah',TS_ACCESS=NULL,DATA='' > WHERE SESSION_ID=###; ( ### was set to LAST_INSERT_ID() between sql > statements ) > COMMIT; > SET AUTOCOMMIT=1; > > ## Update a Session ( AUTOCOMMIT=1 ) > UPDATE SESSION_DATA SET SESSION_KEY='32bytes ofblah',TS_ACCESS=NOW(),DATA='a > load of text...' WHERE SESSION_ID=###; > > thanks for any info, > dpk > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail [EMAIL PROTECTED] > To unsubscribe, e-mail <[EMAIL PROTECTED]> > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB Tables, InnoDB tables ??
At 9:55 PM +0530 9/9/01, Nilesh Parmar wrote: > Hi > I just came across these words in the mailing lists. > Can anyone tell me what are "BDB tables" and "InnoDB tables" . >I am new to mysql and would like to know what are they . >thanx in advance >Nilesh (1) Go to http://www.mysql.com/doc/ (2) Type innodb or bdb into the search box OR (1) Go to http://www.mysql.com/doc/ (2) Use your browser's search function to search the page for innodb or bdb -steve -- + Open source questions? + | Steve Edberg University of California, Davis | | [EMAIL PROTECTED] Computer Consultant | | http://aesric.ucdavis.edu/ http://pgfsun.ucdavis.edu/ | +--- http://pgfsun.ucdavis.edu/open-source-tools.html ---+ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB Tables, InnoDB tables ??
Hi I just came across these words in the mailing lists. Can anyone tell me what are "BDB tables" and "InnoDB tables" . I am new to mysql and would like to know what are they . thanx in advance Nilesh - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB questions
On Tue, Aug 28, 2001 at 11:39:54AM -0700, Sheena Sidhu wrote: > > I had 2 questions related to using BDB and mysql. I don't see any > startup options for BDB, so can I safely assume that there are'nt > any , so don't need a my.cnf file? There are several. Look in the docs here: http://www.mysql.com/doc/S/H/SHOW_VARIABLES.html Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 3 days, processed 33,423,084 queries (110/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB or InnoDB ?
On Sat, Sep 01, 2001 at 11:42:52AM -0400, Jocelyn Bernier wrote: > Hi everybody, > > I am currently looking the transactional part of MySQL and I'm > wandering which one is best to use in regard to efficiency and > reliability. Which one is the most stable, etc? While I have some of experience with InnoDB and very little experience with BDB tables, here's my take on the situation... BDB tables were/are a nice stepping stone for MySQL. They were the first transactional table type. People who needed transactions (or more fine-grained locking) use BDB, but a lot of folks were still waiting for something like the more traditional systems (Oracle, Sybase, SQL Server, etc). BDB tables do have a few odd qualities. InnoDB performs very well but is newer. It seems to be quite stable if you're not using BLOBs. However, I expect InnoDB to easily surpass BDB in popularity, developer support, and features. For all I know, it already has. I see InnoDB as being one of the more viable MySQL table types for the long-term. You can probably build your application on BDB tables today and be just fine. You can probably build your application on InnoDB today and be just fine if you take backups and keep up to date with MySQL as any InnoDB are fixed. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 3 days, processed 33,371,656 queries (110/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB and InnoDB in windows nt versions
Bruce, you are right. There is only a small note about the 10M limit in the options list in the manual. I will add a note also right after Win option examples. Regards, Heikki http://www.innodb.com ... Thanks guys. Just one thing, why isn't this somewhere in the docs? A small note near the beginning of section 7.6.2 InnoDB startup options would seem to be an ideal place.cheers,Bruce At 03:30 PM 8/17/2001 +0300, you wrote: >Bruce, > >the tablespace must be >= 10 MB. You have only >specified 5M. > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB and InnoDB in windows nt versions
Thanks guys. Just one thing, why isn't this somewhere in the docs? A small note near the beginning of section 7.6.2 InnoDB startup options would seem to be an ideal place. cheers, Bruce At 03:30 PM 8/17/2001 +0300, you wrote: >Bruce, > >the tablespace must be >= 10 MB. You have only >specified 5M. > >I tested with .41 and got the following error >message: > >C:\trybuild\client_debug>mysqld --console >InnoDB: Error: tablespace size must be at least 10 MB >010817 15:28:15 Can't init databases > >Regards, > >Heikki > >Copied message: > >Thanks for the input Heikki, > >I got it the right version mysql-max-nt going now. The problem was that >winmysqladmin automatically started the wrong service- mysqld - so I had to >uninstall that service before it would recognise any changed I made in the >my.ini file done with winmysqladmin. >Now I can use bdb, and show variables gives InnoDB as disabled. >I've tried setting up the InnoDB variables in winmysqladmin like this, but >the server won't start: >[mysqld]basedir=C:/mysql >#bind-address=139.133.200.222 >datadir=C:/mysql/data >language=C:/mysql/share/english >#slow query log#= >#tmpdir#= >#port=3306 >#set-variable=key_buffer=16M ># startup parameters for InnoDB tables >innodb_data_file_path = ibdata1:5M >innodb_data_home_dir = C:\mysql\data\bruce\ibdata >set-variable = innodb_mirrored_log_groups=1 >innodb_log_group_home_dir = C:\mysql\data\bruce\iblogs >set-variable = innodb_log_files_in_group=3set-variable = >innodb_log_file_size=5M >set-variable = innodb_log_buffer_size=5Minnodb_flush_log_at_trx_commit=1 >innodb_log_arch_dir = c:\mysql\data\bruce\iblogsinnodb_log_archive=0 >set-variable = innodb_buffer_pool_size=5M >set-variable = innodb_additional_mem_pool_size=5M >set-variable = innodb_file_io_threads=4 >set-variable = innodb_lock_wait_timeout=50 > >I created all the directories mentioned, but something is preventing it >working. If I comment out the InnoDB lines mysql starts.Thanks in advance. Cheers, Dr. Bruce A. Scharlau Dept. of Computing Science Aberdeen University Aberdeen AB24 3UE [EMAIL PROTECTED] 01224 272193 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB and InnoDB in windows nt versions
Bruce, the tablespace must be >= 10 MB. You have only specified 5M. I tested with .41 and got the following error message: C:\trybuild\client_debug>mysqld --console InnoDB: Error: tablespace size must be at least 10 MB 010817 15:28:15 Can't init databases Regards, Heikki Copied message: Thanks for the input Heikki, I got it the right version mysql-max-nt going now. The problem was that winmysqladmin automatically started the wrong service- mysqld - so I had to uninstall that service before it would recognise any changed I made in the my.ini file done with winmysqladmin. Now I can use bdb, and show variables gives InnoDB as disabled. I've tried setting up the InnoDB variables in winmysqladmin like this, but the server won't start: [mysqld]basedir=C:/mysql #bind-address=139.133.200.222 datadir=C:/mysql/data language=C:/mysql/share/english #slow query log#= #tmpdir#= #port=3306 #set-variable=key_buffer=16M # startup parameters for InnoDB tables innodb_data_file_path = ibdata1:5M innodb_data_home_dir = C:\mysql\data\bruce\ibdata set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = C:\mysql\data\bruce\iblogs set-variable = innodb_log_files_in_group=3set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=5Minnodb_flush_log_at_trx_commit=1 innodb_log_arch_dir = c:\mysql\data\bruce\iblogsinnodb_log_archive=0 set-variable = innodb_buffer_pool_size=5M set-variable = innodb_additional_mem_pool_size=5M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 I created all the directories mentioned, but something is preventing it working. If I comment out the InnoDB lines mysql starts.Thanks in advance. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB and InnoDB in windows nt versions
At 11:50 17/08/2001 +0100, Bruce Scharlau wrote: Hi! >Thanks for the input Heikki, > >I got it the right version mysql-max-nt going now. The problem was that >winmysqladmin automatically started the wrong service- mysqld - so I had >to uninstall that service before it would recognise any changed I made in >the my.ini file done with winmysqladmin. > >Now I can use bdb, and show variables gives InnoDB as disabled. >I've tried setting up the InnoDB variables in winmysqladmin like this, but >the server won't start: > >[mysqld] >basedir=C:/mysql >#bind-address=139.133.200.222 >datadir=C:/mysql/data >language=C:/mysql/share/english >#slow query log#= >#tmpdir#= >#port=3306 >#set-variable=key_buffer=16M > ># startup parameters for InnoDB tables >innodb_data_file_path = ibdata1:5M You need to use a data file >= 10 MB and <= 2GB Stop the server. Delete all the files from ..\ibadata and ..\iblogs. Change the value above and restart the server. Regards, Miguel >innodb_data_home_dir = C:\mysql\data\bruce\ibdata >set-variable = innodb_mirrored_log_groups=1 >innodb_log_group_home_dir = C:\mysql\data\bruce\iblogs >set-variable = innodb_log_files_in_group=3 >set-variable = innodb_log_file_size=5M >set-variable = innodb_log_buffer_size=5M >innodb_flush_log_at_trx_commit=1 >innodb_log_arch_dir = c:\mysql\data\bruce\iblogs >innodb_log_archive=0 >set-variable = innodb_buffer_pool_size=5M >set-variable = innodb_additional_mem_pool_size=5M >set-variable = innodb_file_io_threads=4 >set-variable = innodb_lock_wait_timeout=50 > >I created all the directories mentioned, but something is preventing it >working. If I comment out the InnoDB lines mysql starts. > >Thanks in advance. > >At 03:16 PM 8/16/2001 +0100, you wrote: >>Hi all, >> >>I'm trying to work out which distribution of mysql supports BDB or InnoDB >>tables. >>I've downloaded 3.23.40 and checked 'show variables' asnd it says these >>tables are not supported. >>I know the docs say they should be available in the distro, but it seems >>not in this version. So, who can tell me which one does? >> >>Cheers, >> >>Bruce >> >> >>- >>Before posting, please check: >> http://www.mysql.com/manual.php (the manual) >> http://lists.mysql.com/ (the list archive) >> >>To request this thread, e-mail <[EMAIL PROTECTED]> >>To unsubscribe, e-mail >><[EMAIL PROTECTED]> >>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > >Cheers, > >Dr. Bruce A. Scharlau >Dept. of Computing Science >Aberdeen University >Aberdeen AB24 3UE >[EMAIL PROTECTED] >01224 272193 > > >- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail ><[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel A. Solórzano <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Mogi das Cruzes - São Paulo, Brazil <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB and InnoDB in windows nt versions
Thanks for the input Heikki, I got it the right version mysql-max-nt going now. The problem was that winmysqladmin automatically started the wrong service- mysqld - so I had to uninstall that service before it would recognise any changed I made in the my.ini file done with winmysqladmin. Now I can use bdb, and show variables gives InnoDB as disabled. I've tried setting up the InnoDB variables in winmysqladmin like this, but the server won't start: [mysqld] basedir=C:/mysql #bind-address=139.133.200.222 datadir=C:/mysql/data language=C:/mysql/share/english #slow query log#= #tmpdir#= #port=3306 #set-variable=key_buffer=16M # startup parameters for InnoDB tables innodb_data_file_path = ibdata1:5M innodb_data_home_dir = C:\mysql\data\bruce\ibdata set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = C:\mysql\data\bruce\iblogs set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=5M innodb_flush_log_at_trx_commit=1 innodb_log_arch_dir = c:\mysql\data\bruce\iblogs innodb_log_archive=0 set-variable = innodb_buffer_pool_size=5M set-variable = innodb_additional_mem_pool_size=5M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 I created all the directories mentioned, but something is preventing it working. If I comment out the InnoDB lines mysql starts. Thanks in advance. At 03:16 PM 8/16/2001 +0100, you wrote: >Hi all, > >I'm trying to work out which distribution of mysql supports BDB or InnoDB >tables. >I've downloaded 3.23.40 and checked 'show variables' asnd it says these >tables are not supported. >I know the docs say they should be available in the distro, but it seems >not in this version. So, who can tell me which one does? > >Cheers, > >Bruce > > >- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail ><[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > Cheers, Dr. Bruce A. Scharlau Dept. of Computing Science Aberdeen University Aberdeen AB24 3UE [EMAIL PROTECTED] 01224 272193 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB and InnoDB in windows nt versions
Sinisa, there is no separate Windows distribution of -Max :). From the -Max page: Note: The MySQL 3.23.38 (and newer) distribution for Windows includes both the normal MySQL and MySQL-Max binaries. Regards, Heikki Sinisa writes: . Bruce Scharlau writes: > Hi all, > > I'm trying to work out which distribution of mysql supports BDB or InnoDB > tables. > I've downloaded 3.23.40 and checked 'show variables' asnd it says these > tables are not supported. > I know the docs say they should be available in the distro, but it seems > not in this version. So, who can tell me which one does? > > Cheers, > > Bruce Download MySQL-Max .. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB and InnoDB in windows nt versions
Bruce Scharlau writes: > Hi all, > > I'm trying to work out which distribution of mysql supports BDB or InnoDB > tables. > I've downloaded 3.23.40 and checked 'show variables' asnd it says these > tables are not supported. > I know the docs say they should be available in the distro, but it seems > not in this version. So, who can tell me which one does? > > Cheers, > > Bruce Download MySQL-Max .. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB tables bugs
Hello Michael, Thursday, May 31, 2001, 11:19:03 PM, you wrote: MW> Hi! >> "Peter" == Peter Zaitsev <[EMAIL PROTECTED]> writes: Peter>> Hello bugs, Peter>> mysql 3.23.38 Peter>> Linux diamond 2.4.4.SuSE-12-4GB-SMP #8 SMP Sat May 19 21:24:05 MSD Peter>> 2001 i686 unknown Peter>> SUSE 7.1 (GLIBC 2.2.x) Peter>> I've got to try to use BDB in my production-like enviroment and Peter>> quickly got above error: MW> MW> select last from MW> layers_st90.g00layers_stat_hits where counter=159806 and type=2 MW> and page_group=255 order MW> by begin desc limit 1 MW> Peter, could you try to check if you can get the problem when just MW> running the above query ? Well. Sorry I fogot to write this. I do not get this then I run just this query. There ara many different queries in my log file which finaly leads to crash and about which applications gets an error (#1) before. So It hapens like - application starts to get error #1 then trying to execute the query and then finaly mysql crashes. This error gets repeatable and stays before mysql restart or crash. Here are some more queries which lead to crash wich exactly the same backtrace, a bit different from the first one, thd->query at 0x8588aa8 = SELECT UNIX_TIMESTAMP(MAX(last)) AS uts FROM layers_st90.g00layers_stat_pagesactions WHERE counter = 138378 thd->thread_id = 42 thd->query at 0x856a160 = SELECT UNIX_TIMESTAMP(MAX(last)) AS uts FROM layers_st90.g00layers_stat_hits WHERE counter = 138378 thd->thread_id = 13 Backtrace is: 0x8234988 pthread_sighandler + 168 0x80ba166 rnd_next__11ha_berkeleyPc + 74 0x80ad63e rr_sequential__FP14st_read_record + 122 0x80964d9 join_init_read_record__FP13st_join_table + 89 0x80915de sub_select__FP4JOINP13st_join_tableb + 86 0x80913a0 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 404 0x808a8eb mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemRt4List1Z15Item_func_matchP8st_orderT5T3T5UiP13select_result + 5547 0x807308a mysql_execute_command__Fv + 758 0x8077074 mysql_parse__FP3THDPcUi + 216 0x8072314 do_command__FP3THD + 1436 0x8071707 handle_one_connection__FPv + 655 MW> I Think this is a long outstanding bug - I got it, then I was trying to play around with BDB first time (about 3 months ago). I wrote about it but it was hard to repeat. Peter>> 010531 18:16:22 /usr/local/mysql/libexec/mysqld: Got error -30989 Peter>> during CHECKPOINT Peter>> 010531 18:16:22 bdb: log_archive: DB_ARCH_ABS: DB_RUNRECOVERY: Fatal Peter>> error, run database recovery Peter>> 010531 18:16:22 /usr/local/mysql/libexec/mysqld: Shutdown Complete Peter>> So it seems like log files went dammaged. Peter>> This is even more surprising as I had no machine crash or reboot... MW> I agree; The above is something that should never happen. MW> How many bdb tables do you have now? About 1200. But only 120 of them are really used now (in test script). MW> To be able to find and fix this, we would need a copy of the logs + MW> the bdb tables to be able to make a bug report for the Sleepycat team. So I packed it and uploaded into secret directory as bdb_tables.tar.gz log.003366.gz MW> Have you had any luck with testing of InnoDB tables ? Well. I'll play more now, after finding out there are some bugs with BDB. I'm a bit afraid of INNODB tables as no recovery tools exists, also it's more hard to make a backups. Also It looks like I'll need to use many tablespaces to hold my database as files grater then 2G is not supported yet. Other think which is really bothering me - data migration which I often have to do if some of my servers gets overload. So I just may copy such databases with bdb or myisam but can't do so with INNODB. My other tests show it's pretty fast, and I'll play more with it I think. -- Best regards, Petermailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB recovery error
On Thursday 12 April 2001 14:03, Ken Menzel wrote: > Hi Sasha, > > > Looks like you found two bugs at once. The first one is that someone > mysqld > > segfaults, probably a bug in processing some query or possibly some > OS issue, > > but there is not much we can tell at this point - the only way to > track it > > down would be if you had full logging enabled and saved the last few > queries > Yes, full logging was enabled. > > > before the crash, and took a full snapshot of the database at the > time of the > > crash. > No I did not take a snapshot. (sorry didn't think of it). > > > > > Second bug is that BDB could not recover after the crash - I hope > you have > > backed up the logs before you deleted them - we will need them to > debug the > > problem. If you still have them, upload them to > > ftp://support.mysql.com/pub/mysql/secret/ > > Unfortunately being in a hurry to get the daemon running again I > deleted the files. > > I will attempt to reproduce the problem on an unused server. I think > it should be easy to recreate, just drop a database after you have > something for it in the log.xx file then after the db is gone, > stop and restart the server. I'll try to come up with a simple test > case tomorrow. In the mean time, we will try to re-produce it ourselves. Monty might even be able to tell right away what is wrong by just looking at the error messages and the code. -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA <___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB recovery error
Hi Sasha, > Looks like you found two bugs at once. The first one is that someone mysqld > segfaults, probably a bug in processing some query or possibly some OS issue, > but there is not much we can tell at this point - the only way to track it > down would be if you had full logging enabled and saved the last few queries Yes, full logging was enabled. > before the crash, and took a full snapshot of the database at the time of the > crash. No I did not take a snapshot. (sorry didn't think of it). > > Second bug is that BDB could not recover after the crash - I hope you have > backed up the logs before you deleted them - we will need them to debug the > problem. If you still have them, upload them to > ftp://support.mysql.com/pub/mysql/secret/ Unfortunately being in a hurry to get the daemon running again I deleted the files. I will attempt to reproduce the problem on an unused server. I think it should be easy to recreate, just drop a database after you have something for it in the log.xx file then after the db is gone, stop and restart the server. I'll try to come up with a simple test case tomorrow. Thanks Ken - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB recovery error
On Thursday 12 April 2001 10:49, Ken Menzel wrote: > We had a crash of MySQL this morning for some reason. (Why who > knows!) The real problem was it would not restart until the log.* > files were deleted. It looks like it was trying to perform a recovery > on a dropped database. The server would not start. This is not cool! > We dropped the test database this morning, when the mysqld crashs it > normally restarts itself (safe_mysqld), but because of the BDB errors > it would not start. > > remove the BDB logs and it starts fine. > > Any ideas/comments? > > Ken > > Here is the tuvok.icarz.com error log snippet: > > > 010412 11:15:39 Aborted connection 11994 to db: 'brtest' user: > 'brtest' host: ` > netb-155.icarz.com' (Got an error reading communication packets) > mysqld got signal 11; > The manual section 'Debugging a MySQL server' tells you how to use a > stack trace and/or the core file to produce a readable backtrace that > may > help in finding out why mysqld died. > 010412 12:07:19 mysqld restarted > 010412 12:07:43 bdb: Recovery function for LSN 52 3669440 failed > 010412 12:07:43 bdb: warning: ./test/acc.db: No such file or > directory > 010412 12:07:43 bdb: warning: ./test/cofile.db: No such file or > directory > mysqld in free(): warning: chunk is already free. > 010412 12:07:43 Can't init databases > 010412 12:07:43 mysqld ended Looks like you found two bugs at once. The first one is that someone mysqld segfaults, probably a bug in processing some query or possibly some OS issue, but there is not much we can tell at this point - the only way to track it down would be if you had full logging enabled and saved the last few queries before the crash, and took a full snapshot of the database at the time of the crash. Second bug is that BDB could not recover after the crash - I hope you have backed up the logs before you deleted them - we will need them to debug the problem. If you still have them, upload them to ftp://support.mysql.com/pub/mysql/secret/ -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA <___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB and Innobase
On Tue, Apr 10, 2001 at 12:37:10PM +0300, Yusuf Incekara wrote: > > does mysql binary win32 dist support innobase and bdb tables. if > yes how ? if not when will you implement? I don't think Innobase is availabe on Windows yet. -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: BDB table corruption when inserting/retrieving a BLOB into a BDB table
We had similar problems with version 3.23.33. For us, the fix was to move
the errant tables to MyISAM. We did not need transaction support for those
tables. We have since moved the tables back to BDB with MySQL version
3.23.34a. The problem seems to have been resolved in this latest update.
Cedric.
-Original Message-
From: Scott McCool [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 13, 2001 3:29 PM
To: 'Stephen Faustino'; '[EMAIL PROTECTED]'
Subject: RE: BDB table corruption when inserting/retrieving a BLOB into
a BDB table
I can also verify that the data seems to be stored properly (from mysql's
command line tool). The problem seems most likely to occur with larger text
strings
Anyone else?
-Scott
-Original Message-
From: Stephen Faustino [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 13, 2001 2:51 PM
To: '[EMAIL PROTECTED]'
Subject: RE: BDB table corruption when inserting/retrieving a BLOB into
a BDB table
We have also experienced the same exact behavior, also using the mm.mysql
driver, on Linux. However, our problem is intermittent. There are times
(though these times cannot be accurately predicted) where the data is
retrieved correctly, which implies that the data is stored correclty. We
have verified that the data is being stored correctly by using mysql to
select the data into a dumpfile. We did not try to recreate the problem
with a table handler other than BDB.
Stephen L. Faustino
Senior Software Engineer
SecureLogix Corporation
Direct/Vmail (210)402-9669x949
mailto:[EMAIL PROTECTED]
This email is intended for the named recipient(s) only and may contain
information that is privileged and/or confidential. Nothing in this
email is intended to constitute a waiver of any privilege or the
confidentiality of this message. If you have received this email in
error, please notify me immediately by reply and delete this message.
-Original Message-
From: Scott McCool [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 13, 2001 1:01 PM
To: '[EMAIL PROTECTED]'
Subject: BDB table corruption when inserting/retrieving a BLOB into a
BDB table
I'm getting a very strange results all of the sudden when dealing with BLOBs
in a mysql 3.23.32 on a Solaris 7 (SPARC) machine (inserts/deletes handled
with the JDBC driver from http://mmmysql.sourceforge.net/)
If I do the following:
1) Create a BDB table with a blob column:
CREATE TABLE test4(
id INTEGER not null PRIMARY KEY,
ablob BLOB null
) TYPE=BDB;
2) Insert a particular blob into that column (I'm storing XSLT stylesheets
in the real table, most of them work fine but one in particular is all of
the sudden causing problems):
---code snip---
String fn="badblob.txt";// Filename of the blob I'm loading that
causes problems
FileInputStream style_fis=new FileInputStream(fn);
byte[] style_bytes=new byte[style_fis.available()];
style_fis.read(style_bytes);
String dQuery="delete from ttest4";
String iQuery="insert into ttest4(id,ablob) values(1,null);";
conn=cp.getConnection();// Gets a JDBC connection from a pool I
keep elsewhere
Statement stmt=conn.createStatement();
stmt.executeUpdate(dQuery);
stmt.executeUpdate(iQuery);
stmt.close();
String uQuery="update test4 set ablob=? where id=1";
pstmt=conn.prepareStatement(uQuery);
pstmt.setBytes(1,style_bytes);
pstmt.execute();
pstmt.close();
cp.releaseConnection(conn);
---end code snip---
(I've modified this some, but basically my code will insert an empty row
then update it with the blob, this is to deal with some issues we've had
with other RDBMs' implementations of JDBC).
This seems to go just fine.
3) Retrieve that blob with the following code:
--code snip--
conn=cp.getConnection();
Statement stmt=conn.createStatement();
String query="select ablob from ttest4 where id=1";
ResultSet rs=stmt.executeQuery(query);
while(rs.next()) {
Blob b=rs.getBlob(1);
byte[] blobBytes=b.getBytes(0,(int)b.length());
System.out.println(new String(b));
}
--end code snip--
At this point my output ends up very garbled. The output usually starts off
with some very strange ASCII characters (seemingly binary data) with things
like filesystem names thrown in the mix... Then at some point parts of my
actual data (from the "badblob.txt" file in step 2) appears... Then the end
of it is usually overwritten with more strange ASCII characters.
In attempting to fix this problem, I dropped and recreated the entire
database a few times, restarted the server daemon, etc. I finally ended up
changing the table to TYPE=MyISAM and the problem went away. Unfortunately,
I need transaction support for this project.
I've been using this code, data, server version, bdb table, etc for a few
weeks now with no problems, but suddenly when
RE: BDB table corruption when inserting/retrieving a BLOB into a BDB table
I can also verify that the data seems to be stored properly (from mysql's
command line tool). The problem seems most likely to occur with larger text
strings
Anyone else?
-Scott
-Original Message-
From: Stephen Faustino [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 13, 2001 2:51 PM
To: '[EMAIL PROTECTED]'
Subject: RE: BDB table corruption when inserting/retrieving a BLOB into
a BDB table
We have also experienced the same exact behavior, also using the mm.mysql
driver, on Linux. However, our problem is intermittent. There are times
(though these times cannot be accurately predicted) where the data is
retrieved correctly, which implies that the data is stored correclty. We
have verified that the data is being stored correctly by using mysql to
select the data into a dumpfile. We did not try to recreate the problem
with a table handler other than BDB.
Stephen L. Faustino
Senior Software Engineer
SecureLogix Corporation
Direct/Vmail (210)402-9669x949
mailto:[EMAIL PROTECTED]
This email is intended for the named recipient(s) only and may contain
information that is privileged and/or confidential. Nothing in this
email is intended to constitute a waiver of any privilege or the
confidentiality of this message. If you have received this email in
error, please notify me immediately by reply and delete this message.
-Original Message-
From: Scott McCool [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 13, 2001 1:01 PM
To: '[EMAIL PROTECTED]'
Subject: BDB table corruption when inserting/retrieving a BLOB into a
BDB table
I'm getting a very strange results all of the sudden when dealing with BLOBs
in a mysql 3.23.32 on a Solaris 7 (SPARC) machine (inserts/deletes handled
with the JDBC driver from http://mmmysql.sourceforge.net/)
If I do the following:
1) Create a BDB table with a blob column:
CREATE TABLE test4(
id INTEGER not null PRIMARY KEY,
ablob BLOB null
) TYPE=BDB;
2) Insert a particular blob into that column (I'm storing XSLT stylesheets
in the real table, most of them work fine but one in particular is all of
the sudden causing problems):
---code snip---
String fn="badblob.txt";// Filename of the blob I'm loading that
causes problems
FileInputStream style_fis=new FileInputStream(fn);
byte[] style_bytes=new byte[style_fis.available()];
style_fis.read(style_bytes);
String dQuery="delete from ttest4";
String iQuery="insert into ttest4(id,ablob) values(1,null);";
conn=cp.getConnection();// Gets a JDBC connection from a pool I
keep elsewhere
Statement stmt=conn.createStatement();
stmt.executeUpdate(dQuery);
stmt.executeUpdate(iQuery);
stmt.close();
String uQuery="update test4 set ablob=? where id=1";
pstmt=conn.prepareStatement(uQuery);
pstmt.setBytes(1,style_bytes);
pstmt.execute();
pstmt.close();
cp.releaseConnection(conn);
---end code snip---
(I've modified this some, but basically my code will insert an empty row
then update it with the blob, this is to deal with some issues we've had
with other RDBMs' implementations of JDBC).
This seems to go just fine.
3) Retrieve that blob with the following code:
--code snip--
conn=cp.getConnection();
Statement stmt=conn.createStatement();
String query="select ablob from ttest4 where id=1";
ResultSet rs=stmt.executeQuery(query);
while(rs.next()) {
Blob b=rs.getBlob(1);
byte[] blobBytes=b.getBytes(0,(int)b.length());
System.out.println(new String(b));
}
--end code snip--
At this point my output ends up very garbled. The output usually starts off
with some very strange ASCII characters (seemingly binary data) with things
like filesystem names thrown in the mix... Then at some point parts of my
actual data (from the "badblob.txt" file in step 2) appears... Then the end
of it is usually overwritten with more strange ASCII characters.
In attempting to fix this problem, I dropped and recreated the entire
database a few times, restarted the server daemon, etc. I finally ended up
changing the table to TYPE=MyISAM and the problem went away. Unfortunately,
I need transaction support for this project.
I've been using this code, data, server version, bdb table, etc for a few
weeks now with no problems, but suddenly when regenerating the schema last
night this started to occur and is now happening regularly.
I'd like to blame this on the particular blob I'm inserting (badblob.txt)
but that doesn't seem to be the problem as it hasn't changed. My other
thought was a corrupt disk, but that doesn't seem likely... I'm open to any
suggestions as this problem seems potentially very serious.
Any help is greatly appreciated!
-Scott
-
Before posting, please check:
http://www.mysql.com/m
RE: BDB table corruption when inserting/retrieving a BLOB into a BDB table
We have also experienced the same exact behavior, also using the mm.mysql
driver, on Linux. However, our problem is intermittent. There are times
(though these times cannot be accurately predicted) where the data is
retrieved correctly, which implies that the data is stored correclty. We
have verified that the data is being stored correctly by using mysql to
select the data into a dumpfile. We did not try to recreate the problem
with a table handler other than BDB.
Stephen L. Faustino
Senior Software Engineer
SecureLogix Corporation
Direct/Vmail (210)402-9669x949
mailto:[EMAIL PROTECTED]
This email is intended for the named recipient(s) only and may contain
information that is privileged and/or confidential. Nothing in this
email is intended to constitute a waiver of any privilege or the
confidentiality of this message. If you have received this email in
error, please notify me immediately by reply and delete this message.
-Original Message-
From: Scott McCool [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 13, 2001 1:01 PM
To: '[EMAIL PROTECTED]'
Subject: BDB table corruption when inserting/retrieving a BLOB into a
BDB table
I'm getting a very strange results all of the sudden when dealing with BLOBs
in a mysql 3.23.32 on a Solaris 7 (SPARC) machine (inserts/deletes handled
with the JDBC driver from http://mmmysql.sourceforge.net/)
If I do the following:
1) Create a BDB table with a blob column:
CREATE TABLE test4(
id INTEGER not null PRIMARY KEY,
ablob BLOB null
) TYPE=BDB;
2) Insert a particular blob into that column (I'm storing XSLT stylesheets
in the real table, most of them work fine but one in particular is all of
the sudden causing problems):
---code snip---
String fn="badblob.txt";// Filename of the blob I'm loading that
causes problems
FileInputStream style_fis=new FileInputStream(fn);
byte[] style_bytes=new byte[style_fis.available()];
style_fis.read(style_bytes);
String dQuery="delete from ttest4";
String iQuery="insert into ttest4(id,ablob) values(1,null);";
conn=cp.getConnection();// Gets a JDBC connection from a pool I
keep elsewhere
Statement stmt=conn.createStatement();
stmt.executeUpdate(dQuery);
stmt.executeUpdate(iQuery);
stmt.close();
String uQuery="update test4 set ablob=? where id=1";
pstmt=conn.prepareStatement(uQuery);
pstmt.setBytes(1,style_bytes);
pstmt.execute();
pstmt.close();
cp.releaseConnection(conn);
---end code snip---
(I've modified this some, but basically my code will insert an empty row
then update it with the blob, this is to deal with some issues we've had
with other RDBMs' implementations of JDBC).
This seems to go just fine.
3) Retrieve that blob with the following code:
--code snip--
conn=cp.getConnection();
Statement stmt=conn.createStatement();
String query="select ablob from ttest4 where id=1";
ResultSet rs=stmt.executeQuery(query);
while(rs.next()) {
Blob b=rs.getBlob(1);
byte[] blobBytes=b.getBytes(0,(int)b.length());
System.out.println(new String(b));
}
--end code snip--
At this point my output ends up very garbled. The output usually starts off
with some very strange ASCII characters (seemingly binary data) with things
like filesystem names thrown in the mix... Then at some point parts of my
actual data (from the "badblob.txt" file in step 2) appears... Then the end
of it is usually overwritten with more strange ASCII characters.
In attempting to fix this problem, I dropped and recreated the entire
database a few times, restarted the server daemon, etc. I finally ended up
changing the table to TYPE=MyISAM and the problem went away. Unfortunately,
I need transaction support for this project.
I've been using this code, data, server version, bdb table, etc for a few
weeks now with no problems, but suddenly when regenerating the schema last
night this started to occur and is now happening regularly.
I'd like to blame this on the particular blob I'm inserting (badblob.txt)
but that doesn't seem to be the problem as it hasn't changed. My other
thought was a corrupt disk, but that doesn't seem likely... I'm open to any
suggestions as this problem seems potentially very serious.
Any help is greatly appreciated!
-Scott
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscrib
RE: BDB tables on Linux
You must compile from source to get BDB support into mysql. Make sure you download the appropriate BDB source and place it in the mysql directory. - / Rick Mehalick Senior Consultant / Shell Services International SSI-GPAX / Phone: 281-544-5092(WCK) / Fax:281-544-2646(WCK) / email: [EMAIL PROTECTED] - -Original Message- From: Stephen Faustino [mailto:[EMAIL PROTECTED]] Sent: Monday, March 05, 2001 4:11 PM To: '[EMAIL PROTECTED]' Subject: RE: BDB tables on Linux show variables showed that have_bdb is no, and none of the bdb variables are there. So does this mean that BDB is NOT compiled into the binary included in the Linux RPM, or does it mean that I need to add the necesary BDB-related variables to /etc/my.cnf? Stephen L. Faustino Senior Software Engineer SecureLogix Corporation Direct/Vmail (210)402-9669x949 mailto:[EMAIL PROTECTED] This email is intended for the named recipient(s) only and may contain information that is privileged and/or confidential. Nothing in this email is intended to constitute a waiver of any privilege or the confidentiality of this message. If you have received this email in error, please notify me immediately by reply and delete this message. -Original Message- From: Jeremy D. Zawodny [mailto:[EMAIL PROTECTED]] Sent: Monday, March 05, 2001 3:51 PM To: Stephen Faustino Cc: '[EMAIL PROTECTED]' Subject: Re: BDB tables on Linux On Mon, Mar 05, 2001 at 03:44:20PM -0600, Stephen Faustino wrote: > > Yes, I specified that the table should use the BDB table handler, > but it does not appear that the table was created as a BDB table. Are you *sure* you're running a version with BDB support compiled in? The output of SHOW VARIBLES will probably tell you. Having not used them, though, it's hard to say... Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: BDB tables on Linux
I never successful rollback if I compile mysql from source in RedHat 7.0, I think it is the compile problem... -Original Message- From: Stephen Faustino [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 06, 2001 5:44 AM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Subject: RE: BDB tables on Linux Yes, I specified that the table should use the BDB table handler, but it does not appear that the table was created as a BDB table. The following are the results of my sessions on Solaris and Linux: Solaris: mysql> create table test (x int) type = BDB; Query OK, 0 rows affected (0.46 sec) mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values (1); Query OK, 1 row affected (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from test; Empty set (0.06 sec) mysql> Linux: mysql> create table test (x int) type = BDB; Query OK, 0 rows affected (0.01 sec) mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values (1); Query OK, 1 row affected (0.00 sec) mysql> rollback; ERROR 1196: Warning: Some non-transactional changed tables couldn't be rolled b ack mysql> select * from test; +--+ | x| +--+ |1 | +--+ 1 row in set (0.00 sec) mysql> Stephen L. Faustino Senior Software Engineer SecureLogix Corporation Direct/Vmail (210)402-9669x949 mailto:[EMAIL PROTECTED] -Original Message- From: Jeremy D. Zawodny [mailto:[EMAIL PROTECTED]] Sent: Monday, March 05, 2001 3:29 PM To: Stephen Faustino Cc: '[EMAIL PROTECTED]' Subject: Re: BDB tables on Linux On Mon, Mar 05, 2001 at 11:27:54AM -0600, Stephen Faustino wrote: > > I've installed 3.23.33 on both Solaris and Linux RH 6.2 using the > tarball for Solaris and the RPM for Linux. The BDB tables work as > expected on Solaris, but they did not not work under Linux. What > I'm seeing is that I can create a BDB just fine. However, > attempting a rollback results in: > > ERROR 1196: Warning: Some non-transactional changed tables couldn't be > rolled back > > When I do a mysqldump, the ddl for the table shows a type of MyISAM. This suggestst that they weren't created as BDB tables. Can you verify that they're created properly? Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB tables on Linux
On Mon, Mar 05, 2001 at 04:10:59PM -0600, Stephen Faustino wrote: > > show variables showed that have_bdb is no, and none of the bdb > variables are there. So does this mean that BDB is NOT compiled > into the binary included in the Linux RPM, or does it mean that I > need to add the necesary BDB-related variables to /etc/my.cnf? You must compile the BDB support. It's currently not in the RPM. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB tables on Linux
As far I can tell, you have to rebuild from source. BDB works very bad for me (linuxppc). Extremely slow and errors. Christian. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: BDB tables on Linux
show variables showed that have_bdb is no, and none of the bdb variables are there. So does this mean that BDB is NOT compiled into the binary included in the Linux RPM, or does it mean that I need to add the necesary BDB-related variables to /etc/my.cnf? Stephen L. Faustino Senior Software Engineer SecureLogix Corporation Direct/Vmail (210)402-9669x949 mailto:[EMAIL PROTECTED] This email is intended for the named recipient(s) only and may contain information that is privileged and/or confidential. Nothing in this email is intended to constitute a waiver of any privilege or the confidentiality of this message. If you have received this email in error, please notify me immediately by reply and delete this message. -Original Message- From: Jeremy D. Zawodny [mailto:[EMAIL PROTECTED]] Sent: Monday, March 05, 2001 3:51 PM To: Stephen Faustino Cc: '[EMAIL PROTECTED]' Subject: Re: BDB tables on Linux On Mon, Mar 05, 2001 at 03:44:20PM -0600, Stephen Faustino wrote: > > Yes, I specified that the table should use the BDB table handler, > but it does not appear that the table was created as a BDB table. Are you *sure* you're running a version with BDB support compiled in? The output of SHOW VARIBLES will probably tell you. Having not used them, though, it's hard to say... Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB tables on Linux
On Mon, Mar 05, 2001 at 03:44:20PM -0600, Stephen Faustino wrote: > > Yes, I specified that the table should use the BDB table handler, > but it does not appear that the table was created as a BDB table. Are you *sure* you're running a version with BDB support compiled in? The output of SHOW VARIBLES will probably tell you. Having not used them, though, it's hard to say... Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: BDB tables on Linux
Yes, I specified that the table should use the BDB table handler, but it does not appear that the table was created as a BDB table. The following are the results of my sessions on Solaris and Linux: Solaris: mysql> create table test (x int) type = BDB; Query OK, 0 rows affected (0.46 sec) mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values (1); Query OK, 1 row affected (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from test; Empty set (0.06 sec) mysql> Linux: mysql> create table test (x int) type = BDB; Query OK, 0 rows affected (0.01 sec) mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values (1); Query OK, 1 row affected (0.00 sec) mysql> rollback; ERROR 1196: Warning: Some non-transactional changed tables couldn't be rolled b ack mysql> select * from test; +--+ | x| +--+ |1 | +--+ 1 row in set (0.00 sec) mysql> Stephen L. Faustino Senior Software Engineer SecureLogix Corporation Direct/Vmail (210)402-9669x949 mailto:[EMAIL PROTECTED] -Original Message- From: Jeremy D. Zawodny [mailto:[EMAIL PROTECTED]] Sent: Monday, March 05, 2001 3:29 PM To: Stephen Faustino Cc: '[EMAIL PROTECTED]' Subject: Re: BDB tables on Linux On Mon, Mar 05, 2001 at 11:27:54AM -0600, Stephen Faustino wrote: > > I've installed 3.23.33 on both Solaris and Linux RH 6.2 using the > tarball for Solaris and the RPM for Linux. The BDB tables work as > expected on Solaris, but they did not not work under Linux. What > I'm seeing is that I can create a BDB just fine. However, > attempting a rollback results in: > > ERROR 1196: Warning: Some non-transactional changed tables couldn't be > rolled back > > When I do a mysqldump, the ddl for the table shows a type of MyISAM. This suggestst that they weren't created as BDB tables. Can you verify that they're created properly? Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB tables on Linux
On Mon, Mar 05, 2001 at 11:27:54AM -0600, Stephen Faustino wrote: > > I've installed 3.23.33 on both Solaris and Linux RH 6.2 using the > tarball for Solaris and the RPM for Linux. The BDB tables work as > expected on Solaris, but they did not not work under Linux. What > I'm seeing is that I can create a BDB just fine. However, > attempting a rollback results in: > > ERROR 1196: Warning: Some non-transactional changed tables couldn't be > rolled back > > When I do a mysqldump, the ddl for the table shows a type of MyISAM. This suggestst that they weren't created as BDB tables. Can you verify that they're created properly? Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB tables crashing mysql 3.23.33
Hi!
> "Sasha" == Sasha Pachev <[EMAIL PROTECTED]> writes:
Sasha> I do have a patch for it now. From what I understood in the code, this is
Sasha> actually a more correct way to do things, but I would like to get Monty's
Sasha> approval to be sure. After the patch, MySQL still passes our test suite,
Sasha> which is a good sign. So if you are in dire straits, go ahead and apply it,
Sasha> otherwise, wait until 3.23.34 - if there is something wrong with the current
Sasha> fix, it will be corrected by then. Here it is:
Sasha> --- 1.33/sql/field.cc Wed Feb 7 19:43:54 2001
Sasha> +++ 1.34/sql/field.cc Thu Mar 1 11:49:05 2001
Sasha> @@ -4075,8 +4075,8 @@
Sasha> const char *Field_blob::unpack(char *to, const char *from)
Sasha> {
Sasha>memcpy(to,from,packlength);
Sasha> + ulong length=get_length(from);
Sasha>from+=packlength;
Sasha> - ulong length=get_length();
Sasha>if (length)
Sasha> memcpy_fixed(to+packlength, &from, sizeof(from));
Sasha>else
Looks ok. Thanks for fixing this!
Regards,
Monty
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB tables crashing mysql 3.23.33
Hi Sasha,
The patch you sent me seems to have created other problems,
or simply has allowed to find new ones :)
I'm working on isolating exactly which query makes the bdb
tables crash. Full bug report on the way.
So far, it looks like left joins on bdb tables does something weird.
I was using the following query:
SELECT ProductName, tblProduct.ProductID
FROM tblService LEFT JOIN tblPackage ON (tblService.PackageID =
tblPackage.PackageID)
LEFT JOIN tblConfig ON (tblConfig.ServiceID = tblService.ServiceID)
LEFT JOIN tblProduct ON (tblConfig.ProductID = tblProduct.ProductID)
LEFT JOIN tblProductConfig ON (tblProduct.ProductID =
tblProductConfig.ProductID)
WHERE ProductGroupID = '4' AND tblService.ServiceID ='2'
AND ModifyAllow = 'Y' AND DefaultValue IS NULL AND UserConfigFlag = 'Y'
GROUP BY tblProduct.ProductID;
which returned no results (it should have). the query did work before
some of the tables were switched to BDB.
I rewrote it look like:
select p.ProductName,p.ProductID FROM tblProduct p, tblService s,
tblConfig c,tblProductConfig pc,
tblPackage pkg WHERE s.PackageID = pkg.PackageID AND c.ServiceID =
s.ServiceID
AND c.ProductID = p.ProductID AND p.ProductID = pc.ProductID AND
p.ProductGroupID = 4
AND s.ServiceID = 2 AND pc.ModifyAllow = 'Y' AND pc.DefaultValue IS NULL
AND pc.UserConfigFlag = 'Y' GROUP BY p.ProductID;
which returns the expected results.
The error log is reporting the following error, but I haven't pinpointed
exactly what triggers it yet.
010301 21:09:51 bdb: transaction has active cursors
010301 21:09:51 bdb: PANIC: Invalid argument
010301 21:09:51 Aborted connection 16 to db: 'Funio' user: 'funio'
host: `localhost' (Got an error reading communication packets)
The error seems to be showing up after doing a transaction which has
executed successfully (I'll confirm shortly).
Here's the table structure:
CREATE TABLE tblProduct (
ProductID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
ProductName varchar(255) DEFAULT '' NOT NULL,
Status enum('Active','Disabled') DEFAULT 'Active' NOT NULL,
ProductTable varchar(255) DEFAULT '' NOT NULL,
AddCommand text,
RemoveCommand text,
EnableCommand text,
DisableCommand text,
ModifyCommand text,
ProductGroupID int(10) unsigned DEFAULT '0' NOT NULL,
PRIMARY KEY (ProductID),
KEY ProductGroupID (ProductGroupID),
KEY Status (Status)
);
CREATE TABLE tblService (
ServiceID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
UserID int(10) unsigned DEFAULT '0' NOT NULL,
PackageID int(10) unsigned DEFAULT '0' NOT NULL,
StartDate date DEFAULT '-00-00' NOT NULL,
EndDate date DEFAULT '-00-00' NOT NULL,
RebillDate date DEFAULT '-00-00' NOT NULL,
RebillRetryDate date DEFAULT '-00-00' NOT NULL,
Status enum('Active','Disabled','Removed') DEFAULT 'Active' NOT NULL,
ScheduleToBeRemoved enum('Yes','No') DEFAULT 'No' NOT NULL,
ServiceActivationIP char(20),
ServiceActivationTimeStamp char(20),
PRIMARY KEY (ServiceID),
KEY UserID (UserID),
KEY PackageID (PackageID),
KEY StartDate (StartDate),
KEY EndDate (EndDate),
KEY RebillDate (RebillDate),
KEY RebillRetryDate (RebillRetryDate),
KEY Status (Status)
) type=BDB;
CREATE TABLE tblConfig (
ConfigID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
ServiceID int(10) unsigned DEFAULT '0' NOT NULL,
ProductID int(10) unsigned DEFAULT '0' NOT NULL,
Configured enum('Y','N') DEFAULT 'N' NOT NULL,
PRIMARY KEY (ConfigID),
KEY Configured (Configured),
KEY ServiceID (ServiceID),
KEY ProductID (ProductID)
) type=BDB;
CREATE TABLE tblProductConfig (
ProductFieldID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
ProductID int(10) unsigned DEFAULT '0' NOT NULL,
UserConfigFlag enum('Y','N') DEFAULT 'Y' NOT NULL,
RequiredConfigFlag enum('Y','N') DEFAULT 'Y' NOT NULL,
ModifyAllow enum('Y','N') DEFAULT 'Y' NOT NULL,
ProductField text NOT NULL,
DefaultValue text,
ProductFieldCheckRegExp varchar(255),
ProductFieldError varchar(100),
PRIMARY KEY (ProductFieldID),
KEY ProductID (ProductID),
KEY UserConfigFlag (UserConfigFlag),
KEY RequiredConfigFlag (RequiredConfigFlag)
);
CREATE TABLE tblPackage (
PackageID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
PackageName varchar(255),
PackageGroupID int(10) unsigned DEFAULT '0' NOT NULL,
PackageTypeID int(10) unsigned DEFAULT '0' NOT NULL,
PackagePeriod tinyint(3) unsigned,
PackagePrice decimal(10,2),
PackageInstallationFee decimal(10,2),
PackageProfit decimal(10,2),
PackageAnswer text,
Rebill enum('Y','N') DEFAULT 'Y' NOT NULL,
Status enum('Active','Disabled'),
PRIMARY KEY (PackageID),
KEY PackageGroupID (PackageGroupID),
KEY Rebill (Rebill),
KEY PackageTypeID (PackageTypeID)
);
Sasha Pachev wrote:
>
> On Wednesday 28 February 2001 22:04, [EMAIL PROTECTED] wrote:
> >Hi folks, I'm having a hard time using bdb tables. Here are the
> >details...
> >
> >I've create a few BDB tables
Re: BDB tables crashing mysql 3.23.33
On Thursday 01 March 2001 13:32, Quentin Bennett wrote: > > >After the patch, MySQL still passes our test suite, which is a good sign. > > > >But how come it passed the test suite with the bug in place ?? ;-) It does not any more, with the bug not fixed, that is. We update the test suite every time we find a new bug. -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA <___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: BDB tables crashing mysql 3.23.33
After the patch, MySQL still passes our test suite, which is a good sign. But how come it passed the test suite with the bug in place ?? ;-) Quentin The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB tables crashing mysql 3.23.33
On Wednesday 28 February 2001 22:04, [EMAIL PROTECTED] wrote:
>Hi folks, I'm having a hard time using bdb tables. Here are the
>details...
>
>I've create a few BDB tables, which are causing me great amounts
>of grief. The tables worked fine for a while, then suddenly
>started crashing mysqld. I decided to drop the offending
>database, and re-create it. No problem so far. Right after
>I run the offending queries (see below), mysqld dies. Any subsequent
>connection attempt to mysqld hangs, and the server is basically
>dead (requiring a killall -9 mysqld). There is a file called
>log.01 that appeared, as well as an error message (see below).
>
>I can replicate this error quite easily, as it happens each time the
>query
>is run.
>
>
>CREATE TABLE tblCharge (
> ChargeID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
> ServiceID int(10) unsigned DEFAULT '0' NOT NULL,
> ChargeDate date DEFAULT '-00-00' NOT NULL,
> ChargeAmount decimal(20,2) DEFAULT '0.00' NOT NULL,
> FedTaxes decimal(20,2) DEFAULT '0.00' NOT NULL,
> ProvTaxes decimal(20,2) DEFAULT '0.00' NOT NULL,
> ChargeStatus enum('New','Auth','Unauth','Sale','Denied','Refund')
>DEFAULT 'New' NOT NULL,
> ChargeAuthorizationMessage text,
> ChargeComment text,
> ChargeTimeStamp varchar(20),
> PRIMARY KEY (ChargeID),
> KEY ServiceID (ServiceID),
> KEY ChargeDate (ChargeDate)
>) type=BDB;
>
>
>
>Query #1:
>BEGIN;
>INSERT INTO tblCharge
>VALUES(NULL,1,CURRENT_DATE(),1,1,1,'New',NULL,NULL,UNIX_TIMESTAMP(NOW()));
>COMMIT;
>
>Query #2
>BEGIN;
>UPDATE tblCharge SET ChargeAuthorizationMessage = 'blablabla' WHERE
>ChargeID = 1;
>COMMIT;
>
>Query #3
>BEGIN;
>INSERT INTO tblCharge
>VALUES(NULL,1,CURRENT_DATE(),1,1,1,'New',NULL,NULL,UNIX_TIMESTAMP(NOW()));
>
>
I do have a patch for it now. From what I understood in the code, this is
actually a more correct way to do things, but I would like to get Monty's
approval to be sure. After the patch, MySQL still passes our test suite,
which is a good sign. So if you are in dire straits, go ahead and apply it,
otherwise, wait until 3.23.34 - if there is something wrong with the current
fix, it will be corrected by then. Here it is:
--- 1.33/sql/field.cc Wed Feb 7 19:43:54 2001
+++ 1.34/sql/field.cc Thu Mar 1 11:49:05 2001
@@ -4075,8 +4075,8 @@
const char *Field_blob::unpack(char *to, const char *from)
{
memcpy(to,from,packlength);
+ ulong length=get_length(from);
from+=packlength;
- ulong length=get_length();
if (length)
memcpy_fixed(to+packlength, &from, sizeof(from));
else
--
MySQL Development Team
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sasha Pachev <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA
<___/
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB tables crashing mysql 3.23.33
On Wednesday 28 February 2001 22:04, [EMAIL PROTECTED] wrote:
>Hi folks, I'm having a hard time using bdb tables. Here are the
>details...
>
>I've create a few BDB tables, which are causing me great amounts
>of grief. The tables worked fine for a while, then suddenly
>started crashing mysqld. I decided to drop the offending
>database, and re-create it. No problem so far. Right after
>I run the offending queries (see below), mysqld dies. Any subsequent
>connection attempt to mysqld hangs, and the server is basically
>dead (requiring a killall -9 mysqld). There is a file called
>log.01 that appeared, as well as an error message (see below).
>
>I can replicate this error quite easily, as it happens each time the
>query
>is run.
>
>
>CREATE TABLE tblCharge (
> ChargeID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
> ServiceID int(10) unsigned DEFAULT '0' NOT NULL,
> ChargeDate date DEFAULT '-00-00' NOT NULL,
> ChargeAmount decimal(20,2) DEFAULT '0.00' NOT NULL,
> FedTaxes decimal(20,2) DEFAULT '0.00' NOT NULL,
> ProvTaxes decimal(20,2) DEFAULT '0.00' NOT NULL,
> ChargeStatus enum('New','Auth','Unauth','Sale','Denied','Refund')
>DEFAULT 'New' NOT NULL,
> ChargeAuthorizationMessage text,
> ChargeComment text,
> ChargeTimeStamp varchar(20),
> PRIMARY KEY (ChargeID),
> KEY ServiceID (ServiceID),
> KEY ChargeDate (ChargeDate)
>) type=BDB;
>
>
>
>Query #1:
>BEGIN;
>INSERT INTO tblCharge
>VALUES(NULL,1,CURRENT_DATE(),1,1,1,'New',NULL,NULL,UNIX_TIMESTAMP(NOW()));
>COMMIT;
>
>Query #2
>BEGIN;
>UPDATE tblCharge SET ChargeAuthorizationMessage = 'blablabla' WHERE
>ChargeID = 1;
>COMMIT;
>
>Query #3
>BEGIN;
>INSERT INTO tblCharge
>VALUES(NULL,1,CURRENT_DATE(),1,1,1,'New',NULL,NULL,UNIX_TIMESTAMP(NOW()));
>
>
>
>ERROR 2013: Lost connection to MySQL server during query
>mysql> COMMIT;
>Number of processes running now: 0
>010228 13:59:40 mysqld restarted
Quick update on this bug - if you change ChargeAuthorizationMessage and
ChargeComment to varchar(255) the bug does not happen. In other words, the
offender is the text field.
--
MySQL Development Team
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sasha Pachev <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA
<___/
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB tables crashing mysql 3.23.33
On Thu, Mar 01, 2001 at 12:04:27AM -0500, [EMAIL PROTECTED] wrote: > > I've replicated this problem on 3 different machines, and > on 3.23.33 and 3.23.32. I had been using BDB tables for a > couple weeks now in testing, without a hitch until the above > query starting being used. I also tried to recompile mysql > statically, with the same results. There is a bug when handling a text (blob) field. I'm looking into it, and hope to have a patch for it this morning. > I really hope there is an easy patch/fix to this problem, as my > development team has just finished writing 15000 lines of code > and use DBD tables extensively throughout the application. My > company has purchased a basic license for mysql, and quite > frankly this error really concerns me. > > If it was this easy to crash BDB tables and there is no quick > fix, I will have to stop using mysql altogether throughout my > company. We rely heavily on mysql right now, and this kind > of error makes me wonder if this is not a mistake. Until a > solution can be found, I will instruct my team to start porting > the application to postgres, I simply can't take the chance of > using these versions of mysql in production. I've added this bug to our test suite, so it won't crop back up again once it's fixed. Thank you for the test case! Tim -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Tim Smith <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Development Team /_/ /_/\_, /___/\___\_\___/ Boone, NC USA <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB tables crashing mysql 3.23.33
On Wednesday 28 February 2001 22:04, [EMAIL PROTECTED] wrote:
>Hi folks, I'm having a hard time using bdb tables. Here are the
>details...
>
>I've create a few BDB tables, which are causing me great amounts
>of grief. The tables worked fine for a while, then suddenly
>started crashing mysqld. I decided to drop the offending
>database, and re-create it. No problem so far. Right after
>I run the offending queries (see below), mysqld dies. Any subsequent
>connection attempt to mysqld hangs, and the server is basically
>dead (requiring a killall -9 mysqld). There is a file called
>log.01 that appeared, as well as an error message (see below).
>
>I can replicate this error quite easily, as it happens each time the
>query
>is run.
>
>
>CREATE TABLE tblCharge (
> ChargeID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
> ServiceID int(10) unsigned DEFAULT '0' NOT NULL,
> ChargeDate date DEFAULT '-00-00' NOT NULL,
> ChargeAmount decimal(20,2) DEFAULT '0.00' NOT NULL,
> FedTaxes decimal(20,2) DEFAULT '0.00' NOT NULL,
> ProvTaxes decimal(20,2) DEFAULT '0.00' NOT NULL,
> ChargeStatus enum('New','Auth','Unauth','Sale','Denied','Refund')
>DEFAULT 'New' NOT NULL,
> ChargeAuthorizationMessage text,
> ChargeComment text,
> ChargeTimeStamp varchar(20),
> PRIMARY KEY (ChargeID),
> KEY ServiceID (ServiceID),
> KEY ChargeDate (ChargeDate)
>) type=BDB;
>
>
>
>Query #1:
>BEGIN;
>INSERT INTO tblCharge
>VALUES(NULL,1,CURRENT_DATE(),1,1,1,'New',NULL,NULL,UNIX_TIMESTAMP(NOW()));
>COMMIT;
>
>Query #2
>BEGIN;
>UPDATE tblCharge SET ChargeAuthorizationMessage = 'blablabla' WHERE
>ChargeID = 1;
>COMMIT;
>
>Query #3
>BEGIN;
>INSERT INTO tblCharge
>VALUES(NULL,1,CURRENT_DATE(),1,1,1,'New',NULL,NULL,UNIX_TIMESTAMP(NOW()));
>
>
>
>ERROR 2013: Lost connection to MySQL server during query
>mysql> COMMIT;
>Number of processes running now: 0
>010228 13:59:40 mysqld restarted
Thanks for reporting the bug - I have been able to repeat it, and am now
trying to figure out what is happening. Unfortunately, Tim and Monty, who are
the ones working on BDB code are not going to be available for a couple of
days, and since I do not know that code as well, it would take me some time
to understand how things work ( which I need to do anyway, and now is a good
chance) before I can provide a fix.
Please note that our BDB tables have not yet received nearly as much of the
"baptism of fire" as MyISAM. We will however, fix all the reported bugs
promptly.
--
MySQL Development Team
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sasha Pachev <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA
<___/
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB tables crashing mysql 3.23.33
[EMAIL PROTECTED] writes:
> Hi folks, I'm having a hard time using bdb tables. Here are the
> details...
>
> I've create a few BDB tables, which are causing me great amounts
> of grief. The tables worked fine for a while, then suddenly
> started crashing mysqld. I decided to drop the offending
> database, and re-create it. No problem so far. Right after
> I run the offending queries (see below), mysqld dies. Any subsequent
> connection attempt to mysqld hangs, and the server is basically
> dead (requiring a killall -9 mysqld). There is a file called
> log.01 that appeared, as well as an error message (see below).
>
> I can replicate this error quite easily, as it happens each time the
> query
> is run.
>
>
> CREATE TABLE tblCharge (
> ChargeID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
> ServiceID int(10) unsigned DEFAULT '0' NOT NULL,
> ChargeDate date DEFAULT '-00-00' NOT NULL,
> ChargeAmount decimal(20,2) DEFAULT '0.00' NOT NULL,
> FedTaxes decimal(20,2) DEFAULT '0.00' NOT NULL,
> ProvTaxes decimal(20,2) DEFAULT '0.00' NOT NULL,
> ChargeStatus enum('New','Auth','Unauth','Sale','Denied','Refund')
> DEFAULT 'New' NOT NULL,
> ChargeAuthorizationMessage text,
> ChargeComment text,
> ChargeTimeStamp varchar(20),
> PRIMARY KEY (ChargeID),
> KEY ServiceID (ServiceID),
> KEY ChargeDate (ChargeDate)
> ) type=BDB;
>
>
>
> Query #1:
> BEGIN;
> INSERT INTO tblCharge
> VALUES(NULL,1,CURRENT_DATE(),1,1,1,'New',NULL,NULL,UNIX_TIMESTAMP(NOW()));
> COMMIT;
>
> Query #2
> BEGIN;
> UPDATE tblCharge SET ChargeAuthorizationMessage = 'blablabla' WHERE
> ChargeID = 1;
> COMMIT;
>
> Query #3
> BEGIN;
> INSERT INTO tblCharge
> VALUES(NULL,1,CURRENT_DATE(),1,1,1,'New',NULL,NULL,UNIX_TIMESTAMP(NOW()));
>
>
>
> ERROR 2013: Lost connection to MySQL server during query
> mysql> COMMIT;
> Number of processes running now: 0
> 010228 13:59:40 mysqld restarted
>
> ERROR 2006: MySQL server has gone away
> No connection. Trying to reconnect...
> Connection id:4
> Current database: Funio
>
> ./mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var \
> --user=root --pid-file=/usr/local/mysql/var/suave.pid --skip-locking
> ./mysqld: ready for connections
> mysqld got signal 11;
> The manual section 'Debugging a MySQL server' tells you how to use a
> stack trace and/or the core file to produce a readable backtrace that
> may
> help in finding out why mysqld died
> Attemping backtrace. You can use the following information to find out
> where mysqld died. If you see no messages after this, something went
> terribly wrong
>
>
> I've replicated this problem on 3 different machines, and on 3.23.33 and
> 3.23.32.
> I had been using BDB tables for a couple weeks now in testing, without a
> hitch
> until the above query starting being used. I also tried to recompile
> mysql
> statically, with the same results.
>
> I really hope there is an easy patch/fix to this problem, as my
> development
> team has just finished writing 15000 lines of code and use DBD tables
> extensively throughout the application. My company has purchased a
> basic license for mysql, and quite frankly this error really concerns
> me.
>
> If it was this easy to crash BDB tables and there is no quick fix, I
> will
> have to stop using mysql altogether throughout my company. We rely
> heavily
> on mysql right now, and this kind of error makes me wonder if this is
> not a
> mistake. Until a solution can be found, I will instruct my team to
> start porting the application to postgres, I simply can't take the
> chance of using these versions of mysql in production.
>
>
>
>
> Description:
> BDB tables crash very easily
> How-To-Repeat:
> See above
> Fix:
> no idea
>
> Originator: Mark Steele
> Organization:
> Mark Steele
> Vice president research and development
> Inet Technologies Inc.
> [EMAIL PROTECTED]
>
> MySQL support: [email support]
> Synopsis: BDB tables crash
> Severity:
> Priority:
> Category: mysql
> Class:
> Release: mysql-3.23.32 (Source distribution)
> Server: /usr/local/mysql/bin/mysqladmin Ver 8.14 Distrib 3.23.32, for
> pc-linux-gnu on i686
> Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
> This software comes with ABSOLUTELY NO WARRANTY. This is free software,
> and you are welcome to modify and redistribute it under the GPL license
>
> Server version 3.23.32
> Protocol version10
> Connection Localhost via UNIX socket
> UNIX socket /tmp/mysql.sock
> Uptime: 1 min 12 sec
>
> Threads: 1 Questions: 61 Slow queries: 0 Opens: 48 Flush tables: 1
> Open tables: 42 Queries per second avg: 0.847
> Environment:
> System: Linux suave 2.4.1 #11 Sat Feb 17
Re: BDB Problem with MAX() and multipart primary keys
Guenther Pewny writes:
> It's a miracle.
>
> Which version of the BDB patch do you use?
> I'm using 3.2.9a.
> (Previously, there has been a version 3.2.3g...)
>
> Thanks...
>
> Günther Pewny
>
Hi!
I used latest source tarball from our 3.23 page.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB Problem with MAX() and multipart primary keys
It's a miracle. Which version of the BDB patch do you use? I'm using 3.2.9a. (Previously, there has been a version 3.2.3g...) Thanks... Günther Pewny Sinisa Milivojevic wrote: > Guenther Pewny writes: > > Hi folks, > > > > I have a problem with BDB tables. Please look at this short mysql session: > > > > mysql> create table sample ( > > id1 int(8) not null, > > id2 int(8) not null, > > primary key (id1,id2)) > > type=BDB; > > Query OK, 0 rows affected (0.67 sec) > > > > mysql> insert into sample values (1, 2); > > Query OK, 1 row affected (0.07 sec) > > > > mysql> select id2 from sample where id1=1; > > +-+ > > | id2 | > > +-+ > > | 2 | > > +-+ > > 1 row in set (0.00 sec) > > > > mysql> select min(id2) from sample where id1=1; > > +--+ > > | min(id2) | > > +--+ > > |2 | > > +--+ > > 1 row in set (0.00 sec) > > > > mysql> select max(id2) from sample where id1=1; > > +--+ > > | max(id2) | > > +--+ > > | NULL | > > +--+ > > 1 row in set (0.00 sec) > > > > Is this a bug in mySQL? > > Why does MIN() return the correct result, while MAX() does not? > > Is it a severe bug which possibly can cause data inconsistency > > within the table data or does there exist a "good" workaround for > > this problem with only modifying the query? > > > > Thanks > > Günther Pewny > > > > > > Hi! > > I have just tested this with 3.23.33 and it executed just fine !! > > Regards, > > Sinisa > > __ _ _ ___ == MySQL AB > /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic > /*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED] >/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus > /*/ /*/ /*/\*\_/*/ \*\_/*/ |*| > /*/^^^\*\^^^ > /*/ \*\Developers Team - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB Problem with MAX() and multipart primary keys
Guenther Pewny writes:
> Hi folks,
>
> I have a problem with BDB tables. Please look at this short mysql session:
>
> mysql> create table sample (
> id1 int(8) not null,
> id2 int(8) not null,
> primary key (id1,id2))
> type=BDB;
> Query OK, 0 rows affected (0.67 sec)
>
> mysql> insert into sample values (1, 2);
> Query OK, 1 row affected (0.07 sec)
>
> mysql> select id2 from sample where id1=1;
> +-+
> | id2 |
> +-+
> | 2 |
> +-+
> 1 row in set (0.00 sec)
>
> mysql> select min(id2) from sample where id1=1;
> +--+
> | min(id2) |
> +--+
> |2 |
> +--+
> 1 row in set (0.00 sec)
>
> mysql> select max(id2) from sample where id1=1;
> +--+
> | max(id2) |
> +--+
> | NULL |
> +--+
> 1 row in set (0.00 sec)
>
> Is this a bug in mySQL?
> Why does MIN() return the correct result, while MAX() does not?
> Is it a severe bug which possibly can cause data inconsistency
> within the table data or does there exist a "good" workaround for
> this problem with only modifying the query?
>
> Thanks
> Günther Pewny
>
>
Hi!
I have just tested this with 3.23.33 and it executed just fine !!
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: BDB
Hi! > "Mehalick," == Mehalick, Richard RE SSI-GRAX <[EMAIL PROTECTED]> writes: Mehalick,> When using the BDB tables and RedHat 6.2 or 7.0, should --skip-locking be Mehalick,> used or not? I know it was recommended for earlier versions of RedHat, like Mehalick,> version 5.x. Now when CHECK TABLE and REPAIR TABLE is integrated with mysqld, You can always use --skip-locking; (In any case, this option has no effect on BDB tables) Regrads, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB
Hello Michael, Friday, February 09, 2001, 3:48:53 AM, you wrote: Peter>> as you see here one thread is doing repear as this happened after Peter>> mysqld crash, but note first insert query in system lock state (I've Peter>> saw several queries at this place, but all of them spent in system Peter>> lock time several tenths of seconds) this can nothing to do with Peter>> recovery (this table was just created by mysqldump couple of minutes Peter>> before) - I habe the only explanation which looks ok the system lock Peter>> takes place while many inserts are going to othe table (g03dirs) - as Peter>> soon as the table changed all other queries which was in system lock Peter>> state got a chance to run. The other thing is mysqldump does not uses Peter>> lock tables to insert data so this looks like real perfomance Peter>> (concurency) problem Some Peter>> more examples: MW> The "System Lock" means that you got some problem with fnctl() on your MW> system. MW> The fix is to restart mysqld with --skip-locking and without MW> --enable-locking to ensure that fcntl() lock is not used. Mysql is started with --skip-locking as it's recomended on linux, so this should not be the problem. MW> If you are using 'mysqldump --opt', it should generate locks for the MW> tables. What command did you use ? At first I used mysqldump --all --opt but then mysqld crashed on multiple inserts. The next time I just started it with --all --add-drop-tables options and this is the cases for this usage. Peter>> onyx:/spylog/db # mysqladmin processlist | grep -v Sleep Peter>> +-+---++-+-+--++--+ Peter>> | Id | User | Host | db | Command | Time | State | Info | Peter>> +-+---++-+-+--++--+ Peter>> | 36 | root | localhost | lacontrol | Query | 384 | update | replace into layers_la00.g00stat_404refs (layer_id,id,visitors,loads,hits) values Peter>> (2,2,0,4,0), Peter>> (2,9, | Peter>> | 39 | root | localhost | layers_la00 | Query | 0| update | INSERT INTO g03stat_404pages VALUES (149,1563,0,1,0) | Peter>> | 272 | root | localhost | | Query | 0|| show processlist | Peter>> +-+---++-+-+--++--+ Peter>> as you see here replace is "hanged" - it's simple query wich should Peter>> not take so long. But just few seconds after: MW> Do you know if some other thread was using the table at this point ? layers_st00.g00stat_404refs ? No. This is the only thread using it. Peter>> onyx:/spylog/db # mysqladmin processlist | grep -v Sleep Peter>> +-+---++-+-+--+-+--+Peter>> | Id | User | Host | db | Command | Time | State | Info | Peter>> +-+---++-+-+--+-+--+ Peter>> | 36 | root | localhost | lacontrol | Query | 16 | update | replace into layers_la00.g00stat_enterpages (layer_id,id,hits,loads) values Peter>> (2,2048,2,2), Peter>> (2,1,60,60 | Peter>> | 39 | root | localhost | layers_la00 | Query | 0| update | INSERT INTO g03stat_404refs VALUES (6,76851,0,1,0) | Peter>> | 271 | titan | mail.local | counter | Query | 1| System lock | select Peter>> visitors, Peter>> visitors7d, Peter>> visitors30d, Peter>> | Peter>> | 273 | root | localhost | | Query | 0| | show processlist | Peter>> +-+---++-+-+--+-+--+ Peter>> As you see here the mysqldump moved to loading other table and the Peter>> replace passed, and now the other replace is waiting for insert. And Peter>> so on the thing continues with each table. MW> Ok, I see what you mean. This looks VERY strang
RE: BDB
When using the BDB tables and RedHat 6.2 or 7.0, should --skip-locking be used or not? I know it was recommended for earlier versions of RedHat, like version 5.x. Thanks in advance, Rick - / Rick Mehalick Senior Consultant / Shell Services International SSI-GPAX / Phone: 281-544-5092(WCK) / Fax:281-544-2646(WCK) / email: [EMAIL PROTECTED] - -Original Message- From: Michael Widenius [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 08, 2001 6:49 PM To: Peter Zaitsev Cc: [EMAIL PROTECTED] Subject: BDB Hi! > "Peter" == Peter Zaitsev <[EMAIL PROTECTED]> writes: Peter> Hello mysql, Peter> 1) Today I tried to reload my tables with backuing up and recovering Peter> whole database by mysqldump - it seems like it does not work - Peter> mysqld crashed during loading data back even after I've removed all Peter> bad tables and bdb logs to have a clean system. Peter> This looks like the same problem as I reported with insert test - Peter> BDB hanges/crashes during huge multiple insert queries. Peter> I've uploaded the mysqldump output wich crashes mysqld during load Peter> to the secret directory as layers_la00.sql.gz. Peter> The thing is after I've dumped the same data without the extended Peter> insert I could load it back without any problems. I will look at this as after the weekend. Peter> 2) Then I was loading the data from .sql file I saw the followings: Peter> onyx:/spylog/db # mysqladmin processlist | grep -v Sleep Peter> +-+---++-++--+-- -+-- + Peter> | Id | User | Host | db | Command| Time | State | Info | Peter> +-+---++-++--+-- -+-- + Peter> | 34 | root | localhost | la00| Field List | 494 | Repair by sorting | | Peter> | 36 | root | localhost | lacontrol | Query | 64 | System lock | insert into layers_la00.g00keywords (counter_id,keyword) values (106339,'RSBAC') | Peter> | 38 | root | localhost | la00| Field List | 468 | Waiting for table | | Peter> | 39 | root | localhost | layers_la00 | Query | 0| update | INSERT INTO g03dirs VALUES (110912,8288,'pictures/company/itartass/calendar')| Peter> | 81 | root | localhost | la00| Field List | 296 | Waiting for table | | Peter> | 121 | titan | php.local | counter | Query | 5| System lock | SELECT lsh.begin AS period, sh.hosts7d,sh.visitors7d Peter> FROM layers_la00.g00layers_stat_hits AS ls | Peter> | 125 | root | localhost | | Query | 0| | show processlist | Peter> +-+---++-++--+-- -+-- + Peter> as you see here one thread is doing repear as this happened after Peter> mysqld crash, but note first insert query in system lock state (I've Peter> saw several queries at this place, but all of them spent in system Peter> lock time several tenths of seconds) this can nothing to do with Peter> recovery (this table was just created by mysqldump couple of minutes Peter> before) - I habe the only explanation which looks ok the system lock Peter> takes place while many inserts are going to othe table (g03dirs) - as Peter> soon as the table changed all other queries which was in system lock Peter> state got a chance to run. The other thing is mysqldump does not uses Peter> lock tables to insert data so this looks like real perfomance Peter> (concurency) problem Some Peter> more examples: The "System Lock" means that you got some problem with fnctl() on your system. The fix is to restart mysqld with --skip-locking and without --enable-locking to ensure that fcntl() lock is not used. If you are using 'mysqldump --opt', it should generate locks for the tables. What command did you use ? Peter> onyx:/spylog/db # mysqladmin processlist | grep -v Sleep Peter> +-+---++-+-+--++ --+ Peter> | Id | User | Host | db | Command | Time | State | Info | Peter> +-+---++-+-+--++ --+ Peter> | 36 | root | localhost | lacontrol | Query | 384 | update | replace into layers_la00.g00stat_404refs (layer_id,id,visitors,loads,hits)
Re: BDB & Check tables
Hi! > "Peter" == Peter Zaitsev <[EMAIL PROTECTED]> writes: Peter> Hello Michael, Peter> Friday, January 26, 2001, 12:20:43 AM, you wrote: MW> Hi! >>> "Peter" == Peter Zaitsev <[EMAIL PROTECTED]> writes: Peter> Hello monty, Peter> As I remember according to documentation MYSQL should support check Peter> table on BDB tables: Peter> `CHECK TABLE' only works on `MyISAM' and `BDB' tables. On `MyISAM' Peter> tables it's the same thing as running `myisamchk -m table_name' on the Peter> table. MW> I tried to implement this; I had got everything coded when I MW> noticed the on can only check BDB tables when there is no active MW> transaction and one is the only one that is using the table. This MW> is impossible to guarantee with the current MySQL code; To do this we MW> have to add a new layer on top on transactions, which isn't something MW> we plan to do until in 4.x MW> I have now removed the notes about BDB tables in the CHECK command. Peter> OK. But just note to really use BDB tables in production people need Peter> to have a possibility to recover data if BDB table somehow got Peter> dammaged. I know there is a chance to recover data by alter table or Peter> if this does not help to do an mysqldump and restore (well of couse Peter> this is worse then with MYISAM there repair table sometimes helps even Peter> then mysqldump does not work). To do the repair you of couse need to Peter> find out something is going wrong and it's better to find this before Peter> the errors will block normal execution. Peter> As far as I know there is no tools now to fix BDB tables (the one Peter> provided with BDB does not work with MYSQL tables) so it was nice Peter> option anyway - to start other mysqld and check the table :) And as Peter> far as I know this is safe anyway as check table does not change Peter> anything in BDB ? I agree that we need to look at this soon. It would be easy to do checking just when BDB is started, but it's a bit hard to do when BDB is in use. Peter> Also - does not lock tables write enshures there are no other active Peter> transactions ? Currently the LOCK TABLE only ensures that no one will be able to execute another query that updates the table. The problem is that we need to also implement a list of tables that has an active transaction on the table and not allow the lock table to succeed until these transactions are taken care of. It will take a couple of weeks until Tim will have time to look at this. Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB & Check tables
Hello Michael, Friday, January 26, 2001, 12:20:43 AM, you wrote: MW> Hi! >> "Peter" == Peter Zaitsev <[EMAIL PROTECTED]> writes: Peter>> Hello monty, Peter>> As I remember according to documentation MYSQL should support check Peter>> table on BDB tables: Peter>> `CHECK TABLE' only works on `MyISAM' and `BDB' tables. On `MyISAM' Peter>> tables it's the same thing as running `myisamchk -m table_name' on the Peter>> table. MW> I tried to implement this; I had got everything coded when I MW> noticed the on can only check BDB tables when there is no active MW> transaction and one is the only one that is using the table. This MW> is impossible to guarantee with the current MySQL code; To do this we MW> have to add a new layer on top on transactions, which isn't something MW> we plan to do until in 4.x MW> I have now removed the notes about BDB tables in the CHECK command. OK. But just note to really use BDB tables in production people need to have a possibility to recover data if BDB table somehow got dammaged. I know there is a chance to recover data by alter table or if this does not help to do an mysqldump and restore (well of couse this is worse then with MYISAM there repair table sometimes helps even then mysqldump does not work). To do the repair you of couse need to find out something is going wrong and it's better to find this before the errors will block normal execution. As far as I know there is no tools now to fix BDB tables (the one provided with BDB does not work with MYSQL tables) so it was nice option anyway - to start other mysqld and check the table :) And as far as I know this is safe anyway as check table does not change anything in BDB ? Also - does not lock tables write enshures there are no other active transactions ? -- Best regards, Petermailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
