Mercurial > p > roundup > code
comparison roundup/backends/back_mysql.py @ 6806:bdd28b244839
- issue2551223 - fix timestamp truncation in mysql and postgresql
The data types used to represent timestamps in pg and mysql for
ephemeral tables: sessions and otks don't have enough signifcant
digits to work. As a result the timestamps are rounduped (up/down)
rsuling in the stored timestamp being 2 minutes (pg) or 2-3
hours(mysql) off from what it should be.
Modify db schema to use a numeric type that preserves more significant
figures. Implement schema upgrade. Document need for upgrade in
upgrading.txt.
Write tests for schema upgrade.
Implement test for updateTimestamp method on BasicDatabase that showed
this issue in the first place. Write overrides for test for
anydbm/memorydb which store timestamp properly or not at all.
| author | John Rouillard <rouilj@ieee.org> |
|---|---|
| date | Mon, 25 Jul 2022 17:20:20 -0400 |
| parents | 38e0fc1c7f11 |
| children | 83fa81f084bc |
comparison
equal
deleted
inserted
replaced
| 6805:09d9c646ca89 | 6806:bdd28b244839 |
|---|---|
| 217 self.sql('insert into `schema` values (%s)', (s,)) | 217 self.sql('insert into `schema` values (%s)', (s,)) |
| 218 | 218 |
| 219 def create_version_2_tables(self): | 219 def create_version_2_tables(self): |
| 220 # OTK store | 220 # OTK store |
| 221 self.sql('''CREATE TABLE otks (otk_key VARCHAR(255), | 221 self.sql('''CREATE TABLE otks (otk_key VARCHAR(255), |
| 222 otk_value TEXT, otk_time FLOAT(20)) | 222 otk_value TEXT, otk_time DOUBLE) |
| 223 ENGINE=%s'''%self.mysql_backend) | 223 ENGINE=%s'''%self.mysql_backend) |
| 224 self.sql('CREATE INDEX otks_key_idx ON otks(otk_key)') | 224 self.sql('CREATE INDEX otks_key_idx ON otks(otk_key)') |
| 225 | 225 |
| 226 # Sessions store | 226 # Sessions store |
| 227 self.sql('''CREATE TABLE sessions (session_key VARCHAR(255), | 227 self.sql('''CREATE TABLE sessions (session_key VARCHAR(255), |
| 228 session_time FLOAT(20), session_value TEXT) | 228 session_time DOUBLE, session_value TEXT) |
| 229 ENGINE=%s'''%self.mysql_backend) | 229 ENGINE=%s'''%self.mysql_backend) |
| 230 self.sql('''CREATE INDEX sessions_key_idx ON | 230 self.sql('''CREATE INDEX sessions_key_idx ON |
| 231 sessions(session_key)''') | 231 sessions(session_key)''') |
| 232 | 232 |
| 233 # full-text indexing store | 233 # full-text indexing store |
| 418 sql = "alter table __words change column _word _word varchar(%s)" % ( | 418 sql = "alter table __words change column _word _word varchar(%s)" % ( |
| 419 self.arg) | 419 self.arg) |
| 420 # Why magic number 5? It was the original offset between | 420 # Why magic number 5? It was the original offset between |
| 421 # column length and maxlength. | 421 # column length and maxlength. |
| 422 c.execute(sql, (self.indexer.maxlength + 5,)) | 422 c.execute(sql, (self.indexer.maxlength + 5,)) |
| 423 | |
| 424 def fix_version_7_tables(self): | |
| 425 # Modify type for session.session_time/otk.otk_time column. | |
| 426 sql = "alter table sessions modify session_time double" | |
| 427 self.sql(sql) | |
| 428 sql = "alter table otks modify otk_time double" | |
| 429 self.sql(sql) | |
| 423 | 430 |
| 424 def __repr__(self): | 431 def __repr__(self): |
| 425 return '<myroundsql 0x%x>'%id(self) | 432 return '<myroundsql 0x%x>'%id(self) |
| 426 | 433 |
| 427 def sql_fetchone(self): | 434 def sql_fetchone(self): |