RE: Is SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED applicable to MyISAM tables?
Thanks for your reply. Should we move this discussion to [EMAIL PROTECTED] Also, notice that unless your application is under _extreme_ load, none of these SET queries will are likely to have an impact on the performance of your application. I was hoping someone would reply saying that SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED would be ignored for MyISAM tables. I may open an official support call to verify. If you're using a newer version of our JDBC driver (3.1.x), you can always add useLocalSessionState=true to avoid having to do _some_ of these queries to the database. I installed the 3.1.10 driver and tried that but I can not tell a difference and don't know how to verify. I submitted it to the coldfusion database access forum for help. I noticed the MySQL Connector/J Documentation for useLocalSessionState says... Should the driver refer to the internal values of autocommit and transaction isolation that are set by Connection.setAutoCommit() and Connection.setTransactionIsolation(), rather than querying the database? Can you elaborate any more on this? From the description it makes it sound like it wouldn't set autocommit or session transaction isolation at all. Then again, I wonder if it is saying that yes it will set them both, it just will not query the database to check its setting before it does (getting rid of show variables?). Humm. Note-I also set the global TRANSACTION ISOLATION LEVEL READ COMMITTED thinking that if it was already set at the global level then it would not be set at the session level (after the show variables). But that didn't help. Thanks, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED applicable to MyISAM tables?
Can you help set me straight? I recently upgraded from coldfusion 5 to coldfusion mx 7. In the process I also switched from odbc to jdbc connections to mysql (4.0.21-nt-max-log). From looking at the query log, I'm now getting... SHOW VARIABLES ...once per connection followed by... SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED - followed by one or more... ^ SELECT ^ ^ followed by... ^ SET autocommit=1 ^ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED ^ - this pattern repeats until the final Quit for the connection. I can include actual entries from the query log if you would like to see them. Is SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED applicable to MyISAM tables? I am not using transactions and InnoDB or BDB tables. Since I am not using transactions is this statement irrelevant (and unneeded)? I'm also a little surprised by the SET autocommit=1. The manual says... The other non-transactional storage engines in MySQL Server (such as MyISAM) follow a different paradigm for data integrity called ``atomic operations.'' In transactional terms, MyISAM tables effectively always operate in AUTOCOMMIT=1 mode. Atomic operations often offer comparable integrity with higher performance. and... By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk. and... Note that if you are not using transaction-safe tables, any changes are stored at once, regardless of the status of autocommit mode. So it would seem that the SET autocommit=1 commands being seen in the query log are not needed. I checked and autocommit is enabled (not that it would seem to matter since I'm using MyISAM tables)... mysql SELECT @@AUTOCOMMIT; +--+ | @@autocommit | +--+ |1 | +--+ Am I right that neither SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED SET autocommit=1 are appropriate/needed for MyISAM tables? If that is true, then I guess my next step is to try and find out why coldfusion mx 7 is issuing them to MySQL when using MyISAM tables. Thanks, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: does anyone know of a utility that will processes the query log to rerun the queries?
I am familiar with mysqlbinlog that is used to process the binary log to produce a text file that can then be feed back into mysql. That appears to be what you are referring to. But I'm not referring to the binary log, or any derivative of it, at all. I am referring to the actual general query log. Does anyone know of a utility that will processes the general query log (not the binary log or the binary log text file from mysqlbinlog) to rerun the queries? The program would need to... ...strip file header information ...strip the leading non query info from the line ...handle queries that span multiple lines ...change databases when appropriate before queries ...add the ; to the end of the query an option to only reprocess unique queries might also be nice. has anyone already done this? daniel -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Monday, January 10, 2005 8:55 PM To: Daniel Gaddis; mysql@lists.mysql.com Subject: Re: does anyone know of a utility that will processes the query log to rerun the queries? Yes. The mysql client is good for this. I used to restore from disasters this way, eg: - full backup every night - transaction log ( the text one, not the binary one ) gets reset each night by restarting mysql after the backup Then when our disaster happened, we'd drop all databases, import from last night's backups, and then run the transaction log: mysql /path/to/transaction/log -p Unfortunately this becomes a little more complicated if you use temporary tables ... especially if you're updating the DB from the contents of the temporary tables, as the recovery process will run as 1 user instead of all the original users that ran it to start with. I believe the binary transaction log is good in this case, but I haven't bothered to check up on how to use it yet. But if you don't create temporary tables, then the above 1-liner should do. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
does anyone know of a utility that will processes the query log to rerun the queries?
does anyone know of a utility that will processes the query log to rerun the queries? The program would need to... ...strip the leading non query info from the line ...handle queries that span multiple lines ...change databases when appropriate before queries ...add the ; to the end of the query an option to only reprocess unique queries might also be nice. has anyone already done this? daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: is there a utility like mysqlbinlog but instead processes the query log?
The program would also need to handle...
...queries that span multiple lines
...change databases when appropriate before queries
yes I could program such a beast but I thought someone else might have
already done it.
daniel
-Original Message-
From: Andy Davidson [mailto:[EMAIL PROTECTED]
Sent: Sunday, January 02, 2005 3:58 AM
To: mysql@lists.mysql.com mysql@lists.mysql.com
Subject: Re: is there a utility like mysqlbinlog but instead processes
the query log?
On 30 Dec 2004, at 13:26, Daniel Gaddis wrote:
is there a utility like mysqlbinlog but instead processes the query
log?
I would like to reprocess the queries from the query log.
I don't see another reply to this on the list, so I hope it helps - the
query log is already in plain-text, so you don't need something to fish
the queries out of an unfriendly format.
This bit of perl should be a good starting point.
elephant:/var/log/mysql# cat pullqueries.pl
#!/usr/bin/perl -w
use strict;
while (my $line = ) {
if ($line =~ /Query/) {
my (undef, undef, undef, undef, $display) = split(/ /,
$line, 5);
print $display;
}
}
example :
elephant:/var/log/mysql# tail -n 20 mysql.log | perl pullqueries.pl
SELECT fname from images where groupid='4' order by viewno desc
limit 0,1
SELECT fname from images where groupid='3' order by viewno desc
limit 0,1
SELECT fname from images where groupid='2' order by viewno desc
limit 0,1
SELECT title,story FROM groups where id='1114'
SELECT id,dirname,fname FROM images where groupid='1114'
SELECT id,dirname,fname,viewno,groupid FROM images where
id='10035' limit 0,1
UPDATE images set viewno='1',lastlook=NOW('') where id='10035'
SELECT entry,whoby FROM ucaptions where picid='10035'
SELECT dirname,fname,caption from images where id='10035' limit
0,1
SELECT title,story FROM groups where id='1114'
SELECT id,dirname,fname FROM images where groupid='1114'
--
Regards, Andy Davidson
http://www.fotoserve.com/
Great quality prints from digital photos.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
is there a utility like mysqlbinlog but instead processes the query log?
is there a utility like mysqlbinlog but instead processes the query log? I would like to reprocess the queries from the query log. Thanks, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
is there a utility like mysqlbinlog but instead processes the query log?
is there a utility like mysqlbinlog but instead processes the query log? I would like to reprocess the queries from the query log. additional features that would be nice would include: listing unique queries the number of times each unique query is executed Thanks, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Should there be an entry in the mysql error log?
From: Victor Pendleton [mailto:[EMAIL PROTECTED] This error should be logged. Do you have a file called hostname.err in your directory? Yes I have a file called hostname.err and that error is not in it. From: Paul DuBois [mailto:[EMAIL PROTECTED] The error log is for messages pertaining to problems with the operational state of the server. Normally there's not much in it at all. The error you show above is a problem with a query sent by a client. That type of error message is sent to the client, not logged. the query (see below) seems to be okay. the problem doesn't seem to be with the query, but with the table. UPDATE referenceEntries SET heading = 'Delete', body = '', keywords = '' , dateVerified = NULLWHERE entryID = 168 the error (see below) seems to be the first indication of a corrupt table. So wouldn't that be a server issue and be appropriate to log into the mysql error log? Incorrect key file for table: 'referenceEntries'. Try to repair it The manual makes a reference to this error in Section 14.1.3.1 Corrupted MyISAM Tables... Typical symptoms for a corrupt table is: You get the error Incorrect key file for table: '...'. Try to repair it while selecting data from the table. Thanks, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Should there be an entry in the mysql error log?
From: Paul DuBois [mailto:[EMAIL PROTECTED] I suppose you could consider it such, but on the other hand, it's not the sort of error that prevents the server from running or processing other queries. In any case, logging it to the error log isn't going to help the client know what is going on and that it should attempt to run a table repair operation. Perhaps what you are suggesting is that the message should go to both places? Yes having the message go to both places would be nice. As a sys admin type I would like to see when the problem was first noticed before the warning: checking/recovering table entries in the error log. Thanks, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Should there be an entry in the mysql error log?
From: Paul DuBois [mailto:[EMAIL PROTECTED] I don't understand that second sentence, but you can submit feature requests at http://bugs.mysql.com/ For example, I would like the error log to look something like... MySQL: ready for connections. Version: '4.0.18-max-nt-log' socket: '' port: 3306 040323 8:46:41 Incorrect key file for table: 'referenceEntries'. Try to repair it attempted query was: UPDATE referenceEntriesSET heading = 'Balanced Budget - Consitution (U.S.) Amendment', body = 'Calling on Congress to convene a constitutional convention to draft a balanced budget amendment (we do not yet have an amendment to ratify) 5/92HCR 31, 65th R.S. 1977 (passed -- pp. 3257-3268, Author: Donaldson, Sponsor: Moore) HCR 40, 65th R.S. 1977 DID NOT PASS HCR 13, 65th 2nd C.S. 1978 (passed -- pp. 41-42 Author: Von Dohlen, Sponsor: Hance) HCR 9, 64th R.S. 1975 DID NOT PASS (pay as you go) HCR 72, 66th R.S. 1979 DID NOT PASS HR 113, 69th R.S. 1985 DID NOT PASS HCR 69, 70th R.S. 1987 DID NOT PASSNone in 71st R.S. 1989 -- nor the 6 special sessions None in 72nd R.S. 1991 -- nor the 3 special sessions None in 73rd R.S. 1993 None in 75th R.S. 1997See also: recent article 89.12.002 for pros and consAmending the Federal Constitution to Require a Balanced Budget (351.7202 N213A)Balanced Budget Amendment to the U.S. Constitution: Impact of Texas (Legislative Study Group, Feb. 23, 1995) L1800.9 L523i 95-03', keywords = 'defecit spending federal', dateVerified = NULL WHERE entryID = 226 040323 8:54:18 Warning: Checking table: './members/referenceentries' 040323 8:54:18 Warning: Recovering table: './members/referenceentries' I will submit a request to http://bugs.mysql.com/ Thanks, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Should there be an entry in the mysql error log?
I copied the following from my coldfusion application.log Error,2152,03/20/04,09:03:13,,ODBC Error Code = S1000 (General error)P [MySQL][ODBC 3.51 Driver][mysqld-4.0.18-max-nt-log]Incorrect key file for table: 'referenceEntries'. Try to repair itPP SQL = UPDATE referenceEntries SET heading = 'Delete', body = '', keywords = '' , dateVerified = NULLWHERE entryID = 168 The contents makes me think there should also be something in the mysql error log similar to... Incorrect key file for table: 'referenceEntries'. Try to repair it But there is no entry in the mysql error log. Any thoughts why not? Thanks, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Paul, do you address this in any of your books?
I'm running MySql 4.0.10-gamma-max-nt-log I have 1 table like the one below... ++-+--+ | DAY| USERID | LIS_QUANTITY | ++-+--+ | 2003-01-02 | H0850A1 | 539 | | 2003-01-02 | LBBSWJR |7 | | 2003-01-02 | O0600B3 | 21 | | 2003-01-03 | H0850A1 |6 | | 2003-01-03 | H2610A1 | 51 | | 2003-01-03 | O0600B3 | 19 | | 2003-01-04 | H0850A1 |8 | | 2003-01-04 | H2610A1 | 13 | | 2003-01-04 | LBBSWJR |3 | ++-+--+ I would like a sql query to produce output like the following... +-++++ | USERID | 2003-01-02 | 2003-01-03 | 2003-01-04 | +-++++ | H0850A1 |539 | 6 | 8 | | H2610A1 || 51 | 13 | | LBBSWJR | 7 || 3 | | O0600B3 | 21 | 19 || +-++++ Can I do this just by using sql and not adding php, perl, or any other language? If mysql can't handle this with sql only, would it be able to do it once subselects or other features are available in future releases? Anyone used postgresql? I wonder if it could handle it. Any thoughts? Thanks, Daniel - 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
I want sql query result with userid 1st col followed by 1colum for each date all corresp numbers
I have 1 table with 3 fields: date, userid, and a number. I would like an sql query result with the userid for the first column followed by 1 column for each date and all corresponding numbers displayed for that userid for that date. Is there a way to do this using just sql and not php or some other add on programming language? Does anyone have a canned sql example for this type of situation? Can you think of any key phrases I can use to search for sql solutions to this type of problem? Any idea if this type of problem has a particular name? It seems kind of basic. Thanks, Daniel - 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 4.0.3beta on XP...didn't find mysql_fix_privilege_tablesafter install, pull unix download?
I downloaded MySQL 4.0.3beta on my XP system. I didn't find mysql_fix_privilege_tables after installing. Am I suppose to pull it from the unix download and do by hand? Thanks, Daniel - 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
