texts: number of seconds to years-months-weeks-days-HH:MM-SS

texts:
I am running this all the time to get HH:MM:SS from a number of seconds:

DECLARE @s INT
SELECT
    @s = 23251
SELECT
    @s
  , CONVERT(TIME, DATEADD(SECOND, @s, 0));

when I want to add days to the equation I run the following query:

DECLARE @seconds AS int = 232511;
SELECT
    CONVERT(varchar, (@seconds / 86400))                --Days
    + ':' +
    CONVERT(varchar, DATEADD(ss, @seconds, 0), 108);    --Hours, Minutes, Seconds

what if I wanted as well the number of weeks, months, and years?

How would I go around that?

I think the exact calculation would need to have a reference to when the number of seconds happened.

Let's say for instance I am interested in how long a sql server job has been running for. I have jobs within the replication category that have been running for years, and the starting point is known.

When not specified we could assume the number of seconds are up to now.


texts: Are null values allowed in any column of a table in SQL?

texts: I have doubts over null values.Therefore can you please explain me briefly what is a null value in sql and can we use null values at any place in sql tables?


texts:
I'm using Microsoft SQL Server 2012 and trying to run a simple query against it within Management Studio. I'm getting the following error (in SSMS, running on the server):

  An error occurred while executing batch. Error message is: Exception
  of type 'System.OutOfMemoryException' was thrown.

The system has 24GB of RAM installed but looking in task manager the sqlservr.exe process is only using 2.9GB.

Is there a setting somewhere that is restricting its RAM usage?


texts: Constrain input to a few different strings

texts:
Hi I can't seem to get a constraint working the way I expect in postgreSQL.
From within pgadmin I execute the following SQL query.

-- Check: "TypeCheck"

-- ALTER TABLE "ComLog" DROP CONSTRAINT "TypeCheck";

ALTER TABLE "ComLog"
  ADD CONSTRAINT "TypeCheck" CHECK ("Type" SIMILAR TO 'email|post|IRL|minutes');
COMMENT ON CONSTRAINT "TypeCheck" ON "ComLog" IS 'One of email|post|IRL|minutes';

When executed this is converted to.

-- Check: "TypeCheck"

-- ALTER TABLE "ComLog" DROP CONSTRAINT "TypeCheck";

ALTER TABLE "ComLog"
  ADD CONSTRAINT "TypeCheck" CHECK ("Type" ~ similar_escape('email|post|IRL|minutes'::text, NULL::text));
COMMENT ON CONSTRAINT "TypeCheck" ON "ComLog" IS 'One of email|post|IRL|minutes';

I expect this to limit my input for the Types column to one of email post IRL or minutes. However when entering table data this constraint fails when i enter one of these types.
The Types column is of character type.
Does anyone know how to fix this.
Thanks.


texts: How to Add Check Constraint between Tables (or change DDL)?

texts:
Given the following PostgreSQL table definitions:

postgres=# create table if not exists messages(
postgres(#     id serial primary key,
postgres(#     max_length smallint not null check (max_length > 0),
postgres(#     default_text text check (length(default_text) <= max_length),
postgres(#     created_on timestamp with time zone not null
postgres(# );
CREATE TABLE
postgres=#
postgres=# create table if not exists customized_messages(
postgres(#     message_id serial references messages(id) not null,
postgres(#     message text not null,
postgres(#     created_on timestamp with time zone not null
postgres(# );
CREATE TABLE

how can I enforce length(customized_messages.text) is <= messages.max_length per the foreign key constraint?

Note that, as-is, I can do the following:

postgres=# insert into messages (id, max_length, default_text, created_on) values (1, 5, null, current_timestamp);
INSERT 0 1
postgres=# insert into customized_messages (message_id, message, created_on)  values (1, 'longer than 5', current_timestamp);
INSERT 0 1
postgres=# select * from messages;
 id | max_length | default_text |          created_on
----+------------+--------------+-------------------------------
  1 |          5 |              | 2019-01-18 14:12:59.692075-05
(1 row)
postgres=# select * from customized_messages;
 message_id |    message    |          created_on
------------+---------------+-------------------------------
          1 | longer than 5 | 2019-01-18 14:13:38.226987-05
(1 row)

So, I would like to not allow, e.g. check constraint or something else, the longer than 5 to be inserted since its length is > 5.


texts: How to avoid tons of lower(column) in WHERE clause for several LIKE comparisons?

texts:
I've got to filter data in a string column and thus need to compare the column using NOT LIKE with a bunch of strings.

I'm using SQL Server and my code looks like this:

SELECT history.date,
       history.new_value,
       res.resource_name
FROM   item_history history,
       owned_resource   res
WHERE  (history.attr_name = 'resource_contact' OR history.attr_name = 'Kontakt-UUID')
       AND res.inactive = 0
       AND history.com_par_id = res.own_resource_uuid
       AND lower(history.new_value) NOT LIKE '%tp für%'
       AND lower(history.new_value) NOT LIKE '%rücklieferung%'
       AND lower(history.new_value) NOT LIKE '%rückläufer%'
       AND lower(history.new_value) NOT LIKE '%stoerreserve%'
       AND lower(history.new_value) NOT LIKE '%zentrallager%'
       AND lower(history.new_value) NOT LIKE '%bhs-pool%'
       AND lower(history.new_value) NOT LIKE '%lager halle%'
       AND lower(history.new_value) NOT LIKE '%lager logistik%'
       AND lower(history.new_value) NOT LIKE 'reserve %'
       AND lower(history.new_value) NOT LIKE '%igeko%bhs%'
       AND lower(history.new_value) NOT LIKE '%service%ecg%'
       AND lower(history.new_value) NOT LIKE '%multifunktionsdrucker%'
       AND lower(history.new_value) NOT LIKE 'nn%gisa%raum%'
       AND lower(history.new_value) NOT LIKE '%citrix%admins%'
       AND lower(history.new_value) NOT LIKE '%personalwesen%'
       AND lower(history.new_value) NOT LIKE '%etagendrucker%'
       AND lower(history.new_value) NOT LIKE '%schulungsraum%'
       AND lower(history.new_value) NOT LIKE '%team%raum%'
       AND lower(history.new_value) NOT LIKE  '%beratungsraum%'
       AND lower(history.new_value) != 'reserve'
)

I guess the performance is not the best calling "lower()" over and over. Also, as a programmer, my nails are rolling up seeing so much redundant code.
Unfortunately I did not find a nice way to use a variable or something.
(I want to add that I cannot simply add a new computed column, which would be a good way here, I'm only authorized to read data.)

Any advise to make the code smarter would be appreciable?


texts: How to restore truncated MySQL table from binary logs?

texts:
I have a mysql server and while working on it I accidentlly truncated the wrong table. I do have a mysql dump wich I immediately used to restore my database structure, but I still miss some entries in only one table.

SHOW BINARY LOGS shows me 64 logs.

How do I know wich log to restore and what is the procedure of restoring with mysqlbinlog? Using the last log is not changing anything ...

EDIT: I already checked http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html  but didn't accomplish anything

Would it be enough to just manually search all logs for the needed insert statements?

Thank you
Soda


texts: Is it normal for MySQL to gradually increase memory usage over time?

texts:
Is it normal for MySQL to increase memory usage over time? See image below of my server's memory usage over the last two weeks. After "service mysql restart" it drops to 40%. The database is used by a node.js app.

Server has 4GB of RAM with below additions to mysqld.cnf:
innodb_ft_min_token_size = 1
ft_min_word_len = 1
innodb_buffer_pool_size = 3G
innodb_buffer_pool_instances = 5
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_log_file_size = 128M
innodb_flush_method = O_DIRECT
max_connections = 300
long_query_time = 1
innodb_ft_enable_stopword = 0
Thank you!


texts: What's the best practice for representing set operations in a relational database?

texts:
I have a simple database representing collections of entities and sets of those collections. Sets contain multiple collections and collections contain multiple entities. Currently, when I query all the entities belonging to a given set I'm returning a union of all the entities in each collection belonging to that set. How could I redesign the schema and queries to allow for more flexible set logic? For instance given 4 collections, when I select all the entities in set 1, I want to get (collection 1 INTERSECT collection 2 EXCEPT collection 3) and when I query set 2 I want to get (collection 3 UNION (collection 2 INTERSECT collection 4)). In other words, I need a way to represent dynamic nested set operators in a relational database.

I can think of a few potential ways to accomplish this, suffice to say they are all non-trivial. What's the best practice for situations like this? Can anyone point towards any papers or articles on how to accomplish this? Thanks in advance.


texts:
I want to have Full-Text Search without the stemming - it feels like too much of a naive approach.
Essentially, what I want is something like:
select *, (
    select avg(levenstein_distance(d, q))
    from split(" ", :query_string) as q,
         split(" ", content.document) as d
) as search_rank from content

I want it to be blazing fast.
I still want to have my queries / documents split by spaces.
Would the GIN index on document still be relevant?
Is this possible? Would it be blazing fast?


texts: Two separate column counts grouped by one of those columns

texts:
I have a Voters table, which has fields including party and email.

party takes just a few acceptable values. I have a query which counts party membership and groups by the party:

SELECT party as Party, COUNT(*) AS Party_COUNT
FROM Voters
GROUP BY party
ORDER BY Party_COUNT DESC;

Works nicely.  I would now like to show additional data: how many of those party members have an email address recorded in the DB?

I should end up with something like the following:

Party   Party_COUNT EMail
DEM     90802       10000
REP     41383       5000
NPP     40000       4750
...etc.

I get the aggregate email count easily with:

SELECT COUNT(*) email from Voters WHERE email <> ''

but I'm having trouble mashing my two count queries together to get the desired tabular result.

This is in MariaDB 10.1.18.

I am counting WHERE email <> '' because I receive the dataset from a governmental agency which can't even provide the DDL for the database, just a CSV dump.

The field seems to have only NULLs or actual e-mail addresses, but I can't be sure, especially as this data is updated somewhat frequently.  Furthermore, CSV will not properly reflect NULLS, so empty strings are imported . . .


texts: Copy Table from One Database to another database in the same cluster?

texts:
Is there a way to copy table of one database to table of another database within the same cluster?

I have a cluster with multiple database.
I have a master Database and multiple slave databases .

I will change one table in master and i want to copy to remaining database.

Is there a way to do it via postgresql console?

I use the following from the bash.

pg_dump -h localhost -U myuser -C -t my_table -d first_db>/tmp/table_dump

psql -U myuser -d second_db</tmp/table_dump

I have to create a temporary dump file.

I use psycopg adapter to manage databases.
Here I am looking forward for some solution.


texts: reading input parameter values in stored procedures

texts:
I'm using the current version of MariaDB 10.0.12 .

I created a stored procedure as specified in the accepted answer for the following question:

https://stackoverflow.com/questions/2950054/let-mysql-users-create-databases-but-allow-access-to-only-their-own-databases

In order to do that, I logged in as root using the mysql-client and typed in the following commands:

-> create user 'myUser'@'localhost';
-> create database myStoredProcedures;
-> grant execute on myStoredProcedures.* to 'myUser'@'localhost';
-> use database myStoredProcedures;
-> delimiter //
-> CREATE
->   DEFINER = CURRENT_USER
->   PROCEDURE myuser_create_db (IN dbName VARCHAR(255))
->   SQL SECURITY DEFINER
->   BEGIN
->     CREATE DATABASE dbName;
->     GRANT ALL PRIVILEGES ON dbName.* TO 'myUser'@'localhost';
->   END;
-> //
-> delimiter ;

If I log in as myUser@localhost, use the database myStoredProcedures, and then call the stored procedure:

call myuser_create_db('testit');

The database dbName is created and I can drop it as myUser. However the database' name is literally 'dbName' and not 'testit'! Why is this the case and how can I fix this?

( And now that I think about it ... would it be possible to use sql injection here? Is it possible to call myuser_create_db with a parameter that contains sql commands within the new db name? That would be bad. But I cannot test this sql injection idea now, because MariaDB interprets dbName as the string 'dbName'. )

Thanks.


texts: Does DBCC TRACEON() invalidate cached execution plans?

texts: In Microsoft SQL Server, does DBCC TRACEON() invalidate cached execution plans, same as SP_Configure invalidates all stored procedure plans ?


texts: PostgreSQL - Update not working inside function

texts:
I am creating a web app that handles coupons. When a user claims a coupon the coupon batch count "should" be updated, and a new coupon is created.

For some reason the UPDATE query does not work inside my PostgreSQL function. Everything else works fine.

I have a coupon batch:

CREATE TABLE public.batch (
    batch_id integer NOT NULL,
    template_id integer NOT NULL,
    created_count integer NOT NULL,
    claimed_count integer NOT NULL,
    redeemed_count integer NOT NULL,
    start_date date NOT NULL,
    expiry_date date NOT NULL,
    status_id integer NOT NULL
);

When a coupon is "claimed" from a batch a new record is created in coupons:

CREATE TABLE public.coupons (
    coupon_id integer NOT NULL,
    code character varying(32) NOT NULL,
    customer_id integer NOT NULL,
    batch_id integer NOT NULL,
    is_redeemed boolean NOT NULL,
    date_claimed date NOT NULL,
    date_used date,
    redeemed_discount integer,
    purchased_item character varying(30)
);

The postgres function to claim a coupon from a batch:

/*Claim a coupon */
CREATE OR REPLACE FUNCTION claim_from_batch(businessID INTEGER,batchID INTEGER, customerID INTEGER)
    RETURNS INTEGER AS $$

DECLARE
    couponBatchID INTEGER;
    newCouponID INTEGER;
BEGIN
    PERFORM update_batch_status();

   /*Check if the batch is available to the customer*/
    SELECT INTO couponBatchID batches.batch_id
    FROM get_customer_available_batches(customerID) as batches
    WHERE batches.batch_id = batchID
    AND batches.business_id = businessID;

    IF couponBatchID IS NOT NULL THEN
        RAISE NOTICE 'success';
        /*update the coupons_claimed value*/
        UPDATE batch SET claimed_count =  cp.count
        FROM (SELECT COUNT(coupon_id) FROM coupons WHERE batch_id = batchID) AS cp
        WHERE batch_id = batchID;

        /*Create the coupon*/
        INSERT INTO coupons(code,customer_id,batch_id,is_redeemed,date_claimed)
        VALUES('PLACEHOLDER', customerID,batchID,false,NOW())
        RETURNING coupons.coupon_id INTO newCouponID;
         IF newCouponID IS NOT NULL THEN
            RETURN newCouponID;
         END IF;
    ELSE
        RAISE NOTICE 'failed';
        RETURN -1;
    END IF;
END $$ LANGUAGE 'plpgsql';

The strangest thing for me is that when I run the update query with a hard-coded batch_id number, outside the function, in the query builder of pg-admin, the value gets updated correctly.

I tried multiple variations of this and and searched through the internet,
I also tried separating the update to a separate function and call it inside the claim function. Nothing works.

I can't debug the reason why this is not working. I am still a CS student so excuse any other obvious mistakes I might have missed.

I tried changing the update portion of the function to:

UPDATE batch
 SET claimed_count = (SELECT COUNT(coupon_id)
                      FROM coupons
                      WHERE batch_id = batchID)
 WHERE batch_id = batchID;

P.S I am not sure if Updating the claimed_count value like this, is the best approach, since the value simply increases by one (1). But it made sense because if I later wanted to (possibly) return a coupon to the batch, I would need the value to be updated.

Thank you for your time.


texts: MongoDB only three of four shards being used

texts:
Using MongoDB 2.6, I've setup four replication sets: a, b, c & d:

>rs.conf()

a:PRIMARY> rs.conf()
{
    "_id" : "name_of_replset",
    "version" : 1,
    "members" : [
        {
            "_id" : 0,
            "host" : "fedora_cje:port_number"
        },
        {
            "_id" : 1,
            "host" : "fedora_cje:port_number"
        },
        {
            "_id" : 2,
            "host" : "fedora_cje:port_number"
        }
    ]
}

Those replication sets have been added to shards

>sh.status()

 shards:
    {  "_id" : "a",  "host" : "a/fedora_cje:27000,fedora_cje:27001,fedora_cje:27002" }
    {  "_id" : "b",  "host" : "b/fedora_cje:27100,fedora_cje:27101,fedora_cje:27102" }
    {  "_id" : "c",  "host" : "c/fedora_cje:27200,fedora_cje:27201,fedora_cje:27202" }
    {  "_id" : "d",  "host" : "d/fedora_cje:27300,fedora_cje:27301,fedora_cje:27302" }

At the bottom of sh.status(), I am seeing this message

{  "_id" : "test",  "partitioned" : false,  "primary" : "d" }
{  "_id" : "test_collection",  "partitioned" : false,  "primary" : "d" }

but test and test_collection don't exist in the test_cluster database.

Inserting records, the fourth replication set and fourth shard are apparently ignored.

test_cluster.test_cluster_collection.stats()

"nchunks" : 3,
"shards" : {
    "a" : {...
    "b" : {...
    "c" : {

Notice also that the number of chunks remains at 3.

I am using the default /etc/mongod.conf file.

This is the insert statement being used:

>use test_cluster
>t=db.test_cluster.test_sharded_collection
>for( var i=0; i < 20000; i++ ){t.insert( { x:i,y:3,z:"test                       test" } )}

Question: Why is the fourth replication set and consequently the fourth shard being ignored during inserts?


texts: MySQL high availability, failover and replication with Latency

texts:
We are in the process of implementing a new CMS (Drupal 6.x) that runs on MySQL. We have two data centers -- primary and secondary -- with known latency between them. We're unsure which version of MySQL we will run ... either Community or Enterprise, but that is a TBD. Looks like we will be running the InnoDB engine, OS is going to be RedHat EL 5.5 The primary servers are going to be active, while the secondary will be passive or hot stand-bys.

I would like to implement replication, high availability and automated failover in MySQL across the two data centers.

After a failover to the secondary servers, when we fail-back to the primary servers, we'd like to have data synched from the secondary DB to the primary DB quickly and completely so that we can continue to serve content from the primary servers.

I am interested in knowing what technologies/tools/best practices can be used to solve/address these issues. Also, any gotchas or ah-ha moments would be much appreciated as well. I've read up on MySQL replication, clustering, and at some 3rd party tools like Tungsten and Dolphinics, but I a unsure what the is best course of action.

Thank you for your time!

KM


texts: Benefits of using Staging Database while designing Data Warehouse

texts:
I am in process of designing a Data Warehouse Architecture.
While exploring various options to Extract data from Production and putting into Data Warehouse, I came across many articles which mainly suggested following two approaches -

  Production DB ----> Data Warehouse (Star Schema) ----> OLAP Cube
  Production DB ----> Staging Database ----> Data Warehouse (Star Schema) ----> OLAP Cube

I am still not sure which one is the better approach in terms of Performance and reducing processing load on Production database.

Which approach you find better while designing Data Warehouse ?


texts: When is it appropriate to use a scalar valued function?

texts:
I've just read several articles that were critical of scalar functions because of performance issues. These articles usually point to using table functions instead. Under what circumstances would it be appropriate to use a scalar function?

Here is a simple example.  In SQL Server, we have 5 parts of a name (prefix, first, middle, last, suffix) that come from a person table.  In addition to having a column for each name part, we also would like to have a column that concatenates them.  In order to be consistent and to save on some typing, we are considering creating a function to create a "full name" string and putting the results in a separate column.


texts: MySql INNER JOIN doesn't use composite index

texts:
I have two tables, table A which contains date and name of table B entry and table B which contains its name and type. Table a has a composite index on table_b_name and date. I'm selecting all table A entries which are connected to table B having certain type.

The question is why the composite index is not being used?

Hede is the DDL and selects:

CREATE  TABLE tbl_a (
  id INT(11) NOT NULL AUTO_INCREMENT ,
  tbl_b_name VARCHAR(64) NOT NULL ,
  date TIMESTAMP NULL DEFAULT NULL ,
  PRIMARY KEY (id) ,
  INDEX idx_1 (tbl_b_name, date ASC)
)
ENGINE = MyIsam
DEFAULT CHARACTER SET = utf8;

CREATE  TABLE tbl_b (
  id INT(11) NOT NULL AUTO_INCREMENT ,
  name VARCHAR(64) NOT NULL ,
  type VARCHAR(64) NOT NULL ,
  PRIMARY KEY (id)
)
ENGINE = MyIsam
DEFAULT CHARACTER SET = utf8;

INSERT INTO tbl_b (name, type) VALUES ('name1', 'type1');
INSERT INTO tbl_b (name, type) VALUES ('name2', 'type1');
INSERT INTO tbl_b (name, type) VALUES ('name3', 'type2');
INSERT INTO tbl_b (name, type) VALUES ('name4', 'type2');

INSERT INTO tbl_a (tbl_b_name, date) VALUES('name1', '2000-01-01');
INSERT INTO tbl_a (tbl_b_name, date) VALUES('name1', '2010-01-01');
INSERT INTO tbl_a (tbl_b_name, date) VALUES('name2', '2000-01-01');
INSERT INTO tbl_a (tbl_b_name, date) VALUES('name2', '2010-01-01');
INSERT INTO tbl_a (tbl_b_name, date) VALUES('name3', '2000-01-01');
INSERT INTO tbl_a (tbl_b_name, date) VALUES('name3', '2010-01-01');
INSERT INTO tbl_a (tbl_b_name, date) VALUES('name4', '2000-01-01');
INSERT INTO tbl_a (tbl_b_name, date) VALUES('name5', '2010-01-01');

/* index is not used */
EXPLAIN
SELECT a.*
FROM tbl_a a
INNER JOIN tbl_b b
ON b.name = a.tbl_b_name AND b.type = 'type1';

/* index is not used */
EXPLAIN
SELECT *
FROM tbl_a
WHERE tbl_b_name IN (SELECT name FROM tbl_b WHERE type = 'type1');

/* index is used! */
EXPLAIN
SELECT *
FROM tbl_a
WHERE tbl_b_name IN ('name1', 'name2');

SqlFiddle


texts: How to log custom messages from inside a PostgreSQL transaction?

texts:
I have a software that generates transactions and executes them. I would like to asynchronously inspect about the transaction progress in term of the simple proportion of queries executed over total number of queries. I'm able to read both stdout and stderr generated by psql, so I was thinking that a solution could be printing to stdout some custom messages like "Progress: 3/8" (or another custom text string).

I've thought about executing a "logging query" which stores in a proper table the informations about query progress, but this table wouldn't be available until the transaction completes, making it unuseful for me to inspect transaction progress.

At the moment I tried the following (suppose to have 3 queries in the transaction which do some stuff):

BEGIN;
CREATE OR REPLACE FUNCTION progress(curr int, total int) RETURNS float AS $$
BEGIN
  RAISE NOTICE '___PROGRESS___%', curr/total::float;
  RETURN curr/total::float;
END;

-- First query
SELECT ... FROM ...;
-- Log the progress
SELECT * FROM progress(1,3);

-- Second query
SELECT ... FROM ...;
-- Log progress
SELECT * FROM progress(2,3);

-- Third query
SELECT ... FROM ...;
-- Log progress
SELECT * FROM progress(3,3);

COMMIT;

I execute the script from a bash file using the following syntax:

res=$((psql -U username -d db -h localhost --log-file=plog.log -f "the transaction above") 2>&1>clog.log)

What I get from a transaction like the one above using the previous bash command are three outputs: plog.log, clog.log and $res bash variable. I'll post some extract of them to make it clear what's their content.

plog.log

....
********* QUERY **********
SELECT * from progress(1, 3);
**************************

     progress
-------------------
 0.333333333333333
(1 row)

...
********* QUERY **********
SELECT * from progress(2, 3);
**************************

     progress
-------------------
 0.6666666666666667
(1 row)

...
********* QUERY **********
SELECT * from progress(3, 3);
**************************

     progress
-------------------
 1
(1 row)

clog.log

...
     progress
-------------------
 0.333333333333333
(1 row)

...
     progress
-------------------
 0.6666666666666667
(1 row)

...
     progress
-------------------
 1
(1 row)

$res variabile (psql return value)

psql:/path/to/script.sql:11: NOTICE:  ___PROGRESS___0.333333333333333
psql:/path/to/script.sql:16: NOTICE:  ___PROGRESS___0.666666666666667
psql:/path/to/script.sql:21: NOTICE:  ___PROGRESS___1

The problem is that I can't real-time access the content of $res variable. I can instead access the content of clog.log and plog.log, so the usage of RAISE NOTICE doesn't make sense at all because it doesn't appear in both clog.log and plog.log but only in $res.

Should I stick to parse clog.log or plog.log in order to find the SELECT output value from the progress() function (removing the RAISE NOTICE statement in that function because of it's uselessness) or there's an alternative method? Am I missing something?

Thank you


texts: Which method gives better insert performance in mysql db table: CSV import or insert statement

texts:
I have combine data from two tables which results in 6727224 rows. I want to insert this data into another mysql db table. Which method should be very fast to insert this number of records. Insert query or CSV file import?

If CSV one is fast, then i will have to first loop through results and insert it into the csv file.

If insert one is fast, i will create one dictionary or array and will loop through it to insert data in one open connection.

Which way is best to do it in short time?

Thanks.


texts: Can't purge old kernel due to unmet MySQL dependencies

texts:
I am trying to purge unneeded files from my ubuntu 16.04 server as the root partition is 99% full. Somehow there is a problem with unmet depencencies regarding mysql:
sudo apt-get autoremove --purge
Reading package lists... Done
Building dependency tree
Reading state information... Done
You might want to run 'apt-get -f install' to correct these.
The following packages have unmet dependencies:
 mysql-server-5.7 : Depends: mysql-server-core-5.7 (= 5.7.31-0ubuntu0.16.04.1) but 5.7.32-0ubuntu0.16.04.1 is installed
E: Unmet dependencies. Try using -f.

How can this be fixed?


texts: Is it normal to constantly have 30+ connections to ‘master’ database in MS SQL Server 2012?

texts:
In troubleshooting why I was seeing so many connections to one of our internally used databases in SQL Server 2012.

A query I found online that had been helpful to me was this:

 SELECT DB_NAME(sP.dbid) AS the_database
         , COUNT(sP.spid) AS total_database_connections
 FROM sys.sysprocesses sP
 GROUP BY DB_NAME(sP.dbid)
 ORDER BY 1;

However, although after fixing an issue, I've seen connections to our own custom database go back to normal levels, I'm always seeing the number of connections to the master database always at over 30 connections.

Is this normal for SQL Server 2012?  Anyone else getting 30 or more connections to master when running this query?


texts: what could make examined_rows change radically?

texts:
I have a mysql-based application which sometimes takes forever to execute a select statement. When this happens, the statement appears naturally in my slow query log:

# Query_time: 51.826420  Lock_time: 0.000143 Rows_sent: 3  Rows_examined: 15574557

Rows examined above tells me that a full table scan occured. However, normally, the same query examines at least one order of magnitude less rows. Namely, performance_schema.events_statements_history tells me exactly 982937. Naturally, the execution time for this query is one order of magnitude smaller in this case.

I checked that explain on this query appears to give the same result in both cases. I also checked that if I add a USE INDEX statement when this problem occurs, the execution time goes down back to normal but, normally, I do not need to add the USE INDEX.

One of the queries that exhibit this behavior is shown below:

SELECT
     COUNT(DISTINCT(t.id)),
     t.tag_3 AS group_0
FROM
     tasks AS t
WHERE
         t.message_time >= FROM_UNIXTIME(1486508400)
     AND t.message_time  < FROM_UNIXTIME(1487113200)
     AND (   (t.type = 12 AND t.site_id = 172)
          OR (t.type = 1  AND t.site_id = 172)
          OR (t.type = 8  AND t.site_id = 173) )
     AND t.tag_1 IN (74,75,76,77,78,79,80,81,99,263)
     AND t.tag_3 IN (302,303,305)
     AND t.tag_4 IN (315,316,317,318,319,320,321,322,323,351,352,357)
     AND t.site_id IN (172,173)
GROUP BY
     group_0;

The explain output:

+----+-------------+-------+-------+-----------------------------------------------------------------------+----------+---------+------+-------+----------------------------------------------------+
| id | select_type | table | type  | possible_keys                                                         | key      | key_len | ref  | rows  | Extra                                              |
+----+-------------+-------+-------+-----------------------------------------------------------------------+----------+---------+------+-------+----------------------------------------------------+
|  1 | SIMPLE      | t     | range | IX_site_id,IX_site_id_type,IX_tag_1,IX_tag_3,IX_tag_4,IX_message_time | IX_tag_4 | 5       | NULL | 24732 | Using index condition; Using where; Using filesort |
+----+-------------+-------+-------+-----------------------------------------------------------------------+----------+---------+------+-------+----------------------------------------------------+

All tables use innodb engines.

What could trigger such different behaviours at different points in time ? Namely, what could have caused a full table scan ?


texts: How to force SQL server to use row locking for specific update/delete statements

texts:
I read this great response to a similar question How to force the use of row locks but I was looking for a per update/delete statement instead of permanently putting the rowlock restriction on an index.

My problem is that I have multiple sessions running the same scripts and updating the same tables giving us some deadlocks...  And we know for a fact that there will never be any conflicting rows between different sessions. The problem seems to be caused by page locking (when a page contains data that confilcts between 2 sessions) I've tried using my update and delete statements WITH (ROWLOCK) hints but when analyzing the locks (starting a transaction and statement and then verifying sp_lock in another session), I see absolutely no difference between specifying and not specifying the WITH (ROWLOCK) hint.

any ideas to resolve this issue are welcome.

Edited for more info:
I ran different scripts testing different scenarios with and without the "With(ROWLOCK)"... the test scenarios I've tested are:

-- deleting 9 rows of 4600 - no difference between with or without rowlock hint
-- 9 row locks and 3 page locks
-- COL1 is part of the combined PK
-- DELETE FROM RPT_CHK WHERE COL1='RPT_LAST'
-- DELETE FROM RPT_CHK with(rowlock) WHERE COL1='RPT_LAST'

-- updating 16000 rows - no difference between with or without rowlock hint
-- table lock in both cases (which is understandable)
-- update price with (rowlock) set cost = cost + 0.05
-- update price set cost = cost + 0.05

-- no difference between with or without rowlock hint
-- 3833 rows -- 4028 locks (row and page Locks)
-- update client set COL1 = 'a' where ClientId <= 3833
-- update client with (rowlock) set COL1 = 'a'  where ClientId <= 3833


texts: Install MariaDB without password prompt in Ubuntu

texts:
I need to install MariaDB server in Ubuntu without password prompt. For this purpose, I execute the following commands, but it doesn't gave me any progress. It again shows password prompt.

I used the following commands :

  export DEBIAN_FRONTEND=noninteractive
  echo mariadb-server-5.5 mariadb-server/root_password password mypass | debconf-set-selections
  echo mariadb-server-5.5 mariadb-server/root_password_again password mypass | debconf-set-selections
  sudo apt-get -y install mariadb-server

I also check some links in stack overflow, but it doesnot worked:

https://stackoverflow.com/questions/8138636/install-mysql-on-ubuntu-natty-without-password-prompt-and-using-shell-variable-a

https://stackoverflow.com/questions/7739645/install-mysql-on-ubuntu-without-password-prompt

Please help me on this regard. Have any wrong with my code?


texts: Current SQL Server services configuration

texts: I know I can use SQL Server Configuration Manager to determine 1) Which services are currently running and 2) which services are set to auto start. However, I have several dozen servers I manage. I'd like to run a quarterly check to make sure all services are still set to auto start and to see which services are currently on. Is there a table or procedure that will give me this information?


texts: DBCC SHRINKFILE (N'XYZ' , EMPTYFILE) using single core

texts:
Is there any way to force DBCC SHRINKFILE to use more than one core? We're running SQL Server 2014 on a hex core machine - and SHRINKFILE is using one hyper threaded core. I/O is at a small fraction of capacity, and WAIT_TYPE is persistently 'SOS_SCHEDULER_YIELD'. Our database is 1TB so this is going to take days currently.

I've seen mentions of using index rebuilds or moving table to a different file group - but is there any way to do this without having to (a) actually move everything to a different file group (as opposed to just other files within the file group) or failing that (b) having to do this manually for every table in the db?


texts: Looking for a book on optimizing tables and queries for handling time sequence data

texts:
I once had an electronic copy of what I understood to be a classic work on optimizing tables and queries for handling timestamped data.  It was old-ish when I found it 6-8 years ago.  I was sure I also printed and bound a copy but that disappeared in the move when I left my last job.

It was DBMS-agnostic and may be obsolete if standard SQL features and/or various vendor extensions have filled the hole it addressed.  However, now that I want it, I can't find it.

As an example of a question it helped answer was: if you record values only on change so your table says it was 75 degrees at noon and went up to 76 at 2pm, what was the temperature at 1pm?

I am not looking for answer to that question but I've Googled and Googled trying to find a new electronic copy and haven't come close.  Does this sound familiar to anyone here?  I'd appreciate any pointers.


texts: How to audit database user password changes in oracle 11g?

texts: I want to record how many times or which user accounts passwords were changed using Oracle database auditing techniques. I also want to record any password changes done to system user account.


texts: Only one truthy bool value for one foreign key in postgre

texts:
Lets assume we have 2 database tables (entites).
The first is named A and the second one is named B.
There are 1:N (where N > 0) relationship (so every record in table A is in relation with 1+ records in B table).

Table A (for simplicity assume there is just id in this table, other table columns are not relevant for this question)

Table B

id (bigint) <- primary key
defaultForA (boolean, default = false)
a_id (bigint) <- foreign key for table A record

Is it possible to set table contraint that will check there is only one truthy value in defaultForA column for every present a_id value?
For example we have 2 records in table A (with IDS 0,1)

And 4 records in table B

id, defaultForA,a_id

0,true,0

1,false,0

2,true,1

3,false,1

This is completely fine state. For each A record I have one "default" value from B table
The constraint should not allow me to add following record to table B

4,true,0

Because there is alredy record linked to record 0 in table A that have defaultForA property set to true


texts: Recommendations for a SQL Server 2014 Database Administrators Course

texts:
I am after some personal recommendations for a SQL Server 2012 Database Administrators Course with exams.

Exam 70-461: Querying Microsoft SQL Server 2014
Exam 70-462: Administering Microsoft SQL Server 2014 Databases
Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2014

I have done previous training with

•   Training Camp (FireBrand) this course they want £ 5,275 (6,330 Inc.
   VAT) this is too costly, yes I know they give Food and accommodation
   and exams. Good but currently out of my budget.

•   Learning tree this was designed to cover everybody was diluted a bit and the
   certification was their own.

•   Parity Training now have gone bust.

•   StayAhead training they mainly do Oracle but not bad.

I am after somewhere quality in that includes instructor lead training and exams the closer to Oxford (UK) the better.

I know this info will become out of date but will be useful as a guide for others, so will close in May 2014.


texts: Can I create a database on a local folder mapped as a drive

texts:
I have mapped a local folder as a drive, as follows:

subst E: c:\MyFolder

I need to create a database on that drive, but it fails:

CREATE DATABASE MyDb
ON(NAME=MyDb_dat,FILENAME='E:\MyDb.mdf',SIZE=30,MAXSIZE=45,FILEGROWTH=1)
 LOG ON(NAME=MyDb_log,FILENAME='E:\MyDb.ldf',SIZE=10,MAXSIZE=20,FILEGROWTH=1)

Here is the error message:

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\MyDb.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Restarting the service did not help at all. How can I have SQL Server create a database on the mapped folder?

Edit: why do I need this.

This database is a local Dev Edition sandbox to run automated tests against. Historically, we used to create such database on RAMDisk, so that tests run fast. Currently we are getting faster workstations with SSD drives. When everyone have them, we shall do away with RAMDisk altogether, and just create the sandbox on C: drive.

For now, while some team members still have to use RAMDisk, I'd like to create my sandbox on E: drive, without setting up RAMDisk. This is why I tried subst.

Can suggest a better approach?


texts:
Though DBCC DBINFO() is undocumented, but we all know that

  dbi_crdate: Database creation datatime
  dbi_dbccLastKnownGood: the
  completion time of the last 'clean' run of DBCC CHECKDB

For one of my database its showing following output.

Why output of DBCC DBINFO() showing smaller value for dbi_dbccLastKnownGood as compare to dbi_crdate. How can someone execute DBCC CHECKDB before a database is created ?


texts: DBCC CheckDB run into early hardware failure

texts:
Our production database server (SQL 2008 R2 Enterprise on Windows Server 2008 R2 DC) run into a strange error during its nightly DBCC CheckDB (Prod_DB) since late June. The same job had been running fine up then.

I have restored the full backup from this server to a test server, and the data file seems ok, and the DBCC CheckDB returned no error there.

I applied this hotfix last week but did not get the issue resolved.

https://support.microsoft.com/en-us/kb/967351/en-us

The message sounds serious enough, however, the application is otherwise functioning normally. So I am not sure what I am having here.

It will be grateful if any one could share any advice.

Here is the error message from SQL agent job history:

Date                      7/28/2015 4:02:00 AM
Log                         Job History (DBCC Check DBs)

Step ID                 1
Server                   XXX
Job Name                            DBCC Check DBs
Step Name                         check dbs
Duration                              00:12:37
Sql Severity        17
Sql Message ID 823
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted          0

Message
Executed as user: XXX. During undoing of a logged operation in database

'Prod_DB', an error occurred at log record ID (21235:2462484:113).
Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
[SQLSTATE 42000] (Error 3314)  A database snapshot cannot be created because it failed to start.
[SQLSTATE 42000] (Error 1823)  A database snapshot cannot be created because it failed to start.
[SQLSTATE 42000] (Error 1823)  The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
[SQLSTATE 42000] (Error 7928)  The database could not be exclusively locked to perform the operation.
[SQLSTATE 42000] (Error 5030)  Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
[SQLSTATE 42000] (Error 7926)  The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x00001a68024000 in file 'M:\Datab\Prod_DB.mdf:MSSQL_DBCC9'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
[SQLSTATE 01000] (Error 823)  The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x00002fd543e000 in file 'M:\Datab\Prod_DB.mdf:MSSQL_DBCC9'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
[SQLSTATE 01000] (Error 823).  The step failed.


texts: Can I tell SQL Server not to place data in the buffer cache?

texts:
One of the servers I support has an ETL job that runs every morning. This isn't an SSIS package, just a series of stored procedures executed via SQL Server Agent. The job takes several hours to run, and regularly brings the server's page life expectancy down under 100, which isn't surprising as it hits pretty much every table in the main operational database.

Most of the data it's reading isn't needed during regular operational use, so it's just wasting space in the buffer cache until the job is done and the cache can start filling back up with data that's actually useful. Is there any mechanism through which I can tell SQL Server not to replace what's in the buffer cache when running these stored procedures?


texts: Dynamic Creation of Table using Stored Procedure Without a Use of OpenRowSet in SQL Server

texts:
Q.--> An alternative of OpenRowSet in SQL Server

Description:

I have created a procedure which uses Dynamic PIVOT to get a result. I have a table which contains column DateRange with values Jan-2014 Feb-2014 Mar-2014 etc...

Using dynamic query in PIVOT, I am able to get the desired output by passing columns from  table. The @Columns variable is set by using Stuff function with FOR XML Path.
By passing this @Columns variable into dynamic sql query for PIVOT I am able to achieve the following:

Result table:

Col1   | Jan-2014  | Feb-2014 | ...............
-------------------------------------------------------
ABC    | 123       |11231     | ...............
ASDF   |123123     |123123    | ...............

Now I have to create a physical table which will be having a result from the above procedure as shown in the table. As I mentioned the procedure is containing dynamic query, it is having EXECUTE. Because of which I am not able to create a table inside the procedure.

I tried to create a view as well but that also didn't worked as my procedure contains variable declaration.

To achieve this I used OpenRowSet in a script that runs on a daily basis.

My problem is: I cannot use OpenRowSet, and I am not able to create a physical table from a stored procedure. The OpenRowSet contains connection string that is why I cannot use it.

Please suggest...

EDIT 1:

My procedure for creating dynamic PIVOT table:

DECLARE
        @Columns AS NVARCHAR(MAX)
        ,@Query AS NVARCHAR(MAX)

SELECT @Columns = STUFF((SELECT DISTINCT ',' + QUOTENAME(DateRange)
                         FROM View
                         ORDER BY ',' + QUOTENAME(DateRange)
                         FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'), 1, 1, '')

SET @Query = 'SELECT Col1, Col2, ' + @Columns +
             ' FROM (SELECT Col1, Col2, [DateRange], Col3 FROM View) A
               PIVOT
                 (SUM(Col3) FOR DateRange IN (' + @Columns + ') ) S '

Execute (@Query)

and script for creating a physical table

SELECT *
INTO Physicaltable
FROM
    OPENROWSET('SQLOLEDB', 'ConnectionString',
               'SET FMTONLY OFF;SET NOCOUNT ON;EXECUTE (AboveProcedure)')


texts: How to script permissions for all logins/users/tables/schemas

texts: Over the years, I have used countless scripts and applications to generate all permissions for SQL Server databases. I have used both paid and free tools. I feel that not one of them seems to be accurate in every instance. I am simply looking for a way to generate all permissions for a given database along with confidence that the script is correct and able to be used after a backup/restore from another instance. Does anyone have a method they have confidence in and use regularly


texts: can't connect to remote postgresql database

texts:
I'm trying to connect to a remote psql database.  Before I added the pg_hba.conf entry with the client's IP address, I was getting an error message :

xdev@xdevbox:~$ psql -U postgres testdb -h 10.1.1.47
psql: FATAL:  no pg_hba.conf entry for host "10.201.50.71", user "postgres", database "testdb", SSL off

I added the client's IP with trust settings.  I also changed the listen address in postgres.conf on the server to listen to "*".
Then I restarted the database server using /etc/init.d/postgresql restart command.

Now when I try to connect, I get the following error message:

psql: could not connect to server: Connection refused
    Is the server running on host "10.1.1.47" and accepting
    TCP/IP connections on port 5432?

in postgresql.conf, the port is set to 5432.
I'm not sure what else to check.

Thanks


texts: SQL Drive getting full delete millions of rows

texts:
I'm looking for the easiest way to accomplish this in the limited hard drive space that I have.

I have a SQL drive that is dangerously close to getting full.  We have about 15 tables that have over 20 million rows in them (these are high read/write tables on a 24/7 database).

After running a few queries, it's determined that we can deleted about 5 million rows each from the 15 tables.

What is the safest way to delete all these rows from the tables without causing the log file to fill the rest of the hard drive space?


texts: Should I split this large table into three smaller tables?

texts:
I'm designing a PostgreSQL database that will contain information about photos uploaded by my users.  All users will have at least one main photo and optionally one or more public and/or private photos.  My first schema is as follows:

user
----
id (PK)

photo
-----
id (PK)
user_id   (FK to user)
photo_id  (unique identifier such as "aK1q9")
type      ("main" or "other")
access    ("public" or "private")

The query that will get run the most is:

SELECT p.photo_id FROM photo p INNER JOIN user u ON p.user_id = u.id WHERE p.type = 'main' AND u.id = (some user id);

The next most popular query will be:

SELECT p.photo_id FROM photo p INNER JOIN user u ON p.user_id = u.id WHERE p.type = 'other' AND p.access = 'public' AND u.id = (some user id);

The problem I forsee is that the Photo table will become extremely large over time as it will contain all public and private photos uploaded by all users.  Since my most popular query will only be looking for the main photo IDs, would it make more sense to break my photo table up into three tables?

main_photo
----------
id (PK)
user_id   (FK to user but in a one-to-one relationship to user)
photo_id

other_public_photo
------------------
id (PK)
user_id   (FK to user)
photo_id

other_private_photo
-------------------
id (PK)
user_id   (FK to user)
photo_id

I would think this latter schema would be preferable because 1) each photo's type and access information is made explicit by where it's stored thus eliminating additional ANDs in my queries; and 2) my queries would run faster since they would be run against one of the three smaller tables rather than one huge table.  Which is the optimal approach from a performance standpoint?

Thanks.


texts:
We recently moved SQL Server to SSD and the performance gain is not that great.

SQLIO shows enormous gains (15-100 times faster), getting sequential read/write speed up to 6GB/s.

But when we do a simple query:

select *
into table2
from table1

it gets roughly like 60MB/s. 2GB table takes 30 seconds to finish.

Is this normal? What speed is considered average for SSD for such a simple query?
Don't believe it's a RAM issue, 50GB of RAM..?


texts: Convert existing table to partitioned table - SQL Server 2017

texts:
I have a large busy database where 99.9% of data lives in one table. IOT devices report their time series data into this table.

This table has 7 billion rows and takes up around 3.5 TB of space. The database is running out of space on its current drive.

I have another drive available that I would like to start saving new data for this one table.

The challenge here is doing all this with no, or very little down time (max 30 minutes). I need a strategy to save this new data.

Here is idea 1:

Create new table called IotSensorData_New which is same as the main table, but has partitioning turned on and lives on new drive (the ID identity seed is a concern here)
Rename current large table to IotSensorData_Old, then rename empty IotSensorData_New to IotSensorData
Turn on data importers so data starts ingesting into this new table (again ID is concern here as it will start from 1)
Convert the old IotSensorData_Old data into the current table - I'm unsure exactly how to do this bit

Here is idea 2:

Turn off data importers
Convert the current table to partitioning (I understand indexes need to be rebuilt if I do this which would probably take over 24 hours)
Turn on importers

I'm looking for exact steps on how to do this with the least downtime

I'm using SQL Server 2017 Enterprise edition.


texts: What is this type of query called, and how can I formulate it?

texts:
I have following tables:

  events(event_id, name, description)

  groups(group_id, event_id, title, description)

  event_group(event_id, group_id)

  users(user_id, name, email)

  event_user(event_id, user_id)

  group_user(group_id, user_id)

As an example, how can I return the users who belong to a certain group which belongs to a certain event?


texts: what is a good use case for splitting user table by role

texts:
I have a requirement where there are primarily 5 roles in the system. Each of these roles increase in hierarchy from junior to senior most role. Each junior role is a subset of its immediate next senior role in terms of access privileges. The roles and their hierarchy in increasing order would be J1-J2-S1-S2-SuperAdmin.

Each role needs to store additional information about the user that is only specific to that role for eg. J1 needs to store grade and location, whereas J2 will only need to store grade. S1 and S2 that manages J1 &J2 needs to store neither.

In a nutshell, its all about separating the admin role users from the non admin role users. The admin roles have a different set of fields which need to be mandatory and the general users who are not admin need to have a separate set of fields that need to be mandatory. For eg. First name, last name are mandatory for all users. Whereas location is only mandatory for non admin users. IF I keep all users in one table, then I cant enforce allowing nulls in certain fields for a certain role while disallowing it for other roles. Also due to admin users being far fewer than non admin users, the data access for admin users at login time could be faster for users belonging to admin roles? There is also the possibility of feilds being added or removed changes being made as which fields are mandatory or non mandatory for non admin users.

I am seeking opinions if anyone has encountered such a situation before and if anyone recommends splitting up tables by roles for such reasons. In such a scenario as mentioned above, what are the arguments that favor storing each role's users in a separate user table? Wont I need to scan each table and find which table the user is in before I authenticate the user? Would there be any specific problem if all such users are stored in the same table? What are the pros and cons of either approach? if I seem to be favoring such a design as mentioned above its only because I'm playing the devil's advocate.


texts: Can I specify the default backup location for non-automatic backups?

texts:
I noticed that when the BACKUP DATABASE command is issued without the TO clause the backup file is stored to the working directory where the command is issued.

I'm wondering if it's possible to configure a default location for backups such that it's used when the location is not specified in the command.

What if the free space on the storage is not enough? Is there a setting to tell DB2 to automatically remove the oldest backup as for circular logging?


texts: SQL query failing because of commented part of T-SQL query

texts:
I am executing below query but I am facing issue.

Note :SQL 2008 R2 and SQL 2014 running with two diff serv accounts on Win 2008 R2 and Win 2012 R2. TCP Ports, Firewall ports are all enabled along with remote connections and I am able to execute the code on local machines (Ex: If I connect to SQL 2008 R2 machine and execute on SQL 2008 R2 Successful, If I connect to SQL 2014 Machine Successful on SSMS on SQL 2014.Issue is only when I connect to SQL 2008 R2 and execute on SQL 2014.When I remove the commented part I am able to execute the code successfully using SQLCMD.) I am not aware of any compatibility issues of this case.

  Msg 121, Level 20, State 0, Line 0
  A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

But when I removed the commented part and execute the query it is running successfully in fraction of seconds. Please let me know what is wrong with the commented part. I have pasted the T-SQL  query below.

/********************************************************************************
* Summary: Creates source database schema for BCP Import. Only created PK on the tables
* No other constraints are replicated
* Parameters:
* tempdbname
* Called by: PROCS.uspEngagement_Import
* Remarks:
* Copyright: Copyright 2012 by XXXXX XXXXX. All rights reserved.
* Author: XXXXX
* Creation Date: July 07 2012
* Revision History:
* <Modified By> <Date Modified> <Changes made>
* XXXXX     July 31 2012    Removed IF EXISTS as this is required for non sysadmin user
* XXXXX     July 31 2012    Removed reference to sys.database_files as it returns NULL for non sysadmin users
* XXXXX     Aug 26 2012     Added Logic for FILESTREAM, ROWGUIDCOL and UNIQUE Columns. Note FILESTREAM
                                Can be created only if there is a UNIQUE ROWGUIDCOL
* XXXXX     Sep 08 2012     Removed logic to create default constraints. The defaults are not required besides
                                are hinderance for schema upgrades
* XXXXX     Feb 02 2013     Added logic to avoid further generation of script if DB already exists
* XXXXX     Oct 24 2013     Added logic to create Database mdf and ldf in the path specified by @pstrFilePath
                                Required for Bug# 29221
                                Further removed comments and modified indentation
* XXXXX     Jan 06 2014     Refer Bug 30633. Conversion fails if schemas with '\' are present
                                in the source XXXXX Database. Added square braces '[' to take care of this issue
                                when generating schemas
                                Further removed the redundant code to PRINT 'Database Exists' if the Temp DB
                                already exists
* XXXXX/XXXXX Dec 09 2014    PaperSizeinBytes column having issues during BCP IN, added logic to manage computed columns and its definition.
                                Computed column definition being hard coded now as xp_cmdshell cannot read from sys.computed_columns
* XXXXX  Dec 18 2015        Bug fix#73849: Conversion creates database in paths not excluded by antivirus
                                (mdf, ldf and filestrTemp DB to be created where current XXXXX DB SQL Server is configured)
*********************************************************************************/


texts:
I'm using a CTE to convert SSRS stored proc's into BO stored proc's, as apparently I can't use temp tables with Business Objects.

I'm having this query:

;WITH cte1
AS
(
    SELECT  cv.issue
          , cv.customfield
          , CAST(STRINGVALUE AS NUMERIC) AS priority_num
    --INTO    #temp_priority_val
    FROM    proddb1.customfieldvalue cv WITH (NOLOCK)
            INNER JOIN proddb1.customfield e WITH (NOLOCK)
                 ON  cv.CUSTOMFIELD = e.id
                 AND e.cfname = 'Issue Priority')

,cte2
AS
(
    SELECT  a.ISSUE
          , f.customvalue priority_num
    --INTO    #temp_priority
    FROM    cte1 a
            INNER JOIN proddb1.customfieldoption f WITH (NOLOCK)
                 ON  a.CUSTOMFIELD = f.CUSTOMFIELD
                 AND CAST(a.priority_num AS NUMERIC) = f.id)

SELECT * FROM cte2

I keep getting an Error converting data type nvarchar to numeric. error on this execution.  The priority_num column is an nvarchar column that is CAST in the first CTE. When I execute this within the original statement that had the temp tables it works perfectly fine. It must be something with the CTE scope?


texts:
I want to transfer my audit data from an audit file to a table using SSIS in SQL Server 2012. I don`t have experience with SSIS, so I am asking for your help.

I have already created a table to store the audit data. I am choosing OLE DB Source as source, but what should be the destination ? There is Audit option in "Other Transforms", but how can I use it ?


texts: Optimization on table selected where primary key

texts:
I have this query:

SELECT *
FROM wp_dev_posts, wp_dev_postmeta, wp_dev_term_relationships
WHERE ((post_type = 'projects') OR (post_type = 'post_cost_codes'))
    AND (wp_dev_posts.ID = wp_dev_postmeta.post_id)
    AND (wp_dev_posts.ID = wp_dev_term_relationships.object_id);

My question, is that when I have this in the where:

wp_dev_posts.ID = wp_dev_term_relationships.object_id

What's the correct way to optimize this? Am I better to use joins? Do I set index's? Do I use foreign keys? On a large database this is actually take a few seconds to run.

SQL newbie here.

FYI, this is Wordpress, with 33k rows in posts, about 250k rows in postmeta.


texts:
I want to manage the copy of archiving to remote location.
For that I want to delete all the archives I copied unless they are required by the standby.

So, for running pg_archivecleanup $ARCLOC $CHECKPOINT I need to find the most recent checkpoint which is ok to delete. I don't look on the recent .backup files as it is too old for my archive capacity. What I do want to look on is the replay_location \ replay_lsn(depend on the version) from pg_stat_replication.

However, I do not know how to convert from replay_location value to checkpoint value. I.e. from something like 7/2DBCED18 to 000000020000000700000029.

Answer:

Function is: pg_xlogfile_name


texts: Is it a good idea to use a personal number as a primary key for a customer table?

texts:
We are designing a new system to handle customers at our firm and me and another developer are split on wether we should use the customers personal numbers as a primary key. My preference would be to auto increment a unique id (int) for each new customer.

Currently all customers we add do have a personal number but are there any good examples of why this may be a good or bad implementation?

One argument is that using an incremental id would simply add an extra field which is unnecessary and take up more space.

The personal number is stored as a varchar and the customer table holds close to a million customers. The personal number or id would be added as a foreign key for orders and customer support requests.

How do I know whether these are good or bad implementations? How would I argue either side?


texts: Is there any way to move expired data from one collection to another periodly?

texts:
We have a very big collection, and for getting a better performance, we want to limit the size of "active" collection.

That means, we have two collection: "active" and "inactive", we need to keep the data of last 90 days in "active" collection, and move the data before 90 days to "inactive" collection automatically.

Is any way that we can do this automatically with mongodb?

Or is there any other solution we can achieve this target? (get better performance for querrying data in 90 days without deleting old data)


texts:
I have set up a hot standby in  PostgreSQL 9.0  on Windows.

I want that, at the time of fail-over, the slave should take over the role of master and start performing operations (both read and write) automatically.

How can I do that?


texts:
EXEC msdb.dbo.sp_send_dbmail
@recipients= 'test@test.com',
@copy_recipients = 'test@test.com',
@query = 'SELECT * FROM INSERTED',
@attach_query_result_as_file = 1,
@subject = 'Auto-Generated Deleted Item in Order Item Detail',
@body = 'Item has been deleted from Order Item Detail',
@body_format = 'HTML',
@importance = 'High'

When the above code executes I get the following error:

  Msg 22050, Level 16, State 1, Line 0
  Error formatting query, probably invalid parameters
  Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 495
  Query execution failed: Msg 208, Level 16, State 1, Server BK00SQL0002, Line 1
  Invalid object name 'INSERTED'.

The below works: (How do I modify for INSERTED?)

    EXEC msdb.dbo.sp_send_dbmail
@recipients= 'test@test.com',
@copy_recipients = 'test@test.com',
@query = 'SELECT * FROM [PreMfg].[dbo].[Order Item Detail Audit]',
@subject = 'An Item Has Been Deleted From the Order Item Detail Table',
@attach_query_result_as_file = 1,
@body = 'An Item Has Been Deleted From the Order Item Detail Table',
@body_format = 'HTML',
@importance = 'High'


texts: Insert multiples rows/columns - PostgreSQL 9.2

texts:
I've got a table that has several columns, But I need to insert data into two columns of that table.
I need to add data into those columns, from a select statement.

Would it be something like:

INSERT INTO dm.billables_links (billable_id, mobiuser_id)
SELECT billable_id FROM junk.wm_260_billables2 WHERE info ilike '%Alisha%'
SELECT id FROM public.ja_mobiusers WHERE name_first LIKE 'Alisha%' AND name_last LIKE 'Dson%'

It has to be in the same transaction because the mobiuser_id must go to the selected billable_id on the first select.

How can I do that?

  1 - Select billable_id from dm.billable

  2 - Select mobiuser_id from
  ja_mobiusers

  3 - Insert the billable_id and the mobiuser_id to the
  dm.billables_links table.

I've a CONSTRAINT on the target table: (That's why I need to get it done at the same time. So I won't get errors like:

  ERROR:  new row for relation "billables_links" violates check
  constraint "cc_one_and_only_one_target"

ALTER TABLE dm.billables_links
  ADD CONSTRAINT cc_one_and_only_one_target CHECK ((("customer_id" IS NOT NULL)::integer + ("role_id" IS NOT NULL)::integer + ("mobiuser_id" IS NOT NULL)::integer) = 1);

This is related to my previous question - Which I've asked to be deleted because have done the wrong question and it has been confused.
INSERT INTO + Selects - PostgreSQL 9.2

UPDATE 1: This is an example of what I need

1 - select the billable_id: (SELECT1)

SELECT billable_id FROM junk.wm_260_billables2 WHERE info ilike '%Alisha%'

2 - select the mobiuser_id: (SELECT2)

SELECT id FROM public.ja_mobiusers WHERE name_first LIKE 'Alisha%' AND name_last LIKE 'Dadryl%'

3 - Insert those two data into the dm.billables_links table (EXAMPLE):

INSERT INTO dm.billables_links (billable_id, mobiuser_id) VALUES (**SELECT1**, **SELECT2**);


texts: Why does changing the declared join column order introduce a sort?

texts:
I have two tables with identically named, typed, and indexed key columns. One of the them has a unique clustered index, the other one has a non-unique.

The test setup

Setup script, including some realistic statistics:

DROP TABLE IF EXISTS #left;
DROP TABLE IF EXISTS #right;

CREATE TABLE #left (
    a       char(4) NOT NULL,
    b       char(2) NOT NULL,
    c       varchar(13) NOT NULL,
    d       bit NOT NULL,
    e       char(4) NOT NULL,
    f       char(25) NULL,
    g       char(25) NOT NULL,
    h       char(25) NULL
    --- and a few other columns
);

CREATE UNIQUE CLUSTERED INDEX IX ON #left (a, b, c, d, e, f, g, h)

UPDATE STATISTICS #left WITH ROWCOUNT=63800000, PAGECOUNT=186000;

CREATE TABLE #right (
    a       char(4) NOT NULL,
    b       char(2) NOT NULL,
    c       varchar(13) NOT NULL,
    d       bit NOT NULL,
    e       char(4) NOT NULL,
    f       char(25) NULL,
    g       char(25) NOT NULL,
    h       char(25) NULL
    --- and a few other columns
);

CREATE CLUSTERED INDEX IX ON #right (a, b, c, d, e, f, g, h)

UPDATE STATISTICS #right WITH ROWCOUNT=55700000, PAGECOUNT=128000;

The repro

When I join these two tables on their clustering keys, I expect a one-to-many MERGE join, like so:

SELECT *
FROM #left AS l
LEFT JOIN #right AS r ON
    l.a=r.a AND
    l.b=r.b AND
    l.c=r.c AND
    l.d=r.d AND
    l.e=r.e AND
    l.f=r.f AND
    l.g=r.g AND
    l.h=r.h
WHERE l.a='2018';

This is the query plan I want:

(Never mind the warnings, they have to do with the fake statistics.)

However, if I change the order of the columns around in the join, like so:

SELECT *
FROM #left AS l
LEFT JOIN #right AS r ON
    l.c=r.c AND     -- used to be third
    l.a=r.a AND     -- used to be first
    l.b=r.b AND     -- used to be second
    l.d=r.d AND
    l.e=r.e AND
    l.f=r.f AND
    l.g=r.g AND
    l.h=r.h
WHERE l.a='2018';

... this happens:

The Sort operator seems to order the streams according to the declared order of the join, i.e. c, a, b, d, e, f, g, h, which adds a blocking operation to my query plan.

Things I've looked at

I've tried changing the columns to NOT NULL, same results.
The original table was created with ANSI_PADDING OFF, but creating it with ANSI_PADDING ON does not affect this plan.
I tried an INNER JOIN instead of LEFT JOIN, no change.
I discovered it on a 2014 SP2 Enterprise, created a repro on a 2017 Developer (current CU).
Removing the WHERE clause on the leading index column does generate the good plan, but it kind of affects the results.. :)

Finally, we get to the question

Is this intentional?
Can I eliminate the sort without changing the query (which is vendor code, so I'd really rather not...). I can change the table and indexes.


texts: Facebook generates a Post record when I rename a group's name or just an ActivityItem?

texts:
My question is more about relational database scheme.

I have in mind two tables for it:

Posts
Activities
Where when I post a a status message it probably creates a record for a Post and an Activity representing this (for build the feed and represent notifications).

Well, in case of renaming the name of a facebook's group I started to have this question.

Is created ("simulated") a Post record for a name changes or just and activity record?


texts: SSIS Data Flow Task Excel to SQL table NULL value will not work for small INT datatype

texts:
I have a package that imports excel data into a SQL table. One of the columns in the table is of "smallint" data type and it allows for nulls. The excel file column that has the data that is suppose to go into this column will always contain either small int or in some cases "NULL".

I have noticed that when my source excel file has a NULL in this particular column I get the following error

  There was an error with input column "Copy of Place of Service" (8582)
  on input "OLE DB Destination Input" (8459). The column status returned
  was: "The value could not be converted because of a potential loss of
  data.".

If my source excel file only contains small int values then the package runs fine. I am not sure how I can fix this problem. I have tried changing data types in the Physical table as well as using different DataTypes (via the DataConversion component in SSIS) and I still keep getting the same error message.

Does anyone have any ideas of how I can get around this issue? Even though the column in the  SQL table is checked to allow NULL I guess it is reading the NULL from the Excel source file  as text or string and not as an actual NULL. Any suggestions?


texts: Data Warehouse: Remove legacy data after conversion into new system

texts:
I have a Data Warehouse that pulls data from two locations, the first being our modern system, the second being our legacy system. The legacy system consists of 200 databases with identical schemas that are periodically and individually being converted into our modern system. The process of converting the legacy system will take several months, and our customer needs reporting on all the data from both modern and legacy systems during this time.

My question is this: Once we remove one of the 200 legacy databases and convert into our modern system, how can its data be easily removed from the data warehouse? I know that removing large amounts of data from a data warehouse can be cumbersome and require rebuilding of indexes, etc.


texts: Oracle Warning in Alert Log Regarding PGA_AGGREGATE_LIMIT

texts:
Introduction

During the startup process of our Oracle instances, we are encountering the following error message in the alert log:

WARNING: pga_aggregate_limit value is too high for the
amount of physical memory on the system
  PGA_AGGREGATE_LIMIT is 2048 MB
  PGA_AGGREGATE_TARGET is 256 MB.
  physical memory size is 0 MB
  limit based on physical memory and SGA usage is 512 MB
  SGA_TARGET is 6144 MB
Using default pga_aggregate_limit of 2048 MB

Physical RAM

What is strange is the fact that the physical memory size in the WARNING is being displayed as 0 MB, because our physical server has 768 GB of physical RAM. (Yes, the server isn't a virtual server; physical only for Oracle.)

Spfile and Pfile Configuration

The SGA_TARGET and SGA_MAX_TARGET parameters in the pfile_SID.ora and spfile_SID.ora have been configured at 6144M. Here a list of the interesting parameters from the PFILE:

...
processes              = 500
...
memory_max_target      = 0
memory_target          = 0
sga_max_size           = 6144M
sga_target             = 6144M
...
pga_aggregate_target   = 256M
...

Documentation for PGA_AGGREGATE_LIMIT

According to the Oracle documentation for PGA_AGGREGATE_LIMIT the value will be set according to the following rules:

  By default, PGA_AGGREGATE_LIMIT is set to the greater of...

  2 GB,
  200% of PGA_AGGREGATE_TARGET,
  and 3 MB times the PROCESSES parameter.

  It will be set below 200% of PGA_AGGREGATE_TARGET if it is larger than 90% of the physical memory size minus the total SGA size, but not below 100% of PGA_AGGREGATE_TARGET.

(Important bit at the end regarding restrictions)

Oracle Patch Level

The current patch level of the server is:

Patch Id: 26758841
Patch Description: WINDOWS DB BUNDLE PATCH 12.2.0.1.171017(64bit):26758841

Applying Documentation to Oracle Configuration

Because we haven't manually configured the PGA_AGGREGATE_LIMIT parameter Oracle is going to be so nice and do that for us. This means Oracle is going to set the value to the greater of the following values:

2 GB
200% of PGA_AGGREGATE_TARGET = 200% * 256 MB = 512 MB
3 MB times PROCESSES = 3 MB * 500 = 1.5 GB

...but there would be the restriction cited above, which in my case would mean:

90% * Physical Memory - 6144 MB = 90% * 0 MB - 6144 MB = <0 MB

Ok. Because Oracle is listing the server's physical RAM as 0 MB we are going to hit the restriction and Oracle should throttle the PGA_AGGREGATE_LIMIT value to 256 MB.

But Oracle is setting the value to 2048 MB!

Questions

Why is the physical RAM being listed as 0 MB?
Why is Oracle setting the PGA_AGGREGATE_LIMIT to 2048 MB instead of 256 MB?
Can I safely ignore the warning?


texts: Best practices for Postgres foreign key constraint definition

texts:
Is there any material difference between these two ways of defining foreign key constraints?
CREATE TABLE table1 (
    id INT PRIMARY KEY,
    other_id INT REFERENCES table2(id)
)

CREATE TABLE table1 (
    id INT,
    other_id INT,

    CONSTRAINT pk_table1 PRIMARY KEY (id),
    CONSTRAINT fk_table1_other_id FOREIGN KEY (other_id) REFERENCES table2(id)
)

Obviously one is more verbose. I've also read that the explicit constraint names might be useful within application code during exception handling.
Is one preferred over the other?


texts: Error 29527: While upgrading from SP1 to SP4 in SQL Server 2005

texts:
We are trying to upgrade from SP1 to SP2 in SQL Server 2005 test box for a named instance.

Initially we got the error

  MSP Error: 1260  Windows cannot open this program because it has been prevented by a software restriction policy" and got it fixed by following the solution from forum https://social.technet.microsoft.com/Forums/windowsserver/en-US/10ea9bc2-db84-4c86-9d5e-b442e4fd9600/cannot-install-sql-server-2005-service-pack-2-unable-to-install-windows-installer-msi-file?forum=winserverGP

But now when we are trying to execute the SP2 exe again we are getting the below error from summary file.

and this is the hotfix error log:

On this box we already have one default instance running SQL Server 2005 SP4.

I tried searching on this error but no luck as such.

Any suggestions how can we proceed for this standard 64 bit VM box. thanks!


texts: pg_ctl start gives FATAL: role does not exist

texts:
On my system, whoami returns Dan. I run initdb with the --username=PgUser argument. Then as OS user Dan I run pg_ctl start on the newly-initialized cluster, and it gives this output:

LOG:  database system was shut down at 2013-06-13 00:54:33 UTC
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
FATAL:  role "Dan" does not exist
 done
server started

The server appears to have started up normally. I believe the error occurs because it wants me to run pg_ctl as the OS user named PgUser. What I want to know is:

Is that belief correct?
Is there a way to specify the user as PgUser without using su? I would prefer not to create a new OS user and would like to use a PostgreSQL user with a different username (I'm doing some code refactoring that makes this more convenient).
Is the instance I started crippled in some way because I started it as OS user Dan?


texts:
Probably this has been asked before, but I can't figure it out.
I have a phone_clicks table (sql fiddle http://sqlfiddle.com/#!15/855e0/1)

CREATE TABLE phone_clicks (
    id integer NOT NULL,
    date date NOT NULL,
    industry_id integer NOT NULL,
    clicks integer DEFAULT 0 NOT NULL
);

insert into phone_clicks(id, date, industry_id, clicks)
values
(1, '2015-03-16', 1, 15),
(2, '2015-03-16', 2, 7),
(3, '2015-03-16', 3, 0),
(4, '2015-03-17', 1, 12),
(5, '2015-03-17', 3, 4),
(6, '2015-03-17', 4, 22),
(7, '2015-03-18', 1, 19),
(8, '2015-03-18', 2, 35);

This table holds phone click event counts for multiple industry_ids and dates.

Is it possible to count these clicks for all available industry_ids with multiple date ranges as conditions?
I would like to have this output:

------------------------------------------------
industry_id |  today | yesterday | last 3 days |
------------------------------------------------
1           |  19    | 12        | 46          |
------------------------------------------------
2           |  35    | 0         | 42          |
------------------------------------------------
3           |  0     | 4         | 4           |
------------------------------------------------
4           |  0     | 22        | 22          |
------------------------------------------------

I've tried using counting with partition by date, but got nowhere.
Is it possible to select this data in one query?
Additional benefit would be to be able to specify previous months as date ranges: today, yesterday, March, February, January, etc

UPDATE: I've updated the fiddle to define current month, previous months and pre-previous month as the date ranges.
SQL Fiddle: http://sqlfiddle.com/#!15/855e0/46

I'm using PostgreSQL 9.3, but 9.4 solutions are welcome, because we'll be migrating to it soon.


texts: manual transfer fails with table does not exist

texts:
I performed a manual transfer of data manually (zip up the dir and rsync'ed it over. I moved the members.ibd (the only .ibd file) to a save place, extracted and then replaced members.ibd with the original.
But only the members table can be found.

The rest of the tables are broken.

error:

mysql> select * from transactions;
ERROR 1146 (42S02): Table 'nyc_primary.transactions' doesn't exist

I can see the tables:

mysql> show tables;
+---------------------------+
| Tables_in_nyc_primary     |
+---------------------------+
| accounting                |
| domains                   |
| members                   |
| phpbb_acl_groups          |
| phpbb_acl_options         |
| phpbb_acl_roles           |
| phpbb_acl_roles_data      |
| phpbb_acl_users           |
| phpbb_attachments         |
| phpbb_banlist             |
| phpbb_bbcodes             |
| phpbb_bookmarks           |
| phpbb_bots                |
| phpbb_config              |
| phpbb_config_text         |
| phpbb_confirm             |
| phpbb_disallow            |
| phpbb_drafts              |
| phpbb_ext                 |
| phpbb_extension_groups    |
| phpbb_extensions          |
| phpbb_forums              |
| phpbb_forums_access       |
| phpbb_forums_track        |
| phpbb_forums_watch        |
| phpbb_groups              |
| phpbb_icons               |
| phpbb_lang                |
| phpbb_log                 |
| phpbb_login_attempts      |
| phpbb_migrations          |
| phpbb_moderator_cache     |
| phpbb_modules             |
| phpbb_notification_types  |
| phpbb_notifications       |
| phpbb_oauth_accounts      |
| phpbb_oauth_tokens        |
| phpbb_poll_options        |
| phpbb_poll_votes          |
| phpbb_posts               |
| phpbb_privmsgs            |
| phpbb_privmsgs_folder     |
| phpbb_privmsgs_rules      |
| phpbb_privmsgs_to         |
| phpbb_profile_fields      |
| phpbb_profile_fields_data |
| phpbb_profile_fields_lang |
| phpbb_profile_lang        |
| phpbb_ranks               |
| phpbb_reports             |
| phpbb_reports_reasons     |
| phpbb_search_results      |
| phpbb_search_wordlist     |
| phpbb_search_wordmatch    |
| phpbb_sessions            |
| phpbb_sessions_keys       |
| phpbb_sitelist            |
| phpbb_smilies             |
| phpbb_styles              |
| phpbb_teampage            |
| phpbb_topics              |
| phpbb_topics_posted       |
| phpbb_topics_track        |
| phpbb_topics_watch        |
| phpbb_user_group          |
| phpbb_user_notifications  |
| phpbb_users               |
| phpbb_warnings            |
| phpbb_words               |
| phpbb_zebra               |
| transactions              |
+---------------------------+
71 rows in set (0.00 sec)

rebuilding manual fails:

mysql> select * from transactions;
ERROR 1146 (42S02): Table 'nyc_primary.transactions' doesn't exist
mysql> CREATE TABLE accounting (
    ->  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->  trans_date TIMESTAMP,
    ->  paid_current BOOL NOT NULL DEFAULT '0',
    ->  active BOOL NOT NULL DEFAULT '0',
    ->  cust_id int(10)
    -> );
ERROR 1146 (42S02): Table 'nyc_primary.accounting' doesn't exist

The database exist:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| nyc_primary        |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> desc transactions;
ERROR 1146 (42S02): Table 'nyc_primary.transactions' doesn't exist

How do I recover my missing data/tables?
I cannot even manually add the table structure:


texts: How to connect to Postgres on localhost with URL: "jdbc:postgresql://database:5432/db123"

texts:
I am able to connect to a postgres 10 instance on my local Win10 machine with pgAdmin 4 and "localhost" as a hostname, but when I change the hostname to "database" I cannot connect. How to fix it?

I've added to C:\Windows\System32\drivers\etc\hosts

127.0.0.1 database

It didn't help so I've also edited %HOME%\PostgreSQL10\data\pg_hba.conf adding this line

host    all             all             database                md5

No success.

What can I try next?

UPD: there is nothing useful in server logs %HOME%\PostgreSQL10\data\log\postgresql-2018-09-05_181552.log (should/can I enable some restrictive logging level?)

2018-09-05 18:15:52.677 EEST [5820] LOG:  database system was shut down at 2018-09-05 18:15:42 EEST
2018-09-05 18:15:52.702 EEST [20656] LOG:  database system is ready to accept connections

and after I stoped the postgres service:

2018-09-05 18:15:52.677 EEST [5820] LOG:  database system was shut down at 2018-09-05 18:15:42 EEST
2018-09-05 18:15:52.702 EEST [20656] LOG:  database system is ready to accept connections
2018-09-05 18:16:45.230 EEST [20656] LOG:  received fast shutdown request
2018-09-05 18:16:45.236 EEST [20656] LOG:  aborting any active transactions
2018-09-05 18:16:45.242 EEST [20656] LOG:  worker process: logical replication launcher (PID 20400) exited with exit code 1
2018-09-05 18:16:45.244 EEST [15252] LOG:  shutting down
2018-09-05 18:16:45.322 EEST [20656] LOG:  database system is shut down


texts: What should we call a table that stores changes to another table?

texts: At the moment, we have a Persons Table. But we also need another table to store changes made to that Persons Table. But, my question is, what naming convention is used for tables that store changes made to other database tables.


texts:
I get the error "Column check constraint cannot reference other columns" with the table below. How can I solve this?

CREATE TABLE SALES_ORDER
(
   S_ORDER_NO   VARCHAR2(6) PRIMARY KEY CHECK (S_ORDER_NO LIKE 'O'),
   S_ORDER_DATE DATE,
   DELY_ADDR    VARCHAR2(25),
   SALESMAN_NO  VARCHAR2(6) REFERENCES SALESMAN_MASTER(SALESMAN_NO),
   DELY_TYPE    CHAT(1) DEFAULT 'F' CHECK (DELY_TYPE IN ('P', 'F')),
   BILLED_YN    CHAT(1),
   DELY_DATE    DATE CHECK (DELY_DATE > S_ORDER_DATE),
   ORDER_STATUS VARCHAR2(10) CHECK (ORDER_STATUS IN ('PROCESS', 'FULFILLED', 'BLOCKORDER', 'CANCELED'))
);


texts: Estimated vs. Actual rows and multi-column statistics

texts:
I'm trying to understand multi column indexes and statistics, here is my real world example:

Table: TaskExecutions, size=~1 million rows.
Query SARGS: TaskExecStatusID = 3 AND TaskExecUpdatedDate IS NULL

Distribution:: TaskExecStatusID: 1=160k rows, 2=370k rows, 3=560k rows.
TaskExecUpdatedDate: Null=171k rows.

I have an index on (TaskExecStatusID,TaskExecUpdatedDate),

My problem/question is that the estimated number of rows for the above query is 256, the actual number of rows is 560K.

I want to understand why there is such a big difference between these two numbers?

Stats are up to date, the stats for the index are multi-column stats.  I've tried creating in index/stats on (TaskExecUpdatedDate, TaskExecStatusID), and the estimate/actual does not change.

If I run a DBCC SHOW_Statistics, the density section has both columns in it, the histogram does not. Does SQL server produce a histogram for the combination of columns in multi-column statistics?

dave


texts: How do you load a data file into a specific field?


texts: SQL Always on Availability Groups Direct Seeding

texts:
Setting up a new SQL cluster and HA using SQL Availability Groups.

SQL 2019 STD 2 Node Multi Subnet Basic AG
Primary Node AWS
Secondary Node VMWare on premise

I am using the newer Automatic Seeding option to replicate the database from the primary to secondary.  When looking at the DMV sys.dm_hadr_physical_seeding_stats the transfer_rate_MB_per_Second starts quickly then drops down to 0 mb/sec and the seed takes hours for a 4GB database.
I have switched on the trace flag 9567 for compression but this doesn't seemed to have helped.  My network and VMware engineers have investigated and cannot see any issues, a standard SMB copy between servers works as expected.
Are there additional settings required in AWS for this process or something we may have missed, any help appreciated.


texts: ORA-00904: Invalid Identifier when selecting from view

texts:
I am attempting to view a view with:

select * from DISCOUNT_AMOUNTS;
ERROR at line 1:
ORA-00904: "SALE_GIFT_VOUCHER_AMOUNT_AMOUN": invalid identifier

I then check the view to see what is there in view.

DESC DISCOUNT_AMOUNTS;
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------------- -------- ------------------------------------------------------------
 LINE_ITEM_AUDIT_ID                                                                        NOT NULL NUMBER(18)
 CREATED_DATE                                                                              NOT NULL DATE
 SALE_AMOUNT_AMOUNT                                                                                NUMBER
 SALE_PROMPT_PAY_AMOUNT_AMOUNT                                                                      NUMBER
 SALE_QUANTITY_AMOUNT_AMOUNT                                                                        NUMBER
 SALE_RETAILER_AMOUNT_AMOUNT                                                                        NUMBER
 SALE_MANUFACTURE_AMOUNT_AMOUNT                                                                     NUMBER
 SALE_REBATE_AMOUNT_AMOUNT                                                                          NUMBER
 SALE_TRADE_IN_AMOUNT_AMOUNT                                                                        NUMBER
 SALE_REWARD_RETL_PROM_AMOUNT_A                                                                     NUMBER
 SALE_REWARD_MANU_PROM_AMOUNT_A                                                                     NUMBER
 SALE_REWARD_CC_AMOUNT_AMOUNT                                                                       NUMBER
 SALE_GIFT_VOUCHER_AMOUNT_AMOUN                                                                     NUMBER

I then check it directly with single quotes of

select 'SALE_GIFT_VOUCHER_AMOUNT_AMOUN' from DISCOUNT_AMOUNTS;

which returns successfully.

I then attempt:

SQL> alter view DISCOUNT_AMOUNTS compile;
View altered.

Reattempt

select * from DISCOUNT_AMOUNTS;
ERROR at line 1:
ORA-00904: "SALE_GIFT_VOUCHER_AMOUNT_AMOUN": invalid identifier

This is not making sense to me.  I can compile, it exists in view, but it won't select in wild character search.  What else can I try?  It is 30 characters as well and running command as user who created the view.

This is the create or replace view statement.  Am on 12.1, which is 30 characters I believe. 12.2 is 128 for identifiers.

CREATE OR REPLACE VIEW DISCOUNT_AMOUNTS
AS
SELECT
LINE_ITEM_AUDIT_ID,
CREATED_DATE,
SALE_AMOUNT_AMOUNT,
SALE_PROMPT_PAY_AMOUNT_AMOUNT,
SALE_QUANTITY_AMOUNT_AMOUNT,
SALE_RETAILER_AMOUNT_AMOUNT,
SALE_MANUFACTURE_AMOUNT_AMOUNT,
SALE_REBATE_AMOUNT_AMOUNT,
SALE_TRADE_IN_AMOUNT_AMOUNT,
SALE_REWARD_RETL_PROM_AMOUNT_A,
SALE_REWARD_MANU_PROM_AMOUNT_A,
SALE_REWARD_CC_AMOUNT_AMOUNT,
SALE_GIFT_VOUCHER_AMOUNT_AMOUN
FROM
(SELECT LINE_ITEM_AUDIT_ID,
DISCOUNT_TYPE_ID,
DISCOUNT_AMOUNT,
CREATED_DATE
FROM LINE_DISCOUNT_AUDIT
) PIVOT ( SUM(DISCOUNT_AMOUNT) AS AMOUNT FOR (DISCOUNT_TYPE_ID) IN (
1 AS SALE_AMOUNT,
2 AS SALE_PROMPT_PAY_AMOUNT,
3 AS SALE_QUANTITY_AMOUNT,
4 AS SALE_RETAILER_AMOUNT,
5 AS SALE_MANUFACTURE_AMOUNT,
6 AS SALE_REBATE_AMOUNT,
7 AS SALE_TRADE_IN_AMOUNT,
8 AS SALE_REWARD_RETL_PROM_AMOUNT,
9 AS SALE_REWARD_MANU_PROM_AMOUNT,
10 AS SALE_REWARD_CC_AMOUNT,
11 AS SALE_GIFT_VOUCHER_AMOUNT));


texts: Should I denormalize lat & lon on area searches?

texts:
I run a hobby web site for tabletop gamers.  They can search based on postal code in a radius round their location.  As I consider adding new countries, my best option seem to be silo'ing the geo postal codes tables for each country instead of one large table in order to keep the length down.  Canada alone is 91,8587 rows.  By the time I add a few more english speaking countries, the table length could be a few hundred thousand.

However I was wondering if it would be smart to store the lat & lon in the user-list instead of normalizing the data where it would have to go through several JOINS.  I can't imagine the user list would ever reach more than 50k. The user-list only has about 30 columns and is indexed.

Which is the better option?

edit:  Forgot to mention that the web site does the radius search, not a 3rd party like google.


texts:
Why is the table() operator not working?

create or replace type tmp_011040_t2 as
  table of (JSON_OBJECT_T) NOT PERSISTABLE;

declare
  l_objects tmp_011040_t2;
begin
  l_objects := tmp_011040_t2();
  l_objects.extend(1);
  l_objects(l_objects.last) := JSON_OBJECT_T.parse('{"a":"1","b":"1"}');
  l_objects.extend(1);
  l_objects(l_objects.last) := JSON_OBJECT_T.parse('{"a":"2","b":"2"}');
  for i in (select * from table(l_objects) ) loop -- ERROR!!!
      null;
  end loop;
end;

-- ORA-40573: Invalid use of PL/SQL JSON object type.


texts:
I have a MongoDB database with ~100GB data size. I ran a test with 300 threads and queries are all reads, no writes (except writes to db profiler I guess). I enabled database profiler to keep track of slow queries. I noticed that queries with high 'numYields' result in high 'millis'; likewise, queries with low numYields responded very fast in low millis.
90% of the queries ran very fast, in 1 ~ 2 ms, however, around 2% of queries end up in 60,000 ms or higher.
According to MongoDB doc:

numYields is a counter that reports the number of times the operation has yielded to allow other operations to complete.
Typically, operations yield when they need access to data that MongoDB has not yet fully read into memory. This allows other operations that have data in memory to complete quickly while MongoDB reads in data for the yielding operation.

I understand a slow query was trying to read data from disk, while yielding to other queries that already have data in memory. However, if this result in 60,000ms for that particular query, it becomes unreasonable.
Perhaps there's a way to limit numYields? Or perhaps try to fit everything to memory? Any suggestions?


texts: Login to SQL Server 2k8 R2 SP1 via Active Directory Group

texts:
I added an Active Directory group as a Windows Authentication Login to our SQL Server 2008 R2 database.

When a user who is a member of that group tries to connect to the server he is given a login failed error.  If I add his specific username as a login, he is then able to connect.

Should I be able to add an Active Directory group to allow users in that group to connect?  It would seem this would be preferred over individually managing specific users.

SELECT @@version;

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)


texts: Should I just rely on Foreign Key Constraints for error handling?

texts:
I have been using the following pattern to validate FOREIGN KEY (FK) parameters for stored procedures which execute INSERTs:
CREATE PROC spCreateChildFoo
    @ForeignKey_Id int,
    @Attribute varchar(50)
AS

IF NOT EXISTS (SELECT ForeignKey_Id FROM ForeignTable WHERE ForeignKey_Id = @ForeignKey_Id)
    BEGIN
        RAISERROR ('The input ForeignKey_Id does not exist', 16, 1)
        RETURN;
    END

INSERT INTO ChildTable (ForeignKey_Id, Attribute)
VALUES (@ForeignKey_Id, @Attribute)

I did this because I wanted to catch bad FK entries early in the execution of the stored procedure before any data is INSERTed, especially for procs that carry out multiple INSERTs. I also thought it would be a good way to return accurate error messages to ease debugging. This pattern works well at serving those two goals.
Questions

Is this a waste of time?

More specifically, should I just rely on FK constraints to validate the FK parameters and rollback if it fails?

Is it ultimately going to hurt the performance of my application as the database grows?


texts: Problem creating a transaction in an SSIS package

texts:
I'm working on a package that needs to use a transaction but I'm currently getting the following error:

SSIS package "CATS-Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4001100A at CATS-Package: Starting distributed transaction for this container.
Error: 0xC001401A at CATS-Package: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.
SSIS package "CATS-Package.dtsx" finished: Failure.

Here is what I know so far:

2012 package
I'm running in package deployment
The TransactionOption property is set to Required
Running against a 2008 R2 instance
I can create a distributed transaction manually on the instance and acording to the event viewer MSDTC is running.
MSDTC has the following settings

Can anyone point in a direction to find additional information.

Edit: One of the steps I read said to run DTC on the local machine running the package as well as the server holding the instance.  Once I did that I got a new error:

Information: 0x4001100A at CATS-Package: Starting distributed transaction for this container.
Error: 0xC001402C at CATS-Package, Connection manager "connectionName": The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D024 "The transaction manager has disabled its support for remote/network transactions.".
Error: 0xC0202009 at CATS-Package, Connection manager "connectionName": SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x8004D024.
Error: 0xC00291EC at Execute SQL Task - Max Product ID, Execute SQL Task: Failed to acquire connection "connectionName". Connection may not be configured correctly or you may not have the right permissions on this connection.

With the important part being The transaction manager has disabled its support for remote/network transactions.  Am I missing something in the permissions?  Note: the Allow Remote Clients has also been checked.

I've seen MSDTC ping mentioned (including in the comments below) but when I looked at it I'm not quite sure what to do with it.


texts: Clarification for database creation and relations

texts:
I've trying to make database with few tables and relations between them. As I'm not very good in databases at all I need some clarifications. The idea is to connect table with table_1, table_2 and table_3 Here are two options:

Option 1 - table is directly connected to other tables and in query will be joining them.

Option 2 - will use middle tables to connect them with foreign keys..

Currently my database schema is a mess I have relations with both methods. Some are with middle table and some are without. This is my current view of relations which I think is not very good.

In option 3(current) you can see that connection are double - one from middle table and one directly. What is the best solution here?

Les's say:
table = hotel, table_1 = rooms, table_2 = reservation and table_3 = food.

When user make reservation the system shows him free rooms. He can also see what kind of food has restaurant in this hotel.
So hotel table should be connected with rooms, food and reservation.

update:
One of the query's that I use to show meals is

    $hotels_id = $_GET['meal_id'];
    $strSQL = "SELECT m.meal_id, meal_name, meal_image, meal_image_big, meal_weight, meal_price, meal_description
        FROM meals m
        JOIN meal_hotel mr
        ON m.meal_id = mr.meal_id
        WHERE hotels_id = '$hotels_id'";

So the table meal_hotel has two columns meal_id and hotels_id and this two columns are FK for hotels and meals. Am I right?


texts: Is there a way to find the least recently used tables in a schema?

texts: Is there a way to find the least recently used tables in a MySQL schema? Besides going into data directories? I was hoping there was a metadata or status trick-- but Update_Time in STATUS and INFORMATION_SCHEMA is always NULL.


texts: How can I see all the incoming queries in real time to my MariaDB 10 server on windows?

texts:
I just want to see the queries coming in to MariaDB (very similar to mysql) in real time to help debug my application.

What windows frontends are there that do this sort of thing?

Thanks