Re: ROLLBACK not working in Red Hat Linux 7 but does in RedHat Linux 6.2 (MySQL3.23.33)
* Irmund Thum <[EMAIL PROTECTED]> wrote on 25.02.01 17:20: > ERROR 1196: Warning: Some non-transactional changed tables couldn't be > rolled back > *** > so I'm not that expert knowing what "non-transactional changed tables" > exactly means You're probably using the RPM version or otherwise just don't have BDB support compiled in. If you do a "show table status" you'll see that the tables you just created with type=bdb actually are MyISAM or ISAM. -- Tomi Junnila <[EMAIL PROTECTED]> http://www.badzilla.net/~topeju/ Electronics and Information Technology, University of Turku, Finland - 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: ORDER BY problem and possibly others..
* J.M. Roth <[EMAIL PROTECTED]> wrote on 28.01.01 02:05: > I just installed the newest MySQL (3.23.32) with PHP 4.0.4pl1 (shared > module) on an Apache 1.3.12 (Linux). >... > $query = "SELECT * FROM $userstable ORDER BY when DESC LIMIT 0, 3"; > doesn't work: >From your query I think you upgraded from a 3.22.x version? When was not a reserved word in 3.22, but is so in 3.23. There have been a few hints how to both circumvent and fix this in 3.23, just search the archives. The thread was called something like "3.22 databases in 3.23 cause problems with fields named 'when'". -- Tomi Junnila <[EMAIL PROTECTED]> http://www.badzilla.net/~topeju/ Electronics and Information Technology, University of Turku, Finland - 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: MySQL-Warnings
* Martin Ramskogler <[EMAIL PROTECTED]> wrote on 26.01.01 12:36: > is there any way to disable all the MySQL-Warnings that are sent to my > visitors browsers when the database is down for a moment. I am using the > database to count the pageviews, and if the connection to the DB is > impossible, there should not be sent any warning to the visitors browser! Is > there any way to disable it via PHP or so? This is not a MySQL issue... If you prefix your mysql_connect(...) call in PHP with an "@", it will disable warnings for that function call. Then just check the return value of the call to make sure you get a proper handle, and if not, just don't try to do anything else with mysql (or prefix all of them with @, which isn't probably a very good idea for debugging). -- Tomi Junnila <[EMAIL PROTECTED]> http://www.badzilla.net/~topeju/ Electronics and Information Technology, University of Turku, Finland - 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: Key trouble
* Andrei Zmievski <[EMAIL PROTECTED]> wrote on 23.01.01 16:54: > mysql> show keys from ARTICLES; >... [Andrei's mail trimmed down quite a bit]: > | ARTICLES | 1 | Publication_ID|1 | Publication_ID| A > |NULL | NULL | NULL | | >... > | ARTICLES | 1 | Status|1 | Status| A > |NULL | NULL | NULL | | >... > mysql> explain select count(*) from ARTICLES where Status = 'completed'; >... > | ARTICLES | ref | Status| Status | 255 | const | 8587 | where used; >Using index | >... > mysql> explain select count(*) from ARTICLES where Publication_ID = 9; >... > | ARTICLES | ref | Publication_ID | Publication_ID | 4 | const | 8526 | where >used; Using index | >... > mysql> explain select count(*) from ARTICLES where Publication_ID = 9 and Status = >'completed'; >... > | ARTICLES | ALL | Publication_ID,Status | NULL |NULL | NULL | 10440 | where >used | >... > Why isn't it using index on the last query? MySQL shows "Using index" in the first two queries because it could find the whole result in one of the indexes in these cases (it only needed to look at the index and not the table). With your last query, there is no index which would contain both Publication_ID and Status in the first two key fields, and thus MySQL must also refer to the table. Cardinality probably also plays an issue here, but I'll leave describing that up to some more knowledgeable people (I can't remember that offhand and I do need to go and eat now). -- Tomi Junnila <[EMAIL PROTECTED]> http://www.badzilla.net/~topeju/ Electronics and Information Technology, University of Turku, Finland - 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
BDB rollback still not working in 3.23.32
>Description: Rollback still does not work with MySQL 3.23.32 and BDB 3.2.3h. This same problem has been encountered with earlier versions of MySQL as well. However, all hosts on which this has been noticed have been running Red Hat Linux 7.0, which could be the culprit. Several different libc versions have been tested with RH7. >How-To-Repeat: The short way: (%=regular user, $=root) 1. Build BDB 3.2.3h % tar xvfz db-3.2.3h.tar.gz % cd db-3.2.3h/build_unix % ../dist/configure --enable-cxx --enable-dynamic --enable-shared % make $ make install 2. Build MySQL % cd ../.. % tar xvfz mysql-3.23.32.tar.gz % cd mysql-3.23.32 % ./configure --enable-assembler --with-mysqld-user=mysql \ --with-unix-socket-path=/var/lib/mysql/mysql.sock \ --with-extra-charsets=complex --prefix=/ --exec-prefix=/usr \ --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share \ --localstatedir=/var/lib/mysql --infodir=/usr/info \ --includedir=/usr/include --mandir=/usr/man \ --with-berkeley-db=/usr/local/BerkeleyDB.3.2 \ '--with-comment=Tarball Source Build' % make 3. Run the test cases % make test The BDB test will fail in line 122, which tries to insert an integer (4) into table t1. This value has earlier been inserted into the same table, but this insert was rolled back so the record should not be there. >Fix: Not known. >Submitter-Id: >Originator: >Organization: Tomi Junnila <[EMAIL PROTECTED]> http://www.badzilla.net/~topeju/ Electronics and Information Technology, University of Turku, Finland > >MySQL support: none >Synopsis: BDB rollback still not working in 3.23.32 >Severity: serious >Priority: medium >Category: mysql >Class: sw-bug >Release: mysql-3.23.32 (Tarball Source Build) >Environment: For documentation on how BDB was installed, see above. System: Linux isis.yok.utu.fi 2.2.16-22isis1 #2 Sat Nov 4 20:36:36 EET 2000 i586 unknown Architecture: i586 Some paths: /usr/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.0) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 11 Nov 23 22:36 /lib/libc.so.6 -> libc-2.2.so -rwxr-xr-x1 root root 5072386 Jan 11 01:37 /lib/libc-2.2.so -rw-r--r--1 root root 24498288 Jan 11 01:35 /usr/lib/libc.a -rw-r--r--1 root root 178 Jan 11 01:35 /usr/lib/libc.so lrwxrwxrwx1 root root 10 Nov 1 19:52 /usr/lib/libc-client.a -> c-client.a Configure command: ./configure --enable-assembler --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --with-extra-charsets=complex --prefix=/ --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/info --includedir=/usr/include --mandir=/usr/man --with-berkeley-db=/usr/local/BerkeleyDB.3.2 '--with-comment=Tarball Source Build' - 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: 3.22 database on 3.23 with field names 'when' will causes errors
* Santeri Paavolainen <[EMAIL PROTECTED]> wrote on 23.01.01 13:05:
> >Fix:
> SELECTs can be worked around with table aliases, others not.
Oops, I forgot to include the cure. The first way to do this I encountered
is to simply mysqldump the whole table into a text file, then search and
replace "when" with something else, and finally drop the table and run the
text file into mysql.
Another way to do this might be with "create table temporary_table ({fields
valid in 3.23}) select {fields from old table with aliases} from old_table"
but I haven't tried this myself.
--
Tomi Junnila <[EMAIL PROTECTED]>
http://www.badzilla.net/~topeju/
Electronics and Information Technology,
University of Turku, Finland
-
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: 3.22 database on 3.23 with field names 'when' will causes errors
* Santeri Paavolainen <[EMAIL PROTECTED]> wrote on 23.01.01 13:05: > Using database created in 3.22 with a field named 'when' > causes errors. For example, When is a reserved word in 3.23. You're in for a nice little restructure... (been there, done that :-( ). -- Tomi Junnila <[EMAIL PROTECTED]> http://www.badzilla.net/~topeju/ Electronics and Information Technology, University of Turku, Finland - 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: Update and change a value
* Jamie <[EMAIL PROTECTED]> wrote on 23.01.01 11:51: > UPDATE users SET visits=visits+1, WHERE user_id='$user' AND > password='$password' If that comma after +1 is not a typo in the mail, then that's what's causing the problem. -- Tomi Junnila <[EMAIL PROTECTED]> http://www.badzilla.net/~topeju/ Electronics and Information Technology, University of Turku, Finland - 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: HELP with SQL statement
* Mike Podlesny <[EMAIL PROTECTED]> wrote on 19.01.01 17:52:
> Actually let me reword this because Tomi's answer won't quite work. The
> table has a field called Rating. A number 1 is stored to represent positive
> a 2 is stored for neutral and a 3 is stored for negative. I need to query
> that will query up all the number 1's (positives) and subtract all the
> number 3's (negatives) to give me a true rating. Any ideas?
Well, the most straightforward solution would be to first:
select @positives:=count(*) from {whatever-the-table-name-was-again} where rating=1;
then:
select @negatives:=count(*) from {table-name-here-again} where rating=3;
and finally, if you need to do the whole thing in SQL:
select @positives-@negatives;
This not very effective as you need two queries to do this (three if you
actually do need to do it in SQL). If you don't need to do it all in SQL,
you can just drop the "@positives:=" and "@negatives:=" parts from the
queries.
However, with your table structure it is still possible to do:
select sum(rating) from {table-name};
You only need to subtract 2 times the number of rows you received to get the
result you want.
Probably the most efficient way would be to rethink your table format to
have positive ratings as +1, neutrals as 0, and negatives as -1. Then you
could simply sum them all up without needing to subtract anything.
--
Tomi Junnila <[EMAIL PROTECTED]>
http://www.badzilla.net/~topeju/
Electronics and Information Technology,
University of Turku, Finland
-
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: HELP with SQL statement
* Mike Podlesny <[EMAIL PROTECTED]> wrote on 19.01.01 17:25: > I have a table called RATING and two fields one called POSITIVE and the > other called NEGATIVE. I need to write an SQL statement for my mySQL > database that will return the value of the total of POSITIVEs minues the > total of the NEGATIVEs. Err, how about select sum(positive-negative) from rating; ? -- Tomi Junnila <[EMAIL PROTECTED]> http://www.badzilla.net/~topeju/ Electronics and Information Technology, University of Turku, Finland - 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: MySQL FLOAT is flaky!
* Petras Virzintas <[EMAIL PROTECTED]> wrote on 15.01.01 23:55: > The column type "FLOAT" seems to be flaky in MySQL 2.23.24-beta. I > wouldn't mind if it was a rounding issue but the value 66.66 becomes > 66.69 and 77.77 becomes 77.75! Sounds to me like a typical binary rounding issue - when you store decimal values in a binary floating point value, the values are not rounded in decimal as you would expect but in binary. For example, it is not possible to denote 0.1 (ie, 10^-1) with binary floating point values. You will only get the approximation 1/16+1/32+1/256+1/512+1/4096+1/8192+1/65536+1/131072+... depending on how many bits you have available. I believe that in MySQL the column type you want to use is decimal(prec,scale) (if you know how many significant digits and digits after the decimal point you need), because these are stored as strings in the database, and thus no rounding occurs (until you use the values somewhere else by transforming them into floating-point values that is :-). -- Tomi Junnila <[EMAIL PROTECTED]> http://www.badzilla.net/~topeju/ Electronics and Information Technology, University of Turku, Finland - 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: comic strip SQL question: slow queries on left outer joins
* Soren Ragsdale <[EMAIL PROTECTED]> wrote on 11.01.01 11:30: > SELECT comics.tagline,votes.score FROM comics,users LEFT OUTER JOIN > votes ON comics._rowid=votes.comic AND votes.voter=30 WHERE > users._rowid=comics.author; I would try the simpler inner join to get the result you want: SELECT comics.tagline,votes.score FROM comics,votes WHERE votes.comic=comics._rowid AND votes.voter=30; It appears that you don't really need the users table here at all. This is probably (at least in part) what is slowing your queries down. Also check the output of EXPLAIN SELECT (rest of your query) to see how MySQL is parsing your query, what indices it is using etc. -- Tomi Junnila <[EMAIL PROTECTED]> http://www.badzilla.net/~topeju/ Electronics and Information Technology, University of Turku, Finland - 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: MySQL transaction problem (Again...)
* Carfield Yim <[EMAIL PROTECTED]> wrote on 11.01.01 06:19: > MySQL 3.23.29 or 3.23.30, with RedHat Linux 7.0, compile from tarball > source, can't rollback. > > Do you mean that it is platform?? There are errors build with RedHat 7.0? Hmm. It certainly does seem so as there have been a few reports after my initial report on December 4 when I encountered this problem. I do believe there would have been more if the problem was more widespread. I'm using RH7, and the versions I have noticed this behaviour in are 3.23.28, 3.23.29 and 3.23.30. I upgraded my glibc on December 21 from 2.1.92 to 2.2.9, and my gcc from 2.96-54 to 2.96-69. This did not help. I've tried compiling MySQL from the source RPMS (first the RedHat ones, hacking them with the correct version and Berkeley DB support; then the MySQL ones, putting the Berkeley DB support in them), and manually from the tarball sources. When I first encountered this, rollback did work for some tables but not others. Nowadays, it doesn't seem to work for any tables. This may be due to the change of MySQL and/or Berkeley DB versions, or the glibc/gcc update, or just some difference in the build itself (which I doubt). I don't know which change to blame as I didn't test rollback except with the table that rollback didn't work with initially, and only now tested rollback on other tables and noticed it didn't work at all anymore. For example, this used to work but now it doesn't anymore: create table tt (id integer) type=bdb; show table status; # Ensure it actually is BDB set autocommit=0; begin; insert into tt (id) values (55); rollback; select * from tt; # Earlier this didn't result in anything, nowadays it does # have the row id=55 -- Tomi Junnila <[EMAIL PROTECTED]> http://www.badzilla.net/~topeju/ Electronics and Information Technology, University of Turku, Finland - 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
