merlin's questions

Estou executando o MySQL 8.0.23 e tento fazer backup/restaurar procedimentos. Por isso criei um usuário "dump@localhost" com os seguintes direitos:

> Grants for dump@localhost
> GRANT PROCESS ON *.* TO `dump`@`localhost` 
> GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER ON `mydb`.* TO `dump`@`localhost`

Criando um backup assim:

 mysqldump -u dump -p -n -d -t -R mydb > test.sql

O comando é executado, mas nenhum procedimento está dentro do arquivo sql.

--
-- Dumping routines for database 'mydb'
--
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2021-12-25 17:21:39

No entanto, se eu usar minha própria conta de usuário em vez de "dump" ou também usando root, ele despejará os procedimentos.

De acordo com o documento do MySQL 8, os direitos fornecidos devem ser suficientes: https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

mysqldump requer pelo menos o privilégio SELECT para tabelas despejadas, SHOW VIEW para visualizações despejadas, TRIGGER para gatilhos despejados, LOCK TABLES se a opção --single-transaction não for usada e (a partir do MySQL 8.0.21) PROCESS se a -- A opção no-tablespaces não é usada. Certas opções podem exigir outros privilégios, conforme observado nas descrições das opções.

Um procedimento típico meu se parece com isso:

create
definer = root@localhost procedure SUM_prices_d()
INSERT IGNORE INTO SUM_prices_d
...

Que direitos estou perdendo?

Eu tenho um dump do MySQL 5.7.x com cerca de 12 GB que tento importar para uma instância local do MySQL 8.0.x recém-instalada.

mysql -u username -p test < DB-210508.sql  

A importação é iniciada, mas falha com a seguinte mensagem:

ERROR 1824 (HY000) at line 518: Failed to open the referenced table 'table-name'

Estou me perguntando por que há um problema com chaves estrangeiras e se não é mais devido ao fato de estar importando para 8.0.x

A instância do MySQL 8.0.x é configurada assim:

[mysqld]
default_authentication_plugin= mysql_native_password
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 32
innodb_buffer_pool_chunk_size = 134217728
collation_server        = utf8_unicode_ci
character_set_server    = utf8

Qual poderia ser o motivo da importação com falha?

Estou lutando para resolver um projeto de banco de dados para MySQL 5.7 para resolver o seguinte cenário:

Existem dimensões de categoria que mudam a cada dia, eu as armazeno em category_entities. Eles pertencem a uma categoria que se refere a um comerciante e seus produtos:

comerciantes

merchant_id | merchant
1           | amazon
2           | ebay

PK: merchant_id

produtos

prod_id    | merchant_id | product
1          | 1           | jumper big
2          | 1           | hat red
3          | 2           | shoe black
3          | 1           | shoe black2 

PK: merchant_id, prod_id

categorias

cat_id     | merchant_id | category
1          | 1           | jumpers in green 
2          | 1           | hats for woman
3          | 1           | shoes
4          | 2           | jumpers 
5          | 2           | hats for children
6          | 2           | shoes

PK: cat_id

category_entities (30 milhões de linhas)

cat_id | prod_id | characteristica | date
1      | 1       | 23              | 2021-01-07
2      | 2       | 22              | 2021-01-07
1      | 2       | 22              | 2021-01-08
2      | 3       | 01              | 2021-01-08
3      | 1       | 22              | 2021-01-08
4      | 2       | 01              | 2021-01-08

PK: cat_id, prod_id, date

Então a tabela de categorias define quais categorias existem e o category_entities talbe mostra o valor para cada dia

Como posso definir uma chave estrangeira nas tabelas (categories, category_entities) em produtos de tabela usando prod_id, comerciante_id. Esses campos são a chave primária do produto.

Eu estou lendo sobre associações polimórficas, mas não tenho certeza se é uma e como resolver isso.

Eu tenho uma tabela prod MySQL em 5.7.32 onde quero atualizar os valores. Ele coutains apenas 4K linhas. No entanto, há outra tabela com uma chave estrangeira para a tabela que quero atualizar e esta contém milhões de linhas.

table a
ID | DIMENSION | VALUE

table b
ID | A_ID | ..
Key: A_ID -> a.ID cascade

Agora, como faço para atualizar a tabela a? Tenho medo de cascatear para a tabela b caso eu faça um truncado antes da importação. Os novos valores da tabela estão em um DBMS de desenvolvimento onde inicialmente se pensou em fazer uma única exportação e depois importar com truncado prévio.

A tabela atualizada contém linhas adicionais, removeu linhas e editou linhas.

Qual seria a maneira recomendada de atualizar os dados?

Eu poderia simplesmente usar as seguintes configurações durante a exportação com o PhpMyAdmin 4.5.7 e depois importar no prod?

Estou tentando encontrar a correspondência mais próxima com um determinado nome com mysql. A pontuação é a mesma para os dois primeiros resultados, mas olhando para ele eu preferiria pegar o segundo, pois quase se encaixa no nome.

SELECT ID, name, MATCH (`name`) 
AGAINST ('Superocean Héritage 42mm' IN BOOLEAN MODE) as `score` 
FROM cat_names cs 
WHERE MATCH (`name`) AGAINST ('Superocean Héritage 42mm' IN BOOLEAN MODE) 
ORDER BY `score` DESC

O resultado:

Superocean Héritage     6.3744072914123535

Superocean Héritage 42  6.3744072914123535

Superocean Héritage 46  6.3744072914123535

Existe uma maneira de ajustar a consulta para recuperar o segundo resultado corretamente?

Além disso, a pesquisa de texto completo retorna um índice de qualidade baixo para correspondências exatas:

Big Bang Aero Bang
9.445959091186523
Big Bang 44 mm
6.32852840423584
Big Bang Caviar
6.32852840423584
Big Bang Jeans
6.32852840423584
Big Bang Meca-10
6.32852840423584
Big Bang Sang Bleu
6.32852840423584
Big Bang Unico
6.32852840423584
Spirit of Big Bang
6.32852840423584
Big Bang 41 mm
6.32852840423584
Big Bang Broderie
6.32852840423584
Big Bang Ferrari
6.32852840423584
Big Bang King
6.32852840423584
Big Bang Pop Art
6.32852840423584
Big Bang Tutti Frutti
6.32852840423584
Big Bang
6.32852840423584

A correspondência exata tem a pontuação mais baixa.

Eu preciso obter algumas estatísticas de nosso MySQL-DB e me pergunto se há uma maneira mais rápida de fazer isso.

Basicamente, preciso de informações sobre o registro do usuário em um determinado mês antes de incluir a separação por atributos.

por exemplo:

SELECT count(*) AS c  
FROM `users` 
WHERE `date_created` LIKE '2015-10%' 
  AND `country` = 'DE'

Isso retornaria um número para outubro de 2015. Como eu poderia alterar esta declaração para retornar resultados para todos os meses dentro de uma declaração?

Eu também estaria interessado em adicionar algo como AND confirmed = 1um resultado separado.

Isso é possível com apenas uma instrução MySQL?

Recentemente, mudei de uma única instalação do MySQL para o MySQL Galera, rodando em 3 nós endereçados via HAProxy. Desde a troca, recebo aleatoriamente erros de "chave duplicada" relatados, o que teoricamente não deveria ser possível, pois verifico se o conjunto de dados já está disponível.

por exemplo:

MySQL Error executing stmt:

        INSERT INTO user_ips
            (user_id,ip) 
        VALUES
            ('444079','41.205.7.198, 176.9.19.101')


 Error No:  1062
 Error:         Duplicate entry '444079-41.205.7.198, 1' for key 'user_id'

Meu código está assim:

function save_user_ip($user_id){
    global $T62, $link;
    $ip = $_SERVER['REMOTE_ADDR'];
    // do we already know that user by this ip?
    $stmt="
        SELECT ID AS ip_id
        FROM $T62
        WHERE user_id = '$user_id' AND ip = '$ip'
    ";
    $row = db_get_row2($stmt);
    $ip_id = isset($row->ip_id) ? $row->ip_id : "";

    if (!$ip_id){ // ip unknown for that user, save it
        $stmt="
            INSERT INTO $T62
                (user_id,ip) 
            VALUES
                ('$user_id','$ip')
        ";
        db_query($stmt);
        $ip_id = db_insertid($link);
    }       
    return $ip_id;
}

Executando isso em uma página de teste, funciona conforme o esperado. No servidor de produção sob carga, no entanto, nem sempre funciona. Pensei em INSERT IGNORE, mas prefiro não executar uma inserção em todas as ações, pois as gravações são mais lentas e também acionam um aviso.

A execução SHOW CREATE TABLE tablenamecria a seguinte saída: 1146 - A tabela 'tablename' não existe

Uma solução possível que parece funcionar pelo menos no cluster de desenvolvimento:

// save ip. Will ignore if already known ip
$stmt="
    INSERT IGNORE INTO $T62
        (user_id,ip) 
    VALUES
        ('$user_id','$ip')
";
#echo $stmt; exit;
db_query($stmt);
$ip_id = db_insertid($link);
// if ip already known, retrieve it
if (!$ip_id){ 
    $stmt="
        SELECT ID AS ip_id
        FROM $T62
        WHERE user_id = '$user_id' AND ip = '$ip'
    ";
    $row = db_get_row2($stmt);
    $ip_id = $row->ip_id;
}   
return $ip_id;

Não tenho certeza de como isso melhora a situação no prod ou se torna as coisas ainda piores. Pode haver uma solução mais elegante.

Agradeço antecipadamente por qualquer ajuda sobre isso.