sa: truncate all timestamps to seconds

[jsha]

As described in #7075, go-sql-driver/mysql v1.5.0 truncates timestamps to microseconds, while v1.6.0 and above does not. That means upon upgrading to v1.6.0, timestamps are written to the database with a resolution of nanoseconds, and SELECT statements also use a resolution of nanoseconds. We believe this is the cause of performance problems we observed when upgrading to v1.6.0 and above.

To fix that, apply rounding in the application code. Rather than just rounding to microseconds, round to seconds since that is the resolution we care about. Using seconds rather than microseconds may also allow some of our indexes to grow more slowly over time.

Note: this omits truncating some timestamps in CRL shard calculations, since truncating those resulted in test failures that I'll follow up on separately.

[jsha]

Passing tests and ready for review @pgporada !

[beautifulentropy]

LGTM. I'd love to have a way to test that it stays this way. Maybe something that runs near the end of the integration tests and looks for any higher-precision timestamps, somehow?

It's a little funky but we could add a trigger to each of our tables (for each of the datetime fields) that will actually reject datetimes with sub-second precision:

DELIMITER $$

CREATE TRIGGER check_timestamp_before_insert
BEFORE INSERT ON foo
FOR EACH ROW
BEGIN
    IF (MICROSECOND(NEW.barCreatedAt) > 0) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Fractional seconds are not allowed in foo';
    END IF;
END$$

DELIMITER ;

[pgporada]

Going the trigger route is going to be fraught with peril because a trigger can only target a single row in a table so we'd need at least 25 separate triggers and then make sure to add/remove triggers as the schema changes. There's also a problem with delimiters in sql-migrate. No matter what I tried I could never get past an error setting the MESSAGE_TEXT = 'whatever' while attempting to create even one trigger just in plain MariaDB.

MariaDB [boulder_sa_test]> CREATE TRIGGER IF NOT EXISTS `checkTS_before_insert_in_expires` BEFORE INSERT ON `authz2` FOR EACH ROW BEGIN IF MICROSECOND(NEW.expires) > 0 THEN
    ->         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'bad';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 2

[jsha]

The trigger idea is very cool! But in addition to Phil's point, I am not positive it will work since our schema sets the microseconds component of those columns to zero, so the DB is rounding them off.

However, I think I can add a setting for "nanoseconds component must be zero" in the arg conversion for borp, which would also get us what we want. I'll do that separately.