Re: Finding a point inside a polygon
Hello Mark, You can locate a point, whether inside or outside a polygon area using the query below: SELECT contains(geomfromtext(@poly),geomfromtext(@p)); where @poly,@p are variables. Eg: set @p='Point(3 2)'; set @poly= 'Polygon((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1)) '; If the return value of the select statement is 0 - Outside the polygon 1 - Inside the polygon Thanks, ViSolve MySQL Support Team. - Original Message - From: Mark Maunder [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, July 21, 2006 4:40 AM Subject: Finding a point inside a polygon I'd like to test whether a point is truly inside a polygon, not just insude the minimum bounding rectangle. Is there a way to do this in MySQL with the spatial extensions? I love mysql but I'm forced to consider migrating to postgresql (ugh!) because it has built in support for testing spatial relationships between polygons and points. Thanks. Mark. -- 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: Finding a point inside a polygon
Thanks, but according to the documentation the Contains() function is the same as the MBRContains() function which only tests if the point is inside the minimum bounding rectangle of the polygon, not the actual polygon. See the following: http://dev.mysql.com/doc/refman/5.0/en/functions-that-test-spatial-relationships-between-geometries.html From the manual: Currently, MySQL does not implement these functions according to the specification. Those that are implemented return the same result as the corresponding MBR-based functions. On 7/21/06, ViSolve DB Team [EMAIL PROTECTED] wrote: Hello Mark, You can locate a point, whether inside or outside a polygon area using the query below: SELECT contains(geomfromtext(@poly),geomfromtext(@p)); where @poly,@p are variables. Eg: set @p='Point(3 2)'; set @poly= 'Polygon((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1)) '; If the return value of the select statement is 0 - Outside the polygon 1 - Inside the polygon Thanks, ViSolve MySQL Support Team. - Original Message - From: Mark Maunder [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, July 21, 2006 4:40 AM Subject: Finding a point inside a polygon I'd like to test whether a point is truly inside a polygon, not just insude the minimum bounding rectangle. Is there a way to do this in MySQL with the spatial extensions? I love mysql but I'm forced to consider migrating to postgresql (ugh!) because it has built in support for testing spatial relationships between polygons and points. Thanks. Mark. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Mark Maunder [EMAIL PROTECTED] http://www.markmaunder.com/ +1-206-6978723 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
hi, I am not able view all the records in the tabel
Hi, Problem is like this. There is table with 'table_name-1' where, when I am executing the query like ' select * from table_name-1;' it is showing only 432 records but acctually there are 539 records in the table. when I select individual records from the table it is showing (able to view the record data) from 1, . . . 539 record. I want all the records to be displayed when i select using 'select * from table_name-1;'. Regards A Srinivas
Re: How to disable foreign_key_checks when using mysqlimport?
Gabriel PREDA schrieb: Try: ALTER TABLE `tbl_name` DISABLE KEYS; -- now insert in the TXT file ALTER TABLE `tbl_name` ENABLE KEYS; I think this is what you were looking for ! That would have been a possibility. I did it this way now: ... echo set sql_log_bin=0; set foreign_key_checks=0; use $db; load data infile \$txtfilename\ into table $tablename fields enclosed by '\';| $MYSQL_BIN $MYSQL_CONNECT ... Script performs several checks before this statement and puts the txtfile in the appropriate directory. Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to disable foreign_key_checks when using mysqlimport?
mysqldump -u [user] -h [host] -p [database] [table] fixme.sql This is for one table. As I need it for all my tables in all my databases, I'd have to write a script for that. And as --tab uses less space, I prefer --tab option for mysqldump. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
HI, I am Annam Srinivas
Re: FULL TEXT and Asian languages
Peter Lauri schrieb: Best group member, I have a problem. I was going to use FULL TEXT search for my Thai client. It is working smooth with English text and wordings, the indexing and search works fine. The problem with Thai text is that words are not separated with a white space as in English and other languages. I think this screws up the indexing, and complete sentences are classed as a word. Assume Thai characters: Thisisasentenceinthai. ButIcannotsearchforsentenceinthatsearch. I want to search for sentence, but can not. How can this be done? And will the indexing ever work? Best regards, Peter Lauri well you can use: WHERE text LIKE '%sentence%' is it that what you are looking for? You can also use regular expressions. These will also work on Asian text. -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Permissions Weirdness
Chris White schrieb: Normally I try to be as descriptive as possible with subject lines but.. not quite sure what else to call it. So basically, I was given ALL access by our sysadmin to a particular database. Now, somehow I was able to create a database and use it! It's my understanding that there is nothing grantable with all that would allow this (correct me if I'm wrong). Then to make it even better, whenever I access/run whatever on the database, permissions get totally whack and nothing can login anymore. I'd love to google this but am not quite sure where to even start. Thanks ahead of time for any ideas. mysql 5.0.22-standard There is a difference on User GRANTS and Table GRANTS. Mixed this up probably? -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication from InnoDB to MyISAM
[EMAIL PROTECTED] schrieb: anyone know if there's any known issue with replication from InnoDB tables to MyISAM tables? I just switched a slave (mysql) to replicate from a different master that uses InnoDB and now I'm seeing weird problems on the slave. Table corruption, apps that can't connect etc. thanks, Jeff For me it reads like i want to replicate bananas but want to shape them like melons. The customers are confused that the melons taste like bananas Am i correct with this? Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Invalid syntax with STD() function when more than one field is used in select query
Oops, the version numbers were 4.1.18-nt and 4.1.19-standard. I have tried it now on the latest 4.1.20 version and still have the same problem. Does anyone have any ideas? Is this a bug? Cheers, Bill -Original Message- From: William Bronsema Sent: Thursday, July 20, 2006 10:18 AM To: mysql@lists.mysql.com Subject: Invalid syntax with STD() function when more than one field is used in select query Hello, I am encountering a strange issue when using the STD function. On my local development machine (MYSQL version 4.18-nt) I can run the following basic SELECT query with no problems: SELECT STD(`LAPSETIME`),UKEY FROM 4b3f91f64a19529a84dff4982c8a6bc5 GROUP BY UKEY When I test this query on my hosted production machine (MYSQL version 4.19-standard) that query results in an invalid syntax error: #1064 - 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 '( `LAPSETIME` ) , UKEY FROM 4b3f91f64a19529a84dff4982c8a6bc5 GROUP BY UKEY LIM' at line 1 The query will work if I remove the UKEY field in the select: SELECT STD(`LAPSETIME`) FROM 4b3f91f64a19529a84dff4982c8a6bc5 Any ideas? Cheers, Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Very slow query
Hello everbody, I have a table in my MySQL 5.0.22 Server with this columns. +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | DATE | datetime | NO | MUL | -00-00 00:00:00 | | | IMAGE| longblob | NO | | NULL| | | IMAGETYPE| varchar(8) | NO | MUL | NULL| | | KEYFILE | longblob | NO | | NULL| | | SEARCHENGINE | varchar(64) | NO | MUL | NULL| | | SEARCHTERM | varchar(256) | NO | MUL | NULL| | | SIZE | int(11) | NO | MUL | 0 | | | URL | varchar(512) | NO | | NULL| | | NAME | varchar(256) | NO | MUL | NULL| | +--+--+--+-+-+---+ When I insert several hundert rows with data (all columns, but without the keyfile column) and query the database with this string: SELECT date, imagetype, searchengine, searchterm, size, url, name FROM digiforensic where searchterm='...' it goes very fast (less than one second) and the right index for searchterm is used. This even works when I shutdown and restart the server. The problem is, that the keyfiles are calculated after the datasets are inserted into the database. After calculation they are inserted with an update of the corresponding row. After that the query of the datasets takes a lot of time - more than 2 minutes. I checked that the right index is used with the EXPLAIN expression. Did anybody know why this happens or what my error is? Thanks and Regards Michael begin:vcard fn:Michael Sutter n:Sutter;Michael org:Forschungszentrum Karlsruhe;Institute of Data Processing and Electronics adr:Hermann-von-Helmholtz-Platz 1;;Geb. 442;Eggenstein-Leopoldshafen;;76344;Germany email;internet:[EMAIL PROTECTED] tel;work:+49 7247 825676 tel;fax:+49 7247 823560 version:2.1 end:vcard smime.p7s Description: S/MIME Cryptographic Signature
Anyone interested in a 4gl compiler ?
Hi there, I'm the lead developer on Aubit4GL (http://aubit4gl.sourceforge.net) which is a 4gl compiler used for writing database centric applications based on the original Informix-4GL language.. I'm just wondering what the appetite is like out there for a mysql compatible version (we've got a very simple mysql connector already, as well as postgres, ODBC). -- Mike Aubury -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Anyone interested in a 4gl compiler ?
Hello, Have you taken a look at MySQL Forge? http://forge.mysql.com/ Great place check out other projects or users working on projects who may be interested in what your working on. Thanks, Jimmy Guerrero Sr Product Manager MySQL, Inc -Original Message- From: Mike Aubury [mailto:[EMAIL PROTECTED] Sent: Friday, July 21, 2006 8:53 AM To: mysql@lists.mysql.com Subject: Anyone interested in a 4gl compiler ? Hi there, I'm the lead developer on Aubit4GL (http://aubit4gl.sourceforge.net) which is a 4gl compiler used for writing database centric applications based on the original Informix-4GL language.. I'm just wondering what the appetite is like out there for a mysql compatible version (we've got a very simple mysql connector already, as well as postgres, ODBC). -- Mike Aubury -- 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: retrieve value from an external file?
On Thursday 20 July 2006 12:48 pm, Bing Du wrote: Hello everyone, mysqlupdate research set publications='lng text in a file' where uid=319; You already have the data in a storage system ( a file on the filesystem ). That said, use what you have, store the location to the file, then grab that and use it to open the file and get the contents, using that for whatever purpose you may have. Thanks Chris. What you said makes sense to me. Sure I can do that in a script. But I'm still not clear how it can be done from the 'mysql' command line. I cannot change the table definition to make the 'publications' field to store the location of the file. The 'publications' field stores the actual content of the file. Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: retrieve value from an external file?
At 02:48 PM 7/20/2006, you wrote: Hello everyone, Without scripting, can this be done from mysql command line? The 'publications' field is mediumtext in table 'research'. Since 'publication' is pretty long, I want to save it in a file on the file system. But I don't know how, or if that's even possible, to grab the content from the file and use it in the following update statement? mysqlupdate research set publications='lng text in a file' where uid=319; Any ideas? Thanks in advance, Bing Bing, You could create a temporary table with a single text field and then use Load Data Infile filepath ... to put the text data into the temporary table. Once it is there you can use the temporary table to update your research table. That sounds like a good idea that's worth trying. Thanks much, Mike. Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Max Build Policy
Dear MySQL users, Starting MySQL 5.1 (1), we’re simplifying life when it comes to the number of builds for each platform. We will be building only one binary package for each platform (2): the binary known in MySQL 5.0 as “max”. The assumption is that users prefer one binary with all options enabled, rather than having to choose the proper version at install time (or worse still, rely on others having made the proper choice on their behalf). And with only one version, there is no need to call it “max”. “The max version is dead. Long live the max version!“. Not to speak in riddles, the standard mysqld binary is intended to contain all mysqld-max features. While 5.1.11 e.g. in Linux (x86) package still has three binaries mysql-5.1.11-beta-linux-i686/bin/mysqld mysql-5.1.11-beta-linux-i686/bin/mysqld-debug mysql-5.1.11-beta-linux-i686/bin/mysqld-max the plan is to reduce this to two. Already now in 5.1, the only difference between mysqld and mysqld-max is the BDB table handler. The only real difference going forward is removing support of BDB, which is the first step towards removing it also from the source after 5.1. We think the majority of our users agree that the simplification is beneficial. This way, MySQL will by default contain MySQL Cluster, together with all other functionality we deem stable enough to provide our user base with. In MySQL 5.0 and earlier, the Standard/Max duality has proven problematic from the user’s perspective: - The question “Which package should I install?” is a very common one on the mailing list. - There is a limit to the number of flavours a user can decide between, more flavours is just overwhelming. - The risk of the user picking the wrong binary and then thinking “this is not supported” is high. - As we want customers to try new things, we should see to it that they already have them installed. I imagine the user asking himself or herself questions like - Has my DBA installed the version I want and need? - Are security functions (such as SSL) available in all binaries? - Does the Debug version have the extra debug information on top of Standard or on top of Max? - Is MySQL Max somehow related to MaxDB? (Footnote: No, it is not) I won’t hide the fact that our Build team also saves some time by not providing multiple binaries: We get a shorter turnaround time for builds, for tests and for uploads to mirrors. And it helps our commercial Support team, which now needs to ask users to separate between flavours for versions, and keep track of differences between flavours. However, our simplification is not as much about reducing our own build complexity, as about simplifying life for our user base. - Fewer choices means less time spent choosing, simpler lists on the Web. - The user/developer is less dependent on which particular version he has, when he uses/develops applications based on MySQL. - The developer does not need to convince his DBA or ISP to upgrade into a particular flavour of a release. ISPs and DBAs don’t have to make complex decisions on which flavours to support. - Distributors will hopefully pick up our choice of configuration, to further reduce the number of different MySQL configurations available (and thereby the confusion). Like with any decision, we don’t have all the facts at hand. So now is the time to let us know if we’re on track or not. This can be done on the forums at http://forums.mysql.com/list.php?3, or on the mailing lists at http://lists.mysql.com/mysql or as comments to my blog on http://www.planetmysql.org/kaj/?p=58 Footnotes: (1) The MySQL 5.1 download pages at http://dev.mysql.com/downloads/mysql/5.1.html say As we’re working on making some changes to how we will provide binary distributions of MySQL 5.1, this release currently only provides “Max” binaries for a few selected platforms as well as the source distributions. Compare this to the download pages for MySQL 5.0 Community Edition - Generally Available (GA) Release at http://dev.mysql.com/downloads/mysql/5.0.html where we say The Standard binaries are recommended for most users The Max version includes additional features that have not been exhaustively tested or are not required for general usage. When these features have matured and proven to be stable, they will be incorporated into future releases of the Standard binaries. The Max version also, for most platforms, contains MySQL Cluster storage node, management server and mysqld/ndb enabling programs. The Debug binaries have been compiled with extra debug information, and are not intended for production use, because the included debugging code may reduce performance. (2) A separate server binary with additional debugging support enabled continues to be included. Kaj -- Kaj Arnö [EMAIL PROTECTED] MySQL AB, VP Community Relations -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
Re: Very slow query
At 08:10 AM 7/21/2006, Michael Sutter wrote: Hello everbody, I have a table in my MySQL 5.0.22 Server with this columns. +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | DATE | datetime | NO | MUL | -00-00 00:00:00 | | | IMAGE| longblob | NO | | NULL| | | IMAGETYPE| varchar(8) | NO | MUL | NULL| | | KEYFILE | longblob | NO | | NULL| | | SEARCHENGINE | varchar(64) | NO | MUL | NULL| | | SEARCHTERM | varchar(256) | NO | MUL | NULL| | | SIZE | int(11) | NO | MUL | 0 | | | URL | varchar(512) | NO | | NULL| | | NAME | varchar(256) | NO | MUL | NULL| | +--+--+--+-+-+---+ When I insert several hundert rows with data (all columns, but without the keyfile column) and query the database with this string: SELECT date, imagetype, searchengine, searchterm, size, url, name FROM digiforensic where searchterm='...' it goes very fast (less than one second) and the right index for searchterm is used. This even works when I shutdown and restart the server. The problem is, that the keyfiles are calculated after the datasets are inserted into the database. After calculation they are inserted with an update of the corresponding row. After that the query of the datasets takes a lot of time - more than 2 minutes. I checked that the right index is used with the EXPLAIN expression. Did anybody know why this happens or what my error is? Try moving the LongBlob to a table by itself with a rcd_id column that matches the one in your other table. Then do a join on the two tables when you need to access KeyFile. The problem may be KeyFile is very large and that results in a lot of unecessary disk i/o. Mike Thanks and Regards Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to grant replication slave/client to class c
Michael M. schrieb: I'm attempting to take a brand new mysql server build on gentoo and set up replication. I'm using GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@192.168.1.0/255.255.255.0 IDENTIFIED BY 'secret'; I'm not sure if this is correct syntax. Use the specific IP address instead and execute show grants for repl@ip Do this for both machines in your multimaster setup (replace the ip of course) Now, what I'll eventually be setting up is a master-master replication (basically only for failover using keepalived), so if anyone has any experience with that, I'd be much appreciated. I set up such a system and wrote a little howto on that in the LVS documentation: http://www.austintek.com/LVS/LVS-HOWTO/HOWTO/LVS-HOWTO.failover.html#ha_mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: effects of aquiring exclusive lock on subsequent reads inside same TX?
I'm new to MySQL mailing lists. Is there a more appropriate list for this type of question? thanks -nikita Nikita Tovstoles wrote: Hi, I'm running into lost of DB deadlocks would really appreciate answers to the questions below which will help me diagnose the problem. I am running MySQL 5 InnoDB, SERIALIZABLE mode. Let's say we have: 1.TX1 START //assume autocommit is off 2.TX1 read on TableA 3.TX1 update on TableA 4.TX1 read on TableB 5.TX1 COMMIT Is the following correct? -In step2, TX1 obtains a SHARED lock. Is it applied to all rows in TableA or only those returned by select statement? -In step3, TX1 obtains an EXCLUSIVE lock. Does that lock out all of TableA or only those rows that are being updated? -In step4, does TX1 apply SHARED or EXCLUSIVE lock? is the lock applied to all of TableB or only to rows returned by the select statement? In general what is the effect (w/r/t locking) on selects performed after a write operation within the same TX? thanks a lot! -nikita Nikita Tovstoles http://doppelganger.com [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Doppelganger, Inc. Re: effects of aquiring exclusive lock on subsequent reads inside same TX? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Searching through an alphabetical range
Dear All, I need to write a query that searches last names between the ranges of m through z. Is there a way to do this in the query? Thank You, -- Paul Nowosielski Webmaster office: 303.440.0666 ext 219 cel: 303.827.4257 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[5.0] Left Join Problem
I'm trying to upgrade from MySQL 4.1 to MySQL 5.0. A query that works in MySQL 4.1 does not work in MySQL 5.0 and I'm at a loss as to how to proceed. SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4, tab_e.item5, tab_f.item6 FROM db.tab_a, db.tab_e, db.tab_f LEFT JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND tab_e.id3 = tab_f.id3 LIMIT 1; The above query now generates this error: Unknown column 'tab_a.id in 'on clause'. Placing parentheses around the ON clause, LEFT JOIN db.tab_b ON (tab_b.id = tab_a.id) does not work. However, splitting the original query apart and grouping the Left Joins in one query and the regular joins in another query does NOT generates any errors: SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4 FROM db.tab_a LEFT JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value' LIMIT 1; - this is ok AND SELECT tab_a.item1, tab_e.item5, tab_f.item6 FROM db.tab_a, db.tab_e, db.tab_f WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND tab_e.id3 = tab_f.id3 LIMIT 1; - this is ok Why doesn't the original query work in MySQL 5.0? What do I need to do to make it work? TIA Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [5.0] Left Join Problem
At 12:47 PM -0500 7/21/06, Stephen P. Fracek, Jr. wrote: I'm trying to upgrade from MySQL 4.1 to MySQL 5.0. A query that works in MySQL 4.1 does not work in MySQL 5.0 and I'm at a loss as to how to proceed. SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4, tab_e.item5, tab_f.item6 FROM db.tab_a, db.tab_e, db.tab_f LEFT JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND tab_e.id3 = tab_f.id3 LIMIT 1; The above query now generates this error: Unknown column 'tab_a.id in 'on clause'. Placing parentheses around the ON clause, LEFT JOIN db.tab_b ON (tab_b.id = tab_a.id) does not work. However, splitting the original query apart and grouping the Left Joins in one query and the regular joins in another query does NOT generates any errors: SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4 FROM db.tab_a LEFT JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value' LIMIT 1; - this is ok AND SELECT tab_a.item1, tab_e.item5, tab_f.item6 FROM db.tab_a, db.tab_e, db.tab_f WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND tab_e.id3 = tab_f.id3 LIMIT 1; - this is ok Why doesn't the original query work in MySQL 5.0? What do I need to do to make it work? The information you need is here: http://dev.mysql.com/doc/refman/5.0/en/join.html See particularly Join Processing Changes in MySQL 5.0.12 -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [5.0] Left Join Problem
Stephen P. Fracek, Jr. wrote: I'm trying to upgrade from MySQL 4.1 to MySQL 5.0. A query that works in MySQL 4.1 does not work in MySQL 5.0 and I'm at a loss as to how to proceed. SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4, tab_e.item5, tab_f.item6 FROM db.tab_a, db.tab_e, db.tab_f LEFT JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND tab_e.id3 = tab_f.id3 LIMIT 1; The above query now generates this error: Unknown column 'tab_a.id in 'on clause'. Placing parentheses around the ON clause, LEFT JOIN db.tab_b ON (tab_b.id = tab_a.id) does not work. However, splitting the original query apart and grouping the Left Joins in one query and the regular joins in another query does NOT generates any errors: SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4 FROM db.tab_a LEFT JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value' LIMIT 1; - this is ok AND SELECT tab_a.item1, tab_e.item5, tab_f.item6 FROM db.tab_a, db.tab_e, db.tab_f WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND tab_e.id3 = tab_f.id3 LIMIT 1; - this is ok Why doesn't the original query work in MySQL 5.0? What do I need to do to make it work? TIA Stephen P. Fracek, Jr. [EMAIL PROTECTED] It is a join precedence issue. Use INNER Join instead of a comma. SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4, tab_e.item5, tab_f.item6 FROM db.tab_a INNER JOIN db.tab_e ON tab_a.id2 = tab_e.id2 INNER JOIN db.tab_f ON tab_e.id3 = tab_f.id3 LEFT JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value' LIMIT 1; -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching through an alphabetical range
I have the solution: SELECT DISTINCT email_address FROM mailing_list WHERE last_name = 'm' AND last_name = 'z' Thank you, -- Paul Nowosielski Webmaster On Friday 21 July 2006 11:03, Paul Nowosielski wrote: Dear All, I need to write a query that searches last names between the ranges of m through z. Is there a way to do this in the query? Thank You, -- Paul Nowosielski Webmaster office: 303.440.0666 ext 219 cel: 303.827.4257 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Searching through an alphabetical range
Not quite... mysql select 'zebra' = 'm' AND 'zebra' = 'z'; +---+ | 'zebra' = 'm' AND 'zebra' = 'z' | +---+ | 0 | +---+ 1 row in set (0.00 sec) mysql select 'seal' = 'm' AND 'seal' = 'z'; +-+ | 'seal' = 'm' AND 'seal' = 'z' | +-+ | 1 | +-+ 1 row in set (0.00 sec) mysql 'zz' will probably do the trick, though. Tim -Original Message- From: Paul Nowosielski [mailto:[EMAIL PROTECTED] Sent: Friday, July 21, 2006 2:10 PM To: mysql@lists.mysql.com Subject: Re: Searching through an alphabetical range I have the solution: SELECT DISTINCT email_address FROM mailing_list WHERE last_name = 'm' AND last_name = 'z' Thank you, -- Paul Nowosielski Webmaster On Friday 21 July 2006 11:03, Paul Nowosielski wrote: Dear All, I need to write a query that searches last names between the ranges of m through z. Is there a way to do this in the query? Thank You, -- Paul Nowosielski Webmaster office: 303.440.0666 ext 219 cel: 303.827.4257 -- 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: [5.0] Left Join Problem
On 2006-07-21 1:01 PM, Gerald L. Clark [EMAIL PROTECTED] wrote: It is a join precedence issue. Use INNER Join instead of a comma. Thanks Gerald. Paul DuBois' polite suggestion to read the manual helped. Upon re-reading the section about the change in precedence with the comma operator and the join, I realized there was a simple fix and that I had misinterpreted the section on the first read. The revised query works. Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching through an alphabetical range
Its working fine for me: mysql SELECT DISTINCT email_address FROM mailing_list WHERE marketing_list LIKE 'Y' AND last_name = 'm' AND last_name = 'z'; 26371 rows in set (1.00 sec) Thank You, -- Paul Nowosielski Webmaster On Friday 21 July 2006 12:46, Tim Lucia wrote: Not quite... mysql select 'zebra' = 'm' AND 'zebra' = 'z'; +---+ | 'zebra' = 'm' AND 'zebra' = 'z' | +---+ | 0 | +---+ 1 row in set (0.00 sec) mysql select 'seal' = 'm' AND 'seal' = 'z'; +-+ | 'seal' = 'm' AND 'seal' = 'z' | +-+ | 1 | +-+ 1 row in set (0.00 sec) mysql 'zz' will probably do the trick, though. Tim -Original Message- From: Paul Nowosielski [mailto:[EMAIL PROTECTED] Sent: Friday, July 21, 2006 2:10 PM To: mysql@lists.mysql.com Subject: Re: Searching through an alphabetical range I have the solution: SELECT DISTINCT email_address FROM mailing_list WHERE last_name = 'm' AND last_name = 'z' Thank you, -- Paul Nowosielski Webmaster On Friday 21 July 2006 11:03, Paul Nowosielski wrote: Dear All, I need to write a query that searches last names between the ranges of m through z. Is there a way to do this in the query? Thank You, -- Paul Nowosielski Webmaster office: 303.440.0666 ext 219 cel: 303.827.4257 -- 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]
newbie needs help
Hi All,
I'm going throught some tutorial about uploading and displaying
images files. But the display script isn't working. Here's what I have:
Table setup is:
CREATE TABLE `image` (
`ImageId` int(10) NOT NULL auto_increment,
`Image` longblob,
`FileType` varchar(32) default NULL,
PRIMARY KEY (`ImageId`)
)
Add image script (addimage.php) is:
?php // add image script
if ($_POST['Submit']) {
if ($_POST['MAX_FILE_SIZE'] = $_FILES['file']['size']) {
//print_r($_FILES);
include ('includes/mysql_connect.php'); // connect to db
$photo = addslashes(fread(fopen($_FILES['file']['tmp_name'], r),
$_FILES['file']['size']));
$query = sprintf(INSERT INTO image(Image, FileType) VALUES ('%s',
'%s'), $photo, $_FILES['file']['type']);
if (mysql_query($query)) {
$messages[] = Your files is successfully store in database;
} else {
$messages[]= mysql_error();
}
} else {
$messages[] = The file is bigger than the allowed size (96k) please
reduce your file size;
}
}
?
html
head
titleAdd Image/title
/head
body
?
if (isset($messages)) {
foreach ($messages as $message) {
print $message .br;
}
}
?
form action= method=post enctype=multipart/form-data name=form1
input type=file name=file
input type=hidden name=MAX_FILE_SIZE value=96000
input type=submit name=Submit value=Submit
/form
/body
/html
And imageloader.php is:
?php // imageloader.php
include ('includes/mysql_connect.php');
$result = mysql_query(SELECT ImageId from image);
while ($row = mysql_fetch_array($result)) {
$ids[]=$row['ImageId'];
}
?
html
head
titleImage Loader/title
/head
body
select image:br
table width=80% border=0 cellspacing=0 cellpadding=0
tr
td width=10%id/td
td width=90%Image/td
/tr
tr
td valign=top
table width=100% border=0
cellspacing=0 cellpadding=0
? foreach ($ids as $id) { ?
tr
tda href=?id=?= $id;
??= $id; ?/a/td
/tr
? } ?
/table
/td
td? if (isset($_GET['id'])) { ?img
src=image.php?id=?= $_GET['id']; ?? } ?/td
/tr
/table
/body
/html
And finally image.php is
?php // image.php
include ('includes/mysql_connect.php'); // connect to db
$result = mysql_query(sprintf(SELECT * from image WHERE ImageId =
%d, $_GET['id']));
$row = mysql_fetch_array($result);
header(sprintf(Content-type: %s, $row['FileType']));
echo Here's the picture: :, $row['Image'];
?
When I click on the individual image id, the actual image won't
show. Can someone tell me what am I missing here?
Much thanks.
Kay
Re: newbie needs help
I'm going throught some tutorial about uploading and displaying images files. But the display script isn't working. Here's what I have: I think you may want to bring this to a php based list, not a mysql one. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie needs help
At 04:22 PM 7/21/2006 Friday, Scott Haneda wrote: I'm going throught some tutorial about uploading and displaying images files. But the display script isn't working. Here's what I have: I think you may want to bring this to a php based list, not a mysql one. Yes, it's cross-posted. I just thought to try it here. Kay -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Generating SQL or flat file code for Object persistence
Do anyone know of any tools to automatically generate SQL tables,
and C++ or Objective C code for object persistence.
What I want is code that takes for example code like this:
class MyClass {
Persistent int key;
/* rest omitted */
};
And generates an SQL table declaration from it.
(In C-based languages with a preprocessor, it's easy to tag variable
declarations
with metadata by using identifiers that is macro-expanded to nothing)
-
This sig is dedicated to the advancement of Nuclear Power
Tommy Nordgren
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie needs help
While this is offtopic, just a side note: You probably want to store your images on disk with the filename in the database rather than the actual image binary data in the db. Filesystems are very good at storing and retreiving chunks of binary. Databases do it because... well... I'm not really sure why. Mark. On 7/21/06, Scott Haneda [EMAIL PROTECTED] wrote: I'm going throught some tutorial about uploading and displaying images files. But the display script isn't working. Here's what I have: I think you may want to bring this to a php based list, not a mysql one. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Mark Maunder [EMAIL PROTECTED] http://www.markmaunder.com/ +1-206-6978723 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
IP address and CIDR / netmask
Hi, My table looks like this: ipv4addr (INT, PK) status (INT) status: 0 = free 1 = used I'm trying to get free IP addresses by only giving CIDR (0 to 32) or netmask (0.0.0.0 to 255.255.255.255). The output should be one of them: 1. start IP 2. start IP CIDR 3. start IP Netmask 4. start IP end IP Let's say I have ipv4addr from 3232235520 to 3232235775 and 3232235525 is used. I try SELECT with CIDR 25. I get 3232235648 as answer, because 3232235520-3232235647 is used (one used address). Is this kind of SELECT query even possible? Currently I SELECT all free addresses and run them in separate function in PHP. But it would be nice to run the whole thing in DB for effeciency. -- Pekka Järvinen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
transaction
I've getting something wrong in transaction in Mysql 5.0.22 on windows that i don't know way it's happen. I've got a script in php which starts a transaction and when if fails and rollbacks, the autoincrement do not back. e.g. If in transaction some field auto increment gets the value 4 and this transaction fails, in the next transaction it gets the value 5. Is it right? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transaction
In the last episode (Jul 22), Joπo CΓndido de Souza Neto said: I've getting something wrong in transaction in Mysql 5.0.22 on windows that i don't know way it's happen. I've got a script in php which starts a transaction and when if fails and rollbacks, the autoincrement do not back. e.g. If in transaction some field auto increment gets the value 4 and this transaction fails, in the next transaction it gets the value 5. The autoincrement value is metadata outside of any table values and is not subject to transaction rollback. Consider the case where you insert one row in each of two separate connections, getting, say, values 4 and 5. Then you rollback the first one. The autoincrement counter is now at 6 and you're never going to get another 4. I guess mysql could decrement the autoincrement counter if only one new row has been reserved but not committed, but that's a lot of work for little gain. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
