Re: Newbie Question - can I collapse these two queries into one line?
on 12/29/03 9:54 AM, Dave G wrote: $wsQuery = SELECT improvwsid FROM improvws WHERE wsdate ' . $today . ' AND wsdate ' . $sevenDays . ' AND cancelled = 0; $wsResult = mysql_query($wsQuery); $wsid = mysql_result($wsResult, 0, improvwsid); $emailQuery = SELECT members.email AS email, members.firstname AS firstname, members.lastname AS lastname FROM members, improvwsattend WHERE members.id = attend.attendeeid AND attend.improvwsid = . $wsid; $emailResult = mysql_query($emailQuery); One extra join will do it. You're looking for all improvws in the next seven days that aren't cancelled, right? $query = SELECT members.email, members.firstname, members.lastname FROM improvws, improvwsattend, members WHERE improvws.wsdate '$today' AND improvws. wsdate '$sevenDays' AND improvws. cancelled=0 AND improvws.wsid = attend.improvwsid AND attend.attendeeid = members.id Also, are you sure you don't want = and = ? While this is getting the job done, it doesn't sit right because it feels like I'm being inefficient. There must be a way to get the results I want in one query. But not only can I not figure out how to do that, I'm at a loss as to how to describe where my thinking is going wrong. Any help would be much appreciated. That's because you were being inefficient. :) -- Bob IQ2GI5SCP2 Things You Don't Hear Every Day, #'s 16 and 17: A professor: It's all right if you come to class high. A(nother) professor: I think base 16 is cool. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help on Select/Join
on 12/29/03 12:00 PM, Bjoern Wuest wrote: Hello I have the following problem: Table1 'pl': +---+---+ | p | o | +---+---+ Table2 'ln': +---+--+---+ | p | l | v | +---+--+---+ Now my problem: how to write a statement to select all 'p' and 'v' from 'pl' and 'ln' where 'o=1' and 'l=de' or 'l=null if l=de is unavailable'. The result is shown below: So... something like this? SELECT ln.p, ln.v FROM pl, ln WHERE pl.p = ln.p AND ( (pl.o = 1 AND ln.l = 'de') OR ln.l IS NULL); -- Bob IQ2GI5SCP2 Things You Don't Hear Every Day, #'s 16 and 17: A professor: It's all right if you come to class high. A(nother) professor: I think base 16 is cool. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie Question - can I collapse these two queries into one line? [SOLVED]
One extra join will do it. You're looking for all improvws in the next seven days that aren't cancelled, right? Yes... thank you. I see where I was going wrong now. The extra joins I needed were to match the wsid to the member.id by matching both to where they appear in the attend table. Previously I was trying to match the member.id directly to the date, which was confusing me. Also, are you sure you don't want = and = ? Um... well, I'm to new to be sure about anything, but I'll try it that way and see if that gets the results I'm expecting. That's because you were being inefficient. :) Thanks for taking the time to respond! My script is working now! -- Yoroshiku! Dave G [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Prepared statements and default values
I see that in the prepared statements C API, I can specify to the bind parameter that the value is supposed to be null using MYSQL_BIND.is_null. Is there any support for the MYSQL_BIND object to use a column's default value? - Yossie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help on Select/Join
Dear all, thank you very much for the fast and numerous responses. The idea of Roger Baklund does exactly what I want. Thank you very much Roger. I oversee the possibility to use IF conditions on MySQL server, this is what I wanted to do programmatically but to do in the DB server is ok. Let's see how this operation will perform with a half million rows. Memory I should have enough. Thanks again Bjoern -- +++ GMX - die erste Adresse für Mail, Message, More +++ Neu: Preissenkung für MMS und FreeMMS! http://www.gmx.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does 5.0 contain the features of 4.1 and 4.1.1?
Hi List, Just a dumb question perhaps, but I am looking at upgrading to 5.0, and as we are happy with the performance and features on 4.1, can we expect the same features to have the same level of stability on 5.0? Also are the features of 4.1.1 also included? I need to start looking at stored procedures and hence the question...thanks! Terence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL user in Indonesia
I am working to port our database to mysql now. I work in PT. ADETEX. Sorry for my english. Regards, Hendro -Original Message- From: Leo [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 1:25 PM To: [EMAIL PROTECTED] Subject: MySQL user in Indonesia Hi All, im sorry if this mail a bit out of topic =) im currently developing a new system based on MySQL for the company i work on. and i have to report how wide and famous is mysql had been used, especially in indonesia. i need the fact that i can show to my boss, why i choose mysql.. well i have a lot of it, but he demand some example, which company in indonesia had successfully implemented mysql in their core bussiness so please, let me know.. i already run a search at google, but i have trouble determining what kwyword should i use if there is among you that work in a company here in indonesia, please, would you kindly share your experience thanks in advance -- Regards Leonardus Setiabudi IT Project Coordinator PT Bina San Prima, www.binasanprima.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
O another thing
I cant add a database.. I have Microsoft access installed. my webpage is www.overclockers-layer.dontexist.com http://www.overclockers-layer.dontexist.com/ go to the forum page and you will see another error if you go to www.crazymod.dyndns.org:8080/public/fm/upload/sm_install.php my username is asus and password is admin that is for both forum and database! HELP thanks
Re: 5.0 binary question
Hi Rick, I have installed binary alpha 5.0 for Linux x86. And I am able to create stored procs. But I found couple problems with stored procs: 1. I have stored proc sp_GetData(param char(31)). When I call stored proc first time (with any parameter) it works just fine, but if I change parameter to param2 and rerun it then it returns me empty resultset. I have tried to call sp with param2 after reconnection to MySQL, and it works perfect. 2. Sometimes MySQL server crashed when I call sp_GetData. Sorry for bad description of the problems. I am still working on getting repeatable case. Mikhail. - Original Message - From: Rick Robinson [EMAIL PROTECTED] To: 'Mikhail Entaltsev' [EMAIL PROTECTED]; 'Mysql' [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 2:27 AM Subject: RE: 5.0 binary question Hi Mikhail- Thanks for responding; the presentation is very helpful; However, I think I may not have been clear enough - I was asking whether the binary alpha 5.0 that is now available from the web site had stored procedure support. I don't think it does as I don't see a catalog table mysql.proc defined and I cannot create any procedures. It's a little odd that 5.0 would be made available as a binary without the primary feature to exercise built into it. Oh well. I still feel like I'm missing something because that just doesn't make sense. Regards, R -Original Message- From: Mikhail Entaltsev [mailto:[EMAIL PROTECTED] Sent: Monday, December 29, 2003 7:18 PM To: [EMAIL PROTECTED]; Mysql Subject: Re: 5.0 binary question Hi, I have found slides show http://mysql.progen.com.tr/events/uc2003/slides/stored-procedures.pdf and examples in mysql-5.0/mysql-test/t/ - sp.test - sp-error.test Best regards, Mikhail. - Original Message - From: Rick Robinson [EMAIL PROTECTED] To: Mysql [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 12:14 AM Subject: 5.0 binary question I downloaded the 5.0 preview alpha binary for Win32 and installed it - so far, so good. But I don't see anything in the doc on the stored procedure capability (how-to/syntax) and I can't create one within 5.0 (using SQL99 syntax - at least I think/hope so). Is stored procedure capability not available in the provided binaries (maybe it's only in the source tree?)? Just want to make sure I'm not missing something. Thanks, Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL user in Indonesia
hendro, We are (Sumitomo Wareghouse Indonesia) using mysql from version 3.5 - untill 5.0 (gamma version). As long as my experience there's no problem using these free cost database. Right now, we still believe mysql as our primary database engine for service our site office at Karawang, Tg-Priok, and Cengkareng. Start on next year, still using this DB , we move a head for services our branch office at Surabaya (Client-Server), and middle of the year we will connect to our sister company at Sumitomo Warehouse Singapore (SIN), and Sumitomo Warehouse Malaysia (MYS), where they using different DB engine (SQL) but we have test the connector for that. Just tell your boss to do it, if she/he not agree then let's spen your company money at least $ 12.000 for basic DB only. I Hope it's help. Thanks Rie - Original Message - From: Hendro S [EMAIL PROTECTED] To: 'Leo' [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 4:17 PM Subject: RE: MySQL user in Indonesia I am working to port our database to mysql now. I work in PT. ADETEX. Sorry for my english. Regards, Hendro -Original Message- From: Leo [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 1:25 PM To: [EMAIL PROTECTED] Subject: MySQL user in Indonesia Hi All, im sorry if this mail a bit out of topic =) im currently developing a new system based on MySQL for the company i work on. and i have to report how wide and famous is mysql had been used, especially in indonesia. i need the fact that i can show to my boss, why i choose mysql.. well i have a lot of it, but he demand some example, which company in indonesia had successfully implemented mysql in their core bussiness so please, let me know.. i already run a search at google, but i have trouble determining what kwyword should i use if there is among you that work in a company here in indonesia, please, would you kindly share your experience thanks in advance -- Regards Leonardus Setiabudi IT Project Coordinator PT Bina San Prima, www.binasanprima.com -- 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]
Re: Does 5.0 contain the features of 4.1 and 4.1.1?
Hi Terence, Just a dumb question perhaps, but I am looking at upgrading to 5.0, and as we are happy with the performance and features on 4.1, can we expect the same features to have the same level of stability on 5.0? I wouldn't expect the same level of stability if you would like to use stored procs. to have the same level of stability on 5.0? Also are the features of 4.1.1 also included? Heikki wrote: For MySQL users release 5.0.0 is a milestone: you can now write stored procedures in MySQL. InnoDB in this MySQL release is essentially the same as in 4.1.1, with the bug fixes of 4.0.17 included. Later 5.0.x versions will probably include new space saving table formats for InnoDB. IMPORTANT NOTE: if you upgrade to InnoDB-4.1.1 or higher (like to 5.0.0), you cannot downgrade to a version lower than 4.1.1 any more! That is because earlier versions of InnoDB are not aware of multiple tablespaces. Best regards, Mikhail. - Original Message - From: Terence [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 9:58 AM Subject: Does 5.0 contain the features of 4.1 and 4.1.1? Hi List, Just a dumb question perhaps, but I am looking at upgrading to 5.0, and as we are happy with the performance and features on 4.1, can we expect the same features to have the same level of stability on 5.0? Also are the features of 4.1.1 also included? I need to start looking at stored procedures and hence the question...thanks! Terence -- 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]
Getting error ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
I am trying to connect to mysql database. getting error ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) Can not shutdown as well as restart. Please help me Thanks for support. - Do you Yahoo!? Free Pop-Up Blocker - Get it now
three highest and lowest column values
Hello, from the following Product-table ProductId MarketId CustomerId Type Price ... i would like to get the three highest and lowest prices for each group of ProductId,MarketId Type. How could i do that ??? Many thanks in advance roland
reg MySQL connetion to COBOL
Hi, I am having responsibility to connect MySQL to COBOL. after such a long analysing I found there are some C API function to connect to MySQL provided by itself. In COBOL I have the option to CALL external programs written in other languages. I follow this way to made connection to MySQL from COBOL. Initially I struck up with Linker error while linking libmysql.lib provided by MySQL file into COBOL, i.e, linking of this library file enable the calling of C API functions from COBOL and invoke the appropriate function. I resolve this, by convert the libmysql.dll file into libmysql.def using pexports.exe (it is a free downloadable utility) and then using LIB.exe in my COBOL compiler I convert the libmysql.def file into libmysql.lib, which is understandable lib file format to my COBOL compiler. So my COBOL compiler recognize those libmysql.lib file while linking but during runtime it shows error as; Cant Connect to MySQL server on localhost (10061) Actually I am trying to connect to the MySQL Server in Linux machine, I can able to connect to server via, MySQLCC, MySQL Explorer (a free utility from Toolmagic Softwares) and more than that I can connect via simple VC++ program using this C API functions too. I thought that I never missed any single step to be followed while MySQL server startup. I appreciate any valuable suggestions with thanks. Regards, Arun. Yahoo! India Mobile: Download the latest polyphonic ringtones. Go to http://in.mobile.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fwd: Re: MySQL 5.0.0 has been released]
Peter, Thank you for respond and sorry... It was my mistake. Today I have compared it once again: on 4.0.14 ~11.5 min on 5.0.0 ~13.0 min Thank you, Mikhail. - Original Message - From: Peter Zaitsev [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 29, 2003 8:46 PM Subject: [Fwd: Fwd: Re: MySQL 5.0.0 has been released] Mike, This is not what is generally expected. Could you please tell me which settings did you run the test with and what was CREATE TABLE for the table which had worse insert speed (at least one). You may also wish to send me test program you have been using as a test. - Forwarded message from Mikhail Entaltsev [EMAIL PROTECTED] - From: Mikhail Entaltsev [EMAIL PROTECTED] To: MySQL list [EMAIL PROTECTED] Subject: Re: MySQL 5.0.0 has been released Date: Mon, 29 Dec 2003 14:45:24 +0100 Hi, First of all, thanks a lot. I have installed MySQL 5.0.0 on my test machine (Suse Linux 8.2 kernel 2.4.20 i586) and have populated data (~40 InnoDB tables with max. 50 000 000 rows) So far it works stable and I didn't have any big problem, but I have found that speed of inserting data is ~3-5 times slower in comparison with version 4.0.14 (the same my.cnf config-file and the same PC). Is it because binary distribution of 5.0 version is not optimized? Thank you in advance, Mikhail. - End forwarded message - Regards, Sergei -- Peter Zaitsev, Full-Time Developer MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reg MySQL connetion to COBOL
What is the syntax of your call to connect to the MySQL server? It seems as though no server parameters are being passed, thereby defaulting to the local machine which apparently has no mysql server running. Pat... CocoNet Corporation SW Florida's First ISP - Original Message - From: Arunachalam [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 6:18 AM Subject: reg MySQL connetion to COBOL Hi, I am having responsibility to connect MySQL to COBOL. after such a long analysing I found there are some C API function to connect to MySQL provided by itself. In COBOL I have the option to CALL external programs written in other languages. I follow this way to made connection to MySQL from COBOL. Initially I struck up with Linker error while linking libmysql.lib provided by MySQL file into COBOL, i.e, linking of this library file enable the calling of C API functions from COBOL and invoke the appropriate function. I resolve this, by convert the libmysql.dll file into libmysql.def using pexports.exe (it is a free downloadable utility) and then using LIB.exe in my COBOL compiler I convert the libmysql.def file into libmysql.lib, which is understandable lib file format to my COBOL compiler. So my COBOL compiler recognize those libmysql.lib file while linking but during runtime it shows error as; Can't Connect to MySQL server on localhost (10061) Actually I am trying to connect to the MySQL Server in Linux machine, I can able to connect to server via, MySQLCC, MySQL Explorer (a free utility from Toolmagic Softwares) and more than that I can connect via simple VC++ program using this C API functions too. I thought that I never missed any single step to be followed while MySQL server startup. I appreciate any valuable suggestions with thanks. Regards, Arun. Yahoo! India Mobile: Download the latest polyphonic ringtones. Go to http://in.mobile.yahoo.com -- 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]
Re: MySQL/InnoDB-5.0.0 is released
Ramesh, - Alkuperäinen viesti - Lähettäjä: Ramesh Vadlapatla [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Lähetetty: Monday, December 29, 2003 10:48 PM Aihe: Re: MySQL/InnoDB-5.0.0 is released Hello Heikki, This is excellent news. Stored Procedures support is really cool. Just a couple of confirmations/questions: 1. Does this release support Sub Queries? yes. 2. Does this release support I18N? MySQL-4.1.1 (and presumably 5.0.0) support UTF8 and UCS-2 with some restrictions: http://www.mysql.com/doc/en/Charset-Unicode.html Note also that InnoDB does not yet support multiple character sets in one installation. I have promised to add that support to InnoDB in 4.1.2. 3. Where can I download this from? I couldn't see it in the Download(s) page. http://www.mysql.com/downloads/mysql-5.0.html thanks, Ramesh Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL support from http://www.mysql.com/support/index.html On Mon, 29 Dec 2003, Heikki Tuuri wrote: Hi! Due to Christmas, this release note comes late. For MySQL users release 5.0.0 is a milestone: you can now write stored procedures in MySQL. InnoDB in this MySQL release is essentially the same as in 4.1.1, with the bug fixes of 4.0.17 included. Later 5.0.x versions will probably include new space saving table formats for InnoDB. IMPORTANT NOTE: if you upgrade to InnoDB-4.1.1 or higher (like to 5.0.0), you cannot downgrade to a version lower than 4.1.1 any more! That is because earlier versions of InnoDB are not aware of multiple tablespaces. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb defragmentation question
since it has been the holidays, I can understand this one failed to draw attention of the people able to answer, so I'm sending it again: Hi all, if I try to defrag an InnoDB table (using alter table ... type=innodb;) I see the size of ibdata1 growing to almost double its size, and again it doubles if I try it again ... there's only 1 innodb table in my setup, so no other table can be causing this. Is this intentional/normal or is there something I should configure? I'm running mysql-4.0.16 on solaris 2.8. Franky -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Keys in CREATE TABLEs produced by mysqldump
Michael, - Alkuperäinen viesti - Lähettäjä: [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: [EMAIL PROTECTED] Lähetetty: Monday, December 29, 2003 11:53 PM Aihe: Re: Foreign Keys in CREATE TABLEs produced by mysqldump Heikki Tuuri [EMAIL PROTECTED] wrote on 12/24/2003 01:53:07 PM: Michael, this is the question where the valid answer is: upgrade! In which version was this corrected? I failed to mark it to the release notes :(. It was fixed in some 4.0.x version = 4.0.12. I'm hesitant to upgrade to the latest and greatest without it being banged on by the masses first.. InnoDB-4.0.17 should be stabler than any of 3.23.xx. It contains more diagnostic code and more checks. I have half a dozen or so business applications running w/ this mysql data store, regression testing them would involve an amount of time I can not afford to spend. The 3.23 branch works well for us right now and I have no real need to use 4.0 (feature-wise.) So until 3.23.x is no longer supported or there is a dire need to use 4.x functionality... I can't make a business case justifying the regression test work. I believe 3.23.58 was the very last release from the 3.23 series. MySQL/InnoDB-3.23.57, June 20, 2003 Changed the default value of innodb_flush_log_at_trx_commit from 0 to 1. If you have not specified it explicitly in your my.cnf, and your application runs much slower with this new release, it is because the value 1 causes a log flush to disk at each transaction commit. Thats it! Thanks. -mike Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL support from http://www.mysql.com/support/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: three highest and lowest column values
* Roland Niederbrucker from the following Product-table ProductId MarketId CustomerId Type Price ... i would like to get the three highest and lowest prices for each group of ProductId,MarketId Type. How could i do that ??? Do you use a programming language, or are you looking for a pure SQL solution? What version of mysql are you using? It is easy to do this programatically, the new mysql 5.0 with stored procedures could have helped you, the GROUP_CONCAT() function from 4.1 could be used, and unions from 4.0 maybe could be used too. To do it programatically, any mysql version (python-ish metacode): rs = q(SELECT ProductId,MarketId,Type,Price FROM Products ORDER BY ProductId,MarketId,Type,Price DESC) p = m = t = false counter = 0 for row in rs: P,M,T = row[ProductId],row[MarketId],row[Type] if ((P == p) and (M == m) and (T == t)): if counter 3: print row[Price], counter = counter + 1 else: print \n+P,M,T,row[Price], counter = 1 p,m,t = P,M,T That should output the three highest prices for each group, repeat without the DESC in the SELECT to get the three lowest prices. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb defragmentation question
Franky, since MySQL performs ALTER TABLE ... TYPE = InnoDB; by totally rebuilding the table, it is very normal that the space usage temporarily doubles in ibdata files. But if it doubles also after an immediate SECOND rebuild, then that must be a bug. If you can reproduce that phenomenon, please send me the following before and after each step: 1) SHOW TABLE STATUS; 2) SHOW INNODB STATUS; 3) ls -l in the datadir, 4) and what the following prints to the .err log: CREATE TABLE innodb_table_monitor(a INT) TYPE = InnoDB; wait some 70 sec here DROP TABLE innodb_table_monitor; Note that if you use multiple tablespaces in 4.1.1, then InnoDB will delete the old .ibd file after the rebuild, and the disk space is freed to the OS. Thus, multiple tablespaces help in your problem. Note also that the big transaction which builds the new table will also use some 20 bytes per row in the undo logs in the system tablespace, that is, the ibdata files. And the ibdata files will not shrink in 4.1.1, either. The space in undo logs is freed within the ibdata files, and can be used for undo logs of other transactions, but the space is not freed to the OS. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL support from http://www.mysql.com/support/index.html ... List:MySQL General Discussion« Previous MessageNext Message » From:Franky Van LiedekerkeDate:December 30 2003 3:00pm Subject:innodb defragmentation question since it has been the holidays, I can understand this one failed to draw attention of the people able to answer, so I'm sending it again: Hi all, if I try to defrag an InnoDB table (using alter table ... type=innodb;) I see the size of ibdata1 growing to almost double its size, and again it doubles if I try it again ... there's only 1 innodb table in my setup, so no other table can be causing this. Is this intentional/normal or is there something I should configure? I'm running mysql-4.0.16 on solaris 2.8. Franky -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: three highest and lowest column values
Using correlated subqueries on 4.1 you could do it all in sql: SELECT ProductId, MarketId, Type, Price FROM Products a WHERE 3=(SELECT count(*) --finds highest prices FROM Products b where b.ProductId=a.ProductId AND b.MarketId=a.MarketId AND b.Type=a.Type AND b.Price=a.Price) UNION --UNION ALL will remove duplicates between highest/lowest SELECT ProductId, MarketId, Type, Price FROM Products a WHERE 3=(SELECT count(*) --finds lowest prices FROM Products b WHERE b.ProductId=a.ProductId AND b.MarketId=a.MarketId AND b.Type=a.Type AND b.Price=a.Price) ORDER BY ProductId, MarketId, Type, Price Thanks, Ed Subject: Re: three highest and lowest column values * Roland Niederbrucker from the following Product-table ProductId MarketId CustomerId Type Price ... i would like to get the three highest and lowest prices for each group of ProductId,MarketId Type. How could i do that ??? Do you use a programming language, or are you looking for a pure SQL solution? What version of mysql are you using? It is easy to do this programatically, the new mysql 5.0 with stored procedures could have helped you, the GROUP_CONCAT() function from 4.1 could be used, and unions from 4.0 maybe could be used too. To do it programatically, any mysql version (python-ish metacode): rs = q(SELECT ProductId,MarketId,Type,Price FROM Products ORDER BY ProductId,MarketId,Type,Price DESC) p = m = t = false counter = 0 for row in rs: P,M,T = row[ProductId],row[MarketId],row[Type] if ((P == p) and (M == m) and (T == t)): if counter 3: print row[Price], counter = counter + 1 else: print \n+P,M,T,row[Price], counter = 1 p,m,t = P,M,T That should output the three highest prices for each group, repeat without the DESC in the SELECT to get the three lowest prices. -- Roger -- 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]
Re: Forms Reports like I use in MS Access
Hi, I am new to MySQL and old to MS Access (advanced skill level). When building Access DBs, I write VB code, SQL, develop DBs using 3rd N.F. and build lovely forms for data entry, print outs, reports etc, etc. After examing MySQL's material, I don't see any forms like I use in Access. So what GUI should I use and how do I link the SQL to things like command buttons, check boxes, drop-down list etc? As a PHP/MySQL programmer I use the web browser as my front end. Form Components are created using HTML and Field Validation is acheived using Javascript. PHP pulls everything together. For Reports the web browser with a white background is fine. For standard letters where you need to control the header and footer content I use ScriptX or output a PDF document. Any client databases I find written in MS Access I normally convert totally so that MS Access is not used. Happy to share, Thanks for your guidance Adam _ E-mail just got a whole lot better. New ninemsn Premium. Click here http://ninemsn.com.au/premium/landing.asp -- 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]
Re: Log Files in MySQL
[EMAIL PROTECTED] wrote: I think I am confused. I have many bin index files, but I have not gotten rid of them. I read somewhere that I should not remove them...or I should not remove the entries in the index file. Since I am unsure of which one above is correct, I am posting to the list. Can someone let me know which of the above is correct, and what I should do? If you want to delete some of binary log files you can use PURGE MASTER LOGS command: http://www.mysql.com/doc/en/PURGE_MASTER_LOGS.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem Installation
I have red hat 9.0, i don't to install mysql RPM appear: erro: Failed dependencies: libmysqlclient.so.10 is needed by perl-DBD-MySQL-2.1021-3 Please Help me!! -- *** Carlos Andre Moura de Amorim -- Funcionario UNCISAL -- Tecnico de Informatica CEFET-AL -- Acad. de Mecicina UNCISAL -- Fone: (0xx82) 9904-0117 *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem Installation
Hi You need to install first rpm with dynamic client libraries (including 3.23.x libraries). You can find them on http://www.mysql.com/downloads/mysql-4.0.html Best regards, Mikhail. - Original Message - From: Carlos Andre Moura de Amorim [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 4:20 PM Subject: Problem Installation I have red hat 9.0, i don't to install mysql RPM appear: erro: Failed dependencies: libmysqlclient.so.10 is needed by perl-DBD-MySQL-2.1021-3 Please Help me!! -- *** Carlos Andre Moura de Amorim -- Funcionario UNCISAL -- Tecnico de Informatica CEFET-AL -- Acad. de Mecicina UNCISAL -- Fone: (0xx82) 9904-0117 *** -- 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]
Re: Problem Installation
Thanks, I installed, but i don't to connect!! bashmysql bashERROR 2002: Can't connect to local MySQL server through socket 'var/lib/mysql.sock' (2) On Tue, 30 Dec 2003, Mikhail Entaltsev wrote: Hi You need to install first rpm with dynamic client libraries (including 3.23.x libraries). You can find them on http://www.mysql.com/downloads/mysql-4.0.html Best regards, Mikhail. - Original Message - From: Carlos Andre Moura de Amorim [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 4:20 PM Subject: Problem Installation I have red hat 9.0, i don't to install mysql RPM appear: erro: Failed dependencies: libmysqlclient.so.10 is needed by perl-DBD-MySQL-2.1021-3 Please Help me!! -- *** Carlos Andre Moura de Amorim -- Funcionario UNCISAL -- Tecnico de Informatica CEFET-AL -- Acad. de Mecicina UNCISAL -- Fone: (0xx82) 9904-0117 *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- *** Carlos Andre Moura de Amorim -- Funcionario UNCISAL -- Tecnico de Informatica CEFET-AL -- Acad. de Mecicina UNCISAL -- Fone: (0xx82) 9904-0117 *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lock Wait Timeout
I have an application that updated or inserted 26,000 records into a MySQL INNODB table in about 12 minutes... A creditable performance as far as I am concerned. However, the application failed on one of the last insert/updates with a lock wait timeout. My script issues a commit every 250 transactions which, I believe, releases any locks up to that point. I've increased lock_wait_timeout to 75 (what is a reasonable higher end limit for this?) but still get the error. I'm using v.4.0.16 on a RH 9.0 box. Any thoughts on what might be happening here? Thanks. Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Running 4.0.17 and 5 on same machine
Currently running 4.0.17 InnoDB on XP/Apache 2.0.47; client wants to investigate using capability for stored procs in v5, but doesn't have another machine on which to experiment. Can these two be run on the same machine (though not at the same time)? Regards Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem
i have a follow problem, please, help-me [EMAIL PROTECTED] db]# mysql --user=root --password=xx ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) -- *** Carlos Andre Moura de Amorim -- Funcionario UNCISAL -- Tecnico de Informatica CEFET-AL -- Acad. de Mecicina UNCISAL -- Fone: (0xx82) 9904-0117 *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: COBOL Syntax of calling MySQL's C API
Being COBOL illiterate, I may not be able to help. Two questions come to mind in reviewing the parameters you are passing. Are the parameters by reference passed as char-like pointers and the parameters by content passed as integers? If they are, then the only issue I see as a possibility is that 'fMySQLSocket' should be passed as NULL not 0. Also make sure mysqld is running on 'myserver'. I hope this helps... Pat... BTW replies to the list usually yield better results. - Original Message - From: Arunachalam [EMAIL PROTECTED] To: Patrick Sherrill [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 8:44 AM Subject: COBOL Syntax of calling MySQL's C API Hello Patrick, The actual calling routines in C is; MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char *unix_socket, unsigned long client_flag) I have declared the variables with values are; 05 C-cMySQLHostName pic x(9). 88 fMySQLHostName value 'myserver'. 05 C-cMySQLUserIdpic x(4). 88 fMySQLUserId value 'arun'. 05 C-cMySQLPassword pic x(4). 88 fMySQLPassword value 'arun'. 05 C-cMySQLDBNamepic x(6). 88 fMySQLDBName value 'MyDBMS'. 05 C-cMySQLPort pic x(4). 88 fMySQLPort value '3306'. 05 C-cMySQLSocketpic x(8). 88 fMySQLSocket value '0'. 05 C-cMySQLFlag pic x(8). 88 fMySQLFlag value '0'. It's equivalent COBOL coding for the C coding syntax is; CALL C_mysql_real_connect using by reference E-ptrSQLConnect by reference C-cMySQLHostName by reference C-cMySQLUserId by reference C-cMySQLPassword by reference C-cMySQLDBName by content C-cMySQLPort by reference C-cMySQLSocket by content C-cMySQLFlag giving E-ptrSQLEnv I have tested that before reaching this Calling portion all the variables hold the values what I have set earlier. but after execution it stores the error as it's value (i.e., Can't connect to MySQL server on localhost (10061) ) and the pointer variable E_ptrSQLConnect hold value 0. Before invoking this Call I have invoked the C_mysql_init funtion and get the pointer value in E_ptrSQLConnect. I could't get where is the possibility of error occurence exist... :( Arun. --- Patrick Sherrill [EMAIL PROTECTED] wrote: What is the syntax of your call to connect to the MySQL server? It seems as though no server parameters are being passed, thereby defaulting to the local machine which apparently has no mysql server running. Pat... CocoNet Corporation SW Florida's First ISP - Original Message - From: Arunachalam [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 6:18 AM Subject: reg MySQL connetion to COBOL Hi, I am having responsibility to connect MySQL to COBOL. after such a long analysing I found there are some C API function to connect to MySQL provided by itself. In COBOL I have the option to CALL external programs written in other languages. I follow this way to made connection to MySQL from COBOL. Initially I struck up with Linker error while linking libmysql.lib provided by MySQL file into COBOL, i.e, linking of this library file enable the calling of C API functions from COBOL and invoke the appropriate function. I resolve this, by convert the libmysql.dll file into libmysql.def using pexports.exe (it is a free downloadable utility) and then using LIB.exe in my COBOL compiler I convert the libmysql.def file into libmysql.lib, which is understandable lib file format to my COBOL compiler. So my COBOL compiler recognize those libmysql.lib file while linking but during runtime it shows error as; Can't Connect to MySQL server on localhost (10061) Actually I am trying to connect to the MySQL Server in Linux machine, I can able to connect to server via, MySQLCC, MySQL Explorer (a free utility from Toolmagic Softwares) and more than that I can connect via simple VC++ program using this C API functions too. I thought that I never missed any single step to be followed while MySQL server startup. I appreciate any valuable suggestions with thanks. Regards, Arun. Yahoo! India Mobile: Download the latest polyphonic ringtones. Go to http://in.mobile.yahoo.com -- 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]
Re: Problem Installation
Try to change localhost in connection string to real IP address or 127.0.0.1. Actually it is not the fix of the problem it is a workaround. Best regards, Mikhail. - Original Message - From: Carlos Andre Moura de Amorim [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 4:42 PM Subject: Re: Problem Installation Thanks, I installed, but i don't to connect!! bashmysql bashERROR 2002: Can't connect to local MySQL server through socket 'var/lib/mysql.sock' (2) On Tue, 30 Dec 2003, Mikhail Entaltsev wrote: Hi You need to install first rpm with dynamic client libraries (including 3.23.x libraries). You can find them on http://www.mysql.com/downloads/mysql-4.0.html Best regards, Mikhail. - Original Message - From: Carlos Andre Moura de Amorim [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 4:20 PM Subject: Problem Installation I have red hat 9.0, i don't to install mysql RPM appear: erro: Failed dependencies: libmysqlclient.so.10 is needed by perl-DBD-MySQL-2.1021-3 Please Help me!! -- *** Carlos Andre Moura de Amorim -- Funcionario UNCISAL -- Tecnico de Informatica CEFET-AL -- Acad. de Mecicina UNCISAL -- Fone: (0xx82) 9904-0117 *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- *** Carlos Andre Moura de Amorim -- Funcionario UNCISAL -- Tecnico de Informatica CEFET-AL -- Acad. de Mecicina UNCISAL -- Fone: (0xx82) 9904-0117 *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running 4.0.17 and 5 on same machine
* Terry Riley Currently running 4.0.17 InnoDB on XP/Apache 2.0.47; client wants to investigate using capability for stored procs in v5, but doesn't have another machine on which to experiment. Can these two be run on the same machine (though not at the same time)? You can run them on the same machine at the same time, just make sure they don't listen on the same port, share the same pid-file, data files or log files and so on. URL: http://www.mysql.com/doc/en/Multiple_servers.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running 4.0.17 and 5 on same machine
I'm doing just that right now. I built from source and put mysql4 in /usr/local/mysql and mysql5 in /usr/local/mysql5 with the my.cnf is /usr/l.ocal/mysqlX/var. In the my.cnf file I put the local sockets into /usr/local/mysqlX/tmp and mysql5 is using port 3305 with mysql4 on 3306. The toughtest thing I bumped into was the permissions for the var and tmp directories weren't set correctly by make install. Terry Riley wrote: Currently running 4.0.17 InnoDB on XP/Apache 2.0.47; client wants to investigate using capability for stored procs in v5, but doesn't have another machine on which to experiment. Can these two be run on the same machine (though not at the same time)? Regards Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1.1 alpha and mysql_install_db grant tables issue
The mysql_install_db script shipped with 4.1.1-alpha seems to leave the mysql/* (user,host,etc.) tables owned root.root on my Debian system; this makes mysqld fail to start after the grant tables are installed. It looks like this is because mysql_install_db calls mysqld with --bootstrap and *without* --user=mysql. mysql_install_db already takes a --user argument but doesn't use it; perhaps it should be added to line 208? -- thanks, Will -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with table corrupting
I've isolated this problem down to a single table and SQL script that
demonstrates the problem repeatably.
There are 4 SQLl scripts here - one to create remove the database, one
to create the database and user entries, one to create the table, and
one to access the table. It is the 3rd that causes the problem. Run it
once, all is OK. Run it a second time - error (it claims a duplicate
index but the query just prior to the insert clearly shows this is
incorrect). Run it a third and you get the table error 127.
If you comment out the lines for testDeleteUser, (and rebuild the
table), you can run the query dozens of times and never get the error.
Somehow, the SQL in testDeleteUser is breaking the table, causing
testupdate to break on one insert (always the same place!).
Any ideas???
Thanks,
-Richard
First, the database and user remove script (to rebuild the table later).
USE mysql;
DELETE FROM db WHERE db='expdb';
DELETE FROM user WHERE user='expdemo';
DROP DATABASE expdb;
Now, the database create and user create...
USE mysql;
INSERT INTO user (host,user,password)
VALUES ('localhost','expdemo',password('demo66exp'));
INSERT INTO db
(host,db,user,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)
VALUES('localhost','expdb','expdemo','Y','Y','Y','Y','Y','Y');
Now, the table create...
--
-- Current Database: expdb
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ expdb;
USE expdb;
--
-- Table structure for table 'appraised'
--
CREATE TABLE appraised (
idNumber int(11) NOT NULL default '0',
indexNo int(11) NOT NULL default '0',
experience int(11) default NULL,
lastused int(11) default NULL,
competence int(11) default NULL,
status int(11) default NULL,
appraiser int(11) default NULL,
comments text,
PRIMARY KEY (idNumber,indexNo)
) TYPE=MyISAM;
Finally, the SQL that causes the problem...
USE expdb;
### start of tests
### testupdate
PRINT;
DELETE FROM appraised;
INSERT INTO appraised VALUES (1,1,1,1,1,1,1,'aaa1');
INSERT INTO appraised VALUES (2,2,2,2,2,2,2,'aaa2');
INSERT INTO appraised VALUES (3,3,3,3,3,3,3,'aaa3');
INSERT INTO appraised VALUES (4,4,4,4,4,4,4,'aaa4');
SELECT * FROM appraised;
SELECT * FROM appraised WHERE idNumber=1 AND indexNo=1;
SELECT * FROM appraised WHERE idNumber=2 AND indexNo=2;
SELECT * FROM appraised WHERE idNumber=3 AND indexNo=3;
SELECT * FROM appraised WHERE idNumber=4 AND indexNo=4;
DELETE FROM appraised WHERE idNumber=2 AND indexNo=2;
INSERT INTO appraised VALUES (2,2,2,2,2,2,2,'aaa2');
SELECT * FROM appraised WHERE idNumber=2 AND indexNo=2;
SELECT * FROM appraised;
DELETE FROM appraised WHERE idNumber=2 AND indexNo=2;
INSERT INTO appraised VALUES (2,2,2,2,2,3,2,'aaa2');
SELECT * FROM appraised WHERE idNumber=2 AND indexNo=2;
SELECT * FROM appraised;
SELECT * FROM appraised;
DELETE FROM appraised WHERE idNumber=4 AND indexNo=4;
INSERT INTO appraised VALUES (4,4,4,4,4,4,4,'aaa4');
SELECT * FROM appraised WHERE idNumber=4 AND indexNo=4;
SELECT * FROM appraised;
DELETE FROM appraised WHERE idNumber=4 AND indexNo=4;
INSERT INTO appraised VALUES (4,4,4,4,4,6,4,'aaa4');
SELECT * FROM appraised WHERE idNumber=4 AND indexNo=4;
SELECT * FROM appraised;
DELETE FROM appraised WHERE idNumber=6 AND indexNo=6;
INSERT INTO appraised VALUES (6,6,6,6,6,6,6,'aaa6');
SELECT * FROM appraised WHERE idNumber=6 AND indexNo=6;
SELECT * FROM appraised;
DELETE FROM appraised WHERE idNumber=6 AND indexNo=6;
INSERT INTO appraised VALUES (6,6,6,6,6,9,6,'aaa6');
SELECT * FROM appraised WHERE idNumber=6 AND indexNo=6;
SELECT * FROM appraised;
DELETE FROM appraised WHERE idNumber=8 AND indexNo=8;
INSERT INTO appraised VALUES (8,8,8,8,8,8,8,'aaa8');
SELECT * FROM appraised WHERE idNumber=8 AND indexNo=8;
SELECT * FROM appraised;
DELETE FROM appraised WHERE idNumber=8 AND indexNo=8;
INSERT INTO appraised VALUES (8,8,8,8,8,12,8,'aaa8');
SELECT * FROM appraised WHERE idNumber=8 AND indexNo=8;
SELECT * FROM appraised;
DELETE FROM appraised;
### testDeleteUser
### NOTE: comment out the SQL statements below and the problem
disappears!
PRINT;
DELETE FROM appraised;
INSERT INTO appraised VALUES (1,1,1,1,1,1,1,'aaa1');
INSERT INTO appraised VALUES (1,5,5,5,5,5,5,'aaa14');
INSERT INTO appraised VALUES (2,2,2,2,2,2,2,'aaa2');
INSERT INTO appraised VALUES (2,6,6,6,6,6,6,'aaa24');
INSERT INTO appraised VALUES (3,3,3,3,3,3,3,'aaa3');
INSERT INTO appraised VALUES (3,7,7,7,7,7,7,'aaa34');
INSERT INTO appraised VALUES (4,4,4,4,4,4,4,'aaa4');
INSERT INTO appraised
Re: problem
On 30 Dec 2003, at 16:22, Carlos Andre Moura de Amorim wrote: i have a follow problem, please, help-me [EMAIL PROTECTED] db]# mysql --user=root --password=xx ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Wrong password or root does not have a password. (The 'Using password' describes what you are doing; not what is set up for root). Try without --password option. If this is the case, you would be wise to create a password for root: $ mysqladmin --user=root password the-password HTH Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Log Files in MySQL
I think I am confused. I have many bin index files, but I have not gotten rid of them. I read somewhere that I should not remove them...or I should not remove the entries in the index file. Since I am unsure of which one above is correct, I am posting to the list. Can someone let me know which of the above is correct, and what I should do? If you want to delete some of binary log files you can use PURGE MASTER LOGS command: http://www.mysql.com/doc/en/PURGE_MASTER_LOGS.html Excellent! Thanks Egor, this is exactly what I wanted. Thanks eric smime.p7s Description: S/MIME cryptographic signature
Re: Lock Wait Timeout
Well now, this is interesting. The agent mentione previously ran flawlessly for a couple of weeks. The day I left for Christmas vacation, I started getting the lock wait timeout error. The difference? I altered the table to include a foreign key constraint. Not sure how this could possibly be the issue but when I dropped the foreign key constraint, the agent worked perfectly. I'm still at a loss as to why this problem happened. I'm using INNODB tables in MySQL 4.0.16. It looks like the one record which timed out was being updated and the referenced record and field values existed. In addition, I had already updated 26,000 records so I can't figure out what's wrong with this one particular record. Any thoughts appreciated. Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
automatic replication
Hi, I am currently using the replication function of my mysql server (3.23.58) to replicate one database. Since I host others I'd like to know if there is a way to automatically replicate a database. I.e as soon as a use issues a mysqladmin create it is propagated to the slaves. Any tips ? __ Do you Yahoo!? Find out what made the Top Yahoo! Searches of 2003 http://search.yahoo.com/top2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Problems: Ignore table
The replication between two servers works. But I tried to disable replication on one table. According to the MYSQL Documentation. I did the following: #1 stop Slave #2 Modified my.cnf on slave [mysqld] server-id=3 master-host=192.168.1.129 master-user=aert12 master-password=password replicate-ignore-table=info.notes # Added this Line #3 Restarted slave server /etc/init.d/mysql restart #4 start slave show status slave shows that replication is running , but actually it doesnt. I even can type start slave many times with no warning. Previously I got an error message if I try to start slave that is running. What I did wrong? _ Working moms: Find helpful tips here on managing kids, home, work and yourself. http://special.msn.com/msnbc/workingmom.armx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running 4.0.17 and 5 on same machine
Thanks, Bruce, Roger Terry Riley --Original Message- Currently running 4.0.17 InnoDB on XP/Apache 2.0.47; client wants to investigate using capability for stored procs in v5, but doesn't have another machine on which to experiment. Can these two be run on the same machine (though not at the same time)? Regards Terry Riley -- 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]
Query +multiple tables group by
Hi
Hi I want to query multiple tables and think I need a little help
Using this example I have 2 tables
Table1
Table Name: one
Field: Surname
Field: Setnumber
Table2
Table Name: two
Field: Surname
Field: Setnumber
Using:
SELECT COUNT(*) AS res, FROM one, two where one.surname like ('
globsurname ') or two.surname like (' globsurname ')
Works fine, However I want to group the results by set number so did this:
SELECT COUNT(*) AS res, setnumber FROM one, two where one.surname like
(' globsurname ') or two.surname like (' globsurname ') group by
setnumber
It fails with 'setnumber' in field list is ambiguous
Simplistically I want to search x number of tables for a surname (all the
tables have a surname column and a setnumber column ) and on the first pass
return how many searched names it found per set.
The user will then be able to focus on a set if required.
Regards
John Berman
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Anger Managment and MySql
I am trying to get some kind of friggin Quake 3 stat generator to work on my Quake 3 servers that I have running. Currently I am running a program called Q3Log, which is java based, but it seems to have stopped working for some reason. (no log to show errors). Anyhow, now my only option is to run a MySql / php backend stat generator. I have included the /var/lib/mysql/quake3server.err log as an attachment. I looked over it, but dont understand the errors. I am running both Quake 3 servers on Slackware 9.1 based systems, fully updated and upgraded to the latest files/programs from Slackware-current. The version of MySql that I am trying to run is: mysql Ver 12.21 Distrib 4.0.15a, for slackware-linux (i486) I do not have safe_mysqld, but I do in fact have mysqld_safe, and when I run it I get the following: #mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 031230 12:53:08 mysqld ended I did do the following (after reading through some of the docs) begin MySql Mayhem--- #mysql_install_db Which gave me the following output: Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables 031230 12:56:56 /usr/libexec/mysqld: Shutdown Complete To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h quake3server password 'new-password' See the manual for more instructions. To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system NOTE: If you are upgrading from a MySQL = 3.22.10 you should run the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! You can start the MySQL daemon with: cd /usr ; /usr/bin/mysqld_safe You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; perl run-all-tests end MySql Mayhem So, I do all the above, cute friggin MySql crud, that I am instructed to do.. I.E. /usr/bin/mysqladmin -u root password 'MY_BIG_FAT_PASSWORD' /usr/bin/mysqladmin -u root -h quake3server password 'Stupid_MySql' which gives me the following unknown errors. [EMAIL PROTECTED]:/usr# /usr/bin/mysqladmin -u root password 'MY_BIG_FAT_PASSWORD' /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2)' Check that mysqld is running and that the socket: '/var/run/mysql/mysql.sock' exists! So now (moving right along.. doing whilst reading) I run the following cmd.: *#mysqld_safe --user=mysql 21 /dev/null * hmm.. nottta! So then I run: #mysql -uroot mysql Which spews this crud: ERROR 2002: Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2) What kind of program is this I ask myself.. that wont even try to run, on a default Linux install.. (Slackware 9.1 even!) So.. I do a google search for: running mysql and end up @ http://www.linuxforum.com/forums/index.php?s=74eee1e361c3628126848736dd92f61fshowtopic=5319 Here I peek around seeing many people having the same errors I am getting. .I see something about mysql.sock, so I do a little locate mysql.sock which shows me zero, notta.. zilch.. .nobody is home! Ok.. so then I click, click, click around and end up @ http://www.linuxforum.com/mysql-manual.php (I swore I wouldn't research this very much.. and now look what I have gotten myself into!) So here I am... http://www.linuxforum.com/mysql/manual_Installing.html#Post-installation I run: mysqld_safe --user=mysql Which gives me the following tasty information: Starting mysqld daemon with databases from /var/lib/mysql [EMAIL PROTECTED]:~# Starting mysqld daemon with databases from /var/lib/mysql 031230 13:19:19 mysqld ended Hmm... something tells me MySql needs a hammer Ok, so now I am very angry.. I continue to look over the info @ http://www.linuxforum.com/mysql/manual_Installing.html#Post-installation still... CONFUSED 8-P and folks.. The mayhem continues! now.. I am here: http://www.linuxforum.com/mysql/manual_Tutorial.html#Tutorial I try this: mysql -h localhost -u root -p I get this: Enter password: I enter password and get this: ERROR 2002: Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2) BUT... I remember that: /usr/bin/mysqladmin -u root password 'new-password' and /usr/bin/mysqladmin -u root -h quake3server password 'new-password' I even tried this command: mysqladmin -u root password mypassword (with out the ' ' on each end) Did not work.. so what am I doing this for? hmmm maybe a bigger hammer is needed for this
MySQL on RAID server????
Hello List: Here comes a newbie's newbie!! I want to install MySQL 5.0.0 on a machine with RAID. It has the following hardware/Software configuration: DISTRO: RH9 1-Disk which has all the software installed. 2-Disks, which are RAID1 format with one folder /data I plan to use the RAID for data only. I am trying to install Source 5.0.0-alpha version of MySQL. I am not sure how to go about installing. I have two chain of thought: (1) Install MySQL at /usr/local as suggested in the Source installation directive, but install the MySQL data on the RAID disk (/data folder) -or- (2) Install the entire MySQL package on the RAID disk. If my explanation needs better explanation, I will be more than happy to do it. I prefer the (1) method but I have no idea how I can install just data on the RAID disk? I think the ./configure statement needs additional directive, but what? I have reviewed the information on the MySQL web site under Source installation page. There are 4-5 notes from people. Please shoe me the way. Thanks in advance. Kirti -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_fix_privilege_tables.sql
The MySql 5.0 Windows binary is missing the file scripts/mysql_fix_privilege_tables.sql, without which, it appears, there is no way to enable the main new feature of 5.0, Stored Procs. If Linux 5.0 builds include this script, could someone please post the script here so we may have at MySql SPs? TIA. PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DROPPING NO-NAME INDEXes
hi everyone, I made a mistake of not giving a name to an index. alter table sometable add index ( field1, field2 ) I would like to remove this index but I can't figure it out. I tried: alter table sometable drop index (field1,field2) How do I fix this? Thank you. -gohaku -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DROPPING NO-NAME INDEXes
I made a mistake of not giving a name to an index. alter table sometable add index ( field1, field2 ) I would like to remove this index but I can't figure it out. If you perform SHOW INDEX FROM tablename; what do you get as the key_name? Try dropping it based on what you see. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Create Tables In MySQL
Hi, I have several simple questions regarding creating tables in the MySQL. 1. For a variable of Java primitive int type, I should use INT or INTEGER? Do I have to specify the length of the field? CREATE TABLE message_thread( thread_id INT or INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY); 2. A field in my table is for storing articles. Articles could be very long in variable length. I should use VARCHAR or TEXT? What about the length of the field? 3. For a variable of the Timestamp type, do I specify CREATE TABLE message_thread( thread_creation_date TIMESTAMP NOT NULL ); What about the length of the field? Thanks for your advices in advance. __ Do you Yahoo!? Find out what made the Top Yahoo! Searches of 2003 http://search.yahoo.com/top2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem Installation
-Message d'origine- De : Carlos Andre Moura de Amorim [mailto:[EMAIL PROTECTED] Envoyé : Tuesday, December 30, 2003 10:43 AM À : Mikhail Entaltsev Cc : [EMAIL PROTECTED] Objet : Re: Problem Installation Thanks, I installed, but i don't to connect!! bashmysql bashERROR 2002: Can't connect to local MySQL server through socket 'var/lib/mysql.sock' (2) Is the server running? Ugo On Tue, 30 Dec 2003, Mikhail Entaltsev wrote: Hi You need to install first rpm with dynamic client libraries (including 3.23.x libraries). You can find them on http://www.mysql.com/downloads/mysql-4.0.html Best regards, Mikhail. - Original Message - From: Carlos Andre Moura de Amorim [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 4:20 PM Subject: Problem Installation I have red hat 9.0, i don't to install mysql RPM appear: erro: Failed dependencies: libmysqlclient.so.10 is needed by perl-DBD-MySQL-2.1021-3 Please Help me!! -- ** ** *** Carlos Andre Moura de Amorim -- Funcionario UNCISAL -- Tecnico de Informatica CEFET-AL -- Acad. de Mecicina UNCISAL -- Fone: (0xx82) 9904-0117 ** ** *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- *** Carlos Andre Moura de Amorim -- Funcionario UNCISAL -- Tecnico de Informatica CEFET-AL -- Acad. de Mecicina UNCISAL -- Fone: (0xx82) 9904-0117 *** -- 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]
RE: problem
-Message d'origine- De : Carlos Andre Moura de Amorim [mailto:[EMAIL PROTECTED] Envoyé : Tuesday, December 30, 2003 11:23 AM À : [EMAIL PROTECTED] Objet : problem i have a follow problem, please, help-me [EMAIL PROTECTED] db]# mysql --user=root --password=xx ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) have you tried without password? if not, look in google mysql root password recovery. There is a section on this in the mysql manual available on mysql.com hth Ugo -- ** * Carlos Andre Moura de Amorim -- Funcionario UNCISAL -- Tecnico de Informatica CEFET-AL -- Acad. de Mecicina UNCISAL -- Fone: (0xx82) 9904-0117 ** * -- 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]
How to backup MySQL databasee?
Hi, Can somebody teach me how to do MySQL server backup? Any answer will be much appreciated. regards, florence Thank you very much. regards, florence The New Yahoo! Search - Now with image search!
RE: How to backup MySQL databasee?
-Message d'origine- De : florence florence [mailto:[EMAIL PROTECTED] Envoyé : Tuesday, December 30, 2003 1:46 PM À : [EMAIL PROTECTED] Objet : How to backup MySQL databasee? Hi, Can somebody teach me how to do MySQL server backup? Any answer will be much appreciated. Start with mysqldump Ugo regards, florence Thank you very much. regards, florence The New Yahoo! Search - Now with image search! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem
Carlos Andre Moura de Amorim wrote: i have a follow problem, please, help-me [EMAIL PROTECTED] db]# mysql --user=root --password=xx ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) try to add -h hostname -- *** Carlos Andre Moura de Amorim -- Funcionario UNCISAL -- Tecnico de Informatica CEFET-AL -- Acad. de Mecicina UNCISAL -- Fone: (0xx82) 9904-0117 *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
