Unable to connect to foreign data source
I had a problem why trying Federated Engine. Creating tables generate no problems but trying inserting raise an error as Error 1429 (HY000): Unable to connect to a foreign data source: Can't connect to MySQL server on '192.168.0.11' (111). My OS is newly install with no firewall restriction on the system. And I'm using 'root' user with all privileges. I can't seem to find a solution at any sites. Anyone with suggestion for this problems? The problem could be recreated as the code below. drop table if exists test001 ; create table test001 ( xx bigint(20) unsigned ) ENGINE=InnoDB DEFAULT CHARSET=utf8; drop table if exists test001FE ; create table test001FE ( xx bigint(20) unsigned ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION = 'mysql://root:root@192.168.0.11:3306/Prelude_copy/test001'; insert into test001FE(xx) values (12);
MySQL Workbench 6.1.4 GA has been released
Dear MySQL users, The MySQL developer tools team announces 6.1.4 as our GA release for MySQL Workbench 6.1. MySQL Workbench 6.1 is an upcoming major update for the official MySQL graphical development tool. Introducing over 30 new features, this version has many significant enhancements focusing on real-time performance assessment and analysis from the SQL statement level to server internals and file IO. You can see this from additions to the SQL Editor as well as new dashboard visualization and reporting that take advantage of MySQL Server 5.6 and 5.7 Performance Schema, and enhancements to the MySQL Explain Plans. Additionally Workbench 6.1 is leveraging work from various teammates in MySQL Engineering by introducing a schema called SYS that provides simplified views on Performance Schema, Information Schema, and other areas. Special thanks to the server optimizer team, server runtime team, and Mark Leith. For Oracle DBAs MySQL SYS is similar to the V$ catalog views, and MSSQL folks its like DMVs (Dynamic Management Views). MySQL Workbench 6.1 includes: * Improved drag and drop support in the Home screen and SQL Editor * Visual Explain 2. The Visual Explain feature was revamped and is now easier to read and interpret. You can also get the traditional tabular explain output from within the same interface. * Performance Dashboard. A graphical representation of some key statistics from the server status, gives you a bird's eye view of the status of key server subsystems. * For advanced users, Performance Schema Instrumentation. A GUI for configuring the Performance Schema in detail, for advanced users. * Performance Schema based reporting. Gives insight into the operation of the server through many high-level reports. * New query result view. Get more information about queries you execute, such as information about the fields in your result set and key performance statistics from your query (timing, index usage, number of rows scanned, joins etc). * Form Editor for resultsets. In addition to the result grid, you can now edit records row by row in a form style editor. * Table Inspector. Similar to the Schema Inspector, view detailed information from tables. A streamlined interface for creating indexes is also included. * Support for the Windows accessibility API and Windows high contrast color schemes. A new high contrast theme has been introduced. * And more. More than 60 enhancement requests and bugs reported by users have also been addressed, providing performance, usability and stability improvements across the board. For the full list of bugs fixed in this revision, visit http://dev.mysql.com/doc/relnotes/workbench/en/changes-6-1.html For discussion, join the MySQL Workbench Forums: http://forums.mysql.com/index.php?151 Download MySQL Workbench 6.1.4 GA now, for Windows, Mac OS X 10.6+, Oracle Linux 6, Fedora 19, Fedora 20, Ubuntu 12.04 and Ubuntu 13.10 or sources, from: http://dev.mysql.com/downloads/tools/workbench/ In Windows, you can also use the integrated MySQL Installer to update MySQL Workbench and other MySQL products. Quick links: - http://mysqlworkbench.org/ - Download: http://dev.mysql.com/downloads/tools/workbench/ - Bugs: http://bugs.mysql.com - Forums: http://forums.mysql.com/index.php?151 Enjoy! Changes in MySQL Workbench 6.1.4 (2014-03-31) Functionality Added or Changed * The Beautify query feature was changed. It now only affects the current statement, unless there is an explicit selection. Before, it would reformat all statements in the window by default. (Bug #18237189, Bug #71544) * The default schema is now expanded automatically when a connection is opened, or when the default schema changes. (Bug #18237247, Bug #70522) * A specific connection string can now be passed to the --query command-line option at startup, in the form of --query user@host:port. If this connection string does not already exist, then it will be created. (Bug #17353691, Bug #70134) * MySQL Workbench now checks if the lower_case_table_name variable is correctly set on Microsoft Windows and Mac OS X systems. If not, then MySQL Workbench will display a dialog after connect, and emit a warning message when performing synchronization or reverse engineering actions. (Bug #14802853) * New Report a Bug functionality now opens a browser with the MySQL bug page pre-populated with MySQL Workbench data. This also allows MySQL Workbench to be fully manipulated while submitting the bug report. (Bug #14079590, Bug #65258) Bugs Fixed * The local private key file for an SSH connection when executing Remote Connection in MySQL Workbench through SSH could not be saved to a folder with accented characters. (Bug #18328846, Bug #71800) * FOUND_ROWS() (after a SQL_CALC_FOUND_ROWS statement) returned 1 (true) instead of the
MySQL Connector/Python 1.2.1 RC has been released
Dear MySQL users, MySQL Connector/Python v1.2.1 is the new version of the 1.2 release series of the pure Python database driver for MySQL. It is intended to introduce users to the new features. MySQL Connector/Python version 1.2.1 is compatible with MySQL Server versions 5.5 and greater, but should work with earlier versions (greater than 4.1). Python 2.6 and 2.7, as well as Python 3.1 and greater are supported. MySQL Connector/Python 1.2.1 is available for download from http://dev.mysql.com/downloads/connector/python/#downloads The ChangeLog file included in the distribution contains a brief summary of changes in MySQL Connector/Python 1.2.1. For a more complete list of changes, see below or online at: http://dev.mysql.com/doc/relnotes/connector-python/en/ Changes in MySQL Connector/Python 1.2.1 (2014-03-31) -- Functionality Added or Changed * It was not possible to initiate an SSL session without explicitly giving a key and certificate. Now it is possible to connect to a MySQL server using only the ssl_ca connection argument pointing to a file of CA certificates. This means the ssl_key and ssl_cert connection arguments are optional. However, when either is given, both must be given or an AttributeError is raised. (Bug #69418, Bug #17054848) * Connector/Python now supports authentication plugins found in MySQL 5.6. This includes mysql_clear_password and sha256_password, both of which require an SSL connection. The sha256_password plugin does not work over a non-SSL connection because Connector/Python does not support RSA encryption. The connect() method now supports an auth_plugin parameter that can be used to force use of a particular plugin. For example, if the server is configured to use sha256_password by default and you want to connect to an account that authenticates using mysql_native_password, either connect using SSL or specify auth_plugin='mysql_native_password'. (Bug #68054, Bug #16217765) * Connector/Python now permits the type for stored procedure parameters to be specified. To do this, specify a parameter as a two-item tuple consisting of the parameter value and type. For more information, see Method MySQLCursor.callproc(procname, args=()) (http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-callproc.html). (Bug #71124, Bug #17965619) * A new MySQLConnection class reset_connection() method enables applications to send a COM_RESET_CONNECTION to the server. This method is analogous to the mysql_reset_connection() C API function added in MySQL 5.7.3. A new MySQLConnection class reset_session() method is similar to reset_connection() but falls back to use reauthentication for older servers that do not support COM_RESET_CONNECTION. * Connector/Python now can report errors to Fabric that occur while accessing a MySQL instance. The information can be used to update the backing store and trigger a failover operation, provided that the instance is a primary server and Fabric has received a sufficient number of problem reports from different connectors. + The fabric dictionary argument to the connect() method now accepts a report_errors value. Its default value is False; pass a value of True to enable error reporting to Fabric. + To define which errors to report, use the extra_failure_report() function: from mysql.connector.fabric import extra_failure_report extra_failure_report([error_code_0, error_code_1, ...]) * Connector/Python now enables applications to specify additional information to be used when connecting to Fabric: User name and credentials, and information to use for establishing an SSL connection. The fabric dictionary argument to the connect() method accepts these additional values: username, password, ssl_ca, ssl_cert, ssl_key. Only the ssl_ca value is required to establish an SSL connection. If ssl_cert or ssl_key are given, both must be specified. * The connect() method now accepts a failover argument hat provides information to use for server failover in the event of connection failures. The argument value is a tuple or list of dictionaries (tuple is preferred because it is nonmutable). Each dictionary contains connection arguments for a given server in the failover sequence. Permitted dictionary values are: user, password, host, port, unix_socket, database, pool_name, pool_size. Bugs Fixed * The MySQLConnection.autocommit attribute failed to set the value of the self._autocommit
MySQL Connector/J 5.1.30 has been released
Hello all, MySQL Connector/J 5.1.30, a maintenance release of the production 5.1 branch has been released. Connector/J is the Type-IV pure-Java JDBC driver for MySQL. Version 5.1.30 is suitable for use with many MySQL server versions, including 4.1, 5.0, 5.1, 5.4, 5.5 and 5.6. MySQL Connector Java (Commercial) is already available for download on the My Oracle Support (MOS) website. This release will be available on eDelivery (OSDC) in next month's upload cycle. As always, we recommend that you check the CHANGES file in the download archive to be aware of changes in behavior that might affect your application. MySQL Connector/J 5.1.30 includes the following general bug fixes and improvements, also available in more detail on http://dev.mysql.com/doc/relnotes/connector-j/en/news-5-1-30.html : Changes in MySQL Connector/J 5.1.30 (2014-03-31) Functionality Added or Changed * Connector/J now supports MySQL Fabric. See Using Connector/J with MySQL Fabric (http://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-fabric.html) for details. Bugs Fixed * Replaced a for loop with the System.arraycopy() method for copying array values in the MysqlIO.unpackNativeEncodedColumn() and LoadBalancingConnectionProxy.addHost() methods, in order to improve the two methods' performance. (Bug #18327245, Bug #71861) * Avoided the use of an iterator over the list of statement interceptors in the methods MysqlIO.invokeStatementInterceptorsPost() and MysqlIO.invokeStatementInterceptorsPre(), so that Connector/J does not increase the stack size unnecessarily. (Bug #18236388, Bug #71679) * Improved on the code for integer-to-hex conversion when building XA commands by avoiding the creation of temporary character arrays, thus enhancing performance. (Bug #18228302, Bug #71621) * The Field.getStringFromBytes() method created a useless byte array when using JVM's converter and the encoding defined by the connection. This fix makes the method call StringUtils.toString() using the original buffer instead of creating a temporary byte array for the call. (Bug #18228668, Bug #71623) * There were sporadic cases of the key store file being open hundreds of times and causing some Too many files open errors. This fix makes sure that in com.mysql.jdbc.ExportControlled and in MysqlIO.sendFileToServer(), the input stream for the key store file is explicitly closed after use. (Bug #18107621, Bug #71432) * It was intended that if a previous query on a connection had used the setMaxRows() method, in the next query, Connector/J would not cancel that by setting SQL_SELECT_LIMIT=DEFAULT if the query contained a LIMIT clause. However, in the actual implementation, the maximum row setting was reused in the subsequent query in various situations beyond expectation (for example, when a table name contains the string limit in it). This fix removes the LIMIT-clause parsing and replaces it by a better way of controlling the maximum rows per session. (Bug #18110320, Bug #71396) * When working with MySQL 5.6, calling PreparedStatement.setTimestamp() resulted in a java.lang.StringIndexOutOfBoundsException being thrown if the Timestamp contained a fractional second. This fix corrects the digit truncation performed in the formatNanos() method, which was the cause of the problem. (Bug #18091639) * Calling ResultSet.close() on an already closed ResultSet caused an SQLException. While the exception was silently discarded, it did result in performance issues. This fix makes Connector/J comply with the Java specification that when a ResultSet object is already closed, application of the close method on it should be a no-op. (Bug #16722637, Bug #67318) * Fixed the problem of the wrong source being provided when the build property com.mysql.jdbc.noCryptoBuild was set. Thanks, On behalf of Oracle/MySQL Build Team, Murthy Narkedimilli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL Utilities 1.4.2 (including MySQL Fabric) RC has been released
Dear MySQL users, MySQL Utilities version 1.4.2 is compatible with MySQL Server versions 5.1 and greater, but should work with earlier versions (greater than v4.1). Python v2.6 and v2.7 are supported. In addition to server utilities, MySQL Utilities also contains MySQL Fabric: a framework for managing a collection of MySQL servers. MySQL Fabric is deployed as a separate service daemon that contains support for high-availability and sharding. The management framework maintains a database of the routing and state information for the servers making up the system and provides an easy-to-use command line interface for adding, removing and organizing servers. High-availability is provided by continuously monitoring the servers and executing slave promotion when the master crashes and just as importantly automatically updates the state and routing information that gets messages to the right server. MySQL Fabric comes with built-in support for sharding either using ranges or consistent hashing and supports the sharding of multiple tables to ensure that rows with matching sharding keys are stored in the same shard. MySQL Fabric also contains support for global tables that are duplicated on all shards as well as the ability to synchronize schema updates across all of the servers. To provide high performance and avoid latency, transactions are directly routed by Fabric-aware connectors rather than routing via an external proxy. The connectors dispatch transactions to the correct shard, perform load-balancing, and handle read-write splitting. Currently there exists Fabric-aware versions of Connector/Python, Connector/Java, and Connector/PHP (through a Fabric-aware mysqlnd_ms plugin). MySQL Utilities v1.4.2 is available for download from http://dev.mysql.com/downloads/tools/utilities/ A brief summary of changes is listed below. Please check the CHANGES.txt file inside the distribution for a more complete list of changes. Functionality Added or Changed (2014-03-31) --- * The mysqlrplsync utility was added, which checks data consistency between servers in a replicated setup. * The mysqlrplms utility was added, which provides round-robin multi-source replication (a slave server continually cycles through multiple masters in order to store a consolidated data set). Bugs Fixed -- * With fabric, the scheduler did not check and determine which procedures needed to be notified after releasing the procedure that was blocking them. (Bug #18454582, Bug #72117) * With fabric, failover was not handled properly when both the global and shard groups had a failed master. (Bug #18403885, Bug #72016) * The mysqlrplsync utility would crash when the --exclude option was used, and at least one database was available to be checked. This issue was due to an incomplete check when determining which tables to exclude from the slaves. (Bug #18388846) * The MySQL Utilities testing suite (mut) now tests if ports are available before assigning them to a test. (Bug #18339351) * MySQL Server UUIDs are now handled in a case-insensitive way, more precisely when checking if GTIDs belong to the master. This helps accommodate MySQL Server 5.6.9, which could change the case for the server_uuid variable. (Bug #18275566) * With Fabric, attempting to destroy a group when the group was used in the shard sub-system would output an unfriendly and unclear error. Fabric now checks if the group is used in the shard sub-system before trying to destroy (remove) it, and a clearer error is emitted if it is in use. (Bug #18280004, Bug #71766) * The mut rpl_admin_gtid test result varied from host to host, due to using the RESET MASTER statement without first stopping all the slaves. This resulted in undefined behavior, which led to inconsistent rest results. (Bug #18203946) * A typo was fixed in the mysqldbcopy utility's help text. (Bug #18206923, Bug #71627) * With Fabric, group definitions now use a single (global) username and password, instead of requiring a username/password for each MySQL server when they were added to the group. (Bug #18153823, Bug #71512) * When checking for proper permissions, Fabric would execute several SELECT statements against the 'mysql' database, which caused the login mechanism to fail if the user lacked access. This mechanism now uses 'SHOW GRANTS' statements instead, and now prints out a detailed error message when access is unavailable. (Bug #18138545, Bug #71448) * The server.set_status() function's status parameter now accepts an integer, to better coincide with the integer value provided by dump_servers(). The strings PRIMARY, SECONDARY, SPARE and FAULTY also remain as acceptable values. (Bug #18124108, Bug #71428) * The mysqlrpladmin utility did not correctly account for
Re: Help with cleaning up data
delete b from icd9x10 a join icd9x10 b on a.icd9 = b.icd9 and a.id b.id ... CREATE TABLE `ICD9X10` ( ... id icd9 icd10 25 29182 F10182 26 29182 F10282 ... Good luck, Bob
RE: Unable to connect to foreign data source
CONNECTION = 'mysql://root:root@*stripped*:3306/Prelude_copy/test001'; Should be more like: CONNECTION = 'mysql://root:stripped_password@localhost/penrepository/test001'; Just seems word if you're showing us your password is root but not host... I ran your example just fine against localhost on MySQL 5.6 Win7-64bit. -Bob
MySQL Community Server 5.7.4-m14 has been released (part 1)
Dear MySQL users, MySQL Server 5.7.4 (Milestone Release) is a new version of the world's most popular open source database. This is the fourth public milestone release of MySQL 5.7. [Due to length restrictions, this announcement is split into two parts. This is part 1.] http://dev.mysql.com/doc/mysql-development-cycle/en/development-milestone-releases.html As with any other pre-production release, caution should be taken when installing on production level systems or systems with critical data. Note that 5.7.4 includes all features in MySQL 5.6. For information on installing MySQL 5.7.4 on new servers, please see the MySQL installation documentation at http://dev.mysql.com/doc/refman/5.7/en/installing.html MySQL Server 5.7.4 is available in source and binary form for a number of platforms from the Development Releases selection of our download pages at http://dev.mysql.com/downloads/mysql/ The platforms and package formats available for MySQL 5.7.4 are the same as for 5.6. MySQL Server 5.7.4 is also available from our Yum repository for some Linux platforms, go here for details: http://dev.mysql.com/downloads/repo/ Windows packages are now available via the new Installer for Windows Installer or .ZIP (no-install) packages for more advanced needs. It should be noted that the previous MSI packaging is no longer available and the point and click configuration wizards and all MySQL products are now available in the unified Installer for Windows: http://dev.mysql.com/downloads/installer/ 5.7.4 also comes with a web installer as an alternative to the full installer. The web installer doesn't come bundled with any actual products and instead relies on download-on-demand to fetch only the products you choose to install. This makes the initial download much smaller but increases install time as the individual products will need to be downloaded. We welcome and appreciate your feedback, bug reports, bug fixes, patches, etc.: http://bugs.mysql.com/report.php The following section lists the changes in MySQL 5.7.4 since the previous milestone. http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-4.html Enjoy! On behalf of the MySQL Build Team at Oracle, - Bjorn Munch Changes in MySQL 5.7.4 (2014-03-31, Milestone 14) Note This is a milestone release, for use at your own risk. Significant development changes take place in milestone releases and you may encounter compatibility issues, such as data format changes that require attention in addition to the usual procedure of running mysql_upgrade. For example, you may find it necessary to dump your data with mysqldump before the upgrade and reload it afterward. Performance Schema Notes * The Performance Schema now instruments prepared statements (for both the binary and text protocols): + Information about prepared statements is available in the prepared_statements_instances table. This table enables inspection of prepared statements used in the server and provides aggregated statistics about them. + The performance_schema_max_prepared_statements_instances system variable controls the size of the table. + The Performance_schema_prepared_statements_lost status variable indicates how many prepared statements could not be insrumented. For more information, see The prepared_statements_instances Table (http://dev.mysql.com/doc/refman/5.7/en/prepared-statements-in stances-table.html). If you upgrade to this release of MySQL from an earlier version, you must run mysql_upgrade (and restart the server) to incorporate these changes into the performance_schema database. Security Notes * Incompatible Change: MySQL deployments installed using RPM packages now are secure by default. The following changes have been implemented as the default deployment characteristics: + The installation process creates only a single root account, 'root'@'localhost', automatically generates a random password for this account, and marks the password expired. The MySQL administrator must connect as root using the random password and use SET PASSWORD to select a new password. (The random password is found in the $HOME/.mysql_secret file.) + Installation creates no anonymous-user accounts. + Installation creates no test database. Those changes are implemented by modifying the default mode of operation for mysql_install_db, which is invoked automatically during RPM installation operations. Therefore, the changes also affect non-RPM installation methods for which you invoke mysql_install_db manually. Because
MySQL Community Server 5.7.4-m14 has been released (part 2)
[ This is part 2 of the announcement ] Bugs Fixed, continued * InnoDB: A type name (srv_shutdown_state) was the same as a variable name. The srv_shutdown_state type name has been changed to srv_shutdown_t. (Bug #16735398) * InnoDB: On Windows, the full-text search (FTS) object ID was not in the expected hexadecimal format. (Bug #16559254) References: See also Bug #16559119. * InnoDB: The buf_buddy_relocate function would perform an unnecessary hash lookup. (Bug #16596057) * InnoDB: Server shutdown would result in a hang with the following message written to the error log: [NOTE] InnoDB: Waiting for purge thread to be suspended. (Bug #16495065) * InnoDB: InnoDB would fail to start when innodb_data_file_path specified the data file size in kilobytes by appending K to the size value. (Bug #16287752) * InnoDB: Fetching and releasing pages from the buffer pool and tracking the page state are expensive and complex operations. Prior to the bug fix, these operations were performed using a page mutex. Using a page mutex to track several things is expensive and does not scale well. The bug fix separates fetch and release tracking (in-use state) of a page from page I/O state tracking. Fetch and release is now tracked using atomics where available. For portability, a new CMake build option, INNODB_PAGE_ATOMIC_REF_COUNT (default ON), can be used to disable atomic page reference counting on platforms where atomics support is not available. When atomic page reference counting is enabled (default), [Note] InnoDB: Using atomics to ref count buffer pool pages is printed to the error log at server startup. If atomic page reference counting is disabled, [Note] InnoDB: Using mutexes to ref count buffer pool pages is printed instead. (Bug #16249481, Bug #68079) * InnoDB: An insert buffer merge would cause an assertion error due to incorrectly handled ownership information for externally stored BLOBs. InnoDB: Assertion failure in thread thread_num in file ibuf0ibuf.cc l ine 4080 InnoDB: Failing assertion: rec_get_deleted_flag(rec, page_is_comp(pag e)) (Bug #14668683) * InnoDB: Decreasing the auto_increment_increment value would have no affect on the next auto-increment value. (Bug #14049391, Bug #65225) * InnoDB: Table renaming errors would appear in the LATEST FOREIGN KEY ERROR section of the SHOW ENGINE INNODB STATUS output. (Bug #12762390, Bug #61746) * InnoDB: The page latching algorithm for b-trees would lock sibling leaf pages, prolonging dictionary locks. The bug fix implements prefectching of sibling leaf pages to reduce index lock holding time. (Bug #12734249, Bug #61736) * InnoDB: An INSERT ...ON DUPLICATE KEY UPDATE statement run on a table with multiple unique indexes would sometimes cause events to be incorrectly written to the binary log. (Bug #11758237, Bug #50413) * InnoDB: BUF_READ_AHEAD_AREA would frequently call ut_2_power_up for workloads with a high I/O rate. The calculation is now performed once and the result is stored in the buf_pool_t structure. (Bug #11762242, Bug #54814) * InnoDB: UNIV_SYNC_DEBUG, which was disabled in univ.i with the fix for Bug#16720368, is now enabled. (Bug #69617, Bug #17033591) * Partitioning: Queries using the index_merge optimization (see Index Merge Optimization (http://dev.mysql.com/doc/refman/5.7/en/index-merge-optimizati on.html)) could return invalid results when run against tables that were partitioned by HASH. (Bug #17588348, Bug #70588) References: See also Bug #16862316, Bug #17648468, Bug #18167648. * Partitioning: When no partition had returned a row since the last HA_ERR_KEY_NOT_FOUND error, the use of uninitialized memory in the priority queue used for returning rows in sorted order could lead to a crash of the server. (Bug #17401628) * Replication: When running the server with --gtid-mode=ON, STOP SLAVE followed by START SLAVE resulted in a mismatch between the information provided by INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO and the Slave_open_temp_tables status variable: the INNODB_TEMP_TABLE_INFO table showed that no temporary tables existed, but Slave_open_temp_tables had a nonzero value. (Bug #18236612) * Replication: Attempting to use semisynchronous replication concurrently with SSH connections caused the server to fail. (Bug #18219471) * Replication: When MASTER_HEARTBEAT_PERIOD was not included in CHANGE MASTER TO, the statement reset Slave_heartbeat_period to its default value and Slave_received_heartbeats to 0.
