texts: Row_Number() not working with OVER PARTITION BY

texts:
I have a query that I am "rolling up values". Lets say I have two rows that are the "same", save for one field:

    [CNumber]   [PNumber]    [date]   [FullName]           [Type]  [P]          [S]   [End]
    T00000000   A0100000    2014/04/11  CUSTOMER            TYPE1   828600.86   0.00    0.00
    T00000000   A0100000    2014/04/11  CUSTOMER            TYPE2   250000.00   0.00    0.00
    T00000000   A0100000    2014/04/11  CUSTOMER            TYPE3   250000.00   0.00    0.00
    T00000000   A0100000    2014/04/11  CUSTOMER            TYPE4   250000.00   0.00    0.00

I am sum'ing those rows, with the help of over(partition by TNumber) That is working just fine. No issue there, the number get added, so both rows have the same amounts.

T00000000   A0100000    2014/04/11  CUSTOMER            TYPE1   1578600.86  0.00    0.00
T00000000   A0100000    2014/04/11  CUSTOMER            TYPE2   1578600.86  0.00    0.00
T00000000   A0100000    2014/04/11  CUSTOMER            TYPE3   1578600.86  0.00    0.00
T00000000   A0100000    2014/04/11  CUSTOMER            TYPE4   1578600.86  0.00    0.00

Totally fine, and is what I want.

Now I just want to pick ONE of them to display. some of the partitions have unique row numbers just fine and dandy.
I am attempting to do that with the code below:

    ;WITH D AS
    (
        SELECT A.[CNumber]
              ,A.[PNumber]
              ,A.[Date]
              ,ROW_NUMBER() OVER(PARTITION BY A.[CNumber] ORDER BY A.[date] DESC) AS CRowNumber
              ,A.CType
              ,A.P
              ,A.S
              ,A.[End]
        FROM #tmp as A

    )

SELECT  B.CNumber
    ,B.PNumber
    ,B.LDate
    ,C.FullName
    ,B.[CType]
    ,D.CRowNumber
    ,SUM(B.P) OVER(PARTITION BY B.[CNumber]) AS P
    ,SUM(B.E) OVER(PARTITION BY B.[CNumber])  AS E
    ,SUM(B.[End]) OVER(PARTITION BY B.[CNumber]) AS [End]

FROM #tmp AS B
    INNER JOIN [DATABASE].dbo.[TABLE] AS C
        ON B.PNumber = c.Code
    INNER JOIN D AS D
        ON t.PNumber = CT.PNumber
WHERE B.I >= @Var1
GROUP BY B.CNumber
    ,B.PNumber
    ,CONVERT(varchar(11),B.[Date],111)
    ,SUBSTRING(c.FullName,10,40)
    ,B.CType
    ,B.Paid
    ,B.Expense
    ,B.[End]
    ,D.CRowNumber
ORDER BY B.[CNumber] DESC

When I run the code, the row_number() isn't unique to the [CNumber] such like:

    [CNumber]   [PNumber]    [date]   [FullName]           [Type]  [P]          [S]   [End] [CRowNumber]
    T00000000   A0100000    2014/04/11  CUSTOMER            TYPE1   1578600.86  0.00    0.00  1
    T00000000   A0100000    2014/04/11  CUSTOMER            TYPE2   1578600.86  0.00    0.00  2
    T00000000   A0100000    2014/04/11  CUSTOMER            TYPE3   1578600.86  0.00    0.00  3
    T00000000   A0100000    2014/04/11  CUSTOMER            TYPE4   1578600.86  0.00    0.00  1

Am I just misunderstanding and or totally off base,(on how to go about this) how row_number functions with over(partition by)?

I would like to see that all the rows within the partition have their own unique row number.

EDIT 1

switched the logic from having  the "type" column tag along through the CTE, to applying it once I had the final select ready. It must've been the join at the bottom for B.PNumber = C.code that was screwing it up.

Thanks for all the suggestions.


texts:
I have a very complex query which generates a view in PostgreSQL.
Now the query returns duplicated rows, well almost duplicated: one field, the "url" column is different.

Now we still want to group those "almost duplicated" rows. We can do this by skipping the url column, but we need that column. We already got the latest date by using this statement:

max("cdata".date) AS date

So we would like to do the same thing, but with url. So get the latest row by date and show that url.

Is this possible?

Like I said, the query is complex, so creating a subquery which executes the same query again with some changes wouldn't be a good idea. Also I don't have a primary key, so that makes it even more difficult. I hope you guys have some kind of idea to fix this issue.

The current query:

CREATE MATERIALIZED VIEW cdataview AS
 SELECT md5(ROW("cdata".acc_id, "cdata".adgcid, "cdata".adgid, "cdata".kid, "cdata".sterm, "cdata".mtype)::text) AS id,
    "cdata".acc_id,
    "cdata".kword,
    "cdata".kid,
    "cdata".url,
    "cdata".adgcid,
    "cdata".adgname,
    "cdata".adgid,
    "cdata".adgstatus,
    "cdata".sterm,
    "cdata".mtype,
    sum("cdata".cost) AS total_cost,
    sum("cdata".cval) AS cval,
    sum("cdata".impr) AS impr,
    sum("cdata".clicks) AS clicks,
    sum("cdata".convcl) AS convcl,
    ( SELECT accs.name
           FROM accs
          WHERE accs.id = "cdata".acc_id) AS accname,
    ( SELECT cmps.name
           FROM cmps
          WHERE cmps.id = "cdata".adgcid) AS campname,
    ( SELECT kpr.qscore
           FROM kpr
          WHERE kpr.id = "cdata".kid
          ORDER BY kpr.date DESC
         LIMIT 1) AS qscore,
    round(
        CASE
            WHEN sum("cdata".impr) = 0 THEN 0::numeric
            ELSE sum("cdata".clicks)::numeric / sum("cdata".impr)::numeric
        END, 3) AS ctr,
    round(
        CASE
            WHEN sum("cdata".clicks) = 0 THEN 0::numeric
            ELSE sum("cdata".cost) / sum("cdata".clicks)::numeric
        END, 3) AS cpc,
    round(
        CASE
            WHEN sum("cdata".convcl) = 0::numeric THEN 0::numeric
            ELSE sum("cdata".cost) / sum("cdata".convcl)
        END, 3) AS cpa,
    round(
        CASE
            WHEN sum("cdata".impr) = 0 THEN 0::numeric
            ELSE sum("cdata".impr::numeric * "cdata".avgpos) / sum("cdata".impr)::numeric
        END, 2) AS avgpos,
    max("cdata".date) AS date
   FROM sqpr "cdata"
     LEFT JOIN kpr kpr ON "cdata".kid = kpr.id AND kpr.date = (( SELECT max(kpr.date) AS max
           FROM kpr
          WHERE kpr.id = "cdata".kid))
  GROUP BY "cdata".kword, "cdata".kid, "cdata".acc_id, "cdata".adgstatus, "cdata".adgcid, "cdata".adgname, "cdata".adgid, "cdata".sterm, "cdata".mtype, "cdata".url


texts: PostgresSQL dynamic execute with argument values in array

texts:
I'm wondering if this is possible in Postgres:

Best explained using a contrived example:

create or replace function test_function(filter_param1 varchar default null
                                       , filter_param2 varchar default null)
  returns integer as
$$
declare
  stmt text;
  args varchar[];
  wher varchar[];
  retid integer;
begin
  if filter_param1 is not null then
    array_append(args, filter_param1);
    array_append(wher, 'parameter_name = $1');
  end if;
  if filter_param2 is not null then
    array_append(args, filter_param2);
    array_append(wher, 'parameter_name = $2');
  end if;

  stmt := 'select id from mytable where ' || array_to_string(wher, ' or ');
  execute stmt into retid using args;

  return retid;
end;
$$ language plpgsql;

In Python there is *args - perhaps PostgreSQL has a similar mechanism?

EDIT for Erwin Brandstetter questions:

All filter parameters will be applied to different columns, but should be AND'ed.
Returning setof makes much more sense here.
All parameters can be of the same column type (ie. varchar).


texts: Select fields from sub query , but I face field could not be bounded

texts:
SELECT
    ROW_NUMBER() OVER(ORDER BY c.FullName ASC) AS No#,
    a.AcctBranchName,
    c.FullName,
    c.BirthDate,
    c.Age,
    a.StartDate as 'Account Start Date',
    c.CustStartDate as 'Customer Start Date',
    c.CustomerNum,
    a.Currency,
    a.AccountNum,
    a.ProductType as 'Product Name',
    a.Balance as 'Balance KHR',
    a.BalanceUSDEquiv as 'Balance USA',
    aa.ClosedDate,

    CASE
        WHEN (a.BalanceUSDEquiv*3 >= 200000) then '200,000'
        ELSE a.BalanceUSDEquiv*3 end as 'Sum Insure in USD',
    CASE
        WHEN (a.BalanceUSDEquiv*3 > 200000) then 200000*(0.1/100)/12
        ELSE a.BalanceUSDEquiv*3*(0.1/100)/12 end as 'Premium to be paid'

FROM
     v_Account AS a
LEFT join v_Customer AS c
    ON c.CustomerID = a.CustomerID AND c.Businessdate = a.Businessdate

WHERE
    a.AccountNum IN (
        SELECT  aa.AccountNum FROM v_Account AS aa WHERE
         aa.Category = 'Deposit' AND aa.ProductCode IN ('6003', '6253')
        AND aa.Businessdate='2018-12-06' AND aa.ClosedDate is not NULL
    )
    AND a.Category = 'Deposit'
    AND a.ProductCode IN ('6003', '6253')
    AND c.Businessdate='2018-12-05'

How can I select aa.ClosedDate from my sub query or any alternate solution which I can get the ClosedDate of 2018-12-06.

Example. Account number: 123456 is closed at date 2018-12-06 (closed mean no balance) So in order to get the balance of this account number I need to look up this at 2018-12-05 (before 1 day this account is closed) ,then I can get the balance for my calculation purpose for example.


texts: How to determine used/free space within SQL database files?

texts:
I normally use the following method to determine the free/used space within each file of a database:

Select *, fileproperty(name, 'SpaceUsed') as Used
From dbo.sysfiles

This returns total and used space in pages, which I then multiply by 8 to get KB (or divide by 128.0 to get MB).

I found another script than instead uses DBCC showfilestats and dbcc sqlperf(logspace) to return the TotalExtents and UsedExtents, which can then be multiplied by 64 to get KB (or divided by 16.0 to get MB).

Ignoring the extra columns, will these two always give identical values for free/total space? What about sp_spaceused?

Does their accuracy both depend on a recent DBCC UPDATEUSAGE?

Is there another, better method for determining used/free space? (I need this script to work on SQL 2000, 2005, and 2008 servers)

Partially related: Can you have a partially-allocated extent? (only 3 of the 8 pages within an extent are allocated, for example)


texts: Using Same CASE WHEN Conditions For Multiple Query Columns

texts:
Is there a "better" way to rewrite a SELECT clause where multiple columns use the same CASE WHEN conditions so that the conditions are only checked once?

See the example below.

SELECT
    CASE testStatus
        WHEN 'A' THEN 'Authorized'
        WHEN 'C' THEN 'Completed'
        WHEN 'P' THEN 'In Progress'
        WHEN 'X' THEN 'Cancelled'
    END AS Status,

    CASE testStatus
        WHEN 'A' THEN authTime
        WHEN 'C' THEN cmplTime
        WHEN 'P' THEN strtTime
        WHEN 'X' THEN cancTime
    END AS lastEventTime,

    CASE testStatus
        WHEN 'A' THEN authBy
        WHEN 'C' THEN cmplBy
        WHEN 'P' THEN strtBy
        WHEN 'X' THEN cancBy
    END AS lastEventUser
FROM test

In non-sql psuedo-code, the code might look like:

CASE testStatus
    WHEN 'A'
        StatusCol        = 'Authorized'
        lastEventTimeCol = authTime
        lastEventUserCol = authUser
    WHEN 'C'
        StatusCol        = 'Completed'
        lastEventTimeCol = cmplTime
        lastEventUserCol = cmplUser
    ...
END

Note:

I am aware of the obvious normalization issues implied by the query.  I only wanted to demonstrate the issue.


texts: Why are my statistics getting out of date so quickly?

texts:
I've got a large table (300 million rows), with about 1 million rows added per day. This is time-series data, so I have the following setup:

CREATE TABLE Readings (
    Id INT PRIMARY KEY,
    TrackerId INT,
    DateProcessed DATETIME,
    -- About 10 other columns of data
)

CREATE INDEX IX_TrackerId_DateProcessed
ON Readings (
    TrackerId,
    DateProcessed
) INCLUDE (
    -- all the other columns
) WHERE (
    TrackerId IS NOT NULL
)

When we view the data, I usually run something like the following query:

SELECT Latest.*
FROM Trackers
CROSS APPLY (
    SELECT TOP 1 *
    FROM Readings
    WHERE TrackerId = Trackers.Id
    ORDER BY DateProcessed DESC
) Latest

This used to work fine. Now, it takes forever to generate the query plan, even when I explicitly tell it to use the index. The only solution I have found so far is to update the statistics for the table with the following command:

UPDATE STATISTICS Readings

This takes about ten minutes to run, and then the select query runs fine, but only for a day. Then I have to update the statistics again. Why are the statistics getting out of date so quickly, and what can I do to prevent this?

Points worth mentioning:

I only ever append to the table, I never update or delete rows
There are some old rows with a null value for tracker id, but no more will be added
The database is running at compatibility level 14
I can't enable trace flag 2371 in Azure SQL

Additional answers to questions in the comments

The estimated query plan before updating the statistics

The estimated query plan after updating the statistics

The plans before and after updating the statistics look identical to me. The issue is not that I am getting a bad query plan, but how long it takes to get the plan. Before updating the statistics, I had to wait two minutes for the plan. After updating the statistics, it got the plan instantly.

While writing this, I just realized that it would actually be quicker to wait for the query, and let the cache do the work, rather than update the statistics, but that's still not solving the problem. This query is executed from within Entity Framework, with a timeout of 30 seconds, so two minutes is still too long.


texts:
I have created a certificate authority on my Linux server using OpenSSL. I have added that certificate authority to the Windows Trusted Root Certificate Authorities, which it now recognises.

What do I need to do, from this point, to create a valid SSL certificate that SQL Server will recognise and be able to use? Assuming that my CA key is named root.key, its certificate named root.pem, and its equivalent certificate signing request named root.csr.

No guide / tutorial has been able to tell me this. Every guide seems to be concerned with using the database server to create a certificate authority and generate keys, or something or other - I do not want this. All I want is for my Linux server to generate the SSL certificates, and for those certificates to be used by my client database machines (possibly without going through Microsoft Management Console and other unnecessarily complicated bullsh*t), such that I can connect to them via TCP/IP from my Linux machine (it can connect fine when using no encryption). The computer running the MSSQL server is not part of a domain, and its computer name / hostname is WORKSTATION (if this matters at all).


texts: SQL Server Snapshot replication - Failing to start the Publisher snapshot agent

texts:
I am trying to set up a Snapshot replication using an Azure SQL Managed Instance. While trying to see the Snapshot Agent status, I see this error. Failed to connect to Azure Storage '' with OS error: 53.
Update : I fixed this error by setting up the Snapshot folder in this format : \\replstorage.file.core.windows.net\replshare
However, when I fixed this error I get this new error.  "Access to the path '\unc\XXXXXXXX-MANAGEDINSTAxxxxxxx_XXXXXXX_PUBLICATION\20200708132018' is denied."
I followed this link and made sure that I I set the  Secure transfer required as Disabled, and Allow access from all networks is enabled, and Azure Active Directory Domain Services is disabled in the storage account settngs in the Azure Portal. Still the error remains the same.
Can anybody guide me to find out the problem ?


texts:
I am sort of confused about orphaned users in database snapshots created on the partner server participating in database mirroring.

Server A acts as principal for database X, with server B as its mirror, having database snapshot configured for that database X for reporting purposes.

We recently had a failover. After that I fixed the orphaned users in database X on server A.

However, when I view the same for the database Snapshot on server B, they are not listed.

If i manually try to fix orphans on X-snapshot on server B, it throws the error

  Failed to update database "X-snapshot" because the database is
  read-only

The snapshot job runs every 30 mins.

Please clarify or suggest how to fix the orphaned users in the snapshot


texts: Drive with ldf files failed and some databases left in recovery pending status

texts:
We are keeping mdf and ldf files on seperate drives of a physical machine. One of the disks forming the drive for ldf files failed. Some of the databases that had their ldfs on that drive are now in "Recovery pending" status.

It is not a production environment so some data loss is acceptable but losing entire databases is not. Restoring from backups is not a viable option at this time.

I tried to:

1) Detach databases, but I got following error:

  File activation failure. The physical file name "D:\LDFfiles\DatabaseName.ldf" may be incorrect.
  The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

  Msg 5181, Level 16, State 5, Line 1 Could not restart database
  "DatabaseName". Reverting to the previous status.

  Msg 5069, Level 16,
  State 1, Line 1 ALTER DATABASE statement failed.

  Msg 823, Level 24,
  State 2, Line 1 The operating system returned error 2(The system
  cannot find the file specified.) to SQL Server during a read at offset
  0000000000000000 in file 'D:\LDFfiles\DatabaseName.ldf'. Additional
  messages in the SQL Server error log and system event log may provide
  more detail. This is a severe system-level 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.

2) Bring them online, but I got following error:

Could you please advise what are my options to recover the database? Best to my knowledge mdf file is healthy and can be reused with rebuild log query. The main problem is that it is currently attached. Any advice will be welcome.


texts: Are my MongoDB servers right in setting?

texts:
I have a small web site, so I used two servers.

A: master 4G Ram, 96G SSD
B: slave 2G RAM, 96G SSD

The B server is always for backup, no read, no write.

In my application, many users can (and will) be trying to update one document at the same time by inserting a few embedded documents (objects) into it during one single update.

My questions

Are all my operations in A server Strong Consistent?
Do I need to use "fsync = true" in my code in PHP?


texts: How to store audios, videos and other files in MySQL database?

texts:
I would like to create a database for my files so that I can search easily without the help of "windows search".

Is it possible to store audio, video and other files in MySQL database in the tables?


texts: PosgreSQL: very slow select on a single table with no joins

texts:
I'm trying to help a colleague who's having some problems with a PostgreSQL query.

We've narrowed it down to a join to one table making the query painfully slow and, upon investigation, we found that even a simple select * request on the "data" table takes up to 2 minutes to return the results.

The table contains 420,000 records and 13 columns, none of which are BLOBs or anything massive (they're all text).

Running an EXPLAIN ANALYSE VERBOSE massively underestimates the time taken to return the results

i.e. for SELECT * FROM DATA

"Seq Scan on data  (cost=0.00..18284.35 rows=389735 width=27)
(actual time=0.135..594.929 rows=426934 loops=1)"
"  Output: id,..."
"Total runtime: 1069.643 ms"

Whereas running the query without EXPLAIN gives

Total query runtime: 86598 ms.
426934 rows retrieved.

I'm sorry if I'm breaking any conventions in asking the question but I don't really know Postgres at all so if anyone could suggest what kind of underlying issues we should be looking for to help resolve this issue, it would be gratefully received.

Edit: as requested, the table definition

CREATE TABLE data
(
  id text NOT NULL,
  methodid text NOT NULL,
  replicateid text,
  t_id text NOT NULL,
  t_name text NOT NULL,
  a_id text NOT NULL,
  originalname text,
  qualifier text,
  frequency text,
  frequencyunits text NOT NULL,
  determiner text,
  sensitive text,
  versionkey text,
  CONSTRAINT data_pkey PRIMARY KEY (id),
  CONSTRAINT sample FOREIGN KEY (sid)
      REFERENCES sample (sid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

CREATE INDEX eventid_idx
  ON data
  USING btree
  (id);


texts: How do I check if a VARCHAR column has control characters in it?

texts:
How do I check if a VARCHAR(MAX) column has ASCII Control Characters in SQL Server?

Specifically I am looking for the presence of 0x01 SOH. Will this code work, it is copied from an example where they check for LF character i.e CHAR(13)

SELECT * FROM notes where content LIKE '%' + CHAR(1) + '%';

In any case I would like to get a generic query  for all the Control Characters instead of a hard-coding like this.


texts: Dump only the Stored Procedures in MySQL

texts: I need to dump only the stored procedures : no data, no table creation. How can I do this using mysqldump?


texts: Automatic failover (Clustering/Always On AG)

texts:
Let's says we have 2 nodes (N1, N2) clustered (say C1) on-site. And an Always On AG is configured between this cluster C1 and a new SQL server instance N3 (N3 is off-site).

Now, if there is a problem (failure) with N1 then N2 will become active (or vice versa) and continue to serve requests. This is due to clustering concept.

However, if there is a failure of the cluster, then as per SQL 2012 documentation, there cannot be an automatic failover as this is not supported between clustered node and separate server instance. DBA will have to manually do the failover.

Please can you confirm the following:

Is this feature (automatic failover with clustered and separate server instance) supported in newer SQL versions (2016,2017)?
So as to be able to use the automatic failover feature we have only 3 options:

a) Cluster all the server instances into 1 cluster and setup a disk backup strategy (since single shared disk is used in clustering concept)

b) Don't cluster at all and only setup AG between SQL server instances.

c) Cluster all server instances into 1 cluster and setup AG between them - however bit confused in practicality of this scenario - What is the purpose of having AG within cluster?

Any guidance and reference links will be very helpful.


texts: InnoDB: "Only one log file found" after binary import

texts:
My first one on DBA...

After successful upgrading a master MySQL server 5.5 to 5.6.17 (CentOS 6 VPS serer), I done and moved my binary backups to a slave server (same version 5.6.17 but different environment on Jelastic). The binary backup was done using Percona Xtrabackup 2.1.7 with preparation. Both systems are x86_64 GNU/Linux

After restoring the binary backups to /var/lib/mysql and starting MySQL I was welcomed with the following error from MySQL log:

140725 15:02:11 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2014-07-25 15:02:13 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-07-25 15:02:13 11854 [Note] Plugin 'FEDERATED' is disabled.
2014-07-25 15:02:13 11854 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-07-25 15:02:13 11854 [Note] InnoDB: The InnoDB memory heap is disabled
2014-07-25 15:02:13 11854 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-07-25 15:02:13 11854 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-07-25 15:02:13 11854 [Note] InnoDB: Using Linux native AIO
2014-07-25 15:02:13 11854 [Note] InnoDB: Using CPU crc32 instructions
2014-07-25 15:02:13 11854 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-07-25 15:02:13 11854 [Note] InnoDB: Completed initialization of buffer pool
2014-07-25 15:02:13 11854 [ERROR] InnoDB: Only one log file found.
2014-07-25 15:02:13 11854 [ERROR] Plugin 'InnoDB' init function returned error.
2014-07-25 15:02:13 11854 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2014-07-25 15:02:13 11854 [ERROR] Unknown/unsupported storage engine: InnoDB
2014-07-25 15:02:13 11854 [ERROR] Aborting

2014-07-25 15:02:13 11854 [Note] Binlog end
2014-07-25 15:02:13 11854 [Note] Shutting down plugin 'partition'

This is ls /var/lib/mysql

backup-my.cnf
ibdata1
ib_logfile0
xtrabackup_binary
xtrabackup_binlog_info
xtrabackup_binlog_pos_innodb
xtrabackup_checkpoints
mysql
.... OTHER DATABASE FOLDERS .....

This is very strange, and it used to work perfectly fine when I was setting up 5.5 > 5.5 replication exactly the same was.

Why is it failing with 5.6 now?
What does it mean: [ERROR] InnoDB: Only one log file found"
Additionally on the master I can see ib_logfile0 and ib_logfile1 but Xtrabackup gives only ib_logfile0

I tried restarting the server.
I tried doing the backup/restore prcedure twice to make sure I didn't miss something.
I tried removing ib_logfile0 file as suggested somewhere on the official MySQL forum.
I tried explicitly enabling InnoDB in my.cnf with Innodb=ON

EDIT: Answering for questions:

The actual line I'm using for the backup?

ulimit -n 10240 && sudo -u mysql \
    /bin/sh -c "cd /var/lib/mysql && /usr/bin/innobackupex          \
      --user=${MYSQL_BACKUP_USER} --password=${MYSQL_BACKUP_PASS}   \
      --lock-wait-query-type=update --lock-wait-timeout=300         \
      --kill-long-query-type=select --kill-long-queries-timeout=10  \
      --rsync --slave-info --safe-slave-backup ${XB_TARGET_DIR}"

/usr/bin/innobackupex --version

InnoDB Backup Utility v1.5.1-xtrabackup;

cat xtrabackup_binary

xtrabackup_56

cat backup-my.cnf

# This MySQL options file was generated by innobackupex.

# The MySQL server
[mysqld]
innodb_checksum_algorithm=innodb
innodb_log_checksum_algorithm=innodb
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=50331648
innodb_fast_checksum=0
innodb_page_size=16384
innodb_log_block_size=512
innodb_undo_tablespaces=0

Do you have the log files outside of the datadir?

No, log files are within the datadir


texts: Sync local mysql database and live server database?

texts:
Someone answer the question on the below but he did make it clear,
I can't see code where he connect to Database
how to combine the file (Footer.php, Config.php, Sync.php, sync_server.php)

Please I need solution onhow to sync local mysql database and live server database?

We don't always have internet. I want the application to run offline in 2 branches and update online database when there is internet.

please help me.

how to sync local mysql database and live server database?


texts:
Hello I see similar questions has been ask before but I am having a bit of trouble with this issue, this is using excel 2013 data files and sql server 2012.

I have a foreach loop container

This for each has a enumerator of "Foreach file enumerator" for "*.xlsx"

On the Data Flow

In the ExcelFilePath I have a base file path

And in the Expression the one of the foreach loop, but if the base file is missing the package fail, unless it finds this starting "point". what I am doing wrong?.


texts: Big size of database logs SQL Server 2008

texts:
I have a database which is running under Microsoft SQL Server 2008. Now, I have seen that the log of the database (the .ldf file) is growing to big size. The database file (.mdf) has a size of 630MB and the log file has a size of 12GB. I wonder what the reason for this can be. Is there a tool which let me seeing into the log where I can see, what is the reason for this big size?

What can I do to prevent that the log is growing to this big size?


texts:
There are several Active Directory user groups in my SQL Server database environment. I changed the users' permissions on all databases on the server. Several users wanted me to give them the update permission back. I used a command like this:

Grant Update To [DomainGroup\User]

But this did not work. Now no-one can update except me. How can I resolve this?

Here is the result of the query;

  principal_id  sid name    type    usage
  35  0x0105000000000005150000008D555C5A9C99DF04AB5BAB3A43140000  NETONE\ibrahim.tackin   WINDOWS LOGIN   GRANT OR DENY
  0   0x01050000000000090400000083741B006749C04BA943C02702F2A762  public  ROLE    GRANT OR DENY
  5   0x010500000000000904000000FD051D9B2622FA4790703C7DB2C405A8  db_executor ROLE    GRANT OR DENY
  6   0x0105000000000005150000008D555C5A9C99DF04AB5BAB3AF2380000  NETONE\sql-ict-ad-rms   WINDOWS GROUP   GRANT OR DENY
  7   0x0105000000000005150000008D555C5A9C99DF04AB5BAB3A5B2B0000  NETONE\sql-ict-ad-rms-mod   WINDOWS GROUP   GRANT OR DENY
  28  0x0105000000000005150000008D555C5A9C99DF04AB5BAB3A292C0000  NETONE\SQLDDL   WINDOWS GROUP   GRANT OR DENY
  32  0x0105000000000005150000008D555C5A9C99DF04AB5BAB3A352A0000  NETONE\SQLQRY   WINDOWS GROUP   GRANT OR DENY
  33  0x0105000000000005150000008D555C5A9C99DF04AB5BAB3A5C410000  NETONE\SQLGelir-guvence WINDOWS GROUP   GRANT OR DENY
  34  0x0105000000000005150000008D555C5A9C99DF04AB5BAB3A5D410000  NETONE\SQLGelir-guvence-mod WINDOWS GROUP   GRANT OR DENY
  16384   0x01050000000000090400000000000000000000000000000000400000  db_owner    ROLE    GRANT OR DENY
  16390   0x01050000000000090400000000000000000000000000000006400000  db_datareader   ROLE    GRANT OR DENY
  16391   0x01050000000000090400000000000000000000000000000007400000  db_datawriter   ROLE    GRANT OR DENY


texts:
I'm new to Oracle DBs and I'm creating a table that contains a CLOB field for very large JSON Objects.

I'm requested to name the LOGSEGMENT object of the CLOB column but I haven't found how to do it. For now, I'm just creating a table that has a CLOB column and a constraint to check that the CLOB field is actually a json. How do I name the LOGSEGMENT object?


texts: How to delete a column named grave accent in MySQL

texts:
As title.

Every time when I try to execute the query:

ALTER TABLE tablename DROP COLUMN ```

I got syntax error, which is understandable.

So, what should I do to delete this column whose name is the symbol of grave accent?


texts: MySQL - Unable to login with password, but without password its working

texts:
I have a master-slave setup, Slave is not in read only mode.(we can do all writes).
I have created a user.

create user 'bhuvi'@'%' identified by 'bhuvi';
grant all privileges on *.* to 'bhuvi'@'localhost';
flush privileges;

But when I try to login, its showing

[root@1user]# mysql -u bhuvi -p
Enter password:
ERROR 1045 (28000): Access denied for user 'bhuvi'@'localhost' (using password: YES)
[root@1user]#

But I can able to login with -p

mysql -u bhuvi

I don't why its happening.
AndI tried to create users like `'user'@'localhost, this is working, But I don't know why the user has '%' is not working.

(Im trying access the user login on the same server where the MySQL is installed)


texts: Query Optimization - CTE Is Slow With OPENQUERY

texts:
I am running the below query that utilizes a CTE - the full recordset is roughly 10K records.  When I do a Select Top 1000 the query takes roughly 45 seconds to return the results.  I am querying a postgresql database from sql server 2008

Is there a quicker way of writing this query?

Select [Employee Name] = employeename,[Customer Number] = Customernumber
,AVG(quantityordered) As AverageQty,AVG(Price) As AveragePrice
,[Sale Date] = CAST(Saledate As Date)
FROM OPENQUERY(192.155.10.22,'with MaxNumber as
                      (
                        select max(cc2) as "MCN"
                        ,Customernumber
                        from employeeinfo
                        group by Customernumber
                      )
                      Select
                      c1.employeename,c1.Customernumber
                      ,c1.Saledate,ep.saledc1cription
                      ,eq.quantityordered,eq.price
                      FROM MaxNumber c2
                      INNER JOIN Customer c1
                      ON c2.Customernumber=c1.Customernumber
                      AND COALESCE(c2."MCN",0)=COALESCE(c1.cc2,0)
                      INNER JOIN orderpart ep
                      ON ep.Customer = c1.Customerid
                      INNER JOIN orderquantity eq
                      ON eq.employeename = ep.employeename
                      INNER JOIN employee cm
                      ON c1.employee = cm.employee')
WHERE CAST(Saledate As Date) >= '01/01/2017'
GROUP BY customername, Customernumber, Saledate

EDIT
There are no indexes on the tables, and I am unable to modify them to add any.  And this is the execution plan


texts: SQL Server, what is the optimal Processor & Core configuration for VMs?

texts:
In my day job I deal with alot of SQL Servers...and lately I've had a rash of clients running 16 cores but configured as 1 core per processor and their SQL Standard edition runs like a dog, as expected.

My question is this: Have you ever looked into what tends to be a better Processor-Core configuration? 1x8, 2x4, 4x4, 4x2, 8x1, etc. I know there are other aspects to this too around NUMA & 24+ cores on enterprise edition, increasing the CPU share in VMWare vs. increasing the CPU core count, etc.

I'm curious if you (or any of your fine friends) have looked into this and what the performance differences may look like as well as test results.

The software I work with typically has a minimum of 8 cores in SQL Server, some clients have 12 or 16, and a rare few outliers run even more.

Additional Details:
-We start Cost Threshold for Parallelism to 50 or 75 depending on the scenario.
-We change MAXDOP to 4 or 8 depending on the scenario.
-We have an army of other changes that we make to SQL Server for best practices (for us).  This question about Processor-Core configuration has never really been dug into that I can recall.


texts:
I have a field which stores the days of the week in binary representation. For example, Mon-Wed-Fri would be represented as b'0101010' with the 1st digit as Sunday and the last as Saturday.

What I want to achieve: If today is Monday, I want to check if the Monday bit is set to 1. I do that by doing (b'0101010' & b'0100000' > 0). But I am struggling in converting the current date into the corresponding weekday binary representation. How can I achieve so?


texts: How to get the corrupted table from a log file in hex?

texts:
I'm running a Magento 2 site, everyday we have a MySQL shutdown because of some corrupted data, we don't know the table causing the issue, this is what the error logs shows:

 Version: '5.7.26-0ubuntu0.18.04.1'  socket: '/tmp/tmp.c5OkKsNidi/mysqld.sock'  port: 0  (Ubuntu)
2019-05-19T06:17:30.001609Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190519  6:17:29
2019-05-19T06:17:37.299075Z 2 [ERROR] InnoDB: Database page corruption on disk or a failed file read of page [page id: space=28974, page number=1]. You may have to recover from a backup.
2019-05-19T06:17:37.299098Z 2 [Note] InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex asc                     u6              q.   ...   ' x         ' x   ' x      .... u6  ;
InnoDB: End of page dump

Any ideas? Doing a MySQL restart does the trick but I need to do it manually, we need to be sure the db is fixed, but we can't get the name of the table.


texts:
select parse_ident('abc,efi&xyz');

Will stay there for ever, not returning. But a similar command will finish.

select regexp_split_to_array('abc,efi&xyz', '[,&]');
 regexp_split_to_array
-----------------------
 {abc,efi,xyz}
(1 row)

Not sure what's wrong with my database.  I am running a vacuum full analyze at the moment. Not sure that cause the hang.


texts: SQL Server Database size unchanged after dropping File column

texts:
I have a database with a table that has about 10 000 records. One of the columns in this table is a VARBINERY(MAX) that is used to store images. A backup of this database is about 1.2GB. It's size as shown in SQL Server Management Studio is about 3.5GB.

Working on the assumption that the images make up the bulk of this size, I dropped the column. This did not alter the database's size at all. The backup and database size is still the same. How is this possible?


texts: How to Query Which Table Is Inserted Mostly In SQL Server?

texts:
I have a database and there is not any trigger which follows the transactions in said database.

Now, I have to make a report that shows which table has mostly inserted so far. How can I do that?


texts: processlist showing '%' as host from trigger

texts:
I'm trying to populate a certain column in a table with the host/IP of the caller. I have an insert trigger on the table which sets the column like so:

CREATE TRIGGER access_insert_trg BEFORE INSERT ON access
FOR EACH ROW
set NEW.hostname = (select SUBSTRING_INDEX(host,':',1)
                    from information_schema.processlist
                    WHERE ID=connection_id())
;

I end up getting % as the hostname even though when I manually run the same query on the processlist table I get an actual hostname. I've checked the connection_id() value and it's correctly the id of a row in processlist with a real hostname:port value in the host column.

I can't figure out how or why it's being translated to a generic %:<port> value. Any ideas on what's going on & how to fix it?

I'm using MySQL 5.1.57 if that changes anything.


texts: What is the difference between TABLESAMPLE(10) and TABLESAMPLE(10 PERCENT) in SQL SERVER 2012

texts:
My understanding of the use of TABLESAMPLE is that it returns the top n percent of rows in a result set where TABLESAMPLE(n)

So I wrote the following queries to just check (as I'm learning T-SQL)

SELECT FirstName,
       MiddleName,
       LastName
FROM Person.Person TABLESAMPLE(10)

SELECT FirstName,
       MiddleName,
       LastName
FROM Person.Person TABLESAMPLE(10 PERCENT)

Each query returns a different number of rows every time I execute it.

So I obviously don't understand TABLESAMPLE.  Could somebody explain?


texts: Question about Dumps in Oracle (REMAP_SCHEMA)

texts:
To export a dumpfile, I can:

EXPDP system/Password@TNS
DUMPFILE=FileName.dmp
Schemas=TheSchemaIWantToDump
DIRECTORY=dumpdir

So, let's say I want to restore this DMP file, but with a different name (I'm a SQL Server Dba, and I'm still learning some Oracle). I would do this:

1)create a new user

2)GRANT CONNECT to this user

3)

IMPDP System/Password@TNS
dumpfile=ThefileName.dmp
REMAP_SCHEMA=OldUser:NewUser
directory=dumpdir

My question is about REMAP_SCHEMA.When I'm restoring schemas inside my own server, I know the OldUser , so I can fill it, and then :NewUser.

But if I'm sending this dump file to our client. Should I mention this OldSchema?  They don't know the schema's name I'm using before the dump. Because on SQL Server I can RESTORE DATABASE , then name with the name a want FROM DISK....

How can I restore a schema with a different name?

Thanks.


texts: How do I calculate COUNT(*) for a range without needing to use multiple queries in mysql

texts:
I am using MySQL and I need help using COUNT(*) for a range of values within a table.

I have a table named PERSON (In the live system I expect several hundred thousand records or more).

Name    Result
a       100
b       130.45
c       182.96
d       65.45
e       199
f       245

I need to query the table to find out how many records belong to a given range. For example, how many persons belong to 0-50 range. Range values are dynamic. So the expected result is something like:

Range       Count
0-50            0
51-100          2
101-150         1
151-200         2
201-250         1

Off course I can do:

SELECT COUNT(*) FROM PERSON WHERE RESULT <= 50
SELECT COUNT(*) FROM PERSON WHERE RESULT > 50 AND RESULT <= 100

and so on for all ranges...

However, there must be a better, more efficient way to do this?


texts: What options are there for security on shared data sources for SSRS?

texts:
While trying to automate deployments that include SSRS reports and SSRS shared data sources/data sets we have been trying to configure security appropriately. Particularly the problem is with shared data sources/data sets and automating their deployment across all environments. The only solution that has come up is to create a single SQL Server Login and use the same username password as the data source for the shared data sources in SSRS. The account would be the same from Dev to Prod.

The newly created SQL Login will need EXECUTE on stored procedures in all these environments. I am against having one SQL login with EXECUTE permissions in ALL environments.

Prior to a more automated deployment, we would have an account in all 3 environments but a different password in Production. The DBA team would manually change the shared data source/data set's password after deployment. The goal is to move away from the manually intervention while also not creating a large security hole.

SSRS config file is encrypted nonetheless I would like to not create a SQL Login with so many privileges. Are there any other options?


texts: Source code of View of Linked server sp_HelpText

texts: How to use sp_helptext for view on the linked server ?


texts:
Posted the same question on SO, but did not get answer.

As per Andrey Nikolov comment, tried with Trigram, but the table name and column(more than 50 columns) are dynamic i mean will get from front end.

Question:

I have the following sample data for understanding the requirement.

Table:

create table ft_test
(
    col1 int identity(1,1),
    col2 varchar(max)
);

insert into ft_test values('John A Henry');
insert into ft_test values('Dsouza mak Dee');
insert into ft_test values('Smith Ashla Don');

Creation of FULLTEXT INDEX:

CREATE UNIQUE INDEX UIX_test_Col1 ON ft_test(col1);

CREATE FULLTEXT CATALOG ftcat
WITH ACCENT_SENSITIVITY = OFF

CREATE FULLTEXT INDEX ON ft_test
(col2 LANGUAGE 1033)
KEY INDEX UIX_test_Col1
ON ftcat
WITH STOPLIST = SYSTEM

Notes:

  Search for the given pattern in the given column of the table.
  I have more than 200 millions of records in the table.
  Created FULLTEXT index on required column.

Looking for best performing search query.

Givens:

  Pattern: oh
  Column to  Search: col2

Expected Output:

col1    col2
----------------------
1       John A Henry

Try:

Using FULLTEXT Index: CONTAINS

SELECT col1, col2
FROM ft_test1
WHERE CONTAINS(col2, '"*oh*"')

No output.

Using FULLTEXT Index: FREETEXT

SELECT col1, col2
FROM ft_test1
WHERE FREETEXT(col2, '"*oh*"')

No output.

Using Pattern Index: PATINDEX

SELECT col1, col2
FROM ft_test1
WHERE PATINDEX('%oh%',col2)>0

Got an output.

Execution Plan: Table Scan

Using character expression: CHARINDEX

SELECT col1, col2
FROM ft_test1
WHERE CHARINDEX('oh',col2)>0

Got an output.

Execution Plan: Table Scan

Using LIKE.

SELECT col1, col2
FROM ft_test1
WHERE col2 LIKE '%oh%'

Got an output.

Execution Plan: Table Scan


texts:
I was wondering if there are any good solutions for recording data in a NoSQL  database and then converting them over to an RDBMS?

For example, if you wanted to capture some data quickly, like session logs, but then you want to be able to create reports on them later.

My favorite database is Postgres, so if your answer is relevant in Postgres that would be great.


texts: Allow self-referential foreign keys to be null from an alter-table

texts:
I am trying to establish a hierarchical relationship between rows in an existing table.  There are two fields of interest: a not-nullable primary key field id, and a second nullable field parentId.

Both these fields are pre-existing, and the normal case is that parentId will be null. This seems to be a normal use case, and in fact we have some old tables using the same pattern.

I am trying to use fluentMigrator to add a foreign key constraint after the fact on these rows:

Create.ForeignKey("C", "ParentId", "C", "CId").OnUpdate(Rule.None);

It is failing with the error:

  The ALTER TABLE statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_C_ParentId". The conflict occurred in database "R", table "dbo.C", column 'CId'.

This is not a problem with illegal values, as I did a bulk update on the test system, and set all C.ParentId = null, and am still getting the same error.

Thoughts?


texts: Does UPDATE INDEXES work for local indexes?

texts:
I have two tables in Oracle 11.2. First is partitioned and has local index (non-prefixed):

CREATE TABLE table1 (col1 INT, col2 INT)
PARTITION BY LIST(col1)
(
    PARTITION p1 VALUES(1),
    PARTITION p2 VALUES(2)
);
CREATE INDEX table1_index on table1(col2) LOCAL;
INSERT INTO table1 VALUES(1, 1);
INSERT INTO table1 VALUES(2, 1);

Second table is similar but without partitions and index:

CREATE TABLE table2(col1 INT, col2 INT);
INSERT INTO table2 VALUES(1, 2);

When I exchange one of partition from table1 with table2 then index for this partition will be unusable. To avoid this I can use UPDATE INDEXES, so I do:

ALTER TABLE table1 EXCHANGE PARTITION p1
WITH TABLE table2 WITH VALIDATION UPDATE INDEXES;

But UPDATE INDEXES seem to doesn't work, because when I check status of index for this partition it's unusable.

SELECT partition_name, status FROM user_ind_partitions
WHERE index_name = 'TABLE1_INDEX';

PARTITION_NAME                 STATUS
------------------------------ --------
P1                             UNUSABLE
P2                             USABLE

For GLOBAL index it works as expected.

The question is: does UPDATE INDEXES work for LOCAL indexes when using EXCHANGE PARTITION?


texts: timestamp with timezone issue with TZR vs TZH:TZM

texts:
I have a Hibernate with Oracle JDBC based application inserting/updating into the following column;

COLUMN_A TIMESTAMP WITH TIME ZONE DEFAULT systimestamp NOT NULL

but while it automatically appends timezone, it uses TZR, e.g. UTC, and weird thing with this data type in Oracle is that, it retains the format of timezone from insertion when we select it without any formatting, explained here

With this said, we are utilizing Golden Gate that is replicating this data to MongoDB, but afaik it requires these timestamps to contain only TZH:TZM formatting, so I have this issue where one side blocked by Oracle JDBC, where insertion is done with TZR, and one side blocked by Golden Gate where data is expected with TZH:TZM

Are there any way to handle this issue? Can I not forbid a certain formatting for TIMESTAMP WITH TIME ZONE ? Using NLS_TIMESTAMP_TZ_FORMAT works for SELECT formatting, but it is not usable for Golden Gate I was told. Also for INSERT case I was able to utilize default value of the column, and using systimestamp does insert with TZH:TZM, but for UPDATE case, I am unable to achieve this. Totally stuck!

I have this question with focus on application side of this issue. I am wondering if there is anything that can be done in DB side?


texts: Restoring a differential backup in SQL server 2005

texts:
I have created a full back up of my database by executing the line below in my application:

Dim Query As String = "backup database Ari to disk='G:\Documents and Settings\i\Desktop\BKAR\" & MyTime.getFullCurrentDate.Replace("/", "") & ".Bak'"

The *.bak file created successfully.

Then I restored it on another Database called test and it was ok. Then I created a differential backup by means of the following code:

Dim Query As String = "BACKUP DATABASE Ari TO DISK='G:\Documents and Settings\i\Desktop\BKAR\" & MyTime.getFullCurrentDate.Replace("/", "") & ".Bak'" & "WITH DIFFERENTIAL"

and it created a *.bak file ,but when I try to restore it on the database I mentioned as test ,I receive the following error:

Please help me solve this problem.


texts: How to avoid warning with file system cache in mongodb?

texts:
OS Windows 10 x64. Version of MongoDB 3.6.5

When I'm starting mongo process, I'm always getting:

MongoDB shell version v3.6.5 connecting to: mongodb://127.0.0.1:27017/
MongoDB server version: 3.6.5 Server has startup warnings: 2018-07-02T20:56:09.470+0300 I CONTROL  [initandlisten] 2018-07-02T20:56:09.470+0300 I CONTROL  [initandlisten] ** WARNING: The file system cache of this machine is configured to be greater than 40% of the total memory. This can lead to increased memory pressure and poor performance. 2018-07-02T20:56:09.470+0300 I CONTROL  [initandlisten] See http://dochub.mongodb.org/core/wt-windows-system file-cache 2018-07-02T20:56:09.470+0300 I CONTROL  [initandlisten]

Probably, same issue was here, but there isn't normal solution. I suppose that I need to add something in config file, for example - https://docs.mongodb.com/manual/reference/configuration-options/#storage-inmemory-options.

Now I have in config:

systemLog:
    destination: file
    logAppend: true
    path: c:\data\log\mongod.log
storage:
    dbPath: c:\data\db
    journal:
        enabled: true
replication:
   replSetName: "rs0"
net:
   bindIp: 127.0.0.1
   port: 27017
security:
     authorization: enabled

If I'm trying to add snippet:

inMemory:
      engineConfig:
         inMemorySizeGB: 10

The whole config looks like:

systemLog:
    destination: file
    logAppend: true
    path: c:\data\log\mongod.log
storage:
    dbPath: c:\data\db
    inMemory:
        engineConfig:
                inMemorySizeGB: 10
    journal:
        enabled: true
replication:
   replSetName: "rs0"
net:
   bindIp: 127.0.0.1
   port: 27017
security:
     authorization: enabled

Then getting the error Unrecognized option: storage.inMemory.engineConfig.inMemorySizeGB

P.S. I didn't use tab.

Any ideas how to avoid warning? Thanks.


texts:
My first attempt to use PostgreSQL:

I have the elephant icon on the top right: running on port 5432.

I want to use LibreOffice as an interface:

Connect to an existing database: PostgreSQL
Datasource URL: ???

What should I put here? The database will be on the same computer where LibreOffice is running. Tried with localhost:5432 with no success.
By the way, I did not create the database, yet. Can I do it from LibreOffice?

Thank you.

Platform:
  Mac OSX 10.7 - PostgreSQL 9.2.2 - LibreOffice 4.0


texts: Schema to generalise potentially parametrised status

texts:
Let’s say I have some entity:
create table entities (
  id serial primary key,
  foo text
);

...and I want to assign it some state, which has N possible values. Some of said states also come with a parameter. For example:

foo
bar
quux, 10
quux, 20
quux, 100

I original set this up as a separate table, with the states and parameters populated by the application at runtime. Then this is just simply referenced in the status table:
create table status (
  entity integer references entities(id),
  state integer references states(id),
  primary key (entity, state)
);

The problem with this is that the semantics of the states are now lost, so it’s up to the application to understand things.
As an alternative, I created an enumeration type of the states and an optional parameter key:
create table status (
  id serial primary key,
  entity integer references entities(id),
  state state_enum not null,
  param integer default null,
  unique (entity, state),
  unique (entity, state, param)
);

I haven’t written it out here, but I also put an elaborate check constraint on param to ensure it’s only non-null when the state is “quux”.
(Rather than using an enum, I could have a lookup table with the states as hard-codes records; not the parameters as they have application-specific meaning. Either way, it’s equivalent.)
Now the states are explicitly enumerated by the schema, one doesn’t have to rely on the application to define semantics. However, it’s really messy in my opinion.
Is there a more elegant way to design this?
(If it makes a difference, in terms of features, I’m using PostgreSQL.)


texts: How should I store multiple relationships (many posts to many categories)?

texts:
I have posts stored in a posts table and I want each post to have multiple categories, what is the most efficient way to store the categories and their relationships ?

I thought of it this way first:

Categories Table:

+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int(11)       | NO   | PRI | NULL    | auto_increment |
| name       | varchar(32)   | NO   |     | NULL    |                |
| parent     | int(11)       | NO   |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

Relationships Table:

+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| post_id    | int(11)       | NO   |     | NULL    |                |
| cat_id     | int(11)       | NO   |     | NULL    |                |
| order      | int(11)       | NO   |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

Using this structure I will have lots of rows inside the relationships table, for example if a post has 4 categories I will have 4 rows only for this post, of course I won't have that much categories related to the post but I want to implement the same structure for tags too.

Am I doing it right ? is there a better structure to use ?


texts: Incremental column depening on column value

texts:
I would like to have a column which has an incremental integer depending on the column value after sorting the table.

E.g. after sorting by state:

Nr  | State      | Counter
---------------------------
234 | Alberta    | 1
534 | Alberta    | 2
111 | Alberta    | 3
123 | California | 1
876 | New York   | 1
101 | New York   | 2
542 | New York   | 3
533 | New York   | 4

Is this even possible with MySQL?


texts:
For example, let's say I'm building a database for an auction system, in which various users can partake in both selling and bidding on different items, I'd want for an "Auction" instance to also store the highest bidder (Which should obviously be updated each and every time someone bids way higher than the previous highest bid).

How would I achieve something like that?


texts: SQL Server: query for getting TOP X groups of records which have highest subsequent values

texts:
I'm querying each SQL Server DB CPU usage (based on query from here  -- Get CPU utilization by database (Query 29) (CPU Usage by Database)) every 60 seconds and storing this information in dedicated table for that purpose with columns:

id, dbname, date, usage

Now I'm interested in querying this DB for databases which are using most CPU usage but only if DB usage X subsequent reads where higher than Y (so I'm not interested in DB which had CPU usage spikes).

Example:

DB1:

time1 - 0
time2 - 35
time3 - 0
time4 - 1

DB2:

time1 - 9
time2 - 8
time3 - 7
time4 - 11

In this example I'm not interested in DB1 because it had only one CPU spike and it's average CPU usage is low. DB2 on the other hand has average higher CPU usage in subsequent reads and this type of DB I'm trying to find in my usage table.

Thank you

EDIT: My mistake. Version 2012


texts: SP returns positive RecordsAffected value even when TSQL statement rolled back

texts:
I'm attempting to build an all-or-nothing TSQL statement, something like this:

BEGIN TRANSACTION
BEGIN TRY
    -- Loop over a TVP passed as a param
    DECLARE i CURSOR LOCAL FAST_FORWARD FOR SELECT [stmt] FROM TVP param;
    OPEN i;
    FETCH NEXT FROM i INTO @iDB, @iTBL, @iATTRS, @iVALS;
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @SQL = next row of TVP
            EXECUTE sys.sp_executesql @SQL
        END
    CLOSE i;
    DEALLOCATE i;
    COMMIT TRANSACTION
END TRY

BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
ROLLBACK TRANSACTION
END CATCH

The SP generally works as intended, in that the transactions are all or nothing, and running the SP from SSMS returns the error as selected in the catch statement.

But on the C# side, the dbResponse.RecordsAffected value is always incremented.

Why is this? Also, how can I get this SP to return the correct rows effected value? (i.e. the number of rows that were effected by the SP on success)

Is it possible to reset this value in the CATCH statement?


texts: GROUP BY with date ranges when values change but then change back

texts:
Consider a historical table where a value was changed and then changed back:

value | date
  3   | 1/1/2019
  3   | 1/2/2019
  3   | 1/3/2019
  4   | 1/4/2019
  4   | 1/5/2019
  3   | 1/6/2019
  3   | 1/7/2019
  3   | 1/8/2019
  3   | 1/9/2019
  2   | 1/10/2019
  2   | 1/11/2019
  3   | 1/12/2019
  3   | 1/13/2019
  3   | 1/14/2019

How do I write a query that shows min and max dates that still shows the value change? I'm looking for:

value | min(date) | max(date)  | days_at_this_val
  3   | 1/1/2019  | 1/3/2019   |        3
  4   | 1/4/2019  | 1/5/2019   |        2
  3   | 1/6/2019  | 1/9/2019   |        4
  2   | 1/10/2019 | 1/11/2019  |        2
  3   | 1/12/2019 | 1/14/2019  |        3

GROUP BY obviously lumps all the 3s together, and RANK and ROW_NUMBER resume counting 3s where they left off. What's the best way to do this?


texts: Subtracting sum of previous rows' data from current row's data in SQL

texts:
I want to create a table or view in SSMS which should has a column called qoh. In that column should be calculate sum values of the column rel_qty.
i.e.

The first data of qoh is the same of the first row's value of
rel_qty.
Second value of qoh should be sum of first and second rows' value of
rel_qty.
Third value of qoh  should be sum of first, second and third rows' value of rel_qty.

is there any possibilities to do this?

Here is my query. but this query satisfied only first and second rows not more that that,

SELECT  a.id, a.tot_qty, a.rel_qty,
    (a.tot_qty -
        COALESCE (a.rel_qty +
            (SELECT b.rel_qty
        FROM    dbo.foo AS b
        WHERE   (b.id = a.id - 1)),
        a.rel_qty)
    ) AS qoh
FROM    dbo.foo AS a
ORDER BY    a.id

And if my id skip one value this query failed to do the calculation. How can I fix this?

here is screenshot of my result


texts: How to check if a query (CRUD) was made on a database

texts:
I'm sorry for the title but I have a problem which I can't sum up in a sentence.
I have a database with 100 tables and probably a thousand columns. Now, every time something changes in the database, for example:

New table is created
New column is created (Insert)
Column is updated (Update)
Column is deleted (Deleted)
And a column is read (Read)

I would like to know.
From what I have read there are 3 options but I have an issue with all three:

SQL Profiler (soon to be obsolete)
Extended Events (too complex for me)
Open MSSQL query field and go crazy

For now, I am trying to go with the third. I am hoping to use sys.object or sys.dm_db_index_usage_stats and any other table to get what I want.
So, my question is: Do you think there is a better alternative or not?


texts: Jet SQL switch — Fill in 'n/a' for blank cells

texts:
My query isn't working as I expect it too.
I am trying to create best_id and where blanks/null cells are set to 'n/a' .

Also I use Microsoft.ACE.OLEDB.12.0 connection via excel to do an internal query within my workbook.

Can anyone help with this?

switch(
bbg_id.[BbgType_MarketSectorDes] not like '[#]N/A%',master.[Identifier],
bbg_cusip.[BbgType_MarketSectorDes] not like '[#]N/A%',replace(master.[Identifier],' cusip','') & ' cusip',
bbg_isin.[BbgType_MarketSectorDes] not like '[#]N/A%',replace(master.[Identifier],' isin','')  & ' isin',
master.[Identifier] is not null, master.[Identifier],
true,'n/a'
) as best_id,

I've tried changing the wildcard to * but can't seem to get the cells to show n/a for blank/null ones

switch(bbg_id.[BbgType_MarketSectorDes] not like "[#]N/A*", master.[Identifier],
       bbg_cusip.[BbgType_MarketSectorDes] not like "[#]N/A*", replace(master[Identifier], " cusip", "") & " cusip",
       bbg_isin.[BbgType_MarketSectorDes] not like "[#]N/A*", replace(master.[Identifier], " isin", "")  & " isin",
       master.[Identifier] is not null, master.[Identifier],
       true, "n/a"
     ) as best_id,


texts: Any advantages if we use multiple database instead of only one in PostgreSQL?

texts:
I would like to get the suggestion for my usecase.

We have a database in our production machine.We are doing continuous writes in that database since we are doing crawling,parsing and it is being inserted into database as a streamer.We are running some crons on the database.That database gets some reads for the web application also.

I got the requirement just to store the html document into one another table for the cross verification.So,the plan was to store the pages into one new table which was created in the same database.Here we need to run one more cron on the new table to find the page number and update into that table.

We got one more idea like to create the table and running cron in new database instead of the old database by considering performance improvement.

Will it be any difference to create a new database and update into it rather than using old database to update the data and running a cron?

we are using amazon ec2 medium instance with Ubuntu operating system also PostgreSQL 8.4.

your thoughts please?


texts:
I've encountered a deadlock on SQL Server 2014, and created a deadlock report using extended events. Below is an excerpt from the report.

What does it mean for objectlock(objid="554979041") to have multiple owners (namely, process14f3f2108 and processf32b5848 (twice)). I understand how a lock may have multiple waiters, but what does it mean to have multiple owners? I thought that a lock could only be owned by a single process, and that all other processes interested in the lock would have to wait for the lock. What am I missing?

<deadlock>

    ... content deleted ...

  <resource-list>
      <keylock hobtid="72057633537982464" dbid="15" objectname="BasketHeader" indexname="UX_BasketHeader_BasketID_Account" id="lock2d9b59f80" mode="X" associatedObjectId="72057633537982464">
        <owner-list>
          <owner id="process203dad848" mode="X" />
        </owner-list>
        <waiter-list>
          <waiter id="process14f3f2108" mode="RangeS-U" requestType="wait" />
        </waiter-list>
      </keylock>
      <objectlock lockPartition="0" objid="554979041" subresource="FULL" dbid="15" objectname="BasketItem" id="lock1e64cbc80" mode="IX" associatedObjectId="554979041">
        <owner-list>
          <owner id="process14f3f2108" mode="IX" />                     !!!     OWNER 1
        </owner-list>
        <waiter-list>
          <waiter id="processf32b5848" mode="X" requestType="convert" />
        </waiter-list>
      </objectlock>
      <objectlock lockPartition="0" objid="554979041" subresource="FULL" dbid="15" objectname="BasketItem" id="lock1e64cbc80" mode="IX" associatedObjectId="554979041">
        <owner-list>
          <owner id="processf32b5848" mode="IX" />                      !!!     OWNER 2
          <owner id="processf32b5848" mode="X" requestType="convert" /> !!!     OWNER 3
        </owner-list>
        <waiter-list>
          <waiter id="process203dad848" mode="IX" requestType="wait" />
        </waiter-list>
      </objectlock>
    </resource-list>
</deadlock>


texts: Replacement for Nested SELECT statements for every row in SELECT clause

texts:
I've created main SELECT statement like below. For every row in Base table (base on IDKey I've added columns with values from result of INNER JOIN of few tables.

SELECT
  IDKey,
  Name,
  (select Amount from Table1 INNER JOIN Table2 ... where ID=IDKey) AS AmountTypeX,
  (select Amount from Table1 INNER JOIN Table2 ... where ID=IDKey) AS AmountTypeY
FROM BaseTable

BaseTable:

IDKey Name
----------
1     Ian
2     John
3     Ann
...

INNER JOIN of few tables Table1, Table2... :

ID Amount Type
--------------
1  100    X
1  200    Y
2  300    X
2  400    Y
3  500    X
3  600    Y
...

Result looks like:

IDKey  Name  AmountTypeX  AmountTypeY
-------------------------------------
1      Ian   100          200
2      John  300          400
3      Ann   500          600
...

Do you have any suggestion how to avoid this nested SELECT statements in the main query at the begining and made this query simpler and more effective ?

I wast thinking about PIVOTING of the INNER JOIN result to look like below (all data for particular ID in one row) and then JOIN with main query but Have no idea how to do this and is it possible.

ID  X   Y
-----------
1   100 200
2   300 400
3   500 600
...


texts:
I have the following queries:

SELECT to_date(to_char(to_date('01-FEB-1949'))) FROM DUAL;
/*this returns 2/1/2049. */

SELECT to_date(to_char(to_date('01-FEB-1949'),'dd-MON-yyyy')) FROM DUAL;
/*this returns 2/1/1949.*/

Why does the first one returns the year 2049 instead of 1949?

By Googling I have found that I can "force" the client date format to be the one desire by changing the keyon the registry:

KEY_OraClient11g_home1
NLS_DATE_FORMAT : YYYY/MM/DD

Thanks in advance!


texts: Postgresql Two-phase Commit Transaction Identifier

texts:
Is it possible to perform a two-phase commit in Postgresql when the databases exist on the same server?

As a simple test, I would connect to the first database and run:

BEGIN;
PREPARE TRANSACTION 'test';

and in a new terminal tab I would connect to the second database and run the same commands. This outputs the error:

  ERROR:  transaction identifier "test" is already in use


texts: Ideal way to start 'Database Server' in PostgreSQL 11

texts:
PostgreSQL version : 11.2
OS : RHEL 7.5

I am new to PostgreSQL.
I would like to know which is the ideal way to start 'Database server' . In Oracle world, we call this starting the instance.

Should I be using Option A or Option B ?

Option A. As root user , run

# systemctl start postgresql-11

Option B. As postgres user, run

$ postgres -D /var/lib/pgsql/11/data

NB: For Option B, PostgreSQL 11's official doc mentions a different directory (/usr/local/pgsql/data) as shown below . Maybe the doc not updated.

$ postgres -D /usr/local/pgsql/data

https://www.postgresql.org/docs/11/server-start.html


texts: SQL Server Installation in a 2 node cluster

texts:
I'm installing SQL Server 2014 in a cluster for the first time, and am running into an issue.

Environment:

x2 Windows Server 2008 R2 - with a load balancer in front

Steps so far:

Have the IP's
Have the Service accounts
Fixed all validation errors network binding etc.

Issue

There is already a service that exists, set up by infrastructure team using the DNS name and IP address I need to use if I'm following the SQL Server Cluster installation wizard.

Questions

How can I install SQL Server on this already existing service?
Going through the installation wizard, do I need to take the existing server and IP offline(indicated by red arrows above) I need to use the name indicated under services and applications in the wizard, but it already exists. I have the same issue for the cluster configuration step the IP address is in use.

would it be easier to just delete the existing service and let the wizard recreate it for me?


texts: How to verify that postgres 9.3 db has no corruption?

texts:
I have a postgres 9.3 db running on ubuntu server.
About a month ago we've had some problems with the hardware on the server which was resolved by our VPS hosting company.

The problem was resolved fairly quickly and everything seemed to be working fine.
We have backups running using barman that is on a different server - and backups and restores were working (I checked).

The first sign of data corruption was few days ago: I decided to do a full pg_dump on our DB as I do every once in a while, and it failed (invalid page header in block...) - The data seemed to have been corrupted a long time ago - around the time of the hardware problem (that was the date on the corrupted record). I resorted to finding the corrupted record and I deleted it and restored it manually.

After that I was able to do a full pg_dump.

In order to check for additional corruption - I've set up different db server from a backup and run pg_repack on all the tables to verify that I'm able to rebuild all the indexes and tables.

My questions are:
1. How can I be certain that I don't have any additional corruption in my DB?
   2. What can I do to periodically check my data integrity?
3. What else can I do to verify the integrity of our DB besides dumping the whole DB and re-indexing it (which I already did)?

P.S - I don't have block checksums enabled.


texts: MySQL Workbench EER Diagram and ENUM data type

texts:
I am creating EER model diagram using MySQL Workbench (version : 5.2.47)

The version for MySQL, I am using is 5.5.35-0ubuntu0.13.10.2.

I have created a schema with 'Default Collation' set to 'utf8 - utf8_general_ci'.

I have created a table in EER diagram.

  My basis goal is to add a column with ENUM data type.

But when I set the data type for my column to ENUM('A', 'B') and move ahead (by clicking enter key or tab key), the datatype for that column is replaced with last datatype (in my case VARCHAR(45) as its default for each column we create).

Can anybody help me to sort out this issue ?
Is there any issue in my configuration or method I am using ?


texts: How to capture missing information in SQL Server tables

texts:
I have a few tables with nullable columns in a SQL Server database. Those nullable attributes are either not supplied at the time of row creation or they are not applicable in some cases.

I realized that I cannot use the same NULL value to mean 'Not Supplied' in some cases and 'Not Applicable' in other cases. These optional columns are of type int, datetime, and string. Even if I mark string columns as mandatory (NOT NULL) and set the attributes to either 'Not Supplied' and 'Not applicable' as appropriate, what can I do for int and datetime columns? I read chapter 23 from the book:

Database Explorations: Essays on The Third Manifesto by C.J. Data and Hugh Darwen

Looks like the column Status only has numeric values but since the authors have set 'n/a' and 'd/k' (don't know), not sure if they created Status as a string column. Is it a good idea to create all the optional columns as string columns and capture the reasons in the attributes when they don't have a value?


texts:
I have a query in mysql as follows.

SELECT DISTINCT `tableA`.`ColA`, `tableA`.`ColB`, `tableB`.`ColA`, ...
FROM `tableA`
LEFT JOIN `tableB` ON `tableA`.`colC` = `tableB`.`colA`
WHERE <whereconditions>
ORDER BY `tableA`.`ColA` DESC `tableB`.`ColA` ASC
LIMIT 0,20

Now executing the query takes approximately 13 seconds due to the size of the respective tables. However this query is a lot faster (i.e. 2 seconds)

SELECT `tableA`.`ColA`, `tableA`.`ColB`, `tableB`.`ColA`, ...
FROM `tableA`
LEFT JOIN `tableB` ON `tableA`.`colC` = `tableB`.`colA`
WHERE <whereconditions>
ORDER BY `tableA`.`ColA` DESC `tableB`.`ColA` ASC
LIMIT 0,20

I'm aware that this may result in non-distinct rows but I'm not sure if this situation has been taken care of by the php code on the front end. So I actually want to compare the data of the two result sets both of which result in approximately 83,000 rows if not limited.

Can anyone think of a way to do this in SQL instead of doing it via a php script. I've compared the first 100 rows by eye without any issues but as we all know it only takes one exception at the 101st row to mess everything up. So I need a query that I can execute a few times every so often to see if there are rows that are in one result set that are not in the other.


texts: MySQL can't connect as user with password locally

texts:
I am running into a strange authentification issue.

Users can't connect to MySQL locally using a password, but they can without one.

mysql -h localhost -u someuser -psomepassword

won't work

but

mysql -u someuser -h localhost

does

Those same users can connect remotely to this machine using their passwords.

mysql -u someuser -h someserver -psomepassword

Any thoughts as to why passwords can't be used locally?

Notes :

MySQL's logs are not providing anything useful
Ubuntu 12.04.3 LTS (GNU/Linux 3.5.0-45-generic x86_64)
mysql  Ver 14.14 Distrib 5.5.35
root can connect with password remotely or locally

Update 08/10 9:39

+-----------------+----------------+
| user()          | current_user() |
+-----------------+----------------+
| Guest@localhost | @localhost     |
+-----------------+----------------+


texts: Adding time to an event that runs on a weekly basis

texts:
I have this event that runs every Thursday

CREATE EVENT le_drop_database
ON SCHEDULE
  EVERY 1 WEEK
STARTS CURRENT_DATE + INTERVAL 3 - WEEKDAY(CURRENT_DATE) DAY
 DO BEGIN
  drop database carbon;
    END */$$
DELIMITER ;

I was wondering how i would add some parameter that indicates the time this event should run on Thursday for example run at 11:00 a.m


texts: Adding group rights in db2 for a stored procedure

texts:
I have a list of user accounts on AIX server, where the db2 database resides in. The user accounts all belong to a specific group which I can confirm from aix shell.

User accounts work in DB2. However, I cannot seem to be able to add execution rights to the group so I wouldn't have to grant execution rights to individual user accounts.

I have tried this:

GRANT EXECUTE ON PROCEDURE MYSCHEMA.MYPROCEDURE TO GROUP MYGROUP WITH GRANT OPTION;

using a user that has created the stored procedures, and it succeeds. However, when trying to execute that procedure using another account that belongs to that group in aix server, I get

"useraccounthere" does not have the required authorization or privilege to perform
operation "EXECUTE" on object "MYSCHEMA.MYPROCEDURE".. SQLCODE=-551, SQLSTATE=42501,
DRIVER=4.14.113

Any ideas what could be wrong, or what I could check?


texts: What does the Perfmon counter "Suboptimal plans/sec" actually measure?

texts:
In SQL Server, what does the Perfmon counter "Workload Group Stats:Suboptimal plans/sec" measure?

It's one of the Resource Governor counters, so it's measured at the workload group (which you have whether you configure RG or not.)

I'm not asking what makes a good plan or a bad plan, but what specifically does that counter measure, and where do you go to find the exact corresponding plans? For example, I can't find anything in Extended Events that would seem to match to a query triggering the "Suboptimal plans/sec" counter.


texts:
I'm new to MySQL database. I want to store emojis in my database. I already have data in my database, and I don't want to lose it.
I don't know the proper way of storing the utf8mb4 character-set using the MySQL console. Is there any way of doing that using mysql-administrator?
mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+-----------------------+
| Variable_name           | Value                 |
+-------------------------+-----------------------+
| innodb_version          | 5.7.33                |
| protocol_version        | 10                    |
| slave_type_conversions  |                       |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2 |
| version                 | 5.7.33-log            |

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR
Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8               |
| character_set_connection | utf8               |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8               |
| character_set_server     | utf8               |
| character_set_system     | utf8               |
| collation_connection     | utf8_general_ci    |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8_general_ci    |
+--------------------------+--------------------+
10 rows in set (0.01 sec)

I changed my.cnf file to
[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

It shows no error, but neither does it store the emojis - when enter emojis it shows as ? this question mark.
I convert the utf-8 encoding to utf8mb4_general_ci:
|--------------------------+--------------------|
| character_set_system     | utf8               |
|--------------------------+--------------------|
| collation_connection     | utf8mb4_general_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8_general_ci    |
|--------------------------+--------------------|


texts: The call to LOADLIBRARY for the XA resource manager DLL failed (PostgreSQL & SQL Server)

texts:
I am attempting to setup a Linked Server from MS SQL Server 2012 to PostgreSQL 9.3 via Linked Servers & ODBC driver from PostgreSQL. Everything works, until a given query invokes MSDTC, at which point I get an error like this on the SQL Server machine, and the query utterly fails:

> The XA Transaction Manager attempted to load the XA resource manager
> DLL. The call to LOADLIBRARY for the XA resource manager DLL failed:
> DLL=C:\Program Files\psqlODBC\0905\bin\pgxalib.dll, HR=%3, File=%2
> Line=%3.%0

The DLL is in fact that location, so the registry seems to be pointing to the right file. The ODBC driver is 64bit and so is my OS.  "File=%2" is pointing to something on the d drive, which doesn't make sense to me, since d drive is a DVD. MSDTC is running... what am I missing?

I have toggled Linked Server Properties "Enable Promotion of Distributed Transactions for RPC" to both "True" and "False" and this doesn't change the issue and does not produce a different error.

Otherwise, scouring the Internet has brought me nothing.

Last thing to point out, my query isn't actually doing any updating - it is just pulling data. So I'm not sure why MSDTC get's invoked in the first place...


texts: What happens on the primary when log cannot be restored on the secondary on Log Shipping

texts:
I would like to setup a log shipping but I have a concern as to what would happen if the restore operation on the secondary would fail for some reason. I want the primary database keep working no matter what happens on the secondary database - even if the log restore can't be completed or secondary db goes offline. Does log shipping offer such an option?

Thank you all.


texts:
Does select_catalog_role if granted to a schema provide access only to dba tables and v$ views only and not to other schemas created for any application?

The reason i'm asking this is I need to grant select_catalog_role to some administrators but not to the application schemas where in sensitive information resides.


texts: Mongodb Not Restarting with Collections, WiredTiger.wt may be corrupt

texts:
I believe my MongoDB did not have a clean shutdown. I am able to restart it in a new location which doesn't have all of my collections. If I try to repair or start it in the old location, it gives the following error:

[ec2-user@ip-172-31-30-192 tmp]$ mongod --repair --dbpath /data

2017-08-20T16:20:30.951+0000 I CONTROL [initandlisten] MongoDB starting : pid=31865 port=27017 dbpath=/data 64-bit host=ip-172-31-30-192

2017-08-20T16:20:30.951+0000 I CONTROL [initandlisten] db version v3.2.16

2017-08-20T16:20:30.951+0000 I CONTROL [initandlisten] git version: 056bf45128114e44c5358c7a8776fb582363e094

2017-08-20T16:20:30.951+0000 I CONTROL [initandlisten] OpenSSL version: OpenSSL 1.0.0-fips 29 Mar 2010

2017-08-20T16:20:30.951+0000 I CONTROL [initandlisten] allocator: tcmalloc

2017-08-20T16:20:30.951+0000 I CONTROL [initandlisten] modules: none

2017-08-20T16:20:30.951+0000 I CONTROL [initandlisten] build environment:

2017-08-20T16:20:30.951+0000 I CONTROL [initandlisten] distmod: amazon

2017-08-20T16:20:30.951+0000 I CONTROL [initandlisten] distarch: x86_64

2017-08-20T16:20:30.951+0000 I CONTROL [initandlisten] target_arch: x86_64

2017-08-20T16:20:30.951+0000 I CONTROL [initandlisten] options: { repair: true, storage: { dbPath: "/data" }

} 2017-08-20T16:20:30.972+0000 I - [initandlisten] Detected data files in /data created by the 'wiredTiger' storage engine, so setting the active storage engine to 'wiredTiger'.

2017-08-20T16:20:30.972+0000 I STORAGE [initandlisten] Detected WT journal files. Running recovery from last checkpoint.

2017-08-20T16:20:30.972+0000 I STORAGE [initandlisten] journal to nojournal transition config: create,cache_size=17G,session_max=20000,eviction=(threads_min=4,threads_max=4),config_base=false,statistics=(fast),log=(enabled=true,archive=true,path=journal,compressor=snappy),file_manager=(close_idle_time=100000),checkpoint=(wait=60,log_size=2GB),statistics_log=(wait=0),

2017-08-20T16:20:30.981+0000 E STORAGE [initandlisten] WiredTiger (-31802) [1503246030:981472][31865:0x7f6ad1d9fd80], file:WiredTiger.wt, connection: unable to read root page from file:WiredTiger.wt: WT_ERROR: non-specific WiredTiger error

2017-08-20T16:20:30.981+0000 E STORAGE [initandlisten] WiredTiger (0) [1503246030:981530][31865:0x7f6ad1d9fd80], file:WiredTiger.wt, connection: WiredTiger has failed to open its metadata

2017-08-20T16:20:30.981+0000 E STORAGE [initandlisten] WiredTiger (0) [1503246030:981548][31865:0x7f6ad1d9fd80], file:WiredTiger.wt, connection: This may be due to the database files being encrypted, being from an older version or due to corruption on disk

2017-08-20T16:20:30.981+0000 E STORAGE [initandlisten] WiredTiger (0) [1503246030:981564][31865:0x7f6ad1d9fd80], file:WiredTiger.wt, connection: You should confirm that you have opened the database with the correct options including all encryption and compression options

2017-08-20T16:20:30.981+0000 I - [initandlisten] Assertion: 28718:-31802: WT_ERROR: non-specific WiredTiger error

2017-08-20T16:20:30.982+0000 I STORAGE [initandlisten] exception in initAndListen: 28718 -31802: WT_ERROR: non-specific WiredTiger error, terminating

2017-08-20T16:20:30.982+0000 I CONTROL [initandlisten] dbexit: rc: 100

Is there a way to fix my wiredtiger.wt file or move my collections and indexes from the old location into the new location?


texts: Calculate Difference in Overlapping Time in PostgreSQL / SSRS

texts:
I'm using PostgreSQL 9.2 and SSRS 3.0.  I'm trying to calculate the difference in non-overlapping time entries in a timesheet.
My data looks like this:

I would like this to return:

I can get the overlapping minutes 635, but am having trouble with the not overlapping, 605 minutes.


texts:
To block a particular user and IP combination on an Oracle Database , I created the following trigger, and compiled without errors.

Create or replace trigger you_may_not_login
after logon on database
begin
if sys_context('USERENV','SESSION_USER')='xx' AND     sys_context('USERENV','IP_ADDRESS')='10.0.30.219' then
raise_application_error(-20001,'Denied!  You are not allowed to logon the database');
end if;
end;
/

When I login using the 'xx' user, the alert log shows the following error but access IS granted to the database, the trigger somehow fails from blocking the login of the user.

Errors in file /oraarch/core/udump/wfsbi_ora_9338.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Sorry, you are not allowed here!
ORA-06512: at line 9

Where am I going wrong?/


texts: Can't create a server role using GUI in SQL Server 2014 Express

texts:
I am still new to DBA and learning. I am having an issue when creating a Server Role using GUI. The option to create the server role does not seem to be visible in the Menu

But when I create a role using the following script it is successful

CREATE SERVER ROLE TestRole AUTHORIZATION MyUser

Also after creating a role I can't update it using the GUI options

How do I enable the Role Administration GUI options for SQLServer express?

edit
Microsoft SQL Server Management Studio                      12.0.2000.8


texts: Oracle Active Dataguard and User passwords

texts:
I support a database that uses an active dataguard physical standby.  The standby database is configured to let users access the read only standby for read only queries and reporting.  We do this allow querying of production data, without risking user queries creating problems with locking/blocking.
The one problem with this, is users are unable to reset their own passwords, because they are going given the connection details for the read only database.  I am trying to think of a way that would allow the users to reset their own passwords from the read only standby, but I am coming up late.  I'm wondering if anyone has dealt with a similar issue has found a work around.
Right now, every time one of the users who only has access to the standby database, has their password expire, they reach out to a member of the dba team and have their password reset manually.
I am trying to find a way to allow users to be, logged into the standby database, but reset their password in the primary copy, without having to give the users the production connection details.


texts: Is it possible to reorder tables in phpMyAdmin?

texts:
By default, database tables are ordered in alphabetical order.

Can this be reordered?

For example:

default alphabetical: company, profile, user
common User Flow: user, profile, company

Maybe the desire to order it from a UX standpoint isn't best practise.

But navigating it several times a day top-down would seem quicker and easier than a dictionary (FCFS).

I currently:

reordering columns by drag-and-drop (or ALTER TABLE)
hiding tables containing static data (rarely changed)


texts: How to make table with row name and column name

texts:
I am new to databases and now i came to a point where i need more complex table.
I have a table which contains site details. Each site can have multiple devices. Each device have multiple measurement tables.

Structure looks like this:

                    DEVICE LIST TABLE 1 ► MEASUREMENT TABLE (each device)
SITE LIST TABLE ►   DEVICE LIST TABLE 2 ► MEASUREMENT TABLE (each device)
                    DEVICE LIST TABLE 3 ► MEASUREMENT TABLE (each device)

Measurement table looks like this

+----+----+----+----+----+
|    | F1 | t1 | F2 | t2 |
+----+----+----+----+----+
| A1 |    |    |    |    |
+----+----+----+----+----+
| A2 |    |    |    |    |
+----+----+----+----+----+
| A3 |    |    |    |    |
+----+----+----+----+----+
| A4 |    |    |    |    |
+----+----+----+----+----+
| A5 |    |    |    |    |
+----+----+----+----+----+
| A6 |    |    |    |    |
+----+----+----+----+----+

I do not understand how to make table like this. If not possible what is the best practice to link every ROW with device.

Or should i create dynamically measrements A1 to An?

+-----------+---------+----+----+----+----+
| device_id | name    | F1 | t1 | F2 | t2 |
+-----------+---------+----+----+----+----+
|           | A1...An |    |    |    |    |
+-----------+---------+----+----+----+----+

And then do

SELECT * FROM measurement_table WHERE device_id = x


texts: Need help to understand how information_schema.TABLES get updated

texts:
I created large innodb table

mysql> select count(*) from big_data;

+----------+
| count(*) |
+----------+
| 23822336 |
+----------+

checked the stats in informaion_schema.TABLES

mysql> select data_length, index_length, data_free
       from information_schema.TABLES
       where TABLE_NAME = 'big_data';

+-------------+--------------+-----------+
| data_length | index_length | data_free |
+-------------+--------------+-----------+
|  4385144832 |            0 |         0 |
+-------------+--------------+-----------+

Then deleted approx 4M rows.

Checked the count

mysql> select count(*) from big_data;

+----------+
| count(*) |
+----------+
| 19978240 |
+----------+

checked the information schema.TABLES

mysql> select data_length, index_length, data_free
       from information_schema.TABLES
       where TABLE_NAME = 'big_data';

+-------------+--------------+-----------+
| data_length | index_length | data_free |
+-------------+--------------+-----------+
|  4385144832 |            0 |         0 |
+-------------+--------------+-----------+

There is no change!!

Can somebody please explain to me what happened?

I'm using mysql 5.6.18


texts: Distributed Availability Group Endpoint_URL Setup Question

texts:
I have two sites: SiteA and SiteB.  Both sites are running SQL Server 2017 Enterprise v14.0.3048.4 on Windows 2012.  Both sites are linked with a dedicated VPN connection.  We have a series of availability groups created: AG1-9 at each site with the intention of synchronizing data from siteA to siteB using distributed availability groups.  All of the AG listeners are configured to listen on 1433.  The SQL 2017 instances are using 5023 as the hadr endpoint and the SQL 2012 instance on the same cluster is using 5022.  The SQL instance listens on another port.
SiteA is a colo that we manage.  There is a 2 node cluster with SQL Server installed as a failover cluster instance (FCI) for fault tolerance.
SiteB uses 2 EC2 instances in AWS.  The AGs are configured for synchronous commit.
When the distributed availability group is created, originally siteA's Clustered SQL Name was used in the endpoint_url (tcp://sqlname:5023) and for siteB the availability group listener name was used in the endpoint_url (tcp://listener:5023).
This worked fine.
The plan is to expand siteA so there is a secondary replica in the local AG for data redundancy and to support local read-only copies.  In order to support this, siteA's endpoint_url in the distributed AG would need to point to the listener (tcp://listener:5023) similarly to how siteB is configured.
This was configured and working as of about 2 weeks ago.  Now a change was needed so 2 of the distributed AGs needed to be recreated, and now the communication is broken.  Whenever siteB tries to communicate back to siteA there is a timeout error:

2019-07-22 08:16:32.790 spid126s     A connection timeout has occurred
while attempting to establish a connection to availability replica
'SiteA_AG5' with id [906D00AA-8A78-5532-47F1-2167C5C4A154]. Either
a networking or firewall issue exists, or the endpoint address
provided for the replica is not the database mirroring endpoint of the
host server instance.

A telnet to the listener over port 5023 fails

telnet SiteA_Listener5 5023

Telnets to SiteB's listeners work fine.  This only seems to affect telnets to the listeners that exist inside the FCI.
Telnets to SiteA's Clustered SQL Name over 5023 succeed.
Is a failover clustered instance with AG listeners listening on 1433 supposed to be able to receive requests from incoming connections over 5023?
If this does not work, then adding a read only replica to our FCI will not be possible.

SiteA Config:
Nodes: SiteA_Node1 SiteA_Node2
FCI SQL Name: SiteA_SQLFCI (dynamic sql port)
AG Listeners: SiteA_AG1, SiteA_AG2, SiteA_AG3, etc... (port 1433)
SiteB Config:
Nodes: SiteB_Node1 SiteB_Node2 (dynamic sql port)
AG Listeners: SiteB_AG1, SiteB_AG2, SiteB_AG3, etc... (port 1433)
telnet SiteA_SQLFCI 5023 (works)
telnet SiteB_Node1 5023 (works)
telnet SiteA_AG1 5023 (fails)
telnet SiteB_AG1 5023 (works)

The really weird part is that I found out that I am able to run this command

telnet SiteA_AG1 5022 (works)

the problem with that is the AG listener exists in the SQL 2017 instance which is using 5023 as the HADR port, not 5022.  The SQL 2012 instance is using 5022.
I'm really confused as to what's going on here. any help is appreciated


texts: How to set variables to multiple values (that are integers)?

texts:
I have 5 facilities in my division, but the code below in a stored procedure only pulls documents when the "facilityID = 4" unless if I change it manually to represent other ID numbers. So I need the facilityID variable below to point to more than one value like:
[Set @FacilityID = 1,2,3,4,5] but of course it's not allowing me. If it was characters, I would be able to put them in quotes. Any advice please?

declare @facilityID tinyint,
@facilitydivisionID tinyint,

SET @facilityID = 4
SET @facilitydivisionID = 6


texts:
What is wrong here in following screen shot?

I am trying solutions posted here and here to add a login


texts: BCP Error SQLState = S1000, NativeError = 0 , Unable to open BCP host data-file

texts:
I am trying to execute the below BCP query in my TEST Server, where BCP has a connection string to the production DB, so my destination folder is in my Test Server and I am getting an error, I think it's a permission related issue.

DECLARE @str NVARCHAR(2000), @path NVARCHAR(200)='Z:\SQL' , @TableName NVARCHAR(100)='tblUsers' Set @str = 'BCP IDB.dbo.'+@TableName+ ' OUT '+@path +'\'+@TableName+'.csv -c -t^| -T -S ProductionServerName\SQL2012' Exec xp_cmdshell @str

Print (@str) = BCP IDB.dbo.tblUsers OUT Z:\SQL\tblUsers.csv -c -t^| -T -S ProductionServerName\SQL2012

  ERROR: output SQLState = S1000, NativeError = 0 Error =
  [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host
  data-file NULL

I shared my Test Server folder to the Production User account. Also I have mapped the shared folder drive as a network share in production and run the below SQL statements:

EXEC XP_CMDSHELL 'net use Z: \\TestServevrName\Test'

EXEC XP_CMDSHELL 'Dir Z:'

Still I am facing the same Issue.

Please guide me what steps are required to fix this permissions issue.


texts: How to join sys.columns and sys.synonyms table using object_id column

texts:
I have used two servers, Server A(local server) and Server B(remote server).
I have created synonyms in the Server A for the Server B table. I have created  synonym using following syntax.

CREATE SYNONYM RemoteServerATable FOR Server B .[database_name].[schema_name].object_name

I have tried to join two tables sys.columns and sys.synonyms using below query

select s.name as [SynonymName], c.name as [ColumnName], c.object_id from [Server B].[DatabaseName].sys.columns c inner join sys.synonyms s on s.name = 'RemoteServerATable' and  c.object_id  = 1973582069

I have joined two tables using object_id of the sys.columns tables in the other server and hard coded integer value. I got this value using below query

exec ('select object_id(''[Server B DatabaseName].[Server B SchemaName].[Server B ObjectName]'') as object_id') at [Server B]

I am not getting correct columns information. Kindly suggest someways join two tables and get the column information.


texts: 'Could not fsync file' while running VACUUM FULL on a table

texts:
I'm getting an error for around half of my tables in Postgres while trying to run VACUUM FULL.
For example, I have a table named 'qwerty', and when i try to vacuum it i get the following error:

ERROR:  could not fsync file "base/16392/3655838": Input/output error

But for some reason, when i run:
SELECT pg_relation_filepath('qwerty');

It shows the file path as another:
base/16392/3391046

I cant seem to find any information about this online, I would be more than glad if anybody could help!


texts: Apply in a CSV file to multiple databases

texts:
Recently, I have been working with a client whom has asked us to provide their dataset for use in a mobile application we are making for them. The requirements for this project include the ability to update the local dataset on the mobile devices with any changes that our client delivers to us. The local databases are to be stored in sqlite according to the mobile device standard.

The Problems:

The Dataset: Our clients can only deliver to us an entire dump of their database in CSV format. We must be able to apply only the changes between the new CSV and our existing databases without causing duplicate entries or stale data.
The Delivery: Only the changes to the dataset may be sent to the mobile databases. Resending of the entire database is too expensive.

Approach:

Currently, the solution we are looking into involves using one of our backend MySQL servers to maintain a backup of database stored on the mobile sqlite databases. When a new dataset arrives, we were planning to make a new database with the new data, and then to be able to use diff and patch linux commands to update the server side data. However, with this approach, knowing what or how to update in the mobile phone's local databases gives us some issues. I am currently unaware of any way to use the patching or the diff file from MySQL in either the Android or iOS environments.

Ideal Approach:

Preferably, when we receive the entirely new dataset, we would like to be able to detect just the changes in the new CSV against the current state of the database. Then, with this list of changes, we would generate INSERT/UPDATE/DELETE statements that would be able to apply each of the changes to both the MySQL and sqlite databases. We would store a list of each of the INSERT/UPDATE/DELETE statements along with a the date updated so that upon request each of the phones can be updated accordingly to the newest data.

However, I am also currently unaware of a way to detect changes from a new CSV to an existing database which makes constructing INSERT/UPDATE/DELETE statements difficult.

The Questions:

What is the best way to get the changes between the CSV file and our current database implementation in a portable and meaningful format?
What is the preferred method for cascading such changes from our server side database to the phone's local databases?

I am currently seeking any advice for either improvements to our design or proposed implementation. I feel like I can't be the first person to try and reflect changes to a CSV in a database or synchronize changes in a server side database to a local offline database.

Thank you everyone in advance for your help. I look forward to hearing alternative approaches to this problem.


texts: What is the SELECT statement equivalent of SHOW ALL SLAVES STATUS?

texts:
The following SQL query in MySQL outputs, in my case just a single row, but with many fields/columns.
SHOW ALL SLAVES STATUS;

However I need a query that will output just one of the fields.
Is there an equivalent query in the form of a SELECT statement, so that I can return only the required field?
e.g. SELECT field_x FROM some_table;