texts: MySQL: My table size using a query does not match the IBD's file size on disk

texts:
I have a table with about 600 million rows.  I ran the following commands and noticed

select table_schema "DB name (table_schema)",
    -> sum((data_length+index_length)/1024/1024/1024) AS "DB size in GB" from
    -> information_schema.tables group by table_schema;
+------------------------+-------------------+
| DB name (table_schema) | DB size in GB     |
+------------------------+-------------------+
| my_db                  | 4423.946937561025 |
+------------------------+-------------------+

The whole db is 4.4GB

The table with 600 million rows is:

+------------------------------------+-----------+
| Table                              | Size (MB) |
+------------------------------------+-----------+
| my_table                           |   3729084 |

So this table is 3.7TB in size.

I went to the file system and issued:

sudo du -hs * | sort -rh | head -5
11T my_table.ibd

My table is taking up 11TB in storage.

I ran the following command on the database:

SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

So is it normal for an innodb file to be that large?  If not, is there something that can be done to optimize its storage on disk?

I am running a master slave database:
sudo mysql --version
mysql  Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using  EditLine wrapper

I am running this on an Ubuntu instance;
lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 16.04.5 LTS
Release:    16.04
Codename:   xenial

Be gentle with your answers as managing mysql is new to me.  I usually just write programs to read from mysql and haul the data to something like Elasticsearch or HBase for big data analytics.

Thank you...Amro


texts: How to count number of distinct days from one table using two dates (for range) from another table for each row?

texts:
So I have one datatable with three columns: userid, mindate, maxdate.
I have another datatable that contains all the login logs for each user.

I need to make a query so that I can sum all the distinct login days from one datatable between the mindate and maxdate from another datatable for each user.

The code I can write until:

SELECT a.*, b.logins FROM 'usertable' as a
LEFT JOIN (SELECT user_id, COUNT(DISTINCT(loginday)) from 'logintable`
GROUP BY user_id) as b
on a.userid = b.user_id;

I just need help adding a 'where' statement that can query from b like:
WHERE loginday between a.mindate and a.maxdate.


texts: Is it possible to backup stored routines and functions with Innobackupex?

texts:
I am running a few restore tests on my Dev and I've noticed that the stored routines are not being restored with the innobackupx. Is that something achievable ? Am I doing something wrong ?

I am only using innodb tables so it will be good to restore the db using only innobackupx.

Thank you.


texts: Different execution plan for the same query on the same Oracle 12cR2 schema

texts:
I'm getting different execution plans for the same query running on the same Oracle 12cR2 instance. The query uses only literals (no variable binding) and takes about 10 seconds when I run it from my client via TOAD and takes abount 12 hours when it is run via the PL/SQL package it belongs to.

To make the comparison I copied the SQL_FULLTEXT column from the V$SQL view showing the query run via the package and pasted it in the TOAD editor.

The only difference I can notice about the two scenarios is the PL/SQL package performs some INSERT/UPDATES on tables from which the SQL query read from: these are regular tables on which we locked the statistics because of the transient nature of the data they store.

Here are the execution plans

How would you investigate this issue?


texts: Using the result set of a select query as the input of another select

texts:
I need to make three select queries over three different tables, using the outputs of each select query, the catch is each one gives multiple results. Here is how I do it.

Select "Title", "Num" from "Table" where "Id" in (

    Select "Id" from "Table2" where "Id"    in (

        select distinct "Id" from "Table3" where foo-clause
           )

)

İt only gives me the result of one of the results. How can I make each one use multiple inputs?


texts: Why straight SQL query run faster than stored procedure?

texts:
I am facing-out a very surprising problem in SQL Server.

The problem is when the query run in SSMS window it will return result in ~1 sec and when the query run through a stored procedure it will execute in ~57 sec.

For example :

DECLARE @ip_RequestId As Int
SET @ip_RequestId = 3287

SELECT
   Tbl1.SurveyResponseId,
   Tbl1.ReportLabelID,
   Tbl1.EntityId AS FeedbackByEntityId,
   (SELECT Candidate + ' ' + LastName
    FROM Tbl2
    WHERE Tbl2.ProposalId = Tbl1.EntityId) AS FeedbackByName,
   (SELECT ISNULL((SELECT MRReportTitle FROM Tbl3 WHERE MultiRaterId = @ip_RequestId), '')) As ReportTitle
FROM Tbl1

When I run the above query in SSMS as a query, it execute in ~1 seconds.

But after creating the stored procedure:

CREATE PROCEDURE [dbo].[spGetResponse]
   @ip_RequestId As Int
AS
BEGIN
    SELECT
       Tbl1.SurveyResponseId,
       Tbl1.ReportLabelID,
       Tbl1.EntityId AS FeedbackByEntityId,
       (SELECT Candidate + ' ' + LastName
        FROM Tbl2
        WHERE Tbl2.ProposalId = Tbl1.EntityId) AS FeedbackByName,
       (SELECT ISNULL((SELECT MRReportTitle FROM Tbl3
                       WHERE MultiRaterId = @ip_RequestId), '')) As ReportTitle
    FROM Tbl1
END

When I execute the above stored procedure with same parameter value (i.e @ip_RequestId = 3287) in SSMS window (exec spGetResponse 3287), it executes in ~57 seconds.

After some googling, I found it's because of "parameter sniffing". But I am not really understand the "parameter sniffing".

**Updated: One more thing **

When the Sp run with search query, i will also fast for me. Means if I convert the SP following format, this will also return result in ~1 sec:

CREATE PROCEDURE [dbo].[spGetResponse]
    @ip_RequestId As Int
AS
BEGIN
     DECLARE @strQuery As VARCHAR(MAX)
     SET @strQuery = '(SELECT Tbl1.SurveyResponseId, Tbl1.ReportLabelID, Tbl1.EntityId AS FeedbackByEntityId, (SELECT Candidate + '' '' + LastName FROM Tbl2 WHERE Tbl2.ProposalId = Tbl1.EntityId) AS FeedbackByName, (SELECT ISNULL((SELECT MRReportTitle FROM Tbl3 WHERE MultiRaterId = ' + @ip_RequestId + '), '''')) As ReportTitle FROM Tbl1)'
     EXECUTE (@strQuery)
END

When I execute the above stored procedure with same parameter value (i.e @ip_RequestId = 3287) in SSMS window (exec spGetResponse 3287), it executes in ~1 seconds.

Is there any solution exist to overcome the "parameter sniffing" problem?


texts:
For couple of scenarios, not many a times but few we have seen doing sp_recompile on a stored proc improving the performance.
Being a DBA i understand few of caveats involved when doing sp_recompile and why it would have worked in improving with performance.
There has been a constant debate within our management to provide Application/DEV DBA's the access to do sp recompile when they see performance issues like above. Just so that less of DBA involvement is there and APP devs can run on their during perf issues
I am very hesitant as i think it can bring some unwanted behaviors where it can sometime even worsen performance of other procs.
I am looking for general guidance and experience from your experts why this can be a bad idea under possible different circumstances or when it can be ideal. Your inputs might help me design and may be explain the approach in different way.
FYI- Above is being thought not across just one app but across few other as automation when seeing issues like parameter sniffing.


texts: What is a good way to copy data from one Cassandra ColumnFamily to another on the same Keyspace (like SQL's INSERT INTO)?

texts:
Trying to find a way to easily transfer all the rows from a Cassandra ColumnFamily/Table to another.

The COPY command, as I understand, is a good option.  However, as it dumps all the data to .csv on disk and then loads it back, I can't help but wonder if there is a better way to do it in-engine.

A specific example of what I mean would be the INSERT * FROM my_table INTO my_other_table available in many SQL databases.  Of course, I realize that Cassandra is NoSQL and therefore does not to work the same way - but it seems like something which might be available.

What is a good way to accomplish this?

Thanks very much!


texts: Extended Events session file truncated on server restart

texts:
I have created an extended events session to capture login and logout information. The session is setup to store events to a file.
I understood that one of the advantages of using an event file (rather than a ring buffer) was that events history would be persistent across a restart.
However it appears that a new event file is created when SQL Server is restarted and the history is lost.
Is this expected? If so, what is the advantage of using a file rather than a ring buffer?


texts: PostgreSQL Correlation Between "max_connections" and "numbackends"

texts:
I'm trying to figure out why the field numbackends in pg_stat_database never goes above 16 (and often stays the same even after a restart).

I have max_connections set to 100 and max_locks_per_transaction set to 64. shared_buffers is set to 128MB.

We are in the process of separating the database off the same server as the application, so obviously there's some resource contention going on, but is there any other property or field I should be looking at?

Running PostgreSQL 9.4 on RHEL 6.

I also just cleared the memory cache, and the printout from free -h shows 16G used, 15G free, 139M shared, 39 buffers, and 7.6 cached.

Any help would be greatly appreciated.


texts: How does Oracle close and unmount a database?

texts:
I was reading the Oracle 12c concepts manual. Then I realised that something is not "technically" explained. Below is an excerpt of paragraph 13.2.2.2:

  When a database is closed as part of a SHUTDOWN with any option other than ABORT,
  Oracle Database writes data in the SGA to the data files and online redo log files. Next,
  the database closes online data files and online redo log files......
  After the database is closed, Oracle Database unmounts the database to disassociate it
  from the instance.

I don't technically understand what happens for those two sentences in bold above. Any ideas?

I don't know the whole process but I believe that when a file is closed, the OS or the program takes it off the RAM. I can't quite align that with the bold sentences above.


texts: Tuning Mongo (TokuMX) for fastest bulk reads

texts:
What options do I have for tuning MongoDB / TokuMX for reading the results of queries with large result sets? I'm currently on a single server (no sharding) with the data on an SSD drive. My queries are simply {field: value} with an index on field. The collection has ~1B records, of which I'm retrieving ~50M.

Looking at top, it seems like there are alternating spikes of mongod CPU (~200% CPU, i.e., 100% of 2 cores) and times when it appears that mongod is waiting on IO.

What kinds of things can I try to improve query time?


texts:
I have downloaded Oracle XE 11.2 and successfully installed on my Windows machine. But, I didn't find Scott schema in it. During installation, I didn't get any option where it asked to create sample schema. Could anyone suggest how to get scott schema in Oracle XE 11.2 ?

List of users :


texts:
I am designing a relational database in mySQL - a library management system. I want to show examples of the data in 1NF, 2NF, 3NF, BCNF, etc. My example of 1NF looks something like this

"A relation is in first normal form if the domain of each attribute contains only atomic values, and the value of each attribute contains only a single value from that domain."

I am unsure of if the data is in 1NF. For example is the attribute 'ISBN' atomic?

ISBN is a unique numeric commercial book identifier. An ISBN consists of 4 parts (if it is a 10 digit ISBN) or 5 parts (for a 13 digit ISBN). The different parts is prefix element, registration group, registrant, publication and check digit.
Would you divide the identification number into smaller units, to guarantee an atomic value?


texts: BLObs – Should I Store In Database Or in Directory Structure?

texts:
BLObs – Should I Store In Database Or in Directory Structure

I have been developing a database to replace an ageing data structure that contains 1000's of files within a directory structure. The database being used is MySQL running on a Linux Mint server although much of the development s being done on a Mint laptop for convenience. Part of the reason for moving from the old structure to a database is to avoid errors that have been caused by misnaming of directories and files, and also to extract related information more easily for use in trend analysis and other scenarios. Two of the tables contain BLObs. One has six of which four are infrequently accessed – five of them can be about 2Mb in size but may be larger. The other has four files – three of around 0.1Mb and one of around 4Mb. At a meeting this morning a consultant (cloud/web/database) said that it would be better to keep the BLObs in a file structure with the database holding pointers to them.

Having spent the last few hours Googling I am now started to get confused. Is there a right or wrong way to handle BLObs, and are there any guidelines on which method to choose?

Thank you...


texts: what's result for sql server log backup twice?

texts:
If I have a sql server database mydb. Then I run a transaction log backup at 12:00pm.
Then run the transaction log backup(either full or differential) again at 12:30pm.

what's result for the second backup? any possible problem for this case?


texts: AdonisJS: is something wrong with belongsTo() relationship design?

texts:
In AdonisJs framework, suppose we have a Profile model in which we define belongsTo() relationship to theUser model. The documentation says this will lead us to define our databases this way:

The column id in users table is a foreign key referencing profiles tables instead of being the primary key of users table. Can this 'bad DB design' lead to some inconsistencies?

Billal Begueradj


texts: How is it possible to convert a 3-node replica set to a 1-node replica set during low-usage hours

texts:
We have a chat application that runs on MongoDB. For the amount of users that we currently have, it is sufficient to have a 2 node+Arbiter set up during peak hours.

However, during off-peak hours, we would like to turn off the secondary node and the arbiter to save on cloud computing costs.

Is this possible? Simply shutting down the secondary+arbiter makes Mongo unable to elect a new primary and the app fails.

If the best/only solution is to remove the secondary and arbiter from the replica set, what are the consequences of doing when adding them back on the next peak hours cycle?


texts: Installation of SQL Server 2012 does not contain Virtual Interface Adapter?

texts:
I have installed the entire Enterprise edition of SQL Server 2012 and have noticed that I do not have the Virtual Interface Adapter.

What is the cause of this?


texts: What is the difference between uniqe index/unique/unique key/unique constraint keywords?

texts:
I am using mysql 5.5.16

What is the difference between the following DDL statements?

1)
create table sql_query (
    id int not null
    , query_text varchar(21826) not null
    , query_md5 binary(16) not null
    , primary key (id)
    , unique index query_md5_index (query_md5)
) engine=InnoDB;

2)
create table sql_query (
    id int not null
    , query_text varchar(21826) not null
    , query_md5 binary(16) not null
    , primary key (id)
    , unique query_md5_index (query_md5)
) engine=InnoDB;

3)
create table sql_query (
    id int not null
    , query_text varchar(21826) not null
    , query_md5 binary(16) not null
    , primary key (id)
    , constraint md5_constraint unique (query_md5)
) engine=InnoDB;

4)
create table sql_query (
    id int not null
    , query_text varchar(21826) not null
    , query_md5 binary(16) not null
    , primary key (id)
    , unique key query_md5_key (query_md5)
) engine=InnoDB;

Which one should I use? I want to ensure that the there are no rows with the same value for query_md5 and also want to create an index on that column so that searches on that column are fast.

I read the manual at https://dev.mysql.com/doc/refman/5.6/en/innodb-storage-engine.html but it is not very helpful.


texts:
Is there anyway to grant specific roles on group of people such as :

Grant SELECT ON Jsmith To (SELECT user_name FROM dba_users);

However, this doesn't work.

Thanks in advance,


texts: Is heap a table without indexes at all or without a clustered index?

texts:
BOL seems to define a heap as a table without a clustered index.

But many online posts seem to equate a heap to a table without any kind of indexes.

Is there any subtlety I am not aware of?

Thanks


texts: name of subquery not accessible by where clause

texts:
SELECT
  NAME,
  (SELECT COUNT(1) FROM MOVIERENTED WHERE MOVIERENTED.MOVIEID=MOVIE.MOVIEID) AS RENTCOUNT
FROM MOVIE
ORDER BY RENTCOUNT;

Some movies in this database have never been rented, how can I select only the movies that have been rented outside of the subquery?

I have tried adding WHERE RENTCOUNT > 0 after the FROM MOVIE statement.

  ORA-00904: "RENTCOUNT": invalid identifier


texts: How to run a query in PL/SQL with Serializable transaction isolation

texts:
I want to run this SQL in Oracle 11g:

declare a number;
  begin
  select count(*) into a  from item w where w.Item_num='MOH601' ;
  dbms_output.put_line(a);
  end ;

How to run this query with isolation Serializable? As I have learnt before, Oracle will lock the rows with w.Item_num='MOH601' when using Serializable.


texts: Select/Join multiple row values from Column A if dates in Column B are within 'x' seconds of each other

texts:
I have the following Database structure and need a query to work in SQL Server 2008 and also SQL Server 2014.

Database: ASSETS

Table: COMPUTERS

   PK (GUID)      (nvarchar)   (nvarchar)
|    PC_ID    |  PC_Hostname  |  Win_Ver |
+-------------+---------------+----------+
| ABCDE-12345 |   ACMEINC_1   |   W732   |
| FGHIJ-67890 |   ACMEINC_2   |   W732   |
| QWERT-34567 |   ACMEINC_3   |   W764   |
| JHGFD-87654 |   ACMEINC_1   |   W764   |
| CVBNM-08643 |   ACMEINC_1   |   W1064  |

Table: SOFTWARE

    PK (GUID)      (GUID)      (int)   (nvarchar)         (datetime)
|    REG_ID   |    PC_ID    | SWType |  NAME_Reg |         DATE_Reg        |
+-------------+-------------+--------+-----------+-------------------------+
|  2WS3-8574  | ABCDE-12345 |    8   |   Bob     | 2018-02-19 15:30:10.980 |
|  D46G-2574  | ABCDE-12345 |    7   |   Admin   | 2018-02-19 15:30:12.001 |
|  7F4H-6523  | FGHIJ-67890 |    7   |   NULL    | 2018-03-01 11:02:02.954 |
|  MNDE-4567  | QWERT-34567 |    8   |   Joan    | 2018-11-23 09:28:59.685 |
|  8S22-1254  | QWERT-34567 |    7   | Warehouse | 2018-11-23 09:29:00.103 |
|  C9CC-1854  | JHGFD-87654 |    8   |   Barry   | 2018-11-23 09:46:21.321 |
|  JH21-6325  | JHGFD-87654 |    7   | Accounts  | 2018-11-23 09:46:21.332 |
|  109A-4785  | CVBNM-08643 |    8   |   Sally   | 2019-01-02 12:59:59.753 |
|  34NB-6855  | CVBNM-08643 |    7   |    HR     | 2019-01-02 13:00:00.211 |

I'm trying to query the SOFTWARE table for values in the 'NAME_Reg' column where not 'NULL'.

If two 'DATE_Reg' values are =< 5 sec apart they should be displayed in results on one row with the value matching 'SWType' of 8 in the 'Name' column, and the value matching 'SWType' of 7 in the 'Dept' column.

There will only ever by 2 entries in 'DATE_Reg' within a 5 sec time range of each other.

The results should display the corresponding 'PC_Hostname' linked on 'PC_ID', and the 'DATE_Reg' value corresponding to the 'SWType' value of 8 (Really I'd be ok with either of the two datetime values).

Desired Output

| PC_Hostname |   Name   |    Dept   |         DATE_Reg        |
+-------------+----------+-----------+-------------------------+
|  ACMEINC_1  |    Bob   |   Admin   | 2018-02-19 15:30:10.980 |
|  ACMEINC_2  |   Joan   | Warehouse | 2018-11-23 09:28:59.685 |
|  ACMEINC_1  |   Barry  |  Accounts | 2018-11-23 09:46:21.321 |
|  ACMEINC_1  |   Sally  |     HR    | 2019-01-02 12:59:59.753 |

I'm still very very new to SQL and the closest I've gotten is with the following code after lots of googling.

USE ASSETS
SELECT c.PC_Hostname,
    CASE
        s.SWType
            when '8' then s.NAME_Reg
    END AS Name,
    CASE
        s.SWType
            when '7' then s.NAME_Reg
    END AS Dept,
    s.DATE_Reg
FROM COMPUTERS c
    join SOFTWARE s ON (c.PC_ID = s.PC_ID)
WHERE s.NAME_Reg not like 'NULL'
ORDER BY c.PC_Hostname,s.DATE_Reg

However this results in the following output, which still has the Name & Dept values on alternating rows and has no time range matching. So after getting stuck at this point and not sure of a way forward I'm hoping someone on here could help.

| PC_Hostname |   Name   |      Dept     |         DATE_Reg        |
+-------------+----------+---------------+-------------------------+
|  ACMEINC_1  |   Bob    |     NULL      | 2018-02-19 15:30:10.980 |
|  ACMEINC_1  |   NULL   |     Admin     | 2018-02-19 15:30:12.001 |
|  ACMEINC_1  |   Barry  |     NULL      | 2018-11-23 09:46:21.321 |
|  ACMEINC_1  |   NULL   |   Accounts    | 2018-11-23 09:46:21.332 |
|  ACMEINC_1  |   Sally  |     NULL      | 2019-01-02 12:59:59.753 |
|  ACMEINC_1  |   NULL   |      HR       | 2019-01-02 13:00:00.211 |
|  ACMEINC_3  |   Joan   |     NULL      | 2018-11-23 09:28:59.685 |
|  ACMEINC_3  |   NULL   |   Warehouse   | 2018-11-23 09:29:00.103 |


texts:
I have a table foo where x, y represents coordinates
How do I discard the immediate next coordinate while using select?

  x   |  y | value
------+-----+-------
   32 | 10 |     7
   32 | 11 |     2
   32 | 15 |     2
   32 | 16 |     5
   32 | 21 |    25
   33 | 17 |    57
   33 | 21 |    83
   33 | 22 |    86

Output

  x   |  y | value
------+-----+-------
   32 | 10 |     7
   32 | 15 |     2
   32 | 21 |    25
   33 | 17 |    57
   33 | 21 |    83

Here cordinates (32,11), (32,16), (33,22) were skipped since they have an immediate previous coordinate (32,10) -> (32,11), (32,15) -> (32,16), (32,21) -> (33,22)

Is this possible using a query?
I am using Postgres


texts: MySQL 5.6 Online DDL lead to to much table-metadata-locks when I just add index

texts:
It's normal that table-metadata-lock shows up for a short time when ALTER TABLE t add index ... starts and ends. But I have encountered all DML operations blocked in Waiting for table metadata lock, and I'm sure there is no long sql statement running before adding index.

What's more confused is that I saw the ALTER statement copying to tmp table in processlist. According to official docs innodb-create-index-overview, INPLACE method should be taken by default, right? ( I just add one secondary index and delete one).

Also I checked information_schema.innodb_trx to see whether uncommitted transactions exist. No.

Can someone could explain why this happens for me?

By the way, my (alter) table t1 has triggers on it. And there's trigger on other table t2 to update t1. Thought I know it's ugly ·_·

show processlist after alter table t1 add key idx_c1_c2(c1,c2), drop key idx_c2：


texts: MySQL: Unable To Create Generated Column With Expression That Uses DATE_ADD() Function

texts:
There might be a really obvious solution to my question, but I am stuck and did not manage find out what I am doing wrong, so you guys might tell me.

I am not able to create a nullable stored column for my expression using the DATE_ADD function in MySQL 8.

See the following example:

ADD COLUMN `test` TIMESTAMP GENERATED ALWAYS AS (DATE_ADD(col1, INTERVAL col2 HOUR)) STORED NULL;

col1 is TIMESTAMP and col2 is INT

MySQL does not complain, but just converts the expression to

(`col1` + interval `col2` hour)

And the resulting is not nullable as well.


texts: Oracle : Redo / Undo for nologging table + Append + archivelog

texts:
I'm trying to replicate a table from an external Oracle DB to my system using a DB Link.

First i tried doing it using an mview but it caused alot of redo/undo(I did the same steps as below but it didn't work for some reason).

Next, I went on a different strategy and created a self implementation of an mview with a table.

Table was set as nologging when created. (verified it in dba_tables)
Index on table is set as nologging when created. (verified it in
dba_indexes).
Database log_mode is set to ARCHIVELOG. (verified in v$database).

process of replication is being done like:

Set index as unusable.
Truncate table.
Insert with append hint.(INSERT /*+append*/ into tbl_name SELECT *
FROM v_tbl)
Rebuild index nologging.

The code is written in a package which is being run with a scheduler_job on an interval.

v_tbl - a view that encapsulate the original external table structure(view is being built dynamically when compiled due to different sites external DB table names and columns) and give constant names for the columns(by the view columns aliases).

v_Tbl :

 SELECT column1 col1,
        column1 col2
 FROM ext_tbl@external_db;

When examining the archives using dbms_logmnr package I found a lot of redolog is being written on this table even though I'm using the append hint and no logging options for table and index.

In v$logmnr_contents view I see alot of rows for this table with operation = "DIRECT INSERT" and I can see all the data which was inserted in the redo/undo columns.

Is it related maybe to the DB link?

Am I doing anything else wrong?


texts: Help design a db for multiple experiments of time series features

texts:
My use case is something like the following:

There are n medical cases today.
Every day 0 to 10 new cases arrive.
Each case is a time series of varying length, of measurements of raw data. Assume a bunch of ecg signals.
Raw data comes from various 1<k<5 sources
Each source has its own sampling rate [between 1 and 100 per second], and can have na values.
Data sources are not necessarily exactly syncronized
Each data source has 'c_k' columns
A case is an .h5 file with ~180000 to ~540000 samples, in the highest sampling rate [100HZ].
Data is mostly sampled into matrices, and columns will be those matrices flattened into their respective indices.
The code base has to be in Python.

For example, n=5, k=2, c_1=1, c_2=2, sampling rate of 1: "every row", sampling rate of 2: "every 2 rows". There is no "time" column. (should there be?)
Sample |  1_1 | 2_1 | 2_2
1      |  1.0 | 10.0| 100.0
2      |  2.0 |     |
3      |  3.0 | 30.0| 300.0
4      |  4.0 |     |
5      |  5.0 | 50.0| 500.0

I would like to have the ability to query in an SQL-like manner, over any intersection of cases, samples, data, with the most flexibility, for research purposes.
This is not for production. Both querying and adding data doesn't happen often, and there are no scale-related constraints.
What would be a good design for this?
I assume some information may be missing, so please ask and I will add in whatever is needed.


texts: How to run a dynamic select query with column names from another query?

texts:
I'm new to Postgres, so I'm sorry if this is overly simple. But I've searched for ages, and can't find a solution for my dynamic-sql problem.

I have one table that contains the list of columns in a second table (and some metadata about these columns) that relate to a particular dataset.

I need to get that dataset, preferably with one stored procedure/function/query.

So far I've created a function (with one input parameter) that returns the necessary select string to extract the records that I want.  But I can't find a way to automatically run this statement.

I can't write a function to return a table, because I can't define the table structure beforehand. Or am I just doing something wrong?

This will ultimately be called from a php statement, so the results can be displayed in a webpage, so if I can't do it on the server, I can always have the php code first return the sql string and then execute it separately.  But it would be neater if I could do this in a single transaction.

I don't need to change any records, just display them.

I'm running Postgres 9.6.

Any ideas?

jo

This is the function I wrote to extract the string of columns and their display names from a table called t_fileattributenames that I want to extract from the table that contains the file attribute data:

CREATE OR REPLACE FUNCTION pdls.getattstring(
    this_dsid bigint)
    RETURNS text
    LANGUAGE 'plpgsql'

   COST 100
   VOLATILE
   AS $BODY$
   declare
   isdone int;
   resultarray text[];
   strcolnames text;
   whichfileid int;
   mviews record;
   mystring text;

  i int;
  begin
    i=1;
    FOR mviews IN SELECT col_name,fieldname,fileid FROM
      pdls.t_fileattributenames where dsid=this_dsid ORDER BY col_order LOOP
      resultarray[i]=mviews.col_name || ' AS "' || mviews.fieldname || '"';

      whichfileid=mviews.fileid;
      i=i+1;
    END LOOP ;
    strcolnames :=  array_to_string(resultarray,',',null)  ;
    if strcolnames is not null
        then strcolnames='dsid, ' || strcolnames;
        else strcolnames='';
    end if;
    return strcolnames;
   end
  $BODY$;

Let's call the output $rtnstring.

With an example input of dsid=9 the output will look like this:

$rtnstring=

'dsid, str1 AS "Researcher code",str2 AS "Filename",int1 AS "Have File?",
int2 AS "Pump number",int3 AS "Test number",int4 AS "Test number order",
int5 AS "BEP",int6 AS "Flowrate in m3/hr",dbl1 AS "Velocity in m/s",
dbl2 AS "%BEP",dbl3 AS "Efficiency",dbl4 AS "Power to pump in kW",
dbl5 AS "Calculated Efficiency",dbl6 AS "NPSHa",dbl7 AS "NPSHratio",
dbl8 AS "NHSPR",dbl9 AS "Total head in m",dbl10 AS "Suction pressure in kPa",
dbl11 AS "Vapor pressure in kPa",dbl12 AS "Cavitation number"'

That bit works fine.

What I really want to do is something like this (Excuse the php format):

"select " . $rtnstring . " from pdls.t_fileattributes inner join
pdls.t_datafiles on t_fileattributes.fileid=t_datafiles.fileid where
t_datafiles.dsid=" . $dsid)

When I manually run the query by cutting and pasting the output from the first query, I get the data I want.

Can I do both queries one SQL step? I don't know what number of columns or their types I will have until I run the first query, but as they will all be rendered on a webpage, they can all be converted to text if necessary.

If not, I'll just have PHP run the two steps separately.


texts: Poor Query performance when scalar variables used in Query

texts:
Facing a strange issue that a query runs incredibly slow (worse) when scalar variables are used as parameter.

Actual Query (Faster) - 9 secs

select
  sum(v.sale_value) as col_0_0_,
  sum(v.vat_value)  as col_1_0_,
  sum(v.sale_fcy)   as col_2_0_,
  sum(v.vat_fcy)    as col_3_0_
from my_view v
where v.company_id = 1 and v.filing_year = 2018 and v.filing_month = 1;

Parameterized Query (Worse) - 16 mins

DECLARE @P0 int, @P1 int, @P2 int;
SET @P0 = 1;
SET @P1 = 2018;
SET @P2 = 1;
select
  sum(v.sale_value) as col_0_0_,
  sum(v.vat_value)  as col_1_0_,
  sum(v.sale_fcy)   as col_2_0_,
  sum(v.vat_fcy)    as col_3_0_
from my_view v
where v.company_id = @P0 and v.filing_year = @P1 and v.filing_month = @P2;

Execution times for both queries were calculated by executing in command line client (IntelliJ Database console).

Query with this scalar variable is generated by our Hibernate Application as we've used setParameter(String name, Object value) to set the param values.

First, we suspected issue in hibernate generated query, but narrowed down to SQL Server query execution after identifying it as parameterized query using sys.dm_exec_cached_plans. Does query optimiser get confused when scalar variables are used?

In java code, we're setting the values as query parameters. In sys.dm_exec_cached_plans, the actual query is:

(@P0 int,@P1 int,@P2 int)select <columns> from my_view v
where v.company_id= @P0 and v.filing_year= @P1 and v.filing_month= @P2

Note: my_view is pretty complex (nested views, group by, joins, order by, where clause...). We tried to compare the execution plans, but they're incredibly huge We are looking into it but might take some time.

If I run the slow query with OPTION (RECOMPILE), it executes in 9 secs.

SQL Server Version

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
    Feb 10 2012 19:39:15
    Copyright (c) Microsoft Corporation
    Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)


texts: We are trying to drop tables from mysql that is installed in amazon linux which the os is not able to handle and is very slow, mysql crashes as well or goes to the defunct mode. We have tried to execute the same on centos5.5 but the process was very fast and the mysql did not crash. The mysql configuration are the same on both the servers. Kindly tell us why this scenario happens ??.


texts: Same name for a view and synonym in same schema

texts:
Scenario:
In a schema (sc1), there is a synonym with name syn1 for table t1. I created a view with name as sc1.syn1.
And it got created successfully without any conflict with synonym.
Synonymn (syn1) and view (sc1.syn1) pointing to same table t1.
But view has some subset of columns from t1.
So, I am thinking what will be the out of query
select * from sc1.syn1;

Will it access synonym first or view?
Database version:
EDBD4=# select version();
                                                   version
-------------------------------------------------------------------------------------------------------------
 EnterpriseDB 10.1.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit


texts: In Oracle, can we restore a database onto the same instance with a different name?

texts:
In SQL server I can restore a database onto the same instance with a different name. Eg: If there is a database DB_prod, I can take a full backup and restore it on the same instance with a different name like DB_test.

Is a similar task possible with Oracle?


texts: org.postgresql.util.PSQLException: This connection has been closed. error for long running transactions

texts:
We are getting "org.postgresql.util.PSQLException: This connection has been closed." on one of our deployments for only long running transactions (more than a few minutes):

Caused by: org.hibernate.TransactionException: rollback failed
        at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.rollback(AbstractTransactionImpl.java:217)
        at org.springframework.orm.hibernate4.HibernateTransactionManager.doRollback(HibernateTransactionManager.java:604)
        ... 87 more
Caused by: org.hibernate.TransactionException: unable to rollback against JDBC connection
        at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doRollback(JdbcTransaction.java:167)
        at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.rollback(AbstractTransactionImpl.java:211)
        ... 88 more
Caused by: org.postgresql.util.PSQLException: This connection has been closed.
        at org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:822)
        at org.postgresql.jdbc2.AbstractJdbc2Connection.rollback(AbstractJdbc2Connection.java:839)
        at org.apache.commons.dbcp2.DelegatingConnection.rollback(DelegatingConnection.java:492)
        at org.apache.commons.dbcp2.DelegatingConnection.rollback(DelegatingConnection.java:492)
        at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doRollback(JdbcTransaction.java:163)
        ... 89 more

Our stack is as follows:

Postgresql 9.2 (on db server Ubuntu 16.03)
PgBouncer (on application server Ubuntu 16.03)
Jars (on application server Ubuntu 16.03)

org.postgresql:postgresql:9.2-1004-jdbc41
javax.transaction:jta:1.1
org.apache.commons:commons-pool2:2.4.2
org.apache.commons:commons-dbcp2:2.1.1'

Postgresql and Pgbouncer use the default parameters and we use the following parameters for dbcp:

database-initial-size = 2
database-max-total = 200
database-validation-query = SELECT 1
database-test-on-borrow = true
database-test-while-idle = true
database-max-wait-millis = 3000
database-time-between-eviction-runs-millis = 34000
database-min-evictable-idle-time-millis = 55000

We have other deployments with same parameters but we are not having the same problem there.

I suspect there is a Firewall/Nat which resets the connection after some timeout but I don't know how to check if this is the case. I would appreciate very much if you can guide me about what logs/parameters/configurations to check which may cause this exception.

I have tested that if Postgresql and PgBouncer are on the same server this problem does not occur.
I have also investigated the Postgresql logs and no error messages are logged.


texts: What can cause low cost and high runtime in EXPLAIN ANALYZE?

texts:
I have a database that pretty consistently runs queries in a magnitude of cost/10 ms.  There are a couple queries where EXPLAIN ANALYZE reports a cost of 2000 (which I'd expect to be somewhere in the ballpark of 200ms) but runs take multiple minutes.

My first thought is that some other activity is bogging down postgres causing this (either other processes on the machine or concurrent database activity). Is there anything else I should be looking into?  Am I mistaken to expect similar cost:time ratios for different queries?


texts: Storing application settings with different datatypes

texts:
I'm developing a web application and I plan to store application settings in the database in name,value fields (one row = one setting).

These settings range from directory paths to simple yes/no flags. Obviously, I can't store a path (string) into a boolean field.

Is my only option to make the value field a string and then interpret the value as necessary per setting, or is there some other strategy for this?

(I'm using PostgreSQL, but the answer should probably apply to any database server.)


texts: Replicating Excel's CHIDIST function in Oracle 11g

texts:
I'm attempting to replicate the functionality of an Excel workbook in Oracle. One of the formulae on the worksheet uses CHIDIST, which "Returns the one-tailed probability of the chi-squared distribution". I can't see a similar function in Oracle anywhere! Am I going to have to write my own CHIDIST function?

Here's a link to Excel's documentation on CHIDIST: http://office.microsoft.com/en-gb/excel-help/chidist-HP005209010.aspx

I've already worked out the Chi Square and Degrees of Freedom.

Thanks


texts: How can I include more query in one query?

texts:
These are my tables:

tbl_answers =>
  aid
  qid
  answer
  uid
  dateposted
  emailnotify
  namedisplay
  status
  isbestanswer

tbl_questions =>
  qid
  question
  detail
  mcid
  cid
  uid
  answercount
  dateposted
  status
  showname
  emailnotify
  question_type_id

I woud like to update those question that has got more answers than it's counted in:
tbl_questions =>  answercount

How can I update the rows which have got more questions than it's counted? (Without php loop)

This is how I'm trying now:

UPDATE tbl_questions
  JOIN (
    SELECT tbl_questions.qid, COUNT(*) AS n
    FROM tbl_questions JOIN tbl_answers ON tbl_answers.qid = tbl_questions.qid
    WHERE answercount = "0"
    GROUP BY id
  ) AS T USING (tbl_answers.qid)
SET answercount = n
WHERE n > 0

IT says me You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.qid) SET answercount = n WHERE n > 0' at line 7


texts: Concerns regarding changing virtual NIC on SQL Server 2008 R2 / Windows Server 2008 R2

texts:
I have SQL Server 2008 R2 running on Windows Server 2008 R2 in a virtual environment (ESXI 5.5). Client has been advised to switch the existing E1000 adapter with a VMXNET 3 adapter because they were told it would perform better.

Anyhow, I've done this several times on member servers as well as domain controllers. As I understand it, SQL Server is more concerned with the network layer so I don't believe this should be an issue. I am not a SQL guru so I figured I would ask here to be safe.


texts: Thread Count is too high-what can I do in short term?

texts:
Before I posted this question read following articles.

Max. Worker Threads and when you should change it by Sakthivel Chidambaram
Are my actual worker threads exceeding the sp_configure 'max worker threads' value? by Tom Stringer
How do you measure CPU pressure? by Thomasda
SQLOS’s DMVs Continue by Slavao

I am familiar with investigating high thread count issue by looking at following items (not comprehensive):

Maxdop setting
Cost threshold of parallelism setting
Cpu pressure
Worker queue length
Execution plan

This investigation can take hours before I decide to make a change. What can I do in short term so the requests are not waiting for worker threads to be assigned?

We use SCOM to monitor many aspects of SQL Server and this is one of them. I understand this is more of a informational message but seeing is consistently from same server gives us opportunity to look at the things that I listed above. What I am trying to find out from the community, if they take certain action (can be temporary, till full evaluation is done) if work_queue_count is constantly greater than zero once alert is triggered by SCOM. There are times it is easy to detect a single query that is consuming lot of threads but not always.


texts: Total duration "server" was in "In" state per day

texts:
I have data of a server start & stop time on a daily basis for one month. I need the result containing the first start time of the day, last stop time of the day, total time the server was started in the day, for each server and each day.

Data sample:

Date & Time           Reader ServerID
3/14/2016 6:36:20 AM    IN    123
3/14/2016 6:58:45 AM    OUT   123
3/14/2016 8:06:19 AM    IN    123
3/14/2016 9:32:48 AM    OUT   123
3/15/16 6:00:00 AM      IN    123
3/15/16 6:01:00 AM      OUT   123
3/14/2016 9:46 AM       IN    124
3/14/2016 10:01 AM      OUT   124
3/14/16 11:01 AM        IN    124
3/14/16 12:01 PM        OUT   124

Expected output:

ServerID  FirstIN  Last Out  TotalInTime (min)  Date
123       6:00     09:32     86                 3/14
123       06:00    06:01     1                  3/15
124       9:46     12:01     75                 3/14

Please note that each IN will always have a corresponding OUT in the source table.

How can I solve this?

Here are the CREATE TABLE and INSERT statements:

create table tbl1 (
    serverid numeric (18,0)
    , Reader varchar (20)
    , Date_Time datetime
);
Insert into tbl1 values (123, 'In', '2015-08-24 06:00:00.000');
Insert into tbl1 values (123, 'Out', '2015-08-24 07:00:00.000');
Insert into tbl1 values (123, 'In', '2015-08-24 08:00:00.000');
Insert into tbl1 values (123, 'Out', '2015-08-24 09:00:00.000');
Insert into tbl1 values (123, 'In', '2015-08-24 10:00:00.000');
Insert into tbl1 values (123, 'Out', '2015-08-24 11:00:00.000');
Insert into tbl1 values (123, 'In', '2015-08-25 10:00:00.000');
Insert into tbl1 values (123, 'Out', '2015-08-25 11:00:00.000');
Insert into tbl1 values (124, 'In', '2015-08-24 06:15:00.000');
Insert into tbl1 values (124, 'Out', '2015-08-24 07:30:00.000');
Insert into tbl1 values (124, 'In', '2015-08-24 08:00:00.000');
Insert into tbl1 values (124, 'Out', '2015-08-24 09:30:00.000');
Insert into tbl1 values (124, 'In', '2015-08-24 10:00:00.000');
Insert into tbl1 values (124, 'Out', '2015-08-24 11:30:00.000');


texts:
I want one server to migrate the database to another server. On another server database already exists, then ist all the tables, but they are empty and I want to fill. For the filling, I want to use bcp - database migration. In command line, I wrote the following:

bcp [dbo].[aspnet_Profile] in "c:\SQLAzureMW\BCPData\dbo.aspnet_Profile.dat" -E -n -b 10000 -a 16384 -S servername.database.windows.net -U username@servername -P "password" -d databasename!

The name of the user and the server name passvord written correctly.

When I run I get this error:

error = Packet size change not supported by server, default used.

Then I fixed packet size to the size of a default. I removed the "-a".

bcp [dbo].[aspnet_Profile] in "c:\SQLAzureMW\BCPData\dbo.aspnet_Profile.dat" -E -n -b 10000 -S servername.database.windows.net -U username@servername -P "password" -d databasename!

Then I got the following error:

Who can tell what I should do, or is there a good way to move the database?
I also used the SQL Azure Migration Wizard,but it don't help me.


texts: How to count the reads on index in MySQL

texts:
I have a table with index.
CREATE TABLE PART  ( P_PARTKEY     INTEGER NOT NULL,
                          P_NAME        VARCHAR(55) NOT NULL,
                          P_MFGR        CHAR(25) NOT NULL,
                          P_BRAND       CHAR(10) NOT NULL,
                          P_TYPE        VARCHAR(25) NOT NULL,
                          P_SIZE        INTEGER NOT NULL,
                          P_CONTAINER   CHAR(10) NOT NULL,
                          P_RETAILPRICE DECIMAL(15,2) NOT NULL,
                          P_COMMENT     VARCHAR(23) NOT NULL );

CREATE TABLE LINEITEM ( L_ORDERKEY    INTEGER NOT NULL,
                             L_PARTKEY     INTEGER NOT NULL,
                             L_SUPPKEY     INTEGER NOT NULL,
                             L_LINENUMBER  INTEGER NOT NULL,
                             L_QUANTITY    DECIMAL(15,2) NOT NULL,
                             L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
                             L_DISCOUNT    DECIMAL(15,2) NOT NULL,
                             L_TAX         DECIMAL(15,2) NOT NULL,
                             L_RETURNFLAG  CHAR(1) NOT NULL,
                             L_LINESTATUS  CHAR(1) NOT NULL,
                             L_SHIPDATE    DATE NOT NULL,
                             L_COMMITDATE  DATE NOT NULL,
                             L_RECEIPTDATE DATE NOT NULL,
                             L_SHIPINSTRUCT CHAR(25) NOT NULL,
                             L_SHIPMODE     CHAR(10) NOT NULL,
                             L_COMMENT      VARCHAR(44) NOT NULL);

ALTER TABLE LINEITEM
ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);

ALTER TABLE PART
ADD PRIMARY KEY (P_PARTKEY);

And query
SELECT Sum(l_extendedprice * ( 1 - l_discount )) AS revenue
FROM   lineitem,
       part
WHERE  ( p_partkey = l_partkey
         AND p_brand = 'Brand#52'
         AND p_container IN ( 'SM CASE', 'SM BOX', 'SM PACK', 'SM PKG' )
         AND l_quantity >= 4
         AND l_quantity <= 4 + 10
         AND p_size BETWEEN 1 AND 5
         AND l_shipmode IN ( 'AIR', 'AIR REG' )
         AND l_shipinstruct = 'DELIVER IN PERSON' )
        OR ( p_partkey = l_partkey
             AND p_brand = 'Brand#11'
             AND p_container IN ( 'MED BAG', 'MED BOX', 'MED PKG', 'MED PACK' )
             AND l_quantity >= 18
             AND l_quantity <= 18 + 10
             AND p_size BETWEEN 1 AND 10
             AND l_shipmode IN ( 'AIR', 'AIR REG' )
             AND l_shipinstruct = 'DELIVER IN PERSON' )
        OR ( p_partkey = l_partkey
             AND p_brand = 'Brand#51'
             AND p_container IN ( 'LG CASE', 'LG BOX', 'LG PACK', 'LG PKG' )
             AND l_quantity >= 29
             AND l_quantity <= 29 + 10
             AND p_size BETWEEN 1 AND 15
             AND l_shipmode IN ( 'AIR', 'AIR REG' )
             AND l_shipinstruct = 'DELIVER IN PERSON' );

I want to know

how many reads are performed on index.
how many reads are performed on table data.
how many index are in the buffer (memory) and in the disk.
how many table data are in the buffer (memory) and in the disk.

Thanks!


texts: Failing to automatically re-connect to New PRIMARY after a replica set failover , from Mongoose (MongoDB, NodeJS Driver)

texts:
I made a simple NodeJS App, with Mongoose as MongoDB Driver. And connected to a mongodb replica set. The App is working fine until I shut down the current primary, When the PRIMARY is down, the replica set automatically elected a new PRIMARY. But, after that the node application doesn't seems to be responding for DB queries.

CODE: DB Connection

var options = {
    server: {
        socketOptions: {
            keepAlive: 1,
            connectTimeoutMS:30000,
            socketTimeoutMS:90000 }
        },
    replset: {
        socketOptions: {
            keepAlive: 1,
            connectTimeoutMS : 30000 ,
            socketTimeoutMS: 90000
        },
        rs_name: 'rs0'
     } };

var uri = "mongodb://xxx.xxx.xxx.xxx:27017,xxx.xxx.xxx.xxx:27017,xxx.xxx.xxx.xxx:27017/rstest";

 mongoose.connect(uri,options);

CODE: DB Query

  router.('/test',function(req,res){

     var testmodel = new testModel('test') ;
     testmodel.save(function (err, doc,numberAffected) {
         if (err) {
             console.log("ERROR: "+ err);
             res.status = 404;
             res.end;
         }else{
             console.log("Response sent ");
             res.status = 200;
             res.end;
         }
     });
 });

Steps Followed

Created a MongoDB replica set in three VMs.
Created a simple nodeJS App (Express + Mongoose) with a test API as above
Sent GET request to 'test' continuously with some time interval to the app from a local system.
Took the PRIMARY instance down
APPLICATION STOPPED RESPONDING TO REQUESTS

Varsions:
    "express": "4.10.6",
    "mongodb": "1.4.23",
    "mongoose": "3.8.21",

A sample app that I have done for debugging this issue is available at https://melvingeorge@bitbucket.org/melvingeorge/nodejsmongorssample.git

I am not sure if this is a bug or some mis-configuration from my end. How to solve this issue ?


texts: How to detect whether a key is present in a JSON object using TSQL?

texts:
This is an interesting one.

Imagine I have a JSON object containing four key-value pairs where the keys are a, b, c and d.

Imagine this object is passed to a stored procedure and every key is optional (as long as there is at least 1 key in the object). That is, it is valid to pass '{"a":"1"}' even though b, c and d have been excluded.

The question is:

How can I perform a check, in the stored procedure, as to whether a particular key has been passed in?

If I attempt to access a key using json_value, and the key is absent, then in lax mode, I receive a null value; in strict mode, I receive an error. I suppose this is one way I can tell the difference: reference the key in strict mode and catch any errors - but I want to avoid using try-catch logic.

Below are some example calls:

declare @data nvarchar(max); set @data = N'{"a":"1","b":"","c":null}';
select [value] from openjson(@data);
select 'strict a', json_value(@data, 'strict $.a') union
select 'strict b', json_value(@data, 'strict $.b') union
select 'strict c', json_value(@data, 'strict $.c') union
-- select 'strict d', json_value(@data, 'strict $.d') union
select 'lax a', json_value(@data, 'lax $.a') union
select 'lax b', json_value(@data, 'lax $.b') union
select 'lax c', json_value(@data, 'lax $.c') union
select 'lax d', json_value(@data, 'lax $.d');

This shows:

A non-null string value is always returned "as is" (key 'a')
An empty string is always returned "as is" (key 'b')
A null is always returned "as is" (key 'c')
An absent key is returned as null in lax mode. If you uncomment the relevant line, attempting to reference an absent key in strict mode returns an error.

In other words, I can't distinguish between an absent key, and a key that has been supplied with a null value.

Why do I want to distinguish the difference between null being passed in, and a key being absent? If the key is present, its value should be persisted (whether null or not). I do not want to persist null in the event the key is absent. If the key is absent, I want to simply do nothing relating to that key (or the column where its value would be persisted).

My next approach was to wonder whether I could count the matching rows - perhaps rather than an error, the count function would return '0' as the count of rows matching on key 'd'.

This is where it gets interesting.

declare @data nvarchar(max); set @data = N'{"a":"1","b":"","c":null}';

with CTE as (select json_value(@data, 'strict $.a') as strictA)
select count(1) from CTE;

with CTE2 as (select json_value(@data, 'strict $.d') as strictD)
select count(1) from CTE2;

I was hoping to see the values 1 and 0. I was thinking it would either be that, or that it throws an error. The actual result is that both queries return a count of 1.

Why does the second query return '1'?

And do I have to resort to try-catch logic to detect whether a given key exists in a JSON fragment?


texts: What is the SQL Server equivalent of MySQL's utf8mb4 character set?

texts:
We have some web applications based on databases using utf8mb4 as character set and utf8mb4_Standard as collation.

We saw that we can use any character we want with this setting.

In SQL Server Express the situation is not very clear to me.

When I switch to Standard it chooses the Latin1_General_CI_AS collation.

But I do not know which character encoding this is and how it would affect the scenario if we would like to take over some data from utf7mb8 MySQL tables into SQL Server.

When I look into the datatype definition in SQL Server, I can see that there are Unicode and non Unicode types. So I am wondering if the collation actually has impact into how it is stored:

It seems that if you use nchar, nvarchar or nvarchar(max) you are on the safe side as you use UTF-16.

But, what does the collation Latin1_General_CI_AS mean?

Especially how would this behave if you had Chinese characters, for example?


texts:
I have been running a server side trace in order to find out how a specific stored procedure is called within my database.

I am running the following query in order to get data from the trace file generated:

select TextData,
db_name(databaseid)
from fn_trace_gettable( 'R:\Profiler\udpProductTaxRateGet4.trc', default) fn
join sys.trace_events te
 on fn.EventClass = te.trace_event_id
where --
1=1
--and EventClass = 42
and textdata like '%udpProductBulletPointSelectByTier1NoteTypeCode%'
--and db_name(databaseid) = 'US16AUTPProduct'
order by starttime desc

But as you can see on the picture below, there are too many rows like this one:

declare @p5 int  set @p5=86400 exec dbo.udpProductBulletPointSelectByTier1NoteTypeCode @Tier1=N'AR763',@LanguageID=2,@SeasonItemID=N'16AUT',@ListNoteTypeCode=N'',@CacheDuration=@p5 output  select @p5

more specifically the procedure was called with an empty parameter value for @ListNoveTypeCode

  @ListNoteTypeCode=N''

How can I filter out all those rows where @ListNoteTypeCode=N''??


texts: how to send mail from gmail smtp server by using the ssis

texts:
I'm just getting into sql-server SSIS, so forgive me if this is a basic question. Use the SSISwant to send mail from  gmail smtp server.

Smtp host =smtp.gmail.com
port=587 ,
From_Mail_ID=xyz@gmail.com,
To_Mail_ID=abc@gmail.com,

Using the above information I want to send mail from SSIS. How do I use SSIS to do this?

If have any questions please ask.


texts: Restore Database using GUI - Wrong file to restore

texts:
I'm just messing around with the SSMS graphic interface and studying the options of the "restore" task.

One thing that I noticed is when I click on "generate script", the first line of the query is:

 RESTORE DATABASE [MyDatabase] FROM  DISK = N'Server_Patch\Database_name_LOGSHIPPING.BKP' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5 ( and a lot of log backups for point in time )

Ok, no problem, but, i'm doing daily backups of this database. this Database_name_LOGSHIPPING.BKP was the name of the file that I made for log shipping a month ago.

Why when I try to use the SSMS graphic interface to restore a backup, it points to this backup file? I don't even have this file anymore.

With this query from MSSQLTIPS I can see all backups from this database:

SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)   and  msdb..backupset.type ='D'
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date

What can be wrong here? I'm not using COPY ONLY.

EDIT2:

i'M making daily manual backups to test, and even this way, SQL Server selects that old backup that doesn't exists anymore. When running RESTORE HEADERONLY... it says ( obviously ) the file does not exists.

EDIT 3:

This the GUI print:

Wow Wow Wow WAIT A MINUTE !

This database was a restore from another server ( same server, different instances ). Huuum... I think the problem is here.

Can you guys see on "server" in the second picture? it has 2 servers. I'm using the one with the names instance GDLIC2014.

the Script:

The backup script:

DECLARE @Patch varchar(1000)

SELECT @Patch = (SELECT 'PATCH\FULL\DATABASE_ ' + convert(varchar(500),GetDate(),112) + '.bkp')

BACKUP DATABASE DATABASE TO DISK=@Patch with compression

With the same query from MSSQLTIPS , I could find these results, using it with no date range:

The red square is the wrong backup from the older instance, the the Blue square is the last backup taken ( the GUI should be using it )

EDIT 4:

Well, With this query to list Backup History, I see that every log and full are correctly listed:

SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO

EDIT5:

Is there something to restart the header of the database

( I'm out of ideas )


texts: Make a query fail in SSMS for higher versions of SQL Server?

texts:
I have SQL server 2014 running on my computer and a database that has a compatibility_level of 100 (SQL Server 2008). I thought that that would be sufficient for writing queries in SSMS that only work for SQL Server versions 2008 and lower but for some reason SSMS runs queries that use OVER (PARTITION BY ...) clauses (with the BETWEEN argument) which are only for 2012 and up.

Our software needs to be compatible with 2008 R2 and up. Is there a setting that I can change that will cause my queries to fail in SSMS if I write a query that is not compatible with 2008 R2?


texts: using Federated tables for different data sources

texts:
We are planning to create a standalone MySQL server specifically for Federated tables that points to 4 DB instances.

Sample table:

federated_db1_users
federated_db1_transactions
federated_db2_users
federated_db2_transactions

Is this kind of setup feasible as long term solution? What we are aiming to do is to centralize all of our data set into 1 data source for our reporting tools.


texts: Should I drop a btree index on a column after adding a unique constraint?

texts:
On one of my existing tables I have a btree index. I am now adding a unique constraint to this column. I know behind the scenes it will add a UNIQUE btree index to this column as well.
In this scenario, should I drop the original btree index added to the column to prevent redundant indexes?


texts: group rows when row count exceeds certain count

texts:
Query:

select city.name as name, sum(count) as views from profpostview
inner join professional as prof on prof.pfid = profpostview.viewerpfid
left join cityinfo as city on city.cityid = prof.cityid
where pfpid = _pfpid group by name order by views desc;

output:

**name , views**

'Ahmedabad', '14'
'Gandhinagar', '7'
'Mumbai', '5'
'Aalborg', '5'
'Rajkot', '3'
'Bhavnagar', '2'
'Baroda', '1'

required:

**name, views**

'Ahmedabad', '14'
'Gandhinagar', '7'
'Aalborg', '5'
'others', '11

explanation: I want to combine rows by adding view count when row count exceeds 3. Profpostview table is related to profpostinfo by pfpid and profpostinfo table is in turn related to cityinfo by cityid.

The final query should never return more than 4 rows. All the (summed) rows after the 3rd should be grouped into a 4th row.


texts: Does granting SA rights to a user in SQL compromise the OS?

texts:
I'm setting up a server that my company will be managing for one of our clients. This server runs an application that is managed by a 3rd party. We are responsible for the server and OS and the 3rd-party is responsible for the application and the MS SQL databases. I'm wondering if the 3rd party is given SA rights to SQL if this will allow them any sort of control over the OS side. SQL is not my forte. We would be using Server 2008 R2 with MS SQL Server 2008.

Thanks.


texts: PostgreSQL: Is it possible to get a different function result type (i.e. TABLE) in stored function based on input?

texts:
The question is as follow, I wanted to get some flexibility in my example function, so based on input arguments to function I wanted to return different table as an output result.

So is it possible to get something like this in function definition:

RETURNS TABLE (
    customer_id INTEGER,
    first_name VARCHAR(45),
    last_name VARCHAR(45),
    country VARCHAR(50)
)

OR

RETURNS TABLE (
    message VARCHAR
)

My broken example function is:

CREATE OR REPLACE FUNCTION get_customer_by_country(
    country_name VARCHAR,
    id_flag BOOLEAN
)
RETURNS TABLE (
    customer_id INTEGER,
    first_name VARCHAR(45),
    last_name VARCHAR(45),
    country VARCHAR(50)
)
AS
$$
<<function_body>>
DECLARE
BEGIN
  IF id_flag THEN
      RETURN QUERY
        SELECT
          cstmr.customer_id,
          cstmr.first_name,
          cstmr.last_name,
          cntr.country
        FROM
          customer AS cstmr
        JOIN address ON cstmr.address_id = address.address_id
        JOIN city ON address.city_id = city.city_id
        JOIN country AS cntr ON city.country_id = cntr.country_id
        WHERE
        cntr.country = country_name;
    ELSE
      RETURN QUERY
        SELECT 'flag was not provided';
    END IF;
END function_body;
$$
LANGUAGE plpgsql;

SELECT get_customer_by_country('Belgium', false);

I searched for a questions like ERROR:  structure of query does not match function result type, but haven't found anything that matched my case.

Thanks in advance!


texts: Connect Application with separate DB links from same server

texts:
My application is connecting to our internal Oracle Server. Our internal Oracle server connects with a third-party server using a DB link. Now, our internal oracle needs to create another DB link to second third party. So our application currently we have,
Application >> connects to internal Internal:SomePort >> connects to third-party ThirdPartyIp:SomePort(via DB link)

Now, all our queries (from the application) are without @DbLink
Now we have created a new DB link on the same internal server,
Application >> SameInternalServer >> connects to another third-party ThirdPartyIp:SomePort(via DB link)

Is it possible to use different connection strings for different third party DB? or we will be struck to @DbLink syntex from the application?
For Third Party, we can use,
select * from ThirdPartyTable

But for the second we have to use,
select * from ThirdPartyTable@DbLink

Any way to mitigate avoid @DbLink because we have DB structure is same for both third-party servers.


texts: Implementing a query, two types of strategies

texts:
Tables have not been created yet, but to simplify there is a groups and an items tables. A group contains an item id (and a group id). And several groups may contain the same item id.

For instance

CREATE TABLE groups (
          gid int,
          iid int,
          primary key(gid,iid)
);
CREATE TABLE items (
          iid int primary key,
          label char(5)
);

and let put some data in these, to help visualize the coming problem

insert into items values (1,"pen"),(2,"gum"),(3,"cat"),(4,"dog"),
                         (5,"hug"),(6,"art"),(7,"fun");

insert into groups values(1,1),(1,2),(1,3),(2,4),(2,5),(2,6),(3,1),
                         (3,4),(4,2),(4,3),(4,5),(4,6),(4,7);

Retrieving items of multiple groups (e.g. groups 1,2,3,4) uses a DISTINCT to remove duplicates

(1) SELECT DISTINCT i.iid,i.label FROM groups g JOIN items i ON g.iid=i.iid
    WHERE g.gid in (1,2,3,4) ORDER BY i.iid;

But since the tables will eventually contain more data, would that query using a subquery be more efficient

(2) SELECT i.iid,i.label FROM items i
    JOIN (SELECT DISTINCT iid FROM groups WHERE gid IN (1,2,3,4)) AS s
    ON i.iid=s.iid ORDER BY i.iid;

since distinct applies only to the items ids, despite the overhead of using a subquery?

Or this one suggested by @hypercube (see comments):

(3) SELECT i.iid,i.label FROM items AS i
    WHERE EXISTS
       (SELECT * FROM groups AS g WHERE g.gid IN (1,2,3,4) AND i.iid=g.iid)
    ORDER BY i.iid

For information, eventually

the items table will have between 50~100k rows
a group will likely have 5~10k items
groups ids to be selected in a single query are in the 5~20 range
probability that an item is part of 2 selected groups: 50%
probability that an item is part of 3 selected groups: 30%
probability that an item is part of 4 selected groups: 10%


texts: I have over 100 system and admin users in my db server. Does anyone know how to show their grants by running a single query? Thank you very much.


texts: SQL Server AlwaysOn from multiple origins to a single secondary replica server

texts:
I started working in a small company which have three separate databases, for three different systems. They are SQL Server 2008, 2012 and 2014, installed in different servers.

We'd like to set up an AlwaysOn AG to replicate into another offsite server (one server only). Is it possible to create 3 availability groups from different servers and SQL versions into a single SQL Server 2014 instance?

I think I'll have troubles setting up the WSFC from multiple origins to a single server.

Is there any other way to fix this issue, since we only have a single offsite server to replicate our systems?


texts: May postgresql's uuid_generate_v1() conflict when we change the computer date/time?

texts:
According to postgresql uuid-ossp documentation uuid_generate_v1() is based on Mac address + timestamp:

https://www.postgresql.org/docs/9.4/static/uuid-ossp.html

On a distributed database scenario where we have hundreds of databases generating records with UUID keys and syncing back to a central database.

Suppose we detect a machine has a wrong date/time in the future and we change it back to the correct date/time. May it generate a conflicted UUID key on this particular machine?

One scenario is the summer time / daylight savings.


texts: Adding Web Reference to Script Task makes the SSIS package invalid.

texts:
Web Reference makes the SSIS package invalid.

Within my SSIS package-controlflow I have a script component and within this component whenever I add a Web reference and close editor, script component gives me "Validation error. Control Flow Task Script Component: The binary code for the script is not found.
Please open the script in the designer by clicking Edit Script button and make sure it builds successfully."

I can successfully build from the editor, but error resides there.
I have search lots of post and sites but haven't find a working solution.
Can someone help me solve this problem.

Ps: I am using SSMS 2012, SSDT for Visual Studio 2012.


texts: Are these two statements the same performance wise?

texts:
Setup

I have a combination index in table SubmissionStatus as show below.  And running a simpl query 2 different ways.

 KEY `Combo_Status_Type_BidID` (`fk_ActivityStatus`,`ActivityType`,`fk_BidIDAssigned`),

Query 1

SELECT *
FROM SubmissionStatus SS
WHERE fk_ActivityStatus IN (5,12)
AND ActivityType = 61

Query 2

SELECT *
FROM SubmissionStatus SS
WHERE ActivityType = 61
AND fk_ActivityStatus IN (5,12)

My understanding

From my understanding the index I have in place here should benefit Query 1 because it has fk_ActivityStatus first, and ActivityType second, just as the Where/And conditions are listed in the statement.

Question

But, Is query 2 also getting to take full advantage of this index?  Or does the fact that it has ActivityType listed first prevent it from being able to use the combo Index?  (Making it having to do a full table scan, or look for an index that has ActivityType as the first.)

P.S.

The data table are currently to small to be able to get much reliable feedback from the EXPLAIN, and execution plan.


texts: Avoid adding an id-only table for a one-to-many table

texts:
I have a table that links user IDs with external identities (one-to-many) and I would like to avoid creating a second table with just IDs.

Simplified example:

CREATE TABLE identities (
    uid     uuid PRIMARY KEY,
    extid   text NOT NULL,
    CONSTRAINT unique_identity UNIQUE (extid)
);

The logic I would like to model is that a user can log in with different external service identities, so the uid could possible have multiple entries; there is one-to-many relationship because each external identity can have only one real user, but real users can have multiple external identities.

However, I wonder if it would be possible to use the uid as a foreign key without having to duplicate this field into a second table with just one single column. Effectively, a one-to-many relationship in one table instead of the obvious JOIN between two tables.

I've thought about storing all the external IDs into an array so there would be only one uid entry, but the recommended GIN index doesn't seem to support unique constraints:

CREATE TABLE identities (
    uid     uuid PRIMARY KEY,
    extids  text[] NOT NULL
);
CREATE INDEX idx_unique_identity ON identities USING GIN(extids);

I've also considered JSONB and recursive self-joining queries, but nothing seems obvious.

So can this be done in a non-hackish way or should I give up and create a table with just one column and JOIN everywhere?


texts: Migration to SQL Database using Transaction Replication - down time?

texts:
I am looking to migrate two on-prem SQL Servers into Azure Hyperscale. Both 28 cores, 1.5 TB of RAM with 10TB data disks.

I can NOT afford any downtime during the migration and have heard transaction replication method might do the trick.

Has anyone had a scenario like this where they used this process?

There really isn't a quiet time. It's a cell phone sub carrier with roughly 2,000,000 users. There is roughly 30GB of data updated twice per minute. Currently on SQL Server 2014 Enterprise Edition.


texts: Dealing with partitioned tables in Postgresql

texts:
Due to historical reasons we used to use one really big table in our Postgresql database to store time-based series of measurements. After a long usage, as the table become 200+ GB in size, we decided we can clean it up a bit, say to keep only 90 days of data to speed things up a bit and also to save some disk space.

The problem is, doing delete from ... where clock_column < ... won't reclaim any space on disk, so we needed both to have VACUUM FULL on it after the delete.

Since we can't afford to stop the DB for such a long time, we decided to go with a partitioning approach and created per-day tables so we'll be able to delete them one by one as time expires. So now we have table (the initial, huge one), and named after date table160101, table160102 etc.

Now I'd like to do:

backup of initial table (only the initial table)

2.1 make some clearing in it (only in it)
or

2.2 maybe simple truncate it (only it)

But as I did the tests, I see COPY table TO 'file' creates dump of the whole table (both table and all of table160101, table160102 etc). The very same fashion, doing TRUNCATE clears the whole table, not the initial one (tested on test server).

When I work with partitioned tables in SELECT, I can simple state ONLY to choose which table to use. But I can not find a way to do that in COPY or TRUNCATE.

So the question is: how can I archive my goals without sacrificing data in the table?

P.S. What I need to archive is when all 'per-day' data are in the 'per-date' tables I simple don't like to keep initial huge table with very old data. In fact the TRUNCATE only on it would be just fine idea.


texts: SSIS: "The buffer manager cannot write 8 bytes to file"

texts:
We have about 60 or more SSAS cubes on one server. All the cubes have the same structure. Each cube has a different size because each cube has a different underlying data warehouse. However the schema of the data warehouses are the same, the only difference is the data.

Each data warehouse is populated using the same ETL job in SSIS. A parameter is passed into the SSIS package that changes the source and destination.

To speed processing we created partitions in the cube. The cubes are processed and ETL ran individually throughout the day with some overlap.

I am looking for advice about how to debug the following error messages we get from SSIS:

  The buffer manager cannot write 8 bytes to file
  "C:\Users\SQLSER~1\AppData\Local\Temp\DTS{A627C703-7524-4906-84D5-6D239B9745FF}.tmp".
  There was insufficient disk space or quota

  Failed to load task "DFT Load Target Stage", type "SSIS.Pipeline.2".
  The contact information for this task is "Performs high-performance
  data extraction, transformation and loading;Microsoft Corporation;
  Microsoft SQL Server v10; (C) 2007 Microsoft Corporation; All Rights
  Reserved;http://www.microsoft.com/sql/support/default.asp;1".

  Unable to load XML data into a local DOM for processing.

  Error 0x800705AF while loading package file "D:\eTrigue
  Library\eTrigue\eTrigue Deploy\BETA\Exec
  Packages\PKG_EventActivity_Staging.dtsx". The paging file is too small
  for this operation to complete.

Why am I not getting the errors every time the SSIS packages are run?

How can I go about finding the root of the problem?

The errors started to happen after I added the partitions to the cube.  I use an SSIS script task to processes partitions that is called in the ETL.

Is there a way to find memory leaks in SSIS script tasks?

Anybody want to see the code?


texts: Downgrading SQL Server 2008 R2 Standard to Web Edition

texts:
I have to downgrade a SQL Server running SQL Server 2008 R2 Standard Edition SP1 to 2008 R2 Web Edition SP1.

I discovered this question on Server Fault:

  Can I downgrade SQL Server 2008 R2 Standard To Web Edition?

Which links to a similar scenario:

  Downgrading from SQL Server 2008 R2 Enterprise Edition (trial) to R2 Web Edition (licensed)

This server doesn't have Analysis Services installed and I tried to run the Edition "Upgrade" tool and got this failure:

I checked the detailed report and all that it says is:

Rule Name: EditionUpgradeMatrixCheck
Rule Description: Checks whether the selected instance of SQL Server meets upgrade matrix requirements.
Result: Failed
Message/Corrective Action: The selected SQL Server instance does not meet upgrade matrix requirements.

Strangely the Edition Downgrade rule has passed.

I ran this from the SQL 2008 R2 Web Edition DVD.

Can anyone guide me through downgrading this SQL server to Web Edition, I am now at a loss.


texts: Sum of values each hour at half past (that hour)


texts: SQL Server 2005/2008 UTF-8 Collation/Charset

texts:
I can't find option(s) directly to set UTF-8 rellated Collations/Charsets in SQL Server 2005/2008, same as is possible to set in another SQL engines, but in SQL Server 2005/2008 are there only Latin and SQL collations.

Is there some option to force/install these collations / charsets in SQL Server engine (for both ver.) 2005/2008 on Win2008 OS


texts:
I have a MySQL database where I would like to return results based on multiple rows.

id | key        | value      | selector
---------------------------------------------------------
1  | username   | person     | 123459795181089198123
2  | password   | PPassword! | 123459795181089198123
3  | firstname  | John       | 123459795181089198123
4  | lastname   | Smith      | 123459795181089198123

Basically, I'm trying to avoid having many columns and altering the database tables, by creating the key/value/selector style instead.

Now the problem I'm having is with slowness on a server I use.  Right now, my SQL query looks something like this:

SELECT *
FROM users
WHERE selector IN (
    SELECT selector
    FROM users
    WHERE (`key` = 'username' && `value` = 'person') || (`key` = 'firstname' && `value` = 'John')) AND `key` = 'password';

This returns something like:

id | key        | value      | selector
---------------------------------------------------------
2  | password   | PPassword! | 123459795181089198123

It works great, except that I'm running this on 20,000 records which causes the performance to drop drastically.

My question is, is there a better way to do this while using the same type of data structure?


texts:
How I can give the sys_admin rights to the local administrator for the SQL Server 2014 database while I installed it using the domain admin account, not local administrator account? Below is the requirement of a manual.

  SQL Server installation

  During the installation of AlarmInsight
  components it is required to use local Administrator login. The SQL
  Server Installation can also be done with local Administrator login by
  giving required access rights. If another login is used to install SQL
  Server, the local Administrator has to be given sys_admin rights in
  the SQL Server Database.


texts: How to validate data before loading it to DB

texts:
I have a situation where in I have million records in CSV file, what I need to do is to apply some business validation on certain columns of CSV and load data to DB only if particular record is valid.

I tried with per record strategy but that could turn into nightmare if the DB is hit millions of times.

I am using MongoDB to store metaData and Redshift for storing actual data.


texts: REORG command does not work in IBM Data studio but same works in Command Line Processor

texts:
I'm using the latest version of IBM Data Studio 4.1.2.0

I tried to reorg a table using IBM Data Studio after some alterations on the table with this command.

reorg table MYTABLE;

This command shows an error

  REFRESH was expected instead of reorg

But even after using the REFRESH command, the table doesn't come out of the "reorg pending" state.

The same command when I tried it via the DB2 Command Line Processor worked perfectly fine.

Any suggestions on how to to enable the REORG command in Data Studio or is it not supported at all?


texts: Mirrored DB on Restoring state after removing Mirroing also

texts: We done Mirroring of MS SQL data base to other system without witness on synchronize mode. When I Remove mirroring(not pausing , not fail over) by manually My Mirrored Database still continuing in Restoring state


texts: Is there a way to list all tables and when they were last accessed?

texts: I'm wondering if there's a way in SQL Server to make a list of all tables and when they were last accessed (select / update / insert or delete) either by a user, view or stored procedure?


texts: How do you design schema for tracking change history in NoSQL?

texts:
I'm thinking of trying a document store db for a project that needs to keep track of changes. I'd like to prototype with firebase, but I think the schema/implementation would be the same in MongoDb

The (simplified) setup is that I have three tables/collections Project, Asset and User. A (logged-in) User can change (Create, Update or Delete) a Project or an Asset. The Asset can be assigned to (maximum one) Project.

Any time the Asset or Project has any changes made, I'd like to capture the User that made the change, when they made it, and in most cases, the previous state.


texts: Sorting a list of records alphabetically: is there any reason NOT to let the database do it

texts:
I have a list of records that contain a column of arbitrary strings. I would like the application to display them sorted in some order (preferably alphabetically, since humans have to read the data).

I can choose to have the database sort the data before returning it to me, or I can choose to have the database give me the raw search results and let the application deal with it.

Currently the database is sorting it cause I don't think I can write a very efficient sort anyways, but I'm wondering whether there are any situations where I want a sorted list, but it would be better to have the application do it?


texts: SQL Server Execution Plan on server level

texts:
Case: I have application which is doing search from database. That is going very slowly (more than 40 seconds). With trace I caught a query. When I run query in SSMS, it takes 2-3 seconds, but when I turn on execution plan it goes to more than 40 seconds. Application by design offers 2 different languages (collations). After investigation, I found out that when language is English (default collation) application works "as quick as possible" (6-7 seconds) but when it language is changed, obviously, SQL server doing recompile and everytime response is more than 40 seconds.

Questions: Can I somehow avoid recompile after collation changing?

SQL Server 2014.


texts: How can I have an aggregate function in the WHERE clause?

texts:
Using PostgreSQL 9.3.24

Issue

Just simple operation, but I don't know how to do that best simplest way.

How can I display results only when sum of available_counts for same product_id is 0 and onway is 0 or NULL? I.e. 130479 in this case. I want display all values, for every store_id.

Thank you.

Update

I tried aggregate functions are not allowed in WHERE.

select
*

from
j_product_store_availability psa

where
sum(available_count) = '0'
and onway = '0'

group by
product_id

order by
product_id

Input.

product_id | store_id | available count | onway
1          | 1        | 0               | 0
1          | 2        | 0               | 0
1          | 3        | 0               | 0
2          | 1        | 0               | 0
2          | 2        | 0               | 0
2          | 3        | 0               | 0
3          | 1        | 0               | 0
3          | 2        | 0               | 0
3          | 3        | 1               | 0
4          | 1        | 0               | 0
4          | 2        | 0               | 0
4          | 3        | 0               | 1

Expected output.

product_id | store_id | available count | onway
1          | 1        | 0               | 0
1          | 2        | 0               | 0
1          | 3        | 0               | 0
2          | 1        | 0               | 0
2          | 2        | 0               | 0
2          | 3        | 0               | 0


texts: What's the maximum number of partition per table I can/should have in MySql 8?

texts:
I have around 1T (1,000,000,000) records of products belong to 500,000 accounts. Each product record have the account_id and since I always query within a single account context (there are no cross-account queries) I thought of creating a partition per account to improve performance.

Is it even possible to create 500,000 list partitions in mysql8?
Will it required changing in the number of open files the process required?
If it is possible, is there a reason for me not to go this path?


texts: Is there a syntax to access the table name in the select query?

texts:
In MYSQL. I want to select the records which has the same contents with the table name.

for example, consider the following table.

<TABLE : tbl_1>
+-------+------+
| id    | no   |
+-------+------+
| admin |    1 |
| guest |    2 |
| tbl_1 |    3 |
+-------+------+

In this case, I want to select the 3rd record.
How can I do this with a single select query?

select * from tbl_1 where id=(name of table);

I can't use
    select * from tbl_1 where id='tbl_1'
since the table name will be decided during runtime.

P.S I can't access information_schema database.

thank you in advance


texts:
If I have administrator rights, would it possible to view details on the compiled version of a stored procedure or a view?

Is there a visual representation available?


texts: SQL Server column type = Date not accepting dates as varchar in insert statemant

texts:
In T-SQL, I am trying to insert values in a date table, US & French dates. When table is created with columns as DATE, the insert does not work, it errors.

When I switch to VARCHAR(12) instead of DATE for those columns, INSERT works fine

My server is windows 2012, english Microsoft SQL Server 2016 (SP2-CU10) (KB4524334) - 13.0.5492.2 (X64), regional settings France
I googled but not able to find a solution.

Here are 3 scripts, 1st one is the table with columns with DATE type (with which insert statement fails)

CREATE TABLE [dbo].[DimDateTest](
[ShortDate] [date] NULL,
[FullDate] [varchar](20) NULL,
[MonthShortName] [varchar](10) NULL,
[MonthLongName] [varchar](15) NULL,
[FrenchShortYear] [date] NULL,
[FrenchUSLongYear] [date] NULL,
[FrenchLongYear] [date] NULL,
[DateAge] [varchar](20) NULL
) ON [PRIMARY]
GO

Here is the insert statement:

INSERT dbo.DimDateTest
( ShortDate, FullDate, MonthShortName, MonthLongName, FrenchShortYear, FrenchUSLongYear, FrenchLongYear, DateAge)
SELECT CAST(DATEADD(dd, Number.n-1, '1/1/2015') AS DATE)
,CAST(DATENAME(mm, DATEADD(dd, Number.n-1, '1/1/2015')) AS VARCHAR(10)) + ' ' + CAST(DATEPART(dd, DATEADD(dd, Number.n-1, '1/1/2015')) AS VARCHAR(2)) + ', ' + CAST(YEAR(DATEADD(dd, Number.n-1, '1/1/2015')) AS VARCHAR(4))
,CAST(DATENAME(mm, DATEADD(dd, Number.n-1, '1/1/2015')) AS VARCHAR(10))
,CAST(DATENAME(mm, DATEADD(dd, Number.n-1, '1/1/2015')) AS VARCHAR(10)) + ' ' + CAST(YEAR(DATEADD(dd, Number.n-1, '1/1/2015')) AS VARCHAR(4))
, CONVERT(date, CAST(DATEADD(dd, Number.n-1, '1/1/2015') as datetime), 5) as FrenchShortYear
, FORMAT( CAST(DATEADD(dd, Number.n-1, '1/1/2015') as date), 'dd/MM/yyyy', 'fr-FR' ) AS 'FrenchUSLongYear'
, CONVERT(VARCHAR(12), CAST(DATEADD(dd, Number.n-1, '1/1/2015') as date), 7) as 'FrenchLongYear'
,
CASE WHEN DATEADD(dd, Number.n-1, '1/1/2015') BETWEEN '1/1/2015' AND '3/31/2015' THEN 'Age of Goats'
WHEN DATEADD(dd, Number.n-1, '1/1/2015') BETWEEN '4/1/2015' AND '4/30/2015' THEN 'Age of Copper'
ELSE 'Age of the Unknown'
END
FROM dbo.Number
WHERE DATEADD(dd, Number.n-1, '1/1/2015') <= '4/30/2020'
ORDER BY Number.n

and here is the error I am getting

  Msg 241, Level 16, State 1, Line 13 Conversion failed when converting
  date and/or time from character string.

When I drop and recreate the table by switching Columns type from DATE to varchar(12), insert works fine, here is the script for the table

CREATE TABLE [dbo].[DimDateTest](
[ShortDate] [date] NULL,
[FullDate] [varchar](20) NULL,
[MonthShortName] [varchar](10) NULL,
[MonthLongName] [varchar](15) NULL,
[FrenchShortYear] [varchar](12) NULL,
[FrenchUSLongYear] [varchar](12) NULL,
[FrenchLongYear] [varchar](12) NULL,
[DateAge] [varchar](20) NULL
) ON [PRIMARY]
GO

  NOTE : dbo.Number is a number table populated by numbers from 1 to
  10000

What am I doing writing wrong?
Thanks for your help


texts: Is SARGable query the first thing to put in developers' minds?

texts:
Is SARGable query the first thing to put in developers' minds from a performance tuning perspective?
It's been a while that I work through this way, but I was just wondering if you guys have the same approach, considering Microsoft will not Make more functions SARGable.


texts: ISO 27001 - resetting passwords for SQL Server accounts

texts:
We are implementing security changes in order to pass an ISO 27001 audit.
We are in the process of setting all domain accounts (user accounts, admin accounts, service accounts) to have their passwords changed automatically by our information security management system (Thycotic).

The question is, do we need to also change the non-domain SQL Server authentication accounts (such as the "SA" accounts) and if so do you know of a way to do this?

Is is standard to reset the passwords for these SQL Server authentication accounts?


texts: Best Innodb Buffer Pool Size for 8GB RAM?

texts: I found out that my MySQL server is configured with only 128MB for innodb_buffer_pool_size.  That is not enough considering that I have a 8GB RAM server. It hosts MySQL server and an Apache/Catalina server.  What should I change this seeting to?


texts: Efficiently finding the ultimate parent in multi-level one-to-many tables

texts:
Let's say I have a database with multi-level one-to-many tables (a contrived example):

CREATE TABLE users (
  id SERIAL PRIMARY KEY
);

CREATE TABLE properties (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id)
);

CREATE TABLE rooms (
  id SERIAL PRIMARY KEY,
  property_id INTEGER NOT NULL REFERENCES properties(id)
)

CREATE TABLE rates (
  id SERIAL PRIMARY KEY,
  price INTEGER NOT NULL,
  room_id INTEGER NOT NULL REFERENCES rooms(id)
)

Let's say a user wants to update the price in rates table but I want to make sure that he is allowed to do so - i.e. the rate belongs to a room that itself belongs to a property that the user owns.

What would be the best/efficient way to do so? Is there a way to add a "deduced" user_id column to the rates table that is automatically populated as the rows are added to the rates table so later I can use it in a query like:

UPDATE rates SET price=100 WHERE (id=30) AND (user_id=1)`


texts: How to save working hours of multiple stores and then query it without duplication?

texts:
I'm want to create database with stores. Each store has their own working hours in a week. Then I want to query the store and sort them by nearest store which is currently open. So far I create this schema but unfortunatly I cannot query it like I would like to. The schema looks like this

This is the query I'm using currently (for day: Saturday and hour: 3:15 PM):

SELECT so.day, so.store_id, so.open, so.close
FROM shop_openings so
ORDER BY FIELD(so.day, 6, 7, 1, 2, 3, 4, 5) ASC,
         '15:15' COLLATE utf8mb4_unicode_520_ci
                 NOT BETWEEN CONCAT( LPAD( so.open, 2, '0'), ':00')
                         AND CONCAT( LPAD( so.close, 2, '0'), ':00'),
         LPAD( so.close, 2, '0' ),
         LPAD( so.open, 2, '0' )

But it query just like the screen above shows.

Now here's what the list I want to achieve:

Let we say that today is Saturday. We have 4 stores which only 2 of them works in Saturday. I want to recieve 4 results order by day and open/close hours. Below I selected which rows I want to get:

No other results. Only 4 results as we have 4 stores in store_table. Nearest 2 shops opened in Saturday and then 2 shops opened in Monday.

I have no idea how the schema should like other than mine, so any suggestion are welcome.

Here is a SQL Fiddle: http://sqlfiddle.com/#!9/9709e/1/0


texts:
I need to select all routines names from a DB2 luw.
I saw the following catalog tables:

sysibm.routines
syscat.routines

If I run the following queries:

select count(*) from sysibm.routines;
select count(*) from syscat.routines;

There is a big Difference between the results.
Can anyone tell what is the difference between these views and which one should be used for the task of selecting all routines names?


texts: Sparse boolean tags: many columns or multiple tables?

texts:
I'm pretty new to database design. I'm storing records which are tagged with some (few) of a (large) number of possible tags (much like StackExchange questions). At the moment I have a single table with one record per row and one column per tag, very simple.

But then I read that "a well-normalized database will never need more than 100 columns in a table", which clashes with my approach of simply adding a new column every time a new type of tag is created.

I guess that the alternative is to have one table per tag, and to JOIN when I want to work out which tags a particular record has. When I create a new tag I'd make a new table rather than a new column.

At what point should I normalise? Clearly if I have millions of possible tags, and each record has only ~10 tags selected, then I'm wasting space by storing the sparse columns. Is there another disadvantage to continually adding columns to a simple table?

Is there an objective answer to this? Does it depend on frequency of new tag-type creation? Or sparsity? "100 columns" seems like a very arbitrary limit.

If it makes a difference I'm using SQLite.


texts:
I am am trying to learn mysql 101. Doing so, I broke my ability to login as root.

At first I could simply connect to mysql by running

sudo mysql

In an attempt to alleviate a problem, I thoughtlessly copy and pasted some sql into the mysql prompt.

Specifically...

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Now when I try to log in using sudo mysql, I get the error...

  Access denied for user 'me'@'localhost' (using password: NO)

What exactly did I do, and how can I rectify it?


texts: wait_timeout kills session in SLEEP MODE or all connection ?`

texts: Do wait_timeout session clear all running session or only sessions which are in SLEEP mode for specified seconds ?


texts:
I have a query:

SELECT
    Author.id,
    Author.name,
    Author.surname,
    Author.photoURL as photoURL,
    Movie.title as lastMovieTitle,
    Movie.publicationDate as lastMovieDate,
    Movie.languageID as lastMovieLanguage,
    Series.name as lastMovieSeriesName,
    CASE WHEN UserAuthorFavourited.userID = '6F9BD058-853F-4B50-92D6-9170118A2727' THEN TRUE ELSE FALSE END as favourited,
    sum(CASE WHEN MovieRating.ratingUp = TRUE THEN 1 ELSE 0 END) as ratingUp,
    sum(CASE WHEN MovieRating.ratingDown = TRUE THEN 1 ELSE 0 END) as ratingDown,
    count(DISTINCT Movie.id) as movieCount

FROM Author

LEFT JOIN Movie ON Author.id = Movie.authorID
LEFT JOIN Series ON Movie.seriesID = Series.id
LEFT JOIN UserAuthorFavourited ON UserAuthorFavourited.authorID = Author.id AND UserAuthorFavourited.userID = '6F9BD058-853F-4B50-92D6-9170118A2727'
LEFT JOIN MovieRating ON Movie.id = MovieRating.movieID AND Movie.authorID = Author.id

GROUP BY
    Author.id,
    Movie.id,
    Series.id,
    UserAuthorFavourited.userID

ORDER BY Author.surname ASC, Movie.publicationDate DESC

If author has more than one Movie it lists this Author duplicated rows instead of listing only once with most recent movie (not as many times as there are this authors movies) I tried to add Distinct ON Author.id but this not change anything.

How to refactor this query to return proper results?


texts:
I have a table containing data that represents a tree structure of data, using the nested set model.  I'd like to store data for more than one tree in the table and have only one of the sets of data be available.  The idea is that there may be more than one version of the tree and even trees in development before going "live" with them.

My question is about where to safely store the "This is the live set" flag.

This is what I have (minimalized):

TreeDataSets
id
DataText
...
VersionId

Versions
id
VerName
InUse

Version
id
InUse

TreeDataSets
ID  DataText    VersionId
1   ABCD           100
2   EFGH           100
3   IJKL           100
4   1234           101
5   5678           101
6   3456           101
7   AB12           102
8   CD34           102

Versions
ID  Vername     InUse
100 Characters  Y
101 Numbers     N
102 Mixed       N

Version
ID  InUse
1   100

So, I can either store the "InUse" flag against every row of the versions table, or have it in a separate table.  (Both option are shown - I hope you get the idea).

Disadvantages with storing the flag in every row is that if more than one row is set to InUse, the data will be messed up.  The disadvantages with storing the flag in an extra table is err, that there is an extra table and sql is a little more complex.

Is this a common data storage pattern?  What is the best practice?

Is there a way of enforcing only one row is set to true within the database, if the 2 table option is preferred?


texts: Error on PostgreSQL streaming replication

texts:
I have the exact same problem as Wal segment still exists on master, but logs on slave and master say it's been removed except that it is in Production !

PostgreSQL 9.4 / Debian Jessie

postgresql.conf:

wal_level = hot_standby
max_wal_senders = 5

pg_hba.conf

host    replication     repl        x.x.x.0/20               trust

On the slave replica : Recovery.conf

standby_mode = on
primary_conninfo = 'host=<master_server_ip> port=xxxx user=xxxx password=xxxx'
trigger_file = '/var/lib/postgresql/trigger_failover'

Error on Master:

ERROR:  requested WAL segment 000000030000007E00000054 has already been removed

Error on slave replica:

LOG:  started streaming WAL from primary at 7E/54000000 on timeline 3
UTC [31252-2] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000030000007E00000054 has already been removed

What can I do to correct it without stopping the production ?

It is not working for several months now, so I guess there is a lot to recover. I can't even find the archives in ./postgresql/9.4/main/mnt/server/archivedir/