Is Percona xtrabackup the right choice for my 500GB MySQL backup? I have a MySQL db with 500GB, consisting mainly of Innodb tables, and one big (200GB) MyISAM table. My current backup strategy is copying the db files to an external harddrive. This causes a downtime of ~4hrs.
My Needs:

Low cost.
Recovery time can be long. No need of real-time replication.
Can handle loss of data in ranges of 1week-2weeks.
Would prefer a short as possible down-time for backup, but can live with anything up to 2hrs.

Currently considering trying to move to Percona xtrabackup. Is it the right solution for my use case? I'm specifically concerned that it locks the MyISAM table and learning curve.

From the sound of this, you could use a different DB infrastructure.

Based on your aforementioned needs, I have a suggestion but would require a little compromise.

SUGGESTION #1 : Use MySQL Replication on the Same Server but Different Disk

Your second need (No need of real-time replication) would have to take a slight backseat in this suggestion. Since you have an external harddrive big enough for XtraBackup, why not use as a datadir for a second instance of MySQL on the same box?

I scripted my own service to provision dedicated mysql instance for ports 3307 - 3399. Here are my past posts on this:

Sep 17, 2012 : mysqlservice command syntax
Sep 30, 2011 : Running multiple instances on the same host

SUGGESTION #2 : Use MySQL Replication on the Different Server

In consideration of your first need (Low Cost), if you have access to a commodity server with adequate diskspace, setup MySQL Replication to that external server. That way you can run backups on the Slave as follows

STOP SLAVE;
XtraBackup
START SLAVE;

You can do this with zero impact (no server load, no disk I/O) on the Master

SUGGESTION #3 : Parallel mysqldumps

You can setup parallel mysqldumps of databases or tables. This may result in a shorter window of time for backups. XtraBackup essentially does a checkpointed transactional backup with point-in-time sensitive operations so that the point-in-time of the backup is when the backup finished. With parallel mysqldumps on a slave which is not replicating gives you backups whose point-in-time is the beginning of the backup. Doing mysqldump on a stopped slave would be storage engine agnostic. In other words, it would not matter whether tables are InnoDB or MyISAM.

If you apply this to SUGGESTION #1, this will incur server load and disk I/O since both Master and Slave reside on the same machine.

If you apply this to SUGGESTION #2, there is no penalty on the Master. You can run backups at will on the Slave. You also will not have to concern yourself with locking MyISAM tables.

Please see my past posts on doing parallel mysqldumps

Jul 24, 2012 : How to copy a MySQL database which is a mixture of InnoDB and MyISAM to the same server?
May 22, 2012 : MySQL Server Backup
Oct 01, 2011 : Quick MySQL Backup
Apr 17, 2011 : How can I optimize a mysqldump of a large database?


SQL Server 2012 Standard Edition - multiple instances and memory utilization If we have multiple instances of a SQL Server 2012 Standard Edition (which has a 64 GB memory limit) on one server which has 192 GBs of RAM, do both instances only have access to the first 64 GBs of memory, or can they access different portions of memory, so that they can each have their own 64 GB "chunk".

This is in consideration for an Active/Active cluster if both nodes fail over to a single node.

Knowing that two instances are absolutely not related to each other, i guess that you can use two instances on one server with 64gig for each. At least to my understanding, OS is the one who assigns memory to apps, so it should be fine :)


Setting up simple relational database structure I'd like to set up simple MySQL database but I'm a bit confused about everything, so let me try to explain...

I have 4 relevant tables, let's call them POS, POS_STRUCT, POS_STRUCT_DIV and MEMBERS.

Relation goes like this:
- POS can have many POS_STRUCT but a single POS_STRUCT can be associated to only one POS
- POS_STRUCT can have many POS_STRUCT_DIV but a single POS_STRUCT_DIV can be associated to only one POS_STRUCT
- POS_STRUCT_DIV can have many MEMBERS but a single MEMBERS can be associated to only one POS_STRUCT_DIV

My table POS_STRUCT_DIV will be consisted of few 'constants'. Let's call them div_1, div_2 (...) div_n, and this is where my confusion starts.
Since this table will probably never change names or amount of those 'constants' should I even create a relation between POS_STRUCT and POS_STRUCT_DIV? Also, since records in MEMBERS will be sometimes associated to POS_STRUCT_DIV with a same name but on different POS and POS_STRUCT, should I create foreign keys for all 3 tables in MEMBERS table? What would be the most efficient way to solve this relation?

I tried to create an UML for this db, although I'm obviously not sure if it is correct at all...:

Unless you embed a list of pos_div_name in a column with type TEXT, you need to create table POS_STRUCT_DIV (POS_div in your diagram) as it has one-to-many relationship with POS_STRUCT.

You could use pos_div_name as the foreign key in the MEMBER as the name is never change. It saves you from looking up pos_div_name when loading MEMBER but the row size could be larger. So it is up to you.


The column 'Date Offset' in table 'Date' has invalid bindings specified I'm trying to deploy a tabular model to a server using the "Analysis Services Deployment Wizard".

When attempting to deploy,I get the below error.

  The JSON DDL request failed with the following error: Failed to execute XMLA. Error returned: 'The column 'Date Offset' in table 'Date' has invalid bindings specified.

The column in question uses the below calculation, which was found here

INT([Date] - TODAY())

What should I look for in order to resolve this error?

The problem in this case was that I was trying to replace an existing column also named 'Date Offset' - this was not a calculated column, but taken from the source table.

Resolution was to do the deployment in 2 phases, first remove the old column, then deploy again to add the new calculated column.


How to optimize and find minimum from the two sql query, by writing it as single query? I have two queries. How can I write them into a single query for efficient performance? I need the minimum value of the result of $sql1 and $sql2. Is it possible?

$sql1="SELECT sum(pv_value) as pv FROM `{$wpdb->prefix}wpmlm_pv_meta` as M WHERE pv_meta like 'left_%' and user_id={$user_id}";
$sql2="SELECT sum(pv_value) as pv FROM `{$wpdb->prefix}wpmlm_pv_meta` as M WHERE pv_meta like 'right_%' and user_id={$user_id}";

From MySQL 8.0, you can use WITH to first filter for user_id:

WITH Raw AS (
  SELECT pv_value, pv_meta
  FROM `{$wpdb->prefix}wpmlm_pv_meta`
  WHERE user_id={$user_id}
), Raw2 AS (
  SELECT
    SUM(
      CASE WHEN pv_meta LIKE 'left_%' THEN pv_value ELSE 0 END
    ) AS leftSum,
    SUM(
      CASE WHEN pv_meta LIKE 'right_%' THEN pv_value ELSE 0 END
    ) AS rightSum
  FROM Raw
)
SELECT
  CASE
    WHEN leftSum < rightSum THEN rightSum
    ELSE leftSum
  END
FROM Raw2

assuming that the external parameters are the same in the queries.

Your original query (queries) used two WHERE-conditions: one for filtering for the user_id, and one for a LIKE prefix search. The best working of the MySQL would be if it would first filter first for the user_id by an index, and then it would use a linear scan for the LIKE. However, there is no guarantee for it to do. It is possible, and even likely, that your query triggers a linear scan on the whole table.

This optimization not only reduces the required two searches to a single one, but it also enforces to commit the user_id scan first.

If you don't have an index of wpmlm_pv_meta(user_id) then create it.


How to use READ_COMMITTED_SNAPSHOT I'm having a database which is used heavily. A lot of writing/reading is done simultanosly. Therefore I've decided to use snapshots to reduce/avoid deadlocks.

I'v turned on READ_COMMITTED_SNAPSHOT, but do I need to specify what transaction level to use in each SP aswell?

Thanks a lot!

Did you set this on the database-level?  If so, then unless otherwise overridden in a query then no you don't need to specify READ_COMMITTED_SNAPSHOT again.

Setting transaction Isolation Level:

ALTER DATABASE YourDb
SET READ_COMMITTED_SNAPSHOT ON

I'm guessing you did the above?

Also, I hope you understand what exactly this Transaction Isolation Level entails.  It has to do with row versioning and is quite the hit on tempdb.  It's not always the answer to heavy "writing/reading".  Not to mention it utilizes row versioning, so you may not be getting what you think you should be getting.


How to delete a column named grave accent in MySQL As title.

Every time when I try to execute the query:

ALTER TABLE tablename DROP COLUMN ```

I got syntax error, which is understandable.

So, what should I do to delete this column whose name is the symbol of grave accent?

OK, I found out the answer.

The correct query should be

alter table tablename drop column ````


Chaining System Query in MySQL I have a table which maintains a user chain

Here is the table structure

+------+--------+
| id   |   user |
+------+--------+
|    2 |      4 |
|   10 |      4 |
|   11 |      4 |
|    1 |      2 |
|    3 |     10 |
|    4 |      5 |
|   34 |      1 |
|   35 |     11 |
+------+--------+

I have to write a query which will out the chain of users

For example : if I have to find chain of user '4' then  result will be

2, 10, 11, 1,3,35, 34

Your Question
What you are looking to do is achievable in SQL Server using CTE recursive queries. MySQL's dialect of SQL does not support such querying.
I do understand the data and the output. For what you are looking to do, this is the solution:
R : 4
NodeQueue: <Empty>
OutputString: <Empty>

Add R's Children to NodeQueue
R : 4
NodeQueue: 2,10,11
OutputString: <Empty>

Remove Front of NodeQueue as R, Add R's Children to the NodeQueue, Add R to OutputString
R : 2
NodeQueue: 10,11,1
OutputString: 2

Remove Front of NodeQueue as R, Add R's Children to the NodeQueue, Add R to OutputString
R : 10
NodeQueue: 11,1,3
OutputString: 2,10

Remove Front of NodeQueue as R, Add R's Children to the NodeQueue, Add R to OutputString
R : 11
NodeQueue: 1,3,35
OutputString: 2,10,11

Remove Front of NodeQueue as R, Add R's Children to the NodeQueue, Add R to OutputString
R : 1
NodeQueue: 3,35,34
OutputString: 2,10,11,1

Remove Front of NodeQueue as R, Add R's Children to the NodeQueue, Add R to OutputString
R : 3
NodeQueue: 35,34 (3 has no children to add)
OutputString: 2,10,11,1,3

Remove Front of NodeQueue as R, Add R's Children to the NodeQueue, Add R to OutputString
R : 35
NodeQueue: 34 (35 has no children to add)
OutputString: 2,10,11,1,3,35

Remove Front of NodeQueue as R, Add R's Children to the NodeQueue, Add R to OutputString
R : 34
NodeQueue: <Empty> (35 has no children to add)
OutputString: 2,10,11,1,3,35,34

Algorithm ends here because the NodeQueue became empty.
GOOD NEWS
I have already written MySQL Stored Functions that do this

Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs
Dec 10, 2012 : MySQL: Tree-Hierarchical query

Please look over these over because the algorithm I described is in these two posts. You will have to decide what constitutes the Root to start with.
Give it a Try !!!


How can I write an "on-update" trigger which takes a table-name as argument? NOTE: The following example is a strongly simplified code, but it illustrates the task in a reproducible example. Looking at this example may make you think "why do it like this"? In reality, the full task is to store an audit-trail for multiple tables, but only if certain conditions are met. The condition is the same for each table (they each share some columns like inserted, updated and so on). So the code when to store an audit-trail is the same for each table. But the actual columns to be copied are different each time. I weant to create a trigger which handles this dynamically such that I don't need to touch it each time the schema changes.

Consider the following working example (question below). This demonstrates a simple schema where every update in table data causes the old values to be moved into data2:

DROP TABLE IF EXISTS data CASCADE;
DROP TABLE IF EXISTS data2 CASCADE;
CREATE TABLE data (
    id SERIAL,
    name TEXT,
    updated TIMESTAMP WITH TIME ZONE
);
CREATE TABLE data2 (
    id SERIAL,
    name TEXT,
    updated TIMESTAMP WITH TIME ZONE
);

CREATE OR REPLACE FUNCTION update_trigger_func()
    RETURNS TRIGGER AS $$
    BEGIN
        NEW.updated = NOW();
        INSERT INTO data2 VALUES (OLD.*);
        RETURN NEW;
    END;
    $$ language 'plpgsql';

CREATE TRIGGER update_trigger
    BEFORE UPDATE ON data
    FOR EACH ROW
    EXECUTE PROCEDURE update_trigger_func();

SET client_min_messages TO 'debug';
INSERT INTO data (name) VALUES ('foo');
COMMIT;  -- Make sure we get new timestamps from NOW()
SELECT * FROM ONLY data;
SELECT * FROM ONLY data2;

SELECT pg_sleep(1);
UPDATE data SET name = 'bar';
COMMIT;  -- Make sure we get new timestamps from NOW()
SELECT * FROM ONLY data;
SELECT * FROM ONLY data2;

SELECT pg_sleep(1);
UPDATE data SET name = 'baz';
COMMIT;  -- Make sure we get new timestamps from NOW()
SELECT * FROM ONLY data;
SELECT * FROM ONLY data2;

Notice that the function update_trigger_func has the "history" table name hard-coded as data2 on the line which reads:

INSERT INTO data2 VALUES (OLD.*);

If data2 was an argument, this function could be reusable for other tables as well. But so far I failed to find the right incantation. I've tried the following two versions so far:

INSERT INTO TG_ARGV[0] VALUES (OLD.*);

But this causes a syntax error:

psql:temptable.sql:28: ERROR:  syntax error at or near "VALUES"
LINE 11:         INSERT INTO TG_ARGV[0] VALUES (OLD.*);

So alternatively I tried with dynamic SQL:

sql := 'INSERT INTO' || TG_ARGV[0] || 'VALUES (OLD.*)';
EXECUTE sql;

But that fails because the OLD variable is not available in the execution context:

psql:temptable.sql:58: ERROR:  missing FROM-clause entry for table "old"
LINE 1: INSERT INTO data2 VALUES (OLD.*)
                                    ^
QUERY:  INSERT INTO data2 VALUES (OLD.*)
CONTEXT:  PL/pgSQL function versioned_update() line 11 at EXECUTE

Given that I would like to use this trigger-function on other tables I can't hard-code the column names. How could I achieve this?

Unfortunately, googling this was fairly difficult as the data-type of OLD is data, and searching for this in Google turned up nothing usable. Additionally, finding out that data is a composite type was key to finding the solution.

The issue is twofold:

The table cannot directly be used in an INSERT statement as it is of type text. So dynamic SQL has to be used. So instead of directly writing

INSERT INTO TG_ARGV[0] VALUES OLD;

the following had to be used:

sql := format(INSERT INTO %I VALUES ...', TG_ARGV[0]);

The OLD variable is not available directly inside an EXECUTE statement. So, building from the previous bullet-point, this was not possible:

 sql := format(INSERT INTO %I VALUES OLD', TG_ARGV[0]);

instead the values from OLD had to be passed on with the USING keyword:

 sql := format('INSERT INTO %I VALUES $1.*', TG_ARGV[0]);
 EXECUTE sql USING OLD;

The final solution:

CREATE OR REPLACE FUNCTION update_trigger_func()
    RETURNS TRIGGER AS $$
    DECLARE
        sql TEXT;
    BEGIN
        NEW.updated = NOW();
        sql := format('INSERT INTO %I VALUES $1.*', TG_ARGV[0]);
        EXECUTE sql USING OLD;
        RETURN NEW;
    END;
    $$ language 'plpgsql';

This allows us to write a trigger which handles events on the data table and writes the history into history table as:

 CREATE TRIGGER data_versioning_trigger_delete
     BEFORE DELETE ON data
     FOR EACH ROW EXECUTE PROCEDURE update_trigger_func('history');


Oracle related to count(*) syntax alternate options i am using a Stored Procedure which are using multiple count(*) in oracle which makes slowness of the server the same i have used in db2 using if exists() which works fine in db2 but the same syntax is not supporting in oracle.
below is my Stored Procedure: i have to use 8 elsif conditions(just use here 2 only)

in the below SP i don't want to use count(*). can anyone help on this:

CREATE OR REPLACE FUNCTION GetStandadrdDefaultDataXml
(
    p_pf_wkstn_oid_sh number,
    p_pf_wkstn_oid_lng number
)
RETURN varchar2
IS
 StdDefaultValue varchar2(1500);
 rate_count1L NUMBER(5);
 rate_count1 NUMBER(5);
 v_count NUMBER(5);
BEGIN

    --RateTable1L
    select count(*) into rate_count1L FROM RateTable1L where wkstn_oid_sh = p_pf_wkstn_oid_sh
            AND wkstn_oid_lng = p_pf_wkstn_oid_lng and exact='STANDARD DEFAULT';
    --RateTable1
    select count(*) into rate_count1  FROM RateTable1 where wkstn_oid_sh = p_pf_wkstn_oid_sh
            AND wkstn_oid_lng = p_pf_wkstn_oid_lng and exact='STANDARD DEFAULT';

    --RateTable1 and RateTable1L
    IF( rate_count1L > 0 )
    THEN
        --RateTable1L
        SELECT rate into StdDefaultValue FROM RateTable1L where wkstn_oid_sh = p_pf_wkstn_oid_sh AND wkstn_oid_lng = p_pf_wkstn_oid_lng  and exact ='STANDARD DEFAULT';
    ELSIF ( rate_count1 > 0 )
    THEN
        --RateTable1
        SELECT rate into StdDefaultValue FROM RateTable1 where wkstn_oid_sh = p_pf_wkstn_oid_sh AND wkstn_oid_lng = p_pf_wkstn_oid_lng  and exact ='STANDARD DEFAULT';
    --RateTable2 and RateTable2L
    --- multiple elsif we have to use here.
        END IF;

    return StdDefaultValue;
END;
/

Please provide solution/hints to handle

One option would be to try the first query and catch the no_data_found exception

begin
  SELECT rate
    into StdDefaultValue
    FROM RateTable1L
   where wkstn_oid_sh = p_pf_wkstn_oid_sh
     AND wkstn_oid_lng = p_pf_wkstn_oid_lng
     and exact ='STANDARD DEFAULT';
exception
  when no_data_found
  then
    SELECT rate
      into StdDefaultValue
      FROM RateTable1
     where wkstn_oid_sh = p_pf_wkstn_oid_sh
       AND wkstn_oid_lng = p_pf_wkstn_oid_lng
       and exact ='STANDARD DEFAULT';
end;

or iterate through the tables

begin
  SELECT rate
    into StdDefaultValue
    FROM RateTable1L
   where wkstn_oid_sh = p_pf_wkstn_oid_sh
     AND wkstn_oid_lng = p_pf_wkstn_oid_lng
     and exact ='STANDARD DEFAULT';
exception
  when no_data_found
  then
    StdDefaultValue := null;
end;

if( StdDefaultValue is null )
then
  begin
    SELECT rate
      into StdDefaultValue
      FROM RateTable1
     where wkstn_oid_sh = p_pf_wkstn_oid_sh
       AND wkstn_oid_lng = p_pf_wkstn_oid_lng
       and exact ='STANDARD DEFAULT';
  exception
    when no_data_found
    then
      StdDefaultValue := null;
  end;
end if;

If it is more likely that the value will be found in one of the tables, it would make sense to check that table first.  Depending on how many tables you have to check and how many times this is getting called, exception handling may not be the most efficient approach.  You might be better off with a single UNION ALL query assuming that the value will appear in at most one table.

  SELECT rate
    into StdDefaultValue
    FROM (SELECT rate
            FROM RateTable1L
           where wkstn_oid_sh = p_pf_wkstn_oid_sh
             AND wkstn_oid_lng = p_pf_wkstn_oid_lng
             and exact ='STANDARD DEFAULT'
          UNION ALL
          SELECT rate
            FROM RateTable1
           where wkstn_oid_sh = p_pf_wkstn_oid_sh
             AND wkstn_oid_lng = p_pf_wkstn_oid_lng
             and exact ='STANDARD DEFAULT')

From a data model standpoint, though, having 8 separate tables with the same basic structure and not knowing which table you need to query seems problematic.  It would seem like the sort of thing that is either calling for a parent entity with a type attribute that stores data common to all 8 tables or for creating a single table with 8 partitions. Either approach would make writing code far easier and would make the code more efficient.


Step through multiple arrays in parallel with FOREACH Is it possible to loop over multiple array with FOREACH in PL/pgSQL? In my case, 3 arrays with identical dimensions. How can I pass the elements like:

for(int i = 0 ; i < array1.length ; i++){
  my_method(array1[i], array2[i], array3[i]);
}

Here is my attempt:

select dugong.session_hugeInsert( 3, '5,2,3,1', '4,3,3,2');

NOTICE:  The transaction is in an uncommitable state.Transaction was rolled back.
NOTICE:  query "SELECT Question_ID, UserResponseID_List, UserResponseList" returned 3 columns 42601
ERROR:  cannot begin/end transactions in PL/pgSQL
HINT:  Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT:  PL/pgSQL function dugong.session_hugeinsert(integer,text,text) line 61 at SQL statement

My function:

 CREATE OR REPLACE FUNCTION Dugong.Session_hugeInsert(
    Quiz_ID_ int,       --FK
    QuestionList_ TEXT,
    UserResponseList_ TEXT
)
RETURNS int AS $$
DECLARE
    Session_ID integer;
    QuestionList INT[];
    Question_ID integer;
    QuizQuestionisDone boolean;
    UserResponseList INT[];
    UserResponseID_List INT[];  -- Get from database
    UserResponseID integer;
    tmp int;

BEGIN
    IF EXISTS ( SELECT 1 FROM dugong.quiz WHERE quiz_id = Quiz_ID_ ) THEN

    QuestionList = string_to_array(QuestionList_, ',');
    UserResponseList = string_to_array(UserResponseList_, ',');
    FOREACH UserResponseID in ARRAY UserResponseList
    LOOP
        tmp := Dugong.UserResponse_Add();
        UserResponseID_List :=array_append(UserResponseID_List, tmp );
    END LOOP;  -- Memo: Got UserResponseID_List array

    FOREACH Question_ID, UserResponseID, tmp IN ARRAY Question_ID, UserResponseID_List, UserResponseList
    LOOP
        RAISE NOTICE '%, %, %', QuestionList[i], UserResponseID_List[i], UserResponseList[i] ;
    END LOOP;
    RETURN 200;

    ELSE
        RETURN -1;
    END IF;
END;
$$ LANGUAGE PLPGSQL;

My first solution does not seem very good:

i:=0; j:=0; k:=0;
FOREACH Question_ID IN ARRAY QuestionList
LOOP
    j := 0;
    FOREACH UserResponseID IN ARRAY UserResponseID_List
    LOOP
        k := 0;
        FOREACH tmp IN ARRAY UserResponseList
            LOOP
        IF (i = j AND j = k) THEN
           RAISE NOTICE '%, %, %', Question_ID, UserResponseID, tmp;
        END IF;
        k := k+1;
            END LOOP;
    j := j+1;
    END LOOP;
i := i + 1;
END LOOP;

Please let me know the proper way to go.

While FOREACH is very convenient to loop through a single array, it's not particularly useful to step through multiple arrays in parallel. Use a plain FOR loop with array_lower() / array_upper() instead:

FOR i IN 1 .. array_upper(UserResponseList, 1)
LOOP
   RAISE NOTICE '%, %, %', QuestionList[i], UserResponseID_List[i], UserResponseList[i];
END LOOP;

Since you created the array in the function, we know the index starts with 1 (default). Else, use:

FOR i IN array_lower(UserResponseList, 1) .. array_upper(UserResponseList, 1)

The index i is defined automatically, the documentation:

  The variable name is automatically defined as type integer and exists
  only inside the loop.

Related cases with more information:

Postgres - array for loop
To ignore result in BEFORE TRIGGER of PostgreSQL?


SQLite: What is the use of specifying data types? As far as I am aware, SQLite does not enforce defined table data types — it has dynamic typing. For example, it allows you to insert strings into number fields, or to exceed the length of the string.

If that is the case, what is the point in defining data types in the CREATE TABLE statement?

Further to Vérace’s answer:

While SQLite doesn’t enforce storage classes as data types, you can apply stricter type-checking using typeof() in a constraint, e.g.

Some_Quantity real check (typeof(Some_Quantity) = 'real')

You do lose automatic type coercion, e.g. it would prevent the values '1' or 1 from being stored in a real column (you would have to specify it as 1.0).  However, you can allow multiple types:

Some_Quantity real check (typeof(Some_Quantity) in ('real', 'integer', 'null')

SQLite's type flexibility is also handy for allowing “out-of-band” indicator values that would otherwise not be valid for the column’s type:

Year check (typeof(Year) = 'integer' or Year in ('illegible', 'not disclosed'))


MySQL update a record on duplicate key update (merge) I have a table products with the follow schema:
CREATE TABLE `products` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `article_id` bigint unsigned NOT NULL,
  `price_cents` int unsigned NOT NULL,
  `quantity` smallint NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_products_unique` (`user_id`,`article_id`,`price_cents`),
  KEY `fk_products_article` (`article_id`),
  CONSTRAINT `fk_products_article` FOREIGN KEY (`article_id`) REFERENCES `articles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_products_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

Now I can do INSERT with this query:
INSERT INTO `products` (`user_id`,`article_id`,`price_cents`,`quantity`)
VALUES (1,1,200,1)
ON DUPLICATE KEY UPDATE `price_cents`=VALUES(`price_cents`),`quantity`=quantity+VALUES(`quantity`)

So now I have 1 product (ID 1) with quantity 1 and price 200.
Now I insert 2 more products with:
INSERT INTO `products` (`user_id`,`article_id`,`price_cents`,`quantity`)
VALUES (1,1,200,1),(1,1,199,1)
ON DUPLICATE KEY UPDATE `price_cents`=VALUES(`price_cents`),`quantity`=quantity+VALUES(`quantity`)

Now I have 2 products, one (ID 1) with quantity 2 and price 200 and the other (ID 2) with quantity 1 and price 199.
Good.
The problem comes now: I want to update the product with price 199 and set a new price to 200. What I do is:
INSERT INTO `products` (`id`,`user_id`,`article_id`,`price_cents`,`quantity`)
VALUES (2,1,1,200)
ON DUPLICATE KEY UPDATE `price_cents`=VALUES(`price_cents`),`quantity`=quantity+VALUES(`quantity`)

and what I would like is a single product with id 1, price 200, and quantity 3, but I get Number:0x426, Message: "Duplicate entry '1-1-200' for key 'products.idx_products_unique' because MySQL does not delete the product with ID 2.
Is there a way to achieve this in MySQL (keep in mind that I want to perform these operations in bulk)?

you can use UPDATE like this:
UPDATE `products`
SET `price` = 200
WHERE ID = 2;

or REPLACE like this:
REPLACE into products (id, price) values(2, 200);


Sample data:

with data as
(
  select 1000 id, 'ABC' name, 2016 year, 50000 sales from dual union all
  select 1000 id, 'ABC' name, 2017 year, 80000 sales from dual union all
  select 1000 id, 'ABC' name, 2015 year, 90000 sales from dual union all
  select 1000 id, 'ABC' name, 2014 year, 45000 sales from dual union all
  select 1000 id, 'ABC' name, 2013 year, 30000 sales from dual union all
  select 2000 id, 'PQR' name, 2017 year, 80000 sales from dual union all
  select 2000 id, 'PQR' name, 2015 year, 90000 sales from dual union all
  select 2000 id, 'PQR' name, 2014 year, 75000 sales from dual union all
  select 2000 id, 'PQR' name, 2013 year, 60000 sales from dual union all
  select 3000 id, 'XYZ' name, 2015 year, 123000 sales from dual union all
  select 3000 id, 'XYZ' name, 2013 year, 56000 sales from dual union all
  select 3000 id, 'XYZ' name, 2012 year, 45000 sales from dual union all
  select 3000 id, 'XYZ' name, 2011 year, 30000 sales from dual
)

Result:

select
  id, name,
  case when rn = 1 then year else fy - 1 end as year,
  case when (rn = 1 or fy - 1 = year) then sales else 0 end as sales
from
(
  select
    id, name, year, sales,
    rank() over (partition by name order by year desc) as rn,
    first_value(year) over (partition by name order by year desc) as fy
  from data
)
where rn <= 2;

        ID NAM       YEAR      SALES
---------- --- ---------- ----------
      1000 ABC       2017      80000
      1000 ABC       2016      50000
      2000 PQR       2017      80000
      2000 PQR       2016          0
      3000 XYZ       2015     123000
      3000 XYZ       2014          0


SQL Server data migrations go through client machine or not? I want to move a lot of data from a SQL Server database on one machine to another machine. Both machines are remote. If I run the Copy Database wizard, will it pull all the data across the Internet to my client machine and then push it back out to the destination? Or will the source and destination machines communicate directly?

It's a very big database, so this matters quite a bit.

One complexity is that my login only shows a view of a subset of the data, so backup and restore would not work. I have to copy from a view to a table.

I can set things up so the two remote machines can see each other. I don't think I would be able to run the wizard on one of them, though, because I all I have is a login to the db and no other access to the OS.

If you run the copy database wizard then yes all the data will go through which ever machine you run the copy database wizard on.

If you are moving a large database, especially one with a complex schema then backup and restore is a much better option.


How to define a parameter that will be used as an IN argument inside a function? I want to make a function that receives two parameters. The first one represents the salaries from a group of employees, and second one, the codes from a group of departments. Both, P_IN_SALARIES and P_IN_DEPARTMENTS_CODES parameters, will be used as
arguments in an IN function of a query, just as demonstrated in the code below:
CREATE OR REPLACE FUNCTION public.get_employees_id(P_IN_SALARIES WHICH_TYPE_COMES_HERE, P_IN_DEPARTMENTS_CODES WHICH_TYPE_COMES_HERE)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
declare
    v_employees_ids text;
begin

    select STRING_AGG(employee.id || '', ',') into v_employees_ids
    from employee
    inner join departament on department.id = employee.departament_id
    where employee.salary in (P_IN_SALARIES)
    and department.code in (P_IN_DEPARTMENTS_CODES);

    RETURN v_employees_ids;
END;
$function$

What is the type of a IN parameter in a SQL statement?
Is there a generic one that I might use in order to allow a kind of portability on an occasional database exchange? (e.g. to Oracle)

Use an array and the ANY operator:
CREATE FUNCTION public.get_employees_id(P_IN_SALARIES int[], P_IN_DEPARTMENTS_CODES text[])
...
    select STRING_AGG(employee.id || '', ',') into v_employees_ids
    from employee
    inner join departament on department.id = employee.departament_id
    where employee.salary = any (P_IN_SALARIES)
    and department.code = any (P_IN_DEPARTMENTS_CODES);
...

When calling the function you can use any array notation you like, I prefer array[...]:
select get_employees_id(array[100,200], array['code1', 'code2'])


I gave 3 large Excel files need to be loaded in Single SQL table I have 3 large Excel files each sized 56896 KB, I need to load all 3 files  into same SQL table. what is better way to handle?
since My excel has more data, my excel hang up when I open and search any data.

I tried to create a ssis package that had excel source and OLDEB destination to load into table.  But I create EXCEL source by selecting one file atleast, the Visual studio hangs up. I could not see the preview of my sheet also.

Please let me know how can we proceed? I need to load every month 3 large files to do some analysis.

Here is an example of using Powershell to import Excel data to SQL Server.  Referencing the example in Using Powershell to Import Excel file into SQL Server:

The script uses the Provider=Microsoft.ACE.OLEDB.12.0 which I downloaded from
Microsoft Access Database Engine 2010 Redistributable.  I had to download the 32-bit version on my computer due to other Office products being installed.  Due to the fact that I had to use the 32-bit version, I also had to run Powershell under the 32-bit version (which on my computer was located at C:\Windows\SysWOW64\WindowsPowerShell\v1.0).  I created the sample table ExcelData_fill (which is referenced in the script below) before running the Powershell script.  You'd need to make changes to the top of the script to point to your Excel files and also specify the correct SQL Server instance information.  You should be able to import your data by modifying the script to point to each Excel file.

#Change these settings as needed
$filepath = 'C:\temp\ps.xlsx'
#Comment/Uncomment connection string based on version
#Connection String for Excel 2007:
$connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$filepath`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"
$qry = ‘select name from [sheet1$]’
$sqlserver = 'localhost'
$dbname = 'test'
#Create a table in destination database with the with referenced columns and table name.
$tblname = 'ExcelData_fill'

#######################
function Get-ExcelData
{

    param($connString, $qry='select * from [sheet1$]')

    $conn = new-object System.Data.OleDb.OleDbConnection($connString)
    $conn.open()
    $cmd = new-object System.Data.OleDb.OleDbCommand($qry,$conn)
    $da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
    $dt = new-object System.Data.dataTable
    [void]$da.fill($dt)
    $conn.close()
    $dt

} #Get-ExcelData

#######################
function Write-DataTableToDatabase
{
    param($dt,$destServer,$destDb,$destTbl)

    $connectionString = "Data Source=$destServer;Integrated Security=true;Initial Catalog=$destdb;"
    $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
    $bulkCopy.DestinationTableName = "$destTbl"
    $bulkCopy.WriteToServer($dt)

}# Write-DataTableToDatabase

#######################
$dt = Get-ExcelData $connString $qry
Write-DataTableToDatabase $dt $sqlserver $dbname $tblname


How to speed up query on table with millions of rows The Issue:

I'm working on a big table that consists about 37mln rows. Data include measurements of many devices made in certain time e.g. '2013-09-24 10:45:50'. Each day all of those devices are sending many measurements in different intervals on different times. I want to make a query which selects all the most actual ( 'actual' I mean the latest from all measurements made in each day) measurement of each day for 2 months e.g from 2013-01-01 to 2013-02-01.

The problem is that this query takes so much time to go, despite all of the indexes i've made on different columns. I've also created auxiliary table that contains max(MeterDate) and MeasurementsId when the measurement was given. I've noticed that index can't be made on MeterDate because it contains date and time which is not useful for making an index on it. So i converted the MeterDate -> CONVERT(date,  MeterDate). I though that after joining The auxiliary table with [dbo].[Measurements] the query would be faster but still query takes more than 12s which is too long for me.

The structure of table:

Create table [dbo].[Measurements]

[Id] [int] IDENTITY(1,1) NOT NULL,
[ReadType_Id] [int] NOT NULL,
[Device_Id] [int] NULL,
[DeviceInterface] [tinyint] NULL,
[MeterDate] [datetime] NULL,
[MeasureValue] [decimal](18, 3) NULL

Every row of Measurements table include measurement value on direct MeterDate e.g. "2008-04-04 13:28:44.473"

Direct select structure:

DECLARE @startdate datetime= '2013-07-01';
DECLARE @enddate datetime = '2013-08-01';

SELECT *
FROM [dbo].[Measurements]
WHERE [MeterDate] BETWEEN @startdate and @enddate

Does anyone knows how to rebuilt table or add new or add indexes on which column that speed up query a bit ? Thanks in advance for any info.

Edit:

The table that I used was created by this query

with t1 as
(
    Select  [Device_Id], [DeviceInterface],  CONVERT(date,  MeterDate) as OnlyDate, Max(MeterDate) as MaxMeterDate
    FROM [dbo].[Measurements]
    GROUP BY [Device_Id], [DeviceInterface], CONVERT(date,  MeterDate)
)
Select t1.[Device_Id], t1.[DeviceInterface],t1.[OnlyDate], r.Id
INTO [dbo].[MaxDatesMeasurements]
FROM t1
JOIN [dbo].[Measurements] as r ON r.Device_Id = t1.Device_Id AND r.DeviceInterface = t1.DeviceInterface AND r.MeterDate = t1.MaxMeterDate

Then I wanted to join the new created table [dbo].[MaxDatesMeasurements] with old [dbo].[Measurements] and select direct rows

DECLARE @startdate datetime= '2013-07-01';
DECLARE @enddate datetime = '2013-08-01';

Select *
From [dbo].[MaxDatesMeasurements] as t1
Join [dbo].[Measurements] as t2 on t1.[Id] = t2.[Id]
WHERE t1.[OnlyDate] BETWEEN @startdate AND @enddate

On reason this can happen is that you're using local variables.

  The problem is that this query takes so much time to go, despite all
  of the indexes i've made on different columns.

Here's an example using a similar setup. In the Stack Overflow schema there's a narrow-ish table called Votes that looks like this.

With no index on CreationDate, our only option would be to scan the Clustered Index. But if we create one only on CreationDate, the optimizer can choose to use that index if it thinks doing a Key Lookup for the rest of the columns is cheaper than scanning the Clustered Index and applying a predicate.

CREATE INDEX ix_yourmom ON dbo.Votes(CreationDate)

For this query:

DECLARE @StartDate DATETIME = '2010-07-01';
DECLARE @EndDate DATETIME = '2010-07-02';

SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate BETWEEN @StartDate AND @EndDate;
GO

The cardinality estimate for unknown variables using between is 16.4317%. That leads to a clustered index scan and a missing index request for an index that covers the entire query.

If you run the query with RECOMPILE, you allow for the parameter embedding optimization.

DECLARE @StartDate DATETIME = '2010-07-01';
DECLARE @EndDate DATETIME = '2010-07-02';

SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate BETWEEN @StartDate AND @EndDate
OPTION ( RECOMPILE );

Which gives us a different query plan, and a more accurate estimate.

Hope this helps!


Have postgres' pg_dump export an index I have an index that is very expensive to calculate, and I'm looking for ways to have it be restored or transfered in a simple copy fashion. It would be done along with the underlying data of course.

Alternatively could I do it with files? Something akin to using pg_class.relfilenode to find where the index lives, and copy these files.

What you want is only possible with filesystem-level backup. Index entries ultimately contain references to tuple IDs (TID) of table rows, which are essentially physical pointers to records within database files. Every row update will move it to a different location with a different TID, making the old TID useless unless updated concurrently.

A table restored from a dump will have (possibly all) row TIDs different from the original; if you somehow managed to duplicate the original index and use it to access this new table, results would be undefined.

Only a filesystem-level backup of the index files together with the corresponding table file(s) can guarantee that TIDs remain unchanged.


How can I export a subset of tabledata from a prodution database into my local testing database? We have a relatively big production postgres based db: ~20GB.
The PostgreSQL database is hosted on heroku.

I would like to copy a small subset of the table data to my local database so I can run some tests on them without having to work on production.

I do not want to generate sample data myself, but rather use the data which already exists in the production environment.

~100 rows from each table in the database would be sufficient.
Is there an easy way to accomplish this?

I haven't used this tool, but Jailer promises to do just that

  Database Subsetting and Browsing Tool. Exports consistent, referentially intact row-sets from relational databases (JDBC)

http://sourceforge.net/projects/jailer/


Crytography in PostgreSQL I'm thinking to use PostgreSQL for a project because its free, people say its good, but I'll need some encryption in my db. I did a little search and I found these options in PostgreSQL:

bf
md5
xdes
des

I don't know what is the "most safe", Can you guys help me with this? In this case I prefer security with a little slowness than speed and a little security.

In this case I guess encryption is better than "just" hash the passwords, and really need this on db. I need a strong encryption for login information of my users.The difficulty is not how to store, but how to protect this with encryption.

If you want to validate your user's password, one of the best algorithms to use is bcrypt.

I'd recommend you implement the encryption and validation at your application layer, so you work with plain text data less time possible.

But if you are looking for one way to do that on PostgreSQL's side, you can use pgcrypto extension. So you have some steps:

Install the contribs package on your system (if not done already).
Connect to your database and create the extension:

CREATE EXTENSION pgcrypto;

Your users tables will need at least the following fields:

CREATE TABLE users(
    login text PRIMARY KEY,
    passwd text NOT NULL
);

When you insert a new password, you do:

INSERT INTO users(login, passwd)
VALUES('my_login', crypt('my_password', gen_salt('bf')));

being 'my_login' the user's login (e-mail, username, etc.), and 'my_password' the plain users password. And 'bf' the encryption algorithm you are using, being 'bf' the bcrypt.
Once your user is there, to validate its password you do:

SELECT count(*) AS match
FROM users u
WHERE
        u.login = 'my_login'
        AND u.passwd = crypt('my_password', u.passwd);

If the above query returns 1, then the login was successful (login and passwd matched), if it returns 0, then the login failed (either login or passwd didn't match).

But again, I still recommend you use the same process in your application instead, because that way you don't ever need to send the plain password to the database, and work with encrypted password as soon as possible. To do on the application the idea is that your query returns the encrypted password, and you just validate it app-side, like (Python-based example):

import bcrypt
import psycopg2

def validate_login(user_login, user_passwd):
    con = psycopg2.connect('<your connection string>')
    cur = con.cursor()
    cur.execute("SELECT passwd FROM users WHERE login = %s", (user_login,))
    db_passwd = cur.fetchone()
    cur.close()
    con.close()

    if db_passwd == None:
        return false # login not found
    else:
        if bcrypt.hashpw(user_passwd, db_passwd[0]) == db_passwd[0]:
            return true # login successfully
        else:
            return false # login exists but passwd is incorrect


Index Allocation Maps I am a developer reading the book "Querying Microsoft SQL Server 2012." I have come across the following diagram:

Why are the pages in the first two rows of the diagram (row 1 starting: 1:174 and row 2 starting: 1:41) not part of an extent? The documentation says: "all pages are stored in extents":
How does the number scheme work e.g. what does 1:174 mean?
Directly above the diagram, the book says: "IAM pages for an object are organized as a doubly linked list;"  The diagram does not show any links between the pages.

They are part of a mixed extent, the diagram just didn't draw a grey box behind them, probably more to keep it from being too busy as much as anything.
Data File : Page
Again, I think this is just meant to simplify and not make the diagram too busy, as well as not give any sort of suggestion that pages will necessarily be linked in a certain order. Surely you can picture the arrows that would represent a doubly-linked list?

For most of this, you're much better off asking the author of the book and creator of the diagram, than a community of peers.


Switching between queries with "date  now" slows down the performance a lot In our MySQL database we have a table with about 12 million posts. We are using a datetime that is indexed to get posts from it.

There is never any UPDATE, INSERT or DELETE made to the table.
What we do is running three different queries in a row:

SELECT CONCAT('datetime', 'Z') as datetime FROM table WHERE
datetime >= FROM_UNIXTIME(1479423600)
AND
datetime <= FROM_UNIXTIME(1479510000)
ORDER BY datetime

SELECT CONCAT('datetime', 'Z') as datetime FROM table WHERE
datetime > FROM_UNIXTIME(1479423600)
LIMIT 1

SELECT CONCAT('datetime', 'Z') as datetime FROM table WHERE
datetime <= FROM_UNIXTIME(1479510000)
LIMIT 1

If we only runt the first two, it works super fast, or if we run one repeatedly after itself, it takes 0.0001 second. But as soon as we search in the opposite direction it gets super slow, because I guess MySQL needs to rebuild the INDEX. Even if we limit the result to 1 post it takes 5 seconds, and we are running this on a dedicated server.

We will get a new server with SSD discs and 64 GB RAM but my colleague says that will not improve the speed enough. What he has suggested is that we create another table and revert the posts there. I would like to find another solution, there must be a way MySQL can store two differently structured indexes for the same table.

Why is the request so slow in some cases?

Answer: The concat slows down everything crazy much!

I simply removed the concat and all queries is super fast now!

You can't get 10 microseconds -- there is just too much overhead in performing a query.  You can get about 1 millisecond.  But you will need INDEX(datetime), at which point, the LIMIT 1 queries will probably run in about a millisecond.  The first query will take an amount of time proportional to the number of rows returned.  If it is a million rows, it may take seconds.

Or perhaps you have the "Query cache" turned on.  It should probably be turned off.  You are probably updating the table a lot; the QC gets flushed for every write.  So 10us will not be consistent, only if no writes happen since you last performed exactly the same query.

You "want the first two..." -- But the answer to the second one is included the first.  And if you add ORDER BY datetime, then it will be the first row.

MySQL "never" needs the index to be rebuilt.

Do not use MyISAM; switch to InnoDB.

One thing that may be happening.  With MyISAM there is only "table locking".  If a lot of writes are coming in, your SELECT could be held up waiting for them.  (Not a problem with InnoDB.)

How many rows in that one day plus one second?

Are those the actual queries?  Or are you limiting to a specific user.  (That would radically change everything I have said.)

If you want to discuss any more, please provide SHOW CREATE TABLE and tell us approximately how many rows in the table.


TSQL performance - JOIN on value BETWEEN min and max I have two tables in which I store:

an IP range - country lookup table
a list of requests coming from different IPs

The IPs were stored as bigints to improve lookup performance.

This is the table structure:

create table [dbo].[ip2country](
    [begin_ip] [varchar](15) NOT NULL,
    [end_ip] [varchar](15) NOT NULL,
    [begin_num] [bigint] NOT NULL,
    [end_num] [bigint] NOT NULL,
    [IDCountry] [int] NULL,
    constraint [PK_ip2country] PRIMARY KEY CLUSTERED
    (
        [begin_num] ASC,
        [end_num] ASC
    )
)

create table Request(
    Id int identity primary key,
    [Date] datetime,
    IP bigint,
    CategoryId int
)

I want to get the request breakdown per country, so I perform the following query:

select
    ic.IDCountry,
    count(r.Id) as CountryCount
from Request r
left join ip2country ic
  on r.IP between ic.begin_num and ic.end_num
where r.CategoryId = 1
group by ic.IDCountry

I have a lot of records in the tables: about 200,000 in IP2Country and a few millions in Request, so the query takes a while.

Looking at the execution plan, the most expensive part is a Clustered Index Seek on index PK_IP2Country, which is executed many times (the number of rows in Request).

Also, something that I feel a little strange about is the left join ip2country ic on r.IP between ic.begin_num and ic.end_num part (don't know if there's a better way to perform the lookup).

The table structure, some sample data and query are available in SQLFiddle: http://www.sqlfiddle.com/#!3/a463e/3 (unfortunately I don't think I can insert many records to reproduce the problem, but this hopefully gives an idea).

I'm (obviously) not an expert in SQL performance/optimizations, so my question is: Are there any obvious ways in which this structure/query can be improved performance-wise that I am missing?

You need an additional index.  In your Fiddle example I added:

CREATE UNIQUE INDEX ix_IP ON Request(CategoryID, IP)

Which covers you for the request table and gets an index seek instead of a clustered index scan.

See how that improves it and let me know.  I'm guessing it'll help quite a bit since the scan on that index is I'm sure not cheap.


Find and return items based on matching strings across 17 tables I must start by saying that I am at best a sql novice. I am doing some data analysis and consolidation for my company and need to find if items match across any of the tables. The info in the tables came from 3 different databases and a bunch of spreadsheets that had been used.

The information I'm looking to match up is circuit numbers.  Some of the tables have the same design, but the information is not always entered in the same fields or even the same format. The tables do not have the same number of columns and sometimes the string that would match across them is a sub string in one of the other tables. The id's can be both numeric and alphanumeric. One other issue is that no one single table has enough good/correctly entered data to use as a primary table to compare the rest to.

What I am looking to achieve is to somehow search all of the tables and if there is a string that matches across any number of them, I would like to identify where it matched and create a table that contains the info from all columns in the row(s) that contained the match.

Below is a list of the tables, the number columns each has and the fields in that table that could potentially contain a circuit ID.  I know this is confusing and I hope I was able to explain it and even more hopeful that someone may be able to help since doing a manual stare and compare will take months. I have tried everything that my limited knowledge allows from joins to unions to putting it in excel and using tools like vlookup, but the state of the data is creating too many stumbling blocks for me.  It seems that I may need to use some sort of stored procedure to check through the tables and return the results, but this is something that is quite a bit over my head.

Table 1 - 152 columns - CktNum(nvarchar(8)), IXC(nvarchar(25)), BOC(nvarchar(25)), AML(nvarchar(10)), LLOCKTID(nvarchar(25)), assignnum(nvarchar(50))

Table 2 - 17 columns - IXC CKT ID(nvarchar(255)), BOC CKT ID(nvarchar(255)), Local CKTID(nvarchar(255))

Table 3 - 10 columns - Name(nvarchar(255)), Name1(nvarchar(255)), Name2(nvarchar(255))

Table 4 - 21 columns - Local Circuit ID(nvarchar(255)), IXC Circuit ID(nvarchar(255)), BOC Circuit ID(nvarchar(255))

Table 5 - 22 columns - Local Circuit ID(nvarchar(255)), IXC Circuit ID(nvarchar(255)), BOC Circuit ID(nvarchar(255))

Table 6 - 20 columns - Local Circuit ID(nvarchar(255)), IXC Circuit ID(nvarchar(255)), BOC Circuit ID(nvarchar(255))

Table 7 - 20 columns - Local Circuit ID(nvarchar(255)), IXC Circuit ID(nvarchar(255)), BOC Circuit ID(nvarchar(255))

Table 8 - 20 columns - Local Circuit ID(nvarchar(255)), IXC Circuit ID(nvarchar(255)), BOC Circuit ID(nvarchar(255))

Table 9 - 5 columns - Local ID(nvarchar(255)), Circuit ID(nvarchar(255)), ECCKT ID(nvarchar(255))

Table 10 - 5 columns - Local ID(nvarchar(255)), Circuit ID(nvarchar(255)), ECCKT ID(nvarchar(255))

Table 11 - 7 columns - Local ID(nvarchar(255)), Circuit ID(nvarchar(255)), ECCKT ID(nvarchar(255))

Table 12 - 5 columns - Local ID(nvarchar(255)), Circuit ID(nvarchar(255)), ECCKT ID(nvarchar(255))

Table 13 - 18 columns - Circuit ID(nvarchar(255)), Customer(nvarchar(255))

Table 14 - 17 columns - Circuit ID(nvarchar(255)), Customer(nvarchar(255))

Table 15 - 40 columns - ECCKT(nvarchar(255)), LO CKT ID(float), CKR(nvarchar(255))

Table 16 - 9 columns - Circuit ID(nvarchar(255)), Customer(nvarchar(255))

Table 17 - 17 columns - Circuit ID(nvarchar(255)), Customer(nvarchar(255)), Contact(nvarchar(255))

Is the application going to search for these one by one?  Is the performance of this query important?  Easy (ish) solution would be to create a stored procedure that accepted some arguments and then returned the results from searching each table.

Results from the stored procedure could include the table it found a match on, the primary key and the field that matched.  Then the caller of that stored procedure could do more work based on that result set.

EDIT - Below is a very basic sample procedure for you.

CREATE PROCEDURE dbo.sp_FindCircuit
    (
    @Search NVARCHAR(100)
    )
AS
BEGIN

--Hold Results.
DECLARE @Results TABLE
    (
    TableName VARCHAR(100) NULL
    , FieldName VARCHAR(100) NULL
    , KeyValue INT NULL
    )

INSERT INTO @Results
        (TableName, FieldName, KeyValue)
SELECT 'Table 5'
    , 'Circuit ID'
    , <PrimaryKey>
FROM [Table 5]
WHERE CircuitID LIKE ('%' + @Search + '%')

--Repeat for each table.

--Return data.
SELECT TableName, FieldName, KeyValue
FROM @Results

END


SQL - double column used from primary key with autoincrement I am user the following as primary key in the SqlDB:

I am doing the Index as Autoincrement, but it is autoincremented in general not per user.
Example userid=1,index=1, then insert another for userid=2 then inserted userid=2index=2
Then we insert a new entry for userid=2 theninserted userid=2 index=3 and not 1.

Is it possible to do what I want?

You can't have more than one IDENTITY column per a table.
Use MAX(index)+1 where userid=X, when inserting a new row without auto-increment.


Purging of unreferenced data via unconditional DELETE, by leaning on foreign key constraints I'm designing a database from which old data has to be purged regularly for legal reasons, and I'm trying to figure out the best way to organise the purging.
Dealing with the main tables is trivial, since they all have a month column and cascading deletes are set up for all owned rows in detail tables. So I can simply iterate over a list of these "master" tables and delete rows older than a given month.
However, there are some tables for which things aren't that simple. They can be referenced from quite a few other tables but their rows have to disappear when they are no longer referenced (because of data protection laws).
I could write some code find out which rows in a certain table aren't referenced anywhere else, based on the foreign key meta data in the database schema.
However, I'd rather lean on the foreign key constraints instead and simply use the moral equivalent of DELETE FROM @TableName. The constraints keep referenced rows from getting deleted and all unreferenced rows disappear as intended. Hence I would make simply make a second list of table names to which the unconditional DELETE should be applied during a purge, and that's it.
That solution would certainly be ideal: you can't make it any simpler, and it is directly based on the declared database schema, i.e. the foreign key constraints.
Would that be considered acceptable practice? Are there any drawbacks*?
*) apart from the fact that the consequences of someone dropping a required foreign key constraint are not only unusually dire but also unusually delayed (until the next purge at the end of the year)
Additional considerations
I found the fly in the ointment: the constraint not only blocks the deletion, it also results in an error state and abortion of the current statement. Hence the scheme doesn't work as is. Back to the drawing board then... Any pointers welcome.

One option would be to create views for these "inconvenient" tables that only return rows where all foreign key relationships have been broken.  Then you could do unconditional deletes from those views.

You would have the overhead of coding each of these views*, but that's a one-time startup cost.  This is essentially the same as what you mentioned already:

  I could write some code find out which rows in a certain table aren't referenced anywhere else

But in the end, you will have the "unconditional deletes" in your cleanup job.

Note: the approach below would not scale well on really large tables without indexes, and potentially some form of batching (e.g., putting a TOP 100 and ORDER BY in the view definition

Say you have two "main tables" that reference this "inconvenient table" (the one you need to remove rows from when they are no longer referenced:

CREATE TABLE dbo.InconvenientTable
(
    Id int IDENTITY(1,1) NOT NULL,
    [Description] varchar(10) NOT NULL,

    CONSTRAINT PK_InconvenientTable
        PRIMARY KEY (Id)
);
GO

CREATE TABLE dbo.MainTable1
(
    Id int IDENTITY(1,1) NOT NULL,
    [Description] varchar(10) NOT NULL,
    InconvenientTableId int NOT NULL,

    CONSTRAINT FK_MainTable1_InconvenientTable
        FOREIGN KEY (InconvenientTableId)
        REFERENCES dbo.InconvenientTable (Id)
);

CREATE TABLE dbo.MainTable2
(
    Id int IDENTITY(1,1) NOT NULL,
    [Description] varchar(10) NOT NULL,
    InconvenientTableId int NOT NULL,

    CONSTRAINT FK_MainTable2_InconvenientTable
        FOREIGN KEY (InconvenientTableId)
        REFERENCES dbo.InconvenientTable (Id)
);

The inconvenient table has 3 rows, all 3 of which are referenced in MainTable1, while only 1 is referenced in MainTable2.

INSERT INTO dbo.InconvenientTable
    ([Description])
VALUES
    ('One'),
    ('Two'),
    ('Three');
GO

INSERT INTO dbo.MainTable1
    ([Description], InconvenientTableId)
VALUES
    ('One', 1),
    ('Two', 2),
    ('Three', 3);

INSERT INTO dbo.MainTable2
    ([Description], InconvenientTableId)
VALUES
    ('Two', 2);

Now we need a view that shows all rows in dbo.InconvenientTable that are not referenced by the two main tables:

CREATE VIEW dbo.InconvenientTable_RowsToDelete
AS
SELECT it.*
FROM dbo.InconvenientTable it
WHERE
    NOT EXISTS (SELECT NULL FROM dbo.MainTable1 mt1 WHERE mt1.InconvenientTableId = it.Id)
    AND NOT EXISTS (SELECT NULL FROM dbo.MainTable2 mt2 WHERE mt2.InconvenientTableId = it.Id);
GO

Currently all rows are referenced, so this query returns 0 rows:

Now let's delete the 'Two' row from both main tables:

DELETE dbo.MainTable2 WHERE InconvenientTableId = 2;
DELETE dbo.MainTable1 WHERE InconvenientTableId = 2;

And now the view returns that unreferenced row:

Now we can delete everything from the view, which successfully removes the 'Two' row from our inconvenient table for compliance reasons:

*You could also attempt to automate the creation of the views with dynamic SQL and metadata queries, but that seems risky


Shall I use JSONB for integers based array? I need to store a list of ids in array per each row.
I thought to use JSONB, then store it as : {ids: [1, 2, 3, 4]}.
Then, I found Array type, that I can use to store ids directly into.
The question is, what is the recommended data type for my case?

Unless you have a very good reason to do otherwise, the recommended storage is always normalised.

Store the integers in a separate table of (other_row_id, the_integer) and join on them.

If you must use arrays, use PostgreSQL's native arrays ARRAY[1,2,3].

Storing them as jsonb is a spectacularly inefficient and clumsy way to do it with essentially no advantages.


How do I get the DDL used to create function based index? I am reverse-engineering the schema of an Oracle database. I have a table and I created a function-based index on it.

DROP TABLE Fbi_tab;
CREATE TABLE Fbi_tab (
  a INTEGER,
  b INTEGER,
  c INTEGER
);

CREATE INDEX Idx ON Fbi_tab (a+b*(c-1), a, b);

How can I generate the DDL used to create this index?

You can use DBMS_METADATA package.

  The DBMS_METADATA package provides a way for you to retrieve metadata
  from the database dictionary as XML or creation DDL and to submit the
  XML to re-create the object.

For details: Oracle Documentation
DBMS_METADATA

SQL> create table fbi_tab(
  2  a number,
  3  b number,
  4  c number
  5  );

Table created.

SQL> create index idx on fbi_tab(a+b*(c-1),a,b);

Index created.

SQL> select dbms_metadata.get_ddl('INDEX','IDX','HR') from dual;

DBMS_METADATA.GET_DDL('INDEX','IDX','HR')
--------------------------------------------------------------------------------

  CREATE INDEX "HR"."IDX" ON "HR"."FBI_TAB" ("A"+"B"*("C"-1), "A", "B")
  PCTF

SQL>


MySQL, optimize and replication I have two MySQL (MariaDB actually) servers, a master and a slave with standard replication.
Two times already in less than a month I've had my slave become unusable.

This is what I experienced:

Notice a drop in query per second on the slave with my monitoring tool
Connect to the server with the mysql client, run SHOW SLAVE STATUS \G
Notice everything looks normal (Waiting for master to send event, seconds behind master = 0, etc)
Try and stop the replication with STOP SLAVE
STOP SLAVE hangs
Impossible to connect to the server with a client
Execute SHOW SLAVE STATUS \G on another already connected client, and it hangs too.

After that, I saw the MySQL process using CPU, and I saw a lot of disk writes with iotop, so I let it do its stuff for a while.

After 15 minutes I killed -9 the MySQL process because I got frustrated. I then restart MySQL, and I can connect and run queries, good.

I execute SHOW SLAVE STATUS \G which now works, and it looks normal: seconds behind master = 0, waiting for events. Then I go on the master and execute CREATE DATABASE foobar to see if the replication works, and it doesn't.

Again, at that point I see a lot of disk writes so I assume the replication is catching up.
Two hours later, I finally decide to check SHOW PROCESSLIST and all I see being executed is a OPTIMIZE TABLE table1 ... and then I remember that I had that exact same problem last time.

I kill the process with that query, then I reexecute SHOW PROCESSLIST and sure enough it's catching up with the master. SHOW SLAVE STAUTS \G reported 15k seconds behind master, which was coherent.

Anyway, after that wall of text, my questions:

why does a OPTIMIZE query block the replication catching up ?
can I prevent those OPTIMIZE queries ? I didn't put any cron to do that, and as far as I can see, nothing in /etc/cron.{d,daily,hourly,weekly} does this.

Thanks.

Your First Question

  why does a OPTIMIZE query block the replication catching up ?

The SQL thread in MySQL Replication processes SQL commands from the Relay Logs as a FIFO queue (i.e. first-come, first-server basis). Once the SQL thread see a command, it processes it to completion. Meanwhile, the Seconds_Behind_Master just keeps creeping up.

While the SQL thread on the Slave is running OPTIMIZE TABLE, once you run STOP SLAVE; it SQL thread has complete the running OPTIMIZE TABLE before the SQL thread can terminate. That's why STOP SLAVE; hangs. To verify this, open another mysql session and run SHOW PROCESSLIST;, You should see a line that has OPTIMIZE TABLE with the status Killing slave....

Your Second Question

  can I prevent those OPTIMIZE queries ? I didn't put any cron to do that, and as far as I can see, nothing in /etc/cron.{d,daily,hourly,weekly} does this.

Someone or something is running the OPTIMIZE TABLE. You have to hunt it down in all your jobs or PHP apps. Use the mysqlbinlog utility to dumps all the binary logs into a text file. Then, grep the text file for OPTIMIZE TABLE or optimize table. The timestamp of when it was submitted into the binlogs will be a few lines about the command.

When you do find that OPTIMIZE TABLE command in your jobs or PHP code, simply run this:

SET SQL_LOG_BIN=0; OPTIMIZE TABLE tblname; SET SQL_LOG_BIN=1;

This will not register the OPTIMIZE TABLE in the binlogs, thus preventing a Slave from replicating it.

As was just pointed out in the comments below, You can also replace

OPTIMIZE TABLE tblname;

with one of the following:

OPTIMIZE NO_WRITE_TO_BINLOG TABLE tblname;
OPTIMIZE LOCAL TABLE tblname;

to restrict the OPTIMIZE TABLE command from landing in the binlogs and thus prevent replication of it.


Increment each row according to condition in SQL C# I have a value of 100 from a textBox and I want to increment that value in the stock_out column but only till stock_out column is equal to stock_in column, if first row value is 40 than first row stock_out should be incremented by 10 from 100 than the remaining value from 100(90) should be incremented in the row below it and then row below it till needed (till 100 is 0). Items are ordered by Item_Name and Bill No asc.

Bill No   Item_Name   Stock_in  Stock_out  stock_in_hand
1          chicken       50        40           10
2          chicken       40         0           40
5          chicken       60        10           50
6          chicken       100       20           80
3          fish          100       30           70
4          fish          20        20            0

The result to be obtained.

Bill No   Item_Name   Stock_in  Stock_out  stock_in_hand
1          chicken       50        50           0
2          chicken       40        40           0
5          chicken       60        60           0
6          chicken       100       20          80
3          fish          100       30          70
4          fish          20        20           0

Here is the code that I have been able to do so far, but it needs a lot of work and I do not have expertise in cte or any complicated c# coding. I am using SQL Server 2014 and c#.

C# code SQL Query

select *
from stock_recieve
where Item_Name like'" + comboBoxitem.Text + "'
where Stock_out < Stock_in
order by [Bill No] asc;

update stock_recieve
     set Stock_out = Stock_out+" + qty + "
where Stock_out < Stock_in;

Any help would be greatly appreciated.

P.S I know about the sql injection and I will change my sql queries to parameterized queries.

The 100 is a example value obtained from user via textbox, how do i implement code to receive value from textbox.

You can use a window function to calculate the rolling sum (of stock_in_hand) in a derived table or a CTE and then use that to update the table accordingly:

WITH upd AS
  ( SELECT
        [Bill No],
        Item_Name,
        Stock_in,
        Stock_out,
        stock_in_hand,
        sum_stock = COALESCE(
            SUM(stock_in_hand) OVER (ORDER BY Item_Name, [Bill No]
                                     ROWS BETWEEN UNBOUNDED PRECEDING
                                              AND 1 PRECEDING)
            , 0)
    FROM
        stock_recieve
  )
UPDATE upd
SET Stock_out = Stock_out
        + CASE WHEN 100 - sum_stock >= stock_in_hand
              THEN stock_in_hand
              ELSE 100 - sum_stock
          END,
    stock_in_hand = stock_in_hand
        - CASE WHEN 100 - sum_stock >= stock_in_hand
              THEN stock_in_hand
              ELSE 100 - sum_stock
          END
WHERE sum_stock < 100 ;


How to count number of occurrences for all different values in database column and obtain 0 if are there empty result? I'am newby with postgresSQL.
I have a table with this structured:

city          | type     | quantity | sellout   | sellout_update
"ACIREALE"    | "Man"    |  "3"     |"1100.0"   |"366.6666666666666667"
"ACIREALE"    | "Unisex" |  "3"     | "1405.0"  |"468.3333333333333333"
"ACIREALE"    | "Woman"  |  "9"     | "2480.0"  |"275.5555555555555556"
"ALESSANDRIA" | "Kid"    |  "1"     |"100.0"    |"100.0000000000000000"
"ALESSANDRIA" | "Man"    |  "13"    | "3500.0"  |"269.2307692307692308"
"ALESSANDRIA" | "Unisex" |  "6"     | "1485.0"  |"247.5000000000000000"
"ALESSANDRIA" | "Woman"  |  "29"    | "8085.0"  | "278.7931034482758621"

I obtain this result from this query:

select lg.city,
        so.type,
        SUM(so.sell_out_quantity)  as quantity,
        SUM(so.estimated_sell_out_value_based_on_srp_euro5) as sellout,
        (SUM(so.estimated_sell_out_value_based_on_srp_euro5)/SUM(so.sell_out_quantity )) as sellout_update
FROM doors As lg INNER JOIN sellout as so ON lg.code = so.code
GROUP BY lg.city,
         so.type

now, i want obtain this result:

city        | N man | N Unisex  | N Woman | N Kid | ...   | sellout
"ACIREALE"    | 3     |  "3"      |   9     | **0**     | ...
"ALESSANDRIA" | 1     |  13       |   6     | 29    | ....

For each row I want count the number of occurrences for all different values and transpose them in a specific column. I think that i need to different subquery and join them. For Example:

...
INNER JOIN (select lg.city,
                 COALESCE(COUNT(so.type),0) as c_kid
                FROM doors As lg INNER JOIN sellout as so ON lg.code =
                so.code
                WHERE so.type = 'Kid'
                GROUP BY lg.city
                         ) As count_kid ON lg.city = count_kid.city
...

It work. (I don't know if exist the a better way to achieve this result)
As it can be see, i use COALESCE function for obtain a 0 if a value doesn't exist but after several attempts I always get an empty line if it doesn't exist. Where am I doing wrong ?

Use the FILTER clause

SELECT city,
       sum(quantity::integer) FILTER (WHERE type = 'Man') AS man,
       ...
FROM doors JOIN ...
GROUP BY doors.city, ...;


Generically get all related records from SQL Server Database based off of Primary Key of one table In Sql Server it is possible to visually see all relations a table has by creating a diagram of one table and right clicking and selecting 'Add Related Tables.'

Is this also possible to do for one record of the table? For example, I have table A and B are related by foreign key ab and I have table B and C are connected by FK bc. With only knowing the fk of one record from A can I get all related ab relations and bc relations generically and without knowing that the FK bc exists ahead of time?

My reason for doing this is we want to be able to pull a singular set of data from our production database to our development database so that we can debug on live data without risking changing live data. Please ask questions if this is not clear.

If the tables have defined foreign keys, you could query the system tables themselves, especially sys.foreign_keys (Documentation here). Here's a good query to start with, written by Stack Overflow user littlesweetseas, from this post :

SELECT
   OBJECT_NAME(f.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM
   sys.foreign_keys AS f
INNER JOIN
   sys.foreign_key_columns AS fc
      ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN
   sys.tables t
      ON t.OBJECT_ID = fc.referenced_object_id
WHERE
   OBJECT_NAME (f.referenced_object_id) = 'YourTableName'

Keep in mind that bringing down data regularly could get more difficult, as you will have to remember the order of tables, and export that data in order every time.

I would suggest finding a place to restore the production database in your test environment. The safest way to get production data into a test environment would be a database restore to your test server. You could either use the latest full backup, or take a copy-only backup of production, and restore that either to replace your test environment's database completely or  to a new database. That would give you all the data and data structures from Prod, but it could also wipe out any changes or on-going tests if you replace your test database.


FOR XML could not serialize the data because it contains character (0x0000) I have a big query (if necessary I will post it here) and I'm getting this error:

  Msg 6841, Level 16, State 1, Line 1
  FOR XML could not serialize the
  data for node 'NoName' because it contains a character (0x0000) which
  is not allowed in XML. To retrieve this data using FOR XML, convert it
  to binary, varbinary or image data type and use the BINARY BASE64
  directive.

The only part I use FOR XML is here:

WHERE
    (CodFuncionario = Results.CodFuncionario)
FOR XML PATH(''), TYPE).value('(./text())[1]',
    'VARCHAR(MAX)'), 1, 2, '') AS [Experiencia]

But, what is node noname? and how can I look for this value: (0x0000)

This is one of the subqueries (the only part I have FOR XML):

SELECT
    [CodFuncionario],
    STUFF
    (
        (
            SELECT
                ' / ' +
            CAST
            (
                [DescFuncao] + '-' +
                [DescTempoExperiencia]
                AS VARCHAR(MAX)
            )...
FROM
    [Linked_Server].db.dbo.tblFuncionarioExperiencia T0
INNER JOIN
    [Linked_Server].db.dbo.tblFuncao T1 On T0.codFuncao = T1.CodFuncao
INNER JOIN
    [Linked_Server].db.dbo.tblTempoExperiencia T2 ON T0.CodTempoExperiencia = T2.CodTempoExperiencia
WHERE
   (CodFuncionario = Results.CodFuncionario)
   FOR XML PATH(''), TYPE).value('(./text())[1]', 'VARCHAR(MAX)'), 1, 2, '') AS [Experiencia]
  FROM
      [Linked_Server].db.dbo.tblFuncionarioExperiencia Results
  GROUP BY
      CodFuncionario) as T2

  On T0.CodFuncionario = T2.CodFuncionario

Left Join...

The line:

...
SELECT
    [CodFuncionario],
    STUFF
    (
        (
            SELECT
                ' / ' +
                CAST
                (
                    [DescFuncao] + '-' +
                    [DescTempoExperiencia]
                    AS VARCHAR(MAX)
                )...

Should be:

...
SELECT
    [CodFuncionario],
    STUFF
    (
        (
            SELECT
                ' / ' +
                CAST
                (
                    replace -- *** NEW! ***
                    (
                        [DescFuncao] + '-' +
                        [DescTempoExperiencia],
                        char(0),
                        ''
                    )
                    AS VARCHAR(MAX)
                )...


Most efficient way to do many UPDATES in Postgresql I have to update cca 2.5 milion rows in a table with cca 20 milion rows.

The updated column value is based on value of another column, but another select is required to determine it's value. The column being updated is also a part of an index.

Currently I generated sql script with one UPDATE statement per row, but I imagine this is not very efficient. What's the most efficient way and are there any post update actions to perform to keep the table performing well? The table is being constantly used (selected/updated/inserted to), I don't want this to cause any downtime. The version of postgresql is 9.1.

EDIT

The updates in the script are of form

UPDATE table1 SET fid=123 WHERE id=345;

Where id column is integer and the primary key of the table.

The schema looks like this:

CREATE TABLE table1 (
    id serial PRIMARY KEY,
    date timestamp,
    rname varchar(50),
    fid integer references table2(id)
);

CREATE INDEX ON table1 (fid, date);

CREATE TABLE table2 (
    id serial PRIMARY KEY,
    rname varchar(50)
);

The fid column was recently added and I need to update some records in table1 so the records are properly linked by record id and not a rname column which causes data redundancy and other problems.

I've written perl script that generates set of update statements to do so based on the current state of the tables. The new inserted records in table have fid properly filled, I need to update the old.

If you don't want to interfere with other activity, the one UPDATE at a time in autocommit mode is very likely  the best option.  You should probably set synchronous_commit=off in that session (and only that session).

The indexes are going to slow you down, perhaps by a lot depending on your RAM and your IO system.  But if the index is necessary for the other actions you don't want to interfere with, then there isn't anything you can do about it.

But since the fid is not yet correctly populated, the index on it is probably not actually useful to the concurrent processes you want avoid interfering with, as they haven't been changed yet to rely on that column being accurate.  If that is the case, you can drop that index to gain speed, and build it in bulk later.  The same probably applies to the foreign key constraint.

Once that index is gone, your updates can proceed via HOT (Heap Only Tuples) updates provided each block has enough free space.  In that case, the updates will not have to do maintenance on the primary key index, either, saving that much more IO.  To maximize the likelihood that this will work optimally, it is important that each UPDATE be its own transaction.  That way one UPDATE can reuse space freed up by an earlier one.

Also, your WHERE clause should probably be like:

WHERE id=345 and fid is not null;

That way if the script gets interrupted, you can re-run it with minimal damage.

Since you seem to be running this on a test system already, then an EXPLAIN (ANALYZE,BUFFERS) of some of the updates would be helpful, especially with track_io_timing set to on.


Disk write in iotop when doing mysql "Copy to tmp table" I have just moved servers from a two-server configuration with the application and database server running separately to a one-server LAMP stack.

Unfortunately the issue that I have encountered on the new server is, when I run a query that requires copying the table to a tmp file "Mysql Status: Copying to tmp table" from what I can see in iotop, the mysql process writes up to approximately 100mb and then slows to a crawl until the file completes at about 400mb (this can take up to 20mins)

On the old server, we were able to write the 400mb to the tmp table within 20 seconds. Is there some setting in MySQL that regulates how fast it can write to the file system?

If your new instance of MySQL is creating temporary tables with the InnoDB storage engine, the innodb_io_capacity could be throttling the rate at which it writes pages to disk.

However, even throttled, I would expect a mere 400MB to be pretty quick to write to disk. Have you considered the possibility that the disk drives on your new server are simply faulty?

I would recommend running some benchmarks with sysbench or iozone to see if you can reproduce the slow I/O speed without MySQL.

Re your comment:

The best figures to look at for a database server are random read, random write, and mixed workload. Run a similar benchmark on your old database server, and compare them. If your new database server has significantly lower numbers, that would explain the difference you're seeing.


Set-up MySQL Server on Windows 7 64-bit I am trying to install MySQL Server on my Windows-7 64-bit machine. I have downloaded MySQL Server Community Edition and MySQL Workbench. I am trying to start a new Server Instance from Workbench. I clicked on New Server Instance -> localhost, but I am getting the following error

I have been trying this for the last 6 hours. Any pointer will be great help.

It looks like you are creating a New Server Instance Profile, not a New Server Instance.

If you installed MySQL already, then go to the DOS Prompt and run

C:\> net start mysql

or

Click Start
Click Control Panel
Click Administrative Tools
Double Click Services
Right Click on MySQL and Choose Start Service

To verify that MySQL is running on the Windows 7 machine,

Right Click on Bottom Task Bar and Click Start Task Manager
Click on Processes Tab
Scroll around and Look for the process mysqld

Once you have MySQL running on the Windows machine, you can go to to create a Server Instance Profile. The key here is to install MySQL first.


MongoDB Cluster - Can't access member of shard with credentials I have a Sharded Cluster and I am trying to access a specific member of a replica set within a shard.

When connecting to the mongos it is fine, but when using the same credentials on a member of the replica set I am unable to gain access even though my user has the role of root.

The error is:

  SCRAM-SHA-1 authentication failed for root on admin from client
  127.0.0.1:39356 ; UserNotFound: Could not find user root@admin

The mongos are started with:

mongos --configdb cfg/mongo-cfg0:27019,mongo-cfg1:27019,mongo-cfg2:27019 --bind_ip_all --keyFile "/etc/mongodb-keyfile"

The members of the replica set are started with:

mongod --auth --bind_ip_all --shardsvr --replSet a --smallfiles --oplogSize 50  --keyFile "/etc/mongodb-keyfile"

When connecting to the mongos I use the following locally:

mongo --port 27017 -u user -p password --authenticationDatabase admin

When connecting to the memnber of the replica set locally i try and use:

mongo --port 27018 -u user -p password --authenticationDatabase admin

I feel like there is something obvious that I am missing but I can not figure out what, any help would be greatly appreciated.

When you are using mongoS authentication is done against config servers admin database. When you connect directly to replica set, you are authenticating against replica set's admin database, where you don't have that root user set.

What can you do? Start replica set's (one by one) without --auth parameter (or equivalent config parameter) to maintenance mode, connect that RS with mongo command, create that root user (and other needed users). After that you can restart RS with authentication and you can do login.


What to do with those invalid JAVA CLASS object type objects in SYS prior to the upgrade the oracle db from 10g to 11g? I am ready to upgrade the oracle 10g prod db to 11g, but we have 130 invalid objects in SYS (all are belong to JAVA CLASS object_type). And from the best practice of oracle upgrade from 10g to 11g, it said should not have any invalid objects in sys or system schema. But I already ran the utlrp.sql to recompile all invalid objects script multiple times, those invalid JAVA CLASS object_type objects are persistent in SYS user. And I tried to manually recompile each of those object as doing following,

  alter java class "java class object name" compile;
  but got error:
  ORA-29521: referenced name balabala could not be found

You have any advice for either how to fix this error or can we go ahead to upgrade the db with those 130 invalid objects in the sys schema safely? Thanks!

From this blog you can try this on development first...

SQL> spool resolve.sql;

SQL> select ‘alter java class “‘||object_name||’” resolve;’
  2  from user_objects
  3  where object_type like ‘%JAVA%’;

SQL> spool off;

SQL> @resolve

The clean sweep approach would be to remove and reinstall the java JVM

The Java VM is created and populated with system classes during CREATE OR REPLACE JAVA SYSTEM command.  Run rmjvm.sql to remove the JVM first, then bounce the database then initjvm.sql

I repeat, these should be tried on your development stack first!


Availability Groups and Transactional Replication My client has a setup as shown below.

AG Group A --------->       Server A (DB--> A) ----> Server B ( DB--> A)
Transactional Rep -->       Server A ( DB-->A ) ---> Server C ( DB--> B)
AG Group B --------->      Server C ( DB --> B)  ---> Server D ( DB --> B)

where  Server A -> Database A  is configured for Transactional replication to Server C -> Database B.
The Availabilty Group and replication are configured fine and the publisher is redirected to the Availability Group Listener.
The distributor database is hosted on Server C ( subscriber).
To top it all the subscriber is part of another AG group so it has its own secondary AG replica in Server D (Database B).
I know in this situation transaction replication is not required at all as we could open secondary replica for Read within Availability Groups. ( client - disagrees to change).
Question is as follows:
If The Subscriber ( server C) crashes along with the distribution database,  what options are available to redirect replication to a new distributor on replica server D ? Assuming both AG are synchronous and can be failed over together.
Your help and advise is much appreciated.

Distribution database should not reside on the servers that are part of AlwaysON availability group that the publishing database is (or will become) a member of.

  Replication configuration is coupled to the SQL Server instance where the Distributor is configured; therefore the distribution database cannot be mirrored or replicated.

If you want to provide HA for distribution database, then you have to go for SQL Server Failover cluster. Thats the only option.

Your scenario is as below :

So if you loose server C, the only option to get distribution running on server D is to do a RESTORE.. with KEEP_REPLICATION from a good backup. You can use this script to restore your distribution database (with some changes as per your environment). I would go for a clean install of replication !

Make sure you script out your replication topology whenever your do any changes. You should have handy scripts of both drop and create, so in a disaster situation, you have scripts that will help easily create replication.

Also, since you are using always-ON with T-Rep, I would suggest you to enable TF 1448.

  Trace flag 1448 enables the replication log reader to move forward even if the asynchronous secondary replicas have not acknowledged the reception of a change. Even with this trace flag enabled,, the log reader always waits for the synchronous secondary replicas. The log reader will not go beyond the min ack of the synchronous secondary replicas. This trace flag applies to the instance of SQL Server, not just to an availability group, an availability database, or a log reader instance. This trace flag takes effect immediately without a restart. It can be activated ahead of time or when an asynchronous secondary replica fails.

Reference : Configure Replication for AlwaysOn Availability Groups


MariaDB Galera setup with drastically different amounts of memory used on cluster nodes - why? We have a 3 node MariaDB Galera cluster.

All nodes are identical and the config on each is identical. They have 64GB of RAM each.

The output of the following query is 28:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

Our database is growing quite quickly and the machines are dedicated so innodb_buffer_pool_size is now set to 56G. Originally it was set to 256M, we just increased it this morning (the cluster was only set up a couple of days ago).

My question is why is the amount of RAM used on each machine so drasticly different, see the graph below. When we changed innodb_buffer_pool_size to 56G (and did a rolling-restart) they all jumped up by around the same amount (in real terms, not proportionally), but as you can see are clearly still quite different. Can anybody shed some light on why?

Thanks!

The InnoDB buffer pool caches queries; if you have less usage on one of the nodes then you're going to have less cache used.

If you're not properly load-balancing queries then the amount of cache in use on each node would be different. If you just turned on a node and added it to the cluster the amount of cache would be different than the others one that have been long running.

It doesn't matter if you use Galera in this case as this is a specific InnoDB question; sure you could be using Galera, but it has nothing to do with this caching question you've asked.


SQL Server Management Studio 2014 12.0.5000.0 "Invalid Column Name" Error The following code executes as expected.  However, the editor shows red lines under attribute5 and WCC_ID in "h.attribute5 = i.WCC_ID" and states "Invalid column name" when the mouse hovers over the red lines.  These columns exist in the respective tables.

What is wrong with the SQL?

SELECT
*
FROM
(
SELECT attribute5
FROM
OPENQUERY([FAMISPRO.WORLD],'SELECT attribute5 FROM FAMIS_EMP')
) AS h
INNER JOIN
(
SELECT DISTINCT h.WCC_ID, h.KEYMARK
FROM
OPENQUERY ( PROJECT ,'SELECT * FROM ENTRY;' ) AS h
LEFT JOIN
(
SELECT *
FROM
OPENQUERY ( PROJECT ,'SELECT * FROM ENTRY;' )
WHERE
EVENT = 'RETURNED'
) AS i
ON
h.WCC_ID = i.WCC_ID AND h.KEYMARK = i.KEYMARK
WHERE
i.EVENT IS NULL
) AS i
ON
h.attribute5 = i.WCC_ID;

If your database [FAMISPRO.WORLD] is a linked server then you'll always get red line. SQL Server doesn't hold meta data about linked servers so there is no way to verify if a column exists in a table or not and this results in a red line under it.

When you navigate to tables in linked server database, you won't see little + sign in front of it as you see in your normal database tables because management studio only allowed to access tables and views from linked server database.

However if you want to verify column exist or not use below query.

Select * from [FAMISPRO].[WORLD].INFORMATION_SCHEMA.COLUMNS
where table_name like 'FAMIS_EMP'

You can also use some sys tables too in linked server.


Similar function NOWAIT in MySQL Select for update nowait doesn't work in MySQL 5x (innodb). Is there any function like NOWAIT in MySQL?

MySQL provides the ability to set application-specific locking using the GET_LOCK() function. You can check if the mutex has been taken by using the IS_FREE_LOCK() function.

You can refer this thread for detail:
http://forums.mysql.com/read.php?22,45679,45679#msg-45679

Also,
Non-blocking queries are not supported by MySQL:

http://lists.mysql.com/mysql/219041


Partial local index on partitioned Oracle table We have a big transactional table, partitioned on transaction date (day). Each day, several million records are added.
We need to do selects on this table, over a period of the last five days, and including the accountid.

In order to keep things a bit performant, I suggested to partition the table on day level (done), and create a local index (prefixed or non-prefixed) on accountid.
Now, one of the teammembers says it will not be possible, since they need to create an index on only the last five partitions of the table, and that would require too much effort.

AFAIK, the creation of the index needs to be done only once, and then daily, the index on the new partition only needs to be created. Besides that, indexes on partitions older than 5 days will never be used anymore, so there's nothing to do there.

What are your ideas on this? How to best solve this issue, with performance in mind?

You can create indexes only for certain partitions - of course this is possible only for LOCAL indexes.

The problem is you would have to set DEFAULT ATTRIBUTES INDEXING ON in order to get index created on new partitions. Index partitions older than 5 days you would have to delete (i.e. ALTER TABLE ... MODIFY PARTITION ... UNUSABLE LOCAL INDEXES; or ALTER INDEX ... MODIFY PARTITION ... UNUSABLE;) manually.

Of course you can set DEFAULT ATTRIBUTES INDEXING OFF but then you have to create (i.e. rebuild) the new index partition every day - which should be the same amount of effort.

It is no big deal to run such task (either of mentioned above) in PL/SQL procedure which is executed daily by a scheduler job in the database.

Inital task

-- Turn off Indexing for existing partitions
BEGIN
    FOR aPart IN (SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TABLE1') LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE TABLE1 MODIFY PARTITION '||aPart.PARTITION_NAME||' INDEXING OFF';
    END LOOP;
END;
/

-- Create the index
CREATE INDEX TABLE1 ON idx (column1, column2) LOCAL INDEXING PARTIAL;

-- Enable indexing, otherwise new partitions will not get indexed.
ALTER TABLE TABLE1 MODIFY DEFAULT ATTRIBUTES INDEXING ON;

-- Rebuild index on most recent partitions
DECLARE
   ts TIMESTAMP;
BEGIN
    FOR aPart IN (SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TABLE1') LOOP
        EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT ts;
        IF ts > SYSTIMESTAMP - INTERVAL '5' DAY THEN
           EXECUTE IMMEDIATE 'ALTER INDEX idx REBUILD PARTITION '||aPart .PARTITION_NAME;
        END IF;
    END LOOP;
END;
/

Daily task

DECLARE
   ts TIMESTAMP;
BEGIN
    FOR aPart IN (SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TABLE1') LOOP
        EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT ts;
        IF ts < SYSTIMESTAMP - INTERVAL '5' DAY THEN
           EXECUTE IMMEDIATE 'ALTER TABLE TABLE1 MODIFY PARTITION '||aPart .PARTITION_NAME||' UNUSABLE LOCAL INDEXES';
        END IF;
    END LOOP;
END;
/

However if you don't query data older than 5 days then this approach does not have any effect on performance, you will just save some disc space.


How to Reorganize Microsoft Access Table I have just taken over a retiring DBA's position and is responsible for migrating the current access database to SQL Server  or PostgreSQL. Right now, the table structure is a mess. Instead of properly establishing relationships between tables, multiple fields and parameters are cramped in a single table.

I have broken up the table and plan to regroup the parameters into separate individual tables and link them together. My question is, the unorganized tables contain many rows of data, with over 40 different parameters, what is the best way to break up the table and still have all the data repopulated in the new tables linked?

I think your best bet is to do the restructuring in Access. After you're confident everything works, you can

create tables and constraints in PostgreSQL,
export tables from Access as CSV files, and
load the CSV files into PostgreSQL.

Everything works includes application code.

Make sure your Access database is backed up, copied, and not in use before you make structural changes.

To restructure tables in Access,

select distinct rows into a new table ("make table" query), or
create views (saved queries) that select the right rows and columns, or
copy a table, open it in design view, and delete irrelevant columns. (You'll probably want to delete some columns from the original table, too.)

I'm pretty sure Access lets you export the results of a query, so you can build queries that have the right structure, then export the queries.


Remove single quotes from db2 column name I have following query which i am trying to execute against db2 database server

SELECT REPLACE('\'abc\'','\'','') FROM TN_DAY_PROCESS

I am expecting the output like abc without single quotes

from the abc text, I want to remove single quote. to do that, I tried the method shown above using REPLACE function in db2, but it is giving me following error

The character "\" following "ELECT REPLACE('\'abc" is not valid.. SQLCODE=-7, SQLSTATE=42601, DRIVER=3.66.46 SQL Code: -7, SQL State: 42601

Could somebody help me to figure out the solution to remove the single quotes from a string in db2 database.Thanks in advance for any help

While searching for an answer for this question, I found following method

SELECT REPLACE('''abc''','''',SPACE(0)) FROM TN_DAY_PROCESS

so, the way to specify single quote is to write it twice

As you found, you have to use '' to represent a single quote (').

You do not need to use the SPACE() function:

select replace(character_column, '''', '')
  from your_table

Note: Using SPACE() could actually create an issue as it returns VARCHAR(4000).


I can't add extra node after reset root password mariadb 10.1.20 I have a mariadb 10.1.20 cluster with 5 nodes.

I don't know root password so I did the following steps:

On second node, I stopped mysql service
I set wsrep = OFF on /etc/my.cnf.d/server.cnf
I executed instructions to reset root password, now password root is empty.
I started mysql service and I logged as root and it works well until now.

But when I set wsrep = ON and then restarted mysql service I got the following error using systemctl status mariadb.service | more:

mariadb.service - MariaDB database server
  Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled)
 Drop-In: /etc/systemd/system/mariadb.service.d
       ââmigrated-from-my.cnf-settings.conf
  Active: failed (Result: exit-code) since Tue 2018-02-27 16:18:29 PET; 7s ago
 Process: 27727 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Process: 2091 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
 Process: 1695 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
 Process: 1693 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 2091 (code=exited, status=1/FAILURE)
   Status: "MariaDB server is down"

Feb 27 16:18:24 cluster-test-node-2.reniec.gob.pe systemd[1]: Starting MariaDB database server...
Feb 27 16:18:25 cluster-test-node-2.reniec.gob.pe sh[1695]: 2018-02-27 16:18:25
139937440761984 [Note] /usr/sbin/mysqld (mysqld 10.1.18-MariaDB) starting as process 2050 ...
Feb 27 16:18:27 cluster-test-node-2.reniec.gob.pe sh[1695]: WSREP: Recovered position bbbe7e3b-4e00-11e6-bb1e-4e7ce2820fe5:8300414
Feb 27 16:18:28 cluster-test-node-2.reniec.gob.pe mysqld[2091]: 2018-02-27 16:18:28 140112964700288 [Note] /usr/sbin/mysqld (mysqld 10.1.18-MariaDB) starting as process 2091 ...
Feb 27 16:18:29 cluster-test-node-2.reniec.gob.pe systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Feb 27 16:18:29 cluster-test-node-2.reniec.gob.pe systemd[1]: Failed to start MariaDB database server.
Feb 27 16:18:29 cluster-test-node-2.reniec.gob.pe systemd[1]: Unit mariadb.service entered failed state.

Mysql service on second node turns on when wsrep is OFF.

What is missing in this case?

I solved by giving permissions (read/write) to grastate.dat


What type of data ingestion does Azure Data Factory use? This article data-ingestion says that there are two types of data ingestion

  Data can be streamed in real time or ingested in batches.

What type of data ingestion does Azure Data Factory's pipelines use when moving data from On-Premises/Cloud sources services to Azure Services (Azure Blob Storage, Azure Data Lake Store, etc)

While going through the documentation I found the following:

  ADF provides the services and tooling to compose and integrate data,
  build data pipelines, and monitor their status in real time

Does that mean that Data is streamed in real time?

According to the following picture found in the documentation, it has a Batch Ingest

You could also use triggers to move data incrementally. But it is not real time. There is frequency limitation.


copy data from public schema to another schema in postgresql I'm creating a multi-tenant RoR app using PostgreSQL 9.3.5 running on linux (Ubuntu 14). I'd like to copy the data from my public schema to another schema.

I tried:

pg_dump -U my_username -h localhost --schema=public -d my_db -Fc -f db/my_dump.backup

pg_restore -U my_username -h localhost --schema=my_schema -d my_db -a db/my_dump.backup

It does not copy the data from the public schema. Any thoughts?

You're setting custom format.

Remove the -Fc option from the pg_dump command so the dump will be generated with COPY commands.

Add set schema 'myschema'; at the beggining of the file.

then

psql -U my_username -h localhost -d my_db -f db/my_dump.backup -v ON_ERROR_STOP=1