Re: BDB table : different results on adjacent queries
Try running ANALYZE TABLE x; and redoing the query. If this fixes your query, there is some index corruption going on. Are there any other queries going through the system during these tests, or is it only your 2 threads? There were some BDB index bugs fixed in the last few mysql releases, but 3.23.41 should be fine. Im using .42 and having no more problems. dpk - Original Message - From: [EMAIL PROTECTED] To: 'Dana Powers' [EMAIL PROTECTED] Cc: mysql mailing list (E-Mail) [EMAIL PROTECTED] Sent: Thursday, September 20, 2001 7:23 AM Subject: RE: BDB table : different results on adjacent queries Hi Dana -Original Message- From: Dana Powers [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 19, 2001 7:19 PM To: [EMAIL PROTECTED] Cc: mysql mailing list (E-Mail) Subject: Re: BDB table : different results on adjacent queries What is the query you are running + what version of mysql are you using? dpk It was a simple 'select * from table where a=value and b=value'. Version: 3.23.41, compiled with BDB and INNODB support, running on Linux (SuSE 7.0). The table we used had the following structure: a varchar(36) not null, pri b varchar(50) null c varchar(4) null d varchar(50) null e decimal (10,0) null f varchar(36) null, mul g varchar(36) null, mul h varchar(50) not null i varchar(50) null there are 3 non_unique keys defined: f+b f+d g column f is filled with all the same values (262 records) the last test was performed with the command line tool mysql. query: select count(*) from table where f='the only value in that column'; count(*) = 262 - ok. (the value in column f is a GUID which uses the full length of the field.) I repeated the query several times, with always the same positive results. Then I opened another connection (as a different user), using the same database, applied my query, and ... OOPS ... count(*) = 0 ! I turned back to the other client and again performed the same query (using the commandline buffer). Now the result of the count was 0 also there !!! Next I tried another application: mysql_navigator (1.2.4), the same query, count was 262, ok. next try: added and b = '4004' to the where clause so that the count should be 1 as there is only one record with '4004' in column 'b'. result: count was 0 ! another try on the previous select : count is 2 instead of 262 ! so what ??? replaced 'count(*)' by '*': one of the resulting records is the one with '4004' in column 'b', the other one's 'b' value is 400403. There is another record in the table beginning with 4004 in column 'b' : value '40040307', but this one wasn't selected. I hope you have an idea about this very weird behaviour. Thanx in advance Andy - 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: BDB table : different results on adjacent queries
What is the query you are running + what version of mysql are you using? dpk - Original Message - From: [EMAIL PROTECTED] To: mysql mailing list (E-Mail) [EMAIL PROTECTED] Sent: Wednesday, September 19, 2001 2:12 AM Subject: BDB table : different results on adjacent queries hi all we encountered the following: - altered table type to 'BDB' - did 2 identical queries - got different result sets ! after restart of MySQL the very next query gave the correct results, the next did NOT. any ideas? Thanx in advance Andreas Schoelver -- Andreas Schoelver (AS) mailto: [EMAIL PROTECTED] mailto: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: need help to uninstall mysql
First, there is no need to reinstall. For future reference, if you forget your password - check the manual for an easy way to fix: http://www.mysql.com/doc/R/e/Resetting_permissions.html For a mysql that wont start, check the error log and isolate what the problem is ( it probably isnt that hard to fix ). The log is probably /var/lib/mysql/hostname.err , but Im not familiar with redhat so that is just a guess. dpk - Original Message - From: GUYOT Carole [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 18, 2001 6:21 AM Subject: need help to uninstall mysql I've changed root's password via netscape with phpmyadmin and then reloaded. It's not possible to connect anymore. The system asked for a new password but didn't accept the new one. So, I've tryed to re-install mysql again version : MySQL-3.23.38-1.i386.rpm on redhat Linux. Now, when I try to connect, I have ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) Things are getting worse and worse! How can I completely uninstall and get clean all files linked to mysql? Need urgent help please ! NTMail K12 - the Mail Server for Education - 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: NUMERIC field contents
Agreed. Definitely odd that it doesnt behave exactly as documented, but it does provide (9,2) precision, non? The fact that you can squeeze (10,2) into the column in certain cases doesnt bother me. Of course, if you came up with a patch to fix it, I dont think anyone would complain. dpk - Original Message - From: Chris Johnson [EMAIL PROTECTED] To: Jim Dickenson [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, September 18, 2001 8:01 AM Subject: Re: NUMERIC field contents On Sun, Sep 16, 2001 at 02:11:20PM -0700, Jim Dickenson wrote: Does anyone have an opinion about the following? Are people using decimal type fields? Is it important for the database to allow only allowed data? I use decimal datatype fields all the time for my monetary amounts. I've always treated them as behaving the way you suggest they should behave, i.e. no extra digit space is provided by using the sign location. That MySQL behaves otherwise seems a bit counter-intuitive. I design my tables and software to treat it otherwise and also to never expect the database to provide the limits. My software insures the limits are never reached. -- ..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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 3.23.41 and .42 threads problems
The quickest way to get this in front of developers is to provide a reproducible test case. I know they like to use the new mysql-test stuff, but its not heavily documented, so bash scripts or perl scripts are probably fine too. This may also help other people confirm the problems that you are seeing. dpk - Original Message - From: djinn [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 18, 2001 9:43 AM Subject: 3.23.41 and .42 threads problems I have posted before regarding a problem with mysql 3.23.41 hitting some sort of threshold limit and spawing threads like crazy. I have seen others post here with the exact symptoms, from what I understand these are all linux boxen. I have personally tested it with the 2.4 series of kernels, and others have reported the same behaviour in the 2.2 series. From what I'm reading, we all experience: 1) Number of concurrent queries and, subsequently, threads increasing rapidly 2) CPU utilization reaching 100% in seconds 3) load levels reaching 100+ in minutes 4) RAM behaving as normally as possible under the circumstances So far, the only fix I have found, despite a ton of suggestions from this list and a linux sys admin list I queried, has been to downgrade to 3.23.32. Which obviously is not optimal, because of all the cool new stuff in the later releases. I don't feel like I can post this to the bugs list because of the strict rules regarding the bug script there. It is obviously a problem tho, since more and more people are reporting identical behaviour. So. Are there patches not mentioned in INSTALL that we should be applying? Is there someone who can help get this to the developers' attention? Can anyone provide any information on this? Thanks jenn - 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: NUMERIC field contents
The problem is that a number that is in the MySQL database might be a magnitude of 10 times larger than a number that is in the PostgreSQL database. Could you explain this? dpk - 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: AW: MySQL is hogging my box
I assume you've done the slow-query-log review thing... making sure all your queries are fast, yes? dpk - Original Message - From: [EMAIL PROTECTED] To: Stefan Pinkert [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, September 18, 2001 11:22 AM Subject: Re: AW: MySQL is hogging my box There are some others with a similar problem(including me). The question still remain, if it is all caused by the same bug(?). What table-types do you use? (MyISAM,Merge,BDB,...) MyISAM How many entries are in the tables? Up to 145000 in some of the tables. But its not the usual stuff... normal tables have around 15000 entries. How many queries/second? Based on the numbers on my system right now: | Questions| 526358 | | Uptime | 2910 | That gives like 180 queries/sec, right? Isn't it a real big number??? Do the queries use indices? Yes they do... Ok, after asking I've run myisamchk on the tables and then after restarting it has started to go better. Not 50 load but 12, and slowly rising. Besides when I look at the processlist I see many (5 to 10 at a time) processes Locked for around 4 or 5 seconds. Any insights on this? Some guy has given me an insight about installing an older version of MySQL. All my problems have been with 3.23.38 and 3.23.42. I'll try the older version and would tell you what happens. Cheers. -- Luis Calero Muñoz $email{luis} = '[EMAIL PROTECTED]' $who{luis} = 'sysadm at ociojoven dot com' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB vs MyISAM on COUNT(*) ... WHERE ...
I believe the MyISAM format is so fast on simple SELECT count(*) FROM table; because it actually keeps the current number of rows as a table statistic - so it doesnt have to look at the data at all. Using a WHERE clause, however, will force MyISAM to actually select all the rows and count how many it finds. This is what InnoDB tables and others do. Of course doing your own testing shouldnt be that hard and is always the best way to figure out how your application will hold up. dpk - Original Message - From: Sander Pilon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 18, 2001 12:19 PM Subject: InnoDB vs MyISAM on COUNT(*) ... WHERE ... I'm thinking of switching to InnoDB, however - my application does a few COUNT(*) WHERE queries on large tables (somewhere between 50K and 2M rows) I've read up on InnoDB and its issues with COUNT(*) on entire tables, but is there a reason to assume that InnoDB is also slower when there is a WHERE clause present? Regards, Sander - 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: NUMERIC field contents
And my question is, if you've defined your column to have (10,2) precision, why would you try to insert a higher precision number? dpk - Original Message - From: Jim Dickenson [EMAIL PROTECTED] To: Dana Powers [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, September 18, 2001 1:35 PM Subject: Re: NUMERIC field contents On 9/18/01 11:25 AM, Dana Powers at [EMAIL PROTECTED] wrote: The problem is that a number that is in the MySQL database might be a magnitude of 10 times larger than a number that is in the PostgreSQL database. Could you explain this? dpk - 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 create a table in PostgreSQL as a single field with numeric(10,2). I create the same table in MySQL. I then run an ODBC application that calls SQLDescribeCol to find out what items are returned from select * from table. After getting a fix for MyODBC both data sources return a precision of 10 and a scale of 2. This is as I would expect. I then know to add room for the decimal point and the sign character. Even though I have room for a 12 digit number I expect the leftmost position to be used only for the sign. In PostgreSQL 7.1.3, using psql, if I try to put 123456789.12 into the table I get an error that the number is too big to fit. This is as expected. If I use mysql, Ver 11.15 Distrib 3.23.42 for pc-linux-gnu (i686), to put the same number into the table I do not get an error and the data is put into the database. This is not as expected. The reason I see this as a problem is that numeric(10,2) says that there are 10 digits in the number and two of them are decimal digits. The valid range is -99,999,999.99 through 99,999,999.99 but MySQL is allowing 11 digits to be put into the database. If my ODBC application expects data in a certain range based on the information returned from SQLDescribeCol then it will not be expecting to see a number with 11 digits in it. This is what I think is wrong with the behavior of MySQL. -- Jim Dickenson mailto:[EMAIL PROTECTED] Computers for Marketing Corporation http://www.cfmc.com/ eFax: 1-419-791-8924 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mySQL database files help
You will not get 3.22 to recognize MyISAM format tables. You need to either: 1. Upgrade your server to 3.23 or 2. Find a running 3.23 server, copy your table files to that server, ALTER TABLE xxx TYPE=ISAM; on all your tables to convert back to older ISAM format, and copy your table files back. Of course, option 1 is prefered. dpk - Original Message - From: Chase Peeler chase@mail To: [EMAIL PROTECTED] Sent: Tuesday, September 18, 2001 4:50 PM Subject: mySQL database files help I was running a mySQL server under slackware for a while, it was interacting with php on an apache webserver, and everything worked fine. There were a few times where I would mess linux up and need to re-install,in which case I would move my database files to my windows partition, re-install linux, move the database files back (database files being everything in /var/lib/mysql) and everythign worked fine. I just decided to change to Debian, and followed the same procedure. After moving my database files back to the /var/lib/mysql directory, and running mysql, if I try to do anything with my tables, i would get the message dbfile.ISM is missing. When I looked in my directory, all the files were of the format dbFile.MYD, dbFile.MYI and dbFile.frm. As far as I know, the files have always been of these extensions (instead of .ISM, .ISD and .frm). If they have not, I have no idea how they changed. Does anyone know how to get mySql (version Ver 9.38 Distrib 3.22.32, for pc-linux-gnu (i686) ) to either recognize my current files as valid, or how to get the data in those files into something that mySQL will recognize. Thank you very much. There is alot of important info in those tables, and I really need them -- -Chase - 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: Error From Web Site
Search the manual, check the archives, blah blah blah. This question was answered yesterday. See forwarded message below. dpk - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Jay Fesco [EMAIL PROTECTED]; Paul Reilly [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, September 17, 2001 1:36 PM Subject: RE: reset auto_increment? At 3:15 PM -0400 9/17/01, Jay Fesco wrote: Can someone tell me how to reset a column's auto_increment starting point back to 0 in mysql ? Thanks Paul According to Paul DuBois on page 169 of MySQL by New Riders (which you should buy): ALTER TABLE t DROP i, ADD i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY Jay Actually, this will drop the column and renumber the rows sequentially. To just reset the sequence to 0 (well, to 1, since sequences start at 1), do this: ALTER TABLE t AUTO_INCREMENT = 1 -- Paul DuBois, [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB table error
Thanks for the reply! I can understand the situation with muti statement transactions, but I'm not using transactions ( at least from my perspective ); Im only issuing single 'atomic' statements. It would make much more sense if mysql internally detected the deadlock, released the locks and tried to process the statement again. It just seems so counter-intuitive that i can connect, issue a single, isolated select statement and get a deadlock. I dont know if this is possible, but I would love something like this in the mysql engine: retry-deadlock: error = do_query(); if ( ( auto-commit == on ) ( error == DEADLOCK ) ( ++tries threshold ) ) { warn('Deadlock detected. Retrying query.'); goto retry-deadlock; } Of course this could go into the application code, but it would have to go around every single query, transactional or not, and that seems so cumbersome given that the solution is the same in every case. dpk - Original Message - From: Michael Widenius [EMAIL PROTECTED] To: Dana Powers [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, September 17, 2001 2:19 PM Subject: Re: BDB table error Hi! Dana == Dana Powers [EMAIL PROTECTED] writes: Dana Certainly I understand how the deadlock can occur, but the question is why Dana is this acceptable behaviour? Is the application developer expected to Dana handle this case? If so, how? and shouldnt this be included in the manual Dana under BDB problems? If Im supposed to wrap _every_ sql call I make to a BDB Dana table with something like: 'do { query } while ( !deadlock );' why would I Dana even bother trying to use BDB tables? This seems to me like a _fundamental_ Dana weakness of the BDB table type, and should at least be documented as such... We shall will add som more documentation about this ASAP. In MySQL 3.23.43 you will get the following error: Error: 1213 (ER_LOCK_DEADLOCK) Deadlock found when trying to get lock; Try restarting transaction, Anyway, the problem you are experiencing is something that is normal with all transactional databases. When you have many clients that is reading/updating the same table there is always cases when you will get deadlocks or other failure in the transaction and you have to redo the whole transaction in the application. Regards, Monty - 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] - 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: Newbie queries
Some people like to use MS Access as a frontend using MyODBC to 'link tables'. If you have access, you'll need to install myodbc ( available in the downloads section of www.mysql.com ), configure a datasource connection with a valid username + password etc, and then in access: File - Get External Data - Link Tables Then choose 'ODBC Connections' in the file type drop down. Select your newly created connection ( from myodbc installation above ), choose your tables and booyaah - your mysql data will seem just like access tables! dpk - Original Message - From: Amit Sathe [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 17, 2001 10:36 PM Subject: Newbie queries Hi I am new to My SQL and have just installed it on a Win 98 machine. can you recommend any good front end tool to generate reports ? At present the interface available for both input and output is phpAdmin. Also how does one import data from excel; is it possible ? ans - 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: mySQL database files help
It appears that your change to debian was also a change from the current 3.23 mysql version to the old 3.22 version ( major releases ). The old version, 3.22, does not support MyISAM tables, so you'll need to upgrade to the new version. Debian stable, aka potato, uses 3.22, but both woody + sid ( testing + unstable ) are using 3.23. I have no problems running apache + php4 + mysql on debian unstable, if you are concerned about upgrading to the 'non' stable trees. I dont know if you can find a potato-fied mysql-3.23 .deb out there, but it may be available. dpk - Original Message - From: Chase Peeler chase@mail To: [EMAIL PROTECTED] Sent: Monday, September 17, 2001 8:05 PM Subject: mySQL database files help I was running a mySQL server under slackware for a while, it was interacting with php on an apache webserver, and everything worked fine. There were a few times where I would mess linux up and need to re-install, in which case I would move my database files to my windows partition, re-install linux, move the database files back (database files being everything in /var/lib/mysql) and everythign worked fine. I just decided to change to Debian, and followed the same procedure. After moving my database files back to the /var/lib/mysql directory, and running mysql, if I try to do anything with my tables, i would get the message dbfile.ISM is missing. When I looked in my directory, all the files were of the format dbFile.MYD, dbFile.MYI and dbFile.frm. As far as I know, the files have always been of these extensions (instead of .ISM, .ISD and .frm). If they have not, I have no idea how they changed. Does anyone know how to get mySql (version Ver 9.38 Distrib 3.22.32, for pc-linux-gnu (i686) ) to either recognize my current files as valid, or how to get the data in those files into something that mySQL will recognize. Thank you very much. There is alot of important info in those tables, and I really need them. -Chase - 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: Can't get MySQL running on RH7.0 at all.
The error logs seem to indicate that the pid file you've specified is not writable and that the system database tables are not installed properly. 1. make sure that you ran mysql_install_db. If you did, make sure that you didnt change the my.cnf datadir setting without moving your system files or re running mysql_install_db. 2. ( #1 usually takes care of this, but ) make sure the pid file is writable ( find path in error log message or ./mysqld --help or /etc/my.cnf if you've overrided default ) dpk - Original Message - From: Mark Johnson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 17, 2001 9:42 PM Subject: Can't get MySQL running on RH7.0 at all. I must be a moron but I can't seem to figure out what I need to do to get mysql to work on RH, on Mandrake it just worked straight away. I read and followed the instructions in the INSTALL-BINARY (inspite of the fact that the way red hat installed mysql does not even closely resemble the instructions) but mysql will not come up. This is the error log: 010917 11:26:38 mysqld started 010917 11:26:38 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 010917 11:26:38 mysqld ended 010917 11:32:09 mysqld started 010917 11:32:10 /usr/libexec/mysqld: Can't create/write to file '/var/run/mysqld.pid' (Errcode: 13) 010917 11:32:10 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 010917 11:32:10 /usr/libexec/mysqld: Error on delete of '/var/run/mysqld.pid' (Errcode: 2) 010917 11:32:10 mysqld ended There must be some secret bit of knowledge that is obvious to everybody else and i'm just not reading the right document or something. I'm sure somebody has been able to install MySQL via the RPMs and get it to work. Can anyone help me with this? Do I need to create a user called mysql and add them to the group root? I'm suspicious of the permission problems... thanks for your time... - 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: BDB table error
Certainly I understand how the deadlock can occur, but the question is why is this acceptable behaviour? Is the application developer expected to handle this case? If so, how? and shouldnt this be included in the manual under BDB problems? If Im supposed to wrap _every_ sql call I make to a BDB table with something like: 'do { query } while ( !deadlock );' why would I even bother trying to use BDB tables? This seems to me like a _fundamental_ weakness of the BDB table type, and should at least be documented as such... dpk - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, September 16, 2001 2:14 AM Subject: Re: BDB table error Dana, if you have page level locking, you can easily get a deadlock from an atomic insert done as a single transaction, and another user doing an atomic select in another single transaction. It happens this way: User A User B insert recordselect record from secondary to primary index index, get S-lock on page 2 get X-lock on page 1 select record from primary index get X-lock on page 1 - MUST WAIT insert record to secondary index get X-lock on page 2 -MUST WAIT - DEADLOCK Regards, Heikki http://www.innodb.com Ok, so the problem Im seeing is that any table that has an index other than the primary key is prone to deadlocks. I cant think of any reason that this is acceptable behaviour ( i.e. not a bug ), because I am doing 4 atomic operations on a single table with no user level locks.Here is a test: CREATE TABLE bdb_test ( id int unsigned not null auto_increment primary key, key1 char(32) not null, val1 char(32), unique (key1) ) TYPE=BDB; ( replacing unique(key1) with index(key1) also results in deadlocks ). Now run the following 4 queries in parallel: ## Insert a new row with 2 key values - 1 auto_incremented and 1 specified INSERT INTO bdb_test (key1) VALUES(CONCAT(CONNECTION_ID(),' - some extratext')); ## Update a non key column in row UPDATE bdb_test SET val1='test val' WHERE id=LAST_INSERT_ID(); ## Select based on secondary key SELECT * FROM bdb_test WHERE key1=CONCAT(CONNECTION_ID(),' - some extratext'); ## Select based on primary keySELECT * FROM bdb_test WHERE id=LAST_INSERT_ID(); I get deadlocks on my machine ( Intel w/ Dual 400 + 512MB RAM, running linux ) with 64 simultaneous threads. It appears that as the table gets larger ( and more pages created for locking, hehe ), the probability that we get a deadlock grows. - 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] - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB table error
I've found the answer to my problem in the bdb source... From bdb/include/db.src: #defineDB_LOCK_DEADLOCK(-30996)/* Deadlock. */ So the question is: how can a bunch of simultaneous connections that only work on 1 table create a deadlock. And how can I write my code to avoid this problem? Is this an intrinsic problem with BDB, a problem with the way mysql handles BDB, or a problem with my application code? Additionaly, it would be nice if either the mysql engine returned a string-ified version of the error code or if perror could handle these error codes. dpk - Original Message - From: Dana Powers [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, September 15, 2001 1:52 PM Subject: BDB table error Im getting this error from attempted inserts into a BDB table using mysql-3.23.42 ( same error but more often in 3.23.38 ): Could not insert new row into SESSION_DATA: Got error -30996 from table handler First - I have not been able to find what this error means in either the source code, online, or with tools like perror ( doesnt like negative numbers ). Does anyone have an idea what this is, or how to find out? Info on the situation: I've got X simultaneous connections inserting and updating rows, and this error consistently pops up if X 16. If X = 32, more than half of the inserts are errors. Here is the table: CREATE TABLE SESSION_DATA ( SESSION_ID int(10) unsigned NOT NULL auto_increment, SESSION_KEY varchar(32) default NULL, TS_ACCESS timestamp(14) NOT NULL, DATA text NOT NULL, PRIMARY KEY (SESSION_ID), UNIQUE KEY SESSION_DATA___SESSION_KEY (SESSION_KEY) ) TYPE=BerkeleyDB; Here are the sql statements used: ## Create a Session SET AUTOCOMMIT=0; INSERT INTO SESSION_DATA (SESSION_KEY,TS_ACCESS,DATA) VALUES (NULL,NULL,''); UPDATE SESSION_DATA SET SESSION_KEY='32bytesofblah',TS_ACCESS=NULL,DATA='' WHERE SESSION_ID=###; ( ### was set to LAST_INSERT_ID() between sql statements ) COMMIT; SET AUTOCOMMIT=1; ## Update a Session ( AUTOCOMMIT=1 ) UPDATE SESSION_DATA SET SESSION_KEY='32bytes ofblah',TS_ACCESS=NOW(),DATA='a load of text...' WHERE SESSION_ID=###; thanks for any info, dpk - 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] - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB table error
I can totally understand that, but the problem is, my queries are only interested in one row each ( accessed by primary key ). Perhaps it has to do with the extra unique index? The flow is this: insert row. grab newly created primary key ( autoincrement ) + generate a unique 32 byte char string based on the primary key. update row to set unique char string. commit | rollback Could it be that bdb needs to grab a page lock on the index as well? hmm, that might explain it. dpk - Original Message - From: Christian Sage [EMAIL PROTECTED] To: Dana Powers [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, September 15, 2001 3:08 PM Subject: AW: BDB table error Dana, a deadlock can easily occur on a single table with row-level (or page-level) locking. What happens is something like the following: connectionhas lock on wants lock on alpha object Aobject B beta object Bobject A With page-level locking this would obviously be possible to happen only if the objects resided in different pages. Anyway, this type of situation cannot be resolved by the connections on their own, because they both see only their own context and end up sitting there indefinitely waiting for the object they want to be freed. Therefore, it must be handled by either the application code or the rdbms itself. Some of the other database systems I know detect this situation on their own. Oracle, for example, will roll back one of the contending connections and write a trace file plus an entry in its alert log (for an ORA-00060 error). Sadly, I don't know anything about BDB, so I can't really help you. Generally speaking I've not yet met a situation where it was necessary to sequentially lock several objects on the same table, though. I may be wrong, but as far as I can see this would seem to point at either loose design (not fully normalized - if the data is normalized you simply go and lock the (single) parent object, then all child objects of this parent are implicitly locked if all connections behave in the same way) or at sub-optimal coding (atomicity of operations should have been preserved). No offense intended, as I say, I may be totally off the beam here. Cheers, Christian Sage - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problems with a fairly basic SELECT/JOIN...
Try this: SELECT news.NEWSID, news.DATETIME, authors.AUTHOR, count(comments.CID) as NUMCOMS from news LEFT JOIN authors ON news.AUTHORID=authors.AUTHORID LEFT JOIN comments ON news.NEWSID=comments.NEWSID GROUP BY news.NEWSID ORDER BY news.DATETIME; I havent tested this, and you may have to add news.DATETIME to the GROUP BY clause, but this should work AFAIK. If you send create scripts + test data for the tables, you're more likely to get a faster response... dpk - Original Message - From: Chad Day [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Friday, September 14, 2001 11:53 AM Subject: problems with a fairly basic SELECT/JOIN... Sending this out again, haven't gotten any help yet.. anyone? -Original Message- From: Chad Day [mailto:[EMAIL PROTECTED]] Sent: Friday, September 14, 2001 9:38 AM To: mysql Subject: problems with a fairly basic join.. been banging at this for a while, can't get my head around it. 3 tables, one called news that stores news items, one called comments that stores user comments, one called authors that holds the author of the news items name. I am trying to display the news item with the authors name, and count the # of comments on the news item. The closest I've come is: SELECT DISTINCT news.NEWSID, COUNT(*) as NUMCOMS from comments, news left join authors on news.AUTHORID = authors.AUTHORID where comments.NEWSID = news.NEWSID GROUP by comments.NEWSID, news.NEWSID ORDER BY news.DATETIME DESC; but that obviously is flawed in this respect: 'where comments.NEWSID = news.NEWSID' as if there are no comments, nothing matches and no news item is returned. I can't seem to figure out exactly what to do, I'm hoping someone can help.. below are my table structures. mysql describe comments; +--+---+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-++ | CID | int(11) | | PRI | 0 | auto_increment | | NEWSID | int(11) | | | 0 || | USERID | int(11) | | | 0 || | TITLE| text | | | NULL|| | COMMENT | blob | | | NULL|| | DATETIME | timestamp(14) | YES | | NULL|| +--+---+--+-+-++ mysql describe news; +---+---+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-++ | NEWSID| int(11) | | PRI | 0 | auto_increment | | AUTHORID | tinyint(4)| | | 0 || | TITLE | tinytext | | | NULL|| | LINE | tinytext | | | NULL|| | LEAD | blob | | | NULL|| | REMAINING | blob | | | NULL|| | DATETIME | timestamp(14) | YES | | NULL|| | CATEGORY | tinytext | | | NULL|| +---+---+--+-+-++ mysql describe authors; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | AUTHORID | int(11) | | PRI | 0 | auto_increment | | AUTHOR | tinytext| | | NULL|| | PASSWORD | varchar(16) | | | || | EMAIL| tinytext| | | NULL|| +--+-+--+-+-++ Thanks for any help! Chad - 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,
Re: mySQL: ORDER
d00d. manual. search it. http://www.mysql.com/doc/S/o/Sorting_rows.html ORDER BY VOTES DESC dpk - Original Message - From: Russ [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, September 14, 2001 3:27 PM Subject: mySQL: ORDER how do i sort my results by reverse order ?? say the collume containts 1 2 3 4 , if i use ORDER BY VOTES it returnes 1234 but i wont it in revers 4321 etc is there any way i can do this with a sql function? Russ - 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: Let's approach stored procedures
Depending on the way you define things, MIN and MAX are essentially stored procedures that were programmed into MySQL. Why not let us make our own? This is already possible with Aggregate UDF 's. Check the manual here: http://www.mysql.com/doc/A/d/Adding_functions.html dpk - 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: Stored procedures (2)
- The Perl stored procedures (myperl project) in not a good idea at all, but, even if called poor man's stored procedure, I think that this project don't add, actually, stored procedure in the precise meamning of term. It add only a binding with Perl via UDF. It's a nice (and quite simple) hack. Dana Power, I've not take a look to _your_ hack, but I think this is the same approach. Actually, its not. Its more along the lines of apache's CGI vs. mod_perl. And its not just perl, its an architecture for pluggable languages ( python, javascript, lisp, or your new pl/sql clone ), which is exactly what you're looking for, if im not mistaken. The problem you're going to have is that MySQL does not support callbacks into the engine - i.e. youre in the middle of an sql call when your function gets called ( whether its udf or something else doesnt matter ), halting everything - effectively pushing it onto 'the stack' - and making separate sql calls, is not supported. You'll find that the hardest part is overcoming the current 'atomic' architecture, which has been used to optimize pretty much every part of the engine ( atomic meaning that 1 sql call is 1 simple actions. ). Of course CREATE ... SELECT and INSERT ... SELECT are simple exceptions, but the same reason that Sub-Selects are non-trivial, is the same reason that stored procedures are non-trivial. dpk - 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: Weird table corruption?
It might be easier to see what is happening if you do something like this: echo select name from pairings where fid=0 and pid=1 and name != 'One'; | mysql -u... -p... yourdatabase | hexdump -c this will show you exactly what data is being returned. Chances are there is a funky character in there somewhere. dpk - Original Message - From: Philip Mak [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, September 08, 2001 2:18 PM Subject: Weird table corruption? Does anyone know what happened here? Notice the strangely formatted output from the second command. I did a repair table but it doesn't seem to detect any table corruption. mysql repair table pairings; +---++--+--+ | Table | Op | Msg_type | Msg_text | +---++--+--+ | shoujoai.pairings | repair | status | OK | +---++--+--+ 1 row in set (0.01 sec) mysql select name from pairings where fid=0 and pid=1 and name != 'One'; +--+ | name | +--+ |Two +--+ 1 row in set (0.01 sec) __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Let's approach stored procedures
Claudio Cicali ([EMAIL PROTECTED]) writes: - where I work, we have a HUGE database-driven web-application. A lot of our businness logic is implemented via stored procedures, that act as black boxes for the web-designers. Think of enterprise java beans. They are not nonsense or such. They are usefull. (I know, you can use ejb with JDBS and mysql..., but if you want to have some logic incapsualted, you should use some kind of component) - sp extends the RDBMS itself in its functionality. Think about some stupid check_fiscal_code() or insert_new_customer(). Web designers use the insert_new_customer, instead of using SQL directly. I have found that the same thing can be achieved using a middle layer like XML-RPC or SOAP. This also allows business logic to appear closer to native functions in your web programming language than still having to use SQL. For example, if you use mod_perl, you can just call 'insert_new_customer()' from perl and this gets transmorgified across XML-RPC to a transaction in the database with very little extra code. Ive been thinking/working on this problem for a while and it seems that there are 2 major issues: 1. Ability to create arbitrary functions in some language X. 2. Ability for arbitrary function to call back into the database. Problem 1 is pretty straightforward. I've even put a patch for an older version of mysql up at http://www.tanisconsulting.com/mysql-perl.html that creates a language 'plugin' architecture and implements perl for starters. ( python would be easy, as would javascript or lisp etc.. I just happen to be a perl hacker ) Problem 2 is not, or at least I havent come up with a good, non-intrusive way to accomplish it yet. dpk - 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: Limit query to a value
Add 'HAVING Tot0' to your query. dpk - Original Message - From: Lorang Jacques [EMAIL PROTECTED] To: Mysql [EMAIL PROTECTED] Sent: Friday, September 07, 2001 10:12 AM Subject: Limit query to a value Hello, How can I limit the query to those rows where SUM(value) as Tot 0. If I put it in an where clause it get an error. So how to do that SELECT id,url,title,description,top,keyword,SUM(value) as Tot FROM links LEFT JOIN keywords on links.id=keywords.LinkId Group by id order by tot desc Lorang Jacques sql is 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 - 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: Perl DBI: Same column name in different tables problem
Or the perl way: my %row; @row{'f_handle','a_handle'} = $sth-fetchrow_array(); $row{f_handle} or $row{a_handle}are now set properly. ( if you really want a reference, you can say 'my $row = {}; @{$row}{'f_handle. ) dpk - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Philip Mak [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, September 07, 2001 10:48 PM Subject: Re: Perl DBI: Same column name in different tables problem On Fri, Sep 07, 2001 at 10:03:03PM -0700, Philip Mak wrote: Let's say I performed the following query using Perl DBI: $row = $dbh-selectrow_hashref(~); SELECT fanfics.handle, authors.handle FROM fanfics, authors WHERE fanfics.aid = authors.aid ~ I won't be able to access both fanfics.handle and authors.handle this way, because they're called handle. I would like to be able to access them e.g. by doing $row-{fanfics.handle} and $row-{authors.handle}, or something like that. What workarounds have people found for this problem? Use a column alias: SELECT fanfics.handle as f_handle, authors.handle as a_handle FROM fanfics, authors WHERE fanfics.aid = authors.aid Then refer to them as $row-{a_handle} or $row-{f_handle}. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 2 days, processed 42,300,526 queries (237/sec. avg) - 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: Last Inserted Record Question
LAST_INSERT_ID() is similar. It will give you the value for autoincrement fields. Note that this is connection dependent ( so your last_insert_id() is not the same as my last_insert_id() ). You would do something like this: INSERT INTO tablex ( columnx ) VALUES ( 'valuex' ); INSERT INTO tabley ( tablex_id, columny ) VALUES ( LAST_INSERT_ID(), 'valuey' ); There are some caveats, of course, so check the great online documentation at www.mysql.com/docs. Search for autoincrement or last_insert_id() dpk - Original Message - From: Dave Carter [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 06, 2001 1:24 PM Subject: Last Inserted Record Question Access and MS-SQL Server have a method called @@IDENTITY used in retrieving the last inserted ID of a recordset. Is mySQL ok with that command. I suppose I could try it, but I'm interested in views etc. also. Dave Carter Chief Web Architect Accelerated Business Technologies, Inc. http://www.abti.net 717.464.2970 - 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
BDB index corruption
I have been having consistent problems with index corruption using BDB tables in the 3.23.38 source version. Looking at the Change Log entries for 3.23.40, i think upgrading may help me out. Does anyone have a more detailed explanation of the bugs addressed by these two fixes: Changes in release 3.23.40 - Fixed problem with UPDATE and BDB tables. - Fixed hard bug in BDB tables when using key parts. thanks, dpk - 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: command line open source ARRRGGGGGG!!!!!!
I know these are pathetic questions to most of you so please have patience with a Windows user... Hehe, welcome to the world of the command line... what is shell? In windows world, this is the 'MS-DOS Prompt'. Will probably look like this to you: C:\ In unix, it is typically something like this: user@host:~/ But can be anything, because unix has many types of prompts to choose from sh, bash, csh, korn to name a few ). what is the ./? This is shorthand for the current directory. In the dos prompt, use .\ is mysql_installation_directory supposed to be replaced with my installation dir? Yes. I realize that I'm going to be in the minority here but I have to say it. Open source would go much farther if they had self explanatory GUIs and had basic pre-setup apps that were ready for the average user to use. I program in Java and I feel the same about the JDK Sun produces. I use JCreator because it has an easy to follow GUI interface. Most open source apps I have attempted to use have some of the worse documentation around. The writers assume so much about the users. OK - call me inept, stupid, or whatever but I had to say it. True. But mysql's user mailing list is typically _very_ helpful. hope this helps, dpk - 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: SQL basica question again
SELECT COUNT(*) as count, hostname FROM host_list GROUP BY hostname ORDER BY count DESC here is my current output 283 host1 210 host2 200 host10 110 host 3 . 1 host941 what I would like to do is list say just the hosts that have greater then 10 in the left column Try adding 'HAVING count 10' to your query. dpk - 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: cannot get mysqld to start
Quoteth the install guide: shell cd mysql_installation_directory shell ./bin/mysql_install_db shell ./bin/safe_mysqld --user=mysql This is always the way to start a fresh mysql install. Note that safe_mysqld gets the --user arg ( and is run in the background with ). Also remember to set your root password! Check the manual for this as well. dpk - Original Message - From: Sheena Sidhu [EMAIL PROTECTED] To: Dana Powers [EMAIL PROTECTED]; Sheena Sidhu [EMAIL PROTECTED]; Mysql (E-mail) [EMAIL PROTECTED] Sent: Wednesday, August 29, 2001 11:41 AM Subject: RE: cannot get mysqld to start Dana, Thanks for looking into this further. I did all that, and ran the command mysql_install_db instead of mysql_install_db --user=mysql as they said in the install guide! I have no clue of the difference between the two and reason why it should work on one and not on another. Would appreciate if you could shed some light on this. Thanks, Sheena. -Original Message- From: Dana Powers [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 28, 2001 4:20 PM To: Sheena Sidhu; Mysql (E-mail) Subject: Re: cannot get mysqld to start This is covered in the mysql manual here: http://www.mysql.com/doc/P/o/Post-installation.html First, you need to install the default databases ( installs mysql/host.frm ): bash mysql_install_db Then tell mysql not to use InnoDB: put this in your /etc/my.cnf file: [mysqld] skip-innodb dpk - Original Message - From: Sheena Sidhu [EMAIL PROTECTED] To: Mysql (E-mail) [EMAIL PROTECTED] Sent: Tuesday, August 28, 2001 2:42 PM Subject: cannot get mysqld to start Hello, I just installed mysql-max-3.23.41 on Linux. All went smooth but alas cannot get myqld to start. here is the log from the systemname.err file in mysql/data directory: 010828 14:26:30 mysqld started Can't initialize InnoDB as 'innodb_data_file_path' is not set 010828 14:26:30 /usr/local/mysql-max-3.23.41-pc-linux-gnu-i686/bin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 010828 14:26:30 mysqld ended Now, I am interested in BDB only, so don't care about Innodb. Seems like I have to . But what do I do about the host.frm file ? Gosh , can someone PLEASE help me here ??? Thanks, Sheena. - 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: reports? from MySQL
linking tables via MyODBC + MS Access works great for me. dpk - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 28, 2001 12:06 AM Subject: reports? from MySQL hello. with a lot of help from many of you and some very nice folks, i have been able to build some example/experiment/'mess around' type of dbs. yay. i'd like to ask... if any of you are familar with (File Maker Pro, most recent versions)... how does one go about using MySQL to create a printable report? e.g., where you select the fields you wanted in the report and then create a layout and page setup for the report, (then print it)? ALL 'directional' aids are welcome... books, websites, tips, personal knowledge. EXAMPLES are super. Thanks in Advance. TR - 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: cannot get mysqld to start
This is covered in the mysql manual here: http://www.mysql.com/doc/P/o/Post-installation.html First, you need to install the default databases ( installs mysql/host.frm ): bash mysql_install_db Then tell mysql not to use InnoDB: put this in your /etc/my.cnf file: [mysqld] skip-innodb dpk - Original Message - From: Sheena Sidhu [EMAIL PROTECTED] To: Mysql (E-mail) [EMAIL PROTECTED] Sent: Tuesday, August 28, 2001 2:42 PM Subject: cannot get mysqld to start Hello, I just installed mysql-max-3.23.41 on Linux. All went smooth but alas cannot get myqld to start. here is the log from the systemname.err file in mysql/data directory: 010828 14:26:30 mysqld started Can't initialize InnoDB as 'innodb_data_file_path' is not set 010828 14:26:30 /usr/local/mysql-max-3.23.41-pc-linux-gnu-i686/bin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 010828 14:26:30 mysqld ended Now, I am interested in BDB only, so don't care about Innodb. Seems like I have to . But what do I do about the host.frm file ? Gosh , can someone PLEASE help me here ??? Thanks, Sheena. - 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: ERROR 1044: Access denied
ERROR 1044: Access denied for user: '@localhost' to database 'hello' This says that you are connected as user '' ( blank ) from localhost. Make sure you're logging on correctly and try again. dpk - 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: ERROR 1044: Access denied
The way mysql access tables work, if it cant find a user named 'user2', it will default to the blank user ( '' ). dpk - Original Message - From: Pierre-Yves [EMAIL PROTECTED] To: Dana Powers [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, August 28, 2001 4:39 PM Subject: Re: ERROR 1044: Access denied - Original Message - From: Dana Powers [EMAIL PROTECTED] To: Pierre-Yves [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, August 29, 2001 1:22 AM Subject: Re: ERROR 1044: Access denied ERROR 1044: Access denied for user: '@localhost' to database 'hello' This says that you are connected as user '' ( blank ) from localhost. Make sure you're logging on correctly and try again. dpk but I use the following command to connect mysql : mysql --user=user2 --host=localhost (there is no password for this user) and when I do this with the user1 it is working... so i don't think that's the problem. - 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: sorting this egroup
Every mail on this list has the following header: List-ID: mysql.mysql.com I use maildrop with the following filter: if ( /^List-ID\: \mysql\.mysql\.com\/) { to Maildir/.mysql } hope this helps, dpk - Original Message - From: Glyndower [EMAIL PROTECTED] To: George Pitcher [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, August 27, 2001 5:55 PM Subject: sorting this egroup Maybe I overlooking the obvious here, but hey it won't be the first time. How does everyone sort this group in their email client? I have yet to find a way to manage these emails because of the lack of a consistent item in each email. i.e MySql in the subject line etc Suggestions? - 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