Home Email Blogger Com Sign in Page

Blogger Com Sign in Page

Switching Database Server Timezone to UTC

by stacy

Since the beginning of Signeasy in 2010, the database that has been used to store user and signature records and power our backend services has been MySQL on Amazon Relational Database Service (RDS). This database has been powered by MySQL.

Each table in the database contains datetime columns that are significant (created time and modified time). It is essential to keep track of the dates and times of events such as document uploads, template uploads, signatures, and requests for signatures in order to determine the status of the documents and the amount of time it takes to complete the process of turning them around with completed signatures.

The problem

Historically speaking, these datetime columns were of the type DATETIME, and the values that were stored in them were in the US/Pacific time zone. To put it another way, the timezone of the RDS database instance was configured to US/Pacific. This quickly became a problem as our user base expanded all over the world over the course of the years, and time conversions that were specific to each user’s region needed to be managed through code in order to avoid confusion. The circumstances were not perfect in any way.

In order to display the datetime for a record in the user’s local timezone, we had to convert the datetime value for the record from US/Pacific to UTC using MySQL queries, and then return the UTC timestamp value to the web and mobile applications. This allowed us to display the datetime in the user’s local timezone. Any new code that was written in the corresponding services was required to have the timezone conversion in queries from US/Pacific to UTC. This was necessary because clients take the datetime values in UTC so that they can be shown in the user’s local timezone.

In addition, on Signeasy’s microservices architecture with service-level schema, we have some databases in certain services that used to store datetime values as UTC, despite the fact that the MySQL database instance timezone was set to US/Pacific. This was a problem because it caused datetime values to be incorrectly interpreted. Because of this, we were unable to use some MySQL methods, such as unix timestamp(), because the server understands datetime as a value in the session or global time zone and then converts it to an internal Unix timestamp value in UTC.

It came to our attention that the values for date and time were not uniform throughout the databases. The pace at which developers worked was significantly slowed down as a result of inconsistencies in the database information. This technological debt was progressively rising in massive proportions, and it was getting harder to live with and scale as it reached these levels.

When you first tackle a problem of this magnitude as an engineer, it may appear to be very straightforward, but as you go through the solution, you may find that it presents you with unexpected problems and roadblocks. This project was no different in that regard.

Mitigation plan

Since the datetime values that were being stored in the columns of the database tables were of type DATETIME, we were faced with the challenge of determining how best to convert the datetime values to UTC. There was no easy way to move these datetime data because the Datetime column type in MySQL does not convey information about the time zone. The tables were migrated, and the DATETIME column was converted to a TIMESTAMP type column as part of our solution to this issue.

The initial solution: Percona toolkit to the rescue

When the TIMESTAMP value is being stored in MySQL, it is first converted from the current timezone to UTC, and then when it is being retrieved, it is converted back from UTC to the current timezone.
The addition of a TIMESTAMP column does not need us to make quick changes to our code in order to eliminate the need for timezone conversion in queries.
We came to the conclusion that if we were to successfully migrate the tables to have the TIMESTAMP column type, then all that would be required of us is to change the Timezone of the database to UTC, and then we could release the application code without any explicit timezone conversion in the queries.

Although at first glance it appeared to be a simple process, we wanted this migration to take place not just for one table but rather for a number of different tables and schemas. The majority of the tables contained millions of rows, which contributed to the complexity of the situation and might lengthen the amount of time it took to execute queries.

Altering a column with MySQL’s ALTER command will cause the database to modify the table with the COPY algorithm rather than the INPLACE algorithm (Reason: you cannot change column datatype INPLACE). The MySQL COPY method first creates a temporary table with an updated schema, then transfers the data from the old table to the new table, swaps the tables, and finally drops the old table. All of this occurs before dropping the old table. This requires the table to be locked for writing during the duration of the query’s execution (which can be in hours). ALGORITHM=COPY is an expensive operation since it prevents concurrent DML operations from being performed, but when LOCK=SHARED is used, it enables concurrent read queries. When the lock mode LOCK=EXCLUSIVE is employed, both reads and writes to the database are halted until the alter query has been completely processed.

To put it another way, the ALTER query migration would have required a large amount of downtime, which we could not afford. We wanted a strategy that would bring the amount of downtime that the migration process experienced to a minimum.

Note that MySQL 5.7 was used during the testing and completion of this database conversion. Click here to read more about online DDL operations.

The first solution was to use the Percona toolset, which was successful.
Then, Percona released their pt-online-schema-change programme. It makes changes to the structure of a table without preventing reads or writes from happening. We were hoping to avoid any disruptions to our production traffic while we were in the process of moving, so this is just what we needed.

What the tool actually accomplishes behind the scenes is as follows:

Make a new, empty temporary table and apply the revised schema to it.
Make sure that any newly added or updated data in the first table is replicated in the second table by adding three triggers to it, one each for inserting, updating, and deleting data. This will accomplish the desired result.
Transfer the data from the first table to the second table in discrete pieces.
When the copying process is finished, it performs an atomic rename operation on both the old table and the new table, and then it deletes the original table.
During this operation, no read and write locks will be applied to the original table, and active transactions will not be affected in any way.

Installation sudo apt-get Percona Toolkit may be installed by using the following command: wget percona.com/get/percona-toolkit.tar.gz
wget./get/percona-toolkit.rpm from./percona.com/get
wget percona.com/get/percona-toolkit.deb
The following is an example of how to use pt-online-schema-change: —host db host> —user <db user> —password <db password> —alter “MODIFY created time timestamp not null” D=[database name],t=[table name] —execute
Sample execution outcome
Not checking the lag of the slaves since there were not any slaves discovered and the —check-slave-lag option was not supplied.
The operation, the tries, and the wait:
perform the following operations on the table: analyze table (10), 1 copy rows (10.25), create triggers (10), 1 drop triggers (10.1), swap tables (10.1)
Altering ‘<db name>
Making a brand-new table…
OK, a new table named db name. table> new has been created.
Changing over to the new table…
Altered ‘<db name>
`.` <table> new` OK.
2022-05-11T23:41:27 Creating triggers…
2022-05-11 23:41:27 Finished creating triggers successfully.
2022-05-11T23:41:27 A total of roughly 3294741 rows are being copied…
Copying ‘<db name>
‘: 77 percent 00:08 remain
2022-05-11 23:42:08 Successfully copied rows
2022-05-11T23:42:08 Analyzing new table…
2022-05-11T23:42:08 Swapping tables…
2022-05-11T23:42:08 Both the old and the new tables were successfully switched.
2022-05-11T23:42:08 Dropping old table…
2022-05-11T23:42:08 Dropped old table ‘db name’.’ <table> old’ OK.
2022-05-11T23:42:08 Dropping triggers…
2022-05-11T23:42:08 Dropped triggers OK.
The alteration of the ‘db name>’ table was successful.
This appeared to be very straightforward, and the results of our preliminary tests with some table migration looked fantastic. The issue began with tables that had referential integrity preserved properly.

Before I explain the issue, let me first describe how Percona deals with the modification of tables that contain foreign keys. If the table being changed contains one or more foreign keys, the tool offers a command-line parameter called –alter-foreign-keys-method. This argument allows the user to define how the foreign key (or keys) will be referred after the atomic renaming operation has been performed. As per Percona docs:

“Foreign keys that reference the table that is going to be changed need to be handled in a special way so that we can guarantee that they will continue to reference the right table. When the tool renames the original table in order to make room for the new one to take its place, the foreign keys “follow” the renamed table and need to have their references altered to go to the new table instead.

Enhanced solution: The best of both worlds

Let’s pretend there are two tables, and we’ll call them T1 and T2. T1 is the table that will be modified, and T2 is linked to T1 via a foreign key in the table T2. A new temporary table with the name _T1 new is generated whenever the Percona query is executed. Percona has two methods, namely rebuild constraints and drop swap, which can be accessed by the –alter-foreign-keys-method option in order to bring the new table’s foreign key references up to date once the original and new tables have been swapped.

Methodology behind the rebuild constraints approach:

When the copying process is finished, it renames T1 to be T1 old and renames _T1 new to be T1.
The foreign key references are then reconstructed by modifying table T2 with the drop fk1 and add _fk1 commands, both of which point to the newly created table.
If the table T2 has millions of rows, the change operation on T2 will be a time-consuming and blocking process since it will take too long. There will be no writing allowed on T2 at all. This is the point at which the issue arises. We simply cannot afford to have any downtime that is too prolonged due to blocking writes. Imagine a situation in which you have numerous such tables with cross-table relationships whose child tables have large amounts of data; imagine also the blocking of writes that this scenario would generate!
Drop swap is an additional technique for updating foreign keys that is available in the Percona toolkit. This method does not block. The operation is as follows:

After the copy process is complete, it sets the value of FOREIGN KEY CHECKS to 0, which disables the foreign key checks.
Remove the T1 table and rename the _T1 new version T1.
Checks for foreign keys should be enabled.
The drop swap strategy is generally more risky, despite the fact that it is quicker overall and does not block. First, while you are dumping the existing table T1 and renaming the temporary table _T1 new, the table that is going to be changed simply does not exist, so any queries that are run against it will result in an error. This situation only lasts for a brief period of time. Second, if there is a problem and the new table _T1 new cannot be renamed in place of the old table T1, then it is too late to abort the process because the old table has been removed from existence for good.

Enhanced solution that combines the advantages of both approaches
Because we wanted to make changes to a large number of tables, the majority of which did not have cross-table references but just a small number of key tables did, we made the decision to use the rebuild constraints and drop swap options wherever they were required. Because we were aware of the potential dangers connected with drop swap, we chose to limit the amount of downtime that was required for tables that included cross-table references before executing the Percona query using drop swap.

After removing the first table, we needed to construct a failover strategy in the event that the renaming operation did not succeed and determine what actions we needed to do next. An RDS snapshot may be brought back to the exact same state it was in right before the outage using the failover strategy that we developed, which includes a restore database technique.

This was nothing more than a precautionary move, and we had to be ready for the worst-case scenario by doing so. However, we were aware that this scenario might not occur, and even if it did, we could always manually rename the table and give it another shot in case the renaming operation was unsuccessful.

When we conducted stringent dry runs on development and pre-production conditions and found that they were all successful without any issues, our level of trust climbed even more. In the course of our dry runs, we prepared various Percona queries using drop swap and rebuild constraints methods.

When it came to migrating to this level, we discovered that the Percona tool was quite beneficial. Imagine the impact on the live traffic and the amount of downtime we would have had to go through if we had been forced to edit each table by using the default Alter queries provided by MySQL.

Although Percona is slow when copying rows from the original table to a new table in chunks, it does not block read and write operations while it is copying the rows. We endured only short periods of downtime in order to circumvent the risk of drop swap queries, which was predictable and within our control.

As soon as the migration of all the tables was complete, we altered the Timezone setting in RDS to UTC. It was as easy as that. Since we have already converted those columns to TIMESTAMP format, changing the Timezone to UTC will not affect any datetime values that are stored in tables. The value will continue to be displayed in UTC.

The datetime values are stored in UTC by the Timestamp type, as I mentioned earlier, and even after they are retrieved, the datetime value will still be in UTC. After that, we deployed the updated version of our code into production without implementing any time zone conversions in the queries.

One step closer to reducing our overall technical debt and improving the experience we provide for our users. If you would want to contact with us and find out more about the engineering procedures that we use at Signeasy, please feel free to send us an email with any questions, ideas, or comments you might have at engineering@signeasy.com.

Related Posts

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More