texts: Sync tables from Host1 to Host2 with over 50 million rows?

texts:
I apologize first for using Sync in the title, that may be the wrong term. I am using MySQL, but can switch to Postgress or other open source platform.

I have a service that when people subscribe to we receive a lot of data from them, around ~60,000 rows that gets split up across multiple tables using a http endpoint processed by php. After the initial sync, we only receive incremental updates.

Looking at server load, we became concerned that it would become an issue as we added more and more customers and so setup a separate server that would handle all the http requests, parse the data and place it in the right tables. As load increased, we could then section off customers to other identical servers.

The question is what should I do to effectively replicate the data on those finished tables over to the production server (same network)? I could setup triggers, but that would result in tens of thousands of individual connection requests for each insert.

If I setup were to setup mirroring / master-slave, I want it to ignore all existing data because I wouldn't want it to try comparing things that have already been sent on a regular basis since that could grow to 50 million rows quickly.

It would be more like :

SELECT @rowcount:=COUNT(*) FROM production.database.table1;
INSERT INTO production.table1 SELECT * FROM workhorse.database.table1 where `id` >@rowcount;

Something this simple could be run once a minute or so I would think and cut down on individual connections. I realize this is kind of messy since COUNT and ID aren't locked together, but it gets the idea across. Also, I don't know a way to run the query on a remote server.

Are there any tools/commands that would make quick work of this?


texts: Is MySQL smart enough to not do a full table scan for LIKE or NOT LIKE when there are other conditions?

texts:
I know that full wildcard searches (example: LIKE '%word%') cause a full table scan.

Is MySQL smart enough to only scan the results, not the entire table, when I use LIKE or NOT LIKE?

For example, say I have a query that selects 10 rows out of 1000000. The query is something like select * from table where id <= 10 and description NOT LIKE '%word%'. Is MySQL smart enough to only scan those 10 result rows?

(My query is very different but that's the gist of it: I want to use NOT LIKE on a result set from a large table. Assume everything is indexed. Client version "14.14 Distrib 5.7.29, for Linux (x86_64)", server version "5.7.29-0ubuntu0.16.04.1 for Linux on x86_64".)


texts: How does Amazon create the RDS for Postgres? Some kind of Docker?

texts:
I am really curious about the infrastructure of Amazon. When I create a new instance of RDS, it gives me a host, username, password.. Everything. Behind the curtains, how do they do this? It´s some infrastructure like Docker to run multiple instances of PostgreSQL? How do I replicate this?

My problem is: I have many users and I would like to let then manage their own tables. I thought that it could be done with schemas, but they still can see the other users tables, and I dont want that. Any sugestion?


texts: Always On best practice for secondary node maintenance

texts:
In my AG, I have two nodes using synchronous commit, automatic failover, allow all connections in primary role, and readable secondary as yes.  The failover cluster is using a fileshare witness as the quorum.  I have to reboot the secondary replica tonight.

What is the best practice for handling this type of maintenance?  Since I have quorum, can I just reboot the secondary?  Do I need to drain roles of the secondary in the cluster manager?  Is there an AG maintenance mode?


texts:
Forgive me if this is an inappropriate question for this forum (being DBA and all, I thought it may fit).
I have an SSIS package that I've recently upgraded and moved from BIDS to VS 2019.
It's a very straightforward process to create and write an annotation, however, there are a number of annotations on the flow diagram which I need to edit, but I simply cannot find an option to do this.  Does anyone know of a way, bar actually editing the XML (dtsx) file and manually using a text editor?
To Clarify...
This was all my fault for being a trendy developer and using a dark scheme on my VS install!  It turns out that you can't see the cursor as it's black on black.
Kudos to billinkc, below, for pointing out my stupidity!


texts: Postgres : prevent altering join condition in plan

texts:
I have a modified query from JOB benchmark:

SELECT *
FROM   company_type AS ct,
       info_type AS it,
       movie_companies AS mc,
       movie_info_idx AS mi_idx,
       title AS t
WHERE  ct.ct_kind = 'production companies'
AND    it.it_info = 'top 250 rank'
AND    t.t_production_year >2010
AND    ct.ct_id = mc.mc_company_type_id
AND    t.t_id = mc.mc_movie_id
AND    t.t_id = mi_idx.mii_movie_id
AND    it.it_id = mi_idx.mii_info_type_id;

Now optimizer is introducing condition

mc.mc_movie_id = mi_idx.mii_movie_id

in plan.

Here is the plan

Hash Join  (cost=127405.50..221937.06 rows=150 width=275)
    Hash Cond: (t.t_id = mc.mc_movie_id)
    ->  Seq Scan on title t  (cost=0.00..91054.20 rows=926896 width=94)
            Filter: (t_production_year > 2010)
    ->  Hash  (cost=127398.60..127398.60 rows=552 width=181)
            ->  Hash Join  (cost=37908.32..127398.60 rows=552 width=181)
                    Hash Cond: (mc.mc_movie_id = mi_idx.mii_movie_id)  // HERE
                    ->  Hash Join  (cost=18.93..89399.66 rows=20806 width=114)
                            Hash Cond: (mc.mc_company_type_id = ct.ct_id)
                            ->  Seq Scan on movie_companies mc  (cost=0.00..75323.67 rows=3693067 width=28)
                            ->  Hash  (cost=18.88..18.88 rows=4 width=86)
                                    ->  Seq Scan on company_type ct  (cost=0.00..18.88 rows=4 width=86)
                                            Filter: ((ct_kind)::text = 'production companies'::text)
                    ->  Hash  (cost=37676.66..37676.66 rows=17019 width=67)
                            ->  Hash Join  (cost=2.43..37676.66 rows=17019 width=67)
                                    Hash Cond: (mi_idx.mii_info_type_id = it.it_id)
                                    ->  Seq Scan on movie_info_idx mi_idx  (cost=0.00..30292.03 rows=1923203 width=49)
                                    ->  Hash  (cost=2.41..2.41 rows=1 width=18)
                                            ->  Seq Scan on info_type it  (cost=0.00..2.41 rows=1 width=18)
                                                    Filter: ((it_info)::text = 'top 250 rank'::text)

This condition

Hash Cond: (mc.mc_movie_id = mi_idx.mii_movie_id)

is not present in original sql query.

My question is, how can I prevent postgres from introducing this condition in plan?

The application is written in our lab as a proof of concept which assumes that there are only pk-fk joins in the database that's why I need it this way. The input to the application is the query plan.

I know it's better this way but I'm testing an application which only supports pk-fk joins. None of mc_movie_id or mii_movie_id is a pk.

Note: Using explicit JOIN syntax is not helping - the same plan is obtained.


texts: Storing a Billing Address Best Practice in Orders Table

texts:
Can someone help me understand this user's answer for a CustomerLocation table. I really want a good method for storing addresses in the orders table.

What I'm looking for is how I can set up my addresses so when I edit
them, the order is not effected by the fact that a customer updates
his address or relocates.

As it stands my schema looks similar to:

 Person           |EntityID|
 EntityAddress    |EntityID|AddressID|
 Address          |AddressID|AddressType|AddressLine1|AddressLine2|
 Order            |OrderID|BillingAddressID|


texts: AG groups don't sync user DB collation change to replica servers?

texts:
We have 2012 AG group set up between server A and server B, where A is the primary, B is the replica

1) When we changed the user DB collation on server A, on server B, the user DB still shows the old collation. Is that by design? So we would need to redo AG to make the change show on the replica servers?

2) When we change the columns and other objects' collation on server A, does that get changed automatically on the replica server through AG sync process?

3) Is there any advantage of having server collation being SQL_Latin1_General_CP1_CI_AI while the DBs being Latin1_General_CI_AI ?

4) Lastly, if the default DB collation is different than SOME of the table/column collation, would that be an issue for AG/DB in general?

Thank you!


texts: Is it possible to determine mysql pre-allocated memory footprint

texts:
At startup, mysql will use some memory.

Some of the memory is pre-allocated for "key_buffer" and "innodb_buffer_pool_size" ( and other things ).

Is it possible to determine how much memory is pre-allocated ( for data ) and how much memory has been used ?

Thanks

UPDATE:

Let me take an example to explain what I want to ask.
After I start mysql server, it use some memory as part of "key buffer". Say, I set key_buffer=1G. Maybe at startup, mysql server has pre-allocated 200M as "key buffer". Then when users begin to do queries against this server. The server can only get extra 800M as "key buffer".
200 is a random number I pick. And apparently besides key_buffer, there are other things ( such as innnodb buffer )  What I want to know is the exact size or the way to calculate it.

Hope I've explained myself clearly.


texts: MySQL default wait_timeout is 28800 but in my server I can see more than that value

texts:
Im using AWS MySQL Aurora, the current wait_timeout is 28800 which is the default one, and interactive_waitimeout is also 28800.

But I can see more than the default value.


texts:
How can we insert random values into a newly created column in a table in SQL Server?

I tried this:

alter table [dbo].[employee] add phoneno int

select * from [dbo].[employee]
update employee set phoneno=(ROUND((RAND(),0)) where phoneno NULL


texts:
How do I set a timestamp column whose default value is the current UTC time?

MySQL uses UTC_TIMESTAMP() function for UTC timestamp:

mysql> SELECT UTC_TIMESTAMP();
+---------------------+
| UTC_TIMESTAMP()     |
+---------------------+
| 2012-07-01 11:36:35 |
+---------------------+
1 row in set (0.00 sec)

So I've tried:

CREATE TABLE `blah` (
`creation_time` TIMESTAMP DEFAULT UTC_TIMESTAMP,
...

And other variations, like UTC_TIMESTAMP(), but without success.


texts: postgresql 9.3: two instances on same port and two different ip adresses don't work

texts:
i'm setting up multiple instances of postgres 9.3 on the same machine (centos 7). I have 2 virtual ip addresses, one instance is bound to first address and second is bound to second one. Same configuration is working on postgres 8.4 and very old red hat server.

I have configured 2 .service files in /usr/lib/systemd/system and i have done the setup initdb servicename commands.

the problem is: if i starts first service it goes well, but the second says that /tmp/.s.PGSQL.5432.lock file already exists.

in previuos db and os version i could set two different /var/run path in order to manage two instances,but now i cannot do it beacause i don't find any setting to write lock file where i want.

obviously if i change listening port on second instance all works well beacuse it create another lock file with different name,but i would to mantian same configuration. I don't know if in new version they delete the possibility to have 2 instances on same port and different ips but it seems strange to me.

how can i do?

thanks


texts: MongoDB - Killed when database disk is full

texts:
I'm using MongoDB 3.6.5 (already tested on 3.2 / 3.4) and i have enabled "directoryPerDB" in /etc/mongo.conf.

#mongod.conf

storage:
   dbPath: /var/lib/mongodb
   journal:
     enabled: true
   directoryPerDB: true

I created LVM disk's for my databases and symlinked in to /var/lib/mongodb

Here is problem:

When one of these lvm's is full, it will kill mongod service, so i can't use the rest of databases, where is free space on storage. Because of that, this will kill my primary node and subsequently the secondary node.

Is there some way to deal wiith it ?

Your help will be greatly appreciated!


texts: Exporting table data into csv format from server to local

texts:
I have connected to remote server and trying to export a table data to local using the command:

copy finance.coa to 'localusername@172.16.1.44:/home/kedarisettyakhiles/akki.csv' DELIMITER ',' CSV HEADER;

I am getting the error:

  relative path not allowed for COPY to file


texts: Select products and include all cities it belong to

texts:
I have 2 tables :

PRODUCTS
+----------+----------+----------+----------+
| id       |  title   |  content |   city   |
+----------+----------+----------+----------+

CITIES
+----------+----------+
| id       |  title   |
+----------+----------+

Where in my products table data stored like : (cities that a product belongs to separated by comma)

PRODUCTS
+----------+----------+----------+----------+
| id       |  title   |  content |   city   |
+----------+----------+----------+----------+
| 1        |  some    |  info    | 1,3,4,15

Now , how can i select products and all cities that it belongs to. And i want to search products by city's id too.

I've read some topics also this one , but mine is a bit different


texts:
I have a table with a geometry column. For one record, there is only one Point stored. A spatial index has been created but queries searching for the nearest location do not use this index resulting in bad performance.

Example script:

--Create the table
create table Location(
    LocationID int not null identity(1,1) primary key,
    LocationPoint geometry
)

--add records
declare @counter int =0
WHILE @counter<150000
BEGIN
    set nocount on
    --select
    set @counter =@counter +1
    declare @RandomLocation geometry=geometry::Point(RAND() *1000, RAND() *1000, 0)
    insert into Location(LocationPoint) values  (@RandomLocation)
END

--create index
CREATE SPATIAL INDEX SPATIAL_StructureBE ON dbo.Location(LocationPoint)
USING GEOMETRY_GRID WITH (
    BOUNDING_BOX  = (xmin  = 0.0, ymin  = 0.0, xmax  = 1000, ymax  = 1000),
    GRIDS = ( LEVEL_1  = MEDIUM, LEVEL_2  = MEDIUM, LEVEL_3  = MEDIUM, LEVEL_4  = MEDIUM),
    CELLS_PER_OBJECT  = 16,
    STATISTICS_NORECOMPUTE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON)

--Search, this query should use the index but it doesn't
declare @CurrentLocation geometry=geometry::Point(24,50, 0)
select top 1 *
from Location
order by LocationPoint.STDistance(@CurrentLocation) asc


texts: Receive and save attachments from emails using Database Mail

texts:
I know you used to be able to do this using the stored procedure 'sp_processmail' using SQL Mail but is there a way to do this now?

I am interested in solutions for SQL Server 2012 and 2014.


texts:
I am running postgresql 9.3.4. I have a table with 3 fields:

id   name  addr
---  ----  ----
1    n1    ad1
2    n2    ad2
...

I need to move the data to a new table with fields like:

id   data
---  ----
1    {'name': 'n1', 'addr': 'ad1'}
2    {'name': 'n2', 'addr': 'ad2'}
...

row_to_json is not the solution for me as SELECT t.id, row_to_json(t) as data FROM (select id, name, addr from myt) t adds id to the result as well. Is there a way to choose the fields I need (name & addr) in my data field?


texts: Error when upgrading SQL 2016 to SP2 - "The given key was not present in the dictionary."

texts:
I am trying to patch one node in a SQL Server 2016 Standard FCI.  I removed the node from the cluster, and ran the installer, but it failed.

The summary file contained the following exit code / message:

Exit code (Decimal):           -2146233079
Exit facility code:            19
Exit error code:               5385
Exit message:                  The given key was not present in the dictionary.

I cannot find anything obvious in the "Detail" file.

Can anyone recommend where I could go to get a clearer idea of what failed?


texts: MySQL: Queries limitations per host/user

texts:
In MySQL 5.7, is there a way to limit specific type of queries up to a certain limit? select queries for instance to be max 100 query at a time.

And is it doable to limit queries per host or user?


texts: Enforcing a "disjoint or identical" constraint

texts:
Suppose that F is an Oracle table with two columns, X and Y, such that the constraint

CONSTRAINT unique_xy_pairs UNIQUE (X, Y)

is satisfied.

For each value [x] of F.X, let S[x] represent the set consisting of the values returned by the following (pseudo-SQL) query:

SELECT Y FROM F WHERE X = [x];

Now, I want to enforce the constraint that for any two arbitrary values [x1] and [x2] of F.X, their corresponding sets S[x1] and S[x2] must be either disjoint or identical.

How can I do this?

EDIT: A uniqueness constraint on F.Y would not be a valid solution, since such a constraint could be violated by tables that nonetheless satisfy the constraint described in the original statement.

For example, the following is an F table that does not satisfy a unique F.Y constraint, but satisfies the "disjoint or identical" constraint in the original post:

 X  Y
10  0
10  1
20  2
20  3
20  4
30  0
30  1


texts: How can I create DB2 objects with Linux SUDOer authority?

texts:
All,

I am relatively new to Red hat linux and I have SUDOer authority but unable to create DB2 database or other objects. How can I accomplish this task? Please give examples.

Also, is there any known freeware or trial tool for converting mainframe databases and associated objects to DB2 LUW specifically on linux box?

Thank you

Thanks in advance for your response.


texts:
I have following columns:

key | value
-----------
foo | 1
bar | 2

Is there a recommended way to turn this into hstore?

{foo => 1, bar => 2}


texts: How to query/monitor index fragmentation?

texts:
I have been a DBA for 3 months now at my first job out of college and I am having a significant amount of difficulty trying to figure out how to accurately query and report on index fragmentation across 2 of our production servers (2 separate clusters, but that doesn't matter for this specific question... let's just pretend I have 1 server).

We use Ola Hallengren's scripts for index maintenance already, but I need to query all of our databases on our server and get the fragmentation % along with the table name, index name, and database name.

Here is my current query that doesn't seem to be correct:

SELECT s.[name] +'.'+t.[name]  AS table_name
 ,i.NAME AS index_name
 ,dbd.name AS [database_name]
 ,index_type_desc
 ,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent
 ,page_count
 FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]
INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id]
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id =   i.index_id)
INNER JOIN sys.databases AS dbd on dbd.database_id = ips.database_id
WHERE i.type IN (1,2) -- Include ONLY Clustered & Non-Clustered Indexes
AND s.name <> 'Audit' -- Exclude Audit Schemas
AND ips.page_count > 100 --Exclude small page counts
AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'
AND ips.index_level = 0 -- Current level of the index. 0 for index leaf
levels... Greater than 0 for nonleaf index levels.

Even when I remove the WHERE clauses it still seems my data is incorrect.  One of my records/tuples shows a table in a database with an index that has 1700 pages, but when I manually go to that index it only has 1?  I have been working on this for hours and can't seem to figure out what is going on.  I have read all of the documentation for this DMV.


texts: List all column names for multi-joined table

texts:
I have a slightly more complicated join:

SELECT person.given_name, person.family_name, person.age, person.sex, person.state,
       person.zip, person.street, person.residential_number,
       c1.name as citizen_of_country,
       c2.name as hospitalization_country,
       c3.name as infection_country

FROM patients

    JOIN person on patients.person_id = person.id
    JOIN country c1 on person."citizenOf_country_id" = c1.id
    JOIN country c2 on patients.hospitalized_in_country_id = c2.id
    JOIN country c3 on patients.infected_in_country_id = c3.id;

.. and I'd like to somehow get the name of all columns in this new table. Based on some answers I found, I tried

SELECT DISTINCT column_name FROM information_schema.columns WHERE table_schema = 'public' AND [code];

Where [code] is the block above.. But it didn't work, giving the error "Subquery must return only one column". I'm new to databases so I'm not sure how to handle this correctly.


texts: SQL Server 2012 autoupdate statistics default behaviour

texts:
I found, that since SQL Server 2008 R2 there is a trace flag 2317 available. It turns on dynamical autoupdate statistics threshold, instead of default threshold of 20%. So, it greatly improves performance on systems with high tables (from 1 billion rows), because it could take a long time to reach 20% of rows modified. More of this here: http://support.microsoft.com/kb/2754171

Secondly, I found few unofficial topics, which says, that this trace flag is turned on by default since SQL Server 2012. But I can’t find any proof of this from vendor, Microsoft.

So, the question is: does anybody know anything about any changes on autoupdate stats default behavior since SQL Server 2012? Any link to BOL or MSDN would be appreciated.


texts:
Just wondering if I could solicit some feedback on a stored procedure I'm running and whether there's a more efficient way of handling the scenario (I'm pretty sure there will be!).

Basically I have a single SP that I call to return a list of records (Jobs) that may have one or more statuses and a sort order (I'm using RowNum for paging). At the moment I'm using WITH RECOMPILE because the variations on the statuses can change all the time (depending on user etc). There's also some filtering going on.

I'm using an IF statement to essentially run the same bit of code with the only change being the sort order.

I guess my questions are: Is there a better way of doing this (maybe different SP's for different statuses)? Am I overcomplicating things due to lack of knowledge (quite likely) Is the SP actually ok, but requires minor tweaks to reduce the number of lines?

I've pasted a portion of the SP below - the only difference to the full code is the additional IF statements for the different sort orders...

I'd appreciate any feedback.

Thanks in advance!

PROCEDURE [dbo].[sp_Jobs]

@PageNumber int,
@PageSize int,
@FilterExpression varchar(500),
@OrderBy varchar(50),
@CustomerID int,
@ShowNotSet bit,
@ShowPlaced bit,
@ShowProofed bit,
@ShowReProofed bit,
@ShowApproved bit,
@ShowOnTime bit,
@ShowLate bit,
@ShowProblem bit,
@ShowCompleted bit,
@ShowDispatched bit,
@ShowUnapproved bit,
@ShowClosed bit,
@ShowReturned bit,
@UserID int

WITH RECOMPILE

AS

--JobNumber DESC
if @OrderBy='JobNumberDESC'
BEGIN

WITH Keys AS (SELECT TOP (@PageNumber * @PageSize) ROW_NUMBER() OVER (ORDER BY JobNumber DESC) as rn,P1.jobNumber,P1.CustID,P1.DateIn,P1.DateDue,P1.DateOut,p1.client,p1.MasterJobStatusID,p1.MasterJobStatusTimestamp,p1.OwnerID

FROM
vw_Jobs_List P1 WITH (NOLOCK)

WHERE
(@CustomerID = 0 OR CustID = @CustomerID)
AND (@UserID = 0 OR OwnerID = @UserID)
AND ((@ShowNotSet = 1 AND MasterJobStatusID=1) OR (@ShowPlaced = 1 AND MasterJobStatusID=2) OR (@ShowProofed = 1 AND MasterJobStatusID=3) OR (@ShowReProofed = 1 AND MasterJobStatusID=4) OR (@ShowApproved = 1 AND MasterJobStatusID=5) OR (@ShowOnTime = 1 AND MasterJobStatusID=6) OR (@ShowLate = 1 AND MasterJobStatusID=7) OR (@ShowProblem = 1 AND MasterJobStatusID=8) OR (@ShowCompleted = 1 AND MasterJobStatusID=9) OR (@ShowDispatched = 1 AND MasterJobStatusID=10) OR (@ShowUnapproved = 1 AND MasterJobStatusID=11) OR (@ShowClosed = 1 AND MasterJobStatusID=12) OR (@ShowReturned = 1 AND MasterJobStatusID=13)) AND (Search LIKE '%'+@FilterExpression+'%')

ORDER BY
P1.JobNumber DESC ),SelectedKeys AS (
SELECT TOP (@PageSize)SK.rn,SK.JobNumber,SK.CustID,SK.DateIn,SK.DateDue,SK.DateOut

FROM
Keys SK

WHERE
SK.rn > ((@PageNumber-1) * @PageSize)

ORDER BY
SK.JobNumber DESC)

SELECT SK.rn,J.JobNumber,J.OwnerID,J.Description,J.Client,SK.CustID,OrderNumber, CAST(DateAdd(d, -2, CAST(isnull(SK.DateIn,0) AS DateTime)) AS nvarchar) AS DateIn, CAST(DateAdd(d, -2, CAST(isnull(SK.DateDue,0) AS DateTime)) AS nvarchar) AS DateDue,CAST(DateAdd(d, -2, CAST(isnull(SK.DateOut,0) AS DateTime)) AS nvarchar) AS DateOut, Del_Method,Ticket#, InvoiceEmailed, InvoicePrinted, InvoiceExported, InvoiceComplete, JobStatus,j.MasterJobStatusID,j.MasterJobStatusTimestamp,js.MasterJobStatus

FROM SelectedKeys SK JOIN vw_Jobs_List J WITH (NOLOCK) ON j.JobNumber=SK.JobNumber JOIN tbl_SYSTEM_MasterJobStatus js WITH (NOLOCK) ON j.MasterJobStatusID=js.MasterJobStatusID

ORDER BY
SK.JobNumber DESC
END

--ELSE IF for other column sorting


texts:
How would you do the ordering of the PK columns in this case (Oracle 11gR2 Standard Edition):

I have a database where I offer the same application to many users. I want to be perfectly sure that all tenants have separated data and that under no circumstances leaving out a WHERE (by user or persistence framework mistake) can show customer data to another customer. Also, I want to have as few maintenance as possible, so I do not want to have the same schema over and over again (because then, in order to make perfectly sure that all the environments are the same, one has neverending schema compare marathons).

My solution:

1 data schema with column "tenant_id" in every table and every view.
For every customer a view VTxxx_tablename or VTxxx_viewname in the form "SELECT * FROM underlyingobject WHERE tenant_id = x WITH CHECK
OPTION" with Select/Insert/Update/Delete-grants.
n customer schemas with no data.
For every of the n tenants synonyms to their granted views in their no-data schemas.
All of the above generated (and re-generated) by script.

Result:

All tables have a PK of (tenant_id, id) (or: (id, tenant_id), this is the question)
All FKs obviously include the tenant_id column, therefore no mixing of customer data possible.

The data:

"normal" OLTP-application (everything normalized, many JOINs in the SELECTs)
tenant_id is currently from [1,2,3], but might eventually get into the range 1..100.
ID is filled by one sequence per table.

The access:

All access is though the views, therefore every SELECT has an implicit WHERE tenant_id = x
Many SELECTs have a WHERE clause with full PK (implicit tenant_id through the used VIEW plus extra id like in WHERE tenant_id = x AND id = y)

Right now I decided for PK-column order (tenant_id, id).

Pro:

when ID column not present, INDEX SKIP SCAN or INDEX RANGE SCAN is possible
As no Oracle EE is available, this is my "poor mans partitioning" (downside below)

Con:

assuming the the application is used the same amount by all tenants, the PK has to be reorganized pretty much / for every insert, as low tenant_id rows are added as well.

What would you do in this case and why:

PK (tenant_id, id)
PK (id, tenant_id)
own schema (with own tablespace) per tenant nevertheless (more admin work (schema compare), better speed, simpler SQLs)

I'm looking for Oracle related physical differences only (like SCANs, Index Update costs or more general read costs/bottlenecks against write costs/bottlenecks)

Thank you,
Blama


texts:
At my work we have three different environments: dev, test, and prod.  We also quite a few different SSIS packages.  Currently, when we need to deploy to a particular environment, we update the connection for the package in Visual Studios and deploy it to the correct environment.  I'd like to make this easier.  I'd like to pass in the environment name, the server name, and the database name and have a parameterized connection string in order to run the package correctly.

I'm currently not allowed to set up an Integration Services Catalog for these environments and variables.  The only options I have is to somehow pass in the parameters when I set up the SQL Server Agent Job that will execute these packages on a schedule.

I previously attempted using a parameter in the SSIS package, but when I attempted to pass in a value I got an error stating that parameters are read only.

So, how can I set up these packages to send the pertinent data they need for the server they're running on?

Clarification: We are currently deploying our packages by uploading them to MSDB.


texts: Neither DBCC FREEPROCCACHE nor DBCC FREESYSTEMCACHE('SQL Plans') do anything to free CACHESTORE_SQLCP memory

texts:
CACHESTORE_SQLCP Sql Plans takes up > 38 GB after a few days.

We are already running with "optimize for ad hoc workloads" option on.  (Entity Framework and custom reporting creates a lot of ad hocs!)

  SQL Server 2016 SE 3.00.2164.0.v1 on AWS RDS with multi-AZ mirroring

When I run:

DBCC FREESYSTEMCACHE('SQL Plans');

or

DBCC FREEPROCCACHE

or

DBCC FREESYSTEMCACHE ('SQL Plans') WITH MARK_IN_USE_FOR_REMOVAL

or

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

It doesn't seem to clear it:

SELECT TOP 1 type, name, pages_kb FROM sys.dm_os_memory_clerks ORDER BY pages_kb  desc

type                name        pages_kb
CACHESTORE_SQLCP    SQL Plans   38321048

I was running with Query Store enabled, but I disabled it to see if that was interfering with anything, it didn't seem to help, but I left it off.

What's really weird also is

SELECT COUNT(*) FROM sys.dm_exec_cached_plans

is 1-3 or so (it seems to show only ever show currently-running queries), even though all that memory is reserved, even before I attempted to clear anything.  What am I missing?

CACHESTORE_SQLCP is taking up more than 60% of all available memory, which is a concern because there are memory waits happening occasionally.  In addition, we had to kill a routine DBCC CHECKDB over the weekend that was lasting 4 hours because insufficient memory was stacking up waits (it completed instantly with no errors with PHYSICAL_ONLY on).

Is there any way to reclaim this memory (other than nightly reboots!?)?

Update from comments/answers

When I run

SELECT * FROM sys.fn_my_permissions(NULL,NULL)

I get

entity_name subentity_name  permission_name
server      CONNECT SQL
server      CREATE ANY DATABASE
server      ALTER ANY LOGIN
server      ALTER ANY LINKED SERVER
server      ALTER ANY CONNECTION
server      ALTER TRACE
server      VIEW ANY DATABASE
server      VIEW ANY DEFINITION
server      VIEW SERVER STATE
server      ALTER SERVER STATE
server      CREATE SERVER ROLE
server      ALTER ANY SERVER ROLE

Which includes the required ALTER SERVER STATE permission.

The output of DBCC FREEPROCCACHE is

  DBCC execution completed. If DBCC printed error messages, contact your
  system administrator.

Which is the standard message.  Other DBCC functions that RDS does not support give error messages about permissions.

(A day later and after running that again, SQL Plans is still 38,321,280 kb)

Update from comments/answers

SELECT pool_id, name, cache_memory_kb, compile_memory_kb FROM sys.dm_resource_governor_resource_pools

outputs:

pool_id name    cache_memory_kb  compile_memory_kb
1   internal    38368408         1168080

DBCC FREEPROCCACHE ('internal') doesn't do anything differently

Update

SQL Error Log logs the following each time:

2017-10-19 14:26:47.22 spid85 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.

2017-10-19 14:26:47.22 spid85 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.

2017-10-19 14:26:47.22 spid85 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.

Update

There's an engine version upgrade on RDS from 13.00.2164.0.v1 to 13.00.4422.0.v1 available.  Though it is set to auto-minor-version upgrade, it hasn't seemed to keep us updated to the latest.  Will reboot and install that this weekend to see if it helps.


texts: Access to Server and Real Data for Performance Tuning

texts:
I'm newbie in performance tuning (PT) in SQL server and I have read some documents about how to do it. It is based on theory from the articles or books.

The next step to is to learn about it by practice and if you are a private person who doesn't have access to a server with lots of data. Let's say that it is a student who wants to learn about PT in practice.

My question is:
How should a student learn about PT in practice when you don't have access to a server with real data?

Is there a company which provides education with a server and real data?


texts: How to reinstall PostgreSQL over an existing installation

texts:
I am trying to upgrade an installer created with NSIS that installs postgresql. It used to install postgresql 8.3 but we want to upgrade the DB to 9.4.4.

The old version on of the DB used to be an MSI installer but they have switched to a Windows EXE with the later version. The old version was able to install over a existing installation. The new version breaks and gives me a very general error that I can't use to pinpoint the issue.

Error:

  The installation directory must be an absolute path, containing only
  letters, numbers and the characters '-', '/', '.' and '_', and must be
  writable.

This is my install command:

  postgresql-9.4.4-1-windows-x64.exe --prefix "C:\postgres" --datadir
  "C:\postgres\data" --enable_acledit 1 --install_runtimes 0
  --serverport 5432 --superpassword "XXXX" --servicepassword "XXXX" --unattendedmodeui minimal --mode unattended --debuglevel 0 --serviceaccount "postgres" --create_shortcuts 0

The Bitrock installer only provides the above error with no path detail or anything to act on.

Any clues or suggestions? (Thanks)


texts: idera - why it restores databases faster

texts:
We have a 2gb live database that takes forever restore whenever I need some live data to test something. If I use idera it takes 5 mins at most. When I use the restore database command it can take almost an hour. How is idera able to do so much faster?

I use the following to restore:

RESTORE DATABASE dbname
  FROM DISK = 'path'
  WITH
  MOVE 'CMTS_dat' TO 'C:\db\MARTIN_Latest.mdf',
  MOVE 'CMTS_log' TO 'C:\db\MARTIN_Latest_log.ldf'
  ,REPLACE

If it has any data partitions then I'll use MOVE to do the partitions as well.


texts: How do uninitialized variables differ from NULL in Mysql?

texts:
Related, please read first for context: MySQL stored procedure: loop through table, delete rows. Logic problem: won't exit loop because of LIMIT option in query

I think this is really strange. I don't understand why the solution in that problem works the way it does, can someone explain the solution please?

This is stated in the mysql documentation:

  If you refer to a variable that has not been initialized, it has a value of NULL and a type of string.

why does it make a difference if you set a variable to NULL before you use it, if that is the default for uninitialized variables anyway?

(I am aware, that it is very dirty programming, not to initialize variables, but still I want to understand the behaviour.)


texts: SQL Server service automatically stops after starting

texts:
I have a problem with my SQL Server 2014 Express instance. It worked fine until now. I can't get it working because SQL Server service automatically stops immediately after I start it.

I'm trying to start it from SQL Server Configuration Manager. When I open it I see that the SQL Server service is stopped, I re-run it and the service doesn't give me any error while starting. Then after pressing F5 to refresh the screen it's stopped again.

Here are some error logs from the starting operation. Can someone explain to me what happened? Can someone help me with solving this situation?

Error log 1
Error log 2


texts: SQL Error (126): Incorrect key file for table '.\mysql\user.MYI'; try to repair it

texts:
To give you a bit more info I had moved my webserver from my C: drive to my D: drive and had upgrade my OS from Server 2008 to 2012.

All my website are fine and load without an issue and I havent run into any weird issues with them.

I will get this error in the title when I try to add a database to specific sql user or even add a new sql user all together.

Also tried a copy of my webserver on a VM with Windows 7 as the OS and did not get this issue so I think its something with Server 2012 but not sure what.

Any help would be greatly appreciated!


texts: Best patterns for encrypting database columns/tables in SQL Azure

texts:
Azure SQL doesn't support many of the encryption features found in SQL Server (Table and Column encryption). What is the best practice for doing application level encryption into a database that doesn't support encryption? Also need

Key rotations
metadata mapping of which tables and which columns are encrypted. This is simple when it's just couple of columns (send an email to all devs/document) but that quickly gets out of hand

If someone would like to recommend a library, I'd be happy to stay away from "DIY" too.


texts:
I know this will be greeted with derision, but yes I'm running Oracle XE on Windows XP.  Long story involving office policies and VMs.

Actually it's been working fine for a couple of years, but today I noticed I couldn't connect - I was getting ORA-12505, ORA-12541 and ORA-12560.

It soon transpired that the DB itself, not the listener, had stopped.  Looking in services.msc, 'OracleServiceDB' is listed as not running (even though it's set to start automatically).  Restarting it gives me a windows error: 'Error 1067 - the process terminated unexpectedly'.  The event viewer shows several instances of the DB stopping by itself.

The only change I can think of which might have caused it to break was the installation of windows XP service pack 3 a couple of days ago.

I'm running windows inside a VirtualBox VM.  There is plenty of disk space.

Any thoughts?

I looked in the log and found this:

Fatal NI connect error 12638, connecting to:
 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

 VERSION INFORMATION:
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 11.2.0.2.0 - Production
Time: 04-NOV-2013 17:48:03
Tracing not turned on.
Tns error struct:
  ns main err code: 12638

TNS-12638: Credential retrieval failed
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


texts: Insert 2 output result sets from sp_spaceused into Temp Table (SQL Server non-2016)

texts:
sp_spaceused, when executed without any parameters, produces 2 result sets

database_name, database_size, unallocated_size
reserved, data, index_space, unused

Usually when you execute some SP, you can insert output into temp table like below example:

insert #temp (column1, column2, column3)
exec sp_someprocedure

but in case of sp_spaceused (without any parameters) - can not insert output, since it produces 2 result sets.
I know 2016 has nice parameter @oneresultset = 1, but is there any way you can insert 2 or more output result sets into #temp in SQL 2014 or lower ?

Is there any tricks ?


texts: Single foreign key for referencing one of multiple tables

texts:
Currently I have a schema that looks like this:
create table person(
    id uuid primary key default gen_random_uuid() not null,
);

create table car(
    id uuid primary key default gen_random_uuid() not null,
);

create table extra(
    id uuid primary key default gen_random_uuid() not null,
);

create table car_extra_m2m(
    car_id uuid not null references car(id) on delete cascade,
    extra_id uuid not null references extra(id) on delete cascade,
    primary key (car_id, extra_id)
);

create table person_extra_m2m(
    person_id uuid not null references person(id) on delete cascade,
    extra_id uuid not null references extra(id) on delete cascade,
    primary key (person_id, extra_id)
);

Is it possible to express the two many to many tables in a single many to many table? Since we're using uuid the ids should never collide, so it might be possible to know the type just from the uuid?
Like this pseudocode: (Edit: not valid syntax in postgresql, does there exist a valid syntax for this?)
create table extra_m2m(
    person_or_car_id uuid not null references (person(id) or car(id)) on delete cascade,
    extra_id uuid not null references extra(id) on delete cascade,
    primary key (person_or_car_id, extra_id)
);


texts: check when last password changed in my user db/table


texts:
I have SQL code that is executing slow

DECLARE @ccode varchar(500)
SET @ccode = '%NIP%'

SELECT [CVN_UID]
FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
WHERE DESCRIPTOR_TEXT LIKE @ccode

Why is SQL Server using nested loops?

This code is working fine, it uses only index scan:

SELECT [CVN_UID]
FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
WHERE DESCRIPTOR_TEXT LIKE '%NIP%'

The slow query is executing 5.5 seconds, without the parameter it's 1.8 seconds.

This query is also working fine:

SELECT [CVN_UID]
FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
WHERE DESCRIPTOR_TEXT LIKE @ccode
OPTION (recompile)


texts:
One of my client is having issues with one of the jobs failing every weekend to perform index maintenance on a couple of databases. It is a SQL job which uses maintenance plans underneath. Okay, so the maintenance plans consist of check database integrity task, followed by 3 t-sql task in sequence which runs the same script but for different databases (code provided below) and finally runs a reorg task and then update of statistics. I know this seems a bit weird the way they are running things at this time but I will change it moving forward. Presently, I am stuck with this job completing the maintenance check but failing to execute the t-sql task with the following error.

Executing the query "USE XMain;

declare @frag_Temp as Table
(
  ..." failed with the following error: "Incorrect syntax near '-'.
Changed database context to 'XMain'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

USE XMain;

DECLARE @frag_Temp AS TABLE (
  ID int IDENTITY (1, 1),
  [objectid] [int] NULL,
  [indexid] [int] NULL,
  [partitionnum] [int] NULL,
  [frag] [float] NULL
)

DECLARE @Count int
DECLARE @i tinyint = 1
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitionnum bigint;
DECLARE @partitioncount bigint;
DECLARE @SQLCommand AS nvarchar(3000)
INSERT INTO @frag_Temp
  SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
  FROM sys.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(), NULL, NULL, NULL, 'LIMITED')
  WHERE avg_fragmentation_in_percent >= 30.0
  AND index_id > 0;

-- Get Total Count
SELECT
  @Count = COUNT(*)
FROM @frag_Temp
WHILE (@i <= @Count)
BEGIN
  SELECT
    @objectid = objectid,
    @indexid = indexid,
    @partitionnum = partitionnum
  FROM @frag_Temp
  WHERE ID = @i

  -- Get tableName and its schema
  SELECT
    @objectname = o.name,
    @schemaname = c.name
  FROM sys.objects o
  INNER JOIN sys.schemas c
    ON o.schema_ID = c.schema_ID
  WHERE o.object_id = @objectid
  -- Get Index Name
  SELECT
    @indexname = name
  FROM sys.indexes
  WHERE index_id = @indexid
  AND object_id = @objectid
  -- Get Partition Count
  SELECT
    @partitioncount = COUNT(*)
  FROM sys.partitions
  WHERE object_id = @objectid
  AND index_id = @indexid

  SELECT
    @SQLCommand = 'Alter Index ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REBUILD;'
  IF (@partitioncount > 1)
    SELECT
      @SQLCommand = @SQLCommand + ' PARTITION=' + CONVERT(char, @partitionnum);

  EXEC (@SQLCommand);

  -- Increment Count
  SET @i = @i + 1

END
GO


texts:
We need to test how an application will fail when the DB2 database cannot be written to.

What is the easiest way to make a DB2 table read-only? Is this considered locking?

Thanks in advance,
Bert


texts:
I am facing a unique problem related to SQL Server query execution. I have around 150 stored procedures, which load data sequentially to the data warehouse tables.
All stored procedures called inside a while loop one after another. I am observing some lag after one stored procedure completes its execution, and before the next procedure starts its execution. The lag ranges from 3 seconds to 55 seconds between subsequent executions of stored procedures.
Any guidance will be greatly appreciated.


texts: Getting database usage of all the database at once in Sybase

texts:
How can I get the database usage of all the databases at once?

sp_spaceused and sp_helpdb <dbname> only gives the usage one at a time.

I use the Sybase Central client.


texts: How can I RAISE_APPLICATION_ERROR and do an INSERT in the same trigger?

texts:
From what I see, having the RAISE_APPLICATION_ERROR cancels the other operations that I would have in the trigger. I tried writing first the INSERT and then the RAISE_APPLICATION_ERROR hoping that they get executed in the order in which they are read but it doesn't work like this...The error is thrown and the INSERT is canceled. How could I make both of the operations get executed (from the same trigger) ?
I also tried creating two triggers .. one of them doing the RAISE_APPLICATION_ERROR part and the other doing the INSERT and establishing the order using FOLLOWS and PRECEDES but it didn't work.
The trigger should block the DROP DDL (that's what for I used the RAISE_APPLICATION_ERROR) and it also should do an insert in a LOG type table (that's what for I used the INSERT).


texts: Strange behaviours with SET options in SSMS

texts:
I am investigating the effect of various SET options on the creation of indexed views on SQL Server 2014 CU1. The following code:

ensures that the SET options are as they should be
creates a table
creates a view based on the table
creates an index on the view
inserts a record in the view (which will fail if the SET options are incorrect)

It works as expected. However, if I uncomment the section where I deliberately set each SET option to the wrong value, the CREATE INDEX statement (which occurs earlier in the script) fails saying that QUOTED_IDENTIFIER has the wrong value.

I have tested with SSMS 2012 and 2014. How can this be?

USE tempdb;

-- ensure correct SET options for indexed view creation
-- http://msdn.microsoft.com/en-us/library/ms191432.aspx
SET ANSI_NULLS  ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET QUOTED_IDENTIFIER ON;

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS
            WHERE TABLE_NAME = 'v1'
            AND TABLE_SCHEMA = 'dbo')
    DROP VIEW dbo.v1;
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_NAME = 't1'
            AND TABLE_SCHEMA = 'dbo')
    DROP TABLE t1;
CREATE TABLE dbo.t1(c1 int);
GO
CREATE VIEW dbo.v1 WITH SCHEMABINDING AS SELECT c1 FROM dbo.t1;
GO
CREATE UNIQUE CLUSTERED INDEX i1 ON dbo.v1(c1);

---- make SET options the opposite of what is required
--SET ANSI_NULLS    OFF;
--SET ANSI_PADDING  OFF;
--SET ANSI_WARNINGS OFF;
--SET ARITHABORT    OFF;
--SET CONCAT_NULL_YIELDS_NULL   OFF;
--SET NUMERIC_ROUNDABORT    ON;
--SET QUOTED_IDENTIFIER OFF;

INSERT INTO dbo.v1(c1) VALUES(1);

DROP VIEW dbo.v1;
DROP TABLE dbo.t1;

I can work around it with dynamic SQL and get the expected error message:

  Msg 1934, Level 16, State 1, Line 32
  INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'...

-- make SET options the opposite of what is required
EXECUTE ('SET ANSI_NULLS    OFF;
SET ANSI_PADDING    OFF;
SET ANSI_WARNINGS   OFF;
SET ARITHABORT  OFF;
SET CONCAT_NULL_YIELDS_NULL OFF;
SET NUMERIC_ROUNDABORT  ON;
SET QUOTED_IDENTIFIER   OFF;
INSERT INTO dbo.v1(c1) VALUES(1);');

but why should I have to?


texts: Reclaiming space from half filled huge FGs having Partitioned and LOB Data in it

texts:
We have a database with multiple filegroups. Out of which, there are 4 filegroups(Non-Primary) which has most of the data. These filegroups have partitioned tables into it. It also contains LOB Data as well.
Now as per customer request, we have purged 50% of data. Because of it, these 4 Filegroups are half empty.
For example these FGs are of 400 GB each and around 200 GB is free.
My requirement is to reclaim the space from these 4 FGs as we are expecting that it will not grow that much in near future. Shrinking is not an option as it is time consuming. Any other faster way to do it.
Can someone please help me. Thanks in advance.


texts: How to avoid dropping schemas when publishing DB in SSDT Database project

texts:
When publishing a database from SSDT, there are several options to "do not drop" an object if it is not in the SSDT project. However, it seems that there is no option to avoid dropping schemas. Do anyone know if this is possible to achieve, or do I have to manually edit the publish script?

This is problem when I choose to "do not drop tables", but then it will try to drop schemas that has tables in it.

br
Gert


texts: Can't find any tables after psql dump import from pgexercises.com

texts:
I'm trying to import the database used for the PostgreSQL exercises at pgexercises.com.

I ran the following command as recommended, and it went through without errors and even showing Inserts with the right number of rows:

psql -U cornelius -f clubdata.sql -d postgres -x

I can connect to the database created with psql exercises. However, I get Did not find any relations with \dt.

When rerunning the import command, I get errors saying that the tables already exist.

What's going on? Where are my tables?

Here's a subset of the dump file:

--
-- PostgreSQL database dump
--
CREATE DATABASE exercises;
\c exercises
CREATE SCHEMA cd;

-- Dumped from database version 9.2.0
-- Dumped by pg_dump version 9.2.0
-- Started on 2013-05-19 16:05:10 BST

SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- TOC entry 7 (class 2615 OID 32769)
-- Name: cd; Type: SCHEMA; Schema: -; Owner: -
--

SET search_path = cd, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 171 (class 1259 OID 32818)
-- Name: bookings; Type: TABLE; Schema: cd; Owner: -; Tablespace:
--

CREATE TABLE bookings (
    bookid integer NOT NULL,
    facid integer NOT NULL,
    memid integer NOT NULL,
    starttime timestamp without time zone NOT NULL,
    slots integer NOT NULL
);

INSERT INTO bookings (bookid, facid, memid, starttime, slots) VALUES
(0, 3, 1, '2012-07-03 11:00:00', 2),
(1, 4, 1, '2012-07-03 08:00:00', 2),
(2, 6, 0, '2012-07-03 18:00:00', 2),
(3, 7, 1, '2012-07-03 19:00:00', 2),
(4, 8, 1, '2012-07-03 10:00:00', 1),
(5, 8, 1, '2012-07-03 15:00:00', 1),
(6, 0, 2, '2012-07-04 09:00:00', 3),
(7, 0, 2, '2012-07-04 15:00:00', 3),
(8, 4, 3, '2012-07-04 13:30:00', 2),
(9, 4, 0, '2012-07-04 15:00:00', 2);

--
-- TOC entry 2196 (class 2606 OID 32822)
-- Name: bookings_pk; Type: CONSTRAINT; Schema: cd; Owner: -; Tablespace:
--

ALTER TABLE ONLY bookings
    ADD CONSTRAINT bookings_pk PRIMARY KEY (bookid);

CREATE INDEX "bookings.memid_facid"
  ON cd.bookings
  USING btree
  (memid, facid);

ANALYZE;


texts:
We were trying to restore a database on the same SQL MI with older date and different name but it got failed and we did get any information from the error message. This is also causing issue while taking backup as well.
Later we came to know the SQL MI has TDE enable onit (Service Managed), is this causing an issue?


texts:
As CMS I use Wordpress. I want to export a whole category (windows 8) so I run the SQL command :

SELECT * FROM wp_posts
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE (wp_term_taxonomy.term_id = 15
   AND wp_term_taxonomy.taxonomy = 'category'
   AND wp_posts.post_type = 'post'
   AND wp_posts.post_status = 'publish');

I get the tables but then I cannot export as SQL (there is no option) - strange. Is there a way to export only selected tables as SQL?


texts: is it reasonable to use NoSQL for my project

texts:
Imagine you want to write a webapp for selling your car online.

A car has a lot of attributes. Like how many doors, color, aircondition, etc. Some of them are simple boolean values, others might need to store more information (number of doors).

Some Accounts will become upgraded to "re/taider" accounts and they will have the possibility to sell more then one car.

From what I learned at school this actually screams for a relational database model.
But I would really like to get into NoSQL.

Would it be very silly to use Mongodb or Couchdb in this case?

Here is a more specific question, which exceeds my understanding and might give you a clue of my thinking.

A car has many attributes and you want to search for a car with as many attributes you like.

Now the only way I know how to handle this in NoSQL is to have a lot of Map-reduced views.
However, with only 12 attributes there is a possibility of 4096 different combinations. One can not do 4096 map-reduces for this use case. It can't be the common way, I mean it does not feel right.


texts:
I think I am confusing things.

In ETL, data conversion starts at Database A and ends up in Database B, both are relational databases.  Maybe I have a 20-year-old system.  I use ETL to get the data between the two databases.  I do not know what else ETL is used for.

In ELT, is that the same except the data from Database A never ends up in Database B?  Instead, the raw data is sitting in tables or some other data structure on Server B, from Database A, but requires something like Hadoop to transform that data into something an application that is specifically designed to use Hadoop?

Edit:  I have done it again.  Hadoop != ELT.  I was looking only at Hadoop and thought it was ELT or the manifestation of it. edit: and that ELT meant you required a unique non-RDBMS file data structure instead of an RDBMS and you dumped the RDBMS altogether.


texts:
There are two subqueries select a set according to certain criteria, such as

select
    id, dt_beg, dt_end
  from ...................
  where ..............

it is necessary to select data from the second if there is no record zero, if it is zero, then from the first, it was originally thought (erroneously), thinking that the first will never return the record zero, therefore the second one

more or less like this:

SELECT
    ao.id,
    DECODE(NVL(an.id,0), 0,ao.dt_beg, an.dt_beg) AS dt_beg,
    DECODE(NVL(an.id,0), 0,ao.dt_end, an.dt_end) AS dt_end
  FROM
  (.....) ao, (.....) an
  WHERE ao.id = an.id(+)
    AND ao.id = _NUMBER_
;

But, it turned out, there are situations when the first returns NULL, while the second is not NULL
and just the data from the second priority, i.e. if they are not NULL, then they must be substituted.

tried to do FULL OUTER JOIN
but nothing is fetched, although there is definitely data in the second query.

SELECT
    ao.id,
    DECODE(NVL(an.id,0), 0,ao.dt_beg, an.dt_beg) AS dt_beg,
    DECODE(NVL(an.id,0), 0,ao.dt_end, an.dt_end) AS dt_end
  FROM
  (.....) ao
  FULL OUTER JOIN (.....) an ON ao.id = an.id
  WHERE ao.id = _NUMBER_ OR an.id = _NUMBER_
;

How to combine and select data?


texts: Is safe to make backups of vm that contain Oracle databases using veeam backup or another similar tool?

texts: I would like to know if it is advisable or safe to make backups of vm that contain Oracle databases using veeam backup or another similar tool, my doubt is caused because I have several vm with Oracle and I am saving them so far with veeam backup, I have tried restores and I have not had problems, but I am concerned that it may have loads or intensive uses of the Oracle database system, there will be some problem if a save is made at that moment and then when restoring the database it cannot start due to inconsistencies


texts: how to update SSRS execution account automatically through power-shell?

texts: I need to update the password for the SSRS Execution account for every 15 days. How can I automate this process using power shell or some other scripting?


texts: MySQL RDS Incompatible Restore while creating read replica

texts:
We have multiple m5 generation MySQL RDS instance. The problem we are facing is that while creating read replica for these instances we are repeatedly getting 'incompatible-restore' error. In the event log of read replica instance we are getting below error :

  Database instance put into incompatible-restore. Your MySql memory
  setting is inappropriate for the usages

However, if we set the parameter group to 'default-5.7' then the read replica gets successfully created.

We have found the differences between the current parameter group and 'default-5.7' and tried to make those changes. But still the issue persists.


texts: pg replication connection not returns wal events to java code client , it hangs some times

texts:
Describe the issue
We are facing issue of pg replication slot not listen database wal events.
Once pg database restarted and we attach

Driver Version: 42.2.6
Java Version: JDK1.8
OS Version: Window 10
PostgreSQL Version: 9.6

Steps to reproduce the behaviour:

Code using the sample from the JDBC driver's documentation

Created replication connection logical with test decode

String url = "jdbc:postgresql://dbhist8:port/" + db;
Properties props = new Properties();
PGProperty.USER.set(props, "postgres");
PGProperty.PASSWORD.set(props, "postgres");
PGProperty.ASSUME_MIN_SERVER_VERSION.set(props, "10.5");
PGProperty.REPLICATION.set(props, "database");
PGProperty.PREFER_QUERY_MODE.set(props, "simple");
Connection slotVerifyConnection = null;
Connection slotConnection = null;
try {
//TODO connection pool
slotVerifyConnection = connect(url, "postgres", "postgres");
slotConnection = DriverManager.getConnection(url, props);
Statement statement = slotVerifyConnection.createStatement();
ResultSet result = statement.executeQuery("select restart_lsn,active,active_pid from pg_replication_slots where slot_type = 'logical' and slot_name ='" + slotName + "'");
while (result.next()) {

            lastLSNNumber = LogSequenceNumber.valueOf(result.getString(1));

        }

        Connection con = DriverManager.getConnection(url, props);
        PGConnection replConnection = con.unwrap(PGConnection.class);

        PGReplicationStream stream = null;

        if (lastLSNNumber != null) {
            stream =
                    replConnection.getReplicationAPI()
                            .replicationStream()
                            .logical()
                            .withSlotName(slotName)
                            //.withSlotOption("include-xids", true)
                             //.withSlotOption("skip-empty-xacts", true)
                            .withStartPosition(lastLSNNumber)

                            // .withStatusInterval(20, TimeUnit.SECONDS)
                            .withStatusInterval(Integer.parseInt("0"), TimeUnit.SECONDS).start();
        } else {

            **ReplicationSlotInfo info=       replConnection.getReplicationAPI()
                    .createReplicationSlot()
                    .logical()
                    .withSlotName(slotName)
                    .withOutputPlugin(wal2json)
                    .make();**
            info.getConsistentPoint();
            stream =
                    replConnection.getReplicationAPI()
                            .replicationStream()
                            .logical()
                            .withSlotName(slotName)
                        //    .withSlotOption("include-xids", false)
                       //     .withSlotOption("skip-empty-xacts", true)
                            .withStatusInterval(0, TimeUnit.SECONDS)
                            .start();
        }
        while (true) {
            //non blocking receive message
            ByteBuffer msg = stream.read();

            if (msg == null) {
                TimeUnit.MILLISECONDS.sleep(10L);
                continue;
            }

            int offset = msg.arrayOffset();
            byte[] source = msg.array();
            int length = source.length - offset;
            System.out.println(new String(source, offset, length));
            String sql=new String(source, offset, length);
            //QueryUtils.getQueries(sql);
            if(stream.getLastReceiveLSN()!=null) {
                stream.setAppliedLSN(stream.getLastReceiveLSN());
                stream.setFlushedLSN(stream.getLastReceiveLSN());
                stream.forceUpdateStatus();
            }else{
                System.out.println("NULL value");
            }
        }

    } catch (Exception ex) {
ex.printStackTrace();
}
}


texts: Would it be safe to save SQL data from memory if the program is having a memory leak and peeks 100% memory and 100% cpu usage?


texts: I am a non IT background, my recent projects uses PowerShell for a lot of SQL Server administration. I would like to start from absolute zero, and learn to use and code the Windows PowerShell as it was meant to be used. I am looking for a good book for beginners or other materials? Thoughts?


texts: insert in Mysql innodb table extremely slow

texts:
I can get 2 million inserts per minute on myisam table, but only 3000 insert per minute if I change to innodb

CREATE TABLE myisam_table ( c1 int default NULL, c2 varchar(30) default NULL, c3 date default NULL) engine=myisam;
CREATE TABLE innodb_table ( c1 int default NULL, c2 varchar(30) default NULL, c3 date default NULL) engine=innodb;

CREATE PROCEDURE load_myisam_table()
       begin
    declare v int default 0;
    while v < 10000000
    do
        insert into myisam_table
        values (v,'testing','2017-01-01');
         set v = v + 1;
    end while;
    end

CREATE PROCEDURE load_innodb_table()
       begin
    declare v int default 0;
    while v < 10000000
    do
        insert into innodb_table
        values (v,'testing','2017-01-01');
         set v = v + 1;
    end while;
    end

And result:

mysql -e "select count(*) from asdf.innodb_table;" ; sleep 60;mysql -e "select count(*) from asdf.innodb_table;" ;
+----------+
| count(*) |
+----------+
|      125 |
+----------+
+----------+
| count(*) |
+----------+
|     2975 |
+----------+
mysql -e "select count(*) from asdf.myisam_table;" ; sleep 60;mysql -e "select count(*) from asdf.myisam_table;" ;
+----------+
| count(*) |
+----------+
|   134446 |
+----------+
+----------+
| count(*) |
+----------+
|  2225277 |
+----------+


texts: Scalability limitations of PostgreSQL and MySQL

texts:
I've heard that performance of non-sharded relational database such as MySQL or PostgreSQL "breaks" beyond 10 TB.

I suspect that limits as such do exist as one would not come up with Netezza, Greenplum, or Vertica, etc, however I would like to ask if anyone here has a reference to any research paper or formal case studies where these limits are quantified.


texts:
I have a query:

DECLARE @str NVARCHAR(max)
SET @str = 'SET QUOTED_IDENTIFIER ON' + CHAR(13)+ CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 'Select 1 from MyTable'
PRINT @str
EXEC (@str)

It returns an error:

  Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'GO'.

But if I exec the printed query in a new query editor it works fine.

I am using SQL Server 2014.


texts: What is best practice for sql server security? Using AD groups that are given rights on schema objects or should I add the AD groups to a database role which is given rights on schema objects ? If the later one is best practice, what is really the benefit of using database roles ?


texts: How to properly format sqlite shell output?


texts: Check partition function if list of value exists, if not iterate through list and add new partition value to the partition function?

texts:
I have an azure sql db with a partitioned table with a partition function on varchar datatype.

In an Etl process I will get a list of partiton values as a parameter that i will the need to:

check partition function if the partition values exist, if not iterate through list of value and add to partition function.
for the list of partition values that is supplied as a parameter, iterate through the $partition function to get the partitionids into a new variable as a comma seperated list
truncate the partitioned table with the command: truncate parttable with partitions (@newvariable)

What I got so far in code is:

-- Table, Partition Function, Partition Scheme

CREATE PARTITION FUNCTION PF_TEXT(varchar(20)) AS RANGE RIGHT FOR VALUES ('A', 'B', 'C', 'D', 'E')

CREATE PARTITION SCHEME PS_TEXT AS PARTITION PF_TEXT ALL TO ([PRIMARY])

CREATE TABLE [dbo].[fact_partitioned](
    [Col1] [int], NOT NULL
    [Col2] [int], NOT NULL
    [TEXT] [varchar(20)] NOT NULL
) ON PS_TEXT ([TEXT])

---------------------------

DECLARE @cmd nvarchar(max);
DECLARE @partitions nvarchar(max);
DECLARE @partitionids nvarchar(max);

SET @partitions = 'A','B','C'

-- Code to check Partition Function for @partitions values exist?
-- For @partitions values that don't exist, iterate @partitions values
and add to partition function.

--code to iterate @partitions values over $partition.PF_TEXT(@partition)
add result to @partitionids

SET @cmd = N'TRUNCATE TABLE dbo.pt WITH (PARTITIONS (' + @partitionids + N'));';

PRINT @cmd;

--EXEC sys.sp_executesql @cmd;


texts: postgresql 9.* - user cannot SELECT from VIEW inside a SCHEMA as DEFAULT PRIVILEGE

texts:
hope you can help me.

I have 3 users

First is me, the db admin. I run the GRANT queries, I can do anything. (name doesn't matter now)
Second is the data manager user. = manager_u
Third is the data viewer user. = viewer_u

The "data viewer" should read from views created by "data manager", without managing privileges afterwards
For that reason I create a schema, owned by manager, that should be full open to the viewer.

The problem/actual situation is (after running my following code) this.

When manager_u create a named view, viewer_u cannot SELECT from it.

If I run this, AFTER the creation:

GRANT SELECT ON ALL TABLES IN SCHEMA views_for_viewer_u TO viewer_u;

he can read. Everything works.
But I like that he can read any further views without managing his privileges.
I tried with that, with no success:

ALTER DEFAULT PRIVILEGES FOR ROLE viewer_u IN SCHEMA views_for_viewer_u GRANT SELECT ON TABLES TO viewer_u;

Here, the complete code:

--
-- QUERIES as db admin
--

CREATE ROLE manager_u LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE viewer_u LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

ALTER ROLE manager_u WITH PASSWORD 'yyy';
ALTER ROLE viewer_u WITH PASSWORD 'xxx';

GRANT CONNECT ON DATABASE mydb TO manager_u;
GRANT CONNECT ON DATABASE mydb TO viewer_u;

---
---                     schema
---
CREATE SCHEMA IF NOT EXISTS views_for_viewer_u;

ALTER SCHEMA views_for_viewer_u OWNER TO manager_u;

GRANT ALL PRIVILEGES ON SCHEMA views_for_viewer_u TO manager_u;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA views_for_viewer_u TO manager_u;
ALTER DEFAULT PRIVILEGES FOR ROLE manager_u IN SCHEMA views_for_viewer_u GRANT ALL PRIVILEGES ON TABLES TO manager_u;

---
---   privileges on views_for_viewer_u schema
---   I think the problem is here but I'm going crazy
---   I beg help
---

GRANT USAGE ON SCHEMA views_for_viewer_u TO viewer_u;
GRANT SELECT ON ALL TABLES IN SCHEMA views_for_viewer_u TO viewer_u;
ALTER DEFAULT PRIVILEGES FOR ROLE viewer_u IN SCHEMA views_for_viewer_u GRANT SELECT ON TABLES TO viewer_u;

--
-- QUERIES as data manager:  manager_u
--

CREATE OR REPLACE VIEW views_for_viewer_u.proxy_table
    AS SELECT * FROM another_schema.a_table;

--
-- QUERIES as viewer:  viewer_u
--

SELECT * FROM views_for_viewer_u.proxy_table; -- this is what is not working
--
--
-- thanks to anyone can give an hint
--
--


texts: How to preserve key when joining to a preference table in Oracle

texts:
I have a table of customers, and a table of customer preferences, where:

create table customer (
  customerId number,
  name varchar2(200),
  primary key (customerId)
);

create table pref (
  preftype number,
  customerId number,
  prefval varchar2(200),
  primary key (preftype, customerId)
);

So basically the pref table records the customer's preference based on each preference type. customerId is unique for each preftype.

Theoretically the following join should not lose the key-preserved property, but I am getting "ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table." Anyway I can rewrite the query to maintain the key-preservedness?

select rowid, vi.* from (
  select c.customerId, c.name, p.prefval
  from customer c, (select * from pref where preftype = 1) p
  where c.customerId = p.customerId (+)
) vi;

Restrictions / requirements:

In our case a query engine is building that outer query (the one with rowid) around the inner query, so we don't have as much control.

We'd like to have a query that can be wrapped around with something like:

select rowid, v.*
from
    (
    -- query
    ) v ;


texts: Table Relationships: Table 1 (Field 7, Field 8, Field 9) to Table 2 (Field 1)

texts:
I'm sure that this is probably 'Databases 101',  but I can't seem to figure it out.

So, I'm setting up a database in Access - that I will want to move to MySQL, and I currently have 4 tables, set up as so:

Table1
 - ID*
 - itemName
 - etc.
Table2
 - ID*
 - table1ID
 - subitemName
 - subitemDescription
 - table3ID1
 - table3ID2
 - table3ID3
 - table4ID1
 - table4ID2
 - table4ID3
 - table4ID4
Table3
 - ID*
 - itemName
Table4
 - ID*
 - itemName

You see, the items in Table2 are associated with multiple items from both Table3 and Table4.  For instance, 1 item in Table2 may be associated with 3 items from Table3 (and therefore, I'm assuming, 3 unique ID's), and 4 items from Table4 (and, again, 4 unique ID's).

So, I type the unique ID* value into each field, and create relationships from the Table3 ID* field to each table3ID field on Table2 (and same for Table4), yet when I expand the items on Table3 or Table4, I get nothing.  However, if I only link the ID* to one of the table#ID fields, then expanding the item on its respective table shows all the items referencing it on Table2.

Does anyone know what I'm doing wrong/not doing right/generally effing up?  I'd greatly appreciate any help.


texts: MySQL complains key exists but I can't find it

texts:
I have a large (in both schema and data) MySQL database with lots of foreign key constrains. Recently I have discovered that some script can not create a table because key with given name already exists.

I traced down the problem to the following:

If I run something like this:

CREATE TABLE `foo` (
  `bar` int UNSIGNED NOT NULL,
  CONSTRAINT `BAZ` FOREIGN KEY (`bar`) REFERENCES `qux` (`bar`) ON DELETE CASCADE ON UPDATE CASCADE
);

I'm getting:

  ERROR 1022 (23000): Can't write; duplicate key in table 'foo'

But if I:

SELECT *
  FROM information_schema.REFERENTIAL_CONSTRAINTS
 WHERE CONSTRAINT_SCHEMA = "my_db"
   AND CONSTRAINT_NAME LIKE "BAZ";

I'm getting an empty set.

I have also tried to dump the schema and search for "BAZ" there but found nothing.

Creating a table naming foreign key anything but "BAZ" goes through.

How could it be?


texts: Selecting row multiple times, as specified by a field in that row

texts:
As part of an redesign, there's need for an different schema in one of the main tables in our database. The data in one of the existing tables will be kept as legacy data.

To make the changeover easier, I'm trying to create a view on the existing data, which can be union'ed with new data, to allow both to be used until the final change.

The scenario is that there's a table which stores the following:

WorkID
User
HoursWorked
Project
Number of reports written

It's being modified to become two tables, the first being

WorkID
User
HoursWorked
Project
Flag Legacy

And the second, with one row for every report as specified in the number of reports field in the original table.

ReportID,
WorkID [fk to first table]
.. (extra data about the report)

My issue is deriving the second table as a view, that can then be union'ed with data from the new version's table.

So, my question is, how could I go about selecting a row multiple times as specified by a field within that row as a query that can be created as a view - infact is it possible?

The DB is in Sql Server 2008r2

Thanks for your help!


texts: Why select holds exclusive lock on table?

texts:
I'm currently struggling with a deadlock (graph) in our application.

Both processes execute the same query:

exec sp_executesql N'SELECT [GroupBy1].[A1] AS [C1]
                     FROM (SELECT COUNT(1) AS [A1]
                           FROM   [dbo].[Batch] AS [Extent1]
                           WHERE ([Extent1].[BatchGroupId] = @p__linq__0)
                           AND   ([Extent1].[Status] <> @p__linq__1)
                          )  AS [GroupBy1]',
                   N'@p__linq__0 int, @p__linq__1 int', @p__linq__0=6, @p__linq__1=8515

Here is the index definition:

CREATE NONCLUSTERED INDEX IX_Batch_BatchGroupId_Status
ON dbo.Batch
(
    BatchGroupId asc,
    Status asc
)


texts: query performance gains by removing operator hash match inner join

texts:
While trying to apply the contents of this question below to my own situation, I am a bit confused as how I could get rid of the operator Hash Match (Inner Join) if any way possible.

SQL Server query performance - removing need for Hash Match (Inner Join)

I noticed the cost of 10% and was wondering if I could reduce it.
See the query plan below.

This work comes from a query thad I had to tune today:

SELECT c.AccountCode, MIN(d.CustomerSID)
FROM   Stage.Customer c
INNER JOIN Dimensions.Customer d  ON c.Email = d.Email
                                  OR (
                                          c.HomePostCode = d.HomePostCode
                                       AND c.StrSurname = d.strSurname
                                                                    )
GROUP BY c.AccountCode

and after adding these indexes:

---------------------------------------------------------------------
-- Create the indexes
---------------------------------------------------------------------

CREATE NONCLUSTERED INDEX IDX_Stage_Customer_HOME_SURNAME_INCL
ON Stage.Customer(HomePostCode ,strSurname)
INCLUDE (AccountCode)
--WHERE HASEMAIL = 0
--WITH (ONLINE=ON, DROP_EXISTING = ON)
go

CREATE NONCLUSTERED INDEX IDX_Dimensions_Customer_HOME_SURNAME_INCL
ON Dimensions.Customer(HomePostCode ,strSurname)
INCLUDE (AccountCode,CustomerSID)
--WHERE HASEMAIL = 0
--WITH (ONLINE=ON, DROP_EXISTING = ON)
go

CREATE NONCLUSTERED INDEX IDX_Stage_Customer_EMAIL_INCL
ON Stage.Customer(EMAIL)
INCLUDE (AccountCode)
--WHERE HASEMAIL = 1
--WITH (ONLINE=ON, DROP_EXISTING = ON)
go

CREATE NONCLUSTERED INDEX IDX_Dimensions_Customer_EMAIL_INCL
ON Dimensions.Customer(EMAIL)
INCLUDE (AccountCode,CustomerSID)
--WHERE HASEMAIL = 1
--WITH (ONLINE=ON, DROP_EXISTING = ON)
go

this is the new query:

----------------------------------------------------------------------------
-- new query
----------------------------------------------------------------------------

SELECT *
FROM (
SELECT AccountCode
     ,RO=ROW_NUMBER () OVER (PARTITION BY AccountCode ORDER BY CustomerSID)
     --,CustomerSID=MIN(CustomerSID) OVER (PARTITION BY AccountCode ORDER BY AccountCode)
       ,CustomerSID
FROM (
          SELECT c.AccountCode, D.CustomerSID
       FROM   Stage.Customer c
       INNER JOIN Dimensions.Customer d  ON c.Email = d.Email

          UNION ALL

          SELECT c.AccountCode, D.CustomerSID
       FROM   Stage.Customer c
       INNER JOIN Dimensions.Customer d  ON c.HomePostCode = d.HomePostCode
                                        AND c.StrSurname = d.strSurname
) RADHE
) R1
WHERE RO = 1

This has reduced the query execution time from 8 minutes to 1 second.

Everybody is happy, but still I would like to know if I could get more done,
I.e. by somehow removing the hash match operator.

Why is it there at the first place, I am matching all the fields, why hash?


texts: I have a AG with 2 nodes in a site in sync mode and 1 offsite in async mode.  I'm having trouble understanding how the logspace is maintained in this scenario.  For instance, I'm taking log backups every 15 mins on the primary server and on the secondary server in sync modem.  If I move my log backups off to the secondary node (in sync mode) does this mean it will release space on the primary node too, or do I still need to run tran backups on the primary node, which will in turn release space on the secondary nodes too?


texts:
I am using a solution of backblaze B2 / duplicity / duply to create backups of my databases. I upload full backups and incremental backups to B2 using duplicity via duply. The incrementals are done similar to rsync.

My server is 100GB and my databases are currently 70GB, but I don't suspect them to grow much. I dont have a master/slave setup. I want a backup solution that allows me to backup the 70GB of databases but not require very much space during the backup process.

I decided that I could just backup the whole /var/lib/mysql directory instead of using mysqldump or other backup methods which requires a full backup persist on disk.

I read that in order to do this method I would have to completely turn off the mysql server, do the backup, and turn it back on. I am OK with that because duplicity does incremental backups which should only take a few minutes.

I have ran SET GLOBAL innodb_max_dirty_pages_pct = 0; in order to make shutdown/starup as fast as can be and verified via show global status like '%dirty%'; that Innodb_buffer_pool_bytes_dirty hovers around 0.

I also understand the caveat for InnoDB tables is that they require the exact same mysql version in order to properly restore. Is that still the case?

Is there anything I am missing and will I have a near 100% confidence that if I use the exact mysql version (mariadb in my case) that at least one of my hundreds of incremental backups will restore.


texts:
I tried to take the backup of common_schema in Server version: 5.5.44 MySQL Community Server (GPL).

mysqldump -u root -p common_schema > common_schema_bkup.sql

I got the following error:

  "mysqldump: Got error: 1356: View 'common_schema._bare_grantee_grants'
  references invalid table(s) or column(s) or function(s) or
  definer/invoker of view lack rights to use them when using LOCK
  TABLES"

How can I rectify this?


texts: Is it possible to set a timer for autovacuum in PostgreSQL

texts:
I am running PostgreSQL 9.4.1. I am currently cleaning up data, which requires me to run expensive queries. It seems like my tables get "locked" up every day at the same time in the afternoon. My queries don't run during at this time and I can only resume after an hour of waiting. After researching why this could be, I am assuming that my database is starting autovacuum processes, which locks up my datatables.

Is it possible to schedule a time for autovacuum or to set a time where autovacuum should not run? I would like to avoid turning the autovacuum off.


texts: What query can I perform to determine if I am running on a RAC system?  If I'm on a RAC, I need to query from GV$* rather than V$*.


texts:
I'm getting the error: 'ORA-00979: not a group by expression' when I try to run this query.

select empno, empname
from emp.employee
group by empno, empname
having empmsal > avg(empmsal);

I'm attempting to find employees with a salary above the average.
Can you see anything wrong with the query?

Sorry if this is obvious. I'm new to sql.


texts:
Getting the following errors periodically from SP_Send_DBMail:

  Message
  The mail could not be sent to the recipients because of the mail server failure.
  (Sending Mail using Account 1 (2016-08-15T16:46:17).
  Exception Message: Cannot send mails to mail server. (The operation has timed out.).

What's odd is that the e-mails do get sent out successfully the first time, then due to the default DB Mail Account configuration, the "retry" kicks in and a second e-mail gets sent out so the users are receiving duplicates. In other words, even though we receive the error, the e-mail does actually go out successfully. Then the default account configuration has a Retry Count of 1 which results in a duplicate e-mail to the end-users since the system thinks the e-mail was not sent out successfully (due to the timeout error).

I have tried adjusting the timeout of the DB Mail account to 60 seconds but the issue persists.

The Exchange Server in use does not seem to be under great stress or load to cause this kind of problem.

Any ideas would be greatly appreciated!


texts:
I am creating a little mulitplayer game. The game creates a session where players can join in. Then the session has to store some data provided in the game.

Should I create a new session table dynamically or a one table with the col sessionId where I store the data from all sessions?

Easier and better structured would be the dynamic solution, but I have heard this is not the best practise.


texts: Grant permissions to run an SQL server job

texts:
I have a job on my MSSQL server 2005, that I want to allow any database user to run.

I'm not worried about security, since the input to the job's actual work comes from a database table. Just running the job, without adding records to that table will do nothing.

I just can't find how to grant public permissions to the job.

Is there any way to do this? The only thing I can think of at this point is to have the job constantly running (or on a schedule), but since it only needs to do any actual work rarely (maybe once every few months) and I do want the work to be done as soon as it exists, this does not seem to be an optimal solution.


texts:
I have syntax error in UNION ALL line 8.. what is the mistake? I'm using mariadb, (Innodb)

SELECT
'Süre Bitiş Tarihi','Tc Kimlik No', 'Kullanıcı Soyadı, Adı','E-posta',
'Telefon','Diğer Telefon','Adres','Şehir','Posta Kodu','Ev Adresi',
'Ev Şehir','Ev Posta Kodu','Barkod', 'Eser Adı', 'Yazar', 'Yeri',
'Kütüphane Adı','Materyal türü','Yerel Yer Numarası','Basım Tarihi',
'Hedef kitle','Dil kodu'

UNION ALL (

SELECT
i.date_due, bo.cardnumber,CONCAT(bo.surname,',',bo.firstname),bo.email,
bo.phone, bo.mobile, CONCAT(bo.address,' ',bo.streetnumber,' ',bo.address2),
bo.city, bo.zipcode, CONCAT(bo.B_address,' ',bo.B_streetnumber,' ',bo.B_address2) ,bo.B_city,bo.B_zipcode , it.barcode,
b.title,b.author,it.location,br.branchname,itype.description,
it.itemcallnumber,b.copyrightdate,bitems.agerestriction,
EXTRACTVALUE(bitems.marcxml,'/record/datafield [\@tag=\"041\"] /subfield [\@code=\"a\"]')

FROM issues i
LEFT JOIN items it on i.itemnumber=it.itemnumber
LEFT JOIN biblioitems bitems on bitems.biblioitemnumber=it.biblioitemnumber
LEFT JOIN biblio b on b.biblionumber=it.biblionumber
LEFT JOIN itemtypes itype on it.itype=itype.itemtype
LEFT JOIN borrowers bo on bo.borrowernumber=i.borrowernumber
LEFT JOIN branches br on br.branchcode=bo.branchcode
where it.holdingbranch=110

UNION ALL (

SELECT
i.date_due, bo.cardnumber,CONCAT(bo.surname,', ',bo.firstname),
bo.email, bo.phone, bo.mobile, CONCAT(bo.address,' ',bo.streetnumber,
' ',bo.address2),bo.city, bo.zipcode,
CONCAT(bo.B_address,' ',bo.B_streetnumber,' ',bo.B_address2),
bo.B_city,bo.B_zipcode , it.barcode, b.title,b.author,it.location,
br.branchname,itype.description,it.itemcallnumber,b.copyrightdate,
bitems.agerestriction,
EXTRACTVALUE(bitems.marcxml,'/record/datafield [\@tag=\"041\"] /subfield [\@code=\"a\"]')
FROM old_issues i
LEFT JOIN items it on i.itemnumber=it.itemnumber
LEFT JOIN biblioitems bitems on bitems.biblioitemnumber=it.biblioitemnumber
LEFT JOIN biblio b on b.biblionumber=it.biblionumber
LEFT JOIN itemtypes itype on it.itype=itype.itemtype
LEFT JOIN borrowers bo on bo.borrowernumber=i.borrowernumber
LEFT JOIN branches br on br.branchcode=bo.branchcode
where it.holdingbranch=110
)
);

I don'T want this output

ef kitle | Dil kodu |
| Süre Bitiş Tarihi   | Tc Kimlik No | Kullanıcı Soyadı, Adı     | E-posta | Telefon | Diğer Telefon  | Adres | Şehir  | Posta Kodu | Ev Adresi | Ev Şehir  | Ev Posta Kodu | Barkod | Eser Adı  | Yazar | Yeri | Kütüphane Adı    | Materyal türü   | Yerel Yer Numarası  | Basım Tarihi  | Hedef kitle | Dil kodu |
| Süre Bitiş Tarihi   | Tc Kimlik No | Kullanıcı Soyadı, Adı     | E-posta | Telefon | Diğer Telefon  | Adres | Şehir  | Posta Kodu | Ev Adresi | Ev Şehir  | Ev Posta Kodu | Barkod | Eser Adı  | Yazar | Yeri | Kütüphane Adı    | Materyal türü   | Yerel Yer Numarası  | Basım Tarihi  | Hedef kitle | Dil kodu |
| Süre Bitiş Tarihi   | Tc Kimlik No | Kullanıcı Soyadı, Adı     | E-posta | Telefon | Diğer Telefon  | Adres | Şehir  | Posta Kodu | Ev Adresi | Ev Şehir  | Ev Posta Kodu | Barkod | Eser Adı  | Yazar | Yeri | Kütüphane Adı    | Materyal türü   | Yerel Yer Numarası  | Basım Tarihi  | Hedef kitle | Dil kodu |
| Süre Bitiş Tarihi   | Tc Kimlik No | Kullanıcı Soyadı, Adı     | E-posta | Telefon | Diğer Telefon  | Adres | Şehir  | Posta Kodu | Ev Adresi | Ev Şehir  | Ev Posta Kodu | Barkod | Eser Adı  | Yazar | Yeri | Kütüphane Adı    | Materyal türü   | Yerel Yer Numarası  | Basım Tarihi  | Hedef kitle | Dil kodu |


texts: MySQL Cluster Master Master Replication - broken Leg

texts:
I have two MySQL Clusters which normally operate with Master - Master Replication between two of their nodes.

Site 1 gets the majority of the write traffic, with Site 2 writing small amounts of isolated tables. The Auto Inc's are configured differently, so they can not clash.

Site 1 App 1 -> Site 2 App 1 replication is working fine, and was started using the recommended method on MySQL's website.

I had Site 2 App 1 replicating to Site 1 App 1 using the SHOW MASTER STATUS method, and starting the replication from that position.

Last week, Site 2 App 1 was restarted, and Site 1 -> Site 2 replication restarted fine. Site 2 App 1 -> Site 1 App 1 was attempted to restart using SKIP ERRORS to ignore the cluster log file break error (I can't remember the name).

This replication keeps breaking with:

  Error 'Can't write; duplicate key in table ''' on query. Default database: ''. Query: 'COMMIT'

I can't do the normal method (getting the epoch from ndb_binlog_index), because it doesn't return any files. Any time I try the SHOW MASTER STATUS method, it brings up the error above. I've even tried doing it via Site 2 App 2 -> Site 1 App 2, and it still fails.

Any ideas?


texts: Best practices for change management with indexes

texts:
Our IT shop is first starting to build a group of DBA's. All of us (myself included) have come over from the application development/architecture world, so the DBA world is still fairly new to us.

Along with building a DBA group, we are looking to build change manage procedures and processes (hopefully based on best practices) for when we need to move changes.

I've found the following post which is helpful for mostly trigger, stored procedure, and/or DDL changes. But it doesn't necessarily address indexes or vendor databases.

We have a mix of both our own and vendor databases. In our case some of the vendors (though not all) are working with our company to build the database(s) and applications. We are in the process of performance testing our applications now before they "go live". Thus we are analyzing indexes (or the lack thereof) pretty heavily.

As we come across indexes that we feel should be made, how do we best deal with change management with regard to these, both for our own databases as well as for any vendors?

What do you do in your shop? I'm worried less about tools then about the process.

  EDIT: So far, I'm appreciating the feedback, comments, and answers for this question. I have noticed that some of the answers are a bit
  tool specific. I'm looking for more "agnostic" practices, if that can
  be had.

  However if agnostic is not possible, then for tool sets, we use IBM
  DB2 LUW (and that actually on AIX) mostly. We have some DB2 on Windows
  and DB2 for i (IBM's i5/OS), but we are mostly AIX DB2. We do use
  source control, specifically Subversion.

  Again, looking for general best practices, but above is what we use
  that would be vendor specific.

  EDIT:
  Current Decision: We intend to track our reasoning as well as our changes. So we are going to open an issue in our issue-tracking
  software (which in our case is JIRA). Now we can add in documentation
  as to what priority the change has, data that backs up what the change
  should be, the change, and the results of the change from another
  environment where the change was tested.

  We then also intend to keep track of our changes in scripts in SVN
  (much like was suggested below). This way we can track what version of
  what exists where. This can be recorded in our JIRA issue (and in any
  other auditing software we use, ie. pasted links). We can know with
  more certainty what change went to what environment and why. We can
  then also track if the index was something we added beyond the vendors
  implementation or ahead of their implementation, etc.)


texts: Shrink data MDF file on server running on low disk space

texts:
Before proceeding, I know the cons of doing this and have read many questions on same. But since we are here on urgent need and adding disk would take over a couple of days and the DB in question being not that critical, so is there a way I can release the unused space from database.

Below screenshot says unused space as 36 GB, but when I execute sp_spaceused it says unused as some 400 MB but unallocated much more.

I waited for almost 4 hours, trying to shrink in chunks, but not even a single MB got shrinked.

Is there a way this can be done or how can I manage the unallocated space?

Please advise.


texts: Access: How to Combine Data from Two Rows in a Query

texts:
I have a base dataset formatted like the one below.

Sex1    | Sex2    | Number|
F       |         | 5     |
M       |         | 8     |
M       | F       | 5     |
F       | F       | 3     |
M       | F       | 2     |
F       | M       | 1     |
F       | F       | 9     |
M       | M       | 3     |

I'd like to use SQL to create a query that produces totals from the price by Gender composition like so:

Sex | Total Number
F   |    5
M   |    8
MF  |    8
FF  |    12
MM  |    3

However, I'm struggling to find a way to pull the FM and MF categories into the same row. The code I have that leaves them distinct is of the form

SELECT Sex1 & "" & Sex2 AS Sex, Sum(Number) AS [Total Number]
FROM TableName
GROUP BY Sex1 & "" & Sex2

Is there a simple way to combine the rows?


texts:
We plan to backup our SQL Server 2012 databases every night, FTP the backup files to our test servers and restore them. Then deploy the latest schemas to those databases. The reason for this is so we have the latest data with the latest schemas to test on.

I'm using Ola Hallengren's SQL Server Maintenance Solution to back up the databases and Core FTP to FTP the backup files.

The problem is that Ola Hallengren's SQL Server Maintenance Solution puts each backup file in a different folder and the Core FTP has been setup to FTP all the backup files from a single folder.

As I see it, I can:

change the location the databases are backed up to
change the way Core FTP is setup, or
add some SQL script to the main store procedure to copy the backup files to a single folder
find new FTP software that can FTP files from many subfolders with a
single folder (any suggestions?)

Regarding #1, on his website, Ola Hallengren doesn't recommend changing the backup location

  DatabaseBackup backs up to a directory with the SQL Server instance
  name, the database name, and the backup type in the directory path.
  Can I change this behavior?

  DatabaseBackup has been designed to delete backup files only when the
  backup and verification were successful. Even then, DatabaseBackup
  deletes only backups of the same instance, database, and type.
  Therefore, you’re guaranteed to always have the most recent backup on
  disk. If you remove any of this information from the directory path,
  you no longer have this guarantee.

  This is because of how xp_delete_file works: xp_delete_file is the
  extended stored procedure that DatabaseBackup (and the maintenance
  plans) use to delete backup files. xp_delete_file deletes backup files
  based on a directory, a file extension, and a modified date.

Hopefully, there will be a reliable way to copy all the backup files to a single folder -- even when we add a new database.

EDIT: changed title of the question - used to be "Move SQL Server backup files to a single folder"

EDIT: added option #4 ("find new FTP software...")


texts: Gathering all statements executed in PostgreSQL

texts: How to get all the queries ran in PostgreSQL 9.1 database without using log_statement setting


texts: Why doesn't SQL Server have any missing index requests in the DMVs or Query Plans?

texts:
I have a SQL Server database where the queries are pretty slow, and there's a lot of locking and blocking.

When I look at the missing index DMVs and query plans, there aren't any suggestions.

Why is that?


texts: SQL Server replication: can I modify contents of a snapshot?

texts:
I have a merge replication which I was unfortunate enough to start modifying, in a way that required the subscriptions to get re-initialized. It makes very good sense and all, since I was touching some filters. But in my case, I changed something and then changed it back, so I know that the result of the new snapshot and new initialization will be identical to what I had.

However, the snapshot is re-created and the subscription is marked for re-initialization. And now the snapshot fails to get re-applied because there are data in subscriber's tables and the .bcp files in the snapshot have some data to load (same, actually, as we know). I can empty the .bcp file and then it seems to move on to the next table, so that's my strategy for now. I just wonder if anybody sees any pitfalls here. I suspect they are many and deep, just that my lack of experience doesn't let me see them.


texts: Reset default value for many foreign keys in SQL Server

texts: I need to reset the foreign keys default values to null for a big database by using some sql queries. My research in the stackoverflow website got me to drop the existing default values contraints and add new ones (https://stackoverflow.com/questions/15547210/modify-default-value-in-sql-server). I am wondering how can I have this work done automatically only for foreign keys ?


texts: Divide, round and replace in one select statement - tranformation from bytes to GB in SQL Server

texts:
I have a database which has me a value, a sharesize in bytes and a sharename. Which is pretty accurate but not useful for my enduser. Therefore I transformed the value to GB.
Which worked great by using a simple divide.
lets assume my DB consists of two columns. Sharename and Sharesize.
Sharename | Sharesize

Share 1   |71685107549

Now I run this:
SELECT TOP (1000)
      [Sharename]
      ,[ShareSize]
      ,(ShareSize / 1e+9) SharesizeGB
  FROM [mydb].[dbo].[myshares]

The output is:
ShareSize   SharesizeGB
71685107549 71,685107549

Now I need to replace the "," with a "dot" and round the result to just have two digits after the dot.
Is that possible using only a select statement ?
For my example its not needed to divide by 1024.


texts: What is the cost of adding a foreign key in mysql?

texts:
I have an application which has a product_def table defining all products.
The recrods in product_def are fixed and  will never be deleted.

CREATE TABLE `product_def` (
`product_id` INT( 5 ) UNSIGNED AUTO_INCREMENT ,
`product_name` VARCHAR( 20 ) DEFAULT NULL ,
PRIMARY KEY ( region_id ),
) AUTO_INCREMENT=1 ENGINE=innodb CHARACTER SET utf8 COLLATE utf8_general_ci   ;

I have another order_records table which contains 5 products in a record.
This order_records table will be gigantic. I am wondering whether I shall use foreign keys in order_records to refer to product_def table.
What is the cost to use foreign keys in a table in consideration of data size and searching or updating speed?
My case here is that the records in product_def table will never be deleted, therefore data integrity is not the concern here.

My questions are :

(1) Assume order_records table has records 10G in size without those 5 foreign keys. Then how big order_records will be if I add 5 foreign keys to the table?

(2) What is the performance difference in updating and selecting from database after adding 5 foreign keys?
(The table order_records will be extremely huge.)

CREATE TABLE `order_records` (
`order_id` BIGINT( 5 ) UNSIGNED AUTO_INCREMENT ,
`order_1_product_id` INT( 5 ) UNSIGNED  NOT NULL ,
`order_2_product_id` INT( 5 ) UNSIGNED  NOT NULL ,
`order_3_product_id` INT( 5 ) UNSIGNED  NOT NULL ,
`order_4_product_id` INT( 5 ) UNSIGNED  NOT NULL ,
`order_5_product_id` INT( 5 ) UNSIGNED  NOT NULL ,
`name` VARCHAR( 20 ) DEFAULT NULL ,
PRIMARY KEY ( order_id ),
FOREIGN KEY ( order_1_product_id ) REFERENCES product_def(product_id)  ON UPDATE CASCADE
   ???  use foreign key  ????
FOREIGN KEY ( order_5_product_id ) REFERENCES product_def(product_id)  ON UPDATE CASCADE
) AUTO_INCREMENT=1 ENGINE=innodb CHARACTER SET utf8 COLLATE utf8_general_ci   ;


texts: there is some question about mysqlbackup

texts:
there is some question about oracle backup tools  mysqlbackup;
Q1:where can i find ibbackup_logfile , i check mysqlbackup log .it appeared " /usr/openv/meb_bkdir/datadir/ibbackup_logfile"
but when i location the directory /usr/openv/meb_bkdir/datadir, i can't find the file ibbackup_logfile.
Q2: What the meaning of apply_log_done parameter in backup_variables.txt
Q3: What is meaning of binlog_position parameter in backup_variables.txt