PHP/MySQL Problem
Hi Guys, I have a problem with MySQL in conjunction with PHP so I also decided to post here: I have a PHP script that contains two consecutive MySQL queries, something like this: Query 1: Delete some rows from Table A Query 2: Insert some rows into Table A The problem is, only Query 2 seems to be executed. Query 1 is not executed at all. I tried running the script with only Query 1 and it worked. However, when I put back Query 2, then the problem comes back. What can be causing this problem? How do I solve this problem? Any help would be appreciated. Thanks! __ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL Clustering and HA (NDB - Emic Networks Solution - Replication) : Enterpise Use
Hi. About stability of MySQL Cluster you can read at www.mysql.com/it-resources/case-studies/b2.php o Does the memory size limit the data we can manage? If it is a memory based solution it should mean that it can handle of a very limited number of databases/tables/rows, based on the available memory of the nodes. Yes, MySQL Cluster stores data in the ndb storage engine, which is memory based. For other questions see: http://dev.mysql.com/doc/mysql/en/MySQL_Cluster_Limitations_in_4.1.html Mark Papadakis [EMAIL PROTECTED] wrote: Hello all, After playing with the idea of 'abandoning ship' in favor of IBM DB2 or Oracle, we deiced to stick with mySQL, due its simplicity and investment in time and experience we have put into it. Our company needs a HA solution for ensuring 24x7 operation for the mySQL server instances. As it is, there are are two solutions available for the problem: Emic Networks's EAC for mySQL and MySQL's Cluster. The Emic solution seems to work but is way too expensive for our budget (around 4k$ for each 2CPUs node). So we need to either go with NDB or try to get replication to work properly. Here is a list of questions: o How 'stable' is MySQL cluster (NDB) ? Is it ready for enterprise use? Or even tested? o Does the memory size limit the data we can manage? If it is a memory based solution it should mean that it can handle of a very limited number of databases/tables/rows, based on the available memory of the nodes. o Is there some sort of tight integration planned for mySQL cluster and mySQL server ? Perhaps in 5.0 ? o When is adding/removing nodes on the fly scheduled for implementation? Without such a feature the system would have to be shutdown - therefore not a complete HA solution. o Has anyone gotten replication to work right? Perhaps Multi-Master replication? Thank you very much in advance, MarkP -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: Load data Infile update?
Hi. If your table has a unique index on field 'name', then use load data infile 'file' replace into table 'table'; Lewick, Taylor [EMAIL PROTECTED] wrote: Can I perform an update on a table using load data infile..? If I have the following table... Name Score Rank John NULL NULL Susan NULL NULL And I have a data file that has... John,95,1 Susan,89,2 Any idea if this can be done? Thanks, -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: load index into cache not working
Hi. There is a bug: http://bugs.mysql.com/bug.php?id=4285. Mark Maunder [EMAIL PROTECTED] wrote: I have a large fulltext index (the MYI file is about 750 Megs) and I've set my key_buffer_size to 1 Gig. I do: load index into cache fttest; and I watch the Mysql process in memory, and it doesn't grow. It just hangs around 250Megs. Why isn't the index loading into memory? Thanks, Mark. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: Database Connection using DSN
Hi. See: http://dev.mysql.com/doc/mysql/en/ODBC_Connector.html When you call mysql_real_connect you use mysqlclient library, which connects to server directly (you don't need to configure ODBC). To connect using DSN, you should use ODBC API. ODBC and MySQL are completly different things. MySQL - database system, and ODBC is an interface to interact with different databases. [EMAIL PROTECTED] wrote: Hi, I would like to know how to connect to the mysql database using a DSN in mysql API's. I am using myodbc 3.51 driver. Used the following API code for the connection MYSQL *link =3D mysql_init(NULL); MYSQL *connectHandle =3D mysql_real_connect(link,localhost,root,,test,0,NULL,0); But this is not having the option for specifying the DSN or Driver.=0D Please provide me the solution. Thanks, Narasimha Confidentiality Notice=0D The information contained in this electronic message and any attachments to= this message are intended for the exclusive use of the addressee(s) and may contain confidential or= privileged information. If you are not the intended recipient, please notify the sender at Wipro or= [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: 4.1.7 serious problems
Hi. There were several posts in list like yours. Do you use InnoDB tables? Try to increase values of key_buffer_size, read_buffer_size and so on. Ugo Bellavance [EMAIL PROTECTED] wrote: Hi, I've upgraded one of my servers (test) to 4.1.7 this week, all went ok. Now I'm trying to upgrade another server (production) from 4.1.3 to 4.1.7 and I'm having serious problems. I tried 4.1.6 as well, same problem. OS: Tao Linux (Red Hat Enterprise Linux 3.0 clone). First, since I couldn't find a procedure saying how to upgrade a binary distribution, here is what I do. -Stop MySQL -Downoad the tarball in /usr/local/. -Check the md5 -untar the tarball -copy the content of the data directory of old version into new version's data directory. -fix up the permissions like described in the INSTALL-BINARY file included -delete the /usr/local/mysql symlink and re-creating another one pointing to the new version. -Start MySQL -Test Here is the problem: After this procedure, I connect with a mysql client, use db, and then when I run a command like a select or update, the server crashes, giving me those errors on the client: ERROR 2013: Lost connection to MySQL server during query Or: ERROR 2006: MySQL server has gone away No connection. Trying to reconnect... Connection id:1 Current database: db When I go to the error log, here is what I got: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16384 read_buffer_size=258048 max_used_connections=1 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 31615 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x8908e40 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbfe7eca8, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x808af93 0x82d6de8 0x80c00bf 0x80bdeee 0x80ba6e9 0x80bcd51 0x80b9de6 0x809a1dd 0x809e6e9 0x8098def 0x8098778 0x8097eb7 0x82d459c 0x82fdf1a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x890f370 = SELECT * FROM `ville` thd-thread_id=1 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. == When resolving the stack trace, I get this: /usr/local/mysql/bin/resolve_stack_dump -s ./symbols -n ./stack 0x808af93 handle_segfault + 423 0x82d6de8 pthread_sighandler + 184 0x80c00bf get_best_combination__FP4JOIN + 147 0x80bdeee make_join_statistics__FP4JOINP13st_table_listP4ItemP16st_dynamic_array + 4206 0x80ba6e9 optimize__4JOIN + 457 0x80bcd51 mysql_select__FP3THDPPP4ItemP13st_table_listUiRt4List1Z4ItemP4ItemUiP8st_orderT7T5T7UlP13select_resultP18st_select_lex_unitP13s + 745 0x80b9de6 handle_select__FP3THDP6st_lexP13select_result + 150 0x809a1dd mysql_execute_command__FP3THD + 1241 0x809e6e9 mysql_parse__FP3THDPcUi + 169 0x8098def dispatch_command__F19enum_server_commandP3THDPcUi + 1643 0x8098778 do_command__FP3THD + 188 0x8097eb7 handle_one_connection + 615 0x82d459c pthread_start_thread + 220 0x82fdf1a thread_start + 4 Any help would be appreciated. Please let me know if you need more info. Thanks, Ugo -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: How to read TINYBLOB fields from the database
Hi. I've run mysqltcl binarytest.tcl (from source distribution) and everything works fine on my W2k Professional SP4, ActiveTcl8.5.0.0b2-win32-ix86-99907, mysqltcl-2.50 windows binary distribution. Did you read FAQ on http://www.xdobry.de/mysqltcl/index.html#faq (the main site of mysqltcl)? There described some problems by retrieving binary data from mysql by some installations. Check, that binary test passes. Danny Terweij [EMAIL PROTECTED] wrote: From: Gleb Paharenko [EMAIL PROTECTED] Send us versions of software you use, and output of The MySQL server is hosted on a windows machine. No remote access to that. just an read only account to the database. show create table 'table'. Table Create Table users CREATE TABLE `users` ( `name` varchar(65) binary default NULL, `a` varchar(129) NOT NULL default '', `b` varchar(255) NOT NULL default '', `c` varchar(255) NOT NULL default '', `d` varchar(33) NOT NULL default '', `e` tinyblob NOT NULL, `f` tinyblob NOT NULL, `g` tinyblob NOT NULL, `h` tinyblob NOT NULL, `i` tinyblob NOT NULL, `j` tinyblob NOT NULL, `k` tinyblob NOT NULL, `l` tinyblob NOT NULL, `m` tinyblob NOT NULL, `n` tinyblob NOT NULL, `o` tinyblob NOT NULL, `p` tinyblob NOT NULL, `locked` tinyint(3) unsigned NOT NULL default '0', `extra` varchar(65) binary default NULL, `time` timestamp(14) NOT NULL, KEY `name` (`name`) ) TYPE=MyISAM Can you read non-blob field from this table (or others)? yes the non-blob fields works just fine And if i do the same way on blob fields i see just garbage like *(^yuhg789 But i know that fields holds a number or word. So i think its something to do with converting binary-format to ?. Danny Terweij. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
RE: PHP/MySQL Problem
when asking a question, it always helpful to post you code/queries so we can see what is happening. but tbh, this sounds like it's a php code problem. you have established that both queries work, on there own I'm don't know anything about php, but it sounds like you are not executing the first query you might have more luck if you post your php code to a php group chris -Original Message- From: Yahoo Default User [mailto:[EMAIL PROTECTED] Sent: 05 November 2004 08:52 To: [EMAIL PROTECTED] Subject: PHP/MySQL Problem Hi Guys, I have a problem with MySQL in conjunction with PHP so I also decided to post here: I have a PHP script that contains two consecutive MySQL queries, something like this: Query 1: Delete some rows from Table A Query 2: Insert some rows into Table A The problem is, only Query 2 seems to be executed. Query 1 is not executed at all. I tried running the script with only Query 1 and it worked. However, when I put back Query 2, then the problem comes back. What can be causing this problem? How do I solve this problem? Any help would be appreciated. Thanks! __ Do you Yahoo!? Check out the new Yahoo! Front Page. www.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 with date field
What's going on here? God knows ! Maybe providing some information (OS, version, host app(if any), code example, db description, etc.) would permit some humble humans to take a guess ... - Original Message - From: Steve Grosz [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 05, 2004 8:13 AM Subject: Problem with date field Why am I having a problem getting a date field to hold date? I will store 2004-10-15, save the record. Reopen the record, and there is just -00-00 What's going on here? Thanks Steve -- 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: PHP/MySQL Problem
Hi. How did you check that Query 2 has been executed? You may add to your php.ini file mysql.trace_mode = On to see some warnings and errors. Yahoo Default User [EMAIL PROTECTED] wrote: Hi Guys, I have a problem with MySQL in conjunction with PHP so I also decided to post here: I have a PHP script that contains two consecutive MySQL queries, something like this: Query 1: Delete some rows from Table A Query 2: Insert some rows into Table A The problem is, only Query 2 seems to be executed. Query 1 is not executed at all. I tried running the script with only Query 1 and it worked. However, when I put back Query 2, then the problem comes back. What can be causing this problem? How do I solve this problem? Any help would be appreciated. Thanks! __ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: nebiew migrate access tables to mysql
Hi. Put Purge into backticks. `Purge` char(1), spiv007 [EMAIL PROTECTED] wrote: The complete error is: ERROR 1064 (42000) at line 355: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Purge CHAR(1), Dehydration CHAR(1), SomachDistentionCHAR(1), TissueGas CH' at line 12 CREATE TABLE if not exists EmbalmingCaseHistory ( CaseNumber CHAR(50) NOT NULL, PersonalEffectsYes CHAR(20), PersonalEffectsNo CHAR(1), Description TEXT, CauseOfDeathTEXT, TimeStarted DATETIME, TimeCompleted DATETIME, Normal CHAR(1), Emaciated CHAR(1), Edema CHAR(1), Purge CHAR(1), Dehydration CHAR(1), SomachDistentionCHAR(1), TissueGas CHAR(1), SkinSlipCHAR(1), JaundiceCHAR(1), BodyRefrigeratedCHAR(1), HowLong CHAR(50), RogorMortis CHAR(1), IVLeakage CHAR(1), Discoloration TEXT, SwellingTEXT, AutopsyFull CHAR(1), AutospyThoracic CHAR(1), AutopsyAbdominalCHAR(1), AutospyCranial CHAR(1), RemarksPriorToEmbalming TEXT, NeedleInjector CHAR(1), Sutures CHAR(1), MouthClosureRemarks TEXT, c_Natural CHAR(1), Mouthformer CHAR(1), Cotton CHAR(1), DenturesUpper CHAR(1), DenturesLower CHAR(1), EyeCaps CHAR(1), EyeCotton CHAR(1), EyeOtherCHAR(1), EyeOther1 CHAR(50), CarotidRCHAR(1), CarotidLCHAR(1), SubclavianR CHAR(1), SubclavianL CHAR(1), AxillaryR CHAR(1), AxillaryL CHAR(1), BrachialR CHAR(1), BrachialL CHAR(1), IlliacR CHAR(1), IlliacL CHAR(1), FemoralRCHAR(1), FemoralLCHAR(1), RadialR CHAR(1), RadialL CHAR(1), UlnarR CHAR(1), UlnarL CHAR(1), ArteriesOther CHAR(50), VJugularR CHAR(1), VJugularL CHAR(1), VAxillaryR CHAR(1), VAxillaryL CHAR(1), VIlliacRCHAR(1), VIlliacLCHAR(1), VFomoralR CHAR(1), VFomoralL CHAR(1), VeinsOther CHAR(50), HairWashedYes CHAR(1), HairWashedNoCHAR(1), BodyBathedYes CHAR(1), BodyBathedNoCHAR(1), EyesNoseMouthDisinfectedYes CHAR(1), EyesNoseMouthDisinfectedNo CHAR(1), ArterialConditionGood CHAR(1), ArterialConditionFair CHAR(1), ArterialConditionPoor CHAR(1), DrainageIntermittentCHAR(1), DrainageContinuoust CHAR(1), AspirationTreatmentImmediateCHAR(1), AspirationTreatmentDelayed CHAR(1), AspirationTreatmentHydroCHAR(1), AspirationTreatmentElectric CHAR(1), CavityTreatmentFluidCHAR(4), CavityTreatmentType CHAR(12), FluidDilutionsPreInjectedOz CHAR(5), FluidDilutionsPreInjectedGalCHAR(5), FluidDilutionsPreInjectedType CHAR(12), FluidDilutionsPreInjectedIndex CHAR(5), FluidDilutionsArterialOzCHAR(5), FluidDilutionsArterialGal CHAR(5), FluidDilutionsArterialType CHAR(12), FluidDilutionsArterialIndex CHAR(5), FluidDilutionsArterialOz1 CHAR(5), FluidDilutionsArterialGal1 CHAR(5), FluidDilutionsArterialType1 CHAR(12), FluidDilutionsArterialIndex1CHAR(50), FluidDilutionsArterialOz2 CHAR(5), FluidDilutionsArterialGal2 CHAR(5), FluidDilutionsArterialType2 CHAR(12), FluidDilutionsArterialIndex2CHAR(5), FluidDistributionGood CHAR(1), FluidDistributionFair CHAR(1), FluidDistributionPoor CHAR(1), AreasReceivingPoorCirculation TEXT, HowTreatedChemicalsUsed TEXT, HypodermicTreatmentArms CHAR(1), HypodermicTreatmentLegs CHAR(1), HypodermicTreatmentTorsoCHAR(1), HypodermicTreatmentNeck CHAR(1), HypodermicTreatmentFace CHAR(1), HypodermicTreatmentHandsCHAR(1), HypodermicTreatmentFingers CHAR(1), HypodermicTreatmentOtherCHAR(1), HypodermicTreatmentOther1 CHAR(10), Condition TEXT, Recommendations TEXT, EmbalmedBy CHAR(50), EmbalmedLicenseNumber CHAR(50), INDEX
Re: BLOB datatype
Hi. create table btable(blb blob); Hi List, Can any one show me the practical query of creating a table which should contain a column with BLOB Datatype. --Nikhil. Mulley, Nikhil [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: Problem with date field
Hi. If you send us output of show create table 'table_with_date_field', and queries, which you use to insert and retrieve date, may be we will be able to help you. Steve Grosz [EMAIL PROTECTED] wrote: Why am I having a problem getting a date field to hold date? I will store 2004-10-15, save the record. Reopen the record, and there is just -00-00 What's going on here? Thanks Steve -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: nebiew migrate access tables to mysql
It worked great but I would like to know why, thank! On Fri, 05 Nov 2004 13:42:29 +0200, Gleb Paharenko [EMAIL PROTECTED] wrote: Hi. Put Purge into backticks. `Purge` char(1), spiv007 [EMAIL PROTECTED] wrote: The complete error is: ERROR 1064 (42000) at line 355: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Purge CHAR(1), Dehydration CHAR(1), SomachDistentionCHAR(1), TissueGas CH' at line 12 CREATE TABLE if not exists EmbalmingCaseHistory ( CaseNumber CHAR(50) NOT NULL, PersonalEffectsYes CHAR(20), PersonalEffectsNo CHAR(1), Description TEXT, CauseOfDeathTEXT, TimeStarted DATETIME, TimeCompleted DATETIME, Normal CHAR(1), Emaciated CHAR(1), Edema CHAR(1), Purge CHAR(1), Dehydration CHAR(1), SomachDistentionCHAR(1), TissueGas CHAR(1), SkinSlipCHAR(1), JaundiceCHAR(1), BodyRefrigeratedCHAR(1), HowLong CHAR(50), RogorMortis CHAR(1), IVLeakage CHAR(1), Discoloration TEXT, SwellingTEXT, AutopsyFull CHAR(1), AutospyThoracic CHAR(1), AutopsyAbdominalCHAR(1), AutospyCranial CHAR(1), RemarksPriorToEmbalming TEXT, NeedleInjector CHAR(1), Sutures CHAR(1), MouthClosureRemarks TEXT, c_Natural CHAR(1), Mouthformer CHAR(1), Cotton CHAR(1), DenturesUpper CHAR(1), DenturesLower CHAR(1), EyeCaps CHAR(1), EyeCotton CHAR(1), EyeOtherCHAR(1), EyeOther1 CHAR(50), CarotidRCHAR(1), CarotidLCHAR(1), SubclavianR CHAR(1), SubclavianL CHAR(1), AxillaryR CHAR(1), AxillaryL CHAR(1), BrachialR CHAR(1), BrachialL CHAR(1), IlliacR CHAR(1), IlliacL CHAR(1), FemoralRCHAR(1), FemoralLCHAR(1), RadialR CHAR(1), RadialL CHAR(1), UlnarR CHAR(1), UlnarL CHAR(1), ArteriesOther CHAR(50), VJugularR CHAR(1), VJugularL CHAR(1), VAxillaryR CHAR(1), VAxillaryL CHAR(1), VIlliacRCHAR(1), VIlliacLCHAR(1), VFomoralR CHAR(1), VFomoralL CHAR(1), VeinsOther CHAR(50), HairWashedYes CHAR(1), HairWashedNoCHAR(1), BodyBathedYes CHAR(1), BodyBathedNoCHAR(1), EyesNoseMouthDisinfectedYes CHAR(1), EyesNoseMouthDisinfectedNo CHAR(1), ArterialConditionGood CHAR(1), ArterialConditionFair CHAR(1), ArterialConditionPoor CHAR(1), DrainageIntermittentCHAR(1), DrainageContinuoust CHAR(1), AspirationTreatmentImmediateCHAR(1), AspirationTreatmentDelayed CHAR(1), AspirationTreatmentHydroCHAR(1), AspirationTreatmentElectric CHAR(1), CavityTreatmentFluidCHAR(4), CavityTreatmentType CHAR(12), FluidDilutionsPreInjectedOz CHAR(5), FluidDilutionsPreInjectedGalCHAR(5), FluidDilutionsPreInjectedType CHAR(12), FluidDilutionsPreInjectedIndex CHAR(5), FluidDilutionsArterialOzCHAR(5), FluidDilutionsArterialGal CHAR(5), FluidDilutionsArterialType CHAR(12), FluidDilutionsArterialIndex CHAR(5), FluidDilutionsArterialOz1 CHAR(5), FluidDilutionsArterialGal1 CHAR(5), FluidDilutionsArterialType1 CHAR(12), FluidDilutionsArterialIndex1CHAR(50), FluidDilutionsArterialOz2 CHAR(5), FluidDilutionsArterialGal2 CHAR(5), FluidDilutionsArterialType2 CHAR(12), FluidDilutionsArterialIndex2CHAR(5), FluidDistributionGood CHAR(1), FluidDistributionFair CHAR(1), FluidDistributionPoor CHAR(1), AreasReceivingPoorCirculation TEXT, HowTreatedChemicalsUsed TEXT, HypodermicTreatmentArms CHAR(1), HypodermicTreatmentLegs CHAR(1), HypodermicTreatmentTorsoCHAR(1), HypodermicTreatmentNeck CHAR(1), HypodermicTreatmentFace
RE: Database Connection using DSN
Hi, Thank you for your reply. After establishing a connection to the Database using ODBC API, can we use the mysql API's like mysql_query() instead of using MyODBC API's. Thanks in Advance. Regards, Narasimha -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Thursday, November 04, 2004 11:00 PM To: [EMAIL PROTECTED] Subject: Re: Database Connection using DSN Hi. See: http://dev.mysql.com/doc/mysql/en/ODBC_Connector.html When you call mysql_real_connect you use mysqlclient library, which connects to server directly (you don't need to configure ODBC). To connect using DSN, you should use ODBC API. ODBC and MySQL are completly different things. MySQL - database system, and ODBC is an interface to interact with different databases. [EMAIL PROTECTED] wrote: Hi, I would like to know how to connect to the mysql database using a DSN in mysql API's. I am using myodbc 3.51 driver. Used the following API code for the connection MYSQL *link =3D mysql_init(NULL); MYSQL *connectHandle =3D mysql_real_connect(link,localhost,root,,test,0,NULL,0); But this is not having the option for specifying the DSN or Driver.=0D Please provide me the solution. Thanks, Narasimha Confidentiality Notice=0D The information contained in this electronic message and any attachments to= this message are intended for the exclusive use of the addressee(s) and may contain confidential or= privileged information. If you are not the intended recipient, please notify the sender at Wipro or= [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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] Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.7 serious problems
Gleb Paharenko wrote: Hi. There were several posts in list like yours. Do you use InnoDB tables? Try to increase values of key_buffer_size, read_buffer_size and so on. InnoDB is enabled but no InnoDB table is used yet (coming soon). However, it crashes with only 1 client connected. There is still plenty of free memory. Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimize query and/or db structure, FullText search + sort by other fields
Hi All, this was already posted on mysql forum preformance,
but forums are really slow, so sorry for the crosspost :)
My objective is to implement quick, really quick complex fulltext search
with 'order by' ( 2 seconds).
The actual table I'd like to search is `lot`. I've created 2 helper
tables : 1. `search`, which contains normalized (or stemmed) title and
description with fulltext search index 2. `category_map` which covers
all relations of categories, and is especially useful when I need to
perform search on all children categories of current category.
The total count of records in lot, and correspondingly in `search`,
tables is expected to be 50 000, each around 2048 (stemmed description)
+ 50 (stemmed title) bytes.
I need 1) fulltextsearch within all records, 2) category listing, 3)
fulltextsearch within a category.
Also, the result set should be *always* ordered either by lot.end_time,
lot.current_price * exchange_rate, lot.title or lot.bid_count. The
fulltextsearch relevance is not important, I'm ready to ignore it in
favor of performance.
Some time ago I've tried to put fulltext right inside the `lot` table,
but it seemed to produce much longer lasting queries than the current,
in plus in this case i was unable to LOAD INDEX INTO CACHE because of
different key length of FULLTEXT and other table's indexes. That's why
I've separated fields which I'd like to index fulltext into `search`
table. But now I'm stuck with performance degradation of ORDER BY. I've
tried to play with the related sort_buffer_size, myisam_sort_buffer_size
and other params described in the docs related ORDER BY and filesort,
but with no significant performance influence. I do not mind to create
as many helper tables as needed, but I cant figure out what else can I
improve. Another disappointing problem is that it is rather slow to
generate 50 000 records for different table structures (around 2 hours)
just to play with them for 10-20 mins.
Here is what I came up with at the moment:
SELECT lot.id, search.title FROM search LEFT JOIN lot ON lot.id =
search.lot_id, category_map, exchange_rate WHERE MATCH(search.title,
search.description) AGAINST ('some query' IN BOOLEAN MODE) AND
category_map.child_id = lot.category_id AND category_map.parent_id = 10
AND exchange_rate.currency_id = lot.currency_id ORDER BY
lot.current_price * exchange_rate.exchange_rate LIMIT 0, 50
Which produces almost acceptable results (which I'd like to improve
further) if I omit ORDER BY statement. I do understand that the slowdown
occurs mostly during filesort, and partially due to temporary table.
The question is
1) Which is a desirable tables structure which will allow to perform
fulltextsearch + ordering
2) Which is preferable LEFT JOIN .. ON, or WHERE ? I ahaven't noticed
any significant difference trying both of them
EXPLAIN:
++-+---+--+-+---+-+--+--+--+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
++-+---+--+-+---+-+--+--+--+
| 1 | SIMPLE | search | fulltext | title_description | title_description
| 0 | | 1 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | lot | eq_ref | PRIMARY,category_id | PRIMARY | 4 |
tauction.search.lot_id | 1 | Using where |
| 1 | SIMPLE | exchange_rate | ref | currency_id | currency_id | 4 |
tauction.lot.currency_id | 2 | Using index |
| 1 | SIMPLE | category_map | ref | parent_id,child_id | child_id | 4 |
tauction.lot.category_id | 12 | Using where |
++-+---+--+-+---+-+--+--+--+
4 rows in set (0.00 sec)
Table structure as follows.
DROP TABLE IF EXISTS lot;
CREATE TABLE lot (
id int unsigned not null primary key auto_increment,
owner_id int unsigned not null default 0,
title char(50) not null,
description text not null,
current_price decimal(16,2) unsigned not null,
quantity int unsigned not null,
bid_count int unsigned not null,
start_time int unsigned not null,
end_time int unsigned not null,
city char(35) not null,
category_id int unsigned not null,
currency_id int unsigned not null,
country_id int unsigned not null,
state_id int unsigned not null,
delivery_id int unsigned not null,
enabled bool default 1 not null,
INDEX title (title),
INDEX current_price (current_price),
INDEX bid_count (bid_count),
INDEX end_time (end_time),
INDEX category_id (category_id)
) TYPE = MyISAM COMMENT = Lots;
DROP TABLE IF EXISTS search;
CREATE TABLE search (
lot_id int unsigned not null,
title char(50) not null,
description text not null,
FULLTEXT title (title),
FULLTEXT title_description
Update query help
I have two tables. One has a list of customers. The other has a record of customer transactions including unix datestamps of each transaction. I've added a field to the customer table called First_Transaction I want to update this field with the datestamp of the first transaction for each customer from the Transaction table. I tried this... UPDATE Customer,Transactions set Customer.First_Transaction = MIN(Transactions.Datestamp) Where Customer.ID = Transactions.CustID But this doesn't work because of MIN() grouping. I'm stumped, anyone know how to accomplish this? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Column grouped by months
My current database: mysql SELECT - Category.Category, - GoalData.Reqvalue, - GoalData.GoalMonth - FROM - goaldata, - category - WHERE - goaldata.catid=category.id; +-+--+---+ | Category| Reqvalue | GoalMonth | +-+--+---+ | Mailroom Mail Opening Orders| 54 |11 | | Mailroom Rewards| 150 |11 | | Mailroom QC Opening Orders | 135 |12 | | Mailroom Opening Surveys| 200 |11 | | Mailroom QC Surveys | 350 |11 | | Mailroom Opening Resubmissions | 90 |11 | | Mailroom QC Resubmissions | 250 |12 | | Mailroom Microfilming | 700 |11 | | Mailroom Taping | 175 |11 | | Mailroom QC Taping | 350 |11 | | Mailroom Scanning | 1200 |12 | | Data Entry Orders (Key from paper) | 35 |11 | | Date Entry Surveys (Key form paper) | 45 |11 | | Data Entry Resubmissions| 50 |11 | | Data Entry Orders (Key from image) | 30 |12 | | Data Entry Surveys (Key from image) | 50 |11 | ... I want my output to look like this: +-+--+--+ | category| Nov| Dec| +-+--+--+ | Mailroom Mail Opening Orders| 54 | 54 | | Mailroom Rewards| 150 | 150 | | Mailroom QC Opening Orders | 135 | 135 | | Mailroom Opening Surveys| 200 | 200 | | Mailroom QC Surveys | 350 | 350 | | Mailroom Opening Resubmissions | 90 | 90 | | Mailroom QC Resubmissions | 250 | 250 | | Mailroom Microfilming | 700 | 700 | | Mailroom Taping | 175 | 175 | | Mailroom QC Taping | 350 | 350 | | Mailroom Scanning | 1200 | 1200 | | Data Entry Orders (Key from paper) | 35 | 35 | | Date Entry Surveys (Key form paper) | 45 | 45 | | Data Entry Resubmissions| 50 | 50 | | Data Entry Orders (Key from image) | 30 | 30 | | Data Entry Surveys (Key from image) | 50 | 50 | | Data Entry QC | 55 | 55 | +-+--+--+ Something like this didn't work -- SELECT category.category, goaldata.reqvalue, goaldata.goalmonth=11 as Nov, goaldata.goalmonth=12 as Dec FROM category, goaldata WHERE goaldata.catid=goaldata.id; How can I get around to it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update query help
Break it down into two steps. Compute your new values by customerid, then update your customer table with your computed data. CREATE TEMPORARY TABLE tmpFirstTran SELECT CustID, min(Datestamp) as mindate from Transactions group by CustID; update Customer c INNER JOIN tmpFirstTran ft ON ft.CustID = c.ID SET c.First_Transaction = ft.mindate; DROP TEMPORARY TABLE tmpFirstTran; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeff McKeon [EMAIL PROTECTED] wrote on 11/05/2004 09:04:06 AM: I have two tables. One has a list of customers. The other has a record of customer transactions including unix datestamps of each transaction. I've added a field to the customer table called First_Transaction I want to update this field with the datestamp of the first transaction for each customer from the Transaction table. I tried this... UPDATE Customer,Transactions set Customer.First_Transaction = MIN(Transactions.Datestamp) Where Customer.ID = Transactions.CustID But this doesn't work because of MIN() grouping. I'm stumped, anyone know how to accomplish this? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Column grouped by months
You're kind of mixing display formatting with data retrieval. MySQL is a database, so it's display options for data are fairly limited, that's usually the job of the front end. But, if you still want to push it to format the way you requested, you need to do a join. Essentially, you're going select one months records and join it with another months records. SELECT category.category, goaldata.reqvalue, Nov.goalmonth as Nov, Dec.goalmonth as Dec FROM category LEFT JOIN goaldata as Nov ON category.id=Nov.catid AND Nov.goalmonth=11 LEFT JOIN goaldata as Dec ON category.id=Dec.catid AND Dec.goalmonth=12 As you can see, scaling this up to 12 months can get pretty long and may take a while depending on the size of your data. Normally, you wouldn't haven't any joins, but would sort by month. Your front end would then reformat the data from a vertical orientation to a horizontal, which would be quicker and scale better. On Nov 5, 2004, at 9:05 AM, Scott Hamm wrote: My current database: mysql SELECT - Category.Category, - GoalData.Reqvalue, - GoalData.GoalMonth - FROM - goaldata, - category - WHERE - goaldata.catid=category.id; +-+--+---+ | Category| Reqvalue | GoalMonth | +-+--+---+ | Mailroom Mail Opening Orders| 54 |11 | | Mailroom Rewards| 150 |11 | | Mailroom QC Opening Orders | 135 |12 | | Mailroom Opening Surveys| 200 |11 | | Mailroom QC Surveys | 350 |11 | | Mailroom Opening Resubmissions | 90 |11 | | Mailroom QC Resubmissions | 250 |12 | | Mailroom Microfilming | 700 |11 | | Mailroom Taping | 175 |11 | | Mailroom QC Taping | 350 |11 | | Mailroom Scanning | 1200 |12 | | Data Entry Orders (Key from paper) | 35 |11 | | Date Entry Surveys (Key form paper) | 45 |11 | | Data Entry Resubmissions| 50 |11 | | Data Entry Orders (Key from image) | 30 |12 | | Data Entry Surveys (Key from image) | 50 |11 | ... I want my output to look like this: +-+--+--+ | category| Nov| Dec| +-+--+--+ | Mailroom Mail Opening Orders| 54 | 54 | | Mailroom Rewards| 150 | 150 | | Mailroom QC Opening Orders | 135 | 135 | | Mailroom Opening Surveys| 200 | 200 | | Mailroom QC Surveys | 350 | 350 | | Mailroom Opening Resubmissions | 90 | 90 | | Mailroom QC Resubmissions | 250 | 250 | | Mailroom Microfilming | 700 | 700 | | Mailroom Taping | 175 | 175 | | Mailroom QC Taping | 350 | 350 | | Mailroom Scanning | 1200 | 1200 | | Data Entry Orders (Key from paper) | 35 | 35 | | Date Entry Surveys (Key form paper) | 45 | 45 | | Data Entry Resubmissions| 50 | 50 | | Data Entry Orders (Key from image) | 30 | 30 | | Data Entry Surveys (Key from image) | 50 | 50 | | Data Entry QC | 55 | 55 | +-+--+--+ Something like this didn't work -- SELECT category.category, goaldata.reqvalue, goaldata.goalmonth=11 as Nov, goaldata.goalmonth=12 as Dec FROM category, goaldata WHERE goaldata.catid=goaldata.id; How can I get around to it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow Inner Joins
I am using 4.1 with inner joins, and my query is taking about 9.4 seconds to excute now. I used looping functions before in php, but I wanted one sql to do it all SELECTlocation FROM kanban_cards WHERE id IN(SELECT Max(id) FROM kanban_cards GROUP BY part, kan_number ORDER BY part, kan_number); My tableholds scan events, each card has a part number, and kan number. Each scan is a row, and the table holds several of the same part/card/locations, but at different dates. I need to select the most current row, one from each card, to get the current information of where they are in the plant. Fields in kanban_cardpart - textkan_number - intlocation - int datestamp - dateid - autonumber
Re: Column grouped by months
I believe you need a self join. Something like SELECT c.Category, g11.Reqvalue AS Nov, g12.Reqvalue AS Dec FROM goaldata g11 JOIN goaldata g12 ON g11.catid = g12.catid AND g11.GoalMonth = 11 AND g12.GoalMonth = 12 JOIN category c ON g11.catid=c.id; This might also work: SELECT c.Category, SUM(IF(g.GoalMonth = 11, g.Reqvalue, 0)) AS Nov, SUM(IF(g.GoalMonth = 12, g.Reqvalue, 0)) AS Dec, FROM goaldata g JOIN category c ON g.catid=c.id; GROUP BY c.Category; Note that if you ever have more than one row with a particular Category-GoalMonth combination (in the 2nd year, perhaps), the first query will give extra rows while the second will add the values. Assuming that's not what you want, you'd have to add an appropriate WHERE condition to limit the results to the right rows (months), or otherwise alter the query to fit however you decide to handle that case. Michael Scott Hamm wrote: My current database: mysql SELECT - Category.Category, - GoalData.Reqvalue, - GoalData.GoalMonth - FROM - goaldata, - category - WHERE - goaldata.catid=category.id; +-+--+---+ | Category| Reqvalue | GoalMonth | +-+--+---+ | Mailroom Mail Opening Orders| 54 |11 | | Mailroom Rewards| 150 |11 | | Mailroom QC Opening Orders | 135 |12 | | Mailroom Opening Surveys| 200 |11 | | Mailroom QC Surveys | 350 |11 | | Mailroom Opening Resubmissions | 90 |11 | | Mailroom QC Resubmissions | 250 |12 | | Mailroom Microfilming | 700 |11 | | Mailroom Taping | 175 |11 | | Mailroom QC Taping | 350 |11 | | Mailroom Scanning | 1200 |12 | | Data Entry Orders (Key from paper) | 35 |11 | | Date Entry Surveys (Key form paper) | 45 |11 | | Data Entry Resubmissions| 50 |11 | | Data Entry Orders (Key from image) | 30 |12 | | Data Entry Surveys (Key from image) | 50 |11 | ... I want my output to look like this: +-+--+--+ | category| Nov| Dec| +-+--+--+ | Mailroom Mail Opening Orders| 54 | 54 | | Mailroom Rewards| 150 | 150 | | Mailroom QC Opening Orders | 135 | 135 | | Mailroom Opening Surveys| 200 | 200 | | Mailroom QC Surveys | 350 | 350 | | Mailroom Opening Resubmissions | 90 | 90 | | Mailroom QC Resubmissions | 250 | 250 | | Mailroom Microfilming | 700 | 700 | | Mailroom Taping | 175 | 175 | | Mailroom QC Taping | 350 | 350 | | Mailroom Scanning | 1200 | 1200 | | Data Entry Orders (Key from paper) | 35 | 35 | | Date Entry Surveys (Key form paper) | 45 | 45 | | Data Entry Resubmissions| 50 | 50 | | Data Entry Orders (Key from image) | 30 | 30 | | Data Entry Surveys (Key from image) | 50 | 50 | | Data Entry QC | 55 | 55 | +-+--+--+ Something like this didn't work -- SELECT category.category, goaldata.reqvalue, goaldata.goalmonth=11 as Nov, goaldata.goalmonth=12 as Dec FROM category, goaldata WHERE goaldata.catid=goaldata.id; How can I get around to it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changing Date Type
I know that defining a Data datatype in a table renders the date in the form that the database is set to, Ie. -mm-dd by default. I want this to remain, but for one particular table, I want to set it to d-mmm-yy Eg. 5-nov-04 or 15-nov-04 instead of 2004-11-05 like the rest of my tables. I do not want to change my other tables or their settings, is this possible? Pls. assist. Regards, Amit Wadhwa. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Update query help
Yeah I thought of that but was hoping not to have to use a temp table. Thanks! Jeff -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, November 05, 2004 9:25 AM To: Jeff McKeon Cc: [EMAIL PROTECTED] Subject: Re: Update query help Break it down into two steps. Compute your new values by customerid, then update your customer table with your computed data. CREATE TEMPORARY TABLE tmpFirstTran SELECT CustID, min(Datestamp) as mindate from Transactions group by CustID; update Customer c INNER JOIN tmpFirstTran ft ON ft.CustID = c.ID SET c.First_Transaction = ft.mindate; DROP TEMPORARY TABLE tmpFirstTran; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeff McKeon [EMAIL PROTECTED] wrote on 11/05/2004 09:04:06 AM: I have two tables. One has a list of customers. The other has a record of customer transactions including unix datestamps of each transaction. I've added a field to the customer table called First_Transaction I want to update this field with the datestamp of the first transaction for each customer from the Transaction table. I tried this... UPDATE Customer,Transactions set Customer.First_Transaction = MIN(Transactions.Datestamp) Where Customer.ID = Transactions.CustID But this doesn't work because of MIN() grouping. I'm stumped, anyone know how to accomplish this? Thanks, Jeff -- 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]
ODBC Initial Setup Problems
Hello All: I am trying to configure ODBC for MySQL but have been unable to make a connection. We are running MySQL 4.0.15 supplied by Server Logistics on OS X 10.3.5. I have installed their ODBC Driver and 4 files show up in the library/MyODBC/Lib/ directory: libmyodbc3_r-3.51.06.bundle libmyodbc3_r.bundle (alias) libmyodbc3-3.51.06.bundle libmyodbc3.bundle (alias) Under the ODBC Admin I have added a driver with the name mysqlreporting specifying the driver file path as : /Library/MyODBC/lib/libmyodbc3-3.51.06.bundle. Didn't know what to put for setup file so the path is the same. I then setup 3 key values: keyword Value useruserloginname passworduserpasswordname databasemydatabase Under UserDSN, I add the above driver naming the data source name also mysqlreporting. I also enter the same keyvalues. When I go to test the connection locally, I get the following: Last login: Fri Nov 5 09:26:40 on ttyp1 Welcome to Darwin! My-Computer:~ myuserlogin$ /usr/bin/odbctest iODBC Demonstration program This program shows an interactive SQL processor Enter ODBC connect string (? shows list): ? DSN| Description --- mysqlreporting | mysqlreporting Enter ODBC connect string (? shows list): dsn=mysqlreporting [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded, SQLSTATE=IM002 Have a nice day. My-Computer:~ myuserlogin$ Can someone explain what I am doing wrong. Because their are two different drivers in the MyODBC Lib folder, I tried both but get the same message. Thanks. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing Date Type
I know that defining a Data datatype in a table renders the date in the form that the database is set to, Ie. -mm-dd by default. I want this to remain, but for one particular table, I want to set it to d-mmm-yy Eg. 5-nov-04 or 15-nov-04 instead of 2004-11-05 like the rest of my tables. I do not want to change my other tables or their settings, is this possible? Isn't that just a _display_ format? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Column grouped by months
Michael and Shawn's suggestions came into exactly same error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Dec, I'm running 5.0.1-alpha-nt. -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Friday, November 05, 2004 9:42 AM To: Scott Hamm Cc: 'Mysql ' (E-mail) Subject: Re: Column grouped by months I believe you need a self join. Something like SELECT c.Category, g11.Reqvalue AS Nov, g12.Reqvalue AS Dec FROM goaldata g11 JOIN goaldata g12 ON g11.catid = g12.catid AND g11.GoalMonth = 11 AND g12.GoalMonth = 12 JOIN category c ON g11.catid=c.id; This might also work: SELECT c.Category, SUM(IF(g.GoalMonth = 11, g.Reqvalue, 0)) AS Nov, SUM(IF(g.GoalMonth = 12, g.Reqvalue, 0)) AS Dec, FROM goaldata g JOIN category c ON g.catid=c.id; GROUP BY c.Category; Note that if you ever have more than one row with a particular Category-GoalMonth combination (in the 2nd year, perhaps), the first query will give extra rows while the second will add the values. Assuming that's not what you want, you'd have to add an appropriate WHERE condition to limit the results to the right rows (months), or otherwise alter the query to fit however you decide to handle that case. Michael Scott Hamm wrote: My current database: mysql SELECT - Category.Category, - GoalData.Reqvalue, - GoalData.GoalMonth - FROM - goaldata, - category - WHERE - goaldata.catid=category.id; +-+--+---+ | Category| Reqvalue | GoalMonth | +-+--+---+ | Mailroom Mail Opening Orders| 54 |11 | | Mailroom Rewards| 150 |11 | | Mailroom QC Opening Orders | 135 |12 | | Mailroom Opening Surveys| 200 |11 | | Mailroom QC Surveys | 350 |11 | | Mailroom Opening Resubmissions | 90 |11 | | Mailroom QC Resubmissions | 250 |12 | | Mailroom Microfilming | 700 |11 | | Mailroom Taping | 175 |11 | | Mailroom QC Taping | 350 |11 | | Mailroom Scanning | 1200 |12 | | Data Entry Orders (Key from paper) | 35 |11 | | Date Entry Surveys (Key form paper) | 45 |11 | | Data Entry Resubmissions| 50 |11 | | Data Entry Orders (Key from image) | 30 |12 | | Data Entry Surveys (Key from image) | 50 |11 | ... I want my output to look like this: +-+--+--+ | category| Nov| Dec| +-+--+--+ | Mailroom Mail Opening Orders| 54 | 54 | | Mailroom Rewards| 150 | 150 | | Mailroom QC Opening Orders | 135 | 135 | | Mailroom Opening Surveys| 200 | 200 | | Mailroom QC Surveys | 350 | 350 | | Mailroom Opening Resubmissions | 90 | 90 | | Mailroom QC Resubmissions | 250 | 250 | | Mailroom Microfilming | 700 | 700 | | Mailroom Taping | 175 | 175 | | Mailroom QC Taping | 350 | 350 | | Mailroom Scanning | 1200 | 1200 | | Data Entry Orders (Key from paper) | 35 | 35 | | Date Entry Surveys (Key form paper) | 45 | 45 | | Data Entry Resubmissions| 50 | 50 | | Data Entry Orders (Key from image) | 30 | 30 | | Data Entry Surveys (Key from image) | 50 | 50 | | Data Entry QC | 55 | 55 | +-+--+--+ Something like this didn't work -- SELECT category.category, goaldata.reqvalue, goaldata.goalmonth=11 as Nov, goaldata.goalmonth=12 as Dec FROM category, goaldata WHERE goaldata.catid=goaldata.id; How can I get around to it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Column grouped by months
This shouldn't be a problem because the data that I list here are only a required amount for operators to perform, hence no calculations required. A list of 17 categories is maximum and I only wanted to list 3 months in advance. -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Friday, November 05, 2004 9:31 AM To: Scott Hamm Cc: 'Mysql ' (E-mail) Subject: Re: Column grouped by months You're kind of mixing display formatting with data retrieval. MySQL is a database, so it's display options for data are fairly limited, that's usually the job of the front end. But, if you still want to push it to format the way you requested, you need to do a join. Essentially, you're going select one months records and join it with another months records. SELECT category.category, goaldata.reqvalue, Nov.goalmonth as Nov, Dec.goalmonth as Dec FROM category LEFT JOIN goaldata as Nov ON category.id=Nov.catid AND Nov.goalmonth=11 LEFT JOIN goaldata as Dec ON category.id=Dec.catid AND Dec.goalmonth=12 As you can see, scaling this up to 12 months can get pretty long and may take a while depending on the size of your data. Normally, you wouldn't haven't any joins, but would sort by month. Your front end would then reformat the data from a vertical orientation to a horizontal, which would be quicker and scale better. On Nov 5, 2004, at 9:05 AM, Scott Hamm wrote: My current database: mysql SELECT - Category.Category, - GoalData.Reqvalue, - GoalData.GoalMonth - FROM - goaldata, - category - WHERE - goaldata.catid=category.id; +-+--+---+ | Category| Reqvalue | GoalMonth | +-+--+---+ | Mailroom Mail Opening Orders| 54 |11 | | Mailroom Rewards| 150 |11 | | Mailroom QC Opening Orders | 135 |12 | | Mailroom Opening Surveys| 200 |11 | | Mailroom QC Surveys | 350 |11 | | Mailroom Opening Resubmissions | 90 |11 | | Mailroom QC Resubmissions | 250 |12 | | Mailroom Microfilming | 700 |11 | | Mailroom Taping | 175 |11 | | Mailroom QC Taping | 350 |11 | | Mailroom Scanning | 1200 |12 | | Data Entry Orders (Key from paper) | 35 |11 | | Date Entry Surveys (Key form paper) | 45 |11 | | Data Entry Resubmissions| 50 |11 | | Data Entry Orders (Key from image) | 30 |12 | | Data Entry Surveys (Key from image) | 50 |11 | ... I want my output to look like this: +-+--+--+ | category| Nov| Dec| +-+--+--+ | Mailroom Mail Opening Orders| 54 | 54 | | Mailroom Rewards| 150 | 150 | | Mailroom QC Opening Orders | 135 | 135 | | Mailroom Opening Surveys| 200 | 200 | | Mailroom QC Surveys | 350 | 350 | | Mailroom Opening Resubmissions | 90 | 90 | | Mailroom QC Resubmissions | 250 | 250 | | Mailroom Microfilming | 700 | 700 | | Mailroom Taping | 175 | 175 | | Mailroom QC Taping | 350 | 350 | | Mailroom Scanning | 1200 | 1200 | | Data Entry Orders (Key from paper) | 35 | 35 | | Date Entry Surveys (Key form paper) | 45 | 45 | | Data Entry Resubmissions| 50 | 50 | | Data Entry Orders (Key from image) | 30 | 30 | | Data Entry Surveys (Key from image) | 50 | 50 | | Data Entry QC | 55 | 55 | +-+--+--+ Something like this didn't work -- SELECT category.category, goaldata.reqvalue, goaldata.goalmonth=11 as Nov, goaldata.goalmonth=12 as Dec FROM category, goaldata WHERE goaldata.catid=goaldata.id; How can I get around to it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Column grouped by months
I can't seem to get to the online manual to check at the moment, but I'd guess that dec is a reserved word. Sorry about that. Just put single quotes around it. SELECT c.Category, g11.Reqvalue AS 'Nov', g12.Reqvalue AS 'Dec' FROM goaldata g11 JOIN goaldata g12 ON g11.catid = g12.catid AND g11.GoalMonth = 11 AND g12.GoalMonth = 12 JOIN category c ON g11.catid=c.id; Or SELECT c.Category, SUM(IF(g.GoalMonth = 11, g.Reqvalue, 0)) AS 'Nov', SUM(IF(g.GoalMonth = 12, g.Reqvalue, 0)) AS 'Dec', FROM goaldata g JOIN category c ON g.catid=c.id; GROUP BY c.Category; Michael Scott Hamm wrote: Michael and Shawn's suggestions came into exactly same error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Dec, I'm running 5.0.1-alpha-nt. -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Friday, November 05, 2004 9:42 AM To: Scott Hamm Cc: 'Mysql ' (E-mail) Subject: Re: Column grouped by months I believe you need a self join. Something like SELECT c.Category, g11.Reqvalue AS Nov, g12.Reqvalue AS Dec FROM goaldata g11 JOIN goaldata g12 ON g11.catid = g12.catid AND g11.GoalMonth = 11 AND g12.GoalMonth = 12 JOIN category c ON g11.catid=c.id; This might also work: SELECT c.Category, SUM(IF(g.GoalMonth = 11, g.Reqvalue, 0)) AS Nov, SUM(IF(g.GoalMonth = 12, g.Reqvalue, 0)) AS Dec, FROM goaldata g JOIN category c ON g.catid=c.id; GROUP BY c.Category; Note that if you ever have more than one row with a particular Category-GoalMonth combination (in the 2nd year, perhaps), the first query will give extra rows while the second will add the values. Assuming that's not what you want, you'd have to add an appropriate WHERE condition to limit the results to the right rows (months), or otherwise alter the query to fit however you decide to handle that case. Michael Scott Hamm wrote: My current database: mysql SELECT - Category.Category, - GoalData.Reqvalue, - GoalData.GoalMonth - FROM - goaldata, - category - WHERE - goaldata.catid=category.id; +-+--+---+ | Category| Reqvalue | GoalMonth | +-+--+---+ | Mailroom Mail Opening Orders| 54 |11 | | Mailroom Rewards| 150 |11 | | Mailroom QC Opening Orders | 135 |12 | | Mailroom Opening Surveys| 200 |11 | | Mailroom QC Surveys | 350 |11 | | Mailroom Opening Resubmissions | 90 |11 | | Mailroom QC Resubmissions | 250 |12 | | Mailroom Microfilming | 700 |11 | | Mailroom Taping | 175 |11 | | Mailroom QC Taping | 350 |11 | | Mailroom Scanning | 1200 |12 | | Data Entry Orders (Key from paper) | 35 |11 | | Date Entry Surveys (Key form paper) | 45 |11 | | Data Entry Resubmissions| 50 |11 | | Data Entry Orders (Key from image) | 30 |12 | | Data Entry Surveys (Key from image) | 50 |11 | ... I want my output to look like this: +-+--+--+ | category| Nov| Dec| +-+--+--+ | Mailroom Mail Opening Orders| 54 | 54 | | Mailroom Rewards| 150 | 150 | | Mailroom QC Opening Orders | 135 | 135 | | Mailroom Opening Surveys| 200 | 200 | | Mailroom QC Surveys | 350 | 350 | | Mailroom Opening Resubmissions | 90 | 90 | | Mailroom QC Resubmissions | 250 | 250 | | Mailroom Microfilming | 700 | 700 | | Mailroom Taping | 175 | 175 | | Mailroom QC Taping | 350 | 350 | | Mailroom Scanning | 1200 | 1200 | | Data Entry Orders (Key from paper) | 35 | 35 | | Date Entry Surveys (Key form paper) | 45 | 45 | | Data Entry Resubmissions| 50 | 50 | | Data Entry Orders (Key from image) | 30 | 30 | | Data Entry Surveys (Key from image) | 50 | 50 | | Data Entry QC | 55 | 55 | +-+--+--+ Something like this didn't work -- SELECT category.category, goaldata.reqvalue, goaldata.goalmonth=11 as Nov, goaldata.goalmonth=12 as Dec FROM category, goaldata WHERE goaldata.catid=goaldata.id; How can I get
Re: Column grouped by months
Michael Stassen wrote: I can't seem to get to the online manual to check at the moment, but I'd guess that dec is a reserved word. DEC is short for DECIMAL. I should have remembered that. Sigh... Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimize query and/or db structure, FullText search + sort by other fields
I found your query hard to understand, however it seems the optimizer
could read it just fine. I VERY MUCH dislike using the comma-separated
list of table names to declare INNER JOINS. I think it allows me too much
opportunity to accidentally create a Cartesian product by accidentally
forgetting a term in my WHERE clause. I firmly believe that a missing ON
clause is much easier to spot.
SELECT lot.id, search.title
FROM search
LEFT JOIN lot
ON lot.id = search.lot_id
INNER JOIN category_map
ON category_map.child_id = lot.category_id
AND category_map.parent_id = 10
INNER JOIN exchange_rate
ON exchange_rate.currency_id = lot.currency_id
WHERE MATCH(search.title, search.description)
AGAINST ('some query' IN BOOLEAN MODE)
ORDER BY lot.current_price * exchange_rate.exchange_rate
LIMIT 0, 50
If I just describe how each table contributes to each query I see that you
are going to scan every row of the search table and optionally match
those to records in the lot table but only if those lot records also
match up with both a category_map record and an exchange_rate record.
Are you sure those are the records you want to query?
What I think you should do is run the full-text portion of the search
first. Then join those results to lot, category_map, and
exchange_rate. If you have the same number of records in search as you
have in lot (I think you said you are testing with 5) then you
reduce the JOIN overhead for your query by quite a bit. If you have 5
search records and your FT search only returns 200 you just eliminated
49800 records from an additional table join.
CREATE TEMPORARY TABLE tmpLots(key(lotID))
SELECT DISTINCT lotID, title
FROM search
WHERE MATCH(search.title, search.description)
AGAINST ('some query' IN BOOLEAN MODE)
SELECT l.ID, tl.title
FROM lot l
INNER JOIN tmpLots tl
on tl.lotId = l.id
INNER JOIN category_map
ON category_map.child_id = l.category_id
AND category_map.parent_id = 10
INNER JOIN exchange_rate
ON exchange_rate.currency_id = l.currency_id
Order by l.current_price * exchange_rate.exchange_rate
DROP TEMPORARY TABLE tmpLots
Now, to address the speed of your ORDER BY. This is a quote from
http://dev.mysql.com/doc/mysql/en/ORDER_BY_optimization.html
If you want to increase ORDER BY speed, first see whether you can get
MySQL to use indexes rather than an extra sorting phase. If this is not
possible, you can try the following strategies:
* Increase the size of the sort_buffer_size variable.
* Increase the size of the read_rnd_buffer_size variable.
* Change tmpdir to point to a dedicated filesystem with lots of empty
space. If you use MySQL 4.1 or later, this option accepts several paths
that are used in round-robin fashion. Paths should be separated by colon
characters (`:') on Unix and semicolon characters (`;') on Windows,
NetWare, and OS/2. You can use this feature to spread the load across
several directories. Note: The paths should be for directories in
filesystems that are located on different physical disks, not different
partitions of the same disk.
Since you are computing the value you are ordering by, you cannot possibly
use an index so I guess that leaves you with just the other three
suggestions. You could possibly try creating another temp table to sort
your calculated values and apply a BTREE index to that value (HASH indexes
are not useful in ORDER BY optimization) so that your (top 50 ) query will
return faster. But you have to compare the difference in performance
between the current query and the process of creating the new temporary
table, populating it, and querying against it. I am not sure the extra
overhead involved in creating another indexed table will help (as the
number of records involved are much fewer), but it could.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Aleksandr Guidrevitch [EMAIL PROTECTED] wrote on 11/05/2004 08:25:00 AM:
Hi All, this was already posted on mysql forum preformance,
but forums are really slow, so sorry for the crosspost :)
My objective is to implement quick, really quick complex fulltext search
with 'order by' ( 2 seconds).
The actual table I'd like to search is `lot`. I've created 2 helper
tables : 1. `search`, which contains normalized (or stemmed) title and
description with fulltext search index 2. `category_map` which covers
all relations of categories, and is especially useful when I need to
perform search on all children categories of current category.
The total count of records in lot, and correspondingly in `search`,
tables is expected to be 50 000, each around 2048 (stemmed description)
+ 50 (stemmed title) bytes.
I need 1) fulltextsearch within all records, 2) category listing, 3)
fulltextsearch within a category.
Also, the result set
Re: ODBC Initial Setup Problems
On 5 Nov 2004 at 10:01, Rick Dwyer wrote: Hello All: I am trying to configure ODBC for MySQL but have been unable to make a connection. Hi, I have no experience of Mac OS X, but I believe it is linux-ish which means case- sensetive path names. The line below shows library with a lower case L: We are running MySQL 4.0.15 supplied by Server Logistics on OS X 10.3.5. I have installed their ODBC Driver and 4 files show up in the library/MyODBC/Lib/ directory: ^ libmyodbc3_r-3.51.06.bundle libmyodbc3_r.bundle (alias) libmyodbc3-3.51.06.bundle libmyodbc3.bundle (alias) Under the ODBC Admin I have added a driver with the name mysqlreporting specifying the driver file path as : This shows library with a capital L: /Library/MyODBC/lib/libmyodbc3-3.51.06.bundle. ^ Could it be that simple? I expect to be wrong ;) Hope this helps Ian -- Didn't know what to put for setup file so the path is the same. I then setup 3 key values: keyword Value user userloginname password userpasswordname database mydatabase Under UserDSN, I add the above driver naming the data source name also mysqlreporting. I also enter the same keyvalues. When I go to test the connection locally, I get the following: Last login: Fri Nov 5 09:26:40 on ttyp1 Welcome to Darwin! My-Computer:~ myuserlogin$ /usr/bin/odbctest iODBC Demonstration program This program shows an interactive SQL processor Enter ODBC connect string (? shows list): ? DSN| Description --- mysqlreporting | mysqlreporting Enter ODBC connect string (? shows list): dsn=mysqlreporting [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded, SQLSTATE=IM002 Have a nice day. My-Computer:~ myuserlogin$ Can someone explain what I am doing wrong. Because their are two different drivers in the MyODBC Lib folder, I tried both but get the same message. Thanks. Rick -- 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: Column grouped by months
I FORGOT my GROUP BY (arrrgh!) [EMAIL PROTECTED] wrote on 11/05/2004 09:28:20 AM: You were very close. What you are trying to produce is called a cross-tab report or a pivot table (depending on who you ask) SELECT category.category, sum(if(goaldata.goalmonth=11, reqvalue, 0)) as Nov, sum(if(goaldata.goalmonth=12, reqvalue, 0)) as Dec FROMcategory INNER JOIN goaldata ON goaldata.catid=goaldata.id; GROUP BY category.category Shawn Green Database Administrator Unimin Corporation - Spruce Pine Scott Hamm [EMAIL PROTECTED] wrote on 11/05/2004 09:05:50 AM: My current database: mysql SELECT - Category.Category, - GoalData.Reqvalue, - GoalData.GoalMonth - FROM - goaldata, - category - WHERE - goaldata.catid=category.id; +-+--+---+ | Category| Reqvalue | GoalMonth | +-+--+---+ | Mailroom Mail Opening Orders| 54 |11 | | Mailroom Rewards| 150 |11 | | Mailroom QC Opening Orders | 135 |12 | | Mailroom Opening Surveys| 200 |11 | | Mailroom QC Surveys | 350 |11 | | Mailroom Opening Resubmissions | 90 |11 | | Mailroom QC Resubmissions | 250 |12 | | Mailroom Microfilming | 700 |11 | | Mailroom Taping | 175 |11 | | Mailroom QC Taping | 350 |11 | | Mailroom Scanning | 1200 |12 | | Data Entry Orders (Key from paper) | 35 |11 | | Date Entry Surveys (Key form paper) | 45 |11 | | Data Entry Resubmissions| 50 |11 | | Data Entry Orders (Key from image) | 30 |12 | | Data Entry Surveys (Key from image) | 50 |11 | ... I want my output to look like this: +-+--+--+ | category| Nov| Dec| +-+--+--+ | Mailroom Mail Opening Orders| 54 | 54 | | Mailroom Rewards| 150 | 150 | | Mailroom QC Opening Orders | 135 | 135 | | Mailroom Opening Surveys| 200 | 200 | | Mailroom QC Surveys | 350 | 350 | | Mailroom Opening Resubmissions | 90 | 90 | | Mailroom QC Resubmissions | 250 | 250 | | Mailroom Microfilming | 700 | 700 | | Mailroom Taping | 175 | 175 | | Mailroom QC Taping | 350 | 350 | | Mailroom Scanning | 1200 | 1200 | | Data Entry Orders (Key from paper) | 35 | 35 | | Date Entry Surveys (Key form paper) | 45 | 45 | | Data Entry Resubmissions| 50 | 50 | | Data Entry Orders (Key from image) | 30 | 30 | | Data Entry Surveys (Key from image) | 50 | 50 | | Data Entry QC | 55 | 55 | +-+--+--+ Something like this didn't work -- SELECT category.category, goaldata.reqvalue, goaldata.goalmonth=11 as Nov, goaldata.goalmonth=12 as Dec FROM category, goaldata WHERE goaldata.catid=goaldata.id; How can I get around to it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: Changing Date Type
Yes, but I want to be able to insert into the column dates in the format d-mmm-yy -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Friday, November 05, 2004 8:38 PM To: Wadhwa, Amit; [EMAIL PROTECTED] Subject: Re: Changing Date Type I know that defining a Data datatype in a table renders the date in the form that the database is set to, Ie. -mm-dd by default. I want this to remain, but for one particular table, I want to set it to d-mmm-yy Eg. 5-nov-04 or 15-nov-04 instead of 2004-11-05 like the rest of my tables. I do not want to change my other tables or their settings, is this possible? Isn't that just a _display_ format? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ODBC Initial Setup Problems
On 5 Nov 2004 at 10:01, Rick Dwyer wrote: Hello All: I am trying to configure ODBC for MySQL but have been unable to make a connection. Hi, I have no experience of Mac OS X, but I believe it is linux-ish which means case- sensetive path names. The line below shows library with a lower case L: We are running MySQL 4.0.15 supplied by Server Logistics on OS X 10.3.5. I have installed their ODBC Driver and 4 files show up in the library/MyODBC/Lib/ directory: ^ Thanks Ian, but the capital L is just how I typed it in the email. The actual path is picked with a picker button so the case is set properly. Thanks though. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing Date Type
Yes, but I want to be able to insert into the column dates in the format d-mmm-yy That's a different story. Question though: why? Read some docs: http://dev.mysql.com/doc/mysql/en/Date_and_time_types.html btw, why is your e-mail important or high priority to me or the list? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com I know that defining a Data datatype in a table renders the date in the form that the database is set to, Ie. -mm-dd by default. I want this to remain, but for one particular table, I want to set it to d-mmm-yy Eg. 5-nov-04 or 15-nov-04 instead of 2004-11-05 like the rest of my tables. I do not want to change my other tables or their settings, is this possible? Isn't that just a _display_ format? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with date field
CREATE TABLE `events` ( `eventID` tinyint(4) NOT NULL auto_increment, `eventDate` date NOT NULL default '-00-00', `eventTitle` tinytext NOT NULL, `eventDesc` mediumtext NOT NULL, PRIMARY KEY (`eventID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | Gleb Paharenko [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi. If you send us output of show create table 'table_with_date_field', and queries, which you use to insert and retrieve date, may be we will be able to help you. Steve Grosz [EMAIL PROTECTED] wrote: Why am I having a problem getting a date field to hold date? I will store 2004-10-15, save the record. Reopen the record, and there is just -00-00 What's going on here? Thanks Steve -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: Problem with date field
This is on a Win2003 server system, and MySql server 4.1 Markus Grossrieder [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] What's going on here? God knows ! Maybe providing some information (OS, version, host app(if any), code example, db description, etc.) would permit some humble humans to take a guess ... - Original Message - From: Steve Grosz [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 05, 2004 8:13 AM Subject: Problem with date field Why am I having a problem getting a date field to hold date? I will store 2004-10-15, save the record. Reopen the record, and there is just -00-00 What's going on here? Thanks Steve -- 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: Changing Date Type
Because im reading data from another source which is always going to be in this format, and then inserting into the database. Thought if there was a way I could escape having to change the format before I insert into mysql... Sorry abt the high importance, it was turned on by default in my editor. -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Friday, November 05, 2004 9:44 PM To: Wadhwa, Amit; [EMAIL PROTECTED] Subject: Re: Changing Date Type Yes, but I want to be able to insert into the column dates in the format d-mmm-yy That's a different story. Question though: why? Read some docs: http://dev.mysql.com/doc/mysql/en/Date_and_time_types.html btw, why is your e-mail important or high priority to me or the list? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com I know that defining a Data datatype in a table renders the date in the form that the database is set to, Ie. -mm-dd by default. I want this to remain, but for one particular table, I want to set it to d-mmm-yy Eg. 5-nov-04 or 15-nov-04 instead of 2004-11-05 like the rest of my tables. I do not want to change my other tables or their settings, is this possible? Isn't that just a _display_ format? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing Date Type
On Friday 05 November 2004 15:56, [EMAIL PROTECTED] might have typed: Yes, but I want to be able to insert into the column dates in the format d-mmm-yy MySQL has a date format function that you can use to translate the format. The format in the table is always -mm-dd. Use translation functions on the way in and the way out of the database to transform the date into the format you want. Data input form and display form do not have to equate to the storage form. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimize query and/or db structure, FullText search + sort by other fields
Hi,
Try to add an index on join fields ( search.lot_id,
exchange_rate.currency_id,lot.currency_id)
Your query can not use an index to sort because MySql can use only 1
index to search and sort and your sort is a function so it scans
result rows and then it sorts the working table.
Santino
At 15:25 +0200 5-11-2004, Aleksandr Guidrevitch wrote:
Hi All, this was already posted on mysql forum preformance,
but forums are really slow, so sorry for the crosspost :)
My objective is to implement quick, really quick complex fulltext
search with 'order by' ( 2 seconds).
The actual table I'd like to search is `lot`. I've created 2 helper
tables : 1. `search`, which contains normalized (or stemmed) title
and description with fulltext search index 2. `category_map` which
covers all relations of categories, and is especially useful when I
need to perform search on all children categories of current
category.
The total count of records in lot, and correspondingly in `search`,
tables is expected to be 50 000, each around 2048 (stemmed
description) + 50 (stemmed title) bytes.
I need 1) fulltextsearch within all records, 2) category listing, 3)
fulltextsearch within a category.
Also, the result set should be *always* ordered either by
lot.end_time, lot.current_price * exchange_rate, lot.title or
lot.bid_count. The fulltextsearch relevance is not important, I'm
ready to ignore it in favor of performance.
Some time ago I've tried to put fulltext right inside the `lot`
table, but it seemed to produce much longer lasting queries than the
current, in plus in this case i was unable to LOAD INDEX INTO CACHE
because of different key length of FULLTEXT and other table's
indexes. That's why I've separated fields which I'd like to index
fulltext into `search` table. But now I'm stuck with performance
degradation of ORDER BY. I've tried to play with the related
sort_buffer_size, myisam_sort_buffer_size and other params described
in the docs related ORDER BY and filesort, but with no significant
performance influence. I do not mind to create as many helper tables
as needed, but I cant figure out what else can I improve. Another
disappointing problem is that it is rather slow to generate 50 000
records for different table structures (around 2 hours) just to play
with them for 10-20 mins.
Here is what I came up with at the moment:
SELECT lot.id, search.title FROM search LEFT JOIN lot ON lot.id =
search.lot_id, category_map, exchange_rate WHERE MATCH(search.title,
search.description) AGAINST ('some query' IN BOOLEAN MODE) AND
category_map.child_id = lot.category_id AND category_map.parent_id =
10 AND exchange_rate.currency_id = lot.currency_id ORDER BY
lot.current_price * exchange_rate.exchange_rate LIMIT 0, 50
Which produces almost acceptable results (which I'd like to improve
further) if I omit ORDER BY statement. I do understand that the
slowdown occurs mostly during filesort, and partially due to
temporary table.
The question is
1) Which is a desirable tables structure which will allow to perform
fulltextsearch + ordering
2) Which is preferable LEFT JOIN .. ON, or WHERE ? I ahaven't
noticed any significant difference trying both of them
EXPLAIN:
++-+---+--+-+---+-+--+--+--+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
++-+---+--+-+---+-+--+--+--+
| 1 | SIMPLE | search | fulltext | title_description |
title_description | 0 | | 1 | Using where; Using temporary; Using
filesort |
| 1 | SIMPLE | lot | eq_ref | PRIMARY,category_id | PRIMARY | 4 |
tauction.search.lot_id | 1 | Using where |
| 1 | SIMPLE | exchange_rate | ref | currency_id | currency_id | 4 |
tauction.lot.currency_id | 2 | Using index |
| 1 | SIMPLE | category_map | ref | parent_id,child_id | child_id |
4 | tauction.lot.category_id | 12 | Using where |
++-+---+--+-+---+-+--+--+--+
4 rows in set (0.00 sec)
Table structure as follows.
DROP TABLE IF EXISTS lot;
CREATE TABLE lot (
id int unsigned not null primary key auto_increment,
owner_id int unsigned not null default 0,
title char(50) not null,
description text not null,
current_price decimal(16,2) unsigned not null,
quantity int unsigned not null,
bid_count int unsigned not null,
start_time int unsigned not null,
end_time int unsigned not null,
city char(35) not null,
category_id int unsigned not null,
currency_id int unsigned not null,
country_id int unsigned not null,
state_id int unsigned not null,
delivery_id int unsigned not null,
enabled bool default 1 not null,
INDEX title
RE: Database Connection using DSN
What Andrey was trying to say is that this group is specifically for the discussion of problems and development issues relating to the MySQL executables themselves, not how to connect to them. Your original question boils down to a basic lack of clue. There are two distinct methods currently at your disposal (more if you needed them) to allow a program you write to interact with a MySQL server. One method is to use the MySQL API's, the other method is to use the ODBC framework. They are separate techniques and separate development paths you can take. The APIs do not use the ODBC layer to abstract the database or it's functionality, they connect to the server as directly as possible. The ODBC framework (which was probably written using the MySQL APIs) is an abstraction of a database, a wrapper. In essence, ODBC is a method of making all databases appear nearly the same by creating a common interface on one side of a driver and mapping that interface to the specific requirements of each database system within the driver itself. If you want to make an ODBC connection (because you insist, for some reason, that you must create and use a DSN) then you need to stay within the ODBC framework. All of your function calls need to use only the methods and properties exposed to you from the ODBC libraries. However, if you want to use the API library (for its increased speed and efficiency) then you must use API methods to make the connection and manipulate data (no DSN, no ODBC). These are two separate systems and should not intermingle. Please direct all future programming related questions to the general discussion list : [EMAIL PROTECTED] Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 11/05/2004 12:21:54 AM: Andrey, I expected the answer, if you know that, help me in that. -Original Message- From: Andrey Hristov [mailto:[EMAIL PROTECTED] Sent: Thursday, November 04, 2004 8:00 PM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Cc: [EMAIL PROTECTED] Subject: Re: Database Connection using DSN Isn't that a question only for the general list. Cross posting is not the nicest possible behaviour. Andrey [EMAIL PROTECTED] wrote: Hi, I would like to know how to connect to the mysql database using a DSN in mysql API's. I am using myodbc 3.51 driver. Used the following API code for the connection MYSQL *link = mysql_init(NULL); MYSQL *connectHandle = mysql_real_connect(link,localhost,root,,test,0,NULL,0); But this is not having the option for specifying the DSN or Driver. Please provide me the solution. Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0.20 for full-text searching with match against
We are switching web servers, and they have installed Mysql 4.0.20. One of our apps uses full-text boolean text searching with MATCH AGAINST. I wanted to ensure this version of MySQL supports it, or if I need a newer version of MySQL. Can someone verify? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load index into cache not working
This bug is a problem with the reporting when sending a SIGHUP or the command mysqladmin debug. What I'm seeing is the process simply isn't growing in memory. I'm looking at the process size in 'top'. I do notice that it grows once I start hitting it with queries. I'd expect it to grow as soon as I preload the index. Isn't that the point of preloading? On Fri, 2004-11-05 at 05:25, Gleb Paharenko wrote: Hi. There is a bug: http://bugs.mysql.com/bug.php?id=4285. Mark Maunder [EMAIL PROTECTED] wrote: I have a large fulltext index (the MYI file is about 750 Megs) and I've set my key_buffer_size to 1 Gig. I do: load index into cache fttest; and I watch the Mysql process in memory, and it doesn't grow. It just hangs around 250Megs. Why isn't the index loading into memory? Thanks, Mark. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- Mark D. Maunder [EMAIL PROTECTED] http://www.workzoo.com/ The Best jobs from the Best Job Sites. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: nebiew migrate access tables to mysql
Put Purge into backticks. `Purge` char(1), It worked great but I would like to know why, thank! It could be that 'purge' is a reserved word in MySQL. I wanted to have a table with a shortened name of 'description' by trying to create a table with a 'desc' field, and MySQL had problems with it too, because 'desc' is a reserved word to 'describe' a table definition. But, creating the table as `desc` varchar(20) worked just fine... -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: nebiew migrate access tables to mysql
The reserved words are listed in the manual http://dev.mysql.com/doc/mysql/en/Reserved_words.html. 'Purge' is on the list. Michael ian douglas wrote: Put Purge into backticks. `Purge` char(1), It worked great but I would like to know why, thank! It could be that 'purge' is a reserved word in MySQL. I wanted to have a table with a shortened name of 'description' by trying to create a table with a 'desc' field, and MySQL had problems with it too, because 'desc' is a reserved word to 'describe' a table definition. But, creating the table as `desc` varchar(20) worked just fine... -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ANNOUNCE: SHSQL - SQL for LINUX/UNIX Shell scripts
Wow - cool idea - nice job. Looking forward to playing with it. David. Eddy Macnaghten wrote: Hi all I have just released a utility (under the GPL) that enables SQL to be incorporated into UNIX/LINUX shell scripts (easier than using psql or similar with better integration). For more information see http://www.edlsystems.com/shsql To download ftp://ftp.edlsystems.com/shsql (needless to say a MySQL version is included :-)) Enjoy Eddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ORDER by date: reverse order
I want to sort by date but the last date appears first. How to write such query? TH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ORDER by date: reverse order
[snip] I want to sort by date but the last date appears first. How to write such query? [/snip] RTFM ORDER BY theDate DESC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER by date: reverse order
write: order by date desc DeRyl - Original Message - From: Jerry Swanson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 05, 2004 8:00 PM Subject: ORDER by date: reverse order I want to sort by date but the last date appears first. How to write such query? TH -- 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: nebiew migrate access tables to mysql
What about this? I will not take AUTO INCREMENT I tried removing the underscore and putting `AUTO INCREMENT` , but im getting the same error as before. CREATE TABLE if not exists TASK ( `Payment Date` DATE, ID INT AUTO_INCREMENT, CaseNumber CHAR(12) NOT NULL, Payment_Amount FLOAT(8,2), INDEX CaseNumber ( CaseNumber ), INDEX ID ( ID ), INDEX ( ID ) ); On Fri, 05 Nov 2004 10:22:42 -0800, ian douglas [EMAIL PROTECTED] wrote: Put Purge into backticks. `Purge` char(1), It worked great but I would like to know why, thank! It could be that 'purge' is a reserved word in MySQL. I wanted to have a table with a shortened name of 'description' by trying to create a table with a 'desc' field, and MySQL had problems with it too, because 'desc' is a reserved word to 'describe' a table definition. But, creating the table as `desc` varchar(20) worked just fine... -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: nebiew migrate access tables to mysql
replied to him privately with this before I realized he'd sent a different copy with the list CC'd: CREATE TABLE if not exists TASK ( `Payment Date` DATE, ID INT AUTO_INCREMENT, CaseNumber CHAR(12) NOT NULL, Payment_Amount FLOAT(8,2), PRIMARY KEY (ID), KEY CaseNumber ( CaseNumber ) ); spiv007 wrote: What about this? I will not take AUTO INCREMENT I tried removing the underscore and putting `AUTO INCREMENT` , but im getting the same error as before. CREATE TABLE if not exists TASK ( `Payment Date` DATE, ID INT AUTO_INCREMENT, CaseNumber CHAR(12) NOT NULL, Payment_Amount FLOAT(8,2), INDEX CaseNumber ( CaseNumber ), INDEX ID ( ID ), INDEX ( ID ) ); On Fri, 05 Nov 2004 10:22:42 -0800, ian douglas [EMAIL PROTECTED] wrote: Put Purge into backticks. `Purge` char(1), It worked great but I would like to know why, thank! It could be that 'purge' is a reserved word in MySQL. I wanted to have a table with a shortened name of 'description' by trying to create a table with a 'desc' field, and MySQL had problems with it too, because 'desc' is a reserved word to 'describe' a table definition. But, creating the table as `desc` varchar(20) worked just fine... -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication log error
Hi. I'm having a problem with a slave replication server. the 'show slave status' command shows : mysql show slave status; +-+-+-+---+-+-+--+---+---+--+---+-+-++--+--+-+-+ | Master_Host | Master_User | Master_Port | Connect_retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | Exec_master_log_pos | Relay_log_space | +-+-+-+---+-+-+--+---+---+--+---+-+-++--+--+-+-+ | hubble | replica | 3306| 60| hubble-bin.009 | 646906124 | gemini-relay-bin.008 | 1006270724| hubble-bin.009 | No | No | | | 0 | error 'unexpected success or fatal error' on query 'INSERT INTO COBGE7A' | 0 | 646905758 | 1006271180 | +-+-+-+---+-+-+--+---+---+--+---+-+-++--+--+-+-+ 1 row in set (0.00 sec) the query shows a table that don't exists ( COBGE7A ) , so what I thought is that the relay log was corrupt. I'm trying to read the log with mysqlbinlog : mysqlbinlog -f gemini-relay-bin.008 nuevo.sql and got : ERROR: Error in Log_event::read_log_event(): 'Event too small', data_len=0,event_type=0 ERROR: Could not read entry at offset 909811060 : Error in log format or read error Could i skip the lines before the infamous offset number or something else...? Thanks in advance and have a good weekend. -- Alvaro Avello [EMAIL PROTECTED] Servinco S.A.
Re: MySQL 4.0.20 for full-text searching with match against
Hi. As of Version 4.0.1, MySQL can also perform boolean full-text searches using the IN BOOLEAN MODE modifier. Eve Atley [EMAIL PROTECTED] wrote: We are switching web servers, and they have installed Mysql 4.0.20. One of our apps uses full-text boolean text searching with MATCH AGAINST. I wanted to ensure this version of MySQL supports it, or if I need a newer version of MySQL. Can someone verify? Thanks, Eve -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: Database Connection using DSN
Hi. No. [EMAIL PROTECTED] wrote: Hi, Thank you for your reply.=0D After establishing a connection to the Database using ODBC API, can we use the mysql API's like mysql_query() instead of using MyODBC API's. Thanks in Advance. Regards, Narasimha -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Thursday, November 04, 2004 11:00 PM To: [EMAIL PROTECTED] Subject: Re: Database Connection using DSN Hi. See: http://dev.mysql.com/doc/mysql/en/ODBC_Connector.html When you call mysql_real_connect you use mysqlclient library, which connects to server directly (you don't need to configure ODBC). To connect using DSN, you should use ODBC API. ODBC and MySQL are completly different things. MySQL - database system, and ODBC is an interface to interact with different databases. [EMAIL PROTECTED] wrote: =0D =0D =0D Hi, I would like to know how to connect to the mysql database using a DSN in mysql API's. I am using myodbc 3.51 driver. =0D Used the following API code for the connection =0D MYSQL *link =3D3D mysql_init(NULL); MYSQL *connectHandle =3D3D mysql_real_connect(link,localhost,root,,test,0,NULL,0); =0D But this is not having the option for specifying the DSN or Driver.=3D0D Please provide me the solution. =0D =0D Thanks, Narasimha =0D =0D Confidentiality Notice=3D0D =0D The information contained in this electronic message and any attachments to=3D this message are intended for the exclusive use of the addressee(s) and may contain confidential or=3D privileged information. If you are not the intended recipient, please notify the sender at Wipro or=3D [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. =0D --=0D For technical support contracts, goto https://order.mysql.com/?ref=3Densita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com --=0D MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Confidentiality Notice=0D The information contained in this electronic message and any attachments to= this message are intended for the exclusive use of the addressee(s) and may contain confidential or= privileged information. If you are not the intended recipient, please notify the sender at Wipro or= [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: Column grouped by months
Hi. Use aliasing. Read carefully comments at http://dev.mysql.com/doc/mysql/en/JOIN.html Scott Hamm [EMAIL PROTECTED] wrote: My current database: mysql SELECT - Category.Category, - GoalData.Reqvalue, - GoalData.GoalMonth - FROM - goaldata, - category - WHERE - goaldata.catid=category.id; +-+--+---+ | Category| Reqvalue | GoalMonth | +-+--+---+ | Mailroom Mail Opening Orders| 54 |11 | | Mailroom Rewards| 150 |11 | | Mailroom QC Opening Orders | 135 |12 | | Mailroom Opening Surveys| 200 |11 | | Mailroom QC Surveys | 350 |11 | | Mailroom Opening Resubmissions | 90 |11 | | Mailroom QC Resubmissions | 250 |12 | | Mailroom Microfilming | 700 |11 | | Mailroom Taping | 175 |11 | | Mailroom QC Taping | 350 |11 | | Mailroom Scanning | 1200 |12 | | Data Entry Orders (Key from paper) | 35 |11 | | Date Entry Surveys (Key form paper) | 45 |11 | | Data Entry Resubmissions| 50 |11 | | Data Entry Orders (Key from image) | 30 |12 | | Data Entry Surveys (Key from image) | 50 |11 | ... I want my output to look like this: +-+--+--+ | category| Nov| Dec| +-+--+--+ | Mailroom Mail Opening Orders| 54 | 54 | | Mailroom Rewards| 150 | 150 | | Mailroom QC Opening Orders | 135 | 135 | | Mailroom Opening Surveys| 200 | 200 | | Mailroom QC Surveys | 350 | 350 | | Mailroom Opening Resubmissions | 90 | 90 | | Mailroom QC Resubmissions | 250 | 250 | | Mailroom Microfilming | 700 | 700 | | Mailroom Taping | 175 | 175 | | Mailroom QC Taping | 350 | 350 | | Mailroom Scanning | 1200 | 1200 | | Data Entry Orders (Key from paper) | 35 | 35 | | Date Entry Surveys (Key form paper) | 45 | 45 | | Data Entry Resubmissions| 50 | 50 | | Data Entry Orders (Key from image) | 30 | 30 | | Data Entry Surveys (Key from image) | 50 | 50 | | Data Entry QC | 55 | 55 | +-+--+--+ Something like this didn't work -- SELECT category.category, goaldata.reqvalue, goaldata.goalmonth=11 as Nov, goaldata.goalmonth=12 as Dec FROM category, goaldata WHERE goaldata.catid=goaldata.id; How can I get around to it? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
[OT] Strange message from mysql-help@lists.mysql.com
Hello, I have just received this message from [EMAIL PROTECTED]: At 21:17 + 5-11-2004, [EMAIL PROTECTED] wrote: To confirm that you would like [EMAIL PROTECTED] removed from the mysql mailing list, please click on the following link: ... Received: (qmail 20876 invoked by uid 48); 5 Nov 2004 21:17:10 - Date: 5 Nov 2004 21:17:10 - Message-ID: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Unsubscribe request From: [EMAIL PROTECTED] This message was generated because of a request from 68.125.48.133. That ip is not my ip and I don't want to be removed. Please, can someone help me? Thanks Santino T -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT where String
hello, I've a query that runs very slow: select name, count(id) where str_field = some string or (str_field string with nuber at end 1 and str_field string with nuber at end 9) group by name i have about 4mil records and the query takes about 3 minutes str_field, name are MUL indexes both varchar(100) and id is a pk. is there a way to speed up the query? (the and then seemed to be pretty fast but adding or str_field = 'some string' made it really slow).
Advanced SELECT Syntax Help Needed!
Hi! I would love some help with my syntax (or another strategy). I keep bombing. I've simplified it. Here is the deal: Three files: Main: id, name Links1: id, linkname1 (a record may or may not exist for each record in Main) Links2: id, linkname2 (a record may or may not exist for each record in Main) I need a list of every record in main plus linkname1 and linkname2. I am trying to use a subquery. SELECT name, linkname2, links1.id, links1.linkname1 FROM (SELECT main.id, main.name, links2.linkname2 from main LEFT OUTER JOIN links2 using (id)) AS subfile LEFT OUTER JOIN links1 using (id) I keep getting syntax errors. I would really appreciate any input. Thanks a bunch -- Monique.
RE: replication log error
mysql show slave status\G; *** 1. row *** Master_Host: hubble Master_User: replica Master_Port: 3306 Connect_retry: 60 Master_Log_File: hubble-bin.009 Read_Master_Log_Pos: 646906124 Relay_Log_File: gemini-relay-bin.008 Relay_Log_Pos: 1006270724 Relay_Master_Log_File: hubble-bin.009 Slave_IO_Running: No Slave_SQL_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: error 'unexpected success or fatal error' on query 'INSERT INTO COBGE7A' Skip_counter: 0 Exec_master_log_pos: 646905758 Relay_log_space: 1006271180 1 row in set (0.01 sec) On Fri, 2004-11-05 at 14:37 -0800, Max Michaels wrote: Please run the command as 'show slave status\G' and send those results. Makes it much, much easier to read :) -Original Message- From: Alvaro Avello [mailto:[EMAIL PROTECTED] Sent: Friday, November 05, 2004 3:36 PM To: [EMAIL PROTECTED] Subject: replication log error Hi. I'm having a problem with a slave replication server. the 'show slave status' command shows : mysql show slave status; +-+-+-+---+-+-+--+---+---+--+---+-+-++--+--+-+-+ | Master_Host | Master_User | Master_Port | Connect_retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | Exec_master_log_pos | Relay_log_space | +-+-+-+---+-+-+--+---+---+--+---+-+-++--+--+-+-+ | hubble | replica | 3306| 60| hubble-bin.009 | 646906124 | gemini-relay-bin.008 | 1006270724| hubble-bin.009 | No | No | | | 0 | error 'unexpected success or fatal error' on query 'INSERT INTO COBGE7A' | 0 | 646905758 | 1006271180 | +-+-+-+---+-+-+--+---+---+--+---+-+-++--+--+-+-+ 1 row in set (0.00 sec) the query shows a table that don't exists ( COBGE7A ) , so what I thought is that the relay log was corrupt. I'm trying to read the log with mysqlbinlog : mysqlbinlog -f gemini-relay-bin.008 nuevo.sql and got : ERROR: Error in Log_event::read_log_event(): 'Event too small', data_len=0,event_type=0 ERROR: Could not read entry at offset 909811060 : Error in log format or read error Could i skip the lines before the infamous offset number or something else...? Thanks in advance and have a good weekend. -- Alvaro Avello [EMAIL PROTECTED] Servinco S.A.
Re: Advanced SELECT Syntax Help Needed!
I'm not sure why you want to use a subquery; if MySQL is anything like DB2, a join usually performs better than a subquery and the optimizer converts a subquery to a join (under the covers) whenever it can anyway. Therefore, how about something like: select id, name, linkname1, linkname2 from main m right outer join links1 l1 on m.id = l1.id right outer join links l2 on m.id = l2.id; I haven't tested it but it ought to work. Rhino - Original Message - From: Monique [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 05, 2004 4:59 PM Subject: Advanced SELECT Syntax Help Needed! Hi! I would love some help with my syntax (or another strategy). I keep bombing. I've simplified it. Here is the deal: Three files: Main: id, name Links1: id, linkname1 (a record may or may not exist for each record in Main) Links2: id, linkname2 (a record may or may not exist for each record in Main) I need a list of every record in main plus linkname1 and linkname2. I am trying to use a subquery. SELECT name, linkname2, links1.id, links1.linkname1 FROM (SELECT main.id, main.name, links2.linkname2 from main LEFT OUTER JOIN links2 using (id)) AS subfile LEFT OUTER JOIN links1 using (id) I keep getting syntax errors. I would really appreciate any input. Thanks a bunch -- Monique. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql query to return unique ids from a table of date stamped results
I am trying to find the sql statement needed to extract, from a table of data with multiple instances of a id no, a list of unique id nos, picking the latest (by datestamp which is stored as a second field) so that a master list is updated. The application is a list of student photos, each database row defines the filename of a photo, the student id is assigned to the photo once it is known, I then need to produce a list for all photos showing the details of the latest photo for each student. SELECT * FROM Photosforimport ORDER BY adno, Lastupdatetime DESC Gives me the data I need, however I need to be able to just pick out the row with the latest Lastupdatetime for each ADNO. I couldn`t find anything on google, but may have been asking the wrong question! Any help greatly appreciated. Rob Keeling -- -- I love deadlines. I love the whooshing noise they make as they go by. - Douglas Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
connection problem with 4.1.7
Hi, I am having an intermitten connection problem with MySQL 4.1.7 . Here is the setup. Intel P4 with HT Fedora Core 1 kernel 2.4.27 smp MySQL version 4.1.7 (RPM install from mysql.org) Qmail with vpopmail using mysql (www.qmailtoaster.com) Sometimes I cannot login to qmail to check mail. Using thunderbird or even webmail. Only way to solve the problem is to restart MySQL. Then everything is ok. After maybe 2 hours the same problem again. I am concluding that this is a MySQL problem because other apps that use MySQL also shows problems. qmail vpopmail is using socket and my cms (WebGUI) is using tcp to connect. I have tried to check the logs at /var/lib/mysql. But do not see anything any error. I do see a warning like this, 041106 9:50:59 [Warning] Asked for 196608 thread stack, but got 126976 The strange thing is that I can still connect to MySQL using webmin. Is there any other thing I can do to find out what is causing the problem? Maybe I have made some silly mistake. Please advice and help. P.V.Anthony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: connection problem with 4.1.7
Hi, When you have this problem, do the MySQL client applications work? Have you tried logging in with the mysql client? Kostas -Ursprüngliche Nachricht- Von: P.V.Anthony [mailto:[EMAIL PROTECTED] Gesendet: Samstag, 6. November 2004 02:36 An: [EMAIL PROTECTED] Betreff: connection problem with 4.1.7 Hi, I am having an intermitten connection problem with MySQL 4.1.7 . Here is the setup. Intel P4 with HT Fedora Core 1 kernel 2.4.27 smp MySQL version 4.1.7 (RPM install from mysql.org) Qmail with vpopmail using mysql (www.qmailtoaster.com) Sometimes I cannot login to qmail to check mail. Using thunderbird or even webmail. Only way to solve the problem is to restart MySQL. Then everything is ok. After maybe 2 hours the same problem again. I am concluding that this is a MySQL problem because other apps that use MySQL also shows problems. qmail vpopmail is using socket and my cms (WebGUI) is using tcp to connect. I have tried to check the logs at /var/lib/mysql. But do not see anything any error. I do see a warning like this, 041106 9:50:59 [Warning] Asked for 196608 thread stack, but got 126976 The strange thing is that I can still connect to MySQL using webmin. Is there any other thing I can do to find out what is causing the problem? Maybe I have made some silly mistake. Please advice and help. P.V.Anthony -- 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]
extracting ddl
Hi, is it possible to extract the ddl for a table or whole db? ie the ddl that would be needed to re-create that table. JBoss automatically creates some tables during ejb deployment and I want to move this to a manual process. cheers Nathan -- Nathan Coast Managing Director codeczar ltd mobile: (852) 9049 5581 email: mailto:[EMAIL PROTECTED] web:http://www.codeczar.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: extracting ddl
Hi, All MySQL server installations ships with some client tools. Try executing the following statement on your server mysqldump -uUSERNAME -p DATABASENAME dump.sql After this operation dump.sql will include all information needed. The information can be loaded into another server issuing the following command mysql -uUSERNAME -p DATABASENAME dump.sql (the option -p makes the client ask for the password for the user USERNAME) kostas -Ursprüngliche Nachricht- Von: Nathan Coast [mailto:[EMAIL PROTECTED] Gesendet: Samstag, 6. November 2004 03:40 An: [EMAIL PROTECTED] Betreff: extracting ddl Hi, is it possible to extract the ddl for a table or whole db? ie the ddl that would be needed to re-create that table. JBoss automatically creates some tables during ejb deployment and I want to move this to a manual process. cheers Nathan -- Nathan Coast Managing Director codeczar ltd mobile: (852) 9049 5581 email: mailto:[EMAIL PROTECTED] web:http://www.codeczar.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 admin clients
Hello, Check out SQLyog at http://www.webyog.com/forums/index.php?s=94f4afb247fdfdfbd6435d793a56d60aact=STf=2t=977st=0#entry3909 Its FREE and very powerful. Regards, Karam --- leegold [EMAIL PROTECTED] wrote: newbie question about mysql admin clients. What are some good ones? And importantly can they access and allow me to edit the db record fields themselves? eg. I see a mis-spelled word in a field - I could go in there and edit the field. Thanks, lee G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
