vitess.io/vitess@v0.16.2/go/vt/vtgr/db/generate_patches.go (about) 1 /* 2 Copyright 2017 Shlomi Noach, GitHub Inc. 3 4 Licensed under the Apache License, Version 2.0 (the "License"); 5 you may not use this file except in compliance with the License. 6 You may obtain a copy of the License at 7 8 http://www.apache.org/licenses/LICENSE-2.0 9 10 Unless required by applicable law or agreed to in writing, software 11 distributed under the License is distributed on an "AS IS" BASIS, 12 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 See the License for the specific language governing permissions and 14 limitations under the License. 15 */ 16 17 /* 18 This file has been copied over from VTOrc package 19 */ 20 21 package db 22 23 // generateSQLPatches contains DDLs for patching schema to the latest version. 24 // Add new statements at the end of the list so they form a changelog. 25 var generateSQLPatches = []string{ 26 ` 27 ALTER TABLE 28 database_instance 29 ADD COLUMN read_only TINYINT UNSIGNED NOT NULL AFTER version 30 `, 31 ` 32 ALTER TABLE 33 database_instance 34 ADD COLUMN last_sql_error TEXT NOT NULL AFTER exec_source_log_pos 35 `, 36 ` 37 ALTER TABLE 38 database_instance 39 ADD COLUMN last_io_error TEXT NOT NULL AFTER last_sql_error 40 `, 41 ` 42 ALTER TABLE 43 database_instance 44 ADD COLUMN oracle_gtid TINYINT UNSIGNED NOT NULL AFTER replica_io_running 45 `, 46 ` 47 ALTER TABLE 48 database_instance 49 ADD COLUMN mariadb_gtid TINYINT UNSIGNED NOT NULL AFTER oracle_gtid 50 `, 51 ` 52 ALTER TABLE 53 database_instance 54 ADD COLUMN relay_log_file varchar(128) CHARACTER SET ascii NOT NULL AFTER exec_source_log_pos 55 `, 56 ` 57 ALTER TABLE 58 database_instance 59 ADD COLUMN relay_log_pos bigint unsigned NOT NULL AFTER relay_log_file 60 `, 61 ` 62 DROP INDEX source_host_port_idx ON database_instance 63 `, 64 ` 65 ALTER TABLE 66 database_instance 67 ADD INDEX source_host_port_idx_database_instance (source_host, source_port) 68 `, 69 ` 70 ALTER TABLE 71 database_instance 72 ADD COLUMN pseudo_gtid TINYINT UNSIGNED NOT NULL AFTER mariadb_gtid 73 `, 74 ` 75 ALTER TABLE 76 database_instance 77 ADD COLUMN replication_depth TINYINT UNSIGNED NOT NULL AFTER cluster_name 78 `, 79 ` 80 ALTER TABLE 81 database_instance 82 ADD COLUMN has_replication_filters TINYINT UNSIGNED NOT NULL AFTER replica_io_running 83 `, 84 ` 85 ALTER TABLE 86 database_instance 87 ADD COLUMN data_center varchar(32) CHARACTER SET ascii NOT NULL AFTER cluster_name 88 `, 89 ` 90 ALTER TABLE 91 database_instance 92 ADD COLUMN physical_environment varchar(32) CHARACTER SET ascii NOT NULL AFTER data_center 93 `, 94 ` 95 ALTER TABLE 96 database_instance_maintenance 97 ADD KEY active_timestamp_idx (maintenance_active, begin_timestamp) 98 `, 99 ` 100 ALTER TABLE 101 database_instance 102 ADD COLUMN is_co_primary TINYINT UNSIGNED NOT NULL AFTER replication_depth 103 `, 104 ` 105 ALTER TABLE 106 database_instance_maintenance 107 ADD KEY active_end_timestamp_idx (maintenance_active, end_timestamp) 108 `, 109 ` 110 ALTER TABLE 111 database_instance 112 ADD COLUMN sql_delay INT UNSIGNED NOT NULL AFTER replica_lag_seconds 113 `, 114 ` 115 ALTER TABLE 116 topology_recovery 117 ADD COLUMN analysis varchar(128) CHARACTER SET ascii NOT NULL 118 `, 119 ` 120 ALTER TABLE 121 topology_recovery 122 ADD COLUMN cluster_name varchar(128) CHARACTER SET ascii NOT NULL 123 `, 124 ` 125 ALTER TABLE 126 topology_recovery 127 ADD COLUMN count_affected_replicas int unsigned NOT NULL 128 `, 129 ` 130 ALTER TABLE 131 topology_recovery 132 ADD COLUMN replica_hosts text CHARACTER SET ascii NOT NULL 133 `, 134 ` 135 ALTER TABLE hostname_unresolve 136 ADD COLUMN last_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 137 `, 138 ` 139 ALTER TABLE hostname_unresolve 140 ADD KEY last_registered_idx (last_registered) 141 `, 142 ` 143 ALTER TABLE topology_recovery 144 ADD KEY cluster_name_in_active_idx (cluster_name, in_active_period) 145 `, 146 ` 147 ALTER TABLE topology_recovery 148 ADD KEY end_recovery_idx (end_recovery) 149 `, 150 ` 151 ALTER TABLE 152 database_instance 153 ADD COLUMN binlog_server TINYINT UNSIGNED NOT NULL AFTER version 154 `, 155 ` 156 ALTER TABLE cluster_domain_name 157 ADD COLUMN last_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 158 `, 159 ` 160 ALTER TABLE cluster_domain_name 161 ADD KEY last_registered_idx (last_registered) 162 `, 163 ` 164 ALTER TABLE 165 database_instance 166 ADD COLUMN supports_oracle_gtid TINYINT UNSIGNED NOT NULL AFTER oracle_gtid 167 `, 168 ` 169 ALTER TABLE 170 database_instance 171 ADD COLUMN executed_gtid_set text CHARACTER SET ascii NOT NULL AFTER oracle_gtid 172 `, 173 ` 174 ALTER TABLE 175 database_instance 176 ADD COLUMN server_uuid varchar(64) CHARACTER SET ascii NOT NULL AFTER server_id 177 `, 178 ` 179 ALTER TABLE 180 topology_recovery 181 ADD COLUMN is_successful TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER processcing_node_token 182 `, 183 ` 184 ALTER TABLE 185 topology_recovery 186 ADD COLUMN acknowledged TINYINT UNSIGNED NOT NULL DEFAULT 0 187 `, 188 ` 189 ALTER TABLE 190 topology_recovery 191 ADD COLUMN acknowledged_by varchar(128) CHARACTER SET utf8 NOT NULL 192 `, 193 ` 194 ALTER TABLE 195 topology_recovery 196 ADD COLUMN acknowledge_comment text CHARACTER SET utf8 NOT NULL 197 `, 198 ` 199 ALTER TABLE 200 topology_recovery 201 ADD COLUMN participating_instances text CHARACTER SET ascii NOT NULL after replica_hosts 202 `, 203 ` 204 ALTER TABLE 205 topology_recovery 206 ADD COLUMN lost_replicas text CHARACTER SET ascii NOT NULL after participating_instances 207 `, 208 ` 209 ALTER TABLE 210 topology_recovery 211 ADD COLUMN all_errors text CHARACTER SET ascii NOT NULL after lost_replicas 212 `, 213 ` 214 ALTER TABLE audit 215 ADD COLUMN cluster_name varchar(128) CHARACTER SET ascii NOT NULL DEFAULT '' AFTER port 216 `, 217 ` 218 ALTER TABLE candidate_database_instance 219 ADD COLUMN priority TINYINT SIGNED NOT NULL DEFAULT 1 comment 'positive promote, nagative unpromotes' 220 `, 221 ` 222 ALTER TABLE 223 topology_recovery 224 ADD COLUMN acknowledged_at TIMESTAMP NULL after acknowledged 225 `, 226 ` 227 ALTER TABLE 228 topology_recovery 229 ADD KEY acknowledged_idx (acknowledged, acknowledged_at) 230 `, 231 ` 232 ALTER TABLE 233 blocked_topology_recovery 234 ADD KEY last_blocked_idx (last_blocked_timestamp) 235 `, 236 ` 237 ALTER TABLE candidate_database_instance 238 ADD COLUMN promotion_rule enum('must', 'prefer', 'neutral', 'prefer_not', 'must_not') NOT NULL DEFAULT 'neutral' 239 `, 240 ` 241 ALTER TABLE node_health /* sqlite3-skip */ 242 DROP PRIMARY KEY, 243 ADD PRIMARY KEY (hostname, token) 244 `, 245 ` 246 ALTER TABLE node_health 247 ADD COLUMN extra_info varchar(128) CHARACTER SET utf8 NOT NULL 248 `, 249 ` 250 ALTER TABLE agent_seed /* sqlite3-skip */ 251 MODIFY end_timestamp timestamp NOT NULL DEFAULT '1971-01-01 00:00:00' 252 `, 253 ` 254 ALTER TABLE active_node /* sqlite3-skip */ 255 MODIFY last_seen_active timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 256 `, 257 258 ` 259 ALTER TABLE node_health /* sqlite3-skip */ 260 MODIFY last_seen_active timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 261 `, 262 ` 263 ALTER TABLE candidate_database_instance /* sqlite3-skip */ 264 MODIFY last_suggested timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 265 `, 266 ` 267 ALTER TABLE primary_position_equivalence /* sqlite3-skip */ 268 MODIFY last_suggested timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 269 `, 270 ` 271 ALTER TABLE 272 database_instance 273 ADD COLUMN last_attempted_check TIMESTAMP NOT NULL DEFAULT '1971-01-01 00:00:00' AFTER last_checked 274 `, 275 ` 276 ALTER TABLE 277 database_instance /* sqlite3-skip */ 278 MODIFY last_attempted_check TIMESTAMP NOT NULL DEFAULT '1971-01-01 00:00:00' 279 `, 280 ` 281 ALTER TABLE 282 database_instance_analysis_changelog 283 ADD KEY instance_timestamp_idx (hostname, port, analysis_timestamp) 284 `, 285 ` 286 ALTER TABLE 287 topology_recovery 288 ADD COLUMN last_detection_id bigint unsigned NOT NULL 289 `, 290 ` 291 ALTER TABLE 292 topology_recovery 293 ADD KEY last_detection_idx (last_detection_id) 294 `, 295 ` 296 ALTER TABLE node_health_history 297 ADD COLUMN command varchar(128) CHARACTER SET utf8 NOT NULL 298 `, 299 ` 300 ALTER TABLE node_health 301 ADD COLUMN command varchar(128) CHARACTER SET utf8 NOT NULL 302 `, 303 ` 304 ALTER TABLE database_instance_topology_history 305 ADD COLUMN version varchar(128) CHARACTER SET ascii NOT NULL 306 `, 307 ` 308 ALTER TABLE 309 database_instance 310 ADD COLUMN gtid_purged text CHARACTER SET ascii NOT NULL AFTER executed_gtid_set 311 `, 312 ` 313 ALTER TABLE 314 database_instance_coordinates_history 315 ADD COLUMN last_seen timestamp NOT NULL DEFAULT '1971-01-01 00:00:00' AFTER recorded_timestamp 316 `, 317 ` 318 ALTER TABLE 319 access_token 320 ADD COLUMN is_reentrant TINYINT UNSIGNED NOT NULL default 0 321 `, 322 ` 323 ALTER TABLE 324 access_token 325 ADD COLUMN acquired_at timestamp NOT NULL DEFAULT '1971-01-01 00:00:00' 326 `, 327 ` 328 ALTER TABLE 329 database_instance_pool 330 ADD COLUMN registered_at timestamp NOT NULL DEFAULT '1971-01-01 00:00:00' 331 `, 332 ` 333 ALTER TABLE 334 database_instance 335 ADD COLUMN has_replication_credentials TINYINT UNSIGNED NOT NULL 336 `, 337 ` 338 ALTER TABLE 339 database_instance 340 ADD COLUMN allow_tls TINYINT UNSIGNED NOT NULL AFTER sql_delay 341 `, 342 ` 343 ALTER TABLE 344 database_instance 345 ADD COLUMN semi_sync_enforced TINYINT UNSIGNED NOT NULL AFTER physical_environment 346 `, 347 ` 348 ALTER TABLE 349 database_instance 350 ADD COLUMN instance_alias varchar(128) CHARACTER SET ascii NOT NULL AFTER physical_environment 351 `, 352 ` 353 ALTER TABLE 354 topology_recovery 355 ADD COLUMN successor_alias varchar(128) DEFAULT NULL 356 `, 357 ` 358 ALTER TABLE 359 database_instance /* sqlite3-skip */ 360 MODIFY cluster_name varchar(128) NOT NULL 361 `, 362 ` 363 ALTER TABLE 364 node_health 365 ADD INDEX last_seen_active_idx (last_seen_active) 366 `, 367 ` 368 ALTER TABLE 369 database_instance_maintenance 370 ADD COLUMN processing_node_hostname varchar(128) CHARACTER SET ascii NOT NULL 371 `, 372 ` 373 ALTER TABLE 374 database_instance_maintenance 375 ADD COLUMN processing_node_token varchar(128) NOT NULL 376 `, 377 ` 378 ALTER TABLE 379 database_instance_maintenance 380 ADD COLUMN explicitly_bounded TINYINT UNSIGNED NOT NULL 381 `, 382 ` 383 ALTER TABLE node_health_history 384 ADD COLUMN app_version varchar(64) CHARACTER SET ascii NOT NULL DEFAULT "" 385 `, 386 ` 387 ALTER TABLE node_health 388 ADD COLUMN app_version varchar(64) CHARACTER SET ascii NOT NULL DEFAULT "" 389 `, 390 ` 391 ALTER TABLE node_health_history /* sqlite3-skip */ 392 MODIFY app_version varchar(64) CHARACTER SET ascii NOT NULL DEFAULT "" 393 `, 394 ` 395 ALTER TABLE node_health /* sqlite3-skip */ 396 MODIFY app_version varchar(64) CHARACTER SET ascii NOT NULL DEFAULT "" 397 `, 398 ` 399 ALTER TABLE 400 database_instance 401 ADD COLUMN version_comment varchar(128) NOT NULL DEFAULT '' 402 `, 403 ` 404 ALTER TABLE active_node 405 ADD COLUMN first_seen_active timestamp NOT NULL DEFAULT '1971-01-01 00:00:00' 406 `, 407 ` 408 ALTER TABLE node_health 409 ADD COLUMN first_seen_active timestamp NOT NULL DEFAULT '1971-01-01 00:00:00' 410 `, 411 ` 412 ALTER TABLE database_instance 413 ADD COLUMN major_version varchar(16) CHARACTER SET ascii NOT NULL 414 `, 415 ` 416 ALTER TABLE 417 database_instance 418 ADD COLUMN binlog_row_image varchar(16) CHARACTER SET ascii NOT NULL 419 `, 420 ` 421 ALTER TABLE topology_recovery 422 ADD COLUMN uid varchar(128) CHARACTER SET ascii NOT NULL 423 `, 424 ` 425 CREATE INDEX uid_idx_topology_recovery ON topology_recovery(uid) 426 `, 427 ` 428 CREATE INDEX recovery_uid_idx_topology_recovery_steps ON topology_recovery_steps(recovery_uid) 429 `, 430 ` 431 ALTER TABLE 432 database_instance 433 ADD COLUMN last_discovery_latency bigint not null 434 `, 435 ` 436 CREATE INDEX end_timestamp_idx_database_instance_downtime ON database_instance_downtime(end_timestamp) 437 `, 438 ` 439 ALTER TABLE 440 topology_failure_detection 441 ADD COLUMN is_actionable tinyint not null default 0 442 `, 443 ` 444 DROP INDEX hostname_port_active_period_uidx_topology_failure_detection ON topology_failure_detection 445 `, 446 ` 447 CREATE UNIQUE INDEX host_port_active_recoverable_uidx_topology_failure_detection ON topology_failure_detection (hostname, port, in_active_period, end_active_period_unixtime, is_actionable) 448 `, 449 ` 450 ALTER TABLE raft_snapshot 451 ADD COLUMN created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 452 `, 453 ` 454 ALTER TABLE node_health 455 ADD COLUMN db_backend varchar(255) CHARACTER SET ascii NOT NULL DEFAULT "" 456 `, 457 ` 458 ALTER TABLE node_health 459 ADD COLUMN incrementing_indicator bigint not null default 0 460 `, 461 ` 462 ALTER TABLE 463 database_instance 464 ADD COLUMN semi_sync_primary_enabled TINYINT UNSIGNED NOT NULL 465 `, 466 ` 467 ALTER TABLE 468 database_instance 469 ADD COLUMN semi_sync_replica_enabled TINYINT UNSIGNED NOT NULL 470 `, 471 ` 472 ALTER TABLE 473 database_instance 474 ADD COLUMN gtid_mode varchar(32) CHARACTER SET ascii NOT NULL 475 `, 476 ` 477 ALTER TABLE 478 database_instance 479 ADD COLUMN last_check_partial_success tinyint unsigned NOT NULL after last_attempted_check 480 `, 481 ` 482 ALTER TABLE 483 database_instance 484 ADD COLUMN source_uuid varchar(64) CHARACTER SET ascii NOT NULL AFTER oracle_gtid 485 `, 486 ` 487 ALTER TABLE 488 database_instance 489 ADD COLUMN gtid_errant text CHARACTER SET ascii NOT NULL AFTER gtid_purged 490 `, 491 ` 492 ALTER TABLE 493 database_instance 494 ADD COLUMN ancestry_uuid text CHARACTER SET ascii NOT NULL AFTER source_uuid 495 `, 496 ` 497 ALTER TABLE 498 database_instance 499 ADD COLUMN replication_sql_thread_state tinyint signed not null default 0 AFTER replica_io_running 500 `, 501 ` 502 ALTER TABLE 503 database_instance 504 ADD COLUMN replication_io_thread_state tinyint signed not null default 0 AFTER replication_sql_thread_state 505 `, 506 ` 507 ALTER TABLE 508 database_instance_tags /* sqlite3-skip */ 509 DROP PRIMARY KEY, 510 ADD PRIMARY KEY (hostname, port, tag_name) 511 `, 512 ` 513 ALTER TABLE 514 database_instance 515 ADD COLUMN region varchar(32) CHARACTER SET ascii NOT NULL AFTER data_center 516 `, 517 ` 518 ALTER TABLE 519 database_instance 520 ADD COLUMN semi_sync_primary_timeout INT UNSIGNED NOT NULL DEFAULT 0 AFTER semi_sync_primary_enabled 521 `, 522 ` 523 ALTER TABLE 524 database_instance 525 ADD COLUMN semi_sync_primary_wait_for_replica_count INT UNSIGNED NOT NULL DEFAULT 0 AFTER semi_sync_primary_timeout 526 `, 527 ` 528 ALTER TABLE 529 database_instance 530 ADD COLUMN semi_sync_primary_status TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER semi_sync_primary_wait_for_replica_count 531 `, 532 ` 533 ALTER TABLE 534 database_instance 535 ADD COLUMN semi_sync_replica_status TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER semi_sync_primary_status 536 `, 537 ` 538 ALTER TABLE 539 database_instance 540 ADD COLUMN semi_sync_primary_clients INT UNSIGNED NOT NULL DEFAULT 0 AFTER semi_sync_primary_status 541 `, 542 ` 543 ALTER TABLE /* sqlite3-skip */ 544 database_instance 545 MODIFY semi_sync_primary_timeout BIGINT UNSIGNED NOT NULL DEFAULT 0 546 `, 547 // Fields related to Replication Group the instance belongs to 548 ` 549 ALTER TABLE 550 database_instance 551 ADD COLUMN replication_group_name VARCHAR(64) CHARACTER SET ascii NOT NULL DEFAULT '' AFTER gtid_mode 552 `, 553 ` 554 ALTER TABLE 555 database_instance 556 ADD COLUMN replication_group_is_single_primary_mode TINYINT UNSIGNED NOT NULL DEFAULT 1 AFTER replication_group_name 557 `, 558 ` 559 ALTER TABLE 560 database_instance 561 ADD COLUMN replication_group_member_state VARCHAR(16) CHARACTER SET ascii NOT NULL DEFAULT '' AFTER replication_group_is_single_primary_mode 562 `, 563 ` 564 ALTER TABLE 565 database_instance 566 ADD COLUMN replication_group_member_role VARCHAR(16) CHARACTER SET ascii NOT NULL DEFAULT '' AFTER replication_group_member_state 567 `, 568 ` 569 ALTER TABLE 570 database_instance 571 ADD COLUMN replication_group_members text CHARACTER SET ascii NOT NULL AFTER replication_group_member_role 572 `, 573 ` 574 ALTER TABLE 575 database_instance 576 ADD COLUMN replication_group_primary_host varchar(128) CHARACTER SET ascii NOT NULL DEFAULT '' AFTER replication_group_members 577 `, 578 ` 579 ALTER TABLE 580 database_instance 581 ADD COLUMN replication_group_primary_port smallint(5) unsigned NOT NULL DEFAULT 0 AFTER replication_group_primary_host 582 `, 583 }