Moving a heap between filegroups I would like to move the tables in my database from one filegroup (Primary) to a another (a new one). I can find lots of advice on this concerning tables with clustered indexes but all these tables in question are heaps. So far I implemented the solution found here:

http://gallery.technet.microsoft.com/scriptcenter/c1da9334-2885-468c-a374-775da60f256f#content

which essentially provides a script to create a clustered index for the table on a secondary filegroup (which essentially moves the data) then removes the index to return it to being a heap. This seems to work fine but may be too slow for our production requirements.

Are there any alternative solutions? This doesn't feel quite elegant enough for me to believe as the only method.

Note1: The tables must remain heaps. A 3rd party app enforces this.

Note2: The DB is about 150GB in size across 200 tables. 197 tables and 99.9% of the data will move to the secondary filegroup.

You're going to have to physically move the data - there is no getting around that. But I don't know who came up with the idea that doing so requires creating a clustered index - this just exchanges certain steps for others, and isn't going to require any different amounts of temporary space to accomplish than the method I outline below.

Why not create a second, empty heap on the new filegroup:

CREATE TABLE dbo.whatever_copy
(
  ... cols ...
)
ON [second filegroup];

Then script the indexes, constraints etc. from the original table, copy the data over, remove any constraints that need to be removed (e.g. foreign keys pointing to the original table), then drop the original table, and rename the new one. Finally, add the indexes and constraints to the new table.

 -- script out indexes, constraints etc.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

INSERT dbo.whatever_copy(cols) WITH (TABLOCK)
  SELECT cols FROM dbo.whatever;

 -- remove constraints etc.

DROP TABLE dbo.whatever;

EXEC sp_rename N'dbo.whatever_copy', N'whatever', N'OBJECT';

 -- add constraints etc.

COMMIT TRANSACTION;


pgAdmin 4 v1.6 setting PostgreSQL Binary Path to a remote server to clear-up "The system cannot find the path specified." error Just wondering how to go about assigning binary path to a remote server. I have pgAdmin on my personal computer, but the server (CentOS) that PostgreSQL resides on is at a different location.

You are getting this error because when using EXPLAIN or pg_dump from pgAdmin, the program uses the utilities in its install directory $DIR/../runtime or C:\Program Files (x86)\pgAdmin 4\v1\runtime to execute these commands. You can put this location in the Postgresql binary path and continue with the operation.


Get top two rows per group efficiently Currently, I use a query with DISTINCT ON to get a list of recent posts for each author.
But how can I get previous recent posts for each author? In other words, how to make DISTINCT ON return second row of each group, not first.
I need this to compare recent post with previous one.
CREATE TABLE posts (
  title varchar(30),
  author varchar(30),
  created_at date
);

INSERT INTO posts VALUES
('Johns first post', 'John', 'January 1, 2021'),
('Johns second post', 'John', 'January 2, 2021'),
('Johns third post', 'John', 'January 3, 2021'),
('Mikes first post', 'Mike', 'January 1, 2021'),
('Mikes second post', 'Mike', 'January 2, 2021'),
('Mikes third post', 'Mike', 'January 3, 2021');

This query selects the most recent post for each author. (Third post for each in my example.):
SELECT DISTINCT ON (author) * FROM posts ORDER BY author ASC, created_at DESC

db<>fiddle here
I also need the previous post for each author (Second post for each in my example.)
I don't want to use window functions because my table is large enough, and I suppose window functions could be slow.

You can use ROW_NUMBER window function to set an order, then simply use it as a subquery with a proper predicate.
SELECT FROM
(
  Subquery
)
WHERE
    post_num <= 2;

SELECT
    title,
    author,
    created_at,
    ROW_NUMBER() OVER (PARTITION BY author ORDER BY created_at) as post_num
FROM
    posts

title             | author | created_at | post_num
:---------------- | :----- | :--------- | -------:
Johns first post  | John   | 2021-01-01 |        1
Johns second post | John   | 2021-01-02 |        2
Johns third post  | John   | 2021-01-03 |        3
Mikes first post  | Mike   | 2021-01-01 |        1
Mikes second post | Mike   | 2021-01-02 |        2
Mikes third post  | Mike   | 2021-01-03 |        3

db<>fiddle here


How to limit maximum number of rows in a table to just 1 I have a configuration table in my SQL Server database and this table should only ever have one row. To help future developers understand this I'd like to prevent more than one row of data being added. I have opted to use a trigger for this, as below...

ALTER TRIGGER OnlyOneConfigRow
    ON [dbo].[Configuration]
    INSTEAD OF INSERT
AS
BEGIN
    DECLARE @HasZeroRows BIT;
    SELECT  @HasZeroRows = CASE
        WHEN COUNT (Id) = 0 THEN 1
        ELSE 0
    END
    FROM
        [dbo].[Configuration];

    IF EXISTS(SELECT [Id] FROM inserted) AND @HasZeroRows = 0
    BEGIN
        RAISERROR ('You should not add more than one row into the config table. ', 16, 1)
    END
END

This does not throw an error but is not allowing the first row to go in.

Also is there a more effective / more self explaining way of limiting the number of rows that can be inserted into a table to just 1, than this? Am I missing any built in SQL Server feature?

These two constraints would do:

CREATE TABLE dbo.Configuration
( ConfigurationID TINYINT NOT NULL DEFAULT 1,
  -- the rest of the columns
  CONSTRAINT Configuration_PK
    PRIMARY KEY (ConfigurationID),
  CONSTRAINT Configuration_OnlyOneRow
    CHECK (ConfigurationID = 1)
) ;

You need both the PRIMARY KEY (or a UNIQUE constraint) so no two rows have the same ID value, and the CHECK constraint so all rows have the same ID value (arbitrarily chosen to 1).
In combination, the two almost opposite constraints restrict the number of rows to either zero or one.

On a fictional DBMS (no current SQL implementation allows this construction) that allows a primary key consisting of 0 columns, this would be a solution, too:

CREATE TABLE dbo.Configuration
( -- no ConfigurationID needed at all
  -- the rest of the columns
  CONSTRAINT Configuration_PK
    PRIMARY KEY ()                -- 0 columns!
) ;


SELECT with equal chance between same IDs - MySQL How can I SELECT data with equal chances for same IDs in MySQL (for php). For example, assume below data structure:

 id  value
 1   alfa
 1   beta
 1   gamma
 2   hello
 2   bye
 3   sky

I want (alfa, beta, gamma), (hello, bye) and (sky) have the same chances. Here is the probability I need for every row:

alfa: 1/3 * 1/3
beta: 1/3 * 1/3
gamma: 1/3 * 1/3
hello: 1/3 * 1/2
bye: 1/3 * 1/2
sky: 1/3

Thank you.

To select a random id with equal chances:

SELECT DISTINCT id
FROM tableX
ORDER BY RAND()
LIMIT 1 ;

You can combine the above in a subquery (derived table) to find a single row:

SELECT t.*
FROM tableX AS t
  JOIN
  ( SELECT DISTINCT id
    FROM tableX
    ORDER BY RAND()
    LIMIT 1
  ) AS i
    ON i.id = t.id
ORDER BY RAND()
LIMIT 1 ;


How can I speed up my count queries? Right now I am creating counts for each hour for the whole week. I am using python to connect to the database, and I am looping through each hour so str(i) is the current hour that is being updated.

UPDATE table1 p SET population_count_hour_' + str(i) + '=sub.popcount
FROM (SELECT state, count(*) as pop_count
     FROM table2
     WHERE start_time < ' + str(i*3600) + '
     AND end_time < ' + str((i-1)*3600) + ' GROUP BY state) sub
WHERE p.state=sub.state AND p.population_count_hour_' + str(i) + ' IS DISTINCT
FROM sub.pop_count;'

I have to do several queries in a similar fashion with different variables. Is there way to do this faster because right now it is a very slow process?

You should construct an UPDATE statement that updates many rows at once, along the lines of:

UPDATE table1
SET population_count_hour_1 = sub.pop_count_1,
    population_count_hour_2 = sub.pop_count_2,
    ...
FROM (SELECT state,
             count(*) FILTER (WHERE start_time < 3600 AND end_time < 0)    AS pop_count_1,
             count(*) FILTER (WHERE start_time < 7200 AND end_time < 3600) AS pop_count_2,
             ...
      FROM table2
      GROUP BY state) AS sub
WHERE p.state = sub.state;

That should be considerably faster.


Installing command line tool with pgAdmin4 I have pgAdmin4 and the latest version of PostgreSQL installed on my machine. When I tried to insert large amount of data using INSERT statements contained in a .sql file of size 40 MB, 50MB and above, the app crashed. Hence, I am wondering if dumping heavy data using those sql files via command line is a good idea? If yes, then from where can I install that command line utility in pgAdmin4 ?

Firstly, I have no idea about pgAdmin4

Secondly, It would take a lot of time when executing a large SQL file. Please consider using pg_dump (custom format) & pg_restore to enhance your performance.

  Output a custom-format archive suitable for input into pg_restore.
  Together with the directory output format, this is the most flexible
  output format in that it allows manual selection and reordering of
  archived items during restore. This format is also compressed by
  default.

For example:

-----#### Exporting large table with custom format
pg_dump -h your_host -p 5432 -U user_name  --format custom --no-password   --encoding UTF8  --no-security-labels --no-tablespaces --table=your_schema_name.your_table_name --verbose --file your_file_name.backup --schema your_schema_name your_database_name

-----#### Importing large table with custom format
pg_restore -h your_host -p 5432 -U user_name --dbname=your_database_name --role=user_name  --format custom --no-tablespaces --schema=your_schema_name --verbose your_file_name.backup


Database setup for datalogging We're currently starting to build a version 2 of our system and one of the things we've noticed with version 1 is that we'll have quite a bit of data so we started to rethink our approach to how to store the data.

Basically we have dataloggers which send data back to the server every 5 minutes. With current system we only have few sensors as we've just been starting so we only have approx 250 loggers out there and they are generating data at a pace of 2.5gb per year or about 21M rows per year. The data is mainly just numbers like light levels, temperature, humidity etc. We expect these logger numbers to grow quite fast and we should have thousands of loggers by end of the next year.

However with this next generation platform we'll have multiple sensors that can be used and multiple similar sensors can be connected to one system. System will have about 8 ports for sensor boards but each sensor board can have multiple sensors. As an rough example one system can have anywhere from 5 to 100 different kind of sensor inputs. ( 2-10 times as much as we currently have)

Data is only written once and then only read. 99% of the reads are to data which is maximum of two months old.

So far I've come up with few different options and I'd love to hear if someone has good insights which one might be the most scalable.

Have a main Ping table which contains some basic data for each report and then have separate tables for each sensor board types. If all of the data needs to be shown the select would be at most 8 table join.
Have one very wide table with all the ping data
Fully or partly use NoSQL ideas and insert most of the ping data in JSON or similar format.

Secondly it would be interesting to hear as to what database setup would be the most scalable:

Have a production server which has maximum of 2 months data so it would be easier to keep fully in memory. Offload all historical data to secondary server.
Have everything in one server.
Sharding?
Replication?

We are doing something very similar at my place and this very question was in my head for a while a few weeks back.

After noticing that logs were consuming 98% of our DBs in SQL Server I did some research on how we should handle this problem.

It all depends on the structure of your logs but our logs are not really relational so storing them in a RDBMS and consuming all the resources built for the RDBMS does not make sense.

I started looking into NoSQL stores and found DB Engines Ranking to be very useful to compare the feature sets of each DB engine.

Personally I found that an eventual consistency solution worked for me as it has good sharding, availability, and fast reads on huge data sets.

Check the CAP Theorem wiki entry to learn about the trade offs on different types of NoSQL solution.

We are currently testing a Cassandra deployment for our logs with MapReduce jobs.


How to match one row in outer join? I have a situation where incoming rows (src) need to be matched to rows in a target table (tgt). However, each target row can only be matched once for all the incoming source rows. Example SQL follows:

drop table if exists src;
create table src(id int not null, join_key text not null);
insert into src(id, join_key) values
(1,'X'),
(2,'X'),
(3,'X'),
(4,'Y'),
(5,'Y')
;

drop table if exists tgt;
create table tgt(id int not null, join_key text not null);
insert into tgt(id, join_key) values
(10,'X'),
(20,'X'),
(40,'Y')
;

with a as (
    select s.id as src_id,
           s.join_key,
           t.id as tgt_id
    from src s
             left join tgt t on s.join_key = t.join_key
),b as (
    select *,
           row_number() over (partition by src_id order by tgt_id) as rw
    from a
)
     select src_id, tgt_id from b
 where rw = 1
;

This gives:

1,10
2,10
3,10
4,40
5,40

But the values should be:

1,10
2,20
3,null
4,40
5,null

How can I do this?

WITH
cte1 AS ( SELECT id, join_key, ROW_NUMBER() OVER (PARTITION BY join_key ORDER BY id) rn
          FROM src ),
cte2 AS ( SELECT id, join_key, ROW_NUMBER() OVER (PARTITION BY join_key ORDER BY id) rn
          FROM tgt )
SELECT cte1.id src_id, cte2.id tgt_id, COALESCE(cte1.join_key, cte2.join_key) join_key
FROM cte1
FULL OUTER JOIN cte2 ON cte1.join_key = cte2.join_key AND cte1.rn = cte2.rn

fiddle


Changing Column type to avoid Arithmetic overflow error I have a table that saves user hits.  It has these columns:

ID , UserID , HitDate , Userlocation , UserUniqueID

Today I'm getting this error "Arithmetic overflow error". After I checked it out I find my Last hit ID was "2147483647" and also my Column type is

Int type , Primary Key with AutoNumber enabled

So the only solution is to Alter the column to bigint type (I guess). I've tried this code :

Alter HitTable alter column id bigint

but it returns this error :

Msg 5074 level 6 stat 1 line 1

The Object 'PK_HitTable' is dependent on colun 'id'

Alter Table Alter Column id failed because one or more objects access this column

So how can I change this huge table without losing any data? Will droping the current constraint cause data loss?  Is below code safe and will it give me the result (also with autonumber)?

ALTER TABLE HitTable
DROP CONSTRAINT PK_HitTable
GO
Alter HitTable alter column id bigint
GO
ALTER TABLE HitTable
ADD CONSTRAINT HitTable PRIMARY KEY CLUSTERED (ID);

Or should I use this?

ALTER TABLE HitTable NOCHECK CONSTRAINT ALL
GO
Alter HitTable alter column id bigint
GO
ALTER TABLE HitTable NOCHECK CONSTRAINT ALL

Also I should mention this table is used heavily.

If you need to change the data type of a column, you'll have to change every data type referencing that column, too.

At the database level, you'll have to change any column referencing this column. Since you need to change a PK column, this will affect all other tables where this column is used as a foreign key reference. In all of those tables you'll need to change the data type of the related columns.

Furthermore, you'll have to perform a code review to catch each and every place where all the columns identified in the step above are referenced. If there are code sections, where a value from such a column is assigned to a variable, you'll need to change the data type of that variable. If the column is used in joins to other tables or in the WHERE section of a query, you might need to change the referenced columns as well.

As a side effect after the change you might see a performance drop caused by implicit conversions, if you didn't change all references.

Once the database changes are identified, you'll have to check the application if the columns and variables that had to be changed will have any side effect to the appl. For instance, if there are columns returned as bigint that used to be integer, the app should be able to handle it.

After all changes are identified, they should be implemented during a maintenance window to avoid data inconsistency due to dropped references.

Long story short: changing a data type needs to be well-prepared and tested. I strongly vote against an "on-the-fly" change!


Shrinking database which is not having any insert/update We have a process of archiving wherein we take the backup of current database and restore it as xxx archive database. Now this database contains previous data and will not have any insert/update/Delete.

As this database will only be used for reporting, we are thinking of shrinking the database. However, this increases the index fragmentation. We are thinking of following below steps:

Shrink the database with re-organize
Re-organize indexes

Will this hamper the database performance? What other options can we consider for recovering the space?

Update: In the process, We change the recovery model to simple, backup the database to move the database out of Pseudo-Simple state, and then planning to shrink the database.

Instead of doing a backup/restore, consider moving data across into a new, presized database, and creating new indexes on this data. This should keep your file size small and your indexes unfragmented. Also make sure you have compression turned on.


How to UPDATE a table for the level of parents in a column? I have a table with internal parents as

id    parent    parent_level
1     NULL      1
2     1         2
3     1         2
4     3         3

where parent has a FK to id.

How can I update the table to assign the values of parent_level, according to the parent, and parent of parent of each row.

I would suggest to create stored function for that.

Table definition I've used for debugging:

DROP TABLE IF EXISTS `test`;

CREATE TABLE IF NOT EXISTS `test`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
parent INT NULL,
parent_level INT DEFAULT 0
)ENGINE = InnoDB;

START TRANSACTION;

INSERT INTO `test`
    (`id`, `parent`, `parent_level`)
VALUES
    (1, NULL, 0),
    (2, 1, 0),
    (3, 1, 0),
    (4, 3, 0);

COMMIT;

Now, create a stored function:

DELIMITER $$

DROP FUNCTION IF EXISTS `getLevel`$$ -- drop if exists

CREATE FUNCTION `getLevel`(`anId` INT) -- argument: current row id
RETURNS INT                            -- returns int
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE `aResult` INT DEFAULT 1;       -- result buffer, default value = 1
DECLARE `aParent` INT;                 -- buffer for parent ids

-- select first parent of this row:
SET `aParent` = (SELECT `parent` FROM `test` WHERE `id` = `anId`);

-- while parent id is set:
WHILE NOT `aParent` IS NULL DO

    -- increase result:
    SET `aResult` = `aResult` + 1;

    -- select next parent:
    SET `aParent` = (SELECT `parent` FROM `test` WHERE `id` = `aParent`);

END WHILE;

-- return result
RETURN `aResult`;
END$$

DELIMITER ;

Now, how to update:

UPDATE
    `test`
SET
    `parent_level` = `getLevel`(`id`)
WHERE           -- if safe mode is on
    `id` > 0;   -- I have to use primary key column in update or it will fail

SELECT
    *
FROM
    `test`;

Results:

1   NULL    1
2   1       2
3   1       2
4   3       3


when running SSIS package with Windows Auth it works but with SQL Auth it fails I have setup a SSIS package, and within one of my connections to the same server that the package is on it fails when I use SQL server Auth for login and it does not matter which user I put in.

I can put in my user name which works fine when logging in from SQL Management studio and which works fine when I run the package manually and the connection is set to Windows Auth.

The error I get is Test connection failed because of an error in initializing provider. Login failed for user.

I even tried using our SA account and it fails as well.

Connection string:
Data Source=SQL004;Initial Catalog=SAP_TO_E1;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=SSIS-Package-{1AE2C7B9-91AC-34B3FB255590}SQL004.SAP_TO_E1;Auto Translate=False;

It's an OLEDB connection.

I believe your connection string is off based on what you added, I would try looking at the following website. It looks like your string is designed for a CE device...

https://www.connectionstrings.com/sql-server-2008/


What is the etymology of the hstore datatype in postgres? Where did the datatype get its name? Hash store?

The name of the additional module hstore is derived from "hash store", because it was inspired by perl hash.
Oleg Bartunov and Teodor Sigaev - the authors of the module - say so in their presentation here:

  Hstore — key/value storage (inspired by perl hash)


Is there an Express version of Azure SQL DB? With on-premise SQL Server, there are editions such as Express, Web, Standard, and Enterprise. Do those editions (or an analog) exist in Azure SQL DB, especially Express?

My Google-Fu is failing here, possibly because it doesn't exist.

Azure SQL DB does not have a direct counterpart for SQL Server Express.
Some functionalities that exist in SQL Express or other versions are simply not there, like filestream. (Also not supported on managed instances).

As a result, you need to find the minimal functionalities you need and see if Azure SQL DB is fit to be a replacement of your SQL Express.

To do this, you would have to look into the Azure SQL DB Purchasing Models.

There are some differences in functionalities depending on the azure tier, as @AaronBertrand mentioned:

  There are some functionality differences between tiers and service
  levels. As two off-the-cuff examples, Columnstore is only in S3 and
  above and In-Memory is only available in Premium and Business Critical
  - you can spot other differences in this doc

As some of these functionalities that are present in higher tiers are available in SQL Express Edition, you will have to check for these and find the minimal tier you could use.

After finding the minimum tier you could use, all that is left is the sizing of your database, the DTU's and other aspects like max concurrent connections.

This all depends on your pricing levels.

At the time of writing, and solely based on the DTU-based model, the pricing levels are: Basic tier, Standard tier, Premium tier and PremiumRS tier.

The vCore-based model also exists, but (again, at the time of writing) there is no low cost option like Basic or low DTU standard.

vCore-based model tiers

Comparison

Basic tier

Basic tier might be what you are looking for.
The downside is that it only goes up to 2GB.
It is not free like SQL Server Express edition, but it will be the lowest cost, granted that:

you are able to stay under 2GB
you don't need any of the other functionalities
a maximum of5 DTU's is enough.
...

Basic tier

Standard tier

If one or more of the above conditions are not met, then you will have to at least go for the standard tier.

It will be more expensive, the price depending on how many DTU's you are going to need (S0 until S12). You can go up to 250GB in this tier.

Standard tier

As a sidenote, there might also be functionalities that you have or use in SQL Server Express which are simply not available in Azure SQL DB, such as filestream. (Also not supported on managed instances). As a result, you need to find the minimal functionalities you need and see if Azure SQL DB is fit to be a replacement of your SQL Express.


Merge two tables on partly matching column I have below 2 tables.

old_name    old_value
Tom1        100
Kate1       80
Jim1        70

new_name   new_value
Tom2       70
Kate2      100
Jim2       80

I want to make a new table look like

old_name    old_value       new_name    new_value
Tom1        100             Tom2        70
Kate1       80              Kate2       100
Jim1        70              Jim2        80

How to do that in Postgres?

Use some pattern matching / string manipulation in the JOIN condition. I use left() in my example:

SELECT *
FROM   tbl_old o
JOIN   tbl_new n ON left(n.new_name, -1)
                  = left(o.old_name, -1)

This assumes that all names have exactly one pending character (digit or no, doesn't matter).

left(string, -1) (with a negative length parameter) effectively trims the last letter from string. Should be fastest. Requires Postgres 9.1.
In older version substitute with substring() or one of many other ways ...


Bulk Insert Permissions Issues I'm experiencing the dreaded Msg 4861 error message when attempting to use Bulk Insert on a 2012 SP4 SQL Server. "Cannot bulk load because the file "\PathToFileShare.d1.xxx.com\folder\file.dat" could not be opened. Operating system error code 5 (Access is denied)

The details:

I have two different domains, D1 and D2. I have three different computers, the SQL Server (in D2), the File Server (in D1), and the Application Server(in D1).

I am trying to call a stored procedure from the Application Server that uses bulk insert to get a file from the File Server and insert into the SQL Server.

Stored procedure is executed as D1\user1 from the application which produces an error. When called from the SQL Server in D2 in SSMS logged in as D1\user1, it works as expected and correct records are inserted into the table. If I try to log into SSMS as D1\user1 on any other machine in D2, execution produces an error.

Additional information:

I cannot provide the SQL Server service account in D2 access to anything in D1.
I can confirm that D1\user1 has access to the file they are trying to read from the file share.
I have seen the information from the following links:
SQL Server Docs
Additional Docs
We are NOT using kerberos authentication.

The bulk insert part of the stored procedure looks like this:

BULK INSERT BulkInsertDB.ExampleDomain.ExampleTable
FROM '\\PathToFileShare.d1.xxx.com\folder\file.dat'
WITH (
     ROWTERMINATOR = '0x0a'
     );

Any help would be much appreciated.

Thank you.

BULK INSERT always impersonates Windows logins for file access.  But your configuration is a double-hop, so Kerberos would be required for impersonation.  So, as stated, you are stuck.

Common workaround is to create a Credential and a SQL Agent Proxy, and kick off the load from a cmdexec or powershell SQL Agent job step.  Agent will use the stored credentials to logon locally and run the job, so it's no longer a double-hop.

A TSQL job step won't work as the credentials aren't used to LogonUser in that case, Agent just connects as the Agent Service Account and does SQL Server impersonation of the job owner.

You can also access the file from the application server and either copy the file to the database server, or drive the bulk load from the client, eg with BCP or SqlBulkCopy.


Test table:

create table dbase234936 (
id integer,
serial varchar(7),
version timestamp,
is_deleted integer
);

Test data:

insert into dbase234936 values ( 10 , 'AAAAAA' , '2019-04-14 15:28:08', 0);
insert into dbase234936 values ( 22 , 'AAAAAA' , '2019-03-04 15:28:08', 0);
insert into dbase234936 values ( 13 , 'AAAAAA' , '2019-02-10 15:28:08', 0);
insert into dbase234936 values ( 40 , 'BBBBBB' , '2019-04-17 15:28:08', 0);
insert into dbase234936 values ( 27 , 'BBBBBB' , '2019-02-20 15:28:08', 0);
insert into dbase234936 values ( 17 , 'CCCCCC' , '2019-03-04 15:28:08', 0);
insert into dbase234936 values ( 35 , 'CCCCCC' , '2019-01-01 15:28:08', 0);

Query:

with windows as (
  select id, serial, version, is_deleted, row_number() over (partition by serial order by version desc) as rn
  from dbase234936
)
select id, serial, version, case when rn=1 then 0 else 1 end as is_deleted
from windows
;

Update based on the above CTE:

with windows as (
  select id, serial, version, is_deleted, row_number() over (partition by serial order by version desc) as rn
  from dbase234936
)
update dbase234936 set is_deleted = case when rn=1 then 0 else 1 end
from windows
where dbase234936.id = windows.id;

I like windowing functions :-)

DB Fiddle link


pgAdmin IV and PostgreSQL 12.0: ERROR: schema "public" already exists I have a database backup from pgAdmin III with PostgreSQL 12.0 (Ubuntu 12.0-2.pgdg16.04+1) on Ubuntu 16.04 LTS.

I would like to restore this database on my pgAdmin IV with PostgreSQL 12.0 on Docker containers (i.e. pgAdmin IV and PostgreSQL (PostGIS)). The images are: kartoza/postgis:12.0 and dpage/pgadmin4:4.14.

The problem is when I restore the database, pgAdmin IV output shows the following error message:

pg_restore: connecting to database for restore
pg_restore: creating SCHEMA "public"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 7; 2615 2200 SCHEMA public postgres
pg_restore: error: could not execute query: ERROR:  schema "public" already exists
Command was: CREATE SCHEMA public;
pg_restore: creating COMMENT "SCHEMA public"
[...]
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA "public"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 7; 2615 2200 SCHEMA public postgres
pg_restore: error: could not execute query: ERROR:  schema "public" already exists
Command was: CREATE SCHEMA public;
pg_restore: creating COMMENT "SCHEMA public"
[...]
pg_restore: creating FK CONSTRAINT "public.places_pilot_area places_pilot_area_fk_street_id"
pg_restore: warning: errors ignored on restore: 1
pg_restore: creating FK CONSTRAINT "public.layer_reference fk_reference_layer_reference1"
[...]
pg_restore: creating FK CONSTRAINT "public.places_pilot_area places_pilot_area_fk_street_id"
pg_restore: warning: errors ignored on restore: 1

I would not like to show these error messages, because they seem to be warnings and not errors (PostgreSQL is trying to create a public schema where it already exists). These errors are not shown on pgAdmin III when I restore using the same database.

There is this similar question. According to that answer, the error is harmless, but I would like to remove it from pgAdmin IV output.

I've already tried to restore with: (1) default options; (2) create option turned on; (3) clean option turned on; and (4) create and clean options turned on; however no success, all previous options result on an error message.

I'm afraid of ignoring these warnings and in the future true errors appear and I do not see them.

Thank you in advance.

Well, I was able to solve my problem by another path.

I suspected the problem was related to pgAdmin III and not PostgreSQL, then I opened my remote server through pgAdmin IV and I backed my remote database up.

After that, on pgAdmin IV, I restored the database and it worked fine, with no error.

Then, I believe the problem is related to creating a backup on pgAdmin III and restore it on pgAdmin IV.

If someone gets the same error in the future, please, use just pgAdmin IV to avoid problems.


Can't load reportserver in browser We are running SQL Server 2016 and cannot view the list of reports in the browser when we go to http://servername/reportserver. When we go to that link in the browser, it asks for credentials. When I've used my own, it simply gives me the credentials pop-up again with no error. This repeats until the browser gives an HTTP 400 error. When I try the sa credentials, I only have to do it once but it still gives the HTTP 400 error. I can connect to the server in SSMS and can even query the Catalog table from the database. I can also save reports onto the server using Report Builder.

The issue is most likely that you haven't configured your Service Principal Names for the Report Server, so it is encountering an error trying to utilise Kerberos Authentication.
Firstly, you need to confirm your SPNs are configured correctly for the Report Server. You can use Kerberos Configuration Manager for SQL Server to identify any issues with your SPNs and fix those issues.
Once your SPNs are corrected, add the address (http://servername/) to your trusted sites to enable your Windows identity to be passed through automatically and facilitate SSO.


What are Objective Business Reasons to Prefer SQL Server 2008R2 over 2012? Basically, I want to take this discussion -- What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2? -- and turn it on its head.

We are a small business, and we are struggling with the pricing-structure change in SQL Server 2012.  We are essentially running a development server as our production/live server; in other words, they're the same box.  Thus we could (until we got legal advice that said otherwise) get away with using a dev. license for it.  But our server is pretty high-end, so the per-core license fees would add up to something outrageous.  Thus, we're considering moving the server back to 2008-R2.  (Or worse, to MySQL or something completely different! if we can't get the pricing problem resolved.)

Our queries and software do not specifically take advantage of any new-in-2012 features that I'm aware of.  But of course the backward migration would be quite a large undertaking.

So what do the pros think?  Is this a recipe for disaster, or are we not totally insane for wanting to do this?  I've listed 2 reasons so far (pricing and non-use-of-features), but I'd like to hear more, on either side of the fence.  Thank you.

I don't know that you can really be objective about choosing a lesser version over a current version. It is more or less forced eventually. Hardware goes out of date and purchasing any new hardware may require using a more current version of Windows Operating System; which that in turn requires you to upgrade SQL Server. I actually had conference call over a client that is in this very boat now.

As well your hand can be forced by Microsoft's release plan. Once a major version is released you will reach a point that you can no longer purchase the older version. Which is the case now with SQL Server 2012, you can't purchase a SQL Server 2008 R2 license. You may use that version but you will have to purchase under the SQL Server 2012 licensing model. SQL Server 2014 CTP2 was just released the other day so after a certain number of days/months after the RTM release you will not be able to purchase SQL Server 2012.

The overall decision has to come from the business' money holders. Business meetings I have taken part in the one thing that is always considered when building a new system out for an application is licensing considerations (support maintenance annual cost, licensing upgrades, etc.). The pros and cons of licensing what version/edition should always be considered up front. I have been in environments where the business was feed the wrong information on what quantity/type of license to purchase, then when they were audited because of industry standards they got bitten pretty hard from it (or the scape goat got fired). It was ultimately their responsibility.

The best thing I would suggest is looking at Software Assurance. Especially since in the two years covered SQL Server 2014 should be released. You may pay a good bit more now, but with the discount pricing you will save a considerable amount of money when you go ahead and purchase SQL Server 2014 licenses. Especially if Microsoft decides to change their licensing model again, although I have heard nothing that indicates they are going to.


How to select an item from a column only if none of the value in corresponding column is NULL I want to know if I can assemble a part.( Which is possible if I have all the raw material). So from the Table I want to select the item number only if Quantity for raw material is NOT NULL (not zero).
The table is like this

So I want the result AS
Model 2
Model 3
Model 4

The query I am using is
SELECT DISTINCT ItemNumber FROM TABLE WHERE QUANTITY IS NOT NULL

But because in 2 rows Model 1 have some quantity which is not NULL I am getting that in result as well.
Please let me know a way of fixing it.

you can use NOT IN and a subselect

CREATE TABLE modelitem
    ([ltemNumber] varchar(7), [Cornponent] varchar(6), [Quantity] varchar(4))
;

INSERT INTO modelitem
    ([ltemNumber], [Cornponent], [Quantity])
VALUES
    ('Modell', 'Wheel', '19'),
    ('Modell', 'Bolt', '19'),
    ('Modell', 'Engine', NULL)
    ,('Modell', 'Engine', NULL),
    ('Model 2', 'Wheel', '19'),
    ('Model 2', 'Bolt', '19'),
    ('Model 2', 'Engine', '6'),
    ('Model 3', 'Wheel', '19'),
    ('Model 3', 'Bolt', '19'),
    ('Model 3', 'Engine', '6'),
    ('Mode14', 'Wheel', '19'),
    ('Mode14', 'Bolt', '19'),
    ('Mode14', 'Engine', '6')
;
GO

SELECT DISTINCT [ltemNumber] FROM modelitem WHERE [ltemNumber] NOT IN (SELECT DISTINCT [ltemNumber] FROM modelitem WHERE [Quantity] IS NULL)
GO

| ltemNumber |
| :--------- |
| Mode14     |
| Model 2    |
| Model 3    |

db<>fiddle here


Tell MySQL to start using utf-8 encoding without `convert to`ing it In a fairly unique situation, my team has ended up with UTF-8 bytes in a database that thinks the data is encoded as latin1.

At least, I'm 85% certain that this is the situation at hand.

For example, a right single quotation mark was handed to the database by a programming language that had no concept of encodings (Ruby 1.8) and just treated the data as raw bytes (0xE2 0x80 0x99). This data, as far as I can tell (how to verify?), was stored as those actual bytes. So now when the data is read out by a more intelligent programming language (Ruby 1.9), the database helpfully says "Oh! 0xE2 is 'â', 0x80 is '€', 0x99 is '™'", and so instead of "Mike’s", we end up with "Mikeâ€™s". This is also what I get in the mysql prompt when SELECTing that value.

So, essentially, we have a bunch of utf-8 encoded data stored in a database that thinks the data is encoded as latin1.

This makes me to somehow tell the database "No, no matter what you think, this stuff is actually utf-8". CONVERT TO doesn't seem like the right tool, because then I'll end up with permanent "Mikeâ€™s".

Failed/moronic attempt #1

I noticed this:

> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_client     | utf8              |
| character_set_connection | utf8              |
| character_set_database   | utf8              |
| character_set_filesystem | binary            |
| character_set_results    | utf8              |
| character_set_server     | latin1            |
| character_set_system     | utf8              |
| collation_connection     | utf8_general_ci   |
| collation_database       | utf8_unicode_ci   |
| collation_server         | latin1_swedish_ci |
+--------------------------+-------------------+

And thought that maybe changing character_set_results to latin1 would trick it into not doing any conversion of the bytes, resulting in the proper display of data on my utf8 OS.

Sure enough, SET character_set_results=latin1; results in ’ instead of â€™. Cool!

So I added this to my ~/.my.cnf (which is the only my.cnf, I checked):

[mysqld]
...
character-set-results=latin1

and when I go back to the MySQL prompt & check the character_set_% variables, it's still utf8.

Yes, it just occurred to me that mysqld is a deamon, which means I probably need to restart the whole mysql process for this to take effect. But whoever installed MySQL on this machine used the dmg instead of the brew (wasn't me!) and the MySQL pref pane is currently telling me that MySQL isn't running even though it clearly is, and anyhow before I go down that rabbit hole, I want to check with an actual DBA and see how ridiculous this is, or if there's just a better, cleaner way to do it.

The solution isn't precisely the same but this question is where I originally found direction for a similar issue and the concepts there should take you where you want to go. MySQL has a BINARY character set and from all appearances, by converting through it, you can prevent MySQL from realizing what you're actually doing and being "too helpful."

Test case with character_set_client = utf8:

mysql> select CONVERT(CONVERT(CONVERT('Mikeâ€™s' USING latin1) USING binary) USING utf8);
+--------------------------------------------------------------------------------+
| CONVERT(CONVERT(CONVERT('Mikeâ€™s' USING latin1) USING binary) USING utf8)     |
+--------------------------------------------------------------------------------+
| Mike’s                                                                         |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

You could use that logic to populate a new column that MySQL believes to be utf8.


Can I launch a stored procedure and immediately return without waiting for it to finish? We have a stored procedure that users can run manually to get some updated numbers for a report that's used constantly throughout the day.

I have a second stored procedure that should be run after the first stored procedure runs since it is based on the numbers obtained from this first stored procedure, however it takes longer to run and is for a separate process, so I don't want to make the user wait while this 2nd stored procedure gets ran.

Is there a way to have one stored procedure start a second stored procedure, and return immediately without waiting for results?

I'm using SQL Server 2005.

It looks like there are multiple ways to accomplish this, but I found the simplest way was Martin's suggestion of setting up the procedure in a SQL job, and starting it using the asynchronous sp_start_job command from my stored procedure.
EXEC msdb.dbo.sp_start_job @job_name='Run2ndStoredProcedure'

This only works for me because I don't need to specify any parameters for my stored procedure.
Other suggestions that may work depending on your situation are

Using the SQL Service Broker like Martin and Sebastian suggest. This is probably the best suggestion if you don't mind the complexity of setting it up and learning how it works.

Running the process asynchronously in the code that is responsible for executing the stored procedure, like Mr.Brownstone suggested.
Not a bad idea, however in my case the stored procedure gets called from multiple places, so finding all those places and ensuring they call the 2nd procedure too didn't seem that practical. Also, the 2nd stored procedure is fairly critical, and forgetting to run it could cause some major problems for our company.

Make the 1st procedure set a flag, and setup a recurring job to check for that flag and run if its set, like Jimbo suggested. I'm not a big fan of jobs that run constantly and check for changes every few minutes, but it certainly is an option worth considering depending on your situation.


Information on Oracle MKSTORE utility for creating wallet I'm looking for more information about the utility MKSTORE that can be used for creating and modifying a Wallet. I would like to know things like what the -createALO option is and what the difference is between -createSSO and CreateLSSO. A link to the information would be fine or a document number on MOS. My goal is to script the Wallet creation and am wondering if these options can help me in any way.

General Information about Oracle Wallet is in the Advanced Security Guide.

This is the link to managing wallets from the command line using the general orapki tool.

This is the link to managing wallets using the MKSTORE command specific to credentials storage.

*add

Here is some q&a on the steps on asktom. He also notes an Oracle bug 4395883 that can affect using connect identifiers 4, 8 or 12 bytes in length.

Here is a blog entry that contains a script for rapidly creating wallet entries. Another entry on that blog about the bug above.

Another blog entry about the whole process.

-createSSO means autologin (aka no password required)

-createLSSO means -auto_login_local (require the hostname matches where the wallet was created)

-createALO means -auto_login_only (require the hostname and the user the wallet was created on/under to match)

This does not appear to be documented under mkstore but is documented under orapki which is a companion utility.  Note auto_login_local security feature can be spoofed and offers little additional protection.  Search the web and you can find more details about why auto_login_local is ineffective.

https://docs.oracle.com/cd/E11882_01/network.112/e40393/asoappf.htm#ASOAG9833


Monitor SQL Server stored procedure latency What is the best way to capture SQL Server stored procedure latency in SQL Server 2016?

Plan is to create a SSRS report and send it to management. We also enabled query store.

Thanks much

Stored procedure execution statistics are surfaced via the sys.dm_exec_procedure_stats dynamic management view.

This query will show stats for stored procedures in the current database:

SELECT ObjectName = s.name + N'.' + o.name
    , sps.last_execution_time
    , sps.execution_count
    , sps.last_elapsed_time
    , sps.total_elapsed_time
    , avg_elapsed_time = sps.total_elapsed_time / sps.execution_count
FROM sys.dm_exec_procedure_stats sps
    INNER JOIN sys.objects o ON sps.object_id = o.object_id
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE sps.database_id = DB_ID()
ORDER BY s.name
    , o.name;

The output looks like:

╔══════════════════════════════════╦═════════════════════════╦═════════════════╦═══════════════════╦════════════════════╦══════════════════╗
║            ObjectName            ║   last_execution_time   ║ execution_count ║ last_elapsed_time ║ total_elapsed_time ║ avg_elapsed_time ║
╠══════════════════════════════════╬═════════════════════════╬═════════════════╬═══════════════════╬════════════════════╬══════════════════╣
║ dbo.procAccessUniqueID           ║ 2018-09-07 13:21:04.403 ║          173346 ║               614 ║          223886470 ║             1291 ║
║ dbo.procAddBatchClassFilter      ║ 2018-09-04 15:25:22.150 ║              43 ║              5124 ║             171329 ║             3984 ║
║ dbo.procAddBatchFilter           ║ 2018-09-06 15:04:34.817 ║              17 ║               251 ║              18767 ║             1103 ║
║ dbo.procAddLock2                 ║ 2018-09-07 13:21:05.047 ║          373086 ║               912 ║          542911271 ║             1455 ║
║ dbo.procAddTempUser_V10          ║ 2018-09-07 10:50:13.973 ║               4 ║               292 ║               1223 ║              305 ║
║ dbo.procAddUserGroupRelationship ║ 2018-09-07 13:14:49.533 ║            1124 ║                70 ║             151824 ║              135 ║
╚══════════════════════════════════╩═════════════════════════╩═════════════════╩═══════════════════╩════════════════════╩══════════════════╝

The avg_elapsed_time, measured in microseconds, shows the average elapsed time for completed executions of each stored procedure listed.


MariaDB or MySQL @@SESSION.tx_read_only always returns 0 I'm trying to figure out possible "read only" transaction setup leak between pooled connections (on the same physical connection) in Java application which lead me to a question "How can I find out what the current transaction read-only-ness really is?"
I'm using versions 10.0.28-MariaDB-wsrep and 5.7.31-0ubuntu0.18.04.1 and whatever I do, when I issue the statement:
SELECT @@GLOBAL.tx_read_only, @@SESSION.tx_read_only;

It always returns 0 for both values. (I've tried also the non-deprecated variable on MySql, the same result.)
I've read how to start transaction for both databases and even when I get the error Cannot execute statement in a READ ONLY transaction. which is haunting me in the application, the tx_read_only is still 0.
I tried the following in IDEA SQL console:

turn of auto-commit
set TRANSACTION READ ONLY;
START TRANSACTION;
INSERT ... - Originally: no problem on MySQL, failed on MariaDB, I understand the reason, it is documented. EDIT: I tried this fiddle and this one and both fail on the first INSERT. My previous experience might have been caused by unclear session.
start TRANSACTION READ ONLY; -- EDIT: probably not relevant now.
INSERT ... - ended with Cannot execute statement in a READ ONLY transaction. on both DBs.

Any step of the way, tx_read_only was always 0. What may be the problem? What is the right way to ask the DB whether INSERT is allowed before actually trying to do it?

another fiddle test
So both MariaDB  on tx_read_only and mysql on transaction_read_only have the session variable set to the current session value. The "next transaction access mode" remains hidden.
Catching insert errors seems like the only option.


Which query performs better in MongoDB ($or or $facet)? I have a query similar to the following;
    db.getCollection('list').aggregate([
    {
        $facet: {
            "events":[{
                $match: {
                   'type': 'Event'
                }
            }],
            "tasks": [{
                $match: {
                   'type': 'Task'
                }
            }]
        }
    },
    {$project: {activity:{$setUnion:['$events','$tasks']}}},
    {$unwind: '$activity'},
    {$replaceRoot: { newRoot: "$activity" }}
]);

One another easier approach to write this query is by using $or operator.
    db.getCollection('list').aggregate([{
    $match: {
        $or: [{
            type: 'Event'
        }, {
            type: 'Task'
        }]
    }
}]);

Source: https://gist.github.com/cthurston/7aead8229e10caa0be175babf7e8ddf1
The query that I'm working with is very similar, except I have other fields, not just 'type' going through this example. My idea is that, if I use $facet I'd get better performance if I create correct indexes for each query (that is inside $facet). On the other hand, if I use $or query as it is in the example, Mongo may struggle while trying to find the correct index for the query. Is this how it works?
Edit: It seems MongoDB can handle indexes for $or statements;
https://docs.mongodb.com/manual/reference/operator/query/or/#or-clauses-and-indexes

$facet - Index Use says:

The $facet stage, and its sub-pipelines, cannot make use of indexes,
even if its sub-pipelines use $match or if $facet is the first stage
in the pipeline. The $facet stage will always perform a COLLSCAN
during execution.

$or Clauses and Indexes says:

When evaluating the clauses in the $or expression, MongoDB either
performs a collection scan or, if all the clauses are supported by
indexes, MongoDB performs index scans.

And, your query's $match stage:
$match: {
    $or: [
        { type: 'Event' },
        { type: 'Task' }
    ]
  }

can be simply written as:
$match: { type: { $in: [ 'Event', 'Task' ] } }

See $or versus $in says:

When using $or with  that are equality checks for the
value of the same field, use the $in operator instead of the $or
operator.

Using the $or with proper indexing is likely to perform better than that of using the $facet stage.


connect to Oracle Cloud from SQLPlus and Instant Client I want to try to connect with SQLPlus to Oracle Cloud.

for regular oracle I can connect from remote machine as follows:

IP = 1.2.3.4
PORT = 1521
ServiceName = "ORCL1"
Username = "username"
Password = "password";

sqlplus username/password@1.2.3.4:1521/ORCL1

Is it possible to connect also to oracle cloud in a similar way?

Next step is to connect Oracle Instant Client to Oracle Cloud, but I want to do so without any installation (except copy of DLLs, but without need of editing 'tnsnames.ora' etc.).

I use occi interface like this:

IP = 1.2.3.4
PORT = 1521
ServiceName = "ORCL1"
Username = "username"
Password = "password";

env->createConnection("username", "password", "1.2.3.4:1521/ORCL1");

Can I connect in the same way (or how to connect) to Oracle Cloud?

Assuming your Oracle database on Oracle cloud then you can connect to SQPLUS over SSH:

connect to oracle cloud service
click on the name of the deployment you want to connect to
copy public IP address
switch to Linux command prombt
check your and confirm the private public key for your deployment exist
run ssh -i ua_key_openssh oracle@IPADDRESS_YOU_GOT_FROM_POINT3
execute sqlplus command
enter username\password


Joining on a field with different values I am trying to join data from two completely different sources. One source contains an employee's schedule information, and the other tracks what they actually worked (like what time they actually took lunch or break). The problem is, the schedule program gives times as BREAK1, BREAK2, BREAK3, and LUNCH, while the tracking program simply lists them as Lunch and Break. I can join the data and get the lunches just fine, but the breaks are throwing me off. If I convert BREAK1, BREAK2, and BREAK3 to just "Break", I end up with too many segments because it is matching every instance with every other instance. Is there a way that anyone can think of to join these two pieces of information?

Here is some sample data:

This is the Scheduled Times:

EMP_ID  NOM_DATE    SEG_CODE    START_MOMENT    STOP_MOMENT
626009  26-Sep-13   BREAK2          9/26/13 5:00 PM 9/26/13 5:15 PM
625650  26-Sep-13   BREAK2          9/26/13 4:30 PM 9/26/13 4:45 PM
638815  26-Sep-13   BREAK2          9/26/13 4:00 PM 9/26/13 4:15 PM
621649  26-Sep-13   BREAK2          9/26/13 3:30 PM 9/26/13 3:45 PM
567005  26-Sep-13   BREAK2          9/26/13 3:30 PM 9/26/13 3:45 PM
626009  26-Sep-13   LUNCH           9/26/13 2:30 PM 9/26/13 3:30 PM
625650  26-Sep-13   LUNCH           9/26/13 1:30 PM 9/26/13 2:30 PM
638815  26-Sep-13   LUNCH           9/26/13 1:30 PM 9/26/13 2:30 PM
621649  26-Sep-13   LUNCH          9/26/13 12:30 PM 9/26/13 1:30 PM
567005  26-Sep-13   LUNCH          9/26/13 12:30 PM 9/26/13 1:30 PM
626009  26-Sep-13   BREAK1         9/26/13 11:45 AM 9/26/13 12:00 PM
625650  26-Sep-13   BREAK1         9/26/13 11:30 AM 9/26/13 11:45 AM
638815  26-Sep-13   BREAK1         9/26/13 11:45 AM 9/26/13 12:00 PM
621649  26-Sep-13   BREAK1         9/26/13 9:30 AM  9/26/13 9:45 AM
567005  26-Sep-13   BREAK1         9/26/13 9:30 AM  9/26/13 9:45 AM

This is the Actual Times

EMP_ID  Seg_Code    Start_Time  Stop_Time
625650  Break           9/26/2013 17:54 9/26/2013 17:55
567005  Break           9/26/2013 14:56 9/26/2013 14:59
567005  Break           9/26/2013 15:32 9/26/2013 15:44
638815  Break           9/26/2013 16:34 9/26/2013 16:47
567005  Break           9/26/2013 10:08 9/26/2013 10:21
626009  Break           9/26/2013 17:01 9/26/2013 17:15
625650  Break           9/26/2013 11:31 9/26/2013 11:45
626009  Break           9/26/2013 11:52 9/26/2013 12:07
621649  Break           9/26/2013 9:34  9/26/2013 9:48
621649  Break           9/26/2013 15:31 9/26/2013 15:45
638815  Break           9/26/2013 11:46 9/26/2013 12:02
625650  Break           9/26/2013 16:35 9/26/2013 16:51
567005  Lunch           9/26/2013 12:31 9/26/2013 13:29
625650  Lunch           9/26/2013 13:31 9/26/2013 14:30
626009  Lunch           9/26/2013 14:31 9/26/2013 15:30
638815  Lunch           9/26/2013 13:31 9/26/2013 14:30
621649  Lunch           9/26/2013 12:31 9/26/2013 13:30

I am trying to get the difference (in minutes) between when they are scheduled, and when they are actually taking breaks. A correct example is:

Badge   Seg_Code    Scheduled Start     Scheduled Stop      Actual Start           Actual Stop      Difference      Seg_Duration
192329  Lunch       9/26/13 8:15 AM     9/26/13 9:15 AM     9/26/2013 8:18:27 AM    9/26/2013 9:17:59 AM        3       0:00:59:32

Thank you


SELECT FOR UPDATE locking other tables in PostgreSQL The situation, with PostgreSQL 9.6:

table A with integer primary key
table B with foreign key constraint
on its primary key referencing table A's primary key

SELECT id FROM A FOR UPDATE; blocks UPDATE B SET x=y; until the lock on A is released.

I assume this is because the referenced value might change. What can I to avoid the first statement blocking execution of the second one without dropping the foreign key constraint?

If I drop the foreign key constraint, what bad things should I expect? In the actual database where this problem occurs, it wouldn't be a significant issue if table B had rows left over after deleting from A. We also don't ever update primary keys.

Edit with code:

-- Setup
create table a (id int primary key);
create table b (id int primary key references a (id) on update cascade on delete cascade, x varchar);
insert into a values (1), (2);
insert into b values (1, null), (2, null);

-- Left
begin;
select 1 from a for update;
/* Keep the transaction open */

-- Right
begin;
update b set x = 'abc' where id = 1;
update b set x = 'xyz'; /* It blocks here /*

We also don't ever update primary keys.

In that case, I think you can use FOR NO KEY UPDATE instead of FOR UPDATE. It's a weaker lock, as explained in Postgres docs about Explicit Locking:

FOR NO KEY UPDATE
Behaves similarly to FOR UPDATE, except that the lock acquired is weaker: this lock will not block SELECT FOR KEY SHARE commands that attempt to acquire a lock on the same rows. This lock mode is also acquired by any UPDATE that does not acquire a FOR UPDATE lock.

Test:
-- Setup
create table a (id int primary key,
                x varchar);
create table b (id int primary key
                    references a (id) on update cascade on delete cascade,
                x varchar);
insert into a values (1), (2);
insert into b values (1, null), (2, null);

-- Left
begin;
select 1 from a for no key update;
/* Keep the transaction open */

            -- Right
            begin;
            update b set x = 'abc' where id = 1;
            update b set x = 'xyz';    -- doesn't block

-- Left
update a set x = 'left' where id = 1;
commit ;

            -- Right
            commit ;


Rusty SQL = Heavily scratched head I am trying to figure out the best way to sort this solution.

I am trying to produce a SP (or series) to generate invoices so that the contractors know how much they will be paid.

I have created two views that query the staff allocation table and produce the invoice lines

select * from EventInvoiceLineView WHERE [date] >= @invoicemonthstart and [date] <=@invoicemonthend
union all
Select * from PTSInvoiceLineView WHERE [date] >= @invoicemonthstart and [date] <=@invoicemonthend

Order By StaffID Asc, [date] Asc

This gives me my invoice line table.
[StaffID], [EventID], [JobType], [EventName], [date]

So I now need to use this information to produce an invoice header for each staff member that has a record from these views.

my invoice header has:
InvoiceRef (PK, Int, not null)
InvoiceDate (date, not null)
StaffID (FK, int, not null)
InvoicePayDate (date, not null)
InvoiceSortCode (nvarchar(6), not null)
InvoiceAccount (nvarchar(8)), not null)

so... I am thinking

Insert Into InvoiceHeader
    InvoiceDate = date.now
    StaffID = cycling through the StaffID from the view
    InvoicePayDate = @scheduledpayday
    InvoiceSortcode = (Select [sortcode] from tblStaff where [staffid] = staffid)
    InvoiceAccount = (Select [Account] from tblStaff where [staffid] = staffid]

I then need to get the InvoiceRef PK int and use that to insert the rows from the view into my InvoiceLineItems which is laid out as:
    LineID (PK, Int, not null)
    InvoiceRef (FK, Int, not null) -- Invoice header record id
    JobID (int, not null) -- JobID from View
    Jobdate (date, not null) -- date from View
    JobType (nvarchar(10), not null) -- JobType from View
    JobName (NVarchar(250), not null) -- EventName from View
    JobPaid (money, not null) -- Calculated field

Any hints, tips, pointers or templates would be greatly appreciated

Due to the lack of information, I can only give you an example code that could solve this problem.

This is based on the AdventureWorks2014 database. I created a simple testing table that only has 2 attributes, the name of the sales person and the amount of sales he's made in total (count):

CREATE TABLE T (
PersonName nvarchar(30),
Sales int
)
GO

And this procedure takes 2 dates as input, selects the required data and inserts it into the table:

CREATE PROCEDURE test
    @startDate date,
    @endDate date
AS
BEGIN

WITH CTE AS (
SELECT  E.BusinessEntityID,
        COUNT(SOH.SalesOrderID) AS [Sales]
FROM AdventureWorks2014.HumanResources.Employee AS E
    INNER JOIN AdventureWorks2014.Sales.SalesOrderHeader AS SOH
        ON SOH.SalesPersonID = E.BusinessEntityID
WHERE SOH.DueDate > @startDate AND SOH.DueDate < @endDate
GROUP BY E.BusinessEntityID
)
INSERT INTO T (
PersonName,
Sales
)
SELECT P.FirstName, CTE.Sales
FROM AdventureWorks2014.HumanResources.Employee AS E
    INNER JOIN AdventureWorks2014.Person.Person AS P
        ON P.BusinessEntityID = E.BusinessEntityID
    INNER JOIN CTE ON CTE.BusinessEntityID = E.BusinessEntityID

END
GO

For every sales person this calculates the amount of sales they have made between the @startDate and @endDate. You're going to have to edit this solution to fit your own tables and data.

Testing this procedure:

EXEC test '2011-06-30', '2012-06-30'

Gives the table this result


Why pgadmin dont enter in admin panel? I install postgresql-12.4 on my win pc. When i'm trying to enter into admin panel (pgadmin) i cathcing message:
could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5433? could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5433?
I have 2 users on my pc. Admin and another user with adming rights. Under admin profile pgadmin works good, uder another user profile pgadmin throws error message. In services server is running.

According this previous post on Stackoverlfow

Enter your server’s IP address in the “Hostname/ Address” field. Default is localhost.

Specify the “Port” as “5432”.

Enter the name of the database in the “Database Maintenance” field.

Enter your username as postgres and password (use the same password you used when previously configuring the server to accept remote connections) for the database.

Click “Save” to apply the configuration.


What is lost when I create a Foreign Key using `WITH NOCHECK`? I know that if I do an EXISTS() call on a FK lookup value, then, if that FK constraint is trusted, the result is immediate.

And if it is not trusted (like when I create the FK using WITH NOCHECK) then SQL Server has to go and check to table to see if the value is actually there.

Is there anything else I lose by using NOCHECK?

As you have discovered with your exists example, SQL Server can use the fact that a foreign key is trusted when the query plan is built.

  Is there anything else I lose by using NOCHECK?

Apart from the fact that you can add values to a column that should not be there as answered by Ste Bov you will have more scenarios where the query plan will be better when the foreign key is trusted.

Here is one example with an indexed view.

You have two tables with a trusted FK constraint.

create table dbo.Country
(
  CountryID int primary key,
  Name varchar(50) not null
);

create table dbo.City
(
  CityID int identity primary key,
  Name varchar(50),
  IsBig bit not null,
  CountryID int not null
);

alter table dbo.City
  add constraint FK_CountryID
  foreign key (CountryID)
  references dbo.Country(CountryID);

There are not so many countries but a gazillion of cities and some of them are big cities.

Sample data:

-- Three countries
insert into dbo.Country(CountryID, Name) values
(1, 'Sweden'),
(2, 'Norway'),
(3, 'Denmark');

-- Five big cities
insert into dbo.City(Name, IsBig, CountryID) values
('Stockholm', 1, 1),
('Gothenburg', 1, 1),
('Malmoe', 1, 1),
('Oslo', 1, 2),
('Copenhagen', 1, 3);

-- 300 small cities
insert into dbo.City(Name, IsBig, CountryID)
select 'NoName', 0, Country.CountryID
from dbo.Country
  cross apply (
              select top(100) *
              from sys.columns
              ) as T;

The most often executed queries in this application is related to finding the number of big cities per country. To speed things up with that we add an indexed view.

create view dbo.BigCityCount with schemabinding
as
select count_big(*) as BigCityCount,
       City.CountryID,
       Country.Name as CountryName
from dbo.City
  inner join dbo.Country
    on City.CountryID = Country.CountryID
where City.IsBig = 1
group by City.CountryID,
         Country.Name;

 go

create unique clustered index CX_BigCityCount
  on dbo.BigCityCount(CountryID);

After a while comes a demand of adding a new country

insert into dbo.Country(CountryID, Name) values(4, 'Finland');

The query plan for that insert has no surprises.

A clustered index insert to the Country table.

Now, if your foreign key was not trusted

alter table dbo.City nocheck constraint FK_CountryID;

and you add a new country

insert into dbo.Country(CountryID, Name) values(5, 'Iceland');

you would end up with this not so pretty picture.

The lower branch is there to update the indexed view. It does a full table scan of City to figure out if the country with CountryID = 5 already has rows in the table City.

When the key is trusted, SQL Server knows there can be no rows in City that would match the new row in Country.


MySQL Inner Join too slow My machine disk is SSD and memory is 32GB.
My executed SQL:

SELECT `country_detail`.`country_name` AS `country_name`
FROM `statistic_detail`
  INNER JOIN `country_detail` ON (`statistic_detail`.`country` = `country_detail`.`country_code`)
GROUP BY 1;

The SQL need cost 30 second. So slowly! My explain is:

I don't understand why it need so much time?
I know how optimize it, I want to know why it is so slow.

My table information is:

CREATE TABLE `statistic_detail` (
  `date` varchar(10) NOT NULL,
  `os` varchar(10) NOT NULL,
  `ver` varchar(16) NOT NULL,
  `country` varchar(16) NOT NULL,
  `utype` varchar(8) NOT NULL,
  `stype` varchar(32) NOT NULL,
  `language` varchar(16) NOT NULL,
  `num0` bigint DEFAULT NULL,
  `num1` double DEFAULT NULL,
  `num2` double DEFAULT NULL,
  `num3` double DEFAULT NULL,
  `num4` double DEFAULT NULL,
  `num5` double DEFAULT NULL,
  `num6` double DEFAULT NULL,
  `num7` double DEFAULT NULL,
  `num8` double DEFAULT NULL,
  `num9` double DEFAULT NULL,
  `num10` double DEFAULT NULL,
  `num11` double DEFAULT NULL,
  `num12` double DEFAULT NULL,
  `num13` double NOT NULL,
  PRIMARY KEY (`country`, `ver`, `language`, `utype`, `stype`, `os`, `date`),
  KEY `ver_idx` (`ver`),
  KEY `language_idx` (`language`),
  KEY `utype_idx` (`utype`),
  KEY `stype_idx` (`stype`),
  KEY `os_idx` (`os`),
  KEY `date_idx` (`date`),
  KEY `country_idx` (`country`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

13731253 rows.

CREATE TABLE `country_detail` (
  `country_code` varchar(20) NOT NULL,
  `country_name` varchar(16),
  `region_code` varchar(16),
  `region_name` varchar(16),
  PRIMARY KEY (`country_code`),
  KEY `region_code_idx` (`region_code`),
  KEY `region_name_idx` (`region_name`),
  KEY `country_name_idx` (`country_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

334 rows.

To understand the reason behind slowness of your current query, you need to understand the Nested Loop Join algorithm used by MySQL. Basically, for every country_code value in the country table, it is looking for all the matching rows in the statistic table. So if you look at your explain, it is roughly accessing 334*306 rows (a large number). Unfortunately, in this case, MySQL optimizer is not smart enough to stop searching for the row as soon as a match is found (because you are using Group By, and no aggregation function is being used).

Now, JOIN is unnecessary here, as it seems that you are just looking to check that the "country" has atleast one corresponding row in the statistic_detail table. This query can be written in a more performant way using EXISTS() on a Correlated Subquery. Exists() stops as soon as a matching row is found in the statistic table.

Try the following query:

SELECT cd.country_name
FROM country_detail AS cd
WHERE EXISTS (SELECT 1 FROM statistic_detail AS sd
              WHERE sd.country = cd.country_code)

If country_name values are not UNIQUE in the country_detail table, you can additionally utilize DISTINCT clause to get rid of duplicates:

SELECT DISTINCT cd.country_name
FROM country_detail AS cd
WHERE EXISTS (SELECT 1 FROM statistic_detail AS sd
              WHERE sd.country = cd.country_code)


Postgres: query on huge (11gb ) index does not return Using Postgres 9.6, I have created a table with 435M rows which is 120GB in size and have added an index which is 11GB in size.

I now want to iterate on the distinct values of the index, but the query fails with no error, it just does not complete. I can see no cpu usage or ram being used. Nb the server is on aws rds with 15GB of ram.

How to best troubleshoot this? Trying to iterate through it with LIMIT and OFFSET fails after about 3 cycles. I haven't managed to get a number of the unique values in the index.

I will try reindexing to see if there is indeed anything corrupted, but any suggestions on using a 11GB index would be great.

Editing to add more info:

This is the format of my table

id bigint,
hit1 character varying(50),
hit2 character varying(50),
offset integer,
year character varying(50)

Row estimate is at 431M rows, approx size at 115GB according to postgres.

Index is on hit1 column with a size of 11GB.

I am trying to calculate the counts of hits:

select hit1,hit2, count(distinct id) as total_count_of_ids,
count(case when offset=-1 then 1 else null end) as prev_position,
count(case when offset=0 then 1 else null end) as same_position,
count(case when offset=1 then 1 else null end) as next_position,
min(cast(substring(year,1,4) as int)) as min_year,
max(cast(substring(year,1,4) as int)) as max_year,
array_agg( id) as id_list
from table where hit1=''
group by hit1, hit2;

As the table is big, I have opted to do this one by one entity like so:

select distinct hit1 from table limit 250 offset x
-- use above query to to store results into table.

This function is wrapped in another function which provides x from a loop. The function is run in a dblink context so that the results can be stored and I can audit as I go along.

The cycle needs to run 1,000,000, I am yet to get a number for the count of distinct hit1 values as the query takes too long to come back. The first 750 values (3 iterations) come back on the iteration in under a minute each, but at the next iteration is where it keels over. I will provide log data when this iteration dies.

While the query is running (at the time when the distinct list is calculated) there seems to be minimal cpu usage. There also seems to be some memory being unavailable.

Edit 2 to show results:
In the functions I use dblink for auditing. After the first 3 cycles (0-2) it takes one hour to return results from
    select distinct hit1 from table limit 250 offset 750;
compared to the previous hit1 order this one is no alphabetical and it seems to return or die only 77 results in.

I don't know if my answer is accurate as we don't have much informations in your question, but perhaps you should consider one of this lead:

partitioning
partial indexes


Subquery inside a select vs left join Often I have to return the count of of some row in a query that fetch other non related rows.

For example A table User a table Review and a Table picture

User:
id
nickname

Review:
id
to_user_id
from_user_id
rating

Picture:
id:
user_id
url

Let's say I want in one single query retrieve the nickname of a 'given' userId all its pictures url as well as the count of how many people reviewed that user.

The first and easy way I think when doing this query would this:

SELECT
  u.nickname
  (SELECT count(*) FROM review WHERE to_user_id = u.id) as reviewCount,
  p.url
FROM user
LEFT JOIN picture ON p.user_id = u.id
WHERE
  u.id = 1

The other way of doing this is without that subselect and by joining the review table on the right user_id

SELECT
 u.nickname,
 r.reviewCount,
 p.url
FROM user u
LEFT JOIN (
    SELECT to_user_id, count(*) reviewCount FROM review GROUP BY to_user_id
 ) r ON r.to_user_id = u.id
LEFT JOIN picture ON p.user_id = u.id
WHERE u.id = 1;

I'm not very an expert with db query performance and tuning. Could someone explain me if a solution is better than the other? (Or if there is an other better solution) ?

EDIT:
Sorry forgot to mention. I'm working with the latest MySQL

You do not specify which RDBMS you are working with. Most what I write here should be quite independent but I mostly have experence in MySQL so maybe different systems allow some other optimizations.

The (SELECT count(*) FROM review WHERE to_user_id = u.id) as reviewCount is a dependent subquery - it will be executed for each row in your results. Even if one execution is fast, potencially thousands of them can make it slow.

The one in the JOIN is a derived table - it will be executed only once and materialized into a temporary table, which will then be joined to your other tables. If the query is fast (can use index on (to_user_id)), it is good. But in this case the count will be counted even for users which do not really show in the results. But.. you can just push the condition in there (to_user_id = 1 instead of the GROUP BY).

But to make things not so simple there exist some optimizations in newer versions. The dependent subquery can be made faster by using a subquery cache in MariaDB 10 (and IIRC MySQL 5.7, but I did not verify). That means that in your case all rows in result have u.id = 1 -> to_user_id = 1 and the subquery will be actually executed only once and then the cached results will be used. If that is available, the difference between both versions will be minimal.

Personally I prefer your second version most of the time but there are some cases when the first one will be faster - I once had a query where it was not simply possible to limit the rows in the JOINED subquery the right way, but switching to the dependent subquery only few unique combinations were actually read.


Temporary table is full The table '/mnt/disks/data/tmp/#sql58de_a0f6_7' is full
The query is quite complex/big. Lots of joins in joins and unions, sorting, grouping, ...
1 out of 10 the result is returned. Otherwise it results in the above error.
CPU and memory increase but never max out while running the query.
There is plenty of disk space (100Gb free).
I have fiddled with:
innodb_log_file_size
innodb_log_buffer_size
innodb_temp_data_file_path
innodb_data_file_path
innodb_data_home_dir

max_heap_table_size
tmp_table_size

table_open_cache
table_definition_cache
thread_cache_size
sort_buffer_size

tmpdir and slave_load_tmpdir are set to /mnt/disks/data/tmp which has plenty of space and nevers runs out of space when running the query. innodb_tmpdir is set to NULL which means it falls back to tmpdir?
Some settings I've changed because of things I read about this issue. Others after mysqltuner advice.
But I could not let the query succeed consistently.
So: there is something running out of space. But what? And where?

Version: Ver 8.0.23 for Linux on x86_64 (MySQL Community Server - GPL)
Storage engine: InnoDB

When an InnoDB table is full, it has nothing to do with the OS Diskspace.
InnoDB views a table as being full in terms of the storage engine itself.
There are two circumstances under which this can happen
innodb_file_per_table off
I once had a client that had 2TB ibdata1/ibdata2. The client was unable to write any more transactions although ibdata file had 106GB of fragmentation. The solution was to give it more space.
innodb_file_per_table on
When InnoDB tables are stored .ibd files, the table can become full when the undo log gets full
See my old posts going back 10 years on this topic

Feb 22, 2018 : The table is full - MariaDB
Jun 16, 2014 : MySQL Index creation failing on table is full
Mar 31, 2014 : mysql directory grow to 246G after one query, which failed due to table is full
May 31, 2013 : `ERROR 1114 (HY000) the table ... is full` with innodb_file_per_table set to autoextend
Nov 26, 2011 : ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full

IMHO this should fully explain a full InnoDB table


Transform a varchar field with #-delimiters into multiple rows I am working with a database where some data (integer values representing selected options of a dropdown menue) is saved as a string (varchar) inside only one table column. In products_table (let's say), there is a product_id column mapped to a selected-options column. In the latter, data is saved as #3#9#15# where 3, 9 and 15 are IDs of dropdown menue options that have been selected by a user. There is another table (let's say option_table) where each of these options with IDs 1 to 15 are given more attributes (like a label and a weight).
For statistical reasons, I want to create a MySQL view with the columns selected option and number of products (the number of products where this option has been selected). To get there, I need the single values from #3#9#15#, that is, explode the string somewhere on the way.
Now I read that this form of saving data is not encouraged, which is why there is no built-in explode/split function for strings in MySQL. But I cannot change the data format, which has been determined inside a huge software. I didn't make this design choice, but I have to somehow work with it.
Working with the built-in function substring_index() is no choice because the number of selected options saved in this field varies, from 0 to 15. Also, the maximum number is not fix, as new options may be created from time to time.
I tried writing an own procedure that will take one number from #3#9#15# (for example, by repeatedly trimming the # delimiters) and create a new row with it. The procedure should access the value with a SELECT statement, create a new view with CREATE OR REPLACE VIEW, and then insert a new row with only the first option (3) option with INSERT INTO `viewname`(`product_id`,`option`) VALUES(@productID, SUBSTR(@options,2,1)); where @options == #3#9#15# for example. I tried writing the procedure for only the first option at the beginning, but it already failed at this stage.
Other ideas I had included trying to convert the string @options into a SET datatype, so I can use find_in_set() function. I also read this question and this article but found them very hard to understand.
Having only basic knowledge of MySQL, is there some way for me to create this view? I would be very thankful for any hint in which direction I should go on researching.
Edit: I have server version 10.3.28 - MariaDB Server. Do you need more information on this?
Edit2: Due to an (ongoing) bug in phpMyAdmin, I could not use SQL WITH statement (see here).
Edit3: It turned out that the delimiter used by the software was not actually a # sign, but was only displayed by phpMyAdmin as such. It was instead the ASCII control character 0001 or "^A".

Here is one way to do it:
SELECT zz.`idx`
  FROM (SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX('#3#9#15#', '#', num.`id`), '#', -1) AS SIGNED) as `idx`
          FROM (SELECT (h*10+t+1) as `id`
                  FROM (SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
                       (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b) num
                 WHERE num.`id` > 0) zz
 WHERE zz.`idx` > 0
 ORDER BY zz.`idx`;

You can pass up to 100 #-separated IDs into the string and get what you need split out as an UNSIGNED integer.


couchbase - multiple buckets or documents with different types Background Information

This is day one for me with couchbase.  I've been reviewing the sample databases using the database console that comes with couchbase.
Specifically, I've been playing with the "beer-sample" application to try to understand how to organize my database.
It seems that they have two different types of documents - breweries or beers - and everything is just in one bucket.

For my personal application, I have the following data:

{
  "server_details": {
    "name": "testserver",
    "dns_name": "testserver.mydomain.net",
    "ipv4": "10.xx.xx.xx",
    "ipv6": "",
    "type": "webserver"
  },
  "server_status": {
    "up_to_date": true
  },
  "packages": {
    "package1": {
      "current_version": "x.x",
      "previous_version": "x.x",
      "last_updated_on": "2016-03-16",
      "play_id": "link to audit table"
    },
    "package2": {
      "current_version": "x.x",
      "previous_version": "x.x",
      "last_updated_on": "2016-03-16",
      "play_id": "link to audit table"
    }
  }
}

I have different server types such as webserver, appserver, dns server etc.  So I'm planning to just use the same json object but change the "type" field.

Question

I also need to create some sort of a audit table that will show the history of when a package on a server was updated.
I need to track things like:

execution date
script name
package1_version_b4_update
package1_version_after_update
server_dns_name

This is the json object I've come up with for this type of a document:

{
  "audit_details": {
    "script_name": "upgrade_all_mrts",
    "rt_ticket_number": "16123",
    "execution_date": "2016-03-16",
    "status": "success"
  },
  "affected_servers": {

    "server1": {
      "package1":{
        "version_after_update": "x.x",
        "version_before_update": "x.x",
        "last_updated_on": "2016-03-16"
      },
      "package2": {
        "version_after_update": "x.x",
        "version_before_update": "x.x",
        "last_updated_on": "2016-03-16"
      }
   },
    "server2": {
      "package1":{
        "version_after_update": "x.x",
        "version_before_update": "x.x",
        "last_updated_on": "2016-03-16"
      },
      "package2": {
        "version_after_update": "x.x",
        "version_before_update": "x.x",
        "last_updated_on": "2016-03-16"
      }
   }
  }
}

Would this be just another document with a completely different structure? or should i be putting it in another bucket?
If it should all be in the same bucket, is the structure of the json object I'm proposing appropriate?  Fundamentally, I guess my question is do you dump everything into one bucket as a collection of varied documents... and just make sure that the related documents have the same structure so that you can query them all at once?

Sorry for the remedial questions but I'm try to build a prototype on the fly ...while reading the docs.  Any tips would be appreciated.

I'm also new to Couchbase but from what I've learned so far: generally, the reason you would want to use different buckets doesn't necessarily relate to the type of documents you want to put in them. That is, you may configure buckets to replicate differently, use XDCR, allocate memory differently, and so on. However, it's perfectly acceptable to have a wide variety of different document "types" in the same bucket.

It might be a good idea to give the different documents a "type" property, so that you can more easily query them.

So, for instance, in your examples, maybe the first one would have "type":"server", and the second one would have "type":"audit", and so on.


Query SQL Server Alert System Mail Session Settings I'm looking for a way to query the Mail session settings under Agent > Alert System > Mail Session. Similar to how sp_MSgetalertinfo returns information about the Fail-safe operator, I'd like to determine if the Enable mail profile is checked, and what the Mail system and Mail profile are set to. I understand these may only be contained in registry settings.

The reason why I want to query for these is because we have them enabled... but twice now they have been disabled by someone, at some point in time, and I've only caught it because I got the dreaded "[476] Database Mail is not enabled for agent notifications. Cannot send e-mail to" error in the agent log. This is a third part DB which support has full access to. I'm not sure if they accidentally (or purposely) disabled it, or if it could be disabled by some other system means that isn't done by a user.

In order to not make this an XY question, I'm open to suggestions on how to audit these changes with a server audit specification.

I use this code to validate Database Mail profile configuration:
/*
    Confirm the Database Mail account and profile is configured correctly
*/
DECLARE @ProfileName        varchar(255);
DECLARE @AccountName        varchar(255);
DECLARE @AccountFromAddress varchar(255);
DECLARE @AccountReplyTo     varchar(255);
DECLARE @SMTPServer         varchar(255);
DECLARE @SMTPPort           varchar(255);
DECLARE @DatabaseMail       varchar(255);

SELECT
      @ProfileName          = smp.name
    , @AccountName          = sma.name
    , @AccountFromAddress   = sma.email_address
    , @AccountReplyTo       = sma.replyto_address
    , @SMTPServer           = sms.servername
    , @SMTPPort             = sms.port
FROM msdb.dbo.sysmail_account sma
    INNER JOIN msdb.dbo.sysmail_profileaccount smpa
        ON sma.account_id = smpa.account_id
    INNER JOIN msdb.dbo.sysmail_profile smp
        ON smpa.profile_id = smp.profile_id
    INNER JOIN msdb.dbo.sysmail_server sms
        ON sma.account_id = sms.account_id;

/*
    Confirm SQL Server Agent is configured to use Database Mail correctly
*/
DECLARE @res TABLE
(
      [Value] varchar(255)
    , [Data] varchar(255)
);
INSERT INTO @res
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail';
INSERT INTO @res
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile';
IF (
        SELECT COUNT_BIG(*)
        FROM @res r
        WHERE r.[Value] = 'UseDatabaseMail' AND r.[Data] = 1
    ) = 1 AND
    (
        SELECT COUNT_BIG(*)
        FROM @res r
        WHERE r.[Value] = 'DatabaseMailProfile' AND r.[Data] IS NOT NULL
    ) = 1
    SET @DatabaseMail = 'Configured'
ELSE
    SET @DatabaseMail = 'Not Configured';

/*
    Combine results into a single row
*/
SELECT
      ServerName            = @@SERVERNAME
    , ProfileName           = @ProfileName
    , AccountName           = @AccountName
    , AccountFromAddress    = @AccountFromAddress
    , AccountReplyTo        = @AccountReplyTo
    , SMTPServer            = @SMTPServer
    , SMTPPort              = @SMTPPort
    , AgentDatabaseMail     = @DatabaseMail;

The output looks like:
╔══════════════╦═════════════╦═════════════╦═══════════════════════╦════════════════════════╦════════════════════════╦══════════╦═══════════════════╗
║  ServerName  ║ ProfileName ║ AccountName ║  AccountFromAddress   ║     AccountReplyTo     ║       SMTPServer       ║ SMTPPort ║ AgentDatabaseMail ║
╠══════════════╬═════════════╬═════════════╬═══════════════════════╬════════════════════════╬════════════════════════╬══════════╬═══════════════════╣
║ servername   ║ DBA         ║ SMTP        ║ sqlserver@company.com ║ dbasupport@company.com ║ mailserver.company.com ║       25 ║ Configured        ║
╚══════════════╩═════════════╩═════════════╩═══════════════════════╩════════════════════════╩════════════════════════╩══════════╩═══════════════════╝
Various parameters used by SQL Server Agent are stored in the registry.  I use this code to get those details:
DECLARE @res TABLE
(
      [Value] varchar(255)
    , [Data] varchar(255)
);

INSERT INTO @res
EXEC master.dbo.xp_instance_regread
         @rootkey      = N'HKEY_LOCAL_MACHINE'
       , @key          = N'Software\Microsoft\MSSQLServer\SQLServerAgent'
       , @value_name   = N'DatabaseMailProfile';

INSERT INTO @res
EXEC sys.xp_instance_regread
         @rootkey      = N'HKEY_LOCAL_MACHINE'
       , @key          = N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
       , @value_name   = N'UseDatabaseMail'

SELECT r.[Value]
    , r.[Data]
FROM @res r;

The output:
╔═════════════════════╦═══════════════════╗
║        Value        ║       Data        ║
╠═════════════════════╬═══════════════════╣
║ DatabaseMailProfile ║ MV2012MailProfile ║
║ UseDatabaseMail     ║ 1                 ║
╚═════════════════════╩═══════════════════╝


Possible to intercept and alter SQL Queries in the engine before it gets executed? I've given up trying to fix a php program (which is of course geared to MySQL) running on SQL Server.

[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near '`'. for query UPDATE wp_posts SET post_author = yyy, post_date =  N'2017-04-16 16:52:44.000', post_date_gmt =  N'2017-04-16 14:52:44.000', post_content =  N'\\ \', `post_content_filtered` =  N'', `post_title` =  N'test \\', `post_excerpt` =  N'', `post_status` =  N'draft', `post_type` =  N'post', `comment_status` =  N'closed', `ping_status` =  N'open', `post_password` =  N'', WHERE ID = xxx made by edit_post, wp_update_post, wp_insert_post, pdo_wpdb->query, pdo_wpdb->_post_query, pdo_wpdb->print_error

Is it possible to create a filter on a specific database only, and remove all the backticks / grave symbols in order to sanitize all incoming queries?

No, it is not possible to intercept and alter queries.

If somebody dangled millions of dollars in front of me to pull it off, I can imagine building a wire-compatible app that acted as if it was taking queries, then rewrite them and pass them off to another layer. However, that's not a small exercise, and it's left for the reader. This is the kind of thing GreenSQL (now Hexatier) was doing, sitting in between the apps and SQL Server, doing caching and auditing.


Avoiding temporary tables while sorting by a column in a different table So I have this MySQL InnoDB query here:

SELECT s.vid, s.body, s.timestamp, COUNT(v.id) AS votecnt
FROM stories s
LEFT JOIN votes v ON s.vid = v.vid
WHERE s.lv = 1
AND s.status = 1
GROUP BY s.vid
ORDER BY votecnt DESC

and profiling shows that over 93% of the required time to run this query is needed to copy to the results temporary table for further ordering by votecnt. What can be done to make it faster?

Explain output:

|  1 | SIMPLE      | s     | ref  | newest        | newest | 2       | const,const    | 19873 | Using where; Using temporary; Using filesort |

|  1 | SIMPLE      | v     | ref  | votes         | votes  | 4       | sikna_ci.s.vid |     1 | Using index                                  |

You may need to bump up the following variables as well:

tmp_table_size
max_heap_table_size

If these are too small, the tmptable goes to disk quickly.

If these are too large, the tmp table goes to quickly when the limit is surpassed but creates intermittency due to moving the large in-memory tmptable to disk before completing the tmptable's usage.

So, you need to perform a tight balancing act with these tmp table variables. These variables also have the same per-thread constraint that @DTest mentioned in this answer.

BTW in theory a sort buffer is a tmptable in itself, though governed by a different session option (sort_buffer_size). Since you are sorting with a group-by column and not a pure table-established column, temptables are somewhat unavoidable.


Possibilities to speed up InnoDB INSERTs and UPDATEs My website retains user sessions in an innodb table. This usually works well, but a few hundred times a day, the INSERT and particularly the UPDATE statements run slower.

I'm monitoring when, and it turns out it's usually when a bot is hitting my site (googlebot for instance always gets the same session assigned, so each hit will require an UDPATE on the session table).

I presume google hits me so hard that mysql can't keep up with updating the same record. Usually an update takes a few mills, but I've seen it reach a full second. My table btw has never reached more than 7000 records.

Knowing that I have only 1 disk at my disposal, but still some free memory to spare, I was wondering what would be the best possibilities to enhance the UPDATE performance on this particular table (I'm not experiencing any issues on other tables as it's much less write intensive).

InnoDB Architecture

You may find this intriguing but you can double or triple the speed of InnoDB writes. How ?

Disable the double write buffer (using innodb-doublewrite)

First, add this to my.cnf (my.ini for Windows)

[mysqld]
innodb-doublewrite=0

Then, restart mysql

Linux: service mysql restart
Windows

net stop mysql
net start mysql

WARNING

In the event of a crash, the crash recovery phase of the mysql startup may produce inconsistent data or lost data.

If you are using Linux, you can counteract that threat by setting up Percona XtraDB Cluster (PXC) with two or more nodes. If your main node crashes, then you can manually failover to one of the other nodes. Make sure all nodes in PXC has the double write buffer disabled.

GIVE IT A TRY !!!


Can you get a filehandle to an Oracle SecureFile LOB? Does Oracle's SecureFile LOB allow me to obtain a filehandle to the underlying resource.

Both PostgreSQL through lo_open and SQL Server with FILESTREAM's OpenSqlFilestream allow you to get a native filehandle to a resource tracked by the database. What's the Oracle alternative to this?

With PostgreSQL you get a filehandle by providing a resource id (large object id) that is only valid for the duration of the transaction and the DB provides the filehandle.
With SQL Server you get a transactionToken which is only valid for the duration of the transaction which you can provide to the OpenSqlFilestream to get a filehandle.

No, to SecureFile's exposing a FileHandle

You can not. There is no filehandle to (internal) LOBs. Oracle separates their large binary implementations into ones that are internal, or external. Internal Lobs are tracked with a "Lob Locator" (Oracle terminology) which is of type OCILobLocator in the OCI. You can perform operations on them with the Oracle Call Interface (OCI). The OCI's operations take a Lob Locator as a handle but are not otherwise useful, and do not have an equivalent for all POSIX operations. For example, there is no fseek.

OCI Lob Interface (C)
OCCI Programming with Lobs (C++)

External LOBs

Oracle Provides an "external LOB" called a "BFILE". The BFILE is associated with a path on disk. Using that path, you can manually open the file with open, but there is no transaction token, and the filehandle is not closed at the end of the transaction. Note "BFILE"s do not support deduplication, compression, or encryption and you'll be left to the underlying filesystem of the disk for those faculties.


Exclamation point on plan guide I created a plan guide using the following query:

EXEC sp_create_plan_guide
@name = N'Entity_Property fix',
@stmt = N'SELECT ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, CREATED, UPDATED, json_value FROM jirascheme.entity_property WHERE ENTITY_NAME=@P0 AND ENTITY_ID=@P1 AND PROPERTY_KEY=@P2',
@type = N'SQL',
@params = N'@P0 nvarchar(255), @P1 numeric(18, 0), @P2 nvarchar(255)',
@hints = N'OPTION (OPTIMIZE FOR UNKNOWN)';

It seems to work fine, but I noticed that there is a little warning icon on the plan in Object Explorer.

It looks like this:

I don't get any warnings when executing the query, and I can't find any information about it when hovering over it or checking the properties of the plan guide.

This is only applied in a test environment but why does it show up and should I be worried about it?

This is an educated guess, so if anyone has something more authoritative I am happy to rescind it.

I've never used the SSMS UI to manage plan guides before, so I immediately checked a few servers where I know I have viable plan guides setup:

The red Xs indicate the plans are disabled, as one might expect.

When tracing SSMS as it populates this part of the UI, we can see it runs this query:

SELECT pg.name AS [Name]
, 'Server[@Name=' + quotename(CAST(serverproperty(N'Servername')AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/PlanGuide[@Name=' + quotename(pg.name,'''') + ']' AS [Urn]
, pg.is_disabled AS [IsDisabled]
FROM sys.plan_guides AS pg
ORDER BY [Name] ASC

Which only includes basic data and the disabled flag - nothing else. My hunch is that the yellow exclamations are actually the default icons for plan guides.

This doesn't seem too far fetched since you're modifying execution plans and a similar icon is used whenever an execution plan might be impacted by something like a conversion or a plan guide: