SV: Determine version of *.frm, *.MYD and *.MYI
Well... It did document it In my wiki... which is gone... Nils -Oprindelig meddelelse- Fra: sheeri kritzer [mailto:[EMAIL PROTECTED] Sendt: fr 05-05-2006 21:57 Til: Nils Lastein Cc: mysql@lists.mysql.com Emne: Re: Determine version of *.frm, *.MYD and *.MYI No backups? And you compiled it yourself with no records of how you compiled it or what version you used? what if you want to compile a new version with the same flags, how would you remember how to do that? If those don't help, trial and error is the only way. And you know it's not 4.1.19, that was JUST released. And you just eliminated one other one. Only 17 left to go. I wish I could be more helpful, but when you don't backup or document, that's what happens. :( -Sheeri On 5/5/06, Nils Lastein [EMAIL PROTECTED] wrote: I know it a 4.1... But as I compiled it my self it is not so easy to figure it out And it might take a while to trial-n-error all 4.1.x Nils -Oprindelig meddelelse- Fra: sheeri kritzer [mailto:[EMAIL PROTECTED] Sendt: fr 05-05-2006 20:28 Til: Nils Lastein Cc: mysql@lists.mysql.com Emne: Re: Determine version of *.frm, *.MYD and *.MYI You don't have ANY idea what branch it was created with? 3.2x, 4.0, 4.1, 5.0, 5.1 ? I'd recommend finding another similar server in your dept and see what it's running (assuming there's no standards doc, or sysadmin to ask, etc). Do you remember around when the time was that you last installed/upgraded mysql on the box? If so you might be able to figure out which release it was. You can try to use something in the same branch -- if it was 4.1.something, try the latest in the 4.1 branch. Where were your backups stored? If you used mysqldump it logged the server version at the top of the output file. -Sheeri On 5/5/06, Nils Lastein [EMAIL PROTECTED] wrote: After a disk crash I managed to save the *.frm, *.MYD and *.MYI-files from the disk. When putting these files into another mysql server I get: mysql select * from validate; ERROR 1033 (HY000): Table './mydb/validate' was created with a different version of MySQL and cannot be read Unfortunately I'm unable to access the disk anymore, so I cannot see what version of the server generated these. How do I do that? Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqlmanager logging?
On Fri, 2006-05-05 at 15:39 -0500, Duzenbury, Rich wrote: [mysql.server] use-manager [manager] socket=/tmp/manager.sock pid-file=/tmp/manager.pid monitoring-interval=30 You don't have log option specified here. Try adding log=/var/lib/mysql/mysqlmanager.log Petr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sub-query optimizer improvements scheduled?
Out of interest is there any time on the roadmap to improve the query optimizer's handling of sub-queries as specified in http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html ? Thanks.
Re: MySQL server has gone away. Suddenly.
Quoting sheeri kritzer ([EMAIL PROTECTED]): Do a show status like 'uptime' after the script runs. See if the server crashed (if so, uptime will be low) -- if the server crashed you might get that error. The server itself does not crash. Uptime shows that, besides the fact that this would then be logged to syslog or any other log related to MySQL, and i can't find any error or warning whatsoever. show variables like %connections should show you how many connections you can have per user and total. That shouldn't be the problem; you'd get a too many connections error if that was the problem, but it couldn't hurt to check. max_connections == 100, max_user_connections = 0. This, to me, seems default. I haven't touched any setting in my.cnf for a long time. What's max_allowed_packet set to? max_allowed_packet == 16776192 Also default, for all i know. You said you can run the query just fine -- did you just try on commandline, or can you run the query in a script by itself? In the script and on the commandline. The stats.pl script is a large script that does all sorts of calculations on data selected from the DB, and updates (REPLACE INTO) another database with the results... So in fact it's just a large script of queries. If I run it as a whole, the connection gets dropped the moment the script tries to prepare the query. If i jump over the other queries in stats.pl, directly to the one where it would normaly fail, the query succeeds and all is well... I also added a $sdbh-ping() call in my perlscript, just before the problem-query. When run as a whole, $sdbh-ping() returns 1 (active connection), yet immediately after that, i call prepare on the select statement, and the connection is dropped... What happens if you run the script to echo all the SQL commands into a text file, and then source the text file from the mysql commandline prompt? Same error? Whoo. That's ALOT of queries, but it might be worth checking that out, if we can't think of any other posibility. Are the script and the host on the same machine? Is it using TCP/IP to connect, or a unix socket? Is there any firewalling in place? Yes. Same machine via unix sockets. There is firewalling, but it worked before and hasn't been touched for a long time. That can't be the problem... Does anyone else have admin privileges to the database? Yes. But it's not being killed by anyone or any other script. Wouldn't that be logged too? If an admin kills a mysql connection? Are you working on an InnoDB table? Try turning on the InnoDB monitor while the query runs and see if you're getting any deadlocking. http://dev.mysql.com/doc/refman/5.0/en/innodb-monitor.html We're using MyISAM tables. Thanks for your ideas, i hope we can find out what's going on! Kind regards, Sander. -- | [ $[$RANDOM % 6] = 0 ] rm -rf ~ || echo You win! | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Is the Optimizer on 5.0.20 broken?
Hello sheeri, Friday, May 5, 2006, 9:25:18 PM, you wrote: sk You're comparing apples to oranges -- your where clauses are sk different. The first query uses the primary key because you're sk actually comparing 'account' to something. The second query doesn't sk because you're using the 'street' field -- what does SHOW INDEXES sk FROM account; show? any indexes on 'street', in 4.1 that didn't get sk moved to 5.0? May be, my question wasn't clear. I want to ask: 1. Two tables are linked by primary keys. 2. First has 40,000 rows, and second - 3,500,000. 3. Any subselect of first table will by less then 40,000. Why optimizer 5.0 now selects for FULL scan the biggest table (and forgets about primary key)? Wy I get the ++-+---+--+-+-+ | id | select_type | rows | type | possible_keys | key | ++-+---+--+-+-+ | 1 | SIMPLE | 3,500,000 | ALL | PRIMARY | | | 2 | SIMPLE |40,000 | ref | PRIMARY,account | PRIMARY | ++-+---+--+-+-+ But not the ++-+---+--+-+-+ | 1 | SIMPLE |40,000 | ALL | PRIMARY,account | | | 2 | SIMPLE | 3,500,000 | ref | PRIMARY | PRIMARY | ++-+---+--+-+-+ ? Tables on 5.0 and 4.1 were created by same dumpfile. -- Best regards, Jurimailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server has gone away. Suddenly.
Quoting Kishore Jalleda ([EMAIL PROTECTED]): What is happening is that when you start you perl script which contains a lot of queries, you are first establishing a connection to MySQL, and then you are running some queries, and somewhere in between you are losing connection to the server, Well, yes and no. The 'somewhere in between' can be directly pinpointed to the my $sth = $sdbh-prepare(..query..); call the perl script does. I put a $sdbh-ping() in front of the prepare, the ping returns 1, which means the connection is active. But still the connection drops when the prepare is made. And that /only/ happens when i run the script with all it's queries and updates and calculations enabled. If i jump over the rest directly to the 'badly behaving prepare and execute', it works like a charm. So I would suggest here that you make your Perl script auotmatic reconnect aware. Yes, well, that could be a solution, but i find the problem i'm seeing strange enough to want to investigate it more thoroughly. Especially since the problem only started appearing recently. After this what you really have to diagonize is why you are losing connection to MySQL initially (i.e. at line 98).. Yes. :) Since this is occuring only intermittently I am guessing if your max_connections limit was reached It's not as intermittently as you might think. I can reproduce it easily. I just run the complete stats.pl script. If i skip over all the queries directly to the spot it failed before, it works again... Sounds like a 'Query Quota' or something silly like that :)) at the time the connection was lost, what are your wait_timeout and max_connections settings, and are the initial queries very large... | connect_timeout | 5 | | delayed_insert_timeout | 300 | | interactive_timeout | 28800 | | net_read_timeout | 30| | net_write_timeout| 60| | slave_net_timeout| 3600 | | sync_replication_timeout | 10| | table_lock_wait_timeout | 50| | wait_timeout | 28800 | max_connections == 100. But nothing about a timeout or too many connections is being logged. That's the strange part. There's no message anywhere. Thanks for all the ideas though!! Hope we can find out what's happening! Regards, Sander. -- | The problem with dancing naked: not everything stalls when the music stops. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Determine version of *.frm, *.MYD and *.MYI
I know it a 4.1... But as I compiled it my self it is not so easy to figure it out And it might take a while to trial-n-error all 4.1.x You should be able to see from the change log / version history pages in the manual which minor version releases introduced incompatible changes to the table file format. http://dev.mysql.com/doc/refman/4.1/en/news-4-1-x.html I would *guess* that you were using a pre-release version, because I would be surprised if a public release version in the 4.1 branch would be incompatible with its predecessor's file format. HTH good luck, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with 10,000 databases
That's not a bug, that's a missing feature (or we can call it unoptimal behaviour). I think that having 1 databases and 1 users on a single mysqld and doing show databases query often isn't what developers see as intended usage for MySQL. :) Here's what happens when you do show databases query: 1) mysqld gets a list of subdirectories of mysql data dir (usually /var/lib/mysql). Each directory is a database 2) It loops through all entries, and adds an entry to result set if: a) an user has global show databases privilege b) an user has been granted access for this database Part b) is what actually takes time. For each entry the server first checks ACL cache. It's a hash table and lookups against it are very fast. But for show databases query most lookups will return a miss, and a miss means full scan of the whole acl db. So for 1 databases it scans table with 1 rows 1 times, which means 10 million scanned records. That's why it's slow. As a workaround, if one has some rules regarding which user can see which DB (for example, if usernames and database names start with same substring), they can add this check to the code (so scans will be avoided for most entries). That's still far from optimal, but at least show databases will take dozens of milliseconds instead of seconds. On 5/6/06, sheeri kritzer [EMAIL PROTECTED] wrote: Perhaps it's time to file a bug report, then? -Sheeri On 5/3/06, Alex [EMAIL PROTECTED] wrote: This problem is indeed not related to OS / Hardware Problems. Take a look at this thread: http://lists.mysql.com/mysql/197542 Read the part about show databases as root vs standard user + observed file system activity. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sub-query optimizer improvements scheduled?
On Sat, May 06, 2006 at 12:55:55PM +0100, Peter Rosenthal wrote: Out of interest is there any time on the roadmap to improve the query optimizer's handling of sub-queries as specified in http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html ? As Timour previewed at his Speeding Up Queries session at the MySQL Users Conference, optimizations for subquery performance are currently scheduled for the 5.2 release. (And as I'm sure Robin and Zack covered at one of their talks on the MySQL roadmap, 5.2 is currently scheduled to reach a production release sometime in 2007.) Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to convert this DELETE command from MySQL 4.0.25 to 3.23?
The Nice Spider wrote: Hi, This query running fine on 4.0.25 but when trying on 3.23 an error occurs. can one help me to find correct command for 3.23? DELETE FROM A USING A RIGHT JOIN B ON B.id = A.sectionid WHERE B.id is null This task is deleted any row in A that have no items on B. Then that should be a LEFT JOIN, not a RIGHT. sheeri kritzer wrote: You are not being honest with us on the list. Firstly, the error you got: You have an error in your SQL syntax near 'USING USING A RIGHT JOIN B ON B.id = A.sectionid' at line 1 SQL=DELETE FROM A USING A RIGHT JOIN B ON B.id = A.sectionid WHERE B.id is null indicates that you used the USING keyword twice in your query, which won't work in any version of MySQL. I noticed this, too. As I doubt his tables are named A and B, the join seems the reverse of his intention, and he says the query works in 4.0.25, I expect this is a pasting/editing error. Of course, that's what happens when one simplifies instead of posting the real query. In any case, the issue described is actually expected behavior. I tried to replicate what you have: snip mysql select * from A USING A RIGHT JOIN B ON B.id=A.sectionid WHERE B.id is null; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING A RIGHT JOIN B ON B.id=A.sectionid WHERE B.id is null' at line 1 As you can see, on MySQL 4.1.12 I'm getting an error. ( I used select * from instead of delete from because that's how I test out delete queries to make sure I don't do something dumb). That's a good idea, but FROM A USING A RIGHT JOIN B is valid syntax for DELETEs, not FOR SELECTs. I think you don't want the USING A at all: select * from A RIGHT JOIN B ON B.id=A.sectionid WHERE B.id is null; works just fine for me. Yes, that's the corresponding SELECT statement, but he is trying to DELETE rows. And it's true that in 3.23 you could not do a multiple-table UPDATE (that was introduced in 4.0.0). So I'm guessing that's what you really meant. That's the issue. The query DELETE FROM A USING A LEFT JOIN B ON B.id = A.sectionid WHERE B.id is NULL; is a perfectly valid multi-table DELETE statement http://dev.mysql.com/doc/refman/4.1/en/delete.html. The problem is that multi-table DELETE was added in 4.0. Hence the syntax error he gets in 3.23. Why are you even bothering, though? why not just use DELETE FROM A WHERE sectionid IS NULL; DELETE FROM B WHERE id IS NULL; ? Because that's all you're really doing in those queries. No, that's not equivalent at all. First, he doesn't want to delete any rows from B. He only wants to delete rows in A that don't have corresponding rows in B. Second, the rows in A to be deleted have non-NULL sectionids. The sectionids just don't have corresponding rows in B. I believe the answer is that there isn't a way to fix the syntax for 3.23, because 3.23 simply doesn't support multi-table deletes. An alternative solution is needed. One option would be to do this programmatically. Collect the sectionids with a SELECT, then delete them with a separate query. Another option would be to select the good rows into a new table, then use this new table to recreate table A. Something like CREATE TABLE A2 SELECT A.* FROM A JOIN B ON A.sectionid = B.id; then DELETE FROM A; INSERT INTO A SELECT * FROM A2; or RENAME TABLE A TO A_old, A2 TO A; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
