texts:
I've been trying to resolve an issue which I think may be helped by changing the wait_timeout variable in MySQL (5.1.67) on my CentOS machine.

So, I updated /etc/my.cnf with the value I want (180), and restarted MySQL, but now I'm surprised to find that I'm getting different values for wait_timeout depending on how I display it (see below):

mysql> show global variables like "%wait%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
| table_lock_wait_timeout  | 50    |
| wait_timeout             | 180   |
+--------------------------+-------+
3 rows in set (0.00 sec)

mysql> show variables like "%wait%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
| table_lock_wait_timeout  | 50    |
| wait_timeout             | 28800 |
+--------------------------+-------+
3 rows in set (0.00 sec)

I'm guessing that the second query is showing session variables, as opposed to global variables, but I don't understand why they would be different, after having exited my MySQL session, restarted MySQL, and then logged back in again. How come they're not the same? And, I guess, how to I ensure that the same value (180) is consistent across everything?

Any suggestions/help appreciated. Thanks.


texts: Effects of monolothic databases on performance and security?

texts:
Please note: although I'm specifically calling out MySQL here, I would also appreciate/entertain generic answers that apply to all/most relational systems.

As a developer I don't see the design benefits of grouping tables into different databases, especially if all the tables are somehow connected/related to each other. From a performance perspective, to me (a dumb developer), why should it matter if I have 100 tables located inside a single database, or 10 databases with 10 tables in them?

So I ask: Are there performance/security/other benefits to decomposing a monolithic database into smaller ones? What are they?

Also, what if I have hundreds of tables, and they are literally all somehow linked? Meaning, there are no completely isolated tables in the entire system; all of them have foreign keys in them to some other table. Doesn't this mean I have to have only 1 big/monolithic DB?


texts: A tool to convert PostgreSQL database dumps between versions?

texts:
On different computers/servers we have different versions of PostgreSQL installed and can't upgrade all of them yet.

Are there any tools which would allow to convert a database dump from the newer PostgreSQL version to an older one?


texts: glogin.sql supress error when Instance Idle

texts: I have a pl/sql block in my glogin.sql file to show information from v$instance when I connect on the database server.  Is there a way to suppress the ORA-01034 we would naturally get when the instance is idle?  An idle instance reports that it is idle when you connect, so anything additional is not necessary and could be confusing.


texts: Query over text column takes ages to run

texts:
I'm not used to querying text columns with a large amount of data as I've always used NVARCHAR(MAX) and VARCHAR(MAX) but this is not my Database that I'm querying.

myTable has 305,000 rows.

myDescriptionField is a text column and allows nulls & has loads of data in it.

When I run the following query, it returns a row instantly.

SELECT TOP 1 userid
FROM myTable
WHERE ISNULL(userID,0) > 0
AND TRIM(SUBSTRING(myDescriptionField,1,1)) is not null

However, when I add this line to the where clause, the query just sits for over a minute (not sure how long it actually takes to finish as I always end up stopping the execution).

AND myDescriptionField NOT LIKE ''

I thought it might be because I'm forced to do a not like as it's a text column and also that it may be where a lot of rows have blanks or null so I tried different combinations of the following :-

AND ISNULL(TRIM(SUBSTRING(myDescriptionField,1,1)),'') <> ''

None of this worked.

So now a quick explanation as to what I need to achieve :-

I just need to find out if at least one row has data in the column myDescriptionField and if so, I can move on to the next table.

Is there a quick way of returning just one row once it's hit one?


texts:
EXEC xp_cmdshell 'bcp "SELECT lastconnectip FROM RF_USER.dbo.tbl_UserAccount" queryout "C:\test.txt" -T -c'

The command above runs, the output:

NULL
Starting copy...
1000 rows successfully bulk-copied to host-file. Total received: 1000
NULL
1158 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1      Average : (1158000.00 rows per sec.)
NULL

But no file is actually written. what am i missing?  The user does have full rights (sysadmin) SQL 2008 R2.

Also alternative way to use NETSH in sql query/trigger would work aswell instead of the one  above.


texts:
I am trying to design a database management system with my PHP and intermediate MySQL knowledge, keeping normalization in mind.

These are the assumptions:

The school has many students from grade 1 to grade 6.
Each grade is divided into classes (e.g. we have grade 1 A, grade 1 B, grade 1 C) due to the class size.
Grade 1 to grade 3 offer the same subjects, but from grade 4 to grade 6 users can select which courses to offer.
Many teachers teach ONLY one subject BUT can teach MORE THAN one grade.

As an intermediate programmer, it all got twisted.
I was able to create the following tables and columns

students{(id),(name),(name),(parent_id)} -student details

parents{(id),(name),(email),(phone),(address)} - parent details

teachers{(id),(name),(email),(subject_id)} - teachers details

subjects{(id),(name),(description)} - subject details

attendance{(date),(status),(student_id)} - attendance of student with id of student_id,
                                           status in boolean TRUE = present, FALSE=absent

This is how far I have gone.

Now I want to show a table where a student can be linked to all the subjects he/she is taking and a teacher linked to all the grades (no need to specify classes) he is teaching. How can I do that?

I will appreciate it if I can be pointed in the right direction.


texts: Stored procedure to change string to datetime in MySQL

texts:
This is a follow up to this question. Turns out the driver that I am using (can't change it) is not compatible with timestamp values. Lets say it is set in stone that I have to load the data into my MySQL 8.0 database as a string type. There is no native function within the software I am using to read in the PLC and write into the db that can change a string to a timestamp.
What I'm trying to figure out now, is how to set up a trigger that converts the string to a timestamp on insert. This idea was suggested to me by the help hotline of the software, but if you have any better ideas, I'm open to that too. Thus far, I have 2 columns: one where I "receive" the string which is of type VARCHAR(30), and then another one where I write the timestamp value with the trigger.
This setup feels super stupid to me because I end up with 2 columns with the same data just in different formats, and one of them is completely useless.
Thus far, my table looks like:
CREATE TABLE `test` (
  `id` mediumint NOT NULL AUTO_INCREMENT,
  `Date_VARCHAR` varchar(30) DEFAULT NULL,
  `Date_TIMESTAMP` timestamp(6) NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=52383 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

And the trigger is:
DELIMITER $$
CREATE DEFINER = CURRENT_USER TRIGGER `db_test`.`test_BEFORE_INSERT`
    BEFORE INSERT ON `db_test`.`test` FOR EACH ROW
    BEGIN
    SET NEW.Date_TIMESTAMP = NEW.Date_VARCHAR ;
    END$$
DELIMITER ;

Is there a better way of doing this to avoid having the same data twice? I would like to make a trigger that just converts the data and writes it all in one column instead of the setup I have come up with.
Here's a quick look at what I have going on . I have done an insert of some random values, but it's actually all being done with the datalogger plugin of KEPServerEx.


texts: MySQL 5.5 : (InnoDB) - Manually edited an .ibd file to test recovery, now entire MySQL installation is broken!

texts:
So I wanted to test MySQL recovery from Innodb corruption.
file-per-table is enabled.
I have a few test db's on the MySQL instance.
I shutdown mysql.  Manually edited/added random characters to an .idb file within a DB.
... I wanted to test credibility of innodbchecksum, mysqlcheck, etc.

MySQL started but shut down right away with errors.
Only innodb-force-recovery=6 seemed to keep MySQL up.

I couldn't dump the corrupted DB at all.
I couldn't select the table. if I tried MySQL crashed again.
I also couldn't drop the table or database even.
error.log mentioned something about not being able to remove the folder.

I manually did an rm on the entire db folder.  edit: I understand it may be impossible to get the data back after wrecking the .idb file, I am only interested in bringing MySQL back to normal status.

MySQL seemed to have started but with errors everywhere in the log saying:

121122 17:34:01  InnoDB: Error: page 7 log sequence number 1 2040399532
InnoDB: is in the future! Current system log sequence number 0 8204.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files...

So my question is, how can this MySQL instance be recovered?
If an .idb file is corrupted due to disk error or such, is the entire MySQL instance hosed?
is there any recovery from this?

Edit: there must be a way of forcing drop a corrupted DB or even table without breaking the tablespace.


texts: Is pg_trigger_depth() bad to use for preventing trigger cascading (recursion)?

texts:
Why is pg_trigger_depth() = 0 bad to use (for anything other than debugging)  when preventing trigger cascading (recursion)?

Can someone provide code to demonstrate why it is bad?

I am guessing because if multiple triggers are working on the same data at the same time a condition that stops a trigger using pg_trigger_depth() = 0 will stop any trigger that is second in line to do work.

I thought it would be a good solution to this (my) question here, but I am told otherwise:

Within a trigger is there a way to tell if an update or insert came from a trigger or not?

Thought it would make a good question.

It is offered here as a solution:

Prevent recursive trigger in PostgreSQL

Postgres 9.3 documentation:

https://www.postgresql.org/docs/9.3/static/functions-info.html


texts:
I'm trying my best not to ask a leading question, but after a lot of searching I'm unable to find a similar answer or recommendation.

First, take a look at this question I asked on Stack Overflow last week: Why does inserting a row with a foreign key referencing a row by pk modified in another snapshot isolation transaction cause the transaction to hang?

In our database we have 4 tables. Two has a foreign key to One's primary clustered key. Four has a foreign key to Three via a surrogate key. In all cases, the keys are meaningless.

Imagine two scenarios:

In a snapshot transaction where a column is modified in One, another snapshot transaction begins where a row is simply inserted into Two, where the foreign key column has the value of the row being modified in One. The latter transaction will block until the former transaction is committed, and then the latter transaction will abort with a snapshot isolation error.

In a very similar situation, a snapshot transaction begins where a column is modified in Three, another snapshot transaction begins where a row is simply inserted into Four, where the foreign key column has the value of the row being modified in Three (via its surrogate key). The latter transaction will finish without issue before the former transaction is committed.

Despite the answer being rather simple, this is unintuitive to me. I almost want to consider this a bug in SQL Server. The first scenario's transaction doesn't have a legitimate reason to block, and it especially doesn't have a legitimate reason to roll back the transaction. It does so basically because of an implementation detail. But if you just use a surrogate foreign key, everything works as expected and nothing is blocked or conflicts.

Is it best practice to use surrogate keys when creating foreign key constraints in SQL Server?


texts: why is granting ALL permissions and privileges to a new role/user for a new database difficult with postgresql?

texts:
With mysql and am sure countless other database softwares, it is pretty easy to create a new database, create a new user and grant that user to own and be able to do everything on that database
this is NOT the case with postgresql
Every time i am about to create a new database and user on postgresql it is a such a pain to think about because i KNOW i will come across head ache again
Why is postgresql such a pain with uses and database and allowing a user to own a database so the user/role has all permissions
It is crazy to always be getting errors like
permission denied for table sites

How can one create a new database, new user with password and grant ownership of the new database to that new user so the user/role can do EVERY and ANYTHING on that new database? what commands to achieve all these? for postgresql?
Thanks!


texts:
pgAdmin4 has introduced 'alerts' or 'toasts' in the UI that display the status of certain tasks.

There seems to be no way to dismiss these alerts, which obscure other parts of the UI, without first clicking on the 'Click here for details' link.  This opens yet another window, which you then can close to dismiss the alert.

I've searched the docs for any mentioned of 'alert', 'notification', 'toast', etc with no luck.  I would like to know if there is a way to hide multiple alerts at once, or change their default behavior, for example to get them to disappear after a few seconds (like an Android 'toast').


texts: Are snapshots generated for each sub query inside a Postgres READ_COMMITTED transaction?

texts: From what I understand - in a READ_COMMITTED Postgres transaction “the transaction obtains a snapshot whenever an SQL command is executed” source. Does this mean a snapshot will be obtained for each sub query in a nested query? Does the use of CTEs in place of sub queries affect this behavior?


texts: How to import data from a pair of .mdf/.ldf files into a MySQL database

texts: I have two Microsoft SQL Server database files named "xxx.mdf" & "xxx.ldf" and I want to import that database into MySQL so I can run queries against it using phpMyAdmin. How would I go about doing this?


texts: SQL Server auto-trim of varchar value in equal comparison but not like comparison

texts:
I came across some interesting behaviour on SQL Server (observed in 2005 and 2012) today that I was hoping someone could explain.

A query doing a comparison using = on an NVARCHAR field ignored the trailing space in the string (or auto-trimmed the value before comparison) but the same query using the like operator did not ignore the space. Collation being used is Latin1_General_CI_AS in 2012.

Consider this SQL Fiddle: http://sqlfiddle.com/#!6/72262/4

Note that the like operator does not return a result for the trailing space string, but the = operator does. Why is this?

Bonus points: I am unable to replicate this on a VARCHAR field, I would have thought that a space would be handled in the same way in both data types - is this true?


texts: What is the fastest way to copy large postgres(>1TB) database?

texts:
Original Problem Statement:
Create a test DB with the latest production database(RDS Postgres)greater than 1.5 TB quickly.
We have 1 production database running in a single instance and we have 8 dev/test databases running in another instance.  The snapshot copy will create a new instance and DB but I am looking to copy only the DB from production and restore it to multiple DB's on another instance. I need to refresh dev/test DB's frequently with production data.
Solution:

pg_dump the production database and restore it to a new test database. This takes more than 6 hours.

Identify the delta from the tables and export only the data or tables that have changes.
There are a lot of tables and I am in process of identifying the tables with changes. But again do not know how much time this takes.

A crazy thought and not a solution - Create a read replica outside RDS and use forking to copy the changes.  The main idea of forking is from this https://www.howtoforge.com/tutorial/how-to-set-up-master-slave-replication-for-postgresql-96-on-ubuntu-1604/
I am not sure if this is even possible as AWS RDS masks a lot of their configurations.

How do I achieve a copy faster.?
I am not sure if StackOverflow is the right place to ask this question.


texts: How to remove duplicates and keep the one with the most additional information

texts:
I have a postgres table (postgres version 9.3.4) of addresses with duplicate addresses:

          Column           |            Type             |                        Modifiers
---------------------------+-----------------------------+---------------------------------------------------------
id                        | integer                     | not null default nextval('properties_id_seq'::regclass)
street_number             | character varying(255)      |
street_name               | character varying(255)      |
street_direction          | character varying(255)      |
unit_number               | character varying(255)      |
city_id                   | integer                     |
postal_code               | character varying(255)      |
year_built                | integer                     |
floor_area                | integer                     |
bedroom                   | integer                     |
bathroom                  | numeric                     |
lot_size                  | integer                     |
den                       | integer                     |

The duplicates are based on the addresses containing of street_number, street_name, street_direction, unit_number and city_id. The table has additional information such as # of beds and # of baths etc. This additional information is not populated for all rows of the table, which makes removing the duplicates tricky as I do not want to lose any information. I would like to mark the duplicates with the least amount of information for deletion (add a field with 1 for delete and 0 for keep). I can see 2 ways of doing this:

Duplicate the information of the entry with the complete information to the other entries and randomly mark all but one entry for deletion
Identify the row with the most information and mark the other rows for deletion.

Ideally, I would like to just identify the rows with the most information and then delete the other rows. Is there anybody who could help me with a query for this?


texts: How to get the data from same table by different conditions in SQL?

texts:
I have below data in my ColorAssign table;

| PKID | Name    | Type | HasAssigned |
| 11   | Red     | 1    | True        |
| 12   | Blue    | 2    | False       |
| 13   | Yellow  | 3    | False       |
| 14   | Red1    | 1    | False       |
| 15   | Yellow1 | 3    | True        |
| 16   | Blue1   | 2    | False       |

Note in this above table I have color types in type field and assign status in HasAssigned field also with different primary keys with different names (can be same name - but type is the identifier to find the actual color).

My goal is to get the data as the condition:-

Find the records with the color type with Hasassigned = true or if no records for the type has true. Then type with Hasassigned = false of color type. So my resultset would be;

| 11   | Red     | 1    | True        |
| 12   | Blue    | 2    | False       |
| 15   | Yellow1 | 3    | True        |
| 16   | Blue1   | 2    | False       |

What are all the ways we can do this, with a focus on optimization?


texts: Storing many small - and a few large - strings in an RDBMS

texts:
Let's say I have a table, and (among other things) it needs to store a large number of strings. Most will be small (20-100 characters), but a few will be quite large. Reads will be frequent, writes will be rare.

What is a good general design for this type of table, using a traditional relational database?

Here are some of the things I have been thinking about:

A VARCHAR (let's say VARCHAR(200) and a CLOB field - One will be null, the other will contain data, depending on the size of the value
Keep a VARCHAR column in the main table but store CLOBs in a separate table - Then I can do one of:

OUTER JOIN the tables
UNION the tables
Use two separate queries to bring back data from each table

Keep all of the metadata in the main table, add a type field and store all text data in its own tables Again, this depends on an OUTER JOIN, UNION or multiple queries

The queries to return data from the table are fairly complicated, and I'm concerned that any solution involving multiple queries (or UNIONs) will be expensive to execute - but more importantly - complicated to maintain (i.e. two versions of almost identical queries).

Thoughts?


texts:
I'm using MySQL5.5 and for this question I have 3 simple tables t1, t2 & t3 designed like this:

+------------+   +------------+    +------------+
| t1         |   | t2         |    | t3         |
+------------+   +------------+    +------------+
| t1_id (PK) |   | t2_id (PK) |    | t3_id (PK) |
+------------+   | some_value |    | t1_id (FK) |
                 +------------+    | t2_id (FK) |
                                   +------------+

PK = primary key, FK = foreign key, and all fields are of type INT

The problem I have is that I want to add a constraint to table t3 that involves the field t2.some_value. Basically I want the combination of t1.t1_id and t2.some_value to be unique within table t3. For example here is a situation I want to avoid:

  t1           t2                        t3
+-------+    +-------+------------+    +-------+-------+-------+
| t1_id |    | t2_id | some_value |    | t3_id | t1_id | t2_id |
+-------+    +-------+------------+    +-------+-------+-------+
| 10    |    | 20    | 1          |    | 30    | 10    | 20    | <-- t2.some_value = 1
| 11    |    | 21    | 2          |    | 31    | 10    | 21    |
+-------+    | 22    | 3          |    | 32    | 10    | 22    |
             | 23    | 1          |    | 33    | 10    | 23    | <-- t2.some_value = 1
             +-------+------------+    | 34    | 11    | 23    |
                                       +-------+-------+-------+

The problem here is that two records in t3 link the same t1 record (t1_id=10) to two different t2 records that have the same some_value. Is there any way to prevent this from happening? To my knowledge MySql does not allow constraints to be defined that span multiple tables.

I can think of one solution, but that involves duplicating the some_value field in table t3 so I can enforce a unique key directly on it. This obviously breaks the normalisation design rule, but is there any other way?


texts: What is the best way to migrate old data into a running mariadb database?

texts:
We have a need to migrate a large number of 100GB databases onto a new infrastructure.

I would like to minimise downtime as much as possible, so am toying with the idea of migrating the clients to the new infrastructure on a clean,new, database and after the event load the existing data in behind the new data going in.

I fully understand that this is probably a "how long is a piece of string" question without seeing the schema (which is around 400 tables), but, does anyone know if this is a) possible and b) of any tools that might help.

These systems are all running on Ubuntu and are using mariadb.

I'm not sure what else I can provide to help extract a cogent answer, but I can but ask.

Thanks.
Peter.

OK, so the stat of play is thus...

I have dumped the data from the tables I need to migrate and have added the --insert-ignore field to the dump to allow me to not get hung up on primary keys when I re-run things.

The data file produced is 23GB in total.

It all works well until I hit a table that has had the schema changed such that it has more columns than the source schema had at which point the inserts blow up because there is not enough data in the import line (the new fields do allow nulls, but I guess don't have a null default).

The error returned is Column count doesn't match value count on the table in question.

Any ideas on how to get around that?


texts: Is it possible to emulate the WITH command in MySQL 5.1.73?

texts:
I have the following query:

SELECT
    COUNT(*) AS `count`
FROM
    agreements
WHERE
    start_date >= STR_TO_DATE('14-01-2015','%d-%m-%Y')
    AND
    start_date < STR_TO_DATE('15-01-2015','%d-%m-%Y')
    AND
    active = 1
    AND
    (driv_id, veh_id) NOT IN (
        SELECT
            a.driv_id, a.veh_id
        FROM
            agreements a
        INNER JOIN
            types t
            ON
            a.agreement_type_id = t.id
        WHERE
            DATE_ADD(a.start_date,INTERVAL t.initial_term YEAR) <
                DATE_SUB(STR_TO_DATE('14-01-2015','%d-%m-%Y'),INTERVAL 2 MONTH)
    )

I then have to run the same query, having made a single change - removing the NOT from the (driv_id, veh_id) NOT IN ... part of the WHERE clause.

This is obviously not good because it means I literally copy and paste the query minus one word which violates DRY.

I googled around and discovered the WITH command, but it's not available in MySQL 5.1.73. The query I would have executed using WITH was:

WITH tuples AS
(
        SELECT
            a.driv_id, a.veh_id
        FROM
            agreements a
        INNER JOIN
            types t
            ON
            a.agreement_type_id = t.id
        WHERE
            DATE_ADD(a.start_date,INTERVAL t.initial_term YEAR) <
                DATE_SUB(STR_TO_DATE('14-01-2015','%d-%m-%Y'),INTERVAL 2 MONTH)
)
SELECT
    SUM(IF((a.driv_id, a.veh_id) IN `tuples`, 1, 0)) as in_sum
    SUM(IF((a.driv_id, a.veh_id) NOT IN `tuples`, 1, 0)) as not_in_sum
FROM
    agreements
WHERE
    start_date >= STR_TO_DATE('14-01-2015','%d-%m-%Y')
    AND
    start_date < STR_TO_DATE('15-01-2015','%d-%m-%Y')
    AND
    active = 1

I tried doing this with a temporary table as well by storing the result of the subquery in a temp table called tuples and selecting (in my main query):

SUM(IF((a.driv_id, a.veh_id) IN tuples, 1, 0)) as in_sum
SUM(IF((a.driv_id, a.veh_id) NOT IN tuples, 1, 0)) as not_in_sum

But this would throw a syntax error (not quite sure why).

Is there a way to do this, perhaps using joins (I tried to work out a join query but didn't have any breakthroughs)?


texts: Reasonable times for a mysqldump restore?

texts:
NOTE: question edited to take into account restore over LAN vs. local restore.

Are there benchmarks available for the time spent doing a database restore from a mysqldump file?

Based on my experience, a restore over LAN has a speed of 100-130 Mb/min, while if the dumpfile is on the local SSD drive we're around 1 Gb/min. We're talking about database size, not dumpfile size.
Hardware capabilities seem pretty much irrelevant once one has a fairly decent CPU and amount of RAM.
I have been wondering if that above is a reasonable time or if this can be speeded up -- especially in the case of a non-local restore (i.e. done over the network).


texts: VS 2019 - Schema Compare - "The certificate chain was issued by an authority that is not trusted" when TrustServerCertificate=True

texts:
I'm trying to do a schema compare in VS 2019 with Active Directory Password Authentication. The account I'm using works in SSMS, but fails in VS with the following error, even when TrustedServerCertificate is True.

  The certificate chain was issued by an authority that is not trusted

Here is a screenshot of what I'm entering and one with the TrustedServerCertificate property set to true.

Anyone know what could be the issue? Is there something that needs to be enabled on the server?


texts: How to get rid of using temporary when using DISTINCT in MySQL?

texts:
When I do the following, I get Using where; Using index; Using temporary. How do I get rid of Using temporary?

http://sqlfiddle.com/#!8/f61a2/3/0

CREATE TABLE `test` (
  `a` varchar(45) NOT NULL,
  `b` varchar(45) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `index1` (`b`,`date`),
  KEY `index2` (`b`,`date`,`a`)
) ENGINE=InnoDB;

INSERT INTO test (a, b, date) VALUES ('a1', 'b1', now());
INSERT INTO test (a, b, date) VALUES ('a1', 'b2', now());
INSERT INTO test (a, b, date) VALUES ('a2', 'b1', now());
INSERT INTO test (a, b, date) VALUES ('a2', 'b2', now());

INSERT INTO test (a, b, date) VALUES ('a3', 'b1', '2000-01-01 01:01:01');
INSERT INTO test (a, b, date) VALUES ('a3', 'b2', '2000-01-01 01:01:01');

EXPLAIN SELECT
DISTINCT a
FROM test
WHERE b IN ('b1', 'b2') AND
date > NOW() - INTERVAL 1 MONTH;


texts: Oracle remote OS authentication, security risks with domain users

texts:
What security risks can be with domain users?

create user "OPS$KA\USERNAME" identified externally.

grant create session to "OPS$KA\MARIK";

Then MARIK that is in KA domain, can logon to the database without password.

I wander to know if oracle checks that the user is a really domain user?

For example is there a risk that somebody with the computer name LB and username MARIK can connect to the database , even it is not a domain user?


texts: Replace oldSchema with newSchema in functions

texts:
I exported a database called oldDatabase using pg_dump in directory format.  This database contains a schema, tables and functions owned by oldRole.  These functions contain SQL statements like

INSERT INTO oldSchema.table(...)

To load mydump/ into newDatabase, I followed these steps:

Create oldRole in my newCluster (to load without errors),
Load my dump into the newDatabase using this command:

pg_restore mydump/ -d newDatabase

Reassign objects owned by oldRole to newRole,
Rename oldSchema to newSchema,
And drop oldRole.

But functions still contain statements like INSERT INTO oldSchema.table(...) and I get errors when I run these functions, because oldSchema does not exist.  I have to modify manually all functions to get INSERT INTO newSchema.table(...).

Is there a best practice to load properly from a database to another database with different schema and owner names?  How to perform a correct mapping?

I use PostgreSQL v9.5 with Red Hat 7.


texts:
I've recently finished a project, during which many DB tables were created.

Most of these tables contain temporary garbage, and I am looking for a simple way to list all these tables.

Is there a way to list all DB tables sorted according to their creation date?


texts: Find all IP's or users who access a specific SQL Server

texts:
Let's say I found a SQL Server and I have no idea which applications connect to it. Maybe I find one application but I am not sure if it is the only one using it.

Is there a nice way of finding all distinct connections?


texts: Dump roles on AWS without pg_dumpall -g access (postgresql 9.6.8)?

texts:
We have a system on AWS RDS Aurora, PostgreSQL v 9.6.8 option.

We are trying to move its roles to a new encrypted 10.5 instance, but we don't have superuser access that's required to do pg_dumpall -g on the old instance. (I am aware of the update to Postgres 10.0 that lets you do this without superuser access, but this is 9.6.8).

Any ideas for a workaround? Thanks in advance for any suggestions whatsoever!


texts: Grant permissions to allow user to check if session is on read replica or not

texts:
Azure SQL Managed Instances offer access to read replicas from the availability group (in specific tiers at least).

I am writing a test script for our application to connect to read/write and read only connections to prove everything is functioning as it should.

I know we can use the databasepropertyex SP to identify the current database access level, which will be enough.

However, the application user is obviously limited in permissions - so I want to grant the lowest possible permissions to the application user that still allows that user to identify the connectivity type.

I am really struggling to identify what permissions I need to grant to the app user that will allow them to see the output of this command - currently it returns 'NULL' regardless of rights I have granted.

I have obviously missed something somewhere!


texts:
Things go inside other things.  More than one can go inside and each one can be inside many different containers.  This is for a on-line shopping site where assorted things that we get from our suppliers are bundled together, given a web-page and then sold as that collective.  It all used to be tracked with a bunch of horrible CSV files but I'm setting up a proper database now.

create table collections(
    sku character varying(50),
    components text[]);

create table products(
    ordering_id character varying(50),
    inside text[] );

inside's elements are skus from collections and components elements are ordering_ids from products.

The contents of these easily fill in both columns in the products table and sku in collections.  Now I want to fill in components: preferably with some tricky SQL statement.  The best I could come up with is:

with new_values as ( select ordering_id, inside from products )
    update collections set components = components ||
        nv.ordering_id::text  from new_values nv
    where collections.sku::text = any( nv.inside );

but that only fills in the first product that's found inside each collections.  Why is that?  Do I need to do some sort of JOIN to get this to work?

Sample data:

collections

sku    components
10A    {}
20A    {}

products

ordering_id    inside
1234            {10A}
5678            {10A, 20A}

should leave products unchanged but collections should now be

collections

sku    components
10A    {1234,5678}
20A    {5678}

Unfortunately what I get from the SQL that I came up with above is

sku    components
10A    {1234}
20A    {5678}


texts: Using SSIS to get record counts for tables that may not exist

texts:
I have an SSIS package that executes simple queries against a list of servers.  I am having an issue with one particular simple query:  If a table exists, get the rowcount.  Otherwise report "not exists".

The SQL code works in Management Studio if I connect directly, but when I connect with SSIS using an ADO NET data flow task to execute the code I get a validation error.  It seems to me that SSIS is examining all objects in the code to see if they exist before executing.  It fails before it even tries to run.

If I comment out the "select count(*) from Table1" then everything works no problem.

The distributed servers are all using SQL Server 2008R2 Express Edition.

declare @Result varchar(1000),
        @Table1Count int

select  @Result = '',
        @Table1Count = 0

-- If tables exist, get the counts
IF EXISTS(select 1 from information_schema.Tables where Table_Name = 'Table1')
BEGIN
  select @Table1Count = count(*) from Table1
  select @Result = 'Table1: ' + convert(varchar(12),@Table1Count)
END
ELSE
  select @Result = 'Tables do not exist'

Any ideas?


texts:
I'm lost when it comes to choose the failover technology for my postgresql database. The options I currently see are:

PG Cluster
Streaming Replication
Warm Standby (log shipping?)
Hot Standby

Is there a feature comparison site where I can see the differences between those strategies? Is one of the mentioned techniques already deprecated or not anylonger in development? Is there a statement which requirements are covered by the available failover techniques?

My primary requirement is to provide a protection against data loss in case of a server failure. I already have a nightly backup of the database, however, this would not save me from loosing intra-day data.


texts: How do I get the total number of databases that are active?

texts:
I am learning Oracle. I am trying to figure out (using the command prompt) the total number of databases that are up.

I tried:

select name from v$database


texts:
I have created a report with following data

Age_Category  Count
31-40          10
41-50          20
51-60          30
61 and Over    40
Under 30       50

Now I want to order "Age_Category" column in SSRS like below, what is the best approach

Age_Category  Count
Under 30       50
31-40          10
41-50          20
51-60          30
61 and Over    40


texts: SSIS Expressions Date Format get just YY for year

texts:
I found many articles on date formatting expressions in SSIS but I couldn't find one that specified how to get just the Year such as 13 for 2013 or 12 for 2012... I tried

DATAPART("yy",GETDATE())

but it gives me the same result as

DATAPART("yyyy",GETDATE())

I was thinking of a dirty/hack fix which would be something like

DATAPART("yyyy",GETDATE()) - 2000

but it just doesn't feel right.... what if someone runs my package 1000 years from now? lol. Any suggestions?

I am trying to add a time stamp to a file name.. so today's file should be Auth040113.txt. I am creating the file name in a variable using expressions.


texts: Does an "update-update" conflict get created from the same node in P2P replication?

texts:
I have been told that an update-update conflict is being created in a production environment, which is set up using p2p replication, because of concurrent update by stored procedures.

The msdn reference states the update-update conflict happens only when a same row is being updated by different nodes.


texts: Restore SQL Server 2012 backup to a SQL Server 2008 database?

texts:
Is there a way to restore a SQL Server 2012 database backup to a SQL Server 2008?

I tried to attach file, it does not work.


texts: Splitting a column in two and returning a summary of results

texts:
Lets take a very simple table

Department           | Staff Name
---------------------|---------------
Web - Electronics    | Matthew
Store - Electronics  | Mark
Web - Home           | Luke
Store - Home         | John
Store - Home         | Paul
Store - Home         | Simon
Web - Electronics    | Peter

I need to work out some SQL to return the following:

Deparment    | Web  | Store
-------------|------|---------
Electronics  | 2    | 1
Home         | 1    | 3

So I need to do a count of web and store for each department. Is it possible to do something like this in SQL?

In C# I would loop through the results. I would split the deparment into two strings on the -. I would add an entry in a dictonary so the deparment name is the key. For the entry I would have a small object with a field of Web and Store. I would then increment the value by one if the entry is stor or web.

After this I would then loop throgu hmy dictiaonry to output the result by priting the dictionary key then printing the count for web and store.

I am not even sure where to begin trying to do this in just SQL...


texts: How to get First in and Last out with multiple records?

texts:
I have this records and it is attendance of each employee. I'd like to get their attendance, what i have tried Query is like this which is getting their attendance but if it has a multiple record it will get the latest attendance.
Verify_States stands for 0 = Time in and 1 = Time out
This is the Query what i have tried :
select * from
(select A.*,

LEAD(A.Verify_State,1) OVER (PARTITION BY A.user_id
ORDER BY A.user_id, A.verify_date) as Nxt_row_State,

LEAD(A.verify_date,1) OVER (PARTITION BY A.user_id
ORDER BY A.user_id, A.verify_date) as TO_Date
from tbl_excel_attendance as A ) as tbl_DTR_Mapped
where tbl_DTR_Mapped.verify_state = 0 and tbl_DTR_Mapped.Nxt_row_State = 1

EDIT : I Also Tried This Query
    Select * from (
SELECT DISTINCT
       user_id,
       verify_state,
       DATE(verify_date) the_date,
       MIN(verify_date) OVER (PARTITION BY user_id, DATE(verify_date)) min_datetime,
       LEAD(Verify_State,1) OVER (PARTITION BY user_id
ORDER BY user_id, verify_date) as Nxt_row_State,
       MAX(verify_date) OVER (PARTITION BY user_id, DATE(verify_date)) max_datetime
FROM tbl_excel_attendance) as q
where q.verify_state = 0 and q.nxt_row_state = 1
ORDER BY q.user_id, q.the_date;

Sample SQL Fiddle
Desired Output :
id  user_id      verify_date    verify_state  Nxt_row_State    TO_Date
6      16    2019-11-06 07:00:00    0            1       2019-11-06 19:45:00
3      16    2019-11-07 07:35:00    0            1       2019-11-07 18:05:00
13     19    2019-11-05 06:57:00    0            1       2019-11-05 18:28:00
10     19    2019-11-07 06:42:00    0            1       2019-11-07 18:04:00
15     39    2019-11-07 07:39:00    0            1       2019-11-07 19:09:00
20     40    2019-11-06 20:39:00    0            1       2019-11-07 06:39:00

Which verify_date is time in and TO_Date is time out
The Problem is the night shift. I can't get the attendance of night shift with multiple rows.


texts: What is wrong with this mysql INSERT statement?

texts:
I have table:
Book:
- id:(int) primary key, autoincrement, not null,
- title:(varchar) not null,
- price: (decimal) not null

and
Artical:
- id:(int) primary key, autoincrement, not null,
- title:(varchar) not null,
- price: (decimal) not null,
- book_id:(int) foreign key referencing book

I already have couple of books in Book table while Artical table is empty, i want to have and insert statement in Artical that will insert data and fill in book_id foreign key properly. I came up to this statement wich is not good but i'm feeling i'm close, here it is:
INSERT INTO Artical (id,title,price,book_id)
VALUES (
    null,
    'Six faces of shadow',
    15,
    (
        SELECT book.id
        FROM book
        JOIN Artical AS ar
        WHERE book.title = ar.title
    )
);


texts: Fulltext searching on shared hosting - 2000 Entires with around 500 words each

texts:
I'm fairly new to db administration and would appreciate some help. I want to host a site which requires full text search on around 2000 items, each with around 500 words each. I need this to be sorted by relevancy.

I'm trying to avoid going for google custom search, amazon aws search, google app search or any hosted service like sphinx, solr etc. The only reason I'm trying to avoid this is it means my costs and complexity is kept right down.

With this fairly low amount of entires is it possible to still keep high performance using MySQL Fulltext search? It seems possible to still get reasonable performance, especially when the amount is documents is small. Normally comparisons are done against millions of entries, not thousands.

I guess my question is, it is possible to get reasonable full text searching using MySQL on simple shared hosting on that amount of documents or am I still asking too much without bringing in proper search engine software.


texts: Finding matching records in a chain to form a parent user

texts:
I need to find commonality across 3 columns of data. For instance, user 1 and user 2 have the same email. user 2 and user 3 have the same device id.  So I want to assign a unique parentuserid of 1 to user 1, 2, and 3. See below for sample data.
DROP TABLE IF EXISTS #usr
SELECT 'u1' AS userid, 'email1' AS email, 'device1' AS device INTO #usr UNION ALL
SELECT 'u2' AS userid, 'email1' AS email, 'device2' AS device UNION ALL
SELECT 'u3' AS userid, 'email99' AS email, 'device2' AS device UNION ALL
SELECT 'u4_nomatch' AS userid, 'email77' AS email, 'device77' AS device

-- expected results
-- new parentuserid    userid
--  1                u1
--  1                u2
--  1                u3
--  2                u4_nomatch

This parentuserid = 1 is a set of 3 users, but this could actually be n (50+). I thought maybe cross join with window functions would be needed here or maybe recursive CTE but I haven't had any luck. It's hard to Google this sort of problem.
SQL Server 2017


texts: PostgreSQL JSON field performance with large documents

texts:
How well is the Postgre JSON type optimized for large JSON documents? I am especially concerned about partial retrieval (e.g. the cost of getting the last N items of a JSON array or looking up one particular item in a JSON dict) in situations where the JSON object is multiple MB in size and too large to be loaded efficiently in full.

Background: I am working with a dataset where each record has 10,000s of annotations. I do not need these annotations fully indexed, but do I need the record to insert quickly, so I am considering storing them in a JSON field instead of creating thousands of additional rows in a mapping table.

This relates to PostgreSQL 9.3.


texts: Schema for an RBAC (Role-based Access Control System)

texts:
I want to implement an RBAC, Flat based.  I've decided to base my system on the NIST standardization model.

Although all fine and well, I have an inclusion to that design.

Instead of a hierarchy for roles, I have a hierarchy at the permissions side: each permission will be related to a "module".

I'll explain with a diagram:

User U1 -> R1 & R2.
Role R1 -> P1, P2.
Permission P1, P2 -> Module M1.

A user can have multiple roles.
Multiple roles can be assigned to a single permission.
A permission can only be assigned to a single module.

What I have in my head in terms of a schema to hold this data, is as follows:

ac_users
     user_Id
     userRole_Ref

ac_user_roles
      role_Ref
      user_Ref

ac_roles
      role_Id
      role_Title

ac_modules
      mod_Id
      mod_Title
      modPerm_Ref

ac_permisions
      perm_Id
      perm_Title

ac_permision_roles
      pr_Id
      pr_Role
      prPerm_Ref

I'm worried this will cause to much overhead for this particular design.

The basic concept is that all tables will need to have a many-to-many relationship, except for ac_permissions which will have a many-to-one relationship with ac_modules.

Could anyone point out flaws with this design?


texts:
I have a table structure like this.

tblquestion
(
  nQuestionId1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT_NULL,
  nQuestionId2 INT UNSIGNED ALLOW NULL
);

Here..nQuestionId2 has a foreign key constraints to the nQuestionId1 on the same table i.e. tblquestion.

So..while restoring backup i am getting some warning and errors for foreign key on nQuestionId2..So,is it because of the foreign key reference to same table column??

Does this type of table structure can create any issues further?

Thanks


texts: Spotlight for SQL Server LockType explanation required

texts:
I have just started using Spotlight as I'm a software developer, but I am forced to identify database issues causing blocking (Spotlight shows me AWAITING_COMMAND at the points and all other processes are blocked, I assume this happens because the awaiting command involves a lock on a table that the rest of processes are trying to update). But this is not the case actually.

In SQL Activity => Locks I've noticed that there's plenty of locks in S mode on DATABASE (Lock Type), would anyone explain me what that actually means, is it harmful for the database and what may be the cause of it?

I've used sys.dm_tran_locks and what I'm receiving are locks on resource_type DATABASE. This is basically what I'd like to get insight on. I don't understand what that means.


texts: Altering table schema takes too much time

texts:
I am using PostgreSQL database.

I have a table with millions of rows. I have a varchar column with 2000 size and i want to make it to 4000. I ran the alter table command, but it is taking too much time.

Is there any quicker way to do it?


texts:
I have a Firebird sql query that returns 3 fields: Surname, Name and date as (YYYY-MM-DD HH:mm:ss)

select Surname, Name, "WHEN" from users
left join attendant on (users.id = attendant.userid);

This is the result:

Surname Name    2018-04-11 08:35:37
Surname Name    2018-04-11 12:52:30
Surname Name    2018-04-11 13:44:18
Surname Name    2018-04-11 17:08:05
Surname Name    2018-04-12 08:31:17
Surname Name    2018-04-12 12:36:07
Surname Name    2018-04-12 13:28:30
Surname Name    2018-04-12 17:44:55

What I want to achieve is to put the date field of the same day (YYYY-MM-DD) in the same row.

Surname Name    2018-04-11 08:35:37 2018-04-11 12:52:30 2018-04-11 13:44:18 2018-04-11 17:08:05
Surname Name    2018-04-12 08:31:17 2018-04-12 12:36:07 2018-04-12 13:28:30 2018-04-12 17:44:55

Is this possible with Firebird?


texts: Database backup and restore incase Always Encrypted feature is enabled

texts:
I would like to know that How SQL database backup and its restoration on other server will work if Always Encrypted feature is implemented on most of its tables?

Thanks


texts:
I have two different servers(A1, B1) with mysql-5.1.73 and currently, I'm replicating DBs with another two servers(A2, B2).

I would like to know is it possible to do a centralized DB replication, which means replicating DBs of both Master_A1 and Master_B1 to a single Slave_A2.

P.S: database and their table names will be same in both Master servers.


texts: 300 inserts per minute, 6 million rows per month

texts:
I have a Django application using Postgresql 9.3 that is about to grow in load to about 300 inserts per minute (peak load) and about 6 million rows per month in one table. Also there should be a lot of queries on that same table, nothing complicated, just a sum grouping by and indexed field.

The table looks like this:

        Column        |           Type           | Modifiers
----------------------+--------------------------+-----------
 id                   | integer                  | not null default nextval('seq'::regclass)
 commercial_entity_id | integer                  | not null
 commercial_branch_id | integer                  |
 when                 | timestamp with time zone | not null
 currency_id          | character varying(3)     | not null
 amount               | numeric(10,2)            | not null
 loyalty_account_id   | integer                  | not null
 code                 | character varying(16)    | not null

Can anyone tell me how much hardware should I provide for this load or what would be a good way to handle this?

Right now it is running on an Amazon AWS S3 m3.large with 2 vCPU and 7.5 GB of RAM. I guess this would not be enough but I don't have any real world experience to know better.

Update:

After some help from Meta I reformulate my question as this:

How you should go about determining what sort of hardware would I need to handle this load? Where can I learn another changes that could be done to the database to improve its performance?


texts:
I am having a problem on two different servers, both are running sql 2014.  The maintenance plan does not delete the older backups.  I have checked the permissions and the formatting and everything is correct.  During troubleshooting I ran the xp_delete_file command to delete a single backup file.  I got the error "Error executing xp_delete_file extended stored procedure: Specified file is not a SQL Server backup file." I ran a manual full backup and tried it again on that new backup file and received the same error.

I then copied a couple of the older transaction log files to a different server that was working correctly, ran the same script on them and the files were deleted.

Is there any way to force sql to recognize the backups that it makes as sql backups?  The files are good, account used is sysadmin account, and file permissions are not the problem.


texts: Exporting/Importing a large MySQL database without mysqldump

texts:
I'm attempting to export about 150GB of data from a database with hundreds of tables, and have found mysqldump to be very buggy.

After running a simple --all-databases dump succeeds, but the import always encounters random (and frequent) errors. These are generally syntax errors in the SQL statements because of incorrectly escaped quotation marks within the dump file.

I've done raw data transfers before by copying the actual data files, however the destination server I seek to import into does not allow me to access the file system.

Are there any other tools that are less buggy than mysqldump for very large exports/imports?


texts:
I am working on a sports database.
I have table for persons(players), teams, leagues etc.
The problem is, how do I identify a person uniqely? I was thinking that a new person could be added by either league organizer, team, or the player themselves, by registering to the website with this database.
One player can play in multiple leagues. So when they are already in the database and a new league will want to add him again, there will be a duplicate.
The only solution I can think of is using the persons national ID, as there potentially could be two persons with the same name, birthplace, and birth date.
Another solution I can think of is alerting the user that there is already a person with the same or similar data. Then they can check if it is really new person or just a duplicate. However, this doesn't work well as the user could still add duplicates.
What are your opinions on this problem? Can you give me some advice?


texts: Oracle multiple databases on same server

texts:
I want to be able to import multiple database dumps locally and be able to switch between them.

What I would like to achieve:

Import dumps (objects+data) from multiple databases to local database
Switch between imported databases (db1 and db2) locally

db1 and db2 have users/schemas/objects/etc with same name and this shouldn't cause a conflict.

Expected result would be following:

connect with one USERNAME/PASSWORD and query tables from schema named MYSCHEMA and then db1 data would be used.
"switch" active database (by passing some additional parameter on connecting) or executing some command.
now same USERNAME/PASSWORD combination and querying tables from MYSCHEMA should return data from db2


texts: securely exposing sql server 2008 for windows authentication

texts:
My company wants to have approximately 100 of their sales people (distributed around the country) to be able to run stored procedures from excel and return the data onto the spreadsheet.

We have sql server 2008. i need to figure out a safe way to do this.

i will create a form in excel where the user can push a command button to refresh the data based on the parameters that they choose.

how can i ensure that the connection from excel to the sql server is secure?
how do i run a stored procedure from excel?

i found this to be very good information: http://office.microsoft.com/en-us/excel-help/connect-to-import-sql-server-data-HA010217956.aspx

  Windows Authentication    Select this option to use the Windows user
  name and password of the current user. This is the most secure method,
  but it can affect performance when many users are connected to the
  server.

however, i would like your input on this.

yes, the sales reps do have windows logins, but can i use this solution if they will actually be entering specifying the data criteria, then sending the criteria over into the stored procedure and then getting the data from the server?


texts: What is the "SQL Agent - Generic Refresher"? Do I need it?

texts:
In relation to my other question: Why do connections to my database fail, but only sometimes? (more details of the specific problem are here).

What is the SQL Agent - Generic Refresher service?

It runs on my database every 15 minutes. Whenever it runs, an error occurs and it resets the SQL Server Service causing connections to the database to fail temporarily.

Do I need it enabled? Should I disable it?

What exactly does it do?

I have deleted all maintenance plans and SQL server agent jobs from my server but that doesn't seem to affect it.


texts: Prevent the collision data because duplicate id from imported database MariaDB

texts:
I am currently having a trouble with my Web Application. I made a Web application for a certain company. I made the app using CodeIgniter 3.

I built the database using Maria DB. For the id in each table, i am using Incremental Id for my application database for each table. I usually deploy the web app to the cloud server (sometimes the company have their own dedicated server, but sometimes haven't ). One day, there is a company that  they don't want to deploy the app that i have made before to the cloud ( for the security purposes they said ).

This company wanted to deploy the app to the employee's PC personally in the office, while the pc for each employee not connected to each other ( i.e stand alone pc/personal computer/employee's Laptop ). They said, for every 5 months, they would collect all of the data from employee's personal computer to company's data center, and of course the data center are no connected to the internet. I told them that's not the good way to store their data. ( because the data will be duplicate when i am trying to merge all of the data into one, since my database id are in Incremental id ). The id would be collapse while my database are incremental ID. But, The company still kept to make the app that way, and i don't know how to solved this. Does anyone know how to solved this problem ?

They have at least 10 employees that would used this web app. According that, I have to deploy the app to the 10 PC personally.

Additional info : Each employee have their own unique id which they got from the company

The study cases is what i describe below:

id   |  employee_id  | employee_name   |
1    | 156901010     |  emp1
2    | 156901039     |  emp2
3    | 156901019     |  emp3
4    | 156901015     |  emp4
5    | 156901009     |  emp5
6    | 156901038     |  emp6

the problem is whenever they fill the form from that application, some of the table are not stored the employee's id but the new id that come from increment id.

For example electronic_parts table. They have the attribute like below:

| id  |  electronic_part_name  |  kind_of electronic_part  |

if the employee 1 fill the form from the web app , the table's content would like below.

| id  |  electronic_part_name  |  kind_of electronic_part  |
|  1  |   switch               |           1               |

and if the employee 2 fill the form from the web app , the table's content would like below.

| id  |  electronic_part_name  |  kind_of electronic_part  |
|  1  |      duct tape         |           10              |

When i tried to merge the contents of the table into the data center it would falling apart because the duplicate id.


texts: DPM transaction log backups of SQL Server partially contained databases

texts:
I am trying to use Microsoft System Center Data Protection Manager (DPM) 2016 (5.0.342.0) to protect SQL Server 2017 Standard CU8 (14.0.3029.16) on Windows Server Std 2016.

I have a named instance with the standard built-in databases (master, msdb, model) and a couple of custom databases that were created by doing a restore from a full backup of SQL Server 2012.

I have switched the custom databases to partially contained and to database compatibility level 140.  Their Recovery Model is Full.  They are not in an Always-On Availability group, they are not configured for mirroring, they are not configured for transaction log shipping.

When I set up a DPM protection group and choose the entire instance, then configure 15-minute synchronizations along with daily Express full backups, DPM sets up Recovery Point jobs (effectively, transaction log backups) every 15 minutes for model, but my custom databases only get jobs for the daily full-database backup.

I can't find any specific error messages that seem relevant in Event logs (System or Application) on the DPM Server or the SQL Server, in the DPM logs (C:\Program Files\Microsoft Data Protection Manager\DPM\Temp) on the DPM server or the SQL Server, or in SQL Server's own error log.  I've granted both the DPM Server's domain computer account and the local account running the DPM agent (NT Service\DPMRA) a local user in the custom database with the db_owner role, but that doesn't help.

Any suggestions?

thanks!

Martin


texts:
Here is my situation.

I have an ubuntu server monitored by nagios.
recently nagios sends alarms regarding the memory consumption on the server. I logged into the server and executed the top command then I found that mysql is the process that consumes a lot of the memory (about 64%).

This server contains a forum, and DB replica for another server so I logged into mysql and show processlist I found only 7 records.

How could I dig more in this situation to decide what is the application which consumes all that memory?


texts: Log Shipping with Database Snapshot for Read Queries

texts:
Company may implement Log Shipping with Snapshot. This provides basic Disaster Recovery with Reporting area. Server A OLTP contains database which log ships to Server B (ReadOnly-Standby), Snapshot is taken on ServerB database. For updated data, we will create database snapshots every 30 min on ServerB.

(1) While restoring log every 30 min, I assume we cannot take snapshots on databases which are currently restoring, is that true?

(2) So plan is to take snapshots, when transaction log backups complete. If I have snapshot, and another transaction log restores occurs later on database, will be snapshot break or continue to operate normally?


texts: Have full SQL Server restore but losing 1 day of data

texts:
I have SQL 2008 R2 64bit and it is backed up with ASIGRA SQL backup. The issue I have is that we had corruption on the server and the DB got corrupted yet the LDF file is in tact and does have all the changes since the last backup.

So I can easily restore the backup, but Can I  then implement the changes logged in the LDF file so I can get back the day of changes we lost.


texts:
We have an events table that is reasonably large (~35 million rows)

The original table is like this,

CREATE TABLE [dbo].[Events](
[EventID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[EventTypeID] int,
[SourceID] int,
[EventTitle] [varchar](max) NOT NULL,
[EventContent] [varchar](max) NULL,
[EventDate] [datetime] NOT NULL,
CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED
([EventID] ASC)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

and it has an index that looks like

CREATE NONCLUSTERED INDEX [ix_EventDate] ON [dbo].[Events]
(
    [EventTypeID] ASC,
    [SourceID] ASC,
    [EventDate] ASC
)
INCLUDE (EventTitle, EventContent)

When adding new events, there is a business requirement to check to see if there's a "duplicate" event filed within the last 7 days (duplicate defined as having the same title and content).  The above index is used in the check query, but it is still very slow and only rarely finds any duplicates.

The query looks like

SELECT *
FROM #NewEvents
WHERE EXISTS (SELECT *
    FROM [dbo].[Events] e
    WHERE
    DATEDIFF(dd, #NewEvents.EventXML.value('(/Event/EventDate)[1]', 'DATETIME'), e.EventDate) BETWEEN -7 AND 0
    AND e.EventTitle = #NewEvents.EventXML.value('(/Event/EventTitle)[1]', 'VARCHAR(MAX)')
    AND e.EventContent = #NewEvents.EventXML.value('(/Event/EventContent)[1]', 'VARCHAR(MAX)')
)

I had thought maybe a couple of computed columns with hashes on the title and content might speed the check (by making the indexes much more compact).  Not wanting to alter the underlying table on a theory (possibly mucking with the existing replication setup), I tried to create an indexed view with those computed columns.

I created an index on the view like the one above INCLUDING the computed columns, but for whatever reason I can't get the query to use that index on the view; it always defaults back to the base table index, and to make matters worse doesn't use the INCLUDE values for the computed values; it joins with the PK to fetch the original columns.

The view index looked like this (where TitleHash and ContentHash are HASHBYTES of the full column text)

CREATE NONCLUSTERED INDEX [ix_viewEvents_EventDate] ON [dbo].[viewEvents]
(
    [EventTypeID] ASC,
    [SourceID] ASC,
    [EventDate] ASC
)
INCLUDE (TitleHash, ContentHash)

Anyway, I was wondering if anyone had any suggestions to speed up the query given the business constraints.

EDIT:  The first thing I tried was to change that DATEDIFF expression on the idea that it might have been blocking the use of the old index, but the plan for the old query showed that it was doing an Index Seek (not scan).

As Josh pointed out below, yeah the old query was doing an Index Seek - but only on the 2 leading columns.  The date was an output column and that filtering was done later.  But that stays that way no matter if I phrase the date filter as DATEDIFF() or "e.EventDate BETWEEN DATEADD(DAY, -7, #NewEvents.EventXML.value('(/Event/EventDate)1', 'DATETIME')) AND  #NewEvents.EventXML.value('(/Event/EventDate)1', 'DATETIME')"

EDIT 2: Using WITH (NOEXPAND) did, indeed, get sql server to use the new view and the new index, though to my disappointment running the new view-based query and the old query in the same batch came back with the Actual Plan saying the view was 99% of the 2 query batch.  So the experiment doesn't appear to have been successful.  Changing the phrasing of the date filtering here, though, does seem to be reflected in the Index Seek node.


texts: Upgrade PostgreSQL 9.1 to 9.3 and server no longer starts

texts:
Technically, I didn't upgrade from 9.1 to 9.3, I built a new server with Ubuntu 14.04 and used my old 9.1 config files. PostgreSQL wouldn't start:

* Starting PostgreSQL 9.3 database server
* The PostgreSQL server failed to start. Please check the log output.

but the logs were empty.

Eventually, after comparing the stock 9.3 postgresql.conf file and my 9.1 file I figured out that, at some point, the config variable unix_socket_directory had been changed to unix_socket_directories (not that easy to pick up in a diff with many changes and comments after each line).

Posted because I couldn't find anything on the net about this (until after I already knew what the issue was).


texts: Delete records in Peer to Peer Replication in SQL Server 2008 R2

texts: We have a peer-to-peer replication setup on our real time servers I want to delete records from some of the tables that are involved in the replication. I'm using a stored procedure to do this. Some of the tables have 8+ million records. I want to know if by running the stored procedure on one server is good enough or do i need to run on both the servers? And if I run on just one server how does it impact the performance? Please recommend any best practices for this process. This needs to be run as a job every month.


texts: Upgrade process from SQL Server 2000 to SQL Server 2012

texts:
I have a SQL Server 2000 database that I would like to upgrade to SQL Server 2012 (running on a different server).

I don't think there is a direct upgrade path, but if there is, what is it?

My current plan is:

Backup SQL Server 2000 database
Restore on server with SQL Server 2008 installed
Set compatibility level to 100 (SQL Server 2008 / SQL Server 2008 R2)
Backup database from SQL Server 2008 server
Restore on server with SQL Serevr 2012 installed
Set compatibility level to 110 (SQL Server 2012)

Am I doing unneeded steps?  Am I missing anything (like running utilities to identify usage of unsupported Transact-SQL)?


texts:
I have a database running on SQL Azure that is currently 280mb. It is a test database for a system that we're going to be putting into production soon so the data frequently gets deleted in batches and then recreated.

When I used the "Copy" feature on SQL Azure the new database that it created was only 156mb. When running a query to show how much data each table is using it appears that each table is pretty much half the size it used to be.

I've worked out that this is going to be down to data fragmentation, but my question is what can I do about this? It doesn't appear that Microsoft perform any maintenance on the data itself, and as it's a pay-per-use model I'll end up hitting the 1gb limit when I've not got 1gb of data!

For reference this is the query I ran to show table size:

select sys.objects.name, (reserved_page_count * 8.0 / 1024)
from sys.dm_db_partition_stats, sys.objects
where sys.dm_db_partition_stats.object_id = sys.objects.object_id


texts: Oracle index on column with length > maximum varchar2 length

texts:
Description:

We have a table on PostgreSQL 9.3 and we migrate it (data & structure) to Oracle 11.0.2.4. Here is our table:

create table cstb_temp_log
(
id NUMBER PRIMARY KEY,
log_info data_type(max_length) -- max_length < 10000
)
-- log_info : data is inserted/updated every second (**)
-- data_type: may be we can use "clob" because max_length of varchar2 in Oracle is 4000 (SQL).
-- cstb_temp_log : size = 1 GB, row = 400000

We want to index and search "text" on log_info column, so we tried Oracle Text 11g .

Problems:

If we use "data_type" is "clob", we can use "context index" and it have to synchronize after DML . ( we can not use this way because of (**) )

If any, how can we index and search on "log_info" column (max_length < 10000 and data is changed every second) ?


texts:
I'm quite new on learning SQL and I seem to be running into an issue with the following code:

CREATE TABLE `read`
  (`order` NOT NULL UNIQUE AUTO_INCREMENT,
  `title` VARCHAR(50) NOT NULL,
  `url` VARCHAR(50) NOT NULL,
  PRIMARY KEY(`title`,`url`)
)DEFAULT CHARSET=UTF8;

It's returning the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL UNIQUE AUTO_INCREMENT, title VARCHAR(50) NOT NULL, url VARCHAR(50' at line 2


texts: DB2: Is it good to create the index table space to use 32 KB data page

texts: I found our DB2 server configs all the index into one table space. And it then defines the table space to use 32kb data page. Is this good? How to judge it? Any advice is welcome.


texts: Is it better to use C# for complex business logic that uses SQL and then run it from SQL Server or do everything from within SQL Server?

texts:
I find a C-based language like C# to be better in terms of programming constructs for handling complex code. So has anyone here used SQL simply to extract data and managed their business logic in C++ or C#? How was your experience?

Is it better performance-wise to be doing this?


texts: How can I recover oracle database without new data?

texts:
I have oracledb 11g. I setup archivelog mode on my server. There's my backup\recovery scenario:

rman TARGET SYS/pass@XE
RMAN> backup database plus archivelog;

After that I make changes to the data tables. Create new customer as example.
After that I execute in rman:

shutdown immediate;
startup mount;
restore database;
recover database;
alter database open;

My server is started. I run my application and creater customer is there... Where's my mistake? I tried delete all dbf from db folder. And after restore database I saw my new customer. How can I restore a database without the data that was added to the database after the backup?


texts: Is there any scenario where a exported (.sql) database dump could be bigger in size, than the database itself?

texts:
I'm a developer in a local company, and I'm currently working on a project which involves migrating a website to my company's server, from some other hosting provider.

For some reason this other company won't give us access to the cPanel, but rather provided us with the project (files) and an exported db script(.sql).

Now I'm having issues after importing the db because it lacks few tables.
I actually think the db export is okay itself. While the script file is rather large ~1.5GB, the db after importing is only ~1GB.

Q:

I'm using cPanel & phpMyAdmin, and now I'm wondering if there's any scenario where the db SQL script file could be larger than the database itself, or might there be a limitation within cPanel or phpMyAdmin regarding the size, which would cause the import to skip/stop or anything similar on about 1GB?

I always thought (and still think it makes more sense) that the db could be only bigger than the script file, due to how the SQL scripts are built, lack of indexes etc.

Just wanted to check if this is possible, before I start inspecting the 1.5GB sql file myself and see wether tables are missing there too.

Thanks PS couldn't find similar questions.


texts: Enable compression for SQL Server Managed Backup to Microsoft Azure

texts: I am using SQL Server Managed Backup to Microsoft Azure for multiple databases on my SQL Server 2017. I am trying to figure out how to enable compression on some of the databases. It seems from this link that it should be possible as it recommends doing so if your backup size is going to exceed the max block blob size. However I cannot find any information on how to enable this.


texts: avoid lock and allow two scripts to insert data in one table

texts:
I have a database called db1 and I'm filling it with other two different databases, let's call db2 and db3. I connect db2 and db3 to db1 using postgres_fdw and now I'm running scripst like this:

insert into db1.table from select a,b,c from db2.table

and

insert into db1.table from select a,b,c from db3.table

turns out that only the first script is running, the second is waiting with idle in transaction. This is what htop shows me from the second script, while the first is ok.

looking for reasons, I found out that this may be happening due to a lock, so investigating on postgres, I also found this

I'm inserting data into interactions and each interactions has user, so I'm selecting the correct user before insert into interaction.
Is there a way to bypass the lock? If so, is it safe?


texts:
I'm creating a view on a SQL Server 2005 database via SQL Server Management Studio 2008, the view reads from two tables. The view is accessed from a web application and I want users to be able to update one of the tables by updating the view, but not the other. One table is live data from an important system and should not be changed by this application. I control the web application so it shouldn't ever generate a query that will update that table, but I don't want it to be able to at all.

Is there any way to set permissions so the view itself can only read from one of the underlying tables? Everything I know about permissions is at the user level, but I thought there was a way to make a view read-only.


texts: Do I have to worry that root user can login into MariaDB 10.5 without password

texts: I remember when installing MySQL Server 8.0, or in some database setups, there are warnings about no password login. Should I worry about this? I think if someone gets access to root user on my debian they can reset root password of MariaDB. What is the real answer here? I appreciate doc reference.


texts: How is it possible for Hash Index not to be faster than Btree for equality lookups?

texts:
For every version of Postgres that supported hash indexing, there is a warning or note that hash indexes are "similar or slower" or "not better"  than btree indexes, at least up to version 8.3. From the docs:

Version 7.2:

  Note: Because of the limited utility of hash indexes, a B-tree index should generally be preferred over a hash index. We do not have sufficient evidence that hash indexes are actually faster than B-trees even for = comparisons. Moreover, hash indexes require coarser locks; see Section 9.7.

Version 7.3 (and up to 8.2):

  Note: Testing has shown PostgreSQL's hash indexes to be similar or slower than B-tree indexes, and the index size and build time for hash indexes is much worse. Hash indexes also suffer poor performance under high concurrency. For these reasons, hash index use is discouraged.

Version 8.3:

  Note: Testing has shown PostgreSQL's hash indexes to perform no better than B-tree indexes, and the index size and build time for hash indexes is much worse. Furthermore, hash index operations are not presently WAL-logged, so hash indexes might need to be rebuilt with REINDEX after a database crash. For these reasons, hash index use is presently discouraged.

In this version 8.0 thread, they claim that had never found a case where hash indexes were actually faster than btree.

Even in version 9.2, the performance gain for anything other than writing the actual index was almost nothing according to this blog post (14 March 2016):
Hash Indexes on Postgres by André Barbosa.

My question is how is that possible?

By definition, Hash indexes are a O(1) operation, where a btree is an O(log n) operation. So how is it possible for a O(1) lookup to be slower than (or even similar to) finding the correct branch, and then finding the correct record?

I want to know what about indexing theory could EVER make that a possibility!


texts: Application queries are not getting generated in trace file in oracle

texts:
I am getting queries in trace file which are fired from Oracle SQL Developer IDE but I want to trace queries on Oracle database, which got fired from my java application.

I'm using ALTER SESSION SET SQL_TRACE = TRUE; command and using tkprof utility.


texts: How do I drop all constraints from all tables?

texts: I want to drop all default constraints, check constraints, unique constraints, primary keys and foreign keys from all tables in a SQL Server database. I know how to get all the constraint names from sys.objects, but how do I populate the ALTER TABLE part?


texts: Sql restrict insert records for the same client based on 2 columns

texts:
I have a simple table

CREATE TABLE Records
(
    Id INT,
    Client INT,
    IsActive BIT,
    HasSession BIT
)

I tried using OR statement but it only considered the 2nd argument.

CREATE NONCLUSTERED INDEX F
    ON ClientsDB.Records (Client)
    WHERE IsActive = 1;

CREATE NONCLUSTERED INDEX F
    ON ClientsDB.Records (Client)
    WHERE HasSession = 1;

I tried doing some research and seems you cannot use OR statement on these

As per trigger suggestion referred, one of my requirements is to avoid them at all so i cannot use anything related to triggers.


texts: How do I rollback a dropped table using log?

texts:
I have dropped a table from a SQL Server 2008 database.

How do I unroll the transaction log to recover the table and latest contents?

The database recovery model is "Full".

Is there any way or third party applications for rollback the dropped table using log?


texts: How to filter a value in json field type on Postgres 9.2?

texts:
My json field data is like this:

{"active":true,"id":"xxxxxxxxxxxxxxxxx","settings":{"secret":"xxxxxxxxxxxxxxxxxxxxxxxxxx","token":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx","expires":"2019-12-16 01:11:23"},"plan":"Sample"}

Then I tried to query the field like this:

select * from integrations.accounts where field -> 'id' = 'xxxxxx';

But it gives me an error of:

  SQL Error [42883]: ERROR: operator does not exist: json -> unknown

I found that the arrow operator (->) is not supported in version 9.2:

  Unsupported versions: 9.3 / 9.2

Is there any alternative way to do this?


texts:
We are building a new SSRS 2008R2 environment on a separate network.

How can I migrate the reports from our current network to the new network?

Can I backup the reports from the existing SSRS server and then put them in a location where the server on the new network can pick them up?


texts: How does the optimizer in Oracle Database use I/O statistics?

texts:
I would like to help Oracle's query optimizer make better-informed decisions. I have been unable to find much information on how the optimizer determines the cost of I/O operations, other than unsourced remarks that it uses the time that it takes to retrieve a single data block. Here are some things that I would like to know:

Is it possible to view the current I/O benchmarks used by the optimizer?
Is it possible to trigger an update of those benchmarks?
Does the optimizer account for the current I/O cost of an operation? For example, if there is heavy concurrent load on the storage device causing a reduction in performance, does it consider that the relative cost of I/O will temporarily be higher?
Does the optimizer consider latency/random-access performance versus throughput/sequential performance?
If you have data files on multiple storage devices with different performance characteristics, does the optimizer have and apply device-specific costs?


texts: Large batch INSERTs / UPDATEs and DELETEs Performance problem

texts:
I'm having a problem in finding the best option to approach the following problem (postgres 9.5):

I get update batches of about 100.000 rows at a time from another system. This happens every 10-15 minutes usually, but it's possible that I get multiple of these batches at the same time. The batches are separated by "category" and one batch only ever contains data from one. Every "category" gets an updated set every 10-15 minutes.
New rows get inserted, old rows deleted and still existing rows should get updated to the new values.

This poses the problem that the table emasses serious amounts of garbage data, the VACUUM processes run really slowly and general table performance is really poor.

Now I thought I could solve this problem by creating child tables for every "category" within the data and thus "sharding" the data.

Would this make sense in this case, or are there better options for me to persue?


texts: Mysql How to insert a variable as field value in a table

texts:
I have created a variable named hash as a password hash. How can this variable be inserted a a field value in a table?

hash = pbkdf2_sha256.encrypt("password", rounds=200000, salt_size=16)
cur.execute("insert into meanu(username,pwdhash1) values('BV2',hash)")
produces following error:
mysql_exceptions.OperationalError: (1054, "Unknown column 'hash' in 'field list'")
mysql_exceptions.OperationalError: (1054, "Unknown column 'hash' in 'field list'")

cur.execute("insert into meanu(username,pwdhash1) values('BV2','hash')")
inserts the literal "hash" as the field value.


texts: SQL Split Address Line into Separate Columns

texts:
Whats the best way to split a comma list column into different columns? I am looking for a simple algorithm, heard it is best to avoid scalar value functions, as they might be slow. Searching into many methods, substring, find, splitstring; we need to run this algorithm across millions of addresses, so looking for best, optimal answer with good coding practice.

create table dbo.AddressTest (AddressLine varchar(255))

insert into dbo.AddressTest (AddressLine)
values ('123 Maple Street, Austin, Texas, 78653, 555-234-4589')

-- may not require substring, just looking for good way
select
    substring  as AddressStreet   -- Expected: 123 Maple Street
    substring(...) as City,       -- Expected: Austin
    substring(...) as State,     -- Expected: Texas
    substring(...) as ZipCode     -- Expected: 78653
    substring(...) as PhoneNumber -- Expected: 555-234-4589
from dbo.AddressTest

looking into this method also:
https://stackoverflow.com/questions/19449492/using-t-sql-return-nth-delimited-element-from-a-string


texts: Is there such a thing as an OLAP cube that can change the number of levels in a hierarchy without having to be rebuilt?

texts:
The current analytics solution that my team works with cannot handle changes in the structure of one of its hierarchies. If a new level or branch is added, then the entire domain must be first backed up, and then rebuilt with the new hierarchy.

One of our use cases requires an OLPA model where the domain should be able to gracefully handle changes to the hierarchy structure. Does such a thing even exist?


texts: Why doesn't SQL Server rollback the identity_insert state?

texts:
I have a large script for setting up a new database that does lots of identity inserts on many tables; while I'm working on extending it I quite often have transient errors due to it being a work in progress. If one of these errors is between set identity_insert [table] on and set identity_insert [table] off the script refuses to run a second time with the following error:

  Msg 8107, Level 16, State 1, Line 15
  IDENTITY_INSERT is already ON for table 'x'. Cannot perform SET operation for table 'y'.

This is a pain! To work around it I highlight the 'off' line and run that manually, or kill the connection and reconnect (identity insert is per session I gather).

I have the whole script wrapped in a transaction, with set xact_abort on at the top so my expectation would be for the server to return to the pre-error state.

Why is this not the case? Am I doing anything wrong, and are there any more robust approaches than what I have here?

The following is a minimal test-case to reproduce the behavior. Run it twice and notice the error changes from a type conversion error in the first run to the identity_insert error on subsequent runs:

-- setup test tables for convenience
if not exists (select * from sys.tables where name = 'bill')
begin
    create table bill (id int identity primary key);
end
if not exists (select * from sys.tables where name = 'ben')
begin
    create table ben (id int identity primary key, height int);
end

set xact_abort on -- automatically rollback transaction on error
begin tran

    print 'adding bill records'
    set identity_insert bill on; -- breaks unexpectedly on *second* run
    insert into bill (id) values (1);
    set identity_insert bill off;

    print 'adding ben records'
    set identity_insert ben on;
    select * from ben
    -- insert that intentionally causes error due to type conversion failure
    insert into ben (id, height) values (1, 'not an int');
    set identity_insert ben off; -- never gets run, as expected

commit

Output from first run (expected behaviour):

  adding bill records
  adding ben records
  Msg 245, Level 16, State 1, Line 22
  Conversion failed when converting the varchar value 'not an int' to data type int.

Output from second run (unexpected behaviour):

  adding bill records
  Msg 8107, Level 16, State 1, Line 14
  IDENTITY_INSERT is already ON for table 'data-test.dbo.ben'. Cannot perform SET operation for table 'bill'.

SELECT @@version gives:

  Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)

I've tried this both in management studio and in visual studio just to check it's not an odd client bug.


texts: Deleting would-be orphans when creating FK in Postgres

texts:
I am working on a script that replicates schema and data. I first create all the structure except for foreign keys after reading it from the source's system tables, then copy the data, then create the FKs so that if a would-be orphan gets inserted in a child table after I copied the parent (while I am running the script), I still get the basic schema and the data, even though the FK creation fails due to the orphan data.

My next step is to do something like wrap a try/catch around the create FK statement and, if it fails, delete the orphan data that caused it. Doing this will be somewhat complicated because I would have to extract all the reference keys and assemble my own delete statement (I currently don't do that because PG system tables provide a ready REFERENCES statement which I can concatenate).

But I am wondering if there is a way to do a ALTER TABLE x ADD CONSTRAINT fk_y FOREIGN KEY ... DELETE ANY WOULD-BE ORPHANS which would save me that step.

So if my parent table is:

 parent_id     | parent_name
---------------------------------
 1             | Joe
 2             | Mary

and the child is:

child_id      | child_name     | parent_parent_id
--------------------------------------------------
 11           | Jimmy          | 1
 21           | Joey           | 2
 31           | Jeff           | 3

'Jeff' would be deleted before enforcing referential integrity because his parent and he were introduced in the source after the source parent was copied to the target. Then the FK can be created against consistent data.

Clarifications (from comments / chat discussion):

  Do you read the data in a single transaction or not?

I don't use a single transaction as it would be too huge. I break the retrieved data by table and for some large tables by table segment (batch of 5000 or 10000 rows).
This is essentially an ETL that makes an "evil twin" out of a DB. hope that makes sense.
On average a DB copy in my environment takes between 20 min (for smalled DBs) to 1.5-2 hrs (for larger DBs) some tables have millions of rows. I load chunk by chunk in the memory of my Python script

  What about pg_dump or pg_basebackup?

My app does some other things that pg_dump doesn't do, such as better logging. Also, pg_dump expects the same users on both the source and target and a host of other issues. I tried it, didn't like it. I like my tool much better, I want to  work through this minor issue that is left and happens only sporadically.


texts: Table structure to store a an array of tags per entry

texts:
Lets say one wants to create a DB to store items, each having a Set<String> tags, associated with it. A straightforward way would be a flat table with ItemID, ItemName, DateCreated,... and a Set<String> tags. This is going to be inefficient for requests

Select * from MyTable where tags like '%my_tag%'

What is the efficient way to store these tags? Should I create a single (huge) table with a compound key ItemID || tag_number || tag, or should I create a lot of small tables, each storing tags for a given item?

Will the answer change if I use PostrgeSQL?


texts: Composite Key with a column that auto increments and reset when foreign key value changes

texts:
I am trying to implement a ticket system, the numbers generated would be sequential.

4000, 4001, 4002, 4003

My idea was to have a table that relates to current raffle, and the ticket_no column is an auto increment column that resets when the foreign key changes.

The composite keys are raf_id and ticket_no since the ticket no should not exist multiple times in same raffle

Is this possible?

|------------|-----------|---------|
| raf_id     | ticket_no | user_id |
|------------|-----------|---------|
| 1          | 4000      | 1       |
| 1          | 4001      | 1       |
| 1          | 4002      | 2       |
| 1          | 4003      | 3       |
| 2          | 4000      | 4       |
| 2          | 4001      | 4       |
| 2          | 4002      | 5       |
| 2          | 4003      | 1       |
|------------|-----------|---------|


texts: Database Properties Error - sys.spt_values missing but dbo.spt_values works?

texts:
We have SQL2012 SP1 installed on several servers and have run into an issue on one instance trying to view the database properties in SSMS. (This happens with both SSMS 11.0.5058.0 and 11.0.3128.0.) I have crawled all over Google results looking for something that makes sense, but I'm baffled at the moment.

The issue is rooted with a view in the master database called dbo.spt_values.  This table is referenced in the error thrown by SSMS when trying to view the properties.

The recommended solution here is to run the u_tables.sql script with will recreate the necessary objects in the master database.  Opening and running this script produces an error stating:

Msg 208, Level 16, State 1, Procedure spt_values, Line 7
Invalid object name 'sys.spt_values'.

Most of the posts I have seen related to this error recommend completely rebuilding the master database at this point, then reattaching or restoring the user databases.  Before I went down this road, I wanted to see what the servers not experiencing this problem look like...  To further my befuddlement, I am not able to see sys.spt_values ON ANY INSTANCE OR ANY DATABASE.  However, the master.dbo.spt_values view does return rows when queried.  When I try to take the select statement out of the view and run it, I get the invalid object message again.

I have connected as my windowd users, which is sysadmin, as well as the SA login.  I have done this on each of the servers as well as a local instance of SQL 2012 SP2.  I have used SSMS installed with the original SP1 installation as well as an instance that had been upgraded to SP2.

I have also looked at synonyms, other views, functions, stored procedures, or any other objects I could parse or query and have no idea where I can find the sys.spt_values table.  Any help to get the properties box working as well as improve my understanding of the purpose of this table and where it lives would be monumentally helpful!

Resources I've already looked at:

http://www.experts-exchange.com/Database/MS-SQL-Server/Q_21958187.html
http://answers.flyppdevportal.com/categories/azure/sqlazure.aspx?ID=92d22012-b829-4823-ab3a-7195fcf2dcb4
http://social.msdn.microsoft.com/Forums/en-US/3343a3a6-bd4a-48d6-b87f-2480ef8b597c/master-db?forum=sqldatabaseengine
http://web.archive.org/web/20150411043738/http://sqlserver2000.databases.aspfaq.com/why-do-i-get-errors-about-master-spt-values.html
http://blogs.msdn.com/b/suhde/archive/2009/04/05/invalid-object-name-master-dbo-spt-values-microsoft-sql-server-error-208.aspx

(I also followed links within the links...  Good thing I'm bald already)


texts: SSIS Package Deployment Failure using SSDT

texts:
SSIS fails almost immediately on the final "Deploying project" step of the Deployment Wizard (Package Deployment Model), using Visual Studio 2010 (SSDT) connecting to a SQL 2012 Server:

Failed to deploy project. For more information, query the operation_messages view for the operation identifier '10028'.  (Microsoft SQL Server, Error: 27203)

The report XML contains few clues to the issue, however the "0x80131904" could indicate a possibly network library issue.

System.Data.SqlClient.SqlException (0x80131904): Failed to deploy project. For more information, query the operation_messages view for the operation identifier '10026'. &#xD;
                    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
...
 Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder.DeployProject(String projectName, Byte[] projectStream)
...
Error Number:27203,State:1,Class:16

The Operation_Messages view returns no data for that ID.

The Integration Services' Catalog All Operations report simply reports a Failed/Deploy Project.

Interestingly, the package deploys successfully if I run the Wizard via the SQL Server Management Studio (right-click on the Projects tab for the project and choose Deploy Project and browse/import the ispac file). Note that I'm running SSMS remotely as well as VS2010/SSDT, so I'm not sure why only VS2010 is failing.

I also tried adjusting the Server-wide Default Logging Level to Verbose but this didn't seem to provide any additional info.

Any thoughts on why SSDT is failing to deploy the package?


texts: How can I find table name for each row result?

texts:
How can I find the table name for each row result in my query?

I have tried ideas suggested on the stack network (overflow, dba)

1 How to make a table name an attribute in PostgreSQL?

2 https://stackoverflow.com/questions/24580352/get-the-name-of-a-rows-source-table-when-querying-the-parent-it-inherits-from

The template is the parent that spawns all the child tables I am searching. When I search just name and state, I get expected results, but they all come from different tables

select first_name, last_name, state
from template
where completed_interview_flag = 1

When I add the tablename as suggested, to try to find the source table name - I get an error

select tableoid::regclass AS source, first_name, last_name, state
from template
where completed_interview_flag = 1

********** Error **********

ERROR: column "tableoid" does not exist

SQL state: 42703

Character: 8


texts: Minimize locking on UPDATE with multiple WHERE columns

texts:
I have a problematic query that's causing a lock timeout:

UPDATE <some_table> SET col1=<some value>
WHERE col1 IS NULL AND col2 > <some value>
ORDER BY col2
LIMIT 100

I have two issues here:

I have multiple servers committing the query simultaneously, they lock each other out and I get lock timeout or even deadlock sometimes. Optimally the servers should update mutually exclusive rows so locking shouldn't happen at all. Is there a way I can skip any locked row in the update?
If I can't avoid locking, and I already have an index for col1 and another one for col2, will Innodb lock all rows that satisfy any condition in the WHERE clause or only the ones that satisfy both conditions? If the answer is the former, can I add an index for the two columns together or do I also need to remove the indexes that I have (for each column separately)?


texts:
I have $PGDATA/base but nothing else.  It was taken from an installation after the postgreSQL service was shutdown, so is clean.  Is there any way to recreate the other elements in $PGDATA that allow it to be used again - pg_tblspc/, global/ ?

FWIW I am running 8.1.23.

Thanks