IF and CASE
It is my impression that when their functions are equivalent, IF takes more time than CASE. Comment? Do they always evaluate all their arguments? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL Community Server 5.1.68 has been released
Dear MySQL users, MySQL Server 5.1.68, a new version of the popular Open Source Database Management System, has been released. MySQL 5.1.68 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.68 on new servers or upgrading to MySQL 5.1.68 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. 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/relnotes/mysql/5.1/en/news-5-1-68.html Enjoy! === Changes in MySQL 5.1.68 (5 February, 2013) Bugs Fixed * Performance: InnoDB: Optimized read operations for compressed (http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_com pression) tables by skipping redundant tests. The check for whether any related changes needed to be merged from the insert buffer (http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_ins ert_buffer) was being called more often than necessary. (Bug #14329288, Bug #65886) * Performance: InnoDB: Immediately after a table was created, queries against it would not use loose index scans (http://dev.mysql.com/doc/refman/5.1/en/group-by-optimization. html#loose-index-scan). The issue went away following an ALTER TABLE on the table. The fix improves the accuracy of the index statistics (http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_ind ex_statistics) gathered when the table is first created. (Bug #14200010) * Partitioning: InnoDB: Previously, when attempting to optimize one or more partitions of a partitioned table that used a storage engine that does not support partition-level OPTIMIZE, such as InnoDB, MySQL reported Table does not support optimize, doing recreate + analyze instead, then re-created the entire table, but did not actually analyze it. Now in such cases, the warning message is, Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. In addition, the entire table is analyzed after first being rebuilt. (Bug #11751825) * InnoDB: The status variable Innodb_buffer_pool_read_ahead_evicted could show an inaccurate value, higher than expected, because some pages in the buffer pool (http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_buf fer_pool) were incorrectly considered as being brought in by read-ahead (http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_rea d_ahead) requests. (Bug #15859402, Bug #67476) * InnoDB: Creating an index on a CHAR column could fail for a table with a character set with varying length, such as UTF-8, if the table was created with the ROW_FORMAT=REDUNDANT clause. (Bug #15874001) * InnoDB: If the server crashed at a precise moment during an ALTER TABLE operation that rebuilt the clustered index (http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_clu stered_index) for an InnoDB table, the original table could be inaccessible afterward. An example of such an operation is ALTER TABLE ... ADD PRIMARY KEY The fix preserves the original table if the server halts during this operation. You might still need to rename the .ibd file manually to restore the original table contents: in MySQL 5.6 and higher, rename from #sql-ib$new_table_id.ibd to table_name.ibd within the database directory; prior to MySQL 5.6, the temporary file to rename is table_name#1 or #2. (Bug #14669848) * InnoDB: An error at the filesystem level, such as too many open files, could cause an unhandled error during an ALTER TABLE operation. The error could be accompanied by Valgrind warnings, and by this assertion message: Assertion `! is_set()' failed. mysqld got signal 6 ; (Bug #14628410, Bug #16000909) * InnoDB: During shutdown, with the innodb_purge_threads configuration option set greater than 1, the server could halt prematurely with this error: mysqld got signal 11 A workaround was to increase innodb_log_file_size and set innodb_purge_threads=1. The fix was backported to MySQL 5.5 and 5.1, although those versions do not have the innodb_purge_threads configuration option so the error was unlikely to occur. (Bug #14234028) * InnoDB: The value of the innodb_version variable was not updated consistently for all server releases for the InnoDB Plugin in MySQL 5.1, and the integrated InnoDB
MySQL Community Server 5.5.30 has been released
Dear MySQL users, MySQL 5.5.30 is a new version of the 5.5 production release of the world's most popular open source database. MySQL 5.5.30 is recommended for use on production systems. MySQL 5.5 includes several high-impact enhancements to improve the performance and scalability of the MySQL Database, taking advantage of the latest multi-CPU and multi-core hardware and operating systems. In addition, with release 5.5, InnoDB is now the default storage engine for the MySQL Database, delivering ACID transactions, referential integrity and crash recovery by default. MySQL 5.5 also provides a number of additional enhancements including: - Significantly improved performance on Windows, with various Windows specific features and improvements - Higher availability, with new semi-synchronous replication and Replication Heartbeat - Improved usability, with Improved index and table partitioning, SIGNAL/RESIGNAL support and enhanced diagnostics, including a new Performance Schema monitoring capability. For a more complete look at what's new in MySQL 5.5, please see the following resources: MySQL 5.5 is GA, Interview with Tomas Ulin: http://dev.mysql.com/tech-resources/interviews/thomas-ulin-mysql-55.html Documentation: http://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html Whitepaper: What's New in MySQL 5.5: http://dev.mysql.com/tech-resources/articles/introduction-to-mysql-55.html If you are running a MySQL production level system, we would like to direct your attention to MySQL Enterprise Edition, which includes the most comprehensive set of MySQL production, backup, monitoring, modeling, development, and administration tools so businesses can achieve the highest levels of MySQL performance, security and uptime. http://mysql.com/products/enterprise/ For information on installing MySQL 5.5.30 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 Database 5.5.30 is available in source and binary form for a number of platforms from our download pages at: http://dev.mysql.com/downloads/mysql/ 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/relnotes/mysql/5.5/en/news-5-5-30.html Enjoy! Changes in MySQL 5.5.30 (5 Feb, 2013) Known limitations of this release: On Microsoft Windows, when using the MySQL Installer to install MySQL Server 5.5.30 on a host with an existing MySQL Server of a different version (such as 5.6.10), that also has a different license (community versus commercial), you must first update the license type of the existing MySQL Server. Otherwise, MySQL Installer will remove MySQL Server(s) with different licenses from the one you chose with MySQL Server 5.5.30. On Microsoft Windows 8, updating a community release to a commercial release requires you to manually restart the MySQL service after the update. Functionality Added or Changed * InnoDB: The innodb_print_all_deadlocks configuration option from MySQL 5.6 was backported to MySQL 5.5. This option records each deadlock (http://dev.mysql.com/doc/refman/5.5/en/glos_deadlock.html) condition in the MySQL error log, allowing easier troubleshooting if frequent deadlocks point to application coding issues. (Bug #14515889) * In RPM packages built for Unbreakable Linux Network, libmysqld.so now has a version number. (Bug #15972480) Bugs Fixed * InnoDB; Performance: Some data structures related to undo logging could be initialized unnecessarily during a query, although they were only needed under specific conditions. (Bug #14676084) * InnoDB; Performance: Optimized read operations for compressed (http://dev.mysql.com/doc/refman/5.5/en/glos_compression.html) tables by skipping redundant tests. The check for whether any related changes needed to be merged from the insert buffer (http://dev.mysql.com/doc/refman/5.5/en/glos_insert_buffer.htm l) was being called more often than necessary. (Bug #14329288, Bug #65886) * InnoDB; Performance: Immediately after a table was created, queries against it would not use loose index scans (http://dev.mysql.com/doc/refman/5.5/en/loose-index-scan.html) . The issue went away following an ALTER TABLE on the table. The fix improves the accuracy of the index statistics (http://dev.mysql.com/doc/refman/5.5/en/glos_index_statistics. html) gathered when the table is first created, and prevents the query plan from being changed by the ALTER TABLE statement. (Bug #14200010) * InnoDB;
MySQL Connector/ODBC 5.1.12 has been released
Dear MySQL users, MySQL Connector/ODBC 5.1.12, a new version of the ODBC driver for the MySQL database management system, has been released. This release is the latest release of the 5.1 series and is suitable for use with any MySQL version since 4.1 (It will not work with 4.0 or earlier releases.). The release is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/connector/odbc/5.1.html and mirror sites. Note that 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. Enjoy! The MySQL build team at Oracle == Changes in MySQL Connector/ODBC 5.1.12 (5 February, 2013) Functionality Added or Changed * The new connection option can_handle_exp_pwd indicates that your application includes error-handling logic to deal with the error code for an expired password. See Connector/ODBC Connection Parameters (http://dev.mysql.com/doc/refman/5.5/en/connector-odbc-configu ration-connection-parameters.html) for the details of this connection option and the associated SQL state and native error code. See ALTER USER Syntax (http://dev.mysql.com/doc/refman/5.6/en/alter-user.html) for details about password expiration for MySQL server accounts. This new option is added to the Windows GUI, through a checkbox Can Handle Expired Password on the Connection tab of the Details dialog. * The following reserved words were added to the list returned by the SQLGetInfo() ODBC function, for compatibility with the latest MySQL 5.6 syntax: + GET + IO_AFTER_GTIDS + IO_BEFORE_GTIDS + MASTER_BIND + ONE_SHOT + PARTITION + SQL_AFTER_GTIDS + SQL_BEFORE_GTIDS Bugs Fixed * The string returned by the SQLNativeSql function was not null-terminated as it should be. (Bug #14559721) * Specifying certain values for the CHARSET option in the connection string could cause a serious error when a query was executed. (Bug #14363601) * If multiple statements were called using the same statement handle, SQLColumns and possibly other catalog functions could return wrong results. Some field length values were not reset in the descriptor records. The issue occurred even if the statement handle was closed with SQL_CLOSE between the statements. (Bug #14338051) * If an application received a SIGPIPE signal, then another SIGPIPE signal immediately after (before the first signal handler was finished), the application could terminate rather than handling the second signal. (Bug #14303803) * Several catalog or info functions could raise an incorrect error String data, right truncated when only partial information was requested. For example, if the application called SQLDescribeCol(hstmt, ColNumber, ColName, BufferLen, ), but did not want the column name (ColName == NULL and BufferLen == 0). SQL_SUCCESS_WITH_INFO could also be returned rather than the correct value SQL_SUCCESS. This issue affected many ADO, DAO, and other applications. Affected functions include: Spurious error and incorrect return code: SQLDescribeCol SQLDescribeColA SQLDescribeColW SQLGetInfoA SQLGetInfoW Incorrect return code: SQLColAttribute SQLColAttributeW SQLGetConnectAttr SQLGetConnectAttrW SQLGetCursorName SQLGetCursorNameW SQLGetInfo SQLGetInfoW SQLNativeSql SQLNativeSqlW (Bug #14285620) * Calling the SQLTables function with a very long database or table name could cause a serious error. This fix allows the SQLTables function to accept database and table names with the maximum length of 64 characters. (Bug #14085211) * On a 64-bit system, calls to the SQLBindCol function using indicator variables (through the last parameter) could return incorrect results. (Bug #11766437, Bug #59541) * The symbols SQLInstallDriverEx, SQLInstallDriverExW, and SQLRemoveDriverW were exported, causing incompatibility with some commercial ODBC packages such as DataDirect ODBC, and making Connector/ODBC dependent on the unixODBC library libodbcinst.so.1. This issue was first observed in Connector/ODBC 5.1.8. (Bug #11766724, Bug #59900) * When a column with type TINYTEXT, TEXT, MEDIUMTEXT, or LONGTEXT was retrieved from a table with a binary collation, the text fields were converted to a hexadecimal representation, even though these values were not really BLOBs. The unnecessary conversion could expand the data, causing overflow problems when storing the result values. (Bug #11746572, Bug #27282) Thanks, RE
MySQL Connector/C++ 1.1.2 has been released
Dear MySQL Users, A new GA (general availability) version of MySQL Connector/C++ has been made available: MySQL Connector/C++ 1.1.2 GA. The MySQL Connector/C++ provides a C++ API for connecting client applications to the MySQL Server 5.1 or newer. You can download the production release at: http://dev.mysql.com/downloads/connector/cpp/1.1.html The MySQL driver for C++ offers an easy to use API derived from JDBC 4.0. MySQL Workbench is using it successfully since years. We have improved the driver since the last GA release. Please see the documentation and the CHANGES file in the source distribution for a detailed description of bugs that have been fixed. Bug descriptions are also listed below. Enjoy! The MySQL build team at Oracle == Changes in MySQL Connector/CPP 1.1.2 (5 February, 2013) Bugs Fixed * Connector/C++ applications could not handle connecting to the server using an account for which the password had expired. Connector/C++ now supports three new connection options: + OPT_CAN_HANDLE_EXPIRED_PASSWORDS: If true, this indicates to the driver that the application can handle expired passwords. If the application specifies OPT_CAN_HANDLE_EXPIRED_PASSWORDS but the underlying libmysql library does not support it, the driver returns sql::mysql:deCLIENT_DOESNT_SUPPORT_FEATURE(820). + preInit: A string containing queries to run before driver initialization. + postInit: A string containing queries to run after driver initialization. A new file driver/mysql_error.h is being added to the MSI package. This file defines an enum DRIVER_ERROR, which contains the definition of deCL_CANT_HANDLE_EXP_PWD. In addition to the preceding changes, these problems with Statement::executeUpdate were fixed: + If Statement::executeUpdate executed multiple statements, the connection became unusable. + There was no exception if one of queries returned a resultset. Now executeUpdate returns and update count for the last executed query. For example code showing how to use the new options, see the file test/unit/bugs/bugs.cpp in the Connector/C++ distribution. (Bug #67325, Bug #15936764) Thanks, RE Team -- Kent Boortz, Release Staff engineer Oracle, The MySQL Team Mobile: +46 76 77 69 049 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL Connector/ODBC 5.2.4 has been released
Dear MySQL users, MySQL Connector/ODBC 5.2.4, a new version of the ODBC driver for the MySQL database management system, has been released. The available downloads include both a Unicode driver and an ANSI driver based on the same modern codebase. Please select driver type you need based on the type of your application - Unicode or ANSI. Server-side prepared statements are enabled by default. It is suitable for use with any MySQL version since 4.1 (It will not work with 4.0 or earlier releases.) The release is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/connector/odbc/5.2.html For information on installing, please see the documentation at http://dev.mysql.com/doc/refman/5.5/en/connector-odbc-installation.html Enjoy! The MySQL build team at Oracle == Changes in MySQL Connector/ODBC 5.2.4 (5 February, 2013) This release fixes any bugs encountered since Connector/ODBC 5.2.3. Its main focus is on compatibility with the latest features of MySQL 5.6. Functionality Added or Changed * The new connection option can_handle_exp_pwd indicates that your application includes error-handling logic to deal with the error code for an expired password. See Connector/ODBC Connection Parameters (http://dev.mysql.com/doc/refman/5.5/en/connector-odbc-configu ration-connection-parameters.html) for the details of this connection option and the associated SQL state and native error code. See ALTER USER Syntax (http://dev.mysql.com/doc/refman/5.6/en/alter-user.html) for details about password expiration for MySQL server accounts. This new option is added to the Windows GUI, through a checkbox Can Handle Expired Password on the Connection tab of the Details dialog. * The following reserved words were added to the list returned by the SQLGetInfo() ODBC function, for compatibility with the latest MySQL 5.6 syntax: + GET + IO_AFTER_GTIDS + IO_BEFORE_GTIDS + MASTER_BIND + ONE_SHOT + PARTITION + SQL_AFTER_GTIDS + SQL_BEFORE_GTIDS Bugs Fixed * When a column with type TINYTEXT, TEXT, MEDIUMTEXT, or LONGTEXT was retrieved from a table with a binary collation, the text fields were converted to a hexadecimal representation, even though these values were not really BLOBs. The unnecessary conversion could expand the data, causing overflow problems when storing the result values. (Bug #11746572, Bug #27282) Thanks, RE Team. -- Kent Boortz, Release Staff engineer Oracle, The MySQL Team Mobile: +46 76 77 69 049 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL Community Server 5.6.10 (GA) has been released
Dear MySQL users, MySQL Server 5.6.10 (GA) is a new version of the world's most popular open source database. This is the first official release of MySQL 5.6. The new features in this release are now deemed to be of Release quality. Note that 5.6.10 includes all features in MySQL 5.5 and previous 5.6 Development Milestone Releases. An overview of what's new in MySQL 5.6 is available online at http://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html For information on installing MySQL 5.6.10 on new servers, please see the MySQL installation documentation at http://dev.mysql.com/doc/refman/5.6/en/installing.html For upgrading from previous MySQL releases, please see the important upgrade considerations at http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html MySQL Server 5.6.10 is available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/mysql/ Please note that the list of platforms for MySQL 5.6 has been adapted to the changes in the field: - Apple Mac OS X 10.6 and 10.7 on x86 (32 bit) and x86_64 (Binary packages of MySQL 5.6 are not provided for OS X 10.5), - Debian 6 on x86 (32 bit) and x86_64 (Binary packages of MySQL 5.6 are not provided for Debian 5), - RedHat Enterprise / Oracle Linux 5 and 6 on x86 (32 bit) and x86_64 (Binary packages of MySQL 5.6 are not provided for RHEL/OL 4), - SuSE Enterprise Linux 11 on x86_64 (Binary packages of MySQL 5.6 are not provided for SLES 10), - generic Linux (kernel 2.6) on x86 (32 bit) and x86_64, using glibc 2.5 (or newer), - FreeBSD 9 on x86_64 (Binary packages of MySQL 5.6 are not provided for FreeBSD 7 and 8), - Oracle Solaris 10 and 11 on Sparc (64 bit), x86 (32 bit) and x86_64, - Windows Vista, 7, 8, Server 2008 on x86 (32 bit) and x86_64, Server 2008 R2 and Server 2012 on x86_64 only. (Binary packages of MySQL 5.6 are not provided for Windows XP and 2003). This does not affect the list of supported platforms for 5.1 and 5.5. For Linux, the dependency on glibc 2.5 is new with 5.6, it is reflected in the names of the binary packages for generic Linux: linux-glibc2.5 (former: linux2.6). All supported specific platforms (RedHat 5 and newer, SuSE 11, ...) are using glibc 2.5 or newer. If you want to check your system, you may simply run the library: | Prompt$ /lib/libc.so.6 | GNU C Library stable release version 2.7, by Roland McGrath et al. | Copyright (C) 2007 Free Software Foundation, Inc. | ... The above example was done on Debian 6, it shows glibc 2.7. Packages for specific Linux distributions are provided in the specific format (RPM or deb), in addition the generic tar.gz packages will fit these distributions. For RedHat-alike distributions like CentOS or Fedora, both the RedHat and the generic packages should work. If you are using a newer version of your operating system, its binary compatibility approach (supporting applications built for older versions) should ensure you can use MySQL 5.6. 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/ The list of all Bugs Fixed for 5.6.10 may also be viewed online at http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-10.html If you are running a MySQL production level system, we would like to direct your attention to MySQL Enterprise Edition, which includes the most comprehensive set of MySQL production, backup, monitoring, modeling, development, and administration and migration tools so businesses can achieve the highest levels of MySQL performance, security and uptime. http://mysql.com/products/enterprise/ More information is available here: http://dev.mysql.com/doc/refman/5.6/en/mysql-enterprise.html On behalf of the MySQL release Engineering Team, - Bjorn Munch Enjoy! - Changes in MySQL 5.6.10 (5 February 2013) Beginning with MySQL 5.6.10, MySQL Enterprise Edition is available for MySQL 5.6. Specifically, MySQL Enterprise 5.6.10 includes these components previously available only in MySQL 5.5: MySQL Enterprise Security (PAM and Windows authentication plugins), MySQL Enterprise Audit, and MySQL Thread Pool. For information about these features, see MySQL Enterprise Edition ( http://dev.mysql.com/doc/refman/5.6/en/mysql-enterprise.html ). To learn more about commercial products, see http://www.mysql.com/products/ . Known limitations of this release: On Microsoft Windows, when using the MySQL Installer to install MySQL Server 5.6.10 on a host with an existing MySQL Server of a different version (such as 5.5.30), that also has a
SELECT subquery problem
De-lurking here. I am trying to figure out how to return results from a query. What I need to do is to return 4 columns from a database. This is easy: SELECT last_name, first_name, phone, email FROM `mydatabasetable` WHERE `current_member` = Y AND `pub_name` = Y ORDER BY last_name ASC This works fine, as expected. But, I want to only display the value in `email` if the value in another field, `pub_email` = Y So, the resultant output would look like this, for instance, if the value of `pub_email` =N for Mr. Wills: Jones John 555-555- johnjo...@nowhere.com Smith Jim 555-222- jimsm...@nothing.com WillsChill 555-111- Zorro Felicity 555-999- felicityzo...@madeup.com Can't quite figure out how to express this. TIA for your suggestions! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SELECT subquery problem
Try using a CASE construct in the select. Should work for this. A On Tue, Feb 5, 2013 at 3:25 PM, Stefan Kuhn stef...@web.de wrote: You cannot do this. A sql result alwas has the same number of columns in each row. You could have null or in the column, though. This could be done via the if(,,)-statement of mysql or by using a union and two selects, one for pub_email=n and the other for the rest. Gesendet: Dienstag, 05. Februar 2013 um 15:49 Uhr Von: cl c...@nimbleeye.com An: mysql@lists.mysql.com Betreff: SELECT subquery problem De-lurking here. I am trying to figure out how to return results from a query. What I need to do is to return 4 columns from a database. This is easy: SELECT last_name, first_name, phone, email FROM `mydatabasetable` WHERE `current_member` = Y AND `pub_name` = Y ORDER BY last_name ASC This works fine, as expected. But, I want to only display the value in `email` if the value in another field, `pub_email` = Y So, the resultant output would look like this, for instance, if the value of `pub_email` =N for Mr. Wills: Jones John 555-555- johnjo...@nowhere.com Smith Jim 555-222- jimsm...@nothing.com Wills Chill 555-111- Zorro Felicity 555-999- felicityzo...@madeup.com Can't quite figure out how to express this. TIA for your suggestions! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql[http://lists.mysql.com/mysql] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: IF and CASE
As a Rule of Thumb, function evaluation time is not significant to the overall time for running a query. (I see IF and CASE as 'functions' for this discussion.) Do you have evidence that says that IF is slower? Perhaps using BENCHMARK()? -Original Message- From: h...@tbbs.net [mailto:h...@tbbs.net] Sent: Monday, February 04, 2013 11:31 PM To: mysql@lists.mysql.com Subject: IF and CASE It is my impression that when their functions are equivalent, IF takes more time than CASE. Comment? Do they always evaluate all their arguments? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Aw: SELECT subquery problem
You cannot do this. A sql result alwas has the same number of columns in each row. You could have null or in the column, though. This could be done via the if(,,)-statement of mysql or by using a union and two selects, one for pub_email=n and the other for the rest. Gesendet: Dienstag, 05. Februar 2013 um 15:49 Uhr Von: cl c...@nimbleeye.com An: mysql@lists.mysql.com Betreff: SELECT subquery problem De-lurking here. I am trying to figure out how to return results from a query. What I need to do is to return 4 columns from a database. This is easy: SELECT last_name, first_name, phone, email FROM `mydatabasetable` WHERE `current_member` = Y AND `pub_name` = Y ORDER BY last_name ASC This works fine, as expected. But, I want to only display the value in `email` if the value in another field, `pub_email` = Y So, the resultant output would look like this, for instance, if the value of `pub_email` =N for Mr. Wills: Jones John 555-555- johnjo...@nowhere.com Smith Jim 555-222- jimsm...@nothing.com Wills Chill 555-111- Zorro Felicity 555-999- felicityzo...@madeup.com Can't quite figure out how to express this. TIA for your suggestions! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql[http://lists.mysql.com/mysql] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: file level encryption on mysql
Thanks! I tried all these methods and you are right this is not going to work for us. I am not a developer, does anyone have any good links or reference to the best way I can share with my developers on best way to encrypt and decrypt personal user info. We do not store credit cards, but want to store 3 tables that have email address, ip address, and personal info. On Sun, Feb 3, 2013 at 12:57 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 03.02.2013 18:52, schrieb Mike Franon: Hi, I was wondering what type of encryption for linux would you recommend to encrypt the database files on the OS level? I had a hard time starting the database after I moved it to a partiton with encryptFS I only need 3 tables encrypted and know it is better to do it from the application, but unfortunately that cannot happen for a while. Has anyone done OS file level encryption, and if so which one did they use? https://wiki.archlinux.org/index.php/Dm-crypt_with_LUKS but this all is useless in case of intrusion because the FS is unlocked and you have no gain - FS encryption only matters if your notebook or disks get stolen which is unlikely on a server -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: file level encryption on mysql
you have to encrypt them in the application and make the key stored as safe as possible, however for a full intrution there is no way to protect data which can not be only hashed somewhere you need the information how to encrypt them Am 05.02.2013 15:18, schrieb Mike Franon: I tried all these methods and you are right this is not going to work for us. I am not a developer, does anyone have any good links or reference to the best way I can share with my developers on best way to encrypt and decrypt personal user info. We do not store credit cards, but want to store 3 tables that have email address, ip address, and personal info. On Sun, Feb 3, 2013 at 12:57 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 03.02.2013 18:52, schrieb Mike Franon: Hi, I was wondering what type of encryption for linux would you recommend to encrypt the database files on the OS level? I had a hard time starting the database after I moved it to a partiton with encryptFS I only need 3 tables encrypted and know it is better to do it from the application, but unfortunately that cannot happen for a while. Has anyone done OS file level encryption, and if so which one did they use? https://wiki.archlinux.org/index.php/Dm-crypt_with_LUKS but this all is useless in case of intrusion because the FS is unlocked and you have no gain - FS encryption only matters if your notebook or disks get stolen which is unlikely on a server signature.asc Description: OpenPGP digital signature
Re: file level encryption on mysql
Which is the best way ? I see you can do it from PHP itself http://coding.smashingmagazine.com/2012/05/20/replicating-mysql-aes-encryption-methods-with-php/ or can use mysql AES? http://security.stackexchange.com/questions/16473/how-do-i-protect-user-data-at-rest From what I understand we need two way and one way encryption. Is the best way what the first article is recommending? On Tue, Feb 5, 2013 at 9:20 AM, Reindl Harald h.rei...@thelounge.net wrote: you have to encrypt them in the application and make the key stored as safe as possible, however for a full intrution there is no way to protect data which can not be only hashed somewhere you need the information how to encrypt them Am 05.02.2013 15:18, schrieb Mike Franon: I tried all these methods and you are right this is not going to work for us. I am not a developer, does anyone have any good links or reference to the best way I can share with my developers on best way to encrypt and decrypt personal user info. We do not store credit cards, but want to store 3 tables that have email address, ip address, and personal info. On Sun, Feb 3, 2013 at 12:57 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 03.02.2013 18:52, schrieb Mike Franon: Hi, I was wondering what type of encryption for linux would you recommend to encrypt the database files on the OS level? I had a hard time starting the database after I moved it to a partiton with encryptFS I only need 3 tables encrypted and know it is better to do it from the application, but unfortunately that cannot happen for a while. Has anyone done OS file level encryption, and if so which one did they use? https://wiki.archlinux.org/index.php/Dm-crypt_with_LUKS but this all is useless in case of intrusion because the FS is unlocked and you have no gain - FS encryption only matters if your notebook or disks get stolen which is unlikely on a server -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: IF and CASE
2013/02/05 17:06 +, Rick James As a Rule of Thumb, function evaluation time is not significant to the overall time for running a query. (I see IF and CASE as 'functions' for this discussion.) Do you have evidence that says that IF is slower? Perhaps using BENCHMARK()? Not BENCHMARK: I did a query with one, and also with the other, and repeated each at least a dozen times, and looked at the reported time. The IF-variant took ever so slightly more time than the CASE-variant. But which of the arguments are always evaluated, which only at need? This could be a difference, that IF s arguments always are, CASE s only at need. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: file level encryption on mysql
AES encryption is weak because it is too easy for the hacker to get the passphrase. If you can somehow hide the passphrase behind 'root', you can at least prevent a non-sudo user from seeing the data. Your web server starts as root, then degrades itself before taking requests. If it can grab the passphrase before that, it can keep it in RAM for use, but not otherwise expose it. Bottom line: The problem (of protecting data from hacker/thief/etc) cannot be solved by just MySQL. (And perhaps MySQL is not even part of the solution.) -Original Message- From: Mike Franon [mailto:kongfra...@gmail.com] Sent: Tuesday, February 05, 2013 6:43 AM To: Reindl Harald Cc: mysql@lists.mysql.com Subject: Re: file level encryption on mysql Which is the best way ? I see you can do it from PHP itself http://coding.smashingmagazine.com/2012/05/20/replicating-mysql-aes- encryption-methods-with-php/ or can use mysql AES? http://security.stackexchange.com/questions/16473/how-do-i-protect- user-data-at-rest From what I understand we need two way and one way encryption. Is the best way what the first article is recommending? On Tue, Feb 5, 2013 at 9:20 AM, Reindl Harald h.rei...@thelounge.net wrote: you have to encrypt them in the application and make the key stored as safe as possible, however for a full intrution there is no way to protect data which can not be only hashed somewhere you need the information how to encrypt them Am 05.02.2013 15:18, schrieb Mike Franon: I tried all these methods and you are right this is not going to work for us. I am not a developer, does anyone have any good links or reference to the best way I can share with my developers on best way to encrypt and decrypt personal user info. We do not store credit cards, but want to store 3 tables that have email address, ip address, and personal info. On Sun, Feb 3, 2013 at 12:57 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 03.02.2013 18:52, schrieb Mike Franon: Hi, I was wondering what type of encryption for linux would you recommend to encrypt the database files on the OS level? I had a hard time starting the database after I moved it to a partiton with encryptFS I only need 3 tables encrypted and know it is better to do it from the application, but unfortunately that cannot happen for a while. Has anyone done OS file level encryption, and if so which one did they use? https://wiki.archlinux.org/index.php/Dm-crypt_with_LUKS but this all is useless in case of intrusion because the FS is unlocked and you have no gain - FS encryption only matters if your notebook or disks get stolen which is unlikely on a server -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
