Re: Examples of savepoints and transactions
Den 22-01-2018 kl. 22:01 skrev shawn l.green: Hello Lars, On 1/21/2018 3:37 PM, Lars Nielsen wrote: Hi, I have a system that uses begin and commit transactions. It works like a dream! ;) Now I want to test it by creating test data. This how ever cannot be rolled back. I think the solution for rolling back test data is to use savepoints and rollback. I think it is hard to find examples of this scenario. Are there some good guides or tutorials out there somewhere? Any suggestions are welcome. Best regards Lars Nielsen Can you mock up an example (a simple text walkthrough) of how you think a savepoint should work with what you are calling "test data" ? I think that the term "test data" is too general to make much sense to most of us in the context you described. Yours, Hello Shawn, Thanks for your interest. Here is an example of my idea. I have a php site working through PDO connections. I insert some data through php like this : |START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x, y, z); COMMIT; ||| ||Now I want to do automated tests that create "dummy" data that i want to remove after the test has finished: like this : |SAVEPOINT autotest1; START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x, y, z); COMMIT; -- DO OTHER TRANSACTIONAL OPERATIONS ROLLBACK TO autotest1; ||| ||All done. I have tested the application and have cleaned up the dummy test-data. The issue is that when I call the first commit then the savepoint is deleted. Is this possible at all? Regards Lars ||
MySQL Community Server 8.0.3-rc has been released (part 3/3)
* For builds on 32-bit platforms with Undefined Behavior Sanitizer enabled, a stack-overrun check could cause a server exit. (Bug #27224961) * The server could hang during spatial reference system (SRS) creation while another session was using that SRS. (Bug #27220467) * A lock for the privilege cache was acquired unnecessarily during privilege-checking operations not involving table permissions. (Bug #27197483) * Persisting the read-only gtid_owned or gtid_executed system variable caused an assertion failure at server startup. These variables can no longer be persisted. (Bug #27193853) * Improper handling of plugin loading and unloading could cause a server exit. (Bug #27151550, Bug #88589, Bug #27116827, Bug #88483) * Error propagation from some windowing functions was not always performed correctly. (Bug #27135084, Bug #27136492) * Negation of some very large values was not handled correctly by an internal function. (Bug #27134168) * Instituted stricter checks when performing addition involving date intervals. (Bug #27134148) * Recently introduced SRID and COLUMN_STATISTICS metadata locks were not instrumented by the Performance Schema. (Bug #27124506) * The name of a derived table was not saved before the table was materialized and assigned the name of the temporary table. Later, when trying to reset the table name, this caused the server to fail due to the missing reference to the original value of the name. (Bug #27121663) * Performance Schema queries that used indexes on OBJECT_TYPE columns could return incorrect results. (Bug #27121500) * Compiling with -DWITH_ASAN=1 and -DWITH_ASAN_SCOPE=1 detected a stack-use-after-scope memory error. (Bug #27108794, Bug #88460) * FILE privilege checking for prepared SELECT ... INTO OUTFILE statements was incorrect. (Bug #27094955) * Some messages with note priority were written to the error log when log_error_verbosity was less than 3. (Bug #27082862) * The ha_create_table_from_engine function failed to pass a table object to the ha_create routine. (Bug #27066335) * Partition by and order by elements of unused window definitions were not included when estimating memory requirements. This is resolved by assigning a parsing context (CTX_WINDOW), but only when the current context is CTX_NONE. As part of this fix, unused window definitions are now removed after being checked for syntax and semantic errors. (Bug #27062031) * GROUP BY with a ROLLUP that generated NULL was not handled correctly. (Bug #27060420) * An Event Scheduler event for which global autocommit was disabled at event expiration time caused an assertion to be raised. (Bug #27041552, Bug #88255) * Length calculations for string-valued user-defined variables could be incorrect if the collation was changed. (Bug #27041543, Bug #88256) * CREATE TABLE ... SELECT statements that attempted to create a non-InnoDB table raised an assertion if a pre-existing view referenced the table to be created. (Bug #27041536, Bug #88258) * When used as an argument to the IF() function, the value of a TIMESTAMP column could be handled differently for different collations. (Bug #27041526, Bug #88259) * With statement-based binary logging, using CREATE TABLE ... SELECT to create a BLACKHOLE table caused an assertion to be raised. (Bug #27041516, Bug #88260) * For debug builds, a TIMESTAMP-related assertion could be raised with explicit_defaults_for_timestamp enabled. (Bug #27041502, Bug #88261) * Under LOCK TABLES, an attempt to execute a DML statement on a table with foreign keys led to assertion failure if the statement was incompatible with the mode under which the tables in the foreign key relationship were locked. (Bug #27041477, Bug #88264) * With a LOCK TABLES statement active, queries that select from the INFORMATION_SCHEMA.FILES table could raise an assertion trying to obtain a metadata lock. (Bug #27041452, Bug #88266) * Server component installation did not properly perform auto-increment handling, which could result in a server exit. (Bug #27041374, Bug #88276) * With big_tables=1 and character_set_connection=ucs2, SHOW TABLE STATUS could raise an assertion. (Bug #27041323, Bug #88279) * With a backup lock active, removal of binary log files and relay log files incorrectly was permitted. (Bug #27030339) * When evaluating an end-range condition in a scan of a non-covering secondary index, and the end-rang
MySQL Community Server 8.0.4-rc has been released (part 3/3)
[If you just recived an email with 8.0.3-rc in the title, please ignore that. Sorry for the confusion] * For builds on 32-bit platforms with Undefined Behavior Sanitizer enabled, a stack-overrun check could cause a server exit. (Bug #27224961) * The server could hang during spatial reference system (SRS) creation while another session was using that SRS. (Bug #27220467) * A lock for the privilege cache was acquired unnecessarily during privilege-checking operations not involving table permissions. (Bug #27197483) * Persisting the read-only gtid_owned or gtid_executed system variable caused an assertion failure at server startup. These variables can no longer be persisted. (Bug #27193853) * Improper handling of plugin loading and unloading could cause a server exit. (Bug #27151550, Bug #88589, Bug #27116827, Bug #88483) * Error propagation from some windowing functions was not always performed correctly. (Bug #27135084, Bug #27136492) * Negation of some very large values was not handled correctly by an internal function. (Bug #27134168) * Instituted stricter checks when performing addition involving date intervals. (Bug #27134148) * Recently introduced SRID and COLUMN_STATISTICS metadata locks were not instrumented by the Performance Schema. (Bug #27124506) * The name of a derived table was not saved before the table was materialized and assigned the name of the temporary table. Later, when trying to reset the table name, this caused the server to fail due to the missing reference to the original value of the name. (Bug #27121663) * Performance Schema queries that used indexes on OBJECT_TYPE columns could return incorrect results. (Bug #27121500) * Compiling with -DWITH_ASAN=1 and -DWITH_ASAN_SCOPE=1 detected a stack-use-after-scope memory error. (Bug #27108794, Bug #88460) * FILE privilege checking for prepared SELECT ... INTO OUTFILE statements was incorrect. (Bug #27094955) * Some messages with note priority were written to the error log when log_error_verbosity was less than 3. (Bug #27082862) * The ha_create_table_from_engine function failed to pass a table object to the ha_create routine. (Bug #27066335) * Partition by and order by elements of unused window definitions were not included when estimating memory requirements. This is resolved by assigning a parsing context (CTX_WINDOW), but only when the current context is CTX_NONE. As part of this fix, unused window definitions are now removed after being checked for syntax and semantic errors. (Bug #27062031) * GROUP BY with a ROLLUP that generated NULL was not handled correctly. (Bug #27060420) * An Event Scheduler event for which global autocommit was disabled at event expiration time caused an assertion to be raised. (Bug #27041552, Bug #88255) * Length calculations for string-valued user-defined variables could be incorrect if the collation was changed. (Bug #27041543, Bug #88256) * CREATE TABLE ... SELECT statements that attempted to create a non-InnoDB table raised an assertion if a pre-existing view referenced the table to be created. (Bug #27041536, Bug #88258) * When used as an argument to the IF() function, the value of a TIMESTAMP column could be handled differently for different collations. (Bug #27041526, Bug #88259) * With statement-based binary logging, using CREATE TABLE ... SELECT to create a BLACKHOLE table caused an assertion to be raised. (Bug #27041516, Bug #88260) * For debug builds, a TIMESTAMP-related assertion could be raised with explicit_defaults_for_timestamp enabled. (Bug #27041502, Bug #88261) * Under LOCK TABLES, an attempt to execute a DML statement on a table with foreign keys led to assertion failure if the statement was incompatible with the mode under which the tables in the foreign key relationship were locked. (Bug #27041477, Bug #88264) * With a LOCK TABLES statement active, queries that select from the INFORMATION_SCHEMA.FILES table could raise an assertion trying to obtain a metadata lock. (Bug #27041452, Bug #88266) * Server component installation did not properly perform auto-increment handling, which could result in a server exit. (Bug #27041374, Bug #88276) * With big_tables=1 and character_set_connection=ucs2, SHOW TABLE STATUS could raise an assertion. (Bug #27041323, Bug #88279) * With a backup lock active, removal of binary log files and relay log files incorrectly was permitted. (Bug #27030339) * Whe
MySQL Community Server 8.0.4-rc has been released (part 2/3)
Bugs Fixed
* Important Change: The following changes are made to the
PERIOD_ADD() and PERIOD_DIFF() functions:
+ A period value used with one of these functions may
not be negative.
+ The month part of a period value may not be equal to
0.
A period value used with one of these functions for which
at least one of these conditions is true now causes the
function to fail with an error. (Bug #27004699, Bug
#27004729)
* Important Change: The LEAST() and GREATEST() functions no
longer attempt to infer a context for their arguments
from expressions in which they are used. For example,
LEAST('11', '45', '2') returns '11', but LEAST('11',
'45', '2') + 0 treated the function arguments as integers
rather than as strings, and returned 2. Now these
functions always evaluate their arguments strictly
according to type, and any data type coercion due to
their inclusion in an expression is performed only on the
result returned by the function. This means that the
expression LEAST('11', '45', '2') + 0 now evaluates to
'11' + 0, and thus to the integer value 11.
This change has been made due to the following
considerations:
+ Rules for deriving the context were not always clear
or consistent.
+ The results of these functions when used in
expressions were not consistent with the results of
COALESCE(), or of a UNION query.
Applications that use these functions within expressions
should be checked to make sure that they do not depend on
the previous behavior, and updated if they do so. (Bug
#83895, Bug #25123839)
* InnoDB: An ALTER TABLE operation that added a foreign key
constraint referencing a table with generated virtual
columns raised an assertion. (Bug #27189701)
* InnoDB: Concurrent XA transactions that ran successfully
to the XA prepare stage on the master conflicted when
replayed on the slave, resulting in a lock wait timeout
in the applier thread. The conflict was due to the GAP
lock range which differed when the transactions were
replayed serially on the slave. To prevent this type of
conflict, GAP locks taken by XA transactions in READ
COMMITTED isolation level are now released (and no longer
inherited) when XA transactions reach the prepare stage.
(Bug #27189701, Bug #25866046)
* InnoDB: A DROP DATABASE operation raised an assertion due
to a missing general tablespace data file. (Bug
#27151163)
* InnoDB: On Windows, an operation that altered a table
partition raised an assertion. The table name was not
parsed correctly. (Bug #27075816)
* InnoDB: A TRUNCATE TABLE operation on a temporary table
raised an assertion. (Bug #27073280)
* InnoDB: A call to a recovery-related function during the
post-DDL phase of a DDL operation raised an assertion.
(Bug #27041487, Bug #88263)
* InnoDB: A table with a 64-character foreign key name
caused an upgrade failure. Foreign key names up to 64
characters in length should be permitted. (Bug #27014308,
Bug #88196)
* InnoDB: The InnoDB recovery process failed with a
tablespace size error for a compressed table that was
upgraded from MySQL 5.7 to MySQL 8.0.
The tablespace file for a compressed table is now created
using the physical page size instead of the InnoDB page
size, which makes the initial size of a tablespace file
for an empty compressed table smaller than in previous
MySQL releases. (Bug #27014083, Bug #88195)
* InnoDB: A typo was corrected in an InnoDB recovery
message. Thanks to Daniël van Eeden for the patch. (Bug
#27010613, Bug #88185)
* InnoDB: An orphan .frm file caused an upgrade failure,
and subsequent upgrade attempts were unsuccessful due to
a full-text search auxiliary table that was renamed
during the first upgrade attempt. (Bug #26995951)
* InnoDB: Unnecessary tablespace fetch and cache update
operations caused a server startup delay. (Bug #26995951)
References: This issue is a regression of: Bug #26832347.
* InnoDB: Workarounds introduced to address conflicting
serialized dictionary information (SDI) inserts during
concurrent CREATE TABLE operations were removed. (Bug
#26995534)
References: See also: Bug #26539665.
* InnoDB: A "no space left on device" error reported an
invalid error message. (Bug #26960345)
* InnoDB: During a fast shutdown, InnoDB attempted to write
dynamic metadata to the data dictionary after files were
closed, resulting in an initialization failure due
pending I/O on the data dictionary tablespace
MySQL Community Server 8.0.4-rc has been released (part 1/3)
[Due to size limitations, the announcement is split in 3. This is part 1.] MySQL Server 8.0.4-rc (Release Candidate) is a new version of the world's most popular open source database. This is the second release candidate of MySQL 8.0. 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 8.0.4-rc includes all features in MySQL 5.7. For information on installing MySQL 8.0.4-rc on new servers, please see the MySQL installation documentation at http://dev.mysql.com/doc/refman/8.0/en/installing.html MySQL Server 8.0.4-rc 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/ MySQL Server 8.0.4-rc is also available from our repository for Linux platforms, go here for details: http://dev.mysql.com/downloads/repo/ We welcome and appreciate your feedback, bug reports, bug fixes, patches, etc.: http://bugs.mysql.com/report.php The following link lists the changes in the MySQL 8.0 since the release of MySQL 8.0.3. It may also be viewed online at http://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-4.html Enjoy! Changes in MySQL 8.0.4 (2018-01-23, Release Candidate) Note This is a milestone release, for use at your own risk. Upgrades between milestone releases (or from a milestone release to a GA release) are not supported. 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. (Making a backup before the upgrade is a prudent precaution in any case.) Beginning with MySQL 8.0.4, macOS 10.13 is a supported platform. Also beginning with MySQL 8.0.4, Ubuntu 14.04 and Debian 8 are no longer supported platforms for MySQL 8.0. * Compilation Notes * Component Notes * Configuration Notes * Deprecation and Removal Notes * Function Notes * InnoDB Notes * Installation Notes * Logging Notes * Packaging Notes * Performance Schema Notes * Security Notes * Server Administration * Spatial Data Support * Test Suite Notes * X Plugin Notes * Functionality Added or Changed * Bugs Fixed Compilation Notes * Linux: Binary packages on Linux platforms now are built using GCC 6. The optimization level has changed from -O3 to -O2. * macOS; Microsoft Windows: For compiling MySQL from source, the -DWITH_SSL=system CMake option now works on Windows and macOS. (Bug #26907731, Bug #87938) * On platforms for which the GNU gold linker is used, removal of unused functions is now enabled, reducing the size of binaries. (Bug #26612067, Bug #87372) * #include directives in source files were rewritten and reorganized to be unambiguous. (Bug #26597243, Bug #87358, Bug #26897738) * The BUILD directory containing compilation scripts is no longer maintained and has been removed from MySQL source trees. (Bug #26576219, Bug #87323) * The minimum version of the Boost library for server builds is now 1.65.0. (Bug #26574924, Bug #87317) * MySQL can now be linked against OpenSSL 1.1 on Unix and Unix-like systems. (Bug #25094892, Bug #83814) * Work was done to clean up the source code base, including: Removing unneeded CMake checks; removing unused macros from source files; reorganizing header files to reduce the number of dependencies and make them more modular, removing function declarations without definitions, replacing locally written functions with equivalent functions from industry-standard libraries. Component Notes * The validate_password plugin has been reimplemented to use the server component infrastructure. To install and uninstall the validate_password component, use these statements: INSTALL COMPONENT 'file://component_validate_password'; UNINSTALL COMPONENT 'file://component_validate_password'; INSTALL PLUGIN and UNINSTALL PLUGIN still work to install and uninstall the validate_password plugin as before, but generate warnings. The plugin form of validate_password is deprecated and will be removed in a future version of MySQL. MySQL installations that use the plugin should transition to the component instead. See Transitioning to the Password Validation Component (http://dev.mysql.com/doc/refman/8.0/en/validate-password-transitioning.html).
MySQL Cluster 7.4.18 has been released
Dear MySQL Users, MySQL Cluster is the distributed, shared-nothing variant of MySQL. This storage engine provides: - In-Memory storage - Real-time performance - Transparent Auto-Sharding - Read & write scalability - Active-Active/Multi-Master geographic replication - 99.999% High Availability with no single point of failure and on-line maintenance - NoSQL and SQL APIs (including C++, Java, http, Memcached and JavaScript/Node.js) MySQL Cluster 7.4 makes significant advances in performance; operational efficiency (such as enhanced reporting and faster restarts and upgrades) and conflict detection and resolution for active-active replication between MySQL Clusters. MySQL Cluster 7.4.18, has been released and can be downloaded from http://www.mysql.com/downloads/cluster/ where you will also find Quick Start guides to help you get your first MySQL Cluster database up and running. The release notes are available from http://dev.mysql.com/doc/relnotes/mysql-cluster/7.4/en/index.html MySQL Cluster enables users to meet the database challenges of next generation web, cloud, and communications services with uncompromising scalability, uptime and agility. More details can be found at http://www.mysql.com/products/cluster/ Enjoy ! Changes in MySQL NDB Cluster 7.4.18 (5.6.39-ndb-7.4.18) (2018-01-17, General Availability) MySQL NDB Cluster 7.4.18 was replaced following release by NDB 7.4.19. Users of NDB 7.4.17 and previous NDB 7.4 releases should upgrade directly to MySQL NDB Cluster 7.4.19 or later. For changes that originally appeared in NDB 7.4.18, see Section Section, "Changes in MySQL NDB Cluster 7.4.19 (5.6.39-ndb-7.4.19) (2018-01-23, General Availability)." -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL Cluster 7.4.19 has been released
Dear MySQL Users, MySQL Cluster is the distributed, shared-nothing variant of MySQL. This storage engine provides: - In-Memory storage - Real-time performance - Transparent Auto-Sharding - Read & write scalability - Active-Active/Multi-Master geographic replication - 99.999% High Availability with no single point of failure and on-line maintenance - NoSQL and SQL APIs (including C++, Java, http, Memcached and JavaScript/Node.js) MySQL Cluster 7.4 makes significant advances in performance; operational efficiency (such as enhanced reporting and faster restarts and upgrades) and conflict detection and resolution for active-active replication between MySQL Clusters. MySQL Cluster 7.4.19, has been released and can be downloaded from http://www.mysql.com/downloads/cluster/ where you will also find Quick Start guides to help you get your first MySQL Cluster database up and running. The release notes are available from http://dev.mysql.com/doc/relnotes/mysql-cluster/7.4/en/index.html MySQL Cluster enables users to meet the database challenges of next generation web, cloud, and communications services with uncompromising scalability, uptime and agility. More details can be found at http://www.mysql.com/products/cluster/ Enjoy ! Changes in MySQL NDB Cluster 7.4.19 (5.6.39-ndb-7.4.19) (2018-01-23, General Availability) MySQL NDB Cluster 7.4.19 is a new release of MySQL NDB Cluster 7.4, based on MySQL Server 5.6 and including features in version 7.4 of the NDB storage engine, as well as fixing recently discovered bugs in previous NDB Cluster releases. NDB 7.4.19 replaces the NDB 7.4.18 release, and is the successor to NDB 7.4.17. Users of NDB 7.4.17 and previous NDB 7.4 releases should upgrade directly to MySQL NDB Cluster 7.4.19 or newer. Obtaining MySQL NDB Cluster 7.4. MySQL NDB Cluster 7.4 source code and binaries can be obtained from http://dev.mysql.com/downloads/cluster/. For an overview of changes made in MySQL NDB Cluster 7.4, see What is New in NDB Cluster 7.4 (http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-what-is-new-7-4.html). This release also incorporates all bug fixes and changes made in previous NDB Cluster releases (including the NDB 7.4.18 release which this release replaces), as well as all bug fixes and feature changes which were added in mainline MySQL 5.6 through MySQL 5.6.39 (see Changes in MySQL 5.6.39 (2018-01-15, General Availability) (http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-39.html)). Bugs Fixed * NDB Replication: On an SQL node not being used for a replication channel with sql_log_bin=0 it was possible after creating and populating an NDB table for a table map event to be written to the binary log for the created table with no corresponding row events. This led to problems when this log was later used by a slave cluster replicating from the mysqld where this table was created. Fixed this by adding support for maintaining a cumulative any_value bitmap for global checkpoint event operations that represents bits set consistently for all rows of a specific table in a given epoch, and by adding a check to determine whether all operations (rows) for a specific table are all marked as NOLOGGING, to prevent the addition of this table to the Table_map held by the binlog injector. As part of this fix, the NDB API adds a new getNextEventOpInEpoch3() method which provides information about any AnyValue received by making it possible to retrieve the cumulative any_value bitmap. (Bug #26333981) * A query against the INFORMATION_SCHEMA.FILES table returned no results when it included an ORDER BY clause. (Bug #26877788) * During a restart, DBLQH loads redo log part metadata for each redo log part it manages, from one or more redo log files. Since each file has a limited capacity for metadata, the number of files which must be consulted depends on the size of the redo log part. These files are opened, read, and closed sequentially, but the closing of one file occurs concurrently with the opening of the next. In cases where closing of the file was slow, it was possible for more than 4 files per redo log part to be open concurrently; since these files were opened using the OM_WRITE_BUFFER option, more than 4 chunks of write buffer were allocated per part in such cases. The write buffer pool is not unlimited; if all redo log parts were in a similar state, the pool was exhausted, causing the data node to shut down. This issue is resolved by avoiding the use of OM_WRITE_BUFFER during metadata reload, so that any transient opening of more than 4 redo log files per log file part no longer leads
