texts: Object name 'Clean Up History' in use when trying to complete the maintenance plan wizard in SQL Server 2008 R2

texts:
I am trying to create a maintenance plan on a instance running SQL Server 2008 R2 SP1 (no CU's installed).

When completing the wizard I get the following error:

  The object name cannot be changed from
  "{1E1746D3-B671-4799-8F61-7EE6117257C2}" to "Clean Up History" because
  another object in the collection already uses that name. Use a
  different name to resolve this error.
  ({1E1746D3-B671-4799-8F61-7EE6117257C2})

I've checked both [msdb].[dbo].[sysjobsteps] and [msdb].[dbo].[sysjobs] and neither contain the name "Clean Up History".

The server is being backed up by Microsoft DPM. Could it be that DPM inserts a cleanup job somewhere that I don't see?


texts: Azure SQL Server Database Error: 18456, Severity: 14, State: 1

texts:
Why can't I login to my SQL Server database on Azure, and how do I correct this? No one in our company has changed the password of our Azure SQL Server database administrator user, yet I'm getting the error noted below when I use SQL Server Management Studio 2017 on my local PC and try to connect to my Azure database as I always have:

Server Name: XXXX.database.windows.net
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536

According to Microsoft (https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error?view=sql-server-2017), Error 18456 is a logon failure. I'm not sure what the Severity value means. But as for the State value of 1, it means, "Error information is not available. This state usually means you do not have permission to receive the error details. Contact your SQL Server administrator for more information."

Unfortunately, the Azure support website is down so I can't investigate further.


texts: How does the transactions interact if you set a different isolation level per connection?

texts:
I read that I can have different isolation levels per connection and in the server.
The default isolation level is REPEATABLE-READ.
So if I have a transaction that issues a SERIALIZED to the client connection, how does it interact with other transactions from other client connections? Do they all end up being serialized? From set transaction seems not:

  With the SESSION keyword, the statement sets the default transaction
  level for all subsequent transactions performed within the current
  session.

But this seems useless to me. I mean if I set serialized to a client connection then I would want all currently running transactions to be sequential, right? Otherwise what's the point?


texts: PostgreSQL: Executing DDL on every schema

texts: I've a multi-tenant DB setup, and need to add some columns.  I'm using schemas (and search_path) to partition my users, so I'm looking for a ubiquitous way to apply a DDL-schema change to all my databases.  Initially, I'd thought I might be able to do it as a single query (cursor on pg_catalog), but thinking a command-line invocation of psql -f might be the preferred way.


texts: Is this inconsistent treatment of `void` return from sql and plpgsql functions documented?

texts:
begin;
create table foo(id integer);
create function f1() returns void language sql as $$delete from foo;$$;
create function f2() returns void language plpgsql as $$begin delete from foo; end;$$;
select f1() is null, pg_typeof(f1()), f1()::text, f1()::text is null;
/*
 ?column? | pg_typeof | f1 | ?column?
----------+-----------+----+----------
 t        | void      |    | t            <----------------------the return is null
*/
select f2() is null, pg_typeof(f2()), f2()::text, f2()::text='';
/*
 ?column? | pg_typeof | f2 | ?column?
----------+-----------+----+----------
 f        | void      |    | t            <----------------------the return is not null
*/
rollback;

Is this a documented inconsistency?

SQLFiddle here


texts:
We have a table that serves as an audit trail table for an app. Let's call it ProductAudit, and let's call the data table for this audit table Products. ProductsAudit got polluted with about 150 million rows due to a bug. There are about 2 million valid rows that need to remain.

So for cleanup, I want to:

create a new table, ProductAudit_TEMP
copy over the 2 million good rows
drop table ProductAudit
rename ProductAudit_TEMP to ProductAudit.

This will be much faster than deleting the 150 million invalid rows - even in batches.

My question is, table Products has INSERT, UPDATE, and DELETE triggers, which write into table ProductAudit. Will my plan break the triggers? I'll have all users out of the system, and even disable the triggers for safety when performing the cleanup.

EDIT:

Just to clarify, ProductAudit does not have triggers, Products does. ProductAudit is being populated by the INSERT, UPDATE, and DELETE triggers that belong to table Products.


texts: How to control the order of servers listed in the SSMS connection dialog?

texts:
My current project involves a lot of database shuffling as we upgrade from SQL 2008 -> 2012, re-task hardware, repeat.

One minor annoyance is the connection dialog in SSMS re-shuffling the server order to put the most recent on top.  Given that on any particular day I'm connecting to 6-10 DB servers and want to quickly find a specific one in the list, this behavior is much more of a hindrance than a help.

Is there a way to disable this behavior, having the servers listed alphabetically, or in the order they were added...or anything that doesn't change every time I connect to a server?

Barring that, is there an entirely different way to better manage connecting to a specific server from a list/farm of them?

I'm using SSMS for SQL 2012 (though I'm curious about 2008 solutions as well).


texts: Seperate value of a specific column with "," delimiter in Oracle using "Regular Expression"

texts:
I have a table with below structure:
create table CUSTOMER_INFO
(
  cust_id      NUMBER,
  cust_name    VARCHAR2(50),
  cust_address VARCHAR2(50)
)

Example Data :
cust_id   |  cust_name |  cust_address
------------------------------------------
  1       |     A      |  Tehran,Tehran
  2       |     B      |  Rasht,Tehran
  3       |     C      |  Minsk,Tehran

I want to have this output:
cust_id   |  cust_name |  cust_address
------------------------------------------
  1       |     A      |  Tehran
  2       |     B      |  Tehran
  2       |     B      |  Rasht
  3       |     C      |  Minsk
  3       |     C      |  Tehran

what I've written so far is this :
select distinct cust_id,
                cust_name,
                address
  from (select distinct cust_id,
                        cust_name,
                        regexp_substr(cust_address, '[^,]+', 1, level) address
          from customer_info
        connect by regexp_substr(cust_address, '[^,]+', 1, level) is not null)
order by cust_id

I want to know if there is better way of writing this query and Will this solution produce false result ?
Thanks in advance


texts: Convert BIGINT to hexadecimal (string) in MonetDB

texts:
I have a column of type BIGINT in my MonetDB database and for readability I'd like to print its contents as hexadecimal number when executing a SELECTstatement. Is there any way to achieve this?

I read through the MonetDB documentation, looking for a function such as HEX(), CONV() etc. but could not find anything like that (or close). I even read through the defined functions (SELECT name FROM sys.functions;) with no luck.

P.S. I'm not allowed to create the tag monetdb here due to lack of reputation. Could someone please add this tag for me? (This is quite annoying since I am forced to select a tag on none of the existing seems appropriate to me here…)


texts: How do I get % progress for "type db.sql | mysql"

texts:
I am doing:

type db.sql | mysql

and the console output is nothing. I see the ibdata1 file growing, but I don't know how many GB it will grow to.

I would like to see a % progress indicator of some kind. Any ideas?


texts:
I went through a re-installation of postgresql on a mac using brew and I'm not sure about some default vs user created databases.

After the installation I see 2 users:

postgres
dan (being my osx user).

These should be ok, normally they are created during installation.

Listing the databases I see:

postgres (ok, default)
template1 (ok, default)
dan (not sure about this)

So my questions are:

what happened to template0 database? Did I deleted by accident or it's normal that postgres doesn't install it by default all the time? In case I've deleted what can I do to restore it? What's used for?
did postgres installed the dan databases for storing info on my dan user or did I create it in the past (and forgot)? In this case I can delete it (it has no tables). How can I be sure who created this?


texts: Azure database - unable to locate in my Azure account

texts:
I have an Azure account where a developer that used to work with me has set up an MSSQL database on an Ubuntu VM.

The PHP (CodeIgniter) system I am working on logs in (so database queries work correctly). I have the following information available:

The connection string uses "sqlsrv" driver in CodeIgniter
The hostname in the CI config is "127.0.0.1".
Using "top" command, I can see "sqlservr" using resources (12.8% mem, 0.7% CPU)
I can run SQL scripts with "sqlcmd -S localhost"

But yet, I can't find anywhere where this database is listed in my Azure portal. Very frustrating. There is nothing listed under "SQL databases" and I have been through all the visible options in the Linux VM, and can't find it.

What am I missing?


texts: How to get around not being able to use Temp Tables in Functions?

texts:
So, to get straight to the point. A Client wants to be able to view all of their staff of a certain position are going to be available to perform a set of shifts between 2 dates.

Seems simple, have a table-valued function to get all staff if that position then use a single-valued function that runs through that staff members leave or current shifts to see if any will stop them from working this shift.

ALTER FUNCTION [Human_Resources].[fIsEmployeeOffshoreBetweenDates] (@EmpID INT, @StartDate DATETIME, @EndDate DATETIME)
RETURNS INT
BEGIN
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
    /*Then it exists*/
    DROP TABLE #Temp

    SELECT *
    INTO #Temp
    FROM Human_Resources.v_GetEmployeeManning(@EmpID)

    DECLARE @RequestID INT

    WHILE EXISTS(SELECT * FROM #Temp)
    BEGIN

        SELECT TOP 1 @RequestID = ID FROM #Temp

        IF (General.IsRangeBetweenDateRange((SELECT TOP 1 StartDate FROM #Temp WHERE ID = @RequestID), (SELECT TOP 1 EndDate FROM #Temp WHERE ID = @RequestID), @StartDate, @EndDate) = 1)
        BEGIN
            PRINT N'IS WITHIN RANGE'
            RETURN 0;
        END

        DELETE #Temp WHERE ID = @RequestID

    END

    RETURN 1;
END

But then I found out that Functions can't actually contain temporary tables. Does anyone know how I could get around this?


texts: Saving multiple authentication strategies to the same database

texts:
I need some advice with the logic behind this authentication method.
I'd like my app to have multiple ways to create accounts:

email/pass
twitter
google

I can handle the email and password login myself, but for everything else I'm using Passport.js and oAuth.
Getting the information from Twitter and Google is pretty simple, but I'm having trouble with how to structure my database table so that all these Authentication Strategies can live in the same place.
My unique constraint would be on my email column, but what would happen if a user registered with their email and then tried to create an account with Twitter that uses the same email? Should I just fail it at that point?
If a user changes their email on Twitter or Google, how do I identify them once they try to login again?
Obviously all of this is easily fixable if I make the id that I get back from both Twitter and Google my unique constraint, but then how do I ensure that the id is unique across platforms? Email/Password login would just auto-increment my id, so what happens then?
I'm just really confused on how to handle the logic for this, any help would be greatly appreciated.


texts: How to export image filenames from Wordpress database?

texts:
I have a Wordpress install that's using about 10,000 images. I'd like to export all filenames used in Wordpress to a file, filenames.log

filenames.log would look like this:

car.jpg
boat.jpg
milf.jpg
car2.jpg
other.jpg

etc...

The reason I'm doing this is that I have orphaned images in /uploads, and I want to run a delete operation and exclude these filenames that are actually in use.

What command could I run to generate such a file?

Thanks!


texts: I need to add new data file into the system tablespace because its running out of space. Are there any precautionary measures I should consider since this is a production environment?


texts: Are there any MySQL Triggers or Events that my program can wait for?

texts:
Say I want a live feed of emails on a web based email system. Emails are inserted into MySQL tables. Let's say I want my program to know immediately when the table is updated so it can check for new emails, would MySQL triggers be a good way to do this?

Lock tables waits for the tables to become available before it returns. Is there a way I can wait for another person to lock it instead of waiting for all others to unlock it.

I could just poll every few seconds to see if the table is updated, but I think it would be better if I could just use some sort of wait until the table is either locked-then-unlocked or wait for the table to be updated before seeing if there are new emails.

Alternatively there might be a way to execute a command when the table is updated?

Do I have to implement my own event waiting service for this, or can it be done in MySQL?


texts: upgrading from postgres 9.1 to 9.3 on ubuntu server

texts:
I have my production server(ubuntu 13.10) running with postgresql 9.1.

I want to use few features of 9.3, hence want to upgrade.

Could someone help me with upgrading from 9.1 to 9.3 so that there is a downtime of not more than 30 mins. or so?

Prime concern is preventing a data loss or file redundancy.


texts: Migrate from innodb_file_per_table to off in MySQL ALTER TABLE removing ibd files

texts:
Trying to migrate individual tablespaces back into the shared ibdata tablespace.
MariaDB version 10.0.16

SET innodb_file_per_table to off in MySQL, restated mysql and new tables are in fact going into shared space.

Attempted to run alter table statement to recreate move the table back into the shared tablespace but the tables are not moving.

SET SQL_LOG_BIN = 0; ALTER TABLE menu_router ENGINE=InnoDB;

But the file still exists

-rw-rw----    1 mysql mysql  3.0M Apr 22 14:50 menu_router.ibd

in information schema

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE PATH LIKE %db4472%';

+---------+---------------------------------+
| SPACE   | PATH                            |
+---------+---------------------------------+
| 2431817 | ./db4472/menu_router.ibd |
+---------+---------------------------------+
1 row in set (0.06 sec)

MariaDB [(none)]>


texts:
DBMS_Resource_Manager has a procedure called Calibrate_IO with a Physical Disks input parameter.  The documentation says this value should be set to the...

  Approximate number of physical disks in the database storage

With a logical drive striped across an unknown number of disks in a SAN disk group containing 30 drives, I'm not sure what this means.  I ran the procedure for values of 1 and 30, yet the IOPS, MBPS and latency were nearly identical.

Is this number just for informational purposes or is it actually used?  If the the later, then what should it be set to when there is not a one to one relationship between logical disks and physical disks?

Block to run procedure:

set serveroutput on;

DECLARE
   vDisks         Binary_Integer := 30;
   vMaxLatency    Binary_Integer := null;
   vMaxIOPS       Binary_Integer;
   vMaxMBPS       Binary_Integer;
   vActualLatency Binary_Integer;
BEGIN
   DBMS_RESOURCE_MANAGER.Calibrate_IO(Num_Physical_Disks => vDisks
      , Max_IOPS => vMaxIOPs, Max_MBPS => vMaxMBPS
      , Actual_Latency => vActualLatency);
   DBMS_OUTPUT.PUT_LINE('      Max IOPS:' || vMaxIOPS);
   DBMS_OUTPUT.PUT_LINE('      Max MBPS:' || vMaxMBPS);
   DBMS_OUTPUT.PUT_LINE('Actual Latency:' || vActualLatency);
END;
/

select * from dba_rsrc_io_calibrate;


texts: Install pg_trgm in PostgreSQL 8.4 on Windows XP

texts:
I just tried to enter in psql shell as user postgres:
> psql -U postgres

then connect to my database
> \c MyDATA

and then try to execute this command to install similarity function from pg_trgm
> \i c:/postgresql/8.4/share/contrib/pg_trgm.sql

I got this result, and seems not to be working:
SET
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE OPERATOR
psql:c:/postgresql/8.4/share/contrib/pg_trgm.sql:44: NOTICE:  el tipo Â«gtrgmÂ»
no ha sido definido aÃºn
DETALLE:  Creando una definiciÃ³n de tipo inconclusa.
CREATE FUNCTION
psql:c:/postgresql/8.4/share/contrib/pg_trgm.sql:49: NOTICE:  el tipo de argumen
to gtrgm estÃ¡ inconcluso
CREATE FUNCTION
CREATE TYPE
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE OPERATOR CLASS
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE OPERATOR CLASS

Is probably something that I have been missing, but I don't know what?
Is this the correct way to install it in 8.4 under XP? (I can't change OS).


texts: Query to map a particular database to a syslogin

texts:
I need to map a database to a syslogin.

Following is the query I have used to create login and make it a db_owner but after the execution, I can see a tick as db_owner for that database in UserMapping tab ,but there's no tick on the database itself that means it's not mapped with the database

CREATE LOGIN [TRAINW7X64CA04\Test8] from Windows with default_database = Eclipse;
Use Eclipse
EXEC sp_addrolemember N'db_owner', N'TRAINW7X64CA04\Test8'

Can some help me with the mapping of the database as well?


texts:
So there is a linked server. However, I am not currently able to view all databases associated with that linked server from within SSMS after expanding the catalog dropdown. It is my understanding that this may be a permissions issue. However, I am unsure as to which permissions I need to grant in order to interact with tat db from the linked server.

Any ideas?

After executing the following command from the linked server:

select * from [<server>].[<database>].[dbo].table1

I receive the following error message:

  The OLE DB provider "SQLNCLI10" for linked server "" does not
  contain the table """."dbo"."table1"". The table either does
  not exist or the current user does not have permissions on that table.

TLDR;

Problem: I'm not sure how to get one of the databases associated with my linked server to show up.!


texts: How to handle multiple changing ID's for a single person

texts:
I have a company with a single party table containing Member ID(the primary key), Subscriber ID, Member Sequence ID, Legacy ID, and a myriad of other identifiers from random source systems. Because they all live in a single table, it's not possible to capture the full scope of a person as they could have multiple unique ID's in this table (they can be assigned a new member ID for various reasons).

They can get also get changes to their subscriber ID (or any other ID) as well, with no other changes. I've tried to figure out the best way to track a person, but am a bit lost.

I've created a new table, DIM_PERSON that contains some attributes. I've linked this to DIM_MEMBER via FK so a record in DIM_MEMBER can be

ID   Person_dim_id  Member_dim_id
1    123            100
2    123            200

Which will allow you to have a one-many membership ID's to a single person. However they can change subscriber ID's without changing member_id or person_ID so i'm wondering if I need to follow the same logic and build a new DIM_SUBSCRIBER table with foreign key for the two previously mentioned tables.

Would this also be the same for all other ID's and each would need a separate dimension table?

The idea is to pull a single person ID and ideally see a history of all data tied to various ID's within the fact tables.


texts: Permissions Dropped after Package Failed

texts:
I created a local db and then set up a one-off job to copy the live db to this new db. Or at least that's what I thought I'd done. The logs suggest that the job thought it had to create the db on live and replicated to that instead so I must have set the job up incorrectly. The log shows that the table was created on live by the job.

For one or more reasons the copy job failed and the database it created was uloaded and apparently deleted (I can't find the .mdf or .log files) and this I could just about live with.

However after the failure of the job, all the object permissions were dropped from the live database so the accounts our application use to access the db were unable to connect.

After a restart the databases are running fine, but the permissions remain dropped.

I'd like to understand the reasons behind this behaviour and gather any dos and don't for the next time I have to do something like this.

Can anyone help?


texts:
Restrictions after you failover to an upgraded server instance.
Can some one please explain this is detail? "After failing over from an earlier server instance to a SQL Server 2016 server instance, the database session is suspended. It cannot be resumed until the other partner has been upgraded".


texts: SQL Server on azure virtual machines default instance

texts:
We have two ways of provisioning SQL Server on an Azure virtual machine, one is creating the VM and then upload the binaries of SQL server and do the installation manually, the second one is using SQL Server on Azure virtual machines which provides images of the OS and the desired version on SQL Server.
The question applies to the second option. Is there a way to provision a SQL Server virtual machine on azure with a named instance or does it always install the default?


texts: Does cross-database permission chaining work for objects owned by sa/dbo?

texts:
I have two databases, both owned by sa. All objects in both databases are in schema dbo.

I have a user bob in dbSafe who needs to select from a view that joins tables from both dbSafe and dbRestricted. I don't want to grant bob direct permission to those tables in dbRestricted, since they contain columns I don't want bob to see.

I've granted bob SELECT permission on the view, and SET DB_CHAINING ON for both databases, but I'm still getting an error:

  The server principal "bob" is not able to access the     database "dbRestricted" under the current security context.

Am I misunderstanding what cross-database chaining does?

  If the source object in the source database and the target objects in the target databases are owned by the same login account, SQL Server does not check permissions on the target objects.

Can database chaining work in this scenario, with database owner sa and schema dbo? Or does it have to be an explicit login account/non-default schema?

The accepted answer to this question suggests creating a view in dbRestricted and assigning direct permission to bob, would that work in this case? What if adding a view to that database isn't an option for me (due to developer/vendor restrictions)? Isn't that what cross-database chaining is for?


texts: Researching Performance Degradation on AO Secondary using MultipleActiveResultSets (MARS)

texts:
Environment: SQL Server 2016 Enterprise Edition on Windows 6.3 9600 two node cluster with Always-On.
We have an application that executes SELECTs on the only secondary node of a SQL Server 2016 always-on availability group. That application uses the ApplicationIntent=ReadOnly;MutlipleActiveResultSets=true options in the connection string.
Query execution via the client's application is almost 50% slower on the secondary compared to the primary and we are trying to understand why. If I execute those same queries, using the same connection string via SSMS, the execution times are identical on both nodes.
Some facts:

SQL Server has identical configurations and resources on both nodes.
The network, VMWare configuration and proximity to the application
server have been eliminated as potential causes

Reviewing wait statistics, there is this surprising information about transaction_mutex waits

and after some research, I found this article which mentions excessive transaction_mutex waits when passing through MARS as is the case here.
There is also the very brief but consistent blocking behavior on the secondary which also seems to be a side effect of MARS.

Notice that the wait type is Transaction_Mutex when the blocking appears.
Questions:

Is using MARS to execute SELECTs on an Always-On Secondary Node causing these transaction_mutex waits and the additional execution time?
How do I track down the root cause?


texts: SQL Server 2014 EE not using all available processors

texts:
First of all let me apologize for not being clear on my requirements in my original question. I am now updating my question with my current scenario:

I have few existing SQL 2014 Enterprise Edition cluster and standalone servers in my environment. I have observed that some of them are not using all the available processors on server. For e.g. One of them is only using 40 processors out of 72 available on the server. I googled for a fix of this and came to know that it could happen when you have servers upgraded from previous Server/CAL based licensed severs(I could be wrong here as well). This limits the number of processors that you can use.

Just to double check my existing license I ran select @@version and this shows my SQL Server is Enterprise Edition only. That means Server/CAL based. I also checked schedulers on my machine and saw that only 40 of them are online and rest of them are offline. Also, there is no affinity masking configured on the server.

Now, I want to know what options do I have to correct this issue and how to apply those changes(seeking only technical details and is not about the legality or pricing of licensing.) to fix it.

Please let me know if you need any further details.


texts: Compare each day of year with DATEFIRST = 1 and DATEFIRST = 7

texts:
To Get a list of weeks, i simply run this (found online):

SET DATEFIRST 7
;with DateCTE   as
(
    select cast('01/01/2017' as datetime) DateValue
    union all
    select DateValue + 1
    from    DateCTE
    where   DateValue + 1 < '1/1/2018'
)

Select
    cast(CAST(DateValue AS CHAR(11)) AS DateTime) AS [FullDate],
    DATEPART(wk, DateValue) AS WeekOfYear,
    DATEPART(yy, DateValue) AS CalendarYear
from   DateCTE
OPTION (MAXRECURSION 0)

Obviously if I SET DATEFIRST = 1, then it gets different results.

I need to compare the days in one table, without using table variables.
This is what I'd like to achieve:

Thus I'd like to see the same 365 days that will be available in both CTE's,

I want to compare the WeekOfYear based on different DateFirsts.

I do not know how to do this effectively. I can only get it to work when I

Create table variable 1 and run the CTE for DateFirst7
Create table variable 2 and run the CTE for DateFirst1

This is inefficient and I am looking for a better way to do this.

There are a few things working against this
1. I dont know how to set the DateFirst inside the query. I only know how to set it before  the cte.
2. I would like to use nested CTE's for this, but OPTION (MAXRECURSION 0) does not work well with nested CTE's

Is this possible - using CTE's to get required results?


texts: Architectural Help Needed - MySQL or NoSQL for a table with 54 million records

texts:
We have a table with 54 million records. Here is the table structure.

CREATE TABLE `metaplay` (
  `track_id` int(11) NOT NULL DEFAULT '0',
  `user_id` int(11) DEFAULT NULL,
  `completed` int(11) DEFAULT NULL,
  `skipped` int(11) DEFAULT NULL,
  `created` int(11) DEFAULT NULL,
  `updated` int(11) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `created` (`created`),
  KEY `updated` (`updated`),
  KEY `skipped` (`skipped`),
  KEY `track_id` (`track_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

All these data are numeric. At this point, we have ~300 inserts and ~100 updates per minute. We also pull out daily, weekly and monthly track playing records from this table. Now my question is which of the followings will be better for architectural design and best performance. I would appreciate if you can highlight hardware details as well.

MySQL in SSD Cached Storage with 4GB Ram
MySQL in SSD with 4GB Ram
Any NoSQL Solution
Anything else?

Also do you suggest any mysql specific tuning tips for a table like this?


texts:
I have two MySQL servers in replication (master -> slave).

I updated them with apt-get upgrade. It runs well on both except the I get some replication errors on slave now.

  Last_Errno: 1005 Last_Error: Error 'Can't create table
  performance_schema.cond_instances

  (errno: 131 "Command not
  supported by database")' on query.

  Default database: 'mysql'.

  Query:
  'CREATE TABLE performance_schema.cond_instances(NAME VARCHAR(128) not
  null,OBJECT_INSTANCE_BEGIN BIGINT not null)ENGINE=PERFORMANCE_SCHEMA'

Server versions are as below:

Master
Server type: MySQL
Server version: 5.5.62-0+deb8u1-log - (Debian)

Slave
Server type: MariaDB
Server version: 10.1.26-MariaDB-0+deb9u1 - Debian 9.1


texts: How do I kill a rollback process when I want to drop the database?

texts:
I want to drop a SQL Server 2008 database, but it is currently performing a rollback for a transaction (that transaction took 3 days to run so far).

How do I stop the rollback because I want to drop the database?

Thank you.


texts: Database design - model a 'Company' with 'Owner', 'Partner', 'Employee'

texts:
I'm working on an app that has mainly two entities: Company and User. A User can be an employee, owner, or a partner.

My current approach of modelling this is:

Capacity is enum with possible values: employee, owner, partner

Someone got me confused suggesting I should have three separate tables for each of these capacities, which I don't think is right.

I'm terribly unsure whether the capacity could grow to more in future.

Should I create another capacity table and reference it in Personnel as a foreign key, in case we wish to add more capacity? Would that be a scalable (and right) approach?

Thank you.


texts: Optimization Technique For Update Query in MySQL 5.1.73-log

texts:
I am trying to optimize an update query on InnoDB table in MySQL 5.1.73-log.
As per my understanding we can not use EXPLAIN on update query for 5.1 version.
So what will be the alternate course of action for such cases. Thanks in advance.


texts: ERD via Chen's notation: How do I organize multiple 'last done' entries?

texts:
I'm having an issue creating a little practice ERD.  I'm trying to do some more difficult stuff, and I'm not sure what the standard way to organize it is.

Basically, I don't know which of these is most correct.  Also, I'm trying to add some data: "Last video chat; last voice chat; last im; last email".

Is has a better interaction word than friends?

Should I use:

user->gold_friends->user; user->silver_friends->user,
user->has->friend; friendship->is->gold (and how would these be connected, if so), or
user->has->gold_friend; goldfriend->is->user?

Similarly, which do you prefer and why:

user->emails->user; email->was at->date, the way silver and gold come out of type in the drawing?
user->has->email_interaction, or
user->has->interaction; interaction->was via->email

If I add a friend of friendID, should I be sure that the friendID is transferred as a derived key generated with the transaction?  Why or why not?  How does the process of friend adding work, programmatically?  Is the key connected to the added user, and then transferred to the adding user, or is the key connected to the adding user and then sent to the added user, or does each user have a permanent key that is added to an array for each user at the same time when they add?


texts: Handling of Topic Drill Downs in Databases

texts:
I have a requirement wherein I have to set up a drill down feature.

For example:
There is a tab in a shopping website "Men". Now if we click on Men we will get more topics like "Clothing", "Accessories" etc. When we click on "Clothing" we again get "Tees", "Polos", "Shirts", "Jeans". Again if we click on "Tees" we get further options of "Plain", "Graphic" etc.

So a specialization kind of thing comes into picture

  Men --> Clothing --> Tees --> Graphics

I am not able to figure out an efficient way how to handle these types of feature in a database. Though this is present everywhere and is quite common, but still figuring out a way to implement this is getting tougher.

Can someone help me with some pointers so that I can do some research in the correct path.


texts:
my sites dont need use ID at all, then I am trying to manage user by username.

Assume my sites is abc.com, then access to user profile by URL: abc.com/user1

So if you want to view all info of that user, you can get by Model User ( Laravel Framework):

$user = User::find(user1);
$email = $user->email;
$sex = user1->sex;

Second method, if use ID as primary key, but my project no need to use id at all.

$id=User::where(username=user1)->id;
$user = $User::find($id);
 $email = $user->email;
 $sex = user1->sex;

As you can see, First method is faster than second, because "Username" field has indexed.
Should i use username for primary key in this situation? I use MySQL.


texts: Replicate RDS-MySQL to a non-amazon host?

texts:
I have a fairly large MySQL (5.1) database running in Amazon as an RDS instance.  I'm working on migrating it out of Amazon.  To do that smoothly I'd like to set up the new hardware as a read-only replica of the RDS instance.

I know RDS supports replication within RDS.  Is there any way to configure it to replicate to a host which is not in RDS?


texts: I have a table Player where playerID is the primary key. Then I have a table Game where gameDate is the primary key (of type DATETIME). Each game is played by 2 players and each player can play many games. So I believe there is a many to many relationship between Player and Game. I want to store both players for each game. I thought of adding player1ID and player2ID on the Game table or to have a bridge table PlayerPlaysGame with playerID and gameDate and then try to find who played the same game so that I get the pair. Any advice as to how I should design my database would be really helpful. Thanks


texts: Oracle SQL query - select or rows without all ids in other tables

texts:
I need to delete customers if customer not have any accounts or all of accounts not have any operations (no records in id_customer, id_account_sender, id_account_receiver in operation table).

example of insert to tables:

INSERT INTO CUSTOMER (ID, SECOND_NAME, FIRST_NAME, MIDDLE_NAME, BIRTHDATE, PASSPORT, PHONE_NUMBER, ID_DISCOUNT, ADDRESS) VALUES (141, 'w', 'w', 'w', '15-APR-89', '521522221', '(847) 256-4120', null, 'new 4');
INSERT INTO ACCOUNT (ID, ID_CUSTOMER, DATE_OPEN, ID_DEPARTMENT) VALUES (121, 140, TO_DATE('2019-11-12 23:41:12', 'YYYY-MM-DD HH24:MI:SS'), 4);
INSERT INTO OPERATION (ID, ID_CUSTOMER, DATE_OPERATION, TYPE, AMOUNT_TRANSFER, ID_ACCOUNT_SENDER, ID_ACCOUNT_RECEIVER) VALUES (131, 5, TO_DATE('2019-11-12 23:31:56', 'YYYY-MM-DD HH24:MI:SS'), 22, 15, 9, 8);

some queries that I tried:
all accounts without operations, but I don't know how select all customers that only contain only such accounts or no accounts even.

SELECT A1.ID, C1.ID
FROM ACCOUNT A1
  JOIN CUSTOMER C1
    ON C1.ID = A1.ID_CUSTOMER
WHERE (A1.ID NOT IN (SELECT  O1.ID_ACCOUNT_SENDER FROM OPERATION O1)) AND (A1.ID NOT IN (SELECT  O1.ID_ACCOUNT_RECEIVER FROM OPERATION O1))
ORDER BY A1.ID;

I try following query, but it give me incorrect result:

SELECT C1.ID
FROM CUSTOMER C1
  FULL JOIN ACCOUNT A1
    ON A1.ID_CUSTOMER = C1.ID
WHERE A1.ID NOT IN (SELECT A1.ID
FROM ACCOUNT A1
WHERE A1.ID NOT IN (SELECT  O1.ID_ACCOUNT_SENDER FROM OPERATION O1)) AND (A1.ID NOT IN (SELECT  O1.ID_ACCOUNT_RECEIVER FROM OPERATION O1))
ORDER BY A1.ID;


texts: Processing data in chunks with window function and where clause

texts:
I have a timeseries table where I need to process data in partitions of hours and processing of each individual row depends on the previous row.
Please consider this simple table.
CREATE TABLE IF NOT EXISTS t (
    timestamp timestamptz,
    value float
);

INSERT INTO t VALUES
    (CURRENT_TIMESTAMP + '1 minute'::interval, 1),
    (CURRENT_TIMESTAMP + '2 minute'::interval, 2),
    (CURRENT_TIMESTAMP + '3 minute'::interval, 3),
    (CURRENT_TIMESTAMP + '4 minute'::interval, 4),
    (CURRENT_TIMESTAMP + '5 minute'::interval, 5)
;

WITH
    v AS
    (
        SELECT
            timestamp,
            lag(timestamp) OVER client_window AS prev_timestamp,

            value,
            lag(value) OVER client_window AS prev_value,
            value - lag(value) OVER client_window AS diff
        FROM
            t
        WINDOW client_window AS (ORDER BY timestamp)
        ORDER BY
            timestamp
    )
SELECT
    *
FROM
    v
WHERE
    timestamp BETWEEN CURRENT_TIMESTAMP + '1 minute'::interval AND CURRENT_TIMESTAMP + '4 minute'::interval
;

The above query returns the result I want. Unfortuantely because postgres doesn't optimize across the boundaries of CTE, the following query scans the whole table.
+-------------------------------+-------------------------------+---------+--------------+--------+
| timestamp                     | prev_timestamp                | value   | prev_value   | diff   |
+===============================+===============================+=========+==============+========+
| 2020-10-01 00:54:14.172296+00 | 2020-10-01 00:53:14.172296+00 | 2.0     | 1.0          | 1.0    |
+-------------------------------+-------------------------------+---------+--------------+--------+
| 2020-10-01 00:55:14.172296+00 | 2020-10-01 00:54:14.172296+00 | 3.0     | 2.0          | 1.0    |
+-------------------------------+-------------------------------+---------+--------------+--------+
| 2020-10-01 00:56:14.172296+00 | 2020-10-01 00:55:14.172296+00 | 4.0     | 3.0          | 1.0    |
+-------------------------------+-------------------------------+---------+--------------+--------+

I've also tried the following query
WITH
    v AS
    (
        SELECT
            timestamp,
            lag(timestamp) OVER client_window AS prev_timestamp,

            value,
            lag(value) OVER client_window AS prev_value,
            value - lag(value) OVER client_window AS diff
        FROM
            t
        WHERE
            timestamp BETWEEN CURRENT_TIMESTAMP + '1 minute'::interval AND CURRENT_TIMESTAMP + '4 minute'::interval
        WINDOW client_window AS (ORDER BY timestamp)
            ORDER BY
            timestamp
    )
SELECT
    *
FROM
    v
;

Unfortunately, because of the filtering by where clause first row returns NULL for prev_value.
+-------------------------------+-------------------------------+---------+--------------+--------+
| timestamp                     | prev_timestamp                | value   | prev_value   | diff   |
+===============================+===============================+=========+==============+========+
| 2020-10-01 00:54:14.172296+00 | <null>                        | 2.0     | <null>       | <null> |
+-------------------------------+-------------------------------+---------+--------------+--------+
| 2020-10-01 00:55:14.172296+00 | 2020-10-01 00:54:14.172296+00 | 3.0     | 2.0          | 1.0    |
+-------------------------------+-------------------------------+---------+--------------+--------+
| 2020-10-01 00:56:14.172296+00 | 2020-10-01 00:55:14.172296+00 | 4.0     | 3.0          | 1.0    |
+-------------------------------+-------------------------------+---------+--------------+--------+

Is there any way to get the intended result without scanning the whole table?


texts: How to update one column with the value of another if one of their columns match

texts:
I've found a lot of similar questions, but not exactly what I need. I want to make a SQL command to find all duplicate email rows and update the notes of them with 'do not email' if it is written in the note of one of them.

In the example below I would like to update row 2,4 and 6 with the words "do not email" in the note.

ID | email | note
--------------------
1 | A | do not email
2 | B |
3 | C |
4 | A |
5 | D |
6 | A |
7 | B | do not email

Does anyone have any hints?


texts:
In the definition, it says minimal key is a minimal set of attributes needed to identify the table. Does the "minimal" part mean "minimal number of attributes" or "cannot be eliminated"?

For example:

For a relation R(A, B, C, D, E, F), the closure of A, BC, and DEF are all {A, B, C, D, E, F}.

Are they all minimal keys? Or is only A the minimal key?


texts:
The database was not responding to simple select statements like

select * from table

I noticed in sys.dm_os_waiting_tasks that one session is blocking, blocking session was pointing to a stored procedure which use lock & Tran like below

    BEGIN TRAN

declare @min numeric ,@com numeric

select top 1  @com=companyid from tblcompanies
order by newid()

select @min=min(productid) from tblproducts(UPDLOCK)
where companyid=@com and (lockstatus<>1 or lockstatus is null )

update tblproducts
set lockStatus='1'
where productid>=@min and productid<@min+4

select distinct top 4 s.productname,s.productid,s.companyid,r.Type,r.requestType,r.UserID
from tblcompanydetail rd inner join tblproducts s
on rd.productid=s.productid
inner join tblcompanies r
on r.companyid=s.companyid
where s.productid>=@min and s.productid<@min+4

if @@error >0 rollback tran

commit tran

Please advise me if I have written it wrong.


texts: Cube processing either takes too long or fails

texts:
I have a SSAS Cube with 35 dimensions and 10 measures.

Some of the dimensions are quite large in size.
In almost all dimensions data either get updated and inserted.
Measures have high volume of data.

When I run from SSMS (by right click on SSAS Database) "process full" on database it takes around 1 Hr 30 min.

When I am Processing cube (process full) from SQL Agent job via XMLA script it takes around 1 Hr 20 min.

When I processing dimensions and measures (Process Full) separately via SSMS it is taking 1 hr 50 min.

But when I am processing dimensions and measures from SSIS (DML Task via XMLA) it is taking more than 5 hours. [Memory consumption went to almost 100%]

So my questions are:

Does processing dimensions and measure from SSIS can cost that much?
What kind of processing should I choose for fast cube processing. I am doing Process full.
When I am processing cube from first two options then most of the time cube is getting failed by saying "attribute key not found" but it works well when I process dimensions and measure separately. Doesn't process full cube ensure proper processing of dimension and measures?


texts: How can I modify an existing column into a foregin key in postgres?

texts:
Given for example the following:

CREATE TABLE agency(agency_id, agency_name)
  AS VALUES (1,'Test Agency');

CREATE TABLE employee(employee_id,agency,first_name)
  AS VALUES (16,'Test Agency','Paul');

Desired output:

|---------------------|------------------|------------------|
|      employee_id    |      agency_id   |     first_name   |
|---------------------|------------------|------------------|
|          15         |         1        |       Paul       |
|---------------------|------------------|------------------|

I populated the Employee table from a JSON using ogr2ogr and naturally it wouldn't catch that the agency value is supposed to be referenced from the Agency table.

How can I ALTER or UPDATE the existing column agency in Employee table to take the agency_id of the Agency table and change its type as foreign key?


texts:
I found myself in a rather complex situation and need some advice...

We are moving our complete environment to a new Server and virtual servers.

I have reached a point where i found out that some of the old Servers (2000 and 2003) that I wanted to dispose contain Databases which are required for some of our (old but essential) software packages. These contain hardcoded data about these old servers and cannot be altered, so i'm stuck with them.

I wonder if there is a way to create a virtual name/path or sth on the new server, so i could move the old databases there, but while this is done, the software still needs to recognize them by their old name. For example - old database in on old server STHSRV1/name1 or STHSRV2/name2, and i want both of these to be on the new server, but accessible through their old names.

Is this even possible?

Thanks everyone


texts: How to find an text in entire Oracle database?

texts:
I have a Oracle database (11g2). But i don't know how to find a text in all of table on a database. Because there are so many tables, can't find down by one column, one table... it takes me a lot of times.
Is there any way to do this ? Or any tools, software can help me to do this ?
Please help me! Thank in advance.


texts:
We currently have a transaction log thats been growing incrementally and has too many VLF files.  I am going to follow a process of shrinking and then re-growing the log file to a suitable size to alleviate this problem.

My question is shrinking will only reduce from the end of the file to the last active portion of the log.  Therefore should I change to simple recovery first, then perform the shrink?

Following this I will change back to full perform a full backup to start the backup chain again.


texts:
We have a situation where we need to update on a single row several thousands time a second and this requirement lasts continuously for 5 hours every day due to the nature of the business. Design cannot be changed so we are looking for solutions. Usually we use very fast SSD drives for faster performance and so far we are able to cope up with the requests.
But our traffic is increasing every day and we need another solution that can replace our current method and help us update same row many more thousands of time a second.

Any suggestion would be great. We use MYSQL 5.6 currently


texts:
When I try to import a shapefile using db2se, I specify the input columns to be loaded in the syntax as the COLUMN NAME

db2se import_shape <dbname> -userId <username>
-pw <password> -fileName <shapefile to be loaded> -inputAttrColumns N(Cat_Zones) -srsName WGS84_SRS_1003 -tableSchema GEXPDBA -tableName TPOLYGON_STAGING -tableAttrColumns VC_FLD_1 -createTableFlag 0 -spatialColumn POLYGON_OB -typeSchema db2gse -typeName st_multipolygon -messagesFile C:\ABC1.txt -client 1

GSE2112N  The column "CAT_ZONES" cannot be found in the dBASE file.

But if I replace it with the POSITIONAL parameter, it works.

db2se import_shape <dbname> -userId <username>
-pw <password> -fileName <shapefile to be loaded> -inputAttrColumns P(1) -srsName WGS84_SRS_1003 -tableSchema GEXPDBA -tableName TPOLYGON_STAGING -tableAttrColumns VC_FLD_1 -createTableFlag 0 -spatialColumn POLYGON_OB -typeSchema db2gse -typeName st_multipolygon -messagesFile C:\ABC1.txt -client 1

GSE0000I  The operation was completed successfully.

Any reason why this would happen. The shapefile info is as below.

db2se shape_info -filename <shapefile to be loaded>

Shape file information
----------------------
File code                  = 9994
File length (16-bit words) = 2533850
Shape file version         = 1000
Shape type                 = 5 (ST_MULTIPOLYGON)
Number of records          = 2636

Minimum X coordinate = -173.109762
Maximum X coordinate = -66.954002
Minimum Y coordinate = 18.915497
Maximum Y coordinate = 71.398048
Shapes do not have Z coordinates.
Shapes do not have M coordinates.

Shape index file (extension .shx) is present.

Attribute file information
--------------------------
dBase file code                = 3
Date of last update            = 2013-09-03
Number of records              = 2636
Number of bytes in header      = 97
Number of bytes in each record = 305
Number of columns              = 2

Column Number  Column Name      Data Type       Length  Decimal
-------------  ---------------  --------------  ------  -------
            1  Cat_Zones        C ( Character)     254        0
            2  Country          C ( Character)      50        0

Coordinate system definition: "GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID
["WGS_1984",6378137.0,298.257223563]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.01
74532925199433],METADATA["World",-180.0,-90.0,180.0,90.0,0.0,0.0174532925199433,
0.0,1262]]"


texts: SQL Server 2014 Avaliability Group DbMgrPartnerCommitPolicy

texts:
I have an environment with 2 Windows Server 2012 R2 servers, both with SQL Server 2014 Expensive edition.

Whenever a change happens in the AGs (such as: AlwaysOn Availability Groups connection with secondary database established for primary database 'AGDatabase' on the availability replica 'SecondaryReplicaThatIRestarted'), I get an error like this, in my SQL error log:

  Source: SPID52s

  Message
  DbMgrPartnerCommitPolicy::SetSyncState:00000003BFC0B0E0:1

Ending with a result like this:

Imgur: Screenshot of the errorlog

EDIT:

Nothing in the Windows Error log, not even the mentioned error, which leads me to believe it's something SQL internal that's being leaked out into the errorlog.

Any ideas of what this could be caused by? I've set up multiple setups similiar to this, and this is the first time I've seen it.
I plan on patching the SQL to SP1 this week, however I don't see the error in the release notes.


texts: What should it look like when your SQL servers are properly licensed?

texts:
New job, going through my server inventory. I've been using this script to get the version & licensing:

select @@version as version,
SERVERPROPERTY('LicenseType') as licenseType,
SERVERPROPERTY('NumLicenses') as NumLicenses

But all my results are returning a disabled license, and null for the number:

version                                 licenseType NumLicenses
Microsoft SQL Server 2008 R2 (SP2) ...  DISABLED    NULL

Which has me questioning myself. Licensing isn't something I've had to deal with before. Is this query correct? Is it even possible for it to function without being properly licensed?

question: What should my query return if my server is properly licensed? Is there a better way to check?


texts: SQL Server LocalDB Instance; error restoring backup (MasterDBPath not available)

texts:
I have SQL Server 2012 Local DB Installed, and am connecting to it via (localdb)\v11.0.

When trying to restore a backup file, I get:

  TITLE: Microsoft SQL Server Management Studio

  Property MasterDBPath is not available for Information
  'Microsoft.SqlServer.Management.Smo.Information'. This property may
  not exist for this object, or may not be retrievable due to
  insufficient access rights.  (Microsoft.SqlServer.Smo)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1846+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=MasterDBPath&LinkId=20476

I have tried setting MasterDBPath string value in:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer\CurrentVersion`

but this hasn't helped.

Anyone have any thoughts?


texts: Moving Innodb database from Server A to server b

texts:
Instead of using dump, is there a way I can move the data files from one server to another instead?

We are migrating our application to a new server in January, the one concern is the downtime when taking first server offline to do a dump of the database and the time to restore it on new sever.

I'm finding dump and restore is taking a long time due to size of database.

Thanks,
Jim

UPDATE:
Operating system is windows server
Old server is 5.5
New server is 8.0


texts: I want to setup a script that sums up a bunch of values in a list and then puts down the result in total-count and then removes the entire list, And this all should be repeated once a minute or as soon as the script completes. Is it possible to make a script that does this?


texts: SQL Server If statement assigning values

texts:
Please see the pseudocode below. Basically what I'm trying to do is write a statement that when an account number contains one of the specified codes it will assign that Level to the entire account not to just that one row.

I've tried using a case statement in a CTE and few other options and I'm not having much luck. Any help is appreciated.

if AcctNo contains Code = '1'
then level = '1'
elif AcctNo contains Code = '2'
then level = '2'

AcctNo  Code  charge   level
1       2       50     Null
1       17      60     Null
1       28      61     Null
1       36      62     Null
1       18      63     Null

Desired Results

 AcctNo  Code  charge    level
    1       2       50     2
    1       17      60     2
    1       28      61     2
    1       36      62     2
    1       18      63     2


texts: Take full and differential backup on secondary replica - SQL Server 2014

texts:
I do have SQL Server 2014 high availability setup in Prod env.

Server A – Primary, B-Secondary and C -DR.
Server A to B are synchronous commit and automatic failover.
Server A to C are asynchronous commit and manual failover.

I need to setup full-backup and diff backup. Can I do it on Server C?
I have given server C 98% priority in backup preferences – AG.
Running below query on server C but it throwing 1 which means I can't take backup on that server.
SELECT sys.fn_hadr_backup_is_preferred_replica (‘dbname’);
GO


texts: Convert an interaction matrix into a ternary relation

texts:
I have what I'm calling a sparse "interaction matrix" of data in MS Excel that I wish to convert into a "ternary relation" (table) in Access.

The spreadsheet data are in this pattern:

Name   Act-1  Act-2  Act-3  ... Act-n
name-1         week
name-2                week
name-3  week                     week
  :                         week
name-m         week   week

There are n activities as Excel headings, m names in the first column of each data row, and zero or more specific weeks entered wherever a person (name) did an activity.
If the n were small and fixed, I could keep the relation as a matrix, but n can get quite large and the matrix will be very sparse.

Thus the desired relational table structure (I'll call it Assignment) is like this:

Assignment: Name, Activity, Week

Is there an easy tool in either Excel or Access for facilitating this data conversion?

A psuedocode algorithm would be

for each Name (row)
    for each Activity (column)
        if Week, add Assignment: Name, Activity, Week

While I've coded in VBA (years ago), I've never used it in conjunction with Excel.
I'm leaning towards using Python and CSV files. Maybe there's a good SQL technique (even if non-Access-specific)? Or is it something to do with "pivoting" (I never understood what that was about)?

Do you have any experience you'd like to share with this sort of conversion?


texts: How to check my MongoDB throughput and perfomance limit?

texts: How can I check how many queries per second my MongoDB is executing and whether it had reached the performance limit or not?


texts: How to force SQL Studio not to use copy-only backups to build database restore script? I want to ommit copy-only backup (because it's not longer available) and force studio to use last full backup (I mean full without copy-only option), which was done earlier of course.


texts: Using views as a source for staging table?

texts:
I am in the process of trying to build a small datamart for my small business. I am planning on initially copying tables from the source DB to another DB for staging, where I can then do any data manipulation required to create fact and dimension tables.

The documentation for my primary business application recommends using the preset views when building any custom reports, as they will not change from release to release. Although I'm not really building custom reports, I'm wondering if the same logic holds here? Should/Could I use them for building a datamart?

I don't know a lot about SQL, but from what I've read, views aren't really tables, but more like preset queries. If I did decide to use them, and was running a stored procedure from the staging database which called for data referenced by that view, does this present a performance issue (as I know that views can require a lot of joins). I suspect that the answer here is to use the original tables instead of any views, but I would like confirmation. Btw, I'm using SQL Server. Thanks.


texts: MySQL when a = 0, b = 0, but a  b (binary)

texts:
Any idea of why this is so,

CREATE TABLE f AS
  SELECT b'000000001' AS a, x'01' AS b;

This creates two columns of

`a` varbinary(2) NOT NULL,
`b` varbinary(1) NOT NULL

However, when I run,

SELECT a=0, b=0, a=b, a<>b FROM f;
+-----+-----+-----+------+
| a=0 | b=0 | a=b | a<>b |
+-----+-----+-----+------+
|   1 |   1 |   0 |    1 |
+-----+-----+-----+------+

What's going on here? From the docs on The BINARY and VARBINARY Types,

  All bytes are significant in comparisons, including ORDER BY and DISTINCT operations. 0x00 bytes and spaces are different in comparisons, with 0x00 < space.

That explains why a<>b, but why does a=0, or b=0 there?


texts: Unable to create replica set for MongoDB in Linux

texts:
When I run the following command: rs.initiate(),

I get the following error:

"info2" : "no configuration explicitly specified -- making one",
        "me" : "ip-10-0-2-113:27017",
        "ok" : 0,
        "errmsg" : "No host described in new configuration 1 for replica set s-1-rs maps to this node",
        "code" : 93

I'm just running on the local host. I'm following this guide: http://docs.mongodb.org/manual/tutorial/convert-standalone-to-replica-set/

I just skipped the part where they named the replica set.

In any case, how can I create a replica set and what am I doing wrong?

Thanks


texts: Does AWS Aurora suffer from the same limitations that MySQL suffer from?

texts: Specifically, MySQL has a limitations max row size as 64kB. I would like to know if Amazon Aurora, which is supposedly a drop in replacement for MySQL suffer the same limitation.


texts: Postgres advisory lock when key doesn't fit

texts:
I've identified that the Postgres advisory lock would be a good match for my use-case (being bound to the current session/transaction) and thus I don't want to use e.g. Redis.

The advisory lock functions are documented at https://www.postgresql.org/docs/9.6/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS having two variants: (bigint) or (int, int).

I've a resource which is 512 bit (a VARHCHAR(64) column, actually). The column is part of a table where I cannot just directly lock the row. The resource being part of that row is just one aspect.

One of the goals here is that I can detect the lock from without other parts of the application: i.e. the application must not hang/wait for the lock but give me back whether the resource is currently locked or not.

Is there a way to express an advisory lock on a resource requiring more storage?  This is a problem because I don't have any unique key that I could use for this purpose.

I'm using Postgres 9.6.3.


texts: Views that inherit a table and syntax in PostgreSQL

texts:
I am getting a syntax error but don't understand what is wrong.

I am using a reference here.

This is my sql-snippet:

CREATE OR REPLACE VIEW stipa_test.stipa_view
AS
  (
    SELECT *
    FROM ...
    WHERE ...
  )
INHERITS (stipa_test.stipa_type_proj);

I hope you get the idea, everything but inheritance works. So how could it look like? both the subquery view and the type_proj table


texts: How to ignore - Error while PROCESSING TOC

texts:
I have a bash script which restores the tables using pg_restore. When I run the script it throws Error while PROCESSING TOC. However, the tables were restored successfully. But the pg_restore command returns exit status of 1 to the bash.
How can I tell postgres to ignore those TOC errors so that it returns exit status of 0 to the bash.

Here is the full error I get:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 9368; 2620 814383804      TRIGGER replicate_track_truncate_public_regex_property_trigger auser
pg_restore: [archiver (db)] could not execute query: ERROR:  trigger "replicate_track_truncate_public_regex_property_trigger" for table "regex_property" does not exist
Command was: DROP TRIGGER replicate_track_truncate_public_regex_property_trigger ON  public.regex_property;


texts: SQL Server 2008 R2 Partitioning - same FileGroup, 1 File, 2 partition_numbers - HELP

texts:
It's my first go at partitioning in SQL Server, I learned from the Brent Ozar guide which is fantastic :)

A few times I have ran into a weird scenario; when I run:

SELECT *
FROM ph.FileGroupDetail
ORDER BY partition_number
Go

There is the same filegroup showing twice with 2 different partition_numbers, 1 correctly at the end with a range value, the other at the start with a null range_value.

click here for enlarge image

Couple of questions:

How is this happening, where have I gone wrong?
How do I resolve the issue, that means how to get rid of the one at the start as I already have an empty partition at the beginning.

I've tried deleting the file (worked when it was empty) and filegroup, but filegroup said it couldn't be deleted.

Can someone explain please how this has happened and how to get rid of the partition 2 entry?


texts: Limit redo for materialized view complete refresh or manual equivalent

texts:
A materialized view(MV) log can be used to allow a MV to do a fast refresh which only modifies the data that has changed.  However, various conditions prevent the MV from using the log and therefore require a complete refresh.  Oracle implemented an atomic complete refresh as a delete and insert of every record.  It does this even if there are ultimately no changes to the data.

Is there a way to make this replication intelligent with regard to redo generation?  A MERGE followed by a DELETE requires querying the source twice.  Would it be worth it to bulk collect the data to do a BULK MERGE and DELETE?  Is there a better way?

Update:

I explored using a global temporary table as a staging area.  Although they use less than half the redo, they still use to much.


texts: Solution to assigning unique values to rows with finite collaboration distance

texts:
I have a table that can be created and populated with the following code:

CREATE TABLE dbo.Example(GroupKey int NOT NULL, RecordKey varchar(12) NOT NULL);
ALTER TABLE dbo.Example
    ADD CONSTRAINT iExample PRIMARY KEY CLUSTERED(GroupKey ASC, RecordKey ASC);
INSERT INTO dbo.Example(GroupKey, RecordKey)
VALUES (1, 'Archimedes'), (1, 'Newton'), (1, 'Euler'), (2, 'Euler'), (2, 'Gauss'),
       (3, 'Gauss'), (3, 'Poincaré'), (4, 'Ramanujan'), (5, 'Neumann'),
       (5, 'Grothendieck'), (6, 'Grothendieck'), (6, 'Tao');

For all rows that have a finite collaboration distance based on RecordKey with another row, I would like to assign a unique value—I don't care how or what data type the unique value is.

A correct result set that meets what I'm asking for can be generated with the following query:

SELECT 1 AS SupergroupKey, GroupKey, RecordKey
FROM dbo.Example
WHERE GroupKey IN(1, 2, 3)
UNION ALL
SELECT 2 AS SupergroupKey, GroupKey, RecordKey
FROM dbo.Example
WHERE GroupKey = 4
UNION ALL
SELECT 3 AS SupergroupKey, GroupKey, RecordKey
FROM dbo.Example
WHERE GroupKey IN(5, 6)
ORDER BY SupergroupKey ASC, GroupKey ASC, RecordKey ASC;

To better aid what I'm asking, I'll explain why GroupKeys 1–3 have the same SupergroupKey:

GroupKey 1 contains the RecordKey Euler which in turn is contained in GroupKey 2; thus GroupKeys 1 and 2 must have the same SupergroupKey.
Because Gauss is contained in both GroupKeys 2 and 3, they too must have the same SupergroupKey. This leads to GroupKeys 1–3 having the same SupergroupKey.
Since GroupKeys 1–3 don't share any RecordKeys with the remaining GroupKeys, they are the only ones assigned a SupergroupKey value of 1.

I should add that the solution needs to be generic. The above table and result set was merely an example.

Addendum

I removed the requirement for the solution to be non-iterative. While I would prefer such a solution, I believe it's an unreasonable constraint. Unfortunately, I am unable to use any CLR-based solution; but if you want to include such a solution, feel free to. I will likely not accept it as an answer though.

The number of rows in my real table is as large as 5 million, but there are days when the number of rows will "only" be around ten thousand. On average there are 8 RecordKeys per GroupKey and 4 GroupKeys per RecordKey. I imagine that a solution will have exponential time complexity, but I am interested in a solution nonetheless.


texts: How to identify queries that cause high Logical I/O?

texts:
I need some help to identify SQL queries that could suddenly increase the SQL Server Logical I/O.
Can someone please help to provide some advice? This was from SQL Server 2017


texts: Fault tolerant cross datacenter MySQL replication & failover strategy

texts:
Which of the following method is the most fault tolerant way to achieve cross datacenter MySQL replication using DNS failover and why?

MySQL master-master replication with one primary/active node and the other acting as hot spare. When the active node is down, the DNS failover switches to hot spare. Question: Does this setup achieve two-way sync reliably & automatically since both nodes are slaves to each other? If yes, the only manual action required would be to switch DNS to primary once its up and sync is complete.
MySQL master-slave replication with master as primary/active node and slave acting as hot spare. When the active node is down, the DNS failover switches to hot spare. This setup requires manual recovery of master from slave after failover as explained here or here. Question: Is this more reliable or fault tolerant than option 1? (I've read of master-master replications having issues with conflicts and sometimes even where both the DBs are out of sync).

What other better options are there that are similar in complexity to setup/manage?

Updates:

Tested out option 1 today with a very complex stack and so far so good. The DNS FO and fault-tolerance is pretty good for production use.


texts:
I have this logon trigger to restrict IP addresses.   It is the same trigger I have seen everywhere.  However, it is not working as expected, and I was hoping someone could give some direction.

I am able to connect to this account / server from SQL*Plus and from a PHP script on a server where the IP is not in the list of IP's.

CREATE OR REPLACE TRIGGER check_ip_addresses_trg
AFTER LOGON
ON DATABASE

BEGIN
  IF USER IN ('JSMITH') THEN
     IF SYS_CONTEXT('USERENV', 'IP_ADDRESS') NOT IN
       ('192.168.170.34',
        '192.168.170.39',
        '192.168.170.57',
        '192.168.170.62') THEN
       RAISE_APPLICATION_ERROR(-20001, 'Can not log in from this IP address (' || SYS_CONTEXT('USERENV', 'IP_ADDRESS') || ')');
     END IF;
 END IF;

But, if I try from a different server:  sqlplus jsmith/password@dbserver.dbase.com, I am able to log into SQL*Plus successfully.   From this one PHP script I wrote, I also try to connect to this account, and I am able to connect successfully.

We are running SE 11g R2.   I had expected an error message.   Any thoughts?


texts: MySQL replication not finished on next site call

texts:
I have 3 MySQL8.0 instances. Two of them are slaves. Each site call I‘m reading the slave till the first write Request is made. Afterwards Only the Master is used during the request. Now, if I write something into the database and redirect the user to a new page, the slave isn‘t updated yet. Is there any way I can handle such things?

Solutions I already found downgrade to MySQL 5.7 with Galera Cluster/Percona xtraDB/Group Replication.

Any other ideas? Which would you prefer?


texts: 294 : Failed with error 'aborted', from rs4 to rs0

texts:
Regarding failed with error aborted will we lose the chunks

In logs I found this:

2017-09-14T01:35:21.533+0900 W SHARDING [conn38309]
Chunk move failed :: caused by :: ChunkRangeCleanupPending:
can't accept new chunks because there are still 4 deletes from previous migration

2017-09-14T01:35:11.439+0900 I COMMAND  [conn38309] command admin.$cmd
command: moveChunk { moveChunk: "transam_db.panelist",
shardVersion: [ Timestamp 762000|1, ObjectId('590b4564c5ac1ee3b6e9050d') ],
epoch: ObjectId('590b4564c5ac1ee3b6e9050d'),
configdb: "configReplSet/mgdb07:27019,mgdb08:27019,mgdb09:27019",
fromShard: "rs1", toShard: "rs4",
min: { panelist_id: 407157 },
max: { panelist_id: 416836 },
chunkVersion: [ Timestamp 762000|1, ObjectId('590b4564c5ac1ee3b6e9050d') ],
maxChunkSizeBytes: 67108864, waitForDelete: false, takeDistLock: false }
exception: can't accept new chunks because there are still 4 deletes from previous migration
code:200 numYields:75 reslen:278 locks:{ Global: { acquireCount: { r: 161, w: 3 } },
Database: { acquireCount: { r: 79, w: 3 } },
Collection: { acquireCount: { r: 79, W: 3 } } }
protocol:op_command 2622ms

Due to this, it's unable to move the chunks. Please give a solution.


texts: How do I use an Ispell dictionary with Postgres text search?

texts: Postgres can use Ispell-compatible dictionaries in text search, but does not provide the required files.


texts: Restore .mdf data file for a File Group in SQL Server

texts:
I have a database with three filegroups and each filegroup hosting one datafile. One of the secondary filegroups XYZ with data file xyz.ndf got updated with wrong/bad information in various tables under this filegroup. Before these various bad changes were made I took a backup of the xyz.ndf file using SSMS --> Backup Component --> Files and Filegroups.

I have an urgent need to restore only xyz.ndf file using the backup without impacting the operation of the  remaining datafiles

For this I ran the query below

RESTORE DATABASE AAAA FILEGROUP='XYZ' FROM dISK='C:\MyLoc\XYZ_Backup.bak'
  WITH RECOVERY

I get the message below.. and there were no log backups after my original backup. What should i do to fix this?

  Processed 48 pages for database 'AAAA', file 'xyz' on file 1. The roll
  forward start point is now at log sequence number (LSN)
  40000000032600001. Additional roll forward past LSN 40000000034300001 is required to complete the restore sequence. RESTORE DATABASE ...
  FILE= successfully processed 48 pages in 0.054 seconds (6.944
  MB/sec).


texts: Tracing, debugging and fixing Row Lock Contentions

texts:
Off late, I've been facing a lot of row lock contentions. The table in contention seems to be a particular table.

This is generally what happens -

Developer 1 starts a transaction from Oracle Forms front end screen
Developer 2 starts another transaction, from a different session using the same screen

~5 minutes in, the front end seems unresponsive. Checking sessions shows row lock contention. The "solution" that everyone throws around is to kill sessions :/

As a database developer

What can be done to eliminate row lock contentions?
Would it be possible to find out which line of a stored procedure is causing these row lock contentions
What would be the general guideline to reduce/avoid/eliminate such problems which coding?

If this question feels too open-ended/insufficient information please feel free to edit/let me know - I'll do my best to add in some additional information.

The table in question is under a lot of inserts and updates, I'd say it's one of the most busiest tables. The SP is fairly complex - to simplify - it fetches data from various tables, populates it into  work tables, a lot of arithmetic operations occur on the work table and the result of the work table is inserted/updated into the table in question.

The database version is Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit. The flow of logic is executed the same order in both the sessions, the transaction isn't kept open for too long ( or at least I think so), and the locks occur during active execution of transactions.

Update: The table row count is larger than I expected, at about 3.1 million rows. Also, after tracing a session I found that couple of update statements to this table are not utilizing the index. Why is it so - I'm not sure. The column referenced in the where clause  is indexed. I'm currently rebuilding the index.


texts:
I am playing and testing with my.cnf and at the moment I have these server specs:
12 CPU vCore
500 GB SSD
24 GB RAM
1 GB Swap
and this is the input of my my.cnf:
# GENERAL #
# user                           = mysql
# default-storage-engine         = InnoDB

# DATA STORAGE #
# datadir                        = /var/lib/mysql/

#TIMEOUTS#
wait_timeout                    = 600

#NO DNS#
# skip-name-resolve

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max_connections = 250
# max-allowed-packet             = 16M
# max-connect-errors             = 1000000

# CACHES AND LIMITS #
tmp_table_size = 512M
max_heap_table_size = 512M
join_buffer_size = 384K
query_cache_size = 0
query_cache_type = 0
# thread-cache-size              = 32
# open-files-limit               = 65535
# table-definition-cache         = 4096
# table-open-cache               = 1024

table_definition_cache = -1
performance_schema = ON

# INNODB #
innodb_lock_wait_timeout = 600
innodb_flush_method = O_DIRECT
innodb_buffer_pool_size = 10G
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_stats_on_metadata = 0
innodb_buffer_pool_instances = 10
innodb_file_per_table = 1

#LOG QUERIES#
# long_query_time                = 3
# log_slow_queries               = 1
# slow_query_log_file            = /var/lib/mysql/mysql-slow.log

[mysqld]
# Skip reverse DNS lookup of clients
# skip-name-resolve

I just made the exprience, that as soon as join_buffer_size exceeds 512k, suddenly the CPU load goes up to 100%. How can this be explained?


texts: What is the Query to display the failed sql jobs,so that I can get all the failed job information with one query


texts: How Do I Filter a Group of Rows based on one Column's Value in TSQL?

texts:
I am trying to make a SQL query in Server 2008 R2 that selects the rows only for employees who have over 40 hours in a pay week and a pay description that doesn't include "over time".  So far I have the following:

WITH RESULTS AS
(
SELECT
      [FullName]
  ,[PayHours]
  ,[SSN]
  ,[EffectiveDate]
  ,[PayDescription]
  ,[OrderNumber]
  ,[WeekStart]
  ,[WeekEnd]
  ,SUM(PayHours)OVER(Partition BY FullName,WeekStart) AS TotalHours

  FROM [viewReportPotentialOverTime]

  WHERE EffectiveDate BETWEEN '01/01/2015 00:00:00' AND '10/23/2015 23:59:59'
 Group By FullName,SSN,WeekStart, WeekEnd, EffectiveDate, OrderNumber, PayDescription, PayHours

  )
SELECT * FROM RESULTS
Having TotalHours > 40 --AND PayDescription !='Overtime Pay'
Order By FullName, WeekStart, EffectiveDate

This gets me close to what I need.  The problem I'm having is filtering out the rows for employees that do include a PayDesciption of 'Overtime Pay'.  When I uncomment the !='Overtime Pay' then it filters out just the one row for that pay week that was 'Overtime Pay' and leaves all of the Regular Pay rows. However, what I need is for it to filter out all rows for that pay week (which I used the WeekStart column to determine the pay weeks)for that employee. I'm using this as an audit to make sure whenever someone worked over 40 hours in a week that they were paid OT.

I've also tried a similar version using the HAVING clause, but the problem I run into there is that you can't have a windowed function in a HAVING clause.


texts: How to check particular SQL UserID windows permissions in MS SQL SERVER 2012?

texts:
I would like to say as per my network system administrative security of windows Server 2012 R2. I am unable to start SQL Server agent. As per in MS SQL Server 2012 , i have "sysadmin" & "public"  privilege.
As per microsoft documentation (https://msdn.microsoft.com/en-us/library/ms178130(v=sql.110).aspx) those who have below mention windows credential then he/she will be able to run SQL Server agent.

Security

Permissions

To perform its functions, SQL Server Agent must be configured to use the credentials of an account that is a member of the sysadmin fixed server role in SQL Server. The account must have the following Windows permissions:

•
Log on as a service (SeServiceLogonRight)

•
Replace a process-level token (SeAssignPrimaryTokenPrivilege)

•
Bypass traverse checking (SeChangeNotifyPrivilege)

•
Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)

How to check particular SQL UserID windows permissions in MS SQL SERVER 2012 , to avail all the feasibility of "SQL Server agent" services.


texts: Recommended way to test code updates in Oracle

texts:
We have lots of self-built high performance Oracle 12c database servers with two quite large instances (OLTP and data warehouse) for development purposes. Our PL/SQL developers sometimes have to redefine table structures, update millions to billions of rows, create/test new indexes and so on. At the moment everytime the update scripts run and the data is changed, we have to re-import all tablespaces, recreate replication, etc. because we need to test the updates again. This procedure takes about 3-5 hours (much of it running in the background) until the developers can access the server again. In the meantime they switch to another, unchanged server to test again, which, of course, is then unusable after the update (repeat as often as needed...).

My teams tasks include recreating the databases for these exact purposes, which is annoying, to say the least.

I'm trying to come up with some alternatives and have looked into creating RMAN backups prior to the tests, ARCHIVELOG-mode, huge UNDO tablespaces or retention time, FLASHBACK RECOVERY, etc.
Nothing I've come up with is exactly what I need, I think.

Mind: I've got very little DBA-experience and all of that little experience is self-taught or learning by doing.

So what I need or try to comprehend is, what techniques could I use to reset the database (or the changed tables / data) to some point in the past (maybe even five minutes ago), without having to re-import the database?
I think ARCHIVELOG mode and restoring the changed tables / data might be the best way, but I'm not remotely sure.

Could someone point me in the right direction?


texts: How to go to definition for PostgreSQL functions?

texts:
Is there a support in pgAdmin or another IDE environment that allows one to "go to definition" of a SQL function?

I am chasing a chain of PL/PGSQL and SQL function calls in a PostgreSQL extension, and often got lost while switching between multiple locations in the source files. This makes me wonder if there is some support in the SQL world similar to the go to definition function in Eclipse (Ctrl + Click) and many other IDEs/languages.


texts: Using MyISAM for reading and InnoDB for writing data

texts:
I read many articles about comparisons of MyISAM and InnoDB from performance point of view and I decided to use MyISAM for reading data and InnoDB for writing data.

I want to use two structure like an image below. Two same (synchronized) databases but different table engines.

What is your suggestions about this structure?
What are disadvantages of this design?


texts: What permission needs to be added to see the Security folder in Azure SQL?

texts:
I have the Security folder, and I am trying to setup other Users with the same permissions that I have.

My User is not the Administrator. Though I was granted all of my permissions (which is about everything) by the Administrator. But this was done months ago and the Administrator (just a dude) does not remember what permissions they gave me.


texts:
Suppose I have Table A

|-10|
|-1 |
|0  |
|1  |
|10 |
|100|

and take the most significant bit position of each value and import it into Table B, like so:

|-4|
|-1|
|0 |
|1 |
|4 |
|7 |

How do we do it efficiently? Is there a user-defined function that mimics the behavior of __builtin_clz as with GCC?

UPDATE: Added example for zero and negative values.


texts: Filestream directory exists for a partition but the corresponding partition does not exist in the database

texts:
I am investigating a high memory usage issue of tempdb on a production SQL Server 2017 Enterprise (64-bit) deployment. This is similar to an issue discussed here. The server has two production DBs each with a FILESTREAM. As part of my investigation, I ran

DBCC CHECKDB ([db_name]) WITH ESTIMATEONLY;

to check if tempdb is sized correctly. The output was as follows.

  DBCC results for 'db_name'.
  Estimated TEMPDB space (in KB) needed for CHECKDB on database db_name = 53.
  Msg 7933, Level 16, State 1, Line 1
  Table error: A FILESTREAM directory ID b1e50dcc-0511-4488-8eeb-ea34966edfab exists for a partition, but the corresponding partition does not exist in the database.
  Estimated TEMPDB space (in KB) needed for CHECKDB on database db_name = 16379997.
  CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I was surprised by the error considering that the senior DBA has all of Ola Hallengren's scripts running on a schedule. No errors were reported by these scheduled scripts and despite the error, the DBs are still functioning with the FILESTREAM.

I continued my troubleshooting on a test environment with a restore of the full production DB backups. As expected the same error is present. Consequently, I ran

DBCC CHECKDB ([db_name]) WITH NO_INFOMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY;

but no errors were found. I wanted to execute

DBCC CHECKFILEGROUP

but as per the documentation, it cannot be executed on a FILESTREAM filegroup.

Question

Is the FILESTREAM-partition-does-not-exist-in-database-error a problem? Can it be resolved? It does not seem that any of the other DBCC commands detect a problem.


texts: SSMS Crashes when attempting to restore DB from Azure URL

texts:
I'd previously been using the April 2016 version of SQL Server Management Studio with no issues, but have run into problems since updating to the GA June 2016 version of SSMS.  When I try to restore a DB from Azure blob storage, SSMS immediately crashes.  I'm choosing Restore Database > Device > clicking the browse (3 dots) button > changing backup media type to "URL" > clicking the "Add" button.  SSMS crashes immediately upon selecting the "Add button."
I've tried going back to the April 2016 version, removing all versions of SSMS, repairing SQL 2014, reapplying SP1 and CU6, and reinstalling the new June SSMS.
I'm wondering if it has something to do with the saved Azure credentials that SSMS was using to access the blob storage.  How do I remove those credentials to see if that's the case? Any help is greatly appreciated!

Event viewer details:

Application: Ssms.exe
Framework Version: v4.0.30319
Description: The process was terminated due to an unhandled exception.
Exception Info: System.Exception
   at Microsoft.SqlServer.Management.Smo.RegSvrEnum.DataProtection.UnprotectData(Byte[], Int32)
   at Microsoft.SqlServer.Management.Smo.RegSvrEnum.DataProtection.UnprotectData(System.String)
   at Microsoft.SqlServer.Management.Smo.RegSvrEnum.UIConnectionInfo.get_Password()
   at Microsoft.SqlServer.Management.SqlMgmt.AzureConnectToStorageDialog.HasAccountInformation(System.Object, System.ComponentModel.PropertyChangedEventArgs)
   at Microsoft.SqlServer.Management.SqlMgmt.AzureConnectToStorageDialog.set_DataSource(Microsoft.SqlServer.Management.SqlMgmt.AzureConnectToStorageViewData)
   at Microsoft.SqlServer.Management.SqlMgmt.AzureConnectToStorageDialog.AzureConnectToStorageDialog_Load(System.Object, System.EventArgs)
   at System.Windows.Forms.Form.OnLoad(System.EventArgs)
   at System.Windows.Forms.Form.OnCreateControl()
   at System.Windows.Forms.Control.CreateControl(Boolean)
   at System.Windows.Forms.Control.CreateControl()
   at System.Windows.Forms.Control.WmShowWindow(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.Control.WndProc(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.ScrollableControl.WndProc(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.Form.WmShowWindow(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.Form.WndProc(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.Control+ControlNativeWindow.OnMessage(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.Control+ControlNativeWindow.WndProc(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr, Int32, IntPtr, IntPtr)

More details:

Faulting application name: Ssms.exe, version: 2015.130.15000.23, time stamp: 0x573fd920
Faulting module name: KERNELBASE.dll, version: 6.3.9600.18264, time stamp: 0x56e1b34d
Exception code: 0xe0434352
Fault offset: 0x00015b68
Faulting process id: 0x2900
Faulting application start time: 0x01d1bf54f078f2e5
Faulting application path: C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe
Faulting module path: C:\Windows\SYSTEM32\KERNELBASE.dll
Report Id: 3c1dd04f-2b48-11e6-80c7-00155d745d0a
Faulting package full name:
Faulting package-relative application ID:


texts: Seconds_Behind_Master alternates between 0 and a number x

texts:
I have a slave server with mariadb 10.3.10 on debian 9. For several days the server has been working correctly, Seconds_Behind_Master was always kept at 0 indicating that it was properly synchronized. Since yesterday Seconds_Behind_Master has started to alternate between 0 and numbers greater than 1000. I run show slave status and see:
  Seconds_Behind_Master: 3893, then I run it again and Seconds_Behind_Master: 0 then Seconds_Behind_Master: 3895 and it has kept growing and decreasing. Can someone help me solve this problem?


texts: Patching SQL Server 2008 and upgrading to 2014

texts:
The environment which I am working right now is on SQL Server 2008, 2008 R2. Do you think its a good idea to first patch it with the latest service pack and then upgrade to SQL Server 2014?

Thanks


texts: Confusion about connection_id in SQL Server DMV

texts:
I am confused about the connection_id column of sys.dm_exec_connections DMV:

SELECT connection_id FROM sys.dm_exec_connections;

If I connect to an instance of SQL Server using SSMS, should it be one physical connection only? And multiple New Query windows would represent different sessions represented by different session_ids or @@SPIDs?

In particular, why does the result set of the above query return an different uniqueidentifier for each session (and sessions that do seem to be currently open) of this same server connection?

Is this connection the same as the physical connection to an instance of SQL Server?

Thanks


texts: Prepared statements with many parameters is slow

texts:
I have an application written in PHP with Laravel that regularly prepares and executes statements like this:

   -- All parameters are varchar(10)
   SELECT c1, c2, c3, c4
      from MyBigTable
     where is_active = 1
       and c1 in (@P1, @P2, @P3, @P4 ... @P250)
       AND c2 is not NULL

Users have a big data grid, and they can select many rows (there is even a button to select 'ALL'). If they select 250 rows, this statement is what happens. But it takes more than one minute to run, which is unacceptable.

Table MyBigTable has about 10 millions rows. The estimated execution plan shows that 100% of time is spent on an "index seek, non-clustered". From this I deduce that the situation can not be improved using indexes, and that the only issue is in the use of prepared statements. (If you think I am wrong, just let me know). Moreover, I understand that these prepared statements are prepared, used once and discarded, so I don't think they are really beneficial.

What recommendation should I give to the developers?

Should I just tell them to stop using prepared statements, and hardcode the 250 values in the query?

Or should I give them some workaround, like the use of temporary tables (make a temporary table, insert 250 values, then make a query on MyBigTable JOINed with temp)?

Or any other idea?

EDIT: execution plan https://www.brentozar.com/pastetheplan/?id=rJ-b2XalH


texts:
I'm storing documents with fields that are set in a DataSource table. The document is written to the database as follows:

Document table (ID, DataSourceID)
Document fields (DocumentID, DataSourceField, FieldValue)

For example:

Document
100 | "Invoice"
101 | "Receipt"

Fields
100 | "Date" | 2011/12/01
100 | "Amount" | 1200,00
101 | "Date" | 2011/12/02
101 | "Warehouse" | "Central"

The documents have many more fields. Now, the question is how to write the fastest query to find documents by entering some filter values on fields (ie. Date between '2011/12/1' and '2011/12/31' and Amount > 1000,00)?

This is what I came up:

select * from Document, Fields
where DocumentID = FieldDocumentID and DataSource = "Invoice"
and DocumentID in (select FieldDocumentID from Fields where Field = "Date"
and FieldValue between '2011/12/01' and '2011/12/31')
and DocumentID in (select FieldDocumentID from Fields where Field = "Amount"
and FieldValue >= 1000)

I'm not sure how well this is going to work with millions of rows. How could I do this better?


texts: Why does a Set Returning Function (SRF) run slower in a FROM clause?

texts:
This is a database-internals question. I'm using PostgreSQL 9.5, I'm wondering why Set Returning Functions (SRFs), also known as Table Valued Functions (TVFs) run slower when in a FROM clause, for instance when I execute these commands,

CREATE TABLE foo AS SELECT * FROM generate_series(1,1e7);
SELECT 10000000
Time: 5573.574 ms

It's always substantially slower than,

CREATE TABLE foo AS SELECT generate_series(1,1e7);
SELECT 10000000
Time: 4622.567 ms

Is there a general rule that can be made here, such that we should always be running Set-Returning Functions outside of a FROM clause?


texts: extract state changes from snapshot-based archive table

texts:
I have a table snapshot, storing snapshots (of the new state) every time an object stored in another live changed one of its attributes. I need to find all objects that changed a specific variable to a specific value in a given time frame.

For example, assume the following layout:

    CREATE TABLE snapshot
    (
        "timestamp" timestamp with time zone NOT NULL,
        person text NOT NULL,
        haircolor text NOT NULL,
        city text NOT NULL,
        CONSTRAINT snapshot_pkey PRIMARY KEY (person, "timestamp")
    )

Here, I want to find all persons that moved (from any other city) to NY between 2006-02-01 and 2006-02-14. I want all of them, even the ones that moved in on 2006-02-03 and moved out on 2006-02-05 again. (And of course I do not want to list people who only changed their haircolor in the given time frame).

What would an efficient query look like?


texts:
I cannot make an update query work. As a first step, the following one works correctly:

UPDATE Tab1
SET Tab1.a = '3'
WHERE
    Tab1.id IN ( 123, 456 );

where id is the primary key of Tab1.
However when I add a select to get the set of values Tab1.id must be in, I get the following MySQL error:

  Error code: 1093
  You can't specify target table 'Tab1' for update in FROM clause.

This is the complete query:

UPDATE Tab1
    SET Tab1.a = '3'
    WHERE
        Tab1.id IN (
            SELECT Tab1.id
            FROM Tab1, Tab2
            WHERE Tab1.b = Tab2.b AND Tab1.c = '4'
        );

I cannot see how to fix this error.
Am I forced to split this query in two?

Thank you!


texts:
I'm new in PostgreSQL database. Recently our developer needed to do some upgrades in our systems.

Because of that we are planning to implement some method in order to implement database failover.

Based on my reading from postgresql wiki here, we are trying to implement either warm standby or hot standby.  So my questions are:

What are major differences between them?
Which one is better?
Is there any other method that we can consider for making high availability in our Postgres databases?


texts: Ola Hallengren Maint Solution - USER_DATABASES vs @AvailabilityGroup = AG1

texts:
I have two SQL 2016 Instances that are configured to use Always On AGs (1 AG group with around 25dbs in it running between the two instances) There are also a number (5-10) of Read Only databases on each instance that are not in the AG.
Using Ola's Maintenance Solution I use @Databases = 'USER_DATABASES' and this backs up ALL databases on the primary/secondary depending on where I run the job from.
However if I used @AvailabilityGroup = AG1 that would just backup all databases in AG1, that I get, but I am wondering if I need a separate job for backing up dbs in the AG or is that just good house keeping and my decision alone?  Just trying to think of an example where I would need to use @AvailabilityGroup = AG1 in the OH code.


texts: How to Query Properties of an Entity in Apache Cassandra

texts:
I am new to Apache Cassandra, and I would like to know how to create a table in which I can filter rows by non-key columns.

In relational databases, I would simply create a table with an ID as a primary key, and then execute a SELECT, filtering by non-key column entries. In Cassandra however, I can not do such a thing.

I have already read about secondary indexes in Cassandra. I know that I could add indexes to the property columnss and then execute a simple CQL SELECT, similar to relational databases, and which yields me the desired rows, but that would be inefficient. Isn't there a better way?


texts: How to add a linked server to a sql server management studio?

texts:
I have an account in sql server management studio, which I can login to database B, which is on server X.
I also have a crystal report on my local PC.  Using the crystal report, I can access a database (A) thru a ODBC driver, which is DRV_A, install on my local PC.  I can write reports against the database A using the crystal report.

Now I want to directly from sql server management studio and database B to query both database B and A.  So it looks I need to add database A as a linked server to database B.

When I try to do that, I get the following error message:

Cannot show request dialog
  Additional information:
   A requred operation could not be completed.  You must be a member of the susadmin role to perform this openration.  (SqlManagerUI)

So, it seems that

I need to get a role of permission to add the linked server
Install the ODBC driver on server X

What else I should have, IP address and login name, password?

Thank you very much.


texts: Distribution statistics on VARCHAR / VARGRAPHIC columns?

texts:
It appears that only the first n characters is used as colvalue for varchar/vargraphic columns. From my test below it appears to be 33/16. Is the length documented somewhere? I tried to search the documentation, but can't find anything (probably searching for the wrong thing)

Simple test case:

DROP TABLE LEJO0004.T;
CREATE TABLE LEJO0004.T (
        ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY
            ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 NO CYCLE CACHE 20 NO ORDER ),
        TCOL_C VARCHAR(64) NOT NULL,
        TCOL_G VARGRAPHIC(64) NOT NULL
);

insert into LEJO0004.T (tcol_c, tcol_g)
with n100 (n) as ( values 0 union all select n+1 from n100 where n<100 )
   , n10  (n) as ( values 0 union all select n+1 from n10 where n<10 )
   , n1   (n) as ( values 0 )
select 'commons.domain.regel.avbrottsregel'
     , 'commons.domain.regel.avbrottsregel'
from n100
union all
select 'commons.domain.regel.grupp.overfors.nya.endast.som.merit'
     , 'commons.domain.regel.grupp.overfors.nya.endast.som.merit'
from n10
union all
select 'commons.domain.regel.ingar.i.grupp.overfors.till.nya'
    ,  'commons.domain.regel.ingar.i.grupp.overfors.till.nya'
from n1;

runstats on table lejo0004.T with distribution on all columns;

select cast(colname as varchar(15))
    ,  type
    ,  seqno
    ,  cast(colvalue as varchar(40))
    ,  valcount
from sysstat.COLDIST
where tabschema = 'LEJO0004'
  and tabname = 'T'
  and colname in ('TCOL_C', 'TCOL_G')
  and colvalue is not null
  and type = 'F'
order by type, colname, seqno;

Result is:

TCOL_C  F 1 'commons.domain.regel.avbrottsrege' 101
TCOL_C  F 2 'commons.domain.regel.grupp.overfo'  11
TCOL_G  F 1 g'commons.domain.r'                 113

Appears to be the same for Q

values char_length(g'commons.domain.r' using codeunits32)
16

values char_length('commons.domain.regel.avbrottsrege' using codeunits32)
33

Tested on:

db2level
DB21085I  This instance or install (instance name, where applicable:
"lejo0004") uses "64" bits and DB2 code release "SQL11050" with level
identifier "0601010F".
Informational tokens are "DB2 v11.5.0.0", "s1906101300", "DYN1906101300AMD64",
and Fix Pack "0".
Product is installed at "/home/lejo0004/sqllib".


texts: Is there a way to determine what caused the last growth operation of my database (MDF file)?

texts: If I don't already have any logging in place, is there any way to use the DMVs or anything else on SQL Server to determine which query / event caused my database's last grow operation?


texts: Missing the pg_ctl package in Postgres 9.3 installation

texts:
I have installed postgresql 9.3 on ubuntu 14.04 using "apt-get install postgresql".

Everything was going well until I discovered that I do not have access to the "pg_ctl" commands. Installing the postgres-xc literally breaks postgresql server. The package links become broken. I cannot re-install and have had to revert my VM and keep working without pg_ctl.

My question is, to get access to this package, should I start from scratch or uninstall and re-install postgresql?
What should I have installed in the first place?
Alternatively, is there something wrong with my path?

(I am new to postgresql and have been tossed into it for work purposes)