MySQL: Transactions and Autocommit – O'Reilly Network

by Lanh on August 10, 2014

by Roland Bouman

Some people believe that the ability to commit or rollback multiple statements as a single unit is a defining characteristic of the concept of transactions. They draw the -false- conclusion that enabling autocommit is the same as disabling transactions. It is easy to demonstrate why this is not true.

7 CommentsSynaptic2007-08-19 01:23:46

Great. But in the real world, there are often records inserted into multiple tables with foreign key constraints. With autocommit enabled, when a subsequent query fails, you are left with orphaned records.Showing some special case of inserting multiple records into one table with a single query doesn’t really show anything other than the fact that you get paid per article.

Roland Bouman2007-08-19 04:43:06

“Great. But in the real world, there are often records inserted into multiple tables with foreign key constraints. With autocommit enabled, when a subsequent query fails, you are left with orphaned records.”mm, you mean *without* foreign key constraints…Otherwise, I don’t see how you can get orphans if your constraints are enabled?As for “the real world”: well, the real world is diverse. Some databases have autocommit on by default – others have it off by default. The purpose of this article was to explain why having autocommit on is still transactional – it does not aim to judge whether autocommit is good or bad.”Showing some special case of inserting multiple records into one table with a single query doesn’t really show anything other than the fact that you get paid per article.”Well, I guess you are living in another real world than I do. In my experience, inserting many rows per statement happens quite often, and it is in fact one of the reasons why transactions are great to have. So it is by no means special in my opinion. Obviously my example in this article is not convincing, but that is besides the point.BTW – I don’t really understand where the remark is coming from with regard to “getting paid per article”. I don’t get paid for writing articles. I have had the odd offer to write a column or article in paid-for magazines for a fee, but so far I have declined those. I am not a full-time writer, and I fear that comitting to a deadline for a fee might sometimes mean submitting articles that don’t live up to my personal quality standards. But suppose I would recieve money for writing – I still don’t see why you should think badly of it.

Frank2007-11-04 12:33:14

Very well written article. Thank you very much, you have cleared up doubt about using autocommit while still being transactional.

Earnie2007-12-13 10:58:28

‘”Great. But in the real world, there are often records inserted into multiple tables with foreign key constraints. With autocommit enabled, when a subsequent query fails, you are left with orphaned records.”mm, you mean *without* foreign key constraints…Otherwise, I don’t see how you can get orphans if your constraints are enabled?’It is possible to have data inserted into the controlling table that should not remain if the dependent rows do not successfully commit. In this instance the transaction needs to be one large transaction that commits only at the end of the process. If you are doing this, a SET autocommit=0 is always recommended (it’s best not to assume).

Arnab2008-02-11 15:07:14

What is the syntax to create an auto-commit transaction in SQL Server 2005.Please Help

Roland Bouman2008-02-11 15:24:29

Arnab,To be sure, the article was about MySQL – not Microsoft SQL Server. For Microsoft SQL Server, look here:http://msdn2.microsoft.com/en-us/library/aa213069(SQL.80).aspx”Autocommit mode is the default transaction management mode of Microsoft® SQL Server™….A SQL Server connection operates in autocommit mode until a BEGIN TRANSACTION statement starts an explicit transaction, or implicit transaction mode is set on.”I hope this helps

mike2008-07-29 18:09:26

“BTW – I don’t really understand where the remark is coming from with regard to “getting paid per article”.Pay no attention to this griefer. It was intended as an insult, as in, padding with useless content (like MS writing OS/2 KLOC’s).

 

Continued here: MySQL: Transactions and Autocommit – O'Reilly Network

MySQL Online Backup – O'Reilly Media

by Lanh on August 9, 2014

There are several methods to do live MySQL database backups. These methods are either storage engine specific (InnoDB hot backup, mysqlhotcopy), or require read locks (mysqldump), or require additional hardware (backup using replication slaves, LVM snapshot). Some of these backup methods can do backups of remote MySQL servers also.  So far, there is no backup  method that will provide storage engine agnostic, consistent full backups of local and remote servers.   Goal of Zmanda Recovery Manager (ZRM) of MySQL is to consolidate all these and future methods of MySQL backup and use the optimal method for the MySQL configuration.One of exciting developments in MySQL is the development of MySQL Online backup. The functional specification for the MySQL Online Backup APIs are available in MySQL forge and currently, the initial implementation of ARCHIVE storage engine backup and recovery is available in the bitkeeper source tree.You can read more about MySQL Online Backup and ZRM for MySQL in our blog.

Read the original post: MySQL Online Backup – O'Reilly Media

No Server Left Behind! (Removal of MySQL Blockers) | cPanel Blog

by Lanh on August 9, 2014

Earlier this year, we implemented a blocker that requires you to upgrade your MySQL server to version 5.5 or later before you can upgrade your cPanel & WHM server to version 11.44.
We are removing the blocker from cPanel & WHM versions 11.40 and later for MySQL versions 5.0 and 5.1 so that you can use the new and improved MySQL update tool that we will release very soon.
This does not mean that cPanel is supporting MySQL 5.0 and 5.1 again – those versions are not receiving security updates and patches from Oracle and are highly insecure. We are removing the blockers for the sole purpose of allowing you to upgrade to cPanel & WHM version 11.44.1.12 in order to use the MySQL update tool that no longer requires upgrading Apache and PHP at the same time.
We currently plan to reinstate the MySQL blockers for cPanel & WHM version 11.46.

Read this article: No Server Left Behind! (Removal of MySQL Blockers) | cPanel Blog

MySQL performance problems on particular vhosts – Server Fault

by Lanh on August 9, 2014

I have a dedicated server setup (8 cores, 16 GB of RAM), running Ubuntu 12.04 LTS with latest updates, and I am experiencing some performance problems related to MySQL. I have multiple vhosts active. Did some basic performance tuning. See below.

Problem
On one vhost, when I import a lot of data in my WordPress site, I see a huge CPU spike going to 100% when running htop. This is not split over the different cores though. I also notice that browsing on the vhost becomes very very slow, I even get some 503 errors, while my other vhosts on the machine are running just fine.

Question
How can I manage to get the speed of the vhost with the problems (and where the script is running on) to an acceptable level while running the script? Could this be resolved with PHP.ini tuning? Please note I am not looking for a solution to adjust the query, so no questions about slow query logs etc, please.

Additional info

#
# The MySQL database server configuration file.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
local-infile=0
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
#tmpdir = /tmp
tmpdir = /run/mysqld
lc-messages-dir = /usr/share/mysql
skip-external-locking

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address = 127.0.0.1
#
# * Fine Tuning
#
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
join_buffer_size = 24M
table_cache = 128K
table_definition_cache = 128K
open_files_limit = 32K

# originals
key_buffer = 384M
max_allowed_packet = 128M
thread_stack = 256K
thread_cache_size = 8K
#low_priority_updates=1
#concurrent_insert=ALWAYS

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
max_connections = 150
thread_concurrency = 16

#
# * Query Cache Configuration
#
query_cache_limit = 2M
query_cache_size = 128M
max_heap_table_size = 4096M
tmp_table_size = 4096M
query_cache_min_res_unit = 512
query_cache_type = 1

#InnoDB Tuning
innodb_buffer_pool_size = 1024M
innodb_thread_concurrency = 16

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error log – should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI “tinyca”.
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

[mysqldump]
quick
quote-names
max_allowed_packet = 32M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer = 512M

#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with ‘.cnf’, otherwise they’ll be ignored.
#
!includedir /etc/mysql/conf.d/

And here is the php.ini of the particular vhost, I changed the open_basedir in this post for security reasons:

; ATTENTION!
;
; DO NOT MODIFY THIS FILE BECAUSE IT WAS GENERATED AUTOMATICALLY,
; SO ALL YOUR CHANGES WILL BE LOST THE NEXT TIME THE FILE IS GENERATED.

[PHP]
soap.wsdl_cache_limit = 5
tidy.clean_output = Off
include_path = “.:”
mysql.allow_persistent = On
mysqli.max_persistent = -1
safe_mode_allowed_env_vars = PHP_
session.bug_compat_42 = Off
mysql.connect_timeout = 60
session.use_only_cookies = 1
register_argc_argv = Off
mssql.min_error_severity = 10
open_basedir = “/var/www/vhosts/domainname-scrambled-for-security-reasons/:/tmp/”
session.name = PHPSESSID
mysqlnd.collect_statistics = On
session.hash_function = 0
magic_quotes_runtime = Off
log_errors_max_len = 1024
mssql.secure_connection = Off
pgsql.max_links = -1
variables_order = “GPCS”
ldap.max_links = -1
sybct.allow_persistent = On
max_input_time = 1200
odbc.max_links = -1
mysqli.default_pw =
session.save_handler = files
mysqli.cache_size = 2000
pgsql.auto_reset_persistent = Off
session.cache_expire = 180
error_reporting = E_ALL & ~E_DEPRECATED
safe_mode_gid = Off
auto_prepend_file =
sybct.min_client_severity = 10
pgsql.max_persistent = -1
auto_globals_jit = On
soap.wsdl_cache_ttl = 86400
allow_url_fopen = On
zend.enable_gc = On
smtp_port = 25
register_globals = Off
display_startup_errors = Off
user_dir =
session.cookie_lifetime = 0
mysqli.max_links = -1
default_socket_timeout = 60
session.serialize_handler = php
session.hash_bits_per_character = 5
unserialize_callback_func =
pdo_mysql.cache_size = 2000
default_mimetype = “text/html”
magic_quotes_gpc = Off
safe_mode_include_dir =
max_execution_time = 1200
mail.add_x_header = On
upload_max_filesize = 256M
ibase.max_links = -1
register_long_arrays = Off
safe_mode = off
zlib.output_compression = Off
safe_mode_protected_env_vars = LD_LIBRARY_PATH
odbc.max_persistent = -1
mssql.compatability_mode = Off
file_uploads = on
ibase.max_persistent = -1
mysqli.reconnect = Off
session.cookie_domain =
mssql.allow_persistent = On
mysql.max_persistent = -1
mssql.max_links = -1
session.use_trans_sid = 0
mysql.default_socket =
mysql.max_links = -1
odbc.defaultbinmode = 1
sybct.max_persistent = -1
output_buffering = 4096
ibase.timeformat = “%H:%M:%S”
doc_root =
log_errors = On
mysql.default_host =
mysqli.default_port = 3306
display_errors = Off
mysqli.default_socket =
safe_mode_exec_dir =
html_errors = Off
session.entropy_length = 0
ibase.allow_persistent = 1
y2k_compliance = On
mysql.allow_local_infile = On
post_max_size = 256M
asp_tags = Off
memory_limit = 4096M
short_open_tag = on
SMTP = localhost
precision = 14
session.bug_compat_warn = Off
sybct.min_server_severity = 10
mssql.min_message_severity = 10
session.gc_maxlifetime = 1440
session.gc_probability = 0
allow_url_include = Off
mysqli.default_host =
mysqli.default_user =
session.referer_check =
pgsql.log_notice = 0
mysql.default_port =
odbc.defaultlrl = 4096
pgsql.ignore_notice = 0
mysql.trace_mode = Off
ibase.timestampformat = “%Y-%m-%d %H:%M:%S”
engine = On
odbc.allow_persistent = On
ibase.dateformat = “%Y-%m-%d”
track_errors = Off
max_file_uploads = 20
pgsql.allow_persistent = On
session.auto_start = 0
auto_append_file =
disable_classes =
pdo_mysql.default_socket =
mysql.default_password =
url_rewriter.tags = “a=href,area=href,frame=src,input=src,form=fakeentry”
allow_call_time_pass_reference = Off
ignore_repeated_source = Off
mysqli.allow_persistent = On
sql.safe_mode = Off
session.cookie_path = /
expose_php = off
report_memleaks = On
session.gc_divisor = 1000
mssql.max_persistent = -1
serialize_precision = 17
odbc.check_persistent = On
sybct.max_links = -1
mysqlnd.collect_memory_statistics = Off
define_syslog_variables = Off
session.cookie_httponly =
session.cache_limiter = nocache
enable_dl = Off
ignore_repeated_errors = Off
request_order = “GP”
disable_functions = pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,
magic_quotes_sybase = Off
soap.wsdl_cache_enabled = 1
soap.wsdl_cache_dir = “/tmp”
session.use_cookies = 1
mysql.default_user =
mysql.cache_size = 4000
implicit_flush = Off
bcmath.scale = 0

Read this article: MySQL performance problems on particular vhosts – Server Fault

pizza recipes free

Changes in MySQL 5.6.20 | Open Source DBA's Blog

by Lanh on August 8, 2014

The MySQL Release Notes should be part of any DBA’s regular reading list. The Changes in MySQL 5.6.20 came out last week and there are some interesting goodies.
MySQL now includes DTrace support on Oracle Linux 6 or higher with UEK kernel.
A new system variable binlog_impossible_mode controls what happens if the server cannot write to the binary log, for example, due to a file error.
The mysqlhotcopy utility is now deprecated and will be removed in a future version of MySQL
5.6.20 has a slew of bug fixes, functionality changes, and notes.
So why should you be reading the changes on a regular basis? There isa goldmine of information in them. For instance, if you use blobs, consider this:
Important Change: Redo log writes for large, externally stored BLOB fields could overwrite the most recent checkpoint. The 5.6.20 patch limits the size of redo log BLOB writes to 10% of the redo log file size. The 5.7.5 patch addresses the bug without imposing a limitation. For MySQL 5.5, the bug remains a known limitation.
As a result of the redo log BLOB write limit introduced for MySQL 5.6, innodb_log_file_size should be set to a value greater than 10 times the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields). Failing to do so could result in “Row size too large” errors. No action is required if your innodb_log_file_size setting is already sufficiently large or your tables contain no BLOB data. (Bug #16963396, Bug #19030353, Bug #69477)
That is golden information for those of us who used a lot of blobs and great info for configuring servers.

About these ads

div { margin-top: 1em; } #google_ads_div_wpcom_below_post_adsafe_ad_container { display: block !important; }
]]>

More: Changes in MySQL 5.6.20 | Open Source DBA's Blog

An approach to MySQL dynamic cross-reference query | Fatal …

by Lanh on August 8, 2014

I’m using Redmine to manage projects and bug reports, I was needing a daily report with spent time by user on each project, nothing complicated, but I wanted a cross-reference result like Excel dynamic tables. As we know, MySQL doesn’t support dynamic cross-reference queries but I make an approach:
1. Write the main query with rows: project identifier, task subject and total hours in a date range.

SELECT p.identifier,
e.issue_id, i.subject, round(SUM(e.hours),2) AS total_hours
FROM redmine_nsp.time_entries e
INNER JOIN redmine_nsp.issues i ON e.issue_id = i.id
INNER JOIN redmine_nsp.projects p ON e.project_id = p.id
WHERE e.spent_on BETWEEN ’2014-07-01′ AND ’2014-07-07′
GROUP BY p.identifier,e.issue_id;

+————+———-+—————————-+————-+
| identifier | issue_id | subject | total_hours |
+————+———-+—————————-+————-+
| bg02 | 3223 | Gestion de proyecto | 0.25 |
| bg04 | 3256 | 1.1 Preparación del entor | 0.63 |
| emision | 3251 | Desarrollar la aplicación | 3.97 |
| nsp00 | 3236 | Preparar propuesta para G | 2.02 |
| nsp02 | 3234 | Subida al servidor de pro | 0.52 |
| nsp02 | 3240 | Agregar funcionalidad de | 0.55 |
| nsp02 | 3241 | Revertir el Documento | 2.80 |
| nsp02 | 3242 | Agregar Filtros en las vi | 0.72 |
| nsp02 | 3243 | Reportes de Cargo de entr | 2.35 |
| nsp02 | 3254 | Control de sesiones | 3.23 |
| nsp05 | 3252 | Mantenimiento del servido | 0.18 |
| nsp05 | 3253 | Mantenimiento a redmine | 0.53 |
| nsp06 | 3203 | Elaborar el visualzador d | 0.23 |
| nsp06 | 3228 | Evitar que se tome mas de | 0.25 |
| nsp06 | 3255 | Actualizar el porcentaje | 1.50 |
| nsp08 | 3239 | Asistencia remota | 1.38 |
+————+———-+—————————-+————-+

2. Then list the active users between dates:

SELECT e.user_id, u.login
FROM redmine_nsp.time_entries e
INNER JOIN redmine_nsp.users u ON e.user_id = u.id
WHERE e.spent_on BETWEEN ’2014-07-01′ AND ’2014-07-07′
GROUP BY e.user_id;

+———+———-+
| user_id | login |
+———+———-+
| 1 | user1 |
| 4 | user2 |
| 5 | user3 |
| 6 | user4 |
+———+———-+

3. Now, create a stored procedure to create a dynamic SQL command with the second query as column names and values with each Redmine issue. The second query is used as cursor.

DELIMITER $$
 
CREATE PROCEDURE `get_range_summary`(dDate1 date,dDate2 date)
BEGIN
 
DECLARE done INT DEFAULT 0;
declare p_user_id int;
declare p_sql text;
declare p_login varchar(255);
declare c_users cursor for
select e.user_id, u.login
from redmine_nsp.time_entries e
inner join redmine_nsp.users u on e.user_id = u.id
where e.spent_on between dDate1 and dDate2
group by e.user_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE ’02000′ SET done = 1;
 
set p_sql = ‘select s.codigo,s.issue_id,s.subject ‘;
open c_users;
 
read_loop: LOOP
FETCH c_users INTO p_user_id, p_login;
IF done THEN
LEAVE read_loop;
END IF;
set p_sql = concat(p_sql,
‘, (select round(sum(t.hours),2) as total
from redmine_nsp.time_entries t
where t.spent_on between ”,dDate1,” and ”,dDate2,”
and t.issue_id = s.issue_id
and t.user_id = ‘,p_user_id,’) as `’,p_login,’` ‘);
 
END LOOP;
 
set @sql = concat(p_sql,’ ,s.total_hours from
(select p.identifier, e.issue_id, i.subject,
round(sum(e.hours),2) as total_hours
from redmine_nsp.time_entries e
inner join redmine_nsp.issues i on e.issue_id = i.id
inner join redmine_nsp.projects p on e.project_id = p.id
where e.spent_on between ”,dDate1,” and ”,dDate2,” group by p.identifier,e.issue_id) as s’);
 
 
close c_users;
 
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
 
END

The result of call get_range_summary(’2014-07-01′,’2014-07-07′):

+———+———-+—————————-+——-+——–+———+———-+————-+
| identif | issue_id | subject | user1 | user2 | user3 | user4 | total_hours |
+———+———-+—————————-+——-+——–+———+———-+————-+
| bg02 | 23 | Gestion de proyecto | NULL | 0.25 | NULL | NULL | 0.25 |
| bg04 | 56 | 1.1 Preparación del entor | NULL | 0.63 | NULL | NULL | 0.63 |
| emision | 51 | Desarrollar la aplicación | NULL | NULL | 3.97 | NULL | 3.97 |
| nsp00 | 36 | Preparar propuesta para G | NULL | 2.02 | NULL | NULL | 2.02 |
| nsp02 | 34 | Subida al servidor de pro | NULL | 0.52 | NULL | NULL | 0.52 |
| nsp02 | 40 | Agregar funcionalidad de | NULL | NULL | 0.55 | NULL | 0.55 |
| nsp02 | 41 | Revertir el Documento | 1.40 | NULL | 1.40 | NULL | 2.80 |
| nsp02 | 42 | Agregar Filtros en las vi | NULL | NULL | 0.72 | NULL | 0.72 |
| nsp02 | 43 | Reportes de Cargo de entr | NULL | NULL | NULL | 2.35 | 2.35 |
| nsp02 | 54 | Control de sesiones | NULL | NULL | NULL | 3.23 | 3.23 |
| nsp05 | 52 | Mantenimiento del servidor | NULL | 0.18 | NULL | NULL | 0.18 |
| nsp05 | 53 | Mantenimiento a redmine | NULL | 0.53 | NULL | NULL | 0.53 |
| nsp06 | 3 | Elaborar el visualzador de | NULL | 0.23 | NULL | NULL | 0.23 |
| nsp06 | 28 | Evitar que se tome mas de | NULL | 0.25 | NULL | NULL | 0.25 |
| nsp06 | 55 | Actualizar el porcentaje d | NULL | 1.50 | NULL | NULL | 1.50 |
| nsp08 | 39 | Asistencia remota | NULL | 1.38 | NULL | NULL | 1.38 |
+———+———-+—————————-+——-+——–+———+———-+————-+

That’s it. I put the result easily in a php based report and added it to cron task.

See more here: An approach to MySQL dynamic cross-reference query | Fatal …

healthy recipes free app

MySQL backups using ZFS snapshot – O'Reilly Network Articles

by Lanh on August 8, 2014

One of the significant features in version 2.0 of Zmanda Recovery Manager for MySQL is MySQL backups using Solaris ZFS. Doing MySQL backups usingfilesystem snapshots has minimal impact on the MySQL databases. The MySQL databases are not available for updates for less than a second and the application impact is not dependent on the size of the database.ZRM 2.0 can be downloaded from Zmanda downloads page. It supports all Linux and Solaris distributions. The documentation is available on ZRM wiki.ZRM forums can be used to get questions answered about the project.This article shows an example of how to install, configure, backup and restore MySQL databases using Zmanda Recovery Manager (ZRM) for MySQL on OpenSolaris. The example takes advantage of ZFS snapshots to do full backups.

Visit link: MySQL backups using ZFS snapshot – O'Reilly Network Articles

pizza recipes free

MySQL 5.6.17 Overview and Highlights « ChriSQL – Chris on …

by Lanh on August 7, 2014

MySQL 5.6.17 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here:
For this release, I counted 7 “Functionality Added” and/or “Incompatible Change” fixes:
Incompatible Change: The AES_ENCRYPT() and AES_DECRYPT() functions now permit control of the block encryption mode and take an optional initialization vector argument.
Incompatible Change: The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE SQL modes now are deprecated and setting the sql_mode value to include any of them generates a warning. In MySQL 5.7, these modes do nothing. Instead, their effects are included in the effects of strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES).
InnoDB: MySQL now supports rebuilding regular and partitioned InnoDB tables using online DDL (ALGORITHM=INPLACE) for the following operations: OPTIMIZE TABLE, ALTER TABLE … FORCE, and ALTER TABLE … ENGINE=INNODB
On Solaris, mysql_config –libs now includes -R/path/to/library so that libraries can be found at runtime.
mysql_install_db provides a more informative diagnostic message when required Perl modules are missing.
The IGNORE clause for ALTER TABLE is now deprecated and will be removed in a future version of MySQL. ALTER IGNORE TABLE causes problems for replication, prevents online ALTER TABLE for unique index creation, and causes problems with foreign keys (rows removed in the parent table).
Incompatible Change: Old clients (older than MySQL 5.5.7) failed to parse authentication data correctly if the server was started with the –default-authentication-plugin=sha256_password option.
In addition to those, there were 60 other bug fixes:
21 InnoDB
13 Replication
01 Partitioning
25 Miscellaneous
There were 2 regression bugs fixed, so check if they might have affected you, and if so, then you’ll want to consider upgrading (*especially* note the 1st since it’s InnoDB and performance degradation)
InnoDB: A regression introduced by Bug #14329288 (Oracle-internal) would result in a performance degradation when a compressed table does not fit into memory. (Bug #71436)
Building MySQL from source on Windows using Visual Studio 2008 would fail with an identifier not found error due to a regression introduced by the patch for Bug #16249481 and Bug #18057449 (both Oracle-internal bugs).
Conclusions:
If you use AES_ENCRYPT() and AES_DECRYPT(), I’d definitely investigate the changes, and plan for an upgrade (but test/check first and make and necessary changes first).
Since there were 21 InnoDB bugs fixed, including 1 regression bug, I would recommend upgrading if you’re running a previous 5.6 version.
Similarly, since there were 13 replication bugs, if you have a replication setup, I’d also plan to upgrade to take advantage of the fixes (and not be affected by the bugs).
The full changelogs can be viewed here (which has more details about all of the bugs listed above):

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-17.html

Hope this helps.

Tags: AES_DECRYPT(), AES_ENCRYPT(), ALGORITHM=INPLACE, download mysql 5.6, ERROR_FOR_DIVISION_BY_ZERO, innodb bugs, InnoDb regression bugs, MySQL, MySQL 5.6, mysql 5.6 download, mysql 5.6.17, mysql changelogs, mysql downloads, mysql upgrade recommendations, NO_ZERO_DATE, NO_ZERO_IN_DATE, replication bugs

This entry was posted
on Thursday, April 10th, 2014 at 7:01 pm and is filed under MariaDB, MySQL.
You can follow any responses to this entry through the RSS 2.0 feed.

Both comments and pings are currently closed.

See more here: MySQL 5.6.17 Overview and Highlights « ChriSQL – Chris on …

MySQL Fabric/MySQL Utilities 1.4.4 released | Andrew Morgan on …

by Lanh on August 7, 2014

The binary and source versions of MySQL Utilities/MySQL Fabric have now been made available at http://dev.mysql.com/downloads/utilities/.
This release contains bug fixes and minor enhancements – full details can be found in the MySQL Fabric/MySQL Utilities release notes.

Visit link: MySQL Fabric/MySQL Utilities 1.4.4 released | Andrew Morgan on …

car insurance phone app

Introducing the MySQL Sandbox – O'Reilly Media

by Lanh on August 7, 2014

This documentation is OBSOLETE.Please refer to https://launchpad.net/mysql-sandbox for up-to date material.Installing a side instance of MySQL for testing purpose is a task that many administrators can perform without breaking a sweat. If you need to do that only once in a while, you need just to read the manual carefully, or to have some experience in this matter, and the task is accomplished quite easily.If, however, your skills are below the Guru level, even to get this task done just once you may find yourself in trouble. And, let’s face it, even experienced administrators, when they need to do this several times, with different versions of MySQL, may have trouble doing it right. It would be nice to have a tool that takes care of the dirty details for you and gets the job done quietly, without interfering with existing installations, and without side effects.Such a tool exists, it’s The MySQL Sandbox. It is a framework for testing features under any version of MySQL from 3.23 to 5.1. Whitout fuss, it will install one server under your home directory, and it will provide some useful commands to start and stop it, and to use it within the sandbox.There are many reasons for installing a side server. One is testing a potentially dangerous application, and you don’t want to try it on a production server. Another reason is to try different versions of MySQL on a piece of code when hunting a bug. Or you are a consultant, your customers are all using different versions of the DBMS, and you need to test your procedures in an environment that is as close as possible to the your clients are using. I don’t know about you, but in my job I have all the above needs, sometimes all at once.

Imran Chaudhry2006-05-26 04:25:00

Bonjourno Guiseppe, I just tried out the sandbox on a machine wrunning 4.0 in production and 5.0.21 in the sandbox. It worked like a charm (would we expect no less from a “datacharmer”?).One thing I noticed that you might want to change in 1.6 is that in the default options file you have named the sandbox_directory to ‘mysql_sandbox5_0′ which is appended to ‘install_version’ of 5.0 to make a resultant dir called ‘mysql_sandbox5_05.0′Like you, I do MySQL consultantcy gigs and this is a useful tool to have.Regards,Imran Chaudhrywww.atomdatabase.com

Michael Shapiro2006-06-20 08:03:33

Thanks for infromation. I am just looking for such a tool to make a safe debugging.

Jacqueline2006-08-02 09:33:57

One of the cases that may happen, but only in some Linux distributions, is that a old version sandbox will complain about something along the lines of not finding a file that actually exists. Good!!

Gerd Rein2006-10-19 11:12:26

Well Guiseppe, greetings from Sweden. I just downloaded your tutorial on the sandbox and was hot and ready to do som serious studying, but I got as far as $ ./install.pl and there the fun ended. I could not get that install file to work no matter what. When I use run install.pl in WindowsXp it opens the file as notebook file. Now that helps me a lot. The info file also states just run the file, but not how! I hope someone has an answer for me, I am aware that once you know how it is very simple.Best regards, Gerd Rein.

Giuseppe2006-10-19 11:45:42

Gerd,Greetings from Sardinia, Italy.the requirements say that you need a Unix box for the Sandbox.It does not work on Windows.CheersGiuseppeP.S. It’s Giuseppe, not Guiseppe
Vic T2007-04-10 23:50:53

This is great stuff.One very useful application that I can see (and will try soon) is to put the sandbox on another physical hard drive, and run the sandbox as a MySQL slave of the production dB on the primary drive.
lynn2008-05-08 04:37:02

I just cant get the ./start_replication working well.It keeps telling me the following errors.=============installing and starting masterCan’t use string (“server-id=1″) as an ARRAY ref while “strict refs” in use at ./install.pl line 506.error installing the masterunpacking /home/testuser/mysqlpackages/mysql-5.1.24-rc-freebsd6.0-x86_64.tar.gzExecuting ./install.pl –basedir=/home/testuser/mysqlpackages/5.1.24 –sandbox_directory=msb_5_1_24 –install_version=5.1 –sandbox_port=5124 –no_ver_after_name –datadir_from=script –no_confirm –home_directory=/home/testuser/rsandbox_5_1_24 –sandbox_directory=master –sandbox_port=18539 –load_grants –prompt_prefix=master -c log-bin=mysql-bin -c server-id=1===========The directory for replication is created but nothing inside.Can anyone please advise?Thank you.

Originally posted here: Introducing the MySQL Sandbox – O'Reilly Media

diet plans android