Re: Weird delete behavior on mysql 4.0 - rows not disappearing....
No, with the default transaction isolation level, REPEATABLE READ, that's how it is supposed to work. You've started a transaction in Window B, so Window B is immune to changes made in Window A until you finish the transaction in Window B. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html; I haven't explicitly started any transactions in Window B - it's select-only (with autocommit set to 0). Are you saying that even though transactions have happend and been committed in Window A, I won't be able to see those transactions in Window B? The relevant part of the documentation in the link you sent is, The query see[s] the changes made by exactly those transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by the transaction itself that issues the query. In otherwords, if you start a query (and it's a long running query), you won't see the results of any data committed by another session during the running of that query. Fine. That's expected. But if I am doing only queries (no transactions) via a connection, and no query is running when I commit data in another session, then the query-window should see the results of those changes. I suspect that the mysql client is implicitly starting a transaction when you do a set autocommit=0. Thus, any changes made by any other sessions won't be visible till you do a commit or rollback. Each time a commit or rollback is issued in the non-auto-commit session, you can see data changed by other sessions. Regardless, this is not a repeatable-read issue. I think it's a mysql client issue, and the fact that the client is creating transactions for you in the background. This is not how the Oracle client works - you are always in non-auto-commit mode (and I'd love to figure out how to set that - autocommit is so dangerous), and until you actually start a transaction with an update, insert, delete or select-for-update, no transaction is started, and you can see the changes made by other sessions once they've been committed (I tested SQL*Plus on Oracle 8i to make sure). David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird delete behavior on mysql 4.0 - rows not disappearing....
David Griffiths wrote: No, with the default transaction isolation level, REPEATABLE READ, that's how it is supposed to work. You've started a transaction in Window B, so Window B is immune to changes made in Window A until you finish the transaction in Window B. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html; I haven't explicitly started any transactions in Window B - it's select-only (with autocommit set to 0). Are you saying that even though transactions have happend and been committed in Window A, I won't be able to see those transactions in Window B? The key word is explicitly. You have implicitly started a transaction with your first SELECT, precisely because you turned AUTOCOMMIT off. That transaction continues until you COMMIT or ROLLBACK (or perform an action that implicitly commits http://dev.mysql.com/doc/mysql/en/innodb-implicit-command-or-rollback.html). That's the point of setting AUTOCOMMIT to off. If you only want to start transactions explicitly (with START TRANSACTION or BEGIN), then you need to leave AUTOCOMMIT on. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-and-autocommit.html. The relevant part of the documentation in the link you sent is, The query see[s] the changes made by exactly those transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by the transaction itself that issues the query. In otherwords, if you start a query (and it's a long running query), you won't see the results of any data committed by another session during the running of that query. Fine. That's expected. But if I am doing only queries (no transactions) via a connection, and no query is running when I commit data in another session, then the query-window should see the results of those changes. From the AUTOCOMMIT manual page cited above, In InnoDB, all user activity occurs inside a transaction. I suspect that the mysql client is implicitly starting a transaction when you do a set autocommit=0. Thus, any changes made by any other sessions won't be visible till you do a commit or rollback. Each time a commit or rollback is issued in the non-auto-commit session, you can see data changed by other sessions. With AUTOCOMMIT off, the transaction starts, in your case, with your first SELECT. Regardless, this is not a repeatable-read issue. I think it's a mysql client issue, and the fact that the client is creating transactions for you in the background. It's not the client. That's how InnoDB works. This is not how the Oracle client works - you are always in non-auto-commit mode (and I'd love to figure out how to set that - autocommit is so dangerous), and until you actually start a transaction with an update, insert, delete or select-for-update, no transaction is started, and you can see the changes made by other sessions once they've been committed (I tested SQL*Plus on Oracle 8i to make sure). I'll make no comments on how Oracle works, but what you seem to be describing is effectively what happens with AUTOCOMMIT on in MySQL. In general, I'd suggest that expecting any two RDBMSs (MySQL and Oracle, for example) to behave in exactly the same way will usually get you in trouble. David Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird delete behavior on mysql 4.0 - rows not disappearing....
I believe you - I'm just a but surprised. I guess I had a singular view of how a session should work based on Oracle. I would have expected that until you execute SQL that requires a commit or a rollback, you wouldn't be in a transaction. Unfortunately, if you have connections that are read and write, and one connection ends up being used for SELECTs only (just bad luck) , it's going to have an out-date view of the database. To me, a transaction is something you commit or rollback. You can't commit or rollback a SELECT unless you've done a locking-read. I guess Oracle is just smarter about it, only starting a transaction behind the scenes if you've actually done something that warrants a transaction. David Michael Stassen wrote: David Griffiths wrote: No, with the default transaction isolation level, REPEATABLE READ, that's how it is supposed to work. You've started a transaction in Window B, so Window B is immune to changes made in Window A until you finish the transaction in Window B. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html; I haven't explicitly started any transactions in Window B - it's select-only (with autocommit set to 0). Are you saying that even though transactions have happend and been committed in Window A, I won't be able to see those transactions in Window B? The key word is explicitly. You have implicitly started a transaction with your first SELECT, precisely because you turned AUTOCOMMIT off. That transaction continues until you COMMIT or ROLLBACK (or perform an action that implicitly commits http://dev.mysql.com/doc/mysql/en/innodb-implicit-command-or-rollback.html). That's the point of setting AUTOCOMMIT to off. If you only want to start transactions explicitly (with START TRANSACTION or BEGIN), then you need to leave AUTOCOMMIT on. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-and-autocommit.html. The relevant part of the documentation in the link you sent is, The query see[s] the changes made by exactly those transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by the transaction itself that issues the query. In otherwords, if you start a query (and it's a long running query), you won't see the results of any data committed by another session during the running of that query. Fine. That's expected. But if I am doing only queries (no transactions) via a connection, and no query is running when I commit data in another session, then the query-window should see the results of those changes. From the AUTOCOMMIT manual page cited above, In InnoDB, all user activity occurs inside a transaction. I suspect that the mysql client is implicitly starting a transaction when you do a set autocommit=0. Thus, any changes made by any other sessions won't be visible till you do a commit or rollback. Each time a commit or rollback is issued in the non-auto-commit session, you can see data changed by other sessions. With AUTOCOMMIT off, the transaction starts, in your case, with your first SELECT. Regardless, this is not a repeatable-read issue. I think it's a mysql client issue, and the fact that the client is creating transactions for you in the background. It's not the client. That's how InnoDB works. This is not how the Oracle client works - you are always in non-auto-commit mode (and I'd love to figure out how to set that - autocommit is so dangerous), and until you actually start a transaction with an update, insert, delete or select-for-update, no transaction is started, and you can see the changes made by other sessions once they've been committed (I tested SQL*Plus on Oracle 8i to make sure). I'll make no comments on how Oracle works, but what you seem to be describing is effectively what happens with AUTOCOMMIT on in MySQL. In general, I'd suggest that expecting any two RDBMSs (MySQL and Oracle, for example) to behave in exactly the same way will usually get you in trouble. David Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird delete behavior on mysql 4.0 - rows not disappearing....
If you are NOT in autocommit mode, your connection (or the server, it doesn't matter which) starts a transaction *when you issue your first command*. Every command you issue on that connection is in that initial transaction until you EXPLICITLY commit or rollback (or do something else that commits or rolls-back your transactions like ALTER TABLE) . At that point a new transaction is automatically started when you issue your next command. If I remember correctly, closing a connection with a pending transaction defaults to a ROLLBACK. That way if a transaction is left incomplete due to communications failure, you maintain a consistent database. If autocommit is enabled (SET autocommit=1) then each command executes within it's own mini-transaction (one little, tight transaction wrapped around each statement). Each SELECT can see what every other INSERT, UPDATE, or DELETE has done (assuming their transactions are committed) because it is not already inside a pending transaction. This is the default mode for user interaction for nearly every database product I have used. With autocommit active, you are required to explicitly issue a START TRANSACTION if you want a transaction that includes several commands. Are you sure that's not how Oracle operates, too? I ask because MS SQL acts the same as MySQL when it comes to autocommits Shawn Green Database Administrator Unimin Corporation - Spruce Pine David Griffiths [EMAIL PROTECTED] wrote on 09/01/2005 12:33:55 PM: I believe you - I'm just a but surprised. I guess I had a singular view of how a session should work based on Oracle. I would have expected that until you execute SQL that requires a commit or a rollback, you wouldn't be in a transaction. Unfortunately, if you have connections that are read and write, and one connection ends up being used for SELECTs only (just bad luck) , it's going to have an out-date view of the database. To me, a transaction is something you commit or rollback. You can't commit or rollback a SELECT unless you've done a locking-read. I guess Oracle is just smarter about it, only starting a transaction behind the scenes if you've actually done something that warrants a transaction. David Michael Stassen wrote: David Griffiths wrote: No, with the default transaction isolation level, REPEATABLE READ, that's how it is supposed to work. You've started a transaction in Window B, so Window B is immune to changes made in Window A until you finish the transaction in Window B. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html; I haven't explicitly started any transactions in Window B - it's select-only (with autocommit set to 0). Are you saying that even though transactions have happend and been committed in Window A, I won't be able to see those transactions in Window B? The key word is explicitly. You have implicitly started a transaction with your first SELECT, precisely because you turned AUTOCOMMIT off. That transaction continues until you COMMIT or ROLLBACK (or perform an action that implicitly commits http://dev.mysql.com/doc/mysql/en/innodb-implicit-command-or- rollback.html). That's the point of setting AUTOCOMMIT to off. If you only want to start transactions explicitly (with START TRANSACTION or BEGIN), then you need to leave AUTOCOMMIT on. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-and-autocommit.html. The relevant part of the documentation in the link you sent is, The query see[s] the changes made by exactly those transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by the transaction itself that issues the query. In otherwords, if you start a query (and it's a long running query), you won't see the results of any data committed by another session during the running of that query. Fine. That's expected. But if I am doing only queries (no transactions) via a connection, and no query is running when I commit data in another session, then the query-window should see the results of those changes. From the AUTOCOMMIT manual page cited above, In InnoDB, all user activity occurs inside a transaction. I suspect that the mysql client is implicitly starting a transaction when you do a set autocommit=0. Thus, any changes made by any other sessions won't be visible till you do a commit or rollback. Each time a commit or rollback is issued in the non-auto-commit session, you can see data changed by other sessions. With AUTOCOMMIT off, the transaction starts, in your case, with your first SELECT. Regardless, this is not a repeatable-read issue. I think it's a mysql client issue, and the fact that the client is creating transactions for you
Re: Weird delete behavior on mysql 4.0 - rows not disappearing....
Yah, I tested in SQL*Plus - one window could see inserts, updates and
deletes that had been committed in another window (in which a commit or
rollback had not been issued). I ran the test again - delete data from a
table in one window and commit the change, and a select in the other
window displays the results.
Note that SQL*Plus by default does not auto-commit, but the key elements
of the test are the same. Data committed in one session is visible in
another session once committed.
In Oracle/SQL*Plus, data committed in session A will show up in Session
B if Session B has an open transaction. Here's the example (using
session A and B).
Session A:
insert into temp_table (col1) values ('a');
Session B:
insert into temp_table (col1) values ('b');
At this point, neither is committed, and neither session can see what's
the other has done (the left hand doesn't know what the right is doing,
so to speak).
Session A:
commit;
Session B:
SQL select * from temp_table;
C
-
b
a
Session B has an open transaction, yet can see the data that was
committed in another transaction. It's view of the data is, Show me all
the data that has been committed to the database at the point where I
started my query, plus all changes that I've made yet not committed or
rolled back.
Oracle runs in READ COMMITTED (the above), while INNODB runs in
REPEATABLE READ. Big difference. And I (stupidly) assumed they ran as
the same transaction isolation level.
Learn something new every day.
David
[EMAIL PROTECTED] wrote:
If you are NOT in autocommit mode, your connection (or the server, it
doesn't matter which) starts a transaction *when you issue your first
command*. Every command you issue on that connection is in that
initial transaction until you EXPLICITLY commit or rollback (or do
something else that commits or rolls-back your transactions like ALTER
TABLE) . At that point a new transaction is automatically started when
you issue your next command. If I remember correctly, closing a
connection with a pending transaction defaults to a ROLLBACK. That way
if a transaction is left incomplete due to communications failure, you
maintain a consistent database.
If autocommit is enabled (SET autocommit=1) then each command
executes within it's own mini-transaction (one little, tight
transaction wrapped around each statement). Each SELECT can see what
every other INSERT, UPDATE, or DELETE has done (assuming their
transactions are committed) because it is not already inside a pending
transaction. This is the default mode for user interaction for nearly
every database product I have used. With autocommit active, you are
required to explicitly issue a START TRANSACTION if you want a
transaction that includes several commands.
Are you sure that's not how Oracle operates, too? I ask because MS SQL
acts the same as MySQL when it comes to autocommits
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
David Griffiths [EMAIL PROTECTED] wrote on 09/01/2005 12:33:55 PM:
I believe you - I'm just a but surprised. I guess I had a singular view
of how a session should work based on Oracle. I would have expected
that
until you execute SQL that requires a commit or a rollback, you
wouldn't
be in a transaction. Unfortunately, if you have connections that are
read and write, and one connection ends up being used for SELECTs only
(just bad luck) , it's going to have an out-date view of the database.
To me, a transaction is something you commit or rollback. You can't
commit or rollback a SELECT unless you've done a locking-read. I guess
Oracle is just smarter about it, only starting a transaction behind the
scenes if you've actually done something that warrants a transaction.
David
Michael Stassen wrote:
David Griffiths wrote:
No, with the default transaction isolation level, REPEATABLE READ,
that's how it is supposed to work. You've started a transaction in
Window B, so Window B is immune to changes made in Window A until
you
finish the transaction in Window B. See the manual for details
http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html;
I haven't explicitly started any transactions in Window B - it's
select-only (with autocommit set to 0). Are you saying that even
though
transactions have happend and been committed in Window A, I won't be
able
to see those transactions in Window B?
The key word is explicitly. You have implicitly started a
transaction with your first SELECT, precisely because you turned
AUTOCOMMIT off. That transaction continues until you COMMIT or
ROLLBACK (or perform an action that implicitly commits
http://dev.mysql.com/doc/mysql/en/innodb-implicit-command-or-
rollback.html).
That's the point of setting AUTOCOMMIT to off. If you only want to
start transactions explicitly (with START TRANSACTION or BEGIN), then
you need to leave AUTOCOMMIT on. See the manual for details
Weird delete behavior on mysql 4.0 - rows not disappearing....
I just discovered some weird behaviour with MySQL 4.0 (4.0.24 and
4.0.18) using InnoDB.
If you have two connections to mysql (I use the mysql client), one of
which has autocommit turned on, an the other turned off, a row deleted
from the client with autocommit turned on still shows up in the client
with autocommit turned off, even after a commit.
That's complicated, so here's an example.
CREATE TABLE bug_find (col1 VARCHAR(10) NOT NULL);
Now open two windows (I'll call them Window A and Window B).
Leave Window A alone (I am assuming your client is in auto-commit mode).
In Window B, type,
SET autocommit = 0;
In Window A, type
INSERT INTO bug_find (col1) VALUES ('a');
This should be committed automatically.
In Window B, type
SELECT * from bug_find;
The column should be there.
In Window A, type,
DELETE FROM bug_find;
Again, this should be committed.
In Window B, type,
SELECT * FROM bug_find;
Whoops - still there, even though it's been removed.
In Window A, type,
commit;
In Window B, type,
SELECT * FROM bug_find;
Still there.
To make it disappear from Window B, type,
commit;
That makes no sense. The changes Window B sees (that are made by Window
A) should not depend on issuing a commit - it has to see any data
committed by Window A (unless it's trying to avoid dirty reads, which
isn't the case here).
If Window B is in autocommit mode, you see the deletion right away. It
seems to be the autocommit=0 that's screwing stuff up. I haven't tested
this with the JDBC drivers, or with the Query Browser, or anything else.
It may just be a MySQL client issue.
This is a big problem with data consistency. Note that this bug also
exists for updates (any updates made in Window A are not seen by Window
B until Window B issues a commit). Also, turning autocommit off in a
session half way, and the same behaviour happens.
Is this a known bug?
David.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird delete behavior on mysql 4.0 - rows not disappearing....
David Griffiths wrote:
I just discovered some weird behaviour with MySQL 4.0 (4.0.24 and
4.0.18) using InnoDB.
If you have two connections to mysql (I use the mysql client), one of
which has autocommit turned on, an the other turned off, a row deleted
from the client with autocommit turned on still shows up in the client
with autocommit turned off, even after a commit.
That's complicated, so here's an example.
CREATE TABLE bug_find (col1 VARCHAR(10) NOT NULL);
Now open two windows (I'll call them Window A and Window B).
Leave Window A alone (I am assuming your client is in auto-commit mode).
In Window B, type,
SET autocommit = 0;
In Window A, type
INSERT INTO bug_find (col1) VALUES ('a');
This should be committed automatically.
In Window B, type
SELECT * from bug_find;
The column should be there.
In Window A, type,
DELETE FROM bug_find;
Again, this should be committed.
In Window B, type,
SELECT * FROM bug_find;
Whoops - still there, even though it's been removed.
In Window A, type,
commit;
In Window B, type,
SELECT * FROM bug_find;
Still there.
To make it disappear from Window B, type,
commit;
That makes no sense. The changes Window B sees (that are made by Window
A) should not depend on issuing a commit - it has to see any data
committed by Window A (unless it's trying to avoid dirty reads, which
isn't the case here).
No, with the default transaction isolation level, REPEATABLE READ, that's
how it is supposed to work. You've started a transaction in Window B, so
Window B is immune to changes made in Window A until you finish the
transaction in Window B. See the manual for details
http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html
If Window B is in autocommit mode, you see the deletion right away. It
seems to be the autocommit=0 that's screwing stuff up. I haven't tested
this with the JDBC drivers, or with the Query Browser, or anything else.
It may just be a MySQL client issue.
Each time you commit, you end the transaction and start a new one. The new
transaction gets a new snapshot of the data, so it sees previously committed
changes. With autocommit on, you are doing that automatically with every
statement. With autocommit off, you start a transaction with your first
select that doesn't end till you commit.
This is a big problem with data consistency. Note that this bug also
exists for updates (any updates made in Window A are not seen by Window
B until Window B issues a commit). Also, turning autocommit off in a
session half way, and the same behaviour happens.
I think you have it backwards. REPEATABLE READ transactions assure that for
the duration of a transaction, the only changes to your copy of the data are
the changes you make.
Is this a known bug?
It's not a bug.
David.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird delete behavior on mysql 4.0 - rows not disappearing....
On Wed, Aug 31, 2005 at 11:18:40PM -0400, Michael Stassen wrote: No, with the default transaction isolation level, REPEATABLE READ, that's how it is supposed to work. You've started a transaction in Window B, so Window B is immune to changes made in Window A until you finish the transaction in Window B. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html Is this a known bug? It's not a bug. Oracle works in a similar fashion. -Jason Martin -- Never eat anything bigger than your head. This message is PGP/MIME signed. pgpnWgmFXjYfU.pgp Description: PGP signature
Re: Disappearing .frm files ?
Geoffrey, Chris, - Original Message - From: Chris [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, May 01, 2005 5:26 AM Subject: Re: Disappearing .frm files ? Geoffrey R. Thompson wrote: ... Things have been fine until recently, when the .frm files for these tables mysteriously disappeared from the mysql data directory (even though the underlying data for the tables was still intact in the separate InnoDB data file). ... As far as I'm aware InnoDB doesn't use the .frm files, those are MyISAM only. So, if you converted the tables to InnoDB, the .frm files would disappear. I know almost nothing about this, but I'd say you had a better chance of someone in the know answering you if the question was a little clearer. Include some examples maybe, errors you've received, Queries you ran to convert the tables... Chris InnoDB does use .frm files. Did you use symlinking? There might be a bug there. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL Network from http://www.mysql.com/network/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Disappearing .frm files ?
I posted a few days ago, but didn't get any responses. I'm hopeful someone has seen this, and can offer advice. We recently converted some tables from MyISAM to InnoDB because the need had arisen for transactional support. Things have been fine until recently, when the .frm files for these tables mysteriously disappeared from the mysql data directory (even though the underlying data for the tables was still intact in the separate InnoDB data file). We replaced the .frm files from our prod database (we have identical dev and prod) - and all was well again - but I am curious as to how these files could disappear? I would think if someone had errantly dropped the tables, the data would be gone also. Any advice or guidance would be greatly appreciated. Thanks, Geoff Thompson Avaion Support [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] http://www.avaion.com http://www.avaion.com/
Re: Disappearing .frm files ?
Geoffrey R. Thompson wrote: ... Things have been fine until recently, when the .frm files for these tables mysteriously disappeared from the mysql data directory (even though the underlying data for the tables was still intact in the separate InnoDB data file). ... As far as I'm aware InnoDB doesn't use the .frm files, those are MyISAM only. So, if you converted the tables to InnoDB, the .frm files would disappear. I know almost nothing about this, but I'd say you had a better chance of someone in the know answering you if the question was a little clearer. Include some examples maybe, errors you've received, Queries you ran to convert the tables... Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB .frm files disappearing?
We have been using MyISAM tables with MySQL merrily for about 18 months. Recently we upgraded to MySQL 4.1, and even more recently, we converted some of our MyISAM tables (which needed transactional support) to InnoDB. After some configuration issues - the worst of which was the need to use a symbolic link to redirect the data directories for MySQL to another disk partition (our /usr/lib directory did not have sufficient space allocated to support the data stores), we got things working, and they have been working for about a month. Today, however, we lost all of the InnoDB tables in one of our databases. Upon examining the MySQL data directory, we discovered that the .frm files for these three tables were gone (although the ibdata1 file was still there). These same tables were still working fine in another of our databases (dev1 vs. dev2 - both of which share the same ibdata1 file), so we copied the .frm files from that database's data directory over, and once we did this, the tables re-appeared, complete with the correct data that had been originally loaded into these tables prior to their disappearing. So, it appears that while the data file was fine, the .frm files were somehow deleted. Anyone seen this happen before? Any insights would be very much appreciated! Regards, Geoff Thompson Avaion Support [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] http://www.avaion.com http://www.avaion.com/
RE: disappearing data - please help!
Lee wrote: Are you escaping the data you insert? If you have apostrophies eg. Sheryl's Website, you could have problem if you do not escape special chars. See if it happens with just aa (without the quotes) in all the fields in the record...then try unescaped words with apostrophies. I'm not, and I should be. It not the cause of my data disappearing problem because my test data was safe, but I shouldn't assume that customer-entered data will be safe. Thanks for the reminder. A nasty is case sensitivity. Some MYSQl vers. (esp. unix vers.) install with case sensivity turned on - so make sure all your table names and stuff match case. I'm used to that with Unix - everything is case-matched. Par down the PHP or what ever script you're using to the minimum ie. make the test case as blatently simple SQL as possible. I can't reproduce it with a simple case. It's an intermittant problem, and it seems to be related to some complexity buried in my program. - Sheryl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: disappearing data - please help!
-Original Message- From: Sheryl (Permutations Software) [mailto:[EMAIL PROTECTED] Sent: maandag 31 januari 2005 14:44 To: 'leegold'; mysql@lists.mysql.com Subject: RE: disappearing data - please help! Par down the PHP or what ever script you're using to the minimum ie. make the test case as blatently simple SQL as possible. I can't reproduce it with a simple case. It's an intermittant problem, and it seems to be related to some complexity buried in my program. Hello Sheryl, Well, just to rule out the obvious, what client library did you link your PHP to? You can see that running phpinfo(). I mean, if you ever upgraded from, say, MySQL 3.23, to, say, 4.0.18, the C headers of your client libraries will have changed, and everything which is statically linked to them, like PHP, should be recompiled. If your client library is at 4.0.18, and MySQL itself at, say, 4.0.23, you would still be okay; but with an older client library, you may expect weirdness. ;) I'm sure you already looked into this; but just in case you didn't... - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: disappearing data
Sheryl Canter wrote: I've got a weird problem that's driving me nuts. I'm updating a set of scripts for a customer database that supports sending out software registration codes in real time. I've had this working for some time now, but I'm having the most frustrating problem. Data I've inserted simply VANISHES. Sheryl: A sure way to fix weird problems like this is to get organized a bit. Here is how I would approach this problem: * Make sure all of your queries are being sent through the same interface in your script. The way I accomplish this in mine is to create a subroutine called safe_query() and use only that to issue a query. This way you can control a lot of things, such as error checking, logging, timing, etc. * Make sure to check for errors diligently. * Make your version of safe_query() logs everything query it is sending to the client * Study the logs thoroughly and with a clear mind ( good sleep strongly recommended) * If after implementing the above steps the problem does not become obvious, run mysqld with --log option and capture every command it receives. You could indeed have a goblin script/cron-job that deletes the records or something crazy like that, and this will help you detect it. -- Sasha Pachev AskSasha Linux Consulting http://www.asksasha.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
disappearing data
I've got a weird problem that's driving me nuts. I'm updating a set of scripts for a customer database that supports sending out software registration codes in real time. I've had this working for some time now, but I'm having the most frustrating problem. Data I've inserted simply VANISHES. I've added extensive debugging code to these scripts to track what's going on - almost line-by-line - and I see NOTHING wrong. The best I can figure is that the data exists until the script completes, and then (sometimes but not always) it simply disappears. I get no error messages of any kind, and I have confirmed that every step of the way things are happening as they should. Execution is in the order I expect, the variables hold the values I expect, etc. For example, the echo statements will say that order #1001 or whatever was inserted, but when I go to look after the script completes, it's not there! It's not my looking program, either - I've tried two. In phpMyAdmin I'll look for ever record inserted in the last two days and there is simply nothing there. I also have a custom program that displays sales information that similarly shows the order as vanished. And of course, the total number of records doesn't change - the record that MySQL says it inserted is definitely not there. What makes this all the more mysterious is that it happens intermittently. Sometimes when the script completes and reports success, the record really is there, and sometimes it's not. I am going crazy with this. Does this sound familiar to anyone? Do anyone have any idea what can be going on? One other bit of weirdness that may be related. The scripts are in a dozen ore more different files. (This is all CGI-PHP on an Apache server.) One of the things I was doing during this update was to go through all the scripts and be more consistent about error handling. There was one file where in the mysql_query I was specifying the query but not the link (letting it default - I'm only using one database). I tried adding the connection because it's in all the other files, but I couldn't. Whenever I tried, I got a warning and then an error about it being a bad link. But this is not a bad link. It's the same link I'm using in every file in this database application. Is my computer haunted? Can anyone help?! - Sheryl
disappearing data - please help!
I've got a weird problem that's driving me nuts. I'm updating a set of scripts for a customer database that supports sending out software registration codes in real time. I've had this working for some time now, but I'm having the most frustrating problem. Data I've inserted simply VANISHES. I've added extensive debugging code to these scripts to track what's going on - almost line-by-line - and I see NOTHING wrong. The best I can figure is that the data exists until the script completes, and then (sometimes but not always) it simply disappears. I get no error messages of any kind, and I have confirmed that every step of the way things are happening as they should. Execution is in the order I expect, the variables hold the values I expect, etc. For example, the echo statements will say that order #1001 or whatever was inserted, but when I go to look after the script completes, it's not there! It's not my looking program, either - I've tried two. In phpMyAdmin I'll look for ever record inserted in the last two days and there is simply nothing there. I also have a custom program that displays sales information that similarly shows the order as vanished. And of course, the total number of records doesn't change - the record that MySQL says it inserted is definitely not there. What makes this all the more mysterious is that it happens intermittently. Sometimes when the script completes and reports success, the record really is there, and sometimes it's not. I am going crazy with this. Does this sound familiar to anyone? Do anyone have any idea what can be going on? One other bit of weirdness that may be related. The scripts are in a dozen ore more different files. (This is all CGI-PHP on an Apache server.) One of the things I was doing during this update was to go through all the scripts and be more consistent about error handling. There was one file where in the mysql_query I was specifying the query but not the link (letting it default - I'm only using one database). I tried adding the connection because it's in all the other files, but I couldn't. Whenever I tried, I got a warning and then an error about it being a bad link. But this is not a bad link. It's the same link I'm using in every file in this database application. Is my computer haunted? Can anyone help?! - Sheryl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: disappearing data - please help!
On Sun, Jan 30, 2005 at 03:28:56PM -0500, Sheryl (Permutations Software) wrote: but I'm having the most frustrating problem. Data I've inserted simply VANISHES. What is the setting for AutoCommit? If it is 0, are you sure that commit is being called before the session ends? -Jason Martin -- Felinious Assault: Striking someone with a cat. This message is PGP/MIME signed. pgp7rOTL5oXlH.pgp Description: PGP signature
RE: disappearing data - please help!
I thought about this possibility, but I'm not using a database type with transactions. It's just the default. I don't know where I'd check AutoCommit settings (or it they even apply to the default database type). It doesn't refer to this in phpinfo.php. - Sheryl -Original Message- From: Jason Martin [mailto:[EMAIL PROTECTED] Sent: Sunday, January 30, 2005 3:38 PM To: mysql@lists.mysql.com Subject: Re: disappearing data - please help! On Sun, Jan 30, 2005 at 03:28:56PM -0500, Sheryl (Permutations Software) wrote: but I'm having the most frustrating problem. Data I've inserted simply VANISHES. What is the setting for AutoCommit? If it is 0, are you sure that commit is being called before the session ends? -Jason Martin -- Felinious Assault: Striking someone with a cat. This message is PGP/MIME signed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: disappearing data - please help!
On Sun, Jan 30, 2005 at 04:03:37PM -0500, Sheryl (Permutations Software) wrote: transactions. It's just the default. I don't know where I'd check AutoCommit settings (or it they even apply to the default database type). It doesn't refer to this in phpinfo.php. Try doing this immediately after inserting the data: mysql_query(COMMIT, $this-dbh); and see if that changes anything. -Jason Martin -- My karma ran over my dogma This message is PGP/MIME signed. pgpplCPHXl079.pgp Description: PGP signature
RE: disappearing data - please help!
-Original Message- From: Sheryl (Permutations Software) [mailto:[EMAIL PROTECTED] Sent: zondag 30 januari 2005 21:31 To: mysql@lists.mysql.com Subject: disappearing data - please help! What makes this all the more mysterious is that it happens intermittently. Sometimes when the script completes and reports success, the record really is there, and sometimes it's not. I am going crazy with this. Sounds like a concurrency problem to me. Is your code ACID (Atomicity, Concurrency, Isolation, Durability) safe? I do not think it is a commit problem. For one, because PHP/Perl client interfaces almost always default to auto-commit (so you would have to have disabled it explicitely); and, for two, because, as long as your MySQL server still runs, the data is transparently present (whether physically saved to disk or not). I tried adding the connection because it's in all the other files, but I couldn't. Whenever I tried, I got a warning and then an error about it being a bad link. But this is not a bad link. It's the same link I'm using in every file in this database application. I take it by link you mean something which describes your database:user:password, etc? Please, show the error. And you are not sharing the same database handle for multiple connection, btw, right? - Mark System Administrator Asarian-host.org --- If you were supposed to understand it, we wouldn't call it code. - FedEx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: disappearing data - please help!
From: Sheryl (Permutations Software) [mailto:[EMAIL PROTECTED] Sent: zondag 30 januari 2005 21:31 To: mysql@lists.mysql.com Subject: disappearing data - please help! What makes this all the more mysterious is that it happens intermittently. Sometimes when the script completes and reports success, the record really is there, and sometimes it's not. I am going crazy with this. ...snip... Are you escaping the data you insert? If you have apostrophies eg. Sheryl's Website, you could have problem if you do not escape special chars. See if it happens with just aa (without the quotes) in all the fields in the record...then try unescaped words with apostrophies. A nasty is case sensitivity. Some MYSQl vers. (esp. unix vers.) install with case sensivity turned on - so make sure all your table names and stuff match case. Par down the PHP or what ever script you're using to the minimum ie. make the test case as blatently simple SQL as possible. Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Privileges are disappearing...
Hi, I have a strange problem with priviledges. They just stop working. The tables have the data in them. But they don't work. So I do another GRANT And then they work fine, until the next day (not sure about the timing) when they have to be GRANT'ed all over again. There doesn't seem to be any pattern to this. It happens more often on some dBs than others. This is something that's be going on for a while now. It even caused me to get un-lazy and upgrade to 4.0.13 which didn't seem to help. The dBs are all dumped every night. I connect via DSL so I have a different IP, but I don't think that's an issue, here is the GRANT: GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON DbNAME.* TO YYY@'%.t-dialin.net' -- To catch the different IPs. IDENTIFIED BY 'XXX'; I thought it may be a MyODBC issue, but it also doesn't work in PERL/DBI until I do the GRANT again. flush priviledges doesn't seem to have any effect. Only another run of the GRANT. I should specify that it doesn't lose all GRANT info as everything connecting from localhost (but not localhost) is fine, just my connections from outside the server. Any ideas? Thanks!! Tosh -- Tosh Cooey Twelve Hundred Group http://www.1200group.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
heap tables keep on disappearing!
I have a mysql table that I refer to a lot and needs to be quick. I set it as a HEAP table because of the speed (and HEAP tales ARE fast. zip baaannn). Anyway, whenever I restart the server the contents of the heap tables disappear! What the hell? But I read the manual and it seems that heap tables stay in memory and not disk. The particular table I am using doesn't change very often (once every three months or so ).Is there a way that I can somehow keep the table from being erased on server restarts? Is the best way perhaps copying the table to a heap table every time the server starts up? - Steve - 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: heap tables keep on disappearing!
Store it in another table and have a start up script that creates a heap once the server is started ? Jerry - Original Message - From: Steve Quezadas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, March 06, 2003 7:41 PM Subject: heap tables keep on disappearing! I have a mysql table that I refer to a lot and needs to be quick. I set it as a HEAP table because of the speed (and HEAP tales ARE fast. zip baaannn). Anyway, whenever I restart the server the contents of the heap tables disappear! What the hell? But I read the manual and it seems that heap tables stay in memory and not disk. The particular table I am using doesn't change very often (once every three months or so ).Is there a way that I can somehow keep the table from being erased on server restarts? Is the best way perhaps copying the table to a heap table every time the server starts up? - Steve - 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: heap tables keep on disappearing!
Or just give MySQL plenty of ram and trust it to keep frequently used data in ram - which it does. Prime the system by doing some form of search which forces all the rows into memory (Select * from table where unindexedfield = somethingimpossible), the do a speed test on this versus a heap table. Wouldn't surprise me if they came out nearly the same. - Original Message - Store it in another table and have a start up script that creates a heap once the server is started ? Jerry From: Steve Quezadas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, March 06, 2003 7:41 PM Subject: heap tables keep on disappearing! I have a mysql table that I refer to a lot and needs to be quick. I set it as a HEAP table because of the speed (and HEAP tales ARE fast. zip baaannn). Anyway, whenever I restart the server the contents of the heap tables disappear! What the hell? But I read the manual and it seems that heap tables stay in memory and not disk. The particular table I am using doesn't change very often (once every three months or so ).Is there a way that I can somehow keep the table from being erased on server restarts? Is the best way perhaps copying the table to a heap table every time the server starts up? - Steve - 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 - 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: heap tables keep on disappearing!
At 11:41 -0800 3/6/03, Steve Quezadas wrote: I have a mysql table that I refer to a lot and needs to be quick. I set it as a HEAP table because of the speed (and HEAP tales ARE fast. zip baaannn). Anyway, whenever I restart the server the contents of the heap tables disappear! What the hell? But I read the manual and it seems that heap tables stay in memory and not disk. It surprises you that HEAP tables behave as documented? :-) The particular table I am using doesn't change very often (once every three months or so ).Is there a way that I can somehow keep the table from being erased on server restarts? Is the best way perhaps copying the table to a heap table every time the server starts up? Exactly. Maintain a version that is a permanent table on disk, and load it into the server at startup time. You might find the --init-file server option useful for this. - Steve sql, query - 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
Disappearing records?
Hello, I have a problem that I could use some help with. We're running a mysql/php intranet site for time sheets (home grown solution). However from time to time a user will tell me hours from the previous week are just gone. I go to look and sure enough, no hours have been entered. I have not been bitten by this bug (yet), but I have never encountered this problem with any other databases that exist on this very same box. I have mysql logging enabled and cannot even find existence of these records being inserted into the database at all. Where should I start looking for clues? I am at a loss. Any direction is appreciated. Running Mysql 3.23.49-log on a SuSE 7.3 box running on Dell PowerEdge 2550. Lots of RAM and hard drive space. It's a MyISAM table at the moment. The machine has been running since late July w/o reboot (server and mysql itself). - Greg - 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: Disappearing records?
In the last episode (Nov 18), Greg Macek said: Hello, I have a problem that I could use some help with. We're running a mysql/php intranet site for time sheets (home grown solution). However from time to time a user will tell me hours from the previous week are just gone. I go to look and sure enough, no hours have been entered. I have not been bitten by this bug (yet), but I have never encountered this problem with any other databases that exist on this very same box. I have mysql logging enabled and cannot even find existence of these records being inserted into the database at all. If you have no record of the records having been entered, maybe the user never entered them? Or could there have been a problem with the frontend program that kept it from submitting the data? -- Dan Nelson [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: Disappearing records?
That's the weird part of it. Last week in reviewing the system, making sure reports were working I can personally verify his stuff was in there (not even logged on as him). This week: nothing. Is there any record of 3.23.49 ever having random data loss issues? Related to something else on the system? On Mon, 2002-11-18 at 10:37, Dan Nelson wrote: In the last episode (Nov 18), Greg Macek said: Hello, I have a problem that I could use some help with. We're running a mysql/php intranet site for time sheets (home grown solution). However from time to time a user will tell me hours from the previous week are just gone. I go to look and sure enough, no hours have been entered. I have not been bitten by this bug (yet), but I have never encountered this problem with any other databases that exist on this very same box. I have mysql logging enabled and cannot even find existence of these records being inserted into the database at all. If you have no record of the records having been entered, maybe the user never entered them? Or could there have been a problem with the frontend program that kept it from submitting the data? -- Dan Nelson [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: Disappearing records?
In the last episode (Nov 18), Greg Macek said: That's the weird part of it. Last week in reviewing the system, making sure reports were working I can personally verify his stuff was in there (not even logged on as him). This week: nothing. Is there any record of 3.23.49 ever having random data loss issues? Related to something else on the system? Even if there was dataloss, how could you explain not seeing the entries in the mysql log? I think in this case the query never made it to mysql. You can always try doing a repair table in case the index got damaged. Also check your log to see if the record was manually deleted. -- Dan Nelson [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: Disappearing records?
I'll look into the code on the PHP side (since I wrote that too). Maybe it is something I did. :) I may avoid repairing the tables for now, since the missing records have already been re-entered and I don't want to risk anything being duplicated at this point. Perhaps I'll drop all the indexes for now and keep a watch on things. The table is still small ( 5000 records) so reporting speed isn't too much of a problem right now. On Mon, 2002-11-18 at 10:52, Dan Nelson wrote: In the last episode (Nov 18), Greg Macek said: That's the weird part of it. Last week in reviewing the system, making sure reports were working I can personally verify his stuff was in there (not even logged on as him). This week: nothing. Is there any record of 3.23.49 ever having random data loss issues? Related to something else on the system? Even if there was dataloss, how could you explain not seeing the entries in the mysql log? I think in this case the query never made it to mysql. You can always try doing a repair table in case the index got damaged. Also check your log to see if the record was manually deleted. -- Dan Nelson [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: Disappearing records?
Greg Macek said.. --- I have a problem that I could use some help with. We're running a mysql/php intranet site for time sheets (home grown solution). However from time to time a user will tell me hours from the previous week are just gone. I go to look and sure enough, no hours have been entered. I have not been bitten by this bug (yet), but I have never encountered this problem with any other databases that exist on this very same box. I have mysql logging enabled and cannot even find existence of these records being inserted into the database at all. Where should I start looking for clues? I am at a loss. Any direction is appreciated. -- Greg, In my experience, it will be something simple like not escaping ticks... Are you escaping mysql unfriendly characters from text? i.e. ' vs \' I use a simple logfile to to track these kinds of things. In your case, I would put a line of code in your script, right before the line that executes the insert into MySql that writes the query into a plaintext log somewhere. Sequence in code.. 1. Build the sql statement store in a variable. 2. Using that variable and a unix date, write a line in the logfile. 3. Execute the sql in the variable. When the user says that records are missing, you can look back in the log and find out whether the query was ever sent to mysql in the first place and what that query was. If I had to guess, I would say the problem would be in the app or mysql syntax error rather than a mysql itself problem. Hope this helps, Andrew Sql,mysql - 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: Disappearing records?
Greg Macek wrote: That's the weird part of it. Last week in reviewing the system, making sure reports were working I can personally verify his stuff was in there (not even logged on as him). This week: nothing. Is there any record of 3.23.49 ever having random data loss issues? Related to something else on the system? Big rollback? Drive corruption? User deleing records? Renaming files / directories? QUERY, SQL -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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 - MYSQL databases disappearing!
Is your win2k server networked? does it have multiple users? I would first check the various profiles to ensure it was not moved to a different profile. Next I would ask did the administrator install the server? if another username installed the server even with admin rights that server is now officially part and parcel of that user only. Next I would investigate where the server was installed. There are a number of directories that are auto deleted every time you reboot. Look for unusually large ~.tmp files. Like ~exp0023.tmp or perhaps File00452.tmp. If the server crashes and a program is up in memory windows has a nasty habit of spitting the file in memory immediately to the disk without giving you more than a small hint what the original name was. My suspicion is you won't find any of these. Here's a typical scenario... The server is set-up with a cleaning scheduler. Every night at midnight the system runs a virus check, a scandisk and a defrag then empties the temp files and reboots. The Mysql program is running at the same time. Mysql is using a section of memory that is also used by defrag. Microsoft sometimes lets programs share memory space but some programs (virus checkers and excel are the worst) fail to give the memory back. A good midnight query is run. It crashes. The program is dropped into the Temp bin as ~ql883.tmp the server runs the cleanup process and finds a huge tmp file it cannot put in the trash bin because of it's size (mysql server + query + tables trashbin size) therefore for your convenience Microsoft auto deletes the file. Then reboots. You might get a warning that the server cannot be found but that would be it. -Original Message- From: Mike Taffi [mailto:[EMAIL PROTECTED]] Sent: Monday, September 16, 2002 1:21 PM To: [EMAIL PROTECTED] Subject: HELP - MYSQL databases disappearing! Importance: High Does anyone know why a MYSQL database would just disappear and how I might prevent it from happening in the future? I run it on win2k advanced server. Much obliged, Mike Taffi - 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
Disappearing of New Inserts to MyISAM table
Hi Everyone, I am in kind of a jam, and I could use some help. Hopefully someone out there has encountered something similar to this. I have a MySQL database, and I am using Java to make the connection to the database. At one point in the execution of my app, I need to issue an ALTER TABLE command to add a column to one of my tables. The command issues successfully, however, if I login to the Server itself, and issue a DESCRIBE TABLE command, the newly added column is not among the list of columns. When I use WINMYSQLADMIN to monitor the database, it to shows the addition of the new column. Only when I login to the server itself does the column not show. It is strange because the command issues flawlessly, and I get no SQL or Java Errors...I have checked my permissions on the tables, as well as on the server itself, and I have full rights to do anything I please on the database. Please help me out with any advice you may have on this issue, anything would be a huge help. Thanks, Chris - 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: Disappearing of New Inserts to MyISAM table
At 23:05 -0500 3/14/02, Chris Stark wrote: Hi Everyone, I am in kind of a jam, and I could use some help. Hopefully someone out there has encountered something similar to this. I have a MySQL database, and I am using Java to make the connection to the database. At one point in the execution of my app, I need to issue an ALTER TABLE command to add a column to one of my tables. The command issues successfully, however, if I login to the Server itself, and issue a DESCRIBE TABLE command, the newly added column is not among the list of columns. When I use WINMYSQLADMIN to monitor the database, it to shows the addition of the new column. Only when I login to the server itself does the column not show. It is strange because the command issues flawlessly, and I get no SQL or Java Errors...I have checked my permissions on the tables, as well as on the server itself, and I have full rights to do anything I please on the database. Please help me out with any advice you may have on this issue, anything would be a huge help. Bizarre. Perhaps this is a silly question, but are you sure you're actually connecting to the same server each time? Thanks, Chris - 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: Disappearing of New Inserts to MyISAM table
Yes, it is connecting to the same server each time, it is very strange. I have tried moving the entire database to a different server, and I seem to encounter the same problem as well. It is almost as if the tables themselves have an issue which is causing the problem. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 14, 2002 11:49 PM To: Chris Stark; [EMAIL PROTECTED] Subject: Re: Disappearing of New Inserts to MyISAM table At 23:05 -0500 3/14/02, Chris Stark wrote: Hi Everyone, I am in kind of a jam, and I could use some help. Hopefully someone out there has encountered something similar to this. I have a MySQL database, and I am using Java to make the connection to the database. At one point in the execution of my app, I need to issue an ALTER TABLE command to add a column to one of my tables. The command issues successfully, however, if I login to the Server itself, and issue a DESCRIBE TABLE command, the newly added column is not among the list of columns. When I use WINMYSQLADMIN to monitor the database, it to shows the addition of the new column. Only when I login to the server itself does the column not show. It is strange because the command issues flawlessly, and I get no SQL or Java Errors...I have checked my permissions on the tables, as well as on the server itself, and I have full rights to do anything I please on the database. Please help me out with any advice you may have on this issue, anything would be a huge help. Bizarre. Perhaps this is a silly question, but are you sure you're actually connecting to the same server each time? Thanks, Chris - 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
Data disappearing problem
Hi, I'm having a very annoying and elusive problem. You probably won't be able to help me, but any guess or pointer would be appreciated nonetheless. The main table in the db contains some 50 fields, mostly of type varchar and tinyint. This table is updated through php. Now the problem is: sometimes some of the data for a specific record goes missing after I access a page. I cannot reproduce the problem. Only certain fields are affected, and these fields vary every time the problem occurs. These fields are somewhat reset, but I say 'somewhat' because they don't necessarily go to the default value specified in MySQL. The UPDATE queries on the page that *seem* to be where the problem occurs are all valid and well-formed, and some of the fields that get reset are not even in those queries at all. This has been specifically occuring on a machine using Win98/Netscape4 to access the page. The server is running Cobalt Linux release 6.0 (Shinkansen-Decaf) Kernel 2.2.16C24_III on an i586, MySQL-3.23.36-1, php-4.0.3pl1-C1r4, apache-1.3.12-1C9 I know this is not very much information, and it's not helpful that I can't reproduce it... Anything leaps to mind as something I could check or look for?, any wild guess, anything would be appreciated. Could MySQL be doing it? What else could it be? Thanks. Michael Hebert D.Black Communications - 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: Disappearing \
Hi On Monday 09 April 2001 05:34, Jack A. Tinsley Jr. wrote: First, things, first - I am a newbie to MySQL but I have it up and running quite well. The only thing I haven't been able to figure out is why I'm losing "\" (back slashes) in my stored data. I have a application I'm considering MySQL for and one table must have a column containing a UNC path. When I insert the value of "\\server\folder", MySQL turns it into "\serverfolder" - very strange. I've set this column up as a varchar. Actually, this behaviour is not strange it is correct, since the '\' character is considered an escape character. If you want to insert the value "\\server\folder" you would need to add extra '\' characters i.e. "\\\server\\folder" - 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 -- Regards John -- MySQL Development Team __ ___ __ __ / |/ /_ __/ __/ __ \/ / John Dean [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\/ Mansfield, England, UK ___/ - 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: Disappearing \
Without being picky, and just to let Jack know, the line: "\\\server\\folder" should be: "server\\folder" Each double slash pair (\\) is replaced in the database with one slash (\). I recommend you take a look at the AddSlashes() and ereg_replace() functions if you're using PHP (http://php.net), or looking at some regular expressions for perl in order to format strings for database insertion. Ben -Original Message- From: John Dean [mailto:[EMAIL PROTECTED]] Sent: 09 April 2001 08:25 To: Jack A. Tinsley Jr.; [EMAIL PROTECTED] Subject: Re: Disappearing "\" Hi On Monday 09 April 2001 05:34, Jack A. Tinsley Jr. wrote: First, things, first - I am a newbie to MySQL but I have it up and running quite well. The only thing I haven't been able to figure out is why I'm losing "\" (back slashes) in my stored data. I have a application I'm considering MySQL for and one table must have a column containing a UNC path. When I insert the value of "\\server\folder", MySQL turns it into "\serverfolder" - very strange. I've set this column up as a varchar. Actually, this behaviour is not strange it is correct, since the '\' character is considered an escape character. If you want to insert the value "\\server\folder" you would need to add extra '\' characters i.e. "\\\server\\folder" - 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 -- Regards John -- MySQL Development Team __ ___ __ __ / |/ /_ __/ __/ __ \/ / John Dean [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\/ Mansfield, England, UK ___/ - 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
Disappearing \
First, things, first - I am a newbie to MySQL but I have it up and running quite well. The only thing I haven't been able to figure out is why I'm losing "\" (back slashes) in my stored data. I have a application I'm considering MySQL for and one table must have a column containing a UNC path. When I insert the value of "\\server\folder", MySQL turns it into "\serverfolder" - very strange. I've set this column up as a varchar. - 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: Disappearing \
On Sun, Apr 08, 2001 at 10:34:17PM -0600, Jack A. Tinsley Jr. wrote: First, things, first - I am a newbie to MySQL but I have it up and running quite well. The only thing I haven't been able to figure out is why I'm losing "\" (back slashes) in my stored data. I have a application I'm considering MySQL for and one table must have a column containing a UNC path. When I insert the value of "\\server\folder", MySQL turns it into "\serverfolder" - very strange. I've set this column up as a varchar. The "\" character is an escape character, often used to include otherwise "special" characters in your data. To include a literal "\" in your data, you need to actually double it up "\\". 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: Disappearing \
You need to escape it with another "\". Look up the manual about escaping special chars. - Original Message - From: "Jack A. Tinsley Jr." [EMAIL PROTECTED] To: "[EMAIL PROTECTED]" [EMAIL PROTECTED] Sent: Monday, April 09, 2001 12:34 Subject: Disappearing "\" First, things, first - I am a newbie to MySQL but I have it up and running quite well. The only thing I haven't been able to figure out is why I'm losing "\" (back slashes) in my stored data. I have a application I'm considering MySQL for and one table must have a column containing a UNC path. When I insert the value of "\\server\folder", MySQL turns it into "\serverfolder" - very strange. I've set this column up as a varchar. - 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: /tmp/mysql.sock disappearing?
On Tue, Feb 13, 2001 at 11:50:28AM -0500, Brian Reichert wrote: I couldn't fine this mentioned in the archives, so I hope someone has seen this before: I'm running into a situation wherein /tmp/mysql.sock keeps disspearing. There is still server process (sleeping), and there is still a pidfile. This can happen after ten or fifteen minutes of queries. I don't have a cronjob, or any other external process that scrubs /tmp. I have to apologize to everyone; I found out that I _did_ have a cronjob that caused this socket to go away: I had been doing this nightly: /usr/local/bin/mysqladmin -uroot -proot flush-logs /usr/local/bin/safe_mysqld safe_mysqld out-of-hand removes $MYSQL_UNIX_PORT; I don't know why. (I also don't know why I felt the need to restart mysqld after a flush-logs.) Thanks for everyone's advice... -- Brian 'you Bastard' Reichert[EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA Intel architecture: the left-hand path - 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
/tmp/mysql.sock disappearing?
I couldn't fine this mentioned in the archives, so I hope someone has seen this before: I'm running into a situation wherein /tmp/mysql.sock keeps disspearing. There is still server process (sleeping), and there is still a pidfile. This can happen after ten or fifteen minutes of queries. I don't have a cronjob, or any other external process that scrubs /tmp. Has anyone seen this sort of symptom before? FWIW, the details: OS: FreeBSD 3.4-RELEASE MySQL 3.22.32 -- Brian 'you Bastard' Reichert[EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA Intel architecture: the left-hand path - 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: /tmp/mysql.sock disappearing?
On Tue, Feb 13, 2001 at 11:50:28AM -0500, Brian Reichert wrote: I couldn't fine this mentioned in the archives, so I hope someone has seen this before: I'm running into a situation wherein /tmp/mysql.sock keeps disspearing. There is still server process (sleeping), and there is still a pidfile. This can happen after ten or fifteen minutes of queries. I don't have a cronjob, or any other external process that scrubs /tmp. Has anyone seen this sort of symptom before? I've seen it after installing two versions of MySQL on one server. I forgot to set another location for the second MySQL process before starting it with /bin/safe_mysqld. The result was that the socket was removed by the second process. Looking at safe_mysqld you would probably get the same result if you run multiple MySQL processes of the same version but from different data directories. Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - 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: /tmp/mysql.sock disappearing?
On Tue, Feb 13, 2001 at 08:35:44PM +0100, Fred van Engen wrote: I've seen it after installing two versions of MySQL on one server. I forgot to set another location for the second MySQL process before starting it with /bin/safe_mysqld. The result was that the socket was removed by the second process. Looking at safe_mysqld you would probably get the same result if you run multiple MySQL processes of the same version but from different data directories. Believable, but we in fact only have the one instance of mysql running. :/ Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- Brian 'you Bastard' Reichert[EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA Intel architecture: the left-hand path - 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: /tmp/mysql.sock disappearing?
On Wed, Feb 14, 2001 at 09:03:16AM +0800, Rolf Hopkins wrote: Do you have a cron job that's cleaning up your socket file along with whatever other garbage it's cleaning up? Nope. As I said in my original mail: I don't have a cronjob, or any other external process that scrubs /tmp. I'm waiting for it to happen again; it's been behaving for twelve hours now... -- Brian 'you Bastard' Reichert[EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA Intel architecture: the left-hand path - 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
