texts: How to convert varchar iPv4 Address into a number deterministically

texts:
What are the various ways to convert a varchar iPv4 Address into a number without using PARSENAME() (non-deterministic) function in SQL Server, in order to create a persisted calculated column?

I used the string manipulation technique from Extract part of string based on nth instance of character to create the following:

Query

select
((256*256*256)*convert(bigint, ltrim(rtrim(substring(replace('255.255.255.255','.',replicate(' ',8)),1, 9)))))
+
((256*256)*convert(bigint,ltrim(rtrim(substring(replace('255.255.255.255','.',replicate(' ',8)),10, 9)))))
+
((256)*convert(bigint,ltrim(rtrim(substring(replace('255.255.255.255','.',replicate(' ',8)),19, 9)))))
+
(convert(bigint,ltrim(rtrim(substring(replace('255.255.255.255','.',replicate(' ',8)),28, 9)))))

Here is a visual of what the string manipulation looks like before each segment is converted to BIGINT and multiplied:

... This works to create a deterministic calculated column that can be persisted. What other more efficient techniques are there out there?


texts: PostgreSQL: native library learn the filesystem path that was used to load it?

texts:
From the _PG_init of a dynamic library that postgres has loaded (either because of a function declaration referring to it, or just LOAD pathname; in SQL), is there any call I can make, not platform-dependent, to find what full pathname was used to load me? (I could get the dynamic_library_path and try to find myself on it, but that's error prone, and if the LOAD command was used, or a full path was used in a function declaration, then all bets are off.)

It seems like there's a linked list in dfmgr.c with exactly the full path I want ... if it were not declared static, and if there were an easy way to know something like my own handle to match in the list....


texts: Why is adding a NOT NULL column with a default constraint instantaneous?

texts:
CREATE TABLE TestTab (ID INT IDENTITY(1,1), st nvarchar(100))

INSERT INTO TestTab (st) values ('a')
INSERT INTO TestTab (st) values ('b')
INSERT INTO TestTab (st) values ('c')
INSERT INTO TestTab (st) values ('d')
INSERT INTO TestTab (st) values ('e')

INSERT INTO TestTab (st) SELECT TOP 10000 st from testtab
GO 30

ALTER TABLE TestTab ADD newcol nvarchar(10) DEFAULT 'newcol'
UPDATE TestTab SET newcol = 'newcol'  --6 sec
ALTER TABLE TestTab ADD newcol1 nvarchar(10) DEFAULT 'newcol1' NOT NULL

DROP TABLE TestTab

When I execute this test script, the ALTER with UPDATE takes 6 seconds which is understandable.

However, the ALTER with the DEFAULT NOT NULL executes instantaneously even on a much larger table. Is there any explanation on why this is instantaneous? On the physical disk, data still needs to be written to all rows right?

I tried looking at SET STATISTICS IO ON and the Query plan, however those dont seem to be available for DDL operations.


texts: MySQL Workbench, how to refresh EER diagram?

texts:
I'm using MySQL Workbench to design my MySQL database and model how entities would look like and how they would relate to each other. Now, sometimes I use Doctrine ORM. When using Doctrine ORM, I create classes/objects that are domain objects and can be persisted to a database. And through DocBlock Annotations I can express how to relate entities to each other.

With MySQL Workbench I can view an EER Diagram that shows me how objects are related, and by looking at symbols I can see relations clearly. The problem with this approach is that I need to generate database schema using Doctrine CLI. And then in MySQL Workbench I must create an EER model from an existing database. The thing is I change the database schema a lot, and to see the relations in MySQL Workbench I must create an EER model every time. This is time consuming.

Is there a way to just load an EER diagram from an existing database, and then, when the database schema changes, just refresh and see changes, so I don't have to generate the EER diagram for every little change?

I have used Navicat in the past, and there you can do that. When the database changes, just press F5 and relations change immediately in the ER diagram. The only problem with Navicat is there are no symbols between tables that show their relation. Only lines.

Is it possible to have that functionality in MySQL Workbench? If not, are there other tools with which I can see ER diagrams, and that refresh the diagrams as soon as I change the database schema?


texts: Insert into table is slow in the night but very fast during day time

texts:
I have an SSIS package that does an insert into a table and is taking 90 minutes to run but when I run the same package during day time its very slow.

during whole insert process in the night, it is waiting on PAGEIOLATCH_EX wait and during the day time it doesn't wait on anything and runs fast.

I couldn't understand why this is happening. Also during night, there is processes that runs on other database which is not related to this database and that runs for almost 4 hours. Not sure if that is impacting disk and causing slowness overall.

Here's the SQL query...

INSERT INTO dbo.Test (
    Source_System_Cd
    , Account_Id
    , Portfolio_Num
    , Instrument_ID
    , Market_Value_Dt
    , Broker_Dealer_Cd
    , Advisor_Firm_Cd
    , Advisor_Rep_Cd
    , Advisor_Rep_Cd_1
    , Advisor_Rep_Cd_2
    , Advisor_Rep_Cd_3
    , Advisor_Rep_Cd_4
    , Advisor_Rep_Cd_5
    , Strategy_id
    , Strategy_Nm
    , Managed_Product_Fl
    , External_Security_ID
    , Instrument_Type_ID
    , Share_Qty
    , Market_Val
    , Strategy_Family_Id
    , Strategy_Manager_Id
    , Portfolio_Manager_Id
    , Product_Id
    )
SELECT DISTINCT p.Source_System_Cd
    , p.Account_Id
    , ISNULL(p.Portfolio_Num, - 1) AS Portfolio_Num
    , p.Instrument_ID
    , p.Market_Value_Dt
    , p.Broker_Dealer_Cd
    , p.Advisor_Firm_Cd
    , p.Advisor_Rep_Cd
    , aa.Advisor_Rep_Cd_1
    , aa.Advisor_Rep_Cd_2
    , aa.Advisor_Rep_Cd_3
    , aa.Advisor_Rep_Cd_4
    , aa.Advisor_Rep_Cd_5
    , p.Strategy_id
    , p.Strategy_Nm
    , p.Managed_Product_Fl
    , p.External_Security_ID
    , p.instrument_type_id
    , p.share_qty
    , p.market_val
    , p.Strategy_Family_Id
    , p.Strategy_Manager_Id
    , p.Portfolio_Manager_Id
    , p.Product_Id
--select count(*)
FROM dbo.Test_Stage p
LEFT JOIN Advisor.Account_Test aa
    ON aa.Source_System_Cd = p.Source_System_Cd
        AND aa.Advisor_Firm_Cd = p.Advisor_Firm_Cd
        AND aa.Account_Id = p.Account_Id
LEFT JOIN advisor.Test h
    ON p.Source_System_Cd = h.Source_System_Cd
        AND p.Account_Id = h.Account_Id
        AND p.Portfolio_Num = h.Portfolio_Num
        AND p.Instrument_ID = h.Instrument_ID
        AND p.Market_Value_Dt = h.Market_Value_Dt
WHERE p.Source_System_Cd = 2
    AND h.Source_System_Cd IS NULL
    AND p.Market_Value_Dt = @Business

O drive has the data file of this database and during the whole insert process, O drive is 95% busy and i'm also seeing some IO latency in the disk sub system.

dbo.Test table has 300 million rows.

can anyone please help on this ?


texts: Replication from Azure SQL Database table to On-premise SQL Server possible?

texts:
I have a table on Azure SQL Database that I would like to have replicated/mirrored to our on-premise SQL Server

So the on-prem SQL Server would have a copy of table from Azure that is always up-to-date, available for read-only queries

Is there a technology for this ?

The reason I need this is because I need to join this Azure table to some tables on-premise (on 300K + rows) in a query, and linked server is not working for me very well, despite all the tricks and workarounds I have tried

Regards,


texts: Design: tracking receipt of categorically different things

texts:
I'm designing a database to track receipts and shipments of various items for a repair facility.

First, let's say that the items are all notebook computers that circulate in and out, then the schema is obvious. Three tables -- device, receipt, and receipt_device -- will suffice for the receiving part (which is mirrored by the shipping part).

But what if not only devices are received, but also parts? Like devices, parts also have identifying part numbers and serial numbers, but the similarities end there. A device entry can have owner and distributor and various details like OS and RAM that are not applicable to a part. And a part entry may (depending on implementation) have a used_for field to denote which device it ends up in, which naturally doesn't apply to a device.

I've thought of various ways of dealing with this, and so far the most promising solution I've come up with is to create two new tables, part and receipt_part. For data entry, the receipt form will need two subforms, one for receipt_device and one for receipt_part, which is somewhat clunky but not a deal-breaker.

Any suggestions would be welcome. If it makes any difference, devices and parts are rarely, if ever, received or shipped together.


texts:
I can't quite seem to figure this out, and hoping someone can help. I was trying something like this, but doesn't seem to work:

SELECT *
FROM testtable
GROUP BY SUBSTRING_INDEX(urlTest,'/',3)
HAVING COUNT(*)>1

Here is a (simplified) table structure with what I am using:

CREATE TABLE `testtable` (
  `field1` int(11) NOT NULL,
  `field2` datetime NOT NULL,
  `urlTest` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `testtable` (`field1`, `field2`, `urlTest`) VALUES
(2, '2010-01-01', 'http://test1.com/somethingelse.php?id=1'),
(5, '2010-01-01', 'http://test1.com/'),
(6, '2012-02-02', 'http://test1.com/newscript/something'),
(7, '2013-02-02', 'http://test2.com/newscript/something'),
(8, '2014-02-02', 'http://test3.com/newscript/something'),
(9, '2015-02-02', 'http://test3.com/');

ALTER TABLE `testtable`
  ADD PRIMARY KEY (`field1`);

The output I want to get is to return any "base" urls that are identical with an 'identical' date, that I can manually inspect.

In other words, although "test1.com/somethingelse.php?id=1" and "test1.com" are "unique" url entries, the "base" url (i.e., "test1.com") is identical/a duplicate (because the dates -> 2010-01-01 <- are also identical). However, test1.com/newscript/something would not count as a 'duplicate' - because although the 'base' url is the same - it has a 'unique' date to the rest of the 'test1.com' urls (i.e., "2012-02-02")

So the output I'd like to get is:

2, 2010-01-01, 'http://test1.com/somethingelse.php?id=1'
5, 2010-01-01, 'http://test1.com/'

(because only test1.com has the same "base" unique URL (test1.com) as well as has the same timestamp. "test3.com" - while the same "base" url - since the actual timestamps are different, would be considered 'unique' urls)

How would I accomplish this?

Thanks!


texts: SQL Server date conversion fails due to some user/db setting

texts:
Which user/db setting would cause the date conversion here to fail with Conversion failed when converting date and/or time from character string?

SELECT
    -- This works:
    CONVERT(datetime2, '01 ' + LEFT([MonthName], 3) + ' ' + CAST([Year] as char(4)), 106)

    -- This doesn't:
    ,CONVERT(datetime2, '01 ' + LEFT([MonthName], 3) + ' 2018', 106)

[Year] is of type int NOT NULL.

[MonthName] is of type nvarchar(100) NOT NULL and its collation is SQL_Latin1_General_CP1_CI_AS.

DBCC USEROPTIONS shows this:

language   | us_english
dateformat | mdy

I tried creating another table with seemingly the same settings and the query works there. Something is obviously different but I can't figure out what.

EDIT

Doing a SELECT DISTINCT [Year], [MonthName] reveals that there's data only for 'December', 2017. So yes, the month name is in English.

Leap years can't be the problem because of the previous point but also because I'm forcing the day to be 1.

Interestingly, TRY_CONVERT doesn't return NULL for any row. I did a

WHERE TRY_CONVERT(datetime2, '01 ' + LEFT([MonthName], 3) + ' 2018', 106) IS NULL

I'm suspecting that inputting the ' 2018' causes the resulting string to be of some other collation (or something) which makes SQL Server expect the date format to be something different.

Here's the weird part. The query works if it's like this:

SELECT DISTINCT
    CONVERT(datetime2, + LEFT([MonthName], 3) + '01 2018', 106)
    ,CONVERT(datetime2, '01 ' + LEFT([MonthName], 3) + ' 2018', 106)
    ,[Year]

But removing either the [Year] or the DISTINCT will again produce the same error.

The query has only two tables joined in the FROM clause and no WHERE clause. The [MonthName] comes from the second table and the [Year] from the first.


texts: How to find number of days since beginning of fiscal month?

texts:
This relates to my question How to find XXth day of previous month in SQL server?

The fiscal month starts on 26th of each month.

I need to find out from any date:

The date of the start of the fiscal month (Start_Of_Fiscal_Month)
The number of days into the fiscal month (Day_Of_Fiscal_Month)

For example:

CurrentDate - '2014-04-26'

Start_Of_Fiscal_Month = 2014-04-26
Day_Of_Fiscal_Month = 1

CurrentDate - '2014-05-02'

Start_Of_Month = 2014-04-26
Day_Of_Fiscal_Month = 7


texts: More efficient way to write multiple "AND"?

texts:
Anyone know a way to write this in a different manner ?

SELECT tableA.NO
FROM tableA
WHERE tableA.NO IN (SELECT GRI_NO FROM tableBB WHERE GRO_NO =62)
  AND tableA.NO IN (SELECT GRI_NO FROM tableBB WHERE GRO_NO =50)
  AND tableA.NO IN (SELECT GRI_NO FROM tableBB WHERE GRO_NO =70)

I have many more AND to add and it become quite a long query.  Does an equivalent of WHERE GRO_NO IN (62, 50, 70) exist for AND?

Edit below:

The equivalence of sampleA1 is sampleA2 for the OR keyword.

sampleA1: WHERE GRO_NO IN (62, 50, 70)

sampleA2: WHERE GRO_NO =62 OR GRO_NO =50 OR GRO_NO =70)

Does an equivalent of sampleB2 exist for the AND keyword ?

sampleB2:WHERE GRO_NO =62 AND GRO_NO =50 AND GRO_NO =70)

Edit2: I'v remove performance tag since it was more related to clarity / syntax / shorter sql than DB performance.


texts: Create Oracle DB LINK to a database with no DB_DOMAIN

texts:
I'm trying to create a database link between this two DBs.

My local DB is 11.2.0.3.

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MYDB.DOMAINNAME

SQL> select name, value from v$parameter where name in ('db_name', 'db_domain', 'global_names');
NAME                 VALUE
-------------------- ----------------------------------------
db_domain
global_names         TRUE
db_name              mydb

The remote DB is 11.2.0.2 configured as follows:

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
myremotedb

SQL> select name, value from v$parameter where name in ('db_name', 'db_domain', 'global_names');

NAME                 VALUE
-------------------- ----------
db_domain
global_names         FALSE
db_name              myremotedb

I want to create a DBLINK in the local database to the remote database.

I have my tnsnames (mydb's machine) configured as follows:

REMOTE-DATABASE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(Host = 192.168.1.2)(Port = 1521))
    )
    (CONNECT_DATA =
      (SID = myremotedb)
    )
  )

Then, when I try to create a database link in my local database, since global_names parameter is set to true, oracle appends the local domain name to the database link name

SQL> create database link myremotedb@myuser connect to myuser identified by mypasswd using 'REMOTE-DATABASE';

Enlace con la base de datos creado.

SQL> select db_link from user_db_links;

DB_LINK
------------------------------
MYREMOTEDB.DOMAINNAME@MYUSER

and if I try to use it, it fails

SQL> select * from dual@MYREMOTEDB@MYUSER;
select * from dual@MYREMOTEDB@MYUSER
                   *
ERROR en línea 1:
ORA-02085: database link MYREMOTEDB.DOMAINNAME@MYUSER connects to MYREMOTEDB

SQL> select * from dual@MYREMOTEDB.DOMAINNAME@MYUSER;
select * from dual@MYREMOTEDB.DOMAINNAME@MYUSER
                   *
ERROR en línea 1:
ORA-02085: database link MYREMOTEDB.DOMAINNAME@MYUSER connects to MYREMOTEDB

How can I create this database link in this scenario?

The remote db doesn't have a db_domain. How can I force oracle to create a DBLINK with no domain name?

Regards!


texts: How to update commands in Ola Hallengren's SQL Server jobs?

texts:
I am looking for an easy way to update the jobs that get created from Ola's MaintenanceSolution.sql script without having to completely delete and re-create them or having to modify each job individually by going into the job's properties, then into the step and modifying the command.

Specifically, I'm trying to update the @CleanupTime argument, which is used to tell the job when to delete old backups. According to his FAQ, whenever a newer version of his script is available and you want to update to that newer version, you just need to download the MaintenanceSolution.sql script and run it again, with the option of specifying SET @CreateJobs = 'N' if you don't want to create the jobs. That tells me that if you leave @CreateJobs = 'Y', it would create the jobs anew, but this doesn't seem to be the case.

For example, if the "DatabaseBackup - USER_DATABASES - FULL" job already exists, with the command in the step showing @CleanupTime = 48, then I re-run the MaintenanceSolution.sql script with @CleanupTime = 168, and then go back into the job's step, it still shows @CleanupTime = 48.


texts:
Can someone help me with this school question? I have this data model shown below:

here is the question:

You are modeling an emergency communication system used to contact teachers in case inclement weather emergencies cause delayed openings or closures of the school. You may reach teachers by phone and/or email. The school must have at least one means of contact (phone or email) for each teacher. Each teacher may have more than one of each contact type. Which type of entity would you use in conjunction with the TEACHER entity to model a teacher’s contact information?

You decide to model a CONTACT table with primary key of Teacher-ID and Contact-Info, which is the contact information itself. The school principal, a major stakeholder, tells you she wants to track the area code for each teacher’s phone number(s):

Will your original modeling concept work? If not, how would you change the model to reflect the principal’s request?
How many entities would you need to represent the unique attributes of telephone numbers and email addresses so that no entity contained null values?
The principal reminds you that every teacher must have at least one phone number, but having an email address is not a requirement. Where would you reflect the phone number constraint in your model?

Can someone explain how to enforce the constraint?


texts:
I have created a config file for dumping .sql with no password prompt, is it possible to do this when using import?

I have read somewhere to just add on the cnf file "mysqlimport" then the credential details, but gets me back with an mysql error code 2000 'user'@'localhost' access denied, using password (NO).

I also tried granting this user access, but no success. This user is different from the user I use for sql dumps.

This is how I run the import command:

mysqlimport --defaults-extra-file=~/.mysql/mysqlimport.cnf reports < /tmp/testdump.sql

error message is

ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: NO)


texts: PhpMyAdmin versus Script (Load Data Infile)

texts:
If I run the following query in the SQL box from PhpMyAdmin:

LOAD DATA INFILE '/home/sveiv/public_html/testload.txt' INTO TABLE testtable FIELDS TERMINATED BY ';'

everything works fine.

If I run the following script (testload.php)

<?php

$conn=mysqli_connect('localhost','username','password','dbname');

$conn->query("LOAD DATA INFILE '/home/sveiv/public_html/testload.txt' INTO TABLE testtable FIELDS TERMINATED BY ';'");
?>

nothing happens.

PS: The connection to the database has been established successfully.

Can anyone help me? Thank you very much!


texts: SQL Server - How to improve/optimize remote scan on a linked server

texts:
I'm trying to figure it out how to optimize/improve the following thing, on a VM hosted in MS Azure and a database, also hosted in MS Azure. Besides the instance hosted in MS Azure, there is another instance (local) that has a dedicated database for an integration made between two software applications and there is a linked server between the two instances.

Lately, I noticed that the remote scan is taking up to 100% causing poor performance on some stored procedures that updates the database hosted in Azure.

Here's how the execution plan looks like:

Part of the stored procedure executed against the database from local instance that do some updates on the linked server, looks like this:

UPDATE docl
SET docl.udfvalue1=WMSC.QtyReceptioned,
    docl.udfvalue2=WMSC.QtySent,
    docl.Stringfield1=WMSC.WAREHOUSE
FROM  MyLinkedServer.DataBase.dbo.DocLines docl
INNER JOIN #WMS_Confirmed WMSC on docl.gid=WMSC.GID

UPDATE es
SET es.ADFLAG3 = 1
FROM MyLinkedServer.Database.dbo.DocHeader es
WHERE es.OrderDate>'2018-01-01' -- OrderDate is one of the indexes on `DocHeader` table
AND EXISTS (SELECT 1
              FROM #WMS_Confirmed WMSC
              WHERE es.gid = WMSC.fDocGID
             );

What I found online, like enabling Dynamic Parameters and NestedQueries on linked server provider or OPTION (RECOMPILE), didn't helped at all. Is there something else that I am missing? Thanks


texts: Select rows from table having two specific values

texts:
I have a single table containing nothing but productID and colorID.  There are 10,000 rows in the table.

I need to be able to select all productID having colorID 2 and colorID 5.  For example:

productID colorID
1         1
2         9
3         2
4         2
3         6
3         5
1         5

Using this data set I am looking for productID 3, since it is the only productID that has colorID 2 AND colorID 5 (it also has colorID 6, but I don't care about that)

Using WHERE colorID=2 AND colorID=6 returns 0 rows.

Using WHERE colorID IN (2,6) returns values for products that do not have both colorID 2 and 6.

This seems like a simple thing to query, but I am finding myself up against a wall.  What's the best way to accomplish this in a reasonably efficient way?


texts:
I'm not a DBA by any means, but I have a basic understanding of some of the underlying principles of database optomization.

Currently we have a table that logs session data, it writes about 1.2million rows a month.  At the beginning of each month we split off last months rows and move them to a table called "tbl_session_mm_yyyy".  We want to start querying the live table and the older tables and pull out details like last logged in (simple sql query, select maxdate from tbl_live, where accountid = 123 union select etc from tbl_old etc).

Now i'm thinking that the design of our database structure isn't quite right for this.  So where we would have 12 tables for the year, each year, we would have to keep building huge unions to get all those tables.  Thus would it be better to do this:

tbl_sessions: - this is the live table that is written to all the time, indexes on things like userid, accountid. this would only ever contain the current months records.

tbl_old_sessions: - this would replace the monthly table and be one giant table that contains all previous months records. Partioned by month (on a datefield populated by now()) and indexed on accountid, userid etc

Does this design make better sense than my previous one of monthly tables? should i be worried about partioning the tbl_old_sessions if i'm doing very little querying by actual date?


texts: Find next entity with ordered junction table

texts:
I have guides which are linked to series (many to many) with a junction table like so:

guides

id  etc.. (more columns)
--
1
13
21
30

series

id  etc.. (more columns)
--
1
3
4

series_guides (primary key consists of these 3 columns) also guide_id is unique, a guide can only link to a series once.

PK series_id  PK guide_id  PK order
------------  --------     -----
1             13           1
1             21           3
1             1            2
3             30           2

Now my question is: How do I get the next guide when I know the ID of some other guide?

Example: Someone is reading guide 13 (guide_id), what query do I need to run to get the next guide from the series. Which is guide 1.

This is what I tried so far, and I actually think it works. However maybe there is a better way to do this?

SELECT
    sg2.`order`,
    sg2.guide_id
FROM series_guides sg
JOIN series_guides sg2 ON sg2.series_id = sg.series_id
WHERE sg.guide_id = 13 AND sg2.order > sg.`order`
ORDER BY sg2.`order`
LIMIT 1;

Any help is much appreciated


texts: SQL Server on CentOS using CAL licensing

texts:
I have been running a PoC for a large data science workload with SQL Server on CentOS.
I recently discovered that SQL is using only 20 cores of a 56-core server.  I understand that this is because it is operating under the Server+CAL licensing mode, as shown by this entry in the SQL log:

  SQL Server detected 2 sockets with 28 cores per socket and 28 logical processors per socket, 56 total logical processors; using 20 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

I know I can change Edition using mssql-conf, but is it possible to change the licensing mode so that I can address all CPU sockets?


texts: Create Slowly Changing Dimension table from Repeated Data

texts:
I am receiving transaction snapshot data from a file, it contains a history of repeated data. Currently trying to find Slowly Changing Dimensions on table with [ProductId] Business Key. Many attributes exist: ProductTitle, Category, this is a sample table, actually have around 10 more attributes. How do I create a Product Slowly Changing Dimension table query?

Searching for a performance optimized way, if I have 10 columns, not sure if Group By on 10 columns is optimal

With SQL 2016, is there a function to obtain this data? Should I use Lead/Lag Function? FirstValue/Last Value? New analytics syntax? An attempted query is below.

Note: Data comes from a 1970 legacy file system containing historical data.

Data:

create table dbo.Product
(
    ProductId int,
    ProductTitle varchar(55),
    ProductCategory varchar(255),
    Loaddate datetime
)

insert into dbo.Product
values
 (1,'Table','ABCD','3/4/2018')
,(1,'Table','ABCD','3/5/2018')
,(1,'Table','ABCD','3/5/2018')
,(1,'Table','ABCD','3/6/2018')
,(1,'Table','XYZ','3/7/2018')
,(1,'Table','XYZ','3/8/2018')
,(1,'Table','XYZ','3/8/2018')
,(1,'Table','XYZ','3/9/2018')
,(1,'Table-Dinner', 'GHI','3/10/2018')
,(1,'Table-Dinner', 'GHI','3/11/2018')
....more data with ProductId =2,3,4, etc

Current Repeated Data in File:

Expected Output:

Attempted Query

(seems to be inefficient, especially when having 10 attribute columns)

select
    product.Productid
    ,product.ProductTitle
    ,product.ProductCategory
    ,min(product.LoadDate) as BeginDate
    ,case when max(product.LoadDate)  = (select max(subproduct.LoadDate) from dbo.Product subproduct where subproduct.productid = product.productid) then '12/31/9999' else max(product.loadDate) end as EndDate
from dbo.Product product
group by Productid, ProductTitle, ProductCategory


texts: In what way are Access tables linked with SQL server?

texts:
I have a mdb file with lots of OBDC linked tables that are linked to the tables in SQL server elsewhere.
I can not see any information on how they are "linked". It is a little scary not knowing whether an accidental key press will effect the live database.

If the SQL server table is modified will it be automatically reflected in the table and when?
If I edit the linked Access table will it be reflected in the SQL server table?
(Trusted_Connection=YES any significance?)


texts: Doing a left join and having every match include an extra null row

texts:
So I want to do a left join and have every row on the left table include a row of nulls on the right, even when there is a match.

Is there a name for this type of join?

And is there a better way to do it than how I do it below (namely something avoiding a union)?

SELECT
    T1.C1,
    ...
    T1.CN
    T2.C1,
    ...
    T2.CM
FROM
    Table1 T1
    INNER JOIN Table2 T2
        ON T1.Key1 = T2.Key1

UNION

SELECT
    T1.C1,
    ...
    T1.CN,
    NULL, --NULL 1
    ...
    NULL, --NULL M
FROM
    Table1 T1

(I could also start with a left join and then union a select like the second select above filtered to only the tables that have a matching ID, but that is still complex way to do it).

I was wondering if there is something like

SELECT
    T1.C1,
    ...
    T1.CN
    T2.C1,
    ...
    T2.CM
FROM
    Table1 T1
    INNER JOIN Table2 T2 INCLUDE_NULL_ROW_WITH_MATCH
        ON T1.Key1 = T2.Key1

This is specifically for SQL Server 2008 if that is relevant.


texts: mysqlcheck to optimize innodb tables -- performance/scheduling

texts:
I'm attempting to optimize tables on a very large, production database server.  I'm looking for statistics or benchmarks on how long it will take for certain sized databases/tables.

Is there a multiplier of some sort using database/table size to figure out how long it will take?

My total database is about 90GB.

Running SHOW VARIABLES LIKE 'innodb_file_per_table'; indicates that innodb_file_per_table is on.


texts: How to prevent null foreign key during database normalization?

texts:
I'm rather new to databases and am having a hard time trying to figure out how to structure my database. This database contains students, classes and all their grades both overall and in categories (such as tests, homework, etc.)

So far I have created the following tables:

Student: id|firstname | lastname (pk = id)

Class: id|name|room num (pk = id)

Categories: id|name|weight (pk = (id, name, weight))

Grades: student_id (fk)| class_id (fk)| category_id (fk) | name | grade_val

My question is if I wanted to save the students overall grade with this schema how would I do it without having a null foreign key and preserve normalization?

Currently to store their grade I would have to have an entry like Grade: 1, 1, null, 'Overall Grade', 95 in the database.

What is the correct way to do this? Add another table, an extra row in category like: Category: 1, 'Overall', 100 or something else?

Edit:

Currently the primary key on the grades table is just the combination of the other columns.
    pk = (student_id, class_id, category_id, name)


texts: "No such interface supported" after upgrading SourceSafe project to 2012 SSMS format.

texts:
We had SQL Server 2008 r2 project.
Then we converted it to SSMS 2012 format.
Now I can't open the converted project.
I am getting an error

C:\Views.ssmssqlproj : error  : Unexpected error encountered. It is recommended that you restart the application as soon as possible.

Error: No such interface supported
File: vsee\internal\inc\vscomptr.inl
Line number: 259

The older project was using SourceSafe 6. I wonder if it is the reason. SQL Server Management Studio 2012 doesn't support SourceSafe 6 anymore.


texts:
Hey there. I am looking for a free to use for commercial website database that should have these design features, in the given order:

Auto-Sharding (getting more free space by adding more server nodes)
high read performance
data larger than available memory (so, persistent of course)
New inserts should be available to read/update in real time.

Features that would be very handy, but not necessary:

In-Place-Updates like in MongoDB because the columns 1 to 7 never change in size. And the column 0 never changes at all.
Handy would be something where I can query slave nodes too. Not just masters. To achieve more concurrent operations (especially reads).

Non-relational, relational, does not matter.

I am going to have some billions of entries that look like this:

{
    0: (64 or 32, not decided yet) byte string,
    1: 64 bit integer,
    2: 64 bit integer,
    3: 64 bit integer,
    4: 64 bit integer,
    5: 64 bit integer,
    6: 64 bit integer,
    7: 64 bit integer
}

The only key/index will be "0" (primary index in SQL, or the key in NoSQL databases).

Queries will look something like these:

SELECT * WHERE 0='string'
UPDATE 3=int, 6=int, 7=int WHERE 0='string'
DELETE WHERE 0='string'
INSERT {0: 'string', 1=int, 2=int, 3=int, 4=int, 5=int, 6=int, 7=int}

No other queries (like range, etc).

For every 1,000 operations there will be:

600 reads (not sequential)
200 updates (not sequential)
150 inserts (bulk if possible)
50 deletes (bulk if possible)

Thank you for your answers!


texts: Restore Verify Only w/ Transaction Log Backups

texts:
Question
When RESTORE VERIFY ONLY on a transaction log backup fails, what do you do?

Background
After FULL and DIFFERENTIAL backups, I run RESTORE VERIFY ONLY on the backup file(s). This rarely ever fails. But when it does, I simply re-run the backup, and re-run RESTORE VERIFY ONLY.  Currently, I don't verify transaction log backups. It's an oversight on my part that I'm looking to correct.  When I get to that point, I have to be prepared for all outcomes.

Possible Scenario
Let's say I have a full backup with trx log backups performed periodically afterwards.  If there are 10 trx log backups, but #7 failed a RESTORE VERIFY ONLY, the best I could do in a DR situation is to restore the FULL backup and six subsequent trx log backups.  (I write this based on my understanding that when RESTORE VERIFY ONLY fails, RESTORE DATABASE...FROM <7th Trx Log Backup> will fail too.)

So, even though the trx log chain is not technically broken, it's "effectively" broken since I can't restore the 7th trx log backup (or any subsequent trx log backup).

If I had taken a DIFFERENTIAL backup immediately after #7 failed, in a DR situation I could:

Restore the FULL backup
Restore the DIFFERENTIAL backup
Restore transaction log backups 8, 9, and 10


texts:
Please note that it's not a duplicate thread because I already set variable optimizer_search_depth and didn't have any impact.

Sometimes, our database/application services crashed due to max connection limit. When we see show processlist output we found lot of sessions in the "statistics" state. Our system immediately get normal if we kill these connections. We are not running any DDL/optimize/stats-update related commands during that period. We are using 5.7.12 on Linux Centos 7.5

How can I find the source of these sessions?


texts: Restoring Database, creating new copy and use existing transaction logs for the original backup database to restore the NEW database to be current?

texts:
I am not a DBA nor a database guy, we just recieved a database backup, a differential database backup and daily database transaction logs (each day probably has about 15-20 trans logs). I now have about 20 days worth of transaction logs.

In case the database crashes, I do not want to re-install the original database, the original differiential backup and XXX days of transaction logs.

My question is:
Can I just restore the base database backup, the differential database backup, the 1st 10 days of transaction logs and then do a backup of the database, and then still use the 11th day of the transaction logs to add to the NEW backup I just created? How would I go about doing this? Could someone provide me some T-SQL code on how this could be done or is there a much easier way to work around this?

Well actually I already have some of the steps done, but confused on what would be the best solution since my new database does not seem to work with the trans logs, below is my code:
Restores base backup

  RESTORE DATABASE db_wasumu FROM DISK=
  'C:\Summit_FTP\Full_Backup\ECW33DBCUSTR12A_db_wasumu_FULL_20160522_060256.bak'     WITH MOVE 'db_wasumu' TO 'C:\Program Files\Microsoft SQL
  Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\db_wasumu.mdf',     MOVE
  'db_wasumu_Log' TO 'C:\Program Files\Microsoft SQL
  Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\db_wasumu_log.ldf',
    REPLACE, NORECOVERY, STATS = 10;

Restores diff backup

  RESTORE DATABASE db_wasumu FROM DISK=
  'C:\Summit_FTP\Full_Backup\ECW33DBCUSTR12A_db_wasumu_DIFF_20160527_004940.bak'
    WITH MOVE 'db_wasumu' TO 'C:\Program Files\Microsoft SQL
  Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\db_wasumu.mdf',     MOVE
  'db_wasumu_Log' TO 'C:\Program Files\Microsoft SQL
  Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\db_wasumu_log.ldf',
    REPLACE, NORECOVERY, STATS = 10;

Restores transaction log

  RESTORE LOG db_wasumu FROM DISK =
  'C:\Summit_FTP\wasumu_20160527\ECW33DBCUSTR12A_db_wasumu_LOG_20160527_010404.trn'

    WITH NORECOVERY;

Create new  backup

  Now I do the backup: BACKUP DATABASE db_wasumu TO DISK = 'C:\Program
  Files\Microsoft SQL
  Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\db_wasumu-Full Database
  Backup_after0529_2.bak'

But when I tried to append the next Transaction log it states:

  Msg 4330, Level 16, State 1, Line 1 This backup set cannot be applied
  because it is on a recovery path that is inconsistent with the
  database. The recovery path is the sequence of data and log backups
  that have brought the database to a particular recovery point. Find a
  compatible backup to restore, or restore the rest of the database to
  match a recovery point within this backup set, which will restore the
  database to a different point in time.  For more information about
  recovery paths, see SQL Server Books Online. Msg 3013, Level 16, State
  1, Line 1

RESTORE LOG is terminating abnormally.

Thanks in advance for your help!!

Sincerely,
Paul


texts: Getting database sizes on an MSSQL Server named instance

texts:
I need to get a list of database sizes for a SQL server migration project.  The SQL Server server we're migrating from has three installed instances, one default and and two named.  It's running SQL Server 2008 R2, on Server 2008 R2, if it matters.

To get this list, I dusted off the old T-SQL "script" I used at my last position as the defacto/accidental DBA, and it works as expected on the default instance, but returns no data on either of the named instances.

SELECT [Database Name] = DB_NAME(database_id),

   [Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'

                 WHEN Type_Desc = 'LOG'  THEN 'Log File(s)'

                 ELSE Type_Desc END,

   [Size in MB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )

FROM   sys.master_files

GROUP BY      GROUPING SETS

          (

                 (DB_NAME(database_id), Type_Desc),

                 (DB_NAME(database_id))

          )

ORDER BY      DB_NAME(database_id), Type_Desc DESC

GO

Can someone tell me why (and how to fix it), or suggest a better solution?

(It's gotta be the FROM sys.master_files bit, because what else could it be, but I'm not sure what I should be using instead on a named instance.)

EDIT:  Below is what I'm seeing on my named instances, anyway... so if this code does/should work on named instances, thoughts on why it doesn't work on these would be much appreciated.


texts:
I was wondering if there was a way I can execute a SQL Loader script from SQL Plus.

We are using Oracle 10g.


texts: What are the challenges of administrating an International stock trading database?

texts:
I am a student, and have a question for an assignment:

What problems and challenges do database administrators face when designing systems to trade international stocks in real time and 24 hours a day, 6 days a week? Are cloud based database systems as a service a viable option?


texts: Default value stays after I dropped the default constraint

texts:
So, here is a test for fun on SQL SERVER 2012. I have a test table, you could choose any table you have in your local.

Then I add a NOT NULL column with default constraint:

alter table test add new_col8 nvarchar(25) not null constraint df_new_col8 default('aaa');

It won't be surprised to see the value get populated but the physical size is still 0. You could refer to the scripts in Why does ALTER COLUMN to NOT NULL cause massive log file growth?.

I used following script and you need to change it a bit when you tested it.

DBCC IND ('master', 'test', 0);

DBCC TRACEON (3604);
GO
DBCC PAGE ('master', 1, 692, 3);
GO

Now, I decided to drop the default constraint. I checked the table again, but the value stays but the physical size is still 0!

alter table test drop constraint df_new_col8;

I checked the sys.default_constraints table and sys.columns table, there is no clue for this default value. How does SQL SERVER still get the default value?!


texts: Max connections SQL Server Express could have?

texts:
I have SQL Server 2005 Express edition installed on one of my client's machine, and lots of other users are also connected to that SQL Server through the C# application that I have provided them as an interface.

Sometimes a pc get connected to SQL Server and sometimes just throw the exception

  SQL Server not found....

I believe the Express edition reaches the maximum connection limit of it and then gives error.

Please guide me how could I solve this issue.

Any white paper that help me know which edition have how many number of parallel connections,

I am open to upgrade to SQL Server 2008 or SQL Server 2012, but prefer Express edition. I could go for other versions as well if they really pay me back in this context, otherwise Express edition is doing well.


texts:
I inherited an old old 9i DB that now needs recovery.  It is unstable (disk corruption and performance problems).

I have a backup.

In the interest of time - I have 64-bit VMs all over the place, but 9i was only made for 32-bit according to Oracle support.

Would installing 32 bit packages on the existing 64-bit VM be an option to run Oracle 9.2?


texts: How to design to avoid two-way reference between two tables

texts:
I am having a problem with foreign keys between two tables. I am trying to model a relationship between members of a club and their relatives. This is a straightforward many-to-one relationship, with many relatives related to one member. However, one and just one of this relatives must be the main contact. Also, many members can have the same relative as main contact (siblings to their father as an example).

EDIT: Members are kids, and relatives must be adults, so relatives can't be members, and members can't be main contact of another member.

A FK on member table refering the PK of relatives would work, but this will leave me with a circular reference and a "chicken-or-egg" problem.

What is the right approach to this kind of problems? Maybe I am just thinking it too much.

Schema is more or less like this:

+--------------+    +-------------+
| member       |    | relatives   |
+--------------+    +-------------+
|id            |<-+ |id           |<-+
|name          |  +-|*member_id   |  |
|address       |    |name         |  |
|phone         |    |relationship |  |
|...           |    |phone        |  |
|*main_contact |-+  |...          |  |
+--------------+ |  +-------------+  |
                 +-------------------+


texts: How check that the Oracle Database is up?

texts:
For our application we have a configuration file for connecting to the Oracle database (URL, username, password).

The application takes 2-5 minutes to start.  If there is something wrong with database I see this in logs, but I have to wait 2-5 minutes. This is a lot of time.

So I want to find out quickly and simply a way to determine that the database is all OK and online.

Do you have any idea how I might do it ?


texts:
We're looking to upgrade our stack from MySQL5.7 to MySQL8. We are using a standard LAMP system on ubuntu16, php7.0 and symfony 2.3 .
On our tests, I have noticed that there is a lot of trailing queries left open on both mysql workbench processlist and also on the aws connection count. In the connections opened by the pull it looks like queries are not cleared however the state of the connection is 'None', the command is 'sleep' but the info still shows a query. Are we missing any parameters, or is it the case that we need to update the rest of the stack as well?

I attach an image to show the state and command in the MySQL workbench processlist. These queries do not come from the app, but the app will show select queries.


texts: Recover from Postgres FATAL: could not open file pg_tblspc/

texts:
I have a pretty big PostgreSQL 9.1.3 running on Ubuntu 10.04. The data is spread over multiple tablespaces = physical drives.

One of these drives is gone, so the directory of that tablespace doesn't exist any more. e.g.: I have lost the directory where the symlink in "pg_tblspc/176967555" linked to.

Well. Status: After a restart, that DBMS comes up with no error. Accessing that particular database is not possible though

  psql: FATAL:  could not open file pg_tblspc/176967555/

I tried to simply create these folders as empty, but then the PG wants a PG_VERSION and pg_filenode.map file in that directory, which I can not simply create.

90% of the data in the affected database is stored in other, sane tablespaces. But I can't access any table in the database, because some of the tables were stored on the now-vanished tablespace.

My goal is to read data from the unaffected tablespaces. It would be fine if postgres would just drop anything that lied on that tablespace.

I recovered most files from the lost tablespace directoy (e.g. pg_tblspc/176967555/). When I put that recovered folder back in place, PG is still compaining about a missing file when accessing that DB - one file which I could not recover.

Could starting the DBMS with zero_damaged_pages=true help to just ignore the missing files? If zero_damaged_pages intended to be used for the ''missing file'' szenario?
edit: No luck -  it will still complain about the missing file:

set zero_damaged_pages = true;
SET
postgres=# \connect problemdb ;
FATAL:  could not open file "pg_tblspc/176967555/PG_9.1_201105231/123304298/135285149": No such file or directory

What are my options?

Should continue I to try to recover the database with the broken tablespace? This discussion seems to provide some tips on how to restore when single files are missing. Can I somehow create these files with dd ?

Should I try to get the needed tables from the binary files with pg_filedump ?

Someone disussed the option to import a tablespace to a new database on the postgres mailinglist in 2009, but there seems to be no way.

Isn't this a pretty normal crash scenario: some file has vanished - and you still wan't to access the tables stored in the other files?

Many thanks in advance for any help.
Steve


texts: Excel spreadsheet into a MySQL database tables

texts:
I am relatively new at database design and would like to get this right from the start. I have had differing advice on how to do this and believe that the common suggestion of importing all the data is just wrong as the data won't be normalised and will have a lot of redundant repeated data. I come from an MS Access background rather than MySQL.

This question is about the design of the database tables not on the PHP processing to get it into the tables.

I need to add a lot of custom data to a Wordpress website. This data is currently in multiple Excel spreadsheets and has a lot of duplicated data in both rows and columns and appears to me to be ideal for splitting into 4 tables. The main table will contain initially around a million rows and will grow regularly. I intend to have a front end upload facility for certain users to upload their files and would like to automate this.

Each spreadsheet contains 10,000 to 15,000 rows. A lot of the rows have duplicated data with just two fields transposed with another two fields so these rows can be deleted in the database.

I have attached an image of part of one of the spreadsheets.

My current thinking is to have one table for Column E as every row contains exactly the same data. A second table for Column N as there is lots of repeated data  here. A third table for column's A B F G H with linked data to the first two tables and a fourth table for columns C D I J K L M with linked data in the other 3 tables. As you can see rows 2 and 3 are the same except for transposed data between columns I and J with columns C and D but this is not always the case See rows 8 and 9 for instance).

Is this the best way to break up the data or should I just be looking at one large table. The data will be queried in different ways (4 result sets). It is unlikely that once the data is entered it will need to be updated.

I would welcome any thoughts or advice before I actually start building this.


texts: ALTER DATABASE ARITHABORT configuration minimum privilege needed

texts:
What is the minimum privilege needed to execute ALTER DATABASE {db_name} SET ARITHABORT ON?

I found that the privilege ALTER ANY DATABASE would suffice this, but I wonder if there isn't any other "smaller" privilege that does the same thing, because I think this permission is too broad and maybe I'll not have it in production environment.


texts: What is an Effective Way to Count the Number of Pages in Oracle?

texts:
We've implemented paging in our PL/SQL procedures by building a dynamic SQL query and then selection only a portion of results.

We implement paging by putting the original query inside a SELECT that fetches only certain rownums from given query, like this:

SELECT * FROM (
      SELECT ROWNUM rnum, d.* FROM (
      ' ||
      pSql ||
      '
        [[filter]]
        [[sort]]
      ) d
      WHERE rownum < [[end]])
      WHERE rnum> [[start]]

The only way to implement page counting I could think of was to do a count of all records in pSql. This, however, turned out to be very resource heavy and would needed to be done quite often, as we ofer filtering of data, which in return creates a whole new query in pSql.

Is there a more efficient way of doing a record count?


texts: What are the downsides of running DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') hourly?

texts:
I have been seeing some concerning behavior in the SQL Server memory caches on one of our production servers. We are on SQL Server 2014 SP2 Enterprise Edition, 16 cores, 400GB RAM (300 GB dedicated to SQL Server).

Using this query (credits to Glenn Berry):

SELECT TOP(10) mc.[type] AS [Memory Clerk Type],
       CAST((SUM(mc.pages_kb)/1024.0) AS DECIMAL (15,2)) AS [Memory Usage (MB)]
FROM sys.dm_os_memory_clerks AS mc WITH (NOLOCK)
GROUP BY mc.[type]
ORDER BY SUM(mc.pages_kb) DESC OPTION (RECOMPILE);

I have discovered an issue. The USERSTORE_TOKENPERM values grow to a point where they seem to overwhelm the cache, as a result, almost all plans are compiled and none are cached. That is CACHESTORE_SQLCP and CACHESTORE_OBJCP will sit at around 200 MB each, while the USERSTORE_TOKENPERM cache is upwards of 11 GB. With over 168,000 tokens stored, for about 100 concurrent/600 total users with 3000-5000 connections. These numbers seem way off.

Unfortunately, the vendor that provides this application doesn't have crazy things like "System Requirements" documentation, and they won't commit to supporting the application on untested Service Packs/CU's. So my hands are tied to SQL Server 2014 SP2, as other threads I've read suggest upgrading to SQL Server 2014 SP2 CU7 or higher. That is the long-term goal. So I needed another solution for the short term.

After running the command suggested in this thread from 2009.

Specifically:

DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')

I see our CPU usage dropping from 30%-80% utilization, to under 30%. User complaints of "slowness" in the application have trailed off. Plan caches are now growing to 10 GB or more.

As the day goes on, the token cache grows again. Near the end of the day we start to see a the problematic caching behavior again. I have scheduled the DBCC command to run nightly, outside of business hours.

I understand that I can apply trace flag 4610 and 4618 here to limit the number of stored tokens.

  Increases the size of the hash table that stores the cache entries by
  a factor of 8. When used together with trace flag 4618 increases the
  number of entries in the TokenAndPermUserStore cache store to 8,192.

By the end of the day, we end up with about 40,000 tokens cached.

Does anyone have any experience (or knowledge) with this issue that can say whether using trace flags, or clearing the cache, is a more effective solution?

What are the downsides of clearing the TokenAndPermUserStore cache more frequently than once per day? Say, hourly? Is there a "too often"?

And what would be the downsides of limiting the token cache through trace flags? Will I have end users hit bottlenecks with token limits, or otherwise (thus causing another issue)?

My major concern here is that, this is medical data, and I do not want to lose patient data as a result of a problem associated with a cache flush.


texts: SQL Server Management Studio 2012, authentic multi-user login

texts:
Issue:

I set up SQL Server and SQL Server 2012 Management Studio on the Administration login.

When I login on my login I can open SQL Server 2012 Management Studio, however I cannot access the database. I can see the database name but I am not able to open it.

Error message:

  The database xyz is not accessible. (ObjectExplorer)*


texts: PostgreSQL Database Service does not start after upgrade

texts:
I need to fix a PostgreSQL Services. Seems that there have been a change to the service. Running pg_ctl start -l logfile works. But the Service fails.

postgres@HOSTNAME ~ > cat /etc/redhat-release
    Red Hat Enterprise Linux Server release 7.3 (Maipo)

postgres@HOSTNAME ~ > systemctl status postgresql.service
    ● postgresql.service - PostgreSQL database server
       Loaded: loaded (/etc/systemd/system/postgresql.service; enabled; vendor preset: disabled)
       Active: failed (Result: exit-code) since Mi 2018-08-29 14:18:36 CEST; 1h 39min ago
      Process: 14731 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=1/FAILURE)

postgres@HOSTNAME ~ > echo $PGDATA
        /var/lib/pgsql/data

$PGDATA seem to be correct. /etc/systemd/system/postgresql.service Looks also fine.

postgres@HOSTNAME data > ls
base          pg_hba.conf_bak  pg_serial     PG_VERSION
global        pg_ident.conf    pg_snapshots  pg_xlog
logfile       pg_log           pg_stat       postgresql.auto.conf
pg_clog       pg_logical       pg_stat_tmp   postgresql.conf
pg_commit_ts  pg_multixact     pg_subtrans   postgresql.conf_bak
pg_dynshmem   pg_notify        pg_tblspc     postmaster.opts
pg_hba.conf   pg_replslot      pg_twophase   postmaster.pid

I did not do the Upgrade. Maybe it was upgraded with zypper or a different DBA did it by installing it prallel and importing the dump.

I checked : Process: 14731 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=1/FAILURE).

# PGVERSION is the full package version, e.g., 9.1.2
# Note: the specfile inserts the correct value during package build
PGVERSION=9.2.24
# PGMAJORVERSION is major version, e.g., 9.1 (this should match PG_VERSION)
PGMAJORVERSION=`echo "$PGVERSION" | sed 's/^\([0-9]*\.[0-9]*\).*$/\1/'`
# PREVMAJORVERSION is the previous major version, e.g., 8.4, for upgrades
# Note: the specfile inserts the correct value during package build
PREVMAJORVERSION=8.4
# PGDOCDIR is the directory containing the package's documentation
# Note: the specfile inserts the correct value during package build
PGDOCDIR=/usr/share/doc/postgresql-9.2.24

The versions don't match.


texts: Do any SQL dialects permit the logical sequence of SELECT clauses?

texts:
SELECT in ISO/IEC-standard SQL prescribes the following syntactical order for the sub-clauses:

SELECT
    projection-expressions
FROM
    sources
WHERE
    predicate-expression
GROUP BY
    key-expression
HAVING
    predicate-expression
ORDER BY
    ordering-expressions

While the logical execution order is this:

FROM
    sources
WHERE
    predicate-expression
GROUP BY
    value-expression
HAVING
    value-expression
SELECT
    projection-expressions
ORDER BY
    ordering-expressions

To novice users of SQL it becomes surprising that a projection defined in the SELECT clause is not available in the WHERE or GROUP BY clauses even though it's declared first - given that computer programs generally follow the top-down execution order.

It's also surprising that SQL authors are required to repeat their expressions  in SELECT, WHERE, and GROUP BY clauses redundantly or use a subquery which does not lend itself to a succinct query. At least when the user is familiar with the actual clause execution order they know why they need to repeat themselves, but that doesn't stop it being frustrating.

This problem, and other related issues, is documented in this article I found: https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-true-order-of-sql-operations/ and it's no surprise that a QA on StackOverflow has almost 30,000 views: https://stackoverflow.com/questions/3241352/using-an-alias-column-in-the-where-clause-in-postgresql

It made me wonder if any implementations of SQL allow for this more "logical" ordering of clauses. I note that Linq in .NET does actually follow this order, though I wouldn't describe it as being a true SQL implementation, but indeed, in Linq the equivalent would be:

source // FROM equivalent
    .Where( predicate-expression )
    .GroupBy( key-expression )
    .Where( predicate-expression ) // HAVING equivalent
    .Select( projection-expression )
    .OrderBy( ordering-expression )

(I also like how Linq lets you add a Select() projection anywhere in the command sequence so you can use your evaluated expressions without needing to invoke expressions again).

So, do any SQL implementation let you express queries in a more logical structure?


texts: Avoid changing table reference in view after renaming the main table

texts:
My table name 'Transaction'.

I renamed the table name as 'Transaction_backup' and I have created a new Table 'Transaction'.

When I renamed the old table, the table reference in view also changed into 'Transaction_backup'.

Here, I don't want to change the view reference. I found some answers from Postgresql Rename Table without Updating View Definitions.

It's not possible to 'Create or Replace' all the views.

Is there any other option to rename table without changing view reference?


texts: Occasional transaction replication error

texts:
I have a transaction replication setup between two servers. Database size is around 400GB on publisher. There are six publications being published to subscriber. Occasionally, I see one of the publications (not always the same) errors out due to blocking at subscriber. When I searched for blocking, I found out following statement usually blocks update processes running on subscriber. It blocks processes related to publication and causes error since log reader is unable to proceed. Only workaround I have found so far is to kill spid which runs following process. This process is run by Replication Distribution Agent on subscriber. However, distributor resides on publisher.

DECLARE @spid INT
DECLARE @locktype NVARCHAR(60)
DECLARE @lockmode NVARCHAR(60)
DECLARE @indexname SYSNAME
DECLARE @tablename SYSNAME
--Following spid is related to DML process running on subscriber related to publication showing error
SELECT @spid = 69
SELECT @locktype = L.resource_type
   ,@lockmode = L.request_mode
   ,@indexname = I.NAME
   ,@tablename = OBJECT_NAME(I.object_id)
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.indexes I ON I.object_id = P.object_id
   AND I.index_id = P.index_id
WHERE L.request_status = 'WAIT'
   AND L.request_session_id = @spid

RAISERROR (
          21870
          ,10
          ,0
          ,@spid
          ,@locktype
          ,@lockmode
          ,@indexname
          ,@tablename
          )

Any clue why this happens and what's the best practice to avoid this in future?
Thank you.


texts: I Need to provide encryption at rest for a 50GB MySQL instance running on Centos 6.5

texts:
I can't use GusterFS because they don't recommend it and Ceph because it's clear they can't guarantee performance.

I can't just encrypt certain database objects because it will take too much engineering

I have lots of disks. I need an effective way of making sure that no-one can get at the data if they do not have access to the platform that uses it.

I'm open to hardware appliances and software solutions. any thoughts?


texts: When installing SQL Server 2014 SSIS, what are the meaning of "authentication level"?

texts:
After installing SSIS 2014, it is the time to deal with the permissions.

When dealing with the DCOM Config (control Panel->Administrative Tools-> Component Services), you want to set up the permissions for Microsoft SQL Server Integration Services 12.0 as you can see on the picture below:

You right click on it, and on the first tab, called General, you see the option Authentication Level. The default is Call, but there are other options, as you can see on the picture.

Where is the documentation for this? What does each option really mean?


texts: Can SQL server deny connections until the AG failover has completed ?

texts:
We have been doing rigorous testing of the Always On environment by doing manual failovers multiple times at the same time doing transaction in the midst of failover and we observed the following and would like to know your opinion on this.

The application that uses the AG databases does inserts/updates frequently (like in seconds interval). During failover… the application is connected to the failed over AG Group but throwing an error saying the database is in read only state and not able to write to the database and it couldn’t do any inserts and updates until the failover completes. The Nodes are set up for synchronous commit mode (automatic failover).

When we are doing manual failover it takes about 10 seconds to fully failover. The issue we have is on this 10 second interval.

I know we can add error handling codes from the application side to handle the error or make the application wait until the failover fully takes place. The problem is to change the application code is not going to be an easy process for us since it is a third party application.

Is there any way we can set/tell SQL server to not allow connections to the server until the failover has completed; hence we do not see the “database is in read only state” error. In other words, can SQL server deny connections until the failover has completed?


texts:
We have a database table growing at a rate of ~20 million records per month, we're expecting this to double shortly.
The existing schema has a uuid4 as a primary key, and then two additional indexes on two other uuid4 columns. These columns are searched frequently for diagnosing issues. We can't change the UUID4 to something else as they are populated by an application we can't change.
We're planning on creating a new table and then back populating the data required from the old table into this one, fortunately, there's no real-time requirement for historic data.
The basic structure of the table is as follows

Column
Type

Started
Date Time ( will always be increasing )

app_id_one
uuid version 4

app_id_two
uuid version 4

app_id_three
uuid version 4

My questions are as follows:

As we don't control the uuid generation process does (started, app_id_one ) make sense as a primary key for the above structure, are there similar performance issues using uuid version 4 as the second column in a composite key?
We regularly have to search on app_id_two/app_id_three but I assume even have a secondary index of type uuid4 is bad for performance, so I am thinking we should also index these (started, app_id_two), (started, app_id_three )?
If we do option 2 this leads to a duplicate of the date time in the secondary index ( due to the chosen primary key ), space isn't too much of an issue we've got lots available on the servers, but would it be better just to generate a more suitable primary key instead?
We're considering populating this new table via a trigger as changing the application that populates the existing table is also not without its challenges. I'm worried about this adding to our already struggling write performance, but if I remove the two indexes from the existing table ( app_id_two / app_id_three ) I assume we'll get more than enough performance back from doing so?

Thank you!


texts:
I would like to know if db_owner can change the recovery model of database.

Using TSQL-   alter database db set recovery simple


texts: Backing up a SQL 2008 DB, then restoring, gives a SQL 2005 (compatibility level 90) DB

texts:
We have four different databases on a SQL Server 2008 (not SP2 - don't ask me why) database server that have an odd problem.

When listed from sys.databases, these databases report that their compatibility is SQL Server 2008/R2 (compatibility level 100) as shown for these three of the DBs here:

MyDB_Name               100 SQL Server 2008/R2
MyDB_Name_MirrorTables  100 SQL Server 2008/R2
MyDB_Name_Reporting     100 SQL Server 2008/R2

As output from this query:

select
    name, compatibility_level , version_name =
    case compatibility_level
        when 90  then 'SQL Server 2005'
        when 100 then 'SQL Server 2008/R2'
        when 110 then 'SQL Server 2012'
        else 'unknown - ' + convert(varchar(10), compatibility_level)
    end
from sys.databases

Restores from backups of other user DBs on the server restore correctly as compatibility level 100. However, restores of these four DBs from backups restore as compatibility level 90 (SQL Server 2005). Only these four DBs appear to have this issue.

To test this, I took a manual backup of one of these DBs with only the options INIT, SKIP specified. I then restored this backup to a SQL Server 2012 server. When restored, the DB went through the upgrade process from Version 655 to 706. Nothing unusual happened.

However, when looking at the compatibility level on SQL Server 2012, using the same code as above, the information showed up as:

bwh_MyDB_Name_MirrorTables  90  SQL Server 2005

Additionally, when restored to the original SQL Server 2008 DB server under a different name, the database still returns SQL Server 2005 (compatibility level 90) for the DB version, even though it was backed up on the same server as compatibility level 100.

Finally, although the DB reports as SQL Server 2008, when returning a date column from a DB2 linked server, the query returns a datetime (with 00:00:00.000 for the time). Running the query in SQL Server 2012, the value is returned as a Date data type, which is what it should be returned as in a SQL Server 2008/R2 database.

In my last tests, I added a test table to the DB containing a date and a time field, and inserted a row of data. Then I backed up and restored the DB again.  The table worked correctly, though the compatibility level was 90. I also created a table with date, time, and datetime2 fields with no problems.

I'm at a loss where to look next. The date/datetime problem was the original trigger for looking at this, but it has become a much larger puzzle. Obviously, I could simply CAST the incoming data as a date value (which does work, oddly enough), but this doesn't explain the RESTORE returning a DB to compatibility level 90, or the DB2 query returning a datetime instead of a date from the linked server. Any suggestions would be gladly accepted.


texts: SQL Server - Login failed. The login is from an untrusted domain and cannot be used with Windows authentication

texts:
I am getting below error while trying to connect SQL from remote server. Local login is working fine. Can anyone help on this. Thanks in advance.
"Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 10.186.12.10]"
My environment :
Client - already test AD/DNS domain joined
Remote SQL Server - already prod AD/DNS domain joined
I have running inside test AD/DNS server isolated environment. No relationship between test AD/DNS server and prod AD/DNS server. Test domain name : contoso.com Otherwise , PROD domain name : contoso.com
Test AD/DNS Server : 10.190.10.1
Prod AD/DNS Server : 10.150.10.1
Do I have to join prod AD/DNS client machine as well ? I mean , is there any workaround solution ?
Thanks in advance,
Last Update:
Solved my issue.


texts: In MySQL, does the order of the columns in a WHERE clause affect query performance,why?

texts:
I have a query that doesn't use any indexes:

SELECT 32,
       guid,
       1,
       1,
       1,
       0,
       5
FROM   test
WHERE  level >= 20
       AND ( ( fun_GetIndexValue(data, 354) >> 16 ) +
             ( fun_GetIndexValue(data, 355) >> 16 ) +
             ( fun_GetIndexValue(data, 356) >> 16 ) +
             ( fun_GetIndexValue(data, 357) >> 16 ) +
             ( fun_GetIndexValue(data, 358) >> 16 ) +
             ( fun_GetIndexValue(data, 359) >> 16 ) ) >= 1;

The level column has only about 80-90 distinct values, the table test has about million rows, and the data column is passed to the function, so I think the query can not use any indexes. But I found that if I put the level condition in the end, the query performs slower. Why is that?


texts: SSRS dataset query not ordering by properly

texts:
I have a dataset text query and I would like to have an ORDER BY in it. The issue is in SSRS, the ORDER BY is not working as desired. However, when I run the query in SSMS, the output is ordered as I would like.

This image is in SSMS where it is working properly. The payroll = 000004 is just to isolate a specific payroll to make it easier to read. The ISNULL(OrderByNumber, 1000) is just to put the total row down at the bottom.

Here is a picture of dataset query where the ORDER BY resides

And here is the results I get in SSRS preview. Not ordered how I want at all :( As far as I can tell, SSRS should support ORDER BY in the dataset query. I also tried removing the ISNULL( ,1000), and the order by still didn't function as desired.

Thoughts?


texts:
I have two SQL Servers 2014 Enterprise Edition (SERVER A, and SERVER B) designed for Always On Availability Group. There are 5 databases in the AG.

I have been told that we need to replicate one of the databases (Database A) from the AOAG. I set up a transactional replication for database A, pointing to listeners which shouldn't cause any issue if a failover happened. Tested, worked just fine.

I'm thinking to create two distribution databases, each on a different server.  The reason is, I'm trying to be redundant. If Distribution Server A is gone for any reason, I should be able to replicate database A from Distribution Server B. Is it possible, any alternative to this?

Here is what my design is for Replication on AOAG:

Distribution Server A (contains Distribution database) - SQL Server 2014
Two subscribers - SV1 and SV2  - SQL Server 2008R2 Standard
Publishers  (SERVER A and B - from AOAG) - SQL Server 2014

What I'm trying to do: Add one more distribution server (B).

Distribution Server A (contains Distribution database)
Distribution Server B (contains Distribution database1) - SQL Server 2014
Two subscribers - SV1 and sV2  - SQL Server 2008R2 Standard
Publishers (SERVER A and B - from AOAG)

What I see as a challenge:

Can't configure remote Distribution server for possible publishers. Help/ideas are welcome!


texts: What is the best way to store an email address in PostgreSQL?

texts:
What would be the right datatype to store email addresses in PostgreSQL?

I can use varchar (or even text), but I wonder if there is a more specific data type for emails.


texts:
Can anyone help me with the following query?

SELECT
  voc.username AS Medewerker,
  voc.bestvastonbep_count AS Doorgezet,
  IFNULL(cc.call_count, 0) AS Telefoongesprekken,
  voc.bestvastonbep_count / IFNULL(cc.call_count, 0) AS Ratio
FROM (
  SELECT
    u.id AS user_id,
    CONCAT(u.firstname, ' ', u.lastname) AS username,
    COUNT(vo.id) AS bestvastonbep_count
  FROM `bestvastonbep`       vo
  JOIN `best`                b   ON b.id = vo.best_id
  JOIN `vastonbepbeststatus` vos ON b.id = vos.best_id
  JOIN `user`                u   ON u.id = b.user_id
  WHERE vos.type = 2
  AND   vos.status = 2
  AND  (b.date BETWEEN '2017-02-04' AND '2017-05-04')
  GROUP BY u.id
) voc
LEFT JOIN (
  SELECT
    user_id,
    COUNT(id) AS call_count
  FROM `call` c
  WHERE  (c.start BETWEEN '2017-02-04' AND '2017-05-04')
  AND c.duration > 0
  AND c.calltype = 'out'
  GROUP BY c.user_id
) cc
ON voc.user_id = cc.user_id;

It produces the desired result, but it's immensely slow.
I've read questions from other people about similar queries. For them it turned out that joining subqueries wasn't necessary.
The difference for me is that I'm using GROUP for both subqueries.
Hopefully there's a way and I'm just not seeing it.

Edit in response to RDFozz

The EXPLAIN query gives the following result.

The first subquery is very fast. (0.1 s)
The second subquery takes about 6 seconds.
The complete query takes about 57 seconds.

There is an index on call.user_id as well as call.start.
But the call table currently has about 32 million rows.

Answer
I took Plan A as described by Rick James.
The query now looks like the following:

SELECT
    voc.username AS Medewerker,
    voc.bestvastonbep_count AS Doorgezet,
    IFNULL(count(c.user_id), 0) AS Telefoongesprekken,
    IFNULL(voc.bestvastonbep_count / count(c.user_id), "&infin;") AS Ratio
FROM (
    SELECT u.id AS user_id, CONCAT(u.firstname, ' ', u.lastname) as username, COUNT(vo.id) AS bestvastonbep_count
    FROM `bestvastonbep`       vo
    JOIN `best`                b   ON b.id = vo.best_id
    JOIN `vastonbepbeststatus` vos ON b.id = vos.best_id
    JOIN `user`                u   ON u.id = b.user_id
    WHERE vos.type = 2
    AND   vos.status = 2
    AND  (b.date BETWEEN '2017-02-04' AND '2017-05-04')
    GROUP BY u.id
) voc
LEFT JOIN `call` c on
    (c.start BETWEEN '2017-02-04' AND '2017-05-04')
    AND c.duration > 0
    AND c.calltype = 'out'
    AND voc.user_id = c.user_id
GROUP BY voc.user_id;

It takes about 7 seconds, depending on the size. Faster would be nice, but this is just about acceptable for my purposes.


texts:
I have two tables in MySQL: orders and line items.

When orders are deleted, the line items are orphaned. Thus, I need a query that will delete the line items that were associated with the order before the order was deleted.

Something like:

 DELETE FROM line_items WHERE line_items_id NOT EXIST IN TABLE orders COLUMN orders_id

In other words, of the order id referenced in the line item row does not exist, then delete the line item in question.

(I know... it would have been better if the person who designed this had used foreign keys and cascade... but they didn't).


texts: Variable usage in MySQL Stored Procedure

texts:
I've some issue with a stored procedure on a MySQL 5.6.36:

DELIMITER $$
USE `test_schema`$$

DROP PROCEDURE IF EXISTS `count_requests`$$

CREATE PROCEDURE count_requests()
    BEGIN
    DECLARE tablename_tmp VARCHAR(64);
    DECLARE counter_tmp INT;
    DECLARE counter INT;
    DECLARE v_finished INT DEFAULT 0;
    DECLARE v_cur CURSOR FOR SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema='test_schema' limit 10;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

    OPEN v_cur;
        get_flagged: LOOP
            FETCH v_cur INTO tablename_tmp;
            IF v_finished = 1 THEN
                LEAVE get_flagged;
            END IF;
            SELECT COUNT(*) INTO counter_tmp FROM tablename_tmp WHERE data_richiesta > (UNIX_TIMESTAMP(NOW())-21600);
            SET counter=counter+counter_tmp;
        END LOOP get_flagged;
        CLOSE v_cur;
    END $$
DELIMITER ;

The targets, in order, are:

extract a list of tables from a specific schema;
counting the records matching a WHERE clause;
use the previous count result to increment a variable;

The trouble comes when I try to call it because, for some reason, the SELECT COUNT statement doesn't use "tablename_tmp" as variable, but as string. So:

mysql> call count_requests;
ERROR 1146 (42S02): Table 'test_schema.tablename_tmp' doesn't exist

Now, the stored procedure (logically speaking) should works beacause if I remove the SELECT COUNT to use a simple

SELECT CONCAT ('Tab name: ', tablename_tmp)

I get the expected result:

+-------------------------------------+
| CONCAT('Tab name: ', tablename_tmp) |
+-------------------------------------+
| Tab name: tab_10001                 |
+-------------------------------------+
1 row in set (0.10 sec)

+-------------------------------------+
| CONCAT('Tab name: ', tablename_tmp) |
+-------------------------------------+
| Tab name: tab_10002                 |
+-------------------------------------+
1 row in set (0.10 sec)

+-------------------------------------+
| CONCAT('Tab name: ', tablename_tmp) |
+-------------------------------------+
| Tab name: tab_10003                 |
+-------------------------------------+

I did some experiments, but without serious results and now I don't now how to proceed.

I appreciate any help.

Thanks in advcance.


texts:
I need to stop read-write service on standby so is there a way to lock connection within a specific service or to set a service as read-only?

Thanks in advance.


texts: is it relatively safe to union two tables with UUID PK?

texts:
I want to union two tables whose primary keys are UUID(V4) and I want to preserve them in the result. I work with PostgreSQL

Is it safe to do this?
What are the pitfalls may be encountered?


texts: How to pass scheme name to PgSQL function?

texts:
I'm using PostgreSQL 9.6 (can move to 12 if needed).

I have many schemes with same table_name, so I want to pass scheme_name to functions like this:

CREATE FUNCTION public.test()
RETURNS TABLE(base_name text, slow real)
    LANGUAGE plpgsql
    AS $$
BEGIN
RETURN QUERY
    SELECT * FROM scheme_name.bases, scheme_name.servers
    WHERE scheme_name.bases.id = scheme_name.servers.id;
END
$$;


texts:
Is a pivot the best way for me to display this data?  I want to show id, uuid on the left then show each event1 across the top with a 1 if exists or a 0 if it does not. So look like this

id  uuid    cycle   run walk
1   mn12    1   1   0
2   cr23    0   0   1
3   bb11    1   0   0

This is the syntax I attempted, but it gives me an error of

  Incorrect syntax near the keyword 'For'

What should I alter in order to have my data display as desired?

create table #robschneider
(
  ID int IDENTITY(1,1) PRIMARY KEY
  ,event1 varchar(100)
  ,uuid int
)
create table #bigponyhorses
(
  ID int IDENTITY(1,1) PRIMARY KEY
  ,uuid int
  ,empid varchar(10)
)
Insert Into #robschneider Values ('cycle', '1'), ('run', '2'), ('walk', '3')
Insert Into #bigponyhorses VALUES ('1','mn12'), ('2','mn12'), ('3', 'cr23'), ('1', 'bb11')

;WITH data AS
(
  select *
  FROM
  (
      Select
      [rs].[ID],
      [bph].[uuid]
      [rs].[event1],
      CASE when [bph].[uuid] is not null then '1' else '0' end As [Exists]
      from #robschneider rs
      OUTER APPLY (SELECT *
                   FROM #bigponyhorses
                   WHERE [rs].[uuid] = [uuid]) [bph]
  ) src
  pivot
  (
      [Exists]
      For [planname] IN ('cycle', 'run', 'walk')
  ) piv
)
SELECT
[rs].[ID],
[rs].[event1],
'cycle', 'run', 'walk'
FROM data


texts: Oracle SQL Developer: Set the  value when exporting to CSV

texts:
When exporting to CSV in Oracle SQL Developer, null values are replaced with "".  I want them to be exported as the string null.  Is this configurable anywhere?

Example of what is wished:

"STARTDATE", "ENDDATE"
"2017-03-16 00:00:00", null

instead of:

"STARTDATE", "ENDDATE"
"2017-03-16 00:00:00", ""

Exporting is performed by running a query, right-click on the data shown in the grid and select "Export..."


texts: get all posts with their associative tags without duplicates

texts:
Two tables. Post and Tag. Many to Many bi-directional relationship.

Post Table              Tag Table             Post_Tag Pivot Table
---------------         ------------          -----------------
id   content            id   name             id_post   id_tag
---------------         ------------          -----------------
 1   hey there           1    php                1        1
                         2    python             1        2

I want to get all posts with their tags.

I've tried this:

SELECT P.title, T.tag
From Post P
JOIN Tag T
ORDER BY P.id DESC

It works, but it gives duplicates:

hey there, php
hey there, python

Is there a way to group tags together in one row here. Thanks


texts: How to delete list of email addresses from MySQL database?

texts:
I have a list of invalid email addresses parsed in the following format;

There are roughly 1500 invalid addresses, and I need to remove them from my myBB users.

What SQL query can I run to remove the list from my database?


texts:
I need to unpivot a table in PostgreSQL. The first dataset is what I have, the second is what I want:

Below is my schema with sample dataset:

http://www.sqlfiddle.com/#!17/3f45c/1

My understanding is that you may have to use JSON functions to accomplish this in PostgreSQL, as it doesn't have an unpivot function as some other SQL variants do. I'm open to other solutions, though.


texts: DBCC SHOWCONTIG returns 2 row against certain tables in one environment and 1 row in another environment

texts:
I am seeing that in 2 different SQL 2016 envs one is returning 1 row against DBCC SHOWCONTIG and in other 2 rows against the same table. Why would this be happening? Both the servers are on SQL2016 SP2-CU2 and the table structure too is similar. Any clue on why this would be happening?
Example:
Env 1
ObjectName  ObjectId    IndexName   IndexId Level   Pages   Rows    MinimumRecordSize   MaximumRecordSize AverageRecordSize ForwardedRecords    Extents ExtentSwitches  AverageFreeBytes    AveragePageDensity  ScanDensity BestCount   ActualCount LogicalFragmentation    ExtentFragmentation

Table_x 1116450251  col_st_idx  1   0   0   0   0   0   0   0   0   0   0   0   100 0   0   0   0
Table_x 1116450251  col_st_idx  1   0   515 24376   41  328 148.876 0   68  70  88.058  98.9120583148011    91.5492957746479    65  71  1.16504854368932    10.2941176470588

Env 2
ObjectName  ObjectId    IndexName   IndexId Level   Pages   Rows    MinimumRecordSize   MaximumRecordSize   AverageRecordSize   ForwardedRecords    Extents ExtentSwitches  AverageFreeBytes    AveragePageDensity  ScanDensity BestCount   ActualCount LogicalFragmentation    ExtentFragmentation

Table_x 1075131221  col_st_idx  1   0   0   0   0   0   0   0   0   0   0   0   100 0   0   0   0


texts: PostgreSQL 9.4.6 under RHEL 7 - Extensive logfile because of log_statement_stats - Can't turn off

texts:
I got a newly installed database (PG 9.4.6 / RHEL 7) which logfile grows rapidly.

Most entries are like

2016-03-03 10:33:29.259 CET [56868] user@db STATEMENT:  SELECT field1 AS a1, field2 AS a2, field3 AS a3, field4 AS a4, field5 AS a5, field6 AS a6, MSISDN AS a7, field8 AS a8, RETRY_COUNT AS a9 FROM table WHERE ((field3 <= $1) AND (field8 IS NULL)) ORDER BY field3 ASC LIMIT $2 OFFSET $3
2016-03-03 10:33:29.259 CET [56868] user@db LOG:  EXECUTE MESSAGE STATISTICS
2016-03-03 10:33:29.259 CET [56868] user@db DETAIL:  ! system usage stats:
        !       0.000006 elapsed 0.000000 user 0.000000 system sec
        !       [0.526408 user 0.112125 sys total]
        !       0/0 [0/0] filesystem blocks in/out
        !       0/0 [0/1281] page faults/reclaims, 0 [0] swaps
        !       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
        !       0/0 [3516/0] voluntary/involuntary context switches

I read through the documentation and found out, that it should be one of the log_*_stats parameters and despite the missing config entry in the postgresql.conf and postgresql.auto.conf

> egrep "log_.*stats" postgresql.*conf
postgresql.conf:#log_parser_stats = off
postgresql.conf:#log_planner_stats = off
postgresql.conf:#log_executor_stats = off
postgresql.conf:#log_statement_stats = off

the running instance shows that the parameter is set to "on".

> echo "show log_statement_stats" | psql
 log_statement_stats
---------------------
 on

I tried to change it by applying an "ALTER SYSTEM", but it doesn't help. The parameter stays "on".

I also reloaded the database (pg_ctl reload) and restarted it (its a pacemaker resource, so pcs resource restart postgre_db) - But nothing happens.

How do I turn off this setting?


texts: MySQL 5.1: How to rollback partitioned table

texts:
I have partitioned a table on the basis of one flag column have value either 0 or 1.
Now, I want to roll back these tables.
How can I do this?


texts: SQL to get performance metrics shown in EM Express 12C Resources

texts:
I would like to start recording some historical performance metrics on my Oracle database server.  I can use these metrics to evaluate growth of database resource requirements over time.  When it's time to upgrade or replace the server, I can use this data to choose the right sized server.
The important metrics for me to collect are CPU percentage used, memory usage broken out by process, and although it isn't shown in EM, disk latency metrics would also help.  Below is a screenshot:

We don't have any of the additional performance packs licensed, but I have to figure if EM can query it every minute then I should be able to get it too.  Can someone give an example of a query or queries that can provide this data?


texts: SQL Server Query Store - What is considered an 'ad-hoc' query?

texts:
I've been doing a deep dive into the SQL Server Query Store and I often see references to 'ad-hoc' queries. However, I haven't seen what the Query Store determines an ad-hoc query to be. I've seen places where it could be inferred to be a query without parameters or a query executed just one time. Does a formal definition exist for this? I don't mean in general. I mean as it relates to the Query Store.

As an example, this page shows an example of removing ad-hoc queries from the query store, but it appears the criteria it's using is an execution count of just one. This seems to be an odd definition of an ad-hoc query. BTW, if you go to the page, search for 'Delete ad-hoc queries'.


texts: postgres boolean function and check constraint

texts:
I been using postgres for a while.  Mostly simple stuff.

Now trying to implement a small perl function to check for a single valid email address.

My employees table should have either a single valid email address, or none (ie empty, zilch).
I assume none means undef(email)

My sample code below.

I'm trying to return FALSE only if the given variable is NOT an email address.

The function appears to correctly check a given email address, BUT wrongly returns FALSE if email is blank or empty.

Grateful for any help to show me how to do this the-right-way.

CREATE OR REPLACE FUNCTION email_valid(email text) RETURNS boolean AS
$BODY$
use Email::Address;
my $answer = 1;
return $answer if (undef ($_[0]));
my @address = Email::Address->parse($_[0]);
$answer = scalar(@address);
return $answer;
$BODY$
LANGUAGE plperlu;

ALTER TABLE employees
ADD CONSTRAINT check_email CHECK (email_valid(email) = TRUE) ;

Edit.  Here's the revised procedure commented below, in easier to read format.  :-)

CREATE OR REPLACE FUNCTION public.email_valid(email text) RETURNS boolean AS
$BODY$
    use Email::Address;
    my $input = shift;
    return('TRUE') if ($input eq '');
    my @address = Email::Address->parse($input);
    return (@address == 1 ? 'TRUE' : 'FALSE');
$BODY$
LANGUAGE plperlu

This is a different question to the similar previous question at What is the best way to store an email address in PostgreSQL?

I need to also allow null or blank email address, whereas other question and answer will require valid e-address.


texts: Why does the query plan still sort a table despite having an index sorted on the columns?

texts:
I'm using Postgres 9.1
I have two tables I am joining:

wikidb=> \d page
                         Table "public.page"
        Column         |     Type      |          Modifiers
-----------------------+---------------+------------------------------
 page_id               | bigint        | not null
 page_namespace        | integer       | not null default 0
 page_title            | text          | not null default ''::text
 [...]
Indexes:
    [...]
    "page_page_namespace_page_title_idx" UNIQUE, btree (page_namespace, page_title)

wikidb=> \d pagelinks
                 Table "public.pagelinks"
      Column       |  Type   |         Modifiers
-------------------+---------+----------------------------
 pl_from           | bigint  | not null default 0::bigint
 pl_namespace      | integer | not null default 0
 pl_title          | text    | not null default ''::text
 [...]
Indexes:
    [...]
    "pagelinks_pl_namespace_pl_title_pl_from_idx" btree (pl_namespace, pl_title, pl_from)

Notice how both have indices on the (namespace, title) columns.  I am interested in finding out how many (pl_namespace,pl_title) pairs in the pagelinks table do not show up in the page table as (page_namespace, page_title).

If I use a join, then I get the following plan:

wikidb=> explain SELECT COUNT(*)
FROM pagelinks
LEFT OUTER JOIN page
  ON page.page_namespace = pagelinks.pl_namespace AND
     page.page_title = pagelinks.pl_title
WHERE page.page_namespace IS NULL AND page.page_title IS NULL;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1310748.56..1310748.57 rows=1 width=0)
   ->  Merge Anti Join  (cost=1189384.02..1310748.56 rows=1 width=0)
         Merge Cond: ((pagelinks.pl_title = page.page_title) AND (pagelinks.pl_namespace = page.page_namespace))
         ->  Sort  (cost=1144343.89..1164498.31 rows=8061768 width=19)
               Sort Key: pagelinks.pl_title, pagelinks.pl_namespace
               ->  Seq Scan on pagelinks  (cost=0.00..219551.68 rows=8061768 width=19)
         ->  Sort  (cost=45038.32..45975.52 rows=374880 width=20)
               Sort Key: page.page_title, page.page_namespace
               ->  Seq Scan on page  (cost=0.00..10331.80 rows=374880 width=20)
(9 rows)

Which, as you can see, sorts each of both tables and merges them. I don't understand why it does this if the indices already contain both columns in sorted order.

Any explanations?


texts: SQL Server Auditing is not available in Standard editions?

texts:
If the Auditing option is not available in SQL Server 2008 R2 Standard edition, then why I still see it under the Security object in SSMS?

After reading online, it seems that my option is to use the Trace Profiler that it seems will be deprecated by the time I master it! Can I use extended events in my SQL version?

I only want to capture events like what stored procedures are used more frequently, who are the login names and staff that runs them through a front end application which is built in Visual Basics... Thanks for any advice. Thanks.


texts: Search entire database(210 tables) for a unique Id (PK) and get the table name

texts:
Rewritten Question:

I need to look through an entire database, 210 tables for a unique ID that is a number.

I have limited access to my database through things called nodes (Visual programming.)

These nodes allow for simple querys like SELECT x FROM x WHERE x I believe I can use most commands here.

I'm unfamilar with sql and thought I might be able to get some help here.

Below you can see an example of a node where I can type in commands.

and below that my database structure.

What i'm looking to do is look through the entire database for a unique number, this number changes dynamically but can always be found in the "Id" column of every table. This column is also a Primary Key.

By searching through the database and finding this dynamic unique number in one of the 210 tables I then need to be able to get the table name that the data is located in and the row information ie; all the different column entries for that row.

Example:

more info:


texts: How to structure an append-only database in PostgreSQL?

texts:
Preface

We have been stuck for years using a monolithic program with terrible and inefficient database structure. Now we are finally moving away from it with our own program.

The only area we are having difficulties with is the database. Our data is financial and our existing program causes loss of data mostly through overwrites (UPDATE, DELETE). We have decided to move toward an append-only structure to keep all of the data we accumulate. We may look into using a warehouse if it grows past our initial estimates, but in the meantime, we expect the database to grow to a few millions rows over a few years for some of the larger tables; not massive in scale at this point.

We are using PostgreSQL and will be denying modification privileges such as UPDATE and DELETE for users to ensure historical consistency of data.

Our Ideas

We thought we might be able to contain all current and historic data into one table such that we query the last record (or newest timestamp) for each id as the following table suggests.

To clarify, we have dozens of tables; Below represents a simple version of one such tables.

╔═════╦════╦════════╦════════╦═════════════════════╗
║ key ║ id ║ field1 ║ field2 ║ timestamp           ║
╠═════╬════╬════════╬════════╬═════════════════════╣
║ 0   ║ 0  ║ a      ║ b      ║ 1970-01-01 00:00:01 ║
╠═════╬════╬════════╬════════╬═════════════════════╣
║ 1   ║ 1  ║ x      ║ y      ║ 1970-01-01 00:00:02 ║
╠═════╬════╬════════╬════════╬═════════════════════╣
║ 2   ║ 0  ║ a      ║ c      ║ 1970-01-01 00:05:01 ║ # new version of id=0 row
╚═════╩════╩════════╩════════╩═════════════════════╝

We have also considered NoSQL solutions, except we prefer to remain ACID compliant (and most NoSQL solutions cannot meet that expectation).

We have also looked into other products such as AWS QLDB, but we're limited by budget, so we've decided to go with PostgreSQL for the time being and will re-evaluate when necessary.

Questions

How can we best structure the database to ensure optimal performance, but also keep in mind developer usage and ease of use?
Does our existing idea for structuring the database allow for optimal performance and is it coherent?
What limitations and issues (apart from disk space) might we be creating for ourselves with this plan?


texts: Getting (InvalidInputException) when calling the UpdateRelationalDatabaseParameters operation through the awscli

texts:
I am trying to increase the value of max_connections of my MySQL DB on my Lightsail account. To do so, I am connecting through ssh using the awscli.
Unfortunately, I receive an error when I type the following command:
aws lightsail update-relational-database-parameters --relational-database-name DATABASENAME --parameters "parameterName=max_connections,parameterValue=500"
(Of course, DATABASENAME is replaced by the actual name of my db)
The error only says:
An error occurred (InvalidInputException) when calling the UpdateRelationalDatabaseParameters operation: (and nothing after the colon)
Is there something wrong with the syntax? I am following the one from the doc


texts: How often to backup the master database?

texts:
The suggestion in BOL is fairly vague:

  Back up master as often as necessary to protect the data sufficiently
  for your business needs. We recommend a regular backup schedule, which
  you can supplement with an additional backup after a substantial
  update.

If you venture further, you will find these details:

  The types of operations that cause master to be updated, and that
  require a backup to take place, include the following:

  Creating or deleting a user database.
  If a user database grows automatically to accommodate new data,
  master is not affected.
  Adding or removing files and filegroups.
  Adding logins or other operations that are related to login
  security.
  Database security operations, such as adding a user to a database,
  do    not affect master.
  Changing server-wide or database configuration options.
  Creating or removing logical backup devices.
  Configuring the server for distributed queries and remote procedure
  calls (RPCs), such as adding linked servers or remote logins.

So if all our logins are added through Windows groups, and we don't make any other changes to the database, does that mean that a one-time backup of the master is sufficient?

If not, what is the standard backup interval for the master database?


texts: Running a Job from a Stored Procedure in another server?

texts:
How can I run a job from another server using a stored procedure?

Let's say

server 1 : db1
server 2 : db2
username : testssis
password : testssispass
sqljob found in db2: job1

Here is the part im lost with how can I make this work?

xp_cmdshell + osql command -U: -P: + sp_startjob


texts: Mysql closing immediately after new installation

texts:
I am using mysql as an backend for my webapp ,the software ran without any glitch but today it
gave unexpectedly ERROR:2003 i tried many solution on the internet but none worked. so reinstalled the mysql software and now its command line closes immediately without even asking for password i am logged in as admin   . This problem is troubling me for the past 5hrs does anybody has a solution