texts: Complex query performance in MySQL InnoDB versus MyISAM

texts:
I am running on MariaDB 10.0.15 on a 4GB RAM cloud server with Local SSD disk for database.

My model:

users <n---n> right_holders <n---n> tracks ---n> raw_detections

I have this table raw_detections with more than 100 million records. I want to sum all raw_detections for a specific user in a specific period. This is the query that I am having problem:

SELECT
   DATE(raw_detection.created_at),
   SUM(1)
FROM `raw_detection`
INNER JOIN `audio_sources` ON `audio_sources`.`id` = `raw_detection`.`audio_source_id`
INNER JOIN `cities` ON `cities`.`id` = `audio_sources`.`city_id`
INNER JOIN `tracks` ON `raw_detection`.`track_id` = `tracks`.`id`
INNER JOIN `track_right_holders` ON `tracks`.`id` = `track_right_holders`.`track_id`
INNER JOIN `right_holders` ON `track_right_holders`.`right_holder_id` = `right_holders`.`id`
INNER JOIN `user_right_holders` ON `right_holders`.`id` = `user_right_holders`.`right_holder_id`
WHERE `raw_detection`.`duplicated` = 0
AND `user_right_holders`.`user_id` = 1
AND (raw_detection.created_at >= '2015-01-18 00:00:00')
AND (raw_detection.created_at <= '2015-02-19 23:59:59')
AND (audio_sources.source_type = 'Radio')
AND (track_right_holders.role = 'Interpreter')
GROUP BY DATE(raw_detection.created_at);

There are some users that have a lot of detections (tens of thousands) per month.

When raw_detection table is MyISAM, this query takes 2-3s to run. When the table is InnoDB, it takes 10-20s to run. My questions are:

1) Why this is happening?
2) How can I improve this performance for both MyISAM and InnoDB

raw_detection schema:

CREATE TABLE `raw_detection` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `score` int(11) NOT NULL,
  `track_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `audio_source_id` int(11) NOT NULL,
  `duplicated` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_raw_detection_on_duplicated_and_created_at` (`duplicated`,`created_at`),
  KEY `index_raw_detection_on_created_at` (`created_at`),
  KEY `index_raw_detections_audio_source` (`audio_source_id`,`duplicated`,`created_at`),
  KEY `index_raw_detection_on_track_id_and_duplicated_and_created_at` (`track_id`,`duplicated`,`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=126224926 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


texts: Storing type in each table or give item a type

texts:
I need to design schema for a project that involves two things.
1. providing services ( realted to vechicles)
2. Also sells insurance and spare parts

Below is the schema that I have designed so far.

Items which can be services or spare parts or other things

Services
--------------------
service_id
service_request_number
vehicle_id
user_id
status_id

Spare Parts
-------------------
spare_part_id
name
description
spare_part_detail_id
hsn_code
cat_id

I treated service too as a item that can be sold like others.

Below is the user's Cart.

Cart
--------------------
cart_id
user_id

CartItems
------------------
cart_item_id
cart_id
cart_item_id
quantity
// should I also place item type here?

Items_type
------------------
item_type_id
item_type // service, insurance, spare parts

So against each cart item an order would be placed.

Order processing would be different according to item_type.

So I want to know,  Should I insert item_type_id in Orders and Cart Items tables, Or should I just store item_type_id in each table of the item?
Like: in services put a item_type_ids.so all in other.

But Further I have two other tables.

UsedCouponDetails
--------------------------------
id
coupon_id
item_id
item_type //should I store it here too

As it is clear that If just store item_id in UsedCoupons , querying would take time just know type of the item.

Also there can two entries with same item_id.That would cause ambiguity
This is what I have tried for now, here.
Can someone someone suggest a better approach?


texts:
Looking for some advice here:

I currently have 3 SQL servers in a single AG with a single network card on each server. During times of heavy network usage, we are sometimes seeing failovers to a secondary server. After some research, I see that the way round this is to have a dedicated NIC for the AG traffic on a private IP address range.

What I was wondering is, it is possible to drop and create existing endpoints/listeners with the updated IP addresses or would I have to create a new AG and move databases across to it?


texts: How do I corrupt an mdb intentionally for test purposes

texts:
I'm creating a process to check for corrupt tables in an MDB on server.  There's no code, just tables.
I would like to corrupt an mdb intentionally for testing.  Has anyone ever done this?


texts: Changing compatibility level from 2008 to 2016

texts:
I have a database with hundreds of stored procedure, views, triggers, function, ...
The database Compatibility Level is 2008 (100) and we would like to move it to 2016 (130).

The SQL Server version is 2016. We read that migrating the Compatibility Level could/would improve performance.

What is the best way to verify each object of the database to ensure working compatibility of the database? I already checked the "Upgrade Advisor" and it doesn't support the version of the database.


texts: default permissible datatype conversion matrix

texts:
Today I wanted to define a uuid of value 00000000-0000-0000-0000-000000000000. Being a SQL Server man, I'd usually...

select cast(0x0 as uniqueidentifier);

...but I'm in postgres world now so I whipped out a sensible...

select cast('\x00'::bytea as uuid);

ERROR:  cannot cast type bytea to uuid
LINE 1: select cast('\x00'::bytea as uuid);
               ^

Damn! So I head over to the postgres docs on Type Conversion hoping to see a doc like this one to review which datatypes I can cast between by default without needing to create an explicit cast.

If such a chart does exist in the docs, it's well hidden. Google is likewise not super helpful in this regard.

  Is there a good documentation reference for default permissible type casts in postgresql?

To be clear, I don't actually care about the uuid


texts: Can't change root password: "The system cannot find the file specified."

texts:
I'm trying to change the root password in MySQL on my development machine (I've just installed MySQL, so it currently doesn't have a password), but it keeps failing with the following error message:

  The system cannot find the file specified.

I'm using MySQL 5.1.70 (x86, 32-bit) on Windows 7 SP1 (64 bits). I've added MySQL's "bin" directory to my "Path" environment variable.

In the comments of the MySQL documentation, I read that I should have installed the service using the absolute path, so I stopped MySQL, and uninstalled it:

C:\Windows\system32>mysqld --remove
Service successfully removed.

Then I installed it again, using the absolute path this time:

C:\Windows\system32>C:\web\mysql-5.1.70\bin\mysqld.exe --install
Service successfully installed.

I started MySQL, and tried to change the password again:

C:\Windows\system32>mysqladmin -u root password Pe%8XiduwOqdZ<ZFE5!
The system cannot find the file specified.

I also tried with quotes:

C:\Windows\system32>mysqladmin -u root password 'Pe%8XiduwOqdZ<ZFE5!'
The system cannot find the file specified.

I also tried to change the current directory to MySQL's "bin" directory:

C:\Windows\system32>cd C:\web\mysql-5.1.70\bin

C:\web\mysql-5.1.70\bin>mysqladmin -u root password Pe%8XiduwOqdZ<ZFE5!
The system cannot find the file specified.

C:\web\mysql-5.1.70\bin>mysqladmin -u root password 'Pe%8XiduwOqdZ<ZFE5!'
The system cannot find the file specified.

What's wrong?


texts:
I created a table and inserted values into it but they are not storing in orderly manner.
The table looks like:

I can order them by using ORDER BY but it does not change them permanently. Is there a way to save them in orderly manner permanently.

Clarification: I don't want how they are stored internally, what I want is when ever I execute select * from cricket; the results to come sequentially. So that I don't want to use ORDER BY clause every time. Is there a way?


texts: admin user creation is giving error in mongodb 3.0

texts:
I am trying to create admin user in mongoDB and it's giving me following error. MongoDB 3.0 version is being used.

Error :
2015-12-10T15:23:47.065+0000 E QUERY    Error: couldn't add user: Could not lock auth data update lock.
    at Error (<anonymous>)
    at DB.createUser (src/mongo/shell/db.js:1101:11)
    at (shell):1:4 at src/mongo/shell/db.js:1101

I have followed the link below to create the admin user.
link : https://docs.mongodb.org/v3.0/tutorial/enable-authentication/

    use admin
    db.createUser(
                  {
                     user: "myUserAdmin",
                     pwd: "abc123",
                     roles: [ { role: "userAdminAnyDatabase", db:"admin" } ]
                  }
                 )


texts: Storing GTIN/EAN values with PostgreSQL. Which data type to use?

texts:
I am building a product database and I need to store GTIN/EAN (Global Trade Item Number/European Article Number) values. Probably except GTIN-14 as it does not seem to be used at retail sales? I read the first digit is used to show some packing information but it is not globally agreed which numeric value represents what exactly?

Anyway, I tried to find out how they work to understand which column type would be appropriate. I used bigint when I started my little project. But now I wanted to investigate the issue deeper.

https://www.gs1.org/sites/default/files/docs/barcodes/GS1_General_Specifications.pdf Page 31 says:

  Note: GTINs may be stored with or without leading zeroes in the same
  database field, depending on the requirements of the particular
  application.

  Note: A GTIN-12 may start with one, two or three leading zeros. These
  zeroes are meaningful since they are part of the U.P.C. Company
  prefix, and therefore these must be preserved when storing the GTIN-12
  in a database field. For the list of U.P.C. Prefix ranges see section
  1.4

https://www.gtin.info/ says:

  GTIN is term referring to how the data is stored, i.e., padding the
  item number with zeroes to a uniform length.

https://www.gs1.dk/media/1246/udi-guideline.pdf Page 14 says:

  GTINs should be represented in software applications as 14 digits by
  right justifying and zero-filling to the left as appropriate.  In
  order to preserve any leading zeros that may be present, the GTIN
  field should be represented ina database as a textfield, not as a
  numeric field.

https://www.barcodefaq.com/1d/upc-ean/ says:

  To be fully GTIN compliant, all UPC and EAN numbers should be stored
  in databases as 14-digit numbers and filled in with zeros for the
  spaces to the left as shown in Fig: 1. For example, a UPC-A barcode of
  123456789012 would be represented as 00123456789012 in the database of
  a GTIN compliant system.

Then I found that PostgreSQL has a module for this called [isn][1]. There it says:

  Internally, all these types use the same representation (a 64-bit
  integer), and all are interchangeable.

First of all, I did not quite understand why one would need to store zeros in database if they are for padding only. It made no sense to me. (except for GTIN-12)

So, I was also thinking 64-bit integer sounds a reasonable type to store EAN values as I used bigint. Now I am thinking should I have used isn extension? But I am using Doctrine on PHP/Symfony and it  may be difficult to get it to understand ean13 column type and need to do custom mapping etc.

Perhaps somebody can tell if simply using bigint could cause troubles in the long run? I thought GTIN-14 can contain all other types and I do not necessarily need conversion between types in the database end. So I don't think there should be any problems but perhaps somebody else may have hands on experience? Any suggestions?

Thanks!


texts: What Permissions Are Needed for Selecting Data and Viewing All Schema?

texts:
I'm attempting to create a role in Azure SQL such that the user can perform Select operations, view all schema (but not modify schema) but not perform any writes or execute stored procs. Granting a user Select permission gets me close. However, the user cannot see any stored procs or scalar functions.

So, I want to allow the user to see all schema definitions, not modify any schema objects and perform Selects. Is this possible?


texts:
We are using 4032 & 3605 trace flags in SQL Server 2012 to get the expected log output.

Now, we would like to rotate the log files on daily basis to archive it in Amazon Glacier storage facility.

Any pointers on log rotation would be appreciated,


texts: How to identify a Windows Remote Desktop user by SQL Server session id?

texts:
In SQL Server I've got the session id of a process that is blocking other processes. Now I'd like to identify the specific user or machine that causes this blocking. The users are authenticating using SQL Server Authentication.

Normally I can get the desired information from the activity monitor (or through a query). But in case that the user connected from a Remote Desktop session, I'm lost. The host name is same for all RDP users and there is no additional information that helps distinguishing the sessions. It seems there is no way to actually identify the user who is actually blocking the others.

Is there any way to identify the user nevertheless? If yes, how?

(If it's not possible, then I have to live with it...)


texts: Why does xp_logininfo truncates AD user account names? How to overcome this?

texts:
As per this question, I use the following SP to see the members within an AD Group that's been added as a "login" in my database server.

EXEC master.dbo.xp_logininfo 'MyDomain\ADGROUPNAME' , 'members'

The issue is that, the resultant data gives me only up to 30 characters of the user's account name. This is because in the Active Directory, there are two sets of account names, one that says pre-windows-2000 , and another is for post-windows 2000. The pre one is truncated and that's the one I get.

For example, if the user account in the AD group is: MyDomain\ShortName, I get MyDomain\ShortName. But if the account name is:
MyDomain\HasOverThirtyCharacters, then I get
MyDomain\HasOverThirtyCharact

How do I overcome this limitation? What can I use instead of xp_logininfo??


texts:
I want to design a database for school the output of the system have to be like this :

So I design this Database

Is there any tips or  notes that may make it better .
Thanks in advance


texts: MySQL Subquery returns more than one row

texts:
I have a table namely tbl_plays. It has the following columns and data

id(PK)|    gameid    |    drawid    |   etc
----------------------------------------------
   1  |     1509     |      73      |   etc

I am running this simple query.

SELECT id FROM tbl_play WHERE id = 1 OR gameid = 0

This query is run as a subquery which returns the id column data to a variable.

Now my question, why do I get the error SUBQUERY RETURNS MORE THAN ONE ROW where as it returns only one row. In Workbench it returns an editable result-set, and myAdmin shows edit/delete buttons.

Does it have anything related to the fact that I am fetching a PK column data? If it does, then how can I over come this.

Regards

P.S: id column has all unique values.


texts: Corruption on MS SQL Database due to 1 failed disk on RAID 6

texts:
Why is that when a single disk fails from a RAID 6 setup MS SQL 2014 database corrupts with the following error:

DESCRIPTION:    SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual signature: 0xad191b0d). It occurred during a read of page (1:144366313) in database ID 19 at offset 0x0001135b5d2000 in file 'D:\MSSQL12.LIST133\MSSQL\DATA\test.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

The RAID 6 status is healthy and hasn't change even with one disk failed (the issue should appear when 3 DISKs fail). How does the SQL knows what is going on with the DISK since it is not a software RAID 6? There is a seperate controller and a storage.
Also what is the point of having spare disks in the RAID  if the SQL is going to corrupt anyway? How can I avoid this?


texts: Does pg_export_snapshot work across replicas?

texts:
This is probably of little importance, but it's possible in PostgreSQL to retrieve the transaction snapshot with pg_export_snapshot and restore it with set transaction to start a second transaction with the same level of visibility (see the documentation).

I'm interested to know if that works on a hot-standby or is specific to a single server? Could you export a snapshot on the standby and then use it with set transaction on the master? I doubt it.


texts: SQL Server benchmarking HammerDb TPM = 0

texts:
I'm trying to use HammerDb 3.0 to get a baseline to compare my servers.

I can create the Db, schema, data and items required no problem. I can execute the test and see that it's running.

When the script completes it give me a NOPM number but says TPM is zero. When it's running the Transaction counter doesn't graph anything either.

Is this a known or common problem?


texts: Spatial Data Cluster in sql-server ? ( like ST_ClusterWithin in Postgis )

texts:
Postgres 9.5 providing ST_ClusterWithin for Cluster the spatial data in DB level.

Is there any available solution for data clustering in MS SQL (NOT SERVER CLUSTER)

OR

Is there Any Function ( like ST_ClusterWithin ) in MS SQL Server

------------NO POSTGRES/ POSTGIS SOLUTION -------------

------------NO OL3 SOLUTION -------------


texts:
I moved a 2 25 GB databases to an RDS instance with 100 GB of SSD storage allocated.

However, I see that I only have 2 GB of space left!

What can cause this behaviour?

At first I thought logs, but there is no way you can have 50 GB of logs on a database that has less than 1 week of operation! or is it?

How can I free up space?


texts:
I am new to this, therefore need some help:

We are using PeopleTools 8.48, Peoplesoft Enterprise 8.8 for CRM application. We have database of Oracle v10.2.0.5.0. Recently, because of SCN issue, we have been asked to apply the following patches- Patch 14121009 + patch 16619894 DBPSU (July 2013 PSU).

my question- are these patches compatible with the PeopleSoft/PeopleTool and Oracle versions? Do we need to make any upgrades in parallel?


texts:
What is the location of the Listener.Ora file in Windows OS?
I need to edit  that file and I am not able to locate where it is.


texts: Is it possible to have a temporary, on-the-fly PostgreSQL table which only ever lives in RAM?

texts:
I know that PostgreSQL automatically uses RAM only for tables that are small enough. But I'm not just talking about the data inside the table, but about the entire table itself.
Basically, I've (poorly) re-implemented various database functionality in PHP arrays, for example when I fetch data from an API and want to sort it or "massage" it before displaying it in my control panel. In such a situation, it makes no sense (at least to me) to have an actual database table around which is only ever used for this, with temporary data. It would be much better if I could on-the-fly create a table which I fill up in RAM, sort and then fetch records from with normal PG SQL queries.
Is this a thing? It feels stupid that I have array structures and various functions that try to mimic SQL's "SORT BY".
Naturally, I'm not talking about executing CREATE TABLE, add the data to it, and then return it again to PHP, and then DROP TABLE. That would be ridiculously bad for performance.
If there is no way to do this, I'll just accept it, but it's something which I've often thought would make perfect sense.


texts:
This might be a novice question. But sometimes, I find it necessary to use things like (SELECT * FROM A) in doing relational algebra in SQL (instead of using the relation A itself).

For example, when trying to work on the UNION of two relations A and B having the same structure, the following

SELECT * FROM (A UNION B) t;

generates a syntax error (with PostgreSQL 9.x).

ERROR:  syntax error at or near "UNION"

And I had to do

SELECT * FROM ((SELECT * FROM A) UNION (SELECT * FROM B)) t;

My questions are:

Is the verbosity of the subquery (SELECT * FROM A) really necessary in the SQL language (or am I missing something about better way to write it)? Why can't the relations A and B be used directly here?

Is (SELECT * FROM A) equivalent to A in relational algebra? What does the expansion (SELECT * FROM A) buy us? When is the long form required?


texts: How to take backup of a database in mysql?

texts:
For Back up I have used the following commands.

mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

mysqldump -u [username] -p[root_password] --single-transaction
 --routines --triggers --lock-tables [database_name] > backup.sql;

Getting same error:

  mysqldump: Couldn't execute 'show table status like 'uc_order_products_pair_vw'': SELECT command denied to user 'username'@'localhost' for column 'nid' in table 'uc_order_products' (1143)

After running select * from mysql.tables_priv;


texts:
Can anybody tell why the value of @URlID is always -1 even when i know the query will return a result? I have tried any possible type of variables and any possible way of inserting into the variable yet no effect.

    DELIMITER $$
    CREATE FUNCTION getShortURLID(URL varchar(1200))
    RETURNS INT
    DETERMINISTIC
    READS SQL DATA
    MODIFIES SQL DATA
    BEGIN
        SET @newShortURL:=NULL;
        SELECT SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(URL, "http://", ""), "https://", ""), "www.", ""), '/', 1) INTO @newShortURL ;
    set @URLID:= -1;
    set @URLID := (SELECT ifnull(id,0) FROM url_productivity WHERE url = @newShortURL LIMIT 1);

    IF @URLID <=> 0 then
        INSERT INTO urls(url) VALUES(@newShortURL);
        RETURN LAST_INSERT_ID();
    ELSE
        RETURN @URLID ;
    END IF;
    END$$


texts: Data and index file compatibility between patch/revision releases of MySQL

texts:
Let's say a server is on MySQL 5.6.1234 (yes I know that number's made up) and gets updated to 5.6.1235 (so an increment in revision / patch number).  How safe would it be roll back MySQL to 5.6.1234 - would the data / index / log / table definition / etc. files still work on the prior release?

The scenario I'm envisioning is a backwards compatible change is introduced and we need to buy time to fix all the websites affected by it.

This happened before with MySQL 5.0.x on CentOS 5 due to  some change which MySQL didn't even officially label as a backwards incompatibility in the changelog. In MySQL 5.0.84 they changed max_allowed_packet from being able to be set as a SESSION variable but it having no effect to being readonly and we got a nice surprise when Red Hat bumped MySQL up from 5.0.77 to 5.0.95 (see http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_allowed_packet vs. the "oh by the way" at the end of http://dev.mysql.com/doc/relnotes/mysql/5.0/en/news-5-0-84.html).  That case was easy enough to fix, but I'm trying to plan for a more difficult situation and want to know my options.


texts: Getting Request Timeout from stored procedure?

texts:
Only sometimes, I get request timeout (means my stored procedure is not responding within a specified time-period which is 90 seconds).

Here is my stored procedure:

ALTER PROCEDURE [dbo].[Insertorupdatedevicecatalog]
                (@OS                NVARCHAR(50)
                ,@UniqueID          VARCHAR(500)
                ,@Longitude         FLOAT
                ,@Latitude          FLOAT
                ,@Culture           VARCHAR(10)
                ,@Other             NVARCHAR(200)
                ,@IPAddress         VARCHAR(50)
                ,@NativeDeviceID    VARCHAR(50))
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @TranCount INT;
    SET @TranCount = @@TRANCOUNT;

    DECLARE @OldUniqueID VARCHAR(500) = ''-1'';
    SELECT @OldUniqueID = [UniqueID] FROM DeviceCatalog WHERE (@NativeDeviceID != '''' AND [NativeDeviceID] = @NativeDeviceID);

    BEGIN TRY
        IF @TranCount = 0
            BEGIN TRANSACTION
        ELSE
            SAVE TRANSACTION Insertorupdatedevicecatalog;

        DECLARE @Geo GEOGRAPHY = geography::STGeomFromText(''POINT('' + CONVERT(VARCHAR(100), @Longitude) + '' '' + CONVERT(VARCHAR(100), @Latitude) + '')'', 4326);

        IF EXISTS(SELECT 1 FROM DeviceCatalog WHERE [UniqueID] = @UniqueID)
        BEGIN
            DECLARE @OldGeo     GEOGRAPHY
                    ,@OldCity       NVARCHAR(100)
                    ,@OldCountry    NVARCHAR(100)
                    ,@OldAddress    NVARCHAR(100);

            SELECT  @OldGeo = [LastUpdatedLocationFromJob]
                    ,@OldCity = [City]
                    ,@OldCountry = [Country]
                    ,@OldAddress = [Address]
            FROM    DeviceCatalog
            WHERE   [UniqueID] = @UniqueID;

            UPDATE  DeviceCatalog
                SET [OS] = @OS
                    ,[Location] = @Geo
                    ,[Culture] = @Culture
                    ,[Other] = @Other
                    ,[IPAddress] = @IPAddress
            WHERE   [UniqueID] = @UniqueID;

                            IF (@OldGeo IS NULL OR @OldAddress IS NULL OR @OldCity IS NULL OR @OldCountry IS NULL OR ISNULL(@Geo.STDistance(@OldGeo) / 1000,0) > 50)
            BEGIN
                UPDATE  DeviceCatalog
                    SET [Lastmodifieddate] = Getdate()
                WHERE   [UniqueID] = @UniqueID;
            END

        END
        ELSE
        BEGIN
            INSERT INTO DeviceCatalog
                        ([OS]
                        ,[UniqueID]
                        ,[Location]
                        ,[Culture]
                        ,[Other]
                        ,[IPAddress]
                        ,[NativeDeviceID])
                VALUES  (@OS
                        ,@UniqueID
                        ,@Geo
                        ,@Culture
                        ,@Other
                        ,@IPAddress
                        ,@NativeDeviceID);
                IF(@OldUniqueID != ''-1'' AND @OldUniqueID != @UniqueID)
                BEGIN
                    EXEC DeleteOldAndroidDeviceID @OldUniqueID, @UniqueID;
                END
        END
LBEXIT:
        IF @TranCount = 0
            COMMIT;

    END TRY
    BEGIN CATCH
        DECLARE @Error INT, @Message VARCHAR(4000), @XState INT;
        SELECT  @Error = ERROR_NUMBER() ,@Message = ERROR_MESSAGE() ,@XState = XACT_STATE();

        IF @XState = -1
            ROLLBACK;
        IF @XState = 1 AND @TranCount = 0
            rollback
        IF @XState = 1 AND @TranCount > 0
            ROLLBACK TRANSACTION Insertorupdatedevicecatalog;

        RAISERROR (''Insertorupdatedevicecatalog: %d: %s'', 16, 1, @error, @message) ;
    END CATCH
END


texts: Return more columns than were inserted inside a PostgreSQL CTE

texts:
I would like to use a modifying CTE to insert some values. The insert statement inserts data from a select statement. I use the returning keyword to return the values inserted (including auto-incrementing columns). However, I also want that CTE to return other columns. How can I do this?

An example follows:

drop table if exists customers;

CREATE TABLE customers (
 customer_id serial PRIMARY KEY,
 name VARCHAR UNIQUE,
 email VARCHAR NOT NULL,
 active bool NOT NULL DEFAULT TRUE
);

INSERT INTO customers (NAME, email)
VALUES
 ('IBM', 'contact@ibm.com'),
 (
 'Microsoft',
 'contact@microsoft.com'
 ),
 (
 'Intel',
 'contact@intel.com'
 );

drop table if exists customers2;

CREATE TABLE customers2 (
 customer_id serial PRIMARY KEY,
 name VARCHAR UNIQUE,
 email VARCHAR NOT NULL
);

with x as (
    INSERT INTO customers2 (NAME, email)
    select name, email from customers
    returning customer_id, name, email, active
    )
select * from x
;

I would like the last statement to return columns customer_id, name, email, active. But I get an error:

Error: ERROR: column "active" does not exist
  Position: 123
SQLState:  42703
ErrorCode: 0
Error occurred in:
with x as (
    INSERT INTO customers2 (NAME, email)
    select name, email from customers
    returning customer_id, name, email, active
    )
select * from x


texts: If I apply Cumulative Update 3 in SQL Server 2012 SP1, do I still have to do Cumulative update 1 and 2 before I can apply CU 3? And also, how would you know what CU's or hot fixes that has already been applied in SQL Server?


texts: Replication and table locks during alter table

texts:
Recently we have noticed that a number of our Rails migrations end up deadlocking / freezing our app+DB in production. Preliminary investigation reveals that this is probably due to concurrent access by the app and the migration on a table with very high reads.

Would it make sense to explore a replicated PG setup (master-slave perhaps), where all writes and migrations are executed against the master, and all high volume reads are executed against the slave?

How does PG behave when an ALTER TABLE statement is replicated to a slave? Does the slave also acquire the same table locks? Will replication solve the problems that we are currently facing?


texts: Getting duplicate primary key for auto increment column

texts:
I have a MySQL Innodb table with 'id' as auto increment primary key column.

My Scripts are doing following operations:

insert ignore into tablename set x='a', y='b'
update ignore tablename set x='a', y='b' where id = 'smthing'
delete from tablename where  id = 'smthing'

Now after running the script when I insert something manually (insert into tablename set x='ax', y='bx') I get the following error:

Duplicate Entry for Auto Increment Value.

On check I see that Auto Increment is trying to put the highest possible value of int that MySql will support as new value even and it already exist in db.

The table has two index: id+x, id+y though idk if this is related to this issue.


texts: MySQL replication - database size is different

texts:
I have enabled MySQL master-master replication with 2nd master acting as read-only. Recently I checked the out the size of the databases in both master and slave and I found that database size in two databases is 1mb less than the Master. Does it mean that the setup which I made for master-slave was not done properly or the replication is not happening properly?
Also, how can I make the changes so that it remains the same in both master and slave?

Thanks!


texts:
I have a custom eCommerce web site, using third-party web services, that is processing dues payments for our members. Twice in the last day a specific data row for 50,000 users is having two of the dollar amount columns change to $0 for all 50,000 rows. Just those two columns are updated, nothing else in the row changes.

We have reviewed our coding and SPs and can find nothing that would update those rows.

I have approximate times that the events occurred, how would I find out what was running on the SQL Server at the time that could have made the change? I have looked through the Windows Event and SQL Server logs and do not see anything out of the ordinary. So I'm assuming that the update is coming from an authorized user.

I realize I may not be able to see the past if I don't have logging set up properly. The SQL Server (12.0.5557.0) is very vanilla configuration. But any clue to looking at history or setting up for future logging would be greatly appreciated.


texts:
I want to add a function like trigger to my Postgres database. The problem is that this function should executed one time each day automatically and change values in the specified table.

Is it possible to carry out this by SQL syntax or is it better to write a bash script with some queries I need?


texts: Enabling mySQL replication using GTID after purging binlog

texts:
I am currently working to get GTID based replication established between mySQL 5.7.33 on 2 servers. Amidst this work, I ran into a disk space issue and purged the binary logs on the master.
Now I have run into the problem where I cannot get the replication started due to the missing binary logs.
What can I do, without the bin logs, to get replication going?
Thanks for any advice!


texts:
I have two tables, let's call them leave_request and the other one leave_given.

The employee submit a leave_request to take a vacation where he has to fill place of vacation, date of start, date of end, reason etc.

The boss of that employee will proceed that request and decide whether the employee can take the vacation or not. If he gives him one, leave_given is filled with the details that the employee gave (date start and date end might change as the boss may not like the dates).

Now, regarding the 'Status' of the request. I was first thinking that If id of leave_request is not found on leave_given then It's rejected If it's there then It's accepted which I believe make sense.

I'm however writing an application and I want the user to know If the request has not been proceed yet so I will have to add a status field.

My question is, does it make sense to do the following:

Add status in leave_request?
Once the employee make a request, Status is on hold by default.
If the boss accepts it, status will change to Accepted and It will be added to leave_given. If rejected, the status will change to Rejected and nothing will be added to leave_given.


texts: EXP-00085 warnings while doing full backup

texts:
During a full export of my Oracle 10.2.0.2 database, I encounter the following warnings

[...]
. exporting post-schema procedural objects and actions
EXP-00008: ORACLE error 6502 encountered
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_SCHED_MAIN_EXPORT", line 351
ORA-06512: at "SYS.DBMS_SCHED_JOB_EXPORT", line 14
ORA-06512: at line 1
EXP-00085: The previous problem occurred when calling     SYS.DBMS_SCHED_JOB_EXPORT.create_exp for object 102269
EXP-00008: ORACLE error 6502 encountered
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_SCHED_MAIN_EXPORT", line 351
ORA-06512: at "SYS.DBMS_SCHED_JOB_EXPORT", line 14
ORA-06512: at line 1
EXP-00085: The previous problem occurred when calling SYS.DBMS_SCHED_JOB_EXPORT.create_exp for object 102271
EXP-00008: ORACLE error 6502 encountered
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_SCHED_MAIN_EXPORT", line 351
ORA-06512: at "SYS.DBMS_SCHED_JOB_EXPORT", line 14
ORA-06512: at line 1
EXP-00085: The previous problem occurred when calling SYS.DBMS_SCHED_JOB_EXPORT.create_exp for object 102273
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully with warnings.

I looked for these three objects querying dba_objects with their object_id and it returns

ADV_SQLACCESS5150248
ADV_SQLACCESS9067727
ADV_SQLACCESS2676493

Are these warnings worrying?

How should I fix them and what is going on exactly?


texts:
I've a database value 'INS. Company Cancelled' where multiple values are separated by '.'
I'm trying to replace 'INS' with 'INSEXP' my expected result is 'INSEXP. Company Cancelled'
I've tried below two queries to update the field my output is like 'INSEXP. Company Cancelled. Company Cancelled'
update my_table
SET column_name = (select replace(column_name, 'INS', 'INSEXP')
                    from my_table WHERE seq_num = 123)
WHERE seq_num = 123;

update my_table
set column_name = replace(column_name, 'INS', 'INSEXP')
WHERE seq_num = 123;

Could someone please tell me what I'm doing wrong here?


texts: Should I create a child table without primary key?

texts:
I'm designing a database with a child table that may contains billions of records in the future. So I try to use less fields (and smaller ones) as possible.

So I'm considering not using a primary key, as it would have to be a bigint, but I will never use it in my queries (this child table won't have ever a 1-N relationship).

Does a primary key useful for something else than query and join ? I suppose Mysql have an other system to distinguish a record from another, or am I wrong ?

Thanks a lot

Edit : my create statement

CREATE TABLE `receipt_line` (
  `id` bigint(20) unsigned NOT NULL,
  `receipt_id` mediumint(8) unsigned NOT NULL,
  `prod_id` smallint(5) unsigned NOT NULL,
  `coupon_id` smallint(5) unsigned DEFAULT NULL,
  `price` decimal(5,2) NOT NULL,
  `qty` decimal(4,2) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

ALTER TABLE `receipt_line `
  ADD PRIMARY KEY (`id`),
  ADD KEY `id ticket` (`receipt_id`),
  ADD KEY `id produit` (`prod_id`),
  ADD KEY `id coupon` (`coupon_id`),

Obviously, the parent table is Ticket, and I have other linked tables (product, coupon).
The id field is linked to nothing. The other keys aren't unique.


texts: Does SQL Server cache calculated values in a query?

texts:
Every time I run into this type of queries I always wonder how would SQL Server work it out. If I run any type of query that requires a calculation and then use that value in multiple places, for example in the select and the order by, will SQL Server calculate it twice for every row or will it be cached? Furthermore, how does this work with User Defined Functions?

Examples:

SELECT CompanyId, Count(*)
FROM Sales
ORDER BY Count(*) desc

SELECT Geom.BufferWithTolerance(@radius, 0.01, 0).STEnvelope().STPointN(1).STX, Geom.BufferWithTolerance(@radius, 0.01, 0).STEnvelope().STPointN(1).STY
FROM Table

SELECT Id, udf.MyFunction(Id)
FROM Table
ORDER BY udf.MyFunction(Id)

Is there a way to make it more efficient or is SQL Server smart enough to handle it for me?


texts: How to optimise T-SQL query using Execution Plan

texts:
I have a SQL query that I have spent the past two days trying to optimise using trial-and-error and the execution plan, but to no avail. Please forgive me for doing this but I will post the entire execution plan here. I have made the effort to make the table and column names in the query and execution plan generic both for brevity and to protect my company's IP. The execution plan can be opened with SQL Sentry Plan Explorer.

I have done a fair amount of T-SQL, but using execution plans to optimise my query is a new area for me and I have really tried to understand how to do it. So, if anyone could help me with this and explain how this execution plan can be deciphered to find ways in the query to optimise it, I would be eternally grateful. I have many more queries to optimise - I just need a springboard to help me with this first one.

This is the query:

DECLARE @Param0 DATETIME     = '2013-07-29';
DECLARE @Param1 INT          = CONVERT(INT, CONVERT(VARCHAR, @Param0, 112))
DECLARE @Param2 VARCHAR(50)  = 'ABC';
DECLARE @Param3 VARCHAR(100) = 'DEF';
DECLARE @Param4 VARCHAR(50)  = 'XYZ';
DECLARE @Param5 VARCHAR(100) = NULL;
DECLARE @Param6 VARCHAR(50)  = 'Text3';

SET NOCOUNT ON

DECLARE @MyTableVar TABLE
(
    B_Var1_PK int,
    Job_Var1 varchar(512),
    Job_Var2 varchar(50)
)

INSERT INTO @MyTableVar (B_Var1_PK, Job_Var1, Job_Var2)
SELECT B_Var1_PK, Job_Var1, Job_Var2 FROM [fn_GetJobs] (@Param1, @Param2, @Param3, @Param4, @Param6);

CREATE TABLE #TempTable
(
    TTVar1_PK INT PRIMARY KEY,
    TTVar2_LK VARCHAR(100),
    TTVar3_LK VARCHAR(50),
    TTVar4_LK INT,
    TTVar5 VARCHAR(20)
);

INSERT INTO #TempTable
SELECT DISTINCT
    T.T1_PK,
    T.T1_Var1_LK,
    T.T1_Var2_LK,
    MAX(T.T1_Var3_LK),
    T.T1_Var4_LK
FROM
    MyTable1 T
    INNER JOIN feeds.MyTable2 A ON A.T2_Var1 = T.T1_Var4_LK
    INNER JOIN @MyTableVar B ON B.Job_Var2 = A.T2_Var2 AND B.Job_Var1 = A.T2_Var3
GROUP BY T.T1_PK, T.T1_Var1_LK, T.T1_Var2_LK, T.T1_Var4_LK

-- This is the slow statement...
SELECT
    CASE E.E_Var1_LK
        WHEN 'Text1' THEN T.TTVar2_LK + '_' + F.F_Var1
        WHEN 'Text2' THEN T.TTVar2_LK + '_' + F.F_Var2
        WHEN 'Text3' THEN T.TTVar2_LK
    END,
    T.TTVar4_LK,
    T.TTVar3_LK,
    CASE E.E_Var1_LK
        WHEN 'Text1' THEN F.F_Var1
        WHEN 'Text2' THEN F.F_Var2
        WHEN 'Text3' THEN T.TTVar5
    END,
    A.A_Var3_FK_LK,
    C.C_Var1_PK,
    SUM(CONVERT(DECIMAL(18,4), A.A_Var1) + CONVERT(DECIMAL(18,4), A.A_Var2))
FROM #TempTable T
    INNER JOIN TableA (NOLOCK) A ON A.A_Var4_FK_LK  = T.TTVar1_PK
    INNER JOIN @MyTableVar     B ON B.B_Var1_PK     = A.Job
    INNER JOIN TableC (NOLOCK) C ON C.C_Var2_PK     = A.A_Var5_FK_LK
    INNER JOIN TableD (NOLOCK) D ON D.D_Var1_PK     = A.A_Var6_FK_LK
    INNER JOIN TableE (NOLOCK) E ON E.E_Var1_PK     = A.A_Var7_FK_LK
    LEFT OUTER JOIN feeds.TableF (NOLOCK) F ON F.F_Var1 = T.TTVar5
WHERE A.A_Var8_FK_LK = @Param1
GROUP BY
    CASE E.E_Var1_LK
        WHEN 'Text1' THEN T.TTVar2_LK + '_' + F.F_Var1
        WHEN 'Text2' THEN T.TTVar2_LK + '_' + F.F_Var2
        WHEN 'Text3' THEN T.TTVar2_LK
    END,
    T.TTVar4_LK,
    T.TTVar3_LK,
    CASE E.E_Var1_LK
        WHEN 'Text1' THEN F.F_Var1
        WHEN 'Text2' THEN F.F_Var2
        WHEN 'Text3' THEN T.TTVar5
    END,
    A.A_Var3_FK_LK,
    C.C_Var1_PK

IF OBJECT_ID(N'tempdb..#TempTable') IS NOT NULL
BEGIN
    DROP TABLE #TempTable
END
IF OBJECT_ID(N'tempdb..#TempTable') IS NOT NULL
BEGIN
    DROP TABLE #TempTable
END

What I have found is that the third statement (commented as being slow) is the part that is taking the most time. The two statements before return almost instantly.

The execution plan is available as XML at this link.

Better to right-click and save and then open in SQL Sentry Plan Explorer or some other viewing software rather than opening in your browser.

If you need any more information from me about the tables or data, please don't hesitate to ask.


texts: Configuration required for a big mysql database around 20GB

texts:
I need to host a single machine database (constraints), wherein the database size is roughly 20gb. Most of the tables are in MyISAM. Any way I can actually run it with decent speed ? Alternatives I can look into are :
TokuDB, Percona, MariaDB.

A few of the tables have around 50-60 million rows.
So what I want to know is, how much of RAM and possibly what my.cnf settings might be required to host such a database.

Thanks


texts: Running ratio of unique counts to total counts

texts:
Any ideas on how to code a running ratio of counts (unique and total) for ordered observations (rows) in t-SQL (SQL Server 2017 Community Edition or higher)?

Given a data set, which is necessarily ordered (Ex. a time series indexed/ordered by date):

CREATE TABLE #x (dt DATE, Name VARCHAR(MAX));
INSERT INTO #x VALUES
('2012-01-01', 'a'),('2012-01-02', 'b'),('2012-01-03', 'c'),
('2012-01-04', 'a'),('2012-01-05', 'b'),('2012-01-06', 'd'),
('2012-01-07', 'a'),('2012-01-08', 'b'),('2012-01-09', 'e'),('2012-01-10', 'e');

So, given columns Dt and Names, I need the output Ratio below. For clarity, I also included output columns UCnt that counts unique names to date (i.e. a restricted running count) and TCnt counts all names to date (i.e. a simple running count). The output Ratio field is a just a running proportion of the two.

Dt          Name    UCnt    TCnt    Ratio
2012-01-01  a       1       1       1.000
2012-01-02  b       2       2       1.000
2012-01-03  c       3       3       1.000
2012-01-04  a       3       4       0.750
2012-01-05  b       3       5       0.600
2012-01-06  d       4       6       0.666
2012-01-07  a       4       7       0.571
2012-01-08  b       4       8       0.500
2012-01-09  e       5       9       0.555
2012-01-10  e       5       10      0.500


texts: encryption of SSRS subscription emails and attachments

texts:
Is it possible to set up an SSRS e-mail subscription, with an attachment, that is encrypted?

POST ANSWER-ACCEPT UPDATE: Does the SMTPUseSSL switch in the RSReportServer.config file help me at all?

ONE MORE UPDATE: No, the SMTPUseSSL switch does not get me there.  I have validated the answer below here:

The default e-mail delivery extension does not provide support for digitally signing or encrypting outgoing mail messages.


texts: Configure Oracle database (first time user)

texts:
I am trying to learn how to use Oracle Database. I downloaded the Standard Edition version 12c from here: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html.

I successfully installed it (I think). I don't know how to proceed now. I was researching online and found the the file tnsnames.ora in D:\Programs\Oracle\app\Admin\product\12.1.0\dbhome_1\NETWORK\ADMIN. I found out something is using port 1521. So I went to localhost:1521 in my browser, but nothing comes up.

Is there a web interface? Can I install one?

I also opened up SQLDeveloper and made a local connection. When I try to open up / as sysdba, I get this error:

java.lang.NullPointerException
    at oracle.dbtools.raptor.navigator.xml.NavigatorHook.getConnectionType(NavigatorHook.java:81)
    at oracle.dbtools.raptor.navigator.ObjectFactoryRegistry.createFactory(ObjectFactoryRegistry.java:157)
    at oracle.dbtools.raptor.navigator.ObjectFactoryRegistry.getFactory(ObjectFactoryRegistry.java:146)
    at oracle.dbtools.raptor.navigator.ConnectionFilter.getFactory(ConnectionFilter.java:95)
    at oracle.dbtools.raptor.navigator.ConnectionFilter$1.doWork(ConnectionFilter.java:119)
    at oracle.dbtools.raptor.navigator.ConnectionFilter$1.doWork(ConnectionFilter.java:104)
    at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:193)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
    at java.util.concurrent.FutureTask.run(FutureTask.java:138)
    at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:515)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:439)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
    at java.util.concurrent.FutureTask.run(FutureTask.java:138)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918)
    at java.lang.Thread.run(Thread.java:682)

If someone can help me understand how to get everything set up (with a web interface), I would greatly appreciate it. I have watched videos and read articles but I am just not getting it.

I just want to be able to make Java programs that can communicate with the database using JDBC. I need to learn for my new job. Any help would be appreciated, thank you!


texts: Database Design for Questions and Answers

texts:
I'm building a website that will host a variety of questionnaires. Each questionnaire has a different number of questions and each question has a different number of answers. I have attempted to design a database to hold the questions and possible answers for each questionnaire, but I end up having separate tables for each question. Is this correct or am I going wrong somewhere?

For example

Table for question x

  Answer  | Answer ID
       1         019
       2         089

I cant have a fixed size table for all questions as I don't have a maximum amount of answers. This obviously means I could end up with hundreds of tables, one for each question.


texts: Is it OK to create hundreds of databases in SQL Azure versus one big one and run the risk of deadlocking

texts:
I need to create highly scalable solution - where field devices in thousands of sites are delivering data in real time to a back end system, and SQL Azure seems to fit the bill nicely in terms of adding sql databases and application servers.

Each field device is effectively sending 400 sensor values every second - for about two hours a day, and those 400 sensor values every 5 minutes for all other times forever. Additionally, when an error occurs on this field device, it will send up the last minute's data for all 400 sensors as well (400 * 60 readings) - causing a mass flood of data when anything goes wrong.

I really want to design the system so that the independent field devices and the data in which they store can not affect other devices. Allowing each field device to not affect the performance of other field devices.

I started the design with thinking a single database holding all the device's data - but have started to get deadlocks occurring when simulating multiple site devices. Hence, am in the process of moving to a multiple database solution. Where a master database holds a lookup table for all the devices - returning a connection string to the real database

At this stage of the project, it is most important that I am able to pass that data back to User Interfaces running in web browsers in real time - updating their screens every second.

In future stages of the project it will be necessary to start aggregating data across multiple devices showing statistics such as sum of sensor X in region Y. I can see this will be hard to do with the multiple database approach.

So would value any advice e.g.

Do you think it is sensible to use Sql Azure to host potentially 1000's of databases and to use this master database to indirectly point to the real ones?

Will I have a problem with Connections to the databases from the applications- with issues with connection pooling for example?

How will I be able to aggregate data from all these different databases in Sql Azure.

Would be interested in all your comments. Regards, Chris.


texts: SQL Server 2012: How do I capture server activity during a specific time frame?

texts:
SQL Server Gurus,

I have a situation on one of our servers. This server's sql agent job executes an SSIS flow every hour 24/7. The SSIS flow boils down to inserting/updating/deleting multiple tables while in a transaction block.

Another remote job executes at 8 AM daily which looks at a view that displays the data written by the SSIS job.

I have the view that the remote job reads from set up with a nolock hint to avoid locking errors--since the writes are near continuous throughout the day, however the occasional consequence is the remote job will sometimes not pick up the most current data--basically because the last launch of the SSIS job is still in the midst of writing rows out. I don't usually hear about it until several days have passed, so I lose the opportunity to do a quick sp_who2, etc. to survey what else might be going on with the server that would cause especially slow writes.

2 questions--the first, is how do I set something up going forward to monitor and capture data from the sever for the time slots the remote job executes/reads from my server/view? The second, any suggestions for a better approach to managing the write/lock/read avoidance? Any advice you have is welcomed.


texts: Where Clause for Results Appearing after Occurence

texts:
I'm wondering if it's possible to find records that only appear after the first occurrence of a where clause. I'm currently using PostgreSQL for this example but something not PostgreSQL specific would be preferred.
If I had a table such as this for an example
drop table if exists dummy;
create table dummy (
   col_a   varchar(10),
   col_b   varchar(10),
   col_c   varchar(10),
   test    integer
);

insert into dummy values
('A', 'B', 'C', 1),
('B', 'C', 'A', 2),
('C', 'A', 'B', 3),
('D', 'E', 'F', 4);

If I run the bellow query I can get the data in a new order
select * from dummy
order by col_b;

returning
C | A | B | 3
A | B | C | 1
B | C | A | 2
D | E | F | 4

Is it possible to do something so that I can only return the rows occurring after a certain where clause, something like
select * from dummy
/* where row appears after results 'A', 'B', 'C', 1 */
order by col_b;

To get
B | C | A | 2
D | E | F | 4

UPDATE
The reason I am not using where col_b > ? is because it doesn't cater for duplicate values
It would work until I need to match on an additional column like this
select * from dummy
where col_b > 'B'
and col_c > 'C'
order by col_b;

In which case it will return the last row because both values are above the cutoff but it is not cutting off after the first occurrence of the specified row.
I'm trying to have a where clause that can identify the specific row being row
A | B | C | 1

In this case


texts:
I am working with postgresql-9.4 and for support and monitoring tasks I need to know how long my server has been up for? Is there any function or way to know this? Is there an sql way to know when my server started?

I really need to know the elapsed time since it started?


texts: Where to find the size of SQL Server data types?

texts:
This question has already been asked here but none of the answers was really helpful in finding out the size for example, of a XML variable.

declare @myVariable XML

I have learned from this question:
how to test if XML = '' in sql server?

  Please also note that while it is possible for the internal
  representation of XML data to change between versions, I have tested
  on SQL Server 2008 R2, 2012, and 2014 and the size of an empty XML
  item is consistently 5.

How can I find the size of each SQL Server Data Type?

How can I prove that the size of an XML is 5?

all this started when I was tuning a stored procedure and while using XML as a parameter
improving performance - how to pass a table as parameter in sql server 2005?


texts: How to write a check constraint to disallow empty varchar fields?

texts:
On SQL Server not nullable varchar columns can hold empty strings.

Under ORACLE they can't, because varchar2 treat '' as equivalent to NULL.

If you design a database schema suitable for both RDBMS it seems to be a good idea to add to each not nullable varchar column on SQL Server a constraint which disallows empty strings.

But which is the best way to formulate such a constraint?

I started with

if OBJECT_ID('varchar_without_empty_cols') > 0 drop table varchar_without_empty_cols
go

create table varchar_without_empty_cols (
id int ,
val varchar(10) not null CHECK (val <> '')
)
go

insert into varchar_without_empty_cols values (1, ' ')  -- this ought be OK
go
insert into varchar_without_empty_cols values (2, '')   -- this has to violate the check
go
insert into varchar_without_empty_cols values (3, null) -- this violates the not null
go

select * from varchar_without_empty_cols

But this constraint not only inhibits empty strings, which is intended, but it also inhibits strings consisting of a single character, and that is not what I intend.


texts:
I'm building a MySQL database and a frontend.  One function of the database application is to make an invoice.  I'm looking for a way to archive all of the data that went onto the invoice so that if some data changes, the invoiced data is still available.

For example:
On table customers,

|---------+-------------------+-------------|
|  name   | email             | phone       |
|---------+-------------------+-------------|
|  George | george@domain.com | 3179094235  |
|---------+-------------------+-------------|

Suppose I make an invoice for George in year 2017.  In 2018, George changes his phone number.  I want my database to have a current phone number for George.  Now in 2018, my customers table looks like

|---------+-------------------+-------------|
|  name   | email             | phone       |
|---------+-------------------+-------------|
|  George | george@domain.com | 2024123987  |
|---------+-------------------+-------------|

but I want the data that my invoice used to still be used in 2018 when I generate the invoice document again.

In the real world, much more data will be put onto the invoice.  How can I make it so that the invoice that I generate in 2017 will use all of the same data as the one I generated in 2018, even if I've updated the data in my database?  Invoice is currently generated from data from multiple tables: parts, labor, customer info, etc. Can I archive all of the invoice data to a table, and always generate invoices from the archive table? Snapshots? Transactions?  I'm new to databases, so I don't know if I'm looking for a MySQL feature or a database design technique, or something else.


texts:
I'm running this update script in PostgreSQL 9.6:

do $$
begin
    create temporary table if not exists tmp_rutting1 (
        road_id integer,
        direction_id integer,
        begin_km double precision,
        end_km double precision,
        owp_max_rut_depth double precision,
        iwp_max_rut_depth double precision);

    insert into tmp_rutting1
    (
    select  rut.road_id,
        case when rut.direction_id in (1,2) then 1
        else 2
        end as direction_id,
        case when rut.direction_id in (1,2) then rut.begin_km
        else
            rut.end_km
        end as begin_km,
        case when rut.direction_id in (1,2) then rut.end_km
        else rut.begin_km
        end as end_km,
        rut.owp_max_rut_depth, rut.iwp_max_rut_depth
        from pms.pms_raw_rutting rut left join rrs_road r on r.id = rut.road_id
        where r.network_id = 9 and r.road_type_id = 1
        );

    CREATE INDEX idx_tmp_rutting1 ON tmp_rutting1 USING btree (road_id, direction_id, begin_km, end_km);

    update md.md_video upd
    set lrut = rutting.owp_max_rut_depth, rrut = rutting.iwp_max_rut_depth
    from tmp_rutting1 as rutting
    where upd.road_id = rutting.road_id

    and upd.direction_id = rutting.direction_id                 -- Scenario 1: terminated after 4 700 000ms
    --and upd.direction_id = 1 and rutting.direction_id = 1             -- Scenario 2: 250 783ms
    --and upd.direction_id = 2 and rutting.direction_id = 2             -- Scenario 3: 53 300ms
    and upd.chainage_km >= rutting.begin_km
    and upd.chainage_km < rutting.end_km;

    drop table tmp_rutting1;
end $$

The temp table gets 1 435 065 records, and takes 3 710ms to create, fill and index.

The query takes more than an 2 hours, then I terminated it, because that doesn't make sense.

Notice the 2 commented lines. If I uncomment them, one at a time, the script takes about 5 minutes (exact times shown in brackets).

The direction_id field only contains either a 1 or a 2. No null direction_id values exist. Not in the tmp table, nor in the 'update' table.

Why does it take so much longer (actually it doesn't even complete) with the upd.direction_id = rutting.direction_id statement in the where clause?

The following links shows the explain diagrams for the 3 scenarios. One would expect Scenario 2 and 3 to have the same diagrams, but they differ dramatically.

Explain Diagrams and Query Plans

Here is the layout of the table being updated:

CREATE TABLE md.md_video
(
  id serial NOT NULL,
  road_id integer,
  latitude double precision,
  longitude double precision,
  altitude double precision,
  begin_km double precision,
  end_km double precision,
  chainage_km double precision,
  frame_number integer,
  liri double precision,
  riri double precision,
  iri double precision,
  lrut double precision,
  rrut double precision,
  path_id integer,
  survey_date date,
  lane_nr integer,
  direction_id integer,
  new_road_id integer,
  pd_video character varying,
  import_comment character varying,
  camera_direction integer,
  CONSTRAINT pk_md_video PRIMARY KEY (id),
  CONSTRAINT "Ref_md_video_to_vid_path" FOREIGN KEY (path_id)
      REFERENCES md.vid_path (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE md.md_video
  OWNER TO postgres;

-- Index: md.idx_md_video

-- DROP INDEX md.idx_md_video;

CREATE INDEX idx_md_video
  ON md.md_video
  USING btree
  (road_id, direction_id, chainage_km);

-- Index: md.ui_md_video

-- DROP INDEX md.ui_md_video;

CREATE UNIQUE INDEX ui_md_video
  ON md.md_video
  USING btree
  (road_id, camera_direction, direction_id, lane_nr, chainage_km, survey_date);


texts:
I have installed Ubuntu 15.04

I've bind the IP address, there are no firewalls installed, but yet connection is still being refused! I've tried everything I could find online, anyone have any way of doing this or testing to see what is actually wrong?

The "master" server is what I'm trying to connect to from a "slave" server, the master is running MySQL, and is setup with tcadmin and running fine on port 8880. What I'm struggling to do is allow remote connection from the "slave" to the "master".

Root has permission from any host.

I'm using tcadmin to connect on the master server, but when going through the setup, I get

Can't connect to MySQL server on '151.80.108.75' (10061): No connection could be made because the target machine actively refused it 151.80.108.75:3306

in ssh this works

mysql -u root -h 127.0.0.1` - which works

but when using the IP of the server connection is refused, When connecting via the IP it gives error:

  ERROR 2003 (HY000): Can't connect to MySQL server on '' (111)

This is all I have in the cnf file:

[mysql]
bind-address        = ***.***.***.***`

I can ping the address, it currently has tcadmin on it, which uses the port 8880 which works fine, but for some reason, I can't get the remote access to work with MySQL. I'm able to ping it from a remote location.

Doesn't allow connection via telnet, but Unsure how to open a port that isn't being blocked? There's no firewalls installed, nothing should be limiting it, port 8880 for tcadmin works fine?


texts: Copy primary keys/clustered indexes from Prod to DEv/Test env

texts:
We followed the best practices of creating Primary Keys/Clustered indexes on tables which are actively queried on prod.

Now there is a scenario where we need to get the similar above changes done onto DEV/test. Since we did not expect we might require this on dev, we never documented so as to map similar changes.

Is there a better and faster way of doing this rather than , manually scripting out and creating them henceforth.

Thanks.


texts: SQL Server Agent initiating self-termination

texts:
For the past month, one of my servers is facing issues with SQL Server Agent. It stops automatically with below error, which happens randomly:

  Sql Agent Output file
  [097] Memory leak detected [204 bytes]
  Exception 5 caught at line 403 of file e:\sql9_sp2_t\sql\komodo\src\core\sqlagent\src\alerter.cpp. SQLServerAgent initiating self-termination

I googled this error but did not get much info about it. Both SQL Server and Agent are running with the same domain account; however, the issue is only with the SQL Server Agent service.

I am also unable to see the SQL Server Agent service in SQL Server Configuration Manager. I checked the SQL Server error log, and Windows Event Viewer logs, but did not see any suspicious activity.

My memory settings are configured correctly. The server has 32GB RAM and SQL Server is assigned to use max memory of 26GB.

System details:

SQL Server 2012 SP2 Std Edition 11.0.5058 64 bit
Windows Server 2008 R2 Ent Edition 64 bit
Dedicated to SQL Server (no other apps run on the server)
The server was upgraded a few months ago from SQL Server 2005 EE to 2012 SE.
We are not using an Events Forwarding Server.

I checked the msdb database as well. Ran checkdb on it and it completed successfully without any errors. I need your help in fixing two things:

To fix the random stopping of SQL Server Agent service
To add SQL Server Agent service in SQL Server Configuration Manager

We have third-party software for monitoring, so we get notified immediately if the agent service stops. For now we are just restarting the service. But we want to find the root cause.

I ran the following query suggested by Sean Gallardy and got 95 rows back. I don't know how to interpret the result.

select *
from sys.dm_os_loaded_modules
where name not like '%microsoft%'

Unfortunately I cannot post the results for security reasons.

Until I find the solution to this problem I have added retry attempts to the SQL Server Agent so that it can restart automatically when it gets stopped.


texts: What is the maximum length of a comment in PostgreSQL?

texts:
PostgreSQL has a special piece of a functionality called COMMENT which attaches metadata to an identifier or any type (schema, table, column, function, type, domain, etc). It looks like this,

COMMENT ON TABLE mytable IS 'This is my table.';

Is there a maximum size for the comment?


texts:
I want to truncate the majority of data on a slave such that it only has the most recent records.  I'll eventually demote the current master, making it a read-only archive, and promote the truncated slave to master.  The primary motivation for truncation is reclamation of disk space; I want to dump and restore the slave.

Questions:

Regarding the dump/restore operation on the slave, I'm planning on stopping the IO and SQL threads, then dumping the table, then dropping the table, the restoring from the dump, then starting the slave threads.  Does this sound like the right process?
What happens, after deleting rows from the slave, if the master sends an UPDATE or DELETE referencing a deleted row?


texts: Do Triggers in the Active Secondary Database of an AlwaysOn Group Execute?

texts:
If I have triggers on tables in a database that is a member of an AlwaysOn group, will they fire on the secondary? If they do fire, and they do something different on the secondary will it matter as long as it's not writing to the secondary db? For example, something external to the db, like call a service broker service, or insert into an external db if it and the table exists, will that succeed on the active secondary database?

Damien


texts: best performance for storing notifications in postgresql

texts:
I have a question concerning the performance of 2 different options, which one is better?
Explanation:
I have 5 tables in a postgresql db: users, followers, posts, likes and comments. They look like this (simplified):
users (
  user_id bigint,
  username character varying(24)
);
followers (
  follower_id bigint,
  user_id bigint,
  following_user_id bigint,
  timestamp timestamp with time zone DEFAULT NOW()
);
post (
  post_id bigint,
  user_id bigint,
  text text,
  timestamp timestamp with time zone DEFAULT NOW()
);
likes (
  like_id bigint,
  post_id bigint,
  user_id bigint,
  timestamp timestamp with time zone DEFAULT NOW()
);
comments (
  comment_id bigint,
  post_id bigint,
  user_id bigint,
  comment text,
  timestamp timestamp with time zone DEFAULT NOW()
);

I now want to make a notifications component in my app to show someone he received a new like or comment on a post.
FIRST OPTION:
The first option (I have now) is to create a sixth table named notifications:
notifications (
  notification_id bigint,
  type character varying(12),
  g_user_id bigint,
  r_user_id bigint,
  s_user_id bigint,
  post_id bigint,
  timestamp timestamp with time zone DEFAULT NOW()
);

A notification row would look like this:
+---------------------------------------------------------------------------------------+
| notification_id | type   | g_user_id | r_user_id | s_user_id | post_id | timestamp    |
+---------------------------------------------------------------------------------------+
| 1               | post   | 1         | NULL      | NUL       | 1       | 2021-01-01...|
| 2               | comm   | NULL      | 2         | 1         | 1       | 2021-01-01...|
| 3               | like   | NULL      | 1         | 2         | 1       | 2021-01-01...|
| ...                                                                                |
+---------------------------------------------------------------------------------------+

It seems to me this is the easiest solution, a query to select and order the notifications for a user (and fetch meta-data like the username of the sender_user_id) is easy with a JOIN.
The problem I'm facing is: I have more notification types (not only likes and comments but around 20 different notification types). Some notifications are for a user specific (defined in receiving_user_id), some notifications are 'general' and are for all users following an account (I defince this in general_user_id).
My (simplified) query to fetch these notifications now look like this. I'm selecting all notifications for r_user_id (receiving_user_id) 1 with a JOIN to the table users to select all usernames from the sending_user_ids (s_user_id) (if not null) and to select all "general" notifications (all notifications with g_user_id NOT NULL where g_user_id is a user_id that user_id = 1 is following in the table followers).
SELECT
    a.notification_id,
    a.type,
    a.timestamp,
    b.username
FROM
    notifications a
LEFT JOIN
    users b
  ON
    a.s_user_id = b.user_id
WHERE
    (
      a.r_user_id = 1
      OR
      a.g_user_id IN (SELECT following_user_id FROM followers WHERE user_id = 1)
    )
ORDER BY
    a.timestamp DESC

The biggest problem I have with this solution is that it requires an extra table and extra commands in my node.JS code. When someone likes a post, I have to create a notification row, when some dislikes a post, I have to delete this notification again.
SECOND OPTION:
My second option is to just leave these 5 tables and with a UNION ALL query "create" the notifications on the fly, if possible.
I don't know how other people create a notification system, and I didn't found much tutorials about it. Is it OK to create a separate notifications table (and use postgresql trigger functions to automatically create/delete notification rows), or should I create these notifications 'on the fly' based on the rows in likes, comments and posts?


texts: Foreign Key referencing multiple tables in a Question Bank scenario

texts:
I am working on a business domain in which there are QuestionBanks that contain a set of Questions. Multiple Quizzes can refer to a specific QuestionBank (BankId), and there is an entry in QuizQuestionStudent for each Question on the Quiz for each Student.

I have created the following tables to shape my database structure:

CREATE TABLE QuestionBanks (
    BankId UniqueIdentifier --PK
)

CREATE TABLE Questions (
    BankId UniqueIdentifier, --PK
    QuestionNumber Int, --PK
    QuestionText VarChar(1000)
)

CREATE TABLE Quizzes (
    QuizId UniqueIdentifier, --PK
    BankId UniqueIdentifier
)

CREATE TABLE QuizQuestionStudent (
    QuizId UniqueIdentifier, --PK
    QuestionNumber Int, --PK
    StudentId UniqueIdentifier, --PK
    IsCorrect Bit
)

What is the best way to design the schema such that the QuizQuestionStudent also has a Foreign (FK) to Questions (to ensure only valid questions get added). I essentialy want a FK that consists of columns in QuizQuestionStudent (QuestionNumber) as well columns in the Quizzes table (BankId).

One solution is to repeat the BankId for each row in QuizQuestionStudent, but that seems quite wasteful.

Is there any other way to achieve this?


texts: High PageIOLatch_SH Waits with High Drive Idle times

texts:
We are experiencing high volume of PageIOLatch_SH waits on our database (row counts in the Billions).  However it seems that our drive Idle time Percentage hovers around 50-60 percent.  CPU usage is nill.  The Database Tuning Advisor gives no suggestions for optimization.  The query plan (actual) from the single stored procedure used on the database puts the majority of the expense on index seek (yeah I know these should be optimial) operations.

Anyone have suggestions of how to increase throughput?


texts: Show sql statements from mysql binlog (GTID)

texts:
I have mysql binlogs  for replication through GTID.

I'm trying to show executed update/insert statements like this:

mysqlbinlog --base64-output=DECODE-ROWS mysql-bin.000024

But all I see is something like this, no traces of update or insert stmt:

SET TIMESTAMP=1431681617/*!*/;
BEGIN
/*!*/;
# at 746987321
# at 746987392
# at 746987484
#150515 11:20:17 server id 1  end_log_pos 746987515 CRC32 0xeb874754    Xid = 997501767
COMMIT/*!*/;
# at 746987515
#150515 11:20:22 server id 1  end_log_pos 746987563 CRC32 0xc5ece64a    GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'a4ade293-c63a-11e4-94cf-005056944a56:2059057'/*!*/;
# at 746987563
#150515 11:20:22 server id 1  end_log_pos 746987650 CRC32 0x92296355    Query   thread_id=71622 exec_time=0 error_code=0

I'm not sure about format/configuration of GTID replication and where to look for it...


texts: Decode URL encoded characters in inline table valued function?

texts:
I'm looking for a inline table valued function that decodes URL encoded characters (like %20 for space). reference

I see other functions but haven't seen one that is an inline table valued function. I'm tempted in just writing a bunch of REPLACE statements in a function but I'm wondering if there's a better way.

CREATE FUNCTION dbo.itvfURLDecode
(
    @StringValue VARCHAR(MAX)
)
RETURNS TABLE

AS
RETURN (
    SELECT REPLACE(REPLACE(@StringValue,'%2B','+'),'%20',' ') /* etc..*/ AS DecodedValue
)

;WITH cteData AS
(
    SELECT 'This%2Bis%20a%20test%3C%3E%20ok%3F%20100%25' AS example
)
SELECT
    example, q.DecodedValue  /* returns: This+is a test<> ok? 100% */
FROM cteData c
CROSS APPLY dbo.itvfURLDecode(example) q


texts: Effect of Reorganizing and Rebuilding an index

texts: What are the effects of Index Rebuilding and Reorganizing on clustered and non-clustered indexes. How does these 2 operations affect the logical storage and physical storage of data in Hard disk?


texts: SSRS 2012 - Dynamically change value of textbox in report header

texts:
I am in the process of converting some MS Access 2003 reports to SSRS 2012 (using VS 2010 to develop the reports).  I have a report with a number of parameters.  Some of the parameters are populated dynamically from a query (Location, Client, etc).  The report header is set up in the following way:

REPORT TITLE

Location

Date

Detail data tablix (Type, size, etc)

There is a grouping on the Location ID value.  Each page of the report is for each location selected from the Location parameter list (ie New Jersey, New York, Washington, etc).  I get the following error on the Location text box:
The Value expression for the text box 'Location' refers directly to the field 'LocationName' without specifying a dataset aggegate.  When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions which specify a dataset scope.

The first function just shows the first location in the list on each page.  I tried to use Lookup function, but still got the same aggregate error.

I know there has to be a way to do this.  (I am new to SSRS, but learning VERY fast.)

Thank you - Susan


texts: What does "Deleting unrecoverable checkpoint table row" mean?

texts:
When I look at our production SQL Server 2016's server logs I am seeing multiple entries that say:

  [INFO] Database ID: [5]. Deleting unrecoverable checkpoint table row
  (id: 294).

The id at the end is incremented by 1 for each entry.

I Googled for this error and there is literally nothing about it.  What does this entry mean?  It sounds rather ominous.


texts:
I need to calculate a rolling sum over a date range. To illustrate, using the AdventureWorks sample database, the following hypothetical syntax would do exactly what I need:

SELECT
    TH.ProductID,
    TH.TransactionDate,
    TH.ActualCost,
    RollingSum45 = SUM(TH.ActualCost) OVER (
        PARTITION BY TH.ProductID
        ORDER BY TH.TransactionDate
        RANGE BETWEEN
            INTERVAL 45 DAY PRECEDING
            AND CURRENT ROW)
FROM Production.TransactionHistory AS TH
ORDER BY
    TH.ProductID,
    TH.TransactionDate,
    TH.ReferenceOrderID;

Sadly, the RANGE window frame extent does not currently allow an interval in SQL Server.

I know I can write a solution using a subquery and a regular (non-window) aggregate:

SELECT
    TH.ProductID,
    TH.TransactionDate,
    TH.ActualCost,
    RollingSum45 =
    (
        SELECT SUM(TH2.ActualCost)
        FROM Production.TransactionHistory AS TH2
        WHERE
            TH2.ProductID = TH.ProductID
            AND TH2.TransactionDate <= TH.TransactionDate
            AND TH2.TransactionDate >= DATEADD(DAY, -45, TH.TransactionDate)
    )
FROM Production.TransactionHistory AS TH
ORDER BY
    TH.ProductID,
    TH.TransactionDate,
    TH.ReferenceOrderID;

Given the following index:

CREATE UNIQUE INDEX i
ON Production.TransactionHistory
    (ProductID, TransactionDate, ReferenceOrderID)
INCLUDE
    (ActualCost);

The execution plan is:

While not horribly inefficient, it seems like it should be possible to express this query using only window aggregate and analytic functions supported in SQL Server 2012, 2014, or 2016 (so far).

For clarity, I am looking for a solution that performs a single pass over the data.

In T-SQL this is likely to mean that the OVER clause will do the work, and the execution plan will feature Window Spools and Window Aggregates. All language elements that use the OVER clause are fair game. A SQLCLR solution is acceptable, provided it is guaranteed to produce correct results.

For T-SQL solutions, the fewer Hashes, Sorts, and Window Spools/Aggregates in the execution plan, the better. Feel free to add indexes, but separate structures are not allowed (so no pre-computed tables kept in sync with triggers, for example). Reference tables are allowed (tables of numbers, dates etc.)

Ideally, solutions will produce exactly the same results in the same order as the subquery version above, but anything arguably correct is also acceptable. Performance is always a consideration, so solutions should be at least reasonably efficient.

Dedicated chat room: I have created a public chat room for discussions related to this question and its answers. Any user with at least 20 reputation points can take part directly. Please ping me in a comment below if you have less than 20 rep and would like to take part.

Discussion for "Date range rolling sum using window functions"


texts: SSDT - Exclude certain schema along with unnamed constraint

texts:
Task:

Automate database deployment (SSDT/dacpac deployment with CI/CD)
The database is a 3rd party database
It also includes our own customized tables/SP/Fn/Views in separate schemas
Should exclude 3rd party objects while deploying the database project(dacpac) to Production
Thanks to Ed Elliott for the AgileSqlClub.DeploymentFilterContributor. Used the dll to filter out the schema successfully.

Problem:

The 3rd party schema objects(Tables) are defined with unnamed constraints(default / primary key) when creating the tables. Example:

CREATE TABLE [3rdParty].[MainTable]
(ID INT IDENTITY(1,1) NOT NULL,
CreateDate DATETIME DEFAULT(GETDATE()))  --There is no name given to default constraint

When I generate the script for deployment using sqlpackage.exe, I see following statements in the generated script.

Generated the script using:

    "C:\Program Files\Microsoft SQL Server\150\DAC\bin\sqlpackage.exe" /action:script /sourcefile:C:\Users\User123\source\repos\DBProject\DBProject\bin\Debug\DBProject.dacpac /TargetConnectionString:"Data Source=MyServer; Initial Catalog=MSSQLDatabase; Trusted_Connection=True" /p:AdditionalDeploymentContributorPaths="C:\Program Files\Microsoft SQL Server\150\DAC\bin\AgileSqlClub.SqlPackageFilter.dll" /p:AdditionalDeploymentContributors=AgileSqlClub.DeploymentFilterContributor /p:AdditionalDeploymentContributorArguments="SqlPackageFilter=IgnoreSchema(3rdParty)" /outputpath:"c:\temp\script_AfterDLL.sql"

Script Output:

/*
Deployment script for MyDatabase

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/
...
...
GO
PRINT N'Dropping unnamed constraint on [3rdParty].[MainTable]...';

GO
ALTER TABLE [3rdParty].[MainTable] DROP CONSTRAINT [DF__MainTabl__Crea__59463169];

...
...
...(towards the end of the script)
ALTER TABLE [3rdParty].[MainTable_2] WITH CHECK CHECK CONSTRAINT [fk_518_t_44_t_9];

I cannot alter 3rd party schema due to company restrictions
There are many lines of unnamed constraint and WITH CHECK CHECK constraints generated in the script.

Question:

How can I be able to remove the lines to DROP unnamed Constraint on 3rd party schemas? - Even though the dll excludes 3rd party schema, it still has these unnamed constraints scripted/deployed. Also, it is not Adding them back too !!
How can I be able to skip/remove generating WITH CHECK CHECK CONSTRAINT on 3rd party schemas

Any suggestions will be greatly helpful.

EDIT:

Also, I found another issue. The deployment will not succeed due to Rows were detected. The schema update is terminating because data loss might occur

Output:

/*
The column [3rdParty].[MainTable_1].[Col1] is being dropped, data loss could occur.

The column [3rdParty].[MainTable_1].[Col2] is being dropped, data loss could occur.

The column [3rdParty].[MainTable_1].[Col3] is being dropped, data loss could occur.

The column [3rdParty].[MainTable_1].[Col4] is being dropped, data loss could occur.
*/

IF EXISTS (select top 1 1 from [3rdParty].[MainTable_1])
    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT

GO


texts: index corrupt error while trying to index a large table

texts:
I am trying to index a large table, approximately 12 million rows and 33GB in size. There is currently no indexing on the table. When I am trying to index one of the columns in a table, I get the error: " Index for table 'table_name' is corrupt; try to repair it". But there are no existing indexes in this table, not sure why this error.

I am using "alter table table_name add index (DateTime);" query to add index to the table.

Can anyone please advise where could be the problem?

Its an InnoDB engine.


texts: How to capture all user's query on Oracle database

texts:
I have an Oracle Database installed over Unix system. On this single instance database I have a lot of sessions opened from an application pool (application is installed on another server). Every sessions are opened with the same user name. As sessions can be closed by the pool (really, it can be) I can't capture session id and - for each - enable query trace functionality.
The application is a closed source code (and proprietary), so I can't (and don't want to) reverse engineering it.

Now, I need to capture every query that this user execute. Ideally I want to get one file by user session (but it will be perfect if I get one file with all queries). My real goal is to get queries but session_id and/or query's timestamp could be welcome.

Important note: as my database is a Standard Edition I have no access to any Oracle advanced pack as "Oracle Tuning Pack", "Oracle Diagnostic Pack" or so on ... I only have access to the StatPack and regular SQL query over all database dictionary objects (as a sysdba could be).

Platform information:

Unix RedHat 6.x
Oracle Database 11g Standard edition
only one database instance
number of sessions opened between 150 and 250

Thanks for your ideas

Thomas


texts: Is there a way to show index_advantage more friendly?

texts:
Is there a way to show index_advantage more friendly like 99.54%?

SELECT TOP 25
user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage]
FROM sys.dm_db_missing_index_group_stats
ORDER BY index_advantage DESC;

It shows a number like 41554133.7 so I want to show in percentage e.g. 99.54.


texts: Migrate Availability Groups from SQL Server 2014 to 2016

texts:
Is it possible to migrate AGs 2014 to 2016?
We are currently on 2014.

Or we need to rebuild from scratch on 2016 with old backup files.

Anyone who have done it please suggest the pros and cons .

What things to consider for this change .


texts: Configure postgresql recovery again to be slave

texts:
I completed a replication setup using PostgreSQL. Everything was working fine, so I proceeded doing a test by bringing down the main database and promoting the slave to be new master (via touch trigger file).

Again,it worked as expected and the slave became a full read/write database. Now, I want to return everything to normal state which means starting up master database, copying all the transactions that were done on slave during the transition, and finally start again the replication on slave. How can I do this... there seems to be no docs about restoring everything to normal state. Should I just rename the recovery.done to recovery.conf?

Thanks a lot


texts: MySQL: list users which have been granted EXECUTE permission on a given procedure

texts:
I would like to audit my database to check which users have been granted individual EXECUTE permission to which procedure.

I would like to use this information to be able to save GRANTs when changing (DROP+CREATE) the definition of some procedure in order to restore them after.

How can I query the INFORMATION_SCHEMA or MYSQL schemas to retrieve that information?

Update I'm interested in a solution working on MySQL 5.6+.

For granular security, users of our datase are not granted an EXECUTE permission on the whole schema but on specific stored procedures. Answers on schema-level permissions are irrelevant.


texts: Oracle: Sum function "not a single-group group function error"

texts:
I have an ORDER_LINE table in which I am trying to multiply the NUM_ORDERED of parts by the QUOTED_PRICE for each ORDER_NUM. I am adding SUM() function to group by ORDER_NUM, because if I didn't, there would be multiple entries for the same order.

ORDER_LINE Table:

ORDER_NUM   PART_NUM    NUM_ORDERED     QUOTED_PRICE
21608       AT94        11              21.95
21610       DR93        1               495
21610       DW11        1               399.99
21613       KL62        4               329.95
21614       KT03        2               595
21617       BV06        2               794.95
21617       CD52        4               150
21619       DR93        1               495
21623       KV29        2               1290

Query:

SELECT ORDER_NUM, SUM(NUM_ORDERED * QUOTED_PRICE) AS TOTAL_AMOUNT
FROM
ORDER_LINE
ORDER BY ORDER_NUM;

Expected Result:

ORDER_NUM   TOTAL_AMOUNT
21608       241.45
21610       894.99
21613       1319.8
21614       1190
21617       2189.9
21619       495
21623       2580

  Error ORA-00937: not a single-group group function

If I remove the Sum function I get the following result:

SELECT ORDER_NUM, (NUM_ORDERED * QUOTED_PRICE) AS TOTAL_AMOUNT
FROM
ORDER_LINE
ORDER BY ORDER_NUM;

ORDER_NUM   TOTAL_AMOUNT
21608       241.45
21610       495
21610       399.99
21613       1319.8
21614       1190
21617       1589.9
21617       600
21619       495
21623       2580


texts: Automatic incremental backup of MySql on windows

texts:
I have three Mysql Servers, Server-1, Server-2 and Server-3 all running on Windows. I have to configure Master-Master replication on Server-2 and Server-3.

But Server-2 has to be a mirror of Server-1. I have configured binlog on server-1. So I basically need a cron job that can run daily and ftp all the bin-logs created by Server-1 to the system having Server-2 and execute the queries from these binlogs on Server-2. Once the Server-2 gets into the same state as Server-1, I want all the bin logs to be deleted.

How should I do that.


texts: SQL Server Estimated I/O Cost in real units

texts:
As I understand from other StackExchange posts, SQL Servers estimates are unitless meaning an Estimated I/O cost of 2.82387 doesn't refer to the time taken or disk I/O's, but a relative measure of how it compares to other query criteria such as the CPU cost.

Is it possible to get a real estimate of disk I/O's for a query, how many pages it believes it will read in etc.? I'm also interested in the actual costs in these units.

As part of a course I'd like to use methods for calculating estimates (Disk I/O's and result sizes) manually and comparing these to SQL Server results for the same queries.

Is it possible real information on execution plans in SQL server and if so how? Or is it only possible to get this unitless information, which I can't really use for comparison.


texts: Can multiple cores be used by PostgreSQL to run code in sections (cursors) of a table effecting parallelism?

texts:
Let's say I have a 1 million record table. Each record is uniquely identifiable by id. If the table was divided into 10 cursors and 10 requests were made by a web client for each cursor to say change the last name field to upper, and 10 cores were available, would the work get done 10x faster? Or any time faster, compared to a straight for .. loop regular processing of the entire table?

I know PostgreSQl can't run parallel queries that make changes. I'm trying to get around that by using code instead. Note that these records are independent. All changes are strictly particular to the record.


texts: Offsetting reporting Load from one server to another

texts:
I have a SQL Server 2008r2  database ( Let's call it "BIG_DB") at 200GB.  This server gets hit hard.  It is the back end for an application, BIG_DB is a  source of a lot of reporting.

There are about 10 tables that are being queried for reports constantly and I would like to offset that load to another server( Let's call it "OFFSET").  The Boss wants a maximum 2 minute latency between the   BIG_DB AND OFFSET on these  10 tables.  This is my proposed solution:

When an insert or update happens on any one of those 10 tables on BIG_DB, I write that to a corresponding table ("UPDATE_TABLE") with a similar name and same column list + default GETDATE() column on BIG_DB.
Then have OFFSET grab the info on BIG_DB.UPDATE_TABLE and place it into the OFFSET database with the same table setup as BIG_DB.
I then have OFFSET serve the reports.

I have only one geographic location, servers are only in house. BIG_DB is OLTP. Is there a better solution? Do I go with replication, SSIS, or straight T-SQL with a linked server? or other?


texts: Setting up simple relational database structure

texts:
I'd like to set up simple MySQL database but I'm a bit confused about everything, so let me try to explain...

I have 4 relevant tables, let's call them POS, POS_STRUCT, POS_STRUCT_DIV and MEMBERS.

Relation goes like this:
- POS can have many POS_STRUCT but a single POS_STRUCT can be associated to only one POS
- POS_STRUCT can have many POS_STRUCT_DIV but a single POS_STRUCT_DIV can be associated to only one POS_STRUCT
- POS_STRUCT_DIV can have many MEMBERS but a single MEMBERS can be associated to only one POS_STRUCT_DIV

My table POS_STRUCT_DIV will be consisted of few 'constants'. Let's call them div_1, div_2 (...) div_n, and this is where my confusion starts.
Since this table will probably never change names or amount of those 'constants' should I even create a relation between POS_STRUCT and POS_STRUCT_DIV? Also, since records in MEMBERS will be sometimes associated to POS_STRUCT_DIV with a same name but on different POS and POS_STRUCT, should I create foreign keys for all 3 tables in MEMBERS table? What would be the most efficient way to solve this relation?

I tried to create an UML for this db, although I'm obviously not sure if it is correct at all...:


texts: Slow import for users .DMP files in Oracle 11G

texts:
After upgrading my DBMS Oracle 10g to 11g, when it will import user files, they are slower than the old version.

A .DMP file in Oracle 10g with the size of 20GB takes almost 1 hour to import, but Oracle 11g version takes almost 3 hours for the same .DMP file.

Export script:

NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss' ; export NLS_DATE_FORMAT
NLS_NUMERIC_CHARACTERS=',.'; export NLS_NUMERIC_CHARACTERS

exp system/datapar1 file=copy.dmp log=exp.log owner=username \
    buffer=4096000 recordlength =65535 statistics=none \
    feedback=500000 consistent=yes

Import script:

NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss'; export NLS_DATE_FORMAT
NLS_NUMERIC_CHARACTERS=',.'; export NLS_NUMERIC_CHARACTERS

imp system/password file=copy.dmp log=imp.log fromuser=username \
    touser=newusername ignore=yes

Could anyone help me with this?


texts: Mysql compatibility between client and server of different versions

texts:
How to know what version of mysql client is compatible with what version of the server?

Is there backward or forward compatibility tables from the client and/or server point of view?

For a given version of the server, how to know which clients are compatible, and conversely?


texts: Multi-expression indexes in PostgreSQL not using being used as expected

texts:
I have a PostgreSQL 9.3 table with multiple expression indexes defined. When I use explain to see which indexes are being used for my query, I am surprised to see that PostgreSQL is not using the index which most closely-matches the conditions in my WHERE clause. It appears to be a problem specific to expression indexes. Here is a sqlfiddle link which demonstrates the problem:

http://sqlfiddle.com/#!15/580e0/1

CREATE TABLE Table1
    ("a" int, "b" int, "c" int, "d" int)
;
CREATE index a ON Table1 (a);
CREATE index ab ON Table1 (a,b);
CREATE index bplus1 ON Table1 ((b+1));
CREATE index abplus1 ON Table1 ((a+1),(b+1));

The basic multi-column index ab is being used properly:

 explain select * from Table1 where a = 1 AND b = 1

    |                                                       QUERY PLAN |
    |------------------------------------------------------------------|
    | Index Scan using ab on table1  (cost=0.13..8.15 rows=1 width=16) |
    |                                Index Cond: ((a = 1) AND (b = 1)) |

However the similar multi-column expression index abplus1 isn't being used:

explain select * from Table1 where (a+1) = 2 AND (b+1) = 2

|                                                           QUERY PLAN |
|----------------------------------------------------------------------|
| Index Scan using bplus1 on table1  (cost=0.13..8.16 rows=1 width=16) |
|                                            Index Cond: ((b + 1) = 2) |
|                                                Filter: ((a + 1) = 2) |

The strange thing is that the more-specific index does get used if the single-column expression indexes are removed. See this example http://sqlfiddle.com/#!15/f4fda/1:

explain select * from Table1 where (a+1) = 2 AND (b+1) = 2

|                                                            QUERY PLAN |
|-----------------------------------------------------------------------|
| Index Scan using abplus1 on table1  (cost=0.14..8.15 rows=1 width=16) |
|                         Index Cond: (((a + 1) = 2) AND ((b + 1) = 2)) |

It looks to me like there is a conflict between the single-expression and the multi-expression indexes. The surprising thing is that there doesn't appear to be any such conflict when using basic (non-expression) indexes.

Is there anything that I can do to work around this problem? I would like to keep both of my expression indexes, as each one is well-suited for particular queries.


texts:
I am currently trying to deploy Load Balancing using HAProxy for my PostgreSQL cluster (used Patroni HA solution). Now, I have been able to successfully implement the setup and Load Balancing is working to an extent. My HAProxy configuration :

global
    maxconn 100

defaults
    log global
    mode tcp
    retries 2
    timeout client 30m
    timeout connect 4s
    timeout server 30m
    timeout check 5s

listen stats
    mode http
    bind *:7000
    stats enable
    stats uri /

listen read_write
    bind *:5004
    option httpchk OPTIONS /master
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg1 IP:5432 maxconn 100 check port 8008
    server pg2 IP:5432 maxconn 100 check port 8009

listen read_only
    bind *:5005
    mode tcp
    balance roundrobin
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg1 IP:5432 maxconn 100 check port 8008
    server pg2 IP:5432 maxconn 100 check port 8009

However, my question is how will the application interact with HAProxy for read only requests. Does the application need to connect using port 5005 or is there any internal logic applied by HAProxy to do this.

This is my first time implementing a Load Balancing setup, hence would appreciate your patience. :)


texts: Check if a database table has been renamed

texts:
My app has a list of an external db's tables and it needs to be regularly updated. How do I check if a table has been renamed? i.e. how to differentiate between a new table and an old table that's been renamed? Looking for sql/python/sqlalchemy solutions that'll work in as many engines as possible.
I'm aware of OID in postgres but as far as I could tell it isn't very reliable and there's no equivalent in mysql. I suppose I could do something with the create time and modify time but I'm trying to see if there's an easier way.
Edit: The app is a data visualisation tool to which external dbs connect. I do not have control over the design of dbs that connect to the app which is why I need to account for tables getting renamed.


texts: Does the PCI require to protect the Credit Card Info in the transaction log?

texts:
The PCI Data Security Standard https://www.pcisecuritystandards.org/documents/pci_dss_v2.pdf
forbid storing Credit Card info in the transaction log

3.2.1 For a sample of system components, examine data sources, including but not limited to the following, and verify that the full contents of any track from the magnetic stripe on the back of card or equivalent data on a chip are not stored under any circumstance:
Incoming transaction data

All logs (for example, transaction, history, debugging, error)
History files
Trace files
Several database schemas
Database contents

Could anybody with read access to the log or log backup file be able to read CC number?
It is not in a clear text. Still, is it possible to decript it?
Is it enough to restrict user access to the log and log backup files?