texts:
I'm being asked to create what (at first) seemed to be a fairly simple/straight forward query. However, I am running into some problems caused by the link relationships between the tables being queried. And the wide range of data to be included on the report from various different tables.
This is a postgresql database that contains information on cooks who sell food items. Simple enough. The requested report queries for personal information on the cooks, their available menus, food items on those menus, and the last time that item was purchased...data which resides on 6-7 different tables.
I'm having a really difficult time JOINing all of these tables together in a way that returns the data in an acceptable way. Here is a visualization of the tables I'm working with and their link relationships:

As you can see, everything originates and links back to the user's entry on the account table. From there, sellers have an entry on the "store" table, which links to pretty much everything else:

'store_address' and 'address' tables contain address info.
'menu' table contains the names of the menus- each store can have multiple menus which offer certain food items.
kitchen_item table contains all of the specific food items available on each of the various menus.
'menu_item' table is what's used to link the food items on kitchen_item table to it's menu on the menu table.
'orders' tracks order information for each item sold

So I'm having trouble joining all of these tables together correctly. I need to be able to show each seller (account table), their personal info (address table), their available menus (menu table), the items on each of those menus (kitchen_items table), and the date of that item's last sale (orders table).
When I simply left join everything, every possible menu contains every possible food item... I attempted a combination of left joins, right joins, full joins, etc... but I think this one might be a little above my skill level... given the layout of the relationships.
So, would anybody be so kind as to demonstrate these tables might be joined together?
the joins:
select
account.id, account.email, account.firstname, account.lastname,
address.address, address.address_2, address.city, address.state,
menu.name,
kitchen_item.name,
orders.placed
from account
left join store on account.id = store.account_id
left join menu on store.id = menu.store_id
left join menu_item on menu.id = menu_item.menu_id
left join kitchen_item on (menu_item.kitchen_item_id = kitchen_item.id and store.id = kitchen_item.store_id)
left join orders on store.id = orders.store_id
join store_address on store.id = store.address.store_id
join address on store_address.address_id = address.id
group by table.value, table.value...

When I run the query with the tables joined like above, it's just missing random entries... and I'm not sure why.
The data is for a report. Tabular "everything together" or split into subject areas is fine - though split into subject areas might look nice. The only requirement is a column for each data point.
The last part I'm trying to accomplish is to join the table "orders" to obtain the newest single entry in orders.placed for each already existing row/food item. I'd imagine I need to write a subquery?
I completely agree with splitting the query up, though this is strictly the query that the individual wants. I would guess they're going to want me to implement some sort of " case when rowcount() >1 = '' " to clear out all of the duplicate entries and turn it into whitespace.


texts: SQL Fastest Way to find Data Difference Between Two Tables

texts:
I am trying to locate fastest way to find difference between two tables.  The first table is populated from text files daily, the second table is maintained in our database.  We need to add data that is new or has changed from the text files. The comparison is done on the primary key.

create table dbo.CustomerTransaction
(
    CustomerTransactionId int primary key,
    CustomerName varchar(50),
    ProductName varchar(50),
    QuantityBought int
)

So row values Table 1: (1,'Bob','Table',8) is the Same as Table 2: (1,'Bob','Table',8)

These are different (1,'Bob','Table',8) ,  (1,'Bob','Chair',8) , different on the primary key.

I am seeing a lot of articles discussing methods, with Left Join, Except, tablediff, Visual Studio DataComparison, Union group.

However, no one talks about performance/speed.  Which is the fastest internal algorithm way? I am guessing it is the TableDiff Utility, which is a SQL tool designed specifically for this purpose.

We do not have access to CDC in legacy text file system, so we are extracting all new data into SQL Server daily, and comparing to previous data. We are finding incremental load values, and placing into Kimball Data Warehouse.

Neither table is actively being used at the time we are doing the comparison.

How to find out the content differences between 2 SQL tables and produce sync SQL
https://stackoverflow.com/questions/4602083/sql-compare-data-from-two-tables


texts: Replicating to Older Versions of MS SQL Server

texts:
According to an old MSDN article (from the SQL Server 2008 R2 era), Microsoft SQL Server transactional replication subscribers need to be within two versions of the publisher. Does this hold true for more recent versions (i.e. 2012 & 2014)?

I've been unable to find current recent documentation on this.


texts: Why does SQL Server require the datatype length to be the same when using UNPIVOT?

texts:
When applying the UNPIVOT function to data that is not normalized, SQL Server requires that the datatype and length be the same.  I understand why the datatype must be the same but why does UNPIVOT require the length to be the same?
Let's say that I have the following sample data that I need to unpivot:
CREATE TABLE People
(
    PersonId int,
    Firstname varchar(50),
    Lastname varchar(25)
)

INSERT INTO People VALUES (1, 'Jim', 'Smith');
INSERT INTO People VALUES (2, 'Jane', 'Jones');
INSERT INTO People VALUES (3, 'Bob', 'Unicorn');

If I attempt to UNPIVOT the Firstname and Lastname columns similar to:
select PersonId, ColumnName, Value
from People
unpivot
(
  Value
  FOR ColumnName in (FirstName, LastName)
) unpiv;

SQL Server generates the error:

Msg 8167, Level 16, State 1, Line 6
The type of column "Lastname" conflicts with the type of other columns specified in the UNPIVOT list.

In order to resolve the error, we must use a subquery to first cast the Lastname column to have the same length as Firstname:
select PersonId, ColumnName, Value
from
(
  select personid,
    firstname,
    cast(lastname as varchar(50)) lastname
  from People
) d
unpivot
(
  Value FOR
  ColumnName in (FirstName, LastName)
) unpiv;

See SQL Fiddle with Demo
Prior to UNPIVOT being introduced in SQL Server 2005, I would use a SELECT with UNION ALL to unpivot the firstname/lastname columns and the query would run without the need to convert the columns to the same length:
select personid, 'firstname' ColumnName, firstname value
from People
union all
select personid, 'LastName', LastName
from People;

See SQL Fiddle with Demo.
We are also able to successfully unpivot the data using CROSS APPLY without having the same length on the datatype:
select PersonId, columnname, value
from People
cross apply
(
    select 'firstname', firstname union all
    select 'lastname', lastname
) c (columnname, value);

See SQL Fiddle with Demo.
I have read through MSDN but I didn't find anything explaining the reasoning for forcing the length on the datatype to be the same.
What is the logic behind requiring the same length when using UNPIVOT?


texts: How to move old data to cheap hard drive

texts:
I have a quite big PostgreSQL database (with timescale plugin). Right now it is consuming about 500Gb on a SSD. Most of the data is in the form of time series. In most cases data older than a few months isn't really interesting.

My idea was to move that data to a cheap SATA hard drive instead of buying more expensive SSD's. Is that a good idea, and is there some good practice for implementing?

My naive implementation would be:
Keep two databases (or create a tablespace on the cheap HDD). Fetch every few hours data from "fast" (SSD) database to the "slow" database (HDD). Every few days, delete data from the slow database. Is this a good idea? I am happy to hear some feedback and better suggestions.


texts:
I'm trying to get the numeric difference between two dates.  (Ex:  11/30/2018 - 11/05/2018 = 25)

One of the dates (@EndOfMonth) represent the last day of the prior month and is being converted into a 'yyyymmdd' format

BEGIN
DECLARE @EndOfMonth AS VARCHAR(10)
SET @EndOfMonth = (SELECT CONVERT(VARCHAR, DATEADD(m, DATEDIFF(m, 0, DATEADD(m, 1, DATEADD(MM, -1, @maxDwdate))), -1), 112))

The second date that I'm looking to subtract from @EndOfMonth, is a field named "DUEDATE" and it's in a 'mm/dd/yyyy' format.


texts: SQL Server in Azure VM + Azure AD + SSIS - how to authenticate?

texts:
Our configuration:

Windows Server 2016 VM in Azure, running SQL Server (same as on-prem version)
Azure Active Directory with users etc.
Local Windows 10 PCs are joined to the AAD domain
SQL Server is configured for mixed-mode auth.
Azure firewall allows connections to the SQL Server from the Internet (for now at least).

One of our developers is trying to setup an SSIS package to run in the server, and is running into the error stating you can only deploy/run SSIS packages from SSMS if you are using Windows authentication. I therefore am now looking into setting up "proper" authentication.

I've looked into Azure AD Domain Services and I can set that up to allow the Windows Server VM to join the domain.

My questions:

If I join the Windows Server VM to Azure AD using Azure AD DS, will the Windows authentication work from client PCs, out on the Internet, that are joined to the Azure AD via Windows 10's "Azure AD Join" feature? I noticed that in SSMS, the username is listed as "AzureAD\".
If I need to add more infrastructure, I'm obviously looking to minimize cost, so the options I see are as follows - which one of these makes the most sense/actually works?:

Deploy a local Windows Server on prem. This is at least a $500 cost (Server Essentials) and would restrict people from only being able to log in while on prem, unless I also setup a VPN service. It also excludes anyone from working on a home machine or basically any machine not joined to the domain - I think?
Not sure if this is possible, but, setup a site-to-site VPN with the Azure VNet containing Azure AD DS, and then use a Linux box on prem (free of software cost, could be a very minimal mini-box or even an ARM-based board) to connect local to the Azure VNet. Then, I should be able to theoretically just join all my machines to Azure AD DS as if I had a local server? This would also potentially enable us to restrict access to the SQL Server and just have everyone use the same VPN to connect to Azure and get to the SQL Server. Still doesn't really help people using non-joined machines...
Forget AD DS entirely. Not a good idea since we also have Office365 licensing already set up for everyone along with Email addresses, which integrates nicely with the Azure AD.

I've done a lot of work on local Windows Server infrastructure but am still new to Azure and how all of it fits together. Again, the ultimate goal here is to allow SQL Server devs and admins to use Windows Authentication against an SQL Server running in an Azure VM, with the directory being an Azure AD directory.


texts: SQL Insert error Column name or number of supplied values

texts:
I have one of the stored procedures for my replication giving this error

  Insert Error: Column name or number of supplied value does not match table definition".

The stored procedure is spInsertsrra and the table is srra, I have put the syntax below for the 2 articles.

       CREATE PROCEDURE SpInsertSrra @num_SessIdSession NUMERIC(10),
                                      @tin_SrraCptAnom1  TINYINT,
                                     @tin_SrraCptAnom2  TINYINT,
                                     @tin_SrraCptAnom3  TINYINT,
                                     @tin_SrraCptAnom4  TINYINT,
                                     @tin_SrraCptAnom5  TINYINT,
                                    @tin_SrraCptAnom6  TINYINT,
                                    @tin_SrraCptAnom7  TINYINT
AS
    INSERT INTO srra
    VALUES      (@num_SessIdSession,
                 @tin_SrraCptAnom1,
                 @tin_SrraCptAnom2,
                 @tin_SrraCptAnom3,
                 @tin_SrraCptAnom4,
                 @tin_SrraCptAnom5,
                 @tin_SrraCptAnom6,
                 @tin_SrraCptAnom7)

GO

CREATE TABLE [SRRA] (
[SESS_ID_SESSION] [numeric](10, 0) NOT NULL ,
[SRRA_CPT_ANOM1] [tinyint] NULL ,
[SRRA_CPT_ANOM2] [tinyint] NULL ,
[SRRA_CPT_ANOM3] [tinyint] NULL ,
[SRRA_CPT_ANOM4] [tinyint] NULL ,
[SRRA_CPT_ANOM5] [tinyint] NULL ,
[SRRA_CPT_ANOM6] [tinyint] NULL ,
[SRRA_CPT_ANOM7] [tinyint] NULL ,
[rowguid]  uniqueidentifier ROWGUIDCOL  NOT NULL CONSTRAINT
           [DF__SRRA__rowguid__5F94079C] DEFAULT (newid()),
       CONSTRAINT [PK_SRRA] PRIMARY KEY  CLUSTERED
           (
    [SESS_ID_SESSION]
         )  ON [PRIMARY] ,
         CONSTRAINT [FK_SRRA_CPTRS_ANO_SESS_SAN] FOREIGN KEY
         (
    [SESS_ID_SESSION]
        ) REFERENCES [SESS_SANTE] (
    [SESS_ID_SESSION]
     )
          ) ON [PRIMARY]
          GO


texts: Column Data Type for Bitwise Comparison?

texts:
I have a table where I have an int32 column storing values for bitwise comparison. I've ran out int space with a int32. I can convert to int64 but that will just prolong the inevitable. Is there an efficient data type I should store these values in instead of int?

It would be OK to re-assign all my existing values if I have to.


texts: CentOS 7, PostgreSQL failed to read permission data directory in mounted partition

texts:
I've a problem with start PostgreSQL 9.4 on Centos 7 Server. Because the partition /var/lib/pgsql/9.4/data/ (which contains PostgreSQL data) is full so I want to change to another partition.

/dev/sdb1               154685904  322500 146482752   1% /run/media/root/Data

I've used this command to copy all the old data to new data folder:

cp -a /var/lib/pgsql/9.4/data/ /run/media/root/Data/pgsql_data

Then I changed the directory to new data directory in postgresql.conf (/var/lib/pgsql/9.4/data)

data_directory = '/run/media/root/Data/pgsql_data/'     # use data in another directory

And then I try to restart PostgreSQL, here are errors:

postgresql-9.4.service - PostgreSQL 9.4 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-9.4.service; enabled)
Active: failed (Result: exit-code) since Wed 2015-03-11 09:17:21 ICT; 4s ago
Process: 54975 ExecStop=/usr/pgsql-9.4/bin/pg_ctl stop -D ${PGDATA} -s -m fast (code=exited, status=0/SUCCESS)
Process: 56133 ExecStart=/usr/pgsql-9.4/bin/pg_ctl start -D ${PGDATA} -s -w -t 300 (code=exited, status=1/FAILURE)
Process: 56127 ExecStartPre=/usr/pgsql-9.4/bin/postgresql94-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 54580 (code=exited, status=0/SUCCESS)

Mar 11 09:17:16 localhost.localdomain pg_ctl[56133]: LOG:  skipping missing configuration file "/run/media/root/Data/pgsql_data/postgresql.auto.conf"
Mar 11 09:17:16 localhost.localdomain pg_ctl[56133]: < 2015-03-10 22:17:16.187 EDT FATAL:  could not read permissions of directory "/run/media/root/Data/pgsql_data": Permission denied
Mar 11 09:17:21 localhost.localdomain pg_ctl[56133]: pg_ctl: could not start server
Mar 11 09:17:21 localhost.localdomain pg_ctl[56133]: Examine the log output.
Mar 11 09:17:21 localhost.localdomain systemd[1]: postgresql-9.4.service: control process exited, code=exited status=1
Mar 11 09:17:21 localhost.localdomain systemd[1]: Failed to start PostgreSQL 9.4 database server.
Mar 11 09:17:21 localhost.localdomain systemd[1]: Unit postgresql-9.4.service entered failed state.

Please help!


texts:
I'm trying to select attribute value from XML column using XPath with a parameter that can be a Unicode string. Could you please tell me how to make these two examples work?

create table #test
(
    Document xml
);

insert into #test (Document) values
('<FormDocument xmlns="http://mynamespace.org/Schemas">
  <Fields>
    <Field Id="a" Value="qwerty" />
    <Field Id="ą" Value="123" />
  </Fields>
</FormDocument>')

-- works
declare @p1 int
set @p1=-1
exec sp_prepexec @p1 output,N'@p0 nvarchar(4000)',
N'select t.Document.value(''declare default element namespace "http://mynamespace.org/Schemas"; /FormDocument[1]/Fields[1]/Field[@Id = sql:variable("@p0")][1]/@Value'', ''nvarchar(255)'') as Col1
from #test t',@p0=N'a'
go

-- doesn't work
declare @p1 int
set @p1=-1
exec sp_prepexec @p1 output,N'@p0 nvarchar(4000)',
N'select t.Document.value(''declare default element namespace "http://mynamespace.org/Schemas"; /FormDocument[1]/Fields[1]/Field[@Id = sql:variable("@p0")][1]/@Value'', ''nvarchar(255)'') as Col1
from #test t',@p0=N'ą'
go

-- works
declare @p0 nvarchar(4000) = N'a'
select t.Document.value('declare default element namespace "http://mynamespace.org/Schemas"; /FormDocument[1]/Fields[1]/Field[@Id = sql:variable("@p0")][1]/@Value', 'nvarchar(255)') as Col1
from #test t
go

-- doesn't work
declare @p0 nvarchar(4000) = N'ą'
select t.Document.value('declare default element namespace "http://mynamespace.org/Schemas"; /FormDocument[1]/Fields[1]/Field[@Id = sql:variable("@p0")][1]/@Value', 'nvarchar(255)') as Col1
from #test t
go

drop table #test;


texts: How to convert from one type of relationship table to another type

texts:
I currently have a relationship table where it's:

documentID, dataType, dataID

Where documentID is the link to the document file. The dataType is if the data is a specific type of user, for example a student, teacher, vendor, etc. (as these all have different tables). The dataID is the ID of the user.

So for example:

documentID, dataType, dataID
12343, 2, 4323

Would be the file 12343 for the type teacher (type 2 equals teacher) and it would be for teacher with ID 4323.

What I want to do is convert this table to the format:

documentID, dataType, studentID, teacherID, vendorID, etcID...
12343, 2, -1, 4323, -1, -1, ...

The reason I want to do it this way is because the files are now going to be cross-referenced a lot more and in the majority of cases a specific document will be linked to several different users and user types.

I could keep the original table but I would prefer to keep the inserts, updates, etc. to a single database to the document relationship table. That and a few other reasons.

Anyway is there a way to convert from one table to the other? The only possible solution I came up with is to transfer all the documentID and dataTypes in one step. Then transfer the dataID using a combination of matching document ID with dataType, and running one update per dataType. At least in theory... I haven't yet been able to make that work. Is this the only way and if so any suggestions as to the actual SQL would be appreciated. So far I have:

INSERT INTO documentRelNew (documentID) SELECT documentID FROM documentRelOld;

which works great but the following SQL line fails:

UPDATE
    documentRelNew
SET
    documentRelNew.studentID=documentRelOld.dataID
FROM
    documentRelOld
WHERE
    documentRelOld.dataType=2;

It fails at the FROM portion. Yet everything I've read seems to indicate this should work. In theory I need to run this for all the different data types however I can't even get it to work for the first dataType I want to test...


texts: Error text: "Key ... is not present in table" even though the key is there?

texts:
Using Postgresql 9.5 on Ubuntu 16.04.  Importing large .csv files into a new database using COPY FROM.  The database is created by the same script that attempts to import the .csv files, so I know the tables are initially empty.  The script successfully imports around 45,000 rows into the first table.

The problem:
Partway through importing the 2nd table's data, I get errors like this one:

ERROR:  insert or update on table "inactive" violates foreign key constraint "inactive_drug_code_fkey"
DETAIL:  Key (drug_code)=(47543) is not present in table "qrym_drug_product".

But when I check the 1st table, I see that that key exists:

select * from dpd.qrym_drug_product where drug_code=47543;
  id   | drug_code | product_categorization | class | ...
-------+-----------+------------------------+-------+----
 17970 |     47543 |                        | Human | ...
(1 row)

Why would PostgreSQL complain about that key not being present in the table, when I can see it does exist?

I'm not using table inheritance (I saw the caveat on [1]).

Here is the first table:

\d dpd.qrym_drug_product;
                                       Table "dpd.qrym_drug_product"
           Column           |  Type   |                             Modifiers
----------------------------+---------+--------------------------------------------------------------------
 id                         | bigint  | not null default nextval('dpd.qrym_drug_product_id_seq'::regclass)
 drug_code                  | integer | not null
 product_categorization     | text    |
 class                      | text    |
 drug_identification_number | text    |
 ai_group_no                | text    |
Indexes:
    "qrym_drug_product_pkey" PRIMARY KEY, btree (id)
    "qrym_drug_product_drug_code_key" UNIQUE CONSTRAINT, btree (drug_code)
Referenced by:
    TABLE "dpd.inactive" CONSTRAINT "inactive_drug_code_fkey" FOREIGN KEY (drug_code) REFERENCES dpd.qrym_drug_product(id) ON DELETE RESTRICT

Here is the definition for the 2nd table that bombs partway through the COPY FROM:

\d dpd.inactive;
                                       Table "dpd.inactive"
           Column           |  Type   |                         Modifiers
----------------------------+---------+-----------------------------------------------------------
 id                         | bigint  | not null default nextval('dpd.inactive_id_seq'::regclass)
 drug_code                  | integer | not null
 drug_identification_number | text    |
 brand_name                 | text    |
 history_date               | date    |
Indexes:
    "inactive_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "inactive_drug_code_fkey" FOREIGN KEY (drug_code) REFERENCES dpd.qrym_drug_product(id) ON DELETE RESTRICT


texts: Performance of primary key IDs with gaps (but in sequential order)

texts:
I know that having non-sequential IDs is bad for index performance.  But assuming all my IDs are created in correct order, but with large gaps: i.e:

154300000
283700000
351300000
464200000

...will the performance be any worse than having gapless auto_increment IDs?

I'll be using MySQL, or perhaps PostgreSQL.  The gaps between the IDs would not be even.  They'll be BIGINTs with a unix timestamp at the start (left side) of the number, and the rest of the numbers mostly being random, as discussed in another question I asked here:

https://stackoverflow.com/questions/6338956/mysql-primary-keys-uuid-guid-vs-bigint-timestamprandom/6339581


texts: How to set lower case for identifiers in Toad?

texts:
I'm using Toad to write queries agains an Oracle database. When I write table_name. I get an auto-suggestion menu with all the column names. But if I choose a column name, both the table name and column name is written by uppercases. Is there any way to change this to be written in lowercases instead?

In View > Formatting Option > Case "Identifiers" are set to Lowercase


texts: Sql Express Backup and Restore to Different Server

texts: I have been translating an old ADP file format to a more current VB front end using Sql Express Backend.  In order to do the project the owners had me either VPN or Team Viewer in to a connected machine to run the software  As this was painfully slow.  I oersuaded them to let me create a bak file and then I would download that bak file and restore to my local sql express server.  I have had no issues.  Now I have never included the transaction log just the DB .  On my local copy I have added SP's and views and every time i do the restore the one I created that are not on the original server are still intact - which to my surprise and delight is great.  Ids this because I am not backing up the transition log or is there a different explanation?  Plus is it safe to assume any edits i have done locally to an SP or View whose name does exist in the original DB, will be overwritten?


texts: Tool to help breaking multitenant into a single tenant

texts:
Oracle 11.2.0.4

I've been tasked with breaking out a single customer from our multitenant system into a separate system. And the data will have to be continuously updated.  This is a large system with thousands of tables and unfortunately all foreign keys are not in place, the application maintains some level referential integrity (horrible, but in place well before me) AND the system doesn't have a tenant identifier at every level.

Essentially, the relationships between tables are often very complex.

In my mind, all this leads to the conclusion that effectively custom views or custom etl scripts will have to be created for every table.  "Management" thinks there must be some kind of tool to assist with this, defining rules, generating scripts, and such.

So, is anyone of aware of such a tool to assist with breaking a single tenant out of a multi tenant?  I know I'm being vague here, but its a vague issue and I keep getting asked if I've "found a tool yet"...which seems unlikely to me.


texts: How to enable Cloud Watch logs for AWS DMS task?

texts:
I am creating AWS DMS task for migrating data from GCP SQL server to AWS Aurora MySQL. How can I attach log group which I created?
I could able to enable cloud watch logs for the task but when I click on "View CloudWatch logs" its showing as log group doesn't exit. Details in image -


texts: PostgreSQL: Disk space not released after TRUNCATE

texts:
I haveTRUNCATEd a huge (~120Gb) table called files:

TRUNCATE files;
VACUUM FULL files;

The table size is 0, but no disk space was released. Any ideas how to reclaim my lost disk space?

UPDATE:
The disk space was released after ~12 hours, without any action on my side. I use Ubuntu 8.04 server.


texts: What's the practice to generate long user id like those on Facebook?

texts:
Now I am about to launch a site and I have trouble coming up with a way to create long user id(e.g. 4000002).

Do I set id to auto-increment(Starts at 0) and then add something(e.g. 4000000) to this id and put this new number in a new column in my table?

Does any body have a better practice to achieve this?

Thanks in advance!


texts: MySql Order by isnull() Performance Problem

texts:
my sql below is use for listing stock added from 10 days ago.
Order by isnull(Price) is use so that stock without any price yet will still being listed.

AddDate and Price has an index.

SELECT Id, Price FROM tblStock
where AddDate >= date_sub(curdate(),interval 10 day)
order by isnull(Price), Price asc limit 50

Explain the sql shown that it is not using the Price index.
So i tried to improve the query and has come out with below sql

SELECT Id, Price FROM tblStock
where AddDate >= date_sub(curdate(),interval 10 day)
and Price is not null
order by Price asc limit 50

The new sql works a lot faster and explain shown it uses Price index but the problem is that Price with null values will never selected.

Looking for any comments or suggestions on how to solve this problem. Thanks.


texts: PostgreSQL asynchronous replication (and possible data loss)

texts:
I'm trying to wrap my head around Postgres' asynchronous replication. The way I'm reading the documentation, if the master goes down after data was committed, but before it's shipped to slaves, the slaves will never get that data (even after the master goes back up).

This, as I understand it, is different than MySQL's async replication, where the master resumes syncing it's binary log once it's brought back up (without any data loss).

Seems a bit crazy to me, so I wanted to make sure I was reading the docs properly.

To be clear, I'm not talking about data loss resulting in electing one of the slaves as the new master. Simply data loss with respect to the master going down before the WAL is shipped and it not being shipped once the master comes back up.


texts:
I have been trying to make a new table and want to use the date of the existing data. Can anyone help me understanding what is wrong with this formula?

dDate = CALENDAR(DATE(YEAR(MIN(Ftransactions[Date])),1,1),DATE(YEAR(MAX(Ftransactions[Date])),12,31))

The error which keeps popping up after pressing enter is:

  An argument of function 'YEAR' has the wrong data type or the result is too large or too small. If the argument is expected to be a date, that date must be between January 1, 100 and December 31, 9999.


texts:
I have this query that deadlocks every once in a while. It is doing a self update to a table. ( update, from ) Is there any better way to handle the concurrency?
Here is the plan:
https://www.brentozar.com/pastetheplan/?id=r19CZMC8-

Deadlock:

UPDATE j SET StatusId=@StatusId -- SELECT *
FROM jobmanager.JobRun j with(rowlock) WHERE PrintBatchId=@PrintBatchI

Proc [Database Id = 11 Object Id = 191196327]


texts: Use database to track locking/unlocking of objects

texts:
I have a requirement to track lock/unlock actions of objects. Before any action made on an object (contract, partner, etc), an lock event is issued. After the action is finished, it issues the unlock event.

I want to obtain those objects which are locked but not yet unlocked. The goal is to make the query quick and to avoid deadlocks.

Below is the table

create table locks (
    id int identity,
    name varchar(255),
    lock int
)

insert into locks values('a', 1)
insert into locks values('b', 1)

insert into locks values('c', 1)
insert into locks values('d', 1)

insert into locks values('a', 0)
insert into locks values('c', 0)

insert into locks values('a', 1)
insert into locks values('b', 1)

I use the query below to object not-yet-unlocked objects:

select distinct m.name from locks m
    where (select COUNT(id) from locks locked
           where locked.lock = 1 and locked.name = m.name)
        >  (select COUNT(id) from locks unlocked
            where unlocked.lock = 0 and unlocked.name = m.name)

It works correct and result a, b and d.

My questions are:
 - Is my solution sufficient enough to avoid deadlocks? Is there any problem may occurs if there are a lots of INSERT during the query execution?
 - Do you have any other (better) way to solve this?

UPDATE

I apologize for not putting the context into the question. The database design above is not for replacing database locking.

We have an external system which we call it from our system. It requires to call lock and unlock method on their systems before each action performing on an object (could be a contract or a partner).

Recently, we have situations such that the server crashes and we have to restart it. Unfortunately, the running processes which already called lock did not have a chance to call unlock to release the objects, thus led to several other problems when our system connects again to the external one.

So we want to provide a capability to track each lock call. Upon restarting the server, we will call unlock on the objects which previously locked.

Thank Remus Rusanu for pointing out that my question is using a prototype DDL. This is the first time I posted a question on DBA and I apologize for not reading the FAQ.

Thanks


texts:
I'm trying to port over some statements from Oracle to Postgres.
One specific one that I have a hard time even understanding on the Oracle side is this:
rating_scheme_oid NUMBER;
rs_cnt number;

From what I can understand it seems like these are just defining variables, so I figured I could translate that to this:
CREATE TYPE rating_scheme_oid NUMERIC;
CREATE TYPE rs_cnt NUMERIC;

However this doesn't seem to work:
ERROR: syntax error at or near "NUMERIC"
If anyone with expertise in this area could describe what these are doing and a possible solution, that'd be great.
Here's the full context (this is the working Oracle statement):
        DECLARE
        CURSOR update_cursor
        IS
        SELECT oid, subscription_id, configuration_oid
        FROM scope
        WHERE subscription_id != 1 and subclass_type = 'W' and configuration_oid is not null;

        TYPE type
        IS
        TABLE OF update_cursor%ROWTYPE;
        update_array type;

        rating_scheme_oid NUMBER;
        rs_cnt number;

        BEGIN
        OPEN update_cursor;
        LOOP
        FETCH update_cursor BULK COLLECT INTO update_array LIMIT 1000;
        FOR i IN 1 .. update_array.COUNT LOOP

        BEGIN
        select count(*) into rs_cnt from workspace_config where workspace_oid = update_array(i).oid and OBJECTIVE_STATE_SCHEME_OID is null;
        IF rs_cnt = 1
        THEN

        SELECT OID_SEQ.nextval into rating_scheme_oid from DUAL;

        INSERT INTO RATING_SCHEME (OID, SUBSCRIPTION_ID, VERSION, NAME, CREATION_DATE, WORKSPACE_OID, UUID)
        VALUES (rating_scheme_oid, update_array(i).subscription_id, 1, null, SYSDATE, update_array(i).oid, sys_guid());

        UPDATE WORKSPACE_CONFIG set OBJECTIVE_STATE_SCHEME_OID = rating_scheme_oid where oid = update_array(i).configuration_oid;

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'Defined', 0, sys_guid());

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'Committed', 1, sys_guid());

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'In-Progress', 2, sys_guid());

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'Measuring', 3, sys_guid());

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'Achieved', 4, sys_guid());

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'Closed', 5, sys_guid());

        END IF;

        END;
        END LOOP;
        COMMIT;
        EXIT WHEN update_cursor%NOTFOUND;
        END LOOP;
        CLOSE update_cursor;
        END;


texts: Is it possible to set auto-increment-increment by 10 for only one table?

texts:
Is it possible to add auto-increment-increment by 10 for only one table in mysql?

I have a testing db that has a lot of tables that do not abide by the auto-increment-increment of 10.  I won't want to mess up my testing rig.

The production server has auto-increment-increment of 10.


texts:
I am looking to generate a cryptographically secure string for password reset in MySQL. I know I could do this at the application level in PHP; but that requires the OpenSSL extension which many of my customers might not have.

If I can do it in MySQL 5 in a secure way that is widely available that would be ideal. Is this possible?

Note: This is for generating a secure token for password reset. It doesn't have anything to do with a secure connection, so using https is not a solution.


texts:
I am having duplicates data with this enormous query.

SELECT        t2.`1st_test` AS agric1, t2.`2nd_test` AS agric2,
              t2.exam AS agricExam3, t2.result_total AS agricTotal,
              t2.result_grade AS agricGrade,  t2.result_remark AS agricRemark
FROM
              result_summary AS t1
              LEFT  JOIN
                     agricultural_science AS t2
                     ON t1.student_id = t2.student_id
WHERE        (    t1.student_id     = 'GCU/16/10414'
              AND t1.result_term    = '1st'
              AND t1.result_session = '2016/2017'
              AND t1.result_level   = 'JSS'
              AND t1.result_class   = '2')

Table Structure

Structure for the Agricultural Science table is like so:

|id|student_id|result_session|result_term|result_arm|
 result_class|result_level|1st_test|2nd_test|exam|result_total|
 result_grade|result_remark|

The table structure for Result_Summary Table look like so:

|id|student_id|result_score|result_average|result_term|
 result_session|result_level|result_class|result_arm

Data that match the criteria exist in some tables only, while others contains two rows each with the same Student_ID but different Session. When the query is executed I get enormous duplicates and even data that does not match the 2016/2017 session.

Please, how can I fix this?


texts: PostgreSQL 10 logical Replication not working !!! slots blocking calls not sending wal events back to client

texts:
Postgres logical replication not working

we have created logical replication slot on pg 10 using below command

select *
from pg_create_logical_replication_slot('slot', 'wal2json');

Above request creates slot on database

 select * from pg_replication_slots;

PG replication not returning stream events using slot listen command.

Slots size automatically increases to high value.

Below are postgress level global configs

archive_mode=off
archive_command=(disabled)
ssl=off
wal_level=logical
shared_preload_libraries=decoderbufs, wal2json
wal_keep_segments=100
max_replication_slots=100
max_wal_senders=30
wal_sender_timeout=0
max_standby_streaming_delay=-1

Question:

What is best configuration for pg logical replication to fix our issue?
Which is best plugin for logical replication our of below options:

wal2json
test decoding
protobuf

Please provide inputs to fix our issue.


texts: How to connect use INSERT query using SELECT query in mySQL

texts:
I have 2 tables, cce_sa_fa & cce_activity

I used the following line to get the cce_sa_fa not included in cce_activity

select id from cce_sa_fa where id NOT IN (select safa from cce_activity)

Using this query, I got 113 rows. Now I need to INSERT these ids into cce_activity. Here the id needs to be inserted and other columns are with fixed value. Usual insert query for cce_activity is as follows.

INSERT INTO `cce_activity` (`id`, `safa`, `name`, `mark`) VALUES (NULL, '"+id_of_ cce_sa_fa+"', 'Activity 1', '0');

How can I insert all the ids from the SELECT query to INSERT into cce_activity


texts: why is the name `loop` treated differently in PostgreSQL

texts:
I issue the following DDL:

CREATE TABLE foo
(
loopa INTEGER,
loop  INTEGER
);

Then, examining the DDL in the server (using a client-side tool like DbVisualizer - sorry don't know the query to produce it directly) I see the following:

CREATE TABLE
    foo
    (
        loopa INTEGER,
        LOOP INTEGER
    );

Observe the change in case for the column named loop but not for the one named loopa. Looking at the list of SQL keywords loop doesn't appear to be a keyword. Why then, this selective change?

This seems to be related to a problem I am having with a trigger trying to access a column loop in a table where I get:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: record "new" has no field "loop"

(where I get the same error regardless of whether the code in the trigger function access the field as NEW.LOOP or NEW.loop)

UPDATE

The upper-casing seems to be an artifact of DbVisualizer. psql shows:

psql (9.1.13)
Type "help" for help.

foo-test=> \dS+ foo
                    Table "test.foo"
 Column |  Type   | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
 loopa  | integer |           | plain   |
 loop   | integer |           | plain   |
Has OIDs: no

Yet this still doesn't explain why the PostgreSQL engine apparently doesn't allow my trigger to access NEW.loop. When I rename the column (and the trigger code) to loopi everything appears to work fine. So, there is likely something deeper at work here than just a DbVisualizer glitch. I'll try to create a SSCCE later.

UPDATE 2

A SSCCE for the trigger problem has been created here.


texts: Allow access to enter data through forms, but not full database access

texts:
Using Access 2013 or Sharepoint 2013 is it possible to give someone access to data entry forms that work with a back-end database whether Access database or Sharepoint SQL.

But from everything I've looked at I can't find a way to separate someone from entering data and viewing all data?

For example in Access even if I split the database to a back end, it still has all the tables linked, and fully accessible from the "front end" this seems to be needed to allow Write access, it seems that you can have no access, read-only access and Read/Write access but no write-only access (this might be where I am wrong).

I don't want the people entering data to see the back end data (regardless if they are on Sharepoint or in access) and certainly do not want them to be able to modify the database directly (they should only be able to enter data via a form that can be validated before entered.)

Now I come from a Windows Forms and ASP.NET MVC background. I am used to hosting a SQL server and simply not writing code that pulls data from the database, and if I did I could set parameters on what data they could access. I could also set up server-side validation based on users and groups.

I can't seem to find any documentation on how to do this in either Access or on Sharepoint. I have read every article I could find on these topics via google and Support.Office.com for starters:

Introduction to integrating data between Access and a SharePoint site

Ways to share an Access desktop database

SharePoint Online environment

and a multitude of other articles both on TechNet and various third-party sites. While some do touch base on user access rights, they seem to allow only access to a specific site or database, not parts of, as my needs are allowed access to a way to enter data but not access to the data.

The client I am working with is addiment on using Access and Sharepoint. And before telling them I can't limit a user to data entry only, I wanted to check here first.

I did read a site that stated an issue with Access 2013 and Sharepoint is

  Security setup is basic and somewhat limited. Item (or row) level security is not possible and edit access appears to allow users to edit information across all tables – not just one.

this was found here: http://blogs.perficient.com

but this seems to state that when you give someone table access they have all tables, and NOT that you can't restrict table access.

It does seem to state however that the only true options are HTML or developing something stand alone in Visual Studios.


texts: Join on dm_db_index_physical_stats very slow

texts:
I'm running a small query below in a MS Dynamics AX 2012 database and it runs more than 5 minutes which I had to cancel and it is showing PAGEIOLATCH_SH wait type. The database data file is 560GB and on SQL Server 2012 SP1.

SELECT TOP 1 A.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A
INNER JOIN sys.objects B
ON A.object_id = B.object_id
INNER JOIN sys.indexes C
ON B.object_id = C.object_id AND A.index_id = C.index_id
INNER JOIN sys.partitions D
ON B.object_id = D.object_id AND A.index_id = D.index_id

I checked resource monitor in the server for disk activity and the Read(B/sec) shoots up from an initial 23,000 to over 13,000,000 while the query is running and goes back down after the query is cancelled.

Any ideas what may be causing this?


texts: How do I check if a stored procedure is defined in MySQL

texts:
When I do :

select routine_definition
from information_schema.routines
where routine_name = 'sp_name';

it returns a row, but the routine_definition column is NULL.  Does this mean that this Stored Procedure is not defined?

Also, when I do show create procedure sp_name, it says that procedure does not exit.


texts: Passing array parameters to a stored procedure

texts:
I've got a process that grabs a bunch of records (1000's) and operates on them, and when I'm done, I need to mark a large number of them as processed.  I can indicate this with a big list of IDs.  I'm trying to avoid the "updates in a loop" pattern, so I'd like to find a more efficient way to send this bag of ID's into a MS SQL Server 2008 stored proc.

Proposal #1 - Table Valued Parameters.  I can define a table type w/ just an ID field and send in a table full of IDs to update.

Proposal #2 - XML parameter (varchar) with OPENXML() in proc body.

Proposal #3 - List parsing.  I'd rather avoid this, if possible, as it seems unwieldy and error-prone.

Any preference among these, or any ideas I've missed?


texts: Master ip has changed in postgresql replication

texts: I have postgresql replication system with two machines, suppose I want to change the master ip,the streaming to slave machine will stop. Can I resume streaming with the new master ip instead of doing the replication from scratch.


texts: Database design for an online babyShop ,Toy shop

texts:
Hi i'm designing a database for a toy shop this shop offer many products with different categories. boystoys,girlstoys,puzzles,beds,stroller, etc
i designed my database as it is consists of 5 tables
(Customer,Order,order-details,product, product-category)

in the product category i added 2 columns one the primary key(ProductCategoryID) and the second for the (ProductCategoryName) i made it a unique so i can make relation between it and the product table.

My question : do you think that this design is True or not i'm still a beginner in designing a database and i want to know do you advice me to add another tables ?

note: i will not let the customer to pay here i just want from him to order his item and the shop will call him to confirm his order , the customer only will choose his products and then click Order and then a message come's to the shop owner with his details and order

i dont know why my client want to do this in his web site but im trying to develop it :D


texts: Make primary key and unique id as foriegn key

texts:
I have two tables as follows:

UserProfile

CREATE TABLE IF NOT EXISTS USER_PROFILE (
  id                BIGINT       NOT NULL AUTO_INCREMENT,
  user_id           BIGINT       NOT NULL,
  name              VARCHAR(100) NOT NULL,
  username          VARCHAR(100) NOT NULL,
  mobile            BIGINT       NOT NULL,
  description       VARCHAR(100),
  created_date      DATETIME,
  modified_date     DATETIME,
  PRIMARY KEY (id),
  UNIQUE (user_id)
);

UserDescriptionHistory

CREATE TABLE IF NOT EXISTS USER_DESCRIPTION_HISTORY (
  id           BIGINT        NOT NULL   AUTO_INCREMENT,
  user_id      BIGINT        NOT NULL,
  description  VARCHAR(100),
  dt_datetime  DATETIME      NOT NULL   DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  CONSTRAINT FOREIGN KEY (user_id) REFERENCES USER_PROFILE (id)
);

SHOW CREATE TABLE UserProfile

CREATE TABLE `CYCLOS_USER_PROFILE` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `username` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mobile` bigint(20) DEFAULT NULL,
  `description` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `mobile` (`mobile`)
   ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

I read that the best practice is to reference pk from the parent table. But I also need the unique constraint from the parent table which is the user_id. So can I reference both pk and unique constraints on foreign key? If yes, how can I do that? Thanks in advance!


texts: I can't see some databases even though my user has the "root" role

texts:
I installed MongoDB 3.2 and I enabled authentication:

security:
  authorization: enabled

I created an admin user with the following command in the terminal:

mongo admin --eval 'db.createUser({user:"admin",pwd:"password",roles:["root"]})'

Then I created new database and a user for this database using the following command:

mongo -u admin -p password admin --eval 'db.getSiblingDB("shop").createUser({user:"shop_user",pwd:"password",roles:["dbOwner"]})'

Everything seem to be working. When I'm logged in with the shop_user user I can do everything in the shop database and I can only see this database. But when I'm logged in with the admin user I cannot see the shop database. Since the admin user has the root role, I assume I should be able to see all the databases in my system, but the shop database is hidden from me.

Does anybody know what is happening and how I can make the admin user see all databases?


texts: Sort buffers and tables on disk, MySQL/InnoDB

texts:
I have the following type of query frequently running:

# Time: 120322 13:03:29
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 17.039286  Lock_time: 0.000141 Rows_sent: 24  Rows_examined: 1471904
SET timestamp=1332417809;
select cast(cast(someplaceen0_.datePlaced as date) as datetime) as col_0_0_,     count(distinct someplaceen0_.user) as col_1_0_, lower(someplaceen0_.deviceId) as     col_2_0_, someplaceen0_.country as col_3_0_ from SomePlaceEntry someplaceen0_ where someplaceen0_.datePlaced between '2011-12-17 00:00:00.0' and '2012-03-16 00:00:00.0' group by col_0_0_ , lower(someplaceen0_.deviceId) , someplaceen0_.country;

This particular database is pretty much doing that query after a bunch of inserts at given intervals, and Im now trying tweak my.cnf accordingly.

Engine: InnoDB

RAM: 12G, single purpose.

No TEXT or BLOB, still temporary tables on disk. My understanding is that this should never have to happen, given that max_heap_table_size and tmp_table_size are high enough. Is that correct?
Perhaps joins and sort buffers shall be increased, how can I see how filled they have ever been?

mysqltuner output:

<snip>
[!!] Sorts requiring temporary tables: 175% (49 temp sorts / 28 sorts)
[OK] Temporary tables created on disk: 0% (132 on disk / 20K total)

Variables to adjust:
    query_cache_limit (> 4M, or use smaller result sets)
    sort_buffer_size (> 4M)
    read_rnd_buffer_size (> 1M)
    table_cache (> 128)

Relevant my.cnf config:

sort_buffer_size = 4M
read_rnd_buffer_size = 1M
max_heap_table_size = 128M
tmp_table_size = 128M

What's your general recommendation in terms of config here?


texts: Is adding the ‘tbl’ prefix to table names really a problem?

texts:
I’m watching some Brent Ozar videos (like this one, for instance) and he suggests not prefixing tables with ‘tbl’ or ‘TBL’.

On the internet I found some blogs saying it adds nothing to documentation, and also that “it takes longer to read it”.

Questions and considerations

It this really a problem? Because I’m prefixing tables with ‘tbl’ since my first dba job (the senior DBA told me to do that for organization).
Is this something that I need to get rid of? I made some tests, copying a really big table and giving it the ‘tbl’ prefix, while keeping the other one without it, and I didn’t notice any performance issue.


texts: Should I manually VACUUM my PostgreSQL database if autovacuum is turned on?

texts:
I use software which makes a big PostgreSQL database (there is a table with a million rows in it) and the developers says I should VACUUM and ANALYZE periodically. But the PostgreSQL database default is autovacuum turned on.

Should I vacuum/analyze at all? What are the benefits? What's the difference between automatic and manual vacuum

For example, in Pgadmin3, I have this:


texts: Improving database design for category based site

texts:
I'm working on a site that gets about 30k visits a day and it makes use of a lot of queries which uses UNION's over about 45 different tables, these tables generally range from about 15k rows to about 500k rows in each table.

We have optimized the query cache as good as possible (to my knowledge from the extensive research I did) but whenever one of the tables is changed and the data in the cache has to be removed the server starts to lock up and the mysql tmp dir fills up and the server load goes sky high and the following has to be done to fix the issue:

Stop mysql (load goes to normal)
Repair the database tables
Clear cache from mysql
Flush memcache
Repair database tables again
Restart mysql

I didn't code the site nor did I design the database schema, but I have noticed in the code that the SELECT's that make use of the UNION's all start with a parentheses and hence shouldn't be cached; though if they aren't cached, then it doesn't explain why the site goes haywire whenever a record is deleted and the cache would be cleared for those tables.

I'm told that MySQL should be able to handle 39k visits/day fine; so I'm wondering if the database design could be improved and if so how?

Obviously putting all the data into one big table is an even worse design than it has now, but I'm unsure what other options there are if it isn't one big table or category based data.

Below is a screenshot of an EXPLAIN which seems to show that the queries are indexed fine:

http://i.stack.imgur.com/jyjlJ.png (Linked it as site was resizing it and was unreadable)

I've searched around the net but couldn't find much of anything that documents other category based data models.


texts: What is the proper setup for PG user for web application

texts:
We have a web app that detects if the DB does not exist and creates it.
Users like it, but my thought is that such user has too much privileges.

I would optimally propose to change the DB creation screen of the app to supply 2 sets of credentials - one that will be used for creating DB initially and not stored permanently (e.g. 'postgres' role)  and the other that will be used by the web app and stored in config file (e.g. 'webapp').
Furthermore, 'webapp' will need to be able to 1) add new and 2) modify existing tables, procedures, and functions (the web app automatically updates the schema as required)

So some of the questions I have are:

What are some of the best practices in above scenario when using PostgreSQL 9.x?
Should the DB created be owned by 'webapp' role or should it be owned by 'postgres' and I just grant 'webapp' the necessary privileges. In case of latter, would newly added objects be owned by 'webapp' and should I avoid that?
What are some of the approaches users in community are currently using?


texts:
This is my table tracks

id | art | time

id is an auto-increment primary key
art is a varchar(512)
time is a timestamp not null predefined as current_timestamp and on update current_timestamp

It seems really a dumb question but after one hour of tries, someone could please tell me why this simple query doesn't UPDATE the rows with empty art field but instead UPDATE only the time to 0000-00-00 00:00:00?

UPDATE `tracks` SET `time` = `time` AND `art` = 'default.jpg' WHERE `art` = '';

UPDATE

I ran SELECT * FROM tracks WHERE art = ''; and it founds right rows, same using WHERE TRIM(art) = '' .
Instead WHERE art IS NULL is empty.

UPDATE2

This query works but it updates the time and I don't want it.

UPDATE `tracks` SET `art` = 'default1.jpg' WHERE `art` = '';


texts: Need to deny DML access through SSMS using web app credentials

texts:
A user can login to some web application and can submit any application which will affect the data(insert/update) in database, but that specific user should not be able to manipulate the data through SSMS login with the same credentials.

How can we configure this role?


texts:
I have been given a server (SQL Server 2008R2 64 bit) where our application is running. There has been a gradual decrease in the performance of the application. Now it has come to a point where performance has become a serious concern. I started to investigate and went through the following steps.

Checked the Processor and Memory usage on the sql server (nothing is maxed out) plenty of free resources.
Index fragmentation was minimum yet rebuilt the indexes and updated statistics.
No changes has been made to the code in application (application code/sql server code) hence poor performance because of the poorly written code is unlikely to be the cause of overall poor performance of the application.
Finally got Paul Randal’s script Wait statistics, or please tell me where it hurts. The result of the script shows that sql server has to wait a lot when writing to log file. The biggest wait type is WRITELOG.

I used the SQLIO tool to measure the disk Read/Write performance and it seems as fast as any other disk.

I know for a fact I suspect that it is more likely that there is something wrong with the log write process, but I am running out of places to look for, can someone please advise me where else I should look for issues.

What can be the possible causes of slow log writes? or any advice or pointers in the right direction are much appreciated. Thank you.

╔═══════════════╦═════════════╦═══════════╗
║   WaitType    ║   Wait_S    ║ WaitCount ║
╠═══════════════╬═════════════╬═══════════╣
║ IO_COMPLETION ║ 4850500.20  ║ 4553514   ║
║ WRITELOG      ║ 25291893.90 ║ 795877    ║
╚═══════════════╩═════════════╩═══════════╝


texts: How can I solve postgresql problem after deleting wal files?

texts:
I turned the archive_mode on on my postgresql configuration for testing a backup server . And since the wal files took lots of disk space, after my test I turned it off and also deleted the wal files. When I tried to restart postgresql I got the following error.

 root@hooshang:/etc/postgresql/9.1/main# /etc/init.d/postgresql restart
 * Restarting PostgreSQL 9.1 database server
 * The PostgreSQL server failed to start. Please check the log output:
 2014-10-16 13:15:28 IRST LOG:  database system was shut down at 2014-10-15 15:51:53 IRST
 2014-10-16 13:15:28 IRST LOG:  could not open file "pg_xlog/00000001000007DC00000037" (log file 2012, segment 55): No such file or directory
 2014-10-16 13:15:28 IRST LOG:  invalid primary checkpoint record
 2014-10-16 13:15:28 IRST LOG:  could not open file "pg_xlog/00000001000007DC00000029" (log file 2012, segment 41): No such file or directory
 2014-10-16 13:15:28 IRST LOG:  invalid secondary checkpoint record
 2014-10-16 13:15:28 IRST PANIC:  could not locate a valid checkpoint record
 2014-10-16 13:15:28 IRST LOG:  startup process (PID 17467) was terminated by signal 6: Aborted
 2014-10-16 13:15:28 IRST LOG:  aborting startup due to startup process failure

How can I fix this issue?


texts:
I have to design a database covering business assumptions using MS SQL-Server 2008 R2. The current data of the tables will be exported to text files and used as input for a certain application. I would like to implement the tables as bitemporal tables as described by R. Snodgrass in order to track valid and transaction times.

However, it is possible and quite likely that the logical schema of the tables could change, i.e. new columns could be added or old columns could be removed.
How would I reflect this in the design of the tables?

Obviously, I couldn't really delete a column, as all old data would disappear. Should I 'flag' not available columns in rows with a NULL entry? Likewise, how would I keep track of columns which are currently in use? This would be crucial for the export of the data as only 'active' columns should be exported.

And last, but not least, how could I log that a new column was added or an old one removed?


texts: one node in cluster got crashed ,how to recover it?

texts:
we are using percona galera cluster with 3 nodes.
In my cluster one node got crashed due to low disk space ,we have cleared some log files and provided sufficient disk space.Now we are trying to start our cluster in that node.mysql is not up .when i go to error log i found below errors. i was tried with innodb_force_recovery=6 also but no luck. can anybody help me how to fix below issue.

error log

130919 12:55:15 [Note] WSREP: Closed send monitor.
130919 12:55:15 [Note] WSREP: view((empty))
130919 12:55:15 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1
130919 12:55:15 [Note] WSREP: gcomm: closed
130919 12:55:15 [Warning] WSREP: gcomm: backend already closed
130919 12:55:15 [ERROR] WSREP: gcs/src/gcs_fifo_lite.c:gcs_fifo_lite_close():70: Trying to close a closed FIFO
130919 12:55:15 [Note] WSREP: Flow-control interval: [16, 16]
130919 12:55:15 [Note] WSREP: Received NON-PRIMARY.
130919 12:55:15 [Note] WSREP: Shifting JOINED -> OPEN (TO: 66674748)
130919 12:55:15 [Note] WSREP: Received self-leave message.
130919 12:55:15 [Note] WSREP: Flow-control interval: [0, 0]
130919 12:55:15 [Note] WSREP: Received SELF-LEAVE. Closing connection.
130919 12:55:15 [Note] WSREP: Shifting OPEN -> CLOSED (TO: 66674748)
130919 12:55:15 [Note] WSREP: RECV thread exiting 0: Success
130919 12:55:15 [Note] WSREP: recv_thread() joined.
130919 12:55:15 [Note] WSREP: Closing replication queue.
130919 12:55:15 [Note] WSREP: Closing slave action queue.
130919 12:55:15 [ERROR] WSREP: gcs/src/gcs.c:gcs_close():1321: Failed to join recv_thread(): -3 (Unknown error 18446744073709551613)
130919 12:55:15 [Note] WSREP: Closing replication queue.
130919 12:55:15 [Note] WSREP: /usr/sbin/mysqld: Terminated.
130919 12:55:15 [ERROR] WSREP: gcs/src/gcs_fifo_lite.c:gcs_fifo_lite_close():70: Trying to close a closed FIFO
130919 12:55:15 [Note] WSREP: Closing slave action queue.
130919 12:55:16 mysqld_safe Number of processes running now: 0
130919 12:55:16 mysqld_safe WSREP: not restarting wsrep node automatically
130919 12:55:16 mysqld_safe mysqld from pid file /u01/mysql/folmobileqa2/log/mysqld.pid ended


texts: Execute special processing on calling table in hive

texts:
I want to set up so that when someone calls my table, it first does some processing to the table, and then later, restores the table when it is no longer being used. Is there a way to do this? I think I may be reaching, but I hope not. If not, then I'll have to do this manually.
For instance, I have a proprietary code reference that is part of the table in production, but I'm not allowed to have it stored in my test data. I want to translate this column as soon as the table is called and make it as seamless as if the actual table is called.
If that isn't possible, is it possible to concatenate two databases, treating them as one, and having the first database being used for the table in question? I've done this before in non-database processing, but not sure if hive can handle something like that.


texts: Restore Postgres 9.0 backup into Postgres 9.3

texts:
I'm having a problem restoring my Postgres 9.0 database into a 9.3 version.

I did the backup with this command:

pg_dump -U user database -f /root/mydb.sql

And when I send the backup to the new server and try to restore it I get this error:

psql.bin:/root/mydb.sql:5628157: invalid command \n
psql.bin:/root/mydb.sql:5628158: invalid command \n
psql.bin:/root/mydb.sql:5628159: invalid command \n
psql.bin:/root/mydb.sql:5628160: invalid command \n
psql.bin:/root/mydb.sql:5628161: invalid command \n
psql.bin:/root/mydb.sql:5628162: invalid command \n
psql.bin:/root/mydb.sql:5628163: invalid command \n
psql.bin:/root/mydb.sql:5628164: invalid command \n
psql.bin:/root/mydb.sql:5628165: invalid command \n
psql.bin:/root/mydb.sql:5628166: ERROR:  syntax error at or near "PageRange"
LINE 1: PageRange=1-2</PARAMETERS><image_type>high</image_type></SPO...
        ^
psql.bin:/root/mydb.sql:5628166: invalid command \n
psql.bin:/root/mydb.sql:5628167: invalid command \n
psql.bin:/root/mydb.sql:5628168: invalid command \n
psql.bin:/root/mydb.sql:5628169: invalid command \n
psql.bin:/root/mydb.sql:5628170: invalid command \n
psql.bin:/root/mydb.sql:5628171: invalid command \n
psql.bin:/root/mydb.sql:5628172: invalid command \n
psql.bin:/root/mydb.sql:5628173: invalid command \n
psql.bin:/root/mydb.sql:5628174: invalid command \n
psql.bin:/root/mydb.sql:5628175: invalid command \n
psql.bin:/root/mydb.sql:5628176: invalid command \n
psql.bin:/root/mydb.sql:5628177: invalid command \n
psql.bin:/root/mydb.sql:5628178: invalid command \n
psql.bin:/root/mydb.sql:5628179: invalid command \n
psql.bin:/root/mydb.sql:5628180: invalid command \n
psql.bin:/root/mydb.sql:5628181: invalid command \n
psql.bin:/root/mydb.sql:5628182: invalid command \n
psql.bin:/root/mydb.sql:5628183: invalid command \n
psql.bin:/root/mydb.sql:5628184: invalid command \n
psql.bin:/root/mydb.sql:5628185: invalid command \n
psql.bin:/root/mydb.sql:5628186: invalid command \n
psql.bin:/root/mydb.sql:5628187: invalid command \n
psql.bin:/root/mydb.sql:5628188: invalid command \n
psql.bin:/root/mydb.sql:5628189: invalid command \n
psql.bin:/root/mydb.sql:5628190: invalid command \n
^CCancel request sent
psql.bin:/root/mydb.sql:5628191: ERROR:  syntax error at or near "n"
LINE 1: n Berrocal_Ausonia3.psd</name><type>Foto_ALTA</type><mimetyp...

I'm trying to restore the database with this command:

psql -U user -d database -f /root/mydb.sql

When the restore finish I could see that there are a lot of information that doesn't exist so it's a big problem !

Can anybody help me?

Thanks a lot !


texts: Why only ~half of RAM is used by Oracle on Linux server?

texts:
Why Oracle 11.2 would be using only around half of available RAM on RHEL5 box? I have 64GB but 32GB is free which really annoys me. Any memory not used is wasted memory in my books - especially on Linux boxes.

Unfortunately I have no DBAs available currently so can't ask them...

host-rac-1|root[/root]# free -m
             total       used       free     shared    buffers     cached
Mem:         64455      34021      30434          0       1014       2958
-/+ buffers/cache:      30048      34407
Swap:        16415          0      16415
host-rac-1|root[/root]#

/dev/shm only used in 1%:

host-rac-1|root[/root]# df -m |grep tmpfs
tmpfs                    40960       254     40707   1% /dev/shm
host-rac-1|root[/root]#

meminfo:

host-rac-1|root[/root]# cat /proc/meminfo
MemTotal:     66002520 kB
MemFree:      31231544 kB
Buffers:       1039520 kB
Cached:        3022168 kB
SwapCached:          0 kB
Active:        5520172 kB
Inactive:      1245240 kB
HighTotal:           0 kB
HighFree:            0 kB
LowTotal:     66002520 kB
LowFree:      31231544 kB
SwapTotal:    16809976 kB
SwapFree:     16809976 kB
Dirty:            4452 kB
Writeback:           0 kB
AnonPages:     2754560 kB
Mapped:         346652 kB
Slab:           429768 kB
PageTables:     122116 kB
NFS_Unstable:        0 kB
Bounce:              0 kB
CommitLimit:  36169508 kB
Committed_AS:  5439032 kB
VmallocTotal: 34359738367 kB
VmallocUsed:    366812 kB
VmallocChunk: 34359371495 kB
HugePages_Total: 13322
HugePages_Free:    500
HugePages_Rsvd:    491
Hugepagesize:     2048 kB

Snippet from init.ora:

###########################################
# Miscellaneous
###########################################
compatible=11.2.0.0.0
db_unique_name=DBNAME
diagnostic_dest=/orabase/dbhome_1
memory_target=13497270272

###########################################
# Processes and Sessions
###########################################
processes=400
sessions=445

If there is anything I could add to improve this questions please let me know.


texts:
This morning, our SQL Server 2016 started using high CPU percentage(90%) when normally is about 30%.

Reviewing cache plan with sp_blitzcache, I saw a new plan for an old query in the top of CPU time.

Full query plan is here.

This plan had a warning about no join predicate.

(@TokenCentro nvarchar(36),@Tarjeta nvarchar(max) ,@TarjetaAux nvarchar(max) )
SELECT   Socios.id, Socios.id AS IdSocio, Centros.id AS IDCentro, Centros.nombre,
         ISNULL(NombreWEB, Centros.Nombre) AS NombreWeb, Socios.Token, Foto, ISNULL(Socios.IdIdioma, 1) AS IdIdioma,
         AplicacionPropia, ISNULL(Socios.IdConf_Regional, 1) AS IdUnidad, Socios_Asignar_Centro.AplicacionWEB,
         CASE WHEN Socios.Email = '' THEN 1
              ELSE CASE WHEN Socios.Email IS NULL THEN 1
                        ELSE 0
                   END
         END AS ConEmail, Centros.HorarioVisible, Centros.PerfilApp, TactilEntrenoSocio, Centros.ColorKiosco,
         Conf_Regional.PrimerDia, ISNULL(Centros.IdGrupoApp, 0) AS IdGrupoApp, Centros.requiere_LOPD,
         ISNULL(Centros.LOPD, '') AS LOPD, Socios.aceptada_lopd, Centros.IntegracionDieta, Empresas.Chat, Centros.field,
         Centros.valoracionApp, Empresas.p_Premios, Centros.Reservas, ISNULL(CustomCss, '') AS CustomCss,
         Socios_Asignar_Centro.PuedeReservar, zonasHorarias.Zona, zonasHorarias.ZonaIana,
         ISNULL(Socios.TokenExten, '') AS TokenExtern, Centros.field1, Centros.tipoServicioPush,
         Socios.Nombre AS NombreSocio, Socios.Apellidos AS ApellidosSocio, Centros_Opciones.forzarLogin
FROM     Socios
INNER JOIN Socios_Asignar_Centro
ON Socios_Asignar_Centro.IdSocio = Socios.Id
INNER JOIN Conf_Regional
ON Conf_Regional.Id = Socios.IdConf_Regional
INNER JOIN Centros
ON Centros.Id = Socios_Asignar_Centro.IdCentro
   AND Centros.FechaBaja IS NULL
INNER JOIN Empresas
ON Empresas.Id = Centros.IdEmpresa
INNER JOIN zonasHorarias
ON Socios.idzonahoraria = zonasHorarias.id
INNER JOIN Centros_Opciones
ON Centros_Opciones.IdCentro = Centros.Id
INNER JOIN Centros_Tactiles
ON Centros_Tactiles.IdCentro = Centros.Id
WHERE    Socios.tarjeta IS NOT NULL
         AND Socios.tarjeta <> ''
         AND Socios.tarjeta <> '0'
         AND (
                 Socios.Tarjeta = @Tarjeta
                 OR Socios.Tarjeta = @TarjetaExtend )
         AND Centros_Tactiles.Token = @Token
         AND (
                 Socios_Asignar_Centro.FechaBaja IS NULL
                 OR Socios_Asignar_Centro.FechaBaja > CONVERT(DATE, GETUTCDATE()))
ORDER BY IdCentro, IdSocio DESC

After changing the length of the parameter from nvarchar(max) to nvarchar(50), the new plan was normal again. However, I can not see where the problem is.

Any ideas?


texts:
Why does the table have two identical statistics? The first is a consequence of the nonclustered index, the second is created by the server.
The histograms are exactly the same. Only diference is that second one(auto created) is created five minutes after first one.
The index consists of one field of type guid.
Statistics for INDEX 'IX_LogEvents_Guid'.
                        Updated                            Rows                    Rows Sampled                           Steps                         Density              Average Key Length                    String Index
    Mar 18 2021  1:54AM                        24349284                        24349284                             188                               1                        34.71614                             YES                                                        24349284                               0

                 All Density                  Average Length                         Columns
                4.495376E-08                        34.71614                          acGUID

             Histogram Steps
                RANGE_HI_KEY                      RANGE_ROWS                         EQ_ROWS             DISTINCT_RANGE_ROWS                  AVG_RANGE_ROWS
                                                          0                         2104204


texts: Very slow query on table with Blob column

texts:
I have a table with approximately 3500 rows.  This table contains a longblob column with an average blob size of 750kb.  My query performance really deteriorates when trying to filter on the primary key column.

Select * from tblphotos  yields a response in 1 second, returns 3500 rows
Select * from tblphotos where ActLogId = 20992   yields a response in 130 seconds, returns 6 rows.

I'm looking for any sort of rationale as to why this is so slow and how to make this quicker.  Moving from ROW_FORMAT=COMPACT to DYNAMIC improved performance about 30%.

CREATE TABLE `tblphotos` (
  `PhotoID` int(10) NOT NULL,
  `ActLogID` int(10) DEFAULT NULL,
  `Order` smallint(5) DEFAULT NULL,
  `PhotoFile` longblob,
  `PhotoA` varchar(5) DEFAULT NULL,
  `Caption` varchar(100) DEFAULT NULL,
  `Entered` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

--
-- Dumping data for table `tblphotos`
--

INSERT INTO `tblphotos` (`PhotoID`, `ActLogID`, `Order`, `PhotoFile`, `PhotoA`, `Caption`, `Entered`) VALUES
(1, 19264, 1, blobvalued, 'JPG', 'Nameplate', NULL);

ALTER TABLE `tblphotos`
  ADD PRIMARY KEY (`PhotoID`),
  ADD KEY `ActLogID` (`PhotoID`,`ActLogID`,`Order`,`PhotoA`,`Caption`,`Entered`,`PhotoFile`(20)) USING BTREE;

-- AUTO_INCREMENT for table `tblphotos`
--
ALTER TABLE `tblphotos`
  MODIFY `PhotoID` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3421;


texts:
Same question has been asked here but its not helping me out.

In my python utility, I'm trying to take MySQL database backup using mysqldump. It runs successfully but I'm getting 0kb file in my backup folder.

This is my code;

DB_HOST = '192.168.1.1'
DB_USER = 'root'
DB_USER_PASSWORD = '_root_user_password_'
DB_NAME = 'db_name'
BACKUP_PATH = '/backup/dbbackup/'

DATETIME = time.strftime('%m%d%Y-%H%M%S')
TODAYBACKUPPATH = BACKUP_PATH + DATETIME

dumpcmd = "mysqldump -h " + DB_HOST + " -u " + DB_USER + " -p" + DB_USER_PASSWORD + " " + db + " > " + TODAYBACKUPPATH + "/" + db + ".sql"

os.system(dumpcmd)

Can anyone guide me out that what I'm missing here??


texts:
I have a Mongo service with which multiple hosts are interacting. The host on which the Mongo service is running is quite special -- it has 3TB of RAM. However, that host also throws intermittent Bus Error responses. If a Bus Error occurs within the Mongo service process, the service is halted and all celery (Python) processes that are interacting with the resource are served Connection Refused responses.

Is there a way to allow the Mongo service to somehow recover from a Bus Error? Can sharding help with this problem? Is there some other potential solution to the Bus Error that can be made at the application configuration level? I'd be grateful for any suggestions others can offer on this question!

I built Mongo from source on RedHat so I can use any recent version if that helps. The currently installed version is 3.6.4.


texts: Oracle: How to create a "reverse" index primary key?

texts:
I can create a primary key for a table and then reverse it with the command

alter index T_PK rebuild reverse online;

How can I create it reversed?


texts:
What is the difference between the return values for these two expressions in MySQL:

date('2011-01-01')

cast('2011-01-01' AS DATE)


texts: How to create a SQL Server 2008 backup without logs with mirroring

texts:
How do I create a backup of a SQL Server 2008 in a mirrored setup without any logs?

We need to do this so that when we restore our local development databases, we would have the most up to date data, but we don't necessarily have the space for all the transaction logs. (we're all using ssd's).

Currently, because it is mirrored, I can't create simple backups, it must be full backups.


texts: Create rows groups by using subgroups limits

texts:
I had originally posted this in SO but reasoned that it would be more appropriate here.

I have a view in an sqlite database such as this:

SQL Fiddle
(http://www.sqlfiddle.com/#!5/ae95b/1)

This represents a list of boxes and items that go into each box. The items are always ordered by box_start and the ranges box_start and box_end never overlap.

As shown in the first row there's a box which can store items with codes from 1 inclusive to 3 inclusive. For example the items that would go in the box A would be 'a' and 'b'.
The box-items grouping is denoted by the coloring, rows with the same color means that those are a group.
There are also items without a designated box (box label is null), such as items 'c', 'f', 'g', 'h'.

I need to write a query, if it is possible at all, to create ad-hoc boxes for items without a box and group contiguous items into the same box as follows:

As seen in the image the item 'c' and its corresponding box were labeled with '4-4' and the previously unassigned items 'f', 'g', 'h' are now grouped under the same box labeled '8-10' which corresponds to 'min(box_start of f,g,h)-max(box_end of f,g,h)'

I not sure how to do this in SQLite. I thought about using somekind of recursive query with CTEs but don't have a clue how to do it.


texts:
In my application, with a DB running on SQL Server 2012, I've got a job (scheduled task) that periodically executes an expensive query and writes the results to a table that can later be queried by the application.

Ideally, I would like to run that expensive query only if something changed since the query has last executed. Since the source tables are very big, I cannot just select a checksum over all candidate columns or something like that.

I've got the following ideas:

Explicitly write a last changed timestamp, a "must be queries" flag, or something like this to a tracking table whenever I change something in a source table.
Use a trigger to do the same.

However, I'd really like to know whether there is a lightweight way to detect changes on a table without me explicitly tracking the writes. Can I, for example, get the "current" ROWVERSION of a table or something like that?


texts: What's default path for "db2move export" command's output in Linux RedHat 7.2 and how to change it to specific folder?


texts: Get products of category plus all child categories till leaf nodes

texts:
We have a hierarchy which looks this:

Now the problem is that most products are only connected with 1 category, which is most of the time the lowest level.

I am trying to get ALL products below a given category. So If select one category, I want ALL products from current category and ALL categories below.
So this didn't work:

SELECT "category"."id"
    ,"products"."name"
FROM "products"
INNER JOIN "product_categories" ON "products"."id" = "product_categories"."product_id"
INNER JOIN "categories" ON "product_categories"."category_id" = "categories"."id"
WHERE "categories"."parent_id" = 0

Anyone who can help me out? BTW, we are using PostgreSQL

EDIT:

We are using "PostgreSQL 9.3.5"
And these are the tables for this case, you can see in my above query (and below ERD) which connections I would like to accomplish.

EDIT 2:
This was my query for mysql (here we use taxonomies and not categories):

SELECT T2.id, T2.taxonomy, T2.parent, T2.terms_id, terms.term
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent FROM term_taxonomies WHERE id = _id) AS parent,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 1, @l := 0) vars,
        term_taxonomies m
    WHERE @r <> 0) T1
JOIN term_taxonomies T2
ON T1._id = T2.id
JOIN terms ON T2.terms_id = terms.id
ORDER BY T1.lvl DESC;

But this only works for mysql


texts:
I'm trying to come up with a mysql boolean search that matches "monkey" but ignores / doesn't take into account any matches of "monkey business".

Something like the following is the general idea but it negates the value. I need it to not match rows with "monkey business" unless they also have "monkey" without "business" after it elsewhere in the text.

SELECT * FROM table
MATCH (title) against ('+monkey ~"monkey business"' IN BOOLEAN MODE);

Is it possible? Thanks in advance for any help you can give! I realise this could be done with better search engines, at the moment fulltext is all I can use.


texts: Using aggregate function on data masked column returns zeroes

texts:
Recently we have implemented dynamic data masking in SQL Server 2019 database in order to hide sensitive information from developers. However, for testing purposes I'd like them to see close-to-real values, so my mask looks like this:
CREATE TABLE fin.Salaries
(
    TargetMonth    DATE            NOT NULL,
    Login          VARCHAR(100)    NOT NULL,
    Department     VARCHAR(100)    NOT NULL,
    AmountUSD      MONEY           MASKED WITH (FUNCTION = 'random(500, 5000)') NOT NULL,
    CONSTRAINT PK_Salaries PRIMARY KEY CLUSTERED (TargetMonth, Login, Department)
)

It works fine for simple queries, however aggregate functions seem to always return zero values. I haven't found anything on this in the documentation, so the question is whether this is an intended behavior and is there any way to get non-zero values from aggregated data masked column?
SELECT   TargetMonth,
         AVG(AmountUSD) -- Returns all zeroes
FROM     fin.Salaries
GROUP BY TargetMonth


texts: Identify whether the given servers time is in daylight saving or not

texts:
I have to get local date data across many servers scattered around the globe and identify whether it is in daylight saving mode or not ?

Need help.


texts: Postgres - manual over-riding the default locks?

texts:
Is it possible to over-ride the locks that postgres chooses to create?

Here's the situation, I have a table something like this....

CREATE TABLE foo (
  recId        SERIAL PRIMARY KEY,
  recordTime   TIMESTAMP,
  fieldA.....
  fieldB....
  fieldC....
)

The table is appended to almost continuously throughout the day. (all day everyday) where recordTime is the time the record is added

Once a day I need to run a query to UPDATE the same table. Looks something like this...

WITH subquery AS (
                   SELECT blah, blah, blah
                   FROM foo, bar, etc)
UPDATE foo
SET fieldX = subquery.result1,
    fieldY = subquery.result2
WHERE foo.recId = subquery.recId
AND recordTime = yesterday

So the query is basically both selecting from and updating to the same table.

The UPDATE query runs just fine (takes about 5 minutes) when it has exclusive access to the table.

The problem is that in a live environment table foo is continuously being appended to, so the query can't run because it can't obtain a full lock on it (I think that's the problem - my understanding of deadlocks is hazy)

The UPDATE query is designed to process yesterdays records only, so to my way of thinking there's no reason it needs a full lock on the table. Is there anyway to allow the addition of new records whilst allowing the updating of yesterdays records? I could probably partition the table by day to achieve this, but was hoping to avoid this if there's a simpler solution.

thanks

Update in response to comments. Ok, I think it's back to the drawing board for me on this one. further tests show that with exclusive access to the DB the update query takes 4-5 minutes. With shared access to the database it either takes about 6 hours (9 times out of 10) or about 5 minutes. There are no reported errors.


texts:
I have table products, in which there is an hstore array field countries_of_origin.

So a product that is assembled from multiple products might have multiple countries of origin:

[
  { "country" => "USA" },
  { "country" => "MEX" }
]

My only issue is, I have no idea whatsoever on how to write a SQL update. Our developers interact with the table via an ORM. But I sometimes have to do a mass update for expediency's sake, and I don't know to do it!

Thanks for any help you can offer.


texts: SQL Server - How does Always On in a shared nothing config maintain transaction durability during a failover?

texts:
I've been reading up on Always On, and I was wondering how transactional durability is maintained during a failover when using a shared nothing configuration.

The only things I can find on google talk about log shipping, but this would mean that if a failover occurs in-between log restores, then some transactions could be lost.


texts: MySQL Replication Duplicate Entry for Primary Key

texts:
160523 13:07:29 [ERROR] Slave SQL: Error 'Duplicate entry '914166' for key
'PRIMARY'' on query. Default database: 'zo_dev_20121216'. Query: 'UPDATE
   activity
SET
   activity_type_id = 9,
   subject = 'Send departure email',
   date_due = '2016-05-26 01:00',
   date_start = '2016-05-23 01:00',
   activity_status_id = 1,
   content = 'Send departure email'
WHERE
   id = 2888555', Error_code: 1062
160523 13:07:29 [Warning] Slave: Duplicate entry '914166' for key 'PRIMARY'
Error_code: 1062
160523 13:07:29 [ERROR] Error running query, slave SQL thread aborted. Fix
the problem, and restart the slave SQL thread with "SLAVE START". We stopped
at log 'mysql-bin.000004' position 14847360

Many people have suggested to use slave-skip-error option etc.. but that will completely avoid the query. How do you actually continue with the query that poses the error ? Also, I could not find a good answer as to why this actully occurs and what the number "914166" indicates ? Can someone please explain ?


texts: Slow query on mid-size tables with good index coverage

texts:
I'm running a query that touches 2 moderately sized tables (1-5 million rows). EXPLAIN shows it touches only indexes and very few rows, however it takes very long to complete (~8 seconds). The only thing that might be of concern is the temporary table it creates. If anyone has any ideas, it would be appreciated!

Query:

SELECT `visits`.`id`
FROM `visits`
INNER JOIN `visits_views` ON visits_views.visit_id=visits.id
GROUP BY `visits`.`id`
ORDER BY `visits`.`created` DESC
LIMIT 20

EXPLAIN:

id  select_type  table          type    possible_keys   key     key_len     ref         rows    Extra
1   SIMPLE       visits         index   PRIMARY     created     4           NULL        20  Using index; Using temporary
1   SIMPLE       visits_views   ref     visit_id    visit_id    4           visits.id   1   Using index

Table structure

CREATE TABLE `visits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `affiliate_id` int(11) DEFAULT NULL,
  `referer` varchar(250) DEFAULT NULL,
  `domain` varchar(70) DEFAULT NULL,
  `user_agent` varchar(300) DEFAULT NULL,
  `country` char(2) DEFAULT NULL,
  `ip` int(10) unsigned DEFAULT NULL,
  `bot` tinyint(1) NOT NULL DEFAULT '0',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `domain` (`domain`),
  KEY `created` (`created`),
  KEY `referer` (`referer`),
  KEY `ip` (`ip`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `visits_views` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `visit_id` int(11) NOT NULL,
  `url` varchar(250) NOT NULL,
  `prev_id` int(11) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `visit_id` (`visit_id`),
  KEY `created` (`created`),
  KEY `url` (`url`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


texts: SQL Server: error 7303 (architecture mismatch)

texts:
I've searched a lot, but I still didn't find out a solution. I have to create a linked server to get the data from a mdb file (MS Access). I use the SQL Server 2012 Express x64 on the Windows 8.1 x64. I ran the odbcad x32 (the one that's inside the syswow64 folder), so I created the data source for this file:

But when I try to create the linked server I got the error:

Searching, I found out that the x64 SQL Server server can use any x32 driver. I can't see what I'm doing wrong.


texts: What does the term "projection" refer to in an SQL query (or view definition)

texts:
In Oracle's document The Query Optimizer, under View Merging, I found the following information

  The view merging optimization applies to views that contain only
  selections, projections, and joins. That is, mergeable views do not contain
  set operators, aggregate functions, DISTINCT, GROUP BY, CONNECT BY, and so on.
  (emphasis mine)

Yet, I can only guess as what such a projection actually refers to.


texts: Create Temporary Index for bulk operation and Delete after its completed

texts:
I am moving bulk data to archive tables.

Some times query returns up to 4.5million records, which takes about 2:15 minutes for a table of 40 columns.

I want too increase the performance for the selection by using a temporary non clustered index and drop that index after select operation.

I am also setting data base logging to bulk logging while performing the bulk operation.

Will that really help, or it is even possible o not.

Any other suggestions are also welcome.


texts:
My collection.stats():

{
    "ns" : "csdbprotobuf.archive",
    "count" : 429787895,
    "size" : 48374032272,
    "avgObjSize" : 112,
    "storageSize" : 56527326864,
    "numExtents" : 47,
    "nindexes" : 1,
    "lastExtentSize" : 2146426864,
    "paddingFactor" : 1,
    "systemFlags" : 1,
    "userFlags" : 1,
    "totalIndexSize" : 19917512720,
    "indexSizes" : {
        "_id_" : 19917512720
    },
    "ok" : 1
}

paddingFactor is 1, avgObjSize is 112. But the average document size, which I can see using Object.bsonsize() function, is 73.4 bytes. I have no updates, only inserts and deletes. How can I optimize space consumption? How much RAM does each document consume: 112 or 73.4 bytes?

Upd: How many documents can be stored in a one page: <page size>/<avgObjSize from db.stats()> or <page size>/<avg size from Object.bsonsize()>, i.e 4096/112 or 4096/73.4?


texts: search xml column inside large table (7 million rows)

texts:
Database design:

Table Name : Plays ( 6 million records)

Columns: Play id, col1, col2......col7, PlayFieldValues

PlayFieldValues xml column. sample xml stored in this column

<PlayAttributes>
  <PlayFields>
    <PlayField ID="Play.GenericInt1" >25</PlayField>
    <PlayField ID="X" TS="">0</PlayField>
    <PlayField ID="Y" >OFF</PlayField>
  </PlayFields>
</PlayAttributes>

Each xml has more than 25 elements

Using following query I am returning all Playid's from plays table having "Play.GenericInt1" attribute value 25 (we can add more conditions in where clause. This is just an example.)

Select

playId
FROM
(
SELECT playId, x.v.value('@ID[1]', 'VARCHAR(100)') AS playfield ,
x.v.value('.', 'VARCHAR(100)') AS playfieldvalue
from plays
cross apply PlayFieldValues.nodes('/PlayAttributes/PlayFields/PlayField') x(v)
)T

where
T.playfield in('Play.GenericInt1') and T.playfieldvalue=25

Problem: This query is working great on plays table with 1k or 2k rows, but it is not scaling when running on 5 million rows table. execution time is crossing 20 min. Please advice what types of methods and procedures in sql I can use to search the xml column. I tried XML indexes but it costing us lot of space and maintenance problems.


texts: PostgreSQL select count with dynamic date range

texts:
Firstly, I am new to this forum. I hope that someone can help or advise me on my issue.

Given a User createdAt date and the current date. I would like to retrieve the sum of Friends records for the user with 10 linear time intervals between these dates.

So if the createdAt was 30 days ago then I want the sum of users Friends over EVERY 3 day interval.

User Table

╔════╤══════════╤════════════════════════════╗
║ id │ name     │ createdAt                  ║
╠════╪══════════╪════════════════════════════╣
║ 1  │ Timothee │ 2018-03-01 13:02:20.904+00 ║
╚════╧══════════╧════════════════════════════╝

Friends Table

╔════╤════════╤══════════╤════════════════════════════╗
║ id │ userId │ friendId │ createdAt                  ║
╠════╪════════╪══════════╪════════════════════════════╣
║ 1  │ 1      │ 234      │ 2018-03-20 15:41:51.779+00 ║
╟────┼────────┼──────────┼────────────────────────────╢
║ 2  │ 1      │ 254      │ 2018-03-20 16:16:34.698+00 ║
╟────┼────────┼──────────┼────────────────────────────╢
║ 3  │ 1      │ 288      │ 2018-03-15 15:17:39.907+00 ║
╟────┼────────┼──────────┼────────────────────────────╢
║ 4  │ 1      │ 293      │ 2018-03-07 16:15:49.379+00 ║
╚════╧════════╧══════════╧════════════════════════════╝

Expected Output

╔═╤═══════╤════════════════════════════╤════════════════════════════╗
║ │ count │ startDate                  │ endDate                    ║
╠═╪═══════╪════════════════════════════╪════════════════════════════╣
║ │ 0     │ 2018-03-01 13:02:20.904+00 │ 2018-03-03 13:02:20.904+00 ║
╟─┼───────┼────────────────────────────┼────────────────────────────╢
║ │ 0     │ 2018-03-03 13:02:20.904+00 │ 2018-03-05 13:02:20.904+00 ║
╟─┼───────┼────────────────────────────┼────────────────────────────╢
║ │ 0     │ 2018-03-05 13:02:20.904+00 │ 2018-03-07 13:02:20.904+00 ║
╟─┼───────┼────────────────────────────┼────────────────────────────╢
║ │ 1     │ 2018-03-07 13:02:20.904+00 │ 2018-03-09 13:02:20.904+00 ║
╟─┼───────┼────────────────────────────┼────────────────────────────╢
║ │ 1     │ 2018-03-09 13:02:20.904+00 │ 2018-03-11 13:02:20.904+00 ║
╟─┼───────┼────────────────────────────┼────────────────────────────╢
║ │ 1     │ 2018-03-11 13:02:20.904+00 │ 2018-03-13 13:02:20.904+00 ║
╟─┼───────┼────────────────────────────┼────────────────────────────╢
║ │ 1     │ 2018-03-13 13:02:20.904+00 │ 2018-03-15 13:02:20.904+00 ║
╟─┼───────┼────────────────────────────┼────────────────────────────╢
║ │ 2     │ 2018-03-15 13:02:20.904+00 │ 2018-03-17 13:02:20.904+00 ║
╟─┼───────┼────────────────────────────┼────────────────────────────╢
║ │ 2     │ 2018-03-17 13:02:20.904+00 │ 2018-03-19 13:02:20.904+00 ║
╟─┼───────┼────────────────────────────┼────────────────────────────╢
║ │ 4     │ 2018-03-19 13:02:20.904+00 │ 2018-03-21 13:02:20.904+00 ║
╚═╧═══════╧════════════════════════════╧════════════════════════════╝

I can get the desired day interval with the following SQL-Server Query:

@DECLARE @EndDate DATE = GETDATE();
WITH start as
(SELECT createdAt
 FROM user where id = '123'
)
SELECT diff/10
FROM (
    SELECT DATEDIFF(days, start, @EndDate)
) as diff

How would this be in PostgreSQL?

I also know that I can retrieve the count of records between a static date range with SQL-Server Query:

SELECT count(*)
FROM friends
WHERE userId = '123'
AND date BETWEEN CURDATE() - INTERVAL 3 DAY AND CURDATE()

How would this be in PostgreSQL?

But what I am struggling with is the combining of these two things.

How can I retrieve the sum of records over ALL date intervals between the two dates?

I hope the question is understandable, thank you for any help or advice.


texts: Possible to view historical login and database information from backup of master database

texts:
About a month ago, we migrated a production database and logins to a new instance.  The client is currently troubleshooting an issue and wants to confirm that the login permissions in the new instance match exactly what they were in the old instance.

We have long since deleted the old logins as well as the database that was migrated from the old instance.  However we have backups of the master database.  I though I could restore the master as a regular user database and query the views to peek at the server and database principals as they existed before the migration, but the views appear to be returning current information rather than historical.

So for one, there's a fundamental misunderstanding on my part regarding what is contained in the master database (or at least how the views work).  If anyone can shed some light on this, that would be great.  But mostly, does anyone know if what I'm trying to do is possible?

Edit:
As a test, I restored the same backup of the master database onto a completely different instance, naming the restored database:  master_temp.  Then I tried the following:

SELECT * FROM master_temp.sys.server_principals

The results that were returned only contained information from the instance the database was restored onto.  Not a single user login from the instance the backup was taken from was returned in the result set.


texts:
For a University course I've been given the following task

Suppose that we have an employee table like (first_name, last_name, salary, hire_date, ...). There is regulation which states that every employee each year after he/she is hired, must renew  his contract with  company. Show how many months remain before the next renewal for each employee.

This is my attempt:

select (abs(months_between(sysdate,hire_date)/12 -
         round((months_between(sysdate,e.hire_date))/12)))
from employees e


texts: Database design for stores with and without branches

texts:
I am building a database with primarily these two kind of stores:

a) Simple small family stores

b) Big and many branches stores

Those two kinds of stores are represented in a Stores table; with the b) case I'm grouping them by same store with the Branches table, something like this:

Stores table   -> 'The Apparel Store'; IDStore 1
Branches table -> 'California Branch'; IDBranch 1; IDStore 1
Branches table -> 'Miami Branch'; IDBranch 2; IDStore 1
Branches table -> 'Texas Branch'; IDBranch 3; IDSTore 1

The problem for me is, is this a good approach for the single and small businesses? i.e. an Ice Cream family store in a neighborhood; I did think about just having these kind of stores in the Stores table with just one record in Branches table, but I'm worried about the simplicity of the design.

Simplicity in the sense that, I will have thousands of single little stores, they are just perfectly representated with only the Stores table. With that being said, also there is a small possibility the little ice cream store have another branch, you never know.

Is this the most simple yet flexible design?