texts:
I logged onto a new clients systems and ran sp_blitz to see what's shaking. It reports back that "CPU Schedulers Offline" which is a new one for me.

  Some CPU cores are not accessible to SQL Server due to affinity masking or licensing problems.

Fair enough, I run the base query

SELECT
    DOS.is_online
,   DOS.status
,   DOS.*
FROM
    sys.dm_os_schedulers AS DOS
ORDER BY
    1;

That reports back that I have 8 set to VISIBLE OFFLINE, 43 to online. To my knowledge, no one at this client would have intentionally set any CPU affinity.

I decided to see if I could unbugger it. When I look at the properties window, I see 40 processors available and none of them set to have affinity.

Why there's 40 showing yet 43 entries in the dm_os_schedulers where is_online is true seems curious as well. The cpu_id of the 8 offline are 32 to 39.

sys.configurations seems to concur with affinity not being explicitly on

name                 value  value_in_use  description
affinity I/O mask    0      0             affinity I/O mask
affinity mask        0      0             affinity mask
affinity64 I/O mask  0      0             affinity64 I/O mask
affinity64 mask      0      0             affinity64 mask

This isn't an Enterprise Edition so the CAL grandfather thing shouldn't be factor here but I can ask that question tomorrow if need be

ProductVersion  ProductLevel  ProductUpdateLevel  Edition
11.0.5058.0     SP2           NULL                Standard Edition (64-bit)

Running Glenn Berry's diagnostic queries, this stuff might be relevant

System Manufacturer: 'Dell Inc.', System Model: 'PowerEdge R720'.
Intel(R) Xeon(R) CPU E5-2680 v2 @ 2.80GHz

So, what gives? Am I virtualized and don't know it? Is there some other place I should be looking to determine why SQL Server can't use some of the CPUs?

Reference articles

A list of articles I read but clearly didn't comprehend well enough to answer my own question

BOL sys.dm_os_schedulers
Glenn Berry's DMV a day
SQLServerGeeks DMV a day


texts: Using SQL Profiler on a database that's in production

texts:
As a developer, I use SQL Profiler quite often. It's a good debugging tool, both to track what my code is doing and to analyse performance problems.

But I've always used it on my development environment, and in a very controlled way.

Start my application, and get it into a specific state
Start a trace on the profiler
Perform a specific sequence of actions on my application
Stop the trace and examine the results.

Can the SQL Profiler be practically used in an in-production environment?

My first concern is that it would degrade the performance.

My second concern is that, because it's in production, you aren't triggering the interesting actions itself. You would have to leave the profiler running for a long period then analyse the results. Would the result set become too unwieldy? (Taking up too much disk space and being too hard to query).

Does anyone use the SQL Profiler in production?


texts: Indexing strategy when using the between operator SQL Server 2008

texts:
I have a large table ~25 million rows with the structure
CREATE TABLE [dbo].[rx](
            [pat_id] [int] NOT NULL,
            [fill_Date] [date] NOT NULL,
            [script_End_Date]  AS (dateadd(day,[dayssup],[filldate])) persisted,
            [drug_Name] [varchar](50) NULL,
            [days_Sup] [int] NOT NULL,
            [quantity] [float] NOT NULL,
            [drug_Class] [char](3) NOT  NULL,
            CHECK(fill_Date <=script_End_Date
PRIMARY KEY NONCLUSTERED
(
          [clmid]
)

create clustered index ix_rx_temporal on rx(fill_date asc, script_end_date asc, pat_id asc)

The primary key on this table is never queried on.  This table by far is used most often with queries that involve date ranges.  I have a calendar table with the structure
CREATE TABLE [dbo].[Calendar](
             [cal_date] [date] PRIMARY KEY,
[Year] AS YEAR(cal_date) PERSISTED,
[Month] AS MONTH(cal_date) PERSISTED,
[Day] AS DAY(cal_date) PERSISTED,
             [julian_seq] AS 1+DATEDIFF(DD, CONVERT(DATE, CONVERT(varchar,YEAR(cal_date))+'0101'),cal_date));

The query that I'm trying to speed up is:
;WITH x
     AS (
        --join finds the amount of distinct drugs that a person was prescribed on a given day
        SELECT rx.pat_id,
               c.cal_date,
               Count(DISTINCT rx.drug_name) AS distinctDrugs
         FROM   rx,
                calendar AS c
         WHERE  c.cal_date BETWEEN rx.fill_date AND rx.script_end_date
         GROUP  BY rx.pat_id,
                   c.cal_date),
     y
     AS (
        --makes a sequence so that contiguous dates can be grouped together as a date range
        SELECT x.pat_id,
               x.distinctdrugs,
               c2.julian_seq- Row_number()
                         OVER(
                           partition BY x.pat_id, distinctdrugs
                           ORDER BY x.cal_date) AS rn,
               x.cal_date
         FROM   x,
                calendar AS c2
         WHERE  c2.cal_date = x.cal_date)
--finds the max and minimum dates which a person was taking X amoung of drugs
SELECT y.pat_id,
       Min(y.cal_date)    AS min_overlap,
       Max(y.cal_date)    AS max_overlap,
       Min(distinctdrugs) AS distinctDrugs
FROM   y
GROUP  BY y.pat_id,
          rn

I tried a couple of the following indexes
CREATE NONCLUSTERED INDEX [ix_rx2] ON [dbo].[rx]
(
[drug_name] ASC,
[drug_class] ASC,
[fill_date] ASC,
[script_end_date] ASC
)

and
CREATE NONCLUSTERED INDEX [ix_rx3] ON [dbo].[rx]
(
[fill_date] ASC,
[script_end_date] ASC
)
INCLUDE ( [pat_id],
[drug_class],
[drug_name])

Whenever I check out the execution plan, none of the indexes on the rx table are being used.  There are other aspects of the execution plan, but the bulk of it  looks like

I even tried removing the current clustered index and just using (fill_date,admi_date) and yet I still can't find a way to keep from running into that hash match operator in the execution plan that is taking up the vast majority of the resources for this query.  The primary key on the rx table is never queried on, and I'll primarily be doing queries involving date ranges with this table.  The indexes on the tables have nominal fragmentation and the densities of the indexes are all very low.  What can I do do speed up this query, or am I going to be limited by hardware?


texts: MSMerge_contents Table Populated After One Sync

texts:
I am running merge replication with SQL 2012.

When I first create my publication the MSMerge_contents table is populated with a large number of records with the colv1 being set to 0xFF.

When I run the stored procedure like this,

sp_showcolv @colv =0xFF

It becomes apparent that these records do not represent actual changes to the underlying records, unless I am missing something?

Why are those records there? Is it because of partitioning, or column level tracking? I am using column level tracking, but am wondering whether I need to revisit this option?


texts: Max Memory settings on Multi-Instance SQL Server 2008 R2 Cluster

texts:
SQL Server 2008 R2 multi-instance cluster (on VMware vSphere 5.1)
2 nodes, each running 2 instances (4 instances in total)
16GB RAM per node.

The only time the instances are on the same node is when I am patching the other node.

Brent Ozar's "maximum server memory" recommendation is to leave Windows 4GB or 10%, whichever is more.

Since this a cluster, how should I set the max memory on each node? Should I treat each as a standalone server? This would make sure memory on each node is not wasted. However, in a node failure, the max memory total for all 4 instances would exceed system memory of the single node. Will this cause any issues in the timeframe until we get the second node recovered? Do I need to lower the max memory setting on the 4 instances until the secondary node is recovered? Or is SQL Server smart enough to keep working (using page file if necessary).


texts: Find all tables without foreign keys in a database

texts:
In SQL Server, how do we find all tables which do not have either of:

foreign key constraints
references by other tables' foreign keys


texts: Mysql slave use more memory than master server

texts: I have used top command to check memory usage on both mysql master and slave server. Master is using only 12% of allotted memory but slave is using 82%. configuration wise both are same. can anyone tell me how to resolve this and why its taking more than slave?


texts: Contents of transaction log file in more detail

texts:
I have a question regarding transaction log (let’s just call it LDF for short) contents. I am assuming a database with full recovery model.

I have read that LDF file contains (logs) each and every operation to the database (that is in full recovery mode). How is it different from logging during BEGIN TRAN; COMMAND(s); COMMIT? I am asking because apparently you can roll back transactions, but you cannot roll back standard commands (in full recovery mode).

I guess that during transaction the contents being logged into the LDF file are different than in regular full recovery logging. Is that right? How is it different? Is it only the inclusion of “undo” operations for each action?

On a related note, I have heard that there are commercial tools to "rollback/undo" standard queries using full recovery LDF file. How do they do it? Do they analyze the LDF contents and try to come-up with inverse/undo operations?


texts: Architectural Question about Data analytics

texts:
I understand that in many OLTP architectures there is some ELT stuff which creates a OLAP Data warehouse model and then you can run data analytics on this.  However, this can take some time to generate.  Suppose you need to run your data analytics immediately, is there another well known pattern or approach for this?

Many thanks.


texts: Is there any way to recover  a database after dropping from phpmyadmin?

texts:
I was planning to drop all the tables from a test account, but unfortunately, I didn't notice that I was in the production account when I chose to drop all the tables of my db from phpmyadmin interface.

Is there any way of recovering my db content?


texts: Create dynamic set conditionally excluding member if exists

texts:
As part of my cube definition I am creating a dynamic set of all claim status' minus the PEND status.  This has worked great for years, but now we have someone who does not have the PEND status which is resulting in an error when opening the cube.  I would like to create a set that is all Claim status minus PEND if it exists.

The error I am getting is "The level '&['PEND]' object was not found in the cube when the string, '[Claim].[Claim Status].%[PEND], was parsed."

CREATE DYNAMIC SET CURRENTCUBE.[Non Pending Claims]
  AS [Claim].[Claim Status].[All Claims].Children - [Claim].[Claim Status].&amp;[PEND];

I tried using except without luck.

CREATE DYNAMIC SET CURRENTCUBE.[Non Pending Claims]
  AS Except ([Claim].[Claim Status].[All Claims].Children, [Claim].[Claim Status].&amp;[PEND]);

Even tried

Except ([Claim].[Claim Status].[All Claims].Children, StrToSet("[Claim].[Claim Status].&[PEND]"))

Does anyone know how to safely tell if a member exists then exclude it?


texts: clarification of ALTER DATABASE error message

texts:
I took a SQL Server 2008 database offline:

alter database FOO set offline;

Then I copied its MDF and LDF physical files to a new folder on another hard drive.

Then I ALTERed the database:

ALTER DATABASE FOO
MODIFY FILE ( NAME = FOO_data, FILENAME = 'H:\SQL2008DATA\FOO.mdf' );

ALTER DATABASE FOO
MODIFY FILE ( NAME = FOO_log, FILENAME = 'H:\SQL2008DATA\FOO.ldf' );

which results in these status error messages, where xxx = data or log:

  The file "UZ_{xxx}" has been modified in the system catalog. The new path will be used the next time the database is started.

I bring the database back online:

ALTER DATABASE FOO SET ONLINE;

and then I try to delete the old data and log files, but am getting an error that the log file cannot be deleted because it is open in SQL Server.

Do I have to shut down and restart the entire database (i.e. bounce the SQL Server process) before the LDF file will be released?


texts: Converting string to date with timezone adjustment

texts:
I am attempting to convert stirngs to dates in mysql

Thu Oct 23 16:46:47 2014 +02:00

Im unsure how to handle the +02:00, The rest is simple enough

 update logs
 set date = str_to_date(date_raw, '%a %b %e %T %Y')

This returns

Truncated incorrect datetime value: 'Thu Oct 23 16:46:47 2014 +02:00'

Any ideas?


texts:
Having just set up our new SQL Server Managed Instance, restored a sample database for testing, and run Azure's vulnerability assessment, it produces this high risk finding:

VA2120 - Features that may affect security should be disabled
The more SQL Server features and services you enable, the larger its
surface attack area becomes, making your system more vulnerable to
potential attacks. These fetures should be disabled unless it is
absolutely needed in this environment.
Remediation Script:
EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXECUTE sp_configure 'remote access', 0;
RECONFIGURE;
EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;

Turning to Google before doing anything, I found this Microsoft Docs article stating that (emphasis mine):

This topic is about the "Remote Access" feature. This configuration option is an obscure SQL Server to SQL Server communication feature that is deprecated, and you probably shouldn't be using it.

Can anyone therefore please provide some clarity on the following?

Why is it enabled given Microsoft's description?
Does it need to be enabled in Azure SQLMI? Because...
When I run the remediation script I get this error:

Changes to server configuration option remote access are not supported in SQL Database Managed Instances.


texts: Ola Hallengren SQL Server Backup - CleanupTime

texts:
When the @CleanupTime parameter is used in the [dbo].[DatabaseBackup] procedure, are the targeted old backup files deleted after each successful database backup?  Or are all the backups processed first and then cleanup is performed?

I'm having problems with free disk space and trying to give my network administrator an accurate spec for the size of the backup volume.

I read through Understanding CleanupTime in Ola Hallengren's SQL Server scripts in relation to FULL backups and LOG backups before posting.  I couldn't find an answer to my question there. If it is there, I missed it.  Just read through it again, but still don't see anything.


texts: How to archive a transactional table on another server?

texts:
(Feel free to tell me the correct name for this... it is a common scenario!)

We have a production SQL Server 2008 box. One table is heavily transactional. It is also purged every few days (old records are deleted).

We want to set up a 2nd SQL server to receive all the rows from this table and archive them for later analysis. We need to archive all rows, including rows that are purged from the production table. E.g. we want to push over insert and update, but not delete.

Some stats:

DB itself is about 1TB
The table we would like to archive: record size is about 1K
The table we would like to archive: 50K-150K records/day are created (and later purged)

We are looking for:

Data to move at least once each 24 hours. (more frequently is fine, but not reqd)
Simple to set up. (the three-box transaction replication looks rather top heavy)
If possible, the receiving system could be a different ver/flavor of SQL (e.g. receiving system could be SQL Server 2012 express -- yes, I know about the SQL Express db size limit--just looking for flexibility)

I thought there would be a proc out there that does this, but I haven't found one yet.


texts: Update field with new string made from characters in existing value

texts:
field is meta_value and current value is like 01/01/2006

i want to change it to a string 20060101 so thought i could get the value and update it with the characters in the correct order.

ie

UPDATE wp_postmeta SET meta_value = meta_value[9]meta_value[8]meta_value[7]meta_value[6]


texts: Understanding non clustered index locking during an update

texts:
The setup script

CREATE TABLE t2 (   [col1] INT, [col2] INT  );

DECLARE @int INT;
SET @int = 1;

WHILE (@int <= 1000)
BEGIN
    INSERT INTO t2
        ([col1], [col2])
    VALUES (@int*2, @int*2);
    SET @int = @int + 1;
END
GO
create clustered index cl on t2(col1)
create  index ncl on t2(col2)

I run an simple update and keep the transaction open in read committed isolation level.

begin tran
update t2 set [col2]=[col2]+1  where col1=6

If I check sp_lock in another session I get the below results

What I am trying to understand are the key locks on the non-clustered index(indid 2).  Why are there two key lock on non-clustered index?

If I check dbcc page on page id 248, I could locate the obvious one((1bfceb831cd9)) which is the lock for the entry for the record 6 which got changed to 7.  Output of DBCC PAGE below

What I am trying to understanding is what the purpose of the other key lock(5ebca7ef4e2c) is and what its locking.


texts: pg_upgrade unrecognized configuration parameter "unix_socket_directory"

texts:
I'm trying to upgrade Postgresql from 9.2 to 9.3 in Fedora 18 using this command as the postgres user

$ pg_upgrade -b /bin -B /usr/pgsql-9.3/bin -d /var/lib/pgsql/data -D /var/lib/pgsql/9.3/data/ -j 2 -u postgres

The error in the log

  command: "/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/data" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directory='/var/lib/pgsql'" start >> "pg_upgrade_server.log" 2>&1
  waiting for server to start....FATAL:  unrecognized configuration parameter "unix_socket_directory"
  .... stopped waiting
  pg_ctl: could not start server

As pointed by a_horse in the comments that parameter was replaced by unix_socket_directories (plural) in 9.3. But the server version being started is the old one 9.2:

$ /bin/pg_ctl --version
pg_ctl (PostgreSQL) 9.2.4

Any ideas?


texts:
Table structure for table tbl_appointment

CREATE TABLE IF NOT EXISTS `tbl_appointment` (
`ap_id` int( 11 ) NOT NULL ,
`ap_starting` time( 6 ) NOT NULL ,
`ap_ending` time( 6 ) NOT NULL ,
`ap_day` varchar( 255 ) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = latin1;

MySQL said: Documentation

  1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(6) NOT NULL,
    ap_ending time(6) NOT NULL,
    ap_day varchar(255) NOT NULL
  )' at line 9`

I tried to import the SQL file to phpmyadmin. the file works fine on later version of XAMPP from what I have. I tried to edit the SQL file based on the error but to no avail.


texts:
Hi i'm currently working with oracle that will give me my requered output.
I able to make the query but when i'm putting it in stored procedure it gives me error of Error(11,1): PL/SQL: SQL Statement ignored and Error(15,20): PL/SQL: ORA-00936: missing expression

here's  my query not in procedure:

with
t1  as (
select * from vw_break_time_doublebreak
/* where clause that satisfies your query */
where date_time >= date '2020-01-01' and date_time < date '2020-02-01'
)
,t2 as (
select shift, trunc(min(min(date_time)) over (), 'mm') mn, last_day(max(max(date_time)) over ()) mx
from t1
group by shift
)
,t3 as (
select shift, mn + level - 1 dateshift from t2
connect by mn + level - 1  <= mx
and prior shift = shift
and prior sys_guid() is not null
)
SELECT t3.shift, t3.dateshift AS date_time, count (CNT) AS COUNT
    FROM t3
    left join
    vw_break_time_doublebreak db
    on (t3.shift = db.shift and t3.dateshift = TRUNC (db.date_time))
GROUP BY t3.shift, dateshift
ORDER BY t3.shift, dateshift;

but when i put it on store procedure it has error:

create or replace PROCEDURE           sp_bt_getcountDblBreakbyshift (
   in_date      IN       VARCHAR2,
   in_date_end  IN       VARCHAR2,
   in_pagenum   IN       INT,
   p_rec        OUT      sys_refcursor
)
AS
BEGIN
   OPEN p_rec FOR

with   <-------Error here
t1  as (
select * from vw_break_time_doublebreak
/* where clause that satisfies your query */
where date_time >= date in_date and date_time < date in_date_end <------ error here
)
,t2 as (
select shift, trunc(min(min(date_time)) over (), 'mm') mn, last_day(max(max(date_time)) over ()) mx
from t1
group by shift
)
,t3 as (
select shift, mn + level - 1 dateshift from t2
connect by mn + level - 1  <= mx
and prior shift = shift
and prior sys_guid() is not null
)
SELECT t3.shift, t3.dateshift AS date_time, count (CNT) AS COUNT
    FROM t3
    left join
    vw_break_time_doublebreak db
    on (t3.shift = db.shift and t3.dateshift = TRUNC (db.date_time))
GROUP BY t3.shift, dateshift
ORDER BY t3.shift, dateshift;
END;

sorry i'm just kinda new in oracle and don't know what i'm doing wrong.
hope someone help me out with this.


texts: Sql Server Restoring Process Time Takes Too Long Time

texts: I have 7 databases on my reporting server. I restore those databases every night with differetial backups. Normally, the differential restore time was taking about 1 or 1.5 hour for each databases. Last day, my restore process took so much long time. 4 of those databases has restored about 11 hours. The other one was completed normally. What can cause those databases to take such a long restoring time?


texts:
I've written a trigger for my database which is supposed to conduct the following business rule: if a customer doesn't have a valid driving licence, they are not allowed to rent a car. They are allowed to come back with a valid one (obviously).

Does this make sense? I have a column called status, but the receptionist will have to fill out the status anyway? Also, would you recommend doing a trigger for age separately?  Thanks.

DELIMITER //

CREATE TRIGGER check_licence
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
DECLARE Status ENUM('Approved','Unapproved');
IF NEW.valid_licence = 'Yes' THEN
SET Status='Approved';
ELSE
SET Status='Unapproved';
END IF;
END //

DELIMITER ;


texts:
I have a simple table with a clustered columnstore index:

ID INT NOT NULL,
Hash BINARY(20) NOT NULL

This table has some billion rows and according to sp_spaceused, sys.allocation_units and SSMS reports, its size is about 25GB.

My problem is I can't account for all of this space. Querying sys.column_store_row_groups and sys.column_store_segments only gives me about 7,8GB. The index uses no dictionary: primary_dictionary_id and secondary_dictionary_id are -1 for all segments. Querying sys.column_store_dictionaries returns no rows at all.

The tuple mover has done its job and all row groups are in the compressed state. I already tried an ALTER INDEX REORGANIZE just in case.

My only idea for the size difference is some dictionary-like stuff I'm not accounting for. Any ideas on what I might be missing?

I'm running SQL Server 2017 (RTM-CU4).

EDIT 1:

This is the output from sp_spaceused for the table in question:

+--------+------------+-------------+-------------+------------+----------+
|  name  |    rows    |  reserved   |    data     | index_size |  unused  |
+--------+------------+-------------+-------------+------------+----------+
| IdsBin | 1073741824 | 25028112 KB | 25007432 KB | 16 KB      | 20664 KB |
+--------+------------+-------------+-------------+------------+----------+

EDIT 2:

This is a repro script with 1 million rows. It runs in about 1 minute on my machine. warning: it drops and recreates a new database

USE master;
GO
DROP DATABASE IF EXISTS MyDbWeirdTest;
GO
CREATE DATABASE MyDbWeirdTest;
GO
USE MyDbWeirdTest;
GO

CREATE TABLE IdsBin (
    ID INT NOT NULL,
    Hash BINARY(20) NOT NULL
);
CREATE CLUSTERED COLUMNSTORE INDEX ix1 ON IdsBin
GO

CREATE TYPE tBin AS TABLE (
    ID INT,
    Hash BINARY(20)
);
GO

CREATE OR ALTER PROCEDURE pBin (
    @ids AS dbo.tBin READONLY
)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT dbo.IdsBin
    SELECT ID, Hash FROM @ids;
END;
GO

SET NOCOUNT ON;
DECLARE @i INT = 1, @t INT = 1;
DECLARE @tvp dbo.tBin;

WHILE @t <= 1000000
BEGIN
    DELETE @tvp;
    BEGIN TRAN;
    WHILE @i <= 1000
    BEGIN
        INSERT @tvp VALUES (@t, HASHBYTES('SHA1', CAST(@t AS BINARY(4))));
        SET @i = @i + 1;
        SET @t = @t + 1;
    END;

    EXEC pBin @tvp;
    COMMIT;
    SET @i = 1;
END;
GO

ALTER INDEX ix1 on IdsBin REBUILD;
GO

For this repro, sp_spaceused shows:

+--------+----------------------+----------+----------+------------+--------+
|  name  |         rows         | reserved |   data   | index_size | unused |
+--------+----------------------+----------+----------+------------+--------+
| IdsBin | 1000000              | 22728 KB | 22640 KB | 0 KB       | 88 KB  |
+--------+----------------------+----------+----------+------------+--------+

sys.column_store_row_groups:

+-----------+----------+------------------+--------------+---------------------+-------+-------------------+------------+--------------+---------------+
| object_id | index_id | partition_number | row_group_id | delta_store_hobt_id | state | state_description | total_rows | deleted_rows | size_in_bytes |
+-----------+----------+------------------+--------------+---------------------+-------+-------------------+------------+--------------+---------------+
| 901578250 |        1 |                1 |            0 | NULL                |     3 | COMPRESSED        |    1000000 |            0 |       5896938 |
+-----------+----------+------------------+--------------+---------------------+-------+-------------------+------------+--------------+---------------+

So sp_spaceused gives me about 22MB and sys.allocation_units (not shown) agrees. But no columnstore DMVs seems to agree on that number and they say the index is less than 6MB in size.


texts: Beginner problems administering users on mysql

texts:
Very new MySQL user here. Two questions for the community.

I seem to have created an account that I can't delete/drop.
I can't seem to get MySQL to actually require newly created accounts to require passwords even when I specify them during creation.

I ran myisamchk and corrupton doesn't seem to be an issue. Logs below.

mysql -u root -p
use mysql
select * from db\G;

*************************** 3. row ***************************
      Host: localhost
      Db: projectdb
      User: project
      Select_priv: Y
      Insert_priv: Y
      Update_priv: Y
      Delete_priv: Y
      Create_priv: Y
        Drop_priv: Y
       Grant_priv: Y
      References_priv: Y
       Index_priv: Y
       Alter_priv: Y
Create_tmp_table_priv: Y
 Lock_tables_priv: Y
 Create_view_priv: Y
   Show_view_priv: Y
Create_routine_priv: Y
 Alter_routine_priv: Y
     Execute_priv: Y
       Event_priv: Y
     Trigger_priv: Y

mysql> drop user project;
ERROR 1396 (HY000): Operation DROP USER failed for 'project'@'%'

~~~~~

CREATE USER project123 IDENTIFIED BY 'project123';
Query OK, 0 rows affected (0.00 sec)

GRANT ALL ON projectdb.* TO project123 IDENTIFIED BY 'project123';
Query OK, 0 rows affected (0.00 sec)

quit;

MySQL then proceeds to let me log in without a password.

macbook-pro-8: $ /usr/local/mysql/bin/mysql -u project123
Welcome to the MySQL monitor.  Commands end with ; or \g.

What am I doing wrong?


texts: Why there is a start (*) after Nodegroup:0 in MySQL cluster? What does it means?

texts:
I am new to MySQL and MySQL cluster setup. However going through several blogs and Web links I've finally able to manage the cluster up and running. I have 1 management, 1 SQL and 3 data nodes (currently without any replicated SQL or data nodes as I have set NoOfReplicas=1 in the config.ini file). After running ndb_mgm> show I can see there is a start just after the first data node, what does it means? I searched over google but didn't find any proper answer. Kindly help.

ndb_mgm> show
Cluster Configuration
[ndbd(NDB)]     3 node(s)
id=3    @10.10.10.3  (mysql-5.5.34 ndb-7.2.14, Nodegroup: 0, *)
id=4    @10.10.10.4  (mysql-5.5.34 ndb-7.2.14, Nodegroup: 1)
id=5    @10.10.10.5  (mysql-5.5.34 ndb-7.2.14, Nodegroup: 2)
[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.10.10.1  (mysql-5.5.34 ndb-7.2.14)
[mysqld(API)]   1 node(s)
id=6    @10.10.10.6  (mysql-5.5.34 ndb-7.2.14)


texts: Howto do a hard upgrade from postgis-1.5 to postgis-2.0

texts: I need to upgrade from postgis 1.5 to postgis 2.0 but i cant backup my data and restore it can someone give me a link where it is clear enough for doing this?


texts:
I use sqlite3 on a Mac Mini. I'm a Java and Web programmer, with some SQL experience, not as much as an expert.

I have three requests. First and second are fast (0.2s), and the third is very slow (7s) when the WHERE clause is the combination of the first ones.

// Fetch all items in a universe (it's a category in marketing terms)

 SELECT item.codeItem, item.designation
 FROM Item item
 WHERE item.universe = 10
 ORDER BY designation  LIMIT 0,25

// search articles from a supplier (an article may have many prices with different suppliers)
//Price table is used as an associative table between Article and Supplier

SELECT DISTINCT item.codeItem, item.designation
FROM Item item, Price price, Supplier supplier
WHERE  (price.codeItem = item.codeItem AND price.supplier = 29184)
ORDER BY designation  LIMIT 0,25 ;

// search Article with a supplier in a universe

SELECT DISTINCT item.codeItem, item.designation
FROM Item a, Price t, Supplier supplier
WHERE item.universe = 10 AND  (price.codeItem = item.codeItem AND price.supplier = 29184)
ORDER BY designation  LIMIT 0,25 ;

First question is : Why is the last one so slow ? I would have thought that adding AND filters could make the request even faster.

Second question is : what would you try first to improve the query. Is a nested SELECT a good solution ?


texts: How to get overall rank based on data regardless of scope (all items, user specific, etc.)?

texts:
Table schemas:

servers
- id
- user_id
- game_id
- slug
  ...

server_votes
- id
- server_id
- ip_address
- created_at
  ...

So, I have a two tables: servers and server_votes, and I want to get the rank of each server based on how many votes each server has received in a certain amount of time. I have created the following query to do just that (apart from the ranking part):

SELECT
    s.id,
    s.user_id,
    s.slug,
    (
        SELECT
            COUNT(sv.id)
        FROM
            server_votes AS sv
        WHERE
            DATE(sv.created_at) > '2016-01-24 19:11:15'
        AND
            sv.server_id = s.id
    ) AS votes
FROM
    servers AS s
LEFT JOIN
    server_votes AS sv
ON
    sv.server_id = s.id
WHERE
    s.game_id = 1
GROUP BY
    s.id
ORDER BY
    votes DESC,
    s.id ASC

The query above will return the following:

*----*---------*---------*-------*
| id | user_id | slug    | votes |
*----*---------*---------*-------*
| 4  | 1       | Server4 | 3     |
| 2  | 1       | Server2 | 2     |
| 1  | 1       | Server1 | 0     |
| 3  | 2       | Server3 | 0     |
| 5  | 2       | Server5 | 0     |
*----*---------*---------*-------*

I'd like to end up with the following:

*----*---------*---------*-------*------*
| id | user_id | slug    | votes | rank |
*----*---------*---------*-------*------*
| 4  | 1       | Server4 | 3     | 1    |
| 2  | 1       | Server2 | 2     | 2    |
| 1  | 1       | Server1 | 0     | 3    |
| 3  | 2       | Server3 | 0     | 4    |
| 5  | 2       | Server5 | 0     | 5    |
*----*---------*---------*-------*------*

Or if I'm querying a specific user's server's, I'd get the rank in context of all servers:

*----*---------*---------*-------*------*
| id | user_id | slug    | votes | rank |
*----*---------*---------*-------*------*
| 3  | 2       | Server3 | 0     | 4    |
| 5  | 2       | Server5 | 0     | 5    |
*----*---------*---------*-------*------*

Any insight would be appreciated.

Also, here's the SQL Fiddle incase that is of any help to you.


texts: Report subscription deletion failed due to FK constraint

texts:
We have many SSRS report timed subscriptions (SQL Server 2008R2 SP4).
One of our report developers tried to delete a report subscription, and got the following error:

  An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. (rsReportServerDatabaseError) Get Online Help The DELETE statement conflicted with the REFERENCE constraint "FK_ReportSchedule_Subscriptions". The conflict occurred in database "ReportServer", table "dbo.ReportSchedule", column 'SubscriptionID'. The statement has been terminated.

I solved the issue by manually deleting the schedule record from the ReportServer.dbo.ReportSchedule table.

The steps were the following:

1. Find the relevant subscription that needs to be deleted.

SELECT sub.SubscriptionID,
       rs.ScheduleID,
       SUB.[Description]
       ,SUB.EventType
       ,SUB.DeliveryExtension
       ,SUB.LastRunTime
FROM dbo.Subscriptions AS SUB
INNER JOIN dbo.Users AS USR ON SUB.OwnerID = USR.UserID
INNER JOIN dbo.ReportSchedule AS RS ON SUB.Report_OID = RS.ReportID
                            AND SUB.SubscriptionID = RS.SubscriptionID
WHERE SUB.Description like '%**Mail reciepient here**%'
ORDER BY sub.Description

2. Find the relevant schedule that prevents the record from being deleted.

SELECT [ScheduleID]
      ,[ReportID]
      ,[SubscriptionID]
      ,[ReportAction]
FROM [ReportServer].[dbo].[ReportSchedule]
WHERE SubscriptionID = '**GUID HERE**'

3. Backup the table

SELECT *
  INTO [ReportServer].[dbo].[ReportScheduleBackup]
  FROM [ReportServer].[dbo].[ReportSchedule]

4. Delete the schedule

DELETE FROM [ReportServer].[dbo].[ReportSchedule]
WHERE  ScheduleID ='**GUID HERE**'

The delete command deleted the schedule AND deleted the job from SQL instance that holds the RS databases. That job triggered the timed subscription.

After that, I could delete the report timed subscription from the reports web GUI.

My questions:

Why a manual deletion was needed in the first place ?
Why couldn't I delete the report subscription form the RS GUI? (http://REPORTSERVER_NAME/Reports/Pages/Subscriptions.aspx)


texts: Use of smallserial gives error with PostgreSQL

texts:
I know the very basics of databases. I worked in the past with MySQL and recently switched to PostgreSQL (9.1) with PostGIS 2.0.1.

At the moment I'm designing a new database and I use the tool pgModeler for that. In MySQL I could use autoincrement for my primary keys, but PostgreSQL doesn't have that. Some searching showed me that I should use serial instead.
There are some small lookup tables in my design which have a smallint as primary key. So I switched them to smallserial, which is an option in pgModeler.
Now I'm trying to execute my query and it returns with the error:
ERROR: type "smallserial" does not exist

According to the documentation I see now that smallserial is not listed in table 8.2 but later on in the document (8.1.4) it is mentioned. Is this an small error in the documentation or am I missing something like some setting somewhere?


texts: Can I somehow ignore uncomitted transactions which are causing the database to go into recovery?

texts:
I have a database which I have inherited which is just a collection of static data in seperate tables with no relations between tables. It is essentially just a number of tables which are the results of queries run against other databases. No-one else has access to the database. It turns out this hasn't been backed up.

A query was running over the weekend and something happened, perhaps a power cut. Now when I start SQL Server the database in question goes into recovery. Normally I would just leave this and let the recovery finish but in this case having left it a few hours I looked back and the estimated time to completion is 4.5 days!!!

Now, I'm not bothered about anything which happened as a result of the query which I ran over the weekend, I don't actually need the results of that anyhow now. All the query was doing was creating a new table, reading data from existing tables, no writing to existing tables. I can recreate almost everything in the database by running the original queries which will just take a few hours. My biggest problem is that I need access to the database, I can't wait 4.5 days!

Is there a way I can delete the transaction log, accepting that it won't recover whatever it was interruped over the weekend, so that SQL will stop trying to recover the database so I can carry on using the data within it?


texts: Testing Database Replication and timestamps are different

texts:
This may be a stupid question.

I've just setup database replication across two servers on different networks.

The Master is on MySQL v5.5.52 and Slave is on MySQL v5.7.21.

The replication all looks good, but when I manually checked the databases this morning to make sure it's pulling through accurately, I noticed the timestamps were different.

One of my tables has a column for the current timestamp, which on update is updated with NOW().

The master timestamp reads as 2018-02-01 16:43:23 the slave as 2018-02-01 22:43:23

Why would these be different? How can I make these match?


texts: PostgreSQL claims to support 170 of 179 SQL:2016 features, but it also lists 309 features of SQL:2016 that it doesn't support. Why the discrepancy?

texts:
As of September 2020, PostgreSQL claims to support most of the SQL:2016 standard, but the numbers in don't match up at all. From PostgreSQL's documentation:

As of the version 13 release in September 2020, PostgreSQL conforms to at least 170 of the 179 mandatory features for SQL:2016 Core conformance.
(emphasis mine)

So 9 features are unsupported. But elsewhere, PostgreSQL lists all the features of SQL:2016 that it doesn't support, and there's 309 features listed. This is more unsupported features than the first page claimed even exist in SQL:2016. Granted, some of the unsupported features are irrelevant, like "Embedded Ada" or "Module Language COBOL", but either way, there's way more than 9 unsupported features listed here. And I don't think that SQL:2016 changed so much (or even at all) between 2020 and 2021, so that can't be the reason for the mismatch.
I thought that individual unsupported features might be grouped together into 9 "features" somehow, e.g. all features involving support for dead languages are counted as one feature, and all XML-related features are counted as one feature, etc., but after skimming through the list of 309 unsupported features, I don't see any clear way to do this.
So, why the discrepancy? Is it because of individual features being grouped together, or is it something else?


texts: Why the WHERE clause is not pushed down in the view's query?

texts:
With Postgres 9.4, I'm doing the following query quite often:

SELECT DISTINCT ON(recipient) * FROM messages
LEFT JOIN identities ON messages.recipient = identities.name
WHERE timestamp BETWEEN timeA AND timeB
ORDER BY recipient, timestamp DESC;

So I decided to create a view:

CREATE VIEW myView AS SELECT DISTINCT ON(recipient) * FROM messages
LEFT JOIN identities ON messages.recipient = identities.name
ORDER BY recipient, timestamp DESC;

I just realized if I query my view like SELECT * FROM myView WHERE timestamp BETWEEN timeA AND timeB I get a significantly worse performance.

Doing EXPLAIN ANALYZE on both queries, I found out the reason is that the database in the second case brings up all the records, does the left join and then applies the WHERE clause. In other words, the WHERE clause is not pushed down into the view's query. I also tried to remove the ORDER BY from the view, but still the database performs the LEFT JOIN on full data rather on the filtered set.

What is the reason of this behavior? Is there a way I can get a comparable performance when using view?


texts: ORA-01722 Error when running stored procedure

texts:
I am trying to run a stored procedure however when I run it with an a valid Number it gives me an ORA-01722 error. I checked to make sure that the columns in the where clause where the parameter is used is of NUMBER type. I checked to make sure that the record exists. Note: When I take out c.charges-c.payment the query runs fine.

create procedure patient_InfoNew(vpatientID NUMBER) IS
x VARCHAR2(300);
BEGIN
 select a.firstname || a.lastname || ' ' ||  a.ssn || ' ' || a.streetname || ' ' || a.phonenumber || ' ' || b.servicedate || ' ' ||  b.servicetype || ' ' || c.charges - c.payment || ' ' || e.name || ' ' || e.insnumber into x
 from patient a, patientaccount b, patient_info c, patientinsurance d, insurance e
 where a.id = vpatientID  AND e.id = d.insuranceid AND a.id = b.id AND a.id = c.id AND a.id = d.id
AND b.servicedate =
   (Select MAX(servicedate)
    from patientaccount
    where id = vpatientID);

DBMS_OUTPUT.PUT_LINE(x);

EXCEPTION
    WHEN     VALUE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('Error '||SQLCODE||
SUBSTR(SQLERRM,1,80));
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('Divide by zero');
    WHEN OTHERS THEN
        IF SQL%NOTFOUND THEN
               DBMS_OUTPUT.PUT_LINE('No such record was found');
        END IF;
        DBMS_OUTPUT.PUT_LINE('Error '||SQLCODE ||
                        SUBSTR(SQLERRM,1,80));

END;

This is the error I get below:

No such record was found
Error -1722ORA-01722: invalid number
PL/SQL procedure successfully completed.


texts:
Just trying to wrap my head around the use of subqueries. Is it correct to say that they come in three main categories:

Scalar: returns a single value
Table-valued: Returns a table, to compare or add to the outer query.
Correlated: Cannot be run as a stand-alone, must make use of data from outer query in order to run, to be executed.

Is this correct, or are there more categories? Or some subdivisions within the ones I wrote? Of course, there is, I guess, a degree of idiosyncrasy here.


texts: What exactly is an unstructured data and why to use Non-relational DBMS for that?

texts:
I read some posts online about Unstructured data like this one.
It says

Unstructured data is information that is not arranged according to a pre-set data model or schema, and therefore cannot be stored in a traditional relational database or RDBMS.

But RDBMS allow BLOB storage. So we can store unstructured data in traditional RDBMS.
The examples that they have given for unstructured data are Email, Text files, Media files etc. First of all, how are all these types unstructured? And even if they are, how can a Non-relational DBMS like MongoDB store/retrieve it better than RDBMS' BLOB storage?
A common example for unstructured data that I found almost everywhere is storing products' information in a e-commerce company like Amazon. Every product has different attributes and so in a NoSQL DBMS like MongoDB we can have a collection called Products in which we will have all the products' information. But we can store this data using RDBMS by creating a separate table for each product. This will probably a much cleaner way to store the data. Then why to use a non-relational DBMS for that?
So my questions are:

What exactly is an unstructured data in the context of databases?
How is a non relational DBMS like MongoDB better for unstructured
data than a RDBMS?
Can you give a real life example of unstructured data?

Thank you!


texts: Get starting and ending balance, with total credit and debit transactions

texts:
I have a table that contain transactions like this:

I get the required Data by using scalar function, but it is executing very long time to get the query results. Can any one help me how to get the required data faster?

I read it can be done using a table function, but I didn't know how to or how to use a join with the same table.

For context, TransactionType 1 = debit and TransactionType 2 = credit.

Here is the function I created:

ALTER FUNCTION [dbo].[CardBalanceByDate_FN]
(
    @studentID int ,
    @CurrancyID int,
    @BalanceDate Date
)
RETURNS decimal(18, 2)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result decimal(18, 2)

    -- Add the T-SQL statements to compute the return value here

    select @Result =  ISNULL((((dbo.ProfileCardAmountByDate_FN(@studentID,@CurrancyID,1,@BalanceDate) + (dbo.ProfileCardAmountByDate_FN(@studentID,@CurrancyID,3,@BalanceDate))) )-(dbo.ProfileCardAmountByDate_FN(@studentID,@CurrancyID,2,@BalanceDate))) ,0)

    --******************************************************************************************

    -- Return the result of the function
    RETURN ISNULL(@Result,0)

END

and

ALTER FUNCTION [dbo].[PeriodTotalTransactions]
(
    -- Add the parameters for the function here
    @studentID int,
    @CurrancyID int,
    @TransactionTypeID int,
    @StarteDate Date,
    @EndDate Date
)
RETURNS decimal(18, 2)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result decimal(18, 2)

    -- Add the T-SQL statements to compute the return value here

    select @Result =  ISNULL(SUM(TransactionAmount) ,0)
    FROM     dbo.Student_Card_Transaction CT
    WHERE   CT.StudentID=@studentID
    and ct.IsDeleted=0
    and CONVERT(date, ct.TransactionDate) between @StarteDate  and DATEADD("day", 1, @EndDate)
    and ct.CurrencyID=@CurrancyID
    and ct.TransactionTypeID=@TransactionTypeID

--******************************************************************************************

    -- Return the result of the function
    RETURN ISNULL(@Result,0)

END

and the select query is

SELECT sp.studentcode,
       sp.studentnameen,
       sp.flag,
       dbo.Cardbalancebydate_fn(sp.serial, 1, '2017-12-15')
       StartBalance,
       dbo.Periodtotaltransactions(sp.serial, 1, 2, '2017-12-15', '2018-01-15')
       DebitTransaction,
(
dbo.Periodtotaltransactions(sp.serial, 1, 1, '2017-12-15', '2018-01-15')
+ dbo.Periodtotaltransactions(sp.serial, 1, 3, '2017-12-15', '2018-01-15') ) CreditTransaction,
dbo.Cardbalancebydate_fn(sp.serial, 1, '2018-01-15')
EndBalance
FROM   student_profile sp,
student_cards SC
WHERE  sp.serial = sc.studentid
AND sp.isdeleted = 0
AND sc.isdeleted = 0

the transaction Table is

CREATE TABLE [dbo].[Student_Card_Transaction](
    [Serial] [int] IDENTITY(1,1) NOT NULL,
    [StudentID] [int] NOT NULL,
    [CardID] [int] NOT NULL,
    [TransactionTypeID] [int] NOT NULL,
    [CurrencyID] [int] NOT NULL,
    [TransactionAmount] [decimal](18, 2) NOT NULL,
    [TransactionDate] [datetime] NULL,
    [UserID] [int] NULL,
    [Notes] [nvarchar](2000) NULL,
    [IsDeleted] [bit] NOT NULL CONSTRAINT
)

and the table involved

CREATE TABLE [dbo].[Student_Profile](
    [Serial] [int] IDENTITY(1,1) NOT NULL,
    [StudentCode] [nvarchar](20) NOT NULL,
    [StudentNameAR] [nvarchar](500) NOT NULL,
    [StudentNameEN] [nvarchar](500) NOT NULL,
    [StudentImagePath] [nvarchar](2000) NULL,
    [StudentEmail] [nvarchar](1000) NULL,
    [Flag] [int] NOT NULL,
    [IsDeleted]
)


texts: How to list all the inherited tables from the parent table?

texts:
I have 3 tables (janitor, security & manager) which inherited from the user table.

What is the query can be used to produce the following result(list all the name of the child tables)?

Thank you. Cheer :)


texts: Where is this mystery trace going (SQL Server 2000)?

texts:
We had a sudden disk usage explosion this morning and the SQL monitor has no useful query information. I decided to check on any potential traces that could have captured something.

SELECT * FROM :: fn_trace_getinfo(default)

gives me

traceid     property    value
-------     -------     ------
1           1           1
1           2           NULL
1           3           5
1           4           NULL
1           5           1

Property #2 should be the file name to which the trace is going but, as you can tell, it's NULL.

It's running, and just to confirm, I did this which confirms that the trace is running, has no stop condition, grows max file size of 5MB (default).

Is there a way to determine the destination of this trace, or is it a known location and my Google-FU is broken?


texts: Is it possible to have extra tables in a Slave with MySQL Replication

texts:
As my title mention I have a Master and a Slave database.

Master if for operations data and my slave mainly for reporting stuff.

The issue is that I need to create extra tables on reporting that can't be on the master, but the way my replication is set (the simplest one mentioned by the official doc)  at the moment, this breaks the replication system.

How could I add tables on the Slave without Master caring about it ? Is it even possible ?


texts: Setting up AlwaysONAG in SQL server 2016 on windows 2016

texts:
I am going to set up AG's for the very first time, so with little or no experience, please guide me on below understanding:

If i am setting up 4 node AOAG with below scenario:

Node1-NY Data Center - Primary replica

Node2-NY Data Center - Secondary replica

Node1---Node2 Synchronous mode

Node 3 - Michigan Data Center secondary replica acting as DR site with different subnet with ASYNC mode

Node 4 -- Readable secondary in NJ data center for reporting purpose using ASYNC mode

Doubts/Questions:

In above Multi Subnet configuration can i still use a different storage across 4 nodes or do i need to use SAN replication as i have read few blogs and confused if that is a requirement for multi site env
What should be ideal Quorum setup? -- Can i have that in NJ site as file share witness?

Also if there is better design than above, please suggest as i have never implemented or worked on AOAG before.

RPO/RTO for critical apps varies between 2 hrs to 4 hours

While for mid critical its between a day and 2

I can add up information as needed.

Thanks


texts: Should I use a surrogate key in a mapping table?

texts:
I am deploying a mapping table which connects Items to ItemGroups

Though an Item will only ever be in a single ItemGroup please accept a priori it is not appropriate in my use case to append a column to the Items table.

So a table will be created with two columns:

ItemId INT NOT NULL
ItemGroupId INT NOT NULL

It is tempting to make ItemId the CI and PK for this table.... but is that appropriate? I think it makes sense as a primary key but since it is neither sequential nor perpetually-increasing it seems incorrect as a CI. I will never execute range queries against ItemId

... But a surrogate key solely for the purpose of a sequential CI also doesn't seem appropriate.

I am equally likely to execute either:

SELECT [ItemGroupId] FROM [Table] WHERE [ItemId] = @id
SELECT [ItemId] FROM [Table] WHERE [ItemGroupId] = @id

So as far as I can tell I have  few options:

ItemId is both the CI and the PK
ItemId is not the CI, but it is the PK
ItemId is neither the CI nor the PK but has a UNIQUE constraint
ItemId, ItemGroupId combination becomes the PK and ItemId has a UNIQUE constraint


texts: selecting more fields causes catastrophic failure

texts:
I have a connection to a linked server from sql server 2012 to pervasive sql.

When I do select field1, field2, field3 from mytable everything works!

However, when I do select field1, field2, field3, field4, ...field10+ from mytable I get this erro message:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "KSLAP208" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "KSLAP208".

I looked at the logs and this is what it says:

2013-02-08 11:27:30.93 spid55      ***Stack Dump being sent to D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\SQLDump0049.txt
2013-02-08 11:27:30.93 spid55      SqlDumpExceptionHandler: Process 55 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2013-02-08 11:27:30.93 spid55      * *******************************************************************************
2013-02-08 11:27:30.93 spid55      *
2013-02-08 11:27:30.93 spid55      * BEGIN STACK DUMP:
2013-02-08 11:27:30.93 spid55      *   02/08/13 11:27:30 spid 55
2013-02-08 11:27:30.93 spid55      *
2013-02-08 11:27:30.93 spid55      *
2013-02-08 11:27:30.93 spid55      *   Exception Address = 00000000018F5BBE Module(UNKNOWN+0000000000000000)
2013-02-08 11:27:30.93 spid55      *   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION
2013-02-08 11:27:30.93 spid55      *   Access Violation occurred reading address 0000000009400090
2013-02-08 11:27:30.93 spid55      * Input Buffer 128 bytes -
2013-02-08 11:27:30.93 spid55      *             select * from [KSLAP208].[C003]..PA_Profile_BASE_1119

I am able to do this same exact select * from a different server without problems!

What's happening here? Is this some kind of 2012 bug or a memory issue?


texts: Okay to merge included columns on otherwise identical indexes?

texts:
I have two indexes that index the same field on the same table. The only difference between them is the included columns.

There's no reason I shouldn't combine the included columns and just have one index, right?


texts: Can I bring a warm standby SQL Server database online and then return to standby mode?

texts:
What I'm trying to do falls outside of the normal SQL Server work flow so might be impossible.

I'm implementing a system that serves out copies of a large production database to multiple test / dev / training systems.  We're using a differencing-based file system (the Actifio implementation of ZFS) that allows these copies to be created quickly with low disk space overhead.  That system would normally be connected to a production database but that's making people nervous organisationally so I've been asked to operate from a warm standby database.

My problem is that Actifio can't see the standby database (it seems to use vsswriter to iterate databases and the standby database doesn't appear in the list that vsswriter iterates). The database is in standby read-only mode.

I can bring the warm standby database online so that Actifio can take an image but I then need to put it back to standby mode to continue applying logs and I can't find a reference that describes how to do this (or confirms that it's impossible)

Deleting the database and creating a new warm standby copy of production isn't an option as that disrupts the differencing engine's view of storage changes forcing a new full copy to be taken.  To put it another way, this will work but destroys the value of the ZFS-based disk space change tracking that allows us to hand out copies of this database with very low disk overhead.

Can I bring a warm standby database online temporarily and then return to standby mode and continue applying logs?  The database version is SQL Server 2008 R2


texts: How to clean every non-digit except the + at beginning of string with regexp_replace in Postgresql?

texts:
Related to a comment, I would like to find a way to clean with regexp_replace in PostgreSQL any non-digit except the + at beginning of the string.

For example +(7)9125415501 shall become +79125415501


texts: Full text index for a column that stores json which contains guid - Is this a good idea, space-wise?

texts:
I'm considering adding a full text index for a column whose values are json strings. These jsons usually contain Guid (or UUID) values.
The index goal is to allow fast search over both "normal" words (e.g., some user generated text) and guids.
Some more info about this column, called RequestContent:

I actually have another column, ResponseContent, which the following applies for as well, so that I presume that an answer for this question would apply for it as well.

This column is essentially used as a log for HTTP Post requests' body, so that each incoming POST request body is logged into that column. There are hundreds of web services whose requests bodies are logged, and there is no relationship between two different web services' requests.
For example, one request could look like:
{ "orderId": "607bbc9b-9c0e-4921-b5c3-55f68891e619", "status": 0, "createdOn": 16011560804216 ... }

While another could look like:
{ "claimStatus": 1, "actionStatus": 8, "senderId": "807acc23-9dde-4aaa-b5c3-55df5431e619", "documents": [ { "documentTitle": "some free text" }] ... }

Any json may contain fields of any kind: number, short string fields (like firstName, or Guids, etc.) or long string fields (usually containing hundreds of words of free text, but sometimes even large Base64 encoded data), boolean, nested objects, arrays. If it matters, it would be safe to say that most requests (I'd say 80%) contain number and short string fields.
This column's type is ntext (yes, it's old), but I can recreate it as any other type that I'd like.
Is it a good idea to build a full text index for this column? My concern is that the index would contain a row for each unique Guid, which would result in consuming an unfeasible disk space.
EDIT:
@David Spillett asked:

Could you extract the parts that you want indexed into a persisted
computed column that you can then add a full text index for?

It's technically feasible, but I'm looking for an answer for the current use case.


texts: PostgreSQL update table then insert on another table

texts:
I have a User table and a Log table. I need to update a field on the User table and insert a new entry on the Log table recording the update. I have written the statement for the update:
  UPDATE users SET free_shipping_until = spu.created_at::date + interval '1 year'
  FROM shipping_priority_users AS spu
  WHERE spu.role_id = #{role.id} and users.id = spu.user_id and spu.created_at is not null;

For each update I need to also add (probably in a transaction) an insert statement on the Log table which has the following columns
user_id: string,
created_at: timestamp
data: jsonb

The data column contains a jsonb value including the free_shipping_until value from the update.

data: {"free_shipping_until": [null, "2021-07-30"]}

user_id should match the updated record's value
The created_at column is the current time, I'm using RoR and could interpolate the value with the expected format.

I'm using PostgreSQL 12.3


texts: should I select columns before or after join?

texts:
I have a join that takes a lot of time:

select t1.*, t.b from t1 join (select id, b from t2) t on t1.id = t.id

I have an index on id in t2.

Is index on id preserved when I create a derived table t?
In terms of performance, do I need to select from t2, or would this be always as fast:

select t1.*, t.b from t1 join t2 t on t1.id = t.id ?


texts: How to query an Extended Events target file on a SQL Azure Managed Instance by using T-SQL?

texts:
I'm setting up an Extended Events trace in a Managed Instance (NOT Azure SQL DB) for the first time.
These are the steps I've carefully followed:

Created a Master key on my Database.
Created a Database Scope Credential by using the correct Shared Access Signature.
Created and started a trace. This works and everything gets traced as expected.

Now, I'm trying to query the XEL file by using T-SQL:
SELECT event_data FROM sys.fn_xe_file_target_read_file (N'https://whatever.blob.core.windows.net/xevents/MyTrace_132410674100570000.xel',NULL, NULL, NULL);

However, I get two different behaviors:

From SSMS: I just get zero rows and no error or messages displayed.

From Azure DataStudio: I get the following error message

Msg 300, Level 14, State 1, Line 1 VIEW SERVER STATE permission was
denied on object 'server', database 'master'. Msg 297, Level 16, State
1, Line 1 The user does not have permission to perform this action.

Some facts:

I'm able to download the XEL file by using Azure Storage Explorer.
I have already checked the full path to the XEL file and it's correct. I can download the files directly by using a browser.
The access level of the container is Public.
I've tried with different files (running and stopped traces, all of them with data).
The XEL files are full of data since I query them locally and I can open them by using SSMS.
I really don't think there's a missing key or scoped credential. Otherwise, the tracing would not work. Anyway, I dropped everything and started again from scratch and the behavior did not change.
All my tests were carried out by using the managed instance administrator account.

So...

Is reading XEL files really supported on Managed Instances?
Is there any cath on how to access the files by using the sys.fn_xe_file_target_read_file function?


texts: Availability Strategies in MS SQL Server 2008

texts:
I need some assistance in developing a backup strategy for my SQL Server deployments. Currently I have two classes of customer: Hosted customers, whose data is housed in our datacenter, and On Premises customers who host their own data locally.

We are currently exploring whether to use Log Shipping or Database Mirroring for our Hosted customers as data loss of up to 15 minutes is acceptable (Log shipping interval would be 15 minutes, Mirroring would be in High Performance Mode), but we are still conducting testing on that.

My question is, if we choose a Mirroring strategy for our hosted customers, I doubt we would be able to do that with our our OnPrem customers - they will likely be using log shipping. Is it possible to have multiple recovery strategies database-by-database, or would I need a new instance of SQL Server for the OnPrem customers?

EDIT - Changed title to "Availability Strategies" because, well, this is more about availability than recovery.


texts: Push replicas from master to slave in postgresql

texts:
I have a network topology where the master serves as a read/write database in one network, and has several slaves in different networks that are read-only for security reasons. Due to firewall restrictions, the slaves cannot establish a connection to the network the master resides in.

Is there a mechanism that allows the master to replicate itself to the slaves by only pushing out to the slaves? After reading a bit about replication in psql, it seems that the slaves need network access to the master.


texts:
Scenario:
Windows Server 2012R2 & SQL Server 2014 SP2, no CUs FCI adding a non-default port and keeping the default port

I made the change on both nodes via SQL Configuration Manager, however, neither failover or complete resource restart allows a connection to be made on the new port.  We have tested connection to the port with the application, telnet and PoSH (Test-NetConnection) to no avail.  The SysInternals PortMon utility does not show SQL Server listening on the added port.  I did confirm the port shows in the registry on both nodes.

Edit:
A huge detail I should have listed but forgot to.  When the service restarts, there is not indication that it is listening on the new port, just the default.

What did I miss or what else do I need to check?

Obviously, I cannot test any changes without some notice.

Thank you in advance!


texts: Is it possible to use Oracle XMLTABLE and %type column type selector?

texts:
I am creating a stored procedure which gets an xml input and inserts the data to a table using XMLTABLE.

I want to know how can i specify a field type according to a table column type when passing columns in XMLTABLE. (using something like %type).

Here is my simplified code:

SELECT tmp.*
FROM (SELECT XMLTYPE(xml_in) AS xml FROM dual),
      XMLTable('for $i in /rowset/row return $i'
                PASSING xml COLUMNS
                  name MYTABLE.name%TYPE path 'name', // %TYPE just doesnt work here :(
                  family MYTABLE.family%TYPE path 'family'
              ) tmp


texts: Mongodb 2.6 Server crash and not restarting

texts:
We are using mongodb 2.6 with one Primary and 2 secondaries. One of the secondary has gone bad  and not restarting.

2014-04-18T21:13:34.765+0000 [rsSync] replSet still syncing, not yet to minValid optime 535185c4:fe
2014-04-18T21:13:34.766+0000 [rsBackgroundSync] replset setting syncSourceFeedback to server134:27017
2014-04-18T21:13:34.880+0000 [rsSync] replSet SECONDARY
2014-04-18T21:13:34.972+0000 [rsSync] SEVERE: Invalid access at address: 0x7f9694fdc13c
2014-04-18T21:13:34.982+0000 [rsSync] SEVERE: Got signal: 7 (Bus error).
Backtrace:0x11bd301 0x11bc6de 0x11bc7cf 0x7fb08d53fcb0 0xf1d43a 0x8aef63 0xf0e4a7 0xf14efe 0xf14f6f 0xf1e02f 0xf1d9c0 0x8afd8f 0x8b1c5a 0xe42251 0xe9b0eb 0xea52e4 0xea5c6a 0xea5da0 0xea6098 0x1201c99
 /usr/bin/mongod(_ZN5mongo15printStackTraceERSo+0x21) [0x11bd301]
 /usr/bin/mongod() [0x11bc6de]
 /usr/bin/mongod() [0x11bc7cf]
 /lib/x86_64-linux-gnu/libpthread.so.0(+0xfcb0) [0x7fb08d53fcb0]
 /usr/bin/mongod(_ZN5mongo17RecordStoreV1Base12deleteRecordERKNS_7DiskLocE+0xea) [0xf1d43a]
 /usr/bin/mongod(_ZN5mongo10Collection14deleteDocumentERKNS_7DiskLocEbbPNS_7BSONObjE+0x633) [0x8aef63]
 /usr/bin/mongod(_ZN5mongo16NamespaceDetails11cappedAllocEPNS_10CollectionERKNS_10StringDataEi+0x267) [0xf0e4a7]
 /usr/bin/mongod(_ZN5mongo16NamespaceDetails6_allocEPNS_10CollectionERKNS_10StringDataEi+0x2e) [0xf14efe]
 /usr/bin/mongod(_ZN5mongo16NamespaceDetails5allocEPNS_10CollectionERKNS_10StringDataEi+0x5f) [0xf14f6f]
 /usr/bin/mongod(_ZN5mongo19CappedRecordStoreV111allocRecordEii+0x3f) [0xf1e02f]
 /usr/bin/mongod(_ZN5mongo17RecordStoreV1Base12insertRecordEPKcii+0x60) [0xf1d9c0]
 /usr/bin/mongod(_ZN5mongo10Collection15_insertDocumentERKNS_7BSONObjEbPKNS_16PregeneratedKeysE+0x7f) [0x8afd8f]
 /usr/bin/mongod(_ZN5mongo10Collection14insertDocumentERKNS_7BSONObjEbPKNS_16PregeneratedKeysE+0x22a) [0x8b1c5a]
 /usr/bin/mongod(_ZN5mongo11_logOpObjRSERKNS_7BSONObjE+0x341) [0xe42251]
 /usr/bin/mongod(_ZN5mongo7replset8SyncTail15applyOpsToOplogEPSt5dequeINS_7BSONObjESaIS3_EE+0x4b) [0xe9b0eb]
 /usr/bin/mongod(_ZN5mongo7replset8SyncTail16oplogApplicationEv+0x4f4) [0xea52e4]
 /usr/bin/mongod(_ZN5mongo11ReplSetImpl11_syncThreadEv+0x14a) [0xea5c6a]
 /usr/bin/mongod(_ZN5mongo11ReplSetImpl10syncThreadEv+0x30) [0xea5da0]
 /usr/bin/mongod(_ZN5mongo15startSyncThreadEv+0xa8) [0xea6098]
 /usr/bin/mongod() [0x1201c99]

Is there a way by which we can start the secondry to work or workaround this bug?


texts: MySQL 5.7 Replication failover in Master-Master configuration in RHEL

texts:
Asynchronous Replication Scenario ( Binary Logging Method) :

                         Primary Node <-----> Secondary Node

Write is enabled on Primary node and Read is enabled on both node.

Problem :

     How to do automatic switching once Primary node went down i.e. all write events will point to Secondary Node ? Can it be done by Shell scripting

Solution i tried :

   1. Automatic switching via JBOSS . but we suspect that during switch some records may get loss

Solutions i haven't tried yet  :

   1. DBVIP : - I haven't tried this as per https://dba.stackexchange.com/questions/24549/how-do-i-switch-master-with-slave-and-vice-versa-on-mysql mentioned by Rolando. I haven't tried this because i don't have DBVIP.

   2. MySQLFailOver utilities : Because i am not using GTID replication

   3. I haven't use any 3rd party tool like HA proxy because of additional overhead.

Question :

Which is the best possible method for auto switching ?
Also , during switch over , can we have a loss for some events ?


texts: Time to generate execution plan for select into from a view takes significantly more time than select

texts:
I have a view, vw_example, that is quite complicated and has multiple joins across multiple databases. This view has been causing significant delays when used as part of other queries, and we've narrowed down the problem to execution plan generation.
One thing we've noticed that has vexed us is that this query takes a few seconds to run (and display all the data, ~44k rows):
SELECT * FROM vw_example

whereas this query takes minutes to run (tbl_example is created by the query):
SELECT * INTO tbl_example FROM vw_example

We've additionally compared the SQL execution plans between the two in Microsoft SQL Server Management Studio (2014). The former plan took 3 seconds to generate. The latter plan took 37 minutes.
Comparing the two plans, the only difference is that the latter plan has a "Table Insert" node at the start. Everything else is identical.
Does anyone have any idea why it would take significantly longer to generate a plan for the SELECT INTO statement vs a SELECT statement?


texts: Howto preserve statistics when switching partitions of partitioned tables

texts:
Given are two partitioned tables T1 and T2 with no automatic statistics (their statistics where updated WITH NORECOMPUTE option).
T1 has only partition N populated and T2 has partition N empty. Now I need to switch partition X between T1 and T2.

With SQL Server 2008 statistics where unmergeable and would have to be recreated for T2 (T1 is empty after switching in my example).

SQL Server 2014 allows this but requires equal sampling rates for T1 and T2.

But how can I prevent the optimizer from changing the sampling rate which I provide to the UPDATE STATISTICS command?
Is the optimized sampling rate change maybe guaranteed to be small
enough to never disrupt merging statitcs?
Is at least a FULLSCAN (i.e. 100% sampling rate) guaranteed to not be
altered by the optimizer?

To my understanding the RESAMPLE keyword does not help me as it would require an update of statistics after SWITCHing (even though only for the new partition which is a great improvement over SQL Server 2008). One of the reasons for me to SWITCH is to never have T2 in maintainance mode where the user can not access it. In other words, I prepare the readonly data for the user in T1 but the user only accesses T2.


texts:
I have an Amazon RDS instance that is producing unwanted NOTICEs.
I have never seen this behaviour before.

mydb=> select current_setting('client_min_messages');
 current_setting
-----------------
 notice
(1 row)

Now, if I ask for geometry column... I get the NOTICEs:

mydb=> SELECT id, ST_AsText(geom) FROM mytable limit 1;

NOTICE:  [lwgeom_ogc.c:LWGEOM_asText:833] Called.
NOTICE:  [g_serialized.c:gserialized_get_type:50] entered
NOTICE:  [g_serialized.c:lwgeom_from_gserialized:1137] Got type 1 (Point), srid=4326
NOTICE:  [g_serialized.c:lwgeom_from_gserialized_buffer:1091] Got type 1 (Point), hasz=0 hasm=0 geodetic=0 hasbox=0
NOTICE:  [g_serialized.c:gserialized_get_type:50] entered
NOTICE:  [lwgeom.c:lwgeom_set_srid:1455] entered with srid=4326
NOTICE:  [lwout_wkt.c:lwgeom_to_wkt_sb:590] lwgeom_to_wkt_sb: type Point, hasz 1, hasm 0
NOTICE:  [lwgeom_ogc.c:LWGEOM_asText:841] WKT size = 33, WKT length = 32
  id   |            st_astext
-------+----------------------------------
 12445 | POINT(-119.67608831 48.15767852)
(1 row)

Setting client_min_messages up one level will suppress then, but then it obviously spoils NOTICEs for all.

mydb=> SET client_min_messages=WARNING;
SET
mydb=> SELECT id, ST_AsText(geom) FROM mytable limit 1;
  id   |            st_astext
-------+----------------------------------
 12445 | POINT(-119.67608831 48.15767852)
(1 row)

What's going on here? My local and custom installed versions of Postgres never acted this way.

Update: This seems to be isolated to Postgres 9.4.4 - 9.4.1 does not raise these.


texts:
I have two servers set up in Master-Master with one additional Slave connected to the first master. Lately I've started getting alerts from monyog that Master 1 has a seconds_behind_master value of 1109. Oddly enough, it's ALWAYS 1109. If I log into the server and just do show slave status\G over and over, every 10-15 seconds it'll show 1109 in seconds_behind_master and then immediately goes back to 0. Then 10-15 seconds later it'll go back to 1109, then back to 0.

I found this article about the issue I'm experiencing, Unfortunately the resolution was unrelated to what's happening on my server. I did show slave status until I got it to show 1109 again, then I looked at the relay log position, looked it up in the relay log and checked all events for several seconds before and after and all events came from master 2, as expected. All of the timestamps were in order, too.

Any ideas?


texts:
I have an Sql servers of 2008/2012. Some users run very expensive queries, such as updating 1G rows in one transaction or cross join several very big tables, and it causes the server no response from time to time.

What's the good way to trace/trigger the server to save the running queries when it happens?


texts: SQL Server 2012 Replication Backup and Restore

texts:
I am using SQL Server 2012. We have setup a replication using 'transactional with queued updating'.

If the publisher database crashes and some records in the subscriber database have not been replicated to the publisher database (and in the publisher database backup), how would we have to re-sync the data after restoring the publisher database and setting up replication without losing the new data in the subscriber database?

(And, also apply to Vice Versa case)

Thanks.


texts: Cannot login to postgres with newly created users

texts:
I have created a number of users in postgres using createuser --interactive --password but whenever I run sudo -i -u newuser I receive the following error:

sudo: unknown user: staging
sudo: unable to initialize policy plugin

I then checked the user list when using the default postgres user to see if there were in fact any other users. It returned the following:

List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 newuser   | Superuser, Create role, Create DB              | {}
 newuser2  | Superuser, Create role, Create DB              | {}
 newuser3  | Superuser, Create role, Create DB, Replication | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

So I know that they are there. Why is it failing?

My end goal is to use this local db as my database for my deployed server. They are both installed on the same vm and I just want the REST calls from my server to interact with the local database, which I assume should be no problem, if I could ever connect to it.


texts:
Possible Duplicate:
  Why is My SQL Server Transaction Log Growing or Running out of Space?

The transaction log in one of my client's databases is 16GB, when the database itself is only 2MB. I have begun backups of the transaction log and they occur every half an hour.

I have attempted to run DBCC SHRINKFILE (Log,size) but it says all virtual pages are in use.

Does this mean there is an uncommited transaction or something? The file has been this big for a while now. Am I able to somehow truncate the log?


texts:
I have a SQL Server database with a full text index. When I created the app that uses it, I made a backup of the database and I ship the .BAK file with the app. As part of the app install it does a restore of this .BAK file. I like this option because it auto-creates all of the tables for me, with the default rows some tables need, and includes the full text index as well. Nice and simple.

Now I need to add a backup/restore feature, and the end-user wants it to work more like an MS Office app than a client/server app.  He thinks of it as a file, and just wants to backup to a file, and then restore whenever he wants... not your typical SQL Server backup, and not automated either, just simple "copy" backup, no appends/backup sets/etc.

I'm thinking of using BACKUP DATABASE TSQL command, and he doesn't mind that it only saves to a folder on the server. Then restore database TSQL command to restore -- what I currently use for my initial install.

I ran in to one problem though, and this is my question. I have a version number that I keep in a row in a table that I use to avoid old apps connecting to new databases and vice-versa.  I'd like to check this version before allowing the restore. I know if it were an .MDF file I could attach to it and check before restoring, but can I do this with a backup (.BAK) file?

Am I missing a better backup/restore method that would work better for my design? For example, should I detach the .MDF file and copy it and the log file, as the backup, and then attach it as the "restore" process? Will that preserve everything the way backup/restore does, including the full text index?

I'm using SQL Server 2008 Express and my app is C# .NET 3.5.

Edit: Backup does not need to happen while other users are in the database, it is fine if exclusive access is required, it is just a small office with a few people that can easily shutdown if needed.


texts: SQL Server 2016 - High page writes, but normal/low everything else

texts:
I am a sysadmin and I am not very well versed in SQL Server metrics. I have been having issues with one of our DB's and the DBA doesn't seem too knowledgeable about measuring performance, so I'm trying to determine why we are having these issues.

This is a DB with 134 GB data file and 25 GB log file currently. The DB is encrypted at rest (TDE) and uses a key stored in Azure KeyVault (same region as the server). It is running in replication mode and replicating to a single SQL Server instance. The main instance runs on an Azure VM (8 vCPU, 56 GB RAM) and replicates to a backup instance in Azure SQL.

The main issue we are having is Page Writes/sec. It is currently always around 250 writes/sec or above, and rarely drops below 200. I have read that 90 writes/sec is a normal threshold. Sometimes, stored procedure executions can increase from a few milliseconds to half a second or more, severely affecting our app performance.

Meanwhile, the PLE is 60,766 secs, the Buffer Cache Hit Ratio is 100%, and Page Reads/sec are at 0.2/sec. So this is most likely not a memory issue, right?

So now what is the best way to determine what is causing the largest number of page writes on an ongoing basis? How can we know if it's simply poor design, code needing optimization, or some other issue?


texts: Storage size for varchar length in Redshift

texts:
So, a lot of databases will store the length prefix for a varchar field in 1 byte if the length is less than 255, and 2 bytes if it is more. For example, the MySQL docs say:

  In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

I'm wondering if the same thing is true for Redshift? I've scoured the docs, but can't find it for the life of me!

Incidentally, I ask the question because a coworker built our entire data warehouse using varchar(256) for most fields - all the restrictions of short field length, none of the benefits of 1-byte length storage (unless Redshift uses 2 bytes regardless).


texts: How mysql innodb know which transaction should rollback after crash restart?

texts: I know when I execute dml sql before commit , innodb will write the result to the innodb page cache which can flush to data page and write to the undo log file ; but at the same time , mysql crash and restart, how mysql innodb which transaction should rollback ?


texts: MVCC in PostgreSQL: visible tuple with xmin > current txid

texts:
I'm trying to better understand MVCC and am confused by the following (contrived) scenario. I was under the impression that for a row to be visible to a transaction, the transaction's id must be greater than the tuple's xmin value.

create table test (id int);

(session 1)
begin;
select txid_current();
┌──────────────┐
│ txid_current │
├──────────────┤
│          704 │
└──────────────┘

(session 2)
begin;
insert into test values (1);
commit;

(session 1)
select xmin, xmax, id from test;
┌──────┬──────┬────┐
│ xmin │ xmax │ id │
├──────┼──────┼────┤
│  705 │    0 │  1 │
└──────┴──────┴────┘

How can a transaction with an id of 704 see a tuple with an xmin of 705?

Update:

Reading http://www.interdb.jp/pg/pgsql05.html helped clarify things a lot more for me. A few videos I had previously watched seemed to imply that, as a hard rule, a tuple is only visible if its xmin is less than the current transaction's txid.

However, the above reading painted a slightly different picture for me. In an isolation level of READ COMMITTED, a transaction gets a new snapshot of the state of other transactions at the start of each query.

The first query in session 1 would get a snapshot of 704:704: (you can see this by running select txid_current_snapshot()) which means that from the point of view of txid 704, all txids < 704 are inactive (committed or aborted) and all transactions >= 704 are active (in progress or not yet started).

The second query of session 1 would get a new snapshot of 704:706: which means that from the point of view of txid 704, all txids < 704 are inactive, and all txids >= 706 are active. However, since txid 705 fits in between these values, and since it has in fact been committed, it too is part of the new snapshot that txid 704 received, and is visible. Therefore, it is not a hard rule that a tuple is only visible if its xmin is less than the current transaction's txid.


texts: SQL Server 2008 - Run the Check Database Integrity Task with the PHYSICAL_ONLY option?

texts:
As part of our nightly maintenance, we include the Check Database Integrity Task which is an encapsulation of the DBCC CHECKDB command. It's beginning to take a considerable amount of time due to the size of our database, and after reading up on the command at MSDN, we decided to take their advice:

  Therefore, using the PHYSICAL_ONLY option may cause a much shorter run-time for DBCC CHECKDB on large databases and is recommended for frequent use on production systems. We still recommend that a full run of DBCC CHECKDB be performed periodically. The frequency of these runs depends on factors specific to individual businesses and production environments.

So we want to change our plan to run DBCC CHECKDB with the PHYSICAL_ONLY option every night, and then without it once a week. Is there any way to get SQL Server to include the PHYSICAL_ONLY option in the integrity task? Can I somehow modify the T-SQL produced by the task? I can't figure out how to include the option as part of the maintenance plan.


texts: PostgreSQL update and delete property from JSONB column

texts:
From this article, I tried to update or delete property of a JSONB column:

CREATE TABLE xxx (id BIGSERIAL, data JSONB);
INSERT INTO xxx(data) VALUES( '{"a":1,"b":2}' );
SELECT * FROM data;
 id |       data
----+------------------
  1 | {"a": 1, "b": 2}

create the update function:

CREATE FUNCTION jsonb_merge(JSONB, JSONB)
RETURNS JSONB AS $$
WITH json_union AS (
    SELECT * FROM JSONB_EACH($1)
    UNION ALL
    SELECT * FROM JSONB_EACH($2)
) SELECT JSON_OBJECT_AGG(key, value)::JSONB FROM json_union;
$$ LANGUAGE SQL;

testing:

-- replace
UPDATE xxx SET data = jsonb_merge(data,'{"b":3}') WHERE id = 1;
SELECT * FROM xxx;
 id |       data
----+------------------
  1 | {"a": 1, "b": 3}

-- append
UPDATE xxx SET data = jsonb_merge(data,'{"c":4}') WHERE id = 1;
SELECT * FROM xxx;
 id |           data
----+-------------------------
  1 | {"a": 1, "b": 3, "c": 4}

The question is:

is there any drawback of using JSONB_EACH (jsonb_merge) instead of JSONB_EACH_TEXT (from the article) in this case?
how to modify the jsonb_merge so if the second parameter property value is null (something like {"b":null}) the value would be erased?

.

-- remove
UPDATE xxx SET data = jsonb_merge(data,'{"b":null}') WHERE id = 1;
SELECT * FROM xxx;
 id |       data
----+-----------------
  1 | {"a": 1, "c": 4}


texts: Can PostgreSQL use multiple partial indexes per query?

texts:
I have read that PostgreSQL can use multiple indexes in general, but in the specific case of a query that ranges across two indexes, will it use both?  If so, will they be loaded sequentially or together?

For example, if this query spans across two partial indexes by column_1, how will partial indexes be used if at all, and how is the index data loaded and discarded:

SELECT 1 FROM sample_table WHERE column_1 > 50 AND column_2 < 50000


texts: SMSS will start for one user, but not another?

texts:
We have a SQL Server 2012 and SMSS 2012 installed.  The C:\ recently ran out of space due to a misconfiguration, and now we have 1 user who cannot start SMSS they receive the error:

"Cannot find one or more components. Please reinstall application"

However when I login with my domain admin account, I can start SMSS perfectly fine.  We are both in the server Administrator group.  I have also "repaired" the SQL Server tools (which includes the SMSS app) on the box with no change in behavior.

Is there a log file somewhere I can check to see why SMSS will not start for this one user?


texts: Getting a query to work on SQLite as well as on MySQL

texts:
I have a subquery with a condition in the main query where clause.

Here is the sample schema

CREATE TABLE `payments` (
  `id` int(11) NOT NULL,
  `discount_id` int(11),
  `amount` int(11)
);

CREATE TABLE `discounts` (
  `id` int(11) NOT NULL,
  `rate` int(11) NOT NULL
);

And the query

SELECT
  discounts.*,
  (SELECT COUNT(id)
   FROM payments
   WHERE payments.discount_id = discounts.id
   GROUP BY payments.discount_id) AS usage_count
FROM discounts
WHERE
  rate > 10
  AND usage_count > 1

Working on SQLite : http://sqlfiddle.com/#!5/9f159/2
Not working on MySQL : http://sqlfiddle.com/#!9/9f1593/1

MySQL shows error Unknown column 'usage_count' in 'where clause'

I can get it to work on MySQL using a HAVING clause but then it fails on SQLite with TypeError: e.STATEMENT is undefined

Not working on SQLite : http://sqlfiddle.com/#!5/9f159
Working on MySQL : http://sqlfiddle.com/#!9/9f1593/2

Is there any way to have a single way of working with both?

Why? I am developing using the Laravel framework, our unit tests run on SQLite and the app server runs MySQL.


texts:
We are looking to do Column encryption on some of our DBs.
So creating the keys and encrypting is all good.
My boss is wanting to use Vault by Hashicorp to store the keys.

So I am wondering if anyone knows how you go about exporting the keys from SQL 2008 and then store them in Vault?


texts: Insert into a view derived from a table with a discriminator field

texts:
I am in a situation in which I have a base table with some fields and a discriminator field which could have one out of a specific set of values.

From that base table, I have derived many views, one for each possible discriminator value, but such views do not include the discriminator column themselves.

The problem is, when I try to INSERT INTO one of the view, the insert succeedes, but the discriminator field in the base table is NULL.

Isn't there a way for me to insert in the view while having the discriminator value correctly set? Or do I need necessarily to insert in the base table instead?

Here is an example of what I mean:

create table base_table (
  a int unsigned auto_increment primary key,
  b char(10) default null,
  c char(10) default null,
  x tinyint unsigned default null
);

create or replace view base_view_1 as
select a, b, c
from base_table
where x = 1;

create or replace view base_view_2 as
select a, b, c
from base_table
where x = 2;

[ ... and so on ... ]

insert into base_view_1 (a, b, c) VALUES (null, 'xxx', 'xxx');

select * from base_view_1; -- no result

select * from base_table; -- one row like (1, 'xxx', 'xxx', null)

See that, in the example, the row from base_table as null as the value for x? Should mysql be able to populate that field automatically based on view definition?

What I would like to achive is that, when I insert on the view base_view_1, since it is defined for x = 1, mysql sets automatically the correct value in base_table, and the same when inserting in base_view_2 where x should be 2, and so on, for example:

insert into base_view_1 (b, c) values ('xxx', 'xxx'); // should insert into base_table the values ('xxx', 'xxx', 1)

insert into base_view_2 (b, c) values ('xxx', 'xxx'); // should insert into base_table the values ('xxx', 'xxx', 2)

// and so on for each distinct discriminator value

Now, is mysql capable of doing this automatically for me, or do I need to resort inserting directly on base_table?


texts: SQL Server Optimization/Configuration for Inflexible Application

texts:
I'm responsible for overseeing an application which uses SQL Server as its backend storage solution. Unfortunately, the application has no concept of how a RDBMS should be utilized and seems to use it mainly like a NoSQL solution. What the application needs most is fetching and storing of single records and doesn't use any foreign keys for data integrity whatsoever.

As expected, this brilliant design leads to rather poor performance because throwing a RDBMS at a problem which merely requires a hash-on-disk style scheme is like using a cannon on a mosquito. But alas, I'm not able to modify the software as it is third party.

I've seen many tips that are happy to tell me I should use more efficient queries, but this simply isn't an option. SQL Server seems to be CPU bound mainly, and most of the data also seems to be cached in memory. Is there a way I can optimize/configure the way SQL deals with requests from this application? (The entire instance of SQL Server is available exclusively to the application.) Alternatively, is there another solution that behaves like SQL Server but acts like NoSQL in speed for simple queries? Is there some sort of single-process mode for SQL server that makes locking records unnecessary?

Any off-the-wall ideas I should give a shot?

Update:
So after a lot of mucking about with traces laced with auto-generated cursor preps, execs, fetches, and so forth, it looks like most of the time in the particular application feature of concern is wasted on populating tables for which only the first row is actually fetched. This actually appears to be a common problem the way the application was written.

Since the select statements behind the cursors are ordering the data, the cursors cannot effectively use a dynamic plan. Ultimately, the data the application is trying to get at is the next record where field A = document_id and the next field B value that's larger than the previously sought field B value. This is, of course, a typical application of cursors -- if only the application was using cursors the typical way.

I'm looking to contact the vendor again because this is... bad. I figure they're more likely to pop in an easy two-line already-existing fix. As such, I'm looking to generate a more efficient version of their query (still using cursors, with the same client-side semantics of 'give me the next record, k?'). I'm not exactly sure how to formulate such a query in TSQL since everything I've given a shot results in a scan, even with indexes I'd consider useful. (Really, only one b-tree search should be required, but somehow I just can't seem to get there.)


texts: Keeping version history / Slowly changing dimension

texts:
We're creating a new version of a 20 years old database, in which we used to keep version history by creating new rows in each table (SCD Type 2).

SCD Type 2 is not that hard when you need to keep history on a single table. However, if a table has dependencies that go all the way up (or down) in the database structure, you need to keep history of the whole structure. It is difficult to keep this clean enough, so I am looking at alternatives.

1rst alternative

I am aware of solutions like Oracle's flashback. What I am not sure about is if it is flexible when it comes to small schema changes (adding a column)? We won't have big changes to our schema, but in 5 years we might discover that we need some additional info.

2nd alternative

We're thinking of creating a history table where we keep the affected structures as XML. If the schema changes, we will create the tools that are going to update the history to include the new schema.

Any pros and cons of these solutions? Any suggestions are welcome (for other alternatives too)!

PS: As you might have guessed we're using Oracle.


texts: how to dump a case-sensitive table within a specific schema

texts:
I'm trying to dump a case-sensitive table named app_auth.User by executing the following command

pg_dump --schema=app_auth -t '"User"' -U em3local -h 127.0.0.1 -Fc -a -f <path> <dbname>

but what I'm returned is:

pg_dump: No matching tables were found

If I actually query my DB, the table does exist:

<dbname>=# SELECT * FROM app_auth."User";
 username | email | password | name | surname | lang | resettoken | userinfo_id | state | tos_accepted | id
----------+-------+----------+------+---------+------+------------+-------------+-------+--------------+----

any hint?


texts: Installing Oracle Forms and Reports on a 64bit machine

texts:
I'm trying to install Oracle Forms and Reports on a new server 64 bit (Ubuntu Trusty Tahr).  (I'm only actually interested in the report server component).

I can't find a version that I can install on oracle.com - the only versions I can find require Oracle Fusion Middleware which we are not licensed for. (I have actually tried to install this but I can't get to work - didn't follow this up too far due to the licensing issues)

My fallback plan is to install the 11g 32 bit version in a 32 bit chroot on the server, but am hoping to avoid this.

Does any one know if it's possible to get the reports server running without Middleware? (Or any ideas on which download from Oracle.com would be a likely candidate).

Thanks

edit: just to clarify - the answer given by Vérace didn't answer the question (the versions suggested still required Middleware to be installed). I'm still wanting to determine if it is possible to run a 64bit version of Reports Server without Middleware already being installed


texts: what is the difference of sequece of index in MySQL

texts:
I am now using this command to select in MySQL 5.7:
SELECT game_id
        FROM platform_reward_record
        WHERE hub_sync = 0
        GROUP BY game_id
        LIMIT 100

when I create index like this:
ALTER TABLE `packet_db`.`platform_reward_record`
ADD INDEX `idx_hubsync_gameid` (`game_id`, `hub_sync`);

the query is very slow, but using this index:
ALTER TABLE `packet_db`.`platform_reward_record`
ADD INDEX `idx_hubsync_gameid` (`hub_sync`, `game_id`);

it is fast, why the field sequence affected whe slect speed? What should I learn from it?


texts: How do I enforce a write-once then read only database table in SQL?

texts:
Is it even possible?

My use case is a ledger table, with a requirement that once a record is created, it should be read-only, i.e. no-one should be able to edit or delete it. This only applies to the ledger table and tables with a direct relation to it - there are other tables in the same schema which will be updated/deleted as normal.

My understanding is that for data integrity purposes these sorts of constraints should be applied at the database layer, but I can't find a clean, widely accepted way of doing this - is this a use case where I'd just be better doing it in the application layer?

The ideal would be for a way to do it in plain SQL, so as to be agnostic of what DB platform is used, since that may be subject to change, but I realise that may be too much to ask for, so if it has to be platform-dependent, some flavour of MySQL is preferred.

Thank you!


texts: Restricting database access per IP addresses

texts:
I have filtered IP addresses to an access DB server like this:

Set the sqlnet.ora

tcp.validnode_checking = YES
tcp.invited_nodes = (localhost, 192.168.100.130, 192.168.100.186)

But in my ORACLE_HOME, there are more than 1 Database, let say DB WORKSHOP and DB COURSE

I want to ask about connecting to database for each DB, example:

DB WORKSHOP only can be access from 192.168.100.130
DB COURSE only can be access from 192.168.100.186

What can I do to make it?


texts: What is the query that updates the Column in a table

texts:
I have a column in a table that I don't know who is updating it .

The table name is dbo.Events and the column name is [Status] and this column values are 1 or 2 .

I am trying to find the query that is updating rows in this column

I am familiar with the table

select * from sys.sql_modules m with(nolock);

Are there other options ?

THANKS


texts: Use domain username on SQL server reporting services installed in a server which is part of a Workgroup

texts:
I have installed SQL Server 2012 on Windows Server 2008. This system is configured to be part of a Workgroup and all the users in our organisation are part of a domain. I am trying to use the reporting services that is part of SQL Server.

I was trying to give "System User" permission to a user who is part of the domain through the "New System Role Assignment" page of the "Report Manager". It keeps throwing the error "user name or group name is not recognized.(rsUnknownUserName)"
Is it because the server is in a workgroup or is there any other reason?


texts:
I have these three tables:

Table1

pro_id(Pk)  pro_name
1           ABC
2           def

Table2

pid pro_id
1   1
2   1
3   1
4   2
5   2
6   2

Table3

pro_id  code
1       dc23
1       ef45
2       456F
2       4er

How can I write a query that would give this result?

pro_id    noof pids  noofcodes
1         3          2
2         3          2


texts:
I have a server in which many users and logins already have access to
I was wondering if it is possible to allow all of these users and logins SELECT privilege to a specific table within a database?


texts: We have some SQL Servers where MDW is configured. we need to pull MDW reports every week and send it to customer. Is there any way we can automate it using powershell..?, i googled it and i didn't get any relevant scripts or commands to do it.


texts: Best practice for upgrading MySQL on a master-master setup

texts: Anyone have experience upgrading a master-master setup of MySQL 5.1 to a release version of MySQL while trying to keep at least one server online (in my case we are upgrading to MariaDB 5.5).


texts:
Can you put a SQL DB on a NAS drive? For example can I put the PostgreSQL DB "files" on a Sinology NAS or am I better to leave everything on a "real" PC? (like, say, a regular PC server running Linux)

I'm asking this question because something weird happened: some accounting app written in WinDev (a RAD tool kinda famous in some countries, like many french-speaking countries), using a HyperfileSQL DB stored on a NAS, just screwed up big times.

wikipedia HyperFileSQL (in french)

wikipedia WinDev (in french)

The entire DB got corrupted, many files had a size of zero and the developer of the application tell my clients: "It's your fault, you shouldn't have put the HyperfileSQL DB files on a NAS, NAS haven't got a CPU powerful enough, so corruption can happen".

I don't get this: HyperfileSQL is supposed to be ACID. I can understand delays and/or poor perfomances but complete corruption?

Heck, I should even be able to yank the "UPS cord" (I mean: the "power cord", but everything is on UPSes) from the NAS and a true SQL DB should be fine no!?

So can I put a SQL DB on a NAS?

Should I expect poor performance? (it's for tiny databases and I never noticed any perf issue)

Are complete DB corruption to be expected?