texts: Authorization dbo maps to a user with an old machine name

texts:
I am trying to run a command that looks like this:

Create ASSEMBLY [AssemblyName]
AUTHORIZATION [dbo]
From ...

When this runs, it returns an error message: Could not obtain information about Windows NT group/user 'oldMachineName\username', error code 0x534.

In this case oldMachineName is the old machine name for my computer. I changed the computer's machine name to newMachineName before every installing Sql Server, but there are obviously some vestiges of it hanging around.

When I log into Sql Server using SSMS, it correctly lists the logged-in user as newMachineName\username. Yet when running this command with Authorization dbo it is trying to use oldMachineName.

So, how can I convince Sql Server that oldMachineName doesn't exist anymore and teach it the correct group/user of the current dbo (ie: my logged in user)?


texts: Is it possible to convince MySQL to use an index with USE INDEX FOR JOIN, even after no longer joining?

texts:
It's been a long day, but I've been wresting with a query which wont use an index, and thus takes much longer than it should.  I stumbled upon USE INDEX FOR JOIN, but it didn't improve anything.

Then I reverted it back to the original query.

Now it's decided to use that index, instead of the previous full table scan and it's down from 17s to 1s.

Did I "convince" the query planner to use the index with USE INDEX FOR JOIN?


texts:
I have a user "sa" which is sysadmin in a MSSQL instance.
All users who log into the database have the permission to change the password and delete the "sa" user.
Is there a way to revoke this permission?


texts:
I want to create a new session in Extended Events to track Queries that use lots of CPU_Time, but whats the Unit of CPU_TIME? ms? Microseconds?...

Duration seems to be Microseconds but I don't really know CPU_TIME and can't find any information regarding this...

Thanks,
Jimm


texts:
I'm trying to trigger an update on a specific user in my user table.
Basically is someone tries to change that user then I want to rollback whatever query was executed and disable the user(using a SP), but if if is not that user, than proceed with the update commit.

This is the code I created:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Users_Trigger_UPDATE]
ON [dbo].[Users]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @user_id as nvarchar(30);
DECLARE @action as nvarchar(30) = 'UPDATE';
DECLARE @source as nvarchar(30) = (SELECT client_net_address FROM
sys.dm_exec_connections WHERE session_id = @@SPID);
    SELECT
      @user_id = inserted.UserID
    FROM
      inserted
   IF @user_id = 'Admin'
    BEGIN
        ROLLBACK TRAN;
        EXEC [dbo].[DisableUser] @user_id, @action, @source;
        PRINT 'Admin User Triggered';
    END
ELSE
    BEGIN
        PRINT 'Other User';
        COMMIT;
    END
END

But I'm receiving the error "The transaction ended in the trigger. The batch has been aborted."

I tried to use the TRY...CATCH but the error is the same. I didn't create a nested transaction so I'm assuming that when I use the ROLLBACK or COMMIT that will apply to the original Query that triggered the UPDATE.

I tried an INSTEAD OF trigger, but the result is the same.

The result of the trigger is correct, it's doing what I want on the DB side, the problem is that there is software using this table that probably has a Try...Catch and as the trigger returns an error, even if the result is correct, the software gives a Login error because the trigger at the end has an error.


texts: Tune Performance by putting large tables into the KEEP Pool

texts:
We currently have a database with 20GB SGA space. SQL has been optimized but has to statisfy its use cases as well and some queries are very, very slow, specifically when large result sets are returned as the results of a text index search, which are then fetched by db file sequential read. This leads to timeouts in the application and our users complain about them. Our storage is slow and we cannot change that, unfortunatelly (slow as in 15 to 20ms peak for an IO request). The idea is now to put these tables (8GB in total) into the KEEP Pool and providing the database with 16GB additional RAM. Further, we will turn on huge pages on the system.

Do you think this is a good way to tackle the problem? Are there any unforseen pitfalls that we should be aware of? Is it easy to put the tables into the KEEP Pool? Will they stay in the KEEP Pool then or is it possible that Oracle just ignores the storage statement?

Database Version is 11.2.0.3.0


texts: How do I prove this relation is in the `3FN`?

texts:
Consider the relation R(A,B,C,D,E) and the set of functional dependencies F={A->D,D->C,BC->AE}
I need to prove this relation is in the 3FN, however I don't think it is; in fact, I don't even think it is in the 2FN.
My reasoning is the following:
I think my candidate key is AB, as AB->A,B, through the reflective property. Because it determines A, it also determines D and C, by transitivity. By pseudo transitivity AB->AE, so AB also determines E; I can't find any subset of AB that also determines every other non key attribute, so I think AB is a candidate key.
Because AB is the candidate key, the relation is in the 3FN if it's in the 2FN and to be in the 2FN, for every non key attribute X, AB->X is elementary, i.e., there can't be a subset of AB that also determines X; this condition already fails in AB->D, as A->D.
Even if it was in the 2FN, it can't also be in the 3FN, as there can't be any non key attribute that depends on another non key attribute, but there cleary are, e.g., D->C.
Am I doing anything wrong, as I can't see how this relation is in the 3FN, but I have to prove it is so.


texts: Insert Multiple Column based on existing table aggregated

texts:
I currently have a SQL query that will go through a table, get the distinct dates and then insert the results into another table.
INSERT [fd_movements_dates](Date)
SELECT DISTINCT(CONVERT(date, MsgTimestamp)) as Date
FROM [fd_movements]
ORDER BY Date

I'm wanting to expand it so that it sums the amount of records within each day. The MsgTimestamp column is a datetime2(0).
Expected result
01-01-2020 - 345600
02-01-2020 - 654000
Thank you.


texts: mysql load data infile takes too long and db file grows too large

texts:
I am trying to import pretty large file (3.5G with 39mlm rows) with

set foreign_key_checks=0;
set sql_log_bin=0;
set unique_checks=0;
load data infile '/var/lib/mysql-files/2.csv' ignore
into table main_data fields terminated by ','
optionally enclosed by '"'
lines terminated by '\n'
ignore 1 rows (col, col2, col3);

It already passed like 24 hours, and database file in /var/lib/mysql/MyDB grow to 11G, I am not sure why, because the file I import from is less than 4G.
It is mysql 8.0. The only things I added to config is

innodb_buffer_pool_size = 22G
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
innodb_autoinc_lock_mode=2

I think if I batch insert these data it would be faster...

As suggester in 1st comment here is creat table syntax.
I now think that so many indexes can be the issue with size and time :

CREATE TABLE `main_data` (
  `file_name` tinyint(4) NOT NULL,
  `line_number` int(11) NOT NULL,
  `link` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `doc_type` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_keywords` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_description` text COLLATE utf8mb4_unicode_ci,
  `language` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `done` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`file_name`,`line_number`),
  UNIQUE KEY `link_UNIQUE` (`link`),
  KEY `done_idx` (`done`),
  KEY `lang_idx` (`language`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I only import 1st three columns (file_name, line_number, link), rest are nulls.


texts:
I am trying to benchmark a new virtual SQL server.  this will be the first time I have virtualized a production SQL server.    I don't think this is a supper busy server.   100-200 users, of varying activity.

I have been capturing statistics from the current physical server for quite a while.

I am running SQLIO on the new server.  The full scope, read/write random/sequential,  2-64 Threads 1-128 outstanding requests.  I captured 5 minute runs with 1 minute pauses in between against a 20GB dat file.
...none of this is really relevant to my question.

I am able to capture the physical disk counters and I can see the latency (avg disk sec/read or write) statistics, that match. I am also checking out the (avg disk byte/read or write) to see how much activity SQLIO produces.  It produces way more disk activity than our production server.

My question is, are there counters I can also grab, to verify the thread and outstanding request options in SQLIO?  I have not been able to find a clear answer.  What I am wanting to do, is look at the statistics from our production server, and gauge what SQLIO settings are most representative of our actual server.

SQLIO is really stress testing the storage system,  and I would like to be able to set it in a way, so that it is a real representation.

I am not sure how to identify the real (avg) thread or (avg) outstanding requests of the current physical server.

Thank you for your time,


texts: Use index intersection feature in MongoDB 2.6 as compound shard key

texts:
I have three indexes on name, age, salary.

I can use them as compound index on MongoDB 2.6 (name and age together), but can I use the same indexes as compound shard key?

I also have a constraint that this will be a unique shard key.

Will it work?


texts: Issue using COPY command with AWS Redshift

texts:
I am trying to copy a CSV file from my PC, which has SSH running, to AWS Redshift.  I have copied the Redshfit public key to authorized_keys for the appropriate user.

copy contact (id,name)
from 's3://mybucket/m1.mfs'
credentials 'aws_access_key_id=xxx;aws_secret_access_key=yyy''
region 'us-west-2'
csv ssh

The manifest file, which has been uploaded to S3, is as follows:

{
"entries": [
{"endpoint":"73.29.89.30",
"command": "/bin/cat /home/user23/rfr/src/redshift/Contact2.csv",
"mandatory":true,
"username": “user23"}
]
}

The error I get is:

  Error: ERROR: Manifest file is not in correct json format**

From what I can see, the file is in the correct format. I think the issue may be something else, but how do I debug this? The endpoint is valid, the command is valid, and the username is valid.


texts: Query with Variable not estimating right

texts:
I have a query with several joins that is not performing well. After a lot of triage, I decided to take the query apart and look at just the driving table. Below is an example of the query based on just the driving table.

DECLARE @VAR AS INT

SET @VAR = 11652862

SELECT Field1, Field2
FROM tablea
WHERE Field2 = @VAR

When I run the code as it is the actual execution plan comes back with 15k estimated rows and 1m actual rows. Yes, this is just a simple SELECT...FROM TABLE, but remember this is just part of a bigger query, so this estimate difference greatly compounds as we add more joins.

I know the problem is the @VAR because the optimizer does not know the value of the variable when it compiles the query into a plan so its coming back with the avg # of rows for the estimate for the field. If I add OPTION (RECOMPILE) it will force in the value when it compiles and come back with the correct # of rows.

Here is the kicker: This is not custom code that my company can edit. It is part of a third party app that I cannot edit. Updating stats will not help (I looked at the histogram and it is correctly listing the value for my variable). I already have a covering NC index on those two fields. The only option I can think of is to use a forced execution plan on this query. I have never been able to successfully do that, though. Anyone have any other ideas or a good How-To reference on forced execution plans?

Since it was asked in the comments, the create statement for the index is:

CREATE NONCLUSTERED INDEX idx ON tablea (Field2)


texts: Query last time a view, function or sp was last used/read

texts:
I use sys.dm_db_index_usage_stats.last_user_seek, sys.dm_db_index_usage_stats.last_user_scan and sys.dm_db_index_usage_stats.last_user_lookup to query last time a table was read.

For views, functions and stored procedures I'm using sys.objects but it doesn't have any field for read datetime.

Is there any system table where I can see the last time these types of objects were read/used?

My objective is to find objects that aren't being used and possibly drop them.


texts:
I have a field in my collection which is a String in nature.
When i run aggregation for this field in data it takes considerable time in aggregation.
Total 6 million records are in collection which has almost 500k different value for this field.

I am in impression if a field is numeric with similar scenario then aggregation on that field will be very fast.

So i planned to save the string as unique integer value by converting it java biginteger but mongo does not support that.
Could you please suggest me what could be the best possible way for this.

This is the data we want to aggregate

{
    "_id" : ObjectId("54d7ac5c3848e23f6f471b9f"),
    "articleId" : [
            {
                    "articleId" : "17084694",
                    "articleCount" : 1
            }
    ],
    "host" : "abc.com",
    "count" : NumberLong(1),
    "cat" : "test test",
    "catIds" : [
            "-2128958273",
            "2886704",
            "5912641"
    ],
    "timeStamp" : NumberLong("1423420500000"),
    "interval" : 1,
    "tags" : "magnetic fields"
}

{
    "_id" : ObjectId("54d7ac5c3848e23f6f471b9f"),
    "articleId" : [
            {
                    "articleId" : "17084694",
                    "articleCount" : 1
            }
             {
                    "articleId" : "17084695",
                    "articleCount" : 1
            }
    ],
    "host" : "abc.com",
    "count" : NumberLong(1),
    "cat" : "test test",
    "catIds" : [
            "-2128958273",
            "2886704",
            "5912641"
    ],
    "timeStamp" : NumberLong("1423420500000"),
    "interval" : 1,
    "tags" : "magnetic fields"
}

{
    "_id" : ObjectId("54d7ac5c3848e23f6f471b9f"),
    "articleId" : [
            {
                    "articleId" : "17084695",
                    "articleCount" : 1
            }
             {
                    "articleId" : "17084696",
                    "articleCount" : 1
            }
    ],
    "host" : "abc.com",
    "count" : NumberLong(1),
    "cat" : "test test",
    "catIds" : [
            "-2128958273",
            "2886704",
            "5912641"
    ],
    "timeStamp" : NumberLong("1423420500000"),
    "interval" : 1,
    "tags" : "taps records"
}

Result will be

{
    "_id" : ObjectId("54d7ac5c3848e23f6f471b9f"),
    "articleId" : [
            {
                    "articleId" : "17084694",
                    "articleCount" : 2
            }
             {
                    "articleId" : "17084695",
                    "articleCount" : 1
            }
    ],
    "host" : "abc.com",
    "count" : NumberLong(2),
    "cat" : "test test",
    "catIds" : [
            "-2128958273",
            "2886704",
            "5912641"
    ],
    "timeStamp" : NumberLong("1423420500000"),
    "interval" : 1,
    "tags" : "magnetic fields"
}

{
    "_id" : ObjectId("54d7ac5c3848e23f6f471b9f"),
    "articleId" : [
            {
                    "articleId" : "17084695",
                    "articleCount" : 1
            }
             {
                    "articleId" : "17084696",
                    "articleCount" : 1
            }
    ],
    "host" : "abc.com",
    "count" : NumberLong(1),
    "cat" : "test test",
    "catIds" : [
            "-2128958273",
            "2886704",
            "5912641"
    ],
    "timeStamp" : NumberLong("1423420500000"),
    "interval" : 1,
    "tags" : "taps records"

}
As you see the aggregation is on tags field where two things are added one is count second artcileCount for same tags.
IT takes a lot of time.
Hash i was referring to keep tag string as some integer form and aggregate that


texts: CPU Usage Incorrect in sys.dm_os_ring_buffers

texts:
I'm running a cloud VPS with a SQL Server instance. Because it's for personal use, I'm using express edition (I can't use developer edition because I technically have production apps running on it and there's no way I could afford Standard+).

I'm trying to get baseline captures going using Brent Ozar's tutorial using sp_BlitzFirst. The problem I'm running into is that ProcessUtilization in sys.dm_os_ring_buffers always comes out as 100, no matter what the actual CPU usage is at the time.

VM Info

  @@version: Microsoft SQL Server 2017 (RTM-CU15) (KB4498951) - 14.0.3162.1 (X64)   May 15 2019 19:14:30    Copyright (C) 2017 Microsoft Corporation    Express Edition (64-bit) on Linux (Ubuntu
  18.04.2 LTS)

Host: 1 & 1 Ionos VPS

lscpu output

Architecture:        x86_64
CPU op-mode(s):      32-bit, 64-bit
Byte Order:          Little Endian
CPU(s):              2
On-line CPU(s) list: 0,1
Thread(s) per core:  1
Core(s) per socket:  1
Socket(s):           2
NUMA node(s):        1
Vendor ID:           GenuineIntel
CPU family:          6
Model:               63
Model name:          Intel(R) Xeon(R) Gold 5120 CPU @ 2.20GHz
Stepping:            0
CPU MHz:             2194.844
BogoMIPS:            4389.68
Hypervisor vendor:   VMware
Virtualization type: full
L1d cache:           32K
L1i cache:           32K
L2 cache:            1024K
L3 cache:            19712K
NUMA node0 CPU(s):   0,1
Flags:               fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts mmx fxsr sse sse2 ss syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon pebs bts nopl xtopology tsc_reliable nonstop_tsc cpuid pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm cpuid_fault invpcid_single pti ssbd ibrs ibpb stibp fsgsbase tsc_adjust bmi1 avx2 smep bmi2 invpcid xsaveopt arat flush_l1d arch_capabilities


texts: Selecting dates that are between the previous 12 to 9 month range

texts:
I need help selecting dates that are between the previous 12 to 9 month range.  As an example if today is 2-22-2017 I need to pull a date range from 2-1-2016 to 3-31-2016.

I can select all dates from the previous 12 months using this:

WHERE READDATE >= DATEADD(month, DATEDIFF(month, 0, DATEADD(MONTH,-12,GETDATE())), 0)
AND READDATE <= DATEADD(s,-12,DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE()),0))

But I can't figure out how to exclude dates after the previous 9 months giving me that 3 months window.


texts: Un-Install OS Patch (KBs) in Physical PRODUCTION Servers - Consequences

texts:
We have our PRODUCTION Database and Application Servers which are Physical Servers .

Recently , the October month OS Patches (specific KBs) are little problematic and reported by Microsoft too that they are going to fix in November patches .

https://blogs.msdn.microsoft.com/dataaccesstechnologies/2017/10/18/unexpected-error-from-external-database-driver-1-microsoft-jet-database-engine-after-applying-october-security-updates/

The document says :

Uninstall the security patch (Not recommended):
Registry change (Not recommended): [Request you to take a backup of the registry key before making any changes]
Please guide me on :

Do I need to expect any issue while Uninstalling patches (specific KBs).

Since my servers being PRODUCTION and Physical in nature , what are the consequences I might face while rollback of patches .

In this case is it recommended to Rollback patches ?

I am preparing one PowerShell code for this process .
Kindly help .


texts:
i have a problem when i try to export ...

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SEMESU
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SEMESU
About to export SEMESU's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 12704 encountered
ORA-12704: character set mismatch
EXP-00000: Export terminated unsuccessfully

anyone? (i'll apreciatte)


texts: How to Remove Multiple Databases Marked with "Recovery Pending"

texts:
Recently I tried to clean my appilcations which used SQL database(s), therefore, the leftover databases are now marked with "Recovery Pending".

Right now I can follow this to delete them manually.

How do I remove all those DBs marked with "Recovery Pending" in only one action?


texts:
Why is it that when we have a NULL value in a column and we order by the value ascending, the NULLs are sorted first?

select 1 as test
union all
select 2
union all
select NULL
union all
select 3
union all
select 4
order by test

results in

NULL
1
2
3
4

I keep thinking that NULL meant "Indeterminant" or possible "Unknown".  If that's true, wouldn't they sort last, since the value could be greater than all other values?  (Or is this a sorting option somewhere?)

I'm on SQL Server 2008R2, but I suspect this is true across all SQL Servers, and probably across all RDBMSs.


texts: What is best practice for SQL Server 2014 regarding number of instances per node? I know SQL Server 2014 added CSVs that will do away with the drive letter limitation, but what is best practice on the number of instances per node? To my knowledge, SQL Server 2008 R2 best practice was 5 per node.


texts: unable to start mongod - Fatal assertion 28579 UnsupportedFormat

texts:
I was frequently getting Segmentation fault (core dumped). Not knowing what to do, I just restarted my ubuntu. Then tried repair which failed because of Segmentation fault. Now when I am trying to start mongod

root@ubuntu-s-4vcpu-8gb-nyc1-01:~/mongo# mongod --dbpath /var/lib/mongodb
2018-11-12T10:13:15.235+0000 I CONTROL  [initandlisten] MongoDB starting : pid=2091 port=27017 dbpath=/var/lib/mongodb 64-bit host=ubuntu-s-4vcpu-8gb-nyc1-01
2018-11-12T10:13:15.235+0000 I CONTROL  [initandlisten] db version v3.2.21
2018-11-12T10:13:15.235+0000 I CONTROL  [initandlisten] git version: 1ab1010737145ba3761318508ff65ba74dfe8155
2018-11-12T10:13:15.235+0000 I CONTROL  [initandlisten] OpenSSL version: OpenSSL 1.0.2g  1 Mar 2016
2018-11-12T10:13:15.235+0000 I CONTROL  [initandlisten] allocator: tcmalloc
2018-11-12T10:13:15.235+0000 I CONTROL  [initandlisten] modules: none
2018-11-12T10:13:15.235+0000 I CONTROL  [initandlisten] build environment:
2018-11-12T10:13:15.235+0000 I CONTROL  [initandlisten]     distmod: ubuntu1604
2018-11-12T10:13:15.235+0000 I CONTROL  [initandlisten]     distarch: x86_64
2018-11-12T10:13:15.235+0000 I CONTROL  [initandlisten]     target_arch: x86_64
2018-11-12T10:13:15.235+0000 I CONTROL  [initandlisten] options: { storage: { dbPath: "/var/lib/mongodb" } }
2018-11-12T10:13:15.255+0000 I -        [initandlisten] Detected data files in /var/lib/mongodb created by the 'wiredTiger' storage engine, so setting the active storage engine to 'wiredTiger'.
2018-11-12T10:13:15.255+0000 W -        [initandlisten] Detected unclean shutdown - /var/lib/mongodb/mongod.lock is not empty.
2018-11-12T10:13:15.255+0000 W STORAGE  [initandlisten] Recovering data from the last clean checkpoint.
2018-11-12T10:13:15.255+0000 I STORAGE  [initandlisten] wiredtiger_open config: create,cache_size=4G,session_max=20000,eviction=(threads_min=4,threads_max=4),config_base=false,statistics=(fast),log=(enabled=true,archive=true,path=journal,compressor=snappy),file_manager=(close_idle_time=100000),checkpoint=(wait=60,log_size=2GB),statistics_log=(wait=0),verbose=(recovery_progress),
2018-11-12T10:13:15.310+0000 I STORAGE  [initandlisten] dropping unused ident: index-0-8381493109628483945
2018-11-12T10:13:15.312+0000 I CONTROL  [initandlisten] ** WARNING: You are running this process as the root user, which is not recommended.
2018-11-12T10:13:15.312+0000 I CONTROL  [initandlisten]
2018-11-12T10:13:15.312+0000 I CONTROL  [initandlisten]
2018-11-12T10:13:15.312+0000 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/defrag is 'always'.
2018-11-12T10:13:15.312+0000 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2018-11-12T10:13:15.312+0000 I CONTROL  [initandlisten]
2018-11-12T10:13:15.312+0000 I -        [initandlisten] Fatal assertion 28579 UnsupportedFormat: Unable to find metadata for table:index-0--3161084524998267994 Index: {name: _id_, ns: local.startup_log} - version too new for this mongod. See http://dochub.mongodb.org/core/3.4-index-downgrade for detailed instructions on how to handle this error.
2018-11-12T10:13:15.313+0000 I -        [initandlisten]

***aborting after fassert() failure


texts:
I would like to monitor how many queries are utilizing the indexes. Is there any program that can show me live query performance and index utilization?

Note: I am already aware of slow log file and its usage.


texts: mysqlbinlog failing with " ERROR 1032 (HY000) at line 48: Can't find record in `` " Error

texts:
I am facing some issue with PITR restore using mysqlbinlog . It is failing with the following error .

 # mysqlbinlog mysql-bin.000035 | mysql -u root -p db
 Enter password:
 ERROR 1032 (HY000) at line 48: Can't find record in 'dlfilerank'

dlfilerank is used by Liferay CMS App and the total records for this table keeps on changing when Liferay Portal is being accessed.

Following are the latest few binary log files generated by MySQL in my DB Server.

-rw-rw---- 1 mysql mysql 1073742407 Dec 27 18:26 /var/lib/mysql/mysql-bin.000033
-rw-rw---- 1 mysql mysql 1073742182 Feb  2 18:58 /var/lib/mysql/mysql-bin.000034
-rw-rw---- 1 mysql mysql        665 Feb  2 18:58 /var/lib/mysql/mysql-bin.index
-rw-rw---- 1 mysql mysql  422392027 Feb 18 15:50 /var/lib/mysql/mysql-bin.000035

From the above listing , we can see that the latest binary log (mysql-bin.0000350) was created around Feb 2,19:00 and all DB transactions from this timestamp onwards are being written to mysql-bin.000035

Now since latest binary log was created on  Feb 2, , so I am considering full backup taken using mysqldumpon Feb 2. For full backup , we have an automated script using mysqldump which runs every midnight to take the backup.

So , to test restore for this setup , I am doing the  following steps:

Restore from full backup taken using mysqldump on  Feb 2
Next step , I execute mysqlbinlog generated on Feb 2 for PITR restore but it is failing with the error mentioned in original description.

Kindly suggest how to resolve the issue ? Do I need to manually generate a new binary log file before taking full backup .


texts:
I've created a user ... but forgotten the password

mysql> create user 'blayo'@'%' identified by 'right';

Which Linux command line tool can encrypt the password the same way mysql does ?

mysql> select Password,User from mysql.user
------------------------------------------+-------+
*920018161824B14A1067A69626595E68CB8284CB | blayo |

...to be sure I use the right one

$ tool right
*920018161824B14A1067A69626595E68CB8284CB


texts: Create DB2 on networked drive but managing from Windows

texts:
I'm relatively new to DB world and DB2 is very new. Below is what I'm trying to achieve:

Install DB2 express C and IBM data studio on windows (with Fixed IP)
create/share/host DB2 on a shared/networked drive used within an organisation having its own fixed IP. its not a server. its netgear NAS.

I want this database to be shared in my organisation.

I know its relatively easy if I host it on my own windows pc (ie Localhost) but I would want to host on a network drive.

Is it possible? If so, where can I find relevant information?


texts: Sum purchased & sold quantities for all items on daily/weekly

texts:
I have 4 tables Items, DimDate, Purchase and Sales for items definitions, calendar table, purchased and sold Items respectively. Tables structure here

I need to outer join sum(Purchased_Qty) and sum(Sales_Qty) with a date range, and also with the Items table so that the results would display as follows:

Date, Items.Product_Name, Sum(purchased_Qty), Sum(sold_Qty)
group by [Date], Items.Product_Name.

I need to display purchased & sold quantities for all items and for all days of a date range.

THIS is an example of a possible starting point query, i tried several combinations of joins & sub queries on query in this example, but none of them is correct in logic as i tested them.


texts: How to change the expiration date of binlog files?

texts:
I have a server with Ubuntu 18.04 and MySQL 8.0.11

I have binlog files in /var/lib/mysql that make several GO and my server will soon saturate.

How to change the expiration date of binlog files ?

If I go to :

nano /etc/mysql/my.cnf

The file contains :

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

If I go to :

nano /etc/mysql/conf.d/mysql.cnf

The file contains :

[mysql]

If I go to :

nano /etc/mysql/mysql.conf.d/mysqld.cnf

The file contains :

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log

I can not find the file to configure. Is it safe for the database to change the timeout ?


texts: Is Policy-Based Management dead? Is it scalable?

texts:
Policy-Based Management was introduced in SQL Server 2008.

I'm looking for software/tool I can use to enforce configuration standards across a SQL Server 2012 /2014 database environment.  Instances are in the hundreds & globally distributed.

Is PBM the right tool to use? It has great out of box functionality, but i fear it will not scale & will not be future-proof.

Other end of the spectrum is Powershell DSC.  Which is the more 'roll your own' approach. Admittedly there are 'experimental' scripting resource kits.

Are there other tools which provide a good middle ground?


texts:
I'm developing a simple double entry accounting system for managing the payments, charges, cars expenses, etc. for car rental companies. I'm done (Development and Database wise) with the Main operation system for managing cars, customers, contracts and so on and now I'm stuck in the accounting part.I have come up with the below DB design for managing the accounting part but I'm still not 100% sure if I can use it and consider it as a go.

Now in the t_Transaction table I have a Credit Account and Debit Account with 1 Amount field. My question is what is the best approach for managing the amount part?
1- Is it better to be logged into 1 field in the database? but then how can I distinguish weather the amount is a debit or a credit while generating reports.
2- Or should I have 2 separate fields (a. Debit Amount) and (b. Credit Amount) and then based on the transaction 1 field will have the actual amount while the other will have NULL value.

I'm still confused on how to develop the code for logging the transactions into the database as this is my first time development for an accounting application


texts: Had to force loop join and don't understand why

texts:
Hello SQL Server Experts. I have a table (table_A) with about 2 million rows and a table_B with about 10 million rows.

SELECT b.title
FROM table_A A
   JOIN table_B B
       ON A.id = B.A_id
WHERE a.last_activity > @previous_36_hours

B has an index (A_id). A has an index (last_activity [include id]). A.id is not the primary key (so it is unsorted).

The filter on A produces about 5000 rows and B will have about 12000 rows.

My execution plan without the loop hint has it scanning B.A_id without a predicate and sorting A.id to perform a merge join on A.id = B.A_id. This takes about 750ms in parallel. When I give a loop hint, it seeks on B.A_id and the time drops to 50ms non-parallel.

Is this a sign of bad sampling of data for the histogram and a sign that we need to customize the stats-- that the out of the box sampling is not working? I'm a complete novice when it comes to the finer points of this, I'm just an app developer. Obviously it is able to seek on B_id, but it refuses. The row estimates are also way off.

Personally, I can see why it might think this is a reasonable plan. It has a limited rows from A.id to sort on and B_id is already sorted. It thinks it is getting for more rows from B than it needs, so a merge join would seem like the thing to do.


texts: SQL Server -- see when user/ login was last modified? And other meta-data

texts:
I know you can see when tables or views were last modifed in sys.tables or sys.views in SQL Server.

It does not appear there is a table like this for users or logins --- or is there?

Basically, a user is having permissions issues and --- I can see their current permissions --- but I would like to know when it was last "changed" and possibly by "whom" --- is that possible retroactively?


texts: Can you force pgpool to require SSL connections?

texts: Can you force pgpool to drop non-ssl connections? I know you can with postgresql (Force PostgreSQL clients to use SSL?), but dropping that same syntax (hostssl instead of host) results in a hba_pool.conf syntax error.


texts: Mixing sharding and Replica in MongoDB cluster

texts:
We know there are two ways to create a mongoDB cluster :-

1.) Sharding

2.) Replication

My question is can we combine both sharding & replication like we have in elastic cluster?


texts: INSERT MASTER.sys.sysdatabases result into a temporary table

texts:
I have this query that iterate all databases on my SQL Server

DECLARE @DB_Name varchar(100)
DECLARE @Command nvarchar(200)
DECLARE database_cursor CURSOR FOR

SELECT name
FROM MASTER.sys.sysdatabases

where name not in ('master','tempdb','model','msdb')

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0
BEGIN
     SELECT @Command = 'SELECT ' + '''' + @DB_Name + '''' + 'as DB_name, SF.filename, SF.size FROM sys.sysfiles SF'
     EXEC sp_executesql @Command

     FETCH NEXT FROM database_cursor INTO @DB_Name
END

CLOSE database_cursor
DEALLOCATE database_cursor

But how can I store the result all in one temporary table?


texts: MySQL replication: purge_logs giving error

texts:
On a MySQL replication instance I have this error message:

[ERROR] MYSQL_BIN_LOG :: purge_logs Was called Expired with file / db-space-no-connected-cpa / DBMS / dxnc6sla / arch / not listed in the index .

Can you give me an idea about the solution? thank you in advance


texts: How to handle concurrent write access to two related tables

texts:
Suppose there are two tables t1 and t2. t1 has a boolean column ct1.
There are two scenarios:

If ct1 is false, create new entries in t2 and make ct1 true
If ct1 is true, just return

How to handle above scenario in Postgres if there are concurrent queries like above?

The possible race condition is: the first query sees ct1 as false and then create entries in t2, then the second query also see ct1 as false before the first query can make it true.


texts: SQLite CSV import won't create a table automatically

texts:
According to https://www.sqlite.org/cli.html#csv,

sqlite> .mode csv
sqlite> .import C:/work/somedata.csv tab1

  There are two cases to consider: (1) Table "tab1" does not previously
  exist and (2) table "tab1" does already exist.

  In the first case, when the table does not previously exist, the table
  is automatically created and the content of the first row of the input
  CSV file is used to determine the name of all the columns in the
  table. In other words, if the table does not previously exist, the
  first row of the CSV file is interpreted to be column names and the
  actual data starts on the second row of the CSV file.

  For the second case, when the table already exists, every row of the CSV file, including the first row, is assumed to be actual content. If the CSV file contains an initial row of column labels, that row will be read as data and inserted into the table. To avoid this, make sure that table does not previously exist.

I tried this and got an error:

$ cd /tmp
$ rm -f database.sqlite3
$ ( echo 'a,b,c' ; echo '1,2,3' ) > somedata.csv
$ sqlite3 database.sqlite3
SQLite version 3.7.13 2012-07-17 17:46:21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .version
SQLite 3.7.13 2012-07-17 17:46:21 65035912264e3acbced5a3e16793327f0a2f17bb
sqlite> .mode csv
sqlite> .import somedata.csv sometable
Error: no such table: sometable

However, if I create the table first, then it "works", but I end up with an unwanted header row since it is case (2):

sqlite> CREATE TABLE sometable (a, b, c);
sqlite> .import somedata.csv sometable
sqlite> SELECT * FROM sometable;
a,b,c
1,2,3

I've reproduced this problem using /usr/bin/sqlite3 3.7.13 in OS X 10.9.5 (as shown above) and version 3.7.3 in Debian 6.

Am I looking at the wrong documentation for my version of SQLite?  Is there some mode I've neglected to set?  Or is the SQLite documentation totally inaccurate?


texts:
How can I achieve this?

ValidationMessage table:

ValidationMessageID | Text
1                   | Item(s) <ItemList> do not exist in project <ProjectID>.
5                   | This value <Value> is invalid.

ReplacementValues table:

ValidationMessageID | ReplacementValues | Index
1  | <614882,614889>,<8068637>          | 10
1  | <614882>,<8068634>                 | 11
1  | <614883>,<8068639>                 | 12
5  | <John Smith>                       | 22

Expected Result:

ValidationText                                              | Index
Item(s) 614882,614889 do not exist in project <8068637>.    | 10
Item(s) 614882 do not exist in project <8068634>.           | 11
Item(s) 614883 do not exist in project <8068639>.           | 12
This value John Smith is invalid.                           | 22


texts: Index performance for CHAR vs VARCHAR (Postgres)

texts:
In this answer (https://stackoverflow.com/questions/517579/strings-as-primary-keys-in-sql-database) a single remark caught my eye:

  Also keep in mind that there's often a very big difference between a
  CHAR and a VARCHAR when doing index comparisons

Does this apply / still apply for Postgres?

I found pages on Oracle claiming that CHAR is more or less an alias for VARCHAR and so index performance is the same, but I found nothing definitive on Postgres.


texts: Can I upgrade SQL Server 2008 R2 Standard to SQL Server 2014 Developer?

texts:
Is there a migration path to upgrade SQL Server 2008 R2 Standard to SQL Server 2014 Developer?

If so, are there any pain points?

If not, how would you recommend I proceed?

The context of this question is a Developer machine that is running SQL Server 2008 R2 Standard and wants to now run SQL Server 2014 Developer.

Update 07/11/2014 - The accepted answer worked for me. I installed SQL Server 2014 Developer as a named instance then followed Zasz's StackOverflow answer to convert the named instance to the default instance.


texts: How to get mongodump --quiet to still report errors?

texts:
I'd like to run mongodump so that it only outputs errors (to avoid extraneous logwatch reporting for an automated nightly backup script).

I've tried mongodump --quiet but from a quick test this supressess error messages also (running it at root folder without permission suppresses the "error creating directory" message that I get without --quiet).

How do I get only error output from mongodump?

Here's the error I get when running without --quiet but which I do NOT get with --quiet:

  2016-04-17T13:50:26.294-0400  Failed: error dumping metadata: error creating directory for metadata file dump/234-watchdog: mkdir dump: permission denied

MongoDB is running on Ubuntu. I understand why this error is happening. I want errors like this NOT to be suppressed with the --quiet option.


texts: how to get a sorted result from mysql query?

texts:
I am trying to get a sorted result from a MySQL query and I don't have any idea of making the query.

I have a example MySQL table as follows :

 id | cat_type
 1  |  free
 2  |  free
 3  |  free
 4  |  free
 5  |  paid
 6  |  paid
... |  ...
... |  ...
... |  ...
   and it goes on.

The result should be as follows from the above table :

 id | cat_type
 1  | free
 2  | free
 5  | paid

 3  | free
 4  | free
 6  | paid

... | free
... | free
... | paid
  and goes on(should be sorted)

The total result should be split as 3. In the first three result, first two should be free and the next one should be paid likewise all the remaining result should be sorted as two free and one paid.

The most important is that it should be sorted as two free and one paid.

Any idea how to accomplish this using a query?


texts:
I have the following partitioned table

create table T (Month char(6), ID int, .... primary key (ID, Month)) on psMonth(Month)

The partition function is

CREATE PARTITION FUNCTION [pfMonth](char(6)) AS
RANGE RIGHT FOR VALUES (N'200001', .... N'200705', N'200706', N'200707', ....)

However, the actual partition count of following query shows is 260 instead of 1?

select count(*)
from T (nolock)
where Month = '200706'

The execution plan is

  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[globalagg1006],0)))
       |--Stream Aggregate(DEFINE:([globalagg1006]=SUM([partialagg1005])))
            |--Parallelism(Gather Streams)
                 |--Stream Aggregate(DEFINE:([partialagg1005]=Count(*)))
                      |--Clustered Index Scan(OBJECT:([DB].[dbo].[T].[PK_T]), WHERE:([DB].[dbo].[T].[Month]=[@1]))

Update:
The query run fast and the Actual Partition Count is 1 if I run the following query.

select count(*), $partition.pfMonth(Month)
from T with (nolock)
where Month = '200706'
group by $partition.pfMonth(Month)

Update 2:
The following query also only scan one partition.

select count(*), min($partition.pfByMonth(Month))
from T
where Month = '200707'

Update 3:
The original SQL scans only one partition now. No clue what happened. The only difference is that more data has been inserted into the table.
Answer: I used a CTE which wrapped the table. And it worked around the issue caused by type precedence. (However, the CTE didn't work around the issue at first though, the whole thing is strange and unpredictable.)


texts: SQL Server Installer appears to be resetting MSDB Recovery Mode to Simple

texts:
Everytime we upgrade a SQL Server installation (service pack or cumulative update), msdb is changed from FULL recovery mode to SIMPLE recovery mode.

I understand that the msdb database doesn't change much and it is common to have it in SIMPLE, but I'm wondering why the installer is changing it.  It is causing errors in my backup software.

Here is an extract from the installation logs:

2020-02-15 03:06:38.25 spid8s      Starting execution of MSDB.SQL
2020-02-15 03:06:38.25 spid8s      ----------------------------------
2020-02-15 03:06:38.35 spid8s      Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
2020-02-15 03:06:38.35 spid8s      Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
2020-02-15 03:06:38.56 spid8s      Checking the size of MSDB...
2020-02-15 03:06:39.41 spid8s
2020-02-15 03:06:39.41 spid8s      Setting database option TRUSTWORTHY to ON for database 'msdb'.
2020-02-15 03:06:39.41 spid8s      Setting database option RECOVERY to SIMPLE for database 'msdb'.
2020-02-15 03:06:39.46 spid8s      ----------------------------------
2020-02-15 03:06:39.46 spid8s      Finished execution of MSDB.SQL
2020-02-15 03:06:39.46 spid8s      ----------------------------------
2020-02-15 03:06:39.46 spid8s      -----------------------------------------
2020-02-15 03:06:39.46 spid8s      Starting execution of MSDB_VERSIONING.SQL
2020-02-15 03:06:39.46 spid8s      -----------------------------------------
2020-02-15 03:06:39.47 spid8s      -----------------------------------------
2020-02-15 03:06:39.47 spid8s      Finished execution of MSDB_VERSIONING.SQL
2020-02-15 03:06:39.47 spid8s      -----------------------------------------


texts: I have a users table and friends table. The user with ID 1 has 4 friends (with IDs 2,3,4,13). I want to get names and surnames of users 2,3,4 and 13. I can to it with standard queries but it will slow down the process; querying for every friend. I didn't get how and what to use of left, joints, right joints, simple joints, ASs, ONs, ...


texts: Resolving "there is unknown charset/collation names" warnings on views

texts:
I recently moved our MySQL 5.6 server over to MariaDB 10.3.10 and, for the most part, have not had any problems. We have a couple of databases that have close to 1000 views which are now filling the log file with warnings which read:

View 'dm'.'v_conservation_20110906': there is unknown charset/collation names (client: ''; connection: '').
View 'dm'.'v_conservation_20110913': there is unknown charset/collation names (client: ''; connection: '').
View 'dm'.'v_conservation_20110920': there is unknown charset/collation names (client: ''; connection: '').
View 'dm'.'v_conservation_20110927': there is unknown charset/collation names (client: ''; connection: '').

I probably wouldn't be too concerned about it but each time the database is hit the log file grows by over 1200 lines. And that is from just one database.

When I made the switch both mysql_update and mysqlcheck with auto repair were run with no errors related to the views in question. I also removed everything from the ini file for the new installation except those things that were required. The ini file contents are:

[client]
default-character-set=utf8mb4
[mysqld]
port=3306
datadir = "D:/SQL_Data"
character-set-server=utf8mb4
collation_server='utf8mb4_unicode_520_ci'
default-storage-engine=InnoDB
max_allowed_packet = 128M

A lot of searching here and via Google has not provided any concrete suggestions of things to try. I would be grateful for a hint on where to look to even find out what the unknown charset/collation names are. Even better would be a way to fix the hundreds of views that apparently need fixing.


texts: SQL Server "empty table" is slow after deleting all (12 million) records?

texts:
I have a SQL Server 2008 instance with approximately 150 columns. I have previously populated this table with approximately 12 million entries, but have since cleared the table in preparation for a new data set.

However, commands that once ran instantly on an empty table such as count(*) and select top 1000 in SQL Management Studio now take eons to run.

SELECT COUNT(*) FROM TABLE_NAME

took over 11 minutes to return 0, and SELECT TOP 1000 took almost 10 minutes to return an empty table.

I've also noticed that the free space on my hard drive has literally disappeared (down from about 100G to 20G). The only thing that happened between was a single query I ran:

DELETE FROM TABLE_NAME

What in the world is going on?!?


texts: SQL Server Standard High Availability (HA) with two nodes

texts:
Is it possible to accomplish High Availability (HA) with two SQL Server Standard nodes ?

We can't use SQL Server 2012 AlwaysOn without Groups as it has single point of failure by using the "single" database file
We can't use SQL Server 2012 AlwaysOn with Groups because it's placed in Enterprise package and we don't have license for that
We can use any other SQL Server version but in Standard packaging level
We can't use mirroring because it requires a third SQL Server used as witness server

Any other suggestions ? I have tried to find an answer all over the internet and didn't find anything.

Thanks


texts: How to remove blockages in MongoDB when multiple write query comes for same collection at same time

texts:
I want to use MongoDB in a clustered environment with 3 shards and make each shard replicated. MongoDB is a single threaded so when multiple requests hit shard 1 to insert or edit one collection at a same it causes blockages in the architecture.

How can I remove or prevent these blockages?


texts: Cannot drop constraint in SQL server 2008

texts:
When I try to delete a constraint, it gives me the following message:

  "The constraint is used by merge replication for identity management
  and cannot be dropped directly. Execute sp_changemergearticle
  @publication, @article, "identityrangemanagementoption", "none" to
  disable merge identity management, which will also drop the
  constraint."

However, the replication was deleted from the database earlier by previous users. Now I cannot remove or even modify the constraint. Can you please advice how to remove it now?


texts: How to change location of SQL Server 2012 database which is already configured with a merge replication

texts:
I have SQL Server 2012 configured with merge replication. currently the location of the replication database and the .mdf and .ldf files reside in C:\ drive.

The C:\ drive is now full due to that fact that these files reach over 80GB. Therefore, I want to relocate the files to another drive where I have more space with out affecting the replication and the data.

How do I do this safely?


texts: Table replication with extra TIMESTAMP column on slave

texts:
I have  a table on master and same table on slave with an extra column like

InsertTime timestamp DEFAULT CURRENT_TIMESTAMP

I want to keep record of time when there is update on slave.

Replication is working fine, but added column InsertTime on slave DB is storing 000.000.00 00-00-00 value. It is not storing current system timestamp. When I issue separate insert statement on slave side, it is storing system time but not with replication. Can anyone suggest me how to achieve system timestamp on slave side?

Other details as follows:

MySQL DB: MariaDB 5.5.23
Replication Type: Row based
Platform: Linux


texts:
On SQL Server 2012 can a process SPID change mid-operation? We have a very unique query that we've been monitoring (production, but controlled testing) and have seen it at different intervals with different SPIDs. It's a unique query that no one else should be running, at least with these parameters, and as such we shouldn't see it with a different SPID, unless... can a query can change SPIDs mid process?

My understanding is this is impossible, so more likely is that for some unknown reason the application is re-submitting this unique and performance destroying query, thus generating a new SPID, despite no user interaction.


texts: SQL Server uses only 1 CPU of 4 available

texts:
I've two machines with SQL Server 2012 Express SP1 with default configuration settings for CPU usage.

I'm using this query on my work machine:

SELECT cpu_count
FROM sys.dm_os_sys_info

It returns "4", which is true.

The same query on production machine returns "1" (that machine has 4 processors too).
There's no additional CPU usage settings in both cases, except defaults.

Production machine is a virtual machine, which, possibly, was reconfigured after SQL Server installation.

Is there any way to reconfigure SQL Server for using of 4 processors?


texts: In PostgeSQL's psql interactive terminal, you can type \?+<enter> to produce a overview that shows a list of common commands. In Ubuntu Linux, is there a way that I can pipe that content to a file that I can cat or open up in a GUI text editor?


texts:
I am getting Access Denied error ..

Openrowset is throwing error for file access as Access Denied

I tried running these 2 queries as well..

 EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

SELECT  *   FROM OPENROWSET(BULK '\\MySharedFolder\\TEST\\MyFile.txt',SINGLE_CLOB) AS  ExternalFile;

I am getting an error as

Cannot bulk load because the file "..." could not be opened. Operating System error code 5(Access is denied.).


texts:
I am running mysql with MyISAM storage engine to store user status(user updates data).
To load user recent updates data i am using this query:

$limit = 20;
$uid = 3;
SELECT * FROM userstatus WHERE uid = {$uid} ORDER BY id DESC LIMIT {$limit}

but is the query is performance efficient, because i am ordering it descending by the status ID (which is in ascending order)?
SQL command to create the Table:

CREATE TABLE userstatus (
  id INT NOT NULL AUTO_INCREMENT,
  uid INT NOT NULL,
  status TEXT NOT NULL,
  datentime TIMESTAMP NOT NULL,
  visible INT (3) NOT NULL,
  statustype CHAR (16) NOT NULL,
  PRIMARY KEY (id)
);

So how the query will work? Is it go through and fetch all the posts of an user then order it DESC using the status ID and finally limit it to 20? is it ok, or any better way to query it?
Thanks


texts: Recursive CTE to get Grandparent, parent, children

texts:
I have a data structure that relies on a parent/child model. The top level parent can have multiple children where they can also be parents.
Here's an example of how things can look in the tree:
900107
    --> 200237
            --> 100112
            --> 100113
    --> 200238
            --> 100114
            --> 100115
                --> 100120
                    --> 100001
                    --> 100002
--> 200239
--> 200501
--> 300112
--> 400987
--> 444444

In this example, 900107, 200237, 200238, 100120 are all parents where 900107 is going to be our grandparent.
Here's how it looks in the table:
ParentPart   Component
900107       200237
900107       200238
900107       200239
900107       200501
900107       300112
900107       400987
900107       444444
200237       100112
200237       100113
200238       100114
200238       100115
100120       100001
100120       100002

I have a recursive CTE that will get me all of the parents and children, but not the "grandkids" or "great grandkids" if they exist.
Here's my CTE:
CREATE TABLE #Hierarchy (
ParentPart NVARCHAR(6),
Component NVARCHAR(6))

INSERT INTO #Hierarchy (ParentPart, Component)
VALUES ('900107', '200237'),
       ('900107', '200238'),
       ('900107', '200239'),
       ('900107', '200501'),
       ('900107', '300112'),
       ('900107', '400987'),
       ('900107', '444444'),
       ('200237', '100112'),
       ('200237', '100113'),
       ('200238', '100114'),
       ('200238', '100115'),
       ('100115', '100120'),
       ('100120', '100001'),
       ('100120', '100002')

;WITH hier AS (
SELECT ParentPart AS MainPart, ParentPart, Component
  FROM #Hierarchy
UNION ALL
SELECT hier.MainPart, p.ParentPart, p.Component
  FROM hier
  JOIN #Hierarchy AS p
    ON p.Component = hier.ParentPart
)

SELECT ParentPart
     , Component
  FROM hier
ORDER BY ParentPart, Component

OPTION (MAXRECURSION 0)

This works for the most part....I can get this if I use one ParentPart. We have thousands of ParentParts and we'd need to know who the grandparent is. Ideally we'd want the output to look like this where the parent part is the grandparent and every child and grand child is in the component field
ParentPart    Component
900107        200237
900107        200238
900107        200239
900107        200501
900107        300112
900107        400987
900107        444444
900107        100120
900107        100112
900107        100113
900107        100114
900107        100115
900107        100001
900107        100002

What would be the best way to do this?


texts: Is there any significant difference between Month(date) and DatePart(month, date)?

texts:
MSDN states that both Month(date) and DatePart(month, date) return the exact same value, however is there any difference between the two methods, performance or otherwise?

I'm building some queries that aggregate large sets of data based on month, and sometimes they take a while to run so I'd like to be sure I'm using the most efficient syntax possible.

I'm currently using SQL Server 2005


texts: Improving database design, is this a valid case for entity attribute values?

texts:
Well currently we are not using eav, however our database seems a bit finicky and one suggestion was something along the lines of entity attribute values.
What we have is a table which acts as "translations". It has multiple rows with each line describing a translation. With multiple column, each column being a language.
CREATE TABLE table_name (
    id serial,
    organisation int,
    key text,
    en_gb text,
    nl_nl text,
   ....
);

This works fine (omitted constraints). However one feature is that users need to supply their own translations for data they inserted. In whatever language they wish. (Given the language needs to be formally described in iso 639-1)
This creates the weird situation where upon inserting not only a new row might be added. But the database schema also might be modified: a check is performed if the language column exists, if it is not it is added.
Which also adds a lot of columns (with nulls) for all fields - a minor problem though.
One solution would be to instead of having a column for each language use a json "translations" so that not each field/row needs to have a value for everything. This was dismissed on grounds that the far majority of the time we modify a single field, in an already existing column: apart from 6 or so languages where we operate we do not expect many more to be inserted.
Another solution was to use EAV, with instead of using a single row for all translations from a single field there is a new row for each language/translation of a certain field.
CREATE TABLE table_name (
    id serial,
    organisation int,
    key text,
    language text,
    translation text,
   ....
);

This seems a good case for entity attribute values right? Apart from that the database now is quite convoluted and isn't easily visualized as a neat table. So is it?
The ability to visualize as a neat table I personally like since insertion happens most often by providing a csv, so the visualization mirrors that.

For a given entity and language, could there be more than one translation provided? If so, how would they be distinguished from each other?

The user just inserts translations for organisations. A translation would be:
{
    organisation: 1,
    key: 'orgname',
    'en-us': 'somename',
    'nl-nl': 'dutchname',
    'fr-fr': 'french-organisation-name'
}

Uniqueness is given by (organisation, key) (or when transformed to multiple lines (organisation, key, language)). When multiple users edit, given they have the right to do so, they translate "for everyone", there is no merit of who did the translation, or for whom the translation is.


texts: SSAS Clustered Instance - no connection could be made because the target machine actively refused it

texts:
I have a clustered SSAS Tabular named instance (TAB_PROD) running on SQL Server 2016 SP2/Windows 2012 R2.
When the instance is running on Node1 in the cluster I can connect to it using

SQL Network Name + Instance : TABULAR\TAB_PROD

But when the instance is failed over to Node2 in the cluster I can no longer connect to it using TABULAR\TAB_PROD. When I do I get the error "no connection could be made because the target machine actively refused it :59755"
SQL Server Browser is running on both nodes in the cluster as I know named SSAS instances use dynamic ports assignments. I've tried switching the account that runs Browser from Local Service to Local System as I've seen some suggestions that might resolve this, but it didn't work.
Grateful for any suggestions as to why it only connects on one node in the cluster.


texts:
I'm considering to use MySQL Workbench at work, mainly because of the modelling tools and to easily sync the development and the production DBs. I work with another developer on the same project, and we currently use GIT to version our work. In a perfect world it would be possible just to merge different changes in the models directly within GIT, but it's not possible, since the Workbench files are binary, so now I'm looking for a team strategy to work together with Workbench, share and update the same files and still be able to work on different features.

I also tried to extract the .mwb content to version the .xml inside it, but when it comes to merging it's very difficult to edit the XML involved without breaking/corrupting it.

Any suggestion? How did you solve this problem in your team?


texts: Okay to run sp_refreshview against all views on a regular basis?

texts:
Microsoft SQL Server has a system stored procedure called sp_refreshview which updates the metadata of the view that's passed in.

Are there any downsides to running this procedure regularly on all of my views? (Such as performance statistics being affected, etc?)

Could I run it nightly, hourly, every few minutes?

If there's no repercussions in doing so, it seems like a good way to ensure my views metadata stays up to date when there's potential for the underlying entities they reference to change.


texts: Combine sales data without knowing the number of sold units per one sale

texts:
I’d like to consult for a strategy plan of a database structure for the following scenario:

My app is for offices buildings sales reports where a building is the basic unit that I need to generate the reports for. The reports for a sale of a single office are straightforward, I have the building, offices and sales tables and just take it from there.

However, a sale is not limited to one office – one buyer, but one buyer can purchase several offices and combine them to one big office.

Now, since the original data is based on the original basic office unit (id, building_id, address, type, floor, space, number_of_rooms, description, sales_person_id, etc.) I need to find a way to combine few units (offices) as one sale.

The data for actual sale of combined offices is fed by the sales person as one sale and there’s no split of the sale data, they just mark the offices in this sale as combined.

My first thought was to split the sales data like so:

Tables buildings and units (and all the rest like sales persons, units types, etc.).
The sales table, as the name suggests, holds sale-specific details.
The combined_units_in_sale table will keep the IDs of each sale, one row per basic unit with the sale ID from sales.

I feel this is not how it should be and need some sanity check.

Here is the diagram I created so far (please ignore the data types shown in it):


texts: How do I REPLACE values in column after querying

texts:
I have a column with numeric values

I am querying

SELECT column
FROM table
WHERE column='1' OR
column='2' OR
column='3' OR
column='4'

THEN

REPLACE (column, "1", "ABC");
REPLACE (column, "2", "DEF");
REPLACE (column, "3", "GHI");
REPLACE (column, "4", "JKL")

I am not sure if I may be using the semicolons wrong.

Please help!


texts: How can I speed up this query on table with 65 million records

texts:
select hash_id, hash_value,
            substring(T.cri, 1, 7),
            substring(T.cri, 9, D2.Pos-9),
            substring(T.cri, D2.Pos + 1, D3.Pos - D2.Pos - 1),
            substring(T.cri, D3.Pos + 1, D4.Pos - D3.Pos - 1),
            substring(T.cri, D4.Pos+1, len(T.cri) - 6 - D4.Pos),
            substring(right(cri,5),1,1),
            substring(right(cri,3),1,1),
            substring(right(cri,1),1,1),
            ref_count,
            compartment_id,
            cg_id,
            cri_version_id
from hash_key as T WITH(index(IX_hashkey_hashid_covering_refcount),nolock)
    cross apply (select charindex('?', T.cri, 31)) as D2(Pos)
    cross apply (select charindex('?', T.cri, D2.Pos+1)) as D3(Pos)
    cross apply (select charindex('?', T.cri, D3.Pos+1)) as D4(Pos)
where hash_id between 1 and 10000

Create table:

create table hash_key_binary
(
  hash_id bigint not null identity,
  hash_value nvarchar(100) not null,
  cri nvarchar(100) not null,
  ref_count_checksum int not null default(0),
  compartment_id int ,
  cg_id int ,
  cri_version_id int,
  primary key (hash_value, cg_id),
  constraint fk_hash_compartment foreign key (compartment_id) references compartment(compartment_id) on delete no action,
  constraint fk_hash_compartmentgroup foreign key (cg_id) references compartment_group(group_id) on delete no action
)

These are the indexes present on this table :

CREATE NONCLUSTERED INDEX IX_hashkey_hashid_covering_refcount
  ON hash_key (hash_id ASC)
  INCLUDE (ref_count)

CREATE NONCLUSTERED INDEX IX_hashkey_cri
  ON hash_key (cri ASC)


texts: Using NEXT clause to set periodic materilized view refresh in oracle and verifying refresh

texts:
I am not able to find the right documentation on setting specific timings for materialized view refresh for oracle. I read up the documentation and some examples there, however there is no definite documentation on using NEXT clause. What would I do to set up refresh every day at 8am and 8pm for example? How would I achieve refresh 3 times a day?

Here's what I am using in my case. I understand this will start refresh at 7am tomorrow and next every 12 hours?

alter materialized view MY_VIEW
refresh fast
start with (sysdate+1) + 7/24
next trunc(sysdate) + ((trunc(to_char(sysdate,'HH24')/12)*12)+12)/24

I also want to know if there is a way to know how to verify if the materialized view was refreshed once the timings are set.

Update:
I tried to refresh my MV every hour, which didi not work. I am wondering why is that. Here's what I used:

CREATE MATERIALIZED VIEW  "MYVIEW"
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "APEX_xxxxxxxxxxxxxxxxxx"
  BUILD IMMEDIATE
  USING INDEX
  REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT sysdate+1/24
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
  AS select *
from <mydblinktable>


texts: Cassandra upgrade/repair issues in migration

texts:
I wanted to migrate part of the column families in a cluster to a new cluster.

I made a snapshot of the source cluster and copied the data, made a new cluster with the same name, same number of nodes that all have the same tokens as their parallel in the original cluster.
I copied the data to the data directory, renamed the file names to match the schema in 1.1 (original cluster is 1.06).

After recreating the schema in the CLI, and upgrading sstables no nodes own any of the data (effective ownership 0.00), any idea what I am doing wrong here?


texts:
In my project, I have about 100.000 users and can't control their behavior. Now, what I would like to do is log their activity in a certain task. Every activity, is one record which includes columns like user_id and some tag_id's.

The problem I have, is that these tasks in some cases can go up to 1.000.000 per year per user. So if I would store all these activities in one table. that would obviously become very big (=slow).

What is best to do here? Create a single table per user (so I have 100.000 log tables) or put all these activities in one table? And what kind of engine should I use?

One important thing to note: Although i simplified the situation a bit the following doesn't look normal, but users can also change values in these tables (like tag_id's).


texts: Primary key vs. unique index: performance difference with foreign keys?

texts:
Will there be any difference in performance if you compare a query fetching data via a join between two tables where there's a relationship against either of the following:

A primary key
A unique index


texts: How do I design a database to store quotes online?

texts:
I have a form to generate quotes online.  The form has many products with their respective prices and options.

The users will generate the quote online and I need to store the quotes of the users when they submit the form.

My question is how I do a design a database to store this information?

This is an example of my form:

Description        amount        price x un      total
product1             1             15000         15000
product 2            3             10000         30000
Total                                            45000

I have thought the next options:

id_quote        descriptions          total
1              product1 1 15000       45000
               product2 3 45000

Other option is by date:

id    date                      description     amount         price          total
1    15/03/2012 3_45:45         product 1       1             15000          45000
2    15/03/2012 3_45:45         product 2       3             10000          30000


texts: What are different ways to replace ISNULL() in a WHERE clause that uses only literal values?

texts:
What this isn't about:

This is not a question about catch-all queries that accept user input or use variables.

This is strictly about queries where ISNULL() is used in the WHERE clause to replace NULL values with a canary value for comparison to a predicate, and different ways to rewrite those queries to be SARGable in SQL Server.

Why don't you have a seat over there?

Our example query is against a local copy of the Stack Overflow database on SQL Server 2016, and looks for users with a NULL age, or an age < 18.

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE ISNULL(u.Age, 17) < 18;

The query plan shows a Scan of a quite thoughtful nonclustered index.

The scan operator shows (thanks to additions to actual execution plan XML in more recent versions of SQL Server) that we read every stinkin' row.

Overall, we do 9157 reads and use about half a second of CPU time:

Table 'Users'. Scan count 1, logical reads 9157, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 485 ms,  elapsed time = 483 ms.

The question:
What are ways to rewrite this query to make it more efficient, and perhaps even SARGable?

Feel free to offer other suggestions. I don't think my answer is necessarily the answer, and there are enough smart people out there to come up with alternatives that may be better.

If you want to play along on your own computer, head over here to download the SO database.

Thanks!


texts: Find option on SQL Server for execute process in Visual Basic

texts:
I am a new developer in a system, I will comment a little of the problem clarifying that there is no documentation of this process where I work and that's why I ask around here.
We have 2 services and 2 databases, these databases are synchronized with a program developed by the company, the program is very old in fact no one touch this in many years and is made in visual basic 6.0. I will call this program "synchronizer".
I have a table where I add a record, by adding this record runs the "synchronizer", this "synchronizer" runs several sp, also for what I understand the code of the "synchronizer" prints in console everything that runs.
Question:
This "synchronizer" is in the SQL Server, my question is: where is configured the execution of this type of programs in the SQL Server? Is there an option to see the console in SQL Server of this program? Where can I find some option that links this program with the SQL Server?


texts: Can I do something like RESTORE DATABASE .?

texts:
Is it possible to restore a database on a server, using a script on another server?

For example, I have a server A and a server B and I want to restore a database foo on server B from within a script on server A.
I was thinking of something like this: RESTORE DATABASE B.foo FROM DISK ..., but I can't find any information about it.

And I'm looking for something similar for BACKUP DATABASE too.


texts: Safely changing innodb_buffer_pool_size on MySQL replication master?

texts:
We have a MySQL server which is a replication master. We need to change the innodb_buffer_pool_size and innodb_flush_method to (hopefully) improve performance as the database has grown.

What is the "safest" method for us to do this? We have 2 slave servers which will need to continue replication after this is done.

The plan so far is:

Stop replication on the 2 slave servers with STOP SLAVE.
Stop MySQL on all 3 servers with service mysql stop.
Make the changes in the my.cnf file on the master server.
Start MySQL on the master again to get the live database back online ASAP with service mysql start.
Make the changes in the my.cnf file in both the slave servers.
Start MySQL again on both slave servers with service mysql start.

The settings are:

innodb_buffer_pool_size = 54G
innodb_flush_method=O_DIRECT

The server has 64GB of memory and is a dedicated MySQL server, the server doesn't really do anything else at all. It's a Dell PowerEdge R720 running Ubuntu 12.04 Server.

Can anyone foresee any problems with this method? Is there anything else we need to be aware of?


texts: Is there a way to optimize the query if we are using ROUND function 100 times in a single query

texts:
The below query is to compare the last day of the month and select either zero or a value.
I am not pasting the entire query as its huge and the i am just pasting the condition which if possible can be optimized.

The fields M_H_REP_DATE , M_F_CCFRMCCD1 , M_F_CCFRMCD2  are dates and are  getting compared with date , and  GAP.M_F_CCNOM is of float(21,2) format

ROUND(sum(case when LAST_DAY(ADD_MONTHS(TD.M_H_REP_DATE,0)) >=
M_F_CCFRMCD1 and
LAST_DAY(ADD_MONTHS(TD.M_H_REP_DATE,0)) < M_F_CCFRMCD2 then
GAP.M_F_CCNOM else 0 end )) as
M_0M

ROUND(sum(case when LAST_DAY(ADD_MONTHS(TD.M_H_REP_DATE,1)) >= M_F_CCFRMCD1 and
LAST_DAY(ADD_MONTHS(TD.M_H_REP_DATE,1)) < M_F_CCFRMCD2 then GAP.M_F_CCNOM else 0 end )) as
M_1M

ROUND(sum(case when LAST_DAY(ADD_MONTHS(TD.M_H_REP_DATE,2)) >= M_F_CCFRMCD1 and
LAST_DAY(ADD_MONTHS(TD.M_H_REP_DATE,2)) < M_F_CCFRMCD2 then GAP.M_F_CCNOM else 0 end )) as
M_2M

ROUND(sum(case when LAST_DAY(ADD_MONTHS(TD.M_H_REP_DATE,3)) >= M_F_CCFRMCD1 and
LAST_DAY(ADD_MONTHS(TD.M_H_REP_DATE,3)) < M_F_CCFRMCD2 then GAP.M_F_CCNOM else 0 end )) as
M_3M
ROUND(sum(case when LAST_DAY(ADD_MONTHS(TD.M_H_REP_DATE,4)) >= M_F_CCFRMCD1 and
LAST_DAY(ADD_MONTHS(TD.M_H_REP_DATE,4)) < M_F_CCFRMCD2 then GAP.M_F_CCNOM else 0 end )) as
M_4M
ROUND(sum(case when LAST_DAY(ADD_MONTHS(TD.M_H_REP_DATE,5)) >= M_F_CCFRMCD1 and
LAST_DAY(ADD_MONTHS(TD.M_H_REP_DATE,5)) < M_F_CCFRMCD2 then GAP.M_F_CCNOM else 0 end )) as
M_5M
ROUND(sum(case when LAST_DAY(ADD_MONTHS(TD.M_H_REP_DATE,6)) >= M_F_CCFRMCD1 and
LAST_DAY(ADD_MONTHS(TD.M_H_REP_DATE,6)) < M_F_CCFRMCD2 then GAP.M_F_CCNOM else 0 end )) as
M_6M
ROUND(sum(case when LAST_DAY(ADD_MONTHS(TD.M_H_REP_DATE,7)) >= M_F_CCFRMCD1 and
LAST_DAY(ADD_MONTHS(TD.M_H_REP_DATE,7)) < M_F_CCFRMCD2 then GAP.M_F_CCNOM else 0 end )) as
M_7M
ROUND(sum(case when LAST_DAY(ADD_MONTHS(TD.M_H_REP_DATE,8)) >= M_F_CCFRMCD1 and
LAST_DAY(ADD_MONTHS(TD.M_H_REP_DATE,8)) < M_F_CCFRMCD2 then GAP.M_F_CCNOM else 0 end )) as
M_8M
ROUND(sum(case when LAST_DAY(ADD_MONTHS(TD.M_H_REP_DATE,9)) >= M_F_CCFRMCD1 and
LAST_DAY(ADD_MONTHS(TD.M_H_REP_DATE,9)) < M_F_CCFRMCD2 then GAP.M_F_CCNOM else 0 end )) as
M_9M
ROUND(sum(case when LAST_DAY(ADD_MONTHS(TD.M_H_REP_DATE,10)) >= M_F_CCFRMCD1 and
LAST_DAY(ADD_MONTHS(TD.M_H_REP_DATE,10)) < M_F_CCFRMCD2 then GAP.M_F_CCNOM else 0 end )) as
M_10M

The above line is repeated  for 40 times , since we have to add 34 months .
but the output is same for all conditions , its either GAP.M_F_CCNOM or O.

Is there a way to optimize the above query?
I am new to sql and could only manage the above query.

Thanks

`


texts: Many parallel queries hang but no deadlock

texts:
The sql is a very long multi-query like select-join. All keys are indexed.  When I run a load test with 70 connections continuously querying they all at some point stop receiving any network response (was Exception before I set Command Timeout = 0). I see many AccessSharedLocks started 15 minutes ago! Also there are several ExclusiveLocks for the same queries (why?). In pgAdmin this query is executed in 12 ms (at the same time!). The disk and CPU activity are both low.

SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED;

- returns 0 rows so they are not waiting for locks.

The all queries have waiting = 'f'

How can I fix it?

(Clickable)

select user0_.id as id73_0_, user0_.nickname as nickname73_0_, ..., challenges1_.start_at as start2_78_1_, challenges1_.counter as counter78_1_, challenges1_.user_id as user5_78_1_, challenges1_.challenge_id as challenge6_78_1_, challenges1_.user_id as user5_0__, challenges1_.id as id0__ from public.users user0_ left outer join public.user_challenges challenges1_ on user0_.id=challenges1_.user_id where user0_.id in ([single id]);

SELECT this_.id as id73_4_,..., characters2_.user_id as user28_6_, characters2_.id as id6_, characters2_.id as id1_0_, ..., smartchall3_.character_id as character3_7_, smartchall3_.id as id7_, smartchall3_.id as id79_1_, ..., userupdate4_.id as id99_2_, userupdate4_.coins as coins99_2_, userupdate4_.gold as gold99_2_, ..., userupdate4_.tournament_xp as tournament10_99_2_, userupdate4_.tournament_class_xp as tournament11_99_2_, userupdate4_.tournament_xp_in_current_guild as tournament12_99_2_, userupdate4_.last_login_guild_user_leave_counter as last13_99_2_, userupdate4_.last_login_guild_user_join_counter as last14_99_2_, ..., user5_.id as id73_3_, user5_.nickname as nickname73_3_, ... FROM public.users this_ left outer join public.characters characters2_ on this_.id=characters2_.user_id left outer join public.user_character_smart_challenge_trackers smartchall3_ on characters2_.id=smartchall3_.character_id left outer join public.users_updateable_data userupdate4_ on this_.id=userupdate4_.id left outer join public.users user5_ on userupdate4_.id=user5_.id WHERE this_.id in ([single id])

Note that adding SSD somehow postpones this issue until higher load.

I use Npgsql at the client side. All client connections are just waiting at Socket.Receive.

My log settings are:

log_min_duration_statement = 1000   # -1 is disabled, 0 logs all statements
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_lock_waits = on         # log lock waits >= deadlock_timeout
#log_statement = 'none'         # none, ddl, mod, all
log_temp_files = 0 # 0 logs all temp files

The log file is here https://1drv.ms/u/s!AhAvFYRsqyUIg6tvjXHPxe8iabOFWA


texts: Is the order of records in a VALUES list well-defined (postgres)?

texts:
Postgres 9.4 introduced the WITH ORDINALITY clause which adds an ordinality column to a table-like expression, somewhat similar to ROW_NUMBER() OVER (). Apparently, this is used to provide ranks for set-returning functions like unnest(<array>). My question is, is the order of rows defined for VALUES lists? I would like very much for this to be the case:
 SELECT *, ROW_NUMBER() OVER () FROM (VALUES ('a'), ('b'), ('c')) xs(a);
 a | row_number
---+------------
 a |          1
 b |          2
 c |          3

EDIT: Fixed to use ROW_NUMBER instead of WITH ORDINALITY (which is syntactically incompatible with a values list).


texts: Confused by GRANT ALL PRIVILEGES ON *.* (...) WITH GRANT OPTION

texts:
Normally I manage users on my databases via phpMyAdmin, but since I do everything else in standalone programs such as SQLyog or HeidiSQL, I wanted the ability to manage users remotely from these programs.

The remote user already had SUPER because it is needed when copying views from a local database to a remote database.
The remote user did not have the required database-level rights on mysql and the target databases as SUPER does not give that.

I tried this (MySQL version 5.6.23):

GRANT ALL PRIVILEGES ON *.* TO 'UserName'@'myIP' IDENTIFIED BY 'password'
WITH GRANT OPTION

Strangely, the *.* did not seem to work. For instance:

SELECT USER, HOST, db, select_priv, insert_priv, grant_priv FROM mysql.db
WHERE db="somedatabase"

returned an empty set. As a result, at that stage my remote user was not able to grant privileges to users (error 1442).

And yet,

SHOW GRANTS FOR 'UserName'@'myIP'

did show

  GRANT ALL PRIVILEGES ON *.* TO 'UserName'@'myIP' IDENTIFIED BY
  PASSWORD 'someHash' WITH GRANT OPTION

To solve it, I had to GRANT ALL specifically on mysql and on somedatabase:

GRANT ALL PRIVILEGES ON mysql.* TO 'UserName'@'myIP' IDENTIFIED BY 'password'
WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON somedatabase.* TO 'UserName'@'myIP' IDENTIFIED BY 'password'
WITH GRANT OPTION;

Would someone have insights as to why *.* was not enough?


texts:
This started happening today for absolutely no reason. Worked perfectly last week. I didn't touch the machine.

I have an issue described here and need to downgrade in order to be able to upgrade. Currently I'm not able to enter the shell as it exits with error #4

need to upgrade database admin with pdfile version 4.22, new version: 4.5

If I run it with --upgrade I get:

ERROR: error: exception cloning object in admin.system.users system.users entry must have either a 'pwd' field or a 'userSource' field, but not both obj:{ _id: "nod$
exception in initAndListen: 2 system.users entry must have either a 'pwd' field or a 'userSource' field, but not both, terminating

I'm trying sudo apt-get install mongodb-10gen=2.2.7but it doesn't work because that version is missing - I dont know if there is any way to install legacy versions?

Can anyone direct me to a command I can use to install version 2.2.7?


texts: How to select sub-object with given keys from JSONB?

texts:
How to extract / get / select a "sub object" from a json(b) object in Postgres?
There seems to be a lot of info that get me almost all the way there but not quite. Lots of stuff about converting to records, filtering and then building a new object out of it. TBH, I'm really surprised that this is not a builtin capability. Perhaps there's a simple way to achieve this by composing builtin fns? What I'm looking for is essentially the equivalent of select-keys; a function (say, jsonb_select_keys) that given this:
SELECT jsonb_select_keys('{"a":42,"b":43,"c":44,"d":97}', '{a,d,e}');

would return this:
{"a":42,"d":97}

Kind of like jsonb_path_query_array but for k-v pairs instead of values.


texts:
In my sql server 2008, after running the query the data return is very huge say in millions. I tried copying but it gives an exception sql memory error. Can you let us know how can i copy the entire data and paste it into excel.

I don't want to export it to txt file as the data is not getting aligned.
So i want to manually copy and paste it into excel.
Please let me know the solution for thhis


texts: Is it necessary to create foreign key in this query?

texts: I have the table called investors having as primary key the column id. I have the tables Users, Login_Logs and Accounts. All of these tables contains the foreign key investor_id. Between investor and user we have a one to one relation. between investor and login_logs a one to many relation and between investor and acccount a one to many relation as well. My question is in order to make a query to load easily info contained in the table users, login_logs and accounts do I need to store user id, accounts id and login_logs in the investors table? I mean do I need to create a foreign keys in the investors table?


texts:
I configured MySQL replication between a Master and a Slave throught VPN.
Everything works fine, when connection between them is down, and i make some changes to the Master (like insert a row), then when the connection comes back any operation is performed on the Slave.

This is the SLAVE and MASTER status before the connection down problem.

**SLAVE STATUS**

Slave_IO_state
Waiting for master to send event
Master_log_file
mysql-bin.000001
Read_master_log_pos
600
Connect_retry
60
Relay_master_log_file
mysql-bin.000001
Relay_log_file
mysql-relay-bin.000002
Relay_log_pos
824
Slave_IO_running
Yes
Slave_SQL_running
YES
Seconds_behind_master
0

**MASTER STATUS**

File
mysql-bin.000001
Position
600

Then while the connection down i insert a row in a master's table.
The connection comes back and what is see is

**SLAVE STATUS**

Slave_IO_state
Waiting for master to send event
Master_log_file
mysql-bin.000001
Read_master_log_pos
600
Relay_master_log_file
mysql-bin.000001
Relay_log_file
mysql-relay-bin.000002
Relay_log_pos
824
Slave_IO_running
Yes
Slave_SQL_running
YES
Seconds_behind_master
0

**MASTER STATUS**

File
mysql-bin.000001
Position
**887** <-- JUST THIS CHANGED

So i don't see any change in the Slave Table.
And after this, with the connecton up, the replication doesn't work.
To let it work I have to do in the slave:

CHANGE MASTER TO MASTER_LOG_POS=887

But with i still don't see al the changes made on the Master when the connection was down.

UPDATE
After connection down, when it comes back, to get the replication on the slave i have to restart it with:

STOP SLAVE;
START SLAVE;

I am still confused.


texts: How to create new users for restored databases?

texts:
I am not too familiar with SQL Server and am facing the following problem: The server was freshly installed and the only existing login is sa.

Then a couple of databases were restored from earlier backups.

Now I'd like to run a few SQL scripts that create new users for these databases, running as sa, but am getting the message that I am not allowed to do this.


texts: Set sql_mode “blank” after upgrading to MySQL 5.6

texts:
After upgrading MySQL from 5.5 to 5.6, some our app face to problem and need set sql_mode to blank to solve this issue. I added sql_mode = '' to my.cnf but there was no effect on the mysql setting.

How do I keep the sql_mode blank ?


texts: How do I cast varchar to macaddr without changing client code

texts:
I have a dedicated DB Server with Debian Jessie and Postgresql 9.4

Client: A is a Tomcat Java app which use torque with a JDBC driver connecting to the DB Server.

Issue: on Updating a row with a new value the update fails because the app transfers the wrong datatype.

Error message:

  column "device_macaddr" is of type macaddr but expression is of type character varying at character 159

Update Statement generated by Torque:

UPDATE device SET device_last_change = $1, device_macaddr = $2 WHERE device_ID = $3

Definition of Datatypes in postgresql :

last_change is timestamp (0) without time zone NOT NULL
device_macaddr is macaddr NOT NULL
device_id is bigint NOT NULL

We recently did update both the DB and the jdbc driver from 8.4 to 9.4

I was able to pinpoint the change to the jdbc driver but was unable to find the changelog which exactly showed this change of behaviour.

Since then the above statement seems to not work anymore, while it previously worked despite being possible the wrong datatype back then as well.

Is there a way to "lessen" the strict detection from the Postgres 9.4 DB Server for that specific case?

I did read the enhancement of the jdbc driver to include those additional datatypes, however this hasn't worked for me yet so I try to get a workaround on the other end of the options i have.


texts: Query Error: Error: ER_NON_UNIQ_ERROR: Column 'candidate_id' in field list is ambiguous

texts:
This is my query

SELECT user_name,candidate_id
FROM user AS usr INNER JOIN candidate as can
ON usr.user_id = can.entered_by
INNER JOIN candidate_joborder as canjo
ON can.candidate_id = canjo.candidate_id
WHERE usr.user_id = 1

And my fiddle

https://www.db-fiddle.com/f/gWr5g2222yAE8Fh1Gc6Awf/2

How to resolve this?


texts: Azure SQL Server - Cannot get rid of the sort operator

texts:
I'm using Azure SQL Server (Microsoft SQL Azure (RTM) - 12.0.2000.8   Apr  3 2019 13:27:00   Copyright (C) 2019 Microsoft Corporation), Premium tier 4 (500 DTUs).

Query: offer progress has a history of statuses for each offer and I want to get the first row for each offer/status. So, for offer #1 I want the first time it was set to status 1, status 2, etc.

        SELECT OfferProgressId, OfferId, DateCreated, OfferStatusDate, OfferStatusId, Name
        FROM
        (
            SELECT
                op.OfferProgressId, op.OfferId, op.DateCreated, op.OfferStatusDate, op.OfferStatusId,  u.Name,
                ROW_NUMBER() OVER(PARTITION BY op.OfferId, op.OfferStatusId ORDER BY op.DateCreated) AS FirstStatus
            FROM OfferProgress op
            LEFT JOIN [User] u ON u.UserId = op.UserId
            WHERE
                op.OfferStatusId IN (3, 5, 6, 7, 8, 9, 16, 17) AND
                op.DateCreated >= '8/1/2017'
        ) t
        WHERE
            t.FirstStatus=1

Execution plan: https://www.brentozar.com/pastetheplan/?id=rkhP--LcN

My goal is to get rid of the sort operator that has a warning.

My indexes: IX_OfferProgress_DateCreated, IX_OfferProgress_OfferId_OfferStatusId_DateCreated, IX_OfferProgress_OfferStatusId_DateCreated_OfferId_UserId_with_include


texts: Measuring how long a query is IDLE IN TRANSACTION (postgresql + Django app)

texts:
When looking at pg_stat_activity, how can I tell whether the state IDLE IN TRANSACTION is transient?

Can the table's columns give that information to me (e.g. xact_start or query_start)? Btw, waiting is set to f for all these queries. Maybe that means they're not waiting on any locks, and so everything is fine?

I'm trying to ascertain how bad the IDLE IN TRANSACTION problem is on my app. There are lots of rows in pg_stat_activity with this state, but every time I re-query the table, there's new information. I need a more precise way to measure how long certain queries are IDLE IN TRANSACTION.


texts: Compare strings from two tables SQL Server 2014

texts:
I'm using these two tables in a SQL Server 2014 database:

CREATE TABLE #FORMULAS
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DataSourceAddress] [nvarchar](255) NULL,
    [Description] [nvarchar](255) NULL
)

CREATE INDEX [IX_FORMULAS]
ON #FORMULAS ([DataSourceAddress] ASC)

CREATE TABLE #PARAMETERS
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Datetime] DateTime NULL,
    [ParameterName] [nvarchar](255) NULL,
    [Value] float NULL
)

CREATE INDEX [IX_PARAMETERS]
ON #PARAMETERS([ParameterName] ASC)

INSERT INTO #FORMULAS ([DataSourceAddress], [Description])
VALUES ('(parameter1+parameter2+parameter3)/3', 'Formula description 1'),
       ('parameter4', 'Formula description 2'),
       ('(parameter5*1000)', 'Formula description 3');

INSERT INTO #PARAMETERS ([Datetime], [ParameterName], [Value])
VALUES ('2016-01-26 11:40:00.000', 'parameter1', 1),
       ('2016-01-26 11:40:00.000', 'parameter2', 2),
       ('2016-01-26 11:40:00.000', 'parameter3', 3),
       ('2016-01-26 11:40:00.000', 'parameter4', 4),
       ('2016-01-26 11:40:00.000', 'parameter5', 5),
       ('2016-01-26 11:40:00.000', 'parameter6', 6),
       ('2016-01-26 11:44:00.000', 'parameter1', 1),
       ('2016-01-26 11:44:00.000', 'parameter2', 2),
       ('2016-01-26 11:44:00.000', 'parameter3', 3),
       ('2016-01-26 11:44:00.000', 'parameter4', 4),
       ('2016-01-26 11:44:00.000', 'parameter5', 5),
       ('2016-01-26 11:44:00.000', 'parameter6', 6);

I want to select the parameters of the table #FORMULAS that match with the column [ParameterName] of the table #PARAMETERS.

For example, take the value '(parameter1+parameter2+parameter3)/3' of the table #FORMULAS and receive all the rows of the table #PARAMETERS that contains 'parameter1','parameter2','parameter3'.

I'm using the following select:

SELECT
    A.[Datetime],
    A.[ParameterName],
    B.[Description],
    A.[Value]
FROM
    #PARAMETERS A
JOIN
    #FORMULAS B ON CHARINDEX(A.[ParameterName], B.[DataSourceAddress]) > 0

The #PARAMETERS table has more than 160000 rows (with 1174 unique ParameterNames), #FORMULAS has 3283 rows. The query takes a lot of time to finish, more than 10 minutes.

Is there a faster way to do the query?


texts: Why do these similar queries use different optimisation phases (transaction processing vs quick plan)?

texts:
The example code in this connect item

Shows a bug where

SELECT COUNT(*)
FROM   dbo.my_splitter_1('2') L1
       INNER JOIN dbo.my_splitter_1('') L2
         ON L1.csv_item = L2.csv_item

Returns the correct results. But the following returns incorrect results (on 2014 using the new Cardinality Estimator)

SELECT
    (SELECT COUNT(*)
    FROM dbo.my_splitter_1('2') L1
     INNER JOIN dbo.my_splitter_1('') L2
        ON L1.csv_item = L2.csv_item)

As it incorrectly loads the results for L2 into a common sub expression spool then replays the result of that for the L1 result.

I was curious as to why the difference in behaviour between the two queries. Trace Flag 8675 shows that the one that works enters search(0) - transaction processing and the one that fails enters search(1) - quick plan.

So I assume that the availability of additional transformation rules  is behind the difference in behaviour (disabling either BuildGbApply or GenGbApplySimple seems to fix it for example).

But why do the two plans for these very similar queries encounter different optimization phases? From what I've read search (0) requires at least three tables and that condition certainly isn't met in the first example.


texts: How to read queries of master that sends to slaves?

texts:
I want to see queries that Master sends to slaves in Mysql ?
Is there a way to see that ?


texts: TSQL retrieve backup date from database after restore

texts:
Usecase: MSSQL Database is hosted in the cloud, I download the database thrue FTP and restore it on-premise automatically for reporting.

Now I want to retrieve the date/time when the backup was made, from the restored database, to show my users how old the data is that they are working with.

In my local MSDB, I only see when the database was restored, not when it was originally back-upped in the cloud. I know the date-time is in the .bak, but that is hard to query.

Any suggestions?


texts:
I'm trying to create a view that groups score data for each week and displays it for each user but I don't know which commands to use.

Sample data:

// scorecards table
week | user | score    |
1    | 1    | 100      |
1    | 2    | 50       |
1    | 3    | 75       |
2    | 1    | 20       |
2    | 2    | 30       |
2    | 3    | 40       |

Desired output:

week | user1 | user2 | user3
1    | 100   | 50    | 75
2    | 20    | 30    | 40


texts:
I have a scenario where we want to build an inventory description system and I am trying to design the schema but need a little guidance to make sure i'm not veering off the track.
The basic premise is: A combination of three locations (Selling location, Shipping Location and Destination Location) along with the Item being sent, is used to determine how something should be packaged and sent. It gets a little more complex with breaking this down into levels (Level1: Put item in box. Level2: Tape box up etc...) but still manageable.

By the time we get to the Packing level, we're dragging all the fields in to make a PK using five fields to make a unique record.

My question is, is there a reasonable limit on key field length. Does it make sense to provide a surrogate key for each group and then just just that going forwards?

So the UniqueLocationLookup would have a single field used as an identifier in the ItemLookup Table.

The Combination of UniqueLocationID and Item would then become another unique key used by LevelLookup and so on...


texts: vertica/postgres copy text to binary column

texts:
Until now I used Oracle to store xml file in blob data type column.

Is there any option in Vertica to copy xml file into binary column?

I saw there is varbinary data type but, I haven't succeeded to load this file into one row in this column.

If someone is familiar with this process in PostgreSQL can maybe be helpful (share your knowledge)


texts: Help undoing an accidental restore on SQL Server 2014

texts:
Yesterday I made a serious mistake¹: Restoring the wrong database.

Background (you may skip this section):

I right-clicked the Test database and proceeded to the "Restore Database" section as usual. Then, when selecting the media (file) for the Source, I browsed for my latest production database backup (performed at 01:00 AM). This was not a mistake; I did need to have up-to-date information on the Test database in order to... test.

However, as soon as I hit OK on the "Add media" dialog, SSMS detected that my backup was made from the Production DB and "silently" changed the "Destination" field to reflect that. I didn't realize this until it was too late. All database changes from 01AM to 06:30PM were lost.

After a few hours struggling on how I could approach my manager and deliver these unhappy news, I decided I would try my best to repair the damage in any way possible, and for a certain extent, I luckily succeed.

Now, the steps taken:

First and foremost, I made sure that today's backup had been performed, since it contained DB entries between 06:30PM to 10:00PM which could not be lost.
Went to the "Restore" dialog for the Production DB, and then clicked the Timeline option hoping to perform a point-in-time restore at precisely 06:29PM.
Unzipped the previous day backup (same one I accidentally restored previously) to my default backup location, and made sure a suitable "log backup" was present on my system.
Set up the desired point in time, clicked "Verify Backup Media" and proceeded to backup.

Now, I'm relieved that it worked, but as expected, all of my tables contains records up to 6:29PM and everything else is missing. It's a large database with more than 100 tables, and hand-checking and inserting everything would take days.

Question: is it possible to "restore" only the missing records which are present on my latest backup, but not in my current state DB? If so, what are the steps necessary?

¹ "Not as serious as yours, I fear" - Bane

Edit in response to James Anderson concerns:

About the backup chain: Actually I do have a backup file with the changes from 06:30PM till 10PM. It was created by a Job I've set up. In fact I even restored this backup to a new temporary Database just to be sure.
About getting more people involved to help: My manager is travelling for next couple of weeks, my team is really small (3 people) and I'm the one in charge right now. Which is to say, I'll inform them, but I'm responsible for handling this alone. Even if manually. If there are any tools that could help please suggest. Thank you everyone.