Início / dba / 问题

All perguntas(dba)

Martin Hope
Robert Zaremba
Asked: 2014-10-26 04:00:01 +0800 CST
  • 17

Estou tentando encontrar informações sobre funções definidas pelo usuário do PostgreSQL no desempenho de linguagens procedurais para tarefas em tempo real.

  1. Como eles se comparam às funções internas?
  2. Existe alguma diferença (em sobrecarga) como o Postgres chama / gerencia as funções plpython vs plpgsql vs pllua (estou interessado no lado da integração / contexto / transferência de dados do Postgres, não na própria VM)?
  3. O contexto é uma grande sobrecarga? Posso usá-lo para mapeamento de dados em tempo real (digamos 1000 consultas/s))
  4. Existe algum benefício em escrever funções definidas pelo usuário em plpgsql e depois em outro pg/idioma? Na documentação eles enumeram vantagens, mas acho que se aplicam a todas as linguagens procedurais do postgresql.

Descobertas relacionadas:

postgresql plpgsql
Martin Hope
qazwsx
Asked: 2014-10-22 06:22:55 +0800 CST
  • 17

Estou tentando iniciar o servidor e depois corrigir o usuário root

$ mysql -u root mysql
$mysql> UPDATE user SET Password=PASSWORD('my_password') where USER='root';
$mysql> FLUSH PRIVILEGES;

Mas o servidor não pode ser iniciado:

$ mysqld --skip-grant-tables
2014-10-21 07:18:24 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-10-21 07:18:24 1967 [Warning] Can't create test file /usr/local/mysql-5.6.21-osx10.8-x86_64/data/x4430.lower-test
2014-10-21 07:18:24 1967 [Warning] Can't create test file /usr/local/mysql-5.6.21-osx10.8-x86_64/data/x4430.lower-test
2014-10-21 07:18:24 1967 [Note] Plugin 'FEDERATED' is disabled.
2014-10-21 07:18:24 1967 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-10-21 07:18:24 1967 [Note] InnoDB: The InnoDB memory heap is disabled
2014-10-21 07:18:24 1967 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-10-21 07:18:24 1967 [Note] InnoDB: Memory barrier is not used
2014-10-21 07:18:24 1967 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-10-21 07:18:24 1967 [Note] InnoDB: Using CPU crc32 instructions
2014-10-21 07:18:24 1967 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-10-21 07:18:24 1967 [Note] InnoDB: Completed initialization of buffer pool
2014-10-21 07:18:24 1967 [ERROR] InnoDB: ./ibdata1 can't be opened in read-write mode
2014-10-21 07:18:24 1967 [ERROR] InnoDB: The system tablespace must be writable!
2014-10-21 07:18:24 1967 [ERROR] Plugin 'InnoDB' init function returned error.
2014-10-21 07:18:24 1967 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2014-10-21 07:18:24 1967 [ERROR] Unknown/unsupported storage engine: InnoDB
2014-10-21 07:18:24 1967 [ERROR] Aborting

2014-10-21 07:18:24 1967 [Note] Binlog end
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'partition'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_METRICS'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_CMPMEM'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_CMP'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_LOCKS'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_TRX'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'BLACKHOLE'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'ARCHIVE'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'MRG_MYISAM'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'MyISAM'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'MEMORY'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'CSV'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'sha256_password'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'mysql_old_password'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'mysql_native_password'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'binlog'
2014-10-21 07:18:24 1967 [Note] mysqld: Shutdown complete

Mac OS X 10.9.5 e MySQL: mysql Ver 14.14 Distrib 5.6.21, para osx10.8 (x86_64) usando o wrapper EditLine

Permissões:

$ ls -la /usr/local/mysql-5.6.21-osx10.8-x86_64
total 232
drwxr-xr-x  17 qazwsx  wheel    578 Oct 20 23:27 .
drwxrwxr-x  23 qazwsx  admin    782 Oct 20 23:27 ..
-rw-r--r--   1 qazwsx  wheel  17987 Sep 11 06:53 COPYING
-rw-r--r--   1 qazwsx  wheel  87980 Sep 11 06:53 INSTALL-BINARY
-rw-r--r--   1 qazwsx  wheel   2496 Sep 11 06:53 README
drwxr-xr-x  47 qazwsx  wheel   1598 Oct 20 23:27 bin
drwxr-xr-x  12 _mysql  wheel    408 Oct 21 07:13 data
drwxr-xr-x   5 qazwsx  wheel    170 Oct 20 23:27 docs
drwxr-xr-x  45 qazwsx  wheel   1530 Oct 20 23:27 include
drwxr-xr-x  12 qazwsx  wheel    408 Oct 20 23:27 lib
drwxr-xr-x   4 qazwsx  wheel    136 Sep 11 07:13 man
-rw-r--r--   1 qazwsx  wheel    943 Oct 20 23:25 my.cnf
drwxr-xr-x  19 qazwsx  wheel    646 Oct 20 23:27 mysql-test
drwxr-xr-x   3 qazwsx  wheel    102 Oct 20 23:27 scripts
drwxr-xr-x  36 qazwsx  wheel   1224 Oct 20 23:27 share
drwxr-xr-x  28 qazwsx  wheel    952 Oct 20 23:27 sql-bench
drwxr-xr-x   8 qazwsx  wheel    272 Oct 20 23:27 support-files


$ ls -la /usr/local/mysql-5.6.21-osx10.8-x86_64/data/
total 221248
drwxr-xr-x  12 _mysql  wheel       408 Oct 21 07:13 .
drwxr-xr-x  17 qazwsx  wheel       578 Oct 20 23:27 ..
-rw-rw----   1 _mysql  wheel        56 Oct 20 23:28 auto.cnf
drwx------   3 _mysql  wheel       102 Oct 20 23:45 cloudplatform
-rw-rw----   1 _mysql  wheel  50331648 Oct 21 07:13 ib_logfile0
-rw-rw----   1 _mysql  wheel  50331648 Oct 20 23:25 ib_logfile1
-rw-rw----   1 _mysql  wheel  12582912 Oct 21 07:13 ibdata1
drwx------  81 _mysql  wheel      2754 Oct 20 23:25 mysql
drwx------  55 _mysql  wheel      1870 Oct 20 23:25 performance_schema
drwxr-xr-x   3 _mysql  wheel       102 Oct 20 23:27 test
-rw-r-----   1 _mysql  wheel     24444 Oct 21 07:13 x4430.err
-rw-rw----   1 _mysql  wheel         5 Oct 21 07:13 x4430.pid
mysql permissions
Martin Hope
Nathan Long
Asked: 2014-10-03 08:17:10 +0800 CST
  • 17

A documentação do PostgreSQL sobre WITH mostra o seguinte exemplo:

WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

Também observa:

Uma propriedade útil das consultas WITH é que elas são avaliadas apenas uma vez por execução da consulta pai, mesmo que sejam referidas mais de uma vez pela consulta pai ou pelas consultas irmãs WITH.

Vejo que WITHpode ser usado para outras coisas, como avaliação recursiva. Mas no exemplo acima, existe alguma diferença importante entre usar WITHe criar tabelas temporárias?

postgresql
Martin Hope
tptcat
Asked: 2014-09-06 03:35:18 +0800 CST
  • 17

Estou desenvolvendo um aplicativo da web que dará suporte à autenticação do usuário com funções correspondentes para cada usuário. Além disso, meus usuários podem ser de diferentes tipos e ter diferentes campos associados a eles. Alguns dos campos que cada usuário possui serão os mesmos, como em:

email, password, first_name, last_name, etc.

Mas alguns dos campos para diferentes tipos de usuário serão diferentes. Por exemplo:

User Type: Instructor
Fields unique to this type of user
----------------------------------
hourly_rate, tax_status

==================================

User Type: Student   
Fields unique to this type of user
----------------------------------
instrument, monthly_charge, program

==================================

User Type: Employee
Fields unique to this type of user
----------------------------------
hourly_rate, location

Este é um breve exemplo dos tipos de campos que podem ser semelhantes e exclusivos entre esses tipos de usuários.

As possíveis configurações que pensei são:

Table: `users`; contains all similar fields as well as a `user_type_id` column (a foreign key on `id` in `user_types`
Table: `user_types`; contains an `id` and a `type` (Student, Instructor, etc.)
Table: `students`; contains fields only related to students as well as a `user_id` column (a foreign key of `id` on `users`)
Table: `instructors`; contains fields only related to instructors as well as a `user_id` column (a foreign key of `id` on `users`)
etc. for all `user_types`

ou:

Table: `users`; contains all possible columns for all users and allow columns that could be filled for one user type but not another to be NULL

Minha pergunta: uma delas é uma abordagem melhor do que a outra ou ambas são terríveis e devo olhar para algo totalmente diferente?

mysql database-design
Martin Hope
Robin Heller
Asked: 2014-08-11 08:41:15 +0800 CST
  • 17

Tentei resolver o seguinte problema por cerca de uma hora e ainda não consegui mais.

Ok, eu tenho uma tabela (MyISAM):

+---------+-------------+------+-----+-------------------+----------------+
| Field   | Type        | Null | Key | Default           | Extra          |
+---------+-------------+------+-----+-------------------+----------------+
| id      | int(11)     | NO   | PRI | NULL              | auto_increment |
| http    | smallint(3) | YES  | MUL | 200               |                |
| elapsed | float(6,3)  | NO   |     | NULL              |                |
| cached  | tinyint(1)  | YES  |     | NULL              |                |
| ip      | int(11)     | NO   |     | NULL              |                |
| date    | timestamp   | NO   | MUL | CURRENT_TIMESTAMP |                |
+---------+-------------+------+-----+-------------------+----------------+

Por favor, não se importe com os índices, eu estive brincando tentando encontrar uma solução. Agora, aqui está a minha pergunta.

SELECT http,
COUNT( http )  AS count 
FROM reqs
WHERE DATE(date) >= cast(date_sub(date(NOW()),interval 24 hour) as datetime)
GROUP BY http
ORDER BY count;

a tabela está armazenando informações sobre solicitações da Web recebidas, portanto, é um banco de dados bastante grande.

+-----------+
| count(id) |
+-----------+
|    782412 |
+-----------+

observe que não há melhor maneira de definir uma chave primária, pois a coluna id será o único identificador exclusivo que tenho. A consulta mencionada acima leva cerca de 0,6 a 1,6 segundos para ser executada.

Qual índice seria inteligente? Eu imaginei que a data de indexação me dará cardinalidade "ruim" e, portanto, o MySQL não a usará. http também é uma má escolha, pois existem apenas cerca de 20 valores diferentes possíveis.

Obrigado por sua ajuda!

Atualização 1 , adicionei um índice em (http, data) como sugerido pelo ypercube:

mysql> CREATE INDEX httpDate ON reqs (http, date);

e usou sua consulta, mas teve um desempenho igualmente ruim. O índice adicionado:

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| reqs  |          0 | PRIMARY  |            1 | id          | A         |      798869 |     NULL | NULL   |      | BTREE      |         |
| reqs  |          1 | httpDate |            1 | http        | A         |          19 |     NULL | NULL   | YES  | BTREE      |         |
| reqs  |          1 | httpDate |            2 | date        | A         |       99858 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

e o EXPLIQUE

+----+--------------------+-------+-------+---------------+----------+---------+------+-------+-----------------------------------------------------------+
| id | select_type        | table | type  | possible_keys | key      | key_len | ref  | rows  | Extra                                                     |
+----+--------------------+-------+-------+---------------+----------+---------+------+-------+-----------------------------------------------------------+
|  1 | PRIMARY            | r     | range | NULL          | httpDate | 3       | NULL |    20 | Using index for group-by; Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | ri    | ref   | httpDate      | httpDate | 3       | func | 41768 | Using where; Using index                                  |
+----+--------------------+-------+-------+---------------+----------+---------+------+-------+-----------------------------------------------------------+

Versão do servidor MySQL:

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.1.73              |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | redhat-linux-gnu    |
+-------------------------+---------------------+
5 rows in set (0.00 sec)
mysql performance
Martin Hope
Aaron Johnson
Asked: 2014-07-25 13:10:07 +0800 CST
  • 17

Abordo todos vocês humildemente como alguém que NÃO é um DBA e tenho certeza de que minha pergunta está repleta de deficiências conceituais e "depende de" minas terrestres. Também tenho certeza de que todos vocês que decidirem responder vão querer muito mais detalhes do que posso fornecer atualmente.

Dito isso, estou curioso sobre o seguinte cenário em geral:

  • Digamos que eu tenha duas consultas não triviais.
  • A consulta 1 requer em média 2 minutos para ser concluída.
  • A consulta 2 requer em média 5 minutos para ser concluída.

Se eu executá-los em série, um após o outro, espero que leve 7 minutos para ser concluído em média. Isso é razoável?

Mais do que isso, no entanto, e se eu executar as duas consultas simultaneamente? Duas conexões separadas ao mesmo tempo.

  • Em que condições eu esperaria ver um aumento de velocidade? (Tempo total < 7 minutos)
  • Em que condições eu esperaria ver uma desaceleração? (Tempo total > 7 minutos)

Agora, se eu tivesse 1.000 consultas não triviais em execução simultaneamente, tenho um palpite de que isso resultaria em uma desaceleração geral. Nesse caso, onde provavelmente estaria o gargalo? Processador? BATER? Dirige?

Mais uma vez, sei que provavelmente é impossível responder à pergunta com precisão sem conhecer os detalhes (o que não tenho). Estou procurando algumas diretrizes gerais para pensar ao fazer as seguintes perguntas:

  • Em que circunstâncias as consultas simultâneas resultam em uma aceleração geral?
  • Em que circunstâncias as consultas simultâneas resultam em uma desaceleração geral?
postgresql performance
Martin Hope
Leland Richardson
Asked: 2014-07-04 18:21:17 +0800 CST
  • 17

Eu tenho uma situação em que estou tendo impasses e acho que reduzi os culpados, mas não tenho certeza do que posso fazer para corrigi-lo.

Isso ocorre em um ambiente de produção executando o SQL Server 2008 R2.

Para lhe dar uma visão um pouco simplificada da situação:

Tenho 3 tabelas conforme definido abaixo:

TABLE activity (
    id, -- PK
    ...
)

TABLE member_activity (
    member_id, -- PK col 1
    activity_id, -- PK col 2
    ...
)

TABLE follow (
    id, -- PK
    follower_id,
    member_id,
    ...
)

A member_activitytabela tem uma chave primária composta definida como member_id, activity_id, porque só preciso procurar dados nessa tabela dessa maneira.

Eu também tenho um índice não clusterizado em follow:

CREATE NONCLUSTERED INDEX [IX_follow_member_id_includes] 
ON follow ( member_id ASC ) INCLUDE ( follower_id )

Além disso, tenho uma visão vinculada ao esquema network_activityque é definida da seguinte forma:

CREATE VIEW network_activity
WITH SCHEMABINDING
AS

SELECT
    follow.follower_id as member_id,
    member_activity.activity_id as activity_id,
    COUNT_BIG(*) AS cb
FROM member_activity
INNER JOIN follow ON follow.member_id = member_activity.member_id
INNER JOIN activity ON activity.id = member_activity.activity_id
GROUP BY follow.follower_id, member_activity.activity_id

Que também possui um índice clusterizado exclusivo:

CREATE UNIQUE CLUSTERED INDEX [IX_network_activity_unique_member_id_activity_id] 
ON network_activity
(
    member_id ASC,
    activity_id ASC
)

Agora, tenho dois procedimentos armazenados em um impasse. Eles passam pelo seguinte processo:

-- SP1: insert activity
-----------------------
INSERT INTO activity (...)
SELECT ... FROM member_activity WHERE member_id = @a AND activity_id = @b
INSERT INTO member_activity (...)


-- SP2: insert follow
---------------------
SELECT follow WHERE member_id = @x AND follower_id = @y
INSERT INTO follow (...)

Esses dois procedimentos são executados em isolamento READ COMMITTED. Consegui consultar a saída de eventos estendidos 1222 e interpretei o seguinte em relação aos impasses:

O SP1 está aguardando um RangeS-Sbloqueio de chave no IX_follow_member_id_includesíndice enquanto o SP2 mantém um bloqueio conflitante (X)

O SP2 está aguardando um Sbloqueio de modo PK_member_activity enquanto o SP1 mantém um bloqueio conflitante (X)

O impasse parece estar acontecendo na última linha de cada consulta (as inserções). O que não está claro para mim é por que o SP1 está querendo um bloqueio no IX_follow-member_id_includesíndice. O único link, para mim, parece ser desta exibição indexada, e é por isso que o incluí.

Qual seria a melhor maneira de evitar que esses impasses aconteçam? Qualquer ajuda seria muito apreciada. Não tenho muita experiência em resolver problemas de impasse.

Por favor, deixe-me saber se há mais alguma informação que eu possa fornecer que possa ajudar!

Desde já, obrigado.

Editar 1: Adicionando mais algumas informações por solicitação.

Aqui está a saída 1222 deste impasse:

<deadlock>
    <victim-list>
        <victimProcess id="process4c6672748" />
    </victim-list>
    <process-list>
        <process id="process4c6672748" taskpriority="0" logused="332" waitresource="KEY: 8:72057594104905728 (25014f77eaba)" waittime="581" ownerId="474698706" transactionname="INSERT" lasttranstarted="2014-07-03T17:03:12.287" XDES="0x298487970" lockMode="RangeS-S" schedulerid="1" kpid="972" status="suspended" spid="79" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-07-03T17:03:12.283" lastbatchcompleted="2014-07-03T17:03:12.283" lastattention="2014-07-03T10:25:00.283" clientapp=".Net SqlClient Data Provider" hostname="WIN08CLYDESDALE" hostpid="4596" loginname="TechPro" isolationlevel="read committed (2)" xactid="474698706" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
            <executionStack>
                <frame procname="" line="7" stmtstart="1194" stmtend="1434" sqlhandle="0x02000000a26bb72a2b220406876cad09c22242e5265c82e6" />
                <frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000" />
            </executionStack>
            <inputbuf> <!-- SP 1 --> </inputbuf>
        </process>
        <process id="process6cddc5b88" taskpriority="0" logused="456" waitresource="KEY: 8:72057594098679808 (89013169fc76)" waittime="567" ownerId="474698698" transactionname="INSERT" lasttranstarted="2014-07-03T17:03:12.283" XDES="0x30c459970" lockMode="S" schedulerid="4" kpid="4204" status="suspended" spid="70" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-07-03T17:03:12.283" lastbatchcompleted="2014-07-03T17:03:12.283" lastattention="2014-07-03T15:04:55.870" clientapp=".Net SqlClient Data Provider" hostname="WIN08CLYDESDALE" hostpid="4596" loginname="TechPro" isolationlevel="read committed (2)" xactid="474698698" currentdb="8" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
                <frame procname="" line="18" stmtstart="942" stmtend="1250" sqlhandle="0x03000800ca458d315ee9130100a300000100000000000000" />
            </executionStack>
            <inputbuf> <!-- SP 2 --> </inputbuf>
        </process>
    </process-list>
    <resource-list>
        <keylock hobtid="72057594104905728" dbid="8" objectname="" indexname="" id="lock33299fc00" mode="X" associatedObjectId="72057594104905728">
            <owner-list>
                <owner id="process6cddc5b88" mode="X" />
            </owner-list>
            <waiter-list>
                <waiter id="process4c6672748" mode="RangeS-S" requestType="wait" />
            </waiter-list>
        </keylock>
        <keylock hobtid="72057594098679808" dbid="8" objectname="" indexname="" id="lockb7e2ba80" mode="X" associatedObjectId="72057594098679808">
            <owner-list>
                <owner id="process4c6672748" mode="X" />
            </owner-list>
            <waiter-list>
                <waiter id="process6cddc5b88" mode="S" requestType="wait" />
            </waiter-list>
        </keylock>
    </resource-list>
</deadlock>

Nesse caso,

AssociateObjectId 72057594098679808 corresponde a member_activity, PK_member_activity

AssociateObjectId 72057594104905728 corresponde a follow, IX_follow_member_id_includes

Além disso, aqui está uma imagem mais precisa do que o SP1 e o SP2 estão fazendo

-- SP1: insert activity
-----------------------
DECLARE @activityId INT

INSERT INTO activity (field1, field2)
VALUES (@field1, @field2)

SET @activityId = SCOPE_IDENTITY();

IF NOT EXISTS(
    SELECT TOP 1 member_id 
    FROM member_activity 
    WHERE member_id = @m1 AND activity_id = @activityId
)
    INSERT INTO member_activity (member_id, activity_id, field1)
    VALUES (@m1, @activityId, @field1)

IF NOT EXISTS(
    SELECT TOP 1 member_id 
    FROM member_activity 
    WHERE member_id = @m2 AND activity_id = @activityId
)
    INSERT INTO member_activity (member_id, activity_id, field1)
    VALUES (@m2, @activityId, @field1)

também SP2:

-- SP2: insert follow
---------------------

IF NOT EXISTS(
    SELECT TOP 1 1 
    FROM follow
    WHERE member_id = @memberId AND follower_id = @followerId
)
    INSERT INTO follow (member_id, follower_id)
    VALUES (@memberId, @followerId)

Editar 2: depois de reler os comentários, pensei em adicionar algumas informações sobre quais colunas são chaves estrangeiras também ...

  • member_activity.member_idé uma chave estrangeira para uma membertabela
  • member_activity.activity_idé uma chave estrangeira para a activitytabela
  • follow.member_idé uma chave estrangeira para uma membertabela
  • follow.follower_idé uma chave estrangeira para uma membertabela

Atualização 1:

Fiz algumas alterações que pensei que poderiam ajudar a evitar o impasse, sem sorte.

As alterações que fiz foram as seguintes:

-- SP1: insert activity
-----------------------
DECLARE @activityId INT

INSERT INTO activity (field1, field2)
VALUES (@field1, @field2)

SET @activityId = SCOPE_IDENTITY();

MERGE member_activity WITH ( HOLDLOCK ) as target
USING (SELECT @m1 as member_id, @activityId as activity_id, @field1 as field1) as source
    ON target.member_id = source.member_id
    AND target.activity_id = source.activity_id
WHEN NOT MATCHED THEN
    INSERT (member_id, activity_id, field1)
    VALUES (source.member_id, source.activity_id, source.field1)
;

MERGE member_activity WITH ( HOLDLOCK ) as target
USING (SELECT @m2 as member_id, @activityId as activity_id, @field1 as field1) as source
    ON target.member_id = source.member_id
    AND target.activity_id = source.activity_id
WHEN NOT MATCHED THEN
    INSERT (member_id, activity_id, field1)
    VALUES (source.member_id, source.activity_id, source.field1)
;

e com SP2:

-- SP2: insert follow
---------------------

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION

IF NOT EXISTS(
    SELECT TOP 1 1 
    FROM follow WITH ( UPDLOCK )
    WHERE member_id = @memberId AND follower_id = @followerId
)
    INSERT INTO follow (member_id, follower_id)
    VALUES (@memberId, @followerId)

COMMIT

Com essas duas mudanças, parece que ainda estou tendo impasses.

Se houver mais alguma coisa que eu possa fornecer, por favor me avise. Obrigado.

sql-server deadlock
Martin Hope
noisebleed
Asked: 2014-07-04 07:00:30 +0800 CST
  • 17

Estou usando estas etapas para criar uma tabela my_user, que já existia, mas de alguma forma desapareceu do meu banco de dados my_db:

mysql> USE my_db;
mysql> DROP TABLE my_user;
mysql> ERROR 1051 (42S02): Unknown table 'my_user'
mysql> CREATE TABLE my_user (id INT AUTO_INCREMENT NOT NULL, username VARCHAR(255), group_id VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
mysql> ERROR 1005 (HY000): Can't create table 'my_db.my_user' (errno: -1)

Tentei # mysqladmin flush-tablese repeti os passos acima, mas não foi útil. Além disso, reiniciei o mysqlserviço, mas não adianta.

Alguma ideia? O Google falhou comigo até agora. Obrigado.

Informação extra:

mysql> SHOW engine innodb STATUS;
------------------------
LATEST FOREIGN KEY ERROR
------------------------
140703 15:15:09 Error in foreign key constraint of table my_db/my_user
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
  CONSTRAINT "FK_CFBD431E285FAC6D" FOREIGN KEY ("group_id") REFERENCES "my_group" ("id")
mysql innodb
Martin Hope
Andy Davis
Asked: 2014-04-03 11:04:37 +0800 CST
  • 17

Versão rápida:

Que comando devo emitir para permitir que um proprietário de banco de dados permita que ele acesse tabelas neste banco de dados e isso pode ser feito a partir da conta desse proprietário?

Versão mais longa:

Estou criando um banco de dados no RDS. Eu tenho um usuário 'root' que configurei com a Amazon.

A Amazon cria automaticamente a função de grupo 'rds_superuser', que é muito privilegiada, mas na verdade não é um superusuário.

Estou criando um banco de dados e um usuário para a aplicação da seguinte forma:

create database master_integration;
CREATE ROLE master_application LOGIN ENCRYPTED PASSWORD '...' VALID UNTIL 'infinity';
GRANT ALL ON DATABASE master_integration TO GROUP rds_superuser WITH GRANT OPTION;
GRANT ALL ON DATABASE master_integration TO GROUP master_application;

\c master_integration;
ALTER DEFAULT PRIVILEGES GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO rds_superuser;

Atualizei este script para refletir as sugestões de Craig Ringer sobre como devo lidar com isso.

Quando o aplicativo se conecta (com as credenciais master_application), ele cria (e, portanto, possui) as tabelas.

Meu problema é que não consigo usar meu login administrativo (rootish) para executar consultas porque esse usuário não tem privilégios na tabela.

Consegui resolver isso antes executando o seguinte na conta do aplicativo:

GRANT ALL privileges ON ALL TABLES IN SCHEMA public to rds_superuser;

Mas parece hacky ter um usuário subordinado concedendo privs de volta a um usuário administrativo.

Então... Existe um comando que eu possa executar antes ou depois de criar as tabelas do aplicativo que garantirá que o proprietário do banco de dados possa acessar as tabelas dentro do banco de dados?

atualizar depois de tentar novamente alterar privilégios padrão...

Isso ainda não concede acesso às tabelas; Eu vejo isso sendo sugerido em outro lugar e faz todo o sentido, mas não está funcionando para mim. De um shell psql:

master_integration=> \ddp
                           Default access privileges
      Owner       | Schema | Type  |             Access privileges             
------------------+--------+-------+-------------------------------------------
 integration_root |        | table | integration_root=arwdDxt/integration_root+
                  |        |       | rds_superuser=arwdDxt/integration_root
(1 row)

master_integration=> \dp users
                           Access privileges
 Schema | Name  | Type  | Access privileges | Column access privileges 
--------+-------+-------+-------------------+--------------------------
 public | users | table |                   | 
(1 row)

Integration_root é meu usuário superusuário e users é uma tabela dentro do meu banco de dados.

Atualizar

Recebi uma resposta bastante inútil de alguém da Amazon.

Eles me pediram para chamar ALTER DEFAULT PRIVILEGES do login master_application. Embora isso provavelmente funcione, não responderia à minha pergunta (que é como faço isso acontecer apenas na conta rds_superuser).

Pedi que esclarecessem isso e eles foram embora.

postgresql amazon-rds
Prev
Próximo