Connecting MySQL to Visual Studio 2017 For the life of me, I cannot get the MySQL for Visual Force connector to work in VS. I just want to be able to add a MySQL database as a database connection in VS so I can build an integration.

I have the community version of VS 2017. I have followed the instructions listed on the MySQL website - I have tried the MySQL Installer as well as the standalone download.

My first problems began because the MySQL installer would not recognize the installation of Visual Studio (even though I ALSO have 2013 installed!). So I uninstalled Visual Studio, the MySQL ODBC and .NET connectors, and I tried reinstalling them over and over again. Each time I would attempt to install the MySQL for Visual Studio connector, the installer would say that Visual Studio was not installed on my computer.

So then I installed the connector by itself, BEFORE installing the ODBC or NET connectors and I got it to install. I still could not get the MySQL connector to appear when I tried to connect a new database to VS, though.

Frustrated, I decided to create a User DSN (as opposed to a System DSN) and connect that way. It seemed to work, and I was able to see the connector in VS. However, now when I try to connect, I get "ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application".

I looked up the error, and it appears that there is a mismatch between the x86 and x64 drivers. The problem is that I cannot find a x64 driver for MySQL for VS.

I'm at the end of my rope. I just want to build out a package that will allow me to pull data from the MySQL and push to a SQL server. Is there a better way to do all of this, or am I on the right track? I would appreciate any assistance.

Please use Visual Studio Community Installer and install .Net Development.  After this, you will be able to install MySql for Visual Studio 1.2.7.


Foreign keys to prevent "orphan" file rows in a database for a scenario involving projects, files and downloads In a system that monitors a file mirror by regularly downloading a file, I want to store a history of the results. So I have three tables:

┌──────────┐ ┌──────────────────┐ ┌────────┐
│projects  │ │downloads         │ │files   │
├──────────┤ ├──────────────────┤ ├────────┤
│project_id│ │download_id       │ │file_id │
│name      │ │project_id        │ │contents│
│(FK?)     │ │download_timestamp│ │(FK?)   │
└──────────┘ │file_id           │ └────────┘
             │(FK?)             │
             └──────────────────┘

All downloads and files belong to a project.
Everytime a file is downloaded, a new download is inserted.
If the same contents already exists in the files table for the same project, the new download will point to the existing file, otherwise a new file is also inserted.

I have two foreign keys in downloads, project_id and file_id. Is there a better structure, especially one that prevents orphaned files (files without an associated download)?

Schematically (out of syntax).

Structures:

CREATE TABLE project ( project_id identity,
                       primary key (project_id),
                       name varchar,
                       unique (name) );

CREATE TABLE file ( file_id identity,
                    primary key (file_id),
                    filelink varchar,
                    content varchar,
                    unique (bf(file_link || content)) );

CREATE TABLE download ( download_id identity,
                        primary key (download_id),
                        project_id,
                        foreign key (project_id) references project (project_id),
                        file_id,
                        foreign key (file_id) references file (file_id),
                        download_timestamp default current_timestamp );

Insertion:

INSERT INTO project (name)
VALUES (@name)
ON CONFLICT (name) DO NOTHING;

INSERT INTO file (filelink, content)
VALUES (@filelink, @content)
ON CONFLICT (filelink, content) DO NOTHING;

INSERT INTO downloads (project_id, file_id)
SELECT project.project_id, file.file_id
FROM project, file
WHERE project.name = @name
  AND file.filelink = @filelink
  AND file.content = @content;


User defined table type with a check constraint I have a user defined table type where I want to make sure that a certain column is within a given range. This range however is defined in another table. So I thought I can use a check constraint on this column with a scalar valued function.

    CREATE TYPE [dbo].[Accounts] AS TABLE(
    [AccountName] [varchar](25) NOT NULL
         CONSTRAINT CHK_AccountName CHECK (dbo.validateAccountName(AccountName) = 1),
    [Sales] [int] NOT NULL,
    [Returns] [int] NOT NULL
);

However I get a syntax error, when I do the same on a regular table it works. Based on MSFT documentation check constraints should be ok on table types. What am I missing here ? Any better approach to validate input on a table type ?

Although table types do allow unnamed CHECK constraints, it is not possible to use a user-defined function inside one.

The error message is:

  Msg 2785 Level 16 State 1 Line 3
  User-defined functions, user-defined aggregates, CLR types, and methods on CLR types are not allowed in expressions in this context.
  Msg 1750 Level 16 State 0 Line 1
  Could not create constraint. See previous errors.

See the db<>fiddle demo

Try this solution from https://stackoverflow.com/questions/53330114/user-defined-table-type-with-check-constraint

CREATE TYPE [dbo].[Accounts] AS TABLE(
    [AccountName] [varchar](25) NOT NULL
         CHECK (dbo.validateAccountName(AccountName) = 1),
    [Sales] [int] NOT NULL,
    [Returns] [int] NOT NULL
);


Multiple primary keys in PostgreSQL I have the following table:

CREATE TABLE word(
word CHARACTER VARYING NOT NULL,
id BIGINT NOT NULL,
repeat INTEGER NOT NULL
);
ALTER TABLE public.word OWNER TO postgres;
ALTER TABLE ONLY  word ADD CONSTRAINT "ID_PKEY" PRIMARY KEY (word,id);

When I try to restore it using following command:

psql -U postgres -h localhost -d word -f word.sql

it gives me this error:

  multiple primary keys for table "word" are not allowed

How can I use multiple primary keys in postgres?

how can i use multiple primary keys in postgres ?

You can't. It's an oxymoron - the definition of a primary key is that it's the primary key, singular. You can't have more than one.

You can have multiple unique constraints. You can have a primary key that contains multiple columns (a composite primary key). But you can't have more than one primary key for a table.

However, the code you show doesn't produce the error you mentioned:

$ psql -U postgres regress <<__END__
CREATE TABLE word(
word CHARACTER VARYING NOT NULL,
id BIGINT NOT NULL,
repeat INTEGER NOT NULL
);
ALTER TABLE public.word OWNER TO postgres;
ALTER TABLE ONLY  word ADD CONSTRAINT "ID_PKEY" PRIMARY KEY (word,id);
__END__
CREATE TABLE
ALTER TABLE
ALTER TABLE
$

At a guess you've actually already defined this table and you're ignoring the previous errors, then showing only the last one. If I re-run this code I get the output:

ERROR:  relation "word" already exists
ALTER TABLE
ERROR:  multiple primary keys for table "word" are not allowed

The real error here is the first one, of course.

I strongly recommend always using -v ON_ERROR_STOP=1 in psql, e.g.:

$ psql -v ON_ERROR_STOP=1 -U postgres regress <<__END__
CREATE TABLE word(
word CHARACTER VARYING NOT NULL,
id BIGINT NOT NULL,
repeat INTEGER NOT NULL
);
ALTER TABLE public.word OWNER TO postgres;
ALTER TABLE ONLY  word ADD CONSTRAINT "ID_PKEY" PRIMARY KEY (word,id);
__END__

ERROR:  relation "word" already exists
$

See how it stops at the first error?

(It'd be the default but that'd break backward compatibility).

Response for Google searcher.
If you recibe
multiple primary keys for table "word" are not allowed

maybe you didn't notice that a primary key already exists in the table you want to add it to.


Retrieving users by their age efficiently I have a PostgreSQL database of users and their birthdays and want to get all users in a specific age range:

SELECT * FROM users
WHERE age(birthday) >= 21 AND age(birthday) <= 30;

How can I achieve that this query stays efficient, even when there are millions of users in the database?
Of course there are more filters, I just don't want to calculate the age for every row.

I do not want to use a materialized view.

You want to move the computation from the column, to the other side of the inequality operators where you have literals rather than a column:

WHERE birthday >= current_date - interval '30 years' AND birthday <= current_date - interval '21 years';

Then a simple index on "birthday" will make it efficient.  It might need some fiddling depending on what datatype "birthday" is.  I tested it as a timestamp without time zone.

I can think in several ways:

Create an index on birthday field

CREATE INDEX users_birthday_idx ON users(birthday);

Then i would change the query a little

SELECT * FROM users
WHERE birthday >= (now() - interval '21 years') AND birthday <= (now() - interval '30 years');

Another approach is indexing age function output, this works but in previous experiences query optimizer did not work as well as is an index of table data.

CREATE INDEX users_age_idx ON users(age(birthday));

And if you are repeating query values a lot you can also think in a partial index on fixed ranges

If this is not enough maybe you need to create table partitions.


How to avoid using the IN clause in sql query used in a stored procedure Is there a better way to write the SQL other than using the IN clause in the below given SP. When i am using this IN clause, i suffer a performance dip due to large number or records involved in the User and Member tables.

CREATE PROCEDURE [dbo].[sp_MemberIdsFromUserIds] @dtUserIds UNIQUETABLE readonly
AS
  BEGIN
      SELECT userID,
             Memberid
      FROM   Member
             INNER JOIN USER
               ON UserMemberID = MemberiD
      WHERE  userID IN (SELECT UniqueId
                        FROM   @dtUserIds)
  END

Kindly suggest the alternative as i don't have a full knowledge in writing SP

Where exists is gnerally faster than IN

SELECT userID,
             Memberid
      FROM   Member
             INNER JOIN [USER]
               ON UserMemberID = MemberiD
      WHERE  EXISTS  (SELECT *
                        FROM   @dtUserIds a where [user].userId = a.Uniqueid)

Create proc demo
@A_C varchar(50)
AS
BEGIN
EXEC ('select * from table1 where AC_No in ('+@A_C+')')
END

EXEC demo'321101030249,321101030268'


Getting 0KB of size file while backup mysql database using mysql dump I am using below code to backup my database using php script but I am getting 0KB size file. How can I backup my full database with all routines and functions?

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$dbname='mydatabase';
$toDay = date('d-m-Y');
    //exec("mysqldump --user=$dbuser --password='$dbpass' --host=$dbhost --single-transaction $dbname > D:\Sql_Backups/".$toDay."_DB.sql");
    //exec('mysqldump --user=$dbuser --password=$dbpass --host=$dbhost $dbname > D:\Sql_Backup/file.sql');
      exec ("mysqldump --routines --h $dbhost --u $dbuser --p $dbpass --single-transaction $dbname > D:\Sql_Backup/db3_backup.sql" >

I don't know how to write this in php, but you should write mysqldump command like below, there is no need to write three command one is enough

mysqldump --user=root --password=root --host=localhost --single-transaction --routines db_name > db_name.sql

UPDATE

I did it here is the php script

<?php
define("BACKUP_PATH", "/home/abdul/");

$server_name   = "localhost";
$username      = "root";
$password      = "root";
$database_name = "world_copy";
$date_string   = date("Ymd");

$cmd = "mysqldump --routines -h {$server_name} -u {$username} -p{$password} {$database_name} > " . BACKUP_PATH . "{$date_string}_{$database_name}.sql";

exec($cmd);
?>

It generate backup file named as 20140225_world_copy.sql in /home/abdul/

For reference have a look at This link.

You need find where the mysqldump.exe for backup and mysql.exe for restore is in your computer. Then copy the path to the code: Then create a backup folder to save the backup file.

For backup

 $dumpfile");
?>


You do not have privileges to manipulate with the users! Error message:

You do not have privileges to manipulate with the users!

Appears in PhpMyAdmin using Mariadb 10.3.7 on MacOs High Sierra installation from Homebrew

I'm logged in as root
installed and rebooted with multiple server options: Mysql 5.7, Mysql 8.0
Mariadb 10.2 and 10.3.7.

I've tried everything from granting tables, flushing the permissions.
I need to be able to edit users from the PhpMyAdmin page and its not allowing me even though I have root access (with password set)

According to comments in this Github issue report for phpMyAdmin, you probably need to set this option:

$cfg['Servers'][$i]['DisableIS'] = true;

in config.inc.php. Though it sounds like this is a workaround for a bug, so may be fixed in later releases or not an issue in earlier releases.

See fix and patches here at phpmyadmin:

https://github.com/phpmyadmin/phpmyadmin/issues/14433#issuecomment-544249375


Oracle TRANSLATE Not Returning Expected Result We are trying to execute TRANSLATE on a couple hundred rows of data. We have a temporary table containing ID's to rows that need to be updated using TRANSLATE. The odd part is if we attempt to select say two rows, the command is executed properly and the information is updated. However, when we try to run it against 200 rows, it says that 200 rows were updated, but in inspecting the data, they have not been updated. Is there a limitation to the number of rows that can be updated using TRANSLATE in a single query?

We're doing something like:

UPDATE TABLE_A SET COLUMN_TO_UPDATE =
TRANSLATE(COLUMN_TO_UPDATE,'ñ','n')
WHERE ID IN (SELECT ID FROM TEMP_TABLE);

Phil is right, "No, there isn't a limit", "it /should/ just work", and " this will be difficult to answer without copies of the data or another way to reproduce".  Here is a demonstration:

/*
drop table table_a;
drop table temp_table;
create table table_a as
    (select level id, to_char(level) column_to_update from dual connect by level <=200);
create table temp_table as (select level id from dual connect by level <= 200);
update table_a set column_to_update='2ñ' where column_to_update='2';
*/

SELECT * FROM table_a;

UPDATE table_a SET column_to_update = TRANSLATE(COLUMN_TO_UPDATE,'ñ','n')
WHERE ID IN (SELECT ID FROM TEMP_TABLE);

SELECT * FROM table_a;

You could try adding a WHERE clause to verify that TRANSLATE is actually changing rows.

UPDATE table_a SET column_to_update = TRANSLATE(COLUMN_TO_UPDATE,'ñ','n')
WHERE ID IN (SELECT ID FROM TEMP_TABLE)
AND column_to_update <> TRANSLATE(COLUMN_TO_UPDATE,'ñ','n') ;

If this returns 200 rows then you might not be looking at the correct rows.  If it returns zero rows then the TRANSLATE function is not changing the data.


Placing Transaction Log on Separate Volume [solid state]? Transaction logs are often isolated on a separate volume. The rationale for this practice, as I understand it, is that the transaction log's data is written sequentially -- and hard drives can execute write operations with far greater speed sequentially as opposed to randomly. This is due to the little needle inside the drive which has to move a much shorter distance when writing sequential blocks of data, as opposed to random writes.

(Sorry for the naive interpretation. Just trying to make sense of what I've read.)

With this in mind… It occurs to me that solid state drives don't have little needles and platters and stuff moving around inside of them. If my database and transaction log are both located on a single RAID 5 of eight solid state drives, is there really any upside to moving the transaction log onto its own separate volume? If the supposed efficiency boost is based upon the premise of sequential writes reducing the distance that needles move and platters spin, and a solid state drive has none of these moving parts, what do I gain by isolating the log?

Short answer, use a single array, there is unlikely to be any performance gain from separating logs from data across 8 SSD drives. See SQL on SSDs: Hot and Crazy Love for a more detailed (and entertaining) commentary on SSDs. Pay particular attention to the notes on correlated failures of SSDs.

Separating logs from data on SSDs is more an RPO (recovery point objective) than performance issue. The notion being that you could reduce your RPO by separating logs from data such that in the event of the data array failing, your log array should/could remain accessible. The cautious would consider a different make/model of drive in each of the two arrays to mitigate the correlated failure issue if RPO was critical.

The comments regarding bus bandwidth are irrelevant. If you need to shift that much IO, you've got bigger issues to worry about.

There is a group that agrees that on an HDD it would be beneficial to separate, they still claim that for SSD drives this is no longer needed.

So for them I want to ask "if there is no issue of contention then why a RAID 10? There is no need for stripping anymore! So mirrowing alone would be enough, and of course there is no need for 8 drives, 2x the database size should suffice!".

However the reality is that if something needs a RAID 10 it is the log file!

This is not just because of the issue of sequential vs random (see resources below), but it is actually very crucial once you understand how SSD drives work.

To make a long story short (for a longer description see
http://arstechnica.com/information-technology/2012/06/inside-the-ssd-revolution-how-solid-state-disks-really-work/), an SSD drive is very effecient in reads, and in writing out zeros, however to write out ones it is not so efficient as it has to erase the entire section to write even a single one!

While this is not an issue for general writes, as they are anyway buffered in memory, and written out in page boundaries, it is a major issue for the log file, as the log file is bypassing any cache and instead SQL server blocks until the logs are written to disc!, which means that for each write there will probably be a full section erase.

So to optimize it, I would suggest to dedicate every extra disc (besides 2x the database size, no need for stripping!) for the log file, this way it will be able to process as many as possible in a shorter time frame.

OLDER ANSWER

The answer is yes, for three reasons.
1) Random vs Sequential - While it is clear that SSD increased performance dramatically for random writes, still the issue of random vs sequential remains, as can be seen from the following whitepapaers and links:

http://research.microsoft.com/pubs/63596/USENIX-08-SSD.pdf
http://static.usenix.org/event/fast12/tech/full_papers/Min.pdf
https://stackoverflow.com/questions/2100584/difference-between-sequential-write-and-random-write
http://www.neowin.net/forum/topic/992800-why-sequential-vs-random-io-speeds-differ-for-ssd/

2) Reliability - There is a strong chance that all SSD drives will fail simultaneously, in which case RAID is no protection, however since an SSD drive used solely for sequential has a different life span this might be your lifesaver

3) Write Contention - The reason for putting logs on its own spindle is not just because of random vs sequential but also because of write contention, as one can see from the fact  that it is also recommended to have tempdb on a separate volume which indicates that the issue here is also about write contention.

And this should apply even more for the log file, as writes to the log blocks transactions from being considered commited until it is written to the disk surface.

In fact for the logs you might use regular HDD drives as apr Dell's white paper at http://www.dell.com/downloads/global/products/pvaul/en/ssd_vs_hdd_price_and_performance_study.pdf

EDIT

Putting tempdb on its own array for spinning discs is recommended by Microsoft, see

MSDN - http://technet.microsoft.com/library/Cc966545 (section "Best practices and recommendations")
MSDN - http://msdn.microsoft.com/en-us/library/ms175527(SQL.105).aspx
Sunil Agarwal (SQL Server Storage Engine Team Member) - http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/04/managing-tempdb-in-sql-server-tempdb-configuration.aspx
Paul Randal (By linking to the above link as a useful link) - http://www.sqlskills.com/BLOGS/PAUL/category/tempdb.aspx
StackOverflow - https://stackoverflow.com/questions/360088/sql-server-tempdb-optimization-tips-for-a-new-server
ServerFault - https://serverfault.com/questions/19866/recommended-disk-partition-setup-for-a-sql-server
"Microsoft Sql Server Management And Administration (SAMS)" (page 33)

and numerous others and it is the general accepted notion in Sql Server, while no one expressed a problem with splitting the array.

Further more the SQL Server team has created the concepts of Filegroup and Partioining partitioning, with the sole intention to be able to move them on a separate array.

And in fact the MSDN at http://msdn.microsoft.com/en-us/library/ms187087(v=sql.105) recommends that there might be a performance benefit from separating the nonclustered index on its own array, (though this shouldn't be taken as a general advice for every situation, only for specific workloads, see more info at http://weblogs.sqlteam.com/dang/archive/2008/08/01/Are-you-a-DBA-Monkey.aspx).

As such it is just a logical extension to say the reason for separation on spinning discs is not just tied to the issue of sequential vs random reads, but to general write contention, something that applies to SSD's as well.

While it might be that some people disagree with that advice and consider that there is no benefit of putting tempdb and its own volume (as Jack Douglas), and you might even claim that there is no benefit from separating the log files (as Mark Storey-Smith), and instead claim that splitting the array is much more worse, still don't forget  that this is a new approach going against the general accepted approach suggested by Microsoft and the community, and so far no one has provided links to any benchmark tests to support it.

So my word to all downvoters is, I find it very unethical to downvote a post just because it  has a different opinion than yours, especially when 1) your opinion is going against the general accepted theory 2) and it is against the vendors (Microsoft) own documentation 3) and you haven't provided any proof just an opinion.

But in this case it is even more ridiculous, since my post is nothing more than the logical extension to this theory, so one that considers this post to be bed advice needs of course to go back to all posts that advice this theory and downvote them.

Say someone decides that RAID is old school theory and downvotes all posts recommending it, how does this makes sense?


Improve performance for order by with columns from many tables Using PostgreSQL 8.4, I'm trying to consult two tables with 1 million records using order by with indexed columns of the two tables, and I'm losing performance (with 1 column takes 30 ms and with two columns takes 5 minutes). E.g.:

select r.code, r.test_code, r.sample_code, s.barcode, s.registry_date
from requests r
inner join samples s on (s.code = r.sample_code)
order by s.barcode  asc , r.code asc
limit 21;

Table Info:

CREATE TABLE public.samples (
  code BIGINT NOT NULL,
  barcode VARCHAR(40) NOT NULL,
  registry_date TIMESTAMP WITH TIME ZONE NOT NULL,
  CONSTRAINT samples_pkey PRIMARY KEY(code)
);

CREATE INDEX idx_samp_barcode ON public.samples (barcode);
CREATE INDEX idx_samp_barcode_code ON public.samples (barcode, code);
CREATE INDEX idx_samp_barcode_code_desc ON public.samples (barcode DESC, code DESC);
CREATE INDEX idx_test_identifier_desc ON public.samples (barcode DESC);

CREATE TABLE public.requests (
  code BIGINT NOT NULL,
  test_code INTEGER NOT NULL,
  sample_code BIGINT NOT NULL,
  CONSTRAINT requests_pkey PRIMARY KEY(code),
  CONSTRAINT "Requests_S_fk" FOREIGN KEY (sample_code)
    REFERENCES public.samples(code)
    ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE,
  CONSTRAINT "Requests_T_fk" FOREIGN KEY (test_code)
    REFERENCES public.tests(code)
    ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE
);

CREATE INDEX idx_req_test_code ON public.requests (test_code);
CREATE INDEX idx_req_test_code_desc ON public.requests (test_code DESC);
CREATE INDEX request_sample_code_index ON public.requests (sample_code);
CREATE INDEX requests_sample_code_desc_idx ON public.requests (sample_code DESC, code DESC);
CREATE INDEX requests_sample_code_idx ON public.requests (sample_code, code);

Each table has 1 million rows.
All rows in both tables are distinct.
If I order by either column alone, the execution time is ~ 30 ms.
There is no sample without request.
A sample can have many s.code for the smaes.barcode.

How can I improve the performance?

Problem

This is a more complex problem than is obvious on a quick glance. You are sorting by two columns, each from a different table, while you join on two other columns. This makes it impossible for Postgres to use the provided indexes and it has to default to (very) expensive sequential scans. Here is a related case on dba.SE:

Can spatial index help a “range - order by - limit” query

Indexes

You need two indexes for best performance, both of which are already present:

CREATE INDEX idx_samp_barcode_code ON public.samples (barcode, code);
CREATE INDEX requests_sample_code_idx ON public.requests (sample_code, code);

But the plain query you have can not make use of them, not even in pg 9.4.

Query

Postgres 8.4 is quite a hurdle to take. But I think I found a way with a recursive CTE:

WITH RECURSIVE cte AS (
   ( -- all parentheses are required
   SELECT r.code, r.test_code, r.sample_code, s.barcode, s.registry_date
   FROM  (
      SELECT s.barcode
      FROM   samples s
      -- WHERE  EXISTS (SELECT 1 FROM requests WHERE r.sample_code = s.code)
      ORDER  BY s.barcode
      LIMIT  1  -- get smallest barcode to start with
      ) s0
   JOIN   samples  s USING (barcode)  -- join all samples with same barcode
   JOIN   requests r ON r.sample_code = s.code
   ORDER  BY r.code  -- start with ordered list
   )

   UNION ALL
   (
   SELECT r.code, r.test_code, r.sample_code, s.barcode, s.registry_date
   FROM  (
      SELECT s.barcode
      FROM   cte c
      JOIN   samples s ON s.barcode > c.barcode
      -- WHERE  EXISTS (SELECT 1 FROM requests WHERE r.sample_code = s.code)
      ORDER  BY s.barcode
      LIMIT  1  -- get next higher barcode
      ) s0
   JOIN   samples  s USING (barcode)  -- join all samples with same barcode
   JOIN   requests r ON r.sample_code = s.code
   ORDER  BY r.code  -- again, ordered list
   )
   )
TABLE cte
LIMIT 21;

Tested and works for me in pg 9.4. It uses the indexes (partly in index-only scans in pg 9.2+).

pg 8.4 already has recursive CTEs. The rest is basic SQL (even TABLE is available in pg 8.4 (and can be replaced with SELECT * FROM). I hope there are no restrictions to spoil the party, I don't have an installation of pg 8.4 any more.

Explanation

The commented parts:

-- WHERE  EXISTS (SELECT 1 FROM requests WHERE r.sample_code = s.code)

would be needed if there could be samples with no requests at all, which you ruled out in a comment.

The query relies on this documented behavior of recursive CTEs:

  Tip: The recursive query evaluation algorithm produces its output in
  breadth-first search order.

Bold emphasis mine. And on this, too:

  This works because PostgreSQL's implementation evaluates only as many
  rows of a WITH query as are actually fetched by the parent query.
  Using this trick in production is not recommended, because other
  systems might work differently. Also, it usually won't work if you
  make the outer query sort the recursive query's results or join them
  to some other table.

Bold emphasis mine. So, it's only guaranteed to work in PostgreSQL and only if you do not add another ORDER BY in the outer query.

This is fast for a (relatively) small LIMIT in the outer query. For the case at hand, it should be very fast. The query would be no good for a large LIMIT.

PL/pgSQL function

The other option I can think of is a procedural solution with plpgsql. Should be a bit faster yet, especially for repeated calls:

CREATE OR REPLACE FUNCTION f_demo(_limit int)
  RETURNS TABLE (code int8, test_code int, sample_code int8
               , barcode varchar, registry_date timestamptz) AS
$func$
DECLARE
   _barcode text;
   _n       int;
   _rest    int := _limit;  -- init with _limit parameter
BEGIN
   FOR _barcode IN
      SELECT DISTINCT s.barcode
      FROM   samples s
      -- WHERE  EXISTS (SELECT 1 FROM requests WHERE r.sample_code = s.code)
      ORDER  BY s.barcode

   LOOP
      RETURN QUERY
      SELECT r.code, r.test_code, r.sample_code, s.barcode, s.registry_date
      FROM   samples  s
      JOIN   requests r ON r.sample_code = s.code
      WHERE  s.barcode = _barcode
      ORDER  BY r.code
      LIMIT  _limit;

      GET DIAGNOSTICS _n = ROW_COUNT;
      _rest := _rest - _n;
      EXIT WHEN _rest < 1;
   END LOOP;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT * FROM f_demo(21);

MATERIALIZED VIEW

A large OFFSET has a similar effect as a large LIMIT. While all the skipped rows don't add to I/O, they still have to be computed to determine the first rows after the offset. If you need that, use a MATERIALIZED VIEW with an added row number and an index on that.

Postgres 9.3+ has a built-in feature, with your outdated software you have to knit the solution by hand. It's not that complicated, though. Basically a snapshot- table thats populated from a pre-defined SELECT statement or VIEW, like. Basically, create the table as:

CREATE TABLE req_sample AS
SELECT row_number() OVER (ORDER BY s.barcode, r.code) AS rn
     , r.code, r.test_code, r.sample_code, s.barcode, s.registry_date
FROM   requests r
JOIN   samples s on (s.code = r.sample_code)
ORDER  by s.barcode, r.code;

ALTER TABLE req_sample ADD CONSTRAINT req_sample_rk PRIMARY KEY (code);
CREATE INDEX foo ON  req_sample (rn);

Save that SELECT as function, view or the plain query text. Refresh (expensive) in one transaction:

BEGIN;
-- drop PK & index
TRUNCATE req_sample;
INSERT INTO req_sample SELECT ... ;
-- add PK & index
COMMIT;

Redundant barcode column

The MV gets more expensive if underlying tables change a lot and you need "current" results. There's one more halfway cheap option: store the barcode in the requests table redundantly. You could include it as 2nd column in the FK constraint to samples ("Requests_S_fk") and make that ON UPDATE CASCADE to avoid outdated / conflicting data. Then you can work with an index on (barcode, code) in requests.

For the last two options you can then simplify pagination with directly applicable indexes with a WHERE clause on values from the previous page replacing the OFFSET. Consider this presentation about "Pagination done the PostgreSQL way" on use-the-index-luke.com.

try to do

select * from
(
select
r.code, r.test_code, r.sample_code, s.barcode, s.registry_date,
 from requests r
inner join samples s on (s.code = r.sample_code)
order by s.barcode  asc   limit 21
) as tbl
 order by tbl.code asc

or

    select * from
(
select
r.code, r.test_code, r.sample_code, s.barcode, s.registry_date,
 from requests r
inner join samples s on (s.code = r.sample_code)
) as tbl
 order by tbl.barcode  asc , tbl.code asc limit 21


How to prevent SQL Server database server from running out of disk space? What are the best preventive approaches that we can do to prevent the SQL Server database server from running out of disk space ?

Is there a way to set the maximum database file size (both the PRIMARY and the LOG) ? Once the maximum size reach, it should delete older record to give more free space ?

Notes: I have my transaction log shipping every 2 days, plus daily backup of the db. My hard disk is 500 GB, but the database size is already reaching 490 GB. I'm afraid it'll run out of diskspace soon.

I have my transaction log shipping every 2 days, plus daily backup of the db. My hard disk is 500 GB, but the database size is already reaching 490 GB. I'm afraid it'll run out of diskspace soon.

Taking transaction log backups, as are required for log shipping, internally clears portions of the transaction log, allowing the physical space on disk to be reused.

Assuming it's the size of the log file that's the problem, the frequency of the log backups should be increased. This will mean less physical disk space is required for ongoing operations; the physical file could then be shrunk to reclaim the unused space, probably so it can be used by data file growth.

If you still only want to do log restores on the secondary once every 2 days, that's fine -- the frequency of the backup/copy/restore jobs can be set independently.

Note that this only applies to log files.

If it's the data portion of the database that is growing and the amount of log being generated isn't necessarily increasing (and is a small part of the total space used), you will need to either (a) provision more storage, or (b) implement some kind of archiving/data deletion process, as there is no built-in mechanism that will clear out data.

Best approach would be to make a maintenance job that would delete logdata an such older then X days/months and run it as a night job.
and then shrink the database.

You can also check for the available space on the disk drive via a C# dll for instance that SQL stored procedure can use.

And execute the cleanup on demand


Online Learning: Different User Roles Different Fields I'm trying to build an online learning system ( similar to Udemy ), where users have different types ( or roles ).

For now "roles" are : students , instructors  and admins ,and it may increase in the future.Where each role has its own permissions.

A user can have 1 role or more, so
a user can be a student of some course and an instructor of ( another course )

see below:

the problem :
now, each role may have different fields ,for example:
an instructor role has a unique field like "biography" which no any of the other user roles has.

how can this be accomplished with this database design , I was thiking of creating tables like:

**instructors_users Table**
user_id
some unique fields for instructors role

**students_users Table**
user_id
some unique fields for students role

Is this right ?

Use Table Inheritance

If you don't mind nulls, use Single Table Inheritance. Add a "type" column, then add nullable columns for the different types, and check constraints for integrity

https://www.martinfowler.com/eaaCatalog/singleTableInheritance.html

if you do mind nulls, add one table per subtype and join to super type with a foreign key

https://www.martinfowler.com/eaaCatalog/classTableInheritance.html

ORMs like Hibernate can automate this for you

Add a table called field which has all fileds (biography, mathematics..)

**field Table**
id
name_fields

And another table called user_field_role

**user_field_role table**
user_id
field_id
role_id

if you want to make a user X instructor of biography then in user_field_role you put the user_id and biography id and instroctor id


Oracle 11G Startup failed after altering the number of system processes When I alter the number of system processes to 100000 and restart the database, I get the error below:

ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semids_per_proc failed with status: 0
ORA-27301: OS failure message: Error 0
ORA-27302: failure occurred at: sskgpwcr2
ORA-27303: additional information: semids = 524, maxprocs = 100000

How can I return it to the normal processes?

First you can try:

STARTUP NOMOUNT
ALTER SYSTEM SET processes=400;
SHUTDOWN IMMEDIATE
STARTUP

If Oracle cannot start even in NOMOUNT mode then you can fetch configuration information from spfile:

strings $ORACLE_HOME/dbs/spfile$SID.ora > /tmp/pfile

Then edit processes in /tmp/pfile cleanup some mess made by strings start Oracle with pfile and recreate spfile:

STARTUP pfile=/tmp/pfile
CREATE spfile FROM pfile='/tmp/pfile';


Enable TRY style error handling Is there a SET command or similar I can enable to get the same error handling as a try/catch block? Specifically halting the script/procedure on the first error.

The only way I've currently found so far is to wrap the whole query in a try/catch such as

BEGIN TRY
    -- procedure body goes here
END TRY
BEGIN CATCH
    THROW;
END CATCH

While this hack does work to enable the correct error handling behaviour it just feels wrong to have a catch block that just re-throws the error.

In this particular case there's nothing more I need to do with the error other than halt the script and throw the error back out to the script runner.

In my opinion you should use TRY/CATCH every time when you have update, insert or delete statements. This has saved me a lot of headaches. Check this -- 1


How to change VARCHAR length in PRIMARY KEY in SQL Server 2014 I need to change the VARCHAR length from 20 to 25 in a code column in a library table in SQL Server 2014, but SSMS wants to DROP and RE-CREATE all my tables where the library table in question is linked to.

Is there a way to change the VARCHAR length of a PRIMARY KEY without dropping all the tables?

As you are increasing the length you can just do an alter column, for example:

CREATE TABLE dbo.T1
    (
      C1 VARCHAR(20) NOT NULL
                     CONSTRAINT PK_T1 PRIMARY KEY ,
      C2 INT
    );
GO

INSERT  INTO dbo.T1
VALUES  ( 'test', 1 ),
        ( 'test2', 2 );
GO

ALTER TABLE dbo.T1 ALTER COLUMN C1 VARCHAR(25) NOT NULL;

Further reading on the ALTER TABLE syntax at https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql

Try to uncheck "Deny saving changes which needed recreation of a table".
In SQL Managenet St. click on bar, choise Service - Parameters, select Diesigners - Table and Database Constructors. Then unscheck "Deny saving changes which needed recreation of a table".


User Does not have permission to alter database, the database does not exist, or the database is not in a state that allows access checks SQL server I have a database on SQL Server. Yesterday It went to Suspect Mode, To recover that from the suspect mode I have followed some steps (Run some commands). But while running those commands it is giving me the error like : "User Does not have permission to alter database, the database does not exist, or the database is not in a state that allows access checks SQL server". To resolve that issue I ran the SSMS with 'run as administrator' privilege, and I also tried to change the permissions for the database for the user, But it is not working. I am not able to delete that also and this is the second time I am facing this issue Now I have 2 databases in suspect mode

.

It seems like there was a problem during the restoration process.
This error has nothing to do with permission. A database in suspect mode cannot be used or altered.
As mentionned, you will have to fix the issue with the database first (before you can run a "USE" or an "Alter database" on it).
If you do have backup for those database, restore the database from your backup in order to fix the issue.
If you don't have backup, then it will be a bit harder and information about how your DBs went into suspect mode would be nice to have in order to see the best approch to bring them back online (potentially with data loss or corruption).
You may have to set the database into the Emergency state before running a checkdb with repair_allow_data_loss on the database (which does exactly as it said)

Confirm that you are using a user that is member in the sysadmin
Fix any issue in the database before executing this SP
Try to restart the server with administration


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

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

Expected Value

 : n/a

Actual Value

 : n/a

 List of errors:

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

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

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

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

The root user can ping host2.

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

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

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

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

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

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

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

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

Also, the list of server hardening is here:

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

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

It looks like the hardening was a little to hard. Oracle does need some access to the server. You can revert back the chmods for ping, Oracle rdbms does not need it. /tmp/ is not required, a usable location for tmp is, mostly for scripting. The rdbms has it's own tricks and storage for sorts.

Never install Oracle as root; use the root.sh at the end of the installation to do the system tweeks required. On some platforms there is a root pre script that should run before installation. The Oracle installation scripts refuse to run using the root account and can be run using any other account.

The access to some server config files has been made a little too tight for the install user to check the prerequisites. If you know they are ok, it's ok. Proving they are ok is a little easier when support issues arise. Check the group ownership of the critical config files and add the Oracle installation user to that group to give it read access.

AFAIK, you must be root to perform the install. More steps are listed here. Note that you need to restart after disabling SElinux.


Backing up MS SQL Server database creates a huge transation log I'm hosting a SQL Server 2012 database server with about 100 databases.  I use the standard maintenance plan to backup all user databases daily and do hourly transaction log backups.

One database, which is about 80 MB, backs up fine at 6 AM but the following transaction log is typically 1-2 GB.  I've checked and the transaction log is getting properly truncated on the full backup but it immediately fills up after the backup.  I've tried switching to simple recovery, shinking the transaction log file, then re-enabling full recovery.  That all works fine and the .log file goes down to 4-5 MB.  However, within a day it's already up to 2 GB - most of which seems to happen when I perform the full backup.  The log has gotten up to 10 GB even though it gets truncated daily.

The database is only accessed through a single web application which doesn't appear to have anything hitting the database during the time where the transaction log balloons out.

I've attempted to query the transaction log itself but can't see anything obvious.

How can backing up an 80 MB database create a 2 GB transaction log? How can I find out what data is actually in the log and determine where it's coming from?

The statement "transaction log is getting properly truncated on the full backup but it immediately fills up after the backup" is not how backups operate though.  All transaction log truncation is done during the first transaction log backup after the full backup, the truncates are deferred until the full backup is completed.

What happens to transaction log backups during full backups?

If you are seeing a bigger transaction log after a full backup but before the first transaction log backup, that's typically by design.  However if you are saying you have almost no activity going on during the full backup which would cause a gig of activity then I would immediately start by looking at your jobs.  Often a reindex will cause a lot of logging but it would be odd for that to be the main culprit.  Carefully monitor what happens during that period and run the same queries.  Feel free to profile what is going on during that time as this could be a compound issue.

Jesse,
I'm not sure whether the backup process created a huge log, anyway in your case we have 2 options.

Take log backups every 1 hr.  Or If you don't have any replication then make the db to simple recovery  mode.


SQL Update Efficiency and Blocking Concern Say I have some SQL similar to the below, which is an update with many joins. The number of rows actually being updated may be 1000's or potentially only a few. The actual join takes a long time as there are millions of rows involved, probably around 30s. Would the below only lock the trade table t on the rows that need to be updated or is it being locked as the joins are underway? So would it be better to use a CTE or temp table to gather this data and then update?

I'm having deadlock issues and wanted to make sure updates like this aren't part of the problem.

update
t
set
t.price = d.latestprice * f.rate
from
trade (nolock) t
inner join tradedetails (nolock) d on d.ID = t.ID
...
...
inner join fxdetails (nolock) f on f.CCY = t.CCY

Table locking is a good thing, it's done so your data doesn't get corrupted.

I wouldn't recommend using NOLOCK when writing data, you should have a look at the answers to Is the NOLOCK (Sql Server hint) bad practice?

An article linked from the question (SQL Server NOLOCK Hint & other poor ideas) has as it's first recommendation:

  Do not use any HINTs until your testing proves that you have an issue
  that can’t be solved any other way than by using a HINT.

As ypercube commented, I would work out why your SELECT query is slow before applying lock hints to you query.

SQL Server will U-lock any row that it needs from the table or view or CTE referenced in the FROM clause. If it needs to scan the table, it U-locks it. If it needs to read 3 rows, it only U-locks them.

This is usually a good thing but you might try the following workaround:

UPDATE t1 SET ...
FROM table t1
JOIN table t2 on t1.ID = t2.ID
JOIN ...

Introduce a redundant join. Do all the filtering on t2 which is not locked. When the set of rows to be updated is determined SQL Server will probably seek into t1 and only lock exactly the rows that need to be updated.

The full discussion of this pattern and associated locking is too long for this answer. It is worth playing around with it.

Actually, you can also use a temp table to store the update information. That has more overhead though and not needed here.


When is it appropriate to use a scalar valued function? I've just read several articles that were critical of scalar functions because of performance issues. These articles usually point to using table functions instead. Under what circumstances would it be appropriate to use a scalar function?

Here is a simple example.  In SQL Server, we have 5 parts of a name (prefix, first, middle, last, suffix) that come from a person table.  In addition to having a column for each name part, we also would like to have a column that concatenates them.  In order to be consistent and to save on some typing, we are considering creating a function to create a "full name" string and putting the results in a separate column.

There are good reasons to be critical of the performance of scalar UDFs. Performance problems include forcing the entire plan to be run serially, excessive memory grants, cardinality estimation problems, and lack of inlining. However, that doesn't mean that they don't have good use cases.

Consider a series of stored procedures which needs to call the same complicated business logic that returns a scalar value. A scalar UDF used to assign the requested value to a local variable can work very well. The performance issues of scalar UDFs are really only felt when they are used inside of a query. Using a Scalar UDF inside a stored procedure will not force the entire stored procedure to run serially.

Some applications might require you to send the exact same SQL code to multiple RDBMS platforms. Scalar UDFs can be a good workaround to get functionality which is implemented in different ways on different platforms. For example, truncating a datetime in both SQL Server and Oracle is fairly difficult to do with the same code unless you use a UDF. In this case you will pay the performance penalties of UDFs, but sometimes they are your only option. For a scenario like this you may not be able to use TVFs because that syntax may not be supported by the other platform.

Other than that it's difficult to think of a good use for scalar UDFs. I suppose you might have a query for which response time is not critical. It might be easier to code the query with a scalar UDF, but you're likely to get better (or at the very least no worse) performance with a TVF instead.

I interpret your question as if you are wondering about the overhead of a function call such as:

select my_concat_function(x,y,z) from t

instead of

select x || y || z from t

Without being an expert on SQL-server, I would guess that the overhead of that function call is negligible.

Function calls are generally considered bad when it comes to predicates. For an sql query like:

select ...
from A
join B
    on f(A.x) = f(B.x)

f can[1] prevent index access on A.x and B.x which will result in a scan of the tables. For a nested loop join this means O(||A|| * ||B||) function calls which can severely decrease performance even if the tables are moderate in size.

[1] A smart optimizer that have enough information may handle the situation fine.


Remote MySQL connection fails (10060) - "Can't connect to MySQL server on domainname.com(10060)" I have developed an application in VB6, and am using an online MySQL database server for storing my application's data.

The first time I try to connect to my database server is successful, but after closing my application and starting it again I get:

  Can't connect to MySQL server(10060)"

After some time (approximately 30 minutes), I can successfully connect to the database again.

I have given maximum values to all of MySQL system and global variables like connection_timeout, wait_timeout etc.
I have granted all permissions to my MySQL user.
The firewall on my computer is turned off.
All connections are properly closed when the application closes.

I have asked the technical people who provide the Online MySQL Database service and they gave me these three reasons:

The local machine from where you are trying to connect is not whitelisted on the server.
The maximum number of connections for the user has been exceeded.
The MySQL server was down at the time (though they said that their server was not down).

How can I solve the first two reasons, or determine that they are the cause of the problem? Or could there be another problem not listed there?

you need to make sure there is:

a user created that has remote access for example user@'x.x.x.x'

the two machines can establish a connection (they can ping each
other for example or access each another by another method) the port
is opened between the two machines

check your configuration file  my.ini/my.cnf for parameters like bind-address or skip-networking. Is there a blocking connection parameter
like:bind-address=a.a.a.a or skip-networking?

the service is up and running when you try to connect from the
other machine

some hosting providers block by default, the remote access to his MySql servers by security reasons. If you need to unblocking the remote access service tell them for unblocking this service.

Otherwise, test your app locally, as localhost and when this works fine, change the hostname, and so on database parameters in your app, and upload to your web hosting server, thats works fine!!!.

Take care, that your Database design checks with your local database design, this is very important!!!

I hope this is helpful to you.


Correct way to perform backup of Cassandra/Scylladb What's the correct(advisable) method to backup a cassandra or scylladb so that we can restore it on development environment with ease?

For Scylla, please look at the following official documentation:

Backup your Data

  Full Backup Snapshots are taken using nodetool snapshot.
  Enabling the incremental backup (disabled by default) will create a hard-link from each SSTable, right after it is flushed, to a backups directory.
  For a complete point in time backup you need: a snapshot plus incremental backups and commit logs since that time of the snapshot. Make sure to clean unnecessary incremental backup, Scylla will not do it for you.

Restore from a Backup and Incremental Backup

  Restoring a keyspace from backup requires all snapshot files of the tables, and incremental backup files taken after the snapshot - if available. Before restoring from backup, you should truncate the table data, making sure existing data will not override the restored one.

See the linked documentation for detailed instructions.

In Cassandra we can take full backup from below command.
nodetool snapshot
A snapshot first flushes all in-memory writes to disk, then makes a hard link of the SSTable files for each keyspace. You must have enough free disk space on the node to accommodate making snapshots of your data files. A single snapshot requires little disk space. However, snapshots can cause your disk usage to grow more quickly over time because a snapshot prevents old obsolete data files from being deleted. After the snapshot is complete, you can move the backup files to another location if needed, or you can leave them in place.

We can also take snapshot for keysapce and table as well. Apart full backup we can enable incremental backup(incremental_backups) on cassandra.yaml also by default it is false.

You can refer below link for more details.
https://docs.datastax.com/en/cassandra/3.0/cassandra/operations/opsBackupTakesSnapshot.html

To restore snpashot is cassandra is very easy.however in cassandra multiple scenario for restoration. To restore a node from snapshot just copy the all sstables from snapshot folder to actual data folder after clearing the data and take restart cassandra services. it will restore the data to previous version of data.you can copy also incremental sstable also to actual data folder if require point in time recovery.

Please refer more details on below link.
https://docs.datastax.com/en/cassandra/3.0/cassandra/operations/opsBackupIncremental.html


Unique over 2 columns I have 2 columns col1 and col2

INITIAL QUESTION:
is it possible to make a constraint to not be able to add the same email two times in this columns, only using indexes?

The Question has been edited to:
"Is it possible to define a constraint so as to be unable to add the same email twice in these columns for the same record, using only indexes?"

But it is my fault, I did not give enough details.
When I have a unique column. I can not repeat a entry in this column.
What I need is to expand this "unique" to a second column, so that any entry made in the first column, should not be possible entered to the second column at all.

I can do it using a external program by consequently adding the entries for this two columns in a array and always checking the array before entering something to this two columns. But the solution a joke, it is sub-optimal, because by extrapolation we do not need databases any more we can use arrays.

Is this possible using just mysql, or do I need "my array" ?

I guess you can use CHECK constraint

ALTER TABLE foo
  ADD CONSTRAINT email1_is_not_email2_CHK
    CHECK (email1 <> email2) ;

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(email1,email2);


Trying to restore SQL file from mysqldump, results in syntax error I ran the following sqldump call in a MySQL 5.7 instance (Windows OS):

mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=1 --routines -A > ~/dump.sql

I copied the result in a MySQL 5.7 instance (Linux OS), and executed it via:

mysql -uroot -pMyPwd < dump.sql

But I got the following error:

  ERROR 1064 (42000) at line 813: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '917,0.31000000,267.21000000,'2015-07-08 17:44:39',88758,'BK1','BK1',49208,4921' at line 1

There are some characters in the error message that I haven't been able to copy, so I paste an image here:

So I open the (big) file in nano, and I find the fragment which has this weird characters:

...(49171,0.87000000,267.28000000,'2015-07-08 17:42:48',88671,'BK1','BK1',49167,49170,63,64,'BK11432145524','BK11432145533','xxxxxx',0,0.00000000,0.00000000,0.00000000,0.00000000),(^T9^R17,0.31000000,267.21000000,'2015-07-08 17:44:39',88758,'BK1','BK1',49208,49216,63,64,'BK11432145637','BK11432145661','xxxxxx',0,0.00000000,0.00^P00000,0.0000000^P,0.00000000),(4^Y^R^W6,0.19000000,267.14000000,'2015-07-08 17:47:02',88875,'BK1','BK1'...

As you can see, it seems the first value of a row seems to have a weird element ^T9^R17 instead of a normal integer like the row before (49171).

Tried to run dos2unix on the file as recommended by @Verace, and this is the result:

$ dos2unix dump.sql
dos2unix: Binary symbol 0x14 found at line 813
dos2unix: Skipping binary file dump.sql

Why is this happening? The type of the first column is bigint(20) NOT NULL.

I got 2 comments that hinted me that:

This problem might not be reproducible again if I do a second mysqldump.
The copy of the file from one place to another might have corrupted the file.

So then to ensure (1) was not the case, I tried to mysqldump again, and to make sure (2) didn't happen, I compiled the file before transferring it to another computer (so that if it gets corrupted, at least I would know when trying to decompress it).

The result was: success. It works now. So I suspect it was a glitch in the matrix.
Thanks for all the people that helped.


Running Total of a count per day in SQLite I usually write a couple of very simple queries on a redshift database which used in a marketing visualization tool.  And I repeat - VERY simple - I am a novice at this.

I am now faced with a new data source (SQLite) and I'm stuck.

I have a table with :

order_id
order_date

where there are several orders per day.  And I am trying to get a running total of the daily count of order ids.

For example the table would contain :

order_id      order_date
2541          2017-06-05
26777         2017-06-05
123           2017-06-06
8795          2017-06-07

And I'm trying to get to this result :

Day          RunningTotal
2017-06-05 : 2
2017-06-06 : 3  (the 2 of the previous day + 1 this day)
2017-06-07 : 4

In POSTGRESQL i would use

SELECT
order_date,
SUM (COUNT(order_id)) OVER (ORDER BY order_date rows between unbounded preceding and current row) as RunningTotal
FROM table
ORDER BY
order_date
GROUP BY
order_date

How do I do this in SQLite?

I've googled - and see many examples of either SUM or COUNT but seldom combined and never to achieve a running total of a count.

For anyone trying something similar : this is what worked in the end :

select
a.ORDER_DATE,
sum(b.ID_COUNT) RunningTotal
from
(select
ORDER_DATE, Count(DISTINCT(SOFTWARE_ID)) ID_COUNT
from orders
group by ORDER_DATE
order by ORDER_DATE) a,
(select
ORDER_DATE, Count(DISTINCT(SOFTWARE_ID)) ID_COUNT
from orders
group by ORDER_DATE
order by ORDER_DATE) b
where a.ORDER_DATE >= b.ORDER_DATE
group by a.ORDER_DATE
order by a.ORDER_DATE;

you can simply do like this

select order_date, count(order_date) from orders
group by order_date


Term for non transaction save actions If you only look at the database everything is fine. You have transactions and if somethings goes wrong everything gets rolled back. That's nice - I like this.

BUT: I want to send mails. Now I am in trouble because I can't rollback.

example:

transaction starts
Mail gets send
Other stuff gets done (inside DB)
Something goes wrong.
Rollback.

How to solve this is a different question, not this.

This question how to call this in general. In this example is about sending mails. But the same problem as soon as you do modify something in systems which are outside the transaction boundary.

Is there a name for this problem?

Roughly the same problem arises if you want to import files from a directory. If you delete the file inside the transaction, then the transaction might fail and the file was deleted but never imported. Or you delete the file after the transaction. Then the delete of the file might fail and the file gets imported a second time.

I don't want to reinvent a solution for this. That's why I need the matching term for this problem. Then I can read some papers and learn what's "state of the art" in the year 2018.

Oracle PL/SQL keyword AUTONOMOUS_TRANSACTION will cause a procedure to create another session, do a transaction, commit/rollback just that private transaction, and return flow control back to the parent.

Oh..never send email on uncommitted data.

EDIT: (due to edit of original post)

  Roughly the same problem arises if you want to import files from a
  directory. If you delete the file inside the transaction, then the
  transaction might fail and the file was deleted but never imported. Or
  you delete the file after the transaction. Then the delete of the file
  might fail and the file gets imported a second time.

This type of problem is called a bug.

The solution is:

Define each step as its own TRANSACTION

You'll want to create them in a way that you can rerun (or skip) the step(s) as needed

Run each step in the appropriate order.

do not send email prior to COMMIT.
do not delete a file prior to successful loading of the data

You'll need to keep track of "where you are at" and if that step has passed/failed.

EMAIL example

You should have a procedure to sendEmail that should be called after commit.

If you want to call the procedure prior to commit, you'll need to add a row to a queue that will rollback with the main transaction.  For Oracle, this will be either Advance Queuing or the package APEX_MAIL

By putting it in a separate procedure, you can sendEmail a 2nd time upon [end-user's] request.

Process File

You have an algorithm that contains a few steps where each step can fail.  this is actually different than your sendEmail problem.

You need to record what you are processing, where you are at within your algorithm, and if that step have succeeded or failed.

In order to recover from an error at any step, each step of the process needs to be defined as a discrete TRANSACTION.

In Oracle, I would have these procedures (1 procedure per TRANSACTION):

create or replace
package file_processing_package
as
  procedure update_file_processing_status(
                                p_id       IN files_to_process.id%TYPE
                              , p_status   IN process_states.id%TYPE);

  function add_a_file_to_be_processed( p_filename IN files_to_process.file_name%TYPE )
                               return files_to_process.id%TYPE;

  procedure load_data_from_file( p_id in files_to_process.id%TYPE );

  procedure process_already_loaded_data( p_id in files_to_process.id%TYPE );

  procedure delete_file_from_os( p_id in files_to_process.id%TYPE );
end;
/

This is based on the following tables:

CREATE TABLE PROCESS_STATES (
  id   int generate by default on null as identity, -- 12c+
  state_desc  varchar2(25) not null,
  constraint process_states_pk primary key (id),
  constraint process_states_uq1 unique (state_desc)
);

insert into process_states( state_desc ) values ( 'file to be processed' );
insert into process_states( state_desc ) values ( 'file loaded' );
insert into process_states( state_desc ) values ( 'processing' );
insert into process_states( state_desc ) values ( 'processing failed' );
insert into process_states( state_desc ) values ( 'processing succeeded' );
insert into process_states( state_desc ) values ( 'delete failed' );
insert into process_states( state_desc ) values ( 'OK' ); -- delete succeeded
commit;

CREATE TABLE FILES_TO_PROCESS (
  id               int generate by default on null as identity, -- 12c+
  file_name        varchar2(50) not null,
  process_state_id int not null,
  constraint file_to_process_pk  primary key (id),
  constraint file_to_process_uq1 unique (file_name),
  constraint file_to_process_fk1 foreign key (process_state_id)
                            references (process_states.id)
);

The UNIQUE constraint on FILE_NAME prevents the same file being processed twice.

This is an issue that wasn't accounted for in the "Requirements Engineering" phase of the project. It shouldn't be looked at as an issue of the database system, because the database is performing as it should do. The mail is sent because it is not (yet) part of the correct business logic.

It is called a Business Logic Flaw or possibly even a Business Logic Issue.

Business Logic

  In computer software, business logic or domain logic is the part of the program that encodes the real-world business rules that determine how data can be created, stored, and changed. It is contrasted with the remainder of the software that might be concerned with lower-level details of managing a database or displaying the user interface, system infrastructure, or generally connecting various parts of the program.

Reference:  Business Logic (Wikipedia.org)

Business Logic Flaws / Issues

  Web applications have become the core mechanism for business processes over the Internet. As more and more businesses are migrating to the Internet model, it has led to various information security issues and vulnerabilities. SQL Injection, Cross Site Scripting, Remote Code Execution to name a few. However apart from the conventional vulnerabilities, there are many forms of business logic vulnerabilities commonly exploited by attackers. These vulnerabilities are routinely overlooked during QA because the process is intended to test what a piece of code is supposed to do and not what it can be made to do. The other problem(s) with business logic flaws is scanners can’t identify them, IDS can’t detect them, and Web application firewalls can’t defend them.

  Thus, business logic vulnerabilities are ways of using the legitimate processing flow of an application in a way that results in a negative consequence to the organization. Automating business processes such as customer purchase orders, banking queries, wire transfers or online auctions, for example, requires entities to have access to extremely sensitive information.  Improper implementation of these can lead to the business logic flaws.

Reference: Business Logic Vulnerabilities And Some Common Scenarios Of Business Logic Flaws (Cyber Security Community)

  "Business logic is the intended behavior of the application," explained Dan Kuykendall, co-CEO and CTO of NT OBJECTives. "It’s the functionality that governs the core of what the application does, for example, which users are allowed to see what, how much users are charged for various items, etc. Business logic attacks are things you can do to exploit the logic and cheat the application…(they) are hard to test for because they require both an understanding of the application and of security. In many cases, QA teams know the business logic, but they aren’t security experts and haven’t been trained on the clever attack techniques."

Reference: Common Business Logic Flaws Compromising Application Security (SecurityWeek.com)


Oracle PLSQL code recovery I cannot connect to my database as admin, even using connect descriptor neither via RMAN. I am getting TNS-12514 error.
I consider to install new instance.
Questions:
1) Can I restore/recover PLSQL code from "dead" oracle database?
2) Where is the PLSQL stored?

EDIT: My situation is described here: ORA-01033 "ORACLE initialization or shutdown in progress" caused by ORA-10567 "REDO is inconsistent with data block"

You need to restore/recover your database. If you can't then yes the code is buried in the system table spaces. But it won't be easy looking through the data file with a hex editor trying to find the code. You should either try to restore the database or just rewrite the code. Do you have a backup of your database? Do you have all of the data files, control files, online redo files? If you don't have a backup and your database is in an inconsistent state, then you can probably alter database recover until cancel and then point Oracle to your online redo logs to see if it can find what it needs.

Otherwise you might be out of luck. What is in your alert log for your database?


ODBC Connections locking tables in SQL Server We have an ERP system that is a little less than modern, the backend is currently sitting on a SQL Server 2008 R2 installation. We have through mandate of management (isn't that always the case) the completely impractical practice of allowing many users to extract data from this system at will for the purposes of manipulating the data into their preferred flavor of reporting (BI, Powerquery, Access, Excel, etc)

Though all of these report generating users only have select rights, there are several other applications that have the ability to actually manipulate data for the purposes of doing things like EDI, automated data entry.

Logically this creates a number of race conditions on the tables. First come first serve, or worse still, still serving, please wait, or just NO... I believe it is further causing what has been years of previously unexplained data corruption requiring vendor assistance to correct. Because not all of the applications are properly coded to anticipate the chance that something else may have interrupted its own private intent.

So the task is to prove/correct/mitigate it where possible on a system I only recently inherited.

My first instinct tells me it cannot be done, without some sort of IPC/error/transaction control between different vendor's products and their will to fix it, control over source code, or ability to restrict it, that this CANNOT be fixed from a DBA perspective, it is simply bad practices leading to what should be anticipated as potentially unwanted side effects.

So all I can do at this point is prove it, and see that if with proof, I can solicit some sort of behavioral change in the hearts of the management that insists it has to be this way.

First step, is that I am running a continuous profiler output to disk, albeit resource intensive, I have to be able to go back and look at what has been submitted, by whom, and what impact it had. I am also using perfmon logging and PAL to try and cross reference intensive behavior on the server, "when the processor and memory are pegged, what was the SQL server doing at that instance (most likely servicing one of the GUI written, poorly performing, ten table joining, non indexed queries, that some report writer/tool tossed together"

One of those situations just occurred, we had a user in the ERP system that could not perform a function, we identified the user logged into the database that was not using the ERP software (Was using ODBC, SQL user with select only access, and MS Access), had them disconnect and the function went through. Management refuses to believe they are related because they "Should have been dealing with different tables, and the user only had select access" and I have no profile history of the instant just before it happened.

So all of that boils down to a question to the DBA fellows out there, given the task of PROVING that application A is being adversely affected by application B, what suggestions would you have to offer?

Unfortunately, we've dealt with too may issues with this where I work. In cases where this has been a problem, people who don't know databases, literally open a SQL table "live" in MS Access. At first things can appear transient because they may open and close Access quickly. But then there are those moments where they leave MS Access open on the live SQL table while they go away for lunch or perhaps through the long weekend--blocking applications that try to use the database. Just to state the obvious--blocking can adversely affect any application trying to use the blocked SQL row, table, or database.

All we really had to do to prove anything was capture data from sp_who2 to find the blocking chains.

That being said, when I'm away from the console, I still use the following code I wrote over a decade ago (I mean I'm still using DBCC InputBuffer) to find these pesky issues -- if needed (please run in a test/dev environment to see if it meets your needs). Though certainly not the Rolls Royce of routines it still works for me.

This code essentially creates a table, BlockingProcesses, in a new database DBADMIN to store the blocking information. Two stored procedures sp__Maint_BlockWatch and sp__Maint_Blockingprocesses work together to find the blockers. And a SQL job runs once a minute to check for blocks. You may need to change minor things in the code below like the user who needs to execute the job, possible scheduling, etc.

CREATE DATABASE DBADMIN
GO

USE [dbadmin]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[BlockingProcesses]
(
    [PK] [int] IDENTITY(1,1) NOT NULL,
    [last_batch] [datetime] NULL,
    [spid] [int] NULL,
    [BlockedTotal] [int] NULL,
    [LoginName] [varchar](128) NULL,
    [DBName] [varchar](128) NULL,
    [HostName] [varchar](128) NULL,
    [Program_Name] [varchar](255) NULL,
    [CPU] [bigint] NULL,
    [Physical_IO] [bigint] NULL,
    [Memusage] [bigint] NULL,
    [Open_tran] [int] NULL,
    [EventInfo] [varchar](max) NULL,
    [InsertTime] [smalldatetime] NULL,
    [Params] [varchar](500) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[BlockingProcesses]
   ADD DEFAULT (getdate()) FOR [InsertTime]
GO

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[sp__Maint_BlockWatch]
AS
    declare @blocker smallint

    DECLARE blocker_cursor CURSOR FOR
         select distinct blocked
         from sysprocesses
         where blocked != 0

    OPEN blocker_cursor
    FETCH NEXT FROM blocker_cursor INTO @blocker

    WHILE (@@fetch_status <> -1)
    BEGIN
      IF (@@fetch_status = -2)
      BEGIN
  FETCH NEXT FROM blocker_cursor INTO @blocker
           CONTINUE
      END

  exec sp__Maint_Blockingprocesses @blocker

 FETCH NEXT FROM blocker_cursor INTO @blocker
    END
DEALLOCATE blocker_cursor

GO

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[sp__Maint_BlockingProcesses]    Script Date: 09/20/2016 13:31:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[sp__Maint_BlockingProcesses]
@spid smallint
as
declare
@blockedTotal int,
@loginame varchar(128),
@dbid int,
@hostname varchar(128),
@program_name varchar(128),
@cpu bigint,
@physical_io bigint,
@memusage bigint,
@open_tran int,
@dbname varchar(128),
@EventInfo varchar(255),
@Last_batch datetime
create table #temp
(
 EventType varchar(50),
 Parameters int,
 EventInfo varchar(255)
)

insert into #temp
exec ('dbcc inputbuffer('+@spid+')')

select @EventInfo=eventinfo from #temp
select @blockedTotal= count(*) from sysprocesses where blocked = @spid
select @loginame=loginame, @dbid=dbid, @hostname=hostname, @program_name=program_name, @cpu=cpu,
@physical_io = physical_io, @memusage=[memusage], @open_tran=open_tran, @Last_batch=last_batch
from sysprocesses where spid=@spid
select @dbname = name from sysdatabases where dbid=@dbid
insert into dbadmin..blockingprocesses(last_batch,spid, BlockedTotal, LoginName, DBName, HostName, Program_Name, CPU, Physical_IO, Memusage, Open_tran, EventInfo)
values(@last_batch, @spid, @BlockedTotal, @LogiName, @DBName, @HostName, @Program_Name, @CPU, @Physical_IO, @Memusage, @Open_tran, @EventInfo)
drop table #temp

GO

USE [msdb]
GO

/****** Object:  Job [_Monitor Blocks]    Script Date: 09/20/2016 13:29:34 ******/
BEGIN TRANSACTION

DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 09/20/2016 13:29:34 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'_Monitor Blocks',
        @enabled=1,
        @notify_level_eventlog=2,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'No description available.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Look For Blocking]    Script Date: 09/20/2016 13:29:34 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Look For Blocking',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=1,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'exec sp__Maint_BlockWatch',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Run Once a Minute',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=4,
        @freq_subday_interval=1,
        @freq_relative_interval=0,
        @freq_recurrence_factor=0,
        @active_start_date=20060918,
        @active_end_date=99991231,
        @active_start_time=0,
        @active_end_time=235959
        --, @schedule_uid=N'bf518f11-c5ba-438a-8afd-e4e33e8dad1e'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

I have same environmental vulnerability, albeit at smaller scale:  Many users connect with their preferred front-end such as MS Access via ODBC, inadvertently locking SQL tables and views even when user has SELECT ONLY.

Happily much avoidance of blocks seems feasible following a mandate that these users also install SQL Server 2012 SSMS.  The install package included "SQL Server Native Client 11.0".  ODBC Connections that use this driver have a new "Application Intent" option.  Choose 'READONLY' instead of default 'READWRITE'.
Voo doo?


Locking issue with concurrent DELETE / INSERT in PostgreSQL This is pretty simple, but I'm baffled by what PG does (v9.0).
We start with a simple table:

CREATE TABLE test (id INT PRIMARY KEY);

and a few rows:

INSERT INTO TEST VALUES (1);
INSERT INTO TEST VALUES (2);

Using my favorite JDBC query tool (ExecuteQuery), I connect two session windows to the db where this table lives. Both of them are transactional (ie, auto-commit=false). Let's call them S1 and S2.

The same bit of code for each:

1:DELETE FROM test WHERE id=1;
2:INSERT INTO test VALUES (1);
3:COMMIT;

Now, run this in slow motion, executing one at a time in the windows.

S1-1 runs (1 row deleted)
S2-1 runs (but is blocked since S1 has a write lock)
S1-2 runs (1 row inserted)
S1-3 runs, releasing the write lock
S2-1 runs, now that it can get the lock. But reports 0 rows deleted. HUH???
S2-2 runs, reports a unique key constraint violation

Now, this works fine in SQLServer. When S2 does the delete, it reports 1 row deleted. And then S2's insert works fine.

I suspect that PostgreSQL is locking the index in the table where that row exists, whereas SQLServer locks the actual key value.

Am I right? Can this be made to work?

Mat and Erwin are both right, and I'm only adding another answer to further expand on what they said in a way which won't fit in a comment. Since their answers don't seem to satisfy everyone, and there was a suggestion that PostgreSQL developers should be consulted, and I am one, I will elaborate.

The important point here is that under the SQL standard, within a transaction running at the READ COMMITTED transaction isolation level, the restriction is that the work of uncommitted transactions must not be visible. When the work of committed transactions becomes visible is implementation-dependent. What you are pointing out is a difference in how two products have chosen to implement that. Neither implementation is violating the requirements of the standard.

Here's what happens within PostgreSQL, in detail:

S1-1 runs (1 row deleted)

The old row is left in place, because S1 might still roll back, but S1 now holds a lock on the row so that any other session attempting to modify the row will wait to see whether S1 commits or rolls back. Any reads of the table can still see the old row, unless they attempt to lock it with SELECT FOR UPDATE or SELECT FOR SHARE.

S2-1 runs (but is blocked since S1 has a write lock)

S2 now has to wait to see the outcome of S1. If S1 were to roll back rather than commit, S2 would delete the row. Note that if S1 inserted a new version before rolling back, the new version would never have been there from the perspective of any other transaction, nor would the old version have been deleted from the perspective of any other transaction.

S1-2 runs (1 row inserted)

This row is independent of the old one. If there had been an update of the row with id = 1, the old and new versions would be related, and S2 could delete the updated version of the row when it became unblocked. That a new row happens to have the same values as some row that existed in the past doesn't make it the same as an updated version of that row.

S1-3 runs, releasing the write lock

So S1's changes are persisted. One row is gone. One row has been added.

S2-1 runs, now that it can get the lock. But reports 0 rows deleted. HUH???

What happens internally, is that there is a pointer from one version of a row to the next version of that same row if it is updated. If the row is deleted, there is no next version. When a READ COMMITTED transaction awakens from a block on a write conflict, it follows that update chain to the end; if the row has not been deleted and if it still meets the selection criteria of the query it will be processed. This row has been deleted, so S2's query moves on.

S2 may or may not get to the new row during its scan of the table. If it does, it will see that the new row was created after S2's DELETE statement started, and so is not part of the set of rows visible to it.

If PostgreSQL were to restart S2's entire DELETE statement from the beginning with a new snapshot, it would behave the same as SQL Server. The PostgreSQL community has not chosen to do that for performance reasons. In this simple case you would never notice the difference in performance, but if you were ten million rows into a DELETE when you got blocked, you certainly would. There's trade-off here where PostgreSQL has chosen performance, since the faster version still complies with the requirements of the standard.

S2-2 runs, reports a unique key constraint violation

Of course, the row already exists. This is the least surprising part of the picture.

While there is some surprising behavior here, everything is in conformance with the SQL standard and within bounds of what is "implementation-specific" according to the standard. It certainly can be surprising if you are assuming that some other implementation's behavior will be present in all implementations, but PostgreSQL tries very hard to avoid serialization failures in the READ COMMITTED isolation level, and allows some behaviors that differ from other products in order to achieve that.

Now, personally I'm not a big fan of the READ COMMITTED transaction isolation level in any product's implementation. They all allow race conditions to create surprising behaviors from a transactional point of view. Once someone becomes accustomed to the weird behaviors allowed by one product, they tend to consider that "normal" and the trade-offs chosen by another product odd. But every product has to make some sort of trade-off for any mode not actually implemented as SERIALIZABLE. Where PostgreSQL developers have chosen to draw the line in READ COMMITTED is to minimizing blocking (reads don't block writes and writes don't block reads) and to minimize the chance of serialization failures.

The standard requires that SERIALIZABLE transactions be the default, but most products don't do that because it causes a performance hit over the more lax transaction isolation levels. Some products don't even provide truly serializable transactions when SERIALIZABLE is chosen -- most notably Oracle and versions of PostgreSQL prior to 9.1. But using truly SERIALIZABLE transactions is the only way to avoid surprising effects from race conditions, and SERIALIZABLE transactions always must either block to avoid the race conditions or roll back some transactions to avoid a developing race condition. The most common implementation of SERIALIZABLE transactions is Strict Two-Phase Locking (S2PL) which has both blocking and serialization failures (in the form of deadlocks).

Full disclosure: I worked with Dan Ports of MIT to add truly serializable transactions to PostgreSQL version 9.1 using a new technique called Serializable Snapshot Isolation.

We also faced this issue. Our solution is adding  select ... for update before delete from ... where. The isolation level must be Read Committed.


Don't use a transaction for Stored Procedure I have a stored procedure that runs a few commands. I don't want these commands to be wrapped in the transaction of the stored procedure. If the 4th command fails, I want the 1st, 2nd and 3rd ones to stay and not rollback.

Is it possible to write the stored procedure in such a way that it doesn't all execute as one big transaction?

I think there may be some confusion here about a batch vs a transaction.

A transaction is a statement or set of statements that will either succeed or fail as a unit.  All DDL statements are in transactions themselves (i.e. if you update 100 rows but row 98 throws an error, none of the rows are updated).  You can wrap a series of statements in a transaction as well using BEGIN TRANSACTION and then either COMMIT or ROLLBACK.

A batch is a series of statements that are executed together.  A stored procedure is an example of a batch.  In a stored procedure, if one statement fails and there is error trapping (normally TRY/CATCH blocks) then the subsequent statements will not execute.

I suspect your issue is the batch is getting cancelled when an error occurs because either the stored proc itself or an outer scope (like the application or stored proc that calls this procedure) has error trapping in it.  If that is the case this is trickier to resolve since you need to adjust how you handle errors at whatever scope is trapping them.

separate each of the parts with a BEGIN TRAN and to a check if the transaction was successful. if it was commit it, otherwise do a rollback, since they are all executing from the same level you will be able to commit each section separately without having to rollback all if one fails.

For more info you can take a look at: http://msdn.microsoft.com/en-us/library/ms188929.aspx


Copy MySQL User but change HostName I am migrating to a docker MariaDB setup and all my users were created w/ "Localhost". From what I read and tested ironically '%' excludes localhost. I want to copy my existing users adding a second entry utilizing '%' as the hostname. Any suggestions?

You have to call SHOW CREATE USER 'username'@'localhost' and SHOW GRANTS FOR 'username'@'localhost' for all the users fetched by

SELECT user, host
  FROM mysql.user
 WHERE host = 'localhost'

and to replace all 'localhost' entries by '%' in the returned statements.


MongoDB Replica Set Failure Some background

I had an issue with my replica set the last 24 hours, where the members could not connect to each other, and I got errors such as; could not connect to .... the machine actively refused it; read error; connect error etc.

Things I did to try and resolve it:

I rebooted my PC
I restarted the servers a bunch of times

But nothing seemed to work. I could connect to each member via the shell, and each member status was SECONDARY.

Why I did this

On one of my members a kept getting a message that there was a problem or failure with my shutdown, and that there was some issue with the mongod.lock file (I can't remember the exact message...).

My resolution

Finally, I deleted the mongod.lock file in each member directory, boot up the members, and a connection was established and a vote took place.

My Question

Was this a wise move? What would be the preferred method?

Thank you for your input and help!

As per my original question, my solution was as follows:

My resolution

I deleted the mongod.lock file in each member directory, boot up the members, and a connection was established and a vote took place.

Thank you to Dmytro Shevchenko for assisting with clarification via his link, explaining that deleting the mongod.lock files are OK.

please create keyFile and place in each server , put the keyFile in config file, you can create keyFile using below command

openssl rand -base64 741 > mongodb-keyfile
chmod 600 mongodb-keyfile

you can put the keyFile in config file using below option and start the replication

keyFile = /path/to/keyFile

AND One more thing is if your system belongs to any domain, it should be in that vpn itself to communicate, you can try ping those server whether they can send packets or not.


Creating 7 digit codes in database for every new insertion I am aware that using identity column we can auto-increment the keys from 0, one-by-one. What I want to do is:

Generate codes in SQL server 2014 which will be of 9 digits (operational codes).

First 2 digits are going be pre-defined and remaining 7 will increment by 1 for every new insertion of record.  For example:

51 (predefined ) and remaining 7 are 0000001  and so insertions will happen as:

510000001
510000002
510000003
510000004
510000005
510000006

and so on.

If it always needs to start with 51 then you can use a sequence (or identity) that starts at 510,000,001.

CREATE TABLE #Test (
    ID BIGINT IDENTITY(510000001, 1),
    SomeColumn VARCHAR(10)
);

INSERT  INTO #Test (SomeColumn)
VALUES ('Test'), ('Test2');

SELECT * FROM #Test AS t;

Just keep in mind that once the sequence reaches 519,999,999, it will not restart at 510,000,001 (particularly if you use the identity property) but will continue at 520,000,000.

Also, since you are on SQL Server 2014, please consider the fact that starting from the 2008 version IDENTITY values are cached and can have gaps in them after a restart. If you want to have the values follow exactly (i.e. no gaps) you should either use a SEQUENCE starting at 510000001 or apply Trace Flag 272.
This seems to be documented in this connect item

I guess you can try something like below. This is combination of identity column primary key with the computed column. Hope this helps.

CREATE TABLE dbo.TestTable (
    Id INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , CombID AS LEFT('51000000'+ LTRIM(CAST(Id AS VARCHAR(9))), 9)
    , SomeColumn VARCHAR(16)
)


12c Grid Agent installation issues - Shell path is incorrect or not defined I am installing 12c Grid Control agent on my server via Enterprise manager.

The targer server is a UNIX server with AIX OS on it. I add target host manually, enter authentication information, host name, installation directory, etc. and all goes well untill I click "Deploy Agent" button, then I get the following error:

"Shell path is incorrect or not defined.:/bin/bash(SH_PATH),-c(SH_ARGS) on host MyHostName"

and details are :

"Check the property values in the following files in this order, ssPaths_.properties or sPaths.properties or Paths.properties, in "/u02/app/oracle/middleware/oms/oui/prov/resources"

There isn't /u02/app" directory on my target server, only /u01/app/oracle" so I assume the above directory is on the same server as Enterprise manager.

Although I did find a directory where I have files of the format  ssPaths_.properties  - /u01/app/oracle/product/11.2.0.3/oui/prov/resources

Since my platform ia AIX, I used file   ssPaths_aix.properties

On my target server if I run which bash I get /usr/bin/bash and which ssh returns /usr/bin/ssh ahd which sh returns /usr/bin/sh

So I edited the ssPaths_aix.properties file so that SH_PATH=/usr/bin/sh  but still getting the same error. I have also tried setting to SH_PATH=/usr/bin/bash but nothing seems to make any difference. I am thinking one possibility would be I am editing wrong file.

Can anyone help?

try the following doc
EM 12c Agent Push Fails With Shell Path is Incorrect or Not Defined : /bin/bash(SH_PATH),-c(SH_ARGS) (Doc ID 1489437.1)


Is it possible to make one column readonly? I'm curious if it is possible to create a table with a column that can never be changed, but the other columns of the table can.

For instance I could imagine a CreatedByUser column that should never be changed.

Is there a built-in functionality in SQL Server for this, or is it only possible via triggers or something else?

There is no built in declarative support for non updatable columns (except for specific predefined cases such as IDENTITY)

This Connect item requested it but was rejected. Add DRI to enforce immutable column values

An UPDATE trigger would probably be the most robust way of achieving this. It could check IF UPDATE(CreatedByUser) and raise an error and rollback the transaction if true.

Why are you updating the createdby column?

I would have two columns a [created_by] and a [modified_by] column, where the first insert would insert all the respective columns in the record, and any subsequent updates would just update the [modified_by] column ( via a trigger on in the application layer you can structure your update to only change the [modified_by] , along with your respective columns )