texts:
First let me describe my case:

I need to store around 200extra detail of each user. I am using wordpress. Now there is a default function to save data associated with user. But i am not sure what should i use. In my case i will need some of data in 1 page. For example i need 20 data in one page and 100 data in another. User may need 20 data at once or 100 data at once.

I didn't designed my database table yet. After understand the best way of query i will design it. To get 200 data (fields) of a user if it is ok to make 200 search query then i will user wordpress default meta table. Structure is given below.

| umeta_id | user_id | meta_key | meta_value |

From structure above you can see wordpress save each data of a single user as a single row. SO in this case for one user's 200 data wordpress will save 200 row. And to retrieve the data i have to make 200 queries.

Now if it is better to get all data of user by a single query and save it in session then i will create my own table. Structure is given below.

| User_ID | User_detail_1 | User_detail_2 | User_detail_3 | ...

In the above structure i can save all those 200 data in  one row and can get all in one query and then save to session and user when needed somewhere.

Now in my site i dont need all data in one page. On one page i might need 100 data and in another page i might need 50 data. So considering my case should i save each data as a row(using wordpress meta table) and then make 100 queries for the page where i need 100 data and make 50 queries where i need 50 data?

Or i should use one query and get all the data, save in SESSION then use data from SESSION where 50 or 100 data is needed?


texts: How to Fail-over of MYSQL 8.0 on Red Hat Cluster without Application to Re-login & avoiding service interruption

texts: Getting the error after every fail-over test case is run :- host is blocked because of many connection errors . We need to login back again to database and use Flush Host command to allow new connection to the database ,but this disrupts the ongoing services and defeats the whole purpose of HA .We have bought Red Hat HA Add-on licenses to create the Cluster on which MYSQL 8.0 DB has been installed . App connects to the DB via a VIP and during any DB transaction if we test the Cluster node Fail-over ,it disrupts the ongoing App jobs . Tried using Connection Time-out set to 30 secs (by this time the fail-over completes and DB resources successfully move to the second node ) and setting Connection life-cycle to 10 sec on the connection string ,but same behaviour .Set the SET GLOBAL max_connect_errors=10000 as well . Please suggest a way forward .


texts: Optimize mysql query, on table with 32 million records

texts:
I have a database that I have created from DISCOG, currently it takes between 32 and 40 seconds to query the database for one record, my query looks similar to;

SELECT MIN(NULLIF(year, 0)) as year, genre, release_date, styles
FROM track_master
WHERE (artist LIKE '%Lo%Fidelity%Allstars%')
  AND (track LIKE '%Battle Flag%')

year, genre, release_date, styles, artist and track are all indexed, when I ran the query with EXPLAIN I received a ALL under TYPE column, which as I've been reading is NOT good, however I do not know what else to do to optimize the query.

Besides the mentioned columns, there is also

id int(25)
release_id int(25)
album_title varchar(2250)
master_id int(25)
main_release int(5)
artist_id int(25)


texts: Which way should be selected when move from mysql to mariadb?

texts:
Now, the mysql version is 5.1.I plan to move to mariadb 10.2.Use mysqldump and source to update databases.
Based on MySQL Upgrade Strategies,

  Upgrading to the latest release is recommended before upgrading to the
  next version

.
Way 1:mysql 5.1->mysql 5.7->mariadb 10.2
Way 2:mysql 5.1->mariadb 5.1->mariadb 10.2
Which way is better(less data loss)?


texts:
This is one thing I have been trying to read from few days. I am not getting any satisfactory explanation anywhere. Tried surfing some blogs and videos in youtube.

Appreciate if anyone one can throw some light on this.

What are the query optimization techniques in SQL Server or how to optimize a SQL query in SQL Server?
If we have a query which is 2-3 pages long; what are the first steps to take for optimizing the query?

Faced this in one of the interviews and couldn't give a proper answer other than indexing.


texts:
After a reboot and latest CU for sqlserver 2017 I get this annoying message (the xxxxx was a number)

SSBDT: Dialog timer delete during registration (did:
xxxxxxxxxxxxxxxxxxxxxxxxxxxx:Initiator, OpType: CloseDialog
OpResult: Null)

I've read that it isn't to worry : true?
This message is filling my sql server logs every second: how can I make it stop?


texts: Calling a web service from T-SQL (Stored Procedure) using MSXML

texts:
EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody
    IF    @hResult <> 0
    BEGIN
          EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
          SELECT      hResult = convert(varbinary(4), @hResult),
                source = @source,
                description = @desc,
                FailPoint = 'Send failed',
                MedthodName = @methodName
          goto destroy

When I Call a web service Through Sp IN SQl Server In Above Mention I'm getting error as Shown In The Pics


texts: TokuDB table stats row count decreases to 1

texts:
I have a few Percona 5.6.30 servers with a TokuDB table that has ~120 million rows. On some of these servers, row count in SHOW TABLE STATUS mysteriously decreases until is hits 1. This is accompanied by rather unpleasant performance reduction and happens on both master and slave servers.

I've tried to fix this by running ANALYZE TABLE, running OPTIMIZE TABLE, restarting the affected server, running ALTER TABLE ... FORCE. None of these has had any effect. The only two things that help are ALTER TABLE ... ENGINE=TokuDB and recreating the table from dumped data. These bring row count back to sane values, after which they begin to decrease again.

Any hints about what might have caused this issue and how to fix it would be helpful.


texts: PostgreSQL : Is this because of locks(row level)?

texts:
I have a table with approximately 2.1 million tuples. Which has latitude and longitude columns. I am trying to convert this into a geographic type (point with SRID).

The function(procedure) I have written, works fine when I limit the entries (say : SELECT id,longitude,latitude FROM list_of_location limit 50).

CREATE OR REPLACE FUNCTION convertlatlon() RETURNS VOID AS $$
DECLARE rec RECORD;
BEGIN
    FOR rec IN SELECT id,longitude,latitude FROM list_of_location
    LOOP
    UPDATE list_of_location SET location= concat('SRID=4326;POINT(',rec.longitude,' ',rec.latitude,')') WHERE id=rec.id;
    END LOOP;
END;
$$ LANGUAGE 'plpgsql' ;

When I try to run it on the entire table, PostgreSQL seems to do nothing. Have waited for an hour and a half.
Consumes 99% of CPU on the core it is running.
Does not spring any other instance of PostgreSQL to utilize other cores(since the request is from a single user?).
Is this because of locks(row level)?
How to circumvent this?


texts: MySQL Enterprise Monitor Installation Error: MySQL Instance

texts:
I am trying to set up MySQL Enterprise Monitor on a RHEL8 server with MySQL 8.0.22 Enterprise Edition.
I hit this error:

Blockquote
Error running /opt/mysql/enterprise/monitor/mysql/bin/mysql
--defaults-file=/opt/mysql/enterprise/monitor/mysql/my.cnf -S  /opt/mysql/enterprise/monitor/mysql/tmp/mysql.sock -u root -D mysql -e

"DELETE  FROM user WHERE user = 'root' AND host NOT IN ('localhost',
'127.0.0.1'); ALTER  USER 'root'@'localhost' IDENTIFIED BY '****';
UPDATE user SET User =  'service_manager' WHERE User = 'root'; DELETE
FROM user WHERE User = ''; FLUSH  PRIVILEGES;":

/opt/mysql/enterprise/monitor/mysql/bin/mysql: error while loading
shared libraries: libtinfo.so.5: cannot open shared object file: No
such file or  directory
Press [Enter] to continue:

I was able to continue, but I am not able to load the MySQL Enterprise Monitor dashboard.
I have an existing instance on the server, but I chose bundled MySQL database in the installation.
How do I resolve this error?


texts: how do I set the compression level in tokudb

texts:
Tokudb has various algorithms, lmza, zlib, zstd etc.
These algorithms have the ability to be configured. In that, you can set the compression level to a value between 1 and 10 depending on the algorithm.

How do I do this when creating a table using one of those compression algorithms, https://www.percona.com/doc/percona-server/LATEST/tokudb/using_tokudb.html#tokudb-compression

https://www.percona.com/blog/2016/03/09/evaluating-database-compression-methods/

MariaDB [test]> create table t_zlib (x int, y int) compression=tokudb_zlib;

MariaDB [test]> show create table t_zlib;
CREATE TABLE `t_zlib` (
  `x` int(11) DEFAULT NULL,
  `y` int(11) DEFAULT NULL
) ENGINE=TokuDB DEFAULT CHARSET=latin1 `compression`=tokudb_zlib


texts: MySQL views and table locking (5.1 MyISAM)

texts:
I have a large (160 million rows, 50GB) table in MySQL 5.1 using MyISAM.

If I were to construct a view defined by a fairly complicated query, would a SELECT on the view lock the underlying table?


texts: Adding checkboxes in SSRS reports to filter values on a table

texts:
I am trying to create a report that would list records but I need some way to include or exclude some values probably by using some form of option like a checkbox or a custom element in SSRS.

For example I'd have a table with the following data:

|---------------------|------------------|
|         Name        |     Category     |
|---------------------|------------------|
|         John        |     Project      |
|---------------------|------------------|
|          Bob        |      Support     |
|---------------------|------------------|

Is it possible to have checkboxes on the side that can filter out let's say just rows with "Project" as the category?


texts: Return "parent" rows where only one "child" record exists in one-to-many relationship

texts:
Having a one-to-many relation, I am trying to get records in the accounts table where only one record exists in the accounts_prop table.

I have the following query, but it does not do the desired. The query returns records with more records in the accounts_prop table.

select *
from `accounts`
where exists (
    select *
    from `accounts_prop`
    where `accounts`.`account_id` = `accounts_prop`.`account_id`
      and `von` >= '2017-08-25'
    having count(account_id) = 1
) limit 3


texts: How to make a replica slave member to be primary member?

texts:
I have setup a shard cluster but there is some errors on my config server replica. Below is my configure server replica status:

cnf-serv:SECONDARY> rs.status()
{
    "set" : "cnf-serv",
    "date" : ISODate("2017-08-21T01:28:00.926Z"),
    "myState" : 2,
    "term" : NumberLong(1),
    "configsvr" : true,
    "heartbeatIntervalMillis" : NumberLong(2000),
    "optimes" : {
        "lastCommittedOpTime" : {
            "ts" : Timestamp(0, 0),
            "t" : NumberLong(-1)
        },
        "appliedOpTime" : {
            "ts" : Timestamp(1502320369, 1),
            "t" : NumberLong(1)
        },
        "durableOpTime" : {
            "ts" : Timestamp(1502320369, 1),
            "t" : NumberLong(1)
        }
    },
    "members" : [
        {
            "_id" : 0,
            "name" : "172.19.0.13:27017",
            "health" : 0,
            "state" : 8,
            "stateStr" : "(not reachable/healthy)",
            "uptime" : 0,
            "optime" : {
                "ts" : Timestamp(0, 0),
                "t" : NumberLong(-1)
            },
            "optimeDurable" : {
                "ts" : Timestamp(0, 0),
                "t" : NumberLong(-1)
            },
            "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
            "optimeDurableDate" : ISODate("1970-01-01T00:00:00Z"),
            "lastHeartbeat" : ISODate("2017-08-21T01:28:00.831Z"),
            "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
            "pingMs" : NumberLong(0),
            "lastHeartbeatMessage" : "Connection refused",
            "configVersion" : -1
        },
        {
            "_id" : 1,
            "name" : "172.19.0.4:27017",
            "health" : 0,
            "state" : 8,
            "stateStr" : "(not reachable/healthy)",
            "uptime" : 0,
            "optime" : {
                "ts" : Timestamp(0, 0),
                "t" : NumberLong(-1)
            },
            "optimeDurable" : {
                "ts" : Timestamp(0, 0),
                "t" : NumberLong(-1)
            },
            "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
            "optimeDurableDate" : ISODate("1970-01-01T00:00:00Z"),
            "lastHeartbeat" : ISODate("2017-08-21T01:28:00.831Z"),
            "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
            "pingMs" : NumberLong(0),
            "lastHeartbeatMessage" : "Connection refused",
            "configVersion" : -1
        },
        {
            "_id" : 2,
            "name" : "172.19.0.7:27017",
            "health" : 1,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "uptime" : 119601,
            "optime" : {
                "ts" : Timestamp(1502320369, 1),
                "t" : NumberLong(1)
            },
            "optimeDate" : ISODate("2017-08-09T23:12:49Z"),
            "configVersion" : 185982,
            "self" : true
        }
    ],
    "ok" : 1
}

I can see from above command two replica members are not reachable and the only reachable member is a secondary. So I need to make the secondary member to be primary in order to make the config replica works. I tried below command in secondary but not works:

cnf-serv:SECONDARY> rs.slaveOk()
cnf-serv:SECONDARY> var cfg = rs.config();
cnf-serv:SECONDARY> cfg.members[0].priority=0.5
0.5
cnf-serv:SECONDARY> cfg.members[1].priority=0.5
0.5
cnf-serv:SECONDARY> cfg.members[2].priority=1
1
cnf-serv:SECONDARY> rs.reconfig(cfg, {force: true})
{ "ok" : 1 }

after making this change the secondary is still secondary. It didn't become the primary. How can I fix this replica set issue?


texts:
When I go to publication properties and General Tab in that. I see an option

Subscription expire and may be dropped if not synchronized in the following number of days. Replication meta data is kept for this amount of time.

Here some day is set, i.e. 7.

Now I am trying to change this figure to something else, i.e. 20. So If I make this change will the existing subscription reinitialize?


texts: Performance of a big INSERT...SELECT with multiple JOIN

texts:
I'm having trouble with a big INSERT...SELECT in my DB. For context, the targeted table already have around a million entries and my SELECT query return approximately 2 millions. On a production server, the query execute in 20min.

First question: is it slow or average considering the size of the base?

Second question (if slow): here the request, is there room for improvement (I think there is)? I'm considering using chunking but I'm not sure how.

INSERT INTO output
(id_people, id_product)

SELECT p.id_people, 146
FROM people p
INNER JOIN sell s
  ON (s.id_sell= p.id_sell AND s.status <> 'CLOSED' AND s.date < '2017-08-09 13:29:12.46')
INNER JOIN group g
  ON (g.id_group = s.id_group AND (g.date_cancel IS NULL OR g.date_cancel > '2017-08-09 13:29:12.46'))
INNER JOIN contract c
  ON (c.id_contract = p.id_contract AND c.date_effect < '2017-08-09 13:29:12.46' AND (c.date_end IS NULL OR c.date_end > '2017-08-09 13:29:12.46') AND c.status <> 'CANCELED' AND (c.date_cancel IS NULL OR c.date_cancel > '2017-08-09 13:29:12.46'))
INNER JOIN company co
  ON (co.id_company = c.id_company)

WHERE g.type = 'OD'
AND g.start < '2017-08-09 13:29:12.46'
AND g.end > '2018-08-09 13:29:12.46'

ON DUPLICATE KEY UPDATE

Any hint will be very welcome :)


texts:
Looking into using Galera and MariaDB across 3 data centers. We have a metroE comnection between the sites. Connectivity is pretty stable, but my concern is if a WAN link goes down at one of the sites, and I am writing to the local nodes.

How does it handle that situation?

How many nodes at minimum at each site is recommended?

This question was similar, but different:
 https://stackoverflow.com/questions/27777134/galera-cluster-replication-between-two-data-centers/27793927#27793927


texts: sorting output by incrementing IP in mysql

texts:
I want an incrementing list of IP #'s in a subnet to display on a php web page in a table. Right now, 10.1.2.3 would display after 10.1.2.10 in the sequence.

My current query is this:

SELECT * FROM `ip` WHERE `subnet` LIKE '10.1.2.0/24' ORDER BY ip;

What query do I need?

My IP field is 16 character VARCHAR.


texts: When selecting the maximum date of a column how do I obtain which quarter that date falls into without using a CASE statement?


texts: Is it a bad practice to query pg_type for enums on a regular basis?

texts:
In Postgres, I'm thinking of query pg_type for an up-to-date list of enumerations I'm using on a regular basis. I'd be using something like this:

SELECT pg_type.typname AS enum_type, pg_enum.enumlabel AS enmu_label FROM
pg_type JOIN pg_enum ON pg_enum.enumtypid = pg_type.oid;

or

SELECT distinct pg_type.typname AS enum_type FROM pg_type JOIN pg_enum ON
pg_enum.enumtypid = pg_type.oid;

Is this bad practice?


texts: Shrink database or DB files not reducing size although I dropped biggest table

texts:
Our MDF file was originally 580GB in size, and the biggest table we had [HourlyCounters] was 140 million rows with 580 columns (varchar[50]). It's a table with a very inefficient schema, but there's nothing we can do about that.

Anyways, after moving some data around, I was able to drop [HourlyCounters]. So now, the new table (with the same name) has 50K rows. I then ran SHRINKDATABASE and SHRINKFILE, but the MDF went down about 40GB. I know that's a decent chunk, but I know for a fact that this dropped table was larger than that (the current backup of that table with only 7M rows is 63GB).

How can SQL Server release all that free space? The new version of [HourlyCounters] doesn't currently have any indexes, but I know the old one did have them.

It's worth mentioning how I went about dropping the table:

Rename the original table [HourlyCounters] to [HourlyCounters_Old] with right-click in SSMS and Rename. This table had indexes.
Create a new table [HourlyCounters] with no indexes so that processes that stored in this table are not affected.
Create table [HourlyCounters_New] where I would store the most recent data from [HourlyCounters] (now called [HourlyCounters_Old]). This table now has 7M rows.
Drop [HourlyCounters_Old]

Thanks.


texts: Determine record count grouped as year-month for each user including 0 counts

texts:
I have table with following schema:

article(id,title, content, created_at, created_by)

I want to find all the records created by each user in each month. If the user did not create the the count should show 0. The date range should start from oldest record and end at most recent record. I want to group records by month-year, meaning I want to check records created by each user in each month, if a user did not create any record then it should NOT be skipped in result set rather it should show count for that month as 0.

Assume I have 5 records as follows:

id, title,      content,          created_at, created_by

1,  Some title, some description, 2017-04-21, 2

2,  Some title, some description, 2017-04-10, 2

3,  Some title, some description, 2017-07-15, 2

4,  Some title, some description, 2017-08-21, 2

5,  Some title, some description, 2017-10-04, 2

The result should be like this:

date,    count, created_by
2017-04,  2,     2
2017-05,  0,     2
2017-06,  0,     2
2017-07,  1,     2
2017-08,  1,     2
2017-09,  0,     2
2017-10,  1,     2

Please note that even if the user did not create any record in a specific month still that month-year was included, that is exactly what I want.

I want to do that in MySQL.

Thank You.
And please don't ask what I have tried, I am not good at such complex queries.


texts: Mix of Cyclic and Chained MySQL replication - What do you do with "log-slave-updates"..?

texts:
I have the following MySQL replication topology set up:

Master1 (M1) replicates to Master2 (M2)
Master2 (M2) replicates to Master1 (M1)
Master1 (M1) replicates to Slave1 (S1)

Pictorial View

+<------------+
|             ^
|             |
V             |
M1----------> M2
|
+-----------> S1

All users connect to their masters for selects and updates.

The theory in the cyclic replication is that if I need to do any maintenance on the Master, I can point everyone temporarily at Slave 1 whilst I do the work, and then when Master is back online and the changes have been replicated from M2, I can seamlessly swap everyone back to working on the Master.

S1 is an offsite server in case of a local disaster, so does not need the cyclic replication set up.

The problem I currently have is that any changes made on M2 are not replicating to S1, so in the scenario above where I do need to take the master offline, any changes which get made on M2 in the meantime, will not make it to the offsite copy on S1.

I understand I can set the flag "log-slave-updates" on Master which would then cause changes made on M2 and pushed to Master as part of the replication, to be passed to S1 but I am nervous, that with the cyclic replication in place between M1 and M2, that this will also, in some way try and push the same changes back to M2 again and end up in an endless loop of replication.

I am guessing that MySQL is clever enough to handle this, but can someone help guide me towards anything I may need to consider in this situation before I end up in an infinite loop of replication!!

Many thanks in advance!


texts:
I have two tables :

CREATE TABLE fiber (
 fib_id uuid primary key default gen_random_uuid(),
 fib_number not null,
 fib_object uuid not null reference cable, --useless here but imagine a cable
 fib_optr uuid reference optical_road on delete set null,
 fib_color text not null
);

-- optical road are multiple fiber, spliced together.
-- Onces spliced, it is just one long fiber.

CREATE TABLE optical_road (
 optr_id uuid primary key default gen_random_uuid(),
 optr_name text not null,
 optr_client uuid reference client -- just an example
);

I want to enforce the fact that you cannot delete a fiber if it is linked to an optical road. I cannot find a design without trigger.

Imagine this record :

 fib_id   | fib_number | fib_object | fib_optr | fib_color
 <anuuid> |          1 | <anuuid2>  | <anuuid3>| #12345

Then here I want that DELETE FROM fiber where fib_id=<anuuid> raises an error.


texts: Access: Number format for VBA in queries of variant type

texts:
Lets say I have two queries like

select iif(condition, col, null) from table

select f(col) from table

where col is a double-valued column f is a VBA-public function(value as double) as variant. Somewhere later, I alter the format of the respective field to be displayed with a certain number format, for instance as a percentage. I have decided that f should be a variant since it is supposed to return Null if appropriate. However, in the former case, the format I specify is respected, but if I switch to the latter, the number format is totally ignored. Why?


texts: If I have two tables that have identical constraints, datatypes and primary keys is there a way to 'import' the index from one table to another if they're on the same server, but in separate databases?


texts: BACKUP WITH CHECKSUM works but DBCC CHECKDB detects problems

texts:
A server I administer recently experienced a power outage that caused issues with the SSRS ReportServerTempDB. After the outage, the nightly job that runs Ola's DatabaseIntegriyCheck started reporting problems:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d Master
  -Q "EXECUTE [dbo].[DatabaseIntegrityCheck]
      @Databases = 'USER_DATABASES,SYSTEM_DATABASES',
      @LogToTable = 'Y'" -b

  Command: DBCC CHECKDB ([ReportServerTempDB]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY
  Msg 8979, Level 16, State 1, Server XXXXXXX, Line 1
  Table error: Object ID 133575514, index ID 1, partition ID 72057594040156160, alloc unit ID 72057594041466880 (type In-row data). Page (1:130168) is missing references from parent (unknown) and previous (page (1:123239)) nodes. Possible bad root entry in system catalog.
  Msg 8979, Level 16, State 1, Server  XXXXXXX, Line 1
  Table error: Object ID 133575514, index ID 1, partition ID 72057594040156160, alloc unit ID 72057594041466880 (type In-row data). Page (1:130169) is missing references from parent (unknown) and previous (page (1:123239)) nodes. Possible bad root entry in system catalog.
  Msg 8979, Level 16, State 1, Server  XXXXXXX, Line 1
  Table error: Object ID 133575514, index ID 1, partition ID 72057594040156160, alloc unit ID 72057594041466880 (type In-row data). Page (1:130170) is missing references from parent (unkn...
  Process Exit Code 1.  The step failed.

However, our nightly backup job continued to report success every time it ran. SSRS didn't seem to have any problems either. The backup job does have multiple backup commands rolled up into one step:

BACKUP DATABASE [ReportServer] TO  DISK = N'C:\Backup-SQLServer\ReportServer.bak' WITH NOFORMAT, COMPRESSION, CHECKSUM, INIT,  NAME = N'ReportServer-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [ReportServerTempDB] TO  DISK = N'C:\Backup-SQLServer\ReportServerTempDB.bak' WITH NOFORMAT, COMPRESSION, CHECKSUM, INIT,  NAME = N'ReportServerTempDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [TEST-PH] TO  DISK = N'C:\Backup-SQLServer\TEST-PH.bak' WITH NOFORMAT, COMPRESSION, CHECKSUM, INIT,  NAME = N'TEST-PH-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Step two of our backup job checks the files. It ran every night and reported success each time:

RESTORE VERIFYONLY FROM DISK= N'C:\Backup-SQLServer\ReportServer.bak'
GO
RESTORE VERIFYONLY FROM DISK= N'C:\Backup-SQLServer\ReportServerTempDB.bak'
GO
RESTORE VERIFYONLY FROM DISK= N'C:\Backup-SQLServer\TEST-PH.bak'
GO

This went on for a few days until a convenient downtime. I resolved the issue by restoring/overwriting ReportServerTempDB with a nightly backup from prior to the outage.

Our databases' PAGE_VERIFY_OPTION is set to CHECKSUM.

What scenarios would explain this behavior?


texts:
I use SQL Server and have multiple indexes in system database whit index fragmentation 30% or more.
I don't know that SQL server manage system database index automatically or I want to manage them. and manage index manually by I is a good thing or not.

thanks in advance


texts: SQL query to find Employees Skiils,Companies and projects

texts:
This is my ER diagram :

Actually I tried to write it with recursive CTE but I did not succeed
I was wondering If you could help me with that
Thank you

Sample Data

EmployeeInfo:

EmployeeID   FirstName
---------   ---------
  1            A
  2            B

Skill:

SkillID   SkillName
--------  ---------
  1         Java
  2         Python
  3          CSS

Company:

CompanyID    CompanyName
---------    -----------
   1             X
   2             Y

Project :

Projectcode   ProjectDesc
----------    ------------
1                GTHY
2                GYHU

I need to write a query to give me this result (The Expected Result)

EmployeeID  EmployeeName   Skills       Companies     Projects
---------   ------------   ------       ---------     ---------

   1           A        Java,Python,Css    X,Y            GTHY

I mean The EmployeeID and Name , His skills , Companies he had worked on and projects he had participated in


texts: How are "DEFAULT PRIVILEGES" implemented in Postgresql?

texts:
If I create a new database cluster (via initdb), then create a new database. How are the "default privileges" and "global default privileges" implemented?

For example, when I run \ddp in psql, it shows no rows. Does this mean  ALTER DEFAULT PRIVILEGES adds/removes rows in a table somewhere?


texts: Why does my scalar UDF perform so differently on two different (but extremely similar) servers?

texts:
Recently, I was troubleshooting a strange performance issue that impacted an application's production environment, but not any of the lower environments. I managed to replicate the issue in its simplest form with this query:
SELECT product_id, dbo.TranslateStatusToActive(status_id) FROM prod_Products

TranslateStatusToActive is a very simple scalar UDF, which basically just joins the value given to another table and returns 1 or 0 based on a case statement. I'd post the code, but it's a vendor written function and I'm not particularly interested in getting sued today. (Yes, the logic can be inlined. Yes, it fixes the performance issue. Yes, we've convinced the vendor to implement the change. This is not my question.)
When executing in production, the query would take between 10 and 20 seconds to return results. In development, the same query returns in less than 3 seconds. The execution plans are nearly identical, except for showing that CPU time was around 15000 ms in production and 3000 ms elsewhere.
I suspected there were some environmental differences, so I set up another server that replicated the conditions of production as closely as possible: I made sure the number of CPUs, the amount of memory given to SQL Server, and the specific patch level (13.0.4451) were the same.
I restored a copy of the production database to this new sandbox server, and to my surprise, the query executed about as quickly as it did in development. Once again, the plan and data were identical, save for the extra CPU time. The waits listed in the execution plan were the same types and within a few ms of each other on every environment.
At a loss for what to do next, I enabled optimize for ad hoc workloads on the production server. This fixed the performance issue! One thing, though: neither of the other environments had this setting enabled. I had been regularly clearing the procedure and system caches in each environment during testing, so I don't think it was the result of changing a setting causing a recompile.
Questions

What could cause the UDF to run so differently in each environment despite identical plan and near identical systems?
Why did the production environment need to have optimize for ad hoc workloads enabled to perform as well as the other environments, which did not have it enabled?
Is there some setting I didn't think to check that might cause such a large difference?

Development is shared, while production is currently used only by this application. The third box's usage would be almost the same as the production one. I cleared pretty much every cache they give a DBCC command for. The development environment is used regularly as a training system, so I am fairly confident it wasn't a plan caching issue.
The only difference with the third box is that there isn't an app hooked up to it, but there was little to no app usage while I was testing the function in production, so the difference was, based on my experience working in this environment, negligible. The only thing I couldn't do was restart the production server, but Microsoft's documentation explicitly states that enabling optimize for ad hoc workloads doesn't clear or affect any existing plans, so I don't see what the difference would be.


texts: How to to select data from a large MySQL table? Is there any best solution?

texts:
Let say we have a table with a large number of records. Now to show up all the data using pagination in a basic framework using MySQL query, we can use limits to get a piece of record.

SELECT * FROM TABLE_NAME WHERE CONDITION ORDER BY COLUMN1 LIMIT 0,100;
... and so on ...

Though to my understanding LIMIT will work once the temporary result table is formulated or in other words, the search will go through all the rows once and then the final result will be populated. Firstly Am I right?

The query result takes much time to get data with limit and sometimes the system just reaches execution time limits. Is there any other better solution for this?


texts:
declare @DATADIR varchar(128)
set @DATADIR= 'C:\SQL2012\SH_DATA'

msg:Command(s) completed successfully.

EXECUTE('bulk insert CHANNELS from '''+@DATADIR+'\CHANNELS.txt''
        With (firstrow = 2,
 fieldterminator=''|'',
 KEEPNULLS,
rowterminator=''\n'',
 MAXERRORS = 10000)
 ')

msg:Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@DATADIR".

I am using SQL2012.


texts: Within a PL/SQL procedure, wrap a query or refcursor in HTML table

texts:
It seems really easy if you are using SQL*Plus to use

SQL> set markup html on;

and get some lovely results to the SQL*Plus window.  we have an oracle job that runs overnight and sends an email of results to a number of people.  I would like to wrap a sql statement in an HTML table to be in that message.  What is the best way of doing that?


texts: Safe to kill SPID as long as its a SELECT

texts:
We just ran into a problem, where a server process running a select was blocking our entire public website. What we ended up doing was killing the server process.

My question is: Is it considered safe to kill of a server process as long as the process is only running a SELECT statement?
Or could there be some side-effects to this I haven't considered?


texts:
I've got a multi-site 2008 R2 WSFC with a file share witness. I noticed that the possible owners for the FSW is every node in the cluster. I thought the concept of a FSW was that no node of the cluster owns it. Its a witness that sits outside of the cluster. So what is the purpose of having "owners" of a FSW resource and what would be the effect if I limited the possible owners to just those nodes at the primary site?

This secondary site of this cluster only exists to support a SQL Always On Availability group that's using asnyc commit for DR. Failover to the DR site will be manual. All nodes at the DR site are set to NodeWeight = 0 so they don't participate in quorum anyway.


texts: Primary key added automatially to seconday indexes on MySQL 5.5?

texts:
I have a table (purchaseorders) that has the following colums:

PurchaseOrderID, PurchaseDate, CreatorID, TotalValue

and a primary key consisting of the field PurchaseOrderID

As I often have queries that do something like this:

select * from purchaseorders where creatorID=100 order by purchaseorderID asc;

I thought of creating a secondary index with the fields creatorID, purchaseorderID

Recently I came across a PPT file on Percona's homepage (Indexing Best Practices for MySQL 5.6) which status (on page 15 in case you have the file too) that
"Primary key is implicitly appended to all indexes), key(A) is really key(A,ID) internally.

Questions:

Anyone know if that also applies for MySQL 5.5?

Do I understand right that I should therefore not (or don't need to) specify purchaseorderID when creating the secondary index, as MySQL adds it anyways.


texts: WEHRE IN array values using stored procedure

texts:
I have a select query that uses a WHERE IN clause in a MemSQL database. The list of values searched in are a result of another select query.

Before, I inserted the values for that clause as a string through a web service like so: (1,2,3..).

Now I want to move the second query into a stored procedure, and have the values mentioned above be a part of the parameters for that procedure.

Say I have a table:

create table t (num int);

Then when I try to create the sp:

DELIMITER //
CREATE PROCEDURE test (nums ARRAY(INT)) AS
BEGIN
ECHO SELECT * FROM t WHERE num IN nums;
END //
DELIMITER ;

I get:

ERROR 1064 (42000): Compilation error in function `test` near line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'nums'

So apparently it doesn't work that way. Is there way I can get this to recognize the data type I'm inserting?
I want to avoid manipulating strings, but if there is no choice, would it require running the query as a string in the stored procedure?


texts: MYSQL Dense Rank Trigger or Update Statement

texts:
I have a MYSQL table that I need to rank. Each group of child_no needs to be ranked by Parent_NO. I have a select statement see below, that successfully does the ranking. Now I'm looking at at taking the results of the select and actually updating the table. I would ideally like to do this with a trigger on insert,else an update statement that I can run manually will suffice.

Any help on formulating either option would be greatly appreciated. See example on sqlfiddle (I've added an auto id, and target rank column in by statements below): http://sqlfiddle.com/#!9/ebe33/18

Table

CREATE TABLE ranktest
(`parent_no` varchar(10),
 `child_no` varchar(10),
 `rankfinal` int,
 `id`   MEDIUMINT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
);

INSERT INTO ranking
(`parent_no`, `child_no`)
VALUES
('1234', '20'),
('1234', '21'),
('1234', '21'),
('9845', '50'),
('9845', '50'),
('9845', '52'),
('9845', '52'),
('9845', '53'),
('9845', '53'),
('9845', '55'),
('5678', '60'),
('2468', '10'),
('2468', '10');

Select Statement to gather rank

set @rank = 0;
set @prev_child = NULL;
set @prev_parent = NULL;
select parent_no, child_no, rank from (
    select parent_no, child_no, CASE
     when @prev_parent != parent_no then @rank := 1
     when @prev_child = child_no then @rank
     else @rank := @rank + 1
END as rank,   @prev_parent:= parent_no, @prev_child:=child_no
from ranktest
order by parent_no, child_no
) as rankcalc


texts: Testing equivalence in strings with different character sets

texts:
I have to synchronize a number of tables with string columns between two databases:

the source Oracle 10.2.0.3 NLS_CHARACTERSET WE8MSWIN1252, NLS_LENGTH_SEMANTICS BYTE
the destination Oracle 10.2.0.5.0 NLS_CHARACTER_SET AL32UTF8, NLS_LENGTH_SEMANTICS BYTE

The source database is an off the shelf product that I have limited ability to change.  It stores strings as NCLOB which are truncated with DBMS_LOB.SUBSTR and presented in a view as NVARCHAR2(2000). The destination database stores the same strings as VARCHAR2(4000).

When I execute a statement like

Select origin.view_description, destination.description
from origin, destination
where origin.pk = destination.pk
and origin.view_description <> destination.description

I get hundreds of results that are identical for case, white space and hidden characters. I verified the identical nature using two text tools.  The only difference I can find is the length of the description due to the difference in character set.

I tried using COMPOSE, CONVERT and CAST to only detect real changes in the description but this gives the same results as using only an equivalence operator.

How can I compare two strings in SQL that use different character sets and find the ones that have different text?

Edit: Raj asks if I have used CAST as in

where CAST(origin.view_description AS VARCHAR2(4000)) <> destination.description

This yields the same subset as using COMPOSE or CONVERT with AL32UTF8.  Two strings that are identical as far as upper/lower case, white space, invisible characters but have a different length.  Security restrictions mean I cannot post real data.


texts: Find locations in table that are within a certain radius distance of a given latitude/longitude

texts:
We have a table named areas with column - id, name, lattitude, longitude and position. The position column is of POINT type in MySQL and has latitude, longitude value.

To get areas within 25 kilometers of given latitude, longitude i.e. (28.638753, 77.073803) and order them based on proximity to this point, we are using the following query as recommended here by Google.

SELECT id, name, ( 6371 * acos( cos( radians(28.638753) ) * cos( radians( Lattitude ) ) * cos( radians( Longitude ) - radians(77.073803) ) + sin( radians(28.638753) ) * sin( radians( Lattitude ) ) ) ) AS distance
FROM areas
HAVING distance < 25
ORDER BY distance asc;

The table is:

CREATE TABLE `areas` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) NOT NULL,
  `Lattitude` decimal(18,6) DEFAULT NULL,
  `Longitude` decimal(18,6) DEFAULT NULL,
  `position` point NOT NULL,
  PRIMARY KEY (`ID`),
  SPATIAL KEY `sx_areas_position` (`position`)
)

The explain query gives following. Please note that rows = 21750 as it's test data. Actual data on production will be 100k+ rows.

Following is the index information:

This query is really slow when run with > 100k of records (takes up to 500 milliseconds). Is there any way to optimize it i.e.using spatial index?


texts: Can I delete SQL Server 2008 R2 "audittrace" (.trc) files

texts:
This is precaution question :)

When I was configuring SQL Server 2008 R2 database engine, I checked "Enable C2 audits" which I don't really need. From that time it created a lot of files like audittrace20130918235148.trc. I disabled C2 auditing in SQL Server Management Studio by unchecking checkbox.

Are those files really from C2 audit? If so, can I safely remove them without worring about data stored in databases? Again: I do not need audit logs.


texts:
I have following simplified version of my actual query, in SQL Server 2012. It has a scalar operator in seek predicate when selecting data from Containers table.

What is the purpose of scalar operator in this seek predicate?

CREATE TABLE #EligibleOrders (OrderID INT PRIMARY KEY,
                             StatusCD CHAR(3),
                              CreatedOnDate DATETIME
                              )
--insert logic into #EligibleOrders

--Final Query
SELECT T2.OrderID ,olic.LineItemID,
        SUM(c.quantity) AS ShippedQty,
        COUNT(DISTINCT c.ContainerID) AS ShippedCases
FROM #EligibleOrders T2
INNER JOIN dbo.OrderLineItemContainers (NOLOCK) AS olic
    ON  olic.OrderID = T2.OrderID
INNER JOIN dbo.Containers (NOLOCK) AS c
    ON olic.Containerid = c.Containerid
GROUP BY T2.OrderID ,olic.LineitemID
OPTION (MAXDOP 1)

Execution Plan

Seek Predicate


texts: How to change database encoding in an Amazon RDS Postgresql instance?

texts:
I need to create a database with SQL_ASCII encoding in Amazon RDS PostgreSql database instance.

Since template0 and template1 are both with utf8 encoding, I get the following error

$ createdb -h endpoint.rds.amazonaws.com -U user -E SQL_ASCII -T template0

createdb: database creation failed: ERROR:  encoding "SQL_ASCII" does not match locale "en_US.UTF-8"

DETAIL:  The chosen LC_CTYPE setting requires encoding "UTF8".

In previous versions of Postgresql, template0 had no predefined encoding, so that command gave no errors.

Also, I don't have permissions to change template1's enconding:

postgres=> update pg_database set datistemplate = FALSE where datname = 'template1';
ERROR:  permission denied for relation pg_database

Although the \l command says my user is the owner of template1

Any ideas?


texts:
I want to reverse engineer what exactly happens to the MySQL database when a product is added in Magento. I will have to reproduce the query, because I would like to manually enter new records using a triggered procedure. Is it possible to somehow capture how the changes were made on the database?

Thanks in advance.


texts: Find problematic MySQL users and databases

texts:
We have a MySQL 5.1 server that is an "open" service: anyone on campus can request a database. As such, we have several thousand databases on this one server.

As you might imagine, sometimes one user's database causes problems for all the rest. However, I am finding it difficult to tell which process or account is causing the problem. Using innotop shows me a bunch of queries that are taking a long time to finish, but that could be because other queries are using up all the server's resources.

What I want is an automated (or semi-automated) way to track down the databases or accounts that are using too many resources. For example, it would be nice to be able to run a command that would show me the 10 accounts or databases using the most disk I/O or the most memory.

Are there any tools out there that might help me do this?

(Note: an answer of "you shouldn't be using MySQL this way" although true is not going to help me as I have no choice in how this service is setup: I just have to maintain it.)


texts: How to check if I am hitting the Express Edition size limit?

texts:
I am confused. AFAIK SQL Server 2005 Express has a limit of 4GB database data size. However I have the following results from sp_spaceused:

How can I check if my DB is hitting the size limit?
Is unallocated space the space left untill I hit the limit?
How much space do I have left?
Does index_size count in the limit?


texts:
I'm new to mySQL relastionships and I'm wondering if you can help me out.

This is what I want to do:

Users Table

user_id
user_name
pass_word
permission_id

Permissions Table

p_id
permission_name
permission_type

I want to create a relationship between p_id (permissions table) & permission_id (user table) so when I query the user table it also brings through the corresponding permission name & type?

Is this possible or am I getting it all wrong?

Should I just use joins?


texts:
We have it set so that any database that shows an error code during the server scan is set to write the database name into a table. What I want to do is then have an email generated that will send the database(s) that threw an error code.

I have been toying with this syntax; however, it will not execute, I keep getting:

  Msg 422, Level 16, State 4, Line 9
  Common table expression defined but not used.

This is my syntax -- what would be my best way to accomplish this?

Scan the table [dbo].[FailingDatabase] and get the values from the two fields datefaield, database
Execute the Select Statement to produce a result set of the failed databases
Generate an email (I can do this element, this part is no issue)

Here is DDL for my table FailingDatabase:

Create Table [dbo].[FailingDatabase]
(
  datefailed datetime
  ,[database] varchar(1000)
)

Insert INTO [dbo].[FailingDatabase] VALUES
('2016-03-18 08:29:00.000', 'server.database.dbo.tablename')
,('2016-03-18 08:35:00.000', 'server.database.dbo.tablename')

I am trying to use a CTE to query the databases, as I set a flag in the email based off if I can query the database. If it queries succesfully, then I know the database is still up and running. If I am unable to query the database, then it is offline for whatever reason, and I need to immediately try to remedy.

Here is my un-working syntax...

declare @fullsyntax varchar(MAX)
;with faileddb
as
(
  Select Top 2 [databasename] FROM [dbo].[FailingDatabase]
)
select @fullsyntax += 'Select datefaield, database FROM ' + [databasename] + 'from faileddb; '
Print @fullsyntax

Could I also add a where clause like:

FROM '+[databasename]+'
WHERE databasename <> ''Master'';'
from faileddb;


texts: Filemaker Database. How to import to one table then copy information to a different table and populate realted tables

texts: I'm using filemaker to create a union membership database, the problem i'm having is that you can't import files into related tables. Basically i'm trying to figure out how I could import an excel spreadsheet containing member information and dues payment information and then populate the correct member and their related table information. I read something I thought might be helpful about magic keys and tried to implement that but i'm wondering if my parent table needs to be my flat import table.


texts: Car service database design (domain model)?

texts:
First of all I know that there is no Car, Model, Manufacture... tables that I probably should make, but that is not a problem right now.

I must say that I have already made database and relational model looks like this (I simplified it, to concentrate on important), but I need to draw a conceptual (domain) model of this:

Client (clientID, name, surname, telephone)
Address (clientID, number, street, cityZipCode)
City (cityZipCode, cityName)
Visit (clientID, visitDate, totalPrice)
Service (serviceID, serviceName)
Criteria (criteriaName, size, measureUnit)
ServicePriceCriteria (serviceID, criteriaName, size, price)
VisitItem (clientID, visitDate, serviceID, criteriaName, size, quantity)

My the biggest problem was how to provide that ONE service can have different prices for different criteria (i.e., Oil Changes for the truck costs 15€. For suv same service costs 8€.... Other example is when price depends on the size of the wheel. For 14 inches wheel some service costs 10€ and the same service for 17 inches wheel costs 15€.)

So for the first example I have:

Service(123, 'Oil Changes'), Criteria('typeOfVehicle', 'Truck', null), ServicePriceCriteria(123, 'typeOfVehicle', 'Truck', 15)
Service(123, 'Oil Changes'), Criteria('typeOfVehicle', 'SUV', null),ServicePriceCriteria(123, 'typeOfVehicle', 'SUV', 8)

For the second example it looks like this:

Service(124, 'Some Service'), Criteria('Size of wheel', '14', inches), ServicePriceCriteria(124, 'Size of wheel', '14', 10)
Service(124, 'Some Service'), Criteria('Size of wheel', '17', inches), ServicePriceCriteria(124, 'Size of wheel', '17', 15)

I hope so you realized what was my idea. I wanna to ask you is this my solution acceptable at all? And is this correct domain model of this database:
http://imageshack.us/photo/my-images/257/conceptual.png/


texts: Recreate Indexes on 1 billion record table

texts:
I have a table with over 1 billion records and it has 6 indexes (including Clustered index (ID)). I need to partition this table on a new Clustered index with date column. I have just enough space (150gb) in the ldf file to grow in case of index rebuild (with tempdb OFF).

Please help me with an efficient way to rebuild the indexes on new Partition function/scheme. Which of the two approaches would be an efficient and less resource consuming way (OR recommend any other approach)?

1.

Drop existing CIX and drop all N-CIX's
Create CIX on PScheme
Create aligned/non-aligned indexes on PScheme

2.

Drop only CIX
Create CIX on PScheme
Create aligned/non-aligned indexes (with DROP_EXISTING) ON

Thanks


texts:
I wanted to check if my AWS RDS server is over spec'd, it currently costs loads of money and we are trying to reduce our costs.

We are using a db.m5.2xlarge (32 GiB memory / 8 vCPUs / 5000 IOPS).

After using cloudwatch to view some stats i realised that our IOPS seems way to high.

As you can see we never go over 2000 IOPS if you combine read and write.

Our system is constantly being written to every second hence the write IOPS being so high. We only read from the system when someone logs into our portal and runs some reports to view the data. To give some context we are a footfall company who collect town footfall.

We are currently using Provisioned IOPS with 600GB of storage, I'm thinking of switching to General Purpose SSD upping the storage to 1000GB so we get 3000 IOPS just to be safe. Does this seem like a good idea?

I have broke the prices down below and if the performance stays the same this seems like a no brainer.

Provisioned IOPS
$348 (3000 IOPS @ $0.116)
$145 (1000GB @ $0.145)

General Purpose SSD
$133 (1000GB @ $0.133) this would have 3000 IOPS (3 IOPS x 1GB)

I am yet to look into the memory and vCPUs so this may also need reducing but my main concern right now is the IOPS.

Thanks
Luke


texts:
I have a mariaDB database containing details about user devices (sort of an 'allowed device' list for VPN access). Back when the project started, there was no restriction on what devices were allowed so, everyone has 2 devices registered to their name. I've been tasked with removing the older device from the database.
This is a simplified database schema:

UserID|RegisterDate|UniqueID
12345|12-08-2020|ABC123
12345|11-05-2020|XYZ789
23455|20-08-2020|QWE345

The requirement is that I have to delete the older device, in case the user has two devices. So in this example, deviceID XYZ789 belonging to User 12345 would need to be deleted.
What I have so far is this:
select min(RegisterDate), UserID from DeviceDetails  where RegisterDate in (select RegisterDate from DeviceDetails) group by UserID having count(*) > 1;
I understand this is very convoluted, and there is definitely room for improvement, as it takes around 3 minutes to run on around 14k rows.
The next step would be to get these rows exported into a log, and then deleted.
I'm not really a DB person, but its been tasked to me as we inherited this product from the previous vendor and it just works so far.
Database details:
mysql  Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1
CentOS 1810
Out of date, yes, but that decision is taken above my pay-grade.


texts: Only local administrators can connect remotely

texts:
I'm facing a problem that when I try to connect to the SQL Express instance from client to server. The Server logs an error:

  SSPI handshake failed with error code 0x8009030c, state 14 while
  establishing a connection with integrated security; the connection has
  been closed. Reason: AcceptSecurityContext failed. The Windows error
  code indicates the cause of failure. The logon attempt failed
  [CLIENT: 192.168.245.50]

The account I log on with is sysadmin and have all the possible permissions on the database its trying to connect to but still it fails. But if I add the user that is trying to connect to the local administrators group it works. This is not a good solution, I don't want all users to be local administrators on the server. Why is it that the user needs to be local admin?

And I think the SPN is correctly set.

Its all in same domain and using domain accounts.
Windows Autentication is used for login.
Shouldnt be problem with the connectivity to the DC.
Named instances name is used (it works if the domain user is local admin).

Log from SQL error log:

  03/09/2015 15:22:47,Logon,Unknown,Login failed. The login is from an
  untrusted domain and cannot be used with Windows authentication.
  [CLIENT: 192.168.245.50] 03/09/2015 15:22:47,Logon,Unknown,Error:
  18452 Severity: 14 State: 1. 03/09/2015
  15:22:47,Logon,Unknown,SSPI handshake failed with error code
  0x8009030c state 14 while establishing a connection with
  integrated security; the connection has been closed. Reason:
  AcceptSecurityContext failed. The Windows error code indicates the
  cause of failure. The logon attempt failed   [CLIENT: 192.168.245.50]
  03/09/2015 15:22:47,Logon,Unknown,Error: 17806 Severity: 20
  State: 14.

Log from the client trying to connect:

  LSRetailPosis.POS.Program: System.Data.SqlClient.SqlException
  (0x80131904): Login failed. The login is from an untrusted domain and
  cannot be used with Windows authentication.    at
  System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection
  owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean
  allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions
  userOptions, DbConnectionInternal& connection)    at
  System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection
  owningObject, TaskCompletionSource1 retry, DbConnectionOptions
  userOptions, DbConnectionInternal& connection)    at
  System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection
  owningConnection, TaskCompletionSource1 retry, DbConnectionOptions
  userOptions, DbConnectionInternal oldConnection, DbConnectionInternal&
  connection)    at
  System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection
  outerConnection, DbConnectionFactory connectionFactory,
  TaskCompletionSource1 retry, DbConnectionOptions userOptions)    at
  System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection
  outerConnection, DbConnectionFactory connectionFactory,
  TaskCompletionSource1 retry, DbConnectionOptions userOptions)    at
  System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1
  retry)    at
  System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1
  retry)    at System.Data.SqlClient.SqlConnection.Open()    at
  LSRetailPosis.Settings.Database.CheckDatabaseExistence()    at
  LSRetailPosis.Settings.Database.CheckDatabaseSettings()    at
  LSRetailPosis.Settings.ApplicationSettings.InitializeSettings(String[]
  args)    at LSRetailPosis.POS.Program.InitializeAndLoadPOS(String[]
  args, Boolean owned, SplashScreenForm splashScreenForm)    at
  LSRetailPosis.POS.Program.Main(String[] args)
  ClientConnectionId:d57c7fd9-4a47-41b7-af07-ed93627d2930.


texts: Upgrade in-place with low disk space on C: drive

texts:
I am due to upgrade SQL Server 2005 Express to 2014 Express. This will be an in-place upgrade.

It's not my preferred way of upgrading, but being a physical machine and in a specific secure network zone, it has to stay put & keep its instance name, so it must be an in-place upgrade.

The only small issue is there is just under 10% free space available on the C: drive (the drive has 3GB space left). I hope to upgrade in-place and then once complete, go through getting disks replaced for bigger disks etc. (this is due to project upgrade deadlines).

Could someone tell me how much extra space will be put onto the C: drive with a SQL Server Express 2014 upgrade, please? Note no system databases live on the C: drive. Sadly no housekeeping can be performed by the Windows team etc.


texts: "Could not insert a backup or restore history/detail record in the msdb database" While restoring msdb

texts:
Today I found msdb database was in suspected mode, so I restored MSDB from my latest backup(As I have daily backup for this database). When I restore the MSDB database  using Tsql i got below errors, but database restore was successful.

Processed 17056 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 5 pages for database 'msdb', file 'MSDBLog' on file 1.
Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK__backupse__21F79AAB0E391C95'. Cannot insert duplicate key in object 'dbo.backupset'. The duplicate key value is (60979).
Msg 3009, Level 16, State 1, Line 2
Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.
The statement has been terminated.
RESTORE DATABASE successfully processed 17061 pages in 6.953 seconds (19.169 MB/sec).

But after restore I found that SQL server instance is running proper and all database are accessible. But to ensure about the error I got during restore I used CHECKDB command for the MSDB database.It shows below errors.

DBCC results for 'msdb'.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Msg 2515, Level 16, State 1, Line 1
The page (1:1044), object ID 60, index ID 1, partition ID 281474980642816, allocation unit ID 281474980642816 (type In-row data) has been modified, but is not marked as modified in the differential backup bitmap.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.sysobjvalues' (object ID 60).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'msdb'.

So what are these errors? Also is it due to the error I got during restore? Is there anything I need to do as I got error while restore?


texts: Having performance issue for an extract date without time to compare with sysdate,

texts:
I need to get all the rows where its 'time' field is equal to sysdate. The thing is I just want to compare dd/mm/yyyy and as my fields are in the date format I'm using

select *  from myTable where trunc(timefield) = trunc(sysdate)

If there's only one field which is truncated everything works fine, but when I want to execute that request it takes too many times (> 10 minutes and still running).

Is there a way to bypass that?


texts: Check connection details to SQL Server 2000/2005

texts:
I want to migrate some databases hosted in SQL Server 2000/2005 to SQL Server 2014.

I don't have direct access to the databases and I want to know all applications connecting to it and connection details. Is there any best way to get this information? I tried using DB profiling but its not allowed as its taking up lot of space on hosted DB server which causing disruptions.

Any better way like a SQL statement or remote connection monitoring will be helpful.

Thanks in advance for all your help.


texts: Upgrade scenario: Oracle 8.0.5.0.0 to Oracle 12c?

texts:
we have a very old Oracle 8.0.5.0.0 database here that - unfortunately - is still used heavily as every day production system.

I want to start making plans on how to get this DB upgrade to a recent Oracle 12c.

Unfortunately, both DB versions don't seem to be able to talk to each other anymore (*). So to my current knowledge, the only solution is to import daily backups from 8.0.5.0.0 into 12c, which - from the technical point of view - works, but is not feasible because the 8.0.5.0.0 is used in daily business. Beside: The data on the 8.0.5.0.0 server is that large that an import of the backup in the 12c server (on a recent Dell server) takes about 6-8 hours.

So, the question is:

What might be a good approach for a upgrade strategy from 8.0.5.0.0 to 12c?

Thanks for any input.

(*) Of course old Oracle 8.0.5.0.0 clients can talk to the Oracle 8.0.5.0.0 server and recent 12c clients can talk to the 12c server. But there does not seem to be a client solution that can talk to both 8.0.5.0.0 and 12c.


texts:
I'm attempting to export from a physical Oracle 11.2.0.3.0 database to a amazon rds Oracle database.
I used the expdp and exported a full database and incurred no errors upon export.

I've moved the copy over to my server running SQL Developer in my AWS environment, and using the impdp command I incur a slew of errors.

I've seen snippets in my searches for a few things, but having tried suggestions, it seems that isn't the error.

c:\Windows\system32\> impdp c2sadmin@x.x.x.x:1521/DATABASE file=e:\export\full_export.sql log=e:\export\import.log

Running the above command I get the below.

Import: Release 11.2.0.1.0 - Production
Connected to Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
ORA-31626: job does not exist
ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_LOGSTDBY"
ORA-06512: at "SYS.KUPV$FT", line 1009
ORA-04045: errors during recompilation/revalidation of SYS.DBMS_LOGREP_UTIL
ORA-20900: RDS restricted DDL Found: ALTER VIEW SYS.ALL_CAPTURE
ORA-06512: at "RDSADMIN.RDSADMIN", line 367
ORA-06512: at line 2
ORA-04063: view "SYS.DBA_CAPTURE" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGPREP_UTIL"

I've tried different schemas and all end with the same error.
Looking AWS disabled the ... AS SYSDBA option with logging in, so I can't do that either.

I've seen some random web posts regarding datapump and RDS not playing nicely, but I can't get anything concrete.

Our DBA's have assured me that the data pump export worked flawlessly, as they could import it to another physical oracle database. So could this just be an RDS issue and not Oracle?


texts: Calling MySQL stored procedure in python with field names

texts:
I am trying to call a MySQL stored procedure that returns a single row, I can get the values back back using cursor.callproc() and cursor.stored_results(), but I can't get name of the field back 'fileHash'

If I call the SP directly I get results such as:

mysql> call GetAllFileHashesForDirectory('/myroot/');
+------------------+
| fileHash         |
+------------------+
| testa            |
| testb            |
+------------------+
2 rows in set (0.00 sec)

But, when I try and get the field name I get the following instead of 'fileHash':

(u'@_GetAllFileHashesForDirectory_arg1',)

The python code I use to get the value is:

self.__mySQLCursor.column_names

Do I need to specify the name in a stored procedure?  I am really confused.


texts: postgresql cumulative counts in date range


texts: Need a faster method to concatenate rows into column(s)--currently using CTEs

texts:
Currently, we are using a view which I created that pulls data from a number of tables.  The major factor about this is getting the view to specifications involves a mix of CTEs and string concatenating to combine multiple rows into one column (multiple times).
While the view works, users have recently complained about it being too slow.
Currently, the view setup looks something like this:

;
With thing1 AS
(
SELECT DISTINCT
            /* huge block of code with joins and such
            that gets the individual fields and concatenates
            personnel names to one row */
)
,
thing2 AS
(
    SELECT  TaskName,
            Company,
            Lease,
            TaskBegin,
            TaskEnd,
            Field,
            Personnel,
            Invoice,
            InvDate,
            InvTot,
            ROW_NUMBER() OVER (Partition by TaskName ORDER BY [TaskName] ASC) rn
    FROM thing1
)

SELECT  TaskName,
        Company,
        Lease,
        Field,
        ISNULL(Personnel, '') as 'Personnel',
        TaskBegin,
        TaskEnd,
        ISNULL (STUFF( (SELECT ', ' + Invoice
                FROM thing1
                WHERE (thing1.TaskName = thing2.TaskName)
                AND thing2.rn = 1
                ORDER BY Invoice
                FOR XML PATH('')), 1, 1, ''), '') AS 'Invoice',
        ISNULL (STUFF( (SELECT ', ' + InvDate
                FROM thing1
                WHERE (thing1.TaskName = thing2.TaskName)
                AND thing2.rn = 1
                ORDER BY Invoice
                FOR XML PATH('')), 1, 1, ''), '') AS 'InvoiceDate',
        ISNULL (STUFF( (SELECT ', ' + InvTot
                FROM thing1
                WHERE (thing1.TaskName = thing2.TaskName)
                AND thing2.rn = 1
                ORDER BY Invoice
                FOR XML PATH('')), 1, 1, ''), '') AS 'InvoiceTotal'
    FROM thing2 WHERE rn = 1

GO

So it works as intended.  Problem is, once I get to that final SELECT that concatenates the Invoice, InvDate, and InvTot, the query goes from not needing a single second (that is, a straight SELECT from thing2) to taking up to 7 seconds to run its course.  (Average time is about 5 seconds.)  I'm fairly confident that part of the blame lies in the ever-growing database--this view loads much faster on the test environment, which has far fewer records in it.

My question is thus:  Is it possible to go about this in a way that is faster and less resource-intensive on the DB?


texts: Why does SQL Server "Compute Scalar" when I SELECT a persisted computed column?

texts:
The three SELECT statements in this code

USE [tempdb];
GO

SET NOCOUNT ON;

CREATE TABLE dbo.persist_test (
      id            INT NOT NULL
    , id5           AS (id * 5)
    , id5p          AS (id * 5) PERSISTED
);

INSERT INTO dbo.persist_test (id)
VALUES (1), (2), (3);

SELECT id
FROM dbo.persist_test;

SELECT id5
FROM dbo.persist_test;

SELECT id5p
FROM dbo.persist_test;

DROP TABLE dbo.persist_test;

generate this plan:

Why does the final SELECT, which is selecting a persisted value, generate a Compute Scalar operator?


texts: Using RDBMS with pseudo foreign keys VS using NoSQL solutions

texts:
According to some database administrators for our org, it is generally not advisable to actually enforce foreign-key relations within our MySQL DBs. Instead, it is preferred to simply represent this with a pseudo foreign-key ID column and to instead perform extra application processing for the foreign-key coercion. The reason is that inserts and deletes (especially cascading ones) become prohibitively expensive as the database scales out.

But does this not defeat the purpose of a RBDMS to begin with? From what I understand, it seems that one of the biggest reasons to use a RBDMS to begin with (besides enforcing ACID properties) is to ensure application query processing involving related (i.e. bound by junction tables via FKs) objects are minimized.

Is it in general more practical to utilize pseudo foreign-keys with additional application processing then? And, if so, why would you even want to use an RBDMS? I presume the reason would be that the application processing would still be slightly less (and way more straightforward) than a NoSQL solution?


texts: Is the source system typically one of the fields used to uniquely identify business key?

texts:
One of the key components of designing a data vault is identifying enterprise-wide unique business keys ("business key" AKA "natural key").  It's not enough to use OrderID to identify records in an Orders table, because when you add another orders table from another source system (e.g., a brick-and-mortar store launches an online store), there may well be collisions.

In Data Vault best practices, should one include the name of the source system as one of the fields used to uniquely identify a record, when adding it to a hub table?  Rather than searching for some combination of fields in Orders which will hopefully be universally unique ({ CustomerID, OrderID, and OrderDate }, maybe), one could identify brick-and-mortar orders with { "Brick-and-Mortar Order System", OrderID }, and one could identify online orders with { "Online Order System", OrderID }:

This seems like the obvious solution, but I've not seen it mentioned in any of the Data Vault modeling documents I've found, so I wonder if there's some reason not to use this approach.

The Data Vault approach seems to be an outgrowth of Master Data Management practices, so if this is a solved problem in MDM, that solution probably applies here too.


texts: Add Leading Zeros in CASE, CHARINDEX and SUBSTRING Query

texts:
I am splitting a column based on the colon using this code which works.
    ,CASE WHEN CHARINDEX (':', ref) > 0
THEN SUBSTRING(ref, 1, CHARINDEX(':', ref) -1)
    ELSE NULL
END AS EndVal

The 'EndVal' is numeric and when it is less than 4 characters long I need to add leading Zeros so

123  = 0123

1    = 0001

1234 = 1234
I know I can accomplish that using the RIGHT or REPLICATE function but I cannot get my head round how I either add it to my existing code or edit it so it does both jobs?

Any advice on the original code also greatly appreciated. Having to get this to work on SQL 2008, yes I want to upgrade but...


texts: Trouble figuring SQL Count When Dates Overlap

texts:
I'm trying to query a DB with reservations. Each reservation has a Start Date (start), End Date (end), and Type (type) and I feed a month (month)

I want to query the DB to get the # of each type of reservation within a given month. However I can't for the life of me get it to count only the reservations within a date range.

I already managed to get it to give me counts, by type, for all reservations. And also give me only reservations within the date range. But filtering by specific months and counting the type is proving difficult and Google hasn't gotten me anywhere.

My Current Attempt:

// Queries the database for currently scheduled reservations and counts by type
        DateTime today = DateTime.UtcNow;
        DateTime firstDayOfMonth = new DateTime(today.Year, month, 1);
        DateTime lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1);
        string query = $"SELECT type, " +
            $"SUM(CASE WHEN start >= '{firstDayOfMonth}' AND start < '{lastDayOfMonth}' THEN 1 ELSE 0 END) AS count " +
            $"FROM reservation " +
            $"GROUP BY type";

Any guidance would be helpful. How do I get it to only count what's in the date range?


texts:
Let's say I have two tables, foo and bar:

CREATE TABLE foo(
   foo_id    serial    NOT NULL,
   count     integer   NULL,
   PRIMARY KEY(foo_id)
   );

CREATE TABLE bar(
   bar_id    serial    NOT NULL,
   foo_id    SERIAL    NOT NULL REFERENCES foo(foo_id),
   PRIMARY KEY(bar_id)
   );

When something gets inserted into bar with a given foo_id, the corresponding record in foo should have its count incremented. How exactly do I go about doing this? I'm fairly new to databases in general.

For example:

INSERT INTO foo VALUES(1, 0);
INSERT INTO bar VALUES(1, 1);

At the second line, count should be incremented at the record for foo_id = 1.

I also need to the count decremented on a delete, but I'm sure that's pretty similar to incrementing on insert.


texts:
I have a Mysql Database and tables with the structure:

Table 'photos' - ID, Name
Table 'photos_tags' - Key, Photo_ID, Tag

My goal is to select all photos, which has one tag (it's not a problem) and more tags at once.

I've tried this query, but it's not work

SELECT photos.id FROM photos, photos_tags WHERE photos.id = photos_tags.photo_id AND (tag = 'cute' OR tag = 'cool')

The main problem - this query will select both photos with one of these tags or with two of them. But i need only photos with two these tags at one photo.

Can you help me please? I'm new at MySQL

UPDATE: May be there is a better way to organize this schema? My current realization has 'tags' text-field right in table 'photos'. And I use FIND_IN_SET to make a select. But my table has about 1.5M rows - and some queries are very slow.


texts: why order by desc and limit query is taking too much time?

texts:
Currently, i was trying to make a sql query on my postgresql table, all things was going good unless i sort my query by Price in DESC, after this filter query is taking almost round about 60 sec to return results. before that, if i am sorting it in ASC, it takes on few mseconds.
Here it is my query and its query plan.

explain analyze
select *
from products_allproducts
Where lower("SNR_Category")=lower('Mobile Apps')
ORDER BY "SNR_Price" ASC
LIMIT 36 OFFSET 0;

and its query plan is

    QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.44..19620.51 rows=36 width=595) (actual time=221.081..221.148 rows=36 loops=1)
   ->  Index Scan Backward using snr_price_desc on products_allproducts  (cost=0.44..58484176.24 rows=107310 width=595) (actual time=221.079..221.145 rows=36 loops=1)
         Filter: (lower(("SNR_Category")::text) = 'mobile apps'::text)
         Rows Removed by Filter: 83205
 Planning time: 0.101 ms
 Execution time: 221.167 ms
(6 rows)

but as soon as, i sort it in DESC, it takes almost 60 seconds. like this.

explain analyze
select * from products_allproducts
Where lower("SNR_Category")=lower('Mobile Apps')
ORDER BY "SNR_Price" desc
LIMIT 36 OFFSET 0;

and its query plan is.

    QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.44..19620.51 rows=36 width=595) (actual time=10000.687..28416.939 rows=36 loops=1)
   ->  Index Scan using snr_price_desc on products_allproducts  (cost=0.44..58484176.24 rows=107310 width=595) (actual time=10000.686..28416.932 rows=36 loops=1)
         Filter: (lower(("SNR_Category")::text) = 'mobile apps'::text)
         Rows Removed by Filter: 7679720
 Planning time: 0.085 ms
 Execution time: 28416.960 ms
(6 rows)

I have find the problem, if i remove limit section from desc query its taking only few mili seconds

Can anyonle tell me that how can i improve my speed while sorting in desc with limit query? because at end i need only few records.

here is query of DESC which taking few seconds.

explain analyze
select * from products_allproducts
Where "SNR_Category"~*'Mobile Apps'
ORDER BY "SNR_Price" desc;

and its query plan.

    QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather Merge  (cost=128208.97..131324.65 rows=26704 width=595) (actual time=142.024..156.919 rows=32008 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Sort  (cost=127208.95..127242.33 rows=13352 width=595) (actual time=136.088..138.304 rows=10669 loops=3)
         Sort Key: "SNR_Price" DESC
         Sort Method: external merge  Disk: 5336kB
         ->  Parallel Bitmap Heap Scan on products_allproducts  (cost=4796.35..122731.02 rows=13352 width=595) (actual time=73.671..116.140 rows=10669 loops=3)
               Recheck Cond: (("SNR_Category")::text ~* 'Mobile Apps'::text)
               Heap Blocks: exact=5113
               ->  Bitmap Index Scan on snr_category_trigram_idx  (cost=0.00..4788.34 rows=32045 width=0) (actual time=74.976..74.976 rows=32008 loops=1)
                     Index Cond: (("SNR_Category")::text ~* 'Mobile Apps'::text)
 Planning time: 0.293 ms
 Execution time: 163.839 ms
(13 rows)


texts: Imported database file size different with original database file

texts:
I am using SQL Server 2000 Data Transfer Service (DTS) wizard for import database from another existing SQL Server 2000 database. I also choose "Copy objects and data between SQL Server databases" option when import.

After import finished, I saw that size of new imported database are smaller than original database. Is this normal? I worried if that any object are not imported to new database, such as stored procedure or view.

How can I make sure if the new database's object are same with old database?


texts: Import bacpac from SQL Azure into local server fails.

texts:
We are trying to import a bacpac into a local SQL 2017 instance but it gives the following error. This suddenly started happening, previously we didn't have any problem with it so I can't think of any change that could have caused this.

Copying the SQL database via the portal into another SQL Azure server still works thou.

This is a rather big database (12GB of data, bacpac is around 900mb thou).

I'm really baffled why he is giving me some sort of SQL Server 2014 error.

My current SQL server version:

Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)   Aug 22 2017 17:04:49   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 10.0  (Build 16299: )


texts:
I have a Microsoft SQL DB running on Azure VM

I have daily snapshots of the VM and this is currently my only backup mechanism.

Is this considered best practice? Am I risking something here?


texts: What's the most efficient way to batch UPDATE queries in MySQL?

texts:
I'm writing an application that needs to flush out a large number of updates to the database for an extended period of time, and I've gotten stuck at how to optimize the query. Currently I'm using INSERT INTO ... VALUES (..), (..) ON DUPLICATE KEY UPDATE, which works to batch all of the values into one query, but executes excruciatingly slowly on large tables. I don't ever actually need to insert rows.

Other approaches I've seen are to update using SET value = CASE WHEN... (which would be hard to generate due to the way I'm building the queries, and I'm not sure about the performance of CASE for hundreds/thousands of keys), and simply multiple concatenated updates. Would either of these be faster than my current method?

It baffles me that, as far as I can tell, there's no idiomatic, efficient way to do this in MySQL. If there really isn't a way that's faster than ON DUPLICATE KEY, would it be worth it to switch to PostgreSQL and use its UPDATE FROM syntax?

Any other suggestions are also greatly appreciated!

Edit: here's one of the tables that gets updated frequently. I've removed column names due to them being irrelevant.

CREATE TABLE IF NOT EXISTS `table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a` bigint(20) unsigned NOT NULL DEFAULT '0',
  `b` bigint(20) unsigned NOT NULL DEFAULT '0',
  `c` enum('0','1','2') NOT NULL DEFAULT '0',
  `d` char(32) NOT NULL,
  -- trimmed --
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`),
  KEY `c` (`c`),
  KEY `d` (`d`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


texts: How to reorganize MS SQL Server database

texts:
I'm looking for a way to reorganize a MS SQL Server database. By re-organize I mean that both tables and indexes are left with no fragmentation.
The resulting database will only be used in read-only mode. One of the aims of the exercise is to release space in the database and the other is to try to make it go as fast as possible. (assuming here that a contiguous layout of both tables and indexes actually has a positive impact on performance, I understand there's some debate about this in modern SSD world  ... but at the very least it doesn't have a negative impact on performance). All in all the result will be a frozen copy of its source. The clone-and-compact operation must take place periodically and must be able to run unattended.
The size of actual data (tables and indexes) is approx 500 GB but there's a lot of unused space so the on-disk size is almost 2 TB. I don't care if the clone is inaccessible during the reorganization operation, nor do I care if it takes days to execute the reorg. But it must be automated (meaning it must run unattended, please don't point me to MS SSMS)
I come from PostgreSQL background. In PostgreSQL I would use pg_dump/ pg_restore to export/import the whole database and such operation would by its very nature re-write tables and indexes to be contiguous. In Oracle world a so-called exp / imp would achieve the same if I remember correctly. Isn't there are similar export/import tool in MS SQL world ?
I've looked at:

BACKUP / RESTORE.  Not applicable. This method retains table and index fragmentation.
DBCC SHRINKFILE / DBCC SHRINKDATABASE. These methods release unused space at the tail. Not quite what I'm after. They are also insanely slow as they are meant to execute while the database is operational. ... I appreciate the reorg-while-online feature but it is not what I need. (I haven't been able to find any docs if such operations can be made go faster if I put the db into SINGLE_USER mode or whatever)
bcp. Can only export table data, not a full database with all its tables, constraints, indexes, users, triggers, stored procedures, etc.

I'm looking for a Microsoft way of doing this. Buying a third-party tool just to do this type of export/import doesn't seem fair to me. SQL Server is expensive enough as it is.
Perhaps I'm just hung up on how this is done in other database engines and therefore looking for information in the wrong place?


texts:
Is it possible to sample any SQL database?

By sampling I means the database table should remain and retaining the foreign key constraints.
But the actual rows from the table should be reduced.If I have 1 million records in some of my tables then in the sample database I only want any 100 records.
I am doing some analysis and processing all records takes many days.


texts: How can I safeguard against SQL injection where parameterizing my query is not an option?

texts:
I'm working with ESRI's arcpy module, and I need to specify a WHERE clause as a parameter to this function. Since the result will eventually be used to retrieve data from the database, the text of the WHERE clause is passed directly to the database at some point, but sadly, ESRI does not give me any options for parametrizing the query. Since I want to safeguard against potential SQL injection, I need to find some alternative means of protecting my database.

One option that comes to mind is limiting the input and throwing an error before this function call if it doesn't conform. In my case, callers will only ever need ASCII alphanumeric characters. No other kind of characters are allowed in the column to be used for filtering. Would limiting the user to only alphanumeric characters be sufficient to prevent SQL injection, especially since the text must be quoted as a string?


texts: How to avoid deadlock while updating 2 rows in MySQL

texts:
This is a interview problem:

There is a perl program that updates the database, and it could run in different processes.
One process may execute a transaction like:
update row A -> update row B -> commit

The other process may execute a transaction like:
update row B -> update row A -> commit

The rows need to be updated is selected in the program before the transaction.

I was asked how to avoid deadlocks without changing the transaction logic (I cannot commit after updating A and commit again after updating B).
They want me to propose at least 3 different methods. What I know is to use select .. for update when selecting row A and B. Can anyone help to suggest some other methods?


texts: How to repair Microsoft.SqlServer.Types assembly

texts:
When I run a checkdb('mydb') this is the only error message printed.

Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3857, State 1: The attribute (clr_name=NULL) is required but is missing for row (assembly_id=1) in sys.assemblies.

It is referring to 'Microsoft.SqlServer.Types' I do see that in the this db the clr_name is blank. but under the master db there is a value in there.

I tried to drop or alter the assembly to add this value but its restricted.

btw, this db was updated lately from sql-server 2005 to 2008R2.


texts: Postgresql: compare record against sibling, but limit search only to active records

texts:
I'm trying to find a way to compare records against it's sibling record while limiting the comparison only to the most recent entry for each set.

I know it does not make to much sense so I'll go with an example.

Table 1: A list of category price-sheet info.

WITH category_price_sheets(category_id, reg_price_sheet_num, bulk_price_sheet_num) AS (
      VALUES
      (1, 1600, 1601),
      (2, 1602, 1603)
),

Table 2: Item cost entries for both regular cost and bulk cost.

prices(id, price_sheet_num, item_id, cost, created_at) AS (
  VALUES
  (1, 1600, 88, 2.00, '2016-05-06 00:00:00'::timestamp),
  (2, 1601, 88, 2.10, '2016-05-06 00:00:00'),
  (3, 1600, 88, 2.05, '2016-05-07 00:00:00'),
  (4, 1600, 88, 2.00, '2016-05-08 00:00:00'),
  (5, 1601, 88, 1.88, '2016-05-08 00:00:00'),
  (6, 1600, 88, 2.05, '2016-05-09 00:00:00'),

  (7, 1602, 88, 2.00, '2016-05-06 00:00:00'),
  (8, 1603, 88, 2.10, '2016-05-06 00:00:00'),
  (9, 1602, 88, 2.05, '2016-05-07 00:00:00'),
  (10, 1602, 88, 2.00, '2016-05-08 00:00:00'),
  (11, 1603, 88, 2.07, '2016-05-08 00:00:00'),
  (12, 1602, 88, 2.05, '2016-05-09 00:00:00')
),

How can I identify bulk pricing that is more expensive than it's regular counterpart, but only in case they're both the latest entry for same item.

So in the example above the bulk cost for item_id 88 in price-sheet 1601 is indeed a better price than it's counterpart in price-sheet 1600 but the same is not true when comparing 1603 against 1602.

I tried the following on a small sample set, and the results were correct. But when I tried it on the whole table, I pulled the plug after two hours with no results. (I'm dealing with more than 50,000,000 records)

bulk AS (
  SELECT v.* FROM (SELECT price.*, sheet.reg_price_sheet_num, sheet.bulk_price_sheet_num,
      lead(price.id) OVER (PARTITION BY price_sheet_num, item_id) IS NULL AS latesetrecord
    FROM prices price
    JOIN category_price_sheets sheet ON sheet.bulk_price_sheet_num = price.price_sheet_num
  ) AS v
WHERE v.latesetrecord
)

SELECT v.* FROM (SELECT price.*, sheet.reg_price_sheet_num, sheet.bulk_price_sheet_num,
      lead(price.id) OVER (PARTITION BY price_sheet_num, item_id) IS NULL AS latesetrecord
    FROM prices price
    JOIN category_price_sheets sheet ON sheet.reg_price_sheet_num = price.price_sheet_num
      WHERE price.cost < (
        SELECT cost FROM bulk blk WHERE blk.price_sheet_num = sheet.bulk_price_sheet_num
          AND blk.item_id = price.item_id
      )
  ) AS v
WHERE v.latesetrecord

I think this can be done with some WINDOW functions but I couldn't get it to go.

Thank you all!


texts: Table name with special character in the end

texts:
I have a table in database(oracle sql) which somehow contains one special character at the end which I dont know.I am trying to get that special character from its entry in table tab.

SQL> SELECT tname from tab where tname='OPERATION';

no rows selected

SQL> SELECT tname from tab where tname like 'OPERATION%';

TNAME
--------------------------------------------------------------------------------
OPERATION

SQL> SELECT length('OPERATION') from tab where tname like 'OPERATION%';

LENGTH('OPERATION')
------------------------
                       9

SQL> SELECT length(tname) from tab where tname like 'OPERATION%';

LENGTH(TNAME)
-------------
           10

So , there is one special character after char N in string OPERATION .
I tried getting the last character using substr but that did not help.

How can I find this special charater ( or even ascii value of this character ) ?

And how can I run select on the data in this table ?


texts: This instance of (MS) SQL Server has been using a process id of X since dd/mm/yyyy

texts:
I was looking through the SQL Server logs and come across the following informational message.

  This instance of SQL Server has been using a process ID of 1960 since 12/10/2014 20:02:48 (UTC). This is an information message only: no user action is required.

This message has been generated for the last three days. I cannot see the process-id using the activity monitor.

Is this something that may cause/highlight a problem that I need to look into?


texts: Materialized view for a yet-to-be-defined DBlink

texts:
In our product we have a main application with an oracle 11g DB and an optional module with another DB.

We want the two to communicate through a dblink and I was told to put some code for the communication stored in the main application. In practice, since the optional module may not actually exists at the time the main application is deployed, this means writing code for tables that don't exist yet, but of which we know the structure, the ones in the optional module.

We can encapsulate the dblink inside materialized views in the main module and restrain from calling remote procedures from there, however, to my understanding, we can't actually define materialized views on non-existant dblinks.

Perhaps we could define dummy materialized views, e.g. empty ones defined on dual, that get properly redefined once the optional module is installed and the necessary dblink is created, with a procedure calling dynamic plsql.

What do you think about it? How would achieve that?

TIA

Andrea

EDIT: currently I have to evaluate the opportunity of having the communication code in the main application without having the optional module already deployed. So I'm really interested in hearing how I can get to create materialized views over yet-to-be-defined dblinks and whether my proposed solution is reasonable.

Thanks.


texts:
Our client has agreed to an RPO of one day data loss. So I am going to change back up strategy to eliminate space constraints.

Database is in full recovery mode.

Existing backup plan:-

Once daily we are taking a full back up of the database.

Thinking to change like below:-

Every week, on Sunday or Saturday we are going to take a full back up of the database.
And planning to take transaction backup or differential backup once every day on the database.

This will avoid space constraint for me.

If we take transaction log back up then will the RTO be larger, since we are taking daily only one transaction backup?

Anyways we need to perform transaction log backup to make free space in log files.

So can you people suggest which back up type will be good, either differential or transaction log?

If I am going in the wrong direction please suggest a backup strategy based on my RPO.

Thanks.


texts: Row by row subtraction with single input number

texts:
I want to subrtact dynamically from previous row subtraction result and as input i want to give a single number.

I have table a

CREATE TABLE a (id int, code text, qty numeric);

And data in it

INSERT INTO a (id,code,qty)
(1,'test',5),
(2,'test',3),
(3,'test',10);

1.sample - When input is 16 - Desired result would be:

id | qty
--------------
1  | 0
2  | 0
3  | 2

Calculation would be something like this

case when 16 > 5 then 16-5 else 5-16 end /* result 11, but displayed 0 */
case when 11 > 3 then 11-3 else 3-11 end /* result 8, but displayed 0 */
case when 8 > 10 then 8-10 else 10-8 end /* result 2 and displayed 2 */

2.sample - When input is 6 - Desired result would be:

id | qty
--------------
1  | 0
2  | 2
3  | 10 /* is optional to display */

Calculation would be something like this

case when 6 > 5 then 6-5 else 5-6 end /* result 1, but displayed 0 */
case when 1 > 3 then 1-3 else 3-1 end /* result 2 and displayed 2 */
case when ... end /* result 10, since input is fulfilled */


texts: Pre- and Post- Snapshot Replication Scripts Not Executing and No Errors

texts:
I have a Snapshot Replication Publication setup on Server 1.
Server 2 subscribes to the publication on Server 1.
Data is replicating successfully.
The Distributor Agent and Agent job for the Subscriber of Server 2 runs on Server 1 (where the Distributor database lives).
I created a stored procedure on Server 2 to drop (pre replication) and create (post replication) schemabound entities on Server 2's subscriber database. (I've done this successfully in the past because Snapshot Replication can stall if there's entities in the subscriber database that are schema bound to the replicated entities.)
I also created a script that executes this stored procedure from Server 1 via a linked server to Server 2. (I've verified the linked server is setup correctly and I can query data from and manually execute the stored procedure via the linked server from Server 1 to Server 2.)
I've added this script to the Snapshot Publication's "Snapshot" Properties:

Initially I received an error in the Snapshot Agent job because my Snapshot Agent didn't have file share access to the path in the scripts, but I added that account and then the error went away and the Snapshot job finishes successfully now.
But the entities in the SQL script don't get dropped or created, and I don't see an errors anywhere. I've also tried moving the before and after script file to a folder share local to Server 1 and referencing it with a UNC path (as opposed to the network path in the screenshot) and still no change in behavior.
I've updated the TEST.sql script with a really simple test with the following code:
INSERT INTO DatabaseA.dbo.Table1 (Field1) -- DatabaseA is on Server1, so it should insert to the local table
SELECT 'Test' AS Field1

When I re-run the Snapshot Agent, it still runs successfully, but the table doesn't get inserted into (on Server 1).
Why could my pre- and post- scripts not be running but I'm not receiving any errors either?
(I also have another Publication for Transactional Replication on Server 1 that is syncing data just fine to Server 2 in the same database as the Snapshot Replication. But even when I set the pre- and post- scripts on the "Snapshot" Properties of the Transactional Publication on Server 1, I still get no errors and the SQL doesn't execute.)
Edit: I also just tried moving the script file to a local folder of Server 1, giving the Snapshot Agent read access to that folder and script file, and re-running the Snapshot Agent, and still nothing. No execution of the script, and no errors.
BUT interestingly, if I manually modify the Subscription Agent job itself and add a step to execute the same exact SQL query, it DOES work.
I also noticed the SQL Agent job runs under a different SQL account "sqlmin" then I expected for certain steps (new steps I created, or the first step of the job called "Snapshot Agent startup message" - I guess because these steps aren't of type "REPL-Distributor"). But the other pre-existing steps execute as the SQL Agent I designated, which is "DBReplication". The "sqlmin" account I know has very minimal SQL permissions on both Server 1 and Server 2, could this be why the pre- and post- replication scripts aren't executing (but I'm also not receiving an error anywhere) when I set the scripts on the pre- and post- fields of the Snapshot Publication - Snapshot properties?
Also additional details about the distributor account (who is also my Snapshot Agent account):
The distributor is a Windows AD account that has a SQL LOGIN on both Server 1 and Server 2 and with the SQL role of db_owner in both the source and target replicated databases as well as db_owner on the distribution database (which lives on Server 1).


texts:
EDIT: docker image for testing with the same data (obfuscated)

$ docker run --rm --name pg -d homer5439/pgagg
$ docker exec -ti pg bash
# createdb -U postgres test; zcat /tmp/corr.sql.gz | psql -U postgres test
# psql -U postgres test

and you can run the examples below.

I was wondering why the following query takes more than 30 seconds on postgresql 12.2:

SELECT
    contract_id,
    array_agg(corr) AS corr
  FROM
    corr
  GROUP BY contract_id;

More info: the corr table has about 150000 records. Each record has the following structure:

gse=# \d corr ;
                                         Table "public.corr"
    Column   |  Type   | Collation | Nullable |                             Default
-------------+---------+-----------+----------+-----------------------------------------------------------------
 corr_id     | integer |           | not null | nextval('corr_corr_id_seq'::regclass)
 contract_id | integer |           |          |
 start_date  | date    |           |          |
 corr_type   | text    |           |          |
 descr       | text    |           |          |
 credit      | numeric |           |          |
 detail      | text    |           |          |
Indexes:
    "corr_pkey" PRIMARY KEY, btree (corr_id)
    "corr_contract_id_idx" btree (contract_id)

The detail field contains text up to 2/3 MB in length (about 10% of the records have detail that long; the others have some (~10-20) kilobytes).
The number of distinct contract_id values is currently 2317.

Following other suggestions found on this site, I've tried changing the value of work_mem up to 10GB without noticeable changes.

Here's the query plan:

                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate  (cost=9883.29..9911.57 rows=2263 width=36) (actual time=1184.971..1357.309 rows=2317 loops=1)
   Output: contract_id, array_agg(corr.*)
   Group Key: corr.contract_id
   Buffers: shared hit=78012 read=49899
   ->  Seq Scan on public.corr (cost=0.00..9320.19 rows=112619 width=571 (actual time=0.057..959.359 rows=112619 loops=1)
         Output: contract_id, corr.*
         Buffers: shared hit=78012 read=49899
Planning Time: 0.131 ms
Execution Time: 1357.747 ms