Re: Support for subqueries in V 4.1.1
Had the same problem Are u calling the query from the MysqlCC ??? , it is adding some info to server about limited the query . Try from a sql prompt on server or set the automatically limited Select Queries to - unlimited maximum join size - unlimited on the tab : mysql options (right click on ur server , and choose edit) regards Kim Pedersen I've upgraded a MySQL 4.0.14 server to 4.1.1-alpha version because I wanted to experiment with the support for subqueries. Not being an expert on SQL at this point, I tried a couple of simple subqueries and got the following error message: ERROR 1235: This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery' That doesn't seem to fit with what I've read in the MySQL 4.1.1 documentation. I also tried some queries using a sample database from a MySQL book (which presumably reduces the possibility that I made some mindless error with my queries) and got the same response. When I issue a SELECT version(); SQL command, I get 4.1.1-alpha-standard as I would expect. Could something have gone wrong with the upgrade or am I missing something obvious about the subquery support in 4.1.1? Thanks for any pointers you can provide. Michael Michael Greis [EMAIL PROTECTED] Office: (781) 449-8017 Home: (781) 449-8007 mvh Kim G. Pedersen macaos/elprint Development +45 35373808 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Locks dont lock
Hello I try in my program to make a lock. I try to prevent another session to read and write from the whole table I tried both LOCK TABLES pstockx WRITE and LOCK TABLES pstockx READ but no success. I put in a breakpoint in my program to be sure the lock are held and then I try to make a select from another session. It dont lock me up :( What am I doing wrong ,,is it some settings on server ?? 8128 Query LOCK TABLES pstockx READ 031211 0:22:058133 Connect [EMAIL PROTECTED] on elprint 8133 Query SET SQL_BIG_SELECTS=1 8133 Query select amount from pstockx where productID = 82846 8133 Query EXPLAIN select amount from pstockx where pro ductID = 1 031211 0:23:068128 Query SHOW COLUMNS FROM pstockx 8128 Query SHOW INDEX FROM pstockx 8128 Query select amount from pstockx where productID=8 2846 TIA Kim G. Pedersen macaos/elprint Development +45 35373808 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Locking one table blocks others
Hi U lucky man ,,, I can't make my locks work at all :( BUT just a little hint , u sure it not ur _ (underscore) in table name which is a kind of wildcard that give u trouble ?? regards Kim Pedersen Fella's, First of all I'd like to welcome myself to this list. Have been here before, but now that I need your help again I decided to return. For the following question I've of course read the Manual, googled some .. Even checked the archives. Found no answer that satisfied me. For a website I'm writing some kinda stock-market game, really fun. You should check it out when it's done. Of course, while updating stuff on certain tables I want to do some things to prevent lost updates, dirty reads and stuff like that. Would pretty much spoil the game for my dear users. Therefor I request a lock LOCK TABLES eurostoq_units WRITE; That's the only table I really need a lock on. I am inserting in one other, and updating to again one other but since I know beforehand for sure nobody else is changing or reading from there I thought I didn't need to lock it. But now, when I do something like: INSERT INTO eurostoq_transactions (walletid, unitid, value, what) VALUES (9, 22, 400.3, 's'); Mysql starts Yelling at me, stating: ERROR 1100: Table 'eurostoq_transactions' was not locked with LOCK TABLES Now, please .. Of course I can request locks on other tables too. In fact I did, as a workaround. But can anybody explain me why I cannot do anything (just found out I can't even do 'describe TABLENAME' anymore when there's one table locked) else with any other table? Thanks, Wouter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] mvh Kim G. Pedersen macaos/elprint Development +45 35373808 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[SQL]how to delete record where I reference to other table
Hi Has look alot around , and tried a lot without success how to do this : Delete from pproductMix PM where PM.mixID in (select ID from pmix where mixno=72000) I would like a solution with and without subqueries thanks Kim G. Pedersen macaos/elprint Development +45 35373808 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Infile confusion
Hello Robert ,, I have no explanation , just a dummy question , 1) Are all values u try to put into column 5 a float with . ? 2) what if u try with integer ?? without . ? 3) can u input float values in column 3 ? 4) are u running mysqlcc from same pc ? 5 could it be some settings with decimal point is set to something diff than . depending of ur enviroment ? Not much help , only a kind of maybe list :) HTH Kim G. Pedersen This one has had me scratching my head all afternoon. I have a comma delimited text file that I am using to import data from a legacy system into a MyISAM table. My server is 4.0.16 as is my client. Both run on Redhat 8.0. An example line of imput looks like 041502f,1,25.00,$,25.00,,2003-1-15T17:47:50,O with my table format being CREATE TABLE IF NOT EXISTS FeeSplit( FileNumber VARCHAR( 50 ) BINARY NOT NULL, AppraiserCode VARCHAR( 08 ) BINARY NOT NULL, SplitNumber FLOAT DEFAULT 0.00, SplitDesignator CHAR( 01 ) DEFAULT '$', CalculatedSplitAmt FLOAT DEFAULT 0.00, DateFeePaid DATE, DateTimeRecordAdded DATETIME, PersonDesignatorCHAR( 01 ), SplitComments TEXT, LastModifiedTIMESTAMP, LastModifiedBy VARCHAR( 08 ), PRIMARY KEY ( FileNumber, AppraiserCode, SplitNumber, SplitDesignator, CalculatedSplitAmt, DateFeePaid, DateTimeRecordAdded ), INDEX FileNumberDesignatorIndex ( FileNumber, PersonDesignator ), INDEX AppraiserDesignatorIndex ( FileNumber, AppraiserCode, PersonDesignator ), INDEX AppraiserCodeIndex ( AppraiserCode ), INDEX FileNumberIndex ( FileNumber ); To load my data, I execute the following commands mysql --user=xxx --password=xxx -e USE Live_Tables; DELETE QUICK FROM FeeSplit; FLUSH TABLES; mysql --user=xxx --password=xxx -e USE Live_Tables; LOAD DATA INFILE 'Converted_FeeSplit.txt' INTO TABLE FeeSplit FIELDS TERMINATED BY ',' ENCLOSED BY '\' ESCAPED BY '~'; When these commands have completed, the value of field 5 is *always* 0.00. This in and of itself has had me confused - especially since I can execute the same SQL commands in MySQLCC and the contents of field 5 will be whatever is in my input file. I had this problem with 4.0.13 as well as now with 4.0.16. Has anyone seen this before? And possibly has a cure or fix? Thanks! Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] mvh Kim G. Pedersen macaos/elprint Development +45 35373808 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Next release of 4.1x , any release plans
Hello Since it quite buggy using 4.1 in combination with subselect it could be nice to know next planned release of 4.1.x regards Kim G. Pedersen macaos/elprint Development -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY DESC order different when using LIMIT
Hi I am not sure how this limit works , but in oracle if you use a rownum100 (guess it is some of the same), the sorting are done After the limitation so... if u say rownum100 , it gives u 100 lines abitrary order and then it sorts that 100 lines HTH Kim G. Pedersen Description: When using ORDER BY column DESC the lines are shown in one order but when I use LIMIT 0,1 the second line insted of the first line are shown. And when I use LIMIT 2,1 the same line as if use LIMIT 3,1 are shown. The table has 4 rows. How-To-Repeat: mysql SELECT ImgId,CreatedDate FROM dogge ORDER BY CreatedDate DESC; +---+-+ | ImgId | CreatedDate | +---+-+ | 2 | 2002-11-22 | | 3 | 2002-11-22 | | 1 | 2002-11-21 | | 4 | 2002-11-21 | +---+-+ 4 rows in set (0.00 sec) mysql SELECT ImgId,CreatedDate FROM dogge ORDER BY CreatedDate DESC LIMIT 0,1; +---+-+ | ImgId | CreatedDate | +---+-+ | 3 | 2002-11-22 | +---+-+ 1 row in set (0.00 sec) mysql SELECT ImgId,CreatedDate FROM dogge ORDER BY CreatedDate DESC LIMIT 1,1; +---+-+ | ImgId | CreatedDate | +---+-+ | 2 | 2002-11-22 | +---+-+ 1 row in set (0.00 sec) mysql SELECT ImgId,CreatedDate FROM dogge ORDER BY CreatedDate DESC LIMIT 2,1; +---+-+ | ImgId | CreatedDate | +---+-+ | 4 | 2002-11-21 | +---+-+ 1 row in set (0.00 sec) mysql SELECT ImgId,CreatedDate FROM dogge ORDER BY CreatedDate DESC LIMIT 3,1; +---+-+ | ImgId | CreatedDate | +---+-+ | 4 | 2002-11-21 | +---+-+ 1 row in set (0.00 sec) mysql SELECT ImgId,CreatedDate FROM dogge ORDER BY CreatedDate DESC LIMIT 0,4; +---+-+ | ImgId | CreatedDate | +---+-+ | 2 | 2002-11-22 | | 3 | 2002-11-22 | | 1 | 2002-11-21 | | 4 | 2002-11-21 | +---+-+ 4 rows in set (0.00 sec) mysql DESCRIBE dogge; +-+--+--+-+++ | Field | Type | Null | Key | Default| Extra | +-+--+--+-+++ | ImgId | int(10) unsigned | | PRI | NULL | auto_increment | | CreatedDate | date | | MUL | -00-00 | | +-+--+--+-+++ 2 rows in set (0.00 sec) mysql SELECT * FROM dogge\G *** 1. row *** ImgId: 1 CreatedDate: 2002-11-21 *** 2. row *** ImgId: 4 CreatedDate: 2002-11-21 *** 3. row *** ImgId: 2 CreatedDate: 2002-11-22 *** 4. row *** ImgId: 3 CreatedDate: 2002-11-22 4 rows in set (0.00 sec) Fix: Don't know Submitter-Id: submitter ID Originator: Martin Gillstrom Organization: MySQL support: none Synopsis: ORDER BY DESC order different when using LIMIT Severity: serious Priority: medium Category: mysql Class: Release: mysql-4.0.16-standard (Official MySQL RPM) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux server1.bymartin.com 2.4.20-20.7 #1 Mon Aug 18 14:56:30 EDT 2003 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.3 2.96-113) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=i486 -fno-strength-reduce' CXX='g++' CXXFLAGS='-O2 -mcpu=i486 -fno-strength-reduce -felide-constructors -fno-exceptions -fno-rtti ' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Mar 21 2003 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x1 root root 1260480 Mar 5 2003 /lib/libc-2.2.5.so -rw-r--r--1 root root 2312442 Mar 5 2003 /usr/lib/libc.a -rw-r--r--1 root root 178 Mar 5 2003 /usr/lib/libc.so Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--with-server-suffix=-standard' '--without-embedded-server' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--libdir=/usr/lib' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--enable-thread-safe-client' '--with-comment=Official
Re: Sql - Problem with Left Join
Thanks to You Brent and Bill Easton and Sorry Hrmm , that what happen when not clipping direct into mail program , here is the Original :-) : Select A.DepartmentName,A.Address,P.Postcode,P.cityname,CP.firstname from caddress A,cpostinfo P left Join CContactPerson CP , caddresscontactperson CACP ,ccontactinfo CCI on CP.ID =1001 and CACP.ContactpersonID=CP.ID and CACP.AddressID=A.ID and CACP.ID=CCI.AddressContactPersonID and CCI.AddressID=-1 and CCI.ContactInfoTypeID=1 where A.ID=10 and A.PostInfoID=P.ID Something was wrong in this query above , so I did rewrite the query : Select A.DepartmentName,A.Address,P.Postcode,P.cityname ,CP.firstname from caddress A,cpostinfo P Left join CContactPerson CP on CP.ID=1001 left join caddresscontactperson CACP ,ccontactinfo CCI on CACP.ContactpersonID=1001 and CACP.AddressID=10 and CCI.AddressID=-1 and CCI.AddressContactpersonID=CACP.ID and CCI.contactInfotypeID=1 where A.ID=10 and A.PostInfoID=P.ID but I still do not understand : if I just have one record in caddresscontactperson the query return a the correct answer but I caddresscontactperson are a empty table it give my empty answer - why not --company , streeet ,2000,copenhagen , NULL I know I use addresscontactperson in my left join , but why does that influent other that giving me a NULL for the CP.firstname value Your syntax is wrong, or at least not standard, if you are trying to do multiple left joins. Your ordering is not your typical for a query. And you say there instead of where. Your query should be structure like this: SELECT fields FROM table, table,... LEFT JOIN table ON join condition LEFT JOIN table ON join condition LEFT JOIN table ON join condition ... WHERE filter condition I'm surprised your query worked at all. On Wednesday, November 12, 2003, at 05:20 PM, Kim G. Pedersen wrote: the little Query1 works until I add the left join : Query 1 ) Select A.DepartmentName,A.Address,P.Postcode,P.cityname from caddress A,cpostinfo P there A.ID=10 and A.PostInfoID=P.ID Query 2) Select A.DepartmentName,A.Address,P.Postcode,P.cityname, CP.firstname left Join CContactPerson CP , caddresscontactperson CACP ,ccontactinfo CCI on CP.ID =1001 and CACP.ContactpersonID=CP.ID and CACP.AddressID=A.ID and CACP.ID=CCI.AddressContactPersonID and CCI.AddressID=-1 and CCI.ContactInfoTypeID=1 from caddress A,cpostinfo P there A.ID=10 and A.PostInfoID=P.ID Query1 return : - company , streeet ,2000,copenhagen Query2 return : - Empty I know that the jeft join will give zero result , since the table caddresscontactperson are empty. But I do not Understand Why I not get - company , streeet ,2000,copenhagen , NULL Normally a left join should not effect ur result. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 mvh Kim G. Pedersen macaos/elprint Development +45 35373808 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[SubQuery] BUG ??
hello I have a very strange case where the my result depend on the fields I want return from query :-( A simplified query (Extracted the Sub queries out for read easyness): select POL1.*,PRO.ID from oPositionLine POL1 , (SUB2) T4 join pproduct PRO on POL1.ProductID=PRO.ID wherePOL1.positionID = T4.PositionID this works perfect, BUT if I just change to vv select POL1.*,PRO.ID from oPositionLine POL1 , (SUB2) T4 join pproduct PRO on POL1.ProductID=PRO.ID wherePOL1.positionID = T4.PositionID I get empty result SUB2 = Select OPOL.PositionID from oPositionLine OPOL,oeventtype OET , (SUB1) t3 where OPOL.PositionID = t3.positionID and OET.ID=OPOL.EventtypeID group by OPOL.PositionID,OPOL.ProductID having sum(OET.SignValue*OPOL.LocalPrice) 0 Sub1 = select PositionID from opositionline OPOL ,oeventtype OET ,oposition POS where OET.ID=OPOL.EventtypeID and OET.kind=1 and POS.ID=OPOL.positionID and POS.finish='false' group by OPOL.PositionID having sum(OET.signvalue*OPOL.ProductAmount) =0 ** FULL QUERY ** select POL1.ID,PRO.ID from oPositionLine POL1 , (Select OPOL.PositionID from oPositionLine OPOL,oeventtype OET ,(select OPOL.PositionID from opositionline OPOL ,oeventtype OET ,oposition POS where OET.ID=OPOL.EventtypeID and OET.kind=1 and POS.ID=OPOL.positionID and POS.finish='false' group by OPOL.PositionID having sum(OET.signvalue*OPOL.ProductAmount) =0) t3 where OPOL.PositionID = t3.positionID and OET.ID=OPOL.EventtypeID group by OPOL.PositionID,OPOL.ProductID having sum(OET.SignValue*OPOL.LocalPrice) 0 ) T4 POS2.InvoiceAddrID = A.ID join pproduct PRO on POL1.ProductID=PRO.ID wherePOL1.positionID = T4.PositionID ** FULL QUERY END ** Using 4.1.0-alfa-log regards Kim G. Pedersen macaos/elprint Development +45 35373808 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
from char to Date routine
Hello
I looking for a way to convert a datestring to date value
example
UnknowFunction('23.03.68','dd.mm.yy') - unixtimestamp
In oracle we have
to_date('23.03.68','dd.mm.yy')
I have search the net for hours without luck.
Regards
Kim G. Pedersen
macaos/elprint Development
+45 35373808
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Truncate do not reset auto increment counter
Kim G. Pedersen [EMAIL PROTECTED] wrote: I once red that using truncate table xx instead of delete from xx should reset autoincrement , but it seem not to happend. but when I try to insert data with insert or load data in it remember the old autoincrement value. from manual : Truncate operations drop and re-create the table, which is much faster than deleting rows one by one. seems not , when I drop table and the create table again it works perfect. What table type do you use? TRUNCATE TABLE reset auto_increment value for MyISAM table, but not for InnoDB. thanks Egor I use InnoDB , so that explains it :-) what Are the most easy way to reset the value in innodb 4.014 regards Kim G. Pedersen macaos/elprint Development +45 35373808 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Truncate do not reset auto increment counter
hello I once red that using truncate table xx instead of delete from xx should reset autoincrement , but it seem not to happend. but when I try to insert data with insert or load data in it remember the old autoincrement value. from manual : Truncate operations drop and re-create the table, which is much faster than deleting rows one by one. seems not , when I drop table and the create table again it works perfect. Mysql Linux 4.014 regards Kim G. Pedersen macaos/elprint Development +45 35373808 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Hi Alejondro I use ORDER BY, because I want that order in PTemp table, so I do not have to order them later (because they are retrieved several times later). If I understand correct ,,, U can never trust the order ur records get return from DB (it is indepented of the order u insert records ) with other words , I u are interest in a specific order when selecting , u MUST use Order By hope this help regards Kim G. Pedersen macaos/elprint Development +45 35373808 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with SQL Query
Hi Having 2 tables TOrders: ProductID Amount 400810 4008 -5 4110 7 4110 2 4110 -4 4110 1 TStock ProductID Count 4110 3 4110 2 What I want Is a sum of product need to be produced. I try without the stock first :-) Select O.ProductID , Sum(Amount) from TOrders O group by O.ProductID having sum(O.Amount)0 work nice so I try include the stock: Select O.ProductID , Sum(Amount) from TOrders O left join Tstock S on S.ProductID=O.ProductID group by O.ProductID having sum(O.Amount)-Sum(S.Count)0 result - ProductIDSUM(Amount) Sum (count) 400850 411012 #6*2 20 #(2+3)*4 should have been 6 and 5 Hope u can see problem (some kind of Cartesian product ) , please help using mySQL 4.014 Regards Kim Pedersen macaos/elprint Development -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How To See Sql Statements send to server ?
Hello How can I see the lastest Sql Commands/statements which has been send to my mySql server ?? Are there Any Logfile or command to use ??? Thanks in advance Kim G. Pedersen macaos/elprint Development +45 35373808 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
