texts: Should I use backup compression for databases where all data is already compressed?

texts: In SQL server with enterprise edition, there are databases with all the tables/indexes compressed. Does it still make sense to do compressed backup?


texts: "TASK MANAGER" process is taking over a single-user mode database. What is it?

texts:
This is a duplicate of the question I asked on stackoverflow, but I was advised, that someone here could have a better idea what is happening.

I have a sporadic problem, when upgrading SQL Server in a single-user mode, using .NET SqlConnection, some other application somehow logs into the database, while the SQL code is being executed, and kicks my process out. SqlConnection is not closed or disposed in any way. But some other application somehow ends up connected to the database and that kicks my connection out.

When I run sp_who, I could see that a process that took control of the database is Command="TASK MANAGER".

Anyone could tell me what is this process, what is it's purpose, and how in the world it could get into a database, which is in single-user mode, and there is an active connection?


texts: Cannot connect remotely to sql server 2012 instance using SSMS

texts:
I have installed sql server 2012 Express on a local network machine, then added a new login: "sqlUser" mapped to "database1", and configured the instance to accept the remote access with both Windows and SQL Server authentication.

In the configuration manager -> SQL Server Network configuration -> Protocols for SQLEXPRESS -> TCP/IP properties, I have configured the IP to use TCP dynamic port: 12345 and TCP static ports is left blank, the IP2 have the static local IP address of the local network.

In the firewall I have added a new rule to allow the sql server program (exe) to accept remote connection.

Locally I can run SSMS and open database1 with both authentications using sqlUser account.

ping from the internet is successful. http site in the same sql server machine is reachable.

But when I want to access sql server instance from an internet machine using SSMS and sql server authentication:

Server:   123.123.123.123\SQLEXPRESS:12345
Login:    sqlUser
Password: pass123

I get the following error:

  Cannot connect to 123.123.123.123\SQLEXPRESS.

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

Any suggestion please ?


texts: Best way to setup master to multi master replication

texts:
ORIGINAL QUESTION:
I have multiple servers that all need to act as masters, as if one fails the next server kicks in and takes over. I'm just wondering what the best way to replicate is. All servers are in different data centers.

Questions

Would it be ok to set up every server as a master-master with the 'supermaster'?
What sort of conflicts would I have?
Are there better ways to do this?

UPDATED QUESTION:
We have servers all around the world in different data centers and each server needs access to a db usually this is on localhost.

Each server needs to be able to update the database and each server is pretty much a mirror of every other server. These all stay in sync via one server(supermaster) that all can communicate with.

All the servers only communicates with supermaster, they are not aware of any other server. The super master it self is a mirror of the other servers, just with the added service of syncing them all.

Some times the supermaster is offline for various reasons; when this happens the other servers carry on as normal which includes reading and writing data. When the supermaster comes back up, it starts the syncing process and sorts out and resolves conflicts between the other servers, when this is complete all servers have a mirror of the data and are all 'synced'.

So after thinking a bit more on the question, My questions is;

Is there any way to have a single db on each server that are the same
across the board, without having a single point where I can update
the db as in a Star Topology?
Is there a better option for replicating mysql across the board for my situation?

Any help is much appreciated.


texts: weird sql server behavior when inserting and selecting

texts:
Today after one of the drives on our sql server ran out of space. The drive contained eventracker logs, so I didn't think it was a big deal, but then I noticed when I insert a record into a database, it inserts it above previous records instead of appending it.

When I do select * the records that were inserted show up first. Is there something I can do to fix this?

section_d   section
-------------------
15          SDSD
16          SDSD2
17          SDSD2
18          adf

(4 row(s) affected)

insert into test2 values ('inserted row')

section_d   section
-----------------------
19          inserted row
15          SDSD
16          SDSD2
17          SDSD2
18          adf

(5 row(s) affected)


texts: How do I check for a null or empty table-valued parameter?

texts:
I have a stored procedure (SS2k8) with a couple table-valued parameters that will sometimes be null or empty.  I have seen this StackOverflow post that says that null/empty TVPs should simply be omitted from the calling parameter list.  My problem is that I can't figure out how to check for empty or null inside the stored procedure as "IF (@tvp IS NULL)" fails on procedure creation with the message 'Must declare the scalar variable "@tvp"'.  Do I have to do a SELECT COUNT(*) on the TVP and check for zero?

Code excerpt:

CREATE PROCEDURE [foo] (@tvp [TvpType] READONLY) AS

IF (@tvp IS NOT NULL) -- doesn't work
BEGIN
  -- lots of expensive processing
END
ELSE
BEGIN
  -- a little bit of cheap processing
END
...


texts: WHERE clause on a TIMESTAMP column causes serious performance penalty

texts:
I have a pages and a page_views tables with 100k~ & 4M~ records respectively.

I run a query to get the top pages that have the most page_views:

    select `pages`.`id`, `pages`.`host`, `pages`.`embed_code_id`, `views`.`count`
from `pages`
inner join `embed_codes` on `pages`.`embed_code_id` = `embed_codes`.`id`
inner join (
    select `page_id`, COUNT(*) AS count
    from `page_views`
    where `page_views`.`created_at` between '2018-12-25 13:20:35' and '2018-12-26 13:20:35'
    group by `page_id`
    ) as views on `views`.`page_id` = `pages`.`id`
where `embed_codes`.`id` in (512)
and `embed_codes`.`account_id` = 173
order by `views`.`count` desc
limit 6

This query takes more than 9 seconds to run, but when i remove the WHERE clause on page_views.created_at, it takes only 700ms~, which is acceptable i guess, given the tables sizes.

NOTE - page_views.created_at is indexed.

Any ideas why the WHERE clause is causing this slowness? Any way to avoid it?
Thanks.


texts: Select rows with repeat data within certain timeframe

texts:
As a follow-up question to my previous question on difficult row categorisation, I have another problem which I'm sure will be easily solvable. After this I plan on reading a good book or two on databases and SQL but for the time being I need to overcome this brick wall.

The table in question has the following layout:

TaskID             -  -  -  1. On-site
DateStart         |         2. Return-to-base
DateEnd           |         3. Chargeable
Type -  -  -  -  -          4. Complaint
Reference                   5. Rebook
Requester
Approver
Cost
...

I want to select all "repeat" TaskID. TaskID is a unique ID but a "repeat" is classed as any two tasks that share the same Reference and where the DateEnd of each task is within a 30 day period, so, I'm thinking something along the lines of:

SELECT BOTH TaskID, Reference
FROM   Tasks
WHERE  Row1.Reference = Row2.Reference AND ABS(Row1.DateEnd - Row2.DateEnd) <= 30;

Obviously the above isn't valid but hopefully it illustrates how I want to select the data. I'm guessing it will involve a self-join but I have no clue where to start... having the query return both TaskIDs with the reference is ideal but if it only returns one TaskID then that's no problem.

I'm version of PostgreSQL that I'm using is 9.1.3, if that helps.

Thanks for your help.


texts: Create a function/tsql to get strings from the 1st table/data and compare and convert if from the value of second table

texts:
I want to create a function/tsql to get strings from the 1st table/data and compare and convert to the value of a second table if the strings match what is on table 2.

Business example.
When a cashier mistakenly encoded ABC1231 as company name instead of ABC the report will have an issue showing ABC1231 instead of ABC. We need to correct this by matching it against a master list of company names in another table.

The query logic Im trying to apply here is to match how many similar strings are in table 1 data to master list data in table 2. Then if the process saw that the strings from table 1 has a match or has the highest similar string it will output what data in table 2. This should be also case sensitive and allows int and special character. See example below.

Example:
Table 1 contains ->

A121B312C33,AB12312C,AB-C,ABC223,AsBCD21,D23EF,D2E2F2,DEF

Table 2 contains ->

ABC,DEF

Output should be:

ABC,ABC,ABC,ABC,ABC,DEF,DEF,DEF

It is not only capital letters. It should match what is on the 2nd table whether lower case or upper case with special characters or with integer as well.


texts: Join three tables that share a common column

texts:
I have three tables with the following columns:

Table 1: Hostname, OS, Confidence
Table 2: Hostname, Manufacturer, Model, Serial_Number, Architecture, Memory
Table 3: Hostname, MAC, Interface

It should be, but I can't be certain yet, that if a Hostname exists on one table it will be on all three tables.  I'm not sure why this data is separated into three different tables but for the moment it is, which is what leads to my question.

How can I combine these three tables into one?  I have the following that'll combine two of them but I'm not sure how to modify it to include Table3:

SELECT
    COALESCE(Table1.Hostname, Table2.Hostname) AS Hostname,
    Manufacturer,
    Model,
    Serial_Number,
    Architecture,
    Memory
FROM Table1
FULL OUTER JOIN Table2
ON Table1.Hostname = Table2.Hostname


texts:
I'm making a sample database to test myself in MySQL workbench and was wondering the following:

I have five tables, Customer, Product, Stock(Quantity), Warehouse and Purchase Order.

For primary keys I have customerId, productId, warehouseId, and invoiceNo...but what would the stocks primary key be? Apologies if the answer is obvious, but as I understand it the foreign key would be the productNo.

Very new to this!

Any input would be greatly appreciated!


texts: SQL Server instance level action tracking

texts:
How can we track actions happening at instance level in SQL Server 2014 .e.g. would like to know who created database, who changed recovery model, who setup backup strategy, who gave permissions to particular user etc..

Is there any native way or external tool effective for this scenario. Please let me know.


texts: MySQL Slave Relay Logging but not logging Binary Log

texts:
I have a master slave setup both mysql 5.5, the setup is fine except that the slave won't Binary log to the standard mysql-binlog only to the relay logs. I fear this may be due to my lack of understanding on the matter but my ultimate goal is to setup another slave feeding off the current slave to then point the two together for a master-master relationship.

My problem is that although on the slave I can insert a record and the master replication position (on the slave) will change, it will not change as a result of slave activity on the same database. Will the slave not dump activity into both logs? If i point the secondary slave to relay logs what are my options down the line?

Hope that makes sense.

my my.ini setup on the slave is as such, a unique server id and hostname are also set:

binlog-do-db=our_database
binlog-ignore-db=mysql
binlog-ignore-db=test
replicate-do-db=our_database


texts: MySQL- Best practices for using rollback

texts:
What is the best practice / recommended way of using ROLLBACK operation, if any DML operation fails ?

Any ROLLBACK, COMMIT mechanism ?

FYI: I'm using InnoDB tables which complies with Atomicity.


texts:
I have performance problem! I searched for Inner Join, tried optimize table but still the same. I have Users, Entries, Tags and tag_user tables.

This code works perfectly :

select * from `entries`
where (
    select count(*) from `users` where `entries`.`profile_id` = `users`.`id`
) >= 1
order by `id` desc

And I should add this to complete my query :

select count(*) from `tags`
inner join `tag_user` on `tags`.`id` = `tag_user`.`tag_id`
where `tag_user`.`user_id` = `users`.`id` and `tags`.`id` in (?,?)) = 2

It becomes like this :

select * from `entries`
where (
   select count(*) from `users`
   where `entries`.`profile_id` = `users`.`id` and (
        select count(*) from `tags`
        inner join `tag_user` on `tags`.`id` = `tag_user`.`tag_id`
        where `tag_user`.`user_id` = `users`.`id` and `tags`.`id` in (119,36)) = 2
    ) >= 1
order by `id` desc

Any solution for better performance ?


texts: sp_Blitz Findings: SQL Server Last Restart is an hour before Server Last Restart

texts:
Result of sp_Blitz:
Finding = “Server Last Restart” ; Details = “Feb 11 2017 5:48AM”
Finding = “SQL Server Last Restart” ; Details = “Feb 11 2017 4:48AM”

SQL Server Last Restart is the tempdb create_date
Server Last Restart is the ms_ticks

I find the results odd because, SQL Server Started an hour before Server Restart?
Am I missing something here?


texts: Constellation Schema; Relationships between fact tables

texts:
What are acceptable types of depedendcies between facts in a multiple-fact schema?

Conceptually talking, As you know a Multi-Dimensional Model may have multiple facts (like in a constellation schema).

Now my question is: if we have for example two facts in the schema and those two facts have a relationship between them.

1- Is having a relationship between the two facts acceptable from a theoretical point of view?

2- If it's acceptable; what is these kinds of the available relationships?

fact1 (1-1) fact2 ;
fact1 (1-n) fact2 ;
fact1 (n-1) fact2 ;
fact1 (n-n) fact2  ;

3- Do the measures have a role in identifying these acceptable relationships. I read that having a (1-n) is acceptable when the measures are in the n side!

Are there any references?


texts: Mounting a database from database files copied from a previous installation

texts:
I recently had on Oracle 11g Express database crash.

I have copied the .dbf files from C:\oraclexe\app\oracle\oradata\XE to another folder and reinstalled Oracle 11g Express.

The files are:

Control.dbf
sysaux.dbf
system.dbf
TEMP.DBF
UNDOTBS1.DBF
USERS.DBF

I want to remount the old database files, so that I can recover the database.

Can you please tell me how can I do this?


texts: Can Extended Events be used instead of Auditing?

texts:
In BOL it is mentioned:

Does this mean Extended Events can capture all the events that are traced by auditing? Can we get all the functionalities of auditing just by using XEvents?


texts: Fast query in MySQL with table of 1B+ rows

texts:
Consider a MySQL table with the following columns:

source_id integer
timestep integer
position integer
value float

This table contains slightly more than 1,000,000,000 rows. The data is read-only and serves as source for certain analyses.

What can be done to speed up the queries?

I've created indices on all four columns. What else can be done?
The DB is currently on a MBP mid 2015, but it is going to be migrated to a workstation. Any suggestions on RAM-wise and / or SSD-wise?

Consider the following view on the previous table and a query on the view:

create view V(id, block, value) as
select source_id, round(timestep/25), avg(value)
from T
group by source_id, round(timestep/25);

select * from V where id=1;

The last query is taking ages to complete.

Version is 5.7.15


texts:
We are migrating to a model where every "customer" lives in its own dedicated schema (database per tenant). This will allow us to later move some accounts to different DB clusters or upgrade specific accounts to newer schemas, for example. However from the get-go we need to automate how databases are managed in bulk or the whole effort will end up in smoke.

Additional info: the split is dictated by regulatory reasons and to scale out.

The number of schemas would be about 1000 (today), hosted in ~10 MySQL instances, so roughly 100 schemas per DB instance. New accounts would grow the system at a rate of 100 schemas-per-DB-instance.

My goal is to apply DevOps practices to database change management. So the schema lives in a git repo. Next, we need to be able to selectively or in bulk upgrade specific accounts to bring them to certain "schema version" that's in git.

Problem is figuring out what changes to apply to what databases, sort of like a diff and make CLI tools but for the database schemas.

I was thinking of creating a file per one of two database objects: a table and a stored procedure. Fortunately these are the only objects I need to track. Each object in the database has a corresponding file in the git. So if I change a definition in the file, I can run a "db-diff" (a tool I'd create) to tell which objects in what databases are outdated.

To bring the DB schema in sync with the git version of the same object would work something like this:

for stored procedures that's easy: DROP/CREATE; I'd utilize the SP's routine_comment field in the information_schema.routines to put there its current git tag. If the version there is outdated, replace it with the SP in the file in git.
for table objects, it is not clear to me how to compare what's in git vs in DB. One major annoyance of MySQL is that the show create.. result doesn't show exactly the definition that you asked for in ALTER/CREATE. For example, if you have a bigint column, you end up with the silly bigint(20). To a diff tool that's a different schema than what we have in the git.

Is this something I'd need to write myself from scratch or anything already on the market that you recommend?


texts:
I have to select 2 values that were less that the given id and greater than the given id. I have tried this query but is there any better way to do it

Fiddle Sql

Begin

declare @rootValue int
declare @repID int

set @repID = 2
set @rootValue = (select Id from tblLookups where Id = @repID)

declare @rootMinusTwo int
declare @rootPlusTwo int

set @rootMinusTwo = (select count(*) from tblLookups where Id < @rootValue)
set @rootPlusTwo = (select count(*) from tblLookups where Id > @rootValue)

if @rootMinusTwo >= 2 and @rootPlusTwo >= 2
Begin
    select *
    from tblLookups
    where Id between @rootValue - 2 and @rootValue + 2
End
else if @rootMinusTwo < 2 and @rootPlusTwo >= 2
    select *
    from tblLookups
    where Id = @repID
    union
    select *
    from tblLookups
    where Id < @rootValue
    union
    select top (4 - @rootMinusTwo) *
    from tblLookups
    where Id > @rootValue
else if @rootMinusTwo >= 2 and @rootPlusTwo < 2
    select *
    from tblLookups
    where Id = @repID
    union
    select *
    from tblLookups
    where Id > @rootValue
    union
    select top(4 - @rootPlusTwo) *
    from tblLookups
    where Id < @rootValue
else if @rootMinusTwo < 2 and @rootPlusTwo < 2
    select *
    from tblLookups
    where Id = @repID
    union
    select *
    from tblLookups
    where Id < @rootValue
    union
    select *
    from tblLookups
    where Id > @rootValue
End

I forgot to add something at any point there should be 5 records if at all there are that many records satisfying the condition.

Example

The ID's are 1,2,3,4,5 if I supply 2 then also it should return 5


texts: Preserving existing logins during database refresh

texts:
I am trying to do database refresh within Sql server 2014 instances from lower env to production for making it to golive. All the required logins exist on current prod and as far as I understand while doing db refresh they will be overwritten or replaced. I know there is sp_helprevlogin but to script out from current prod and then recreate which seems time consuming activity in prod. Just looking if there is a better way i can still preserve those logins or users from getting overwritten?

Update@ Also what about the database roles?


texts: I am using sql server 2008 r2 express. I am getting some problems when i use import data feature to import data from one database to another. I am using SSMS to do it. While using the wizard i choose "Select identity insert" option to copy the identity element property. By using this identity elements are imported properly. But after importing i compared the source database and destination database and found some changes!!. All NULL values in the table are replaced by 0. So how can i avoid it.Is there any option to copy it as it while using import data wizard? Please help


texts: Can I tell postgres to use a different "collation" for an integer column?

texts:
Let's say I'm using an integer column to represent an "enum" in my application code, for a "priority" attribute.

0=low
1=medium
2=high

The app is operating for a while like that, and then I want to add two more

3=medium-low
4=medium-high

Now I want to be able to index this column and do queries on it using the correct semantic ordering. Is there a way to tell postgres, when creating the index and also when doing the query, that the number ordering isn't the semantic ordering?


texts: Cannot release unused space in SQL Server mdf file

texts:
I have a huge mdf file (61 GB) and it is 95% empty
I want to release unused space.
I tried to Shrink the file
but when I click OK the Shrink screen closes and nothing happens
the file still 61 GB
How to resolve this?


texts: Set SA to weak Password in SQL Server 2012

texts:
Hi I'm migrating a database to SQL Server 2012, and I have a problem.

Let me start by saying the application (ISV) has horrible security! So try not to cringe too much.

The app logs in with sa authentication.
The sa password is hard-coded into the app.

So the sa password on the 2012 instance must match the app login. However, it does not meet the 2012 password requirements.

Is there a way to override this functionality?


texts: Transferring Data from OLTP to OLAP databases

texts:
Systems that have both OLTP and OLAP databases periodically move data from the OLTP database to the OLAP database, so that reports can be generated based on the OLAP database.

What's the official term for this process and is there an overview of this process online?


texts: Why does this cursor produce results in the incorrect order?

texts:
I am writing some dynamic SQL to identify and, perhaps if I'm feeling crazy enough, automatically convert my NONCLUSTERED indexes into CLUSTERED indexes.

The line ORDER BY 1,2,3 DESC; in the SQL below is designed to output DROP INDEX... statements before ALTER TABLE... statements in order to DROP the NONCLUSTERED index first and then add a CLUSTERED index.  I had to add the DESC after column 3 to get the DROP first followed by the ALTER.  This is backwards, unless I'm losing it!

DECLARE @Server nvarchar(max);
DECLARE @Database nvarchar(max);
DECLARE @cmd nvarchar(max);
DECLARE @IndexType int;

SET @IndexType = 2; /*  1 is CLUSTERED, 2 is NONCLUSTERED */
SET @Server = 'MyServer';
SET @Database = 'MyDatabase';

SET @cmd = '
    DECLARE @cmd nvarchar(max);
    SET @cmd = ''
    SET NOCOUNT ON;
    DECLARE @IndexInfo TABLE (TableName nvarchar(255), IndexName nvarchar(255), IndexColumnName nvarchar(255));
    INSERT INTO @IndexInfo (TableName, IndexName, IndexColumnName)
    SELECT t.name AS TableName, i.name AS IndexName, c.name AS IndexColumnName /*, t.create_date, ic.*, c.* */
    FROM sys.tables t
        LEFT JOIN sys.indexes i ON t.object_id = i.object_id
        LEFT JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
        LEFT JOIN sys.columns c ON i.object_id = c.object_id and ic.column_id = c.column_id
    WHERE i.is_primary_key = 1
        AND i.type = ' + CAST(@IndexType as nvarchar(max)) + '
    ORDER BY t.create_date desc;
    DECLARE @t1 nvarchar(max);
    DECLARE @t2 nvarchar(max);
    DECLARE @t3 nvarchar(max);
    DECLARE @cmd nvarchar(max);
    DECLARE cur CURSOR FOR
    SELECT TableName, IndexName, 1 AS ExecOrder, ''''DROP INDEX '''' + IndexName + '''' ON '''' + TableName + '''';'''' FROM @IndexInfo I
    UNION ALL
    SELECT TableName, IndexName, 2 AS ExecOrder, ''''ALTER TABLE '''' + TableName + '''' ADD CONSTRAINT PK_'''' + TableName + ''''_'''' + IndexColumnName + '''' PRIMARY KEY CLUSTERED ('''' + IndexColumnName + '''')'''' + '''';'''' FROM @IndexInfo I
    ORDER BY 1,2,3 DESC;
    OPEN cur;
    FETCH NEXT FROM cur INTO @t1, @t2, @t3, @cmd;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @cmd;
        FETCH NEXT FROM cur INTO @t1, @t2, @t3, @cmd;
    END
    CLOSE cur;
    DEALLOCATE cur;
    '';
    EXEC ' + @Server + '.' + @Database + '.sys.sp_executesql @cmd;
';
PRINT @cmd;


texts: MongoDB 3.2.10 Index rebuilding did not complete: 11000 E11000 duplicate key error collection

texts:
MongoDB 3.2.10

Getting the following error when adding a replica set as WiredTiger. This is sharded cluster ( 3 shards nodes ).

2 Shards replica sets created using wiredTiger storage so only 1 shard is having a problem when creating the replica set.

2016-11-11T00:51:55.005+0000 I -     [initandlisten]   Index Build: 39291000/39372845 99%
2016-11-11T00:51:58.032+0000 I -     [initandlisten]   Index Build: 39345000/39372845 99%
2016-11-11T00:52:04.696+0000 E INDEX [initandlisten] Index rebuilding did not complete: 11000 E11000 duplicate key error collection: XYZ.ALBUM index: _id_                                                                         dup key: { : 59000012923579 }
2016-11-11T00:52:04.696+0000 I INDEX [initandlisten] note: restart the server with --noIndexBuildRetry to skip index rebuilds
2016-11-11T00:52:04.696+0000 I -     [initandlisten] Fatal Assertion 26100
2016-11-11T00:52:04.696+0000 I -     [initandlisten]

I did not have issue when I was using mmapv1 replica set but now I have dropped old replica set.

How to fix this issue?


texts: Log file is about to be larger than disk, query still executing

texts:
I have multiple databases with multiple millions of rows. I am trying to delete millions of those rows but am about to run into a problem.

My log files are getting huge and I am about to run out of disk space.

Database A has an .mdf file that is 10gb, and so far it's log file has grown to be over 100gb. It has been running the query for over 3 hours and I have no idea how much longer it will continue to run or how much larger that log file will grow.

What can I do about this? What happens to my query (that is still running!) if the log file consumes the entire disk?

I have a backup (shy the last 4 hours) of all the databases I am working with.

Any suggestions?


texts: Does Aginity Pro support support temporary credentials, assume role and multi factor authentication for connections for AWS Redshift

texts:
I am looking at Aginity Pro for connections to our AWS Redshift cluster. I can't find out if it supports the following:

Assume role. Our IAM users are in a single account, and we assume roles in multiple other accounts to access redshift clusters
Temporary credentials. We use get-cluster-credentials for users to obtain credentials for connecting to redshift.
MFA support. Our roles have a condition which require that the user was authenticated using MFA.


texts: When partitioning a huge table in postgres , how much is too much?

texts:
I have a huge table in Postgres with 10 million inserts per day, Since it's not feasible to keep all these data in a single table, we are splitting it based on the week. we are currently using Postgres table inheritance.
The weekly table currently has about 70 million rows and the data size is approximately 7 to 8 GB, This solution is not feasible long term as the number of data sources(devices) would increase in future.
What we are planning to do is to do declarative partitioning in Postgres and use the device id as the partitioning condition.
The idea is to divide the partition based on device id like so
0- 250 - 1st partition
250-500 - 2nd partition
..
..
..
45500 - 50000 - nth partition
But the problem is that we have to keep a minimum of 3 months' data, so the size of the partition will eventually add up to 100 million by the end of 3 months anyway.
I need to reduce the data in each partition, the two ways I'm planning to approach this issue is

reduce the number of devices in each partition
add a month constraint to partition

My question is if I do the above 2 methods the number of tables for 50,000 devices would be 500 and 2400 respectively, is that ok? or can I increase the number of partitions and get similar or improved performance.
This is running on a server with 32 cores and 256gb of ram.
Any help is appreciated.
Thanks


texts: In mysql, find all instances of a named column that are not foreign keys

texts:
This one has had me trumped for a while!

I want to run a query to retrieve all instances of a column (from information_schema, I guess) that ARE or ARE NOT foreign keys.

Now, let me back off a little and explain my real need. I have a rather large database dealing with company information. Now, I want to be able to delete (or update) a customer record, and have all related foreign keys affected by CASCADE. Now, if the foreign key constraints are not duly set everywhere, then a few records might be orphaned.

So, I want to first query to get a bird's eye view of my column. Now let's assume that all instances of this column have the same name "mycol".

I've taken note of the table REFERENTIAL_CONSTRAINTS, but got intimidated ;). One immediate constraint (pardon the pun) I've seen is: the field CONSTRAINT_NAME is populated with names like customer_ibfk_1, since the naming of FK constraints on this project was left for mysql to handle as default. This table has no field for "REFERENCED_COLUMN", and so I'm stumped.

Any help please!


texts:
does mongodb have any compiled list of non compatible functions on sharded cluster?
I was looking for a compile list of non compatible functions on sharded clusters, so we can anticipate the changes can be made on sharded clusters eg. db.collection.renameCollection() is not supported on sharded collections. You cannot rename views.
So if there are other functions that we can anticipate that will not be used on sharded cluster that will be helpful.


texts: Capturing the result(print statements) of SP_UPDATESTATS

texts:
I am creating a SQL Server Job to update the statistics once in a week. To create a Job I came up with the below code.

USE msdb ;
GO
EXEC dbo.sp_add_job
    @job_name = N'Weekly Statistics Update',
    @category_name=N'Database Maintenance' ;
GO
EXEC sp_add_jobstep
    @job_name = N'Weekly Statistics Update',
    @step_name = N'Updating statistics',
    @subsystem = N'TSQL',
    @command = N'DECLARE @SQL VARCHAR(8000)
DECLARE @DB_NAME SYSNAME
DECLARE STATSUPDATE_CURSOR CURSOR FORWARD_ONLY STATIC FOR
  SELECT Quotename([name])
  FROM   master.sys.databases
  WHERE  [name] NOT IN ( ''master'', ''tempdb'', ''model'', ''msdb'',
                         ''ReportServer'', ''ReportServerTempDB'' )

OPEN STATSUPDATE_CURSOR

FETCH NEXT FROM STATSUPDATE_CURSOR INTO @DB_NAME

WHILE @@FETCH_STATUS = 0
  BEGIN
      SELECT @SQL = Concat(''EXEC '', @DB_NAME, ''.sys.sp_updatestats;'')
                    + Char(13)

      --PRINT @SQL

      EXEC @SQL
      FETCH NEXT FROM STATSUPDATE_CURSOR INTO @DB_NAME
  END

CLOSE STATSUPDATE_CURSOR

DEALLOCATE STATSUPDATE_CURSOR ',
    @retry_attempts = 3,
    @retry_interval = 5 ; --5 minutes
GO
EXEC dbo.sp_add_schedule
    @schedule_name = N'Weekly Once statistics update',
    @freq_type = 8,  -- Weekly
    @freq_interval = 1, --Sunday
    @active_start_time = '1:00:00' ;  -- Sunday Morning 1 AM

USE msdb ;

GO
EXEC sp_attach_schedule
   @job_name = N'Weekly Statistics Update',
   @schedule_name = N'Weekly Once statistics update';
GO
EXEC dbo.sp_add_jobserver
    @job_name = N'Weekly Statistics Update';
GO

Is there a way to capture the result(Print Statements) of sp_executesql inside the Job. I searched for the answer but no luck is there a way to do this?


texts: Anybody know story behind pgFoundry (PostgreSQL project site) downtime?

texts:
pgFoundry site's been unreachable 2 days and counting....

I have googled up some ominous messages from the Spring about it eventually closing, but I had never seen any notice on the site to say that was imminent.

Any inside scoop?


texts: Is there any way RMAN can delete empty backup directories?


texts: Why does mysql show Primary key constraint on a View?

texts:
As far as I know views cannot have a primary key.
However the result of

SELECT FROM KEY_COLUMN_USAGE

says differ.

     PRIMARY            | S_MOBILE_ITEM_VIEW          | ID                 |
     ID                 | S_MOBILE_ITEM_VIEW          | ID                 |
     ID                 | S_MOBILE_ITEM_VIEW          | MOBILE_ID          |
     FKB654BB60D311E75  | S_MOBILE_ITEM_VIEW          | DEL_PACKAGE_ID     |

Are these really primary key and other constraints on a view ?
While my app uses Hibernate, this "primary key on a view" upsets Hibernate so it won't
let me do some O-R mapping.
Can I delete records from KEY_COLUMN_USAGE so that I can do some stuff without constraint violation that I don't believe acually exists ?

Oh ps
that S_MOBILE_ITEM_VIEW IS a view.

SELECT table_name, table_type FROM information_schema.tables WHERE table_schema ='****';

 S_MOBILE_ITEM_VIEW          | VIEW       |

EDIT
I thought it would look ugly so I didn't post the result of show create table but here we go

delimiter $$

CREATE ALGORITHM=UNDEFINED DEFINER=`yoursmart`@`***.***.***.***` SQL SECURITY DEFINER VIEW `S_MOBILE_ITEM_VIEW` AS select `SM`.`MOBILE_ID` AS `MOBILE_ID`,`SM`.`NAME` AS `MOBILE_NAME`,`SM2`.`NAME` AS `MOBILE_OWNER`,`SIH`.`ID` AS `ID`,`SIH`.`NAME` AS `NAME`,`SIH`.`NAME_ENG` AS `NAME_ENG`,`SIH`.`MANUFACTURER` AS`MANUFACTURER`,`SIH`.`MANUFACTURER_ID` AS `MANUFACTURER_ID`,`SIH`.`PRICE` AS `PRICE`,`SIH`.`IS_ONSALE` AS `IS_ONSALE`,`SIH`.`LIMIT_AMOUNT` AS `LIMIT_AMOUNT`,`SIH`.`ROUND_SCALE` AS `ROUND_SCALE`,`SIH`.`PARENT_ID` AS `PARENT_ID`,`SID`.`BODY` AS `BODY`,`SID`.`REFUND_NOTICE` AS `REFUND_NOTICE`,`SID`.`DELIVERY_NOTICE` AS `DELIVERY_NOTICE`,`SID`.`DETAIL` AS `DETAIL`,`SID`.`DESCRIPTION` AS `DESCRIPTION`,`SID`.`MSG_USE` AS `MSG_USE`,`SID`.`LINK_MSG` AS `LINK_MSG`,`SMIO`.`ORDER_LEVEL` AS `ORDER_LEVEL`,0 AS `LIKEITEM`,`SIH`.`DELIVERY_TYPE` AS `DELIVERY_TYPE`,`SIH`.`DEL_STANDAR_AMOUNT` AS `DEL_STANDAR_AMOUNT`,`SIH`.`DEL_PACKAGE_ID` AS `DEL_PACKAGE_ID`,`SIH`.`DEL_QTY` AS `DEL_QTY`,`SIH`.`DEL_AMOUNT` AS `DEL_AMOUNT`,0 AS `PACKAGE_AMOUNT`,(select count(0) AS `COUNT(*)` from `S_MOBILE_ITEM_ORDER` where (`S_MOBILE_ITEM_ORDER`.`ITEM_ID` = `SIH`.`ID`)) AS `SPONSORCNT`,(select count(0) AS `COUNT(*)` from `S_LIKE_ITEM` where (`S_LIKE_ITEM`.`ITEM_ID` = `SIH`.`ID`)) AS `LIKEITEMCNT`,(select count(0) AS `COUNT(*)` from `S_ITEM_REPLY` where ((`S_ITEM_REPLY`.`ITEM_ID` = `SIH`.`ID`) and (`S_ITEM_REPLY`.`MOBILE_ID` = `SM`.`MOBILE_ID`))) AS `REPLYCNT`,(select count(0) AS `COUNT(*)` from (`S_ITEM_CATEGORY` `SIC` join `S_MOBILE_CATEGORY_ITEM_VIEW` `SMCIV`) where ((`SIC`.`ITEM_ID` = `SMCIV`.`ID`) and (`SIC`.`CATEGORY_ID` = 1) and (`SIC`.`ITEM_ID` = `SIH`.`ID`) and (`SMCIV`.`MOBILE_ID` = `SM`.`MOBILE_ID`))) AS `BRAND_CNT` from ((((`S_ITEM_HEAD` `SIH` left join `S_ITEM_DETAILS` `SID` on((`SIH`.`ID` = `SID`.`ID`))) join `S_MOBILE_ITEM_ORDER` `SMIO` on((`SIH`.`ID` = `SMIO`.`ITEM_ID`))) join `S_MOBILE` `SM` on((`SM`.`MOBILE_ID` = `SMIO`.`MOBILE_ID`))) join `S_MEMBER` `SM2` on((`SM2`.`ID` = `SM`.`MOBILE_ID`))) where ((now() between `SIH`.`EXPOSURE_BEGIN` and `SIH`.`EXPOSURE_END`) and (`SMIO`.`DISPLAY` = _utf8'1') and (`SIH`.`APPROVAL` = _utf8'1'))$$


texts: mysql duplicate entry error 1062 when restoring backup

texts:
Sorry, I seen similar threads but I still couldn't find it addressing my issue plus, I needed some more info on this.

Requirement: To create an exact replica 'db4' of an existing DB 'db3'.

Procedure followed:

mysqldump -uuser -ppass db3 > db3.sql (size is 6G)
mysql -uuser -ppass db4 < db3.sql (db4 was a newly created blank database)

The 2nd step throws in the error:

ERROR 1062 (23000) at line 5524: Duplicate entry '600806' for key 1"

I ran the 2nd step  again with --force. The restore completed but with 2 additional similar errors:

ERROR 1062 (23000) at line 6309: Duplicate entry '187694' for key 1
ERROR 1062 (23000) at line 6572: Duplicate entry '1567400' for key 1

On completion when I queried certain tables of db4 database, I was able to see missing records.

Question:

Does this indicate a corrupted/problematic db3 database?
How to proceed to create a 'consistent/working' replica (db4) of db3?
If (2) fails, how to possibly troubleshoot and find the reason behind why it occurs?

Thanks,


texts: Resume Logshipping after Primary database restored

texts:
We have Database (D1) as a primary database and Log shipping setup to the secondary database called (LS_D1). Now what we are doing is taking backup of D1 database and restore it on D2 database. We do schema and data changes for new Release deployment on to D2 database.

What we want is to restore D2 database on D1 database and resume the log shipping to the LS_D1 database without copying backup file. Copying a backup file from primary data center to DR data center cost us too much money for every Release so that's why we want to resume the Log shipping.

When i tried to do this LSN numbers got out of sync, is there any way that we can solve this problem ? or can we have any other alternative to Log Shipping that can provide us DR capability and solve this problem ?

Note: Any solution that can require one time cost of log shipping would also be fine.

Thanks,
Piyush Patel


texts: MongoDB replica index filled hard drive stuck in fatal

texts:
I have a 3 server replica set deployed by Googles 1 Click Deploy functionality, the problem is I created an index on a particularly large collection (100,000,000 documents) and it consumed the whole hard drive and caused Mongo to crash and stay crashed.

I have deleted a 30GB log file and freed up some other space and tried to restart the mongo process on both the primary and secondary servers but the secondary keeps falling into fatal even after getting it to rollback.

I've run rs.reconfig() and it seemed to start the rollback process but it just fell back into FATAL and now it won't budge unless I restart the process and it goes in circles.

To make matters worse, the data (due to connection/training issues) sits only on this busted instance and is not spread across the replica.

I've tried to also run rs.slaveOk() and remove the offending index but I can't query without getting told I can't and obviously when it's in ROLLBACK I can't query for anything because of the lock.

Is there any way I can remove the offending index, repair and resync to fix this while it's in this state?

Thanks.


texts: Is there a way to get the query results (rows affected/time taken) after every query

texts:
I run multiple MySQL queries together in phpMyAdmin, I basically want to be able to see the results of each query (rows affected/time taken etc) as it completes. Say I have the following sample queries

UPDATE PC1 SET `A1` = 'C1' WHERE `A1` = 'B1';
UPDATE PC1 SET `A2` = 'C2' WHERE `A2` = 'B2';
UPDATE PC1 SET `A3` = 'C3' WHERE `A3` = 'B3';

When I run these and say a few more what happens is at the end of the third/last query I get the results together

163 rows affected. (Query took 0.0015 seconds.)
445 rows affected. (Query took 0.0065 seconds.)
779 rows affected. (Query took 0.0215 seconds.)

I currently use SHOW FULL PROCESSLIST but I would like to be able to see the query results as they complete.

Is there a way to for me to run all of the queries together and see the query results as they complete?

I want to run the above three update queries. I can run it one by one, run the first one and wait for it to complete, review the rows affected/time taken and then run the second and wait for it to complete and the same for the third. Alternatively, I can just write all three together and wait for all of them to run and complete. I am doing the second, however what happens is all of the three run together and at the end of the third, I see the rows affected for all three together. I am trying to see if I can see the output of each query as it completes. In reality, I have tens of queries.

I get all three stats, however I get all of them at once on completion of the third query. I am trying to see if I can get the stats as they complete, say first query completes and I get the stats on screen instead of waiting for all three to complete and then seeing all three stats at once.


texts: Restore non-cdb backup into pdb database

texts:
I haven't found any information on is it possible to restore a Oracle non-cdb 19.8 backup into a container-database PDB (still 19c) , or not.
The method i have found is not suitable for my situation (convert non-cdb to pdb using DBMS_PDB.DESCRIBE - that seems to imply that the database type is converted + all data is copied during this operation, which is not the same as restoring from a backup).
*Export/import/Golden Gate are not viable options for me.
Has anyone had a similar challenge and what was the best way to get a non-cdb to get into pdb form.


texts:
I am using sub queries using joins. For 11946 rows, the query is taking 18 seconds.

Select CONVERT(VARCHAR(10), [Date], 101) as schedDate, ClientName,
Coalesce(Error1, '')
+ CASE WHEN Error1 is not null and Error2 is not null THEN ' | ' else '' END
+ Coalesce(Error2, '')
+ CASE WHEN Error2 is not null and Error3 is not null THEN ' | ' else '' END
+ Coalesce(Error3, '') as ErrorList
From(
select [Date], ClientName,
CASE WHEN SchedPayor <> EligPayor THEN 'Payor No Match' END as Error1,
CASE WHEN EligPayor IS NULL THEN 'No Eligibility' END as Error2,
CASE WHEN StaffName IS NULL THEN 'Invalid Staff' END as Error3
From
(Select
       [Date],
       s.clientID,
       c.DisplayName [ClientName],
       si.PayorID [SchedPayor],
       ce.PayorID [EligPayor],
       stf.DisplayName [StaffName]
From Schedule s
       INNER JOIN ScheduleItem si ON s.ScheduleID = si.ScheduleID and s.AccountID = si.AccountID and si.Owner = 1
       INNER JOIN Client c ON s.ClientID = c.ClientID and s.AccountID = c.AccountID
       OUTER APPLY                                                                 (Select ClientID, PayorID From ClientEligibility ce               Where s.ClientID = ce.ClientID and s.[Date] BETWEEN ce.StartDate and ce.EndDate) as ce
       LEFT JOIN Staff stf ON s.StaffID = stf.StaffID and s.AccountID = stf.AccountID
WHERE
       s.accountID = 1) as a) as b

Is there any other way to write query to optimize performance?


texts: How can you save all user-defined functions to txt files on your local hard drive?

texts:
I am running SQL Server 2008 and need to back up the UDF scripts/queries that I have created. How can this be done in a timely manner? I tried right clicking on the folder icon where the UDF are saved (Table-Valued Functions) to copy the folder but am not bale to.

The only thing I can think up is using the MODIFY command on each and every single one my UDF scrips and copying and pasting to separate text files.

Even If if there is only  a simple way to save ALL the Scripts in one text file instead of separate txt files I would be more than happy to do that instead of having to copy and paste every single file one at a time.

Any suggestions?


texts: SSRS Rectangle visibility based on Parameter Value

texts:
I am creating a Report in SSRS.
Its a Dynamic 2011 CRM Report.
I have created some Tables.
And some Rectangles.
Each Rectangle contains some Tables.
Also there is a Parameter that contains value from 1 to 4.
It is a multiple values parameter.
The goal is that the user select parameters( one or more ) and then tables based on the rec visibility condition appear.( Maybe two rec appear, or maybe all of them)
We all know that there is IN operator in SQL Syntax.
But I do not know how to make it here.
I googled a lot but no success.
Here is what I tried in rec visibility.

=Parameters!type.Value.Equals("1")

And in the second table :

    =Parameters!type.Value.Equals("2")

till the last rectangle.

Even changed to :

=Parameters!type.IsMultiValue.Equals("1")

I Also Put both of them in =iif, but no success.
How can I perform this?
Visibility of rectangle based on the multiple values parameter.
Any help will appreciated.


texts: How to Best Implement nearest neighbour search in mysql?

texts:
So, in short,

What should be the data type of latitude and longitude?
What SQL command I should call to get the first 100 nearest restaurants for example?

Detail:

I have 100k biz record each with lattitude and longitude. I see that MySQL actually support a data type called point. Should I use that instead?

Does MySQL support KDTree storage system http://en.wikipedia.org/wiki/File:KDTree-animation.gif

Is it best to use point data type rather than regular float data type to store latitutude and longitude?

Eventually I want to find things like the first 100 restaurants closest to points 105,6 for example and my databases contains a lot of biz and points. Obviously computing the distance one by one for every records and for every points would be O(n) and hence sucks.

Notice that I am aware of a simpler solution described in How do Application Like Yelp Retrieve distance information from data base efficiently and will implement that my self too for a start. That's a good answer.

However, I think there is one creme of the crop answer that should outperform that right? In fact, storing location based on latitude and longitude and finding stuffs nearest to it is a very common problem I expect mysql to have a special design pattern for that. Does it have that?

Where can I learn more about it? Thanks.


texts: Can rebuilding indexes cause worse performance after the rebuild is finished?

texts:
We have a customer database that is heavily fragmented - practically every table with more than 1000 pages has >95% fragmentation. Fill factors are set to sensible values, but page space usage is nowhere near to fill factor for most tables.

This is the result of no maintenance being performed on the database.

Rebuilding the indexes using Ola Hallengren's IndexOptimize reduces fragmentation as expected. On the existing production hardware, performance of the application improves as expected. All the metrics I normally use - client statistics on heavy queries, profiler durations, read/write stalls, application logs and user perception - indicate performance is improved.

However, a new database server backed with Intel PCIe SSDs is showing the opposite of what we expect. Highly fragmented, the application performs well. After rebuilding indexes, the application performs badly. Some operations that took ~90s now take ~6mins. However, none of the other metrics appear to indicate that the system is going slower.

Is this something anyone else has experienced?


texts: What does "ALTER DATABASE ... SET NEW_BROKER" do? Is there anything to worry about?

texts: I'm having an issue with sql broker service. Is there a reason to worry about setting a new sql broker service? I'm really worried about doing this, but I'm kind of at my wits end since I know it will clear out the message queue to send out, but is this the only thing I need to worry about? Is there anything else that I should be looking for or that has to be done when I do run this statement?


texts: why pg_restore ignores --create ? Error: failed: FATAL: database "new_db" does not exist

texts:
I am trying to run following command:

sshpass  -p "pass" ssh  x@1.2.3.4 "pg_dump -Fc -U foo some_db" | pg_restore --create --dbname=new_db

I get:

failed: FATAL:  database "new_db" does not exist


texts: Usage of aggregate function with when self-joining a table

texts:
I want to find people from a database who has a weight bigger then the average weight:

SELECT *
FROM Player p1, Player p2
WHERE p1.weight > avg(p2.weight)

But I have an error:

Result: misuse of aggregate function avg()

I know that I can write:

SELECT *
FROM Player
WHERE weight > (SELECT AVG(weight)
                FROM Player)

But how can I achieve the same by using self join and without subquery?


texts:
I have the following query:

SELECT DISTINCT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink
FROM director_movie as d LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId
WHERE d.castName LIKE '%castname%'

for some names it works well, but for some other it takes a long time to retrieve results..

film_info is a view containing 100,000 rows.
The structure is: movieName, ImdbId, year, rate, posterLink(link to image of movie)

director_movie: is a table containing 2,259,630 rows.
The structure is: Id(unique), castName, castImdbId, movieName, movieImdbId

The goal of this query join is to retrieve list of movies by each director.
I defined castName and movieImdbId as indexes (in order to speed up joins and read from table), but actually I am a bit confused about how to chose an appropriate index. (If I have to use it in my case..). I read one of the factor to see if index is useful is cardinality which I am also confused with this term.. because it change when I add another index to my table..
I tried different combinations of columns for indexes but none of them speed up my query results..

Could someone kindly explain me what is the best way to improve the performance of my query (in terms of time)?

Thanks in advance


texts: ORA-22285 Error while trying to register an xsd schema on oracle database

texts:
Here is the code for user "system"

create or replace directory xml_mape as 'D:\app\Gusts\xml_mape';
GRANT READ,WRITE ON DIRECTORY xml_mape to lietotajs;

Im trying to access the xsd file "dok1_shema.xsd"on the directory "xml_mape"
And here is the code for user "lietotajs"

begin
  DBMS_XMLSCHEMA.REGISTERSCHEMA(
    SCHEMAURL => 'http://localhost:8080/public/dok1_shema.xsd',
    SCHEMADOC => bfilename ('xml_mape', 'dok1_shema.xsd'),
    LOCAL     => TRUE,
   GENTYPES  => FALSE,
   GENTABLES => TRUE,
   OPTIONS   => DBMS_XMLSCHEMA.REGISTER_BINARYXML);

  end;

I get the following error:

Error starting at line : 1 in command -
begin
  DBMS_XMLSCHEMA.REGISTERSCHEMA(
    SCHEMAURL => 'http://localhost:8080/public/dok1_shema.xsd',
    SCHEMADOC => bfilename ('xml_mape', 'dok1_shema.xsd'),
    LOCAL     => TRUE,
   GENTYPES  => FALSE,
   GENTABLES => TRUE,
   OPTIONS   => DBMS_XMLSCHEMA.REGISTER_BINARYXML);

  end;
Error report -
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 744
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 131
ORA-06512: at line 2
22285. 00000 -  "non-existent directory or file for %s operation"
*Cause:    Attempted to access a directory that does not exist, or attempted
           to access a file in a directory that does not exist.
*Action:   Ensure that a system object corresponding to the specified
           directory exists in the database dictionary, or
           make sure the name is correct.

Nothing i try to change seems to have any impact.


texts: sync 2 tables in a single database in mysql with no duplicacy


texts: Combine and display multiple row data with same column data in MySQL

texts:
I have a menu, from where every item ordered is stored in a row. For a single order, there can be multiple rows.What I want is that when I display it, for same order_number ( number) all rows data is displayed as one. Here is a picture describing the situation better

I have used this so far

$sql = "select * from chef_orders";

$result = $conn->query($sql);
  //  $ro=mysqli_query("select * from menu");

and for displaying simple fetch function method in while loop


texts: How to get the greatest element of a GROUP BY?

texts:
As you can see I would like to get the latest purchase order for each SKU.

Sample Data

I have this data set,

CREATE TABLE PurchaseOrders (
        id            int  PRIMARY KEY AUTO_INCREMENT,
        sku           varchar(6),
        purchase_date date
);

INSERT INTO PurchaseOrders VALUES
    ( 1, 'ABC123', '2017-12-23' ),
    ( 2, 'ABC123', '2016-11-11' ),
    ( 3, 'DEF456', '2011-01-03' ),
    ( 4, 'DEF456', '2011-10-21' ),
    ( 5, 'GHI789', '2017-01-23' ),
    ( 6, 'GHI789', '2017-11-21' );

Desired Result

1 | ABC123 | 2017-12-23
4 | DEF456 | 2011-10-21
6 | GHI789 | 2017-11-21


texts: How and when does SQL Agent update next_run_date/next_run_time values?

texts:
I've been working on code in T-SQL to add new schedules to a SQL Agent job  using the sp_add_jobschedule proc in the msdb database.  When I add a new schedule (typically a run-once at a specific date/time) and immediately look at the values in sysjobschedules and sysschedules, I can see that the new schedule has been added and is tied to the job_id for my SQL Agent job. However, the values for next_run_date and next_run_time have 0 in them.  When I come back and look at them again in 2 or 3 minutes, they still show 0's in them.  However when I come back another 5 or 10 minutes later, it now correctly shows the date and time values corresponding to the next scheduled run.

So my questions are:

How often do these values get updated?
What process is it that updates these values?
If I were to add a schedule that was, say, 1 minute in the future, does that mean that the job will not run since the next_run_date/time haven't been updated yet?

Example of the code I use to add a new schedule:

exec msdb.dbo.sp_add_jobschedule @job_id = @jobID
                    , @name = @JobName
                    , @enabled = 1
                    , @freq_type = 1
                    , @freq_interval = 0
                    , @freq_subday_type = 0
                    , @freq_subday_interval = 0
                    , @freq_relative_interval = 0
                    , @freq_recurrence_factor = 0
                    , @active_start_date = @ScheduleRunDate
                    , @active_end_date = 99991231
                    , @active_start_time = @ScheduleRunTime
                    , @active_end_time = 235959

where @jobID is binary(16) that holds the job_id of the job in question, @ScheduleRunDate and @ScheduleRunTime are INTs with the date and time respectively.


texts: How to model the relationship between these three tables

texts:
This is my first database, it is for a nutritional info meal tracker program. I have created a model in mySql work bench but I'm not sure if I have the relationships right. See image below..

My thinking is that each user would have a new table for each day and each day would have multiple meals. I was thinking that for each day the meal number would go back to 1 as it is the primary key. Would this be a problem i.e. have multiple meals with the same number or would I be able to identify them from the day and the meal number and if so how would I do that? I'm not sure if this is even the best way to design this database so any help would be appreciated. Cheers.

From  Mr.Brownstone's comment I've come up with this new design. Is it possible to have the user_name in MEALS automatically filled in from USER?


texts: How to get last identity row Inserted when using instead of trigger

texts: When I insert into tables using instead of triggers, @@Identity, IDENT_CURRENT('Table') and SCOPE_IDENTITY() return null. How I can get the last identity of inserted row?


texts: What is the distinction between ALL_PROCEDURES and DBA_PROCEDURES, and other schema tables?

texts:
I recently failed to find a known procedure when searching ALL_PROCEDURES, and on a whim tried DBA_PROCEDURES: lo, there it is.  The doc pages (ALL_; DBA_) both say "...lists all functions and procedures along with their associated properties."  Oracle seems to have a curious definition of "all".

I'm posting this on DBA rather than SO, though it's a pretty basic question, on the basis that schema management is generally a DBA role rather than a developer role.


texts: How to sum measure value for all records between two dimension values

texts:
I have a cube, called metric_cube, with a measure of Metric and a dimension Week with the given output on SSAS from SQL Server 2014.

Current Output:

Week  Metric
----  ------
 1      6
 2      5
 3      4
 4      3
 5      2

I want to get the cube to output the sum of the Metric for all combinations of a from Week and to Week, where the from Week <= the to Week.

Desired Output:

Week(F)  Week(T)  Metric
-------  -------  ------
  1         1        6
  1         2        11
  1         3        15
  1         4        18
  1         5        20
  2         2        5
  2         3        9
  2         4        12
  2         5        14
  3         3        4
  3         4        7
  3         5        9
  4         4        3
  4         5        5
  5         5        2

I have tried using the same dimension twice, but I get a cross join error. I have also tried creating two dimensions and applying both of them, but all that does is duplicate the Metric value from the current output for all to Weeks without consideration of the desired constraint for the from Week <= the to Week

Is it possible to use a data cube to achieve the desired output? If so, how?


texts: SQL Select query to include each row a number of times

texts:
This is probably easy to do (I hope), but my experiments with OUTER JOIN and CROSS JOIN etc do not give the results I am after, so here's the question:

I need to do do a projection on fields in a table for each day of the week, where the day of the week represents a certain weight. I assumed a number table would help, but how?

Input table:

Key  prc   fixweight
1   23.4   0.2
2   78.1   0.6
3    8.7   0.3

Required output (left out: weight calculation on the columns, based on the day of the week):

1   23.4   0.2   1
1   23.4   0.2   2
1   23.4   0.2   3
1   23.4   0.2   4
1   23.4   0.2   5
1   23.4   0.2   6
1   23.4   0.2   7
2   78.1   0.6   1
2   78.1   0.6   2
2   78.1   0.6   3
2   78.1   0.6   4
2   78.1   0.6   5
2   78.1   0.6   6
2   78.1   0.6   7
3    8.7   0.3   1
3    8.7   0.3   2
3    8.7   0.3   3
3    8.7   0.3   4
3    8.7   0.3   5
3    8.7   0.3   6
3    8.7   0.3   7


texts: Search for multiple keywords related to separate columns on a single table

texts:
Imagine I have a table with the schema shown. Each of the columns represent a searchable collection that I would like to be picked up in the search. I am looking for a method that would derive the best match for the group of keywords entered into the search for by the user. Any example code would be highly appreciated.

    [JobID] [int] IDENTITY(1000000,1) NOT NULL,
    [PersonID] [int] NOT NULL,
    [CityID] [int] NOT NULL,
    [ClassificationID] [int] NOT NULL,
    [SubClassificationID] [int] NOT NULL,
    [JobTitle] [nvarchar](50) NOT NULL,
    [WorkType] [nvarchar](50) NOT NULL,
    [Position] [nvarchar](100) NOT NULL,
    [Keyword] [nvarchar](max) NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
    [Salary] [nvarchar](50) NOT NULL,
    [SalaryType] [nvarchar](50) NOT NULL,


texts: Restoring multiple differential backups SQL Server 2008

texts:
Over the bank holiday the backups failed on a db the full has been restored and now I have several differentials to be restored on top.

Could someone give best method or do I restore just sequentially one after the other in  T-SQL? Are there pitfalls that I should be aware of prior to doing  this.


texts:
I try the following SQL query :

SELECT FirstName, SecondName FROM Persons WHERE CONTAINS(FirstName, 'Mat')

and I got this error :

  Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Person'
  because it is not full-text indexed.

Does I need to index the FirstName column ? What's wrong ?


texts: On a SQL Availability Group what IP/network carries the workload of moving changes to the secondary replica and how do you find this network?


texts:
I need to aggregate incoming data and save the resulting aggregates to a table. A sample of the data is below.

id,time
1967,2014-04-12 00:42:55+00
1967,2014-04-12 00:42:51+00
1972,2014-04-12 00:42:51+00
1972,2014-04-12 00:42:49+00
1972,2014-04-12 00:42:40+00

I need to do a simple aggregation:

SELECT id, MIN(time),MAX(time),COUNT(id) FROM xx GROUP BY id

I've tried a few things:

Directly updating a table each time a piece of data comes in. Was too slow of course.

Saving the incoming data to a temporary table and periodically running an aggregation query similar to the one above and then saving the result to a table. This approach was better but proved too much for my disks as volume increased.

My current approach is a daemon program that reads incoming data, aggregates it in memory and then saves the results to a table based on some time criteria. This approach works much better but still cannot scale as much as I'd like.

I am using Postgres 9.1 on a 4-disk RAID 10 system with 15k SCSI drives and 32gb of RAM. There is no disk partition, WAL and everything is on one disk. I realize this is a large performance hit. Hardware changes are problematic at this point.
This system cannot keep up with what I need to do. At the moment I need to write thousands of these aggregates per second and the solution I'll choose has to scale to much more.

I have been exploring distributed database options and I like the ideas behind Cassandra. However I am not up to speed on all the options as I'm primarily a software developer.
Moving to a distributed database in the cloud seems like a good option for a next step. Being able to sort by the min,max and count columns would be ideal but I can do that in the application layer if I have to.

Another important note is that the result of these data aggregations are actually rarely needed and are only for human consumption. A tiny fraction of these will actually be viewed. I tried generating the aggregations realtime (by querying a table that has the raw data) but they ended up with sequential scans on large tables (100m+ rows). If aggregating this much data and having most of it not seen is nonsensical I'd be happy to hear of a way to generate them upon request. My SQL skills are basic it is possible I am missing techniques that will help.

Here's some relevant parts of my postgres config:

shared_buffers = 22000MB
temp_buffers = 256MB
work_mem = 896MB
maintenance_work_mem = 16MB
fsync = off
wal_buffers = 64MB
checkpoint_segments = 128
checkpoint_timeout = 60min
checkpoint_completion_target = 0.9
random_page_cost = 2.0
effective_cache_size = 8192MB
default_statistics_target = 200
autovacuum_max_workers = 6
autovacuum_naptime = 15s
autovacuum_vacuum_threshold = 25
autovacuum_analyze_threshold = 10
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 1000

So my questions:

How is my postgres config for these tasks?
What kind of limits for this task am I looking at with my current hardware?
Are there distributed databases well suited to my needs?


texts: Options to include with mysqldump when taking backup of hybrid databases

texts:
I'm planning on taking backups of all MySQL databases on a remote production server. The combined database size is around 2 GB. Most of the tables are InnoDB ~1.8 GB and the rest are MyISAM.

Now I've gone through most of the questions on Serverfault and DBA.Stackexchange about taking backups of hybrid databases and almost all opinions are about third party tools like Percona Xtrabackup or about converting MyISAM tables into InnoDB and then running mysqldump with --quick --single-transaction options or taking backup from a replication system.

In my case not all databases are replicated on a slave system hence I cannot run mysqldump on a slave machine, also I'll be running mysqldump nightly either via a script or as a cronjob and I know that acquiring a global read lock all the databases while taking mysqldump won't be a problem as the number of DML statements executing at that time of the night will be extremely low.

So I'm thinking about including following options with mysqldump and I'd really appreciate if someone could help verify/rectify these options.

--extended-insert  --all-databases  --add-drop-database --lock-all-tables  --log-error=file_name --skip-opt


texts: In the database properties in Visual Studio there is a checkbox for encryption. I'm not sure if I check this if it will enforce encryption when doing a DACPAC deploy. What I would really like to do is enable encryption per environment. Ideally I would add a setting to my dacpac publish profile that determines whether or not encryption will be enabled on the database when publishing. Currently TDE gets disabled every time I do a publish.


texts: How to avoid downtime during postgresql upgrade

texts:
Im having a production PostgreSQL server 9.2, it has a few databases around 150GB. I want to upgrade this to 9.6, so I decided to upgrade this on QA server.

I have installed PostgreSQL 9.6.

Then block all the incoming connections.

Drop 9.6's cluster.

And upgrade 9.2's cluster to 9.6.

Everything is fine. I did this upgrade successfully.

But the thing is it took 7hrs to upgrade the databases, So here are my questions,

1.How can I avoid this huge downtime?

2.Any other ways to upgrade with minimal downtime?


texts: Check entry based on entry in another column

texts:
Still fairly new to databases and SQL, so bear with me. I'm trying to check whether or not data can be inserted depending on data in another column,

create table Table1
       (PaymentType varchar(5) not null
       check (PaymentType = 'CC' or PaymentType = 'PO' or PaymentType = 'Check'),
       CCNumber varchar(16)
       check (PaymentType = 'CC' and (LEN(CCNumber) = 15 or LEN(CCNumber) = 16) ),
       PONumber varchar(30)
       check (PaymentType = 'PO'),
       CheckNumber varchar(10)
       check (PaymentType = 'Check') );

This probably isn't the way to go about it, but I want CCNumber to accept data if PaymentType is 'CC', PONumber if PaymentType is 'PO', etc. How would I create those checks or constraints properly?


texts:
I have been working on a few stored procedures that have conditional parameters to them, but one of them is giving me a problem that I can't quite figure out.  This is the code for the procedure:
CREATE PROCEDURE dbo.GetTableData(
    @TblName   VARCHAR(50),
    @Condition VARCHAR(MAX) = NULL,
) AS
BEGIN
    IF(EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TblName))
        BEGIN
            DECLARE @SQL NVARCHAR(MAX) = N'
            SELECT * FROM @TblName WHERE 1=1'
            + CASE WHERE @Condition IS NOT NULL THEN
            ' AND ' + @Condition ELSE N'' END

            DECLARE @params NVARCHAR(MAX) = N'
                @TblName   VARCHAR(50),
                @Condition VARCHAR(MAX)';

            PRINT @SQL

            EXEC sys.sp_executesql @SQL, @params,
                @TblName,
                @Condition
        END
    ELSE
        RETURN 1
END

The way I would like the procedure to work, is that it's suppose to allow me to do quick table look ups.  So if I want to see everything from my Parts table, I'd just run
EXEC GetTableData 'parts'

Or if I wanted to see everything in the Parts table with a specific supplier I'd run
EXEC GetTableData 'parts', 'supplier LIKE ''A2A Systems'''

Now in the above example, when I run it, the PRINT @SQL line prints out the query as:

SELECT * FROM @TblName WHERE 1=1 AND supplier LIKE 'A2A Systems'

So the query it being put together properly (it seems).
However after it prints I am getting the following error:

Msg 1087, Level 16, State 1, Line 4
Must declare the table variable "@TblName"

I still get this error if I change the EXEC line to:
EXEC GetTableData @TblName='parts', @Condition='supplier LIKE ''A2A Systems'''

So what am I doing wrong here?  Why isn't it taking my @TblName variable value?


texts: SQL Server DeleteFolder Job ran successfully but it doesn't deleted the folder

texts:
I have a job to delete past 15 days folder in SQL Server. The job run successfully and delete the folder in local SQL Server.

But it runs successfully and does not delete the folder in another SQL Server which is accessed by remote desktop connection.

Please suggest how to solve it. Is it permission problem?

The following is the code I use

{
--Script to enable the XP_CMDSHELL
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

DECLARE @path VARCHAR(256) -- path for backup files

DECLARE @cmd VARCHAR(8000)

DECLARE @folderName VARCHAR(256) -- filename for backup

SET @folderName = + GETDATE()-15 --(CONVERT(varchar(10), GETDATE()-15, 'yyyy_mm_dd'))  -- 15 days back date folder name
SET @folderName =  CONVERT(varchar(4),YEAR(@folderName)) + '_' + Right('00' + CONVERT(varchar(2),MONTH(@folderName)),2) + '_' + Right('00' + CONVERT(varchar(2),DAY(@folderName)),2)
SET @path = 'D:\FTP-Snapshots\' + @folderName -- Folder path
SET @cmd = 'RD ' + @path + ' /S /Q' -- Delete Files and the Folder IF all files deletions were successful.

EXEC master..xp_cmdshell @cmd
}


texts: I'm about to create a RMAN catalog database for my setup, what are the general precautions or major pre-requisites as in DB parameters, compatibility,etc that I should take care while creating the catalog database?


texts: We are having a database in which one table name dbo.ONOA is corrupt. We need to delete this table. Please let me is there any way to force delete the table?


texts:
i have a table which has only two column one is pid(Photo ID) and another one is tid(Tag ID)

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| pid   | int(10) | NO   |     | NULL    |       |
| tid   | int(10) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

this table will store a photo id with a tag id, but i am facing a problem and i guess which is against normalization as well, here is my table data.

+-----+-----+
| pid | tid |
+-----+-----+
|  10 |   1 |
|  10 |   2 |
|  10 |   3 |
|  10 |   4 |
|   0 |   6 |
|   0 |  10 |
|   0 |  10 |
|  10 |   1 |
|  10 |   2 |
|  10 |   3 |
|  10 |   4 |
+-----+-----+

it store many duplicate values, how to get rid of this?I mean, how i can store this types of row only for one time: |  10 |   1 |
I have tried with UNIQE but this will going to store a photo id or a tag id only for one time?I'm using PHP
Thanks


texts: Unable to connect to MongoDB replica set using public IPs

texts:
I have configured a three-node replica set in MongoDB. I am able to connect to each server and replication is in sync. But when I'm trying to connect to them as a replica set from a public IP, I am getting an error:

  No primary detected for set rs0

But I am able to connect to the replica set from private IPs.

mongo --host rs0/52.X.X.X:27017,52.X.X.X:27017,52.X.X.X:27017 -uroot -pabc123 --authenticationDatabase admin this public IP not able to connect

mongo --host rs0/10.X.X.X:27017,10.X.X.X:27017,10.X.X.X:27017 -uroot -pabc123 --authenticationDatabase admin

With these private IPs we are able to connect to the replica set.


texts: Show master status returns empty set of rows

texts:
Hi currently I am working on implementing data replication on MYSQL for my project. While working on master file configurations on windows when I am trying to check the status of master, it returns empty set. It means it is not locating mysql-bin file and its position. I have made changes in ini file but the changes are not getting reflected. Can you please advise how I can enable binary loggings in windows configuration Please review and advise

Currently it shows like this

It should be like this


texts: Using Powershell can you insert a macro into Access?

texts:
I am using PowerShell to automate an export of an access databse (.accdb) to postgresql. As a step of that I am using a VBA script to convert all the table and column names to lowercase so they play nicer with postgresql queries.

I would like to insert the macro that would perform this task after I copy the database to a local location. This is prevent the cleanup script ever being run on the production database.

My question is, using powershell is it possible to insert  a macro into an access database? I can find documentation on how to do this with excel but as far as I can tell it doesn't translate to access.

Edit: Alternatively a macro that can create another macro with a given input or a macro that will execute code fed to it via an input parameter.


texts: How can I improve performance of update query in PostgreSQL

texts:
I have a table with about 80 million records. I have added a new attribute (heigth_category) which I want to fill based on another attribute (heigth).
If the heigth is between 0 and 6, I want to fill the heigth_category attribute with 1.

UPDATE schema.table t1 SET heigth_category = '1'
WHERE heigth >= 0
AND heigth< 6;

This took about 13 hours. Is that normal for such a query?

The explain gives this:

Update on bomen t1  (cost=13353.34..1932816.30 rows=579346 width=316)
  ->  Bitmap Heap Scan on bomen t1  (cost=13353.34..1932816.30 rows=579346 width=316)
        Recheck Cond: ((boom_hoogt >= 0::numeric) AND (boom_hoogt < 6::numeric))
        ->  Bitmap Index Scan on boom_hoogt_idx  (cost=0.00..13208.50 rows=579346 width=0)
              Index Cond: ((boom_hoogt >= 0::numeric) AND (boom_hoogt < 6::numeric))

I working with PostgreSQL 9.1.12


texts: Frequent, "random" SQL Server connection timeouts

texts:
For the last few days our clients have been suffering intermittent connection timeouts to our database server.

Multiple Ajax calls on the same page which connect to the server multiple times are having mixes of success and failure, and at times specific website users are getting connection issues while other users are not from the same site.

I am not sure if the issue lies with the databases or the server itself, but we may be having connection issues to a different database on the same server.

The server is an Amazon Cloud VPC running Windows Server 2008 RC, and the version of SQL Server is SQL Server 2008 R2. I can provide more information if necessary.

Can someone give me any hints on how to go about debugging this? Maybe a few methods to test the connections?

Many thanks.


texts: migrating instance from 2008r to 2016 Enterprise

texts:
i have a client that wants to migrate his data to sql server 2016 . currently they have 2008r and the databases are huge(total of 4 tb) on the instance.
what is the best way to performe this with the least amount of down time?
i thought about mirroring but im not sure it can be done
between the two versions.
if so what are the steps to do so ? if im missing information please let me know.
thanks


texts: SQL Server Native Client ODBC Connection Fails

texts:
I am trying to troubleshoot an issue where some clients can connect and others cannot.

Server and Database is online, Browser started, Named Pipes & TPC/IP enabled
All users use the same SQL Account and Password
Connection to database from SSMS works
Local firewall rules are from GPO and match all clients
Instance is configured to allow remote connections
Server does not have firewall rule blocking any client IP involved

We create a SQL Server Native Client 11 ODBC connection.  On some clients the connection comes back with Success and others come back TESTS FAILED!

The full text of the message is:

Running connectivity tests ...
Attempting connection
[Microsoft][SQL Server Native Client 11.0]Named Pipes
Provider: Could not open a connection to SQL Server [5].
[Microsoft][SQL Server Native Client 11.0]Login Timeout expired
[Microsoft][SQL Server Native Client 11.0]A network-related
or instance-specific error has occurred while establishing a
connection to SQL Server. Server is not found or not
accessible. Check if instance name is correct and if SQL
Server is configured to allow remote connections. For more
information see SQL Server Books Online.

Any ideas on why some clients are not able to connect?


texts: How can I know the progress when importing data into MySQL using LOAD DATA INFILE?

texts:
I have a 1 billion record stored in csv file. I'm using LOAD DATA INFILE to import the record to the table.
create table weibo_bind
(
    column1 bigint
        primary key,
    column2 bigint
);

And my csv file is column1,column2.
LOAD DATA INFILE '/path/to/demo.csv' IGNORE INTO TABLE weibo_bind FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

But how can I debug or how can I check the progress of importing? Because it takes a lot of time to import.
Or is there any way to speed up?
I'm using
mysql  Ver 8.0.22, Innodb


texts: how to confirm or refute that performance degradation is due because of too many indexes maintained

texts:
We know that indexes improve select statement and at the same time, indexes have a cost of maintenance on every update/insert/delete statement.

I don't know how to measure/evaluate this cost.

Some context: There are a dozen key tables involved with several inserts/updates, having between 20M and 80M rows. 1400 transaction per second average.

In the lasts months, several indexes were added to optimize performance on different parts of the system. This worked right, but now some degradation on another part of the system appears.

I have this theory, but I don't know how to validate or refute it.

EDIT: I've read some other post, but most are SQL Server oriented, I'm running on SYBASE ASE 15.7, as tagged

How to know when/if I have too many indexes?
How to determine if an Index is required or necessary
Where can I find some guidance on index strategies?


texts: Is it Possible To Take Full And Differential Backup in Postgresql Like Sql Server?

texts: I have a postgresql server. I want to take its backup full and differential mode as it is like in sql server. Is it possible to do this in postgresql? How can i do it, if it is?


texts: Creating a Maintenance Plan for a SQL Azure Database

texts:
I need to create a maintenance plan for a number of SQL Azure databases.  Unfortunately, my many years of experience as a SQL Server DBA does not include experience with this task (sarcasm intended).

After a quick search, I found some information in an article here.  Using the information in this article and not knowing whether or not there was a better option, I decided upon using the second option (Database Maintenance Plans).  What follows makes the assumption that I understand the process correctly.

Using a local instance of SQL Server, I created a new maintenance plan.  From there, I added a new connection to one of the SQL Azure databases by using the Manage Connections option.  From there, I added a task to the designer pane.  I used the task for updating statistics but I imagine it could have been any other task.  Editing the properties of the task, I selected a single database.  That is about as far as I made it.  What follows is some of the observations I have made.

By default in the Update Statistics Task property page, the
option Tables and Views is selected for the Object item.  If
I change this option in my environment (for the SQL Azure database),
the dialog is immediately closed (almost as though it has crashed).
If I re-open the Update Statistics Task property page and then
click the View T-SQL button, an error is generated indicated that
login failed for the specified user.  From there, I re-opened the
Manage Connections dialog and checked the properties of the connection to SQL Azure and noticed that the password is blank
(almost as though the password is not being retained anywhere).
If I use a database on the local server this issue does not exist.

Am I proceeding in the correct direction?  If so, is there something I am missing?  Is there a better or more correct way of accomplishing this?


texts:
I have the following scenario: a database in SQL Server 2012 that must replicate changes in a transactional way to a SQL Server 2000 database.  Since the schemas of two databases differ, a transformation must be made for the destination.  Both databases are in the same network.
What's the better approach?

Use triggers in the source database to make the transformations and perform the changes in the destination.
Use SQL Server transactional replication (but transformations seems not possible, as I know).
Any alternative?


texts: Is the genetic query optimiser enabled by default?

texts:
I read with interest about postgres' genetic query optimiser. Is this enabled by default? Is there a setting to enable it? Are there good rules of thumb of when to use it versus when now?

Doc link follows:

https://www.postgresql.org/docs/11/geqo.html


texts: Some queries are very slow after index rebuild

texts:
After finding out almost all databases on my SQL Server had fragmentation over 40%, I decided to do an index rebuild on all tables using a fill factor of 80.

After rebuilding all indexes, some queries seem to take forever for at least two queries/tables.

Here's one of those slow queries:

SELECT  a.FileID,
        a.EventID,
        MAX(b.cyNumber) AS cyNumber,
        MAX(b.skNumber) AS skNumber,
        MAX(b.cyFormat) AS cyFormat,
        MAX(b.Cost) AS Cost,
        MAX(b.PackageRef) AS PackageRef,
        MAX(CASE WHEN b.BMUpdatedON = '1900-01-01 00:00:00.000' THEN NULL ELSE b.BMUpdatedON END) AS BMUpdatedON,
        MAX(b.RunID) AS RunID
FROM    DB.dbo.[File] a
        INNER JOIN DB.dbo.bicy b ON a.InnerFileID = b.InnerFileID
WHERE   a.FileID NOT IN (SELECT FileID FROM DB.dbo.Event_bicy)
GROUP BY a.FileID,
        a.EventID
OPTION (MAXDOP 1);

I always used OPTION (MAXDOP 1) on all queries because when I didn't use it the query would run VERY slowly.  Now, after the index rebuild, the opposite seem to happen. If I remove MAXDOP 1 or if I remove the where clause the query runs fast, which I find strange.

Solution: when I create a non-clustered index on DB.dbo.bicy.[InnerFileID] the query runs fast.

Question: why do I need to create a non-clustered index for the query to run fast when before the index rebuild the query ran just fine?


texts: Connected to MySQL database but cannot print the data

texts:
I connected successfully to my database and the I tried to print my data but I can only see the "Connected succesfully" line in the browser. Here is my code:

<?php

$db=@mysqli_connect("host","root","pass");
if (!db) { die('Cannot connect with the database.');}

// Check connection
if ($db->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";

$sql = "SELECT username FROM students";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "username: " . $row["username"].  "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();

?>


texts:
I have a Start Datetime and a End Datetime.

Eg: 10:00 am - 12:00 pm

I have to create 4 slots between the, each ranging for 30 minutes.

Eg:
10:00 am-10:30 am
10:30 am-11:00 am
11:00 am-11:30 am
11:30 am-12:00 pm

This 30 minutes can vary and is not a constant. I have tried a few things but they don't seem to work. Can someone please help. Thank you.

I have tried this but i only get the slot difference not the  slotfrom-slotto

with cte as
(
select   convert(datetime,CONVERT(VARCHAR(15), getdate(), 112)) as DATE
union all
select   c.DATE+'00:20:00' as QuarterTime
from cte c
where c.date<dateadd(minute,1422,convert(datetime,CONVERT(VARCHAR(15), getdate(), 112)))
)
select * from cte


texts: Nested aggregation and grouping on multiple columns in MySQL

texts:
Consider the following product table (which is highly trimmed down):

`id` int AUTO_INCREMENT
`category_id` int
`subcategory_id` int
`vendor_id` int
`price` decimal(6,2)
`inserted_at` timestamp

For a given category ID, I am attempting to retrieve a list containing the vendor with the lowest latest price for each subcategory. With "latest" I mean that vendors may have multiple prices for a given category ID/subcategory ID combination, so only the most recently inserted price for that category ID/subcategory ID/vendor ID should be used. If there's a tie between 2 or more vendor's prices, the lowest id should be used as the tie-breaker.

For example, with this data:

id | category_id | subcategory_id | vendor_id | price | inserted_at
---------------------------------------------------------------------------
1  | 1           | 2              | 3         | 16.00 | 2015-07-23 04:00:00
2  | 1           | 1              | 2         |  9.00 | 2015-07-26 08:00:00
3  | 1           | 2              | 4         | 16.00 | 2015-08-02 10:00:00
4  | 1           | 1              | 1         |  7.00 | 2015-08-04 11:00:00
5  | 1           | 1              | 1         | 11.00 | 2015-08-09 16:00:00

So, first find the most recent prices for every subcategory/vendor combination (row with price=7.00 would be removed because it's not the most recent for that vendor in that subcategory). Then for subcategory 1 the lowest price would be 9 (so vendor_id = 2) and for subcategory 2 the lowest price is 16 (two vendors tie ()ids 3 and 4) so we choose the one with lowest vendor_id = 3).

I would expect the following results for category_id = 1:

subcategory_id | vendor_id | price
----------------------------------
1              | 2         |  9.00
2              | 3         | 16.00

Here's what I have so far. I feel like it's already starting to get out of hand and this doesn't even account for ties between 2 or more vendor's prices.

SELECT c.subcategory_id, c.vendor_id, c.price
FROM products AS c
JOIN
(
    SELECT MIN(a.price) AS min_price,
           a.subcategory_id
    FROM products AS a
    JOIN
    (
        SELECT  MAX(`inserted_at`) AS latest_price_time,
                vendor_id,
                subcategory_id
        FROM    products
        WHERE   category_id = 1
        GROUP BY vendor_id, subcategory_id
    ) AS b
    ON a.inserted_at = b.latest_price_time AND a.vendor_id = b.vendor_id AND a.subcategory_id = b.subcategory_id
    WHERE a.category_id = 1
    GROUP BY a.subcategory_id
) AS d
ON c.price = d.min_price AND c.subcategory_id = d.subcategory_id
WHERE c.category_id = 1

Before I go any further, I wanted to see if there was an easier way. When it comes to grouping/aggregating results of additional groupings/aggregations, is there a method that will give me the best performance (most important) and/or be easier to read (less important)?


texts: Insert into specific table if the user session group

texts:
I am trying to insert data into specific table by user session and group.

The tables are users, workers, help.

Structure of table "users":

id | username | password | group
---+----------+----------+---------
1  | john     | 123      | workers
2  | david    | 123      | help

Structure of table "workers" and "help":

id | username | message

I finished login with session pages and include session onto every page.
So when the user login it check and display from "user" table session: Hello "user"

The problem is with "insert into" statement, when john or david logs in and then they post something into database.

Which is the best way to create insert into statement so if it's john all insert into will go into "workers" and if it's david all insert into will go into "help"?


texts: Get Row Value by Column Name of Name at runtime

texts:
I currently have a stored procedure that obtains rows from an import table and joins another table which has a row for each column header of the import table.

I am able to query this but in order to obtain the value I need to utilize a huge case statement.

Is there anyway to dynamically get a column value from a row by simply knowing the nvarchar type of the column name?

Here is my current procedure:

...
SELECT

import.ImportID,
f.FieldID,
f.FieldName,

CASE

    WHEN f.FieldName = 'Test'
    THEN import.Test
    WHEN f.FieldName = 'Test2'
    THEN import.Test2
    ELSE
        0

END AS [Value]
--import.[f.FieldName] AS [Value]

FROM [Import_Table] import
LEFT JOIN #Fields f ON (1 = 1)

END

The issue I have is that there are 40+ columns in the import table, case statements aren't too bad, however, would be difficult to maintain/record moving forward. Is there another way to obtain the data dynamically?

Perhaps a temporary pivot table and joining on column names?

TIA

-- edit --

-- this is just used from another table
CREATE TABLE #Fields (FieldID bigint, FieldName VARCHAR(50))

-- this is import table
TABLE Import_Table (ImportID bigint, UserID bigint, Test decimal(5,2), Test2 decimal(5,2), Test3 decimal(7,3), ....

Fields is populated as:

Test
Test2
Test3
Test4
Test5
Test6
...

-- edit 2

//Import Table
1,323,2.3,4.5,3.0,8.803,...

//expected output
1,1,Test1,2.3
1,2,Test2,4.5
1,3,Test3,3.0
1,4,Test4,8.803


texts: View created using Join Queries taking too much to load

texts:
I have written a query to create a view which i can use as a flat table in my application, which is having more than 30 inner join conditions across 25 tables.
Now the problem is every time i am selecting the view with even 2-3 columns without adding any further condition or clause, it is taking more that 45-50 seconds to load.

No of records in the view : around 250

No of Columns in view : 45-50

My.cnf seems okay, i have checked it using mysqltuner perl script.
I have tweaked the relevant parameters in my.cnf , but no significant effect noticed in query response time.

Engine Type : InnoDB, Foreign key constraints applied.

Mysql version : Percona mysql 5.6

Any Ideas?

Thanks in Advance.


texts:
Often I have to return the count of of some row in a query that fetch other non related rows.

For example A table User a table Review and a Table picture

User:
id
nickname

Review:
id
to_user_id
from_user_id
rating

Picture:
id:
user_id
url

Let's say I want in one single query retrieve the nickname of a 'given' userId all its pictures url as well as the count of how many people reviewed that user.

The first and easy way I think when doing this query would this:

SELECT
  u.nickname
  (SELECT count(*) FROM review WHERE to_user_id = u.id) as reviewCount,
  p.url
FROM user
LEFT JOIN picture ON p.user_id = u.id
WHERE
  u.id = 1

The other way of doing this is without that subselect and by joining the review table on the right user_id

SELECT
 u.nickname,
 r.reviewCount,
 p.url
FROM user u
LEFT JOIN (
    SELECT to_user_id, count(*) reviewCount FROM review GROUP BY to_user_id
 ) r ON r.to_user_id = u.id
LEFT JOIN picture ON p.user_id = u.id
WHERE u.id = 1;

I'm not very an expert with db query performance and tuning. Could someone explain me if a solution is better than the other? (Or if there is an other better solution) ?

EDIT:
Sorry forgot to mention. I'm working with the latest MySQL


texts: How to interpret this XML plan fragment?

texts:
I am looking at the query plan produced by SQL Server 2008 R2 and I'm not sure I'm interpreting it properly. Examples:

<RelOp NodeId="2" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="1" EstimateIO="0.0112613"
 EstimateCPU="0.000100822" AvgRowSize="822" EstimatedTotalSubtreeCost="1.06489" Parallel="0"
 EstimateRebinds="0" EstimateRewinds="0">
 ...
  <MemoryFractions Input="0.5" Output="1" />
  <RunTimeInformation>
    <RunTimeCountersPerThread Thread="0" ActualRows="10190123" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" />
  </RunTimeInformation>

and

<RelOp NodeId="6" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimateIO="1"
 EstimateCPU="0.0502411" AvgRowSize="24" EstimatedTotalSubtreeCost="1.05024" TableCardinality="4.93715e+009"
 Parallel="0" Partitioned="1" EstimateRebinds="0" EstimateRewinds="0">
 ...
  <RunTimeInformation>
    <RunTimeCountersPerThread Thread="0" ActualRows="10190123" ActualEndOfScans="1" ActualExecutions="1" />
  </RunTimeInformation>

Specifically, seeing EstimateRows="1" and ActualRows="10190123" I suspect that the optimizer incorrectly estimates the number of rows that will be processed. Subsequently, underestimated row set for the sort might lead to a major sort spill.

Edit:

The query in question looks similar to

select whatever from mytable
where integer_column1 > 848484884 and
      integer_column1 < 949494949 and
      another_int_col in (5, 8, 15, 20)

where the cardinality of integer_column1 is in millions and that of another_int_col is a few dozen (a flag of sorts). There is a non-unique, non-clustered index on (integer_column1, another_int_col). I wonder if even in the presence of updated statistics the optimizer can use the histogram/density vector of that index though.

Does my reasoning make sense?

(I don't have access to the actual system or the Management Console for that matter -- only the XML plan)


texts:
SQL Server audit filenames use the following pattern:

Name_GUID_PartitionNumber_Timestamp.sqlaudit

An example filename below:

Audit-ObjectChanges_2D94C1E8-CCAD-405F-A9F2-A45FA5EDAD8F_0_132327053636850000.sqlaudit

If I query sys.dm_server_audit_status I see the status_time column has a value of 2020-04-30 08:29:22.6070000 for four audits I have. All filenames have 132327053636850000 in the timestamp part of the filename.

So I assume 132327053636850000 = 2020-04-30 08:29:22.6070000, but how can I convert the filename timestamp into datetime format for previous audit files?

Thanks


texts: Update can't see records just committed by another connection

texts:
I am performing a transaction using a connection. Whenever a second connection immediately tries to update the rows just inserted, the rows are not found. Even if the transaction was committed before issuing an UPDATE.

I have the mysql query log which describes the scenario.

The last statement fails with 0 rows affected (UPDATE refresh_token...) :

2018-08-03T13:31:24.829038Z  1150 Query START TRANSACTION
2018-08-03T13:31:24.830026Z  1150 Prepare   INSERT INTO account (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.830493Z  1150 Execute   INSERT INTO account (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.831345Z  1150 Close stmt
2018-08-03T13:31:24.833228Z  1150 Prepare   INSERT INTO refresh_token (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.833666Z  1150 Execute   INSERT INTO refresh_token (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.834356Z  1150 Close stmt
2018-08-03T13:31:24.834477Z  1150 Prepare   INSERT INTO another (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.835155Z  1150 Execute   INSERT INTO another (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.835621Z  1150 Close stmt
2018-08-03T13:31:24.835747Z  1150 Query COMMIT
2018-08-03T13:31:24.840374Z  1150 Prepare   UPDATE refresh_token SET accessed = ? WHERE token = ?
2018-08-03T13:31:24.840799Z  1150 Execute   UPDATE refresh_token SET accessed = '<redacted>' WHERE token = '<redacted>'
2018-08-03T13:31:24.843346Z  1150 Close stmt

If I wait 500ms before issuing the UPDATE refresh_token SET..., the record is found.

Here is the transaction isolation level:

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
'REPEATABLE-READ', 'REPEATABLE-READ'


texts: logic/design for storing achievments and progress for users

texts:
I have a game with a databse of users, I now want to implement unlockable achievments in my game. How would I best go about saving this data for each user? I am still fairly new to using a database.

Relevant info:

Each user in the users-table has an ID
There are 30+ achievments
Many achievments are longer processes, that require the ability to save the progress of that achievment for users, ex: Win 8 rounds in a row.
When the game is started it should load all finished/unfinished achievments as well as progress for each.

I don't even know where to start, any sort of help is appriciated.


texts: MariaDB doesn't set authentication_string during installation

texts:
I'm setting up a new server using MariaDB (current stable version of 10.3) and I notice that all of my users - mainly root and debian-sys-maint - do not have an authentication_string after the installation is done, even after running mysql_secure_installation.

I'm about to re-install my server again and wondering how do I set all my users to get an authentication_string during the installation? And if that isn't possible, would it cause problems if I set a password to debian-sys-maint user?


texts: View Default value for User Defined Table Type columns in SQL Server

texts:
I have a user defined table type in SQL Server 2014 that I need to drop and recreate. However, if I use the script from SSMS, the default value constraints that are on some of the columns, will not be created.

I also have problems viewing the default constraint, so I do not what the default value should be.

Is there a way to view what those default value constraints contain?

CREATE TYPE [Logging].[LogElements] AS TABLE(
[LogMessage] [nvarchar](MAX) NOT NULL,
[MessageTypeId] [smallint] NOT NULL,
[Origin] [varchar](50) NOT NULL,
[JobRunId] [int] NULL,
[InvoiceId] [int] NULL,
[AccountlineId] [int] NULL,
[MSErrorCode] [bigint] NOT NULL,
[LogStatusId] [int] NOT NULL,
[LogUser] [varchar](50) NULL

)
GO

Above is the definition as it is created when generating script in SSMS. As you can see, the default values are not in the script.


texts:
I have table which  has schema  as follows

TABLE  EMP

EMPID  - Primary KEY
DEPTID - NON Clustered Index
ISActive - NON Clustered Index

It has other non index columns as well

This table is highly transactional and will have lots of data. Currently I have 824392 rows in this table.

Now I am executing query which takes lot of time sometime and sometime it works like a charm.

PFB the query  :

UPDATE EMP SET ISActive= 0  WHERE DEPTID in (11,22)

Questions :

Will only nonclustered index on ISActive get updated?
Will all the nonclustered indexes and clustered index get updated ?
If I remove some of the rows as they are quite old will that help with performance ?
Why this query take some minutes to execute and sometimes it will take less then second ?
What is the best possible solution for this ?

Any pointers are highly appreciated

Thanks in advance

Regards,

Phani