Mark D's questions

Tenho as seguintes tabelas:

CREATE TABLE `Channels` (
   `id` INT(11) AUTO_INCREMENT PRIMARY KEY,
   `title` VARCHAR(255) NOT NULL,
   `views` INT(11) NOT NULL DEFAULT 0,
) Engine = InnoDB;

INSERT INTO Channels (title, views) 
VALUES ('Movies', 50030), 
       ('TV shows', 51201), 
       ('Howtos', 18601);

CREATE TABLE `Videos` (
   `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
   `channel_id` INT(11),
   `name` VARCHAR(255) NOT NULL,
   `description` TEXT,
   `views` INT(11) NOT NULL DEFAULT 0,
   `created_at` DATETIME NOT NULL,
   `updated_at` DATETIME,
   CONSTRAINT videos_channel_id_fk
       FOREIGN KEY ( channel_id ) 
           REFERENCES Channels ( id )
) ENGINE=InnoDB;

INSERT INTO Videos (`channel_id`, `name`, `created_at`) 
VALUES (1, 'A', '2019-10-03 00:00:12'), (1, 'B', '2019-03-01 00:12:12'), 
       (1, 'C', '2019-10-01 19:12:12'), (1, 'D', '2019-10-03 01:55:55'),
       (2, 'E', '2019-10-03 01:58:00'), (2, 'F', '2019-10-01 15:11:11'),
       (2, 'G', '2019-09-09 11:11:11'), (2, 'H', '2019-08-01 12:53:10');

Estou tentando obter uma lista distinta de canais com seus vídeos mais recentes.

Por exemplo, para o Canal 1, seria o Vídeo 'D' em '2019-10-03 01:55:55', para o Canal 2 seria o Vídeo 'E' em '2019-10-03 01:58:00' );

Eu tentei fazer uma auto-junção assim,

SELECT v1.id, v1.channel_id, v1.name FROM Videos AS v1
LEFT JOIN Videos AS v2 ON v1.channel_id = v2.channel_id AND v1.created_at>v2.created_at 
WHERE v1.created_at IS NULL

mas meu ID de vídeo nunca corresponde ao vídeo mais recente. ou seja, vou acabar com o ID do vídeo: 1 onde quero ID do vídeo: 4.

Eu tenho a seguinte tabela no SQL Server 2008.

create table OrgUnits
(
    OrgUnitId int not null constraint PK_OrgUnits1 primary key,
    OrgUnitName varchar(100),
    OrgUnitLevel int not null,
    OrgUnitKey varchar(100) constraint UQ_OrgUnits_OrgUnitKey unique,
    Level1Id int not null,
    Level2Id int,
    StatusId int not null constraint FK_OrgUnits_Status references Status,
    VersionNumber int not null
);

INSERT INTO OrgUnits (OrgUnitId, OrgUnitName, OrgUnitKey, Level1Id, Level2Id,
                      StatusId, VersionNumber) 
VALUES 
(218824, 'Company1 (5) (5) (5)', '5', 218824, 1, 1),
(218825, 'Division1 (1) (1) (1)', '1', 218825, 1, 1),
(218826, 'XAVAT (4) (4) (4)', '4', 218826, 1, 1),
(218827, 'WATe (7) (7) (7)', '7', 218827, 1, 1),
(218828, 'Communications', '05-905-6320-300', 218824, 218828, 1, 1),
(218829, 'Corporate Development', '05-955-6320-300', 218824, 218829, 1, 1),
(218830, 'Corporate Board of Directors', '05-111-3515-301', 218830, 1, 1);        

Idealmente, eu teria OrgUnitNameaparecido como OrgUnitName + ' (' + OrgUnitKey + ')'; Mas parece que essa atualização foi executada em alguns, mas não em todos os valores da tabela.

Qual seria a instrução de atualização para atualizar todas as linhas para que elas tivessem o formato correto? Observe que tenho centenas de linhas e isso é apenas para corrigir um problema anterior causado por uma atualização anterior. A consulta só precisa ser executada uma vez.

tenho a seguinte tabela

CREATE TABLE TaskJournal (
  TaskJournalId INT NOT NULL AUTO_INCREMENT,
  TaskId INT NOT NULL,
  TaskStatus TINYINT NOT NULL,
  TaskStart INT NOT NULL,
  TaskEnd INT NULL,
  TaskInfo TEXT,
  PRIMARY KEY (TaskJournalId)
) ENGINE = InnoDB;

INSERT INTO TaskJournal (TaskId, TaskStatus, TaskStart, TaskEnd, TaskInfo) 
VALUES (2, 1, 1533660028, 1533660500, "No issues present"),
(3, 3, 1533660505, 1533660506, "Exception found: Line 95"),
(2, 1, 1533660015, 1533660018, "No issues present"),
(2, 3, 1533660509, 1533660512, "Exception found: Line 95"),
(2, 1, 1533660515, 1533660530, "No issues present");

O problema é este, quero reunir uma lista de erros mas apenas se no futuro não houver resolução.

Idealmente, eu receberia algo assim de volta

| TaskJournalId | TaskId | TaskStatus | TaskStart  | TaskEnd    | TaskInfo          |
| 3             | 2      | 1          | 1533660015 | 1533660018 | No issues present |

Só porque no futuro não há algum TaskId que tenha a exceção presente.

Então eu pensei que poderia fazer isso por meio de um grupo de subconsultas algo como o seguinte:

SELECT * 
FROM TaskJournal 
WHERE TaskId = 2 
GROUP BY TaskId, TaskStatus 
HAVING TaskStatus=3 
ORDER BY TaskStart DESC

Mas o problema com isso é que ele retorna as duas linhas 4 e 2 (ordem cronológica decrescente). Mas em algum momento posterior parece ter havido uma resolução para TaskId=2, mas não para TaskId=3.

Qualquer idéia será útil.

É possível usar um cursor para criar uma tabela dinâmica e depois usar essas colunas para agregar dados no SQL Server 2008?

Por exemplo, pegue a tabela a seguir.

CREATE TABLE Billing (
    BillingId BIGINT IDENTITY,
    SubscriptionId BIGINT,
        ExternalServiceName VARCHAR(50),
        BillYear INT NOT NULL,
    BillMonth INT NOT NULL
);

INSERT INTO Billing (BillingId, SubscriptionId, ExternalServiceName,
                     BillYear, BillMonth)
VALUES (1, 1, 'Dogs', 2018, 4),
       (2, 2, 'Cats', 2018, 4),
       (3, 1, 'Dogs', 2018, 5),
       (4, 2, 'Cats', 2018, 5);

CREATE TABLE BillingData (
    BillingDataId INT IDENTITY PRIMARY KEY,
    BillingId INT NOT NULL,
    Feature VARCHAR(50) NOT NULL,
    Usage INT NOT NULL,
    Measurement VARCHAR(50),
    Cost NUMERIC(18,2) NOT NULL
);

INSERT INTO BillingData(BillingId, Feature, Usage, Measurement, Cost)
VALUES (1, 'Walks', 25, 'walks', 200.32),
       (1, 'Baths', 5, 'baths', 251.32),
       (2, 'Litter change', 53, 'changes', 110.21),
       (2, 'Groom', 25, 'brushings', 123),
       (2, 'Scratching', 213, 'clipping', 123),
       (3, 'Pilling', 11, 'medicate', 10),
       (4, 'Groom', 5, 'brushings', 50),
       (4, 'Exercise', 1, 'run', 25.12),
       (1, 'Walks', 500, 'walks', 12351.31),
       (1, 'Baths', 53, 'baths', 1235),
       (2, 'Baths', 53, 'baths', 1235); 

O que eu gostaria de poder fazer é criar uma tabela com este formato

 +-------------+---------+---------+-----------------+---------+--------------+---------+----------+
 | [BillingId] | [Walks] | [Baths] | [Litter change] | [Groom] | [Scratching] | [Usage] | [Cost]   |
 +-------------+---------+---------+-----------------+---------+--------------+---------+----------+
 | 1           | 525     | 58      | 0               | 0       | 0            | 583     | 14037.95 |
 | 2           | 0       | 53      | 53              | 25      | 213          | 344     | 1591.21  |
 +-------------+---------+---------+-----------------+---------+--------------+---------+----------+

A única maneira que consegui pensar para fazer isso foi agregar a tabela vertical.

Fazendo algo como a seguinte consulta

SELECT MAX(BillingId), MAX(Feature), SUM(Usage), MAX(Measurement), SUM(Cost) 
FROM BillingData;

Mas então eu teria que juntar dinamicamente essas colunas na tabela Billing, especialmente porque o BillingData pode não ser o mesmo mês a mês. Por exemplo:

SELECT DISTINCT Feature FROM BillingData WHERE BillYear=2018 AND BillMonth=5;

É diferente de

SELECT DISTINCT Feature FROM BillingData WHERE BillYear=2018 and BillMonth=4;

Assim, enquanto as colunas BillingId, Walks, Baths, Litter change, Groom, Scratching, Usage, Cost são apropriadas para abril, as colunas de maio seriam apenas BillingId, Pilling, Groom, Exercise, Usage e Cost.

Acredito que uma tabela dinâmica pode ser o que eu preciso aqui, mas suspeito que ela precise ser dinâmica, pois as colunas precisariam ser diferentes para cada mês.

Não tenho certeza da melhor maneira de fazer isso. Alguma ajuda seria muito apreciada.

Supondo que você tenha uma tabela como

CREATE TABLE `t1` (
    `search_id` int(11) NOT NULL,
    `provider_id` bigint(20) unsigned NOT NULL,
    `status` text NOT NULL,
    `imported_at` int(11) NOT NULL,
    `created_at` int(11) NOT NULL,
    `latitude` double DEFAULT NULL,
    `longitude` double DEFAULT NULL,
    `moderation` tinyint(3) NOT NULL DEFAULT '0',
    PRIMARY KEY (`search_id`,`provider_id`),
    KEY `k_search_imported` (`search_id`,`imported_at`),
    KEY `k_scm` (`search_id`,`created_at`,`moderation`),
    KEY `k_sc` (`search_id`,`created_at`)
) ENGINE=InnoDB;

O que eu não tenho certeza é por que

SELECT COUNT(1) FROM t1;

Leva 19 minutos (preenchi a tabela com 1,2 milhão de linhas).

MariaDB [test] 10:53:58> show indexes from t1;
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          0 | PRIMARY           |            1 | search_id   | A         |       13478 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          0 | PRIMARY           |            2 | provider_id | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_search_imported |            1 | search_id   | A         |          30 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_search_imported |            2 | imported_at | A         |       46213 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_scm             |            1 | search_id   | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_scm             |            2 | created_at  | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_scm             |            3 | moderation  | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_sc              |            1 | search_id   | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_sc              |            2 | created_at  | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
9 rows in set (0.00 sec)

Eu me pergunto se o problema aqui é a cardinalidade. Minha pergunta é se os índices InnoDB devem ser ordenados pelas colunas de maior cardinalidade mais próximas do início do índice, ou seja, PRIMARY KEY (provider_id,search_id);ou isso importa?

Estou tentando remover duplicatas de uma tabela.

CREATE TABLE media_views (
  `companyid` INT (11) NOT NULL,
  `channel` INT(11) NOT NULL,
  `date` DATE NOT NULL,
  `views` INT(11) NOT NULL DEFAULT 0
  PRIMARY KEY (`companyid`,`channel`,`date`,`views`)
) ENGINE=InnoDB;

INSERT INTO media_views (companyid, channel, `date`, views)
     VALUES (1, 0,  '2015-10-01', 15),
            (1, 0,  '2015-10-01', 25),
            (1, 0,  '2015-10-01', 541),
            (1, 5,  '2015-10-01', 2),
            (1, 8,  '2015-10-01', 53),
            (1, 91, '2015-10-01', 8571),
            (3, 12, '2015-10-01', 11),
            (1, 0,  '2015-10-02', 1),
            (1, 0,  '2015-10-02', 13),
            (1, 5,  '2015-10-02', 17),
            (1, 91, '2015-10-03', 37),
            (3, 0,  '2015-10-03', 73);

Agora o problema é que tenho erros "duplicados", tenho registros que coincidem com as datas mas as visualizações são diferentes. Apenas os valores máximos das visualizações são realmente úteis. Então, posso resolver esse problema fazendo o seguinte.

SELECT companyid, channel, date, MAX(views) 
FROM media_views 
GROUP BY companyid, channel, date 
HAVING COUNT(1) >= 1;

Isso claramente me fornece o seguinte:

+-----------+---------+------------+------------+
| companyid | channel | date       | MAX(views) |
+-----------+---------+------------+------------+
| 1         | 0       | 2015-10-01 | 541        |
| 1         | 0       | 2015-10-02 | 13         |
| 1         | 5       | 2015-10-01 | 2          |
| 1         | 5       | 2015-10-02 | 17         |
| 1         | 8       | 2015-10-01 | 53         |
| 1         | 91      | 2015-10-01 | 8571       |
| 1         | 91      | 2015-10-03 | 37         |
| 3         | 0       | 2015-10-03 | 73         |
| 3         | 12      | 2015-10-01 | 11         |
+-----------+---------+------------+------------+
9 rows in set (0.11 sec)

O que é ótimo, mas como faço para voltar e excluir as linhas duplicadas e menores que o valor max(views)?

O cenário é o seguinte, há algum tempo um colega executou acidentalmente uma consulta semelhante a

 DELETE FROM app_i18n WHERE disabled = 0 and translation is null or translation = '';

Esta é a estrutura da tabela de app_i18n

CREATE TABLE app_i18n (
     `id` INTEGER(11) NOT NULL AUTO_INCREMENT,
     `category` INTEGER(11) unsigned NOT NULL,
     `keyword`  varchar(50) DEFAULT NULL,
     `language` varchar(2) NOT NULL DEFAULT 'en',
     `translation` text NOT NULL,
     `disabled` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY (`category`, `keyword`, `language`, `disabled`)
) ENGINE=InnoDB;

O problema resultante é que algumas dessas "traduções", embora vazias, estavam lá por um motivo. Portanto, uma restauração estava em ordem. De uma cópia antiga do banco de dados, ele restaurou todas as linhas que estavam vazias ou nulas e não foram desativadas.

O problema foi agravado por um número desconhecido de outros desenvolvedores lançando rapidamente "traduções" para corrigir os problemas que estavam enfrentando. Então pensei que a solução seria

SELECT id 
  FROM app_i18n 
 WHERE disabled = 0 
   AND (translation is null or translation = '') 
 GROUP BY category, keyword, language 
HAVING COUNT(id) > 1;

Essa consulta produziria as respostas que eu quero? ou seja, todos os registros que possuem categoria duplicada, palavras-chave no mesmo idioma que possuem duplicatas?

Informações básicas:

Eu tenho uma tabela contendo informações de upload, todos os dias recebo mais e mais uploads, mas a maioria das consultas que faço giram em torno de informações coletadas nos últimos 12 meses; freqüentemente usa uma escala de tempo ainda mais apertada e as consultas são restritas a informações coletadas nos últimos 30 dias

Este sistema existe desde 2004, quando havia 400 uploads, hoje existem mais de 2 milhões

Estrutura da tabela:

CREATE TABLE `data_mediagallery` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `contenttype` char(40) NOT NULL DEFAULT '',
  `filename` varchar(100) NOT NULL DEFAULT '',
  `injector` char(40) NOT NULL DEFAULT '',
  `hits` int(11) DEFAULT '0',
  `message` longtext,
  `date` datetime DEFAULT NULL,
  `title` varchar(80) NOT NULL DEFAULT '',
  `tags` varchar(255) NOT NULL DEFAULT '',
  `metadata` blob,
  `location` char(8) NOT NULL DEFAULT '',
  `uid` int(11) unsigned NOT NULL DEFAULT '0',
  `filesize` bigint(20) NOT NULL DEFAULT '0',
  `upload` datetime DEFAULT NULL,
  `privacy` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `width` int(10) unsigned NOT NULL DEFAULT '0',
  `height` int(10) unsigned NOT NULL DEFAULT '0',
  `offensive` int(10) unsigned NOT NULL DEFAULT '0',
  `sourcelocation` char(8) NOT NULL DEFAULT '',
  `autoblog` tinyint(1) NOT NULL DEFAULT '0',
  `extension` char(10) NOT NULL DEFAULT '',
  `filetype` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `conversiontime` float NOT NULL DEFAULT '0',
  `converttime` datetime DEFAULT NULL,
  `sender` varchar(100) NOT NULL DEFAULT '',
  `vhost` int(10) unsigned NOT NULL DEFAULT '0',
  `channel` int(10) unsigned NOT NULL DEFAULT '0',
  `rotation` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `ofilesize` bigint(20) NOT NULL DEFAULT '0',
  `moderationstatus` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `rating` decimal(8,6) DEFAULT NULL,
  `votecount` int(10) unsigned NOT NULL DEFAULT '0',
  `url` varchar(150) NOT NULL DEFAULT '',
  `geo_latitude` double DEFAULT NULL,
  `geo_longitude` double DEFAULT NULL,
  `length` decimal(8,2) DEFAULT '0.00',
  `parentid` int(11) NOT NULL DEFAULT '0',
  `language` char(2) NOT NULL DEFAULT '',
  `author` varchar(100) NOT NULL DEFAULT '',
  `context` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `externalid` varchar(255) DEFAULT NULL,
  `originalsaved` bit(1) NOT NULL DEFAULT b'1',
  `hidden` tinyint(4) NOT NULL DEFAULT '0',
  `commentcount` int(11) NOT NULL DEFAULT '0',
  `approvedcomments` int(11) NOT NULL DEFAULT '0',
  `notdeniedcomments` int(11) NOT NULL DEFAULT '0',
  `lastupdatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `channelleft` int(10) unsigned NOT NULL DEFAULT '0',
  `originalLocation` char(8) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `upload` (`upload`),
  KEY `vhostupload` (`vhost`,`upload`),
  KEY `vhostmodstatus` (`vhost`,`status`,`moderationstatus`,`uid`),
  KEY `complexfiletype` (`vhost`,`status`,`moderationstatus`,`filetype`,`channel`),
  KEY `vhostcontext` (`vhost`,`moderationstatus`,`context`,`parentid`,`status`,`filetype`),
  KEY `externalid` (`externalid`),
  KEY `externalcomments`.
  KEY `vhostchannel` (`vhost`,`status`,`moderationstatus`,`context`,`channelleft`)
) ENGINE=InnoDB;

Perguntas

Existe uma maneira de particionar a tabela que faria mais sentido? O particionamento faz sentido? Como faço para lidar com novos dados se eu particionar?

Dado o seguinte tipo de estrutura de dados.

    ID NOME TREELEFT TREERIGHT PAI
    1 XYZ 1 2 0
    2 ABC 3 6 0
    3 DEF 4 5 2
    4 HIJ 7 10 0
    5 KLM 8 9 4
    6 NOP 11 16 0
    7 QRS 12 15 6
    8 TUV 13 14 7

A metodologia é mais ou menos idêntica ao modelo de conjunto aninhado, exceto que incluí um pai, apenas para tornar minha vida um pouco mais fácil. ( https://en.wikipedia.org/wiki/Nested_set_model#Example ) Preciso encontrar uma maneira de detectar lacunas. Por exemplo, se ID=6 tivesse um treeright diferente, digamos 17. Tecnicamente, haveria uma lacuna de 1.

No momento, fiz isso em uma série de consultas diferentes.

Um para detectar se treeright não excede 2n, outro para detectar se treeright é sempre > que treeleft. Outro casal para garantir que nenhum treeleft ou treeright seja idêntico um ao outro.

No entanto, se eu pudesse adicionar apenas uma verificação para garantir que a árvore não tivesse lacunas, tenho certeza de que alguns dos outros testes se tornariam redundantes. Alguém pode pensar em uma boa maneira de fazer isso. Eu estive quebrando a cabeça com isso por um longo período de tempo.

Dadas as seguintes tabelas:

CREATE TABLE `longdescs` (
  `comment_id` int(11) NOT NULL AUTO_INCREMENT,
  `bug_id` mediumint(9) NOT NULL,
  `who` mediumint(9) NOT NULL,
  `bug_when` datetime NOT NULL,
  `work_time` decimal(7,2) NOT NULL DEFAULT '0.00',
  `thetext` mediumtext NOT NULL,
  `isprivate` tinyint(4) NOT NULL DEFAULT '0',
  `already_wrapped` tinyint(4) NOT NULL DEFAULT '0',
  `type` smallint(6) NOT NULL DEFAULT '0',
  `extra_data` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`comment_id`),
  KEY `longdescs_bug_id_idx` (`bug_id`),
  KEY `longdescs_who_idx` (`who`,`bug_id`),
  KEY `longdescs_bug_when_idx` (`bug_when`),
  KEY `comment_id` (`comment_id`),
  CONSTRAINT `fk_longdescs_bug_id_bugs_bug_id` FOREIGN KEY (`bug_id`) REFERENCES `bugs` (`bug_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_longdescs_who_profiles_userid` FOREIGN KEY (`who`) REFERENCES `profiles` (`userid`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=82727 DEFAULT CHARSET=utf8

CREATE TABLE `trace` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `comment_id` mediumint(9) NOT NULL,
  `type` varchar(255) NOT NULL,
  `short_hash` char(22) DEFAULT NULL,
  `stack_hash` char(22) DEFAULT NULL,
  `trace_text` mediumtext NOT NULL,
  `quality` double NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `trace_comment_id_idx` (`comment_id`),
  KEY `trace_short_hash_idx` (`short_hash`),
  KEY `trace_stack_hash_idx` (`stack_hash`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

ALTER TABLE trace ADD CONSTRAINT `fk_trace_comment_id_longdescs_comment_id` FOREIGN KEY (`comment_id`) REFERENCES `longdescs`(`comment_id`) ON UPDATE CASCADE ON DELETE CASCADE;
ERROR 1005 (HY000): Can't create table 'bugs4.#sql-4bb_6f1d' (errno: 150)

MariaDB [bugs4]> mostra avisos;

| Level  | Code | Message |
| Warning |  150 | Create table 'bugs4/#sql-4bb_6f1d' with foreign key     constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
 |
| Error   | 1005 | Can't create table 'bugs4.#sql-4bb_6f1d' (errno: 150)                                                                                                                       |

Alguma ideia de por que recebo esse erro? Para sua informação, pesquisei e parece que era um bug, mas não mais.

http://bugs.mysql.com/bug.php?id=16290

Dado que os campos de bits são apenas representações binárias de dados e precisam ser consultados de maneira um pouco "estranha".

Ele realmente fornece algum benefício usando um campo de bits para um valor booleano? Pelo que posso ver, parece sugerir que o espaço é o único benefício real.

Assumindo uma replicação mista de replicação baseada em instrução e replicação baseada em linha no MySQL 5.5. Como exatamente isso funciona em um cenário mestre-mestre?

O mestre que recebe a consulta executa e, em seguida, grava a instrução no log binário, que é retransmitida para o escravo para execução? Ou o mestre simplesmente escreve o que deve ser atualizado e então o escravo pega as alterações?

Também afirma que no MySQL 5.5 SBR é o padrão, mas muda para RBR quando necessário. Como sei quando é necessário? Existe uma maneira de forçá-lo a usar SBR para apenas uma consulta UPDATE ou INSERT?

É possível criar uma tabela a partir de um nome de variável? Por exemplo, tenho o seguinte procedimento armazenado que estou tentando escrever, mas não será compilado com a instrução create.

CREATE DEFINER = 'sabretooth'@'%' PROCEDURE `sp_archive_sabretooth`(
    IN vhost INTEGER(11),
    IN record_age DATETIME,
    IN delete_records BOOLEAN
)
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE i_exist BOOLEAN DEFAULT FALSE;
    DECLARE table_to_create, backup_table_name, database_name VARCHAR(255);
    DECLARE backup_tables_cursor CURSOR FOR 
            SELECT DISTINCT (INFORMATION_SCHEMA.COLUMNS.TABLE_NAME)
                       FROM INFORMATION_SCHEMA.COLUMNS
                      WHERE (INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME LIKE 'vhost%' 
                             OR
                             INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = 'mid') 
                         AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA LIKE 'sabretooth%';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;    

    INSERT INTO sabretooth.debug (message) VALUES (CONCAT('Initializing sp_archive_sabretooth; vhost = ', vhost, '; record age = ', record_age, '; delete_records = ', delete_records)); 

    SELECT INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME
      INTO database_name
      FROM INFORMATION_SCHEMA.SCHEMATA
     WHERE INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME LIKE 'sabretooth%'
       AND INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME <> 'sabretooth_archive';

      OPEN backup_tables_cursor;

    create_tables_loop: LOOP
        FETCH backup_tables_cursor INTO table_to_create;
        IF done THEN
            LEAVE create_tables_loop;
        END IF;
        CREATE DATABASE IF NOT EXISTS sabretooth_archive;

        SELECT CONCAT(database_name, '.', table_to_create,'_archive') INTO backup_table_name;

        INSERT INTO sabretooth.debug (message) VALUES (CONCAT('I am here 38 backup_table_name = ',backup_table_name));

        CREATE TABLE CONCAT('sabretooth_archive.',table_to_create) LIKE CONCAT(database_name,'.',table_to_create);
    END LOOP;
END

Acredito que o innodb tenha um único thread de leitura. No entanto, com o MariaDB, você pode definir vários threads de leitura. Não tenho certeza de qual seria o valor recomendado.

Perguntas

• Alguém tem alguma experiência com MariaDB e ajuste de desempenho?
• Você consegue pensar em algum recurso do MariaDB que o XtraDB usa e que o MySQL não usa?
• Existem configurações semelhantes que posso usar para explorar totalmente a funcionalidade aprimorada do XtraDB e Maria?

Uma pergunta relativamente simples, mas de todas as minhas pesquisas, não consegui encontrar uma resposta decente.

O que é tecnicamente mais correto, abrir o cursor depois que os manipuladores forem definidos ou antes de realmente usar o cursor?

Por exemplo,

BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END;

As declarações acima parecem ser padrão na maioria dos exemplos, no entanto, seria possível fazer

BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SELECT x,y,z INTO @X, @Y, @Z FROM TableA WHERE X=1;

  IF (@X >= @Y) THEN

      OPEN cur1;
      OPEN cur2;

      read_loop: LOOP
        FETCH cur1 INTO a, b;
        FETCH cur2 INTO c;
        IF done THEN
          LEAVE read_loop;
        END IF;
        IF b < c THEN
          INSERT INTO test.t3 VALUES (a,b);
        ELSE
          INSERT INTO test.t3 VALUES (a,c);
        END IF;
      END LOOP;

      CLOSE cur1;
      CLOSE cur2;
  ELSE 
      OPEN cur1;

      read_loop: LOOP
        FETCH cur1 INTO a, b;
        IF done THEN
          LEAVE read_loop;
        END IF;
          INSERT INTO test.t3 VALUES (a,b);
      END LOOP;

      CLOSE cur1;
  END IF;
END;

É possível desabilitar um gatilho momentaneamente, mas para apenas uma tabela.

Por exemplo, eu tenho tabela, TableA com um gatilho de inserção, atualização e exclusão. Eu também tenho uma tabela B com os mesmos gatilhos, mas eles afetam apenas certas colunas na tabela A.

Agora tenho uma consulta de atualização que usa ambas as tabelas. Eu sei que as atualizações na tabela A precisam disparar os gatilhos, mas as atualizações na tabela B definitivamente não precisam disparar os gatilhos. Então, eu gostaria de desabilitar esses gatilhos até que as atualizações sejam feitas.

Isso é possível? Estou usando o MySQL 5.1

[TERMO ADITIVO]

Aqui está uma tabela de gatilhos A essencialmente

BEGIN
    IF (OLD.status != 1 AND NEW.status = 2) THEN
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL) THEN
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id;
        END IF;
    ELSEIF (OLD.Status = 1 AND NEW.Status != 2) THEN
        IF (NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN
            INSERT INTO geo (datatype, foreignid, long, lat, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status);
        END IF;
    ELSEIF (NEW.status != 3) THEN  
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL AND (NEW.geo_lat IS NULL OR NEW.geo_long IS NULL)) THEN
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id;
        ElSEIF ((OLD.geo_lat IS NULL OR OLD.geo_long IS NULL) AND NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN
            INSERT INTO geo (datatype, foreignid, longitude, latitude, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status);
        ELSEIF (OLD.geo_lat!=NEW.geo_lat OR OLD.geo_long != NEW.geo_long OR OLD.status != NEW.status) THEN
            UPDATE geo SET lat = NEW.geo_lat, long = NEW.geo_long, status = NEW.status WHERE datatype IN (3,4) AND foreignid = NEW.id;
        END IF;
    END IF;
END

Aqui estão os gatilhos na tabela B essencialmente

CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`
    FOR EACH ROW
    BEGIN
       CALL sp-set-comment_count(NEW.`gid`);
    END;

Aqui está o procedimento armazenado que é disparado da tabela B

DELIMITER $$

CREATE PROCEDURE `sp_set-comment_count` (IN _id INT)
BEGIN
   -- AC   - All Count
   -- OLDAC- Old All Count
   DECLARE AC, OLDAC INT DEFAULT 0;

   SELECT COUNT(*) AS ac
     INTO AC
     FROM usergroups AS ug
LEFT JOIN usergroup_comments AS ugm ON ugm.`gid` = ug.`id`
LEFT JOIN mediagallery AS dm ON ugm.mid = dm.`id`
    WHERE dm.`status` NOT IN (200, 201, 202, 203, 204, 205)
      AND ug.`id` = _id;

   SELECT allCount
     INTO OLDAC
     FROM usergroups
    WHERE ug.`id` = _id;

IF (OLDAC <> AC) THEN 

   UPDATE usergroups
      SET allCount = AC,
    WHERE usergroups.`id` = _id;

END IF;

END $$

Eu criei um procedimento armazenado no mysql usando a seguinte sintaxe.

DROP PROCEDURE IF EXISTS `sp-set_comment_count`;

DELIMITER $$

CREATE PROCEDURE `sp_set-comment_count` (IN _id INT)
BEGIN
   -- AC   - AllCount
   DECLARE AC INT DEFAULT 0;

   SELECT COUNT(*) AS ac
     INTO AC
     FROM usergroups AS ug
LEFT JOIN usergroup_comments AS ugm ON ugm.`gid` = ug.`id`
LEFT JOIN mediagallery AS dm ON ugm.mid = dm.`id`
    WHERE dm.`status` NOT IN (200, 201, 202, 203, 204, 205)
      AND ug.`id` = _id;

   UPDATE usergroups
      SET allCount = AC,
    WHERE usergroups.`id` = _id;

END $$
DELIMITER ;

Para sua informação, simplifiquei bastante o procedimento armazenado, mas sei que funciona sem problemas.

O que eu gostaria de poder fazer é configurar um gatilho de usergroup_comments que funciona assim.

DROP TRIGGER IF EXISTS `usergroups_comments_insert` 

CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`
    FOR EACH ROW
    BEGIN
       CALL sp-set-comment_count(NEW.`gid`);
    END;

Mas, por algum motivo, toda vez que eu faço o mysql lança um erro para mim que é menos do que útil, afirmando que há um erro de sintaxe na linha 4.

Eu vasculhei a documentação do mysql e encontrei algumas informações sobre restrições de gatilhos, mas achei bastante complicado.

http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html

Qualquer idéia será útil.

Atualmente, tenho a replicação do MySQL ocorrendo entre dois mestres bidirecionalmente, um em um data center localmente, outro no Amazon EC2.

Tudo parece funcionar normalmente em termos de replicação, sem problemas em si, exceto para consultas ocasionais que causam uma colisão, mas essas são poucas e distantes entre si. Recentemente, configurei o mysql-proxy para tentar equilibrar a carga dos dois servidores, o balanceamento de carga realmente entra em ação depois que a máquina local recebe 40 ou mais conexões, ponto em que as conexões de banco de dados subsequentes são embaralhadas para a máquina EC2.

Algo que notamos recentemente é que o proxy mysql nos notificará que existem 41 conexões e iniciará seu balanceamento. No entanto, quando eu me conecto à máquina local e faço uma SHOW PROCESSLIST;conexão, ela pode fornecer apenas 30 conexões.

Alguém tem alguma ideia do porquê disso talvez?

Além disso, como resultado da emissão do SHOW PROCESSLIST;comando, notei que há muitas consultas em execução em ambas as máquinas que afirmam que estão sendo executadas há mais de 5.000 segundos. Tenho certeza de que essas consultas são "zumbis", mas alguém sabe por que elas foram criadas?

Para sua informação, estamos executando o mysql versão 5.1.54 nas versões mais recentes do ubuntu e do debian.

Qualquer ideia seria extremamente útil.

[Termo aditivo]

Acontece que não estamos usando mysql_pconnect e, de fato, usando as bibliotecas mysqli. Ainda não consegui descobrir por que isso está acontecendo e relatarei assim que descobrir.

Eu tenho uma consulta no mysql da seguinte forma.

SELECT DISTINCT `tableA`.`ColA`, `tableA`.`ColB`, `tableB`.`ColA`, ...
FROM `tableA`
LEFT JOIN `tableB` ON `tableA`.`colC` = `tableB`.`colA`
WHERE <whereconditions>
ORDER BY `tableA`.`ColA` DESC `tableB`.`ColA` ASC
LIMIT 0,20

Agora a execução da consulta leva aproximadamente 13 segundos devido ao tamanho das respectivas tabelas. No entanto, esta consulta é muito mais rápida (ou seja, 2 segundos)

SELECT `tableA`.`ColA`, `tableA`.`ColB`, `tableB`.`ColA`, ...
FROM `tableA`
LEFT JOIN `tableB` ON `tableA`.`colC` = `tableB`.`colA`
WHERE <whereconditions>
ORDER BY `tableA`.`ColA` DESC `tableB`.`ColA` ASC
LIMIT 0,20

Estou ciente de que isso pode resultar em linhas não distintas, mas não tenho certeza se essa situação foi resolvida pelo código php no front-end. Então, na verdade, quero comparar os dados dos dois conjuntos de resultados, ambos resultando em aproximadamente 83.000 linhas, se não forem limitados.

Alguém pode pensar em uma maneira de fazer isso em SQL em vez de fazê-lo por meio de um script php. Eu comparei as primeiras 100 linhas a olho nu sem nenhum problema, mas como todos sabemos, basta uma exceção na 101ª linha para bagunçar tudo. Portanto, preciso de uma consulta que possa executar algumas vezes de vez em quando para ver se há linhas em um conjunto de resultados que não estão no outro.