texts: MSAccess: creating a new row in a related table and linking it back to the "master" table

texts:
In a hypothetical 'cars' database, assume there's a model table and a make table:

models
  modelID int
  model varchar
  make int

makes
  makeID int
  make varchar

Model's make column has a foreign key relation to the make table's primary key. You can use a query against make to populate the 'make' combo box on the model form. If you enter a model for which there isn't a corresponding make row yet, you'd want it to insert the new make (since the input doesn't match any existing makes.make value), then insert the model row with the new make row's primary key in the models.make column. Pretty basic stuff.

I can't seem to figure out how to do it easily in Access 2016, though. If I use the form wizard to create the model management form and feed it either the makes table or a query as a source for the makes combo box element, I can get the list of makes to populate the fill list, but it chokes if I try to enter a make that isn't in the list. It won't let me disable the 'limit to list items' property of that interface element unless I include the makes.makeid column not just in the form's data sources but as another visible item in the box itself. This doesn't fix the insert problem and also breaks the new entry behavior when the make does exist.

Is there an obvious way to do this out of the box in Access 2016? The only solutions I see are

write a macro to insert the new make if it doesn't exist, catch the new primary key, and feed it back into the model insert
change models.make to varchar and link it directly to makes.make
turn the combo box into a restricted list box, use a different form to populate makes

None of these are particularly elegant. I've watched some tutorials and looked at their sample databases and haven't seen this scenario addressed in any useful way. Is there a simple way to use another table as a data source and create new rows in it on-the-fly and I'm just overlooking it, or is this really not something that Access does well natively?


texts: Postgresql - Auto vacuum is disables in conf file, but still everyday its running

texts: Im using Postgresql 9.2. I have disabled auto vacuum in Conf, but still, I can see this auto vacuum is running on few tables every day. Can anyone help me find out why its running?


texts: Copy database wizard-generated job blows up log file; alternative?

texts:
I've recently upgraded our deployment environment to include a staging server. Said server has a database on the same server as our production database. I am trying to set it up to copy data from the production database on a nightly basis.

The production database is set to full recovery model, with hourly trans logs backups and a nightly full backup. It is ~7gb, and its log file sits somewhere around 700mb.

I tried to use the "Copy Database" wizard to copy the prod db to staging and create a job to do so in the future. Upon the initial run from the wizard and any subsequent runs of the job the staging log file blows up to 15-17gb. I assume this is a result of it being in full recovery mode and the method with which it copies the database (an enormous amount of inserts?).

My question is, firstly, why would a built in function allow for log file growth like this? It seems dangerous. And, secondly, what is the best way to go about copying the data to the staging server? I would restore it from the nightly production backups, but the file name of the backup files is dependent on the date (Productiondb-2014-11-05-2205.bak), so I don't know how to script it.


texts: Migrating a maintenance plan from ms sql 2008 to 2016

texts:
I have a bunch of maintenance plans that I want to copy/migrate to sql server 2016. I am able to connect to Integration Services on sql server 2016 but I get the following error when expanding 'MSDB' folder under 'Stored Packages' folder in Integration Services:

The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in SQL Server 2008 R2 Books Online.
Login failed for user 'X'. (MsDtsSrvr)

I assume the configuration is ok since the plans have been there for years now. Is there other ways to deal with maintenance plans without having integration services involved?


texts:
I'm just wondering what might can be wrong

I was trying to round a date by its quarter. Beside some hacks I found the function DATEROUND, which I was thinking of based of documentation is able to do so. So I've build up a little test:

SELECT DATEROUND(qq, now());

which failed with

  Feature 'dateround' not implemented.

Is this a bug inside documentation or did I just miss to enable something? Or is there maybe another easy solution without a case construct for getting this function?


texts:
I have following code for creating gist index

CREATE INDEX web_stats_project_107_properties_crosspromo_gist_idx
    ON web_stats_project_107 USING gist (properties)
    WHERE properties @> '"block_level_0"=>"cerber-head"'::hstore
        AND properties @> '"block_level_1"=>"head"'::hstore
        AND properties @> '"block_level_2"=>"cross-news"'::hstore
        AND properties ? 'block_level_5'::text
        AND (NOT (properties ? 'block_level_6'::text));

It works smoothly when I use it directly.

But I need to use it inside the trigger with some variables. And here the problem comes.

I tried two variants of code:

createIndex := 'CREATE INDEX idx_'||Fulltable_name||'_properties_crosspromo_gist
ON public.'||Fulltable_name||' USING gist (properties)
WHERE properties @> '"block_level_0"=>"cerber-head"'::hstore
AND ...;

and

createIndex := 'CREATE INDEX idx_'||Fulltable_name||'_properties_crosspromo_gist
ON public.'||Fulltable_name||' USING gist (properties)
WHERE (((((properties @> '"block_level_0"=>"cerber-head"'::hstore)
AND ...;

And I even can't apply it for DB because of syntax error

  ERROR:  syntax error at or near "=>" LINE 32: ...perties) WHERE
  (((((properties @> '"block_level_0"=>"cerber-...

or

  ERROR:  syntax error at or near "CREATE" LINE 32:
  createIndex := 'createIndex := 'CREATE INDEX idx...

Seems I have to escape some symbols with some characters.

Any ideas how to do that?


texts: How can I detect what's changing my scoped configurations?

texts:
I'm noticing that I have a database that keeps resetting the scoped configuration , namely maxdop.

Is there any logs that show who or what process is causing these configuration changes?
I'm on Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4


texts: Event Notification / Service Broker - no inserts anymore, "previously existing connection with the same peer"

texts:
I've been working on getting several servers to feed Event Notifications to a central server.  Had everything working yesterday, then managed to break it.  Dropping and recreating the endpoint/route/event notification on the "sending" server (testserver1 below) doesn't fix it.

The only changes I made were a slight modification to the stored procedure to not insert some events, and I added a trace event, TRC_DEPRECATION.

On the sending server, I see nothing in sys.transmission_queue.

On the receiving server, I see nothing in the SB queue, nor in sys.transmission_queue.

When I run a profile trace, I get the following message:

  A previously existing connection with the same peer was found after
  DNS lookup. This  connection will be closed. All traffic will be
  redirected to the previously existing connection. This is an
  informational message only. No user action is required. State 116.

Looking online, I see someone who had the exact same problem; his fix was to restart the service on the initiator.  While doable, I'd rather try and solve the underlying issue.  Thanks in advance.

Here's the ack route on the receiving (enrepository):

CREATE ROUTE [ENAudit_ReturnRoute_testserver1]
WITH SERVICE_NAME = 'http://schemas.microsoft.com/SQL/Notifications/EventNotificationService',
BROKER_INSTANCE = '316C0E13-F1AD-4CFD-AA4E-006E8AD5EF3C',
ADDRESS = 'TCP://testserver1:4022';

Here's the code on the sending server:

CREATE ENDPOINT ENAudit_Endpoint STATE = STARTED
AS TCP (LISTENER_PORT = 4022)
FOR SERVICE_BROKER (
    AUTHENTICATION = WINDOWS
    , ENCRYPTION = SUPPORTED
);

CREATE ROUTE [ENAudit_DestinationRoute]
WITH SERVICE_NAME = 'AuditService'
, BROKER_INSTANCE = '5E885B6D-D24D-4FEB-8E64-0F8D1DE19605'
, ADDRESS = 'TCP://enrepository:4022';

CREATE EVENT NOTIFICATION [ENAudit_ServerDDLEvents] ON SERVER FOR
    ALTER_SERVER_CONFIGURATION
    , DDL_SERVER_LEVEL_EVENTS
    , DDL_ASSEMBLY_EVENTS
    , DDL_DATABASE_SECURITY_EVENTS
    , DDL_EVENT_NOTIFICATION_EVENTS
    , DDL_FUNCTION_EVENTS
    , DDL_PARTITION_EVENTS
    , DDL_PROCEDURE_EVENTS
    , DDL_PLAN_GUIDE_EVENTS
    , DDL_SSB_EVENTS
    , DDL_SYNONYM_EVENTS
    , DDL_TRIGGER_EVENTS
    , DDL_TYPE_EVENTS
    , DDL_XML_SCHEMA_COLLECTION_EVENTS
    , DDL_INDEX_EVENTS
    , DDL_TABLE_EVENTS
    , DDL_VIEW_EVENTS
    , TRC_DEPRECATION
TO SERVICE 'AuditService', '5E885B6D-D24D-4FEB-8E64-0F8D1DE19605';


texts: Does PostgreSQL allow multiple grantees in GRANT commands?

texts:
Looking at the PostgreSQL docs it seems that the GRANT command only grants to a single user or role. In fact it doesn't seem to be PostgreSQL alone.

Isn't possible to do something like GRANT SELECT UPDATE ON atable TO mary, bob, sue?

It looks like a major omission to me, or is there some reason, historical or practical for it?


texts: in place updates causes forwarded records

texts:
I am well aware what a forwarded record within a heap is. Since I want to keep forwarded records at 0, we decided to update only on columns that could not be extended.

Recently on my system I encountered forwarded records.
Table design is like this:

CREATE TABLE dbo.test (
 HashValue BINARY(16) NOT NULL,
 LoadTime DATETIME NOT NULL,
 LoadEndTime DATETIME NULL,
[other columns that never get updates]
) WITH(DATA_COMPRESSION=PAGE);

The insert statements ALWAYS bring HashValue AND LoadTime. I checked the query logs.
I insert a value of '9999-12-31'.

Now system performs an update on LoadTime like this:

;WITH CTE AS (
SELECT *, COALESCE(LEAD(LoadTime) OVER(PARTITION BY HashValue ORDER BY LoadTime) ,'9999-12-31') as EndTimeStamp
)
UPDATE CTE SET LoadEndTime = EndTimeStamp;

Since the LoadEndTime column is always filled, there should be no extension of that column within the row when the update is executed. It should be an in place update. Still I get forwarded records always after that process... It doesn't make sense to me.

Insert Statement is like this:

INSERT INTO dbo.test (HashValue, LoadTime,LoadEndTime)
SELECT HASHBYTES(...), GETDATE(), '1900-01-01'

So there is already a dummy value. Even with compression the fixed and variable representation of datetime '1900-01-01 00:00:00' should be 8 bytes.


texts: Can sharding speed up insertion performance in postgresql

texts: Referenced from this question, the most popular answer does not mention sharding. As far as I know, postgresql will acquire auto-inc lock every time it inserts, so I think sharding can improve insertion performance. Am I right? If sharding can really improve performance, how does it benefit and cost compared other methods?


texts: How to fix discrepancies between publisher and subscription data?

texts:
I have a transactional replication between two SQL Server 2008 SP1 servers, both on Windows 2003 Server. I discovered that there are discrepancies between some of the tables of the main database and the subscription database.  I've done a manual validation over the record counts and there are small differences. I know there's a latency between the two sides, so that's not to worry too much, but there is a specific table that will not synchronize no matter what (new rows appear, but some specific older rows do not appear, so they look like they were removed after synchronization).

The situation is that the subscription database is not read-only, so an application may read and also write data to this database.

Now I'd like to know what solutions would you use for synchronizing the two sides.
I can think of two right now:

use the tablediff.exe utility for each article to compare source and target tables and generate scripts
reinitialize the subscription so it will invalidate last snapshot and generate a new one.

Are there any other solutions to fix this?


texts:
I'm seeing ORA-08181: specified number is not a valid system change number and don't understand why.  The error happens around once a month from a process that runs every few minutes - so very rarely. Running oracle 2 node RAC (11g-r2) linux. Scenario:

on instance 1:

get an scn (my_scn) via dbms_flashback.get_system_change_number
use my_scn in some queries with no problems: select ... from X as of scn my_scn
pass my_scn to a parallel pipelined function that is used in a query (Note the time from obtaining the scn in step 1 to passing the scn in step 3 is less than a minute and potentially less than a second.)

on instance 2 (oracle chose the instance based on parallel query) in the parallel pipelined function:

use my_scn parameter in a query like select ... from Y as of scn my_scn
very rarely get ORA-08181: specified number is not a valid system change number

So, my question is: Should an SCN obtained from one RAC node be immediately usable on other RAC nodes?


texts: How to group list of start times by a category?


texts: Pulling Top queries returns NULL in query plan and sql text

texts:
I'm using the following code to pull our top 20 queries (ordered by CPU):

SELECT TOP 20 qs.sql_handle
    ,qs.execution_count
    ,qs.total_worker_time AS [Total CPU]
    ,qs.total_worker_time / 1000000 AS [Total CPU in Seconds]
    ,(qs.total_worker_time / 1000000) / qs.execution_count AS [Average CPU in Seconds]
    ,qs.total_elapsed_time
    ,qs.total_elapsed_time / 1000000 AS [Total Elapsed Time in Seconds]
    ,st.TEXT
    ,qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC

However, I'm seeing this:

Can anyone shed some light as to why the Query Plan and SQL Text are showing up NULL?  Are they some sort of system process or external application?  We are running SQL 2008 R2.

Thanks, as always, everyone!


texts:
I've a logging table with approx 1.500.000 rows, the primary key is an ascending identity and the clustered index is on the primary key. The identity value is auto-generated => records are always added at the end. The average row size is 1570 bytes.

There are a lot of page splits because new rows are added frequently. No rows get updates/deleted and there is a non-clustered index on the table so rows can be selected.
Due to the page splits, the clustered index is always fragmented > 65%.

I wonder my table would benefit of removing the clustered index and make it a heap table?

This is how my table + non-clustered index looks like:

CREATE TABLE [dbo].[LogEntry](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Application] [varchar](20) NOT NULL,
[EntityFullName] [varchar](80) NOT NULL,
[Action] [int] NOT NULL,
[UserName] [varchar](25) NOT NULL,
[TimeStamp] [datetime] NOT NULL,
[EntityId] [varchar](50) NOT NULL,
[WhatChanged] [nvarchar](max) NULL,
CONSTRAINT [PK_LogEntry] PRIMARY KEY CLUSTERED(
   [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY] )
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE NONCLUSTERED INDEX [ID_Application_Entity_FullName_TimeStamp] ON [dbo].[LogEntry]
(
    [Application] ASC,
    [EntityFullName] ASC,
    [TimeStamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
GO

Update:
Someone had enabled auto-shrink behind my back => this will be the cause of the fragmentation


texts: SQL Server history of growth and shrink events

texts:
Is there a way to look at the complete history of growth and shrink events for the last weeks/months on SQL Server 2014 ? (New instances to support, noticed from the ticketing system that they have previous history of "log drive full", so would like to dig into the root cause before it happens again.)

This was useful: Identify File Growth Events . Came up with below query, but it doesn't show any of the "manual" log shrink events, just the "auto" events. Am I doing this the wrong way, any other place to look for past info ?

select
    te.name as event_name,
    tr.DatabaseName,
    tr.FileName,
    tr.IntegerData,
    tr.IntegerData2,
    tr.LoginName,
    tr.StartTime,
    tr.EndTime
--select *
from
sys.fn_trace_gettable(convert(nvarchar(255),(select value from sys.fn_trace_getinfo(0) where property=2)), 0) tr
inner join sys.trace_events te on tr.EventClass = te.trace_event_id
where
tr.EventClass in (93, 95) --can't identify any other EventClass to add here
order by
EndTime desc;


texts: Is there any PowerShell script to get the SA log in history?  I need to do an audit on SA login.


texts: MySQL User Privileges by IP using Wildcards (%)

texts:
To make a long story short, I'm taking over some support for an electronic medical record (EMR) with a MySQL backend.  When troubleshooting an issue with functionality, we discovered the EMR functionality worked on one VLAN (IP Range) but not another.  We started looking at the MySQL users and discovered the database had two user entries.

User#1@192.168.% - Select and Process privileges were granted
User#1@192.168.%.% - Select was the only privilege granted

My question is, what would % vs. %.% do in the range and why would someone set up a privilege set in that manner?  Wouldn't security just work in a top-to-bottom manner in this scenario and the 2nd entry (only Select) be granted?


texts: Detect IP subnet overlap from mySQL database

texts:
I have a database with a list of IP ranges in my corporate infrastructure. I want to identify any subnet ranges that are overlapping. I was using the code below which converts the IPs into decimal numbers then searches for any ending IP that appears in all ranges more than once. I'd run the same code again for the starting_ip the combine the lists. However I've identified a few ranges that are not showing up as a result. Is there a better way to do this?

SELECT
    COUNT(*), supernet, starting_ip, ending_ip, inet_aton(starting_ip), inet_aton(ending_ip)
FROM
    IPLookup.ADsegments
WHERE
    INET_ATON(ending_ip) BETWEEN INET_ATON(starting_ip) AND INET_ATON(ending_ip)
GROUP BY starting_ip
HAVING COUNT(*) > 1 ;

example of a overlap not detected:

supernet, starting_ip, ending_ip

10.12.0.0/16    10.12.0.1   10.12.255.254

10.12.114.0/24  10.12.114.1 10.12.114.254

10.12.128.0/17  10.12.128.1 10.12.255.254

10.12.180.0/24  10.12.180.1 10.12.180.254

10.12.64.0/18   10.12.64.1  10.12.127.254


texts: How can this larger subquery be (much) faster?

texts:
In my organisation Ricoh is the supplier of printers and copiers. Papercut is used for registering which copies are made and for which account. Another application working with Ricoh is registering copies in a different database, but both are somewhat linked.
My organisation wanted a central place to retrieve the total cost so I wrote some queries. Most work fine, but something weird is going on with the one below and I'm unable to figure out why this is failing. What could be the reason of the following issue.
It's hard to make a db<>fiddle as the databases are quite complicated. I'll try to explain the issue here.
In essence there are two main tables. A table maintained by Ricoh containing client identifiers ClientId, a price (cost of the copywork), print timestamps and a job identifier. The table maintained by Papercut also has a timestamp, a printed flag and a reference to the corresponding JobId in its job_comment.
Now consider the following query which gives the total printcost when having printed using the seperate Ricoh application.
SELECT CliendId,
       SUM(ricoh.price) AS cost
FROM
  (SELECT a.SystemId as ClientId,
          a.ProcessInternalUid as JobId,
          a.price
   FROM   ricoh.printhistory AS a
   WHERE a.Exportdate >= '2020-10-01'
     AND a.Exportdate <= '2020-12-12') AS ricoh
INNER JOIN
  (SELECT substring(b.job_comment, 16, 36) AS JobId
   FROM  papercut.printer_usage_log AS b
   WHERE b.usage_date >= '2020-10-01'
     AND b.usage_date <= '2020-12-12'
     AND b.printed = 'Y'
   GROUP BY substring(b.job_comment, 16, 36)) AS papercut ON (papercut.JobId = ricoh.JobId)
GROUP BY ricoh.CliendId

If I run this query I have instant results, where the subqueries ricoh and papercut have 96.321 and 9.354 rows respectivly.
But if I change the lower bound of the date range to 2020-11-01 the query takes 49 minutes (!). While the subqueries have 46.223 and 4.547 records respectivly.
How can this be? How can a join with larger subqueries result in much faster results? I'm really puzzled by this. What could be a reason for this increase in time? (Then I can try to debug further)
I've done some (initial) debugging and noticed the following:

There is a limit date. If I set the lower bound to anything higher then 2020-10-08, the execution time gets large. I went looking for some troublesome records with that timestamp but found none. Also, choosing any date higher, like even 2020-12-01 results in high execution times.
If I remove the GROUP BY aggregation (and the sum) execution is instantaneous. But if I add an ORDER BY ricoh.price then execution time rises again. Could something with the price be an issue? It's a floating point number. I tried converting it to an integer but the issues remains.

EDIT:
Added the execution plans as requested by user J.D. I'm not really that familiar with these plans. I would guess the main difference is that Hash Match?

'Fast' query: Execution plan
'Slow' query: Execution plan

Also, this query is running on SQL server 2012. And since the database is managed by external firms I cannot add indexes. Is there a way to rewrite the query to force the 'fast' execution plan?


texts: SQL Server: Enable Full-Text Search on model Database

texts:
I want to use the Microsoft Data-Tier Application framework and its associated .Net API to manage the schema of several SQL Server databases, including the model database. However, when I try deploy an updated schema to the model database (using either a SQLCMD script or the DacServices.Deploy(...) method), I receive the following error...

  Cannot use full-text search in master, tempdb, or model database.

Is there any way around this?

EDIT

So, I was going over the generated SQLCMD script with my boss, and he saw this...

IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
    EXECUTE sp_fulltext_database 'enable';

GO

Why would that be needed for a schema deployment? It would be easy enough to strip that block out of the script, but what if I want to use the DacServices.Deploy(...) method to update the schema? I don't see anything in the DacDeployOptions class that seems like it would affect this deployment option.


texts: What real-world problems have you solved with Extended Events?

texts: I have a confession to make: I don't use extended events nearly as much as I've been led to believe I should. Any time I see a blog post on them or read about them in documentation, I say "that's neat, but seems contrived". So I'm wondering what real-world problems have you solved with extended events? In order for it to be useful to everyone, detail the problem and the events session that you set up in as much detail as possible.


texts:
Is it better to use (for perfomance tuning) AUTOCOMMIT = 0 before bulk inserts of data in MySQL, if the insert query looks like

INSERT INTO SomeTable (column1, column2) VALUES (val1,val2),(val3,val4),... ?


texts: Which queries are faster with Postgres than with MySQL InnoDB

texts:
I've read Performance difference between MySQL and PostgreSQL for the same schema/queries. Here is a brief retelling of the article:

  PostgreSQL tables are heap tables (means no clustered index)... a primary key lookup for a (Postgres) table requires hitting the index, lookup up the location in the file then hit the heap table and pull the record. This means a number of pieces of random disk I/O... InnoDB uses a different approach. With InnoDB, the table is a b-tree index (clustered, physically sorted)... less random disk I/O is required for PK lookup... At the same time, an index scan requires traversing two indexes instead of one (index -> PK index -> table row), meaning use of any index other than the primary key ends up being slower and sequential scans are slower still.

Which kind of queries are much faster with Postgres than with MySQL InnoDB?

I understand why PK lookup is much better for MySQL. I DON'T understand:

Why lookup through two indexes (InnoDB, lookup through non-PK index) is much slower? Does it requires two times more I/O or CPU? Can it compensate that huge benefit of PK lookup boost?
Why InnoDB sequential scans are slower?

P.S. Internet says that Postgres is better for complex queries and subquering, but I still don't understand WHY is it better?


texts: Design Sql Server physical configuration from existing VM

texts:
We have 4 virtual machines that we used to test our BI platform (PoC), the final dimensions are:

SSIS (16 vcpu), SSAS (8 vCPU), SSRS (4 VCPU) et Data Engine (24 vCPU)

What are the recommendations to install these components to a physical server?

For SQL Server performance, VMware recommends (chap. 3.5.2) setting the number of cores per socket to one when allocating virtual CPUs to VMs on the vSphere platform". That means that a VM with 16 CPUs will have 16 vCPUs with 1 core each.

Can we calculate physical server dimensions using reverse engineering? I mean: 16 vCPU equals 16 physical core?

The dimensions CPU and RAM of these VM are big so we are asking if it's not more interesting to deploy on physical machines. We need 280 GB of RAM.

The same thing that VMware said here the ratio 1:1 or 1:2 is considered for more performance.


texts: Telemetry_xevents session and auto_stats

texts:
I've noticed on a number of instances that the telemetry_xevents session is full of auto_stats events.

The statistic is not being updated. I've even disabled auto update statistics and auto update asynchronous statistics on development databases but I am still receiving these messages.
Can anyone shed some light on the Loading stats without updating status message?
The environments are SQL Server 2017 CU21.


texts:
Since the progression of this was apparently somewhat difficult to follow the first time around:

I attempted a completely boring, been-done-a-thousand-times-before copy of a database using the copy database wizard with the detach/reattach method.

The copy failed.  The log indicates that it was unable to execute a CREATE VIEW action for a particular view, because the datasource for the view did not exist.  This is interesting in its own right, as the source most certainly exists, and the view(s) in question are fully functional in the source database.  I'm not really clear, just yet, on how significant this is, as I've yet to figure out precisely why this generated an error.

This resulted in the deletion of all non-system user associations from the source database, leaving me with users dbo, information_schema, sys, and guest.  Non-system roles were also deleted.  Schemas were unaffected.

I have since restored the damaged database from backup.  Academically, however, I would like to know the following:

Why would a failed copy operation strip the user associations from the source database?
Is there any sort of maintenance/rebuild/repair operation that could be performed on the source database to repair it?
The loss of the users was immediately obvious, but given the rather mysterious nature of a failed copy job apparently damaging the source database, how concerned should I be about less obvious effects?  In theory, I would expect restoring from backup would alleviate this concern, but do I have any cause to worry about, e.g., the master database?

This is entirely repeatable.  I've made a handful of copies (manually) for the sake of experimenting with this particular issue, and in each case, the failed copy job obliterates the users and roles from the source database.

Removing the views that generated errors allows the copy to complete, and, as one would expect, produces a copy with identical data, users, etc., in addition to leaving the source database unaltered.

If it's important, I've tried rebuilding the indexes of the system databases, as well as the damaged database, to no appreciable effect.

The error generated:

1:00:25 PM,5/28/2013 1:00:25 PM,0,0x,ERROR : errorCode=-1073548784 description=Executing the query "CREATE VIEW [Sourcing].[PermittedArrProducts]
AS
SELECT     dbo.tblArrProducts.ArrProductID, dbo.tblArrProducts.ArrangementID, dbo.tblArrProducts.ContainerTypeID, dbo.tblArrProducts.Quantity
FROM         Sourcing.PermittedArrangements INNER JOIN
                      dbo.tblArrProducts ON Sourcing.PermittedArrangements.ArrangementID = dbo.tblArrProducts.ArrangementID

" failed with the following error: "Invalid object name 'Sourcing.PermittedArrangements'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


texts:
I'm writing scripts in Python for cleaning up a database. It mainly is finding and deleting billions of rows of data based on some logic. I have it running in a loop with a LIMIT that will run until there are no longer any rows affected by a DELETE. It is running on a production database that gets different load during different times of the day, so the time to do these DELETE queries varies based on the load. I don't want to lock up any tables by running a large DELETE, so I've been trying to benchmark the query times for different LIMITs. I found these times varied a lot based on the time of the day, so I started looking into making a dynamic LIMIT that increases or decreases based on the query time. It will increase or decrease between a floor and ceiling based on the the last query time. So the basic logic is

if QUERY_TIME < DECREASE_TIME and LIMIT < MAX_LIMIT:
  LIMIT = LIMIT * 10
elif QUERY_TIME > MAXIMUM_TIME and LIMIT < MIN_LIMIT:
  LIMIT = LIMIT / 10

My question is if this is a good strategy? I'm still learning a lot of optimizations for database work, so I'm not sure if this is a common strategy or what pitfalls I may encounter.


texts: Do I need to detach a database to attach it?

texts:
I am trying to move data from one SQL Server server to another (located on another computer). I know of the detach/attach method, but I don't want to disturb the prod environment (detaching a prod database is out of the question). Can't I just copy/paste the MDF and LDF files IF nothing is being actively written to/read from  the prod?

What if there are processes that are reading or writing to the database, what are my options to create an "attach" file without performing detach?

Thank you very much. I read on two questions related to this but neither one applies here.  One suggests using backup/restore, but that seems a bit difficult.


texts: PostgreSQL: how to create both sequence and table simultaneously?

texts:
I need to create a table with a sequence associated to the gid column that would start from a given value, say 300. I'd like to know if there is a shorter way to achieve that:

CREATE TABLE mytable (gid INTEGER NOT NULL, size INTEGER);

CREATE SEQUENCE mytable_gid_seq MINVALUE 300;

ALTER TABLE mytable ALTER COLUMN gid SET DEFAULT nextval('mytable_gid_seq'::regclass);

Thanks for help!


texts: JDBC - accessing a query, then querying that query

texts:
Sorry if the way I phrased that is confusing.

I'm writing a program that acts kind of like a filter - it retrieves an answer to a query, counts the rows, then I want to do a slightly more narrow query. I want to do those queries until I get a count of 0, which means the query just before it is the narrowest I can get. I then use a similar version of that query, except instead of counting I actually retrieve the records.

The best implementation I know of is to just write all of the different queries, put them into different statements and feed those statements into different result statements, and retrieve the counts from there. Then, I use the last one that counted > 0 and use that main query without a count, and use those results.

This seems like a horrible implementation. It seems like I'm just going to be bombing the database over and over. I'd prefer to hit it once, and then narrow the resultset on the client. Any ideas?


texts: How to check programmatically which replica I'm connected to

texts:
I have an AlwaysOn Availability Group on MS-SQLServer (2 replicas) and a C# application which sends queries to the Availability Group Listener.

How can I check which replica is responding my queries?

I know it's possible, because I've seen it in MS educational videos.

Thank you in advance


texts: LocalDB v14 creates wrong path for mdf files

texts:
Recently, I upgraded LocalDB from version 13 to 14 using the SQL Server Express installer and this instruction. After the installation, I stopped the existing default instance (MSSQLLOCALDB) of version 13 and created a new one, which automatically used the v14.0.1000 server engine.

I often use LocalDB for Database Integration tests, i.e. in my xunit tests, I create a (temporary) database which is deleted when the test finishes. Since the new version, unfortunately all my tests fail because of the following error message:

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Users\kepflDBd0811493e18b46febf980ffb8029482a.mdf'

The odd thing is that the target path for the mdf file is incorrect, a backslash is missing between C:\Users\kepfl and DBd0811493e18b46febf980ffb8029482a.mdf (which is the random database name for a single test). The databases are created via the simple command CREATE DATABASE [databaseName] - nothing special here.

In SSMS, I see that the target locations for data, log, and backup are the following:

However, when I try to update the location, I get another error message:

How can I update the default locations so that LocalDB is able to create databases again? It's obvious that LocalDB does not correctly combine the default location directory and the database file name - is there a registry entry that I can edit? Or anything else?

Update after Doug's answer and sepupic's comment

According to this Stackoverflow question, the default location's should also be changeable via the registry. However, if I try to find the corresponding keys "DefaultData", "DefaultLog" and "BackupDirectory", I cannot find them in my registry. Did SQL Server v14 rename these registry keys, or moved these information out of the registry?


texts: How did you setup MySQL Replication with autofailover that is app transparent?

texts:
Recently told MySQL is shelving MySQL Fabric. Interested how others have implemented a MySQL replicated environment that is app transparent.

I am considering using HA Proxy to host virtual IP address for Master, one for slave pool. The using MySQL Failover to monitor replication cluster, auto promote a slave to master. LinuxHA or HAProxy would change which server the virtual IP address points to. Should work for both Master and Slaves.

We're primarily a PHP shop running MySQL DB's on Centos7 Linux.


texts: Create a plan guide to cache (lazy spool) CTE result

texts:
I normally create plan guides by first constructing a query that uses the correct plan, and copying it across to the similar query that doesn't.  However, that is sometimes tricky, especially if the query is not exactly the same.  What is the right way of creating plan guides from scratch?

SQLKiwi has mentioned drawing up plans in SSIS, is there a way or useful tool to assist in laying out a good plan for SQL Server?

The specific instance in question is this CTE: SQLFiddle

with cte(guid,other) as (
  select newid(),1 union all
  select newid(),2 union all
  select newid(),3)
select a.guid, a.other, b.guid guidb, b.other otherb
from cte a
cross join cte b
order by a.other, b.other;

Is there ANY way to make the result come up with exactly 3 distinct guids and no more? I'm hoping to be able to better answer questions in future by including plan guides with CTE-type queries that are referenced multiple times to overcome some SQL Server CTE quirks.


texts:
We have a partitioned tables partitioned on STATUS Column using below partitioning function. So a table will have total two partitions and both are going to different filegroups as per partition scheme.
CREATE PARTITION FUNCTION PART_FN_STATUS(INT) AS RANGE RIGHT FOR VALUES (1000)

NOTE: As per buisness logic, status cannot be greater than 1000. 1000 is Max.
We have a Non Clustered Index on Status Column as well which is an Aligned Partitioned Index, i.e partitioned on Status Column.
When I run below query and observe actual execution plan, I have below questions:
SELECT COUNT(*) FROM ORDERS WHERE STATUS <> 1000

On NCI Seek, when I hover my mouse on it, I can see "Actual Partition Count" as 2. So why it is not doing partition elimination?
On same tool tip, when i checked estimated / actual number of rows read, it is actually the count of 1 partition. So it looks like partition elimination happened?
<> is a Non-Sargable Operator, so how can a SEEK happen on a NCI?
Can a Non-Sargable Operator do a Partition Elimination?

Any help would be appreciated.


texts: MySQL - Database has huge overhead but tables don't

texts:
I have some problems with two MySQL databases. The databases have huge overheads but none of the tables do. When I recreated the tables the overhead just reappeared aswell. I hope you can help me.

Here are some screenshots I took with phpMyAdmin:


texts: SQL server replication - Does not ask for the snapshot folder

texts:
SQL SERVER 2008

When I created a publisher for the first time, it asked for the snapshot folder path. I deleted that replication and tried to recreate the publisher. This time it doesn't even ask for the path for snapshot folder.

Why is that? and how can I force this to ask for a snapshot path.

If I change the path to one replication (that I am going to create, will it change for all the replications that is running on the sql instance?

Thanks for your help


texts: How to make PG query faster even when it is using indices

texts:
I have a PG table with about 30M records. I'm noticing that when I query the table with IN clause that would fetch lot of rows, the query is taking a long time to respond (~3 minutes) Question Although my query is using indices, how can I make the query faster?

mydb=> explain analyze select count(scores), sum(scores) from mytable
       where classes in ('1st','2nd','3rd') and school_year in ('2018');

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1150864.84..1150864.85 rows=1 width=40) (actual time=185454.773..185454.773 rows=1 loops=1)
   ->  Bitmap Heap Scan on mytable  (cost=16998.63..1146247.22 rows=923525 width=6) (actual time=934.546..184704.246 rows=967663 loops=1)
         Recheck Cond: ((classes)::text = ANY ('{1st,2nd,3rd}'::text[]))
         Filter: (school_year = 2018)
         Heap Blocks: exact=448386
         ->  Bitmap Index Scan on mytable_classes_idx  (cost=0.00..16767.75 rows=923525 width=0) (actual time=736.690..736.690 rows=967663 loops=1)
               Index Cond: ((classes)::text = ANY ('{1st,2nd,3rd}'::text[]))
 Planning time: 1.252 ms
 Execution time: 185463.153 ms
(9 rows)

Additional Details

                                                version
--------------------------------------------------------------------------------------------------------
 PostgreSQL 10.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
(1 row)


texts: How do I debug/set up MariaDB SSL connection?

texts:
[ I originally posted this on ServerFault but then was pointed at this community ]

Since moving from MySQL to a newer MariaDB installation on Debian 9, I a struggling with SSL connections.

I have created a CA certificate and installed it on both server and client. I then have created server and client keys and certificates that I signed with the CA certificate.

What works:

I can connect from the client to the server with the mysql client without SSL connections.

I can verify the client certificate with openssl:

$ openssl verify MariaDB_client.crt

MariaDB_client.crt: OK

I understand that MariaDB uses yaSSL, but this still shows that the certificates and CA are correct, I think.

On the server, I have:

MariaDB> show global variables like '%ssl%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| have_openssl        | NO                                   |
| have_ssl            | YES                                  |
| ssl_ca              | /etc/mysql/conf.d/MariaDB_ca.crt     |
| ssl_capath          |                                      |
| ssl_cert            | /etc/mysql/conf.d/MariaDB_server.crt |
| ssl_cipher          |                                      |
| ssl_crl             |                                      |
| ssl_crlpath         |                                      |
| ssl_key             | /etc/mysql/conf.d/MariaDB_server.key |
| version_ssl_library | YaSSL 2.4.4                          |
+---------------------+--------------------------------------+

What does not work:

If I add a .my.cnf file with the following content to the users $HOME directory...

[client]
ssl-ca=/--path--/MariaDB_ca.crt
ssl-cert=/--path--/MariaDB_client.crt
ssl-key=/--path--/MariaDB_client.key

...the connection fails with:

ERROR 2026 (HY000): SSL connection error: unable to verify peer checksum

In the server log, I find (not sure whether this is related):

[Warning] IP address 'ip.of.connecting.client' could not be resolved: Name or service not known

My questions:

How can I narrow down/debug this connection problem?
I found conflicting information about whether I should use the same or different CN for the server and client certificates. Currently, I use the same CN.
Are there particular access rights required for the certificates?


texts: Stored procedure takes 18 seconds on first load, 0 seconds on subsequent loads, waiting several minutes makes it slow again

texts:
I have a stored procedure that returns a lot of data for a business dashboard. On first load it takes 18 seconds, after that it takes 0 seconds, even if I change the input parameters at random it stays at 0 seconds and returns the correct results.

If I wait about three minutes it goes back to 18 seconds. This is not acceptable for the user and I have exhausted every option I know of.

Why would it be slow on the first load but instant after? I understand it may be recompiling the execution plan so on each query I added  OPTION (KEEP PLAN); with no effect. My stored procedure does not have 'with recompile'. I downloaded 'datawizard sql profiler' and set up a trace to show recompiles but it does not show any, even when I have 'with recompile' in the query.

My only other thought is that mssql is doing some kind of result caching, but the query runs fast even when I change the parameters.

Why would it take 18 seconds, then 0, then 18 seconds again after I wait for a while?


texts: Security update failed : The baseline msi is not installed

texts:
Yesterday we had to apply a security update for SQL Server 2008 SP4(
Current version: 10.0.6000.29) (from KB3045311 and MS15-058) and it failed in both the nodes of a Windows failover cluster and both the nodes are of the same version. The log files only say this:

  2015-10-21 02:26:29 Slp: Patch Id KB3045311_sql_engine_core_inst_Cpu64 - NotInstalled on the baseline msi package sql_engine_core_inst_Cpu64.  Detail description of this patch package is: PatchId=KB3045311_sql_engine_core_inst_Cpu64 PatchVersion=10.4.6241.0 BaselinePackageId=sql_engine_core_inst_Cpu64 BaselineVersion=10.4.6000.29; PatchFileName=sql_engine_core_inst.msp PatchCode={511E22C9-E550-4C68-994B-AA3F388A4E2F}
  2015-10-21 02:26:29 Slp: Patch Id: KB3045311_sql_engine_core_inst_Cpu32 - The baseline msi is not installed. The patch package is ignored.
  [...many more of these for different patchIds and clsids...]

The application event logs has this:

  Product: Microsoft SQL Server 2008 Database Engine Services - Update
   '{511E22C9-E550-4C68-994B-AA3F388A4E2F}' could not be installed. Error
   code 1642.

However, the security update for SQL Server 2012 did go through fine which was done at the same time.

Any thoughts about why the SQL Server 2008 SP4 security update is failing?


texts:
When SQL Server Server 2008r2 begins to process a query, I know it needs to parse the SQL Text and create the plan, plus a few other steps.

It is doing all of that in memory OUTSIDE of the buffer pool, correct?

The buffer pool does not come into play until the query is actually being executed to retrieve/manipulate data, correct?

Scenario: (server was not built on configured by me)

SQL Server 2008r2 SP1 - yes it needs to be patched, that is on the radar
96 GB of RAM
MAX Memory set to 92 GB
Buffer Pool sitting just below 80 GB used
PLE over 4 hours
Seeing resource_semaphore WAITS

If my understanding that the memory grant to process the query does not come out of the buffer pool, but the remaining available memory, reducing the MAX Memory should help re-mediate the WAIT type.  My thought is set MAX Memory at 72 GB.  Your thoughts?

Thanks!


texts: Unable to install MySQL 5.7.12 in RedHat in a separate directory

texts:
I am trying to install the MySQL version 5.7.12 into RedHat in a non default data path.

The installation syntax used

/app/mysql/5.7/bin/mysqld --initialize --user=mysql --basedir=/app/mysql/5.7 --datapath=/app/mysql/data

/app/mysql/data is a softlink to /data

I got the following error.

2016-05-19T02:57:28.087915Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicitdefaults_for_timestamp server option (see documentation for more details).
2016-05-19T02:57:28.090891Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2016-05-19T02:57:28.090910Z 0 [ERROR] Aborting


texts: Insert records from one table to other with default serial

texts:
I have two tables with same structure, first column gid as serial, and many other columns after that. I want to insert selected rows from one table to the other. Without serial it is really easy:

insert into all_roads select * from new_roads where add_road = 1;

But with serial as first column in both I do get error:

ERROR:  duplicate key value violates unique constraint

I really do not want to copy serial number from one table to the other. I rather use default serial value for all new inserted records. Is there a way without writing insert which manually lists all the columns except the first - serial?


texts: How to modify maintenance plan using T-SQL script in SQL Server 2008

texts:
How can I modify a maintenance plan in SQL Server 2008 using a T-SQL script?

I want to change the schedule of a maintenance plan and change the path of the .bak file .

All these changes should be done using a T-SQL script.


texts:
I have a new column in SQL Server 2016, on a table of type bit. (I wanted it to be a persisted calculated column but was overruled and asked to make it a bit that is maintained by program code). Its default value is 0.

I used the following SQL excerpt to attempt to set the initial values for existing rows in a dev environment but had to terminate it after 5 minutes.

update dbo.tableName set newColumn =

cast(
    (
        case when
            (
            isNull(colA,'') <> ''
            or isNull(colB,datefromparts(1901,1,1)) > datefromparts(1901,1,1)
            or colC is not null
            or isNull(colD,'') <> ''
            )
        then 1
        else 0
        end
    )
as bit);

The table has about 92,000 rows and about 3,200 should have the value 1 set. (I understand the above would also be setting the remaining 88,800 row values to 0 even though the default constraint has already set them to 0 but a SELECT using the above logic in a WHERE clause executes within 1 second).

My question is why the above should take so long, and how would I identify the root cause? (Should I obtain a query plan and work from there? What would I look for?)

I re-wrote the update as follows and it completed in 3 seconds.

with cteCommonTableExpression (cteIdColumn)
as
(
    select dbo.tableName.idColumn from dbo.tableName
        where
            case when
                (
                isNull(colA,'') <> ''
                or isNull(colB,datefromparts(1901,1,1)) > datefromparts(1901,1,1)
                or colC is not null
                or isNull(colD,'') <> ''
                )
            then 1
            else 0
            end
        = 1
)

update dbo.tableName set newColumn = 1
where dbo.tableName.idColumn in (select cteIdColumn from cteCommonTableExpression);

The following - which I expect should be logically identical to the last section of code - also completes in 3 seconds.

update dbo.tableName set newColumn = 1
where dbo.tableName.idColumn in
(select tn2.idColumn from dbo.tableName tn2 where case when
                (
                isNull(tn2.colA,'') <> ''
                or isNull(tn2.colB,datefromparts(1901,1,1)) > datefromparts(1901,1,1)
                or tn2.colC is not null
                or isNull(tn2.colD,'') <> ''
                )
            then 1
            else 0
            end
        = 1)

Shortly after posting here I obtained the estimated query plan which reported 89% of the cost goes to a clustered index update on the primary key. The new column is not involved in the primary key. The question becomes: why does this update require a clustered index update (especially while the alternative updates apparently do not)?


texts:
I have a table foo.

So we have a hiearchical object structure where on foo has one parent (or none) and a list of children.
What I want is a sql statement that lists all id:s that can be found either traversing upwards and downwards in that hierarchy.
How?
My current attempt is
CREATE PROCEDURE [dbo].[GetChain]
    @starting_id int
AS
BEGIN
    WITH chainIDsUpwards AS
    (
        SELECT id, parent_id FROM foo
        WHERE parent_id IS NULL

        UNION ALL

        SELECT foo.id, foo.parent_id FROM foo
        JOIN chainIDsUpwards p ON foo.parent_id = p.id
    ),
    chainIDsDownwards AS
    (
        SELECT id, parent_id FROM foo

        UNION ALL

        SELECT foo.id, foo.parent_id FROM foo
        JOIN chainIDsDownwards c ON foo.id = c.parent_id
    )

    SELECT * FROM chainIDsUpwards WHERE id = @starting_id
    UNION
    SELECT * FROM chainIDsDownwards WHERE id = @starting_id
END

but it does only give me a single row.
edit
Sample data:

EXEC [GetChain] @starting_id = 5
returns

but I expect:

5, 2
2, 1
1, null
11, 5

Fiddle at https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=aeaf40c57b3940ce725dccf553a8d9e2
edit 2
I thought I had found the correct sql being
CREATE PROCEDURE [dbo].[GetChain]
    @starting_id int
AS
BEGIN
    WITH chainIDsUpwards AS
    (
        SELECT id, parent_id FROM foo WHERE id = @starting_id

        UNION ALL

        SELECT foo.id, foo.parent_id FROM foo
        JOIN chainIDsUpwards p ON p.id = foo.parent_id
    ),
    chainIDsDownwards AS
    (
        SELECT id, parent_id FROM foo WHERE parent_id = @starting_id

        UNION ALL

        SELECT foo.id, foo.parent_id FROM foo
        JOIN chainIDsDownwards c ON foo.id = c.parent_id
    ),
    chainIDs AS
    (
        SELECT id FROM chainIDsUpwards
        UNION
        SELECT id FROM chainIDsDownwards
    )

    SELECT id FROM chainIDs
END

but that will not find any parents for 9. It only finds 9 itself, but not 3 or 1.
Fiddle at https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=10041653e78b52bc8ec0e3c40cfff7a0


texts: Inserting few millions of rows: Does indexing happens along with the insert operation

texts:
I am inserting 50 million records to an existing table with a primary key , and a non clustered key.
My question is whether indexing of those will existing indexes will happen along with the insert operation or not.?
Or do i have to do an index rebuild/reorganize after insert to keep index updated.

My assumptions is when new records comes in data pages will get updated and so will index pages also.

May be this is a silly question,but i have to know this for some other activities for like bulk insert/update i perform in my tables.


texts: What corner cases exist when relying on undocumented behaviour to determine values selected by MySQL for hidden columns in GROUP BY operations?

texts:
Under GROUP BY and HAVING with Hidden Columns, the MySQL manual documents (added emphasis):

  MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

Despite this explicit warning from the developers, some people continue to rely on undocumented behaviour to determine the value that will be selected from a hidden column.

In particular, MySQL often appears to select the "first" record from each group (where the notion of "first" is itself undocumented, such as the oldest record on some storage engines or according to some sort order applied to a materialised table from a subquery).  I've seen this exploited to retrieve, for example, a groupwise maximum:

SELECT * FROM (
  SELECT * FROM my_table ORDER BY sort_col DESC
) t GROUP BY group_col

For completeness, the same can be accomplished in a standard and documented fashion with a simple join:

SELECT * FROM my_table NATURAL JOIN (
  SELECT   group_col, MAX(sort_col) sort_col
  FROM     my_table
  GROUP BY group_col
) t

I believe that one should never rely on undocumented behaviour because there may be unforeseen corner cases that cause that behaviour to break.  For example, in satisfying a GROUP BY operation with an index, MySQL sorts the results and may thereby choose an unexpected value.

What other corner cases can break this behaviour?  Or is it sufficiently reliable for production systems?


texts: How to check if there is partitions in use in fact tables?

texts: I'm going to take a first look of a large data warehouse with many fact tables. How can I easily check if there is partitions and partition functions in use in these fact tables?


texts: Proper procedure for migrating a MySQL database to another Debian machine?

texts:
I have one server running an older Debian version with MySQL 5.x and a newer Debian server, also running MySQL.

I've created a backup of all databases on  the first server like so:

mysqldump -uuser -ppass --all-databases > dump.sql

On the other server,  I did a:

mysql -uuser -ppass < dump.sql

At first, everything seemed great. I could browse my databases in phpMyAdmin, but as soon as I tried logging in again, it failed. Turns out, my root password had been overwritten with the one from the older database.

I wanted to reset it, but in order to do so, I would have needed to start mysqld_safe. Which I couldn't because the password for the debian-sys-maint user had been overwritten as well in the database. When I thought all hell had broken loose, I somehow reset both the root and debian-sys-maint passwords to the original values of the new server, and I managed to revert to a clean state.

Since I obviously don't want to go down that road again, here's the question(s):

Was I right with my approach of using a complete --all-databases dump?
Was there something I needed to do in advance to reading in that dump to prevent this desaster from happening? Or even before creating the dump?

If I'm going about this the wrong way:

What is the proper procedure for migrating all databases and their users to another server?

Note that I'm not that experienced with MySQL and server administration at all, so I might be missing something obvious. All the tutorials and how-tos I've found never mention anything like this and just talk about importing the complete dump.


texts: How to keep only those archivelogs that are created after a latest db backup in oracle 11g?

texts:
I am little confused with oracle db backup.
I have configured my Oracle 11G DB with ARCHIVELOG in Fast Recovery Area (NFS mount).

I only need latest backup to restore in case of failure, but no point-in-time recovery is needed. So, I only need to have backup of control files, SP files, DB files, Redo files and archive logs (which are created after my latest DB file backup) and I want to use the same Fast Recovery Area for my DB backups.

I have two questions actually:

I don't understand what does it mean by backup of archive log, as the archivelog itself is backup of redo files (old files) and
which I'm already keeping in NFS. So, does backing up archivelog
actually do double backup and as in my case, the backup location is
FRA, will it do backup to FRA again?
How to set the correct retention policy to be able to keep only the archive logs that are created after my latest DB backup.


texts: How to import an existing schema and tablespace into a postgres database, from a filesystem-level backup

texts:
Follow-up question from:
Have postgres' pg_dump export an index

Say I have an existing database, and I want to add to it another schema with content. I saved that content previously through a filesystem-level backup of the tablespace that served the schema's tables and indexes.

I understand that DATABASEs, TABLEs and INDEXes necesitate a tablespace to be stored into, but not SCHEMAs (their declaration is stored in the DATABASE's tablespace?).

So, how do I make the DATABASEs tablespace understand that it needs to access a specific schema that would have its tables and indexes defined through a pre-existing tablespace.

Alternatively, can I make a CREATE TABLE t TABLESPACE tspace statement that would connect to the tspace TABLESPACE (as opposed to starting to write into it an empty instance of the table)?

(In other words, is the execution of a DDL statement stored into the DATABASEs tablespace, and nothing touches another tablespace?)


texts: How to design a table for storing news items?

texts:
I have a script that can get result from news websites and it will run every hour.

The php script will get the title and content.  The things I want to store in MySQL are:

  title
  content
  createTime (which is the time when I inserted the record)

As I will run the php script every hour I know there will be many cases where the same news will be found by the script.

How do I design a table that allows me to INSERT the new records, while previously existing news items are skipped?


texts: Is there a design model for tracking processes done to an item?

texts:
I am working on designing a database model that involves tracking processes almost like an assembly line. There is only one type of item (lets call it 'unit') that has about 10 different tasks done to it. The order of operations performed on the unit is not static and each task could potentially be done many times. However, each task has its own set of associated attributes.

What I currently have is a 'Units' table and a table for each task. Each task table's attributes are the necessary parameters involved with that task. Then there is a table that relates the tasks to the Units. This 'TaskParent' table has foreign key fields for each task and a foreign key field for the UnitId. When inserting only one task field is not null, this way I get a relationship from the unit to the Task details (Unit -> Task -> Task details).

A few things to answer the 'What do I need to know?' when using this data model:

Get all items currently at a specific task
Get all tasks a Unit has encountered
Compare Units based on various task details
There is much more but I am trying to simplify it to my 'current lack of understanding' (I'm sure there will be more of that..)

My question is does anyone know of  a design pattern for this type of situation or have input in how this should be implemented?

I have looked at this.

This is my first question on Stack, but I have had thousands of questions answered by this site. Thanks to all those who share their knowledge!


texts:
Script to create Tables Person and Company

USE optimisationTesting

CREATE TABLE Person(Id int not null, Name nvarchar(60), StreetAddress nvarchar(60)
                  , Height int, CompanyId int)
CREATE TABLE Company(Id int not null, Name nvarchar(60))
GO

ALTER TABLE [dbo].[Person]  ADD CONSTRAINT [PK_Person]  PRIMARY KEY CLUSTERED ([Id] ASC)
ALTER TABLE [dbo].[Company] ADD CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED ([Id] ASC)
GO

DECLARE @i int = 0

WHILE @i < 100000 BEGIN

    INSERT INTO Person(Id, Name, StreetAddress, Height, CompanyId)
    VALUES (
        @i,
        CONVERT(nvarchar(60), newid()),
        CONVERT(nvarchar(60), newid()),
        140+rand()*60,
        rand()*100)

    SET @i = @i + 1
END

SET @i=0
WHILE @i < 100 BEGIN

    INSERT INTO Company(Id, Name) values (@i, convert(nvarchar(60), newid()))

    SET @i = @i + 1
END

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

set statistics time on

select *
    from
    Person p
    where p.Name like 'a%'

set statistics time off

The average elapsed time after three execution was 363ms.
How can this query be further optimized to find out the names of people that starts with A?


texts: Update password across all SSRS reports within a folder in Reporting services 2016

texts:
I am going through a manual intervention of manually updating the password in data source section for all the reports in SSRS2016.

This is very tedious as i need to update this across 40+reports manually by going to manage-> data source and updating the password everytime the AD account has been expired or  reset , one used in SSRS reorts.

How can i make this less tedious may be via some powershell to update the password across all reports within same Data source?


texts: How to create an oracle dump(.dmp) file using Toad?

texts:
We can export the entire database schema(skeleton) along with its data in one .dmp file using oracles "exp" tool.
But how do I do it using Toad only.

I can't use this

C:\Users\kaunds_oliver>exp PORTAL_xxx/xxx@asdb file=E:\\Oliver\\userexpo
rt.dmp log=E:\\Oliver\\userexport.log

As there is no entry in the TNSNAMES.ora for the remote server ="asdb"
Also, I tried doing this

ASDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP) (HOST = 172.27.xx.xx) (PORT = 1530))
    )
    (CONNECT_DATA = (SERVICE_NAME = asdb))
  )

But I don't have the privileges to edit anything in C Drive i.e. the TNSNAMES.ora file

How do I create a dump file using specifically "Toad" tool of a database that I am connected to remotely?


texts: Calling stored procedure from job does nothing

texts:
I have very simple stored procedure

CREATE PROCEDURE FillMissingBirthDateFromMemberNumber
AS
BEGIN
    UPDATE Members
    SET BirthDate =
        CASE
            WHEN CONVERT(DATE, SUBSTRING(m.MemberNumber, 0, 7), 12) > '2030-01-01'
            THEN dbo.fn_LocalTimeToUTC(DATEADD(YEAR, -100, CONVERT(DATE, SUBSTRING(m.MemberNumber, 0, 7), 12)))
            ELSE dbo.fn_LocalTimeToUTC(CONVERT(DATE, SUBSTRING(m.MemberNumber, 0, 7), 12))
        END
    FROM Members m
    WHERE
        m.BirthDate IS NULL AND
        m.MemberNumber IS NOT NULL AND
        m.MemberStatus IN (1,2)
END

that when executed in a query window, performs exactly as promised. It fills out missing birth dates based on the first six chars of the member number (with some caveats, as the CASE WHEN indicates).

Now, I have created a SQL job that simply EXECs this stored procedure and reports success when run.
Only: nothing happens.

Also when I paste the code from the SP into the Command section directly, the result is identical: success but no rows affected when checking afterwards.

I have recreated the SP and the job, thinking I did something wrong. I have also changed the job owner to different accounts (from my own all the way to admin) thinking it had something to do with rights - the job history consistently reports success across the board.

What am I doing wrong or what am I not seeing?


texts:
I am trying to run a sitemap generator job for Magento and here is what I get as Innodb status after the query has failed to complete even after several hours. I do not know much about DBA stuff and would appreciate if someone can give me some pointers on what to do next to resolve this.

------------------------
LATEST DETECTED DEADLOCK
------------------------
160112 19:12:29
*** (1) TRANSACTION:
TRANSACTION 51E4647, ACTIVE 26 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 3
MySQL thread id 496, OS thread handle 0x7fe32e89d700, query id 74695 localhost root update
INSERT INTO `log_url` (`url_id`, `visitor_id`, `visit_time`) VALUES ('341014', '298405', '2016-01-12 19:12:03')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 39353 n bits 760 index `url_id` of table `shopez_demo1`.`log_url` trx id 51E4647 lock_mode X insert intention waiting
Record lock, heap no 1
*** (2) TRANSACTION:
TRANSACTION 51E12D2, ACTIVE 1643 sec fetching rows
mysql tables in use 1, locked 1
13212 lock struct(s), heap size 1571256, 3349297 row lock(s)
MySQL thread id 456, OS thread handle 0x7fe32e91f700, query id 74740 localhost root Sending data
SELECT `log_url`.* FROM `log_url` LIMIT 2048 OFFSET 339968
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 39353 n bits 760 index `url_id` of table `shopez_demo1`.`log_url` trx id 51E12D2 lock mode S
Record lock, heap no 1
Record lock, heap no 2
Record lock, heap no 3
Record lock, heap no 4
Record lock, heap no 5
Record lock, heap no 6
...


texts: Direct values on predicate produce not-so-good plan

texts:
I'm using StackOverflow dump to run some tests.

In particular, I'm querying this table:

I have created this index:

I'm running the following query (just forcing an index to test alternatives)

I get the following exec plan high a cost (66.63).

These are the IO statistics after running this query:

Then, I run the same query by providing variables instead of direct values

I get a better plan (Cost is 0.4385).

Statistics are also better:

At first...I thought SQL Server was not recognizing direct values as INT, but there are neither type mismatch nor implicit conversion warnings.

I also tried avoiding parallelism, but I still get a high-cost plan (and higher IO statistics) with MAXDOP 1 when passing direct values in the predicate.

When comparing both plans, there are different estimates:

What's wrong with direct values being passed as part of predicate?


texts: Where's my Network Alias list in Oracle?

texts:
I can't select any Network Alias from the Network Alias Drop-down.

My TNSNames.ora file is in C:\Oracle

My other machine that is a fresh install doesn't have this problem.

I get an error when I click Test on the connection window.

Status :Failure - Test failed: no ocijdbc11 in java.library.path

I'm guessing somehow it's not pointing to the TNSNames.ora file.

Where is that configured?


texts: How can I move a database from one server to another?

texts:
How can I move MySQL tables from one physical server to another?

Such as this exact scenario:
I have a MySQL server that uses innodb table and is about 20GB size.

I want to move it to a new server, what's the most efficient way to do this?


texts: How to list objects that are using another object?

texts:
I have to move certain objects from a schema to another. The database is rather large and
I want to make sure we test properly all affected objects.

On TOAD there is a Tab called "Used By" that show all objects that are referencing the selected object.

Is there a way to achieve a similar output with an SQL Command?
I tried checking the different DBA Views but I haven't been able to find any with the information I need.


texts: How to multiply rows for a column that contains negative and zero values?

texts:
I'm trying to get the Product of all rows for a specific column in a grouped by query.  Most examples I've found point me towards combining exp, sum and log

exp(sum(log([Column A])))

The problem I'm having is that the column contains some zeros for values and thus I'm getting this error when zeros get passed to the log function:

  An invalid floating point operation occurred.

I thought I could work around this by using a case expression, but that just doesn't work the way I would think it should, as it seems to evaluate all cases...

select
  Name,
  Product = case
    when min([Value]) = 0 then 0
    when min([Value]) <> 0 then exp(sum(log(I))) -- trying to get the product of all rows in this column
  end
 from ids
 group by Name

SqlFiddle

Given the following result set:

Id  Name  Value
_________________________________
1   a     1
2   a     2
3   b     0
4   b     1

I would expect to get the following rows:

Name  Product
_____________
a     2
b     0

So in summary...
How do you multiply rows in a column that can contain negative or zero valued numbers?


texts: Is it possible to ignore query hints in SQL Server 2012?

texts:
I know this sounds a bit odd... the thing is we have a piece of critical code in production which issues a query with a Hash Join hint which is causing high CPU usage on SQL Server.

Owing to some lack of change control in the past, we are not confident of being able to deploy a new version of this code without the HASH without potentially breaking other (much more critical) stuff.

I'd like to ignore this HASH (and even all hints, as I am confident none of them are required and I'd like to allow SQL Server to do its thing without interference!)

Is there a server-wide option to do this?

Thanks!


texts: Why am I getting an error in execute immediate command in oracle 11g?

texts:
I was just testing an anonymous code which I wrote for audit trail It's just a sample for test. However I am getting this error:

[Error] Execution (1: 1): ORA-00984: column not allowed here
ORA-06512: at line 29

Code:

DECLARE
    colName VARCHAR2(2000);
    colNewVal VARCHAR2(2000);
    sqlInsert VARCHAR2(2000);
BEGIN
   colName := 'BOOK_NAME,AUTHOR,PUBLISHER,PUBLISH_YEAR';
   colNewVal := 'BOOK1,AUTH1,PUB1,2001';
   sqlInsert := 'INSERT INTO TBL_TRN_AUDIT_TRAIL_MASTER(';

   FOR cur_var IN (SELECT REGEXP_SUBSTR (colName,'[^,]+',1,LEVEL) col_name FROM DUAL
        CONNECT BY REGEXP_SUBSTR (colName,'[^,]+',1,LEVEL)IS NOT NULL)
    LOOP
        sqlInsert := sqlInsert || cur_var.col_name || ',';

    END LOOP;
    sqlInsert := SUBSTR(sqlInsert,1,INSTR(sqlInsert, ',', -1)-1);
    sqlInsert := sqlInsert || ')VALUES(';
    dbms_output.put_line (sqlInsert);
    FOR cur_var2 IN (SELECT REGEXP_SUBSTR (colNewVal,'[^,]+',1,LEVEL) col_new_val FROM DUAL
        CONNECT BY REGEXP_SUBSTR (colNewVal,'[^,]+',1,LEVEL)IS NOT NULL)
    LOOP
        sqlInsert := sqlInsert || cur_var2.col_new_val || ',';

    END LOOP;
    sqlInsert := SUBSTR(sqlInsert,1,INSTR(sqlInsert, ',', -1)-1);
    sqlInsert := sqlInsert || ')';
    dbms_output.put_line (sqlInsert);
    EXECUTE IMMEDIATE sqlInsert;

END;

Line 29 is the execute immediate command.
Am I doing something wrong here??


texts:
Hey I'm trying to wrap my head around SQLite data storage, specifically how it is storing Records. I've found a book The Definitive guide to SQLlite, where the author explains internal record format (Figure 9-5, page 351):

Given table:

sqlite> SELECT * FROM episodes ORDER BY id LIMIT 1;
id   season  name
---  ------  --------------------
0    1       Good News Bad News

Its internal record format is:

| 04 | 01 | 01 | 49 |   | 00 | 01 | Good News Bad News |

  "The header is 4 bytes long. The header size reflects this and itself is encoded as a single byte. The first type, corresponding to the id field, is a 1-byte signed integer. The second type, corresponding to the season field, is as well. The name type entry is an odd number, meaning it is a text value. Its size is therefore given by (49-13)/2=18 bytes."

Specifically I'm curious about TEXT attribute, in the example above we have a string of length 18 characters. And the rule for TEXT in SQLite is as follows:

Type Value     Meaning   Length of Data
----------     -------   --------------------
N>13 and odd   TEXT      (N-13)/2

What hapenns though when the string is longer ? It'll get out of range of that one byte.


texts:
I have a php script which processes a form with a single input.

I want the value I send through input to be added to an existing value in database (mysql).

For example in database I have value = 10

When I type into the input 25 and submit the info. I want the value in database to be 35. I don't need any php code. I just want to know the correct mysql query

Edit:

UPDATE value =  value + $newvalue

something like this would be right ?


texts: What's the differences between utf8_general_ci and utf8_unicode_ci and utf8_binary collation in MySQL?

texts: I'm can't find the documentation of MySQL on this topic. Anyone can give some explanations please?


texts: Is it advisable to add indexes to a data warehouse's staging area?

texts:
We have a data warehouse built in MSSQL 2012, Staging area, EDW, cubes and reports.

In the staging area, the tables are a 1:1 copy of those supporting the systems used to fill the DWH with. The staging area tables are TRUNC'ed and refilled daily (nightly). Then views on those tables provides input for the ETL process towards the EDW.

Besides providing a copy for the EDW to work with, these staging tables are also queried using MSSQL Management Studio by some of our analists, as not to put pressure on the source systems with their queries.

For both the ETL towards EDW as for the analist's queries, I get the idea that having an index on (some of) these tables might aid performance (greatly). Is it wise to have indexes on (large) tables that get TRUNC'ed daily. If not, why? And what kind of index (Clustered vs Non-clusterd vs someting else entirely)?


texts: Problem getting SQL Server 2016 R Services (In-Database) working

texts:
SQL Server 2016 CU2 (Enterprise) here and one of my users has requested that we get R-Services installed.

I followed what appeared to be very simple instructions from this page

Set up SQL Server R Services (In-Database)

When I got to the part about using the simple test

exec sp_execute_external_script  @language =N'R',
@script=N'OutputDataSet<-InputDataSet',
@input_data_1 =N'select 1 as hello'
with result sets (([hello] int not null));
go

it failed with

  Msg 39021, Level 16, State 1, Line 6 Unable to launch runtime for 'R'
  script. Please check the configuration of the 'R' runtime. Msg 39019,
  Level 16, State 1, Line 6 An external script error occurred:  Unable
  to launch the runtime. ErrorCode 0x80070057: 87(The parameter is
  incorrect.). Msg 11536, Level 16, State 1, Line 6 EXECUTE statement
  failed because its WITH RESULT SETS clause specified 1 result set(s),
  but the statement only sent 0 result set(s) at run time.

So, I started my internet searching for known problems regarding R-Services and found this article - Known Issues for SQL Server R Services

I made sure that R-Services had been brought up to date with the Sql Server version (2016, CU2).
I read where long file names in the rlauncher.config file might cause problems, so I redirected my WORKING_DIRECTORY to C:\TEMP\R_SERVICES.  However, I left the RHOME and MPI_HOME configuration entries untouched and still using long file names.
I made sure the Windows group SQLRUserGroup had full authority to that working directory folder.

After every configuration change, I restarted both SQL Server and SQL Server Launchpad.

However, every time I try to run the sample T-SQL test for R-Services, it fails and I see this entry in the rlauncher.log file

  [Error] Session creation failed with: Failed with 2 to get security
  for C:\TEMP\R_SERV~1\MSSQLSERVER01

I'm reaching out for help from others that might know what the problem is or any additional troubleshooting links.


texts:
is there any way to recover from a ORA-00257: archiver error?

The archiver disks broke, but DB continue operation 2 sequences more.
I have no access to the archiver disk anymore

 SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /drbak/arch/terdb/pro/va
Oldest online log sequence     46612
Next log sequence to archive   46612
Current log sequence           46614

SQL> show parameter db_recovery_file_dest;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 50000M

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------

 5.2429E+10          0                 0               0


texts:
We just went away from SQL Server clusters in SQL Server 2008 to SQL Server 2012 availability groups – have five servers with replicas in a secondary datacenter and syncing over a dedicated line/LAN layer 2 connection - switch to switch.

We had an incident that for the duration of about 2 hours we had several logs showing that replication was terminated and then reestablished with the termination lasting about 10-20 seconds at a pop…happened “a ton” of times in those two hours and our users were complaining of latency and problems accessing the databases on the AG. This happened only on 2 of our 5 AGs that are going between the 2 datacenters.

Assuming intermittent network issues, took a look at switches, firewalls, (all redundant), the eline between and found no indications of any loss of connection issues. No indicators that the SANS had issues either. During those 2 or so hours we also got “blocking” alerts from the DB warden database that runs on all the AGs …for the two that had the replication issues.

If I have ruled out network connectivity issues, should I be looking at the SQL transactions that were running at the time? Is it possible that something in our code/long running job/query could have caused long waits or something that would have triggered the replication errors? If we have replication issues, does that cause latency on the residing databases in the AG?

I am NOT a DBA, unless you count me as accidentally forced into it by default – I'm the network and sys admin/datacenter manager and SQL Server has just fallen into my realm of responsibility so this one has me grasping for explanation to our users.... any suggestions or insight?


texts:
How do I clear user's profile?
I found how I can drop the profile (also using CASCADE), but I want to keep the profile and just remove it from the user.


texts: "Skipping the default startup of database" SQL notification for AlwaysOn Cluster

texts:
So I set up our SQL servers to notify us of alerts at severity 17 or higher (as advised by various SQL Server articles). I've done this to each node of a SQL Server Always On Availability Cluster, with three multi-subnet nodes.

Over the weekend we've received almost a hundred notifications about a single "informational only" message from one of the secondary nodes. The other nodes aren't doing this and the alert doesn't seem to be causing any actual problems. The alert is:

DESCRIPTION:    Skipping the default startup of database '<database>' because the database belongs to an availability group (Group ID: <...>).
The database will be started by the availability group.
This is an informational message only. No user action is required.

Possible culprit: Our network has a few issues, the node could be disconnecting and reconnecting or timing out.

I suppose my questions are, is there any action I should take to resolve this alert? Why is it only affecting one of my nodes? If it really is informational and no action needs to be taken, can I configure my alerts to ignore these alerts?

Upon a comment, I looked at the logs and it appears that the server has been running all that time but that it disconnected from the always on server around that time:

AlwaysOn Availability Groups: Local Windows Server Failover Cluster node is no longer online.
The Availability replica is going offline because the local ...
The Waiting for local Windows Server Failover Clustering service to start ...

(Then it goes through the informational messages regarding starting up each database)
Local Windows Server Failover Clustering service started.


texts: Filling a database (from a temporary database) with a Many to Many relation

texts:
I have a temporary table that I fill in using a CSV file.

CREATE TEMP TABLE temp_products(
    product_marketplace_name varchar,
    product_link varchar,
    product_name varchar,
    product_description varchar,
    product_price numeric,
    product_ean numeric,
);

With this temporary table, I would like to complete these 3 tables :

I have created a many to many relationship:

A product is present on several marketplaces.
A marketplace has several products.

Marketplace Table

CREATE TABLE marketplace (
  marketplace_id  serial PRIMARY KEY,
  marketplace_name varchar UNIQUE
)

Product Table

CREATE TABLE product (
  product_id serial PRIMARY KEY,
  product_name        text NOT NULL,
  product_description varchar,
  product_ean numeric UNIQUE,
);

Junction Table

CREATE TABLE product_marketplace (
  marketplace_id  int REFERENCES marketplace (marketplace_id) ON UPDATE CASCADE ON DELETE CASCADE,
  product_id int REFERENCES product (product_id) ON UPDATE CASCADE,
  updated  date NOT NULL DEFAULT CURRENT_DATE,
  product_link    text NOT NULL,
  product_price   text
);

I dont know how to create the relationship in the junction table... My idea is to retrieve the ids (marketplace_id, product_id) from the previous INSERTS

BEGIN;

INSERT INTO marketplace (product_marketplace_name)
SELECT DISTINCT temp_products.product_marketplace_name
FROM temp_products
ON CONFLICT DO NOTHING;

INSERT INTO product(product_name, product_description, product_ean)
SELECT temp_products.product_name, temp_products.product_description, temp_products.product_ean
FROM temp_products
ON CONFLICT DO NOTHING;

....

COMMIT;

I tried this, but it doesnt work...

INSERT INTO product_marketplace (marketplace_id, product_id, product_price, product_link)
SELECT temp_products.product_price, temp_products.product_link
FROM temp_products
JOIN marketplace as m USING (marketplace)
JOIN product    as p USING (product)
ON CONFLICT DO NOTHING;
```


texts:
I have the below schema, fields owner_id,billing_id,tech_id,admin_id on carts table are pointing to contacts table. As you can see from the query result I have to JOIN for each field .Is there a way to avoid?

SELECT c.domain, c.period, co.name AS owner, co1.name AS admin, co2.name AS tech, co3.name AS billing, h.nameserver
FROM carts c
LEFT JOIN contacts co ON c.owner_id = co.id
LEFT JOIN contacts co1 ON c.admin_id = co1.id
LEFT JOIN contacts co2 ON c.tech_id = co2.id
LEFT JOIN contacts co3 ON c.billing_id = co3.id
LEFT JOIN HOSTS h ON c.host_id = h.id
WHERE c.user_id =1


texts: Postgres full text search with better match

texts:
I have this scripts for full text searching in my postgres database:

function for unaccent concat:

CREATE OR REPLACE FUNCTION f_concat_ws_unaccent(text, VARIADIC text[])
RETURNS text LANGUAGE sql IMMUTABLE AS 'SELECT unaccent(array_to_string($2, $1)

and this script is for full text search:

SELECT * FROM geocode.addresses
WHERE  to_tsvector('simple', f_concat_ws_unaccent(' ', country, city,street, housenumber, postcode))
    @@ plainto_tsquery('simple', 'some address') limit 1;

Can you help mi edit this select or create some function, which return result with the best match. For example in my database exist 2 cities. First city called  CityA and second city is composite from 2 words, where one word is same as name first city .. for example Upper CityA. When I execute my script like this:

SELECT * FROM geocode.addresses
WHERE  to_tsvector('simple', f_concat_ws_unaccent(' ', country, city,street, housenumber, postcode))
    @@ plainto_tsquery('simple', 'CityA') limit 1;

I need result with the best match row, but scipt return my Upper CityA row instead CityA.
Any ideas how can I edit this for required result?


texts: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

texts:
I have a PostgreSQL 9.2 instance running on RHEL 6.3, 8-core machine with 16GB of RAM. The server is dedicated to this database. Given that the default postgresql.conf is quite conservative regarding memory settings, I thought it might be a good idea to allow Postgres to use more memory. To my surprise, following advice on wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server significantly slowed down practically every query I run but it's obviously more noticeable on the more complex queries.

I also tried running pgtune which gave the following recommendation with more parameters tuned, but that didn't change anything. It suggests shared_buffers of 1/4 of RAM size which seems to in line with advice elsewhere (and on PG wiki in particular).

default_statistics_target = 50
maintenance_work_mem = 960MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 11GB
work_mem = 96MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 3840MB
max_connections = 80

I tried reindexing the whole database after changing the settings (using reindex database), but that didn't help either. I played around with shared_buffers and work_mem. Gradually changing them from the very conservative default values (128k / 1MB) gradually decreased performance.

I ran EXPLAIN (ANALYZE,BUFFERS) on a few queries and the culprit seems to be that Hash Join is significantly slower. It's not clear to me why.

To give some specific example, I have the following query. It runs in ~2100ms on the default configuration and ~3300ms on the configuration with increased buffer sizes:

select count(*) from contest c
left outer join contestparticipant cp on c.id=cp.contestId
left outer join teammember tm on tm.contestparticipantid=cp.id
left outer join staffmember sm on cp.id=sm.contestparticipantid
left outer join person p on p.id=cp.personid
left outer join personinfo pi on pi.id=cp.personinfoid
where pi.lastname like '%b%' or pi.firstname like '%a%';

EXPLAIN (ANALYZE,BUFFERS) for the query above:

Default buffers: http://explain.depesz.com/s/xaHJ
Bigger buffers: http://explain.depesz.com/s/Plk

The question is why am I observing decreased performance when I increase buffer sizes? The machine is definitely not running out of memory. Allocation if shared memory in OS is (shmmax and shmall) is set to very large values, that should not be a problem. I'm not getting any errors in the Postgres log either. I'm running autovacuum in the default configuration but I don't expect that has anything to do with it. All queries were run on the same machine few seconds apart, just with changed configuration (and restarted PG).

Edit:
I just found one particularly interesting fact: when I perform the same test on my mid-2010 iMac (OSX 10.7.5) also with Postgres 9.2.1 and 16GB RAM, I don't experience the slow down.
Specifically:

set work_mem='1MB';
select ...; // running time is ~1800 ms
set work_mem='96MB';
select ...' // running time is ~1500 ms

When I do exactly the same query (the one above) with exactly the same data on the server I get 2100 ms with work_mem=1MB and 3200 ms with 96 MB.

The Mac has SSD so it's understandably faster, but it exhibits a behavior I would expect.

See also the follow-up discussion on pgsql-performance.


texts: CREATE TABLE IF ONLY NOT EMPTY RESULT SET

texts:
I'd like to create a table that consists of errors (incorrect format of data for my table, invalid geometry, etc). I have tried both SELECT INTO and CREATE TABLE AS queries, but both of them creates a table even if the resulting set is empty (0 records). Is there an option to perform this kind of query which won't create a table if empty set?
The reason for this request is I have to then collect all error tables and make a dump (to be more specific, a gbd with ogr2ogr) of them, so empty tables will be  disturbing.
P.s. Please do not recommend after creating tables to drop empty tables using information_schema data, I'm looking for more efficient way of dealing with this. Thank you.


texts: psql, I try to connect to a database and I get "psql: FATAL: Peer authentication failed for user ", why?

texts:
I'm using psql (9.2.23) on my Dell Latitude 3540 laptop running Linux CentOS 7.
I want to do a simple thing: create a database username and log in as that username.

My operating system username is "davide", and the username I'd like to create and use in psql is "davide3". My laptop name is "laptop".

Here are the steps I followed, after having installed PostgreSQL:

  [davide@laptop ~]$ sudo su - postgres

  [sudo] password for davide:

  Last login: XXX

  -bash-4.2$ psql

  psql (9.2.23)

  Type "help" for help.

  postgres=# CREATE USER davide3 WITH PASSWORD 'davide3' CREATEDB
  CREATEUSER;

  CREATE ROLE

  postgres=# \q

  -bash-4.2$ exit

  logout

Then I try to login to the database by using the username "davide3":

  [davide@laptop ~]$ psql -d postgres -U davide3

  psql: FATAL:  Peer authentication failed for user "davide3"

Here's the trouble: I was expecting to get a password-insertion message, and to insert the 'davide3' password, but I did not get anything instead.

What did I do wrong?
How to solve this issue?

Thanks


texts: Clustered Table Scan Because of "SELECT *"

texts:
I have a Records table with over 100 columns and very many rows, and a nonclustered index on 5 fields based on my access paths:

CREATE NONCLUSTERED INDEX [IX_Records_CustomerID]
ON [dbo].[Records] (
    [CustomerID] ASC, -- int
    [IsInvalid] ASC, -- int
    [IsProcessed] ASC, -- bit
    [IsRejected] ASC, -- bit
    [RecordName] ASC, -- varchar(12)
;

The 5 fields do not include the primary key RecordID, which is the column in the clustered index.

Here is my poorly performing query:

SELECT * FROM Records WHERE CustomerID IN (181, 283, 505)

The execution plan shows that it performs a Clustered Index Scan, which I understand is because I'm selecting columns that are not included in the index. In Management Studio, I change the query to:

SELECT CustomerID, IsInvalid, IsProcessed, IsRejected, RecordName FROM Records
    WHERE CustomerID IN (181, 283, 505)

And the execution plan shows an Index Seek, and the query execution time drops from 44 seconds to 2 seconds. However, I lack the liberty in the application to replace the * with only the columns I need and have included in my index.

Is there any way around the clustered index scan when I'm locked into SELECT *?


texts: Percona MySQL 5.5.33 not reading my.cnf?

texts:
At some point Percona MySQL on CentOS 6.4 seems to have stopped reading my.cnf, possibly after MySQL updated to 5.5.33 a few days ago.

Selection from /etc/my.cnf:

[mysqld]

# GENERAL #
user                    = mysql
default-storage-engine  = InnoDB
socket                  = /var/lib/mysql/mysql.sock
pid_file                = /var/run/mysql.pid
symbolic-links          = 0

# CHARACTER SET AND COLLATION #
collation-server        = utf8_unicode_ci
character-set-server    = utf8
init-connect            = 'SET NAMES utf8'
init-connect            = 'SET collation_connection = utf8_unicode_ci'

# DATA STORAGE AND LOGGING #
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
slow_query_log_file     = mysql-slow.log
slow_query_log          = 1
long_query_time         = 8
log_error               = mysql-error.log

# CACHES AND LIMITS #
max_connections         = 250
max_connect_errors      = 10000
open_files_limit        = 20000
back_log                = 100
wait_timeout            = 10800
interactive_timeout     = 600
max_allowed_packet      = 256M
tmp_table_size          = 512M
max_heap_table_size     = 64M
query_cache_size        = 32M
thread_cache_size       = 64

Some output from 'mysqld --help --verbose'

log-error                                         mysql-error.log
slow-query-log                                    TRUE
slow-query-log-file                               mysql-slow.log
max-connections                                   250
max-heap-table-size                               67108864

Ok that seems good, matches my config. How about logging into mysql and doing some SHOW VARIABLES:

log_error   /var/lib/mysql/hostname.err
log_slow_queries    OFF
slow_query_log_file /var/lib/mysql/hostname-slow.log
max_connections 151
max_heap_table_size 16777216

Huh, well that ain't right. Slow log is off, error log has the wrong name, connection and heap sizes are wrong, and the server collation is wrong as well.

Processes running

root     23533     1  0 12:32 pts/3    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/hostname.pid
mysql    23624 23533 91 12:32 pts/3    01:05:32 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/hostname.err --pid-file=/var/lib/mysql/hostname.pid

I see the error log listed in the process has the wrong name right off the bat. I've tried manually restarting mysql from /etc/init.d, via 'service mysql restart', via restarting the entire server. Nothing seems to get MySQL to read the conf.

Maybe something is breaking when mysqld_safe spins up a mysql instance? No problems reported in the error log when the server starts. Any ideas?


texts:
EDIT

This query seems to work best so far.

select host,plugin_id,month,year, row_number() over (partition by plugin_id order by plugin_id) as plugin_split
from table
where risk in ('High')
group by plugin_id,host,month,year
order by plugin_id,host,month asc;

This seems to group all the plugin_id,host, and month together to make it easy to parse.

Would it be possible to only select hosts that have the same plugin_id for month 1 and 9? And then only for month 1 but not month 9? And then only for month 9 but not month 1? I was looking into parsing this with ruby if I couldn't do it in SQL. Not really a DBA any help would be appreciated. Thanks!

select host,risk,plugin_id,month,year from table where risk in ('High') group by plugin_id,host,risk,month,year;

     host      | risk | plugin_id | month | year
---------------+------+-----------+-------+------
 172.20.10.122 | High |     10081 |     1 | 2014
 172.20.10.122 | High |     10081 |     9 | 2013
 172.20.10.51  | High |     10081 |     1 | 2014
 172.20.10.52  | High |     10081 |     1 | 2014
 172.20.10.53  | High |     10081 |     1 | 2014
 172.20.10.53  | High |     10081 |     9 | 2013
 172.20.10.54  | High |     10081 |     9 | 2013
 172.20.10.55  | High |     10081 |     9 | 2013
 172.20.10.56  | High |     10081 |     1 | 2014
 172.20.10.56  | High |     10081 |     9 | 2013
 172.20.10.57  | High |     10081 |     1 | 2014
 172.20.10.58  | High |     10081 |     1 | 2014
 172.20.10.58  | High |     10081 |     9 | 2013
 172.20.10.59  | High |     10081 |     1 | 2014
 172.20.10.59  | High |     10081 |     9 | 2013
 172.20.10.60  | High |     10081 |     1 | 2014
 172.20.10.60  | High |     10081 |     9 | 2013
 172.20.10.61  | High |     10081 |     1 | 2014
 172.20.10.61  | High |     10081 |     9 | 2013
 172.20.10.62  | High |     10081 |     1 | 2014


texts: Using “nolock” hint for the SQL View (MSSQL)

texts:
Could you help evaluate the "nolock" hint necessity for the View.

From the view definition, we notice that there’s no WITH(NOLOCK) SQL hint added to any table reference.  Thus, all the reports regarding the view have a potential to block other transactions (insert/update/delete) while it executes.


texts: How to set MSDN in order to open on a certain version of Microsoft SQL Server?

texts:
In a network, there are certain different installed versions of Microsoft SQL Server, starting with 2005, 2008 and 2008 R2.

Is there a specific option of MSDN that can fix the opening of new pages on the version of SQL Server that is being worked on?

For instance, can I set MSDN to open on Microsoft SQL Server 2008 R2, if I work only on that version?

EDIT in order to clarify what I am asking:

When a user searches the Internet using a search engine for the syntax of a specific SQL statement, he might get to a MSDN web page such as the following one, which presents the "SELECT (Transact-SQL)" SQL statement:

https://msdn.microsoft.com/en-us/library/ms189499.aspx

Even if the user is logged in the MSDN system using a user name such as "UserName123", there are no apparent settings - or maybe I have not found them yet - so that the version of the Microsoft SQL Server can be pre-set. Currently, always the pages open with the latest version, which is "SQL Server 2014".
In this situation, the user has to select from the "Other Versions" drop-down list his preferred Microsoft SQL Server version, for instance "SQL Server 2005". There are cases when the user forgets to do that and reads the syntax for a version of Microsoft SQL Server that he is not interested in.
Finally, the question: Is it possible to automatically pre-set somehow, using any MSDN user configuration settings, the Microsoft SQL Server version that is automatically chosen when displaying information?