texts: DISTINCT column combination with permutations

texts:
My postgresql column structure looks like this:

id  | from | to
---------------
1   |  A   |  B
2   |  A   |  B
3   |  C   |  D

Now I want an result which looks like this:

 res
-----
'A:B'
'B:A'
'C:D'
'D:C'

where the first and the rows are permuted from A:B to B:A and 'C:D' to 'D:C' and the second column is omitted due to distinct operation.


texts: Using 100's of schemas in a database SQL2008R2?

texts:
We are in SSAS type of environment with 100's of clients.

Currently each client gets a database and we have 200 per instance on SQL Server 2008R2. This presents limitations with snapmanager being able to do backups as the recommended number is 35 databases per instance (netapp).

One of the solutions we are looking into is to consolidate the databases - collecting say 150 of them into one database using separate schemas.

Has anybody done anything like this?
Are there limitations on the number of schemas per database, or performance issues vs using separate databases that we should be aware of?

Thank you for your answers.

The reason we need Snapmanager is because that is what we currently use for our DR replication and their recommendation is 35 databases per instance because otherwise it might take longer than an hour to backups/transfer the data which breaks our 1 hour recovery requirement.

But according to them if we were to have 1 database(or under 35) per instance they should be able to make it no issues.

So that is why we were looking potentially into schemas but that would mean most likely at least 100 schemas per database.


texts: Where are SYS Audit Entries in Windows Event Log?

texts:
I have audit_sys_operations set to true, audit_trail set to DB, have restarted the database since setting them, and have logged in as and performed actions as sysdba. However, I cannot find these entries in the Windows Event Log Viewer. From what I read, they should be under Windows Logs->Application. When looking there, I see nothing with an event id of 34 or a Source containing Oracle.

Am I missing something, or is something perhaps not configured properly?


texts: Secondary data file is in 'Recovery Pending' state, but database is online

texts:
I got an issue. I have a database (Say, DBASE) which have three files - Two data files (namely D:\Data1.mdf and D:\Data2.ndf) and a log file (E:\log.ldf) and all are in different file group. The secondary data file i.e., Data2.ndf is in recovery pending state. Remaining two db files are good and my database is online. When I took a look on database properties (GUI), I found there the size of Data2.ndf as 0MB where it's actual size is 10GB. We have given a try in below methods to fix the issue, but no luck.

resetdbstatus
Taking database to offline, then back to online.
Detach and attach operations
No errors from checkdb, unfortunately we don't anything in error log (as they are recycled)

Can someone help me in fixing the issue please?

FYI - Version : SQL 2012 SP1 Enterprise.


texts: Limiting Domain Admins permissions on a SQL Server

texts:
I have a SQL Server 2012 machine and I want to limit the Domain Admins permissions. What can I do to make sure that the Domain Admins have read only permissions for the databases and nothing else for SQL Server?

I am interested in limiting their permissions for the SQL Server install. They can do whatever they want on the Windows Server 2008 OS.

Thank you for your help.


texts: Is PostgreSQL 9.2 autovacuum sufficient for a busy table?

texts:
I have one table on a PostgreSQL 9.2/PostGIS 2.0.1 database:

CREATE TABLE ch02.markers
(
  ff_id SERIAL PRIMARY KEY,
  col1 boolean,
  col2 smallint,
  created_at timestamp,
  lat double precision,
  lon double precision,
  geog geography(POINT,4326)
);

CREATE INDEX idx_markers_geog ON ch02.markers USING gist(geog);

This table is having records, probably thousands but potentially tens of thousands per hour, inserted and deleted 24/7.

I am wondering if the default autovacuum settings will sufficiently manage the table?

Otherwise I presume I write a SP function that is called by cron. However, what should I use? VACUUM ANALYZE? I am wary of using any anything that locks the table (CLUSTER, REINDEX). Do these even work for an index on a geography point column?

I am not used to such active tables and I am completely new to Postgres, so I have absolutely no feel for this. Any help appreciated.


texts: Add Oracle Label Security to an existing Oracle installation

texts:
We are using Amazon EC2 to host some Oracle database instances while we are evaluating and prototyping some software. We are using the AMIs provided by Oracle - specifically, Oracle Database 11g Release 2 (11.2.0.1) Enterprise Edition - 64 Bit (Linux).

I now need to do some work based on Oracle Label Security - OLS. It appears that when Oracle was first installed into an AMI, the Oracle Label Security option was not enabled. So I want to add OLS to an existing installation of Oracle.

I have tried following the installation instructions in the Oracle Label Security Administrator's Guide, using media downloaded from following the download links on the Oracle home page. However the instructions don't match up with what I see when I run the installer - according to the instructions, on the second page of the wizard I should see an Advanced Installation option, but I don't see that - I can only see three radio buttons, "Create and configure a database", "Install database software only" and "Upgrade an existing database". I tried to muddle through the instructions but there were several inconsistencies, as if I was running a different version of the installer to the documentation writer. The AMI, documentation and media are all Oracle Database 11g Release 2 (11.2.0.1) so I don't know why they don't all seem to match.

I tried to proceed with an installation anyway, and did find the checkbox to install Oracle Label Security. However it would not accept my existing ORACLE_HOME as an installation destination, telling me: [INS-32025] The chosen installation conflicts with software already installed in the given Oracle home.

So, in short - is it possible for me to add the OLS option to an existing installation? If so - how? If not - how do I make a new installation and attach the existing database to the new installation?

Edit 2011-05-23

After doing some more poking around, I found a copy of the Oracle installer at $ORACLE_HOME/oui/bin/runInstaller. This installer looks visually different and has different page flows. First thing it wanted to know was the source location, so I gave it the path to stage/products.xml on the installation media. It then gave me a choice of Enterprise, Standard or Custom - I selected Custom. It then asked for the Oracle home - this was pre-selected with the value of $ORACLE_HOME. So far so good.

Then, the first odd thing: it gave me a long list of installable products. Oracle Label Security was in this list, but it was already marked as "Installed". However if I clicked the Installed Products... button for information, Oracle Label Security was not present.

I selected Oracle Label Security anyway and it changed to say "reinstall". But on clicking Next I got this error:


texts: Sending an async notification to another database in PostgreSQL

texts:
Is there any way I can send a NOTIFY to a database other than the one that my connection has specified?

[CLARIFICATION]

Consider we have two databases within one instance of PostgreSQL. If you are connected to one of them and you are LISTENing and you won't be able to hear the notifications made on the other database (at least to my experience).

My scenario is:

I want to connect to one unique centeral database within each instance of PostgreSQL from my application. Then, having an arbitrary number of databases on the same instance, I'll be sending notifications to my application, no matter which database has triggered it and I won't need to establish a connection to each database separately.


texts: Display both null and not null under same id but not both null or not null like XOR logic

texts:
Consider the following Employee table:

In this table I need to select only the ids which have both null and not null marks, and it should not be both not null or both are null in col1.


texts:
Let's say I've a table trainStations with the following:

 startStation  | endStation
 --------------+--------------
 SF            | Palo Alto
 Palo Alto     | SF
 Mountain View | Redwood City
 SF            | Palo Alto
 Palo Alto     | Mountain View
 Mountain View | SF

Now, I'm trying to learn how to do a round trip query.
For example, to query SF --> PA I'd use:

SELECT *
FROM   trainStations
WHERE  startStation = 'SF'
AND    endStation = 'Palo Alto'`

But how do I query for round trip (with no additional stops)?

For example: SF--> Palo Alto --> SF

It should return the two lines:

 startStation  | endStation
 --------------+--------------
 SF            | Palo Alto
 Palo Alto     | SF

Only SF -> Palo Alto followed by Palo Alto -> SF rows are valid (direct route). The last three rows (in the table above) will not be selected because it's not a direct route (stop in Mountain View).

If this was code I'd do something like:

if (startStation[row num] == endStation[row num+1])
    && (endStation[row num] == startStation[row num +1):
then print "found direct route"


texts: Why does a simple loop result in ASYNC_NETWORK_IO waits?

texts:
The following T-SQL takes about 25 seconds on my machine with SSMS v17.9:

DECLARE @outer_loop INT = 0,
@big_string_for_u VARCHAR(8000);

SET NOCOUNT ON;

WHILE @outer_loop < 50000000
BEGIN
    SET @big_string_for_u = 'ZZZZZZZZZZ';
    SET @outer_loop = @outer_loop + 1;
END;

It accumulates 532 ms of ASYNC_NETWORK_IO waits according to both sys.dm_exec_session_wait_stats and sys.dm_os_wait_stats. The total wait time increases as the number of loop iterations increases. Using the wait_completed extended event I can see that the wait happens roughly every 43 ms with a few exceptions:

In addition, I can get the call stacks that occur right before the ASYNC_NETWORK_IO wait:

sqldk.dll!SOS_DispatcherBase::GetTrack+0x7f6c
sqldk.dll!SOS_Scheduler::PromotePendingTask+0x204
sqldk.dll!SOS_Task::PostWait+0x5f
sqldk.dll!SOS_Scheduler::Suspend+0xb15
sqllang.dll!CSECCNGProvider::GetBCryptHandleFromAlgID+0xf6af
sqllang.dll!CSECCNGProvider::GetBCryptHandleFromAlgID+0xf44c
sqllang.dll!SNIPacketRelease+0xd63
sqllang.dll!SNIPacketRelease+0x2097
sqllang.dll!SNIPacketRelease+0x1f99
sqllang.dll!SNIPacketRelease+0x18fe
sqllang.dll!CAutoExecuteAsContext::Restore+0x52d
sqllang.dll!CSQLSource::Execute+0x151b
sqllang.dll!CSQLSource::Execute+0xe13
sqllang.dll!CSQLSource::Execute+0x474
sqllang.dll!SNIPacketRelease+0x165d
sqllang.dll!CValOdsRow::CValOdsRow+0xa92
sqllang.dll!CValOdsRow::CValOdsRow+0x883
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x15d
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x638
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x2ad
sqldk.dll!SystemThread::MakeMiniSOSThread+0xdf8
sqldk.dll!SystemThread::MakeMiniSOSThread+0xf00
sqldk.dll!SystemThread::MakeMiniSOSThread+0x667
sqldk.dll!SystemThread::MakeMiniSOSThread+0xbb9

Finally, I noticed that SSMS uses a surprising amount of CPU during the loop (about half a core on average). I'm unable to figure out what SSMS is doing during that time.

Why does a simple loop cause ASYNC_NETWORK_IOwaits when executed through SSMS? The only output that I appear to get from the client from this query execution is the "Commands completed successfully." message.


texts: Unexpected results in query with rownum condition

texts:
I have found old query in application that behaves unexpectally after Oracle update. I have found out that problematic part of this query is condition:

rownum >= 1

When I apply this condition, resultset contains 0 rows, otherwise it returns 1 row (which is same in older version of Oracle).

If I change condition to rownum = 1 it returns one row. It also returns one row for AND(rownum > 1 OR rownum = 1) and oddly for AND(rownum >= 1 OR rownum >= 1)

How is it possible?

EDIT, added SQL:

SELECT DISTINCT
    *
FROM
  NI_TABLE ni
LEFT JOIN NI_TO_BOPER nibo
ON
  ni.ni_id     = nibo.ni_id
AND ni.company = nibo.company
LEFT JOIN CHOPER co
ON
  nibo.operation_id = co.operation_id
AND nibo.company    = co.company
WHERE
  (
    ni.IDENTITY_NUMBER  = '123456789'
  OR co.IDENTITY_NUMBER = '123456789'
  )
AND NI.company   ='K'
AND rownum >= 1
AND 200 >= rownum
AND ni.NI_TYPE   ='PRIV'
ORDER BY
  ni.identityNo;

Note I haven't created this code, so I changed names for security reasons.


texts: Unable to connect to oracle database. Getting ORA12505 error

texts:
I am newbie to ORCALE

I am trying to connect to oracle server and i am getting this error. This is was working fine before but now i am facing this issue. Need help

An error was encountered performing the requested operation:

Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Vendor code 12505

this is my listener.ora file

SID_LIST_LISTNITBSCAT =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ITBSCAT)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = ITBSCAT)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DB-ORACLE-11G-RHEL6)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

LISTNITBSCAT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = DB-ORACLE-11G-RHEL6)(PORT = 1522))
  )

ADR_BASE_LISTNITBSCAT = /u01/app/oracle

This is my tnsnames.ora file which is my env

(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DB-ORACLE-11G-RHEL6)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ITBSCAT)
    )
  )

DMSORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.2.89)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ARCHDBGG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.5.16)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = archdb)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DB-ORACLE-11G-RHEL6)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Listener status:

Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                11-OCT-2018 18:26:36
Uptime                    0 days 2 hr. 8 min. 44 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/DB-ORACLE-11G-RHEL6/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DB-ORACLE-11G-RHEL6)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@DB-ORACLE-11G-RHEL6 admin]$

TNSPING ORCL

[oracle@DB-ORACLE-11G-RHEL6 admin]$ tnsping orcl

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on
11-OCT-2018 21:32:30

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias Attempting to contact
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =
DB-ORACLE-11G-RHEL6)(PORT = 1521))) (CONNECT_DATA = (SERVER =
DEDICATED) (SERVICE_NAME = orcl))) OK (0 msec)
[oracle@DB-ORACLE-11G-RHEL6 admin]$


texts: Fastest way to determine size of groups of FILESTREAM objects

texts:
We have a sql FILESTREAM enabled table, and are wondering what the fastest way is to determine the size grouped by a common criteria, as well as the sum of all objects.

To date, we have been using queries like select ClientID, sum(datalength(FilestreamColumn)) from dbo.tblFilestream group by ClientID; however, this takes a very long time to complete on a large dataset, and I imagine it is fairly hostile to the buffer cache.  I can perform a directory listing, parse the entries in Excel, and get the results back faster than SQL does.  However, that's because the dir command isn't reading the contents into memory.

We could add a new column to store this info and maintain it each time we perform an operation, but we'd prefer not to.  That being said, we may have no other choice.

Regarding the 'all objects' part of the question - this question indicates that sys.database_files can be used, but it is always 0 for us, even in SQL 2008 R2 SP2.


texts: What does below parallel processing execution plan mean?

texts:
I have a table tbl_a with 200 k records. I wrote a query to bring sum of id (int) column. Now execution plans shows 2 workers launched and below that parallel sequential scan on tbl_a is shown 3 times; with processing 66667 records each time(200k/3=66667). Now my question is since there were two workers so should there not be only two times table seq scan and each time with record 100k (200k/3). I am attaching the image which lists the table detail, query and query execution plan for better understanding.

Can someone tell me why with 2 workers there are 3 loops , each processing 6667 records out of total 200k records?


texts: Do I need a domain controller for multiple PCs accessing the same SQL server db? How do I configure it?

texts:
(more details/replicate from: https://superuser.com/questions/1612260)
Dear all,
I'm a total newbie in connecting 2 PCs and making them use the same SQL server DB from one computer ... so please support me.
At home, I have 2 PCs from my company (working from home due to Covid19; I don't have better windows images and I would prefer not changing them, or at least as possible):

PC
Windows
SQL server
SSMS
IP

1
7, version 6.1, build 7601 sp1
2014 (installation in progress)
not yet
192.168.0.10

2
10, version1803, build17134.345
2017
18.1
192.168.0.105

The final goal: to have PC1 act as a db server and PC2 as a client (that can access the server's dbs). On both PCs I will install the same application which should be able (from any PC) to update the data from PC1/the db server.
I'd been told I would need a domain controller (DC) for this scenario, but I really don't have any clue what this means or how I should configure it:

do I need a DC?
is this DC something that comes with the windows images?
is this DC something that comes with SQL Server?
is this an application which I'll need to install separately? (I initially thought that if I'm installing sql server on PC1, I will have to insert the windows credentials from the PC2 and this will suffice)
could you please give me step-by-step on how to make this work?

Thank you!
R


texts: connect to sqlplus without writing as sysdba

texts:
I want to connect sqlplus sys/test without writing as sysdba.
I'm wondering if there is a way to avoid that.


texts: How to programmatically remove a push subscription?

texts:
I have 4 databases, a publisher and 3 subscribers (transactional replication).

In case of an error in the publisher I want to:

delete all subscriptions
create a new publisher
make the two remaining databases subscribe to the new publisher.

(so that the program can continue normally and the 3 databases continue up to date)

I have the code to create a new publication and to create a new subscriber, but I don't know how to remove the old subscription.

I've read this Here:

  At the Publisher on the publication database, execute
  sp_dropsubscription (Transact-SQL). Specify @publication and
  @subscriber. Specify a value of all for @article. (Optional) If the
  Distributor cannot be accessed, specify a value of 1 for
  @ignore_distributor to delete the subscription without removing
  related objects at the Distributor.

  At the Subscriber on the subscription database, execute sp_subscription_cleanup (Transact-SQL) to remove replication metadata
  in the subscription database.

but im my case, publisher and distributor are on the same machine. (and so I can't execute sp_dropsubsription right away, because the publisher machine is down)

edit:
I've done sp_subscription_cleanup @publisher='PCONE', @publisher_db='TEST' (in one of the subscribers) but the subscription still is in the local subscriptions folder

and so this does not prevent the old publisher to send data when he comes back to life.

How can I prevent that? (how can I programmatically do the equivalent to right click a subscription and select delete)


texts:
After trying to use the Propel PHP ORM on my database, I noticed that there is an issue with foreign keys. This is a table with foreign keys:

CREATE TABLE `appusers` (
`AppID` INT(11) NOT NULL,
`UserID` INT(11) NOT NULL,
`Enabled` TINYINT(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`AppID`, `UserID`),
INDEX `FK_AppUserID` (`UserID`),
CONSTRAINT `FK_AppUserID` FOREIGN KEY (`UserID`) REFERENCES `users` (`ID`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `FK_UserAppID` FOREIGN KEY (`AppID`) REFERENCES `apps` (`ID`) ON UPDATE CASCADE ON DELETE CASCADE
);

Notice how the foreign keys reference the tables users/apps with column ID.

When I show the create code for the table again, this is what happens:

CONSTRAINT `FK_AppUserID` FOREIGN KEY (`UserID`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `FK_UserAppID` FOREIGN KEY (`AppID`) REFERENCES `apps` (`id`) ON UPDATE CASCADE ON DELETE CASCADE

The foreign keys now reference the tables users/apps with column id. Every time a foreign key is created, the column on the referenced side is turned into lowercase. This is not correct, mysql does not have an issue with this but the PHP ORM certainly does.

Engine for all my tables is InnoDB and they all use the collation latin1_general_cs.

Does anyone know why it's doing this and what I can do to fix it?


texts:
Actually I got stuck while searching a materialized view approach in SQL Server.  My problem was that I have two databases and I have created some views whose functionality is to read data from one DB to another.

For instance, a view in DB "A" is picking up the data from DB "B".  It worked perfectly, but suddenly today I got Null pointer exception.  When I traced the problem, I found that My DB "B" has crashed.  I searched and found that in Oracle, there is support of this problem by Materialized views whose functionality is to restore the old view state in case of crashing of one DB.  But I didn't find anything like this in SQL Server.

I am really stuck in this issue.  Any help would be greatly appreciated.


texts: Adding parameters to ORACLE sql case statement

texts:
I have the following sql query

SELECT DISTINCT SAMPLE.SAMPLE_NUMBER, LOT.PRODUCT, SAMPLE.LOGIN_DATE, SAMPLE.STATUS as SAMP_STATUS,
CASE
    WHEN SAMPLE.TEXT_ID LIKE '%TOP%'
    THEN 'TOP'
    WHEN SAMPLE.TEXT_ID LIKE '%MID%'
    THEN 'MIDDLE'
    WHEN SAMPLE.TEXT_ID LIKE '%BOTT%'
    THEN 'BOTTOM'
    WHEN SAMPLE.TEXT_ID LIKE '%RETAIN%'
    THEN 'RETAIN' ELSE 'ROUTINE'
END as LOCATION,
CASE
    WHEN WORK_CENTER = 'LABWARE'
    THEN C_PRODN_RESOURCE ELSE WORK_CENTER
END as PROD_RESOURCE, RESULT.ANALYSIS, RESULT.NAME, RESULT.FORMATTED_ENTRY, RESULT.IN_SPEC
CASE
    WHEN SAMPLE.STATUS <> 'X'
    THEN SAMPLE.STATUS
    WHEN SAMPLE.STATUS <> 'A'
    THEN SAMPLE_STATUS
END
FROM LOT JOIN SAMPLE ON (LOT.LOT_NUMBER = SAMPLE.LOT)
JOIN RESULT ON (SAMPLE.SAMPLE_NUMBER = RESULT.SAMPLE_NUMBER)
LEFT JOIN PRODUCT_SPEC ON ((LOT.PRODUCT = PRODUCT_SPEC.PRODUCT)
    AND (RESULT.NAME = PRODUCT_SPEC.COMPONENT) AND (SAMPLE.PRODUCT_VERSION = PRODUCT_SPEC.VERSION)
    AND (LOT.SAMPLING_POINT = PRODUCT_SPEC.SAMPLING_POINT) AND (LOT.PRODUCT_GRADE = PRODUCT_SPEC.GRADE)
    AND (SAMPLE.STAGE = PRODUCT_SPEC.STAGE) AND (SAMPLE.SPEC_TYPE = PRODUCT_SPEC.SPEC_TYPE)
    AND (RESULT.ANALYSIS = PRODUCT_SPEC.ANALYSIS)) WHERE LOT.LOT_NUMBER = '40000165304' AND RESULT.REPORTABLE = 'T'
    AND SAMPLE.SPEC_TYPE <> 'RETAIN' AND PRODUCT_SPEC.T_SHOW_ON_CERTIFICATE = 'T' Order by ANALYSIS, LOCATION

I NEED TO ADD CONDITIONS FOR THE sample.status i.e.,
Sample.status <> ‘x’ or Sample.status <> ‘R’

I’m not sure where to add these conditions to?

This still isn't working

    SELECT DISTINCT SAMPLE.SAMPLE_NUMBER, LOT.PRODUCT, SAMPLE.LOGIN_DATE,   SAMPLE.STATUS as SAMP_STATUS,
CASE
    WHEN SAMPLE.TEXT_ID LIKE '%TOP%'
    THEN 'TOP'
    WHEN SAMPLE.TEXT_ID LIKE '%MID%'
    THEN 'MIDDLE'
    WHEN SAMPLE.TEXT_ID LIKE '%BOTT%'
    THEN 'BOTTOM'
    WHEN SAMPLE.TEXT_ID LIKE '%RETAIN%'
    THEN 'RETAIN' ELSE 'ROUTINE'
END as LOCATION,
CASE
    WHEN WORK_CENTER = 'LABWARE'
    THEN C_PRODN_RESOURCE ELSE WORK_CENTER
END as PROD_RESOURCE,
RESULT.ANALYSIS, RESULT.NAME, RESULT.FORMATTED_ENTRY,   RESULT.IN_SPEC
CASE
    WHEN SAMPLE.STATUS NOT IN ('X', 'A')  THEN SAMPLE_STATUS
END
FROM LOT JOIN SAMPLE ON (LOT.LOT_NUMBER = SAMPLE.LOT)
JOIN RESULT ON (SAMPLE.SAMPLE_NUMBER = RESULT.SAMPLE_NUMBER)
LEFT JOIN PRODUCT_SPEC ON ((LOT.PRODUCT = PRODUCT_SPEC.PRODUCT)
    AND (RESULT.NAME = PRODUCT_SPEC.COMPONENT) AND (SAMPLE.PRODUCT_VERSION =   PRODUCT_SPEC.VERSION)
    AND (LOT.SAMPLING_POINT = PRODUCT_SPEC.SAMPLING_POINT) AND   (LOT.PRODUCT_GRADE = PRODUCT_SPEC.GRADE)
    AND (SAMPLE.STAGE = PRODUCT_SPEC.STAGE) AND (SAMPLE.SPEC_TYPE =   PRODUCT_SPEC.SPEC_TYPE)
    AND (RESULT.ANALYSIS = PRODUCT_SPEC.ANALYSIS))
WHERE LOT.LOT_NUMBER = '40000165304'
  AND RESULT.REPORTABLE = 'T'
  AND SAMPLE.SPEC_TYPE <> 'RETAIN'
  AND PRODUCT_SPEC.T_SHOW_ON_CERTIFICATE =   'T'
Order by ANALYSIS, LOCATION


texts: SQL Server Transaction log Percent Used in Perfmon

texts:
I am doing some research into monitoring transaction log use using windows perfmon.

In the AdventureWorks database I have created the following statement

SET IDENTITY_INSERT Person.BusinessEntity ON

WHILE 1 = 1
BEGIN

    DECLARE @id INT
    SELECT @id = MAX(BusinessEntityID) FROM Person.BusinessEntity
    SET @id += 1

    INSERT INTO Person.BusinessEntity(BusinessEntityID, rowguid, ModifiedDate) VALUES (@id,NEWID(), GETDATE())

    INSERT INTO [Person].[Person]
               ([BusinessEntityID]
               ,[PersonType]
               ,[NameStyle]
               ,[Title]
               ,[FirstName]
               ,[MiddleName]
               ,[LastName]
               ,[Suffix]
               ,[EmailPromotion]
               ,[AdditionalContactInfo]
               ,[Demographics]
               ,[rowguid]
               ,[ModifiedDate])
         VALUES
               (@id,
               'EM',
               0,
               NULL,
               'Ken',
               'J',
               'Sánchez',
               NULL,
               0,
               NULL,
               NULL,
               NEWID(),
               '2

009-01-07 00:00:00.000')
END

The query will indefinitely enter rows into Person.BusinessEntity and Person.Person until I click stop

This should cause the transaction log to grow quickly which I can monitor in perfmon

I have added the perfmon counter for percent log used and run the statement and I get the graph as follows

I am wondering why the graph goes up and down again?

I assume it goes up when the log is written to and back down as the log resizes again?

If this is true then disabling autogrowth on the transaction log in the Adventure Works database and running the statement whilst monitoring again should see the graph go up and up and then reach the top and the query terminate because it is unable to resize the log?

However, I get similar behaviour again.

Can someone explain to me why am not seeing the transaction log start to fill after turning off autogrowth? Is there something else happening here?

The database is in full recovery mode


texts: Cannot restore MySQL database from .frm and .ibd files

texts:
I have a bigger database with around 1000 tables. Unfortunately after a OS X update everything vanished, but I was able to recover all the file that now consists of only .frm and libd files. Following answers from here restore table from .frm and .ibd file? I did the following, test only on one table

1.Installed MAMP

2.Created a new database that has the same name as the old (sunflower)

3.Using mysqlfrm I was able to get a query that created me the table structure( First at once I am trying to recover only one table):

    CREATE TABLE `sunflower`.`USDT@002dNXT_hour` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `datetime` datetime NOT NULL,
  `open` text,
  `close` text,
  `low` text,
  `high` text,
  `volume` text,
  `btc_volume` text,
PRIMARY KEY `PRIMARY` (`datetime`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB;

Here I encountered with one problem. Running this query it created me a file called : usdt@0040002dnxt_hour.ibd why my old file is usdt@002dnxt_hour.ibd. (Renaming 1 file will be fine, but renaming 1000 is quite complicated)

Then I run :  ALTER TABLE USDT@002dNXT_hour DISCARD TABLESPACE;
Copied the old usdt@002dnxt_hour.ibd file to my database directory.
Now I tried to run  ALTER TABLE USDT@002dNXT_hour IMPORT TABLESPACE;
which complained that such a table does not exits, so I renamed my file to usdt@0040002dnxt_hour.ibd.
Running now the same script trows me this error : #1808 - Schema mismatch (Table has ROW_TYPE_COMPACT row format, .ibd file has ROW_TYPE_DYNAMIC row format.) So I changed the row type be running : ALTER TABLE USDT@002dNXT_hour ROW_FORMAT=DYNAMIC;.
Now running the import query again throws me this error : Table sunflower.usdt@002dnxt_hour doesn't exist, however the table exits.

Is my approach is good ? If yes, where I make the mistake ?


texts: ibdata1 grows big again after using innodb_file_per_table and having shrunk it

texts:
After having set innodb_file_per_table=1, dumping and dropping all db's, deleting ibdata1, and restarting mysql ... My ibdata1 file is small at last...

However, directly after reading in my dump, and seeing that all tables now all have their own big ibd files (approx 15GB), but still the ibdata1 is over 2.6 GB again...

The way I dump my data is with:

mysqldump --tab="D:\Mysql Databases\dumps\dump\%%a" --opt --single-transaction --no-autocommit -u root -p

This dumps to .sql (DDL / table structures) and .txt (actual data) files. These files I can use to recover with with:

create database myDb;
use myDb;
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
SOURCE *.sql; -- for each table
COMMIT;
SET autocommit=0;
LOAD DATA INFILE '*.txt'; -- for each table
COMMIT;
SET autocommit=1;
SET foreign_key_checks=1;
SET unique_checks=1;

Why is the ibdata1 file growing again after enabling innodb_file_per_table and having succesfully shrunk ibdata1? And is there a way now to reclaim again this data, seen as all ibd data is already present per table?

Can I for example copy the small ibdata1 after recreating the database with the *.sql files, and placing back that ibdata1 file after reading in the data from the *.txt files?

Here are the non-default items in my.ini:

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=151
tmp_table_size=256M
thread_cache_size=20
myisam_max_sort_file_size=1G
myisam_sort_buffer_size=32M
key_buffer_size=64M
innodb_log_buffer_size=8M
innodb_buffer_pool_size=6G
innodb_log_file_size=1G
innodb_thread_concurrency=16
innodb_open_files=1000
join_buffer_size=2M
max_allowed_packet=16M
group_concat_max_len = 10M
bulk_insert_buffer_size=512M
collation-server = utf8_unicode_ci


texts: I need to log activity of the sa account

texts:
I need to log the activity of the 'sa' database account via Microsoft SQL Server 2012 so I can review the activity periodically and look for strange behaviour for audit purposes.

Unfortunately I'm completely new to SQL Server and searching Google and Stack Exchange turned up blank. Maybe I'm just looking for the wrong thing. Can anyone assist?

If this sort of logging is enabled, will the log have a tendency to spiral out of control?


texts: is it possible to populate a primary key of a table based on a column of another table?

texts:
I have a table that contains a text-type column(for example graph path : 12 5 7 19 30 ..) and I want to split this text by space and put all of the output strings as a primary key in the other table.
Note that the strings found can be repeated so each time I will be adding a new primary key, I will check if it is already there or not.


texts:
I need:

a table containing keyword phrases;
a table containing URLs;
a table (or maybe a view) with keyword-URL pairs (referring to IDs of the two previous tables);
tables holding keywords/URLs for particular users.

The trouble is that both URLs and keywords may have several different forms. For example, both keywords and domains in URLs may be lowercase of uppercase, or http://example.com is the same as http://example.com/ (with trailing slash).

The question is how to manage user input when several possible variants are possible.

For now I can't formulate this question more exactly, as I have not formulated the exact specification of what I need. You are free to interpret my question in any way you may do it.

The sub-questions of this question:

Should I store both uppercase/lowercase variants or just the exact user input, generating my upper case version on the fly? (I need these "standardized" lowercase strings when doing actual URL queries from our server. It looks like for me that it's better to store them in the DB rather than to generate on the fly, for performance reason).
Should I use stored procedures to overcome such a deceiving DB layout, with that mess of lowercase and uppercase strings?

It is OK to use transactions. The DB is in MySQL.


texts: MySQL: INSERT SELECT to two databases (STORED FUNCTION)

texts:
I have to copy values of two twowith slight differences. One of the databases has to get the new corresponding id of the other. Hard to explain, so I'll do it with an example:

These tables are created:

-- -----------------------------------------------------
-- Schema testing
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `testing` DEFAULT CHARACTER SET utf8 ;
USE `testing` ;

-- -----------------------------------------------------
-- Table `testing`.`table1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `testing`.`table1` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `value` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `testing`.`table2`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `testing`.`table2` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `id_table1` INT NOT NULL,
  `value` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `id_table1_idx` (`id_table1` ASC),
  CONSTRAINT `id_table1`
    FOREIGN KEY (`id_table1`)
    REFERENCES `testing`.`table1` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

The values of table1 are:

--------------------
| id  |    value   |
|------------------|
|  1  | 'Abra'     |
|  2  | 'Simsala'  |
--------------------

The values of table2 are:

-------------------------------
| id  | id_table1 |  value    |
|-----------------------------|
|  1  |     1     | 'Kadabra' |
|  2  |     2     | 'Bim'     |
-------------------------------

I want to copy all of the values including new foreign keys using a stored function. So that the results will be:

table1:

--------------------
| id  |    value   |
|------------------|
|  1  | 'Abra'     |
|  2  | 'Simsala'  |
|  3  | 'Abra'     |
|  4  | 'Simsala'  |
--------------------

table2:

-------------------------------
| id  | id_table1 |  value    |
|-----------------------------|
|  1  |     1     | 'Kadabra' |
|  2  |     2     | 'Bim'     |
|  3  |     3     | 'Kadabra' |
|  4  |     4     | 'Bim'     |
-------------------------------

Of course my real problem is much more complicated, but much harder to describe to. So I hope someone knows an answer how to do it in a stored function.


texts: How to improve INSERT speed in large table that has unique constraint

texts:
I have a simple MyISAM table:

explain entities;
+------------+-------------+------+-----+-------------------+----------------+
| Field      | Type        | Null | Key | Default           | Extra          |
+------------+-------------+------+-----+-------------------+----------------+
| id         | int(11)     | NO   | PRI | NULL              | auto_increment |
| name       | varchar(50) | YES  | UNI | NULL              |                |
| created_at | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
+------------+-------------+------+-----+-------------------+----------------+

that is expected to have 10s of millions of rows (or even more) eventually. I noticed that as the number of rows in the table increases, insert statements start to take longer. This is expected, as the documentation states that "the size of the table slows down the insertion of indexes by log N, assuming B-tree indexes". I read in another question that LOAD INDEX INTO CACHE can be used to improve insert speed, but I found this to have a negligible effect. Are there any other pointers on how to improve insertion speed, other than the ones listed here? For example, would partitioning the table help with insertion speed? What guidelines can I use for determining if my insertion speeds are already at a near optimum?

System info:
MySQL 5.1.67-0ubuntu0.10.04.1
Running on Linode VM with 512 MB RAM and quad-core processor


texts: Is it possible to host pbix and rdl files on the same report server instance?

texts:
Just upgraded an existing instance of SQL Server 2014 to 2016. That instance has an instance of Reporting Services installed. My end goal is to connect Power BI Desktop (March 2018) to my existing report server. I've downloaded and installed Power BI Report Server. Worked through the reporting services configuration manager.

In Power BI Desktop, I try to pass my existing url to the Power BI Report Server Selection pane. The url that i'm passing is pulled straight from the config manager and I can connect to it using IE. Current error reads: "We couldn't find a Power BI Report Server at this address"

I've restarted the the service on my windows box, still no luck. Any ideas?


texts: New query slower even if it has less logical reads than the old one

texts:
I have a query in a stored procedure that must list all dates in a range and JOIN on it date from a table, if it exists for that day.
The procedure was created before I was employed as the DBA, so I thought I might optimize it a bit. However, it seems I've hit a brick wall, and I cannot understand why.
The current implementation of the procedure SELECTs data from two normal tables (let's call them TableA and TableB) as a derived query (in the FROM clause), and then has this as a RIGHT JOIN on it:
RIGHT JOIN (
     SELECT DATEADD(DAY,number,@DateFrom) AS DATE
     FROM (
         SELECT DISTINCT number
         FROM master.dbo.spt_values
         WHERE name IS NULL
     ) n
     WHERE DATEADD(DAY,number,@DateFrom) <= @DateTo
) AS y ON derived.Date = y.Date -- "derived" is an alias of the derived query

Since this same code is used in multiple procedures, I thought I would create a table with dates from 2005-01-01 to 2039-01-01:
CREATE TABLE Dates(
     [Date] DATE NOT NULL PRIMARY KEY
)

The table was filled from a warehouse that had a dimension with dates.
So, instead of that RIGHT JOIN, I've written:
FROM Dates d
LEFT JOIN derived ON derived.Date = d.[Date] -- the "derived" table was moved to CTE, since that's easier to read
WHERE d.[Date] BETWEEN @DateFrom AND @DateTo

Satisfied with the code, I've turned on STATISTICS IO and executed both versions of procedure code and was completely shocked with the result.
The statistics of the old query:

Table 'TableA'. Scan count 0, logical reads 456516, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableB'. Scan count 367, logical reads 3949, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'spt_values'. Scan count 1, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The statistic of the new query:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableB'. Scan count 1, logical reads 1767, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableA'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dates'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

But, the second query is slower.
When looking at the execution plans, it says that the first query takes only 2% of cost, while the second one takes 98%. Both have the same Index Seek on TableB (the larger one), with the difference:

in the first query, that Index Seek executes 367 times, and costs 5.4% of the full query,
in the second query, that Index Seek executes only 1 time, but costs 47.1% of the full query.

In both cases, the number of rows returned from the Index Seek is 228258.
Here are both execution plans, as well:

And in the XML format:
XML plan of both queries
My questions are:

Why is the second query slower?
Can I make the second query faster?


texts: Only describing postgres tables with "\d" (not sequences, indexes, views, ...)

texts:
I want to describe every table in a postgres database.

Currently I have this:

psql -d db -c "\d *" > db_description.txt

But this also includes all sequences, views, etc.

Is there a command where the * pattern only matches tables?
Or is there another way to do this?


texts: Is it possible to reliably number rows in some order conforming to SQL standard?

texts:
For example, if I have a table with single column numbers and I want to 1) sort them in asc/desc order 2) (re) number them from 1 to n, using that order (so I can say that this number is ith biggest/smallest).

If the DB doesn't have row_number function (MySQL, for example), how would one do that?

If I understood correctly, the order in which SELECT return the rows in unspecified and could be inconsistent.

And something like this (proposed in many answers to the similar question)

SET @num = 0;
SELECT number, @num := @num + 1 as num FROM ... ORDER BY number

Is wrong, since it seems that there is no guarantee regarding the time when ORDER BY would be evaluated. I.e. it can sort the numbers first, and then number them using @num, or it could do the opposite and sort after all the rows were numbered. Am I correct?

And using inner selects won't help, since it's not guaranteed that the order will be preserved.


texts: MySQL specify default value when data has incorrect values

texts: I have a csv file where some integer fields are blank (e.g. , ,) or have an NA value (e.g. ,NA  ,).  When I load the data (LOAD DATA LOCAL INFILE...)  into a table MySQL correctly warns that the fields have incorrect integer values, however it inserts a 0, not the DEFAULT value specified in the CREATE TABLE DDL, into the columns.  Is there any way I can specify the value to use in case of incorrect values in a data file?


texts: What are the minimal MySQL grants for the debian-sys-maint user?

texts:
What are the minimal privileges that debian-sys-maint@localhost needs for the mysql service to work correctly?

This answer on ServerFault suggests granting RELOAD only, but this is incorrect, as it prevents the mysql service from stopping or starting properly.


texts: permission denied on mongod.lock, despite it being owned by mongodb

texts:
this is my mongod.lock:

-rw-r--r-- 1 mongodb mongodb    0 Dec  3 17:09 mongod.lock

hence this solution doesn't apply: https://dba.stackexchange.com/a/183018

this is how I start mongodb:

sudo systemctl start mongodb

and this is the output in mongod.log:

2019-12-03T17:12:01.676+0100 I  STORAGE  [initandlisten] exception in initAndListen: Location28596: Unable to determine status of lock file in the data directory /run/media/minze/ssd/mongodb: boost::filesystem::status: Permission denied: "/run/media/minze/ssd/mongodb/mongod.lock", terminating

thanks in advance. I really don't see how mongodb would not be permitted to access a file owned by mongodb


texts: PK as ROWGUIDCOL or use a separate rowguid column?

texts:
There's a long-winded debate going on here so I'd like to hear other opinions.

I have many tables with uniqueidentifier clustered PK. Whether this is a good idea is out of scope here (and it's not going to change anytime soon).

Now, the database has to be merge published and the DEVs are advocating the use of a separate rowguid column instead of marking the existing PK as the ROWGUIDCOL.

Basically, they say that the application should never bring into its domain something that is used by replication only (it's only "DBA stuff" for them).

From a performance standpoint, I see no reason why I should add a new column to do something I could do with an existing one. Moreover, since it's only "DBA stuff", why not let the DBA choose?

I kind of understand the DEVs' point, but I still disagree.

Thoughts?

EDIT: I just want to add that I'm in the minority in this debate and the DEVs questioning my position are people I respect and trust. This is the reason why I resorted to  asking for opinions.
I might also be missing something and could have misunderstood their point.


texts:
I would like to know how to left join columns for each group of data that is return in a set of data. The template needs to fill in the blanks for each group where there are missing records. Groups 1 and 2 are part of a single query and need to be part of the same result set.

TEMPLATE

Name          Num
------------------------------------
Apples        10
Bananas       20
Oranges       15
Pineapple     5
Grapes        30
Chips         50
Chocolate     6

GROUP 1

Name          Num         Group
------------------------------------
Grapes        3    --     1
Chips         17   --     1

GROUP 2

Name          Num         Group
------------------------------------
Bananas       30   --     2
Oranges       10   --     2

NEW GROUP 1

Name          Num         Group
------------------------------------
Apples        10          1     <-- from template
Bananas       20          1     <-- from template
Oranges       15          1     <-- from template
Pineapple     5           1     <-- from template
Grapes        3           1     <-- same
Chips         17          1     <-- same
Chocolate     6           1     <-- from template

NEW GROUP 2

Name          Num         Group
------------------------------------
Apples        10          2     <-- from template
Bananas       30          2     <-- same
Oranges       10          2     <-- same
Pineapple     5           2     <-- from template
Grapes        30          2     <-- from template
Chips         50          2     <-- from template
Chocolate     6           2     <-- from template

Groups 1 and 2 are stored in different tables. The number of groups is not limited to two. The algorithm is, basically, "return template but overwrite template's num with group's num where it is given." The result needs to be a single result set.


texts: replication database vs mirroring database

texts:
Can anyone explain the differences from a replication db vs a mirroring db server?

I have huge reports to run. I want to use a secondary database server to run my report so I can off load resources from the primary server.

Should I setup a replication server or a mirrored server and why?


texts:
I use SQL Server 2012 and SSAS Tabular Mode (My Instance Name Is MSSQLSERVER2012T)

Then I download AdventureWorks Tabular Model SQL Server 2012 From This link

I open it with Visual Studio 2010 and configure my Instance Name like this picture :

When I deploy my project I have This picture :

But after one minute I get this error :

And when I click on Details Of Error I get this message :

OLE DB or ODBC error: Login timeout expired; HYT00;
A network-related or instance-specific error has occurred while
establishing a connection to SQL Server. Server is not found or not accessible.
Check if instance name is correct and if SQL Server is configured to allow
remote connections. For more information see SQL Server Books Online.; 08001;
Named Pipes Provider: Could not open a connection to SQL Server [2]. ; 08001.

A connection could not be made to the data source with
the DataSourceID of 'a16b8d9d-3811-4be1-91e7-a9d0a2df4d38',
Name of 'SqlServer localhost'.

And

But it not work for me???

In my SSAS one Database was created like this picture :


texts: xenforo vulnerability, limit max open connections by ip address

texts:
Trying to get some emergency help because my website is being attacked. It isn't as much as an actual attack, as it is a small bot to exploit xenforo vulnerability.

https://xenforo.com/community/threads/mysql-attack.89082

That's a link to the original problem. I thought this was the best place to ask so I will ask here as well.
Here is proof that this is an actual attack.

This is the error that the website gives me

Zend_Db_Adapter_Mysqli_Exception: Too many connections - library/Zend/Db/Adapter/Mysqli.php:333
Generated By: Unknown Account, Today at 7:26 AM
Stack Trace
#0 /home/squadcra/public_html/library/Zend/Db/Adapter/Abstract.php(315): Zend_Db_Adapter_Mysqli->_connect()
#1 /home/squadcra/public_html/library/XenForo/Application.php(719): Zend_Db_Adapter_Abstract->getConnection()
#2 [internal function]: XenForo_Application->loadDb(Object(Zend_Config))
#3 /home/squadcra/public_html/library/XenForo/Application.php(960): call_user_func_array(Array, Array)
#4 /home/squadcra/public_html/library/XenForo/Application.php(991): XenForo_Application->lazyLoad('db', NULL)
#5 /home/squadcra/public_html/library/XenForo/Application.php(1561): XenForo_Application::get('db')
#6 /home/squadcra/public_html/library/XenForo/Model.php(161): XenForo_Application::getDb()
#7 /home/squadcra/public_html/library/XenForo/Model/DataRegistry.php(138): XenForo_Model->_getDb()
#8 /home/squadcra/public_html/library/XenForo/Model/DataRegistry.php(97): XenForo_Model_DataRegistry->_getMultiFromDb(Array)
#9 /home/squadcra/public_html/library/XenForo/Dependencies/Abstract.php(147): XenForo_Model_DataRegistry->getMulti(Array)
#10 /home/squadcra/public_html/library/XenForo/FrontController.php(127): XenForo_Dependencies_Abstract->preLoadData()
#11 /home/squadcra/public_html/index.php(13): XenForo_FrontController->run()
#12 {main}
Request State
array(3) {
["url"] => string(22) "http://squadcraft.net/"
["_GET"] => array(0) {
}
["_POST"] => array(0) {
}
}

I think this is an exploit where he has a bot that spam opens mysql connections, or just opens and holds open mysql connections. I don't know I'm not an expert. Anyway I need to limit the maximum number of open connections per ip address.


texts:
I have a id serial PRIMARY KEY column in a PostgreSQL table. Many ids are missing because I have deleted the corresponding row.

Now I want to "compact" the table by restarting the sequence and reassigning the ids in such a way that the original id order is preserved. Is it possible?

Example:

Now:

 id | data
----+-------
  1 | hello
  2 | world
  4 | foo
  5 | bar

After:

 id | data
----+-------
  1 | hello
  2 | world
  3 | foo
  4 | bar

I tried what was suggested in a StackOverflow answer, but it didn't work:

# alter sequence t_id_seq restart;
ALTER SEQUENCE
# update t set id=default;
ERROR:  duplicate key value violates unique constraint t_pkey
DETAIL:  Key (id)=(1) already exists.


texts: How to make MySQL log to 2 separate remote servers?

texts:
Documentation on this topic is scarce at best. I have two syslog servers and I need to send MySQL logs to them. Currently, I believe the logs are stored at either .mysql_history and/or mysqld.log Does mysql have the capability within itself to log remotely, or do I need to write a script to do so?

If mysql has this capability, it seems that you could only assign it to one location. I need two, so I would need to know if this is able to be done or just go with the script again.

I tried testing one server and tried setting environment variable MYSQL_HISTFILE location to one remote server; however, this is not working.

Any help or resources?


texts:
As per the code mentioned below, I am trying to understand how 'Deadlock' can be avoided with the help of a table and a single row. Could someone help me understand how this technic can help avoid deadlock situations.?

Disclaimer:
I found this from an answer to a different but relevant question in this forum but I could not add a comment on that thread.. Hence thought to raise it as a question by itself..

The idea is to - Instead of invoking sp_getapplock, we can use the following table to avoid deadlocks

CREATE TABLE dbo.DeadlockTestMutex(
ID INT NOT NULL,
CONSTRAINT PK_DeadlockTestMutex PRIMARY KEY(ID),
Toggle INT NOT NULL);
GO

INSERT INTO dbo.DeadlockTestMutex(ID, Toggle)
VALUES(1,0);

Once the above table is ready and populated with the data as shown above, use this statement inside your stored procedures

UPDATE dbo.DeadlockTestMutex SET Toggle = 1 - Toggle WHERE ID = 1;

Such that, the stored procedures are as follows:

ALTER PROCEDURE dbo.UpdateCounter1 @Key1 INT
AS
SET NOCOUNT ON ;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ;

----------
UPDATE dbo.DeadlockTestMutex SET Toggle = 1 - Toggle WHERE ID = 1;
----------

UPDATE dbo.DeadlockTest SET Counter1=Counter1+1 WHERE Key1=@Key1;
SET @Key1=@Key1-10000;
UPDATE dbo.DeadlockTest SET Counter1=Counter1+1 WHERE Key1=@Key1;
COMMIT;
GO

ALTER PROCEDURE dbo.UpdateCounter2 @Key2 INT
AS
SET NOCOUNT ON ;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ;

----------
UPDATE dbo.DeadlockTestMutex SET Toggle = 1 - Toggle WHERE ID = 1;
----------

SET @Key2=@Key2-10000;
UPDATE dbo.DeadlockTest SET Counter2=Counter2+1 WHERE Key2=@Key2;
SET @Key2=@Key2+10000;
UPDATE dbo.DeadlockTest SET Counter2=Counter2+1 WHERE Key2=@Key2;
COMMIT;
GO

  So, how does adding this one liner can avoid deadlocks?

Here is the code for the table used by the stored procedures..

CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY);
GO

INSERT INTO dbo.Numbers
    ( n )
        VALUES  ( 1 );
GO
DECLARE @i INT;
    SET @i=0;
WHILE @i<21
    BEGIN
    INSERT INTO dbo.Numbers
        ( n )
        SELECT n + POWER(2, @i)
        FROM dbo.Numbers;
    SET @i = @i + 1;
    END;
GO

SELECT n AS ID, n AS Key1, n AS Key2, 0 AS Counter1, 0 AS Counter2
INTO dbo.DeadlockTest FROM dbo.Numbers
GO

ALTER TABLE dbo.DeadlockTest ADD CONSTRAINT PK_DeadlockTest PRIMARY KEY(ID);
GO

CREATE INDEX DeadlockTestKey1 ON dbo.DeadlockTest(Key1);
GO

CREATE INDEX DeadlockTestKey2 ON dbo.DeadlockTest(Key2);
GO


texts:
On my desktop and on most other instances of Sql Server 2008 R2 and 2012, the default behavior when you right click on a database in the solution explorer of SSMS and choose "New Query" is a new query window opens and changes the database context to the database you clicked on. If you click new query button from the query window, it will open a new query from the same database.
However, on some other machines, including my boss', the new query opens but the context is the default database (master by default). Clicking new query will likewise open a query in the default database. What is the option to make his instance act like mine?

If I connect to his database from my ssms, it still behaves like it does on his machine (incorrectly).


texts: cause of / how to fix a table with excessive reserved pages

texts:
I have several tables with clustered indexes in azure PaaS sql server which have blown out to be much bigger than their data, with reserved page count several times bigger than the actual amount of data in the table.
These tables are insert only (no deletes or updates), although from time to time rows are inserted, which then fail insert due to PK violation.

is this an issue? - azure is reporting that all the 'reserved' space in this database is actually in use (somewhat expected from what i understand about what reserved means)
is there a known cause for this blowout? - a 10gb table consuming 70gb of space is a bit excessive.
is there a way to clean this up?
is there a strategy i can employ to prevent this happening again?

an example

partition_id    object_id   index_id    partition_number    in_row_data_page_count  in_row_used_page_count  in_row_reserved_page_count  lob_used_page_count lob_reserved_page_count row_overflow_used_page_count    row_overflow_reserved_page_count    used_page_count reserved_page_count row_count
72057594050904064   274100017   1   1   1082295 1086782 5527998 0   0   0   0   1086782 5527998 61559096

This is an object with known extreme fragmentation (>90%) however my personal expectation is that a bad layout on disk shouldn't cause huge amounts of wasted space, just excessive seek latency due to minimising contiguous reads.

Also, assuming that individual pages under random insert fill up, then split into two pages half full, then each page should oscillate between 50% and 100% (average 75%) resulting in an approx 25% storage overhead for a highly fragmented table/index.


texts: check history along with dates for queries in Postgres

texts:
I am running PostgreSQL on my Linux terminal and If we do select pg_backend_pid(); it gives pid for specific session.
Below gives us only the last query executed
select pid,
       usename as username,
       datname as database_name,
       query,
       application_name,
       backend_start,
       state,
       state_change
from pg_stat_activity
where pid = 'your-pid';

but looks like this \s gives us all the history of query but not with dates. can we get dates along with them
and if we do save output to a filename where does it gets stored on Linux server?
\s filename

Kindly suggest if there are more specific way, actually I am looking for the queries executed on particular date.


texts: Deleting child record doesn't delete foreign key reference

texts:
I am using PostgreSQL 10.7. I have this strange occurrence where I fail to see the problem.
Whenever I delete all child-records referring to a parent, somehow the reference between these two tables persists.
I have a child and parent table:
CREATE TABLE IF NOT EXISTS parent
(
    ID SERIAL NOT NULL CONSTRAINT PK_parent PRIMARY KEY,
    reference VARCHAR(100) NOT NULL
);

CREATE TABLE IF NOT EXISTS child
(
    ID SERIAL NOT NULL CONSTRAINT PK_child PRIMARY KEY,
    parentID INT NOT NULL,
);

ALTER TABLE child
ADD CONSTRAINT FK_child_parent_ID FOREIGN KEY (parentID) REFERENCES parent (ID) MATCH FULL;

I delete all the records from the child table referring to the parent:
DELETE FROM child WHERE parentID = 1;

I select all the records from the child table referring to the parent:
SELECT * FROM child WHERE parentID = 1; // 0 records returned

I try to delete the parent record from the parent table
DELETE FROM parent WHERE ID = 1;

// result
ERROR: update or delete on table "parent" violates foreign key constraint "fk_child_parent_id" on table "child" Detail: Key (id)=(1) is still referenced from table "child".

I know about the ON DELETE option on the FOREIGN KEY constraint, but I just want to do this once...
What do I fail to see?
To be clear:

These are separate queries.
I've only 1 child table referring to the parent.
The child table does not return any records related to the parent.
I've checked if I had a duplicate fk constraint, but that is not the case.
I am not running the query within a transaction.


texts: I need to reduce the size of Database file -- I am confused between DBCC Shrinkfile and Data compression options

texts:
I need to reduce the size of Database file. I am confused between DBCC Shrinkfile and Data compression option.

DBCC shrinkfile will reduce the actual database file size as far as I know but Data compression will not do the same. please advice or their are any other options because I cannot delete any file from my prod DB.


texts:
Apologies for asking this like an interview question...
Say you have 3 tables A, B and C
A is huge
B references A’s primary key through a foreign key and is small
C wants to reference the data in B but doesn’t know how
Which of the following options seems better:

Add a column to C having a foreign key to B’s primary key which is a foreign key of A’s primary key
Add a column to C having a foreign key to A’s primary key directly
Something else...

The plan is for C to join with B using our new column, but which table should C reference?
My gut feeling is that on an insertion to C the constraint check will be faster if its referencing a smaller table but I'm not sure if that's how it actually plays out
Clarification: C is a subset of B and B is a subset of A and all proposed relations are 1:1


texts: How to deploy MongoDB config servers to achieve maximum availability during network partitions?

texts:
What's the recommended way to deploy config servers?

Assume, we have two shards, each one is a 3-nodes replica set.
If I deploy all config servers together with one of these shards, a single network partition can make the second shard completely not functional.

If I deploy 3 config servers in a distributed fashion, then after a network partition both shards will be available, but in read-only mode.

If I deploy config servers to a separate datacenter, it will just move the point of failure from shard-to-shard network part to shard-to-datacenter network part.

Any better options?


texts: Error trying to backup MySQL instance with mysqlbackup 8

texts:
I am trying to backup a whole MySQL 5.7 instance, using the following command:
mysqlbackup -u root -p --port 3306 --host localhost --backup-image=/data/mysql/backup/hot_bckp/full_8.22/full_mbi --backup-dir=/data/mysql/backup/hot_bckp/full_8.22/ backup-to-image

But I get the following error message during the early phase of execution:
Starting with following command line ...
mysqlbackup -u root -p --port 3306 --host localhost
    --backup-image=/data/mysql/backup/hot_bckp/full_8.22/full_mbi
    --backup-dir=/data/mysql/backup/hot_bckp/full_8.22/
    --exclude-tables=performance_schema backup-to-image

IMPORTANT: Please check that mysqlbackup run completes successfully.
       At the end of a successful 'backup-to-image' run mysqlbackup
       prints "mysqlbackup completed OK!".

Enter password:
210112 11:08:08 MAIN     INFO: No SSL options specified.
210112 11:08:08 MAIN     INFO: MySQL server version is '5.7.31-log'
210112 11:08:08 MAIN     INFO: MySQL server compile os version is 'el7'
210112 11:08:08 MAIN    ERROR: MySQL query 'SELECT member_role, member_host, member_port FROM
performance_schema.replication_group_members': 1054, Unknown column 'member_role' in 'field list'
210112 11:08:08 MAIN    ERROR: Connection initialization error.

mysqlbackup exit code: 19, Server returned error while executing sql

Any suggestion/help is appreciated.


texts: Select data divided in groups evenly distributed by value

texts:
I would like to select into 4 groups the data from a table having the sum of a values in the groups as evenly distributed possible. I am sure that I am not explaining it clear enough so I will try to give an example.

Here I use NTILE(4) to create the 4 groups:

SELECT Time, NTILE(4) OVER (ORDER BY Time DESC) AS N FROM TableX

Time -  N
-------------
10  -   1
 9  -   2
 8  -   3
 7  -   4
 6  -   1
 5  -   2
 4  -   3
 3  -   4
 2  -   1
 1  -   2

In the above query and result, the other columns have been omitted for brevity.

So you can see the groups also as follows:

  1    2    3    4
---  ---  ---  ---
 10    9    8    7
  6    5    4    3
  2    1
---  ---  ---  ---
 18   15   12   10  Sum Totals of Time

Notice that the Sum Totals of Time using NTile is not really balanced between the groups. A better distribution of the Time values would be for example:

  1    2    3    4
---  ---  ---  ---
 10    9    8    7
  3    5    4    6
  1         2
---  ---  ---  ---
 14   14   14   13  Sum Totals of Time

Here the Sum Totals of Time is more evenly distributed over the 4 groups.

How can I perform this through a TSQL statements?

Furthermore I have to say that I am using SQL Server 2012.
If you have something that can help me out, let me know.

I wish you a nice day.

Stan


texts: Disable parameter sniffing for better performance - good idea?

texts:
I have an optimized query in SQL Server 2012 that takes consistently ~1-3 seconds to run.

It works fast when I run it in SSMS, but when application does it (through sp_executesql), it is really slow, about 13 minutes for larger data.

After some digging and reading Slow in the Application, Fast in SSMS?, I concluded that there is a trouble with parameter sniffing (there is one parameter).

I disabled it with OPTION (QUERYTRACEON 4136) and it works fast with sp_executesql.

Is it a good idea (should I keep the option to disable parameter sniffing) or should I tune query so it works with sp_executesql and parameter sniffing?


texts: Enforce functional dependencies by using triggers in SQL99

texts:
The question is as below:

For R(A, B, C, D, E), write a trigger to enforce AB->C on insert.

This is my solution:

CREATE TRIGGER fdEnforceInsert
BEFORE INSERT ON R
REFERENCING NEW ROW AS N
FOR EACH ROW
DECLARE counter INT
BEGIN
  SELECT COUNT(*) INTO counter
  FROM R
  WHERE R.A = N.A AND R.B = N.B AND R. C <> N.C;
  IF(counter>0)
    THEN raise_exception ('AB->C on R was violated');
END;

One thing I'd like to ask is whether to use "before" or "instead of" in the second line?


texts: Why is a functional dependency described as a "one-to-many" relationship?

texts: As far as I know, the definition of a functional dependency reads that, given the attributes X and Y, "each X value is associated with precisely one Y value". But then the author of the article that I read called this a "one-to-many" relationship. Isn't this really "one-to-one"..?


texts: Not able to install any DB2 LUW 10.5 fixpacks

texts:
I am currently on Fixpack 1 and am trying to install ANY more recent fixpack. All of them refuse to apply themselves with similar errors. The one below is from Fixpack 2 file v10.5fp2_ntx64_server. Each of them up to the most recent says that they cannot upgrade my version, but none of them provides a reason.

After reading Installation Planning link, my brain hurts after coming across of this:

DB2 must be installed on a drive with short names enabled or a directory with no spaces such as E:\DB2\SQLLIB. To check the current 8.3 file name settings for a drive, in a command window with administrator privileges, enter

fsutil.exe behavior query disable8dot3

The following possible values are returned when you run the command:

0: Create 8.3 short file names (default)
1: Do not create 8.3 file names
2: Set 8.3 file names on a per volume basis
3: Disable 8.3 file names on all volumes, except the system volume

The registry state of NtfsDisable8dot3NameCreation is 0, which means that 8.3 file names are enabled on all volumes. To enable 8.3 file names on a global basis, enter

fsutil.exe behavior set disable8dot3 0

If DB2 is installed on a drive without 8.3 name support andin a directory without spaces, it must be reinstalled on a drive with 8.3 name support. If the folder you are trying to install DB2 is already created when 8.3 name support is disabled, then the short name is not created if you try a reinstall onto the same directory. Windows short names are created during the directory creation, hence you must install it into a new directory or if the directory is empty delete the directory and allow recreation.

On my system, the setting is at its default 2, which means "per volume". I am not sure if this is the reason the fixpacks refuse to install themselves.
Does this mean that IBM essentially puts their customers into a death trap, by allowing the initial install of DB2 to place it into C:\Program Files\ but refusing to install fixpacks later? I cannot believe that this is the case. Can anyone whose DB2 runs off of C:\Program Files\ explain how we are supposed to upgrade?
UPDATE:
By running dir /x I found out that all directories in the hierarchy from c:\ down to sqllib have short names. The lack of them cannot be the reason why updates are refusing to install.


texts: Trigger to delete inserted record if it doesn't exist in another table

texts:
I have a question.  I have two tables:

zip_code (zip_id, zip_code, state) - this is a static table that
stores areas I service
tech_zips (tz_id, tech_id, zip) - this is where I store what zip codes a
specific tech services

So, on my form, I have a select list that chooses a tech and a textarea to paste in zip codes.  This works fine, except it doesn't take into consideration whether or not I service a zip code.

Instead of coming up with crazy arrays and whatnot in PHP - I figured a trigger would be easier, except I know nothing about them.  :(

How would I write a trigger that does this: When a new zip code is inserted into tech_zips, the trigger sees whether or not that zip  exists in the zip_code table.  If it doesn't, then it deletes it.

Thanks for any help!  Oh, I have phpmyadmin.


texts: Change permissions of postgres copy to exported file

texts:
I have asked this question on SO but perhaps that was the wrong location. Apologies for double posting but the question may have a better audience here.

The original question is: https://stackoverflow.com/questions/25619290/change-permissions-of-postgres-copy-to-exported-file

And the text is below. Thanks.

When running the postgresql [9.3] "COPY TO" functionality, the postgres user creates a file. I have told it to put the file in the tmp directory (although this can be changed if needed).

This is run as part of a cron job, the user is an "export" user set up especially for this task. The end goal is for the exported file to end up in /home/export/created_file.csv.

1) postgres can't stash the exported file straight into the home directory of another user without setting up the folder to be overly permissive.

2) the export user can copy the file from /tmp to it's home directory, but then can not clean up (delete) the postgres owned file in /tmp.

This is a daily process and so old files will need to be deleted in some way. I can add a cron job to clean up the files afterwards but I was hoping for a cleaner solution - either a way to change the permissions on the file that postgres creates, or some way to give the export user the additional permission of being able to delete that file in advance of it's creation.

Giving the export user the same permissions as postgres in general however isn't an option, as it would then have free reign over the database.

Any advice, be it unix based or postgres based would be very welcome, thanks.


texts:
Our customer has a Microsoft SQL Server 2005 on Windows 2003 Server Enterprise, R2, SP2, 32bit. Running on Intel Xeon X5560 @2.80GHz, with 8 GB of RAM.

Occasionally, the users complained that the access to the server was slow. Although, that might be affected by other factors like workstation hardware, antivirus, etc.

But, I am thinking of maximizing the SQL Server itself.

I read about the "SQL AWE" option which could increase the memory allocation that can be used on the 32 bit OS.

Right now, the AWE is not enabled, I checked using "dbcc memorystatus", and I got result:

Memory Manager:
VM Reserved = 1694984 KB
VM Committed = 1691528 KB
AWE Allocated = 0
Reserved Memory = 1024
Reserved Memory In Use = 0

As I understood correctly, this 8GB of RAM is not being used optimally due to the 32bit OS.

Am I in the right direction here? I meant, if I enabled the SQL AWE, it will improve the SQL performance.

Note that I cannot change the hardware, and I cannot change the OS to 64 bits (not supported by our application).

Thanks.


texts: DROP DATABASE fails due to foreign key constraints

texts:
This is weird and I haven't ever encountered this.

I'm simply dropping a whole database.

$ mysql -u $RDS_USER -p$RDS_PASS -h $RDS_HOST -e "DROP DATABASE dev_test"

And yet it fails with this...

  ERROR 1217 (23000) at line 1: Cannot delete or update a parent row: a foreign key constraint fails

I then go to the database itself and I see that most of the tables have been dropped except for two.

Why does this happen?

I'm using MySQL 5.6.


texts:
I have written the following SQLite query:

SELECT sum(amount) / (julianday('now', '+1 day') - julianday('2012-08-11')) * 14 AS 'Cost Per Fortnight'
FROM
(
    SELECT "transaction".transamount AS amount
    FROM checkingaccount_v1 AS "transaction"
    JOIN Category_v1 AS category ON "transaction".CategID = category.CategID
    JOIN SubCategory_V1 AS subcategory ON "transaction".SubCategID = subcategory.SubCategID
    WHERE "transaction".transdate BETWEEN date('2012-08-11') AND date('now')
        AND category.categname = 'Food'
        AND subcategory.subcategname = 'Groceries'

    UNION ALL

    SELECT splittransaction.splittransamount AS amount
    FROM splittransactions_v1 AS splittransaction
    JOIN Category_v1 AS category ON splittransaction.CategID = category.CategID
    JOIN SubCategory_V1 AS subcategory ON splittransaction.SubCategID = subcategory.SubCategID
    JOIN checkingaccount_v1 AS "transaction" ON splittransaction.TransID = "transaction".TransID
    WHERE "transaction".transdate BETWEEN date('2012-08-11') AND date('now')
        AND category.categname = 'Food'
        AND subcategory.subcategname = 'Groceries'
) AS costs

I would like to know how I can simplify it. The two queries that are UNION ALLed are quite similar. Some values, such as the literal date, are also duplicated.

SQLite doesn't appear to support functions, stored procedures or variables. For this particular query, I require SQLite and can't use another programming language (like Perl) to execute it, so please don't suggest other tools/technologies.

If anyone is curious, the query is for a MoneyManagerEx report.


texts: SQL Server Failover Cluster Instance compatible with VMware HA?


texts:
I have to estimate the size of a DB2 DB; I used several methods and the problem is I get huge differences.
In order to simplify the problem, I'll focus on a unique table.

Use of the following SQL command :

  SELECT
  TABNAME,
  TABSCHEMA,
  SUM(DATA_OBJECT_P_SIZE) AS DATA_OBJECT_P_SIZE,
  SUM(INDEX_OBJECT_P_SIZE) AS INDEX_OBJECT_P_SIZE,
  SUM(LONG_OBJECT_P_SIZE) AS LONG_OBJECT_P_SIZE,
  SUM(LOB_OBJECT_P_SIZE) AS LOB_OBJECT_P_SIZE,
  SUM(XML_OBJECT_P_SIZE) AS XML_OBJECT_P_SIZE
  FROM
  SYSIBMADM.ADMINTABINFO
  WHERE TABNAME IN (‘TABLENAME')
  GROUP BY
  TABNAME,
  TABSCHEMA

  For table A, I get a total of 135MB

Use of the "Estimate Size" functionnality in DB2 Control Center

  For table A, I get a total of 3.5MB

At this point, the difference is REALLY huge. So i digged a little and decided to make an exact copy of table A (without indexes) with the 2 following commands

     CREATE TABLE B AS (SELECT *  FROM A) DEFINITION ONLY;
     INSERT INTO B SELECT * FROM A;

  Next I mesured the size of B (which is a clone of table A) : both methodes give me 3.5MB for this table

So here is my problem : I would like to know why does the SQL command tells me data weighs 38 times more on the original table ? I suspect preallocation and/or fragmentation because the original table is used for insert/update/delete statements, but commands like REORG (with reclaim extent), ALTER TABLESPACE REDUCE (and RUNSTATS) don't change the result.

Any clue ?


texts: why is the Log File is 5 times the database overnight when deleting?

texts:
The log file alone is  close to 500 GB while the database is 80 GB.
This is not a production system so I tried to delete a table which had 780,000 records. this table has 5 individual indexes that are sorted in ascending order, when i tried to delete 1 record with the PK in a clustered index, it got to 20 minutes before I cancelled it.

I left ssms running in "delete From Table" and now the log has exploded to 500GB.I canceled the query and now the database is in recovery. I cant delete anything from this table and its not the IO since i can delete millions from other tables (months ago, this growth is from this one delete).  The waits showed a lot of CXPackets and PageLatch_EX
but im trying to understand if there is a setting that its giving me a row versioning overhead. Read commited snapshot isolation is on, but DB snapshot is off.

now the recovery will take hours but what im planning to do it drop the indexes , update the statistics and drop the contraints so that i can try to tuncate but i see myself back here again. Should i delete at batches of 2000 and back up the log to clear it? remember one delete took more than 20 minutes and the only locked transactions are for the pagelatches. I included the query plan for the delete, im worried that somehow the parallel queries have hammered the log file harder that it needs too, any insight would save me loads of time, thanks in advance.


texts:
Hi all I have 2 fact tables with a measure group each, Production and Production Orders.

Production has production information at a lower granularity (at the component level) productionordershas information at a higher level (order level with header quantities etc.).

I have created a surrogate key link between the two tables on productionorderid. As soon as I add Prod ID (from productiondetailsdim) to the pivot table it blats out the actual qty (from prod order measure group) and I cannot combine the qty's from the two measure groups... How can I design the correct relationship between the two? please see my dim usage diagram.

Production Details is the dim that links the two fact tables... at the moment DimProductionDetails is in a fact relationship with Production.

I'm not sure what the relationship should be with Production Order (it is currently many to many) Thanks for your help!  If I combine the line level qty's with the header level i don't want the header quantities summed for every line (duplicates) i just want it summed at the order(header level) alongside the line qty's...

Please see example data between the two tables

I have to be able to duplicate this behaviour below...


texts: Marking a row as removed or deleted: Is it a good design practice?

texts:
This is a theoretical* design question about marking rows as removed or deleted. I assume rows should be marked as removed whenever there is a need to remove the pertinent data, but the row should stay in the relevant database table to maintain referential integrity.
* Theoretical, within the scope of this question, means that it should not be related to concrete example and also not related to concrete relational database.
For the sake of the question I have chosen a classical example: A Shop's database. And a situation is: shop's user wants to quit using the shop and wants his personal data purged.
We have an Orders table which references a Users table. If we try to DELETE a user row, we will lose all referenced orders (assuming we have strong referencing). Therefore instead of a DELETion of a user row we may mark such row as 'removed' (i.e., have a BIT (or boolean) column SET to '1', e.g., IsRemoved = 1) and clear his login, password, first name, last name columns to purge personal data. This is a first approach, which I believe is bad. While we get rid of personal data, the row still occupies space in database - just for the sake of referential integrity: no other useful meaning it has.
But there is an alternative, instead of having an Orders->Users relationship, we can have a different structure: Orders->Customers->Users, where the Customers table has no personal data. When a user wants to quit, we simply DELETE the pertinent User row, but Customer along with its related Orders stay stored in the database. No need to mark row as removed, no need for additional business rules to handle the removed rows behavior. I believe this approach is better and may be used for any situation where the IsRemoved row approach is used.
Question
Do you think second approach lacks real-life application in some situations? Or can it be used for any DB design?
P.S. Of course, it is possible to referential integrity with NULLs in FOREIGN KEYs, however this approach is out of scope. While it may be useful in the Shop's example, the question is hypothetical, and shop is used only as example, not as a real problem (which may be solved with NULLs).


texts: Using a GIN index on an integer array to model sets of tags

texts:
In PostgreSQL, what are the pros and cons of using a GIN index on a column of type integer[] to model sets of tags, instead of doing a relation table?

I am trying to design a simple data model with a table called post where each post can have a set of tags, similarly to Instagram or StackExchange.
It seems that I would be able to search for a post that contains a set of tags without having to join two tables, but I won't be able to easily get the list of tag strings for one specific post.


texts: APEX 5: Horizontal Scollbar on page instead of region

texts:
I'll generate in APEX 5 (Universal Theme) a large table with PL/SQL Dynamic Content.

To scroll down, there is a scrollbar on the page. To scroll to the right, there is only a scrollbar in the region/div.
So if user want to see the values in the most right column in the first rows, they have to scroll all down, move to the right and scoll all up.

Thats very anoying.

Any suggestions?


texts: In my organization we have several SQL Server 2016 Enterprise licenses in use.  None of them are running SSIS.  Since they are not in use, could I install just SSIS 2016 on a separate server and still be covered under the original enterprise license?


texts: PostgreSQL (10) show all NOTICE messages

texts:
I use https://console.bluemix.net/docs/services/ComposeForPostgreSQL/index.html#about-compose-for-postgresql

And for some select with long column alias ex:

select id as "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"
from tableName

I receive the following message:

NOTICE:  identifier "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa" will be truncated to "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"

Successfully run. Total query runtime: 166 msec.

Where can I find all these messages?

PS
I use sequelize and I met this problem  https://github.com/sequelize/sequelize/issues/2084
after migration from MySQL.

And I want to monitor if there are no selects in the application with such issue.


texts: Mirroring on-premise SQL Server to AWS EC2 SQL Server

texts:
Trying to mirror an on-premise SQL Server instance to EC2 instance, but not much information is available on this topic. The closest one I managed to find is: Implement SQL Server High Availability and Disaster Recovery on Amazon EC2 - Part 4

Has any one done this? If so is there any documentation available?

My intention is to make the EC2 as a backup for the HA in case my on-premise DB crashes.

Please note that this is not for AWS RDS, nor EC2 SQL Server to EC2 SQL Server; but on-premise SQL Server to AWS EC2 SQL Server.

The DB version is not a constraint, it can be SQL Server 2017 too.


texts: Scripting an application role with a hashed password

texts:
I need to script an application role with a hashed password, so I can copy it from one database to another.

Consider the following example code, which uses an Application Role to provide elevated access to a non-trusted user:

USE tempdb;

CREATE LOGIN LimitedLogin
WITH PASSWORD = 'Password1'
    , CHECK_POLICY = OFF
    , CHECK_EXPIRATION = OFF;

CREATE USER LimitedLogin
FOR LOGIN LimitedLogin
WITH DEFAULT_SCHEMA = dbo;

CREATE APPLICATION ROLE MyAppRole
WITH PASSWORD = 'Password2'
    , DEFAULT_SCHEMA = dbo;

EXEC sp_addrolemember @rolename = 'db_datareader'
    , @membername = 'MyAppRole';

CREATE TABLE dbo.Numbers
(
    [Number] int CONSTRAINT PK_Numbers
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1) NOT NULL
);

INSERT INTO dbo.Numbers
VALUES (1)
    , (2);

GO

Once we've created the test setup in tempdb, we can login as the [LimitedLogin] user, and run the following:

-- login as [LimitedLogin]

USE tempdb;

SELECT *
FROM dbo.Numbers;

The following error is returned, as expected:

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Numbers'
    , database 'Test', schema 'dbo'.

However, once we execute the sp_setapprole with the appropriate password, we can see the desired results from the dbo.Numbers table:

DECLARE @cookie VARBINARY(8000);
EXEC sp_setapprole @rolename = 'MyAppRole'
    , @password = 'Password2'
    , @fCreateCookie = 1
    , @cookie = @cookie OUT;
SELECT @cookie;

SELECT TOP(10) *
FROM dbo.Numbers;

EXEC sp_unsetapprole @cookie = @cookie;

I'd like to be able to automate creating the Application Role by scripting it from a source database to be applied to a destination database.  I can easily perform the majority of that by:

SELECT 'CREATE APPLICATION ROLE ' + QUOTENAME(dp.name) + '
WITH PASSWORD = ''xxxx''
    , DEFAULT_SCHEMA = ' + QUOTENAME(dp.default_schema_name) + ';'
FROM sys.database_principals dp
WHERE dp.type_desc = 'APPLICATION_ROLE';

However, I'd really like to be able to have the hashed password in the script, something like:

CREATE APPLICATION ROLE [MyAppRole]
WITH PASSWORD = 0x12345678 HASHED
    , DEFAULT_SCHEMA = [dbo];

Is this possible?  Presumably the hashed version of the application role password is stored somewhere in the database.

Using the SQL Server Management Studio "script to ->" functionality, the Application Role is scripted with a new password, as:

/****** Object:  ApplicationRole [MyAppRole]    Script Date: 9/22/2015 10:18:12 AM ******/
/* To avoid disclosure of passwords, the password is generated in script. */
declare @idx as int
declare @randomPwd as nvarchar(64)
declare @rnd as float
select @idx = 0
select @randomPwd = N''
select @rnd = rand((@@CPU_BUSY % 100) + ((@@IDLE % 100) * 100) +
       (DATEPART(ss, GETDATE()) * 10000) + ((cast(DATEPART(ms, GETDATE()) as int) % 100) * 1000000))
while @idx < 64
begin
   select @randomPwd = @randomPwd + char((cast((@rnd * 83) as int) + 43))
   select @idx = @idx + 1
select @rnd = rand()
end
declare @statement nvarchar(4000)
select @statement = N'CREATE APPLICATION ROLE [MyAppRole] WITH DEFAULT_SCHEMA = [dbo], ' + N'PASSWORD = N' + QUOTENAME(@randomPwd,'''')
EXEC dbo.sp_executesql @statement
GO

Clearly, that is not helpful in my case, although it does provide an interesting method of generating random passwords.

Based on the answers so far, I have created a Connect suggestion to add support for this to the product:

CREATE APPLICATION ROLE should support the 'HASHED' syntax for passwords.


texts: Get categorie, subcategories and number of products assigned to each

texts:
I have a list of products, and a list of categories, and a join table. Products can be assigned to multiple categories. Categories can have one level of subcategories. The tables look something like this:

products
=========
id | name
=========
 1 | taco
 2 | hamburger
 3 | burrito
 4 | pepperoni pizza

categories
=====================
id | name | parent_id
=====================
1 | American | NULL
2 | Italian  | NULL
3 | Pizza    | 2
4 | Chinese  | NULL
5 | Mexican  | NULL

category_product
========================
category_id | product_id
========================
          5 | 1
          1 | 2
          5 | 3
          3 | 4

So you can see that taco and burrito are in the mexican category. hamburger is in the american category. And pepperoni pizza is in the pizza category which is a subcategory of italian.

I'm trying to get a list of ALL categories and subcategories along with how many products are in each category and subcategory.

I thought this would do it:

SELECT
    c.name,
    c.id,
    c.parent_id
    count(p.id) AS num_products
FROM
    products AS p
    CROSS JOIN categories AS c
    LEFT JOIN category_product AS cp
        ON cp.category_id = c.id
        AND cp.product_id = p.id
GROUP BY c.id
ORDER BY c.parent_id, c.name

This properly returns a list of all categories and subcategories (even categories that are not assigned, which is how I want it). But the num_products field is 4 for every row, which not-coincidentally is the total number of products.

Alternatively I can do something like this:

SELECT
    c.name,
    c.id,
    c.parent_id,
    count(p.id) AS num_products
FROM
    categories AS c
    LEFT JOIN category_product AS cp ON c.id = cp.category_id
    LEFT JOIN products AS p on p.id = cp.product_id
GROUP BY c.id
ORDER BY c.parent_id,c.name;

Which does return the correct number of products in each category, however it doesn't include all the categories that don't have any products AND it doesn't return the parent categories of subcategories that have products.

Can either of the above queries be altered to give what I want? Can the first query be adjusted to return the correct number of products per category? Or can the second query be adjusted to also return all categories and subcategories?


texts:
I have an xml column that looks like this:

<document>
      <item>
        <key>
          <string>Michael Burry</string>
        </key>
        <value>
          <string>CDO</string>
        </value>
      </item>
      <item>
        <key>
          <string>Adam Smith</string>
        </key>
        <value>
          <string>£20</string>
        </value>
      </item>
      <item>
        <key>
          <string>World Cup 2018</string>
        </key>
        <value>
          <string>football</string>
        </value>
      </item>......

Instead of listing the entire contents of the column, I want to instead list only the value of <string>football</string> when the first value is <string>World Cup 2018</string>.

Using value('(/document/item/key/string)[7]', 'nvarchar(max)') is not suitable as "World Cup 2018" can appear anywhere.


texts:
I'm working on a project that will result in an SQLite database of about 6 GB of text content (encoded UTF-8). The text will be diverse: it will have a great deal of plain text writing, but also a significant amount of special characters (tildes, backticks, section symbols, mdash's, endash's etc.). There will also be math formulas.

It looks like .import will be the load method.

Question: What could I use as an .separator value that won't be in my text?

I've grepped the future text with a few ideas and have not identified a separator that isn't in the actual content.

I suppose I could escape whatever separators may be in the text. But I prefer to avoid that option if I can.


texts: Joining two Mysql VIEWs takes a very long time

texts:
I have a textbox (for which I used jQuery auto-completion) in my PHP form where user can insert an actor/actress name and then by clicking "Search" button, a new window is opened showing list of movies by that actor/actress.

This is a query where I get movies by actor name:

$query = $conn->prepare("SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink FROM cast_movie as c JOIN film_info as f ON c.ImdbId = f.ImdbId WHERE c.castName LIKE :q");
 $query->execute(array(':q' => '%' . $searchText . '%' ));

My question:

The above query works fine if user select a name from auto-completion list. However I would like to enable user to write any name (even if he couldn't find from auto-completion list). For example, If user write "tom" in the textbox, and click on "search" button, I want to show list of all movies by all actors that their name containing word "tom".

For this purpose, I used LIKE :q and ':q' => '%' . $searchText . '%' in the above query, but the query never ends!!(I think because cast_movie is a VIEW which is quite large (with 3 million rows) and joining this view with the other table takes a very long time (actually I waited for 10 minutes and it didn't finished yet).

Could someone kindly let me know if there is any way to fix this? (I read we can use index for joining very large tables, but I think it's not possible to define index for cast_movie since it is VIEW.)

More info about tables and views:

cast_movie is a view which is made by joining "movie_roleNames" and "movies".

movie_roleNames is also a view which is made by joining two tables "Cast" and "nameRoles".

film_info is also a view that is made by joining two tables "movies" and "movies_info".

The structure of the above tables:

table "movies": Id, movieName, ImdbId(unique Id of movies), Rate, numVotes, year (indexes: ImdbId, movieName, year)

table "cast": castName, castImdbID (unique Id of casts) (indexes: castName, castImdbID)

table "nameRoles": Id, castImdbId, movieImdbId, role_Id, (indexes: movieImdbId, castImdbId)

VIEW "movie_roleNames": Id, castName, castImdbId, movieImdbId and the join statement was: SELECT n.Id, c.castName, n.castImdbId, n.movieImdbId FROM nameRoles as n join Cast as c ON n.castImdbId = c.castImdbID

VIEW "cast_movie": Id, castName, castImdbId, movieImdbId, movieName and the join statement was: SELECT m.Id, r.castName, r.castImdbId, r.movieImdbId, m.movieName FROM movie_roleNames AS r JOIN movies AS m ON r.movieImdbId = m.ImdbId

All ideas are highly appreciated,


texts:
I'm trying to get to grips with Foreign Keys in MySQL (5.6), and can't figure out why the below doesn't work:

CREATE TABLE `test`.`parenttable` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `ix_parent_name` (`parent_name` ASC));

CREATE TABLE `test`.`childtable` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `ix_parent_id` (`parent_id` ASC),
  CONSTRAINT `fk_parent`
    FOREIGN KEY (`parent_id`)
    REFERENCES `test`.`parenttable` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

The warning I get from SHOW WARNINGS; is:

Create table 'test/childtable' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.

But as far as I can see the 'referenced table' is test.parenttable

The 'referenced column' is id, and id is the Primary Key so automatically has a UNIQUE index, right?

I checked the documentation, and as far as I can see I meet all the criteria:

Same Storage Engine: INNODB by default
Similar Data Types: both are INT UNSIGNED NOT NULL
Indexes on both Foreign and referenced keys

What am I missing?


texts:
I would like to ask how am I able to export my output file as YYMMDD.txt format in SQL Job?

Right now i'm using SQL Token with

$(ESCAPE_SQUOTE(DATE))$(ESCAPE_SQUOTE(TIME)).csv

My output now will be

YYYYMMDDHHMMSS.csv

I'm trying to figure out how I could get it to export as YYMMDDHHMMSS.csv

Appreciate any help, been searching but could not find any related question/answer.


texts: When to Use Functions over Stored Procedures

texts:
What advantages/circumstances do functions have over stored procedures? Want to justify use for function. It seems stored procedures benefit in every way, from transaction handling, input, multiple values, with insert into...exec. So when are ideal situations to apply functions rather than sps? This is not an opinion based question or recommendation, or requesting to use sps all the time, just want to see benefits of functions.

I see Number 5 is function advantages, but there are many ways around this in sprocs.


texts: List primary replicas and non-HA databases - excluding forwarder replicas

texts:
When asking which databases are writable on any given server, I've been using the below snippet to list PRIMARY replica databases and databases that do not participate in an AG.

Now that I've deployed a Distributed Availability Group, it's not working anymoar! Since the local forwarder reports as a PRIMARY replica on the sys.dm_hadr_availability_replica_states DMV, my old query is telling me a database is writable when it is not.

  How do I change this query to exclude databases in a local forwarder replica?

select
    ars.role_desc
   ,ag_name = ag.[name]
   ,adc.[database_name]
from sys.availability_groups ag
join sys.dm_hadr_availability_replica_states ars on ars.group_id = ag.group_id
join sys.availability_databases_cluster adc on adc.group_id = ag.group_id
where ars.is_local = 1
    and ars.role_desc = 'PRIMARY'
union all
select
    'LOCAL_ONLY'
   ,@@servername
   ,[name]
from sys.databases d
where not exists (
    select 1
    from sys.availability_databases_cluster adc
    where adc.[database_name] = d.[name]
);

...or more simply...

  How do I list all local forwarder databases?


texts: Is there a way to automatically keep per-tenant schemas synchronized?

texts:
Suppose you create a bug-tracking system, where every customer has their own schema with their own issues table. Later, you are asked to add a priority column to issues.

Is there a way to apply this change to all customer schemas at once? (Preferably without third-party tools.)