Re: map polygon data for popular us cities
http://www.geonames.org/ http://download.geonames.org/export/dump/?C=S;O=D On 02/02/2011 11:30 AM, viraj wrote: dear list, where can i find a list of map polygons for united states cities? any open database? or tool to obtain correct coordinates? i googled but couldn't find anything useful.. may be the terms i use are not the correct keywords :( any help or advice would be really appreciated. ~viraj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Finding Data in One of Two Tables
On 2/2/2011 04:23, Hal Vaughan wrote: I'm using this query in a Perl program: SELECT Distinct x.Search FROM $source.Searches AS x LEFT JOIN searching.Status AS s ON x.Search=s.Search AND s.Source='$source' WHERE x.RedoTime'$now' AND s.Search IS NULL This program runs other programs that do internet searches. I have different sources (stored in $source, of course), and source has it's own DB with a table, Searches. Each row of searches describes a different search that can be done and each search has a name, which is stored in the Searches column (within the Searches table, so, yes, I use that name for a table and a column). When a search is being executed, an entry is placed in searching.Status, with one row in that table showing the status of the search. So if I have a source named alpha and searches named one and two and the system is executing the search one, not only is there a row in alpha.Searches describing one in depth, but there is a row in searching.Status describing the progress with one. When each search is done, the RedoTime is set so it's easy to see when it needs to be executed again. What I want to do is get a list of searches in the Searches table (within the source's DB) that are NOT listed in Status and where the RedoTime is before $now (the current time). From what I've read, the query above should do it, but I have this nagging feeling I've done something wrong. Will that query pick up all rows in $source.Searches that have a RedoTime before $now that are NOT also listed in searching.Status? Thanks for any help on this! Maybe today isn't my day. I can't distinguish between what you are looking for and what you do not want to find. WANTED: a list of searches in the Searches table (within the source's DB) that are NOT listed in Status and where the RedoTime is before $now (the current time). NOT WANTED: rows in $source.Searches that have a RedoTime before $now that are NOT also listed in searching.Status I can't seem to spot the difference. Maybe if you phrased it differently or provided two or three sample rows for each table I could understand your distinction? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN The Customer Support Center Will Retire February 11, 2011 Find out what you need to know about the migration to My Oracle Support: http://www.oracle.com/us/support/mos-mysql-297243.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Help with ORDER BY
I currently have a query that organizes search results for volunteers that should be called for projects based on how close they live to a project the and there past attendance. Currently doing SELECT name, city, state, phone, prods_done, cancels, miles FROM volunteer_search WHERE project_id = 5653 ORDER BY miles ASC, cancels/(prods_done/cancels) ASC, prods_done DESC; The results look something like this: Jim Barnes Chicago, IL 773-555- 2 1 11.5 Kelley Smith Cicero, IL 708-444-2121 3 0 21.6 Kim Ayers Plainfield, IL 630-888-9898 22 1 25.1 I am trying to find a way to give more weight to reliability (projects done and cancels). For example the fact that Kim Ayers has done 22 projects makes her more desirable than either Jim Barnes and Kelley Smith even though she has 1 cancel is farther from the project than both the other candidates. The desired order would be: Kim Ayers Plainfield, IL 630-888-9898 22 1 25.1 Kelley Smith Cicero, IL 708-444-2121 3 0 21.6 Jim Barnes Chicago, IL 773-555- 2 1 11.5 Can the query could be modified to take into account such considerations? I realize that I have not really quantified how much reliability and the number of projects done trumps distance from the project, but in the beginning I am willing to set that somewhat arbitrary and adjust it later. I realize that this may be beyond the scope of a MySQL query. If so I am grateful for any ideas on where to go for info/help. Thanks, Richard
RE: Help with ORDER BY
SELECT name, city, state, phone, prods_done, cancels, miles FROM (SELECT name, city, state, phone, prods_done, cancels, miles, ((prod_done - cancels) * 100 / prod_done) reliability FROM volunteer_search WHERE project_id = 5653) A ORDER BY reliability DESC, miles ASC Give it a try !!! Rolando A. Edwards MySQL DBA (SCMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: Richard Reina [mailto:gatorre...@gmail.com] Sent: Monday, February 07, 2011 1:08 PM To: mysql@lists.mysql.com Subject: Help with ORDER BY I currently have a query that organizes search results for volunteers that should be called for projects based on how close they live to a project the and there past attendance. Currently doing SELECT name, city, state, phone, prods_done, cancels, miles FROM volunteer_search WHERE project_id = 5653 ORDER BY miles ASC, cancels/(prods_done/cancels) ASC, prods_done DESC; The results look something like this: Jim Barnes Chicago, IL 773-555- 2 1 11.5 Kelley Smith Cicero, IL 708-444-2121 3 0 21.6 Kim Ayers Plainfield, IL 630-888-9898 22 1 25.1 I am trying to find a way to give more weight to reliability (projects done and cancels). For example the fact that Kim Ayers has done 22 projects makes her more desirable than either Jim Barnes and Kelley Smith even though she has 1 cancel is farther from the project than both the other candidates. The desired order would be: Kim Ayers Plainfield, IL 630-888-9898 22 1 25.1 Kelley Smith Cicero, IL 708-444-2121 3 0 21.6 Jim Barnes Chicago, IL 773-555- 2 1 11.5 Can the query could be modified to take into account such considerations? I realize that I have not really quantified how much reliability and the number of projects done trumps distance from the project, but in the beginning I am willing to set that somewhat arbitrary and adjust it later. I realize that this may be beyond the scope of a MySQL query. If so I am grateful for any ideas on where to go for info/help. Thanks, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
PgEast CFP closes in (3) Days
Hey, MySQLers if you are looking to present at PgEast, now would be a great time to get your talk in. Yes it is a PostgreSQL conference, but we like to learn from each other, do we not? I would be all over seeing talk titled: What I wish PostgreSQL could do, from a MySQL users perspective! https://www.postgresqlconference.org/talk_types JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL Server 5.0.92 has been released.
Dear MySQL users, MySQL Server 5.0.92, a new version of the popular Open Source Database Management System, has been released. Please note that the active maintenance of 5.0 has ended, and these builds are only provided because of the fixes to security bugs as described below. The release is now available in source and binary form for a number of platforms from our archive download page at http://dev.mysql.com/downloads/ Mirror service for MySQL Server 5.0 has ended. Also, support for some platforms with very low demand has ended. Please bear in mind that MySQL 5.0 now receives extended support only, and that all active development is happening on MySQL 5.1, 5.5, and beyond. You will find the MySQL Lifecycle policy here: http://www.mysql.de/about/legal/lifecycle/ For your own best interest, we strongly recommend all current users of MySQL 5.0 to upgrade to MySQL 5.1. We welcome and appreciate your feedback, bug reports, bug fixes, patches etc: http://forge.mysql.com/wiki/Contributing This section documents all changes and bugfixes that have been applied since the last MySQL Server release (5.0.91). http://dev.mysql.com/doc/refman/5.0/en/news-5-0-92.html If you would like to receive more fine-grained and personalized update alerts about fixes that are relevant to the version and features you use, please consider subscribing to MySQL Enterprise (a commercial MySQL offering). For more details please see http://www.mysql.com/products/enterprise/advisors.html Changes in MySQL 5.0.92 (7 February, 2011) Functionality added or changed: * The time zone tables available at http://dev.mysql.com/downloads/timezones.html have been updated. These tables can be used on systems such as Windows or HP-UX that do not include zoneinfo files. (Bug#40230:http://bugs.mysql.com/bug.php?id=40230) Bugs fixed: * Security Fix: During evaluation of arguments to extreme-value functions (such as LEAST() and GREATEST()), type errors did not propagate properly, causing the server to crash. (Bug#55826:http://bugs.mysql.com/bug.php?id=55826, CVE-2010-3833 (http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2010-3833) * Security Fix: The server could crash after materializing a derived table that required a temporary table for grouping. (Bug#55568:http://bugs.mysql.com/bug.php?id=55568, CVE-2010-3834 (http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2010-3834) * Security Fix: A user-variable assignment expression that is evaluated in a logical expression context can be precalculated in a temporary table for GROUP BY. However, when the expression value is used after creation of the temporary table, it was re-evaluated, not read from the table and a server crash resulted. (Bug#55564:http://bugs.mysql.com/bug.php?id=55564, CVE-2010-3835 (http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2010-3835) * Security Fix: Joins involving a table with a unique SET column could cause a server crash. (Bug#54575:http://bugs.mysql.com/bug.php?id=54575, CVE-2010-3677 (http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2010-3677) * Security Fix: Pre-evaluation of LIKE predicates during view preparation could cause a server crash. (Bug#54568:http://bugs.mysql.com/bug.php?id=54568, CVE-2010-3836 (http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2010-3836) * Security Fix: GROUP_CONCAT() and WITH ROLLUP together could cause a server crash. (Bug#54476:http://bugs.mysql.com/bug.php?id=54476, CVE-2010-3837 (http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2010-3837) * Security Fix: Queries could cause a server crash if the GREATEST() or LEAST() function had a mixed list of numeric and LONGBLOB arguments, and the result of such a function was processed using an intermediate temporary table. (Bug#54461:http://bugs.mysql.com/bug.php?id=54461, CVE-2010-3838 (http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2010-3838) * Security Fix: Using EXPLAIN with queries of the form SELECT ... UNION ... ORDER BY (SELECT ... WHERE ...) could cause a server crash. (Bug#52711:http://bugs.mysql.com/bug.php?id=52711, CVE-2010-3682 (http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2010-3682) * InnoDB Storage Engine: Creating or dropping a table with 1023 transactions active caused an assertion failure. (Bug#49238:http://bugs.mysql.com/bug.php?id=49238) * The make_binary_distribution target to make could fail on some platforms because the lines generated were too long for the shell. (Bug#54590:http://bugs.mysql.com/bug.php?id=54590) * A client could supply data in chunks to a prepared statement parameter other than of type TEXT or BLOB using the mysql_stmt_send_long_data() C API function (or COM_STMT_SEND_LONG_DATA command). This led to a crash
MySQL Server 5.1.55 has been released
Dear MySQL users, MySQL Server 5.1.55, a new version of the popular Open Source Database Management System, has been released. MySQL 5.1.55 is recommended for use on production systems. For an overview of what's new in MySQL 5.1, please see http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html For information on installing MySQL 5.1.55 on new servers or upgrading to MySQL 5.1.55 from previous MySQL releases, please see http://dev.mysql.com/doc/refman/5.1/en/installing.html MySQL Server is available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ Not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches, etc.: http://forge.mysql.com/wiki/Contributing For information on open issues in MySQL 5.1, please see the errata list at http://dev.mysql.com/doc/refman/5.1/en/open-bugs.html The following section lists the changes in the MySQL source code since the previous released version of MySQL 5.1. It may also be viewed online at http://dev.mysql.com/doc/refman/5.1/en/news-5-1-55.html Changes in MySQL 5.1.55 (7 February, 2011) Functionality added or changed: * The time zone tables available at http://dev.mysql.com/downloads/timezones.html have been updated. These tables can be used on systems such as Windows or HP-UX that do not include zoneinfo files. (Bug#40230: http://bugs.mysql.com/bug.php?id=40230) Bugs fixed: * Incompatible Change: When auto_increment_increment is greater than one, values generated by a bulk insert that reaches the maximum column value could wrap around rather producing an overflow error. As a consequence of the fix, it is no longer possible for an auto-generated value to be equal to the maximum BIGINT UNSIGNED value. It is still possible to store that value manually, if the column can accept it. (Bug#39828: http://bugs.mysql.com/bug.php?id=39828) * Important Change: Partitioning: Date and time functions used as partitioning functions now have the types of their operands checked; use of a value of the wrong type is now disallowed in such cases. In addition, EXTRACT(WEEK FROM col), where col is a DATE or DATETIME column, is now disallowed altogether because its return value depends on the value of the default_week_format system variable. (Bug#54483: http://bugs.mysql.com/bug.php?id=54483) See also Bug#57071: http://bugs.mysql.com/bug.php?id=57071. * InnoDB Storage Engine: A compilation problem affected the InnoDB source code on NetBSD/sparc64. (Bug#59327: http://bugs.mysql.com/bug.php?id=59327) See also Bug#53916: http://bugs.mysql.com/bug.php?id=53916. * InnoDB Storage Engine: In InnoDB status output, the value for I/O sum[] could be incorrect, displayed as a very large number. (Bug#57600: http://bugs.mysql.com/bug.php?id=57600) * InnoDB Storage Engine: It was not possible to query the information_schema.innodb_trx table while other connections were running queries involving BLOB types. (Bug#55397: http://bugs.mysql.com/bug.php?id=55397) * InnoDB Storage Engine: The OPTIMIZE TABLE statement would reset the auto-increment counter for an InnoDB table. Now the auto-increment value is preserved across this operation. (Bug#18274: http://bugs.mysql.com/bug.php?id=18274) * Partitioning: Failed ALTER TABLE ... PARTITION statements could cause memory leaks. (Bug#56380: http://bugs.mysql.com/bug.php?id=56380) See also Bug#46949: http://bugs.mysql.com/bug.php?id=46949, Bug#56996: http://bugs.mysql.com/bug.php?id=56996. * Replication: When closing a session that used temporary tables, binary logging could sometimes fail with a spurious Failed to write the DROP statement for temporary tables to binary log. (Bug#57288: http://bugs.mysql.com/bug.php?id=57288) * Replication: By default, a value is generated for an AUTO_INCREMENT column by inserting either NULL or 0 into the column. Setting the NO_AUTO_VALUE_ON_ZERO server SQL mode suppresses this behavior for 0, so that it occurs only when NULL is inserted into the column. This behavior is also followed on a replication slave (by the slave SQL thread) when applying events that have been logged on the master using the statement-based format. However, when applying events that had been logged using the row-based format, NO_AUTO_VALUE_ON_ZERO was ignored, which could lead to an assertion. To fix this issue, the value of an AUTO_INCREMENT column is no longer generated when applying an event
MySQL Community Server 5.5.9 has been released
Dear MySQL users, MySQL 5.5.9 is a new version of the 5.5 production version of the world's most popular open source database. MySQL 5.5.9 is recommended for use on production systems. MySQL 5.5 includes several high-impact changes to address scalability and performance issues in MySQL Server. These changes exploit advances in hardware and CPU design and enable better utilization of existing hardware. MySQL 5.5 also provides a number of other new enhancements, including: - InnoDB I/O Subsystem Changes - Enhanced Solaris Support - Diagnostic and Monitoring Capabilities For a more complete look at what's new in MySQL 5.5, please see http://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html If you are running a MySQL production level system, we would like to direct your attention to the product description of MySQL Enterprise at: http://mysql.com/products/enterprise/ For information on installing MySQL 5.5.9 on new servers, please see the MySQL installation documentation at http://dev.mysql.com/doc/refman/5.5/en/installing.html For upgrading from previous MySQL releases, please see the important upgrade considerations at: http://dev.mysql.com/doc/refman/5.5/en/upgrading.html MySQL Server 5.5 is available in source and binary form for a number of platforms from our download pages at: http://dev.mysql.com/downloads/mysql/ Not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches, etc.: http://forge.mysql.com/wiki/Contributing The following section lists the changes in the MySQL source code since the previous released version of MySQL 5.5. It may also be viewed online at: http://dev.mysql.com/doc/refman/5.5/en/news-5-5-9.html D.1.2. Changes in MySQL 5.5.9 Configuration Notes: * MySQL releases are now built on all platforms using CMake rather than the GNU autotools, so autotools support has been removed. For instructions on building MySQL with CMake, see Section 2.11, Installing MySQL from Source. Third-party tools that need to extract the MySQL version number formerly found in configure.in can use the VERSION file. See Section 2.11.6, MySQL Configuration and Third-Party Tools. Functionality added or changed: * The mysqladmin and mysqldump clients now have --default-auth and --plugin-dir options for specifying which authentication plugin and plugin directory to use. (Bug#58139: http://bugs.mysql.com/bug.php?id=58139) * sql_priv.h now includes an OPTION_ALLOW_BATCH flag for the transaction_allow_batching (http://dev.mysql.com/doc/refman/5.1/en/server-system-variable s.html#sysvar_transaction_allow_batching) feature of MySQL Cluster. (Bug#57604: http://bugs.mysql.com/bug.php?id=57604) * Boolean system variables can be enabled at run time by setting them to the value ON or OFF, but previously this did not work at server startup. Now at startup such variables can be enabled by setting them to ON or TRUE. Any other nonnumeric variable is interpreted as OFF. (Bug#46393: http://bugs.mysql.com/bug.php?id=46393 improves on this such that ON, TRUE, OFF, and FALSE are recognized, and other values are invalid.) (Bug#51631: http://bugs.mysql.com/bug.php?id=51631) Bugs fixed: * Incompatible Change: When auto_increment_increment is greater than one, values generated by a bulk insert that reaches the maximum column value could wrap around rather producing an overflow error. As a consequence of the fix, it is no longer possible for an auto-generated value to be equal to the maximum BIGINT UNSIGNED value. It is still possible to store that value manually, if the column can accept it. (Bug#39828: http://bugs.mysql.com/bug.php?id=39828) * Important Change: Partitioning: Date and time functions used as partitioning functions now have the types of their operands checked; use of a value of the wrong type is now disallowed in such cases. In addition, EXTRACT(WEEK FROM col), where col is a DATE or DATETIME column, is now disallowed altogether because its return value depends on the value of the default_week_format system variable. (Bug#54483: http://bugs.mysql.com/bug.php?id=54483) See also Bug#57071: http://bugs.mysql.com/bug.php?id=57071. * Partitioning: InnoDB Storage Engine: The partitioning handler did not pass locking information to a table's storage engine handler. This caused high contention and thus slower performance when working with partitioned InnoDB tables. (Bug#59013: http://bugs.mysql.com/bug.php?id=59013) * InnoDB Storage Engine: When multiple InnoDB
Re: Replication Error on Slave
All, Thanks for your replies and as per the advise I switched to row-based replication but replication ended with below error. *Last_Error: Error 'Table 'b.sdefrent' doesn't exist' on query. Default database: 'b. Query: 'drop table sdefrent'* above error is due to non existence of database and table. However bit confused on the error I got, if its row based replication why its taking as statement. Please help me here. Thank you -Naga On Thu, Jan 27, 2011 at 3:23 PM, Johan De Meersman vegiv...@tuxera.bewrote: On Thu, Jan 27, 2011 at 10:40 AM, Nagaraj S nagaraj@gmail.comwrote: **On Slave Server I replicate database *A alone* and my replication not working due to data fetching happen on B database. Well, yes. Statement-based replication does what it says on the box: it executes the exact same statement on the slave. If database B is not there, then insert into A select from B will not work. You may switch to row-based replication (which of course has it's own caveats, see the online manuals); or you can simply choose to also replicate database B. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
