re: mysql hangs, restarts on odbc connect
Thank you, Egor! It looks like Red Hat released an update to its glibc libraries yesterday which fix this, so if anybody else is having problems, they should be able to fix it by updating. :) Sage On Thu, 2002-11-07 at 17:46, Egor Egorov wrote: Sage, Wednesday, November 06, 2002, 3:31:21 PM, you wrote: S I'm running MySQL 3.23.49 on Red Hat 7.3, with the latest kernel S (2.4.18-17.7.x) installed. It is running in smp mode on a dual P3 1.0 S gHz. S MySQL works perfectly fine when connecting from the localhost. However, S I have another machine that needs to connect via ODBC. The ODBC client S can connect, but as soon as it does, the connection is suddenly closed. S The same thing happens if I try to telnet to port 3306 on the host S machine. S Every time this happens, I find the following entry in mysqld.log: S Number of processes running now: 1 S mysqld process hanging, pid 906 - killed S 021106 15:51:41 mysqld restarted S /usr/libexec/mysqld: ready for connections S I have no idea what is causing this. Does anyone have any suggestions as S to what might be the cause, or at least what I should double-check? Your problem is described here: https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=75128 You can downgrade glibc or install the patched one or install MySQL server v3.23.53a -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ 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 - 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
mysql hangs, restarts on odbc connect
I'm running MySQL 3.23.49 on Red Hat 7.3, with the latest kernel (2.4.18-17.7.x) installed. It is running in smp mode on a dual P3 1.0 gHz. MySQL works perfectly fine when connecting from the localhost. However, I have another machine that needs to connect via ODBC. The ODBC client can connect, but as soon as it does, the connection is suddenly closed. The same thing happens if I try to telnet to port 3306 on the host machine. Every time this happens, I find the following entry in mysqld.log: Number of processes running now: 1 mysqld process hanging, pid 906 - killed 021106 15:51:41 mysqld restarted /usr/libexec/mysqld: ready for connections I have no idea what is causing this. Does anyone have any suggestions as to what might be the cause, or at least what I should double-check? - 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
AW: Order By Limit; Count
Hi,
Order By clause without Limit returns:-
A
B
C
C
C
C
C
C
M
N
T
W
Order By clause with Limit returns:-
C
C
C
C
C
C
M
N
T
W
Is there any way to fix this, so that the results with the limit comes
out starting with the A and then moves on to the next pages?
Hadn't thought about this before, but what it means (I guess) is that LIMIT
works on the selection, not on the presentation.
What I'm trying to say is that when you consider the sequence in which a
SELECT statement is carried out by the database, an ORDER BY is the second
but last thing that's done (the last being a further restriction of the
result set through a HAVING clause, if present). At the time of the ordering
you already have a result set for the query, and the database is now only
working on the way this result set is presented to the user.
I presume that LIMIT applies to the gathering of the result set, i.e. you
get the required number of rows from the query up to and including the WHERE
clause. Only then it gets ordered, but if your result set does not contain
the records with an 'A' they can't get ordered either. Don't know whether
I'm explaining this very well, but perhaps you get the idea.
...
Have gone and tested it now (3.23.42 on Win2K). Here is the SQL text and the
results:
--- cut
DROP DATABASE IF EXISTS ordertest;
CREATE DATABASE ordertest;
USE ordertest;
CREATE TABLE letters (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
letter CHAR NOT NULL
);
INSERT INTO letters
(letter)
VALUES
('C'),
('C'),
('C'),
('C'),
('C'),
('C'),
('M'),
('N'),
('T'),
('W'),
('A'),
('B');
SELECT letter
FROM letters
LIMIT 10;
++
| letter |
++
| C |
| C |
| C |
| C |
| C |
| C |
| M |
| N |
| T |
| W |
++
10 rows in set (0.00 sec)
SELECT letter
FROM letters
ORDER BY letter
LIMIT 10;
++
| letter |
++
| A |
| B |
| C |
| C |
| C |
| C |
| C |
| C |
| M |
| N |
++
10 rows in set (0.37 sec)
-- cut
Surprise, surprise, turns out MySQL behaves not the way I thought it would,
but rather the way you thought it should. Are you perhaps running a
different version? And could you perhaps show us your table structure(s) and
query so that we can find out what's really happening there?
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
AW: BDB table error
As I said, I don't know anything about BDB, but I don't see how they could possibly prevent chaos without locking both all table pages and all index pages that are being modified by a transaction. And indeed, that would make a deadlock possible for multi-phase updates on the same row and thereby quite neatly explain the behaviour you're seeing. Stupid question on the side (just being nosy): why do you need to generate a unique char string from the primary key? I mean, what is the benefit when it was unique already? An ugly solution to your problem, by the way, might be to split the table into two: if you don't really need the auto-incremented primary key for SELECTs, you could move it to a separate table that contains only this one field. The flow would then be: - insert row into generator table. - grab newly created primary key (autoincrement) and generate a unique 32 byte char string based on it. - insert data row with generated char string as primary key. - commit/rollback This would still give you the unique seed for the generation of your char string, and the insert into the real table would be atomic again. Depends on the needs of your application, of course, whether this is workable. And, yes, from a design point of view it IS ugly. cs -Ursprüngliche Nachricht- Von: Dana Powers [mailto:[EMAIL PROTECTED]] Gesendet: Sonntag, 16. September 2001 00:14 An: Christian Sage Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Betreff: 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
AW: Billionium Bug
Only to make sure we don't have any OS-specific I tried your test on my Win32 installation. Same result: --- snip mysql create table test (seconds bigint not null primary key); Query OK, 0 rows affected (0.00 sec) mysql insert into test values (998556700), (113720), (11); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql select * from test; ++ | seconds| ++ | 998556700 | | 11 | | 113720 | ++ 3 rows in set (0.00 sec) mysql select min(seconds) from test; +--+ | min(seconds) | +--+ |998556700 | +--+ 1 row in set (0.03 sec) mysql select max(seconds) from test; +--+ | max(seconds) | +--+ | 113720 | +--+ 1 row in set (0.00 sec) mysql status -- mysql Ver 11.15 Distrib 3.23.41, for Win95/Win98 (i32) --- snap Then I thought, maybe a problem with indexes, so I recreated the table without a primary key. Still same result. However, in the first case (with primary key), the select might not have used the index, so I tested for that. Indeed: explain came up with the comment --- snip +--+ | Comment | +--+ | Select tables optimized away | +--+ --- snap A quick search of the documentation did not yield any useful information. Therefore, I inserted an additional 20 rows into the table (resulted in 1.7MB data and 3.5MB index size), but still the same comment. So it appears that the min and max value is stored somewhere for the table or the index. To find out a little more, I recreated the table once again without an index and repeated the procedure. The result was: --- snip mysql select min(seconds) from test; +--+ | min(seconds) | +--+ |0 | +--+ 1 row in set (0.07 sec) mysql explain select min(seconds) from test; +---+--+---+--+-+--++---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--++---+ | test | ALL | NULL | NULL |NULL | NULL | 24 | | +---+--+---+--+-+--++---+ 1 row in set (0.00 sec) --- snap Now I got the expected full table scan. Same thing for 3 rows. But still the result is correct. The problem could still be with the port of MySQL Panos is using or with the table definition. Script seems to make it unlikely that it would be in the definition of the variable used for storing the results retrieved. I've wandered a bit off the original topic here, but since I think the results may still be interesting for some of you I'm posting this anyway. Cheers, Christian -Ursprüngliche Nachricht- Von: Jason Brooke [mailto:[EMAIL PROTECTED]] Gesendet: Sonntag, 9. September 2001 09:24 An: Panos Kalos; [EMAIL PROTECTED] Betreff: Re: Billionium Bug To Whom it may concern, I've noticed a bug with the Billionium. Now that we have hit 1 Billion seconds since the beginning of the Epoch mysql will NOT work properly with a MIN clause involving seconds before the Billionium. To clarify, I have a script that keeps track of the seconds of which the data was entered. I then use another script that pulls that data using a 'MIN(seconds)' statement to pull the oldest entry. This no longer works as it will only pull data after 1 billion seconds and disregarding anything prior. Im sure you can easily recreate this event by entering false info into a DB. Regards, Panos I'm not seeing this behaviour with 3.23.41 mysql show columns from test; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | seconds | int(11) | | MUL | 1 | | +-+-+--+-+-+---+ 1 row in set (0.00 sec) mysql select min(seconds) from test; +--+ | min(seconds) | +--+ |998556700 | +--+ 1 row in set (0.18 sec) mysql select max(seconds) from test; +--+ | max(seconds) | +--+ | 113720 | +--+ 1 row in set (0.00 sec) mysql status; -- mysql Ver 11.15 Distrib 3.23.41, for pc-linux-gnu (i686) - 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/
AW: SELECT-problem
Ville, as you are probably aware, there are no subselects in MySQL. Therefore, you will probably have to retrieve the additional waypoints through a second query. (As an aside: there may exist a solution using self-joins, but from looking at the problem I don't think so) I assume you will be using an API and a programming language. Then you could simply go and say (for Perl DBI): SELECT * FROM waypoints WHERE path = ? and (waypoint_nr = ? -1 or waypoint_nr = ? + 1) Parametrize that with the information from your first result set for each of its rows. Merge all the result sets retrieved and eliminate duplicate combinations of path and waypoint, and there you are. Pretty ugly but workable, I think. In fact using Perl hashes even easy to do. However, whether this approach is feasible for you depends entirely on what combination of API and language/tool you are using for your development. Also, there may be better solutions. If so, the list will probably point them out to you. ;-) Cheers, Christian Sage -Ursprüngliche Nachricht- Von: Ville Mattila [mailto:[EMAIL PROTECTED]] Gesendet: Samstag, 8. September 2001 18:17 An: MySQL-mailinglist Betreff: SELECT-problem Hi there, This is my problem now... I have a table containing different paths, like this: +--++---++ | Path | X | Y | WaypointNr | +--++---++ | P1 | 1 | 5 | 1 | | P1 | 2 | 6 | 2 | | P1 | 3 | 7 | 3 | | P1 | 8 | 3 | 4 | | P2 | 11 | 4 | 1 | | P2 | 7 | 3 | 2 | | P2 | 5 | 2 | 3 | | P2 | 2 | 1 | 4 | +--++---++ I need to draw a map from these paths, so I make a following query to get waypoints and paths located in defined area (where the corners are (3,2) and (7,6). mysql SELECT * FROM waypoints WHERE X 2 AND X 8 AND Y 1 AND Y 7; +--+---+---++ | Path | X | Y | WaypointNr | +--+---+---++ | P2 | 7 | 3 | 2 | | P2 | 5 | 2 | 3 | +--+---+---++ That's OK... but now I would like to get also those waypoints which ones are next to these results (on the same path). In this case, I want also points 1 and 4 on P2. How? Emm... Hope that you could understand even something. ;) - Ville . Ville Mattila Ikaalinen, Finland [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
