texts: Checking for fields not on SET clause on before update trigger on MySQL

texts:
I want to set a before update trigger that sets a column to a given value if an update query is done and that column has no new value on the set clause.

The table I'm using. (Sort of)

DROP TABLE IF EXISTS 'hex';
CREATE TABLE 'hex' (
    'id'    INT(11) NOT NULL AUTO_INCREMENT,
    'chunk' VARCHAR(45),
    'flag'  TINYINT(1) NOT NULL DEFAULT '0',
    PRIMARY KEY ('id')
);

So, what I want to do is when hex.chunk gets a new value and flag doesn't, flag is set to 0.

e.g.

Current Dataset

id   chunk           flag
1    "Antelope"      0
2    "Barracuda"     1
3    "Capybara"      0
4    "Dolphin"       1

Operations and Expected Results

Case 1 UPDATE hex SET chunk = "Antelope" WHERE id = 1; doesn't update the flag since chunk doesn't get a new value.

Case 2 UPDATE hex SET chunk = "Elephant" WHERE id = 2; updates the flag to 0 since chunk changes its value and no data for flag is passed.

Case 3 UPDATE hex SET chunk = "Capybara", flag = '1' WHERE id = 3; chunk and flag updates as queried.

Case 4 UPDATE hex SET chunk = "Ferrovax", flag = '0' WHERE id = 3 Same as case 3.

So, I've tried using this trigger but it doesn't seem to work the way I want it to work.

CREATE TRIGGER 'flag_the_hex'
BEFORE UPDATE ON 'hex'
FOR EACH ROW
BEGIN
    IF (NEW.flag IS NULL AND NEW.chunk != OLD.chunk)
    THEN
        SET NEW.flag = 0;
    END IF;
END

I think my main confusion here is how NEW values in the columns not updated are represented in triggers. If I update only the chunk column, how is the NEW value for flag represented? Null? Or does it just not exist yet?

Please keep in mind that there may be more columns in the table I'm using.

Thanks for reading.

EDIT: I asked this question over at StackOverflow and thought that may not be the appropriate place to ask this question. So, I asked this here and deleted it over there.


texts: How to get cross apply to operate row by row on a view?

texts:
We have a view that is optimized for single item queries (200ms no parallelism):

select *
    from OptimizedForSingleObjectIdView e2i
   where ObjectId = 3374700

It also works on small sets of static ids (~5).

select *
    from OptimizedForSingleObjectIdView e2i
   where ObjectId in (3374700, 3374710, 3374720, 3374730, 3374740);

However if the objects come from an external source, then it generates a slow plan.  The execution plan shows that the execution branch for the view part is ignoring the predicate on ObjectId whereas in the original case it uses them to perform index seeks.

select v.*
  from
     (
       select top 1 ObjectId from Objects
        where ObjectId % 10 = 0
        order by ObjectId
     ) o
  join OptimizedForSingleObjectIdView v -- (also tried inner loop join)
    on v.ObjectId = o.ObjectId;

We don't wish to invest in "dual" optimizing the view for non-singular cases.  Rather, the solution we "seek" is to repetitively call the view once per object without resorting to an SP.

Most of the time the following solution calls the view row by row. However not this time and not even for just 1 object:

select v.*
  from
     (
       select top 1 ObjectId
         from Objects
        where ObjectId % 10 = 0 -- non-trivial predicate
        order by ObjectId
     ) o
   cross apply
    (
      select top 2000000000 *
        from OptimizedForSingleObjectIdView v_
       where ObjectId = o.ObjectId
       order by v_.SomeField
    ) v;

At one time I thought there was a claim  that cross apply was guaranteed for row by row execution when it calls a UDF but this also failed:

create function FunctionCallingView(@pObjectId bigint)
returns table
as
return select *
  from OptimizedForSingleObjectIdView
 where ObjectId = @pObjectId;

select v.*
  from
     (
       select top 1 ObjectId
         from Objects
        where ObjectId % 10 = 0
        order by ObjectId
     ) o
 cross apply FunctionCallingView(o.ObjectId) v

Adding option(force order) did not help--however there are two hash hints in the view already. Temporarily removing them did not help and slows down the single case.

Here is a snippet of the estimated plan for the function based slow case.  The estimation of 1 row is correct.  Far off to the right (not shown) is where there is a seek predicate that does not include that top 1 result.  This seems similar to other cases we have where singular probed values from table seeks are not used as seek predicates elsewhere.


texts:
I've created a PostgreSQL database template that I want users to be able to copy and play around with. The problem is that apparently they don't get enough privileges for the tables that are copied from the template. For instance, if a user does:

create database my_copy template that_template;
\c my_copy
create index my_index on a_table_in_the_template(a_column);

It results in an error ERROR:  must be owner of relation a_table_in_the_template. If I'm not mistaken, this has worked before, but now it doesn't. I'm running server 9.4.10.

Is there an easy fix, or do I need to look for another way for users to copy the database to play around with, instead of using template?


texts:
I have the following version of SQL Server:

  Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64)
  Enterprise Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

I created the following view

CREATE VIEW [dbo].[vwGroupsOfficesDependencies]
WITH SCHEMABINDING
AS
SELECT        GC.IdGroup, COUNT_BIG(*) AS countBig, OD.IdOffice
FROM            dbo.Group AS GC INNER JOIN
                         dbo.GroupDependencies AS GD ON GC.IdGroup = GD.IdGroup INNER JOIN
                         dbo.OfficeDependencies AS OD ON OD.IdDependency = GD.IdDependency INNER JOIN
                         dbo.Dependencies AS D ON D.IdDependency = GD.IdDependency
WHERE        (D.Active = 1)
GROUP BY GC.IdGroup, OD.IdOffice

Then I created the clustered index

CREATE UNIQUE CLUSTERED INDEX [IX_vwGroupsOfficesDependencies_IdOficeIdGroup] ON [dbo].[vwGroupsOfficesDependencies]
(
    [IdGroup] ASC,
    [IdOffice] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

But when I run the query

SELECT IdGroup, IdOffice FROM dbo.[vwGroupsOfficesDependencies]

I see in the execution plan that it does not use the indexed view, it performs the joins with the tables inside the view. But if I include the hint WITH(NOEXPAND) it uses the indexed view.
I read that I must include WITH(NOEXPAND)  for non Enterprise version, but in my case it is not necesary. Why it is not using the indexex view?


texts:
This question is almost mandatory in any SE site for basic reference.

Please post only books with permanently value for DBAs in general.

The post should be a must read book, the classical ones, the foundation books, not books for specific products or dated.

The latest book for MS-SQL Server 2008 could be the state of the art but it is dated and specific.

I have a genuine interest. I'm basically a programmer and database knowledge is a flaw on my formation. I know lot of things about the databases just doing day-by-day tasks.


texts: Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query

texts:
I have a simple select select statement, see below.

SELECT     JobTasks.JobTaskID,
           CONVERT(DATETIME, SUBSTRING(JobTasks.FileName, 2, 8), 20)
FROM       JobTasks
INNER JOIN JobExecutions
ON         JobTasks.JobExecutionID = JobExecutions.JobExecutionID
INNER JOIN JobStatus
ON         JobExecutions.JobStatusID = JobStatus.JobStatusID
WHERE      (JobExecutions.JobID = 1)
AND        (JobTasks.FileName IS NOT NULL)
ORDER BY   CONVERT(DATETIME, SUBSTRING(JobTasks.FileName, 2, 8), 20)

It fails on converting the varchar to a date time, but if I change the select part of the script to include the filename field without the convert part it never fails, like below:

SELECT JobTasks.JobTaskID, CONVERT(DATETIME, SUBSTRING(JobTasks.FileName, 2, 8), 20), JobTasks.FileName

I have checked the data and it is all correct and can be converted to a datetime without issue. I have stripped out all the non numeric characters from the data and converted them all to datetime using CONVERT(DATETIME, SUBSTRING(JobTasks.FileName, 2, 8), 20) without issue. The field FileName is  a varchar(200) datatype. Removing or changing the order by does not make any difference. It still doesn't allow it to run

The filenames have a naming convention of

  A20120224_ABC_DEF.txt
  B20120225_XYZ_ABC.txt
  C20120225_XYZ.txt
  A20120225_ABX.txt

FYI

This is an old system that was built using sql server 2008r2 and has been in use for several years, the issue has only just cropped up very recently. Date wasn't a datatype when it was built and I believe datetime type 20 is being used because this data is being inserted into another datetime field.

Can anyone shed some light on the reason for this?

Thanks
Noelle

EDIT

From the answers and comments below and further research I have come to to the following understanding:

Adding the field being used in the where clause alters the execution path of the select statement, in that it excludes the null values before it tries to cast them, where as excluding it from the select doesn't invoke the where clause until after it tries to cast them.

Would this be correct?


texts: Internal reason for killing process taking up long time in mysql

texts:
I copied a big table's structure with (it is an InnoDB table btw)

CREATE TABLE tempTbl LIKE realTbl

Then I changed an index, and filled it up so I could run some test.
Filling it was done using:

INSERT INTO  `tmpTbl`
SELECT *
FROM `realTbl`

This took too long, so I wanted to stop this test.1

I killed the process while it was in a "Sending data" state: it is now "killed", and still in the state "Sending data".

I know some killed processes need to revert changes and so could take (equally?) long to kill compared to how long they were running, but I can't imagine why this would be the case now: The whole table needs to be emptied.

I'm curious as to what is happening that would take stopping/killing a simple query like this very long. To give you some numbers: the insert was running for an hour or 3, the kill is closer to 5 7 now. It almost looks like it runs a DELETE for every INSERT it did, and the delete takes longer then the insert did? Would that even be logical?

(And if anyone knows how to kick my test-server back into shape that would be nice too, as it's eating some resources, but that's not really important at this moment :) )

1)  I don't know yet why (it's a big table, 10M rows, but it should take that long?), but that's another thing / not part of this question :). It might be that my test could have been smarter or quicker, but that is also not the question now :D


texts: HAProxy - Load-Balancing in 2 Node setup - Performance Gain possible?

texts:
I was speaking with my boss about Load-Balancing a 2 node Galera Cluster and we weren't sure if there was any reason.

For writes, his argument was, even if we balance the writes, it has to write to each server to do the replication.

For reads, we could balance the reads across the servers, but would this really save time if everything now goes thru a single VM on another server?

We have two dedicated SQL Servers which are in an Active-Active Galera setup.

The only way I can think of doing a HA-Proxy would be a 3rd VM on another server, is this really worth the performance gain to have everything go thru this one VM which will be on a server congested with other traffic?

Is it possible/Would it make sense to put HA-Proxy right on the SQL Server(s) and load balance the reads, but it would still go thru the Primary server w/ HA-Proxy to get to Server B.

Just looking for some general thoughts and advice for this simple setup.


texts: SQL Server 2008 can't repair consistency

texts:
I have a problem with a SQL Server 2008 database.

Launching

DBCC CHECKDB

I get this error:

  SQL Server detected a logical consistency-based I/O error: incorrect
  checksum (expected: 0xd2e00940; actual: 0x925ef494). It occurred
  during a read of page (1:15215) in database ID 22 at offset
  0x000000076de000 in file 'C:\Program Files\Microsoft SQL
  Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\file.mdf'.
  Additional messages in the SQL Server error log or system event log
  may provide more detail. This is a severe error condition that
  threatens database integrity and must be corrected immediately.
  Complete a full database consistency check (DBCC CHECKDB). This error
  can be caused by many factors; for more information, see SQL Server
  Books Online.

I found the table causing the problem:

DBCC CHECKTABLE

  Msg 824, Level 24, State 2, Line 8
  SQL Server detected a logical
  consistency-based I/O error: incorrect checksum (expected: 0xd2e00940;
  actual: 0x925ef494). It occurred during a read of page (1:15215) in
  database ID 22 at offset 0x000000076de000 in file 'C:\Program
  Files\Microsoft SQL
  Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\file.mdf'.
  Additional messages in the SQL Server error log or system event log
  may provide more detail. This is a severe error condition that
  threatens database integrity and must be corrected immediately.

  Complete a full database consistency check (DBCC CHECKDB). This error
  can be caused by many factors; for more information, see SQL Server
  Books Online.

So I tried with the repair operations:

DBCC CHECKTABLE (table_name, REPAIR_ALLOW_DATA_LOSS)

but I get the same error:

  Msg 824, Level 24, State 2, Line 8
  SQL Server detected a logical
  consistency-based I/O error: incorrect checksum (expected: 0xd2e00940;
  actual: 0x925ef494). It occurred during a read of page (1:15215) in
  database ID 22 at offset 0x000000076de000 in file 'C:\Program
  Files\Microsoft SQL
  Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\file.mdf'.
  Additional messages in the SQL Server error log or system event log
  may provide more detail. This is a severe error condition that
  threatens database integrity and must be corrected immediately.

  Complete a full database consistency check (DBCC CHECKDB). This error
  can be caused by many factors; for more information, see SQL Server
  Books Online.

I've also tried setting the DB in SINGLE_USER mode, but with no result.

I am not able to delete nor truncate the table. As I always get the same error.

The table does not have any constraints. It has one PK and one Index, but I can't drop any of them.


texts: How to remove xmlns from child elements with FOR XML

texts:
I have a TSQL query that I use to generate a SOAP document. I am using two namespaces. Namely:
- http://schemas.xmlsoap.org/soap/envelope/ for "packaging" the soap document
- A custom one that describes the data contained within the body

Currently every single element including those in the body contain the soapnamespace reference which I don't need and want in there I only want it in the root element.

So my XML now looks like

<soapenv:Envelope xmlns="soapenv" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pas="foobar">
    <soapenv:Header xmlns="soapenv" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pas="foobar" />
            <soapenv:Body xmlns="soapenv" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pas="foobar">
                    <pas:SomeObject xmlns="soapenv" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pas="foobar">

and I want it to look like

<soapenv:Envelope xmlns="soapenv" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pas="foobar">
    <soapenv:Header/>
            <soapenv:Body>
                    <pas:SomeObject>

In the query I am currently using

;WITH XMLNAMESPACES ( 'http://foobar' as pas,
      'http://schemas.xmlsoap.org/soap/envelope/' as soapenv
      )
SELECT (SELECT '' FOR XML PATH('soapenv:Header'),type ),
etc...
FOR XML PATH('soapenv:Envelope'), ELEMENTS


texts: "Entity Attribute code uniqueness" error in PowerDesigner

texts:
I am using PowerDesigner to do Conceptual Data modeling. If I use the same attribute name in more than one entities, I will get above error when I run "check model" or try to generate Logical Data model.

Is that constraint PowerDesigner only or it belongs to the database design standard. I cannot remember I have this issue before without the PowerDesigner. Why we cannot use the same name in different entities and tables in the next steps?


texts: How can I have flexible PIVOT column names?

texts:
Following the example in https://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/, I have a PIVOT that works but is not very flexible. It is basically

SELECT * FROM
(SELECT
...) AS raw
PIVOT (
AGGREGATE(aColumn)
FOR x IN ( [c1], [c2], ...) AS pvt;

But the columns I want in my pivot table are listed in another table.  What I'd really like to do is something like:

SELECT * FROM
(SELECT
...) AS raw
PIVOT (
AGGREGATE(aColumn)
FOR x IN ( (SELECT cName FROM otherTable) ) AS pvt;

But SELECT there ends up being a syntax error.  Is there any work around for this?  I really don't want to have to edit this query every time I add another field that should be a column in the output.


texts: Why can it take up to 30 seconds to create a simple CCI rowgroup?

texts:
I was working on a demo involving CCIs when I noticed that some of my inserts were taking longer than expected. Table definitions to reproduce:

DROP TABLE IF EXISTS dbo.STG_1048576;
CREATE TABLE dbo.STG_1048576 (ID BIGINT NOT NULL);
INSERT INTO dbo.STG_1048576
SELECT TOP (1048576) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

DROP TABLE IF EXISTS dbo.CCI_BIGINT;
CREATE TABLE dbo.CCI_BIGINT (ID BIGINT NOT NULL, INDEX CCI CLUSTERED COLUMNSTORE);

For the tests I'm inserting all 1048576 rows from the staging table. That's enough to fill exactly one compressed rowgroup as long as it doesn't get trimmed for some reason.

If I insert all of the integers mod 17000 it takes less than a second:

TRUNCATE TABLE dbo.CCI_BIGINT;

INSERT INTO dbo.CCI_BIGINT WITH (TABLOCK)
SELECT ID % 17000
FROM dbo.STG_1048576
OPTION (MAXDOP 1);

  SQL Server Execution Times: CPU time = 359 ms,  elapsed time = 364 ms.

However, if I insert the same integers mod 16000 it sometimes takes over 30 seconds:

TRUNCATE TABLE dbo.CCI_BIGINT;

INSERT INTO dbo.CCI_BIGINT WITH (TABLOCK)
SELECT ID % 16000
FROM dbo.STG_1048576
OPTION (MAXDOP 1);

  SQL Server Execution Times: CPU time = 32062 ms,  elapsed time = 32511 ms.

This is a repeatable test that has been done on multiple machines. There seems to be a clear pattern in elapsed time as the mod value changes:

MOD_NUM TIME_IN_MS
1000    2036
2000    3857
3000    5463
4000    6930
5000    8414
6000    10270
7000    12350
8000    13936
9000    17470
10000   19946
11000   21373
12000   24950
13000   28677
14000   31030
15000   34040
16000   37000
17000   563
18000   583
19000   576
20000   584

If you want to run tests yourself feel free to modify the test code that I wrote here.

I couldn't find anything interesting in sys.dm_os_wait_stats for the mod 16000 insert:

╔════════════════════════════════════╦══════════════╗
║             wait_type              ║ diff_wait_ms ║
╠════════════════════════════════════╬══════════════╣
║ XE_DISPATCHER_WAIT                 ║       164406 ║
║ QDS_PERSIST_TASK_MAIN_LOOP_SLEEP   ║       120002 ║
║ LAZYWRITER_SLEEP                   ║        97718 ║
║ LOGMGR_QUEUE                       ║        97298 ║
║ DIRTY_PAGE_POLL                    ║        97254 ║
║ HADR_FILESTREAM_IOMGR_IOCOMPLETION ║        97111 ║
║ SQLTRACE_INCREMENTAL_FLUSH_SLEEP   ║        96008 ║
║ REQUEST_FOR_DEADLOCK_SEARCH        ║        95001 ║
║ XE_TIMER_EVENT                     ║        94689 ║
║ SLEEP_TASK                         ║        48308 ║
║ BROKER_TO_FLUSH                    ║        48264 ║
║ CHECKPOINT_QUEUE                   ║        35589 ║
║ SOS_SCHEDULER_YIELD                ║           13 ║
╚════════════════════════════════════╩══════════════╝

Why does the insert for ID % 16000 take so much longer than the insert for ID % 17000?


texts: Does both the sql cluster  nodes needs to use the same service account ? In my environment I saw one one was using a service account and another was using a local account although the sql agent was using the same acccount and once the failover went to the node which was using the local account i was not able to failback to the original node and also the back up were failing. Is this due to the mismatch of the sql server service account ? please help


texts:
I got a thread from the processlist showing System Lock on this query, running about 87 seconds

  update zxmb_tac set status=1, verify_date=now() where uid=? and tac=?
  and status=0 and now()

Meanwhile in the error log receiving this

  Aborted connection 24933615 to db: 'xxxxx' user: 'xxxxx' host:
  'xxx.xx.xxx.xxx' (Got an error reading communication packets)

What is the issue, because received a complaint from users, it is causing user timed out when the user is using the apps in the morning, but suddenly everything back to normal this afternoon without changing anything. Can someone help me?


texts: Postgres index on timestamp column not utilized

texts:
I have a database with a column called timestamp of type timestamp without timezone, there is a btree index on this column.
I have a query as below
SELECT
   *
FROM
  employee
WHERE
  timestamp >= timestamp '2020-01-27 13:24:09'

However, the explain analyze shows that the index on the column timestamp is not used:
Seq Scan on employee  (cost=0.00..5498.73 rows=34377 width=381) (actual time=0.016..37.944 rows=34251 loops=1)
   Filter: ("timestamp" >= '2020-01-27 13:24:09'::timestamp without time zone)
   Rows Removed by Filter: 21167
 Planning Time: 0.255 ms
 Execution Time: 40.277 ms

If i change the query with filter condition including a timestamp which is 1 month instead of 1 year (as above) then the index on the column is used.
Bitmap Heap Scan on employee  (cost=59.51..4510.25 rows=2996 width=381) (actual time=2.164..5.204 rows=2958 loops=1)
   Recheck Cond: ("timestamp" >= '2020-12-27 13:24:09'::timestamp without time zone)
   Heap Blocks: exact=208
   ->  Bitmap Index Scan on timestamp_salary_idx  (cost=0.00..58.76 rows=2996 width=0) (actual time=2.113..2.114 rows=2958 loops=1)
         Index Cond: ("timestamp" >= '2020-12-27 13:24:09'::timestamp without time zone)
 Planning Time: 0.195 ms
 Execution Time: 5.485 ms

Why is the index not used for queries with a range past 1 year?


texts:
I am unioning multiple tables together in Postgres, where the timestamp is slightly different, but the same if I round to the hour. Is it possible to make each union show on the same row, if the date is the same? Currently I'm getting duplicate rows of each date.

select date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp),
COALESCE(count(*), 0) as "1489",
null as "1616"
from actions
where group_id = 1489
and TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
group by 1
union all
select date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp),
null as "1489",
COALESCE(count(*), 0) as "1616"
from actions
where group_id = 1616
and TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
group by 1
order by 1

It returns:


texts: Unable to Start PostgreSQL. kernel's SHMMAX parameter exceeded you limit

texts:
When I am trying to start my PostgreSQL I get the following error in the Postgresql.log file:

FATAL:  could not create shared memory segment: Invalid argument
DETAIL:  Failed system call was shmget(key=5432001, size=41279488, 03600).
HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter.  You can either reduce the request size or reconfigure the kernel with larger SHMMAX.  To reduce the request size (currently 41279488 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
        If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for.
        The PostgreSQL documentation contains more information about shared memory configuration.

So in the etc/sysctl.conf file I have changed the values to

# Controls the maximum shared segment size, in bytes
kernel.shmmax = 52033798400
# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 5203379

I restarted the server still I was getting the same error.
So I tried this command to reload

sysctl -p

Then I came up with this error in console

    error: "Read-only file system" setting key "net.ipv4.ip_forward"
error: "Read-only file system" setting key "net.ipv4.conf.default.rp_filter"
error: "Read-only file system" setting key "net.ipv4.conf.default.accept_source_route"
     error: "Read-only file system" setting key "kernel.sysrq"
error: "Read-only file system" setting key "kernel.core_uses_pid"
error: "Read-only file system" setting key "net.ipv4.tcp_syncookies"
error: "Read-only file system" setting key "net.bridge.bridge-nf-call-ip6tables"
error: "Read-only file system" setting key "net.bridge.bridge-nf-call-iptables"
error: "Read-only file system" setting key "net.bridge.bridge-nf-call-arptables"
error: "Read-only file system" setting key "kernel.msgmnb"
error: "Read-only file system" setting key "kernel.msgmax"
error: "Read-only file system" setting key "kernel.shmmax"
error: "Read-only file system" setting key "kernel.shmall"

HOw should I over come this error. What to do to start the Postgres.


texts: How do I restore a mysql dump that was created in xml format?

texts:
I have a rather big XML formatted MySQL dump

It was created with the --xml param (-X) param of mysqldump.

I need to import it in.

This article from 2010 indicates that there was no "built in" way of importing this in. However many years have passed and stuff changes.

Is there a clean/simple way of importing this dump in, or do I need to run through a conversion first?

If I need to convert, anyone got tools for it that are still maintained?


texts: SSIS Blocking itself with IsolationLevel read Uncommited and TransactionOption supported

texts:
My Project components have IsolationLevel = ReadUncommitted and TransactionOption = supported.

My query is a select on table Y verifying if not exists in table X and the result is written to table X.

SSIS opens two ids in SQL Server, a select and a bulk. The bulk is being blocked by the select.

I tried to use NOLOCK andSET ISOLATION LEVEL READ UNCOMMITTED but they do not work.

I was able to resolve this checking the "Check Constraint" on "OLEDB Destination" and I don't know why.


texts: MySQL: Grant creators of view DROP and ALTER on those views

texts:
Is it possible to automatically grant someone the permission to DROP or ALTER their view that they created?
The problem we've got is that there are some views that we don't want the BI team to be able to DROP or ALTER, but the BI team needs to be able to create and edit views that they create. They have permission for creating views, but once they create them they can't fix them or remove them without having to ask for the permissions.
Is it possible to GRANT DROP, ALTER on * WHERE owner=user?
Or should I be redesigning it so they can only create views prepended with BI_VIEW_ and drop/alter the same? Is it possible to do that?


texts: Did PostgreSQL write amplification reduction WARM efforts get released in 10 / 11?

texts: The MariaDB vs PostgreSQL debate continues to rage in my firm :-( and its fuelled by this somewhat famous uber blog post. I found some interesting discussions about write amplification mitigations here. My question is, did anything like this get release as part of PostgreSQL 10 or 11? I would search the documentation, but I'm not sure what it would be called.


texts: I want to remove Json object from Json Array in Mysql 8. Column data type is Json

texts:
I'm using Mysql 8.
I want to remove the specific object from JSON Array which has value like field_1568988989723 in Mysql JSON column

Here is my JSON

[{"section": {"name": "Basic Info", "order": 0, "fields": [{"name": "field_1568988989723", "order": 0}, {"name": "field_1568989125942", "order": 1}]}}]

I've used this way

UPDATE apps_object
    SET detail_view = JSON_REMOVE(
        detail_view, REPLACE(
            JSON_SEARCH( detail_view, 'all', 'field_1568992047479', null, '$**.fields' )
            , '"'
            , ''
        )
      ) where id = 34;

But it just removes

  "value":"field_1568988989723"

but not he whole object.
Is there any way to remove object in MySql not in Programing code?


texts:
I am downloading a bulk of stock data:

Ticker symbol, company name, industry sector,

Open, high, low, close

I was wondering performance-wise if separating the ticker, company name, industry sector from the price data would be optimal, as the tickersymbol and companyname just repeat over and over again.

Or would inner joining the ticker+price every time be slower?

So:

single table (ticker/price), or
two tables (ticker)(price)?


texts:
RESTORE VERIFYONLY and RESTORE DATABASE always fail immediately with this error:

Msg 3241, Level 16, State 201, Line 1
The media family on device '' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.

The database is running with Simple recovery model and all our backup attempts are FULL backups.
DBCC CHECKDB (TheCure_CDR) is completely clean.
The BACKUP DATABASE succeeds every time.  We’ve backed it up locally, remotely, single file, multi-file, with INIT, with NOINIT, and many other permutations.
RESTORE HEADERONLY succeeds every time and generates information consistent with the BACKUP DATABASE command
We’ve tried RESTORE VERIFYONLY and RESTORE DATABASE on the same computer where the backup was taken and on a different computer running the exact same version of SQL Server. This is the version:

Microsoft SQL Server 2014 (SP2-CU4) (KB4010394) - 12.0.5540.0 (X64)
Jan 27 2017 03:40:25
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.2  (Build 9200: )   >(Hypervisor)

A couple notable properties of our database and the circumstances:

The database has a defunct file group that we cannot remove. It’s been that way for years and we’ve backed up and restored many times.

The database has memory file groups and one memory optimized table. Again we’ve backed up and restored many times since this became the case.

The problem started at about the same time we upgraded the database from SP2 to SP2-CU4

We’ve scoured KBs and forums and have not found a single article directly on point where:
• The database is uncorrupted
• The backup is repeatable and always succeeds
• The restore is on the same server where the backup was taken
• RESTORE HEADERONLY always succeeds
• RESTORE VERIFYONLY always fails

Has anyone seen this before?
I've played with the media set settings a lot because "media family" is mentioned in the error message.  The defunct filegroup has three files of size zero which do not correspond to real files.  I got the database into that state years ago by emptying the files then stupidly deleting them before removing them from the database.  I found a number of articles directly on point saying they can't be removed, and the only remedy is move all the data into a new database.  However, it's never caused a problem with backup/restore or even detach/attach, I've just ignored it.


texts: I have built a linked server which links my SQL Server database to an Access DB. Using this object someone is able to see all tables in Access. What I would like to do is to isolate it just to 1 table. So my question is how to build a linked server (from SQL Server) connected only to one specific table in Access?


texts: How to grant set the replication attribute on RDS PostgreSQL?

texts:
On the AWS RDS documentation I see that rds_superuser can:

  Grant and revoke the replication attribute onto all roles that are not
  the rds_superuser role. For more information, see this section in the
  PostgreSQL documentation.

source: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html

But, if I try, while logged in as my rds_superuser:

ALTER ROLE ROLENAME WITH REPLICATION;

I get an error stating that I cannot perform that action due to not being a superuser.

Do you guys know how to set the replication attribute to a user?


texts: An efficient database design for recording complex contact/configurator form

texts:
I know it's a quite generic question and probably opinionated. I don't know where to ask. Maybe you could point me to the right direction.

Currently I have a very big 'configurator', 10 steps long, online form, with 'substeps'. It is for client asking quotes for custom screwdrivers components applications (called 'auto-feed tightening modules'). Say about 50 fields/props per-submission/record.

WHen compiling the form, the user can:

Add as many 'screws' he wants (each screw described by some subfields)
Add as many 'components' he wants(each component described by some subfields) > and for each component, say what (above) screws it is related to

So when the form is submitted, we have a record composed like this:

a lot of 'shared' fields
some 'screw' fields group
some 'components' fields group
and screw and components must be somewhat associated (each component can have 1 or more screw)

All of this should be saved in a database.

Now, since:

we don't know how many screws and components will be added in a single submission
each screw and component has it's own set of fields (duplicated)

I cannot save each submission in one single row/record.

So, how to? Considering that then I'll need to show up the data somewhere else.

In the end, the real question: how should I design my db tables?


texts: is it possible to create a form in the intranet that can be stored in a database?

texts:
I am trying to create a form for my companies intraNet site. The idea is as follows:

An employee wants to make a purchase, so they will go to the appropriate page in the intraNet
They will fill out the form on the intraNet page
They click the email button
The data in the form is saved in a database, and an email is sent to me stating that there is purchase order request form filled out

I am not exactly sure how to go about this. Part of me wants to create it in a Data Access Page but I am not sure that's correct.

Can someone help me?
Is there anyone who can direct me to someone/something that can help me?


texts: How to get updated stats for a truncated table

texts:
I want to check if tables are empty (after a truncate + reload) in a schema and I tried the following:

CREATE TABLE public.whatever (
    id,
    val
) AS VALUES
  (1, 2),
  (1, 2),
  (1, 2),
  (1, 2),
  (1, 2),
  (1, 2),
  (1, 2);

TRUNCATE public.whatever;
ANALYSE public.whatever;
VACUUM public.whatever;
-- stats table seems to be static in the current transaction
-- https://www.postgresql.org/docs/9.6/static/monitoring-stats.html
COMMIT;
-- no result, so it's empty
SELECT *
FROM public.whatever;

-- but this still shows some data in the table:
SELECT
  schemaname,
  relname,
  n_tup_ins
FROM
  pg_stat_all_tables
WHERE
  schemaname = 'public' AND relname = 'whatever'

When I drop and recreate the table (without values), the above shows 0 as n_tup_ins, so at least that workaround works.

DROP TABLE public.whatever;
CREATE TABLE public.whatever (
  id integer,
  val integer
);

Do I have a wrong understanding of the stats table or is this a bug?


texts: Design for m-to-n relation with unidirectional attribute(s)

texts:
Assume I have a Table StackBox that holds items as Item. I allow StackBox to have multiple Item and I allow Item to be in different StackBox. It is obviously an m-to-n relation. Solving this with an according m-to-n mapping table is easy.

But my problem here is the thing that I want to store the information in which order many Item are stored inside a StackBox. I could e.g. add a column to the m-to-n mapping table. Is this a valid solution even though I never want to ask "At which places does Item occur"? I am only interested in "At which place inside StackBox is Item with id x"?

The other option would be to create two additional tables of course. One for the mappings from StackBox to Item that just contains two index tables and another one for the mappings from Item to StackBox that contains the two index columns plus e.g. an additional column position.

I hope my question is clear. Please let me know if I should clarify anything.


texts: SQL Server 2012 Import Wizard appending new data only

texts:
I am using SQL Server 2012's Import wizard to import data from DatabaseA to DatabaseB.

Source: DatabaseA table A
Destination: DatabaseB table B

I have written a source query that does the job fine, but I have only 2 options

Delete destination data - and pull in everything
Append data - but this creates duplicates (i.e. pulls in everything all over again)

So I wanted to try modify my source query to be something like below

select col1, col2, col3 from A where col1 not in (select col1 from
DatabaseB.dbo.B)

In other words I want to test that the data I am pulling from DatabaseA's table A does not already exist Database B's table B where col1 is a unique value.

I would like to be able to do this over and over again, only ever pulling across the delta data

Whenever I try this  I get an error stating

  Deferred prepare could not be completed.
    Statement(s) could not be prepared.
    Incorrect syntax near the keyword 'in'. (Microsoft SQL Server Native Client
      11.0)

Some extra things worth noting (might give some clues)

DatabaseA and DatabaseB are on separate servers
DatabaseA.A.col1 is an identity column
DatabaseB.B is a table I created to match the columns of A, it has no
identity  column, or primary keys etc as it is only a staging table for a migration script I will run after


texts: Why is my query returning an unexpected result?

texts:
Here are the tables I am using:

parts(pid,pname,color)
suppliers(sid,sname,address)
catalog(sid,pid,cost)

I need to find the name of suppliers that supply two different parts with the same price. I keep getting the incorrect answer.

SELECT S.sname
FROM Supplers S
WHERE S.sid IN (SELECT C.pid
FROM Catalog C, Catalog C1, Parts P, Parts P1
WHERE C1.sid = C.sid  AND P.pid = P1.pid AND C1.cost = C.cost);


texts: Memory distribution across numa nodes within Sql server

texts:
I need to understand following behavior of memory distribution across numa nodes within sql server

Current set up:

2 node cluster SQL server 2014 SP2 with virtualization disabled

We have 4 NUMA nodes with 4 sockets with each 20 cores making total of 80 logical processors available using ent core based licensing. Total memory on box is 1 TB with max memory to 656 GB to sql

When checking the memory usage as i blve each numa node will have its own based on max memory/ no of numa nodes:

Therefore all 4 numa nodes have total node memories as 164 GB each.

However when i check the available free across each node they vary by quite a that is numa nodes 0 has 256 MB available, node 1 has 127, node 3 has 111 and 4 has 28.

Similarly when i check the PLE it also responds similarly across all numa nodes- ie on avg node 1 is having ple of 687, 2 with ple 699, 3 with ple 333  and 4 with 122. I know these PLE is just avg and max min changes throughout the day.

But can this uneven distribution cause a performance issue or is there a change which can be implemented?  Thanks


texts: Compare two tables and find dates present in one and missing in another

texts:
I have a month table tbl_month having only one column cal_dates which is list of dates for a month. And another table tbl_employee is having employee_code and attendance_date and it will have entry of employee code and the particular date which he submits the attendance. I need to find only the absent dates of each employee.

 TBL_MONTH
 -------------
ID CAL_DATES
1   2017/01/01
2   2017/01/02
3   2017/01/03
--  --
--  --
30  2017/01/30

 TBL_EMPLOYEES
 -------------
EMP_ID ATTENDANCE_DATE
001   2017/01/01
001   2017/01/02
002   2017/01/01

Here employee 001 have not entered attendance for '2017/01/03' and employee 002 have not entered attendance for '2017/01/02' and '2017/01/03'. I want to fetch this missed dates alone. I'm using below query, I missed something here.

DECLARE   @SQLQuery AS NVARCHAR(MAX)
DECLARE   @PivotColumns AS NVARCHAR(MAX)
select @PivotColumns=COALESCE(@PivotColumns + ',','') + convert(varchar(20),CALENDAR_DATE,120)
FROM (SELECT  CAL_DATES FROM [dbo]. [TBL_MONTH]) AS PivotExample
select @PivotColumns

SET @SQLQuery = 'select B.EMP_ID,'+@PivotColumns+'
                from TBL_MONTH A
                RIGHT OUTER JOIN
                TBL_EMPLOYEE B
                WHERE A.CAL_DATES = B.ATTENDANCE_DATE
                PIVOT( max(A.CAL_DATES)
                FOR B.ATTENDANCE_DATES IN
                ('+@PivotColumns+')
                ) AS P';

SELECT @SQLQuery


texts: Efficient way to check for number of records more than a limit from a table in SQL Server

texts:
I came across queries that can most efficiently get the total sum of the records in the table. However, i want to group the records in a table by a column and check if the count of the records is higher than 1000, if not so, i need to return the count and if so, i can return -1.

TableDesign

CREATE TABLE [dbo].[Accounts](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](255) NOT NULL,
    [CreatedById] [bigint] NOT NULL,
    [CreatedDate] [datetimeoffset](7) NOT NULL,
    [ModifiedById] [bigint] NOT NULL,
    [ModifiedDate] [datetimeoffset](7) NOT NULL,
    [IsActive] [bit] NOT NULL,
    [StagingStatusId] [bigint] NULL,
    [IsApproved] [bit] NOT NULL,
    [IsActive] [bit] NOT NULL
)

Currently, I have the following query that groups the data by the status and then returns the count.

select StagingStatusId, count(1) AS Total
from I8Core.AccountCode
where IsActive = 1 and IsApproved = 1
group by StagingStatusId

However, as the number of records in the database table is higher (> 100k), the query gets slower and delayed.

The aim of this query is to know what statusids are higher than 999 and what are lesser than 999.

  For the records that are higher than 999 (Ex. if statusid = 1 & count
  = 1244, we need as statusid=1,count=999+), else, we need to return statusid=1, count=998 (for any value less than 999).

I found that the below query can return the total row count per table, but this cannot be applied to this scenario.

Kindly suggest the best practice to achieve this.


texts:
I have a query which parses a string values from a column:

SELECT left(DateTimeSent, 17) AS timesent FROM allog_07_11_e query1

When I do:

SELECT left(DateTimeSent, 17) AS timesent FROM allog_07_11_e query1.timesent != '0'

I get:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'timesent'.

Any ideas why?


texts: SQL daily value (today value - yesterday value)


texts: Order by is ignored if inside a derived table of a update (only in MYSQL 5.7)

texts:
I have a update to fix positions values inside a table. The update worked fine in MYSQL 5.5, but after upgrade to MYSQL 5.7 it doesnt work anymore. Now, the "order by" inside the derived table is ignored. So, the rows are updateds based in primary key, not in order by.

May someone help me? Why the order by is ignored in MYSQL 5.7 and how to fixed it?

Thanks!!!

OLD POSITION VALUES

ImageId     Position
389         0
390         6
391         4
392         1

NEW POSITION VALUES, AFTER FIXED (MYSQL 5.5)

ImageId     Position
389         1
390         4
391         3
392         2

NEW POSITION VALUES, AFTER FIXED (MYSQL 5.7)

ImageId     Position
389         1
390         2
391         3
392         4

UPDATE

UPDATE  Image as t1
        INNER JOIN  (
                    SELECT  t.*, @rownum := @rownum + 1 AS newPosition
                    FROM    Image t, (SELECT @rownum := 0) r
                    ORDER   BY t.Position
                    ) as td
            ON t1.ImageId = td.ImageId SET     t1.Position= td.newPosition


texts: Can I create several foreign key constraints between 2 table?

texts:
I am trying to build a database of recipes. The problem I am facing is that my recipe book also contains recipes for some of my ingredients (a.k.a. intermediary product) Instead of the following structure:

product(
    id VARCHAR(10) PRIMARY KEY NOT_NULL,
    name VARCHAR(100) NOT_NULL,
    description VARCHAR
)
ingredient(
    id VARCHAR(10) PRIMARY KEY NOT_NULL,
    name VARCHAR(100) NOT_NULL,
    description VARCHAR
)
recipe_step(
    step_id INT PRIMARY KEY AUTO_INCREMENT NOT_NULL,
    recipe_id VARCHAR(10) FOREIGN KEY REFERENCES product(id)
    ingre_id VARCHAR(10) FOREIGN KEY REFERENCES ingredient(id)
    amount DECIMAL NOT_NULL
)

I wish to keep product and ingredient in one table. Can I write:

product(
    id VARCHAR(10) PRIMARY KEY NOT_NULL,
    name VARCHAR(100) NOT_NULL,
    description VARCHAR
)
recipe_step(
    step_id INT PRIMARY KEY AUTO_INCREMENT NOT_NULL,
    recipe_id VARCHAR(10) FOREIGN KEY REFERENCES product(id)
    ingre_id VARCHAR(10) FOREIGN KEY REFERENCES product(id)
    amount DECIMAL NOT_NULL
)

In recipe_step everything is measured in grams.

I am a total newbie in RDBMS. I haven't really decided yet, but I wish to start with something simple. If sqlite won't do, I would probably try MySQL.


texts: Oracle: Non key-preserved table should be

texts:
I'm getting "ORA-01779: cannot modify a column which maps to a non key-preserved table" when I try to update a join.  I've searched around the site and found a lot of advice on what key-preserved means and why it is necessary... but as near as I can tell I'm complying with that advice, and still getting the error.

I have two tables:

PG_LABLOCATION has, among other things, the columns:
"LABLOCID" NUMBER,
"DNSNAME" VARCHAR2(200 BYTE)

LABLOCID is the primary key, DNSNAME has a unique constraint

PG_MACHINE has, among other things, the columns:
"MACHINEID" NUMBER,
"LABLOCID" NUMBER,
"IN_USE" NUMBER(1,0) DEFAULT 0,
"UPDATE_TIME" TIMESTAMP (6) DEFAULT '01-JAN-1970'

MACHINEID is a primary key
LABLOCID is a foreign key into LABLOCID in PG_LABLOCATION (its primary key)

The update I'm running is:

update
  (select mac.in_use, mac.update_time
     from pg_machine mac
     inner join pg_lablocation loc
       on mac.lablocid = loc.lablocid
     where loc.dnsname = 'value'
       and '02-JAN-2013' > mac.update_time
  )
set in_use = 1 - MOD( 101, 2 ), update_time = '02-JAN-2013';

I'm only updating values in one table (PG_MACHINE) and the join column in the other table is the primary key, which should make it key-preserved by my reading.  I'm concerned that the where clause is causing the problem, but I tried removing the filter on mac.update_time and got the same error, and loc.dnsname has a unique constraint.

What's even odder is that we have, like many folks, a dev and a prod environment.  We did a complete schema and data migration from prod to dev.  I've looked them both over and they have identical indexes and constraints.  The query works in dev, but generates the above error in prod.

So two questions:

1) Can you see what's wrong with my query?
2) Can you suggest what might be different between my dev and prod environment (e.g. server settings) that could cause this error in one but not the other?


texts: Static archive database: SQL Server can't shrink it and leaves ~400G of unallocated space

texts:
We are having an issue with our databases. Here's what happens:

All databases have around 50% of unallocated space and DBCC SHRINKDATABASE/SHRINKFILE won't reclaim the space back. These are archive databases which have been recently cleaned up and are supposed to be read-only, so performance is not an issue (yes, I'm fully aware of shrink and clustered indexes fragmentation and I couldn't care less). The main objective here is to have all datafiles reduced to save $$ on storage space. Again, I don't care about performance, these data files must be reduced at all costs! I'm talking about databases at 800GB, 1,5TB and 2,1TB. So nothing ordinary or small.

So:

shrink database shows ~50% of wasted space
a post-shrink database with mandatory page reorganization (NOTRUNCATE) and mandatory 1% free space keeps showing with 50%
unallocated space
so an 800 GB database, appears in sp_spaceused as having ~410 GB of unallocated space, even after the shrink
a shrink file acknowledged the database has only 1% of free space
when we started using SELECT INTO to migrate tables to another database, we started noticing that the original tables were occupying hundreds of thousands of pages, but after migration they were taking no more than a few hundreds of pages
DBCC also shows that the original database (before SELECT INTO) uses an huge amount of extents

My analysis of this is the following:

shrink is not freeing unallocated space
SELECT INTO yes frees up a lot of space

What I would like some help with:

why shrink isn't working
why SELECT INTO works
why the original database has so much wasted space (yes, it had auto-growth enabled for a long time before becoming an archive)

We're already looking at things like fill-factor, page and extents usage, but what we would like to have is at least an explanation for so many lost space and how to reclaim it without SELECT-INTO all terabytes of data. Any help would be appreciated.


texts: Connection string for MariaDB using ODBC

texts:
What is the connection string for using MariaDB with ODBC?

I'm guessing it's something like

DRIVER={MariaDB ODBC 5.1 Driver}; SERVER=LOCALHOST; PORT=3306; DATABASE=DatabaseName; UID=root; PASSWORD=password;OPTION=3;

Connection strings seems to show strings for MySQL but not MariaDB.

MariaDB seems to have a lot of connection strings for Access, SQL Lite, excel, etc ...

What have I missed?


texts:
I have a below MySQL configuration.
# The MySQL server
[mysqld]
....
key_buffer = 384M
max_allowed_packet = 5M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
...
# enable GTID mode
gtid_mode = ON
enforce_gtid_consistency = 1

# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M

# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

Currently trying to restore a Mysql dump from below command on a different mysql host.
mysqldump -u <> -p <> --ssl-capath=<> -all-databases --triggers --routines --events --single-transaction --master-data >> /tmp/mysqldump

Restore the same dump using below command
cat /tmp/mysqldump | mysql -u <> -p <> --ssl-capath=<>

Current stats
mysql dump - 3 GB.
No network delays, on the same network
Dump generation time - 1 min
Restoration time - 9 min
Host RAM - 125 GB.
Swap - 8 GB
MySQL 5.6,
no purge logs commands issued any where in the code or in the config.
Methods tried to fasten up restoration
Updated mysql config to below
innodb_buffer_pool_size = 2048M

# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_lock_wait_timeout = 50
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

Disabling checks before dump and resetting them after dump.
Setting below before dump and resetting them after restoring dump
UNIQUE_CHECKS=0;
FOREIGN_KEY_CHECKS=0;
FOREIGN_KEY_CHECKS = 0;

None of them fastened restoring database
Any recommendations on ways to improve time to restore dump ?
Tried several possible ways to improve the time to restore dump. But none of them helped. Any suggestions would be appreciated.
Original use case was to flip primary or RW host during failover. Post flip, all slaves are trying to sync with new master. If the replication is broken, as a fallback step we are taking a dump and restoring which turns out to be a cumbersome process to restore replication across 20+ hosts. To switch replication to new primary we run below command on slave
stop slave; change master to ...; start slave
Some times it works but it is broken at times. Not sure when would it break
Resulting error
error 1236, Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. The GTID sets and the missing purged transactions are too long to print in this message. For more information, please see the master's error log or the manual for GTID_SUBTRACT.'


texts: Select row from non-empty node value in xml column using xml query

texts:
I am trying to select the rows from the table by putting the condition on the xml column. The condition like if there is date value on the node ‘Completedon’ in the xml column then select that row.
For this above requirement I wrote one query, that is

SELECT [Tasks].* FROM [TaskManager].dbo.[Tasks] WHERE ( [Work details].exist('for $x in /Root/Row where  ( (empty(xs:date($x/Completedon[1])) or ($x/Completedon[1])="")  )  return $x')>0 )

But in this above query, it selects all the rows which has the ‘Completedon’ node as well as the row without this node.
Please help me, where did I go wrong? And please help me to correct the query..
Thank you in advance…


texts: getting error installing oracle 11gR2: cannot access node

texts:
I'm installing Oracle 11.2 on dedicated hosted server (centOS 5.5). I've gone through the Oracle Quick Installation guide diligently. I believe I've set everything up correctly, although I'm not a DBA and this is my first time doing such a thing. I'm at the point where I've started the Oracle Installer, and made it step 8 of 11, which is the Prerequisite Checks for "typical" install. I'm getting many errors here, they all point to the same thing (e.g. can't access node). Here's the first error, which is for physical memory:

Physical Memory - This is a prerequisite condition to test whether the system has at least 1GB (1048576.0KB) of total physical memory.

Expected Value

 : n/a

Actual Value

 : n/a

 List of errors:

 - PRVF-7531 : Physical memory check cannot be performed on node "host2"  - Cause:  Could not perform check of physical memory on the node indicated.  - Action:  Ensure ability to access the node specified and view memory information.

My hosted server has address of www.host2.domainname.com.

UPDATE1: I've observed that the oracle user cannot ping its own server:

oracle@host2 [~]# ping host2
ping: icmp open socket: Operation not permitted

The root user can ping host2.

Is there some way I can change permissions/etc. to enable oracle user to ping the server? (I'm assuming this is a sympton of the problem, but let me know if I'm on the wrong track here).

UPDATE2: (is anyone reading this?) I've learned that to make the oracle user able to ping, I can adjust the permissions of the /bin/ping file from

-rwxr-xr-x, to
-rwsr-xr-x

However, there are various places that strongly warn against doing this, such as:

http://forums.cpanel.net/f5/ping-icmp-open-socket-operation-not-permitted-14173.html
[http://nixshell.wordpress.com/2007/04/21/suid-shell-scripts-setting-the-sticky-bit/][1]

I haven't changed this permission, not sure if this is the right approach. I'm thinking that the oracle installation instructions are very detailed, and if this was needed, it would be documented to do so. Any oracle experts out there that can offer any advice?

UPDATE 3: I suspect the "server hardening" performed by the hosting company upon provisioning the server has had some effect on the Oracle database installation process, resulting in this error. I implemented chmod 4755 /bin/ping, as well as chmod 4755 /bin/ping6 (logged in as root). I verified that the oracle user can successfully ping host2 now. Then, I ran the Oracle universal installer. The same thing happened -- all of the pre-requisite checks fail (every one of them for the same reason stated above, cannot connect to node: host2). In desperation, I selected "Ignore All" checkbox, and continued the installation. Hey, the install completed successfully! The database runs OK from an initial check.

I have no idea if changing permissions on the ping and ping6 files were necessary or useful here (e.g. I didn't try to complete the install before changing their permissions, so I don't know if this was actually needed; of course I'm reluctant to revert them back in case things break). Are there any experienced Oracle users that can comment/advise on whether I need the /bin/ping file to be -rwsr-xr-x, or can I change back to -rwxr-xr-x?

Also, the list of server hardening is here:

http://www.liquidweb.com/services/serversecure-linux.html

I'd be interested if anyone has an opinion if any of these hardening rules will impact the use of Oracle database. So far, I've found a workaround for limited permissions for the /tmp directory, which allowed me to complete the installation (but doesn't exist thereafter). Does Oracle db need /tmp directory thereafter? etc.


texts: How and where do I confirm TCP ports are open when performing mirroring? (Principal, Mirror & Witness)

texts:
I have a SQL 2017 Principal, Mirror and Witness that I'm trying to set up for high-availability. I've created certificates specifying port 7024 because the customer is running in a Workgroup and not a Domain.  I keep getting the attached error when trying to start mirroring.  I've tried specifying 7024 as the TCP port in the SQL Configuration Manager. Is there something I'm missing?  Does the port for the Mirroring endpoint need to be different from the Principal?  I've tried searching here for the same error but I'm getting mixed information about this.

The error message shown is:

  The server network address "TCP://kb-versiosql1:7024" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)


texts: what are memory fractions and how to get rid of them (or the sort operator) in the example below?

texts:
This execution plan has the following memory fractions:
Memory fraction input:1, Memory Fraction Output:1.

it is a very simple query:

SELECT                  [a].[activityId],
                        [a].[title],
                        [a].[description],
                        [a].[inclusions],
                        [d].[path],
                        [d].[uploadToBucket],
                        [a].[imageUriMain],
                        [a].[locationId]
FROM                    [dbo].[activity]                AS a
LEFT JOIN               [dbo].[document]                AS d
       ON d.documentId = a.documentId
      AND d.activityId = a.activityId

ORDER BY                title                           ASC

what are memory fractions and how to get rid of them (or the sort operator) in the above example?


texts: Data Synchronization process in Database Mirroring and Alwayson Groups SQL Server

texts:
I have few questions on Database Mirroring and Alwayson, i am reading about always on and i found below data synchronization process from primary replica to secondary replica in msdn,

Quesitions:

Without any HA like DBM or AG, will there be any log buffer present in SQL Server WAL mechanism?
Is there any log buffer and log capture which also present in Database Mirroring, and Is that same as in Always on Groups?
What is log pool here?
Why two different name Log flush and Log Hardening?
Can any one provide a link for database mirroring sync process.
In DBM or AG if i am using Synchronous mode, How does application can know Acknowledge Commit is success on both replicas will relational engine take care of that or storage engine take care about that.

Looking forward to learn more, Thanks in Advance!


texts: group tick data to minute data using MySQL

texts:
I have the following table, Input. Do you know how to group the tick data into minute interval as shown in the table, Desired Output using mySQL? I read the data directly from csv to mySQL

Criteria

It will take the last price within the minute bar and output as its minute bar. ie If I have the these two tick data, 06:04:59   1444.25, 06:04:59   1444.00, my output will be 0604 1444.00.
Convert the date, 2013-01-02 to 20130102

 Input
        row Date        Time        Price   Vol
        1 2013-01-02    06:04:59    1444.25 3
        2 2013-01-02    06:04:59    1444.00 2
        3 2013-01-02    06:05:00    1446.25 1
        4 2013-01-02    06:05:01    1446.00 2
        5 2013-01-02    06:05:02    1446.35 4
        6 2013-01-02    06:07:01    1444.00 2
        7 2013-01-02    06:07:02    1444.25 4
        8 2013-01-03    16:08:31    1434.00 2
        9 2013-01-03    16:08:52    1434.25 4

Desired output

    row Date        Time        Price   Vol
    2 20130102  0604        1444.00 2
    5 20130102  0605        1446.35 4
    7 20130102  0607        1444.25 4
    9 20130103  1608        1434.25 4

I have an idea but I not sure how to put into mySQL code.
1. Convert the date to YYYYMMDD and time to HHMM
2. Remove all the duplicates having the same YYYYMMDD and HHMM and only keep the last entry

Thank you for your help and advices
L

(from Comment)

CREATE TABLE `es_test2` (
   `xdate` date NOT NULL,
   `time` time DEFAULT NULL,
   `price` decimal(8,2) DEFAULT NULL,
   `vol` int(8) unsigned DEFAULT NULL,
   `xcol` int(10) unsigned DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8


texts: Using AWS RDS Postgres: Create a superuser with access to all tables (also future created ones)

texts:
In AWS RDS (A managed Database service), we cannot create real superusers. There is a user RDS calls Master User, which can create new users, databases, tables, etc.

However, when another user has created a table inside of a database, the master user cannot access it.

I want to setup the master user that is has access to all tables in all databases (also for future created ones) as well as one user that will have read-only-access to all tables in a specific database (also future created ones).


texts: How to uninstall SQL Server 2014 Standard Edition without install media?

texts:
I've had a copy of SQL Server 2014 Standard installed on my dev box as the default instance for a few years. I installed standard on my machine because I had a free license that I could use via my MSDN subscription. Now I want to uninstall SQL Server 2014 and make SQL Server 2017 Developer Edition my default instance. I tried to uninstall SQL Server 2014 via the standard Add/Remove Programs workflow but after asking me what features I want to uninstall it prompts me for the directory containing the uninstall media. Unfortunately I didn't save the SQL Server 2014 download package I got from MSDN, and I no longer have access to MSDN. I also checked My Visual Studio but it only goes back to SQL Server 2016. How can I uninstall SQL Server 2014 Standard without the install media?

Further background:

The whole reason I want uninstall SQL Server 2014 is I want to use the STRING_AGG function which is new to Azure SQL Databases and SQL Server 2017. To make setting up development environments easier we use the dot notation for our local environment development connection strings, for example our connection string is:

Data Source=.;Initial Catalog=<Database Name>;Trusted_Connection=True;Connection Timeout=30;

The dot notation connects to the default database, and to my knowledge I can't make SQL Server 2017 the default database without uninstalling SQL Server 2014 first. If I can use the dot notation connection string to connect up to SQL Server 2017 without uninstalling SQL Server 2014 then I'd be open to that solution as well.


texts:
I have an SSIS job that generates a list of servers from a table, then executes a stored procedure located in an administrative database on each server.

When I try to add one specific server to the list table, it fails trying to set the connection manager for the new server, with the message below.

There is nothing to be found in the SQL Server Error Log, nor in the Event Viewer on either the the server running the job or the one I'm attempting to add.

  Executed as user: CORPORATE\SqlagentPRD. Microsoft (R) SQL Server Execute Package Utility
    Version 12.0.5000.0 for 32-bit
    Copyright (C) Microsoft Corporation. All rights reserved.

  Started:
    2:40:56 PM
    Error: 2018-09-26 14:40:58.24

  Code: 0xC0209302

  Source: SQL Server Log Consolidation Connection manager "SourceQuery"

  Description: The requested OLE DB provider SQLCNLI11.0 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode. Error code: 0x00000000.
    An OLE DB record is available.
    Source: "Microsoft OLE DB Service Components"
    Hresult: 0x80040154
    Description: "Class not registered".
    End Error
    Error: 2018-09-26 14:40:58.24

  Code: 0xC020801C

  Source: Data Flow Task OLE DB Source [30]

  Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
    The AcquireConnection method call to the connection manager "SourceQuery" failed with error code 0xC0209302.
    There may be error messages posted before this with more information on why the AcquireConnection method call failed.
    End Error
    Error: 2018-09-26 14:40:58.24

  Code: 0xC0047017

  Source: Data Flow Task SSIS.Pipeline

  Description: OLE DB Source failed validation and returned error code 0xC020801C.
    End Error
    Error: 2018-09-26 14:40:58.24

  Code: 0xC004700C

  Source: Data Flow Task SSIS.Pipeline

  Description: One or more component failed validation.
    End Error
    Error: 2018-09-26 14:40:58.24

  Code: 0xC0024107

  Source: Data Flow Task

  Description: There were errors during task validation.
    End Error
    DTExec: The package execution returned DTSER_FAILURE (1).
    Started:
    2:40:56 PM
    Finished: 2:40:58 PM
    Elapsed:
    1.657 seconds.
    The package execution failed.
    The step failed.


texts: Configuring slave from EC2 master to Amazon RDS slave

texts:
I am configuring replication from EC2 master to Amazon RDS instance.

After starting the slave, I don't see any errors but I see slave_IO:

_thread is connecting.

Master version:5.6.23
Slave Version:5.6.19

show slave status \G

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: XXXXXXXXXXX
                  Master_User: XXXXXX
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: cli-bin.000032
          Read_Master_Log_Pos: 713
               Relay_Log_File: relaylog.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: cli-bin.000032
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table: mysql.plugin,innodb_memcache.cache_policies,mysql.rds_sysinfo,mysql.rds_replication_status,mysql.rds_history,innodb_memcache.config_options
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 713
              Relay_Log_Space: 618
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID:
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

show global variables

mysql> show global variables like '%old_passwords%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | 0     |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show global variables like '%secure_auth%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| secure_auth   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
====================

The problem is Slave_IO_state is connecting and Slave_io_thread is connecting state but replication is not happening.


texts: Recover Wrong encoded string values in MySQL database

texts: I have a database in which I store the alt and title values of the images. Unfortunately the program which inserts these values hasn't been connecting to the MySQL database with a UTF-8 link and the data it has inserted to the database is not in Unicode. I have fixed that but I was wondering if there is some way to retrieve the original values from the database and convert them to Unicod


texts: SQL Server : explicitly create an index on a primary key and unique fields

texts:
UPDATED:

I'll make the question clearer as the first answer isn't quite what I was looking for.

How can I execute this create table statement and ensure that the two automatically created indexes are stored on MY_INDEX_FILEGROUP as I am assuming that these automatically created indexes will be stored on MY_DATA_FILEGROUP that the table data will be stored in.

CREATE TABLE Product.Product(
    ProductId tinyint identity(1,1) constraint PK_Product_Product_ProductId primary key,
    ProductName varchar(50) unique not null,
    SupportEnded date default null null
) ON MY_DATA_FILEGROUP
GO

is it possible to seperate the automatically generated indexes into a different filegroup, from the data?

And as a seperate but related question is it possible to enforce a constraint like PRIMARY KEY or UNIQUE whilst preventing the automatic creation of the associated index?


texts: What is the SQL Server equivalent for Oracle character set AL32UTF8?

texts:
While porting data from oracle to Azure sql database I am facing the following issue where, empty data in Oracle is converted as ➞➞➞ in SQL Server
Pic1: Data as in Oracle DB;

Pic2: Data as in Sql server DB;

Pic3: Data when pasted in Notepad++.

The character set in Oracle is AL32UTF8 and that of target SQL Server database is SQL_Latin1_General_CP1_CI_AS. I have tried changing the datatype to NVARCHAR / NCHAR and changing the collation of the column to SQL_Latin1_General_CP850_CI_AS, but no solution. What is the reason for this difference and is there any solution?


texts: Developer's query performing very poorly in custom database

texts:
We have an in-house-developed web application with a very small database (~20MB) and small number of users (~20 max) it is unfortunately a very high-visibility application used by executives. They have found that late in the afternoon every week, when multiple users are making changes simultaneously, performance is EXTREMELY poor - sometimes taking 5 minutes to "save an update" which sometimes fails completely.

The table design is not ideal and I've recommended the developer make some changes which would take some time to implement. The main table has a number of varchar(max) columns in which they store delimited lists of users. All of the tables in the database have a single clustered index on an ID column - another thing I've recommended improving to the developer.

Some "projects" in this application are significantly slower than others, there is one in particular that is almost always slow to save, but outside the heavy-usage period, we can typically see queries perform well.

I am not a developer, but this query looks poor/inefficient to me as a DBA - this is typical of the application's "save project" queries. It looks auto-generated by some tool - the query is generated by the application and it passes parameters for each column in the table, it does not use stored procedures at all.

UPDATE Actions SET Name = @Name,
Start_Date = @Start_Date,
End_Date = @End_Date,
Status = @Status,
Comments = @Comments,
Comp_Date = @Comp_Date,
Owner = @Owner,
Owner_EmplID = @Owner_EmplId,
Status_Name = @Status_Name
WHERE ID = @ID;

The server is SQL 2012 Standard and it has 4 CPUs, 18GB RAM (pretty typical for our environment) - it does run other application DBs but the overall server load is "normal" during peak usage times for this application, and no jobs/loads are running in other DBs at peak usage time. I have not found any deadlocks or locking at all while we monitor peak usage performance.

We do have a Tableau report connected to this same database that's auto-generating some other poor reporting queries, the entire database has about 25,000 rows among all tables, and the query Tableau runs generates millions of rows; I suspect cross-joins in it somewhere but none of my peers want to focus on that aspect of this issue.

Do these update queries actually follow good development practice? Can you give any advice that would help me convince the developer to redesign some of this, or is there something I can do to improve performance without involving them?

Definition of the Actions table:

TABLE [dbo].[Actions](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Project_ID] [int] NULL,
    [Name] [varchar](1000) NULL,
    [Owner] [varchar](500) NULL,
    [Owner_Emplid] [varchar](500) NULL,
    [Comments] [varchar](max) NULL,
    [Start_Date] [date] NULL,
    [End_Date] [date] NULL,
    [Comp_Date] [date] NULL,
    [Status] [int] NOT NULL,
    [Status_Name] [varchar](50) NULL


texts:
I did some search on the matter and I found out that Mysql uses B+Tree index, but when I run "show index" the index type that I get is Btree. And I found in this article that Mysql uses both Btree and B+tree. If it is true that it uses both; why is it named Btree without mentioning B+tree, in which case each one is used. I know the difference between the two, and I want to do some queries to figure out the difference in performance between B-tree and B+tree indexes. Which leads to my second question, is there a big difference between the two in performing certain queries, if yes, please give an example.
Thank you in advance.


texts: How to find rows that do not match between two tables P and N

texts:
Scenario:
I created and updated a table so that the new table (N) has some rows that are different from the previous table (P). Now I want to find the rows that have been updated.

I'm running the following now:

SELECT N.*
FROM previous_table P
JOIN new_table N
  ON P.item_id = N.item_id
WHERE (P.column_1 != N.column_1)
   OR (P.column_2 != N.column_2)
   OR ... (P.column_n != N.column_N);

Right now I only have three columns, so it's working for me but I imagine this method is rather untenable if the number of columns is very large.

Is there a way to simplify the WHERE bit?


texts: Why is an empty XML node xml datatype not being recognized in this query?

texts:
My query seems to not recognize self-closing or empty nodes at all. Shouldn't the empty  node return 'null'? Or an empty string. Even if I place an empty space in the node, it still is ignored.

declare @x xml;

    set @x='<root>

                    sdfg
                    dfgh

                    foo
                    bar
                    poo

            </root>'
select
    x.c.value('(td//text())[1]', 'varchar(1024)') as col1
    ,x.c.value('(td//text())[2]', 'varchar(1024)') as col2
    ,x.c.value('(td//text())[3]', 'varchar(1024)') as col3
from @x.nodes('/root/tr') x(c)

I want the result to be this:

col1 col2 col3
null sdfg dfgh
foo  bar  poo

..but instead I get this:

col1 col2 col3
sdfg dfgh null
foo  bar  poo


texts: Restore PostgreSQL database with same locale settings as source database

texts:
I have a PostreSQL 9.x database on an Ubuntu 14.04 LTS production machine.
My development machine is Windows 7-based, providing PostreSQL 9.y.

I want to restore the Ubuntu PostreSQL database on my development machine.

I noticed that the Ubuntu database uses the following locale settings:

character set encoding=UTF8
collation order, string sort order=en_US.UTF-8
character type, character classification=en_US.UTF-8

When I restore the database on the Windows machine without specifying locales, it will be set up with

character set encoding=UTF8
collation order, string sort order=German_Germany.1252
character type, character classification=German_Germany.1252

My plan is to have the database on my development machine as similar as possible to the database on my Ubuntu machine. So my idea was to first create a database on my Windows machine with production-matching locales, then restore my Ubuntu database prod-db.backup backup file into that created database:

createdb --host=localhost --username=postgres --encoding=Unicode --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8 --owner=prod prod-db
pg_restore --host=localhost --username=postgres --format=custom prod-db.backup --dbname=prod-db

This ideas does not work as the createdb on Windows will complain with the error invalid locale name en_US.UTF-8.

I went on a hunt to find the Windows locale names that match the Ubuntu locale names, including a solution to use the template0 template database, experimenting with different locale identifiers such as en_US.UTF-8 and en_us_utf8 I found scattered in the Internet ... but no solution works.

Is there a locale identifier for Windows that matches Ubuntu's en_US.UTF-8?
Or is locale German_Germany.1252 identical (enough) to en_US.UTF-8 so that I can stick to it and not worry about locales - I want to make sure that database queries behave identical when it comes to aspects such as query result set ordering.


texts: Running queries one after the other in psql/scripting environment?

texts:
I am a little bit concerned about the possibility of queries running concurrently. I want to run a set of queries at worst as soon as the previous is ready through psql (PostgreSQL) and a shell script.

My initial attempt was something like this example:

queries=("SELECT ...", "SELECT ...")
for query in ${#queries[@]}
    "$query" | psql -U postgres -d database
done

I am pretty sure that with the way this is written that the bash script is waiting for each other command to be executed before moving to the next. But I am not sure on the interaction with psql - can there still exist the possibility that the database is executing queries concurrently? If so, how do I approach this so that my commands and queries are executed after each other?


texts: Is it possible to schedule table update/refresh in Oracle Application Express?

texts: I have SQL Server data dumped into a text or csv file available via HTTP. Is is possible to schedule Apex to create a table on that data and keep it updated? How?


texts: How do I use RMAN to create a test database from a full recovery backup dataset?

texts:
In plain english for a someone whose DBA role is a third or seventh hat, and rarely has to touch Oracle, how do I use RMAN to create a test database from a full recovery backup dataset?

I've kept my whining at the end of the question ...

Scenario:

I have a complete RMAN backup set of a production database, taken as a copy of the flash recovery directory, from which I want to build a test database.

I have started to glean an understanding of what I need to do from this post: Oracle 11 rman db restore file corruption.

What I'm not getting is where some of the "alphabet soup" in his example script comes from.

Example script (with annotations):

rman target /
set DBID ...    // Where does this come from?  The existind DB I plan to overlay, or some magic spot in the RMAN backup set?
startup nomount
RESTORE CONTROLFILE FROM 'file destination' // is this my flash recovery directory?
shutdown;
startup mount;

// Is it right to assume that this is the directory containing the backup?
// Or should I list every single backup file from the flash recovery area (ugh!)?
catalog backuppiece 'C:/BKUP/AL_2851492942_20151016_893271615_PGQJSFHV_1_1';

rman:crosscheck backup; Run{
SET UNTIL SEQUENCE 50511;    // Where did this number come from?  Is it important?  Can I ignore this line since I have a complete recovery set?
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}

sqlplus sys/sys as sysdba

shutdown immediate; startup mount;
alter database noarchivelog; // I see this is considered a bad practice elsewhere??

ALTER DATABASE OPEN;
ALTER TABLESPACE mytempspace // what's this line about?  I just want to take a clone of the DB that was backed up elsewhere
ADD TEMPFILE 'c:\oracle............dbf' SIZE 1G REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Whining:

I know that this is yet another noob question, and some of you experienced oracle DBA's will be rolling your eyes and thinking "RTFM".  Unfortunately, the ease of management of SQL server is changing industry expectations, and anyone who can use a mouse is expected to be an instant DBA.  That works with SQLServer, but not with Oracle.

In SQL server this is a simple task that comes down to pick your source backupset, pick your target database, click on the "replace database from backup", click "go", and then go for coffee.

In Oracle with rman, it looks like it should also be a simple task, but all the documents I have found appear to assume that the oracle DBA role is filled by a full time specialist in oracle who just needs a nudge in the right direction.  While Tom Kite is really good, his page on this exact problem spends a lot of time babbling about transportable spaces and such, and quickly becomes indecipherable because of the assumptions of the depth of knowledge of the reader.


texts: Using Resource Governor and allocate more memory to satisfy high Memory Grant request by queries?

texts:
On a SQL Server 2016 database server, I have few queries which are really slow and which ask for more memory (queries are not optimized). This is making the whole database slow as lots of users are running the same query. This is causing other queries to wait. I get CXCONSUMER wait (due to parallel execution) and RESOURCE_SEMAPHORE.

So this is creating memory pressure (I believe that these slow queries are creating memory contention for other queries and in turn creating memory pressure) and i think this is causing my plan cache to clear in fixed intervals.

I read that Memory Grant % by default is 25%. I am thinking of increasing this 25% to give memory for queries. I am trying to solve the problem temporarily for now.

The queries which are not optimized are LINQ queries and changing that will require some time for the team.

So please suggest whether increasing Memory Grant % in resource governor is a good idea or not?

I have total of 128GB RAM and 75% (102 GB) of it is allocated to SQL Server.


texts: Cannot connect in SSMS with Windows Authentication

texts:
I found and added the server name through <Browse for more...> -> Network Servers so it exists and is correct.

What seems weird is that the Password field is empty and I cannot modify it...?
And when I try to Connect, I get this:

I am complete noob in DB management, so guide me thoroughly, please.


texts:
I have a query that returns the availability of an employee (only days they are available):

SELECT
    emp_id,
    emp_number,
    area,
    day_date,
    shift_type,
    start_time,
    finish_time
FROM EmployeeAvailability

I also have a query that gives me all available working days between two dates, which I have written as a CTE:

WITH AvailableDates

AS (SELECT DATETIMEFROMPARTS(DATEPART(YEAR,AvailableDate)
                        ,DATEPART(MM,AvailableDate)
                        ,DATEPART(DAY,AvailableDate)
                        ,0,0,0,0) as AvailableDate
FROM (
SELECT  TOP(DATEDIFF(DAY, '2019-01-14 00:00:00.000', '2019-01-21 00:00:00.000') + 1)
        AvailableDate = DATEADD(DAY, ROW_NUMBER() OVER(
            ORDER BY a.object_id) - 1,'2019-01-14 00:00:00.000')
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b) AvailDates

)

I need to check the employee availability against the dates available and create an unavailable record for the days they don't have a scheduled shift. However when I join on those dates I am getting the following.

My thoughts are to write a nested cursor to check date and employee but is there a better way to get emp_id, emp_Number, area and availableDate to create a record?

Thanks in advance


texts:
I am creating a stored procedure in SQL Server 2016, in which I have the following query:

DECLARE
    @FechaMovimiento DATETIME

SELECT
   @FechaMovimiento = ISNULL(MAX(FechaMovimiento), '31/12/2014')
FROM
   DB0.[dbo].[TR_TAB_OLAP_FechasCierre]
WHERE
   FechaCierre <= GETDATE() -1

INSERT INTO DB1.[dbo].[HVentas_1]
   (
   IDTienda, Id, Numero, NumeroFactura, idhora, IdLineaDocumento,
   IdDocumento, Fecha, UniqueID, Posicion, CodigoDeBarras,
   CodigoNIT_Productor, CodigoAmarreProductor,
   CodigoMarca, CodigoUnidad, CodigoUnidadDeEmpaque, CodigoImpuesto, CodigoAmarre_Articulo_Referencia,
   CodigoFamilia, CodigoLinea, CodigoCategoria, CodigoSubcategoria,
   DescripcionCortadeProducto, Descripcion, DescripcionTienda, BarraTienda, Unidades, PrecioDefecto, Precio,
   Descuento, IDImpuesto, Importe, ImporteIVA, ImporteDtoTotal,
   ImporteDtoTotalIva, ImporteDtoLinea, ImporteDtoLineaIva, Comentario1, Comentario2,
   VolumenTotal, PrecioPorPesoVolumen, Tiendas_ActivasPorBarra
   )
SELECT DISTINCT TOP 5
   v.IDTienda, v.Id, v.Numero, v.NumeroFactura, v.idhora, v.IdLineaDocumento,
   v.IdDocumento, v.Fecha, v.UniqueID, v.Posicion, v.CodigoDeBarras,
   v.CodigoNIT_Productor, v.CodigoAmarreProductor,
   v.CodigoMarca, v.CodigoUnidad, v.CodigoUnidadDeEmpaque, v.CodigoImpuesto, v.CodigoAmarre_Articulo_Referencia,
   v.CodigoFamilia, v.CodigoLinea, v.CodigoCategoria, v.CodigoSubcategoria,
   v.DescripcionCortadeProducto, v.Descripcion, v.DescripcionTienda, v.BarraTienda, v.Unidades, v.PrecioDefecto, v.Precio,
   v.Descuento, v.IDImpuesto, v.Importe, v.ImporteIVA, v.ImporteDtoTotal,
   v.ImporteDtoTotalIva, v.ImporteDtoLinea, v.ImporteDtoLineaIva, v.Comentario1, v.Comentario2,
   v.VolumenTotal, v.PrecioPorPesoVolumen, 0
FROM
   DB1.dbo.TR_TAB_OLAP_HVentas v LEFT JOIN  DB2.[dbo].[HVentas_1] v1 WITH (NOLOCK)
ON
   v.fecha = v1.fecha AND v.[UniqueID] = v1.[UniqueID] AND v.Posicion = v1.Posicion
WHERE
   v1.fecha IS NULL
AND
   v1.Uniqueid IS NULL
AND
   v1.Posicion IS NULL
AND
   v.fecha > @FechaMovimiento

This is the execution plan:

https://www.brentozar.com/pastetheplan/?id=Sy4qks9SW

It is taking too much time (around 10 minutes), even though I am taking a small quantity of records. I would like to know If there's any way to speed up this procedure.

Query timings:

@FechaMovimiento assignment : less than 1 second.
Select distinct: less than 1 second
Insert From Select Distinct: almost 10 minutes.

I have a non-clustered index on HVentas on Fecha, UniqueId and Posicion.
And a columnstore index in HVentas_1.


texts: Always Encrypted returning Timeout error

texts:
I am trying to encrypt a number of tables (1 column in multiple tables) in two different databases. On Database A I manage to encrypt these, while on database B it fails with an error: 'Unable to reconnect to database: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.'.  Have tried this on another staging server and got the same result so issue seems to be database related and not server related. Tried updating statistics and restating the entire machines however problem persists. Also no activity is going on the staging server so should not be related to locks. Any suggestions?

Thanks in advance.


texts: Why does SQL Developer update Oracle Data Dictionaries while SQL*Plus does not?

texts:
I have the following code stored in a script file

create or replace package test_record
is

    type t_test_rec is record(
        name                  varchar2(64)
       ,value                 pls_integer
    );

end test_record;
/

When I execute it from SQL*Plus, the *_IDENTIFIERS data dictionaries are not updated to include the declaration of the type. However, if I copy/paste the same code in SQL Developer and run it as a script (F5), the mentioned data dictionaries are updated.

Even if I force a recompile of the package from SQL*Plus, the *_IDENTIFIERS tables are not updated, no matter what method I use (alter package ..., dbms_utility.compile_schema, dbms_ddl.alter, ...).

How can I force SQL*Plus to behave like SQL Developer in this respect?


texts:
I would like to start running regular index maintenance on a server that has been running for a while without it. The database is running in FULL recover with log shipping. The problem I am having is log space. The partition holding the log does not have the room to hold all of the growth that will occur during the index maintenance.

So far I have come up with two solutions. The first is rebuilding the big ones manually during my weekly maintenance window until I get them under control. This will take a very long time and be time consuming to do. The second is switching the database to SIMPLE during my maintenance window. This is not ideal but seems to be my best bet for getting this done in a short period of time.

Are there any other solutions to this problem?


texts: Multi user, allow user to read data and add their own data

texts:
I am trying to create a recipe database that can have many users. Each user can enter and save their own recipes, which only they have access to. The recipes are made up with various details including the ingredients that the recipe will use. The ingredients can come from 2 sources, the main 'read only' ingredient list provided by the app or from any 'custom' ingredients that the user has created (likely because the ingredient they wanted was not available in the list provided by the app). In each case the same information is stored about the ingredients (i.e. nutritional information etc.)
So a currently the schema looks something like:
User

id
name

Recipe

id
title
userId

RecipeIngredient

id
ingredientId
quantity
recipeId
userId

Ingredient

id
name
nutritionalInfoId

nutritionalInfo

id
calories
fat
protein
carbs

Category

id
name
userId

Recipe__Category

recipeId
categoryId

What is the best way to handle this? I did start going down the route of adding a UserIngredient table:
UserIngredient

id
id
name
nutritionalInfoId
userId

However, this made things more complicated when it came to assigning an ingredient to RecipeIngredient as it was either a relationship to Ingredient if the User had picked one of the apps 'built in' ingredients from the Ingredient table or one of their own from UserIngredient. It felt like there was probably a better way to handle this?


texts: Replication from Enterprise to Standard edition

texts:
Can I define transactional replication while the Publisher and Distributor are both Enterprise Edition and the Subscriber is Standard Edition?

What do I need to consider in case it is possible (given I already know about partitioned tables)?


texts: How to get the information of the optimizer of sql server?

texts: The info like total optimization time, the time spent on each stage of optimization, the number of alternative plans and the cost of each alternative plan. Is there any way to get these info using sql server 2008?


texts:
Can I share a VMDK among 2 nodes for an active passive setup for a SQL Server Failover Cluster setup?

Most of the articles I see mention to use RDM's, however we use Veeam backup for the environment and it doesn't support RDM's.


texts: SQL INSERT Query takes 2 seconds and sometimes much longer to execute

texts:
I know the question's title is bad, But I have  an hard time figure it out my self...
sometimes this query runs in 2-3 seconds and sometimes it's takes even 30 seconds

I have a table named ranking_temp_free:

I have one Index except the primary key:

I have no triggers or partitions on this table..

I'm using this Query:

INSERT IGNORE INTO ranking_temp_free
(countryID, categoryID, ranking, popID, app_id, check_time)
VALUES (/*Here comes 50,000 rows of Data*/);

And as I wrote before.. The time that takes the query to finish is not fixed as I wish to be...

Logs:

THERE ARE MORE LOGS BEFORE..

07-02-2013 00:24:37 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:24:54 Inserted: 50000 rows
QUERY TIME : 17 SECONDS

07-02-2013 00:25:05 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:25:41 Inserted: 50000 rows
QUERY TIME : 36 SECONDS

07-02-2013 00:26:02 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:26:07 Inserted: 50000 rows
QUERY TIME : 5 SECONDS

07-02-2013 00:26:28 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:26:35 Inserted: 50000 rows
QUERY TIME : 7 SECONDS

07-02-2013 00:26:46 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:26:52 Inserted: 50000 rows
QUERY TIME : 6 SECONDS

07-02-2013 00:27:03 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:27:17 Inserted: 50000 rows
QUERY TIME : 14 SECONDS

07-02-2013 00:28:19 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:28:39 Inserted: 50000 rows
QUERY TIME : 20 SECONDS

AND SO ON.........

It's a simple INSERT so why the time is different?.

Just to be clear: THERE ARE NO ANY OTHER PROCESSES ON THE SQL SERVER!

EXAMPLE FOR THE TABLE DATA:


texts:
I'm seeking for a resolution into wait types I observe on one of my servers.
I've been keeping wait statistics for a long time and can positively say that the top two are PAGEIOLATCH_SH and LCK_M_IX with 29% and 20% respectively of total waits of the instance.
There is only one database in the (only) instance.
I have searched into the resolution online but what I need is some solid steps to take and advice on where and what to look.
Thank you all.


texts: How to create a view with added rows for missing dates?

texts:
I'm not sure how to accurately phrase this question.

Essentially, say I have a bunch of salespeople on different sales teams. Like, the Blue Region, the Orange Region, the Red Region, whatever.

Now, these sales people are each connected to one sales region, but may, on occasion, change sales regions over time.

Say Bob Jones was in the Blue Region for January 2015, February 2015, March 2015, but on April 2015, he went to the Green Region.

So I'm trying to create a report that would aggregate sales revenue based on Region (even though at the technical level, its logged to sales person).

So we would have transaction (table: employee_sales) data as follows:

name           date                 sales
Bob Jones      February 18th, 2015   $50,000

etc. etc.

So in order to aggregate by region, I would need a helper dimension table as follows (Remember, region can change over time) (table: employee_region)

name         region      month      year
Bob Jones    Blue        January    2015
Bob Jones    Blue        February   2015
Bob Jones    Blue        March      2015
Bob Jones    Green       April      2015

Then I can simply do a query:

select er.region, er.month, er.year, sum(es.sales)
from employee_sales es
inner join employee_region er
on es.name = er.name and month(es.date) = er.month and year(es.date) = er.year
group by er.region, er.month, er.year

So this would give me the data I need.

HOWEVER, now I have a problem --- say I have 500 employees and the REGION data is updated a month in arrears. So right now, February 2016, we only have region data from January 2016.

Would I would LIKE to do, is ... for the current month (February 2016) ... if the data in the "helper table" for region is missing for the month, take the last month found (which may be January, but sometimes even December or November potentially).

I'm not sure what to do here. Create some kind of view?

Do I restructure the 'helper table' so it's more of an inequality statement?

EDIT: I think this is a slowly changing dimensions problem. Hmm I probably have to reorganize the dimension table.


texts: Capture live stored procedure calls (as per Query Profiler) in SQL Azure

texts:
TL;DR - Is it possible to capture live procedure calls (with parameter names/values) in SQL Azure, to replicate the output of SQL Profiler illustrated below - or - is there a better way to achieve this?

Historically when my web application has performed badly, I used SQL Server Profiler to capture calls from the web app, and see the stored procedures and their parameters as they were executed.  I then copied/pasted them into SSMS and used the execution plan to modify the procedures until performance was enhanced:

Now, while testing out SQL Azure, I see a lot of people asking how to use Profiler with SQL Azure, and being told it can't (and probably won't in future).  While that might not be true any more (as a lot of questions online are quite old), the question above remains.

I've tested out the Azure Performance Insight, but it gives a good overview, and doesn't capture quite what I'm looking for.