Fragmentation with PXC setup using an ordered UUID as PK I am looking to implement a table which will accessable via a Percona XtraDB cluster of 3+ nodes. I am expecting it to grow at about 1mil rows per month and the data will be kept for about 2 years (24mil rows).

The schema is reletively simple, but for brevity I will ommit the boring parts, using a PK of a binary(16) with an ordered UUID value. Details of this can be seen here (https://www.percona.com/blog/2014/12/19/store-uuid-optimized-way/).

On a single machine this would/should always result in a nice time-based insertion order (due to the ordered UUID v1) and this will keep the data in its intended order within the structure. However, if I start using multiple nodes in a PXC then the UUID being generated could be out of order time-wise on different hosts. Even keeping clock drift to an absolute minimum, with round trips across multiple datacenters, I could not be 100% sure that a timestamp would always be after the last one.

My conclusion would be to make a BIGINT field, with AUTO_INCREMENT enabled, the first part in a compound PK - but then, as far as the DB is concerned, I may as well do away with the UUID and just rely on the BIGINT.

Does anyone have any thoughts on this; am I missing something? It seems that the use of a ordered UUID v1 in a cluster can actually make things worse...

Good.  You understand the problem inherit in UUIDs, and understand how to "fix" it.

Let's start from the other end...  Since you are keeping the data for 2 years, let's start with partitioning by date.  I don't care whether the data is INT, TIMESTAMP, DATETIME, BIGINT, or a rearranged V1 UUID.  (However, you should probably use an date that matches your application.)

PARTITION by the date into 24 partitions.  Each month, DROP the oldest partition and REORGANIZE the future partition into future and nextmonth.  See my blog for more details.

OK, that solves the problem you have not yet encountered -- the ugly overhead of deleting a million rows a month.

But, your concern was about ordering of the clock.  It is not really a problem.  And it is not worth chasing.  Even if an INSERT is a second late -- or even an hour late -- it will make very little difference in the overall performance of the inserts.

Sure, the 'next' row onto the 'end' of a table is well optimized.  But inserting it somewhere in the 'last' block, or even last 100 blocks, is efficient enough.  At best, InnoDB keeps blocks not quite full.  At steadystate with a lot of churn, a BTree is 69% full.  For "Point queries" this number is mostly irrelevant.  For range scans, it has some impact.  I suggest that preventing fragmentation is more costly than ignoring it.

Even with just AUTO_INCREMENT, you cannot be assured that rows will be inserted visible in order!  Think about what happens with multiple threads doing:

BEGIN;
INSERT ... -- the next AUTO_INCREMENT value is grabbed now
...        -- take a variable amount of time
COMMIT;    -- only now is the AI visible to the rest of the world.

So, give up on strict ordering.


MySQL query - Passing variable into subquery's derived table Is there a way to pass a variable into a subquery, and use that variable in a derived table in the subquery?
I am trying identify "active" organizations, and there are multiple actions that constitute "activity". One is creating an invoice, represented here (others not shown here for the sake of readability). However, because there are more types of actions, I cannot do the simpler approach of just querying the Invoice table, like in thisWorks column.
Therefore, I am trying to create a derived table, where multiple sources can be queried, but also filtering the @start and @end timestamps, as seen in the thisDoesntWork column.
Here is a simplified version of what the query looks like:
select
    @row:=@row+1 as row,
    @sixPrior:=date_sub(date_sub(date_sub(curdate(), interval dayofweek(curdate())-2 day), interval @row-1 week), interval 6 month) as sixMonthsPrior,
    @start:=date_sub(date_sub(curdate(), interval dayofweek(curdate())-2 day), interval @row-1 week) as start,
    @end:=date_sub(date_add(curdate(), interval dayofweek(curdate())+5 day), interval @row-1 week) as end,
    @sixPrior as sixCheck,
    @start as startCheck,
    @end as endCheck,
    ( select count(distinct(organizationId))
      from Invoice
      where createdTime between @start and @end
    ) as thisWorks,
    (
        select count(distinct(organizationId))
        from (
            select organizationId
            from Invoice
            where createdTime between @start and @end
        ) d
    ) as thisDoesntWork
from Organization o
join (select @row:=0) r
limit 10;

In the example above, thisDoesntWork, has the same value for every row (this first value). I assume it's got something to do with @start and @end never getting updated values.
Is there any way to filter a derived table, with values that change each row, from an outer table?
I'm also open to alternative approaches, if derived table restrictions will never allow it to work.
I'm also using MySql 5.7, so LATERAL JOIN is not a possibility

Use GROUP BY
And transform the "week" into a number that can be used in the GROUP BY:  FLOOR((TO_DAYS(createdTime) - 1)/7).  As a simple test:
SELECT FLOOR((TO_DAYS(CURDATE()) - 1)/7);
+-----------------------------------+
| FLOOR((TO_DAYS(CURDATE()) - 1)/7) |
+-----------------------------------+
|                            105446 |
+-----------------------------------+

That FLOOR expression gives you a "week number" that for use with GROUP BY.  (Tweek the "-1" if I don't have the week starting on the right day of week.)  You can derive "start" and "end" from that number via some simple expressions like
SELECT FROM_DAYS(105446 * 7) AS 'start',
       FROM_DAYS(105446 * 7 + 6) AS 'end';
+------------+------------+
| start      | end        |
+------------+------------+
| 2020-11-28 | 2020-12-04 |
+------------+------------+


MySQL Replication Duplicate Entry for Primary Key 160523 13:07:29 [ERROR] Slave SQL: Error 'Duplicate entry '914166' for key
'PRIMARY'' on query. Default database: 'zo_dev_20121216'. Query: 'UPDATE
   activity
SET
   activity_type_id = 9,
   subject = 'Send departure email',
   date_due = '2016-05-26 01:00',
   date_start = '2016-05-23 01:00',
   activity_status_id = 1,
   content = 'Send departure email'
WHERE
   id = 2888555', Error_code: 1062
160523 13:07:29 [Warning] Slave: Duplicate entry '914166' for key 'PRIMARY'
Error_code: 1062
160523 13:07:29 [ERROR] Error running query, slave SQL thread aborted. Fix
the problem, and restart the slave SQL thread with "SLAVE START". We stopped
at log 'mysql-bin.000004' position 14847360

Many people have suggested to use slave-skip-error option etc.. but that will completely avoid the query. How do you actually continue with the query that poses the error ? Also, I could not find a good answer as to why this actully occurs and what the number "914166" indicates ? Can someone please explain ?

What the number 914166 indicates?

The number indicates the row of the table where you are trying to insert data. For example, you are trying to insert some values to 914166th row.

Why this error occurs?

This error occurs because of duplicate value. You are trying to insert/update 914166 to a table activity as a primary key, but the value 914166 already exists in that table activity.

How do you actually continue with the query that poses the error?

You cannot continue with that query, since primary key column is a unique column, it does not accept duplicate values. You can skip this query and continue with the next query by using the following statements:

stop slave;

set global sql_slave_skip_counter = 1;

start slave;

Please note that, skipping errors is not a good option, you should try to fix the issues and start slave as suggested by MySQL.

Work around:

Is that mentioned query is complete statement?

It seems the given query is not a insert query but it is a update statement and it is not updating primary key of the activity table.

Check in master server, whether the mentioned primary key (id) column belongs to activity table or not and check the value (id=914166) exists in that column.

select * from zo_dev_20121216.activity where id = 914166;

If the value exist in that activity table, check in slave server, whether the particular value (id=914166) is exist or not, if it exist in slave server, you can simply skip the query.

select * from zo_dev_20121216.activity where id = 914166;

If you are getting empty result set for the above 2 queries, you can be sure that, the query is incomplete, or you are debugging wrong query.

The point is that, the query is trying to insert/update a value into a activity table which is already exists in that table.

I hope it will help you as a starting point for debugging the error.


Can I mysqldump an entire database while using where='condition' on individual tables? I have used mysqldump to dump an entire database,

However, when I want to use --where=condition", on individual tables, I have dumped individual tables

mysqldump  --where='condition' mydb table > table.sql

and then reconstructed the database table by table

 for i in *sql; do mysql newdb < i; done

Is it possible to use the where clause with mysqldump on specific tables when dumping an entire database?

You will have to custom script each table

The --where option is designed to help mysqldump a single table

Here is a post I made back in August 15, 2011

Is it possible to mysqldump a subset of a database required to reproduce a query?

Here is an crazy example

Suppose the table is this in the myworld database:

CREATE TABLE data_for_world
(
    id int not auto_increment,
    continent varchar(16),
    primary key (id)
);
insert into data_world (continent)
values ('Asia'),('Africa'),('Eupore'),
('NorthAmerica'),('SouthAmerica'),
('Australia'),('Antarctica');

and you want to break it up into two dumps

one with continents that start with A
one with continents that do not start with A

You will need three files (one for the database schema, and two for the data)

mysqldump --no-data --database myworld > myworld_schema.sql
mysqldump --no-create-info myworld data_for_world --where="substr(continent,1,1)='A'" > data1.sql
mysqldump --no-create-info myworld data_for_world --where="substr(continent,1,1)<>'A'" > data2.sql


Modify legacy table schemas that have no primary key I've been tasked with updating our database (SQL Server 2008) so that every table in it has a proper primary key defined. Due to various reasons over the years we have somehow built up some legacy tables which have no primary key at all and my colleague wants everything to have a key as then he can use a database comparison tool when deploying changes plus he wants indexes available for everything.

I would agree that having a primary key defined for every table is a must but at this point I'm wondering how much benefit it would provide. Some tables with missing keys are very large, some containing millions of records, and some are simply there to store data; insert often but get the data back not so much. Some also appear to have duplicate data in them, meaning a laborious task of cleaning up the data first, and possibly have no unique key available unless every field in the table was used.

So what I would like to know is:

Is it worth having a key on a table even if it means making a composite key of every table field? Or perhaps an auto-incrementing number would be more beneficial instead?
Is there going to be a performance cost to adding keys to tables which already contain a vast amount of data? For example if a clustered index was going to be added that would mean more space taken up to store the index too; on a table which is basically used for reference data is that worth it?

I would add a BIGINT Identity column as the non-clustered primary key for any table that does not have a primary key.  This will cause minimal disruption and use minimal storage space.

It will often improve performance especially for updates and deletes.


Is there such thing as automatic ENUMs? There are some tables that I manage which contain a string value (for example, a 4 word "Type" field).

There are only a few DISTINCT strings, however
the application may insert new strings that have never been seen before, but
in the life of the application, there will never be more than 200 strings, which means the system could store this field in a single byte.

Disk space is not an issue, but the less bytes, the faster the lookups.

I have this idea that I doubt any database engines are supporting, but I wanted to check with the experts.

The strings could be mapped to IDs in a separate table, and that way you would not need to repeat these strings on every row of the primary table. Ideally the database would manage this internally for you. The unique strings would be stored as part of the table definition, invisibly, internally, and the system would work great, no need to modify the app, for all string fields that are below 255 distinct possibilities.

Is there anything like this on any database engine? I mostly use MySQL, but I am curious if this has ever been implemented, short of modifying the applications call a custom wrapper function. (like INET_ATON, except that it would transparently do an INSERT to an AUTO_INCREMENTing table to get the ID for the first time)

Maybe I'm missing something but why wouldn't you just use a lookup table with a foreign key relationship for this sort of thing potentially with some views to provide a layer of abstraction?  That's a very common approach, it's supported by all databases, it's pretty easy to implement, and it solves other issues such as data consistency (i.e. what happens if you want to update an existing string without updating every row in the table that has that string).  If you know the maximum number of entries in the lookup table, you can pick the data type that minimizes data volumes for the primary key.  That's also much easier to handle in the future when someone decides that the number of distinct values needs to exceed whatever limit you specified initially-- you just need to increase the size of the column in both tables.


Can any database table name support double quotes "? I need to know whether any database supports double quotes as a part of their table name. I am especially interested in JDBC compliant databases. And if they do, can I please know how?

According to the SQL standard, this should be possible by doubling the double quotes (similar to escaping single quotes in string constants).

At least Postgres does support this (and Oracle does not, resulting in "ORA-03001: unimplemented feature")

create table "really""stupid_name"(id integer);

Online example: https://rextester.com/JYCW13692

However, using quoted identifiers is a bad idea to begin with. Let alone identifiers that in turn contain double quotes.

I would strongly suggest you do not do this.


Reset of root password not working? Our previous DBA left and didn't note the root password down, and I've been pressganged into doing part of his job until we can hire someone.

So I create a file :

/root/mysql.password

Inside that file, I have:

UPDATE mysql.user SET Password=PASSWORD('newpassword') WHERE User='root';

So I do a clean shutdown of the database, and then:

mysqld_safe --init-file=/root/mysql.password

I get 'starting mysqld' ... and then it exits with code 1.

I don't get it.  What's wrong with that file?

Using ..

mysqld_safe --skip-grant-tables &

.. isn't an option, unfortunately, as it's a public-facing production box.

Thanks.

The very first thing you should do is take a backup of your database using mysqldump or similar, because if you mangle something, your root password will be useless anyway.


How to use mysql_plugin utility? I tried to follow this official documentation to install unix_socket plugin using mysql_plugin utility but getting an error that

  Cannot read plugin config file unix_socket. Bad format in plugin
  configuration file.

FROM alpine:edge

RUN set -ex \
&& apk add mariadb mariadb-client \
&& mysql_install_db --user=mysql --skip-name-resolve \
&& echo 'auth_socket' >> /usr/lib/mariadb/plugin/unix_socket.ini  \
&& mysql_plugin --basedir=/usr --datadir=/var/lib/mysql --plugin_dir=/usr/lib/mariadb/plugin unix_socket ENABLE

CMD ["mysqld"]

I cross checked the ini file.

cat /usr/lib/mariadb/plugin/unix_socket.ini
auth_socket

The .ini needs to contain the plugin followed by the components.

The document Authentication Plugin - Unix Socket hints that the component name is unix_socket.

So the correct contents of the file should be:

echo -e 'auth_socket\nunix_socket' >> /usr/lib/mariadb/plugin/unix_socket.ini

As a workaround use plugin-load=auth_socket in /etc/my.cnf.d/mariadb-server.cnf seems also sufficient.


How to Skip rows in a SQL Server Query? One I selected the query from my product table with an additional Row_Number() column (Num), I'm getting the answer like below (I limited only 20 rows to retrieve).

Query

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY ProductCode) AS Num,
             ProductName
      FROM   BASE_Product
      WHERE IsActive = 1) BASE_Product
WHERE Num > 0
AND   Num < (21)

Result

My requirement is how to skip each and every 3 rows and retrieve only 7 limit rows.

Expected result

I need the result such as below:

You can use the Modulo operator for this purpose.

  Modulo
  Returns the remainder of one number divided by another.

In this case:

WHERE (Num - 1) % 4 = 0

I've set up a sample, generating a series of 1K integers.

--------------------------------------------------------------------------------
-- ② Integers table
--------------------------------------------------------------------------------
with
  p0(i) as (select 1 union all select 1 union all select 1 union all select 1)
, p1(i) as (select 1 from p0 as a, p0 as b, p0 as c, p0 as d, p0 as e)--1K rows
select row_number() over(order by i) as val
into integers
from p1;
GO

1024 rows affected

select top 25 *
from integers
where (val - 1) % 4 = 0
GO

| val |
| :-- |
| 1   |
| 5   |
| 9   |
| 13  |
| 17  |
| 21  |
| 25  |
| 29  |
| 33  |
| 37  |
| 41  |
| 45  |
| 49  |
| 53  |
| 57  |
| 61  |
| 65  |
| 69  |
| 73  |
| 77  |
| 81  |
| 85  |
| 89  |
| 93  |
| 97  |

dbfiddle here


MongoDB - Change primary shard only for a certain database I have a sharded MongoDB cluster running 3 different Databases.
Currently all 3 databases has the same shard configured as their primary shard (shard000)
Is it possible to change the primary shard for one of these databases to something other than shard000?

Just to make things clear, I have no intention of retiring shard000 any time soon, I just want to remove some of the non shared collections load off it.

Thanks,
Meny

Yes you can with a single command:

db.adminCommand({ movePrimary : "YourDBName", to : "shard001" })

then is important to flush configuration on all mongos (suppose all than the one that did the move, but better be safe):

db.adminCommand({flushRouterConfig: 1})

If your databases are small you can do it on-the-fly, else you better plan for a downtime only for this database during the move process.

Useful link:
http://docs.mongodb.org/manual/reference/command/movePrimary/


Postgres: query multiple records using a list of double keys I have 3 tables:

╔═════════════════════╗
║      companies      ║
╠═════════════════════╣
║ id                  ║
║ some_company_attrib ║
╚═════════════════════╝

╔════════════════════╗
║       people       ║
╠════════════════════╣
║ id                 ║
║ some_person_attrib ║
╚════════════════════╝

╔════════════════════════════════════════════════════════════╗
║                            jobs                            ║
╠════════════════════════════════════════════════════════════╣
║ id                                                         ║
║ company_id – foreign key pointing to the companies table   ║
║ person_id  – foreign key pointing to the people table      ║
║ some_job_attrib                                            ║
╚════════════════════════════════════════════════════════════╝

From an external source, I have a big list of pairs of attributes, which reference companies.some_company_attrib and people.some_person_attrib, like so:

('some_company_attrib123', 'some_person_attrib456'),
('some_company_attrib789', 'some_person_attribxyz'),
etc...

where each pair should match a record in the jobs table, so that the pair ('some_company_attrib123', 'some_person_attrib456') gives me back the  record in the jobs which is associated:

to the record in the companies table where some_company_attrib = 'some_company_attrib123'
and to record in the people table where some_person_attrib = 'some_person_attrib456'

After looking around and not finding any solution, I am using arrays in the following query:

SELECT * FROM jobs
  INNER JOIN people ON people.id = jobs.person_id
  INNER JOIN companies ON companies.id = jobs.company_id
WHERE (
    ARRAY[companies.some_company_attrib, people.some_person_attrib]::text[] in (
    '{"some_company_attrib123", "some_person_attrib456"}',
    '{"some_company_attrib789", "some_person_attribxyz"}',
    '{"and_many_many", "more_like_these"}'
);

Although it works, it feels rather clunky and it's actually pretty slow.
I was wondering whether there was an idiomatic and more elegant way to achieve this.

Well, it all depends on your scenario. If it is a one-off import, you create a junction table and use it for your joins.

CREATE TABLE people_at_companies (
  company_attrib TEXT,  -- whatever type it is
  person_attrib TEXT    -- whatever type it is
)

INSERT INTO people_at_companies VALUES
('some_company_attrib123', 'some_person_attrib456'),
('some_company_attrib789', 'some_person_attribxyz'),
...  -- remember to remove the last comma :)
;

SELECT j.* FROM jobs j
  INNER JOIN people p ON p.id = j.person_id
  INNER JOIN companies c ON c.id = j.company_id
  INNER JOIN people_at_companies pac ON
    c.some_company_attrib = pac.company_attrib
    AND
    p.some_person_attrib = pac.person_attrib
;

Having such junction table might improve performance, and it allows creating indexes.

If the junction data is dynamic in such a way that keeping a junction table in sync is too difficult (a very unusual situation, I’d say), we can avoid the junction table but, at the very least, using VALUES instead of arrays is way simpler:

SELECT j.* FROM jobs j
  INNER JOIN people p ON p.id = j.person_id
  INNER JOIN companies c ON c.id = j.company_id
  INNER JOIN (VALUES
    ('some_company_attrib123', 'some_person_attrib456'),
    ('some_company_attrib789', 'some_person_attribxyz'),
    ...  -- remember to remove the last comma :)
  ) AS pac(company_attrib, person_attrib) ON
    c.some_company_attrib = pac.company_attrib
    AND
    p.some_person_attrib = pac.person_attrib
;

You might have to explicitly cast the first row of the VALUES with the types of companies.some_company_attrib and people.some_person_attrib if PostgreSQL doesn’t figure the correct ones out. E.g.

  ('some_company_attrib123'::TEXT, 'some_person_attrib456'::TEXT)


Finding the partitions of a set which respect to some rules Assume the following table. I want to partition it in a way that it never happens to have elements from Field1 and Field2 which are "connected" (i.e. a and 1, a and 2, b and 1) go to a separate partition.

This is the solution with 3 partitions.

ab12, c3, d45

+--------+--------+
| Field1 | Field2 |
+--------+--------+
| a      | 1      |
+--------+--------+
| a      | 2      |
+--------+--------+
| b      | 1      |
+--------+--------+
| b      | 2      |
+--------+--------+
| c      | 3      |
+--------+--------+
| d      | 4      |
+--------+--------+
| d      | 5      |
+--------+--------+

Using PostgreSQL

WITH t AS (
        SELECT f1, array_agg(f2) AS p
        FROM (
                VALUES
                 ( 'a',1 ),
                 ( 'a',2 ),
                 ( 'b',1 ),
                 ( 'b',2 ),
                 ( 'c',3 ),
                 ( 'd',4 ),
                 ( 'd',5 )
        ) AS t(f1,f2)
        GROUP BY f1
)
SELECT array_to_string(p1, '') || array_to_string(p2, '') AS partitions
FROM (
        SELECT array_agg(DISTINCT t2.f1) AS p1, p AS p2
        FROM t AS t1
        INNER JOIN t AS t2 USING (p)
        GROUP BY p
) AS t;

Result:

 partitions
------------
 ab12
 c3
 d45
(3 rows)


How to decipher entity relationships in a Physical Data Model given the corresponding Logical Data Model Can someone use the entity relationships in this Logical Data Model (LDM):

to explain the entity relationships in this Physical Data Model (PDM):

and how to specify the foreign keys in the PDM?

I'm not sure that I understood your question...

That said, lemme try to help you.

I undestand the model represents the categorization of 'parties' as begin part of 'segments'. These categorizations are mutable and have to be traceable in time.

Let me humbly present what I think is a more complete LDM, where

PARTY is explicited
Segment Group Cd is exported as part of the identifying relationship (a solid line)
the verbs sentences are more verbose

From this LDM, the straightest derived 3NF (3rd Normal Form) PDM should be (PARTY entity not shown):

The PDM you posted:

is denormalized - MRKT_SGMNT_GROUP comprises both MRKT_SGMNT and MRKT_SGMNT_GROUP entities
is incomplete - it's missing Party_Sgmnt_End_Dttm
states redundant relationships - the two relationships are the same
or, more formalistically, states an incorrect relationship - the identifying relationship (the one represented as a solid line) yelds both key properties from MRKT_SEGMNT_GROUP as key properties to PARTY_SGMNT_HIST, which aren't - just Sgmnt_Id has been derived as part of the PK

With the denormalization presented, the PDM ought to be, with the missing property marked in bold red:

Did this serve as any help? Hope so...

Regards, Marcus Vinicius.


DB Restart Taking too long, site is down I tried to restart MySQL through WHM and now its taking way too long to restart, the progress bar is still showing.

What can I do?

I can still access the server via SSH but I cannot connect to MySQL. This is urgent as my online store is now down.

Will restarting the HTTP Server (Apache) help?

Without seeing any additional information, here is my best guess why MySQL is trying to shutdown for a long time: I suspect mysqld no longer has a socket file.

About 1.5 years ago, I answered mysql restart issue after move database. I learned that mysqld depends on the presence of a socket file. If there is no socket, mysqld just draws lots of dots on the screen and then gives up.

In that old post, I wrote three instructions

kill mysqld_safe
kill mysqld
start mysqld

Please go do this now ...

I have discussed MySQL behavior with the socket file (or lack of it) 4 years ago (See my older post mysql restart won't kill child processes on CentOS)


Can this UPDATE be faster with indexes? update line_items set product_id = dups.latest_product_id
    from  line_items as li
    join products p on li.product_id = p.id
join vendors v on p.vendor_id = v.id
    join vendorgroups vg on v.vendorgroup_id = vg.id
    join duplicate_product_sets dups
      on dups.host_id = vg.host_id and dups.vendor_id = v.id
     and dups.invtid = p.invtid and dups.latest_product_id <> p.id

I have dropped indexes on line_items (except the primary key) because I figured since it is the table being updated, indexes would slow down the update.

Other than that, I have indexes on these columns (in addition to primary keys):

products.invtid
products.vendor_id
vendors.vendorroup_id
vendorgroups.host_id
duplicate_product_sets.host_id
duplicate_product_sets.vendor_id
duplicate_product_sets.invtid
duplicate_product_sets.latest_product_id

The result of EXPLAIN:

-------------------------------------------------------------------------------------------------------------------------------
 Update on line_items  (cost=5444.25..5004105.00 rows=397508809 width=753)
   ->  Nested Loop  (cost=5444.25..5004105.00 rows=397508809 width=753)
         ->  Seq Scan on line_items  (cost=0.00..12944.60 rows=434160 width=719)
         ->  Materialize  (cost=5444.25..20030.69 rows=916 width=34)
               ->  Hash Join  (cost=5444.25..20026.11 rows=916 width=34)
                     Hash Cond: (li.product_id = p.id)
                     ->  Seq Scan on line_items li  (cost=0.00..12944.60 rows=434160 width=10)
                     ->  Hash  (cost=5433.62..5433.62 rows=851 width=32)
                           ->  Nested Loop  (cost=147.90..5433.62 rows=851 width=32)
                                 Join Filter: (v.id = p.vendor_id)
                                 ->  Hash Join  (cost=147.47..1253.41 rows=700 width=40)
                                       Hash Cond: ((dups.vendor_id = v.id) AND (dups.host_id = vg.host_id))
                                       ->  Seq Scan on duplicate_product_sets dups  (cost=0.00..706.97 rows=39197 width=28)
                                       ->  Hash  (cost=117.37..117.37 rows=2007 width=20)
                                             ->  Hash Join  (cost=36.70..117.37 rows=2007 width=20)
                                                   Hash Cond: (v.vendorgroup_id = vg.id)
                                                   ->  Seq Scan on vendors v  (cost=0.00..53.07 rows=2007 width=14)
                                                   ->  Hash  (cost=24.09..24.09 rows=1009 width=14)
                                                         ->  Seq Scan on vendorgroups vg  (cost=0.00..24.09 rows=1009 width=14)
                                 ->  Index Scan using products_by_invtid on products p  (cost=0.42..5.96 rows=1 width=24)
                                       Index Cond: ((invtid)::text = (dups.invtid)::text)
                                       Filter: ((dups.latest_product_id <> id) AND (dups.vendor_id = vendor_id))
(22 rows)

I am using Postgres 9.4.

Query

You UPDATE statement looks good except for one major problem. Fixed and re-formatted with some other minor improvements:

UPDATE line_items li
SET    product_id = d.latest_product_id
FROM   products               p
JOIN   vendors                v  ON v.id  = p.vendor_id
JOIN   vendorgroups           vg ON vg.id = v.vendorgroup_id
JOIN   duplicate_product_sets d  ON d.invtid    = p.invtid
                                AND d.vendor_id = p.vendor_id
                                AND d.host_id   = vg.host_id
                                AND d.latest_product_id <> p.id
WHERE  p.id = li.product_id;

You don't need to join line_items a second time. And you also forgot to join the two instances, producing a cross join (as pointed out by @a_horse in the comments). I removed the redundant table and now the join to products does the job.

Indices

  I have dropped indexes on line_items (except the primary key index) ...

Basically correct, but If you only update a small fraction of rows, you should still have an index on product_id, even if that incurs a small cost for index update. The gain should be much more substantial.

For best performance, these are the perfect indices:

li(product_id)
p(vendor_id, invtid, id)
v(id, vendorgroup_id)
vg(id, host_id)
d(vendor_id, invtid, host_id, latest_product_id)

I mean multicolumn indices where I listed multiple columns. Indices on individual columns also go a long way, but not all the way. Multicolumn indices are most efficient when index-only scans are possible. In the best case scenario, this update would only need to scan the suggested indices without touching any table except line_items.

Whether it pays to optimize this operation depends on the complete picture. Additional indices incur a small cost for write performance.

And be sure, that you use the same (adequate) data type for invtid in both columns. I see a cast to text in the EXPLAIN output that shouldn't be there.

Related:

How do I know what indexes to create for a table
Very slow simple PostgreSQL query on RDS
Working of indexes in PostgreSQL


SSMS Database Node Refresh causes Restore to Fail I have confirmed via testing that clicking on the refresh icon (one or more times) for the database node of a SQL Server instance being observed in SQL Server Management Studio (SSMS 2012) causes my 3rd-party database restore software to fail. If the refresh icon is not clicked, then the restore succeeds.

My question: what occurs in the SQL Server engine (or the msdb database) when the refresh icon is clicked?

Update (2:36pm)
When the failure occurs, one database is left in a restoring state. The specific error is "The database cannot be recovered because the log was not restored."

Update (2:48pm)
The 3rd-party database restore software is GUI-driven. A restore script is not available to post. I will capture the commands via Profiler.

Update (3:30pm) - restore script captured from Profiler (with no SSMS refresh)

select name, physical_name from model.sys.database_files
select name from master..sysdatabases where name = 'DC_EARTH001'
select DatabaseProperty( 'DC_EARTH001', 'IsInLoad' )
select database_id from master.sys.databases where name ='DC_EARTH001'
select DatabaseProperty( 'DC_EARTH001', 'IsSuspect' )
select DatabaseProperty( 'DC_EARTH001', 'IsShutdown' )
select name, physical_name from model.sys.database_files
RESTORE DATABASE [DC_EARTH001] FROM VIRTUAL_DEVICE='MVD_SQLAgent-DC_EARTH001__a6d9fb41_c3fc_4b00_a78e_596dd32fc149_' WITH SNAPSHOT, NORECOVERY, MOVE 'DC_EARTH001' TO 'X:\DC_EARTH001.mdf', MOVE 'DC_EARTH001_log' TO 'Y:\DC_EARTH001_log.ldf'

select name, physical_name from model.sys.database_files
select name from master..sysdatabases where name = 'DC_EARTH002'
select DatabaseProperty( 'DC_EARTH002', 'IsInLoad' )
select database_id from master.sys.databases where name ='DC_EARTH002'
select DatabaseProperty( 'DC_EARTH002', 'IsSuspect' )
select DatabaseProperty( 'DC_EARTH002', 'IsShutdown' )
RESTORE DATABASE [DC_EARTH002] FROM VIRTUAL_DEVICE='MVD_SQLAgent-DC_EARTH002__584bf879_215d_4c15_b7ad_230ecab60488_' WITH SNAPSHOT, NORECOVERY, MOVE 'DC_EARTH002' TO 'X:\DC_EARTH002.mdf', MOVE 'DC_EARTH002_log' TO 'Y:\DC_EARTH002_log.LDF'

select name from master..sysdatabases where name = 'DC_EARTH003'
select name, physical_name from model.sys.database_files
select DatabaseProperty( 'DC_EARTH003', 'IsInLoad' )
select database_id from master.sys.databases where name ='DC_EARTH003'
select DatabaseProperty( 'DC_EARTH003', 'IsSuspect' )
select DatabaseProperty( 'DC_EARTH003', 'IsShutdown' )
RESTORE DATABASE [DC_EARTH003] FROM virtual_device = 'DC_EARTH003_00__416c5ed9_54e9_4ad1_9adc_3c7401c3fd57_' WITH NORECOVERY , MOVE 'DC_EARTH003_data' TO 'X:\DC_EARTH003.mdf', MOVE 'DC_EARTH003_log' TO 'Y:\DC_EARTH003.ldf'

exec xp_msver
select convert( char(100), ServerProperty('Edition') )
select is_srvrolemember( 'sysadmin', 'NT AUTHORITY\SYSTEM' )
exec sp_helpsort
select name, source_database_id from master.sys.databases order by name

select DatabaseProperty( 'DC_EARTH003', 'IsTruncLog' )
select status from sysdatabases where name='DC_EARTH003'
RESTORE DATABASE [DC_EARTH003] WITH RECOVERY

select DatabaseProperty( 'DC_EARTH001', 'IsTruncLog' )
select status from sysdatabases where name='DC_EARTH001'
RESTORE DATABASE [DC_EARTH001] WITH RECOVERY

select DatabaseProperty( 'DC_EARTH002', 'IsTruncLog' )
select status from sysdatabases where name='DC_EARTH002'
RESTORE DATABASE [DC_EARTH002] WITH RECOVERY

Update (2015/04/07 @ 9:25am)
Solution proposed by Microsoft: SQL Server 2012 SP1 CU1

Slow performance or deadlock when you restore a database and execute statements at the same time in SQL Server 2012 (Scenario 2 http://support.microsoft.com/en-us/kb/2725950

Update (2015/04/07 @ 9:25am)
Solution implemented: SQL Server 2012 SP1 CU15 http://support.microsoft.com/en-us/kb/3038001

The issue is resolved

SQL Server 2012 SP1 CU15 (http://support.microsoft.com/en-us/kb/3038001) resolves the issue (no deadlock occurs as the result of issuing a command during a database restore).


select all rows with a question mark I've been asked to check an entire database for all records with a question mark (?). I assume I can only do this on a table basis, is there any short cut or do I have to run

SELECT * FROM [table] WHERE [field] LIKE '%?%'

for each column?

Your objective is slightly ambiguous:

  all records with a question mark (?)

Columns that are not character data types may or may not contain a question mark, depending on their text representation.

Also, this could mean to filter for any columns equaling '?' or just containing '?'.

A dead simple solution for the "containing" interpretation (and ignoring my first concern - which seems safe enough for '?') is to cast the whole row to text - generating the default text representation of everything, plus delimiters and decorators - none of which include the question mark (?) per default:

SELECT * FROM tbl t WHERE t::text LIKE '%?%';

You would run this for every (relevant!) table in the DB. (You'll want to exclude system tables at least.) I added an example implementation to the related question on SO:

How to search a specific value in all tables (PostgreSQL)?

Related answer on SE Code Review:

SQL: Search for a keyword in several columns of a table


can i shrink log file in full recovery model i have configured Always on Availability group where iam unable to shrink log file size through simple recovery model. can i shrink the log file size in full recovery model if yes or possible then is there any impact for it..

Shrinking a log file isn't generally a good idea unless you've had a large amount of unexpected log file growth - otherwise it'll only grow again, and a log file that grows can cause performance problems.

You can shrink a log file that's in any of the recovery models, but you won't always see the effect of it, as less of the file will be able to be shrunk - log records have to hang around until they have been backed up, transferred to other machines, and so on.

If you can put up with your log file being large, then that could be okay. Or try to shrink it to an okay size until it shrinks - maybe try every half hour until then? Once it can shrink, it will, and you can stop trying.


Cannot get my slave - replication server to start My replication server will not start. I have followed the instructions here:
http://opensourcedbms.com/dbms/setup-replication-with-postgres-9-2-on-centos-6redhat-el6fedora/

As well as several other places including the Postgres Wiki as they all have the same information.

Here is what happens:
I do a full backup of the /9.2/data folder and move it to the replication/slave server, untar it. I can start PostgreSQL as well as pgAdmin and access all data with no problems.

I then move to the instructions on editing the pg_hba.conf and postgresql.conf for the slave server. I attempt to start it, and it fails (error in red [fail]. I cannot find any logs anywhere to give me a hint as to why.

I even verified there was no postmaster.pid in the data folder.

Also, I cannot find any log files. Do I need to "activate" a log file in the configuration?

So, if anyone wants to take a stab in the dark on my vague description, I'd love to hear any suggestions. I can put my conf files on pastebin if that will help.

Steps to set up the hot standby

First, determine the mount points of the Postgresql data directories and the WAL (Write Ahead Log) directories.
For performance, the $PGDATA and pg_xlog directories should be on separate volumes.

In the examples below, they are defined as follows:

$PGDATA is the "master" cluster, located at /pgdata/9.3/data, at (for example) 192.168.10.0
The "slave" (aka "hot standby") will be (for example) located at /pgdata/9.3/data, at (for example) 192.168.10.1
The WAL staging area on the slave will be /pgdata/WAL_Archive. This is where the WAL segments are sent to from the archive_command on the master. Ideally on a separate volume from $PGDATA.

Assumptions:

The master cluster is up and running normally.
There is no slave cluster yet.
You are doing all operations as the "postgres" OS user (not root!)
ssh between both servers is working
You are using at least PostgreSQL 9.2
pg_hba.conf and postgresql.conf are located at $PGDATA/. If not, change the instructions below to match your location.
You are setting up the slave on a separate server.
If the servers are not on the same network (eg. different colocations), use the "-z" flag in the "archive_command" rsync on the master to compress before transferring.
These commands are for Linux, but with some substitution of commands should work on Windows too.

On the slave, create the staging directory to hold the master's log files

mkdir -p /pgdata/WAL_Archive
chown postgres:postgres /pgdata/WAL_Archive

On the master, edit the $PGDATA/postgresql.conf

wal_level = hot_standby
archive_mode = on
                    ## /pgdata/WAL_Archive is a staging directory on the slave
archive_command = 'rsync -W -az %p postgres@$SLAVE_IP_HERE:/pgdata/WAL_Archive/'
max_wal_senders = 5
wal_keep_segments = 5000   # If you have the room, to help the pg_basebackup
                           # not fail due to WAL segments being removed from master.
                           # For clusters will very little traffic, 100 is probably fine

On the master, create the replication role, which will be copied to the slave via pg_basebackup. Set a password for the "replication" role

psql -U postgres -d postgres -c "CREATE USER replication WITH replication ENCRYPTED PASSWORD 'changeme' LOGIN"

Modify the master $PGDATA/pg_hba.conf and enable the replication user for the IP of the slave

# TYPE DATABASE USER ADDRESS METHOD
#hostssl    replication     replication     $SLAVE_IP_HERE/32       md5
host    replication     replication     $SLAVE_IP_HERE/32       md5

Restart the master cluster to pick up the changes to the postgresql.conf. This is done as the cluster superuser.

For example:

pg_ctl -D $PGDATA restart -m fast
## The master cluster MUST be restarted before the pg_basebackup command is executed.

On the slave, from $HOME, issue the pg_basebackup command to start setting up the hot standby from the master.

## --host=IP_OF_MASTER  -> The master's IP
## --pgdata=$PGDATA     -> The slave's $PGDATA directory
## --xlog-method=stream -> Opens a second connection to the master to stream the WAL segments rather than pulling them all at the end
## --password will prompt for the replication role's password
## Without compression, "stream" gets the changes via the same method as Streaming Replication

time pg_basebackup --pgdata=$PGDATA --host=IP_OF_MASTER --port=5432 --username=replication --password --xlog-method=stream --format=plain --progress --verbose

## Alternate version with compression, note "--xlog --gzip --format=tar"
#time pg_basebackup --pgdata=$PGDATA --host=IP_OF_MASTER --port=5432 --username=replication --password --xlog --gzip --format=tar --progress --verbose

On the slave, after pg_basebackup has successfully completed, edit $PGDATA/postgresql.conf

hot_standby = on #off               # "on" allows queries during recovery
max_standby_archive_delay = 15min   # max delay before canceling queries,
                                    # set to hours if backups will be taken from here
max_standby_streaming_delay = 15min # max delay before canceling queries
hot_standby_feedback = on #off

On the slave, create $PGDATA/recovery.conf:

standby_mode = on

## To promote the slave to a live database, issue "touch /tmp/promote_db"
## Warning: If multiple slaves share the same /tmp/ directory,
##          then the trigger file must be named uniquely, else multiple slaves
##          could attempt to be promoted in the presence of the trigger file.
trigger_file = '/tmp/promote_db_slave'

## Host can be the master's IP or hostname
primary_conninfo = 'host=IP_OF_MASTER port=5432 user=replication password=CHANGEME'

## Log the standby WAL segments applied to a standby.log file
## TODO: Add the standby.log to a log rotator
## The paths must be explicitly defined, including the path to pg_archivecleanup
restore_command = 'cp /pgdata/WAL_Archive/%f "%p" 2>>/pgdata/9.3/data/pg_log/standby.log'

## XXX: If multiple slaves share the staging WAL directory,
##      do not use pg_archivecleanup as WAL segments could be removed
##      before being applied to other slaves.
archive_cleanup_command = '/usr/pgsql-9.3/bin/pg_archivecleanup /pgdata/WAL_Archive %r'

## On hot standby clusters, set to 'latest' to switch to the newest timeline in the archive
recovery_target_timeline = 'latest'

The slave should be ready to be started now. Start the cluster on the slave with whatever method works best.

pg_ctl -D $PGDATA start

The following are the commands I run when recreating a standby over and over for test. I add them to a script called /root/recreate_standby.sh. Runs as root, but that is not necessary - if you run as postgres, removed the "sudo su - postgres -c" command.

#!/bin/bash

## This script runs on the standby.
## Executed as root, else remove the "sudo - postgres -c" commands.
## Assumes you have a valid recovery.conf saved at
## $PGDATA/../recovery.conf.bkp

export PGDATA=/path/to/data/dir     ## Must be set correctly
export PGPORT=5432
export MASTER=192.168.x.x            ## IP or host entry for the master Postgresql server
export PGBIN=/usr/pgsql-9.3/bin

service postgresql-9.3 stop -m immediate

if [ $? != 0 ]; then
    service postgresql-9.3 start
    echo "Could not shut down PostgreSQL. Aborting."
    exit 1
fi

rm -rf $PGDATA

if [ $? != 0 ]; then
    echo "Could not remove the PostgreSQL $PGDATA dir. Aborting."
    exit 1
fi

## If the replication role is not set to "trust" in the master's
## pg_hba.conf file, the password will need to be passed into the command below,
## and "--no-password" will need to be removed or revised to be "--password"
su - postgres -c "$PGBIN/pg_basebackup --pgdata=$PGDATA --host=$MASTER --port=$PGPORT --username=replication --no-password --xlog-method=stream --format=plain --progress --verbose"

su - postgres -c "cp -p $PGDATA/../recovery.conf.bkp $PGDATA/recovery.conf"

service postgresql-9.3 start

su - postgres -c "$PGBIN/pg_isready -U postgres -p $PGPORT -t2"

while [ $? != 0 ]; do
  echo "Sleep 1 second, check if slave is up yet. If not, sleep again."
  sleep 1;
  su - postgres -c "$PGBIN/pg_isready -U postgres -p $PGPORT -t2"
done

su - postgres -c "$PGBIN/psql -d postgres -U postgres -qXc 'select pg_is_in_recovery() as is_pg_in_recovery'"

exit 0

For more in-depth details, current Postgresql docs:

High Availability, Load Balancing, and Replication
Recovery Configuration
Continuous Archiving and Point-in-Time-Recovery


MySQL Replication - transactions missing in M->S1->S2 setup I have MySQL chained replication setup as Master->Slave1->Slave2.

Events are replicating perfectly fine from M->S1 and also recorded into the Relay & Binary logs on S1 but then S2 doesn't receive these events.

SHOW SLAVE STATUS on S2 is showing both IO & SQL threads running, Exec_Master_Log_Pos is constantly changing but Relay_Log_Space & Relay_Log_Pos stay static.

Master is MySQL 5.5, S1 & S2 are both MySQL 5.6.40.

Interesting that if any updates are done directly on S1 then they replicate across to S2 correctly.

I have checked disk space isn't full on any of the servers.

Have also looked and put a comment on Mysql replication is enabled but stuck at "waiting for binlog to be updated"

On S1 I have:

mysql> show global variables like '%slave%';
+------------------------------+-----------------------+
| Variable_name                | Value                 |
+------------------------------+-----------------------+
| init_slave                   |                       |
| log_slave_updates            | ON                    |
| log_slow_slave_statements    | OFF                   |
| rpl_stop_slave_timeout       | 31536000              |
| slave_allow_batching         | OFF                   |
| slave_checkpoint_group       | 512                   |
| slave_checkpoint_period      | 300                   |
| slave_compressed_protocol    | OFF                   |
| slave_exec_mode              | STRICT                |
| slave_load_tmpdir            | /tmp                  |
| slave_max_allowed_packet     | 1073741824            |
| slave_net_timeout            | 3600                  |
| slave_parallel_workers       | 0                     |
| slave_pending_jobs_size_max  | 16777216              |
| slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN |
| slave_skip_errors            | OFF                   |
| slave_sql_verify_checksum    | ON                    |
| slave_transaction_retries    | 10                    |
| slave_type_conversions       |                       |
| sql_slave_skip_counter       | 0                     |
+------------------------------+-----------------------+

On M:
 - SELECT @@global.server_id=6
 - SELECT @@global.server_uuid=05ac0062-7af8-11e5-9af2-9c8e991000d2

On S1:
 - SELECT @@global.server_id=4
 - SELECT @@global.server_uuid=062ad58e-59ed-11e8-a7ea-000d3a32adb2

On S2:
 - SELECT @@global.server_id=6
 - SELECT @@global.server_uuid=184b1c69-7868-11e8-aeab-000d3a321333

The problem stems from log_slave_updates. You need that in S1 so the binary logs of S1 can be the source for S2 reading events.
Please add this line to my.cnf in S1
[mysqld]
log-slave-updates

and restart mysqld on S1. Then, setup replication from S1 to S2. S2 is currently out of sync with S1, so S2 may need to be reloaded or sync'd up using pt-table-checksum and pt-table-sync.
UPDATE 2018-06-25 12:23 EDT
Thank you for the update,
Here is your problem:
M1 and S2 have the same server_id.
S2 will never process an event if the incoming event's server_id is the same as S2.
Change the server_id on S2 to something other than 4 or 6 used by M and S1.


Row-level subquery caching? I have a query that uses a CTE which contains millions of rows. I plan to call this query many times, and the CTE returned each time will have most of the same rows. Is it possible for me to cache the CTE somehow so that only new rows need to be calculated?

The (slightly simplified) query is:

WITH vals AS (
    SELECT '2013-08-01 0:00'::timestamp  AS frame_start,
           '2013-09-01 0:00'::timestamp  AS frame_end,
           '1 day'::interval             AS interval_length
),   intervals AS (
    SELECT tsrange(start_time,
                   lead(start_time, 1, frame_end) OVER (ORDER BY start_time     NULLS FIRST)) AS time_range
    FROM (
        SELECT generate_series(frame_start, frame_end, interval_length) AS start_time,
               frame_end
        FROM vals
    ) _
    WHERE start_time < frame_end
), market_trades_ts AS (
    SELECT time_range, td.id
    FROM intervals i
    LEFT JOIN market_trades td
    ON td.timestamp >= COALESCE(lower(i.time_range), '-infinity') AND td.timestamp < COALESCE(upper(i.time_range), 'infinity')
)
SELECT time_range, count(*) AS agg
FROM market_trades_ts td
GROUP BY time_range
ORDER BY time_range;

It would be great if market_trades_ts could be cached so that, for any intervals which it's seen before before, it can pull the previous result set, and then take its union with the new rows (for intervals which it hasn't seen before).

Is this possible? It seems like it will speed up my query dramatically.

PostgreSQL's CTE's materialize results - they run the CTE term once and cache the output for the duration of the query. So it's already doing what you want.

CTE results cannot be cached between queries. If you want that, you should instead CREATE TEMPORARY TABLE AS SELECT ....


Does installing SQL Server Database Engine and Reporting Services in Azure require two virtual machines? We are evaluating moving a large-ish web application comprising ASP.NET, Classic ASP and SQL Server Reporting Services into Azure, and we want to know the "best practice" for installing the Database Engine and Reporting Services.

In our current environment, we have a standalone Database Engine, and a separate server that hosts Reporting Services.  One of the main reasons for this was to avoid external users making direct HTTP connections to the server housing the databases - isolation for the sake of security.

My understanding of our type of MS licensing is that we only pay for one installation of SQL Server, its components just happen to be spread over two machines - this may or may not be accurate.

If we want to have a similar configuration in Azure - will we be be required to pay 2 x the per-hour charge for SQL Server because they're considered to be two independent installations?

Alternatively, is someone willing to make a convincing case that putting the Database Engine and Reporting Services in the same virtual machine does not significantly reduce security?

I'm aware of the Windows Azure SQL Reporting, but this will not cover requirements because we are already heavily dependent on Report Models, Report Builder, Scheduled Report and custom authentication.

To purely answer your question:

If you set up two different VMs one running the SQL Server and the other one hosting the SQL Server Reporting Services, you would be charged twice the per-hour price of a Virtual Machine, but only once for your the SQL Server License.

Best Practice

I guess the "best practice" answer would be to rely on SQL Azure and SQL Azure Reporting Services, since they provide licensing, installation, maintenance, redundancy, scalability and security built-in.

But since this doesn't seem to be an option to you, here is what I would do based on my experience:

Put the database that hosts the data the reports are built on in SQL Azure. There you can restrict access to the database to only other Windows Azure components (Websites, Hosted Services, VMs) or a given IP-Range
Setup the SQL Server Reporting Services on a Virtual Machine (not a Hosted Service), because Hosted Services lack data persistence
Host your web application on Azure Websites (they are for free at the moment). You can later switch to Hosted Services if you need more performance.

Hope this answer helps.


Definition of indexing, datatype configuration, and DBMS for SHA3-256 hashes I am designing a system within which I will need to be able to search a database using a file or string SHA3-256 hash.

For example the hash of test is

36f028580bb02cc8272a9a020f4200e346e276ae664e45ee80745574e2f5ab80

Which DATATYPE should I set the column that stores this value? I've heard about using BINARY instead of VARCHAR.
How should I index this column so that searches by hash are fastest?
Which database management system (like PostgreSQL, DB2, SQL Server), or document software (like MongoDB) should I use that most efficiently allows me to store and retrieve records by hash? I only have experience with relational database management systems like MySQL, but maybe a document store like MongoDB would be the better way to go.

SHA3-256 is 256bits. That's 32 bytes.

Don't store it as TEXT any form, or it'll be 256 bytes (8x the size).
I would store it as inline binary if your database supports it because I doubt anything it's going to have a data type for a 32 byte data type (which nothing uses except AVX2).

On indexing, any HASH index would work if your database provides it.

From PostgreSQL,

CREATE DOMAIN sha3_256 AS bytea;

CREATE TABLE datastore (
  id     int  PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  myhash sha3_256
);

CREATE INDEX ON datastore USING HASH (myhash);

INSERT INTO datastore (id,myhash) VALUES
  (1, decode('36f028580bb02cc8272a9a020f4200e346e276ae664e45ee80745574e2f5ab80', 'hex'));

As a special optimization you could store it as two UUIDs. Though I probably wouldn't bother.


How can I run a T-SQL Script using SQL Credentials? I need to develop a T-SQL insert script for use on a remote SQL 2005 Express instance but I would like the script to include the necessary SQL credentials so all the user has to do is execute the script.  What is the easiest way to accomplish this?

As you describe it, it's not going to work.  The T-SQL script needs to run on a SQL Server which means that it needs to be run in an appropriately-authenticated session.

One option would be to write the TSQL script, and then provide it with a VBScript or CMD file or PowerShell script that'll launch the TSQL at the correct server with the right credentials.


Storing arrays of integers in database (for efficient select) I am creating a database that will store 100.000 (and probably more in the future) users. While this obviously happens in a table with 1 row per user, every user can (and will) store hundreds of items. In programming language this would mean the user has 2 arrays (or one 2-dimensional array) of integers: a column for the itemid's and a column for the amounts.

My instincts tell me to create a table to hold all these items, with rows like (userid, itemid, amount). However this would result in a huge table. 200.000 users with 250 items each... that's 50 million entries in one table. This, plus the fact that the table will undergo continuous and rapid change, frightens me. (How rapid? I estimate up to 100 modifications per second.)

Typically there will be anywhere between 100 and 2000 users, all adding and removing items, and modifying amounts. These actions can and will happen in programming code. It would go as follows:

User starts session, program loads all the users items from the database
User modifies the item list
Every few minutes, the changes are saved into the database
When the user ends the session, it is also saved into the database

It is worth noting that there is a maximum to the number of items a user can store.

Are there any alternatives to using a separate table? Perhaps save the values in a formatted text string? Or is this one of the instances where using a MySQL database is actually a Bad Idea™?

Thank you for your time and insights.

RDBMs were built to handle these kinds of relations and operations. Plus, storing the users and items separately will help with detailed reporting (eg: number of items added today).

With optimized indexes, reading the many items per user is just one lookup away. The items table can be indexed to store the related items (per user) consecutively, thereby giving a array like reading scenario. Just make sure to use page padding to allow for the future updates/inserts/deletes.

RDBMs are pretty fast and can handle hundred-thousand operations per second, so this may not be an issue. When your items are on a different table, updates done per item will perform better than if the items were stored in a text column (as an array) in the same rows as the user. This will give you faster updates, lesser memory required for the operation, quicker locks and releases (and you may not have to lock your respective user), smaller read/writes, etc...


Optimized my.cnf for high-end and busy server Currently my main culprit of poor performance is MySQL and I'm not DB savvy. So far, I've had little luck in optimizing MySQL configs on my server, which has a Sandy Bridge processor with 16GB RAM and SSD disks serving a Drupal 6 site running on top of Ubuntu. I have ~1300qps with 80/20 read/write ratio. MyISAM is the only engine that I use (My experience with InnoDB have been horrible, despite all buzz about it as being superior to MyISAM).
The original my.cnf shipped with MySQL 5.1 is abysmal and Debian's suggested configs for 'huge' servers seemed to be less than optimal for my case. After a lot of experimentations and tweaks I figured out that tuning-primer script is too generic to give sensible advice (if they are not outright misleading!). So regarding the plethora of parameters involved in an optimal MySQL config, it would be great if some DB-informed drupalers could share their my.cnf for similar Server/case.

If you're running at that load on that hardware, you really should get some experts in to do this for you. Pythian and Percona are 2 consultancy/support organisations that I can think of: I have no link to either of them BTW.

Each install and load has different tweaks needed. The "huge" cases for my.cnf files I've seen are what you'd use a few years ago or on your mobile phone nowadays...

The default settings are amazingly low so if you've never changed them then there is your problem.

Edit: Have you already hacked your my.cnf? https://drupal.stackexchange.com/questions/12085/default-to-myisam-instead-of-innodb-in-drupal-7


How to measure or estimate the streaming replication lag in Postgres 9.2 I've been asked to reverse engineer an app that uses postgresql in the back end.
I can see that there is some database replica
tion going on... based on my reading, I *think it's called streaming replication.
The settings on the master server look like this:

wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32

My question is this:  what impacts the delay between creating a new record on the master, and when it shows up / is replicated to the slave?

From reading the manual, (http://www.postgresql.org/docs/current/static/hot-standby.html) I see that the model it uses is "eventual consistency"... paragraph 4 on that page states:

  The data on the standby takes some time to arrive from the primary
  server so there will be a measurable delay between primary and
  standby. Running the same query nearly simultaneously on both primary
  and standby might therefore return differing results. We say that data
  on the standby is eventually consistent with the primary.

But is there any pattern / ways to guesstimate how long it'll take?  Or is it really just arbitrary?

If you can point me in the right direction, I'd appreciate it.
Thanks.

My question is this: what impacts the delay between creating a new record on the master, and when it shows up / is replicated to the slave?

What really matters

Replication lag depends on a few things.

  The archive command is only invoked on completed WAL segments. Hence, if your server generates only little WAL traffic (or has slack periods where it does so), there could be a long delay between the completion of a transaction and its safe recording in archive storage. To put a limit on how old unarchived data can be, you can set archive_timeout to force the server to switch to a new WAL segment file at least that often. Note that archived files that are archived early due to a forced switch are still the same length as completely full files. It is therefore unwise to set a very short archive_timeout — it will bloat your archive storage. archive_timeout settings of a minute or so are usually reasonable.

wal_level

First, in order to use streaming replication meaningfully WAL has to be set to hot_standby (now called replica in 9.6+). Logical decoding is useful to make sense of WAL logs if you need to do that. For instance, using logical decoding you can make use of these plugins:

test_decoding – the default plugin
wal2json – shows the changes in JSON format
decoder_raw – reconstructs the query that has applied the change

See this post for more information

Other things mentioned

I don't think these things matter substantially,

wal_keep_segments this stores how many past log files you're keeping.  If you exceed this limit you fall out of sync. If you transfer fast enough and don't have connect interruptions I'm not sure how much it matters. You can now use replication slots to do this in a better way.
max_wal_senders WAL shipping either works or not. You set this on servers exporting wal logs to enable replication and it goes against max_connections.


SSAS Calculated Field - rolling sum, for a year of days I have a fact table storing employee occurrences incurred day over day.
I need to have a running sum over the past year of an employee's life. I had, on first pass, done this on ETL, making this "Acitve Occurrences" Measure's AggregateFunction set to LastChild.
The problem happens as there can be more then one "entry" in a day. "Entry" is also a measure with its AggregateFunction set to LastChild. The result is that the occurrences still sum in instances with multiple "entry".

All of this feels terribly wrong. It screams to me that the "Active Occurrences" (the 365 day rolling sum) should be a calculation instead of done in the ETL. The problem is my lack of knowledge with MDX, at this point.

What would a calculation for this look like?

The reason why I would need the individual entries in the day, is that they have a reference to a type/reason dimension. Flattening the figures to the single day would lose those individual references.

Thank you in advance.

So the calculated member I came up with is:

sum(
    {ParallelPeriod([Calendar].[Fiscal Calendar].[Date],365,[Calendar].[Fiscal Calendar].CurrentMember):[Calendar].[Fiscal Calendar].CurrentMember},
    [Measures].[Occurrences Received]
)

simple enough, I guess. It could have been the ParallelPeriod for the Year level and 1 though I guess as this is a Fiscal Calendar 365 members at the day level needs to be used.


How to generate error log file while executing a .sql file in terminal I just started learning coding 2 week ago and I am in a middle of an issue that I am not really able to find online, SO i though why not ask.

I am trying to import Import a MySql DB of around 8 Gigs through Terminal (SSH) But the file execution always stop in the middle somewhere.

My Question is:
Is there a way to write the error log generated my mysql while running the query in terminal backend?
The 2 queries I am using are:

1/mysql -uroot -pxxx -f main < sq2.sql &

2/ gzip -dc < file.sql.gz | mysql -f -uroot -pxxx web &

Both queries run as a back-end daemon because of "&" and "-f" helps in forcing the mysql to run even when an error is encountered.

But still my query gets terminated in the middle, So how can I write and log errors for such commands.

So after a little more searching I found the answer :)

Here is how this needs to be done.

mysql -uroot -xxx! -f main < sq2.sql > /var/www/log.txt &


Performance issues on json field update I have a postgreSQL database on which I am experiencing huge latency issues when trying to update a json field using the following query:

UPDATE collections
SET "layout" = jsonb_set(layout, '{actions}', '[{"actionId":45,"actionName":"my_action"}]')
WHERE id = 1

It takes about 30sec to complete even though it does not seems like a big query.

Furthermore the query seems to block all other incoming queries even the simplest SELECT (on the same collection).

Good to know too, it happens only on our production database. On local databases it's pretty fast (so I'm kind of assuming it has to do with the traffic and locks on that table).

It's also instantaneous on a replica of our production database. Which makes me think it might be related to some things happening under the hood that lock this query.

If anybody has any hints on what could cause those troubles, it would be much appreciated.

Regards,

PostgreSQL: v9.5.20, 8GB RAM, 74/256GB disk used

If that happens only on one database, and the databases are comparable in size, then the problem is probably locking.

There must be other (long running) transactions that modify rows in the table. If your statement tries to modify the same row, it has to wait until these concurrent transactions are finished.

Examine the pg_locks view for entries that are not granted: these correspond to blocked database sessions.


Max allowed packet changed still replication not starting Suddenly I notice my replication stopped and first I got some primary key duplication error when I checked using the show slave status\G. Then I remove those lines and restarted and ending up now with this error. So I first increase both the master and slave max_allowed_packet to 1G yet not working and now I changed to 2G yet is the same. I have run stop slave and
change master to master_host='192.168.**',master_user='repu1',master_password='***', master_log_file='mysql-bin.000039', master_log_pos=640109146;
start slave; yet I keep getting the same error. "Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master'"

I have checked the repu1 password using this command mysql -h 192.168.* -u repu1 -p its working fine too.

show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.100.12
                  Master_User: repu1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000039
          Read_Master_Log_Pos: 640355070
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000039
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: dbn
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1594
                   Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 640109146
              Relay_Log_Space: 247058
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master'
               Last_SQL_Errno: 1594
               Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary lo

Try these steps:

1) Take a mysqldump from master

2) Restore it on a slave, whether it gets restored successfully?

3) You should not keep max_allowed_packet to a high value like 1G or 2G keep it minimal like 16M or 32M and restart Master server.

4) Apply same max_allowed_packet size in slave as well.3

5) Now, stop the slave, execute Change Master .... query accourdingly and start slave

I hope this will work for you.


MySQL MyISAM Locking question It was my understanding that MyISAM uses table locking during inserts and updates.
To me that meant that read and write by other queries on that table is disallowed until the first insertion or update is finished.

However, I am reading the book High Performance MySQL and it clearly states on page 64 that MyISAM allows "Table with concurrent inserts".

Does this mean that MyISAM allows concurrent writes but locks all reads during the writes?  Kindly explain.

In a ready-heavy environment, a MyISAM table behaves a like a prioritized queue.

SELECTs will make all DDL, INSERTs, UPDATEs, & DELETEs, wait until all SELECTs are done
A single write operation makes all SELECT wait. The exception to this rule is Concurrent INSERT. The environment for Concurrent INSERTs:

Only INSERTs and SELECTs
MyISAM tables must not contain any gaps

In other words, SELECTs are not blocked on a MyISAM table as long as newly INSERTs rows are entering a table with no gaps. IF any row being INSERTed has to fill any gaps, then the conditions for Concurrent INSERTs are no longer applicable. SELECTs go back to being handled the way they are normally handled.

If you are performing bulk loading of a MyISAM table, you will need to rev up certain things. For instance, you will need to add this option and restart mysql:

[mysqld]
bulk-insert-buffer-size=256M

Next, change the way you delete the data. Instead of running the DELETE query, try copying the data to be retained into a temp table and then rename. For example, if you have to delete rows from table mydb.mytb whose id <= 500000, run these steps (it should be faster):

use mydb
create table mybt like mytb;
alter table mybt disable keys;
insert into mybt select * from mytb where id > 500000;
alter table mybt enable keys;
drop table mytb;
alter table mybt rename mytb;

Instead of lots of INSERTs into your table, try putting all your new data into a CSV file and use LOAD DATA INFILE to mass populate your production table. You should disable keys before loading and enable keys after the new data is loaded.

There is no need to optimize table doing these things. You may want to run analyze table instead. That will update the index statistics on the MyISAM table.

I hope these suggestions help !!!


Select records in one table but not in another, when the second table is not matching on primary key I have two tables from which I need only the results that appear in one table (list) which do not appear in the second table (cardinal). The list table's primary key is sku, and the table has a vestige id column (which is actually unused in the application at the moment). The cardinal table's primary key is id. The column which should join the tables is sku. These are the tables (relevant fields only, there are other fields that I didn't paste here):

mysql> describe list;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| id              | int(11)      | NO   |     | NULL    |       |
| name            | varchar(63)  | YES  |     | NULL    |       |
| sku             | bigint(20)   | NO   | PRI | 0       |       |
+-----------------+--------------+------+-----+---------+-------+

mysql> describe cardinal;
+------------------+-------------+------+-----+---------+----------------+
| Field            | Type        | Null | Key | Default | Extra          |
+------------------+-------------+------+-----+---------+----------------+
| id               | int(11)     | NO   | PRI | NULL    | auto_increment |
| sku              | bigint(20)  | YES  | MUL | NULL    |                |
+------------------+-------------+------+-----+---------+----------------+

Here are the amount of records in each table:

mysql> SELECT count(*) FROM list;
+----------+
|  2677513 |
+----------+

mysql> SELECT count(*) FROM cardinal;
+----------+
|   970924 |
+----------+

Every record in the cardinal table has a valid entry for the sku column, and all of those valid entries do exist in the list table. There do exist some dupes for sku in the cardinal table.

I need all the records in the list table which do not have a corresponding entry in the cardinal table. However, all the methods that I'm trying are returning either all the records from the list table, or no records at all!

mysql> SELECT count(*) FROM list l LEFT JOIN cardinal c ON c.id=null;
+----------+
| count(*) |
+----------+
|  2677513 |
+----------+
1 row in set (0.49 sec)

mysql> SELECT count(*) FROM list l LEFT OUTER JOIN cardinal c ON l.sku=c.sku where c.id=null;
+----------+
| count(*) |
+----------+
|        0 |
+----------+

The first query obviously doesn't know to match on the sku column. But the second one looks like it should work based on other answers that I've seen online.

I expect about 1700000 - 2000000 rows to be returned. But certainly not 0 or the full table. How should I word the query? This is MySQL 5.5.33 running in Amazon RDS.

MySQL, in fact, all SQL products really barf at
c.id=NULL

It causes confusion. You really mean
c.id IS NULL

You need to change it to
SELECT count(*) FROM list l LEFT OUTER JOIN cardinal c ON l.sku=c.sku where c.id is null;

Give it a Try !!!
ABOUT YOUR QUESTION
Your first query
SELECT count(*) FROM list l LEFT JOIN cardinal c ON c.id=null;

resembles a natural join where nothing matches on the cardinal table, so the count 2677513 is correct.
Your second query
SELECT count(*) FROM list l LEFT OUTER JOIN cardinal c ON l.sku=c.sku where c.id=null;

is little more explicit, but the c.id=null makes it fail, thus getting 0 as a count.


How do I limit the size of my Postgres log? I’m using Postgres 9.5 on Ubuntu 14.04.  I notice I’m generating a lot of Postgres logs in

/var/log/postgresql/postgresql-9.5-main.log

.  I would like to limit the size of this file, and doing some time based rotation (e.g. daily) is not an option.  Is there any way I can tell Postgres to rotate its logs once they reach a certain size?

The PostgreSQL Error reporting and logging documentation states:

  log_rotation_size (integer)

  When logging_collector is enabled, this parameter determines the maximum size of an individual log file. After this many kilobytes have been emitted into a log file, a new log file will be created. Set to zero to disable size-based creation of new log files. This parameter can only be set in the postgresql.conf file or on the server command line.

So, I guess you should set this parameter to the desired value. My postgresql.conf option has the default values:

#log_rotation_size = 10MB       # Automatic rotation of logfiles will
                # happen after that much log output.
                # 0 disables.


IBM DB2 v9.7 HADR TSA failure on the standby server I have an environment here where I have 2 servers running DB2.
One is the primary and the other one is standby. They replicate with HADR and failover is made automatically by TSA.

What's the default behaviour when the standby host fails ? Because for now failover is perfect when the primary peer fails. And then I can reintegrate the old primary peer as standby.
But when the standby peer fails, here is my problem, I have to start HADR manually on this peer. Is it normal ?

Ember Crooks works with HADR and TSA a LOT on DB2. (She uses it for WebSphere Commerce, but you can apply the generalities from her posts.)

Here are two posts(#1 and #2) regarding HADR that feature a similar issue to what you are facing. Based off of what she has documented, what you are seeing appears to be normal behavior (or at least expected behavior, even if it isn't desirable).

Check out Ember's blog (the first link in my post). I subscribe to it as I've learned a lot of key things about DB2 operations-especially related to HADR and TSA-from her.


How to join two table without getting duplicate from both left & right table in Oracle Tables Details:
CREATE TABLE Test2 (
ID INT,
Value INT,
other INT);
CREATE TABLE Test1 (
ID INT,
TYPE INT,
other INT);
INSERT INTO Test2 VALUES (123456, 5, 12);
INSERT INTO Test2 VALUES (123456, 10, 17);
INSERT INTO Test1 VALUES (123456, 00, 2);
INSERT INTO Test1 VALUES (123456, 01, 6);
INSERT INTO Test1 VALUES (123456, 02, 4);
INSERT INTO Test1 VALUES (987654, 00, 7);
INSERT INTO Test1 VALUES (987654, 01, 8);
INSERT INTO Test1 VALUES (456789, 00, 6);
INSERT INTO Test1 VALUES (456789, 01, 16);
This is the Query i m using to avoid duplicate from table Test
SELECT DISTINCT t1.ID, t1.TYPE, t1.other, t2.value
FROM Test1 t1 INNER JOIN Test2 t2 ON t1.ID = t2.ID
GROUP BY t1.ID, t1.TYPE, t1.other, t2.value
ORDER BY t1.ID ASC;
Query Result:
ID  TYPE    other   value
123456  0   2   5
123456  0   2   10
123456  1   6   5
123456  1   6   10
123456  2   4   5
123456  2   4   10
Description:
Expected Result is , when i m fetching match records from tables. Should get  the records from left table(Test1) without duplicate records and avoid duplicate record from right table (Test2).
Reference Check Pic
Expected Query Result are,
23456   0   2   5
123456  1   6   10
123456  2   4
SQL Fiddle Link
http://sqlfiddle.com/#!9/3cd8a0/26
Criteria When there is ID match, should get  matched records from left table (Test1) without duplicate and from right table (Test2) without duplicate.


Converting live MyISAM table to InnoDB I have a table that tracks web traffic. The table is constantly written and read from. Since table is pretty big in size I have to make sure the application will not "freeze" in the process of converting the table's storage engine from MyISAM -> InnoDB. Below is what I'm planning to do based on information I could gather from other posts. Please tell me if I'm missing anything.

mysqldump -u -p --lock-tables=false mydb tracking > tracking.sql - from what I understand this should not lock "tracking" table
Rename table name in the sql file to tracking_new
mysql -u -p mydb < tracking.sql
ALTER TABLE mydb.tracking_new ENGINE=InnoDB;
Insert new rows to "tracking_new" table that were written to the original table while performing dump

SELECT @last_id := tracking.id FROM mydb.tracking_new ORDER BY tracking.id DESC LIMIT 1;
INSERT INTO mydb.tracking_new
SELECT * FROM mydb.tracking WHERE tracking.id > @last_id;

Rename tables

RENAME TABLE
    mydb.tracking TO mydb.tracking_old,
    mydb.tracking_new TO mydb.tracking;

Assuming you don't have any triggers on that table, the simplest way to do this without locking the table is to use pt-online-schema-change.

Make sure you have enough extra disk space to accommodate this change. The table will be larger when you convert it to InnoDB, and you need to maintain both copies of the table during the conversion. For example, if your table is 100 GB make sure you have at least 200+ GB of free disk space.

You should test this out in a non-production environment first to make sure it works for you and to find out exactly how much disk space you need.

The syntax for your case is quite simple.

First do a dry run to verify that it will work:

pt-online-schema-change --alter "ENGINE=InnoDB" D=mydb,t=tracking --dry-run

Then execute it:

pt-online-schema-change --alter "ENGINE=InnoDB" D=mydb,t=tracking --execute


Stored Procedure: Error 1064 I am receiving an error 1064 on the following stored procedure:

12 DELIMITER //
13 DROP PROCEDURE IF EXISTS add_python_photo;
14 CREATE PROCEDURE add_python_photo(  IN fname VARCHAR,
15                                     IN fpath VARCHAR,
16                                     IN fdata LONGBLOB,
17                                     IN python INT,
18                                     IN is_preferred TINYINT,
19                                     OUT python_photo_id INT)
20 BEGIN
21     INSERT INTO python_photo (  file_name,
22                                 file_path,
23                                 file_data,
24                                 python_id,
25                                 preferred)
26                         VALUES (fname,
27                                 fpath,
28                                 fdata,
29                                 python,
30                                 is_preferred);
31
32     SELECT LAST_INSERT_ID() INTO python_photo_id;
33 END//
34 DELIMITER ;

mysql> source stored_procedures/add-python-photo.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
                                                                    IN fpath VARCHAR,
                                                                    IN fdata LONGBLOB,
                                                                    IN python INT,
' at line 1

I'm uncertain as to what syntactic error MySQL is referring to. The list of 'IN' parameters appears to be syntactically correct - yet, that appears to be the source of the error. It seems to fail while processing the 'IN python_id INT' parameter.

UPDATE

Regarding the DELIMITER suggestion, below is another stored procedure - which executes correctly:

12 DELIMITER //
13 DROP PROCEDURE IF EXISTS add_python_parent;
14 CREATE PROCEDURE add_python_parent( IN python_id INT,
15                                     IN mother_id INT,
16                                     IN father_id INT)
17 BEGIN
18     INSERT INTO python_parent (python_id, mother_id, father_id)
19         VALUES (python_id, mother_id, father_id);
20 END//
21 DELIMITER ;

Execution:

mysql> source stored_procedures/add-python-parent.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Your problem is the not the syntax of the stored procedure but the DELIMITER statement.
There are two simple solutions
SOLUTION #1
Change the DELIMITER after you drop the procedure
DROP PROCEDURE IF EXISTS add_python_photo;
DELIMITER //
CREATE PROCEDURE add_python_photo(IN file_name VARCHAR(20),
                                  IN file_path VARCHAR(20),
                                  IN file_data LONGBLOB,
                                  IN python_id INT,
                                  IN preferred TINYINT,
                                  OUT python_photo_id INT)
BEGIN
  INSERT INTO python_photo (  file_name,
                              file_path,
                              file_date,
                              python_id,
                              preferred)
                      VALUES (file_name,
                              file_path,
                              file_data,
                              python_id,
                              preferred);

  SELECT LAST_INSERT_ID() INTO python_photo_id;
END//
DELIMITER ;

SOLUTION #2
Use the DELIMITER // with the DROP PROCEDURE IF EXISTS
DELIMITER //
DROP PROCEDURE IF EXISTS add_python_photo //
CREATE PROCEDURE add_python_photo(IN file_name VARCHAR(20),
                                  IN file_path VARCHAR(20),
                                  IN file_data LONGBLOB,
                                  IN python_id INT,
                                  IN preferred TINYINT,
                                  OUT python_photo_id INT)
BEGIN
  INSERT INTO python_photo (  file_name,
                              file_path,
                              file_date,
                              python_id,
                              preferred)
                      VALUES (file_name,
                              file_path,
                              file_data,
                              python_id,
                              preferred);

  SELECT LAST_INSERT_ID() INTO python_photo_id;
END//
DELIMITER ;

SUGGESTION
You should change the variables so that they are not the exact same name as the DB Columns
CREATE PROCEDURE add_python_photo(IN given_file_name VARCHAR(20),
                                  IN given_file_path VARCHAR(20),
                                  IN given_file_data LONGBLOB,
                                  IN given_python_id INT,
                                  IN given_preferred TINYINT,
                                  OUT python_photo_id INT)
BEGIN
  INSERT INTO python_photo (  file_name,
                              file_path,
                              file_date,
                              python_id,
                              preferred)
                      VALUES (given_file_name,
                              given_file_path,
                              given_file_data,
                              given_python_id,
                              given_preferred);

  SELECT LAST_INSERT_ID() INTO python_photo_id;
END//
DELIMITER ;

GIVE IT A TRY !!!
UPDATE 2016-10-10 13:51 EDT
Turns out to be the use of VARCHAR in the list of Stored Proc variables. While Other RDBMS products will allow for VARCHAR, MySQL will not accept it. VARCHAR variables must be followed by a width definition such as VARCHAR(20) in MySQL's Stored Proc variable list. This was pointed out by @ypercube in the comments, so all kudos for him on this one today.


Database Agnostic Conditional I'm writing a Drupal module and I want it to be agnostic between PostgreSQL and MySQL. I have a field that gets its value from a conditional, and I originally wrote it as an IF(). Then I found out that IF() is a MySQL-ism. So, I looked up the conditionals for PostgreSQL and CASE seemed to be the best tool.

However, I noticed that CASE ends with END in PostgreSQL, while in MySQL it ends with END CASE. Will this destroy my hopes for database agnosticism? Can I write an agnostic query with a conditional?

If you're using CASE expressions in a SELECT statement, you should be looking at this page in the MySQL docs.  As shown there, you use END to end a CASE expression.

If, on the other hand, you're using CASE as part of a flow control statement (like you might use in a stored procedure to conditionally execute other statements), both databases would use END CASE to end a CASE statement.


SQL Server 2008 R2 on Windows 7 x64 Professional - what are the downsides? I need to minimize the cost of the platform, that will run SQL Server 2008 R2 Standard Edition. I have a special licensed version of this SQL server to work with like 20 users (it's an 1C special conditions licensing to be used with 1C products only) with costs not too much. I don't want to buy Windows Server license because with this number of clients (20) I need to buy Enterprise version, which is about $5000.

SQL Server 2008 R2 Standard supports as I can see Windows 7 Professional.
Windows 7 Professional x64 supports up to 192Gb of memory so that's prettty enough.
20 inbound connections limit does not apply to TCP-IP connections as I know.

What are the downsides of using Windows 7 x64 Professional as a host OS for SQL Server 2008 R2?

Your going to run into occasional memory and thread handling issues.  I run 2008 r2 Developer (which is basically enterprise) on Win7 Prof (64x) on a development machine* for writing code.  I do not know the exact reason but SQL acts funky sometimes and you need to either restart service or box.  Ex. No queries will be running and cpu will be pinned at 50% with SQL proc that if you check with ProcExplorer isn't doing anything. If this is something you can live with doing every so often then maybe you can get away with it.  On the other hand if you need high up-time then spend the money on server edition.

*I run like this because Visual Studio runs like crap on server and I like a portable compact dev setup when I'm on site.


Repair table field: Import/append records from specific backup field if they are not null One of my 3rd party applications that writes to my production MySQL database mydb has nulled SOME of the data in one of my fields myfield in table mytable.

I'm trying to import only the NON null records for this field from my backup mydump.sql. The ID field can be used as a unique identifier to make the comparison.

Do I have to import the backup in it's entirety to a temp location, and then build a query/command? Or can I write a query that will also look into an external file? Either way I think the query is a bit above my skill level, so any help will be appreciated.

Ideally, if myfield data in my prod table exists (i.e. not null) then we'll leave that alone, as I'm only looking to import/append myfield data if it's in the backup and not in the prod field.

There are two solutions I can immediately think of for your current situation:

Write a tool to parse the MySQL dump file, extract the records you're interested in, and insert them.
This sounds like a lot of work, and a lot of time (especially if your dump is large).
Do as you suggest in the question: Reimport the data somewhere and use a SQL query to get the fields you want.

My vote would be for (2) because you can be more precise about the selection of the rows (and the SQL will probably be faster than writing a script to parse the dump file. You'll need a machine running MySQL, with enough room to import your data, but you might be able to do that on your main server by restoring to an alternate database.


How do I import a dump for a deleted tablespace? I'm trying to import a database dump, however I am encountering this error:

ORA-39083: Object type TABLE:"MYSCHEMA"."MINF" failed to create with error:
ORA-00959: tablespace '_$deleted$5$0' does not exist
Failing sql is:
CREATE TABLE "MYSCHEMA"."MINF" (
    "P_MID" VARCHAR2(16 BYTE) NOT NULL ENABLE
    , "XML_ORIG_MSG" "SYS"."XMLTYPE"
    , "XML_MSG" "SYS"."XMLTYPE"
    , "P_OFFICE" CHAR(3 BYTE) NOT NULL ENABLE
    , "P_DEPARTMENT" CHAR(3 BYTE) DEFAULT '...'
    , "P_MSG_TYPE" VARCHAR2(15 BYTE) DEFAULT '...'
    , "P_MSG_SUB_TYPE" VARCHAR2(10 BYTE)
    , "P_CDT_MOP" VARCHAR2(6 BYTE)
    , "P_PREFERRED_CDT_MOP" VARCHAR2(6

It's seems the tablespace for the MINF table has been dropped before getting the dump.

Is there a workaround to still import this table?

Update:

I have tried impdp statement, with the same result:

./impdp system/******
DIRECTORY=GPPDUMP
DUMPFILE=SPOCBC_SCD02.ROCKY.20120919102739.dmp.1
,SPOCBC_SCD02.ROCKY.20120919102739.dmp.2
,SPOCBC_SCD02.ROCKY.20120919102739.dmp.3
,SPOCBC_SCD02.ROCKY.20120919102739.dmp.4
LOGFILE=baru.log
REMAP_SCHEMA=SPOCBC_SCD02:OCBCGPP5
REMAP_TABLESPACE=_$deleted$5$0:P_DATA

There is a good chance that the default (and/or temporary) tablespace for MYSCHEMA was dropped. If that is the case, give MYSCHEMA a new tablespace, with quota and re-run.
This can be checked by viewing the entries in dba_users.
This is a bug and should normally not occur.


SQL Server 2008 R2 64bits repair error I had to reinstall the default server instance on one Windows 2008 R2 server.

I checked that the sql groups were cleaned and the registry keys cleaned before reinstalling.

After reinstalling, the sqlagent refused to start.

The only way to start the agent successfully was to add the agent user (domain user) to the group MSSQLUSER$<SERVER_NAME> $MSSQLSERVER

I attempted a repair. all the repair attempts failed with the following message:
 - and removed the user above from the SQL group.

  The following error has occurred:

  The process cannot access the file
  'C:\Windows\SysWOW64\perf-MSSQL10_50.MSSQLSERVER-sqlagtctr.dll'
  because it is being used by another process.

  Any further update fails due to the repair error (cumulative hotfix update 4).

The SQL server log shows the following:

  Error: 18456, Severity: 14, State: 11
  Login failed for user ''. Reason: Token-based server access validation failed with an infrastructure error. Check for previous
  errors. [CLIENT: ]

The user <user> is a domain user used both for the SQL Server database engine and SQL Server Agent services. The password never expires.

I tried to give the user SQL sysadmin rights, then Windows local admin rights without success. So I'm stuck. Looked over the web and didn't find any solution that match

Also tried to : kill the wmi processes locking sql server agent, and stop wmi services (failed).

Any suggestion would be greatly appreciated.

Christophe

This is a known problem with this particular DLL during the repair process.  Copying and renaming the file seems to be a suggested work around - see:

http://connect.microsoft.com/SQLServer/feedback/details/576412/-the-process-cannot-access-the-file-c-windows-syswow64-perf-mssql10-50-mssqlserver-sqlagtctr-dll-because-it-is-being-used-by-another-process


How do I join two tables on two different instances of DB2? There are two instances of DB2 running on the same machine. One has a table aliased to the a table in the other one.  Is there a way to do this in DB2 or does it have to be done via an external tool?

This is with an older version of DB2

The "federation" feature in DB2 will allow you to do what you need.

It's available on the Linux/Unix/Windows version of DB2 - not sure about the mainframe - and there are no licensing issues, it's part of the base product set.

To implement it you only need to create a few extra db objects (wrapper, server mappings, nickname) and you're more or less done.  Follow the documentation and you should be fine.

Cheers.


oracle local partition index I have a table like this:

CREATE TABLE employees
(employee_id NUMBER(4) NOT NULL,
 last_name VARCHAR2(10),
 department_id NUMBER(2))
PARTITION BY RANGE (department_id)
(PARTITION employees_part1 VALUES LESS THAN (11) ,
 PARTITION employees_part2 VALUES LESS THAN (21) ,
 PARTITION employees_part3 VALUES LESS THAN (31) );

Are these statements the same?

CREATE INDEX employees_local_idx ON employees (employee_id) LOCAL;

CREATE INDEX employees_local_idx ON employees (employee_id) LOCAL
(
 PARTITION employees_part1,
 PARTITION employees_part2,
 PARTITION employees_part3
)

Thanks,

Quoting from docs, these are the same:

  If you specify this clause [i.e. LOCAL (...)], then the number of
  PARTITION clauses must be equal to the number of table partitions, and
  in the same order. If you omit partition, then Oracle Database
  generates a name that is consistent with the corresponding table
  partition.


How to find all schemas where a table has records? In my Oracle database, I have a number of schemas (users/owners) with similar table structures (about 100 in total). However, some tables are empty in a lot of schemas. Is there a way to find out for which schemas a table (with a particular name, which will be the same across all the queried schemas) has records/rows?

Is there a view in Oracle which contains all records? For example, if I am looking for columns, I can search ALL_TAB_COLUMNS; if I am searching for tables, I can look in DBA_TABLES or ALL_OBJECTS. Is there an equivalent for records?

This would also be helpful for another query - to find all tables (of a given name) across all schemas, where a particular field has a non-null value in at least one record.

DBA_TABLES.NUM_ROWS column can be used to identify tables that are empty. But this column is updated by DBMS_STATS, so it is not 100% accurate.

There is no view that contains all records.


How should I entitle date columns? Is there any convenience in name date-time related columns? created and delivered, … or createdTS, deliveredTS or createdAt, deliveredAt or…

Chose a name that is unambiguous. created could mean the following things:

"was created yes/no" (a boolean value)
the "object" that was created, maybe the name of it as a string
the datetime when it was created

Probably more. deliveredAt has similar problems. It could be the location where a parcel was delivered at. I really hate these ambiguous naming schemes.

If you just name it createDateTime all ambiguity is gone. The point here is not to add the type to the name. The point is to not leave the name open to interpretation what it means.