texts: Optimizing SELECT query consistently showing up in slow-log

texts:
In a app called 'Links', users post links and photos of interesting content they've discovered recently (and others comment on the said postings).

These posted comments under the photos are saved in a links_photocomment table in my postgresql 9.6.5 DB.

One SELECT query on the links_photocomment table is consistently showing up in slow_log. It's taking longer than 500ms, and is ~10X slower than what I'm experiencing in most other postgresql operations.

Here's an example of the corresponding SQL from my slow log:

LOG:  duration: 5071.112 ms  statement:

SELECT "links_photocomment"."abuse",
       "links_photocomment"."text",
       "links_photocomment"."id",
       "links_photocomment"."submitted_by_id",
       "links_photocomment"."submitted_on",
       "auth_user"."username",
       "links_userprofile"."score"
FROM   "links_photocomment"
       INNER JOIN "auth_user"
               ON ( "links_photocomment"."submitted_by_id" = "auth_user"."id" )
       LEFT OUTER JOIN "links_userprofile"
                    ON ( "auth_user"."id" = "links_userprofile"."user_id" )
WHERE  "links_photocomment"."which_photo_id" = 3115087
ORDER  BY "links_photocomment"."id" DESC
LIMIT  25;

See the the explain analyze results: https://explain.depesz.com/s/UuCk

The query ends up filtering 19,100,179 rows according to that!

What I've tried:

My gut feel is that Postgres bases this query plan on misleading statistics. Thus, I've run VACUUM ANALYZE on the said table. However this hasn't changed anything.

Being an accidental DBA of sorts, I'm looking for some quick expert guidance on the subject. Thanks in advance and apologies for the noob question (if it is).

Addendum:

Here's the entire output for \d links_photocomment:

                                      Table "public.links_photocomment"
     Column      |           Type           |                            Modifiers
-----------------+--------------------------+-----------------------------------------------------------------
 id              | integer                  | not null default nextval('links_photocomment_id_seq'::regclass)
 which_photo_id  | integer                  | not null
 text            | text                     | not null
 device          | character varying(10)    | not null
 submitted_by_id | integer                  | not null
 submitted_on    | timestamp with time zone | not null
 image_comment   | character varying(100)   | not null
 has_image       | boolean                  | not null
 abuse           | boolean                  | default false
Indexes:
    "links_photocomment_pkey" PRIMARY KEY, btree (id)
    "links_photocomment_submitted_by_id" btree (submitted_by_id)
    "links_photocomment_which_photo_id" btree (which_photo_id)
Foreign-key constraints:
    "links_photocomment_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "links_photocomment_which_photo_id_fkey" FOREIGN KEY (which_photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_photo" CONSTRAINT "latest_comment_id_refs_id_f2566197" FOREIGN KEY (latest_comment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_report" CONSTRAINT "links_report_which_photocomment_id_fkey" FOREIGN KEY (which_photocomment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photo" CONSTRAINT "second_latest_comment_id_refs_id_f2566197" FOREIGN KEY (second_latest_comment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED


texts: Why do I need to use a sub query to filter down a grouped select?

texts:
If I do this --

SELECT dv.Name
      ,MAX(hb.[DateEntered]) as DE
FROM
    [Devices] as dv
    INNER JOIN
    [Heartbeats] as hb ON hb.DeviceID = dv.ID
WHERE DE < '2013-03-04'
GROUP BY dv.Name

I get this error --

  Msg 207, Level 16, State 1, Line 17 Invalid column name 'DE'.

If I do this --

SELECT Name, DE FROM (
    SELECT dv.Name
          ,MAX(hb.[DateEntered]) as DE
    FROM
        [Devices] as dv
        INNER JOIN
        [Heartbeats] as hb ON hb.DeviceID = dv.ID
    GROUP BY dv.Name
) as tmp WHERE tmp.DE < '2013-03-04'

it works as expected.

Can someone explain why I need to nest my main query as a subquery to limit my data set?

Also, is there maybe a better way to achieve the goal here? Retrieve all records from one table, and the single "top" related record ordered by [DateEntered] descending?


texts:
I have many schemas in one oracle databse named SCHEMA1, SCHEMA2...SCHEMA999

I want to  find specific value of column named "JN_OSUSER" which is string 'Ed.Spencer'

How to find in every table, in my wholedatabase, column value which contain string value 'Ed.Spencer'?


texts: Cumulative Subtraction from external input

texts:
I have an external value 20. I need 1 to be subtracted from 20 (=> 19) and then 2 to be subtracted from 19 (=> 17) and so on.

Data:

Serial No | Value1 | External value(20)
       A  |     1  |                19
       B  |     2  |                17
       C  |     3  |                14
       D  |     4  |                10
       E  |     5  |                 5

How can I do it?


texts: How to check what Database Engine is installed on the DataBase server that I have acces to run queries on?

texts:
I want to check what type of sql is running on a Datasase server that I can access. I only have access to a web interface and a list of tables.

Through the interface I can run queries on the tables that are present on a list.

How can I get more information about the server and the version that the server is running. I have no idea about the IP or the PORT that the server is running.

I want to know if the server is MySQL, Mircosoft SQL Server, Oracle SQL, Postgre SQL or other sql server.

The website that I am talking about is this one:
w3schools.com SQL editor.

EDIT 2: although for some the command select sqlite_version() works for me it does not work. This is the screenshot of the response.

EDIT 3 :
On Chromium Browser the command is working properly. However on Firefox Browser the command did not work.

I also mention that I am on running Linux.

What do you think could be the reason why on Firefox and on Chrome I get different results ?


texts: How do I Loop through a table and update a field in SQL

texts:
I need to update a column with an sequential number starting with 1 based on the accountId in the table. How do I do this?

so OrderID is NULL to start.
so for each row of AccountID I need to update the OrderID to start from 1 and sequentially update so my data comes out like this.
The table is called Renewals

RowID  AccountID  OrderID
1      A          1
2      A          2
4      A          3
5      B          1
6      B          2
7      C          1


texts:
I am experiencing problems with mysql 5.1 at the moment and possibly due to that I am unable to connect.

The port that I am using for mysql is open (http://www.yougetsignal.com/tools/open-ports/)

All of a sudden i was unable to access mysql remotely, could be due to luck of memory or something similar?! I can put any user name and password and still same result.

I have no data in the log files to share, nothing is logged for some reason.

I have checked and mysqld does listen to correct port

When I telnet xx.xx.xx.xx xxxx I have following result

Trying xx.xx.xx.xx...
Connected to xx.xx.xx.xx.
Escape character is '^]'.
?
5.1.66-0+squeeze1l_k{KBI'(7y!1rlHc8,u^CConnection closed by foreign host.

"and see if you get the same ... if not then you've a problem with the server. if that works ok but you can't do the same from the client machine(s) then you've got a problem at the client end." (http://ubuntuforums.org/archive/index.php/t-1625679.html) So this is a client problem


texts: Why using a local temp table (instead of a global temp table or a regular table) influences the Query Optimizer to choose a poor query plan?

texts:
This Question brings a situation where the Query Optimizer chooses poorly the seek predicate among the existing predicates of a simple query. After running some tests I got to the conclusion that the poor decision is due to the use of a local temp table instead of a global temp table or a regular table.
db fiddle: Local Temp Table, Global Temp Table, Regular Table.

I couldn't find any characteristic on the Temporary Tables doc that would explain the different behavior we see when using a local temporary table instead of a global temporary table or a regular table. Is there a logical reason for this or could it be a bug?


texts: `Invoke-SqlCmd` does not display RAISERROR messages where `sqlcmd` does

texts:
I switched from sqlcmd to Invoke-SqlCmd, and suddenly my RAISERROR messages disappeared. When I run it with sqlcmd, they appear. Is there any way to make them visible with Invoke-SqlCmd?
RAISERROR (N'Hello World',0,1) WITH NOWAIT;

Invoke-SqlCmd -Server $env:MSSQL_SERVERNAME -Username $env:MSSQL_USERNAME -Password $env:MSSQL_PASSWORD -Database "mydatabase" -Query "EXEC dbo.myprocedure" -OutputSqlErrors $true;

/opt/mssql-tools/bin/sqlcmd -S $env:MSSQL_SERVERNAME -U $env:MSSQL_USERNAME -P $env:MSSQL_PASSWORD -d mydatabase -Q "EXEC dbo.myprocedure";


texts: How to identify the db orphan mdf and ldf files

texts:
I want to identify the orphan mdf and ldf files on the disk and remove them. After searching on the internet, found the script below on MSDN forum. But when I run the script it returns many system-like db files that don't seem to orphan or at least not safe to delete!

Do you know any better way to identify the orphan files? Or can you explain what these db files are referring to (is it safe to delete them)?

For example the files below came in the result of this query:

/*
exec sp_configure 'show advanced' ,1
reconfigure
exec sp_configure 'xp_cmdshell' ,1
reconfigure
*/
if object_id('tempdb.dbo.#os_files') is not null
    drop table #os_files
create table #os_files([filename] varchar(2000))

--list all .mdf and .ldf files on the c drive
-- you will need to call this again to
-- populate the #os_files table, if you have
-- db files on other databases eg. d:, e:

insert into #os_files exec xp_cmdshell 'DIR C:\*.mdf /b /s'
insert into #os_files exec xp_cmdshell 'DIR C:\*.ldf /b /s'

delete from #os_files where filename is null

update #os_files set filename=rtrim(filename)

select
    os.filename as orphaned_files
from
    #os_files os
    left outer join master.dbo.sysaltfiles db on rtrim(db.filename) = os.filename
where
    db.dbid is null
order by 1

Thank you.


texts: mysqld - cannot connect to socket after changing datadir location

texts:
I have this weird problem after changing the MySQL datadir location from default "/var/lib/mysql" It was working yesterday, but today I get "Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'," a typical message if the daemon is not running and it's looking for the socket file in the old datadir location.

I changed the my.cnf file and mysqld file to point to the new location, NEW_DIR.
The daemon runs, but I cannot open mysql.

Thanks in advance.

# cat /etc/my.cnf
[mysqld]
datadir=/NEW_DIR/mysql
socket=/NEW_DIR/mysql/mysql.sock

# cat /etc/init.d/mysqld | grep datadir
get_mysql_option mysqld datadir "/NEW_DIR/mysql"
datadir="$result"
get_mysql_option mysqld socket "$datadir/mysql.sock"

# /etc/init.d/mysqld start
Starting MySQL:                                            [  OK  ]

# tail mysqld.log
120203 11:12:20  mysqld started
120203 11:12:20  InnoDB: Started; log sequence number 0 43655
120203 11:12:20 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.77'  socket: '/NEW_DIR/mysql/mysql.sock'  port: 3306  Source distribution

# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)


texts: Optimizing DELETE Query on MySQL MEMORY Table

texts:
I run a large forum which maintains a MySQL database for backend data storage. The 'session' table tracks logged in users & guests. It is currently about 100k records, so not really that large. However, this session table is showing up in the slow query log when we trim old records:

# Time: 120719 10:05:11
# User@Host: xxx[xxx] @  [10.x.x.x]
# Thread_id: 369051896  Schema: forumdb  Last_errno: 0  Killed: 0
# Query_time: 8.352811  Lock_time: 0.000028  Rows_sent: 0  Rows_examined: 19635  Rows_affected: 19635  Rows_read: 0
# Bytes_sent: 13  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
SET timestamp=1342710311;
DELETE FROM session
    WHERE lastactivity < 1342709401;

I've confirmed there is a BTREE index on the lastactivity table:

mysql> SHOW INDEX FROM session FROM forumdb;
+---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| session |          0 | PRIMARY      |            1 | sessionhash  | NULL      |       78941 |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | userid       |            1 | userid       | NULL      |       26313 |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | idhash       |            1 | idhash       | NULL      |        8771 |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | userid_2     |            1 | userid       | NULL      |        NULL |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | userid_2     |            2 | lastactivity | NULL      |       39470 |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | userid_3     |            1 | userid       | NULL      |        NULL |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | userid_3     |            2 | host         | NULL      |       39470 |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | lastactivity |            1 | lastactivity | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (0.00 sec)

I'm curious why the delete query is taking so long with so few records.

It should be noted that this table is utilized very heavily with so many users online at the same time. We have some heavy hardware sitting behind it.

Any ideas on what I can do to speed up this process? It appears to be locking the table so that I see high load across my cluster when this delete function is taking place.

Query cache is turned off (we use memcache). Here is relevant portions of my.cnf:

table_open_cache=8242
table_definition_cache=600
open_files_limit=65535
binlog_cache_size=6M
sort_buffer_size=8M
key_buffer_size=5G
myisam_sort_buffer_size=256M
join_buffer_size=3M
thread_cache_size=1000
thread_concurrency=16
ft_min_word_len=3
tmp_table_size=512M
max_allowed_packet=128M
max_heap_table_size=512M
read_rnd_buffer_size=1M
skip-external-locking

query_cache_limit=1M
query_cache_size= 32M
query_cache_type = 0

Finally, here is some more info on the table:

FIXED:

After changing table over to InnoDB (at 17:15 in the chart below) I am seeing much better performance and no more DELETE in the slowlogs for this table. The whole cluster is seeing better performance since making this change. Thank you.


texts: SQL transaction: Delete between two selects possible?

texts:
I have two SELECT statements within a transaction (repeatable read)

Select @firstItem =id from myTable Where ....

--Do some more magic, so I can't concat the the two queries!!!!

and

Select * from myTable where parent=@firstItem

Is it possible that the children (second query) of the parent(first query) are deleted (from another transaction) after the initial/first select?

How can I prevent this with locks?


texts: How do I write a mysql query to combine data from related tables into a single summary?

texts:
I'm having trouble defining the data in a query for a chart. I want to summarize data from the first table and combine it with a data summary from the second table.

Products   - (serial_number, date)
Warranties - (id, lookup_to_serial_number, warranty_issue)

What I want to do is to get the total number of products grouped by month and the total number of Warranties grouped together by month of production. It is a one product to many Warranties relationship. I can figure out how to get one or the other, but not both. The product serial number is unique but can have many Warranties on a single product.

The chart will be total products produced in a month compared to total number of Warranties on the products produced in that month. Both totals will be plotted on the same chart.

This is what I have so far :

select count(warr_id), prod_date
from product_t, warr_t
where prod_date is not null and serial_number = product_look
group by year(prod_date), month(prod_date)

union

select count(serial_number), prod_date
from product_t
where prod_date is not null
group by year(prod_date), month(prod_date)

This returns the results I'm looking for, but is there a way to differentiate between the two sets of data? I just came up with this an hour ago.


texts: Moving SQL Server default instance's folders to another path on the same server

texts:
My customer installed SQL Server 2012 default instance. But default instance's folders and data folder are on the same disk.

I want to move the default instance's folders to another disk (C:Program Files\Sql Server\).

Can I do it without uninstalling exist instance then installing new instance?


texts: I am currently learning TSQL and am learning that were possible, I should use "standard" SQL dialect. Where can I find out which commands are "standard" and which are TSQL?


texts: Recover corrupted blocks in Oracle 10g XE

texts:
I am using Oracle 10g XE. I've noticed corrupted block error in datafile 1 in alert log. As I've found Oracle XE doesn't support Block Media Recovery(needs to be Enterprise Edition).

So I did the following:

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

But corrupted blocks are still there.

Should not have my recover command solved this problem? Why not?

Edit

I have restored before recover and now it works. So the following steps are working:

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

Does recover just looks for the SCN and if the SCN is current for the datafile then recovers nothing? Not even corrupted ones?

As I guess recovery is just to apply archive logs(or online logs) to catch up to the current SCN, but not correcting corruptions.


texts: How to create two sqlite triggers for two tables without conflict?

texts:
Below are my example queries:
        """
CREATE TABLE IF NOT EXISTS projects (
    Project_ID  TEXT UNIQUE,
    Initial_Project_count   TEXT,
    Additional_Project_count    TEXT,
    Start_Date  TEXT,
    End_Date    TEXT,
    Total_Project_count       TEXT,
    Total_persons  TEXT
    )"""

"""
CREATE TABLE IF NOT EXISTS persons_tb(
    Project_ID  TEXT,
    Initial_count   TEXT,
    Additional_count    TEXT,
    Total_count TEXT,
    persons TEXT,
    is_count_done   TEXT,
        count_Started_Date TEXT,
        count_End_Date TEXT
        )""",

    """
CREATE TRIGGER IF NOT EXISTS after_update_on_projects
    AFTER UPDATE
    ON projects
BEGIN
    UPDATE persons_tb
    SET count_Started_Date = NEW.Start_Date, count_End_Date = NEW.End_Date,
    Initial_count = NEW.Initial_Project_count, Additional_count = NEW.Additional_Project_count, Total_count = NEW.Total_Project_count
    WHERE Project_ID = NEW.Project_ID;
END;
""",

    """
CREATE TRIGGER IF NOT EXISTS after_update_count_pro
    AFTER UPDATE
    ON persons_tb
BEGIN
    UPDATE projects
    SET Total_persons =  Total_persons + NEW.persons
    WHERE Project_ID = NEW.Project_ID and NEW.is_count_done ="count done";
END;
"""

I want to update some columns on two tables with using triggers with after update.
For example:
If I update Start_Date or End_Date or Initial_Project_count on projects(table) that should be updated on persons_tb(table). And if I update persons on persons_tb(table) that should be updated on projects(table). This is working. However, the second trigger(after_update_count_pro) is also running itself automatically if first trigger doing anything. How to avoid this so the triggers will run individually.


texts: ORA-00905: missing keyword when trying to create a synonym

texts:
I have a problem when I try to create a synonym like this:

CREATE SYNONYM B2H.BILLING_USER_ACTIVITY@PACOMNET
 FOR B2H.BILLING_USER_ACTIVITY@PACOMNET.US.ORACLE.COM;

Why does this happen and how can I correct it?


texts:
Would anyone happen to know why Mongo is just using one core instead of distributing traffic on all?

I am using the PyMongo driver and MongoDB is running on a centos server.


texts: Query Quill-Delta JSON array in Postgres

texts:
I need to store text in Quill-Delta format, which is in the form of a JSON array, e.g.

-----------------------------------------------------------------
| id | title | content                                          |
-----------------------------------------------------------------
| 10 | Hello | [ { "insert": "Hello" }, { "insert": "World" } ] |
| 20 | Image | [ { "insert": { "image": "data:image/j..." } } ] |
-----------------------------------------------------------------

Then, I need to query it, e.g. select where text contains "hello world" such as:

if I query content for "hello world" I get row 10;
if I query content for "insert" I get no results;
if I query content form "image" I get no results.

In practice, it should search only the insert key of array objects, where the value is a JSON string (no other types).

Can I achieve this storing data in Postgres? I read about full-text search over JSONB, but I couldn't come up with a working example.


texts: Switching SQL Server service credentials to AD account

texts:
To pursue best practice for SQL Service accounts, I’m working through changing the SQL service account to be AD accounts for our existing SQL servers.  The first one I did was fine because it was a fresh install.  But for existing servers, making the change, I’m not sure about the “rights” required.  The how-to states:
The SQL Server setup program will grant the necessary rights on the machine to that account during installation.
But if I’m not doing a fresh install, I guess I don’t know what the necessary rights are.  I know I will:

Apply Perform Volume Maintenance Tasks Right
Apply Lock pages in memory right
Disable interactive login for the AD service account
Add AD service account to local Administrators group

Is there anything more needed to be done to successfully transfer the SQL Service account from NT SERVICE\MSSQLSERVER?


texts: PostgreSQL: Change default port used by utilites like psql, createdb, etc

texts:
I have 2 instances of PostgreSQL running on some servers. One on the default port (5432) and the other instance on port 5433. Some users (and processes) only need access to the second instance (5433) and I'd like to set it up so that when those users use commands like psql or createdb in their shell it will automatically direct them to the right Postgres instance instead of them having to type -p 5433 along with every command.

I tried to look this up on www.postgresql.org but was unable to find it...probably my fault. Anyone know how?


texts:
I'm having difficulty "optimizing" indexes as a minimally logged operation.  Before the index maintenance is performed, the database recovery model is switched from FULL to BULK LOGGED.  Depending on the fragmentation percent, each index is the recipient of a REBUILD or a REORGANIZE (or no action is taken).  After index maintenance is finished, the recovery model is reverted to FULL.

One database in particular is causing me some pain.  The datafiles are about 64GB (including any free space).  A defrag operation bloated the log file to 38GB, until it filled the logical drive.  Then the level-17 alerts started rolling in.

I tried duplicating this in a test environment.  Numerous attempts were made with different recovery models, index REORG vs REBUILD, different transaction isolation levels, read committed snapshot ON vs OFF, different index fragmentation levels, etc.  Index REBUILDs on DB’s in the FULL recovery model always bloated the t-logs.  No other testing variations did, however.  This was frustrating because I could not duplicate what was happening in production.

What am I missing?  How can I optimize indexes without bloating the log files?

UPDATE 09/23/2015
Not surprisingly, Ola Hallengren's name came up soon after I posted my question.  Although I don't use his scripts, I am somewhat familiar with them.  For those who are interested, there is a wonderful PASS session video--Ola is the presenter.  Near the 48 min mark, an audience member asks a question similar to mine.


texts: update matching column value in anywhere in database sqlserver

texts:
I have assetid column in multiple table in my Database and Asset(main) table

I want to update all occurences of tables in all occurences of inserts in database assetid column where value has 1020 to 1

update AdminInterests set assetid = 1 where 1020

I just want to how to apply this update for entire database all tables with one query


texts: How to create multiple entries on index based on the fields of one row?

texts:
I never found a good way to index multiple fields of one row as entries of an index or simulate this feature on MySQL.

The problem arises when you have fields working as tags or similar concept. Ex.: fields names as tag1, tag2, tag3. To search lines with a specific tag fast requires that you have 3 index and do 3 separate queries on the most basic and obvious way.

Is there any way to index these 3 fields as entries of one index allowing just one search.

ID tag1 tag2 tag3
-- ---- ---- ----
01 abc  xyz  bla
02 foo  bar  ble
03 xyz  bla  bar

index
abc -> 01
bar -> 02 03
bla -> 01 03
ble -> 02
foo -> 02
xyz -> 01 03

Or is there any other way to do this efficiently?


texts:
I am trying to count the number of orders passed by a certain organization over a certain time range. But I found that the below query (time range of 2 days) is much much slower that doing two seperate queries each for one day.

 SELECT COUNT(*)  FROM ORDER_HISTORY
 WHERE organization = 'BA' AND  TIMESTAMP > = TO_DATE('2016-01-05', 'YYYY-MM-DD')
 AND TIMESTAMP<= TO_DATE('2016-01-05', 'YYYY-MM-DD')+2;

I've an index on the column timestamps and another index on column organization

Here is the schema of my table. the column timestamp is of type DATE.

The execution plan of query over 2 days uses the index on organization:

The execution plan of query over 1 day uses the index on timestamp:

To have some stats:

   SELECT COUNT(*)  FROM ORDER_HISTORY
   WHERE ORGANIZATION = 'BA' ;

gives 2359847.

   SELECT COUNT(*)  FROM ORDER_HISTORY
   WHERE TIMESTAMP > = TO_DATE('2016-01-05', 'YYYY-MM-DD')
   AND TIMESTAMP<= TO_DATE('2016-01-05', 'YYYY-MM-DD')+1;

gives 9260. and same query over 2 days gives 16510.

Why could I get the kind of strange behaviour of the oracle DB engine?


texts: Why does an sqlite3 .dump and restore result in a smaller database file size?

texts:
I have a really naive question about database file sizes. I started out with a 1.2M SQLite database, dumped it to a SQL file, and then restored it to a new database. The restored database file was only 50K. Here's what my session looked like:

$ ls -lh
total 1.2M
-rw-r--r-- 1 user group 1.2M Jul  6 18:37 db.sqlite

$ sqlite3 db.sqlite .dump > db.sql

$ ls -lh
total 1.2M
-rw-r--r-- 1 user group  16K Jul  6 18:38 db.sql
-rw-r--r-- 1 user group 1.2M Jul  6 18:37 db.sqlite

$ cat db.sql | sqlite3 db-restored.sqlite

$ ls -lh
total 1.3M
-rw-r--r-- 1 user group  50K Jul  6 18:38 db-restored.sqlite
-rw-r--r-- 1 user group  16K Jul  6 18:38 db.sql
-rw-r--r-- 1 user group 1.2M Jul  6 18:37 db.sqlite

Why is the restored file so much smaller than the original?


texts: Postgres: query on huge (11gb ) index does not return

texts:
Using Postgres 9.6, I have created a table with 435M rows which is 120GB in size and have added an index which is 11GB in size.

I now want to iterate on the distinct values of the index, but the query fails with no error, it just does not complete. I can see no cpu usage or ram being used. Nb the server is on aws rds with 15GB of ram.

How to best troubleshoot this? Trying to iterate through it with LIMIT and OFFSET fails after about 3 cycles. I haven't managed to get a number of the unique values in the index.

I will try reindexing to see if there is indeed anything corrupted, but any suggestions on using a 11GB index would be great.

Editing to add more info:

This is the format of my table

id bigint,
hit1 character varying(50),
hit2 character varying(50),
offset integer,
year character varying(50)

Row estimate is at 431M rows, approx size at 115GB according to postgres.

Index is on hit1 column with a size of 11GB.

I am trying to calculate the counts of hits:

select hit1,hit2, count(distinct id) as total_count_of_ids,
count(case when offset=-1 then 1 else null end) as prev_position,
count(case when offset=0 then 1 else null end) as same_position,
count(case when offset=1 then 1 else null end) as next_position,
min(cast(substring(year,1,4) as int)) as min_year,
max(cast(substring(year,1,4) as int)) as max_year,
array_agg( id) as id_list
from table where hit1=''
group by hit1, hit2;

As the table is big, I have opted to do this one by one entity like so:

select distinct hit1 from table limit 250 offset x
-- use above query to to store results into table.

This function is wrapped in another function which provides x from a loop. The function is run in a dblink context so that the results can be stored and I can audit as I go along.

The cycle needs to run 1,000,000, I am yet to get a number for the count of distinct hit1 values as the query takes too long to come back. The first 750 values (3 iterations) come back on the iteration in under a minute each, but at the next iteration is where it keels over. I will provide log data when this iteration dies.

While the query is running (at the time when the distinct list is calculated) there seems to be minimal cpu usage. There also seems to be some memory being unavailable.

Edit 2 to show results:
In the functions I use dblink for auditing. After the first 3 cycles (0-2) it takes one hour to return results from
    select distinct hit1 from table limit 250 offset 750;
compared to the previous hit1 order this one is no alphabetical and it seems to return or die only 77 results in.


texts: Discover Similar Records based on characteristics on details table (Postgres)

texts:
I think that my need is not so uncommon but I’m not able yet to find the right approach. Need help and suggestions from community.
I’ve 2 table (Master & Details), in the master table I’ve flowers name and other generic informations, in the details table I’ve the characteristics of each flower:
Master Table:
ID,
FLOWER NAME,
SEASON,
LOCATION,
COST
Details Table:
ID (serial),
FLOWER_ID (foreign key master),
CHARACTERISTIC (text),
CHARACT_PREDOMINANCE (numeric)
Usually in the details table I’ve
1, 1(Rose), Petals, 10
2, 1(Rose), Pins, 7
3, 2(Daisy), Petals, 5
4, 2(Daisy), Stem, 8
5, 3(Gerbera),Petals,8
6, 3(Gerbera), Pins, 2
7, 3(Gerbera), Stem, 5
......
I need to find similar flower based on common characteristics, considering as similar those flowers that have more characteristics in common.
Special bonus: I would also specify a Threshold value (CHARACT_PREDOMINANCE)  for filtering only characteristic over a specific value.
Do you have any strategy to suggest trying to use as much as possible only sql?


texts:
We have a requirement to fulfill  - we need an auditing table for all of our entities.
We wonder if creating the following triggers will be a good practice:

CREATE DEFINER=`root`@`localhost` TRIGGER `admin`.`company_AFTER_UPDATE` AFTER UPDATE ON `company` FOR EACH ROW
 BEGIN
  INSERT INTO AuditCompany
   select *, now() from Company where id = NEW.id;
 END

1) Is this a good practice?

2) Is there a better way of doing this?

3) We wanted to avoid the 'insert into XXX values (NEW.id, NEW.col1, NEW.col2) thats why this trigger is doing a subselect on its own - this will remove the need to update the trigger on every new column in the entity. But is it ok?


texts:
This might be a silly question but i really want to know the internals.This is one of small table just to test as below

I have create a table as above and 'name' column is primary key and clustered index so it has sort all words in alphabetical order . Now when i create a non clustered index on column 'number' , the rows in 'name' column not anymore in alphabetical order as below

My question is, the name column should remain the rows in alphabetical order rite since it has clustered index and how come it changed after i add non clustered index on different column? i m sure there might be certain things i missed.Please let me know.Thank you


texts:
I am interested in the totals and the details of every line, in other words, the results of the second query.

Is there an easier way to do a rollup, and just have grandtotals?
(my 'live' query has more group levels that his example...)

CREATE TABLE #TEMP (a int, b int, c int)

INSERT INTO #TEMP VALUES( 1,1,1)
INSERT INTO #TEMP VALUES( 1,1,2)
INSERT INTO #TEMP VALUES( 1,1,3)
INSERT INTO #TEMP VALUES( 1,2,1)
INSERT INTO #TEMP VALUES( 1,2,2)
INSERT INTO #TEMP VALUES( 1,2,3)
INSERT INTO #TEMP VALUES( 1,3,1)
INSERT INTO #TEMP VALUES( 1,3,2)
INSERT INTO #TEMP VALUES( 1,3,3)

SELECT a,b,c, COUNT(*)
FROM #TEMP
GROUP BY ROLLUP(a,b,c)

-- 2nd query
SELECT a,b,c, COUNT(*)
FROM #TEMP
GROUP BY ROLLUP(a,b, c)
HAVING (c is not null and b is not null) or a is null


texts: SQL Job for Monitoring Blockings on SQL Server

texts:
Is it anyway possible to create a SQL Job for monitoring blocking on SQL Instance, if the blocking are still happening after some interval of time?

We are using SCOM Monitoring tool to monitor events from the event viewer and generated alert on the basis of this that. Kindly guide as to what can I do generate an event in case of blocking persists for more than a minute.

I tried enabling the blocking process threshold and created blocking, but I see no event either in event viewer or in SQL Error Logs.
We are using Sql Server 2008 R2 Enterprise.

Thanks in advance.


texts:
I have the following json that I am storing in a jsonb field. For every record representing a product, it contains various categories and in each category a is priority level.

[
      {
        "priority": 0,
        "description": "",
        "categoryName": "Cars",
        "title": ""
      },
      {
        "priority": 1,
        "description": "",
        "categoryName": "Trucks",
        "title": ""
      },
      {
        "priority": 2,
        "description": "",
        "categoryName": "Vans",
        "title": ""
      }
    ]

I would like to order the data by various categories dynamically using the priority field such that, if I am displaying all the products in the category Cars, I will order by the 0 array element, if I am displaying all the products in the category Vans, I order by the 2 array element.

Currently, to order by the first array element I am able to do so:

ORDER BY OBJECT_DATA->'productCategories'->0->>'priority'

However, I would like to order by first selecting the the array value (categoryName) and then using the array value to select its priority.

As an example: order by Trucks->priority (1)

I am using PostgresSQL 9.6.


texts:
I copied a row-based table with about 90 million rows into a column store index Table.

The decrease rate of the table was acceptable. But I want the size of column store index.

The sp_spaceused procedure didn't give any result for the column-based table. How can we get the size of the index?


texts: Ubnutu/MySQL - Error "Unable to locate package innotop"

texts:
I am using Ubuntu 12.04.2 LTS, with MySQL 5.5.29.

Im trying to install innotop for database monitoring, but getting an error "Unable to locate package innotop"

root@server1:~# apt-get install innotop
Reading package lists... Done
Building dependency tree
Reading state information... Done
E: Unable to locate package innotop

I checked the Ubuntu package site at http://packages.ubuntu.com/search?keywords=innotop&searchon=names&suite=raring&section=all
and there is no mention, but I know I used to see it there.

root@server1:~# apt-get update

did not help.

I think I might have once had this problem before, and fixed it by installing some prerequisite, but I cant recall anymore.

I checked Google for the error message, but no mention.


texts: Unable to update table using Oracle linked server : table corrupted?

texts:
I want to synchronize a SQLServer table (version 2008) with data coming from Oracle (11g).
The query is quite simple, uses openquery and looks like:

update t
set field2 = tt.field2
from my_sqlserver_table t
join openquery(XXXXXX, 'select field1, field2
                        from my_oracle_table') tt ON t.field1 = tt.field1

It works well, fast, everybody is happy.
But sometimes (very rare), the query fails with this kind of error:

  OLE DB provider "OraOLEDB.Oracle" for linked server "XXXXXX" returned
  message "ORA-01403: aucune donnée trouvée". Msg 7346, Level 16, State
  2, Line 9 Cannot get the data of the row from the OLE DB provider
  "OraOLEDB.Oracle" for linked server "XXXXXX".

If I make a copy of the table an apply the same update query, it works.

If I make a select instead of an update, it works and retrieves all the rows to be updated.

So that's not an issue with the data, nor with the linked server. It's an issue with the destination table itself (I assume).

Since there is an index on field2, I tried to recreate it. But the error was still there.

And when I recreated the clustered index of the table (on another column, let's call it field0), the error disappeared...

OK, good, but well...

What happened ?
How could we prevent that error to appear again ?


texts:
Recently I faced the problem that my Db became very huge and I cannot allow to myself downtime every time when I want to change the schema.

This caused me to start looking for solution that do schema changing online.

1) pt-online-schema-change. Very attractive project, but honestly I was disappointed. Having downloaded the newest version, I had to change the code in order to get the script working. At the beggining script fails with error messages. All of them I found on the bug report of Percona. After patching script did it's job, hopefully well.

2) oak-online-alter-table. Also very interesting project, facebook mentioned it as good product  on Online Schema Change for MySQL. Tomorrow I gonna try it.

If you are more experienced, please share with us your opinion. What's the best and most reliable product?


texts: AWS DMS Log growth problem - SQL Server as Source

texts:
I have a problem using the AWS Database Migration Service for implementing a transactional replication from SQL Server as a source database engine, a help is highly appreciated.

The 'safeguardPolicy' connection attribute defaults to 'RELY_ON_SQL_SERVER_REPLICATION_AGENT'. The tools will start mimicking a transaction in the database for preventing the log to be reused and to be able to read as much changes from the active log.

But what is the intended behavior of these safeguard transaction? Will those sessions be stopped at some point? What is the mechanism to start / run for some time / stop such a transaction?

The production databases I manage are in Full recovery mode, with Log backups on each half an hour. The log grows to an enormous size due to the inability for a valid truncation procedure to succeed and because of those safeguard transactions initiated by the DMS tool.

The only solution to a full transaction log due to LOG_SCAN caused by such behavior of DMS for now is to stop the DMS tasks and run a manual truncation of the log, to release space not used. But it is not a solution at all if we need to stop the replication each time such a problem occurs, knowing that it will occur often.

Please share some internals about the tool if possible.

Thanks


texts: Is there any time where we should avoid cascading delete?

texts:
In SQL Server 2008, there is a Primary table which is linked to three other child tables by 1 to many relationship. So, we are thinking of using Cascading delete in the primary table, so that all the records on the child table will be removed when record from primary table is deleted.

So, is cascading delete a correct choice here?
When cascading detele should not be used?


texts: MySQL client believes they're in a transaction, gets KILLed, wreaks havoc

texts:
Open up two mysql command-line clients and connect to your database. In client #1, enter

START TRANSACTION;

In client #2, use SHOW PROCESSLIST, then

KILL [n];

where n is the id for client #1's connection. Bam—transaction rolled back. But client #1 doesn't know that. Then from client #1, send some command—say,

UPDATE clients SET important_field = NULL;

You'll get the response:

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...

Query OK, 10000 rows affected (0.05 sec)
Rows matched: 10000  Changed: 10000  Warnings: 0

Oops, you say, better hit ROLLBACK! Then you realize, to your horror, that you're not in a transaction anymore.

Here's my question: In case I want to KILL a connection at some point, is there any way I can ensure that this "now you're in a transaction, now you're not" scenario doesn't happen, short of setting autocommit to 0 at the system level?

Note that the above is tested under mysql 5.1—if later versions provide a fix, I'd love to hear about it. I'd also love to see tests done with JDBC, ADO.NET, etc. to see whether they're susceptible to this same issue.

(On a meta note, this question arose from two questions over at Server Fault. I'm really hoping that the DBA community will prove more helpful...)

Update: See my answers below. This issue appears to be unique to the mysql command-line utility, with its bizarre auto-reconnect "feature." Most likely, any tool or library not built on top of the mysql utility will not exhibit this behavior. However, you may want to test whatever you're using to be sure, or take drachenstern's suggestion and wrap your transactions in stored procedures.


texts: How to use sequential UUID primary key for range query by date

texts:
I'm using a sequential timebased GUID as my primary key in MySQL 5.6 in a transaction table with over 1 billion rows. The application is written in JAVA and GUID is generated as described here: http://wiki.fasterxml.com/JugHome

The question that I have is whether or not it is possible to range query the data by date without additional indexes? I've seen this question and the answer which states that this is somehow possible: Sequential GUID or bigint for 'huge' database table PK

Existing implementation is using Type 1 UUID, as described here. Then I came across this article, which describes efficient way to save the UUID, by re-organizing its contents.

The question that I have is whether searching by such UUID for a date range (and how it can be done) will be efficient, in other words, is it worth to save storage space by not adding index on the Date column?


texts: MySQL slow_log table: querying takes forever

texts:
I'm running a query on the slow_log:

SELECT start_time FROM mysql.slow_log WHERE start_time < TIMESTAMP '2016-01-31 00:00:00'

Without a WHERE clause (using a LIMIT), the selects are fast.

However the LIMIT gives me the oldest logs, ORDER BY has the same problem for me.

Is this a MySQL thing, or does it have to do with the fact I'm running a Google Cloud SQL database?


texts: Redis docker container not coming up with custom config file

texts:
I'm using the below command to run the Redis docker container

docker run -tid -v /data1/REDIS_DOCKER_IMAGE/6379/redis.conf:/usr/local/etc/redis/6379/redis.conf
-p 6379:6379 --name node_6379  redis:5.0.8 redis-server /usr/local/etc/redis/redis.conf

After I run this command, I check "docker ps" but it comes up empty. There are no logs shown by docker logs so I don't know what's going wrong.


texts: How can one make a partial index to optimize a query for a single value?

texts:
Let's say I have table foos with column thing_id.
What's the smallest index I could make for these queries?
select count(*) from foos where thing_id is null;
select id from foos where thing_id is null;

Conceptually, we just need the index to keep track of a list of primary keys which match the criteria. Is it possible/useful to create a partial index on... no columns?
I tried this and postgres considered it a syntax error:
create index on foos where thing_id is null;

This did work
create index on foos (thing_id) where thing_id is null;

But will this result in unnecessarily writing the value of thing_id (always NULL) for each row?


texts:
Running SELECT * FROM dbName.dbo.sysusers ORDER BY name; reveals users that start with \, and this causes errors when try to add the same user without the slash.

Can't run EXEC sp_dropuser or DROP USER. This throws an error telling that user does not exist or you do not have permission.

Any ideas on how to remove the user? I believe that the \user is orphaned from a database migration from SQL Server 2000 or 2005. The current database is running on SQL Server 2016. I've tried using the GUI and T-SQL.

I've tried EXEC sp_dropuser and DROP USER using [\user], '\user',\user, QUOTENAME('\user').

I get either

  Incorrect syntax

or

  Cannot drop the user '\user', because it does not exist or you do not have permission.

errors.

Here is the row from the sysusers table.

uid              23
status           0
name             \User
sid              0x010500000000000512000000FD77B156780C942546170A3273170200
roles            NULL
createdate       2012-08-17 10:01:05.600
updatedate       2012-08-17 10:01:05.600
altuid           NULL
password         NULL
gid              0
environ          NULL
hasdbaccess      0
islogin          1
isntname         0
isntgroup        0
isntuser         0
issqluser        0
isaliased        0
issqlrole        0
isapprole        0


texts: How to change default database to all logins?

texts:
I'm bit new to SQL Server and I'm trying to script out Microsoft SQL Server (2005-2014) post installation activities and I got bit stuck on altering logins. Corporate policy states that none login should have master database as default, thus my plan is to identify all logins that has master as default and change master to tempdb.

Single login can be altered using such query:

ALTER LOGIN [sa] WITH DEFAULT_DATABASE = tempdb

Trying to achieve this on all logins (without knowing their amount nor names), I know I was naive, when hoping something like this would work (but had to try):

UPDATE master..syslogins
SET dbname = 'tempdb'
WHERE dbname = 'master'

Obviously output was an error (logically):

Ad hoc updates to system catalogs are not allowed.

SQL Servers are in MIX authentication mode, so pretty much anything can be login - SQL login, Windows local or domain user or group (not sure if this can make any difference for solution). Solution should be pretty much version independent, but let's say starting SQL Server 2005 forward (...yes, still have some ancient version around...)

Can anyone please help me to find proper query to achieve this?


texts: Football Matches - build temp standings table for each Gameweek

texts:
I have the following data in a sql database and i need that for each game in the table to add new columns with the Rank in Table, Number of Points and Goals Scored at home, for each Gameweek (the trickiest part).
Basically what i need is that after each gameweek (order ascending) to create a standings table and then bring Kpis from the standings table to each team for each match in the gamesweek.

The goal of all this is to build a Data Model with KPIs on how the team performed in the standings before each game and try to make some predictions afterwards  ps: if anyone interested, after the model will be ready please get in touch and will gladly share

Here is the table

Div DataMeciului HomeTeam       AwayTeam    FTHG    FTAG    Gameweek
E0  2018-08-10  Man United      Leicester     2      1         1
E0  2018-08-11  Bournemouth     Cardiff       2      0         1
E0  2018-08-11  Fulham          Crystal Palace0      2         1
E0  2018-08-11  Huddersfield    Chelsea       0      3         1
E0  2018-08-11  Newcastle       Tottenham     1      2         1
E0  2018-08-11  Watford         Brighton.     2      0         1
E0  2018-08-11  Wolves          Everton       2      2         1
E0  2018-08-12  Arsenal         Man City      0      2         1
E0  2018-08-12  Liverpool       West Ham      4      0         1
E0  2018-08-12  Southampton     Burnley       0      0         1
E0  2018-08-18  Cardiff         Newcastle     0      0         2
E0  2018-08-18  Chelsea         Arsenal       3      2         2
E0  2018-08-18  Everton         Southampton   2      1         2
E0  2018-08-18  Leicester       Wolves        2      0         2
E0  2018-08-18  Tottenham       Fulham        3      1         2
E0  2018-08-18  West Ham        Bournemouth   1      2         2
E0  2018-08-19  Brighton        Man United    3      2         2
E0  2018-08-19  Burnley         Watford       1      3         2
E0  2018-08-19  Man City        Huddersfield  6      1         2
E0  2018-08-20  Crystal Palace  Liverpool     0      2         2
E0  2018-08-25  Arsenal         West Ham      3      1         3
E0  2018-08-25  Bournemouth     Everton       2      2         3
E0  2018-08-25  Huddersfield    Cardiff       0      0         3
E0  2018-08-25  Liverpool       Brighton      1      0         3
E0  2018-08-25  Southampton     Leicester     1      2         3
E0  2018-08-25  Wolves          Man City      1      1         3
E0  2018-08-26  Fulham          Burnley       4      2         3
E0  2018-08-26  Newcastle       Chelsea       1      2         3
E0  2018-08-26  Watford       Crystal Palace  2      1         3
E0  2018-08-27  Man United      Tottenham     0      3         3
E0  2018-09-01  Brighton        Fulham        2      2         4
E0  2018-09-01  Chelsea         Bournemouth   2      0         4
E0  2018-09-01  Crystal Palace  Southampton   0      2         4
E0  2018-09-01  Everton         Huddersfield  1      1         4
E0  2018-09-01  Leicester       Liverpool     1      2         4
E0  2018-09-01  Man City        Newcastle     2      1         4
E0  2018-09-01  West Ham        Wolves        0      1         4
E0  2018-09-02  Burnley         Man United    0      2         4
E0  2018-09-02  Cardiff         Arsenal       2      3         4
E0  2018-09-02  Watford         Tottenham     2      1         4

Desired outcome

HTPts HTPos HomeTeam   AwayTeam   ATPos   ATPts
  3     1   Man United Leicester    15      0
  3     3   Bournemouth Cardiff     16      0
  0     13  Fulham     Crystal Palace 6     3

Sorting the temptables to define position should be done one order by PTS, SUM(FTHG - FTAG)


texts: SSMS 2017: Open SQL Files in existing SSMS app with raised admin permissions?

texts:
I realise this is an old chestnut, but does anyone have a definitive solution?

When SSMS (2017) is 'run as administrator', double clicking a SQL file in explorer will open another new instance of SSMS (and not open the file). This is not good. Double clicking a SQL file should open it in the existing open SSMS app.

Is there a solution to this?

What reasons are there to 'run as admin' by default or can this be turned off without impacting functionality?

Many thanks,

James


texts: Oracle - Delete dmp files from ASM diskgroup

texts:
I want to delete my export files daily from ASM diskgroup with a crontab job. I want to prepare a script for it.
ASMCMD> ls

exp1.dmp

exp2.dmp

exp3.dmp

exp4.dmp

ASMCMD> pwd

+DATA/EXP

How can I prepare this script? I prepared a template for it, but I couldn't script it.
set_gridenv
asmcmd
cd +DATA/EXP
rm -rf exp*.dmp
exit
Best regards,


texts: Error while importing sql - Specified key was too long; max key length is 1000 bytes

texts:
I am trying to import an sql file which shows me the following error:

Error
SQL query:

-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE IF NOT EXISTS  `users` (

 `uid` INT( 11 ) NOT NULL AUTO_INCREMENT ,
 `uemail` VARCHAR( 255 ) NOT NULL ,
 `upassword` VARCHAR( 255 ) NOT NULL ,
 `uname` VARCHAR( 255 ) NOT NULL ,
 `uemailh` VARCHAR( 255 ) NOT NULL ,
 `uclass` TINYTEXT NOT NULL ,
 `regno` VARCHAR( 8 ) NOT NULL ,
 `absencecount` VARCHAR( 255 ) NOT NULL DEFAULT  'a:10:{i:0;i:0;i:1;i:0;i:2;i:0;i:3;i:0;i:4;i:0;i:5;i:0;i:6;i:0;i:7;i:0;i:8;i:0;i:9;i:0;}',
 `internalmarks` VARCHAR( 500 ) NOT NULL DEFAULT  's:73:"["0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0"]";',
PRIMARY KEY (  `uid` ) ,
UNIQUE KEY  `uemail` (  `uemail` ,  `uname` ) ,
KEY  `regno` (  `regno` )
) ENGINE = INNODB DEFAULT CHARSET = utf8 AUTO_INCREMENT =2;

MySQL said: Documentation

#1071 - Specified key was too long; max key length is 1000 bytes

I've tried to create a new  database in my local machine everything works fine. But, while importing it in the remote server it shows the above error. Any Idea?


texts: CDC Capture Instance or State to ensure referential integrity

texts:
I'm implementing an SSIS/CDC based data warehouse solution and have a question relating to referential integrity on the destination database.

As an example, I have INVENTORY_TRANSACTION in one table and PART in another table each with their own SSIS package for incremental loads.  I don't want to have a situation where I have INVENTORY_TRANSACTIONS in the data warehouse that don't have an associated parent PART record.

I've read quite a bit on the subject but I'm still unclear.

Should I be using the same capture_instance on the tables that have related data so they all target the same LSN chain?  Should I be using the same State name?  Or, am I missing something entirely?


texts: XAMPP MySQL troubleshooting with MySQL Server

texts:
MySQL in XAMPP isn't starting after I installed MySQL Server.
MySQL Server is using port 3306, so I've changed in xampp\mysql\bin\my.ini:

[client]
port            = 3360
socket          = "C:/xampp/mysql/mysql.sock"

[mysqld]
port= 3360

And in xampp\php\php.ini:

mysqli.default_port=3360
mysql.default_port=3360

Also in XAMPP Control Panel -> Config -> Service and Port Settings -> MySQL changed Main Port to 3360.

After all these changes it still isn't working.

Nestat is showing 3306 is used by mysqld.exe, so I think XAMPP MySQL should work correctly on 3360.

Also I've tried to switch off MySQL server, didn't help to.

Error:

Error: MySQL shutdown unexpectedly

Logs:

2017-11-24 17:08:48 2498 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2017-11-24 17:08:48 9368 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2017-11-24 17:08:48 9368 [Note] InnoDB: The InnoDB memory heap is disabled
2017-11-24 17:08:48 9368 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2017-11-24 17:08:48 9368 [Note] InnoDB: Memory barrier is not used
2017-11-24 17:08:48 9368 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-11-24 17:08:48 9368 [Note] InnoDB: Using generic crc32 instructions
2017-11-24 17:08:48 9368 [Note] InnoDB: Initializing buffer pool, size = 16.0M
2017-11-24 17:08:48 9368 [Note] InnoDB: Completed initialization of buffer pool
2017-11-24 17:08:48 9368 [Note] InnoDB: Restoring page 0 of tablespace 0
2017-11-24 17:08:48 9368 [Warning] InnoDB: Doublewrite does not have page_no=0 of space: 0
2017-11-24 17:08:48 9368 [ERROR] InnoDB: space header page consists of zero bytes in data file C:\xampp\mysql\data\ibdata1
2017-11-24 17:08:48 9368 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
2017-11-24 17:08:48 9368 [ERROR] Plugin 'InnoDB' init function returned error.
2017-11-24 17:08:48 9368 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-11-24 17:08:48 9368 [Note] Plugin 'FEEDBACK' is disabled.
2017-11-24 17:08:48 9368 [ERROR] Unknown/unsupported storage engine: InnoDB
2017-11-24 17:08:48 9368 [ERROR] Aborting

Someone, please help.


texts: MySQL Database import into 5.7 hangs with CLI or with Workbench 6.3 CE

texts: I have a SQL dump file that I exported using Workbench from a server that is being decommissioned. The host and target OS is Microsoft Windows Server 2008 R2 (therefore is x64 natively). There seems to be no way in hell to import this SQL dump file as it will hang no matter what. File size is less than 500Mb. I did look at the database schema itself and tables seem to have overwritten the initially created DB with the same name, however the import will not do anything but hang. I'm not a DBA, just a network/sysadmin so I am most frustrated with getting this thing to import. Any suggestions anyone?


texts: how to take output from second table by joining in SQL

texts:
table 1                              table 2

id  Name  value                    id  status
1  a      1000                     1   open
2  b      2000                     2   closed
3  c      3000                     3   closed
4  d      4000                     4   open
5  e      5000                     5   open

I want the output as the names which are closed. What will be the possible solutions?


texts: Latest version to support MySQL 5.1.69 ib* files

texts:
Would like to recover InnoDB data from a crashed server. The last backup indicated it was running MySQL server 5.1.69. When doing a recovery, the log files show that the current MySQL won't support ib* files from this far back.
I tried installing 5.1.69 from archives and compiling from sources, but no-go, rabbit hole of problems.
Does anyone know the latest version of MySQL to support 5.1.69 ib* files? Will try that as a last resort. If it doesn't work, will go with the latest backup and some auxiliary data to try to reconstruct as much as possible.


texts: Problem Configuring Log Shipping for DR Server

texts:
Problem Statement - I want to configure DR site in SQL Server 2012 with the existing backups.

The backup scenario is as below.
FULL – on every Sunday,
DIFF – daily except Sunday,
LOG – every 15 minutes

Now, I want DR site for my production for which I am interested in using Log Shipping. But, the problem in Log Shipping is that it will initially restore full backup then regularly ships the transaction logs to the secondary server.

But, as in my present scenario if I use this then after every FULL or DIFF backup, my Log Shipping will break as the LSN will get change with respect to my current backup at secondary server.

So, is there any way to configure Log Shipping with using existing backups or do any one have any custom procs or scripts which will help us out?


texts:
I am relatively knew to MSSQL so please bare with me.
My infrastructure is currently in AWS.
I have 3 servers running MSSQL. I have a primary replica and two secondary replicas. All three servers are in 1 AG group and there is 1 database being replicated.
Server 1 is in AZ A (Subnet A)
Server 2 is in AZ A (Subnet A)
Server 3 is in AZ B (Subnet B)
All are set for automatic sync failover. I am working on adding a listen for these 3 replicas.
As far as I can tell you can only add a static listener with the two different subnets.
So lets say I add an IP for Server 1 and Server 3. It wont let me add it for Server 2 because its 1 per subnet.
How would this work. Am i missing something here? Am I doing something wrong?


texts: Why is this MySQL proc using cursors failing to retrieve results?

texts:
I am importing data from a third-party vendor. I have already imported their tables into my existing database. Now, I just need to iterate through each of their person records, munge it a little bit, and insert it into a few tables on my end. My cursor seems to only be iterating through once, though - here is the code.

DELIMITER @@

DROP PROCEDURE IF EXISTS import_members@@
CREATE PROCEDURE import_members ()
BEGIN
    -- Declare loop constructs --
    DECLARE done INT DEFAULT FALSE;

    -- Declare Person variables --
    DECLARE person_id INT;
    DECLARE era_username VARCHAR(100);
    DECLARE last_name VARCHAR(50);
    DECLARE first_name VARCHAR(50);
    DECLARE email VARCHAR(100);
    DECLARE email_primary VARCHAR(50);
    DECLARE degree_id_1 INT;
    DECLARE degree_id_2 INT;
    DECLARE member_status INT;

    DECLARE user_id INT;
    DECLARE user_email VARCHAR(100);

    -- Declare Cursor --
    DECLARE member_cursor CURSOR FOR
        SELECT person_id, era_username, last_name, first_name, TRIM(email), TRIM(email_primary), degree_id_1, degree_id_2, member_status
        FROM z_data_person
        WHERE member_status IS NOT NULL;

    -- Declare Continue Handler --
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN member_cursor;

    read_loop: LOOP

        -- Fetch data from cursor --
        FETCH member_cursor
        INTO person_id, era_username, last_name, first_name, email, email_primary, degree_id_1, degree_id_2, member_status;

        -- Exit loop if finished --
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Create User --
        SET user_id = SELECT (MAX(uid) + 1) FROM users;
        IF email_primary IS NOT NULL AND email_primary NOT LIKE '%null%' THEN
            SET user_email = email_primary;
        ELSE
            SET user_email = email;
        END IF;

        INSERT INTO `users` (`uid`, `name`, `pass`, `mail`, `created`)
        VALUES (user_id, era_username, SHA1(RAND()), user_email, UNIX_TIMESTAMP());

        -- Create Member --
        INSERT INTO `members` (`uid`, `first_name`, `last_name`, `phone`, `member_category_id`, `subscription_weekly_email`, `subscription_monthly_newsletter`)
        VALUES (user_id, first_name, last_name, phone_num, member_status, weekly_com, monthly_com);

        -- Add Degrees --
        IF degree_id_1 IS NOT NULL THEN
            INSERT INTO member_degrees_held
            VALUES (user_id, degree_id_1);
        END IF;

        IF degree_id_2 IS NOT NULL THEN
            INSERT INTO member_degrees_held
            VALUES (user_id, degree_id_2);
        END IF;

        -- Add Areas of Expertise --
        INSERT INTO `member_expertises_held` (`uid`, `specialty_id`)
        SELECT (SELECT MAX(uid) FROM users), z.specialty_id
        FROM z_map_person_specialty AS z
        WHERE z.person_id = person_id;

    END LOOP read_loop;

    CLOSE member_cursor;

END; @@

DELIMITER ;
CALL import_members();

It's as if the SELECT statement used to populate the cursor is returning no results. I've tested that independently, and it DOES return results correctly. I've tried putting a SELECT person_id directly below the first FETCH, and the returns NULL. So...although this query should return results, they're not making it into the cursor's result set. Am I missing something?


texts:
We have a MySQL 5.1 instance running on Windows Server 2008 R2 x64 (Server A) replicated to another MySQL 5.1 instance on Windows Server 2003 R2 (Server B).

We have a new MySQL 5.5 instance on another Windows Server 2008 R2 x64 machine (Server C).

I want to replace Server A with Server C.

My plan is to set up Server C as another slave to Server A by 'cloning' Server B like this:

http://www.redips.net/mysql/add-new-slave/.

After that I plan on doing this:

http://blesseddlo.wordpress.com/2011/06/03/convert-mysql-slave-to-master/.

After that I want to change Server B's master to Server C.

Is this a viable approach? Are there caveats? May there be any compatibility issues when setting a MySQL 5.5 instance as slave to a MySQL 5.1 instance?

I can't just stop everything and use mysqldump since there can be no downtime and we have ~25 GB of data that must be transferred over a Gigabit LAN.


texts: Can't connect to a MySQL from the command line (works from MySQL Workbench)

texts: I am connected through a local socket/pipe and the username password is root/roots. I can login and view the 4 schemas I have created. However, when I use the mysql command at the command line and login to localhost then the only schemas visible are test and information_schema.  What am I doing wrong and how to I login correctly?


texts: How do I stop refresh conflicts between Proactive Caching of facts and timed refresh of Dimensions in a SQL Server Analysis Service 2005 OLAP cube?

texts:
I have a SSAS 2005 OLAP cube which we've very nearly made into a ROLAP cube using a mixture of proactive caching for the fact partitions and timed refreshes for the dimension data.

There are two fact partitions - one containing yesterdays snapshot and one containing intra-day changes to that snapshot.  That's how we're able to achieve near real-time cube updates within SSAS (which Microsoft has stated is not suitable for real-time).

So, proactive caching updates the fact data (with sub-second timing) but is not set to update the dimension data.  About 10% of the dimensions receive updates intraday so we are continually refreshing them using a SQL Server process that runs every 2 minutes.

We are seeing cube corruption, periodically, requiring a full-reprocess of the cubes (taking several hours in some cases).  We believe this corruption is caused when proactive caching happens to update a fact while a dimension update is happening (perhaps when a new fact, requiring a dimension value that doesn't exist yet, is being added).  We don't know for sure because SQL Server and SSAS 2005 provide little no useful trace information for these exceptions.

Our short-term solution has been to turn off the dimension updates entirely.  Proactive caching still updates the facts and keeps our near real-time updates flowing until a new fact requiring a new dimension value comes in.  At that point, the cubes stop updating and we manually determine the faulty dimension and manually re-process it.

So, what we are looking for is a way to avoid having to manually update the dimensions (which causes an outage to users).


texts: SQL Server Transactional Replication between SQL server 2019 Standard and SQL server 2016 Web Edition

texts:
We have SQL Server 2016 Web Edition on production currently. Since this edition supports replication as Subscriber only, I set up a new server with SQL Server 2019 Standard edition. I want to configure SQL Server 2019 as Publisher and SQL Server 2016 as subscriber. To initialize data for Publisher database on SQL server 2019, I created a backup on SQL Server 2016 and restored it on SQL Server 2019. Since our database is very large, I tried to initialize replication from a backup. So I did reverse backup-restore again by creating a backup of Publisher on SQL Server 2019 and restoring it on Subscriber on SQL Server 2016. But this did not work because [SQL Server 2019 backups cannot be restored by any earlier version of SQL Server] (https://docs.microsoft.com/en-us/sql/relational-databases/databases/copy-databases-with-backup-and-restore?view=sql-server-ver15). Could you please tell me what is the best method for initializing the replication in this case?
Thank you very much for reading my question!


texts:
Due to a mistake i deleted the log folder for mysql. Is there a way to generate the folder and the error logs?
I'm using MariaDB 10.3 on Debian 9 Stretch


texts:
I have this tSQL code which works OK:

SELECT
c.logguid,
a.b.value('./PropertyValue', 'varchar(max)') asd
  FROM [dnn].[dbo].[EventLog2] c

cross apply sss.nodes('/LogProperties/LogProperty[PropertyName=sql:variable("@x") and PropertyValue=sql:variable("@y")]') as a(b)

However, what I want to be able to do is pass in a dynamic list of many pairs of values which are OR'd between each one, i.e.

SELECT
c.logguid,
a.b.value('./PropertyValue', 'varchar(max)') asd
  FROM [dnn].[dbo].[EventLog2] c

cross apply sss.nodes(
'/LogProperties/LogProperty[PropertyName=sql:variable("@x") and PropertyValue=sql:variable("@y")
or
PropertyName=sql:variable("@a") and PropertyValue=sql:variable("@b")
]'
) as a(b)

Is there a way of doing this?


texts: Fetch data if exist in distinct of another column

texts:
I am trying to generate the query to fetch the data on the basis of company which have tax paid record in each year of :

Select *
From (
  Select
       CompanyName
     , TaxPaidOn
     , DATEPART(Year, TaxPaidOn) as 'TaxPaidYear'
     , OtherInfo
  From
  TestData
) As BaseTable
Where CompanyName IN (Distinct TaxPaidYear)

I know the above query is wrong. Will you please rectify it?

Sample data are as below:

Script :

CREATE TABLE TestData(
    CompanyName nvarchar(50) NOT NULL,
    TaxPaidOn date NOT NULL,
    OtherInfo nvarchar(max) NULL
)
GO
INSERT TestData (CompanyName, TaxPaidon, OtherInfo)
VALUES
 ('ABC', '2009-01-01', 'Information1')
,('ABD', '2009-01-01', 'Information2')
,('ABE', '2009-01-01', 'Information3')
,('ABF', '2009-01-01', 'Information4')
,('ABC', '2010-01-01', 'Information5')
,('ABE', '2010-01-01', 'Information6')
,('ABF', '2010-01-01', 'Information7')
,('ABC', '2011-01-01', 'Information8')
,('ABD', '2011-01-01', 'Information9')
,('ABE', '2011-01-01', 'Information10')

TestData Table :

Company Name    TaxPaidOn    OtherInformation
==============================================
ABC             2009-01-01   Information1
ABD             2009-01-01   Information2
ABE             2009-01-01   Information3
ABF             2009-01-01   Information4
ABC             2010-01-01   Information5
ABE             2010-01-01   Information6
ABF             2010-01-01   Information7
ABC             2011-01-01   Information8
ABD             2011-01-01   Information9
ABE             2011-01-01   Information10

Expected Result :

Company Name    TaxPaidOn    OtherInformation
==============================================
ABC             2009-01-01   Information1
ABE             2009-01-01   Information3
ABC             2010-01-01   Information5
ABE             2010-01-01   Information6
ABC             2011-01-01   Information8
ABE             2011-01-01   Information10


texts: how to optimize group by query in mysql 5.7

texts:
I am using MySQL 5.7 and I have a sql query like this:
select sum(case when `status` = 1 then 1 else 0 end) as online_count,
sum(case when robot_flag = 1 and `status` =1 then 1 else 0 end) as robot_online_count,
room_play_id ,
room_id,
app_id
from r_room_seat
where app_id = 4
group by app_id,room_id,room_play_id

the r_room_seat has 40775 rows, and app_id = 4 have 1780 rows. one app have n room, one room have n room_play, one room_play have n seat, now the sql execuate takes 200ms +, I have tried to create index like this:
create index seat_idx on r_room_seat(app_id,room_id,room_play_id);

still not change, any good ideas?


texts: Howto (async?) preview intermediate resultset

texts:
Is it possible to retrieve parts of the resultset from SQL Server queries in advance?
For instance if I run a query that will return 20,000 rows, is it possible to throw the results in packages of 100 to the requesting instance (.net code for instance) and allow using them instead of waiting for all 20,000 rows to be read and returned?

A similar behaviour I have observed using SSMS: The resultset is filled ongoing even before the query is completed. Don't know if this is the same, but I am pretty sure if I run an SqlClient ExecuteNonQuery command, I would have to wait for all data being returned before I can fill e.g. a datagridview.

I do not know the ideal wording for this scenario, maybe this is the reason I can't find similar questions.

I have read about OPTION FAST that seems to have no effect. Does this really address the question?


texts: Database returned to CIRCULAR mode but logs are still being archived to old location

texts:
my DB2 database (v 9.7) is in Circular mode.

Current Logging Type:Circular

(USEREXIT) = OFF
(LOGRETAIN) = OFF
(LOGARCHMETH1) = OFF
(LOGARCHMETH2) = OFF
(LOGPRIMARY) = 10
(LOGSECOND) = 10
Path to log files: /home/ctginst1/ctginst1/NODE0000/SQL00001/SQLOGDIR/

I had old archive location /opt/IBM/archive
and now my hard disk space is full 100% because I have over 500 logs in that location which I cannot find that is defined somewhere.

What is going on?Why archive logs keep coming to old archive log location?

This is really big problem for us I will be thankful for any help


texts: How is it possible that this "sub-calculation" of the "main calculation" actually gives a HIGHER value?

texts:
I do this to calculate the average food expenses per month:
    SELECT avg(amount)
    FROM
    (
        SELECT date_trunc('month', "timestamp"), SUM("amount") AS amount
        FROM bookkeping WHERE category = 'Food'
        AND "timestamp" >= now() - INTERVAL '6 months'
        GROUP BY date_trunc('month', "timestamp")
    ) a

Then I do this, to see how much of that is from food orders online:
    SELECT avg(amount)
    FROM
    (
        SELECT date_trunc('month', "timestamp"), SUM("amount") AS amount
        FROM bookkeping WHERE category = 'Food'
        AND "is online" = true
        AND "timestamp" >= now() - INTERVAL '6 months'
        GROUP BY date_trunc('month', "timestamp")
    ) a

The second query shockingly gives a HIGHER value than the first, even though it's obviously a sub-post of the first.
How can this be possible? How can something which introduces more ANDs still give a higher value? I've stared at this code and tried numerous things; it's not a hallucination. I must be missing something fundamental. The queries cannot be calculating what I think they do.
I've relied on the first query (and many similar to it) for a long time for my entire economy, so it really would be bad if this turns out to be fundamentally wrong...
(The reason I don't just do SELECT avg(amount) directly is that then this becomes the average cost per purchase instead of the average cost per month.)

In the visual table in my GUI, I have it so that it shows "Food: blablabla/month", then underneath a list of each "type" of food, so each of those "sub-groups" cannot have a number higher than the "parent".
I guess my brain fundamentally cannot understand how an average (or any number) can be higher for a sub-group of a bigger set which yields a smaller number.


texts: Does loading data to temp table improve performance?

texts:
When I write stored procedure I use views that have calculation inside them and so if I have to reuse the view multiple times in my procedure, I load it to a temp table and then use the temp table throughout the procedure to avoid the same calculation begin done everytime the view is accessed.

But does this thing applies for straight table's data too?

So if I have to access same table multiple time in the stored procedure is it a good idea to load the data in temp table and access it rather than the original table?

Does it improve performance in any way? Since the data is then local to the procedure?

I am not sure because for procedure table data is also local in that sense.

Can any please help?


texts: SQL - Convert text to a list of numeric values

texts:
Looking for SQL code to transform the text string '12,13,14' to this list of numbers

+----+
| 12 |
+----+
| 13 |
+----+
| 14 |
+----+

Example:

table0.params (varchar) field contains this text: 12,13,14
table1 has column ID (numeric).

I would like to form a query along the lines of:

SELECT * FROM table1 WHERE ID in table0.params

I am using Microsoft SQL Server Standard Edition (64-bit) version 10.50.1600.1


texts: SQL Server 2008r2 won't start after windows updates, error log & wmi provider errors

texts:
I have a SQL Server 2008 R2 instance on a Windows 2008 R2 box, and windows has not been updated for almost a year (I know), last night windows patches were applied (around a 100 that were pending), and this morning SQL Server will not start.

The error in event viewer is:

  initerrlog: Could not open error log file 'D:\Program Files\Microsoft
  SQL Server\MSSQL10_50.SQL1\MSSQL\Log\ERRORLOG'. Operating system error
  = 5(failed to retrieve text for this error. Reason: 15105).

As per many links (e.g.SQLServerCentral), I have found this does appear to be some kind of permissions issues. The service account SQL Server is configured to use is a network service account, I have checked this in AD and appears to be OK and the password is correct (nothing has changed since yesterday in that regard, when it was working) I have also tried:

Made the service account local admin on the box (temporarily)
Ensure that the service account was also added to the SQL Server groups on the server
Given explicit permission to the service account to the error log folder (full control); then to the whole program files folder (full control)

And I still get the same error.

Next, I tried to change the service account used (to either a network admin - (I know, just to test) or local service account) in Configuration Manager, when applying these changes I get a WMI Provider error:

  WMI Provider Error [call to WMI Provider error code: 0x800742a2]

I am a bit confused by this and haven't seen the like before.

Does anyone have any ideas about what is going on, or anything else to try that I've missed?

Edit 1 :

To add, I've checked the first hits when searching for the WMI error MS Support, SQLServerCentral, but they still seem to be suggesting permissions problems. I've double checked all the above relating to permissions issues and can't see how that is the problem (The service account is now local admin (!), in the SQL Server groups on the box, and has explicit full control to all program files). I am wondering whether this WMI error is a red herring, or totally separate problem.

Edit 2 :

It seems there was something up with the ERRORLOG file that was preventing startup, not a permissions issue - not sure what, but perhaps a corruption or something of the file itself, as deleting it (well, copying it elsewhere) seems to have forced SQL Server to generate a new one and it started up OK. I'm not sure what is wrong with the errorlog file (I will see if I can open it and investigate), and I don't know what the WMI Provider error was, now that the service is online I can't fully test it now, I will try again later in some planned downtime to see if the WMI Provider comes up (and update this as appropriate/add as answer).

Edit 3 (final update):

Seems all of the symptoms were misleading and the usual cause (permissions) to this kind of problem wasn't the cause in this case, it was a problem with the ERRORLOG file itself, and removing it (to be recreated) solved the issue and services came up. I've added an answer describing this. However, this seems to be the only case I can find of these symptoms having this cause, and many cases where it is permissions, which I've included links to above, the answer in this question goes into good detail on. Therefore, if you have this problem in the future, I suggest follow those steps (checking permissions issue first), before checking out the ERRORLOG file itself.


texts: What is the best way to determine, without an Oracle support contract, the release date of an Oracle Database 12c patchset?

texts:
This is regarding a set of databases that I'm working with (I'm not a DBA, I just verify various settings and check for patching). The DBA is unable to provide the information I need.

What I run is SELECT patch_id, version, action, status, timestamp, description from dba_registry_sqlpatch;

What I need is a way to know when the patch identified by this query was released by Oracle. I have an ordinary Oracle SSO account, but no paid support within my account. The DBA doesn't either, as patches are provided to him and his counterparts at other locations after testing elsewhere.

Or if I'm running down the wrong path here altogether, my end goal is to be able to prove that the database has been patched to either the most current available, or the one released prior to that one.


texts: How to emulate performant date dimension for SSAS model in tabular mode / direct query

texts:
I have an indexed datetime column - lets call it "CreatedDateUTC" in table "Entity" in MSSQL db. Column contains UTC datetime. User connecting to db has known timezone offset. I have another table - DateDim with primary key column Date. This is to support date dimension for SSAS/Tabular model/Direct query scenario.

So on model level "Entity" is defined as SQL query

SELECT CAST(fn_AdjustCurrentTimezoneOffset(CreatedDateUTC) as Date) as CreatedDate
FROM dbo.Entity

The SQL query featuring DateDim from report then looks like

SELECT * FROM [Entity] e
JOIN [DateDim] d on CAST(fn_AdjustCurrentTimezoneOffset(e.CreatedDateUTC) as Date) = d.Date

which is unbearably slow when [Entity] table grows large. Even if i resign on adjusting timezone (which I don't want to), it doesn't get much better - i.e. this query is slow as well:

SELECT * FROM [Entity] e
JOIN [DateDim] d on CAST(e.CreatedDateUTC as Date) = d.Date

My feeling is the issue here is with CreatedDateUTC datetime - it needs to be converted to indexed CreatedDateDayUTC date upfront (not a problem since I query special reporting db). My problem is with the timezone - since users have different timezone offset I cannot simply precalculate this.

On SQL level I would be able to get around this by this ugly hack - precalculate three dates (Date1=DATEADD(day,-1,CreatedDateDayUTC)/Date2=CreatedDateDayUTC/Date3=DATEADD(day,1,CreatedDateDayUTC)) on Entity table and adjusting join to something like:

SELECT * FROM [Entity] e
JOIN [DateDim] d on CAST(e.CreatedDateUTC as Date) = d.Date
  and (e.Date1 = d.Date or e.Date2 = d.Date and e.Date3 = d.Date)

However this can't be translated to query defining "Entity" on model level (there has to be just one column which can create relationship do DateDim Date column).

Any idea how to get around this ?


texts: Cannot allocate memory for the buffer pool when using remote access MySQL

texts:
I tried to separate the webserver and mysql server.
MySQL server = 16GB RAM and the config of Innodb_buffer_pool_size = 10GB. And
my Web server = 4GB RAM

My website connect to my database server using remote access with IP assigned. When I running a website it feels very slowly and then web goes down with the log "Cannot allocate memory for the buffer pool"

Does the webserver need more RAM for Innodb_buffer_pool_size too?
Because what I know is that the webserver only run httpd not mysqld. And mysqld handled by remote mysql server.

In my case the web server still need to increase size for Innodb_buffer_pool_size.

Already add "skip-name-resolve" but no luck.

Is anyone here encounter same issue with me?


texts:
I had pgAdmin4-3.6 installed on my Mac. Worked as it should. And there was created a shortcut at installation to open the pgAdmin server.

Installing version pgAdmin4-4.6 there was no shortcut created to start the pgAdmin4 server. The old shortcut opens the old pgAdmin4.

open -a pgAdmin\ 4

also opens the old pgAdmin4-3.6

http://127.0.0.1:49206/browser/# and the "elephant meny" only works if the server is started. It starts when reinstalling from the dmg package.

How do I create a shortcut to start the new pgAdmin4-4.6 server on a Mac?
Or redirect the old pgAdmin4.app to the new pgAdmin4-4.6?


texts:
I've been thinking of introducing multiple tables each of which will store the chat history between two users in an attempt to not have a big table that would eventually be slow and hard to maintain.

Is there any limitation in having 1000s of seperate tables and if not, since each table is seperate doesn't that mean that queries on individual tables are unaffected by the existence of others?


texts: Optimize InfluxQL query for multi core use?

texts:
I have an InfluxQL query (Influxdb 1.8) & it takes a lot of time for it to finish even on c5.4xlarge with Intel Xeon Platinum 8000 with Turbo CPU clock speed of up to 3.6 GHz + EBS io2 with 16k iops available.
But when I look at htop I see only 1 core loaded 100% & the rest are idle. Aws monitoring shows that volume iops while running the query are ~8k which is far from max 16k & ram is at 20%.
Is there a way to optimize the query to spread the load on all cores?
I have other queries & they load all cores ok.
Here's the problematic query:
select count(*) from
(SELECT "pitch" AS "AAAA" FROM "AAAA"."autogen"."imu_messages"),
(SELECT "pitch" AS "BBBB" FROM "BBBB"."autogen"."imu_messages"),
(SELECT "pitch" AS "CCCC" FROM "CCCC"."autogen"."imu_messages"),
(SELECT "pitch" AS "DDDD" FROM "DDDD"."autogen"."imu_messages"),
(SELECT "pitch" AS "EEEE" FROM "EEEE"."autogen"."imu_messages"),
(SELECT "pitch" AS "FFFF" FROM "FFFF"."autogen"."imu_messages"),
(SELECT "pitch" AS "GGGG" FROM "GGGG"."autogen"."imu_messages"),
(SELECT "pitch" AS "HHHH" FROM "HHHH"."autogen"."imu_messages"),
WHERE time > now() - 60d GROUP BY time(1m) FILL(-1)

If you need any additional info let me know & I'll update the question with it.
Thanks.


texts: Simple access query based on many to many relationship

texts:
I have a simple many to many relationship set up on access with "List", "DayJunction" (Junction Table) and "Days". I am trying to query for records in "List" that have no related records in "Days".

I have tried the following SELECT query:

and the following COUNT query:

But neither is giving me the desired result. Is it possible to extract these results using just one query? Also why did the previous two not give me the intended results?


texts: Empty strings in VARCHAR(MAX) consuming space which cannot be recovered

texts:
I have a table with a non-nullable VARCHAR(MAX) column where every row contains an empty string (third party database so I can't change it). I see a huge amount of space use, over 15GB for 1 million rows.

If I run the following query I see a huge number of used_pages for the LOB_DATA even though there is no data in that column. Excluding the VARCHAR(MAX) the row size is under 8000 bytes.

 SELECT o.name AS table_name,p.index_id, au.type, au.type_desc, au.total_pages, au.used_pages, au.data_pages
 FROM sys.allocation_units AS au
   JOIN sys.partitions AS p ON au.container_id = p.partition_id
   JOIN sys.objects AS o ON p.object_id = o.object_id
 WHERE o.name = 'table
 ORDER BY o.name, p.index_id;

type    type_desc             total_pages   used_pages  data_pages
1   IN_ROW_DATA 23258             23252           23188
2   LOB_DATA              1880733             1880455             0
3   ROW_OVERFLOW_DATA   0             0           0

Updates from comments:

The table is an archive table so the data should never have changed (only inserted)
I have an identical table in a seperate database (same server) with similar data that  doesn't exhibit this problem
Rebuilding the index didn't help
large_value_types_out_of_row is 0
DBCC CLEANTABLE didn't help


texts:
What are cons and pros of using BLOBs on DB2 v9 (or later) on z/OS?

Clarification: are there some limits (like influence on reorg, HA)?

We would like to store large amount of files: 10 millions in the period of up to 6 months. Most of the files should be between 20k and 100k, but there could be exceptions (files greater than 1MB). Most of the files should be XMLs, but some of them could contain digital signatures or other binary data.


texts:
Converting a regular column to a persisted computed column is causing this query to not be able to do index seeks. Why?
Tested on several SQL Server versions, including 2016 SP1 CU1.
Repros

With a computed column
With a regular column

The trouble is with table1, col7.
The tables and query are a partial (and simplified) version of the originals. I'm aware the query could be rewritten differently, and for some reason avoid the problem, but we need to avoid touching the code, and the question of why table1 cannot be seeked still stands.
As Paul White showed (thanks!), the seek is available if forced, so the question is: Why the seek is not chosen by the optimizer, and whether we can do something differently to make the seek happen as it should, without changing the code?
To clarify the problematic part, here's the relevant scan in the bad execution plan:


texts: Moving from SQL 2005 [SQL_Latin1_General_CP1_CI_AS] to 2008 - will I lose any features by using 'backwards compatibility'

texts:
We're moving from SQL 2005 [Instance and DB have collation of SQL_Latin1_General_CP1_CI_AS]  to SQL 2008 [which defaults to Latin1_General_CI_AS].

I completed a SQL 2008 R2 installation, and used default Latin1_General_CI_AS collation, with the restoration of the database still on SQL_Latin1_General_CP1_CI_AS. The excepted problems occurred - the #temp tables where in  Latin1_General_CI_AS whilst the db was in  SQL_Latin1_General_CP1_CI_AS and this is where I am now - I need advice on the pitfalls now please.

On installation of SQL 2008 R2, I have the option on installation to use 'SQL Collation, used for backwards compatibility' where I have the option to select the same collation as the 2005 database : SQL_Latin1_General_CP1_CI_AS.

This will allow me to not have problems with #temp tables, but are there pitfalls?
Would I lose any functionality or features of any kind by not using a "current" collation of SQL 2008?
What about when we move (e.g. in 2 years ) from 2008 to SQL 2012? Will I have problems then?
Would I at some point be forced to go to Latin1_General_CI_AS?
I read that some DBA's script complete the rows of complete databases, and then run the insert script into the database with the new collation - I'm very scared and wary of this - would you recommend doing this?


texts: C# ASP.net MVC - Which way is more efficient and quicker to store data into database?

texts:
I am using Microsoft Visual Studio and C# language to query and store data into my database.

I have two ways in which I can store data into my database in SQL server.

For example, I need to store the amount of fruits in my database:

Apples = 10, Orange = 3 and grape = 5 and watermelon = null.

So is it better to store it as:
(UPDATED!
1)

I use a for loop and store it one by one.

for(int i=0; i<  length ; i++)

{ sql.add(fruit[i],value[i],plotdate[i]);}

INSERT INTO fruit (fruit, value, plotdate)
VALUES ('Apple','10','11/01/2016 1:45:00PM');

Or the second method:

2)

I store it all at once,

sql.add(value,value,value,value,plotdate);

INSERT INTO fruit (Apple, Orange,grape, plotdate)
VALUES ('10','3', '5','11/01/2016 1:45:00PM');

Thank you

EDIT:

What I'm trying to achieve:

A device sends me data every 5 minutes so it will send it using the URL

www.example.com/get?apple=10,orange=2,grape=5,watermelon-1,apple=12,orange,13,grape=12 etc..

I then break down the URL I receive and which is why I use for-loop to store all the data for each fruit given by the url.
After doing so the updated version in the database will look like 1).


texts: Foreign keys to prevent "orphan" file rows in a database for a scenario involving projects, files and downloads

texts:
In a system that monitors a file mirror by regularly downloading a file, I want to store a history of the results. So I have three tables:

┌──────────┐ ┌──────────────────┐ ┌────────┐
│projects  │ │downloads         │ │files   │
├──────────┤ ├──────────────────┤ ├────────┤
│project_id│ │download_id       │ │file_id │
│name      │ │project_id        │ │contents│
│(FK?)     │ │download_timestamp│ │(FK?)   │
└──────────┘ │file_id           │ └────────┘
             │(FK?)             │
             └──────────────────┘

All downloads and files belong to a project.
Everytime a file is downloaded, a new download is inserted.
If the same contents already exists in the files table for the same project, the new download will point to the existing file, otherwise a new file is also inserted.

I have two foreign keys in downloads, project_id and file_id. Is there a better structure, especially one that prevents orphaned files (files without an associated download)?


texts: When `nvarchar/nchar` is going to be used with SQL Server 2019?

texts:
With SQL Server 2019 Microsoft introduces UTF-8 support for CHAR and VARCHAR data types and says:

  This feature may provide significant storage savings, depending on the
  character set in use. For example, changing an existing column data
  type with ASCII strings from NCHAR(10) to CHAR(10) using an UTF-8
  enabled collation, translates into nearly 50% reduction in storage
  requirements. This reduction is because NCHAR(10) requires 22 bytes
  for storage, whereas CHAR(10) requires 12 bytes for the same Unicode
  string.

UTF-8 seems to support every script, so basically we can start storing Unicode data in varchar and char columns. And as is said in the documentation, this can reduce the size of tables and indexes, and from there we can get even better performance, because smaller amount of data is read.

I am wondering does this mean we can stop to use nvarchar and nchar columns which implements UTF-16?

Can anyone point a scenario and reason, not to use the char data types with UTF encoding and continue use the n-chars ones?


texts:
We have been saving the backups of one of our production servers (which is actually an Always On availability group)

this is how I am doing my backups and saving to azure:

    sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master
-Q "EXECUTE dbo.DatabaseBackup
@AvailabilityGroups = 'SQLAG',
@URL = 'https://MYCOMPANYbackups.blob.core.windows.net/SQLMAINSERVER-datafiles-full',
@Credential = 'SQLAzureCredential',
@BackupType = 'FULL',
@Compress = 'Y',
@Verify = 'Y',
@LogToTable = 'Y'" -b

This is what I see when attempting to connect to Azure:
what would be my storage account?

I have a job that I have scheduled to send me an email about all the backups on a server. The script that collect information about the backups and send the email is here.

The email tells me where the backup is.
for example:

here is a picture for illustration purposes:

How can I have access to the backup files?

Where are these azure permissions managed?

EDIT:

The answer by David Browne got me there, thank you David.

I am just adding here a set of steps that took me to my backups (after successfully connecting to azure).

01 - Click on Blob

02 - select one of the containers

03 - the files will be there - ready for dowloading

This article is also very interesting:

First look: back up files and folders in Resource Manager deployment


texts:
I'm aware of and use the techniques in this question.

In my environment we have a lot of container procedures that call subprocedures, that may call other procedures, ad infinitum.

For me personally, I normally use TRY/CATCH in each level of the procedures.  This will sometimes result in multiple duplicate error messages but it's clear what happened and in what proc.

I have some coworkers who take a different approach.  Namely:
- Have each stored proc issue a return code
- Evaluate the return code for each stored proc after execution
- Raiserror in the outermost proc if a bad return code is received

I believe they do this so they can get the actual line number from the erroring procedure (instead of having it return the line number of the RAISERROR in the CATCH block).

To me this seems like it would be harder to implement and maintain, but I'm not an expert on SQL Server error handling, so I thought I would bring it up here.

Is there any material advantage to either method for handling errors in container procedures?


texts: Way to add referencing and referenced entities of a specific entity in E-R Diagram MYSQL

texts:
Is there any way to add entities that are referencing a specific entity in an E-R Diagram. I have a database containing 400 tables. I want to see a view containing a specific entity and its referencing and referenced entities. Some MySQL clients like toad give a way to add in referenced entities when you drag a specific entity but I could not find a way to add in referencing entities that is those which have that specific entity/table as foreign key in them.

P.S. Managing an E-R diagram containing 400 tables is very very tedious


texts:
I have a fairly complex sqlite database with several tables and indices.  I'd like to copy a small subset of all the rows into a new database that I can use to test an app.  Is there any builtin export functionality that can do something like that?  eg.  .clone db where titles.id < 100;

Thanks.


texts: Grouping data with an interval but with overlapping time range groups

texts:
I currently have constructed a group by query in which returns a list of time with a 15 minutes interval and its list of count aggregate

SELECT
    CONVERT(DATE, DATETIME_COLUMN) AS DATE,
    CONVERT(TIME(0), DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', DATETIME_COLUMN) / 15)*15, '20000101')) AS TIME_START_INTERVAL,
    DATEADD(SECOND, -1, CONVERT(TIME(0), DATEADD(MINUTE, 60, DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', DATETIME_COLUMN) / 15)*15, '20000101')))) AS TIME_END_INTERVAL
FROM
    TABLE
GROUP BY
    CONVERT(DATE, DATETIME_COLUMN),
    DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', DATETIME_COLUMN) / 15)*15, '20000101')

However, this will only group them by 15 minutes. IS there any way that I can have them grouped as shown in the image? Overlapping of data is expected as the time range overlaps.


texts:
I have below table:

CREATE TABLE Test
(
    Id int IDENTITY(1,1) NOT NULL,
    col1 varchar(37) NULL,
    testDate datetime NULL
)

GO

insert Test
select null
go 700000

insert Test
select cast(NEWID() as varchar(37))
go 300000

And below indexes:

create clustered index CIX on Test(ID)
create nonclustered index IX_RegularIndex on Test(col1)
create nonclustered index IX_RegularDateIndex on Test(testDate)

When I query on my table:

SET STATISTICS IO ON
select * from Test  where col1=NEWID()
select * from Test  where TestDate=GETDATE()

First is making index scan whereas the second index seek. I expect that both of them must make index seek. Why does the first make index scan?