É possível mesclar 2 MySQL rds em um sem tempo de inatividade? Ambos não têm bancos de dados sobrepostos
Estou pensando em carregar os bancos de dados de um dos bancos de dados para o outro via mysql dump, em seguida, replicar dos outros rds para recuperar o atraso e, em seguida, fazer o failover
Isso é mesmo possível?
Eu uso Amazon rds e tenho uma réplica de região cruzada. Enquanto eu estava fazendo algumas alterações de esquema, minha réplica de leitura me deu esse erro e a replicação parou.
Mensagem: Erro de replicação de réplica de leitura - SQLError: 1146, motivo: Tabela de erro phppoint_koreannoona.phppos_items não existe na consulta. Banco de dados padrão: DB_NAME;. Consulta: ALTER TABLE phppos_items DROP COLUMN aaatex_qb_item_name
O que causaria isso? A tabela está no mestre. Minha migração DID removeu esse campo, mas a tabela inteira estava faltando no escravo.
Eu tive que reconstruir escravo.
Eu tive meu servidor RDS MySQL 5.6.34 travando sob carga normal sem nada fora do comum hoje (que eu consegui encontrar). O suporte da Amazon me apontou o log de erros.
Configuração do MySQL: https://gist.github.com/blasto333/9e82b6261681303f7369c3a2652e03f2
Você pode ver o log de erros completo em:
https://www.dropbox.com/s/8y10mfok4l6zc1m/mysql-error-running.log.21.txt?dl=0
A maior parte do log de erros tem:
InnoDB: Aviso: uma longa espera de semáforo: --Thread 47504778909440 esperou na linha dict0dict.cc 1122 por 241,00 segundos o semáforo: Mutex em 0x2b33b16f34a8 criou o arquivo dict0dict.cc linha 1057, lock var 1 waiters flag 1 InnoDB: Aviso: a long espera semáforo:
Há impasse também
LATEST DETECTED DEADLOCK
------------------------
2018-07-05 23:11:31 2b34c9f8d700
*** (1) TRANSACTION:
TRANSACTION 1056966456, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 22 lock struct(s), heap size 2936, 10 row lock(s), undo log entries 8
MySQL thread id 149979681, OS thread handle 0x2b348aea9700, query id 1268732633 172.31.43.29 phppoint updating
UPDATE `phppos_items` SET `last_modified` = '2018-07-05 18:11:31'
WHERE `item_id` = 9794
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1273297 n bits 152 index `PRIMARY` of table `phppoint_fatpanda`.`phppos_items` trx id 1056966456 lock_mode X locks rec but not gap waiting
Record lock, heap no 30 PHYSICAL RECORD: n_fields 61; compact format; info bits 0
0: len 4; hex 80002642; asc &B;;
1: len 6; hex 00003f00027d; asc ? };;
2: len 7; hex 1b00013aea2ab2; asc : * ;;
3: len 24; hex 4650205468757273646179204368756262792050726f6d6f; asc FP Thursday Chubby Promo;;
4: len 4; hex 80000041; asc A;;
Alguma ideia de onde posso começar a procurar?
mysql> show global status;
+-----------------------------------------------+--------------+
| Variable_name | Value |
+-----------------------------------------------+--------------+
| Aborted_clients | 75 |
| Aborted_connects | 767 |
| Binlog_cache_disk_use | 23592 |
| Binlog_cache_use | 1910314 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 9909 |
| Bytes_received | 19020005934 |
| Bytes_sent | 450148820308 |
| Com_admin_commands | 9 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 6097 |
| Com_alter_tablespace | 0 |
| Com_alter_user | 0 |
| Com_analyze | 0 |
| Com_begin | 50002 |
| Com_binlog | 0 |
| Com_call_procedure | 66 |
| Com_change_db | 332896 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 50169 |
| Com_create_db | 25 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 2822 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 70492 |
| Com_delete_multi | 148 |
| Com_do | 0 |
| Com_drop_db | 1 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 615 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_flush | 2023 |
| Com_get_diagnostics | 0 |
| Com_grant | 2 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 494163 |
| Com_insert_select | 0 |
| Com_install_plugin | 0 |
| Com_kill | 66 |
| Com_load | 0 |
| Com_lock_tables | 2819 |
| Com_optimize | 97 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 662 |
| Com_purge_before_date | 0 |
| Com_release_savepoint | 0 |
| Com_rename_table | 0 |
| Com_rename_user | 0 |
| Com_repair | 0 |
| Com_replace | 47982 |
| Com_replace_select | 9 |
| Com_reset | 0 |
| Com_resignal | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 65 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 14400535 |
| Com_set_option | 11497318 |
| Com_signal | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 33 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_create_db | 1717 |
| Com_show_create_event | 0 |
| Com_show_create_func | 0 |
| Com_show_create_proc | 208 |
| Com_show_create_table | 486369 |
| Com_show_create_trigger | 40 |
| Com_show_databases | 53 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 2 |
| Com_show_events | 20 |
| Com_show_errors | 0 |
| Com_show_fields | 2087822 |
| Com_show_function_code | 0 |
| Com_show_function_status | 3436 |
| Com_show_grants | 10 |
| Com_show_keys | 42 |
| Com_show_master_status | 13 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_code | 0 |
| Com_show_procedure_status | 3436 |
| Com_show_processlist | 119951 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_relaylog_events | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 10 |
| Com_show_status | 1917 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 324246 |
| Com_show_tables | 1276858 |
| Com_show_triggers | 324172 |
| Com_show_variables | 2051 |
| Com_show_warnings | 4 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 3 |
| Com_stmt_execute | 23 |
| Com_stmt_fetch | 10 |
| Com_stmt_prepare | 23 |
| Com_stmt_reprepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 252 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 4537 |
| Com_update | 1876581 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 14109046 |
| Created_tmp_disk_tables | 3483968 |
| Created_tmp_files | 27074 |
| Created_tmp_tables | 5675076 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 16730928 |
| Handler_delete | 58746 |
| Handler_discover | 0 |
| Handler_external_lock | 44461222 |
| Handler_mrr_init | 0 |
| Handler_prepare | 4935298 |
| Handler_read_first | 555947737 |
| Handler_read_key | 4342441284 |
| Handler_read_last | 51021 |
| Handler_read_next | 4410037167 |
| Handler_read_prev | 1041500 |
| Handler_read_rnd | 66317337 |
| Handler_read_rnd_next | 4061126421 |
| Handler_rollback | 14002 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 6298422 |
| Handler_write | 528228581 |
| Innodb_buffer_pool_dump_status | not started |
| Innodb_buffer_pool_load_status | not started |
| Innodb_buffer_pool_pages_data | 157888 |
| Innodb_buffer_pool_bytes_data | 2586836992 |
| Innodb_buffer_pool_pages_dirty | 1456 |
| Innodb_buffer_pool_bytes_dirty | 23855104 |
| Innodb_buffer_pool_pages_flushed | 6097239 |
| Innodb_buffer_pool_pages_free | 8192 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 166080 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 4175098 |
| Innodb_buffer_pool_read_ahead_evicted | 1998 |
| Innodb_buffer_pool_read_requests | 20669375353 |
| Innodb_buffer_pool_reads | 19725302 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 36813736 |
| Innodb_data_fsyncs | 4000141 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 394739994624 |
| Innodb_data_reads | 24092362 |
| Innodb_data_writes | 9335083 |
| Innodb_data_written | 210941677568 |
| Innodb_dblwr_pages_written | 6097239 |
| Innodb_dblwr_writes | 577145 |
| Innodb_have_atomic_builtins | ON |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 20665741 |
| Innodb_log_writes | 2583719 |
| Innodb_os_log_fsyncs | 2659640 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 11108775936 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 323226 |
| Innodb_pages_read | 24091712 |
| Innodb_pages_written | 6097239 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 658 |
| Innodb_row_lock_time_avg | 5 |
| Innodb_row_lock_time_max | 19 |
| Innodb_row_lock_waits | 123 |
| Innodb_rows_deleted | 58747 |
| Innodb_rows_inserted | 503434 |
| Innodb_rows_read | 9533246024 |
| Innodb_rows_updated | 2062321 |
| Innodb_num_open_files | 14 |
| Innodb_truncated_status_writes | 0 |
| Innodb_available_undo_logs | 128 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 13396 |
| Key_blocks_used | 3898 |
| Key_read_requests | 968442355 |
| Key_reads | 260 |
| Key_write_requests | 314009997 |
| Key_writes | 0 |
| Last_query_cost | 0.000000 |
| Last_query_partial_plans | 0 |
| Max_used_connections | 429 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 3 |
| Open_streams | 0 |
| Open_table_definitions | 1400 |
| Open_tables | 2000 |
| Opened_files | 15050970 |
| Opened_table_definitions | 1057163 |
| Opened_tables | 4565317 |
| Performance_schema_accounts_lost | 0 |
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 0 |
| Performance_schema_digest_lost | 0 |
| Performance_schema_file_classes_lost | 0 |
| Performance_schema_file_handles_lost | 0 |
| Performance_schema_file_instances_lost | 0 |
| Performance_schema_hosts_lost | 0 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_mutex_classes_lost | 0 |
| Performance_schema_mutex_instances_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Performance_schema_socket_classes_lost | 0 |
| Performance_schema_socket_instances_lost | 0 |
| Performance_schema_stage_classes_lost | 0 |
| Performance_schema_statement_classes_lost | 0 |
| Performance_schema_table_handles_lost | 0 |
| Performance_schema_table_instances_lost | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Performance_schema_users_lost | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 4359 |
| Qcache_free_memory | 11838224 |
| Qcache_hits | 53345695 |
| Qcache_inserts | 10498816 |
| Qcache_lowmem_prunes | 4945792 |
| Qcache_not_cached | 3901891 |
| Qcache_queries_in_cache | 11254 |
| Qcache_total_blocks | 32300 |
| Queries | 100927051 |
| Questions | 100924711 |
| Select_full_join | 1119531 |
| Select_full_range_join | 1691 |
| Select_range | 976854 |
| Select_range_check | 37 |
| Select_scan | 5601093 |
| Slave_heartbeat_period | 0.000 |
| Slave_last_heartbeat | |
| Slave_open_temp_tables | 0 |
| Slave_received_heartbeats | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 1045 |
| Sort_merge_passes | 7119 |
| Sort_range | 246182 |
| Sort_rows | 66189786 |
| Sort_scan | 1565455 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_server_not_after | |
| Ssl_server_not_before | |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | Unknown |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 22141644 |
| Table_locks_waited | 0 |
| Table_open_cache_hits | 20638283 |
| Table_open_cache_misses | 4565315 |
| Table_open_cache_overflows | 4559458 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 53 |
| Threads_connected | 19 |
| Threads_created | 31446 |
| Threads_running | 2 |
| Uptime | 601636 |
| Uptime_since_flush_status | 601636 |
+-----------------------------------------------+--------------+
341 rows in set (0.00 sec)
Entendo que migrar para o Aurora é simplesmente uma questão de criar uma réplica de leitura da origem do MySQL e, em seguida, promover quando capturado e interromper as gravações no mestre anterior do MySQL RDS.
Eu uso o DNS do Route 53 que possui um registro CNAME. Meu plano é apenas alterar o DNS para o cluster do Aurora, mas não tenho certeza se isso é instantâneo? Existe uma maneira de interromper a instância antiga do MySQL RDS para que ela não possa ser gravada? (É como um Multi-AZ e não há parada)
Eu tenho a seguinte tabela: (Create table statement)
CREATE TABLE `phppos_people` (
`first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`phone_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`address_1` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`address_2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`city` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`state` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`zip` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`country` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`comments` text COLLATE utf8_unicode_ci NOT NULL,
`image_id` int(10) DEFAULT NULL,
`person_id` int(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`person_id`),
KEY `phppos_people_ibfk_1` (`image_id`),
FULLTEXT KEY `full_search` (`first_name`,`last_name`,`email`,`phone_number`),
FULLTEXT KEY `first_name_search` (`first_name`),
FULLTEXT KEY `last_name_search` (`last_name`),
FULLTEXT KEY `full_name_search` (`first_name`,`last_name`),
FULLTEXT KEY `email_search` (`email`),
FULLTEXT KEY `phone_number_search` (`phone_number`),
CONSTRAINT `phppos_people_ibfk_1` FOREIGN KEY (`image_id`) REFERENCES `phppos_app_files` (`file_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
Tenho requisitos para uma pesquisa rápida usando nome, sobrenome, e-mail e número de telefone de uma só vez; mas também ser capaz de pesquisar cada campo individual. Eu também gosto da classificação de relevância do texto completo que não pode ser feito com Btree
As consultas funcionam bem, mas de acordo com meu provedor de banco de dados em nuvem, eles recomendam índices btree para melhor uso de memória e disco.
Tenho alguma outra opção/configuração ou forma de criar índices que não usem muito disco + memória? Tenho cerca de 40 índices de texto completo por banco de dados que seguem um padrão semelhante. O desempenho é ótimo com esses índices e realmente acelerou o produto.
O texto completo do Innodb foi projetado para usar muita memória + disco? Aqui estão minhas configurações de variável mysql
https://gist.github.com/blasto333/aa4241a4e37447961188356719ea6984
Aqui estão algumas estatísticas do meu provedor
Após a revisão, tive uma ideia do tamanho real do conjunto de dados armazenado no InnoDB. Foi uma surpresa quando descobri que o tamanho do conjunto de dados é muito menor do que o uso do disco, conforme listado abaixo:
Essa discrepância entre o uso do disco e do espaço de tabela me preocupou inicialmente com o fato de termos algo órfão, como tabelas temporárias antigas, mas depois descobri que isso estava sendo causado por um uso intenso de índices InnoDB Full Text .
PHP-POS-DB-01
On Disk: 18GB
Total DataSet Size: 4.56GB
Unused allocated Tablespace: 3.10GB ## Fragmentation
Total innodb used plus fragmentation: 7.66GB
PHP-POS-DB-2-01
On Disk: 9.7G
Total DataSet Size: 923.85MB
Unused allocated Tablespace: 363.00MB
Total innodb used plus fragmentation: 1.29GB
Estou adicionando pesquisa de texto completo a tabelas mysql já existentes que possuem chave primária de incremento automático de id integer.
Quando adiciono o índice de texto completo, recebo um aviso:
/*!50604 ALTER TABLE `phppos_additional_item_numbers` ADD FULLTEXT INDEX item_number_search (`item_number`)*/;
Warning (Code 124): InnoDB rebuilding table to add column FTS_DOC_ID
Não posso simplesmente refatorar todo o meu código para adicionar uma coluna FTS_DOC_ID para remover esse aviso. É ruim apenas permitir que esse aviso aconteça?
Ao alterar o modo de sessão no mysql 5.7, recebo o seguinte aviso.
"(Code 3090): Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release."
Não entendo exatamente o que significa. O mysql está removendo a capacidade de definir um modo de sessão?
terminal mysql
mysql> \W
Show warnings enabled.
mysql> SET SESSION sql_mode="NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected, 1 warning (0.00 sec)
Warning (Code 3090): Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
mysql>
Estou procurando gerar uma string criptograficamente segura para redefinição de senha no MySQL. Sei que poderia fazer isso no nível do aplicativo em PHP; mas isso requer a extensão OpenSSL que muitos de meus clientes podem não ter.
Se eu puder fazer isso no MySQL 5 de uma maneira segura e amplamente disponível, seria o ideal. Isso é possível?
Nota: Isso é para gerar um token seguro para redefinição de senha. Não tem nada a ver com uma conexão segura, então usar https não é uma solução.
Estou fazendo uma pesquisa de texto completo complexa em uma tabela com mais de 130 mil registros. Demora 0,5 segundos para ser concluído. Eu esperava que fosse um pouco mais rápido.
Existe algo que eu possa fazer para torná-lo mais rápido?
Abaixo está a consulta junto com a explicação + tabelas relevantes
Query:
SELECT `phppos_items`.*, `phppos_categories`.`name` as category, `phppos_location_items`.`quantity` as quantity, `phppos_location_items`.`reorder_level` as location_reorder_level, `phppos_location_items`.`cost_price` as location_cost_price, `phppos_location_items`.`unit_price` as location_unit_price
FROM (`phppos_items`)
LEFT JOIN `phppos_categories` ON `phppos_categories`.`id` = `phppos_items`.`category_id`
LEFT JOIN `phppos_location_items` ON `phppos_location_items`.`item_id` = `phppos_items`.`item_id` and location_id = 1
LEFT JOIN `phppos_additional_item_numbers` ON `phppos_additional_item_numbers`.`item_id` = `phppos_items`.`item_id`
LEFT JOIN `phppos_items_tags` ON `phppos_items_tags`.`item_id` = `phppos_items`.`item_id`
LEFT JOIN `phppos_tags` ON `phppos_tags`.`id` = `phppos_items_tags`.`tag_id`
WHERE (MATCH (phppos_items.name, phppos_items.item_number, product_id, description) AGAINST ('test')
or MATCH(phppos_tags.name) AGAINST ('test') or
MATCH(phppos_categories.name) AGAINST ('test') or
MATCH(phppos_additional_item_numbers.item_number) AGAINST ('test'))and phppos_items.deleted=0
LIMIT 20
EXPLAIN:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------------------+--------+--------------------------------------+---------+---------+--------------------------------+-------+-------------+
| 1 | SIMPLE | phppos_items | ref | deleted | deleted | 4 | const | 66679 | NULL |
| 1 | SIMPLE | phppos_categories | eq_ref | PRIMARY | PRIMARY | 4 | pos.phppos_items.category_id | 1 | NULL |
| 1 | SIMPLE | phppos_location_items | eq_ref | PRIMARY,phppos_location_items_ibfk_2 | PRIMARY | 8 | const,pos.phppos_items.item_id | 1 | NULL |
| 1 | SIMPLE | phppos_additional_item_numbers | ref | PRIMARY | PRIMARY | 4 | pos.phppos_items.item_id | 1 | Using index |
| 1 | SIMPLE | phppos_items_tags | ref | PRIMARY | PRIMARY | 4 | pos.phppos_items.item_id | 1 | Using index |
| 1 | SIMPLE | phppos_tags | eq_ref | PRIMARY | PRIMARY | 4 | pos.phppos_items_tags.tag_id | 1 | Using where |
+----+-------------+--------------------------------+--------+--------------------------------------+---------+---------+--------------------------------+-------+-------------+
6 rows in set (0.00 sec)
mysql> show create table phppos_items;
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_items | CREATE TABLE `phppos_items` (
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`category_id` int(11) DEFAULT NULL,
`supplier_id` int(11) DEFAULT NULL,
`item_number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`product_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`size` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`tax_included` int(1) NOT NULL DEFAULT '0',
`cost_price` decimal(23,10) NOT NULL,
`unit_price` decimal(23,10) NOT NULL,
`promo_price` decimal(23,10) DEFAULT NULL,
`start_date` date DEFAULT NULL,
`end_date` date DEFAULT NULL,
`reorder_level` decimal(23,10) DEFAULT NULL,
`expire` date DEFAULT NULL,
`item_id` int(10) NOT NULL AUTO_INCREMENT,
`allow_alt_description` tinyint(1) NOT NULL,
`is_serialized` tinyint(1) NOT NULL,
`image_id` int(10) DEFAULT NULL,
`override_default_tax` int(1) NOT NULL DEFAULT '0',
`is_service` int(1) NOT NULL DEFAULT '0',
`commission_percent` decimal(23,10) DEFAULT '0.0000000000',
`commission_fixed` decimal(23,10) DEFAULT '0.0000000000',
`deleted` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`item_id`),
UNIQUE KEY `item_number` (`item_number`),
UNIQUE KEY `product_id` (`product_id`),
KEY `phppos_items_ibfk_1` (`supplier_id`),
KEY `name` (`name`),
KEY `deleted` (`deleted`),
KEY `phppos_items_ibfk_2` (`image_id`),
KEY `phppos_items_ibfk_3` (`category_id`),
FULLTEXT KEY `full_search` (`name`,`item_number`,`product_id`,`description`),
FULLTEXT KEY `name_search` (`name`),
FULLTEXT KEY `item_number_search` (`item_number`),
FULLTEXT KEY `product_id_search` (`product_id`),
FULLTEXT KEY `description_search` (`description`),
FULLTEXT KEY `size_search` (`size`),
CONSTRAINT `phppos_items_ibfk_1` FOREIGN KEY (`supplier_id`) REFERENCES `phppos_suppliers` (`person_id`),
CONSTRAINT `phppos_items_ibfk_2` FOREIGN KEY (`image_id`) REFERENCES `phppos_app_files` (`file_id`),
CONSTRAINT `phppos_items_ibfk_3` FOREIGN KEY (`category_id`) REFERENCES `phppos_categories` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=133095 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table phppos_categories;
+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_categories | CREATE TABLE `phppos_categories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`deleted` int(1) NOT NULL DEFAULT '0',
`parent_id` int(11) DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `deleted` (`deleted`),
KEY `parent_id_name_index` (`parent_id`,`name`),
FULLTEXT KEY `name_search` (`name`),
CONSTRAINT `phppos_categories_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `phppos_categories` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4096 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table phppos_location_items;
+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_location_items | CREATE TABLE `phppos_location_items` (
`location_id` int(11) NOT NULL,
`item_id` int(11) NOT NULL,
`location` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`cost_price` decimal(23,10) DEFAULT NULL,
`unit_price` decimal(23,10) DEFAULT NULL,
`promo_price` decimal(23,10) DEFAULT NULL,
`start_date` date DEFAULT NULL,
`end_date` date DEFAULT NULL,
`quantity` decimal(23,10) DEFAULT '0.0000000000',
`reorder_level` decimal(23,10) DEFAULT NULL,
`override_default_tax` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`location_id`,`item_id`),
KEY `phppos_location_items_ibfk_2` (`item_id`),
CONSTRAINT `phppos_location_items_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `phppos_locations` (`location_id`),
CONSTRAINT `phppos_location_items_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `phppos_items` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table phppos_additional_item_numbers;
+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_additional_item_numbers | CREATE TABLE `phppos_additional_item_numbers` (
`item_id` int(11) NOT NULL AUTO_INCREMENT,
`item_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`item_id`,`item_number`),
UNIQUE KEY `item_number` (`item_number`),
FULLTEXT KEY `item_number_search` (`item_number`),
CONSTRAINT `phppos_additional_item_numbers_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `phppos_items` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table phppos_items_tags;
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_items_tags | CREATE TABLE `phppos_items_tags` (
`item_id` int(11) NOT NULL,
`tag_id` int(11) NOT NULL,
PRIMARY KEY (`item_id`,`tag_id`),
KEY `phppos_items_tags_ibfk_2` (`tag_id`),
CONSTRAINT `phppos_items_tags_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `phppos_items` (`item_id`),
CONSTRAINT `phppos_items_tags_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `phppos_tags` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table phppos_tags;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_tags | CREATE TABLE `phppos_tags` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`deleted` int(1) NOT NULL DEFAULT '0',
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `tag_name` (`name`),
KEY `deleted` (`deleted`),
FULLTEXT KEY `name_search` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Encontrei esta resposta sobre como ter lógica condicional para execução de instruções mysql ( https://stackoverflow.com/a/7537348/627473 ). Parece funcionar muito bem.
Mas preciso encontrar uma maneira de executar o código para uma condição muito específica. O código só pode ser executado se o banco de dados oferecer suporte à indexação FULL TEXT para innodb. Isso acontece no mysql 5.6.4 ou superior. MAS eu também preciso que isso funcione para maria db outras bifurcações do mysql.
Tenho certeza de que poderia encontrar um caminho para a versão do mysql; mas eu realmente não gosto de confiar em uma versão e prefiro confiar na detecção de recursos.
Existe uma maneira de fazer isso?
EDIT: Aqui está o código. Eu só tenho mais erros que não tenho certeza se podem ser corrigidos.
No phpMyAdmin o erro "#2014 - Commands out of sync; you can't run this command now"é exibido. Isso NÃO acontece na CLI. Existe uma maneira de contornar isso?
delimiter //
create procedure update_phppos()
begin
set @supports_ft := (SELECT COUNT(*) FROM information_schema.GLOBAL_VARIABLES WHERE Variable_name = 'innodb_ft_cache_size');
if @supports_ft > 0 then
REPLACE INTO `phppos_app_config` (`key`, `value`) VALUES ('version', '15.0');
else
SELECT 'An error has occured. You MUST have mysql 5.6.4 or higher to update php point of sale' as 'ERROR';
end if;
end;
//
delimiter ;
-- Execute the procedure
call update_phppos();
-- Drop the procedure
drop procedure update_phppos;
Por que preciso fazer outra seleção quando estou tentando inserir valores distintos em uma tabela? Além disso: existe uma maneira melhor de fazer isso?
FUNCIONA:
INSERT INTO phppos_categories (name)
SELECT category FROM
(SELECT DISTINCT category FROM phppos_items as items
UNION
SELECT DISTINCT category FROM phppos_item_kits as item_kits)
as category_result;
NÃO FUNCIONA:
INSERT INTO phppos_categories (name)
(SELECT DISTINCT category FROM phppos_items as items
UNION
SELECT DISTINCT category FROM phppos_item_kits as item_kits)
Eu tenho um diretório tmpfs de 768 milhões que uso para o diretório temporário mysql. Ocasionalmente, ele fica cheio ao executar relatórios em grandes intervalos de datas. Quando isso acontece, o relatório falha e recebo um erro 28.
Na verdade, isso parece aceitável para mim porque evita que uma consulta seja executada por muito tempo e sobrecarregue o servidor. (Eu penso)
A vantagem de usar o tmpfs é que as consultas que usam tabelas temporárias são executadas 50% mais rápido.
Minha pergunta é: o tmpfs é preenchido enquanto uma inserção ou substituição está em execução, isso falhará?
Também parece que quando o mysql preenche o diretório, os arquivos são excluídos quando a consulta falha. Haverá algum caso em que isso não aconteça?
Você acha que eu poderia causar algum problema usando /tmp para o diretório temporário do MySQL? Tenho cerca de 50 conexões simultâneas, mas nem todas estão executando relatórios ao mesmo tempo.
Quais são os prós e os contras de não usar o tmpfs?
Tenho a seguinte consulta:
SELECT COUNT(*) AS `numrows`
FROM (`phppos_items`)
WHERE `deleted` = 0
explique:
mysql> explain SELECT COUNT(*) AS `numrows`
-> FROM (`phppos_items`)
-> WHERE `deleted` = 0;
+----+-------------+--------------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | phppos_items | ref | deleted | deleted | 4 | const | 1 | Using index |
+----+-------------+--------------+------+---------------+---------+---------+-------+------+-------------+
Essa consulta sempre será indexada E rapidamente conforme as linhas da tabela crescem?
Eu adicionei mais tabelas ao meu servidor mysql. Aumento de 20% nas tabelas que são todas innodb. Tenho mais de 200 bancos de dados que usam o mesmo esquema.
Adicionar mais tabelas aumenta o uso de memória? Estou usando cerca de 1,7 gb de ram para mysql onde antes eu estava usando 1,4-1,5 gb