How to check if there are no time gaps in PostgreSQL? Schema:

CREATE TABLE "expenses_commissionrule" (
    "id" serial NOT NULL PRIMARY KEY,
    "country" varchar(2) NOT NULL,
    "created" timestamp with time zone NOT NULL,
    "modified" timestamp with time zone NOT NULL,
    "activity" tsrange NOT NULL
);

Description:

I'd like to create an application to manage commission calculations. Every rule has activity period. Each country has independent set of rules.

Constraints:

First. To avoid ambiguity these activity periods should not overlap. I did it with the following constraint:

ALTER TABLE expenses_commissionrule
ADD CONSTRAINT non_overlapping_activity
EXCLUDE USING GIST (country WITH =, activity WITH &&);

Second. In any point of time there should be only one commission rule, so, there should be no gaps between intervals in the table. In other words sum of all intervals should be -INF:+INF.

Question:
How can I add the second constraint?
Use case:
We have a rule with infinite period. I want to switch to a new rule, which should start from the next month. In this case I'd like to set current rule end period to the end of the current month and add a new rule in a single action.

Update:
In the future I'd like to add the following behaviour:

An ability to specify a rule only for specific user (via nullable foreign key "user_id");
Every user could have his own rule for each country. If user has no rule - "global" rules will be used for commission calculations, so it is like a fallback;
These "user" rules could have any period of activity - (-inf: inf) and concrete intervals as well. It is different from "global" rules - gaps are OK here.
As "may be" feature - extra varchar field, which will have a type of situation, when particular rule could be applied to have more flexibility with the calculation process. This "partial" rules might have different intervals as well, gaps are OK.

In other words all previous constraints should be applied only to rows where user_id IS NULL. How can this be accomplished?

PostgreSQL version: 9.6.2

Chapter 1: Linked List

One way to have this (no gaps) type of constraint enforced in the database is to split activity into the starting and ending parts and then use UNIQUE and FOREIGN KEY constraints to emulate a linked list.

1a.

Every activity_start should reference the previous activity_end:
(country, activity_start) REFERENCES (country, activity_end).
Two periods cannot have the same activity_start and country or activity end and country:
UNIQUE constraint on (country, activity_start).
and UNIQUE (country, activity_end).
We actually don't need both of them, only the second one, for the foreign key to be defined. The exclusion constraint doesn't allow two periods to have the same start or end.
We should not allow multiple rows with (-Infinity, +Infinity) or series like: -Infinity -> DateA -> Infinity -> DateB -> +Infinity. This is achieved with the two partial indexes.

Code:

CREATE TABLE expenses_commissionrule (
    id serial NOT NULL PRIMARY KEY,
    country varchar(2) NOT NULL,
    created timestamp with time zone NOT NULL,
    modified timestamp with time zone NOT NULL,
    activity tsrange NOT NULL,
    activity_start timestamp,
    activity_end timestamp,
    CONSTRAINT non_overlapping_activity
        EXCLUDE USING GIST (country WITH =, activity WITH &&),
    CONSTRAINT country_activity_end_uq
        UNIQUE (country, activity_end),
    CONSTRAINT activity_start_end_fk
        FOREIGN KEY (country, activity_start)
        REFERENCES expenses_commissionrule (country, activity_end),
    CONSTRAINT activity_ck
        CHECK (activity IS NOT DISTINCT FROM
               tsrange(activity_start, activity_end, '[)') )
) ;

CREATE UNIQUE INDEX country_start_ufx
    ON expenses_commissionrule
        (country)
    WHERE (activity_start IS NULL) ;

CREATE UNIQUE INDEX country_end_ufx
    ON expenses_commissionrule
        (country)
    WHERE (activity_end IS NULL) ;

We can then try to insert (valid) data:

WITH ins
    (country, activity_start, activity_end)
  AS
    ( VALUES
          ('IT',  null::timestamp,  null::timestamp),

          ('FR',  null,             '2000-01-01'),
          ('FR',  '2000-01-01',     null),

          ('GR',  null,             '2000-01-01'),
          ('GR',  '2000-01-01',     '2012-01-01'),
          ('GR',  '2012-01-01',     '2017-06-01'),
          ('GR',  '2017-06-01',     null)
    )
INSERT INTO expenses_commissionrule
    (country, created, modified, activity, activity_start, activity_end)
SELECT
    country, now(), now(),
    tsrange(activity_start, activity_end, '[)'),
    activity_start, activity_end
FROM ins ;

Works fine:

> INSERT 0 7

And try with invalid data:

--
( VALUES
      ('US',  null::timestamp,  '2000-01-01'::timestamp)
)
--

-- Fails:

> ERROR:  insert or update on table "expenses_commissionrule" violates
      foreign key constraint "activity_start_end_fk"
> DETAIL:  Key (country, activity_end)=(US, 2000-01-01 00:00:00) is not
      present in table "expenses_commissionrule".

Another try:

( VALUES
      ('UK',  null::timestamp,  '2000-01-01'::timestamp),
      ('UK',  '2000-01-01',     '2000-01-01')
)

-- Fails:

> ERROR:  duplicate key value violates unique constraint
      "country_activity_end_uq"
> DETAIL:  Key (country, activity_end)=(UK, 2000-01-01 00:00:00)
      already exists.

1b.

After all these, we can spot that activity is not really needed in the table as we have start and end and we can compute it. Thus it can be removed:

CREATE TABLE expenses_commissionrule (
    id serial NOT NULL PRIMARY KEY,
    country varchar(2) NOT NULL,
    created timestamp with time zone NOT NULL,
    modified timestamp with time zone NOT NULL,
    activity_start timestamp,
    activity_end timestamp,
    CONSTRAINT non_overlapping_activity
        EXCLUDE USING GIST
            (country WITH =,
             tsrange(activity_start, activity_end, '[)') WITH &&),
    CONSTRAINT country_activity_end_uq
        UNIQUE (country, activity_end),
    CONSTRAINT activity_start_end_fk
        FOREIGN KEY (country, activity_start)
        REFERENCES expenses_commissionrule (country, activity_end)
) ;

-- plus the two filtered indexes. We do need those.

1c.

And then we realize that - due to the foreign key we added - we don't really need the exclusion constraint any more. We can have the same effect by enforcing that activity_end is after activity_start.

CREATE TABLE expenses_commissionrule (
    id serial NOT NULL PRIMARY KEY,
    country varchar(2) NOT NULL,
    created timestamp with time zone NOT NULL,
    modified timestamp with time zone NOT NULL,
    activity_start timestamp,
    activity_end timestamp,
    CONSTRAINT non_overlapping_activity
        CHECK (activity_start < activity_end),
    CONSTRAINT country_activity_end_uq
        UNIQUE (country, activity_end),
    CONSTRAINT activity_start_end_fk
        FOREIGN KEY (country, activity_start)
        REFERENCES expenses_commissionrule (country, activity_end)
) ;

-- plus the two filtered indexes. We do need those.

Chapter 2: No List

After the all the tedious effort, let try something simpler. No explanation this time, lets read the code first and explain later:

CREATE TABLE ec_rule (
    id serial NOT NULL PRIMARY KEY,
    country varchar(2) NOT NULL,
    created timestamp with time zone NOT NULL,
    modified timestamp with time zone NOT NULL,
    activity_end timestamp,
    CONSTRAINT country_activity_end_uq
        UNIQUE (country, activity_end)
) ;

CREATE UNIQUE INDEX country_end_ufx
    ON ec_rule (country)
    WHERE (activity_end IS NULL) ;

OK, what happened here? This is much simpler, it can't possibly be used the same way as the previous designs! It can't possibly be equivalent. Or maybe it can?

Lets see what happens here:

activity_start is missing completely. How are we supposed to find the periods if they are not stored in the table?

The answer is that activity start is indeed stored, just not in the same row as activity end. That's what the foreign key was all about, to ensure that every end had a matching start. So we can easily find the start of every period using LAG():

CREATE VIEW expenses_commissionrule AS
SELECT
    id,
    country,
    created,
    modified,
    LAG(activity_end) OVER (PARTITION BY country
                            ORDER BY activity_end)
        AS activity_start,
    activity_end
FROM
    ec_rule ;

Chapter 3: Spot the difference

While all the above design manage to enforce the "no gaps" rule, they all fail to enforce the second rule: "the sum of all intervals should be -INF : +INF."

Can this be amended? It seems to be easier after all once we have achieved no gaps.

Well, yes and no. And no. It isn't easier. It's similar to enforcing that a table has at least one row. That seems easy, too, but it's actually very hard, if not impossible with DDL alone.

For the specific problem though, the "yes and no" means that the rule:

can be enforced with design 1 (although it makes it even more complicated).
but not with design 2 (at least I can't see a way).

For design 1, we'd need to add a second foreign key (from activity end to the next start) and a unique constraint (country, activity_start).  This essentially would convert our list to a doubly linked list. And for the foreign keys to be satisfied, the list would have to either be infinite (which isn't possible) or have ends both left and right, which means two rows with nulls, one for left and one for right (foreign keys are satisfied if one of the columns is NULL).

For design 2, you'd have to ensure - outside of DDL - that for every country there is one row where activity_end is NULL (that row is the rule for the period up to +Infinity).


Internal architecture of cursors I know that cursor isn't very good idea, but I have some questions for internal structure of cursor.

First step in cursor is determining result set which cursor iterate over. So, did cursor execute SELECT query and put the result in some temporary table or it is implemented somehow different?

SQL will take data pages in memory form disc, first time data pages was needed. So, assume that I was executed simple SELECT query manually, and SQL pull data pages in memory. Assume also that SELECT query has identical query in FOR clause. What happened at the end of cursor, when cursor are closed and deallocated? Did cursor free memory from used data pages, or that data pages remain in memory?

Depends on the cursor type, as documented in Concepts: Cursors:

  Forward-only

  A forward-only cursor does not support scrolling; it
  supports only fetching the rows serially from the start to the end of
  the cursor. The rows are not retrieved from the database until they
  are fetched ... Although the database API cursor models consider a
  forward-only cursor to be a distinct type of cursor, SQL Server does
  not. SQL Server considers both forward-only and scroll as options that
  can be applied to static, keyset-driven, and dynamic cursors.

  Static

  The complete result set of a static cursor is built in tempdb when the
  cursor is opened. ...

  Keyset

  The membership and order of rows in a
  keyset-driven cursor are fixed when the cursor is opened.
  Keyset-driven cursors are controlled by a set of unique identifiers,
  keys, known as the keyset. The keys are built from a set of columns
  that uniquely identify the rows in the result set. The keyset is the
  set of the key values from all the rows that qualified for the SELECT
  statement at the time the cursor was opened. The keyset for a
  keyset-driven cursor is built in tempdb when the cursor is opened.

  Dynamic

  Dynamic cursors are the opposite of static cursors. Dynamic
  cursors reflect all changes made to the rows in their result set when
  scrolling through the cursor. The data values, order, and membership
  of the rows in the result set can change on each fetch. ...

As for memory use, all data access goes through the buffer pool, see Memory Manager Architecture. There is no 'close' nor 'deallocation' occurring. Data is brought in memory as needed, it may be referenced by multiple queries, and evicted only when more free memory is required.


How can I rename a table, but also all FK etc. references to that table? I would like to e.g. rename my Applicant table to ApplicantProfile, as applicant is an abstract entity that has a profile. The profile is not the applicant.

Simply renaming the table is hopelessly inadequate because there are qillions of relationships to Applicant. My closest solution is scripting the whole DB, and doing a search and replace, but this seems risky, as I have fields like ApplicantId that mustn't be renamed. I can't do a whole word only, because I have names like `PK_Applicant'.

Is there a known method or tool I can use for this onerous task?

You should be able to generate the appropriate rename scripts using this, and adjust it to whatever naming convention you choose;  I've used this to ensure that the names use the current table and column names, so the where clause currently finds names that don't follow that convention.

SELECT
    PK_Table = PK.TABLE_NAME,
    PK_Column = PT.COLUMN_NAME,
    FK_Table = FK.TABLE_NAME,
    FK_Column = CU.COLUMN_NAME,
    Constraint_Name = C.CONSTRAINT_NAME,
    'FK_' + FK.TABLE_NAME + '_' + PK.TABLE_NAME + '_' + CU.COLUMN_NAME,
    'sp_rename ''' + C.CONSTRAINT_NAME + ''', ' + '''FK_' + FK.TABLE_NAME + '_' + PK.TABLE_NAME + '_' + CU.COLUMN_NAME + '''' + char(13) + char(10) + 'GO' + CHAR(13) + CHAR(10)
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
    INNER JOIN (
        SELECT i1.TABLE_NAME, i2.COLUMN_NAME
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
        WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
        ) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE
    C.CONSTRAINT_NAME != 'FK_' + FK.TABLE_NAME + '_' + PK.TABLE_NAME + '_' + CU.COLUMN_NAME
ORDER BY
    PK.TABLE_NAME, FK.TABLE_NAME


Possible to CROSS JOIN a subquery without duplicating code? I'm wanting to get all permutations between two columns in the same table.

Currently my query is this:

declare @tgt int = 123456;
select *
from (
  select T.id, T.parentId
  from Task T
  where (
    (T.id = @tgt and T.typeId = 3)
    or
    (T.parentId = @tgt and T.typeId = 1)
  )
) WF1
cross join (
  select T.id, T.parentId
  from Task T
  where (
    (T.id = @tgt and T.typeId = 3)
    or
    (T.parentId = @tgt and T.typeId = 1)
  )
) WF2;

Note that subquery WF1 is the same as WF2. In order to be DRY, I'd really like to do this:

declare @tgt int = 123456;
select *
from (
  select T.id, T.parentId
  from Task T
  where (
    (T.id = @tgt and T.typeId = 3)
    or
    (T.parentId = @tgt and T.typeId = 1)
  )
) WF
cross join WF;

But this is not working in SQL Server 2012.

Is there some technique I can apply to avoid duplicating the query?

You can use a CTE:

WITH WF AS (
    select T.id, T.parentId
    from Task T
    where
        (T.id = @tgt and T.typeId = 3)
        or
        (T.parentId = @tgt and T.typeId = 1)
)
SELECT *
FROM WF AS WF1
CROSS JOIN WF AS WF2


Replication Error when adding small table as new article I have just added a small (200 row) table to my current replication which has now broken my entire replication scenario as it seems to have broken the Log reader. The 2 tables are unrelated.
I am attempting to locate the table the related field is in and drop this table to see if that resolves the issue. Any other help in the meantime would be appreciated if anyone has come across this issue.
The errors I'm getting are:

The process could not execute 'sp_replcmds' on 'QUEEN'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Could not locate text information records for the column "MergeUpdateDesc", ID 28 during command construction. (Source: MSSQLServer, Error number: 18773)
The Log-Scan Process failed to construct a replicated command from log sequence number (LSN) {0022f069:000090cb:0026}. Back up the publication database and contact Customer Support Services. (Source: MSSQLServer, Error number: 18805)
The process could not execute 'sp_replcmds' on 'QUEEN'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)

This is a bug with Replicating BLOBS in transactional replication. when the snapshot agent ran, something updated the BLOBs currently in the publication and this broke ALL replication streams.

After removing the initial table from the publication it moved onto another table containing BLOB data.

To resolve this I separated all tables containing BLOB data into their own Publication and removed them from my main publication. The Log Reader Agent then kicked back in.


What started this SQL Server job? I'm looking at a SQL Server environment in which jobs start in several different ways. One way is from a job running on a different SQL Server.

So, for example, "SQL Server A" runs a job named "DoStuff". One step in "DoStuff" is to run the job "MoreStuff" on "SQL Server B".

If I were looking at a problem on "SQL Server B", how would I answer the question, "What started 'MoreStuff'?"

The Job Properties "General" page has nothing about any external source.

The Application log records what "MoreStuff" is doing, but it doesn't seem to have any information about what started "MoreStuff". Other classic logs and newer Applications and Services Logs don't seem to have anything relevant in them. (I could have missed something.)

I'll take information on Server 2008 R2 and up, and on SQL Server 2005 and up.

Well you can try to run a trace (if it's possible for you). But you can also try to just write it to your SQL Server Agent Job History instead.

I tried to write a little bit of code which can be added as a first step in front of all other steps. It will simply write a line "Executed from [hostname]".

I couldn't test it, due to the fact that I have just one instance on this machine, and I normally use it for trainings and won't misconfigure it just to have another instance. And it's late too. :-)

You can try this code:

DECLARE @host_name nvarchar(255)

SELECT @host_name = host_name
FROM sys.dm_exec_sessions AS ses
LEFT JOIN sys.servers as ser
        ON ses.host_name = ser.name
WHERE session_id = @@SPID -- only current session
    AND ser.name IS NULL -- only local connections

-- remote call
IF @host_name IS NULL BEGIN
    DECLARE @sql nvarchar(max), @server_name nvarchar(max),
            @ParmDefinition nvarchar(max) = N'@hn nvarchar(255) OUTPUT'

    SELECT @server_name = QUOTENAME(ser.name)
    FROM sys.dm_exec_sessions AS ses
    LEFT JOIN sys.servers as ser
            ON ses.host_name = ser.name
    WHERE session_id = @@SPID -- only current session
        AND ser.name IS NOT NULL -- only remote connections

    SET @sql = N'
        SELECT @hn= host_name
        FROM sys.dm_exec_requests AS ses
        WHERE wait_resource LIKE = N''%'+@@SERVERNAME+'%'+CONVERT(nvarchar(max),@@SPID) +N'%'''

    -- Encapsulate EXEC, to run it dynamically on another server
    EXEC (N'DECLARE @host_name nvarchar(255);
            EXEC '+@server_name+'.master.dbo.sp_executesql @sql = N'''+@sql+''',
                @ParmDefinition = N'''+@ParmDefinition+''',
                @hn = @host_name OUTPUT;
            PRINT(N''Executed at ''+@host_name)')

END
-- local call
ELSE BEGIN
    PRINT(N'Executed from '+@host_name)
END

Well, what does it do? It tries to check if the session is a local one or a session from a linked server. If it's a local one, it will just print out the host_name from the caller. If the caller is another server, it will take a look at the other server and find the SPID which executes the current session and will print the host_name from the remote server.

I don't know if it work that way, it need some test. Alongside with that it can be extended to look dynamically to the next server if it's a remote call too. (e.g. ServerA calls ServerB calls ServerC calls ServerD (which is the entry point)).

Hopefully it will help you and give you a working base.


Writing Select statement to join multiple tables I have a database that is designed like so:

And I would like to write a SELECT statement that returns all rows from all tables. I have tried the following but always get 0 results returned:

SELECT
*
FROM TABLEB
INNER JOIN TABLEA on TABLEB.TableAId = TableA.ID
INNER JOIN TABLEC on TABLEB.TableCId = TableC.ID
INNER JOIN TABLED on TABLEC.TableDId = TableD.ID
INNER JOIN TABLEE on TABLEC.TableEId = TableE.ID

Change the INNER JOIN to LEFT JOIN. There's a chance there are missing foreign key values that are causing it to not find any matches across the whole set.

Where you see NULL in the result set, will be an indicator where there is no matching foreign key.


How are bit fields sorted for an index? I am building a compound index, and I'm not 100% sure which way to include a bit field that is part of the table. The index will be for something similar to a list of forum topics, where I want the single index to be by ForumId (int), IsSticky (bit), then DatePosted (desc) (datetime2(0))

But I want to make sure I handle the IsSticky bit field properly; Do I index it as if true is 1 or -1? If it's 1, I would index IsSticky descending; if -1, I would index it ascending.

Also, I'm asking for SQL 2008, but I'd be interested in knowing if it's different in 2005.

A SQL Server bit field can be 0, 1 or NULL, so I'm unsure where the -1 part of your question features.

Skipping past that, the most appropriate solution for "sticky threads" in a forum database is to use a separate filtered index. Flag a sticky thread as IsSticky=1 and create a filtered index along the lines of:

CREATE NONCLUSTERED INDEX IX_Posts_StickyThread
    ON dbo.Posts (ForumId, PostId)
WHERE IsSticky = 1;

Filtered indexes were new to SQL2008 so this wouldn't be an option for SQL2005. The SQLCat article Using a Low-Selectivity BIT Column First Can Be the Best Strategy would be applicable reading for an approach to this in 2005 (edit: article has been archived to the eBook SQLCAT's Guide to Relational Engine).

And just to address the title question bit fields in an index are ordered same as per ORDER BY bit_field i.e. NULL, 0, 1 (credit @MartinSmith).


Sudden reduction in full backup size I recently repaired a single corrupt page in one of the tables in a 70gb database.

I had a backup which included the data contained in the page so I went about using DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS. I then transferred the rows from the old database back to the newly repaired database.

After running a DBCC CHECKDB WITH NO_INFOMSGS to make sure everything was okay again I then ran a full backup. The backup is 10gb less than the previous backups (like the one I took before the repair with CONTINUE_ON_ERRORS).

What is the reason for this? Does the DBCC repair do any reordering of pages which would recover space or something similar? Table sizes look similar to previous but I'm a little worried there could be missing data.

When you transferred data, you probably compacted data by an implied index rebuild.

Or the REPAIR_ALLOW_DATA_LOSS lived up to it's name...


why are people advised not to update through views During College it was banged into our heads to not update tables through views, and again when I got into the workplace we where told do not update the database through views.

Where there/Are there significant performance implications to doing this?
Or is this more a case of senior developers/DBAs' telling junior personnel not to do this because they can unwittingly wreak havoc with an incorrect join.

EDIT
I'm using MSSQL 2000-2008 (Depending on the particulars of the client)

For a view to be updatable without using INSTEAD OF triggers "SQL Server must be able to unambiguously trace modifications from the view definition to one base table.".

There is no performance disadvantage to Updating these Views as SQL Server will just generate a query plan for the base table affected. One possible disadvantage might be that it adds a layer of obfuscation so unless you are using Views as a security layer it is clearer to just write code that updates the base table directly.

Another one might be if the View contains a one to many JOIN and you update the "one" side with a value from the "many" side it is undeterministic what result you end up with but the same applies to SQL Server's proprietary UPDATE ... FROM syntax. You would need to use MERGE or a scalar correlated sub query to avoid this possible issue.

For Views that are not updatable and require an INSTEAD OF trigger there are performance implications as the inserted and deleted pseudo tables need to be generated from the base table so if possible updating the base tables directly will likely be more efficient.


Is there a repository for pgadmin plugins? I've been using PostgreSQL with PGAdmin III for a while now and it's been bugging me that there is a plugins menu option that is empty. I've Googled some and found a plugin here and there but I wanted to know if there was a repository I was missing out on?

If you don't know of a repo but are aware of additional plugins please also let me know about them.

Your best place would be pgfoundry. But you won't find much. Doesn't look like many plugins are available.


How would you normalize a database that has multiple products to a single transaction ID? How would you normalize this? I can't find a way to keep multiple products under a single SalesID

Might as well make my comment an answer so you can close it:

A Sales Order has many Sales Order Line Items. One table for Sales Orders, one for Sales Order Line Items with a foreign key to the former

create table sales_orders (
  id int primary key,
  order_date date,
  ...
);

create table order_items (
  order_id int references sales_orders(id),
  line_number smallint,
  unit_price decimal(19,4) not null,
  quantity smallint not null default 1,
  product_id int not null references products(id),
  ...

  primary key (order_id, line_number)
);


Define a column as MD5 sum of other columns in MySql Suppose I have following table -

id   name
--   ----
1    kshitiz

Is it possible to define another column that is the md5 sum (or some other hash) of values in the previous two columns?

id   name    hash
--   ----    ----
1    kshitiz 392b6be97993de246fe188ac5d0052a7

if you are talking of column definition as in create table, there isn't a data type for hash.

However, it's best to use binary(20) and unhex function to convert your sha-1 into a binary data type because it saves a lot of space.

create table user(
id int primary key auto_increment not null,
username varchar(50),
password binary(20)
);

insert into user(username) values('ndefontenay');

update user set
password = unhex(sha1(concat(last_insert_id(),username)))
where id = last_insert_id();

select upper(sha1(concat(1,'ndefontenay'))) as "on insert",hex(password) as "on retrieve"
from user
where id = 1;


Oracle How to find Debug Compiled Stored Procedures/Functions How can I get list of stored procedures/Function which are compiled for debug?

I do not see relevant column in following tables.

DBA_PROCEDURES
DBA_OBJECTS

I think DBA_PLSQL_OBJECT_SETTINGS gives more appropriate information.


SQL Server Replication: Is there a way to know which articles are being used in a replicated database? I've come into a situation where there are about 1000+ tables being replicated to a database.  When it was created, I think the creators just put a bunch of tables they thought would be needed.  I'd like to reduce the number of tables being replicated to only the ones needed.  Are there any good ways to figure out which tables are needed in replication?
Thank you for your help.

The only way to come close to being sure would be to log their usage via things like the  Profiler, Extended Events, or depending on what version your SQL Server instance is, the Query Store. You'd have to pick a timeframe long enough to log that you think is sufficient since it's possible in some contexts / businesses, some of the tables are used with low frequency for quarterly / bi-yearly / yearly reports (but maybe that doesn't apply in your use case).
Depending on how important it is for that data to be online, you could always also perform a scream test and take the replicated destination database offline for a few hours or a day, or apply a similar strategy to a group of tables at a time, to see who starts complaining about what's broken and then concretely determining which tables were in use for those consumers.


postgres standby - what not WAL records can be replayed? https://www.postgresql.org/docs/current/static/standby-settings.html

  The delay occurs only on WAL records for transaction commits. Other
  records are replayed as quickly as possible, which is not a problem
  because MVCC visibility rules ensure their effects are not visible
  until the corresponding commit record is applied.

I thought while streaming replication xlog is replayed on slave. Local transactions are not replayed. Remote statements that skip WAL (Eg create table nologging) are not replayed on slave. Now I'm confused - what are those other records that are replayed not waiting for recovery_min_apply_delay?..

There are lots of transaction log records other than commits.

Transaction ID allocations (roughly like a BEGIN delayed until first actual write). Inserts, updates, deletes, vacuum, lots of internal operations.

Synchronous streaming replication  only waits for confirmation that the replica has replayed commit transaction log records. Not any of the others.

Some things, like the UNLOGGED tables you refer to, do not generate any transaction logs at all. So they cannot affect replicas. But that is not what that passage in the docs is talking about.


Why would a table use its primary key as a foreign key to itself Looking through a database, I came across a table that used its primary key as a foreign key to itself.

I've seen that a table can have a foreign key to itself to build a hierarchy structure, but it would use another column to reference the primary key.

Since the primary key is unique, in this situation wouldn't the row only be able to point back to itself? That seems to be a tautological link, since if I already have the row, then I already have the row.

Is there any reason this would be done?

I am certain the constraint is written that way (not just looking at the diagram) because the same table and column are used for both halves of the definition.

Like you said. A FOREIGN KEY constraint referencing the same table is typically for a hierarchy structure and it would use another column to reference the primary key.  A good example is a table of employees:

EmployeeId    Int     Primary Key
EmployeeName  String
ManagerId     Int     Foreign key going back to the EmployeeId

So in this case there is a foreign key from the table back to itself.  All managers are also employees so the ManagerId is actually the EmployeeId of the manager.

Now on the other hand if you mean someone used the EmployeeId as the foreign key back to the Employee table then it was probably a mistake.  I did run a test and it's possible but it wouldn't have any real use.

CREATE TABLE Employee (EmployeeId Int PRIMARY KEY,
                        EmployeeName varchar(50),
                        ManagerId Int);

ALTER TABLE Employee ADD CONSTRAINT fk_employee
    FOREIGN KEY (EmployeeId) REFERENCES Employee(EmployeeId);


Trigger Gives an Error on Multiple Rows SQL SERVER I have a problem when I create a trigger which is giving me this error:

  Msg 512, Level 16, State 1
  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression***

The Trigger is :

Create TRIGGER AccountChange
ON Customer
AFTER UPDATE
AS
BEGIN
    IF(UPDATE(TotalSales))
    BEGIN
        DECLARE @TotalSales MONEY,@AccountNumber NVARCHAR(20),
            @TotalSalesOld MONEY,@customText1 NVARCHAR(50),
            @AccountTypeId int

        SET @TotalSales=(SELECT TotalSales FROM INSERTED)
        SET @TotalSalesOld=(SELECT TotalSales FROM DELETED)
        SET @AccountNumber=(SELECT AccountNumber FROM INSERTED)
        SET @AccountTypeId=(SELECT AccountTypeId FROM INSERTED)

            IF(@TotalSales BETWEEN 0 AND 5000)

                    UPDATE Customer SET AccountTypeID=8
                      WHERE AccountNumber=@AccountNumber

   END
END

since INSERTED can contain more than one row the update could be performed using a join and not fillin single valued variables:

UPDATE C
SET AccountTypeID = 8
FROM Customer as C on INSERTED as I on I.AccountNumber = C.AccountNumber
WHERE I.TotalSales between 0 and 5000


Sudden MySQL error: Unable to connect to any of the MySQL Hosts I am using MySQL Community Server 5.5.18 on Windows 7. My application was running absolutely fine without any troubles but since morning it is giving strange messages while opening reports. Reports are on Crystal Reports but the error has nothing to do with the Crystal Reports. This message started appearing suddenly. This application is running very well from last two years.
Error image is below:

Please let me know how to fix this issue.

ISSUE #1 : Connection Errors

Once mysql has racked up enough connection errors, the host blocks all IPs until you do one of two things:

$ service mysql restart
mysql> FLUSH HOSTS;

According to MySQL Documentation on FLUSH HOSTS;

  HOSTS

  empties the host cache tables. You should flush the host tables
  if some of your hosts change IP address or if you get the error
  message Host 'host_name' is blocked. When more than max_connect_errors
  errors occur successively for a given host while connecting to the
  MySQL server, MySQL assumes that something is wrong and blocks the
  host from further connection requests. Flushing the host tables
  enables further connection attempts from the host. See Section
  C.5.2.6, “Host 'host_name' is blocked”. You can start mysqld with
  --max_connect_errors=999999999 to avoid this error message.

Try bumping up max_connect_errors (default is 10) to some obnoxiously high number, like 999999999 (about 1 billion), in /etc/my.cnf as follows:

[mysqld]
max-connect-errors=999999999

You will then just restar mysql. To put this new setting in place without restarting, just run this command in the mysql client:

mysql> SET GLOBAL max_connect_errors = 999999999;

ISSUE #2 : DNS

If you are connecting to mysql using DNS, this could be one possible source of issues if:

DNS servers go down
DNS servers cannot be reached

To alleviate DNS issues, you need to add the following to /etc/my.cnf (mysql restart required):

[mysqld]
skip-host-cache
skip-name-resolve

You also need to look at the host column of mysql.user. If any of the users have a DNS name, replace it with one of the following choices:

Public IP
Private IP
LoadBalacner-managed VIP
Netblocks (such as 10.1.2.%) of the first three choices

Afterwards, run mysql> FLUSH PRIVILEGES;


Size of an "empty" PRIMARY filegroup I have a question for you concerning the PRIMARY filegroup.

Out goal is to have a small PRIMARY, so that we can do partial restores to other systems (test, dev)

So, we moved all tables and indexed from the PRIMARY to other filegroups.

However, the PRIMARY filegroup is still large. I have been trying to see what system object actually is in an "empty" PRIMARY, but can't seem to find anything that takes up that much space!

So, the question is, is the size of an "empty" PRIMARY dependent on the over-all size of the database? And if so, which kind of system objects lurks with in the PRIMARY, and is there anything one can do the decrease the size of these?

The total size of the database is  5300 GB.
The total space used of the "empty" PRIMARY is now 444 GB (and 134 GB free space.)

you can use shrinkdatabase command to reclaim the freespace,
But be aware ! shrinking in many cases causes fragmentation , you can rebuild  or reogranize all indexes depend on fragmentation percentage.

DBCC SHRINKDATABASE(N'YourDatabaseName' ) or
DBCC SHRINKFILE(N'YourDatabaseName', Size )

After that, you should change your default filegroup from primary filegroup to another one.


Oracle second date range comparison ignored in where clause I have the query below and am receiving an unexpected dataset returned back. I was wondering if you all might be able to explain what is going on for me? I am using Oracle 11g.

I understand having the sysdate check and the date range condition of the where clause is redundant, but as for now, I do cannot change the query without having a rationalization as to why we are receiving the data we are receiving. When I run the query below, I receive 25 records with a created_date of 17-Feb-04 and only 1 with 22-Sep-17. There are many more records in this database with other dates, but for some reason, this is what we are receiving.

SELECT created_date
FROM tableName LOAN
WHERE
loan.created_date > sysdate - 11000
AND loan.SYSTEM_ID ='ALS'
AND loan.BANK_NUM = '021'
AND loan.short_name like 'NAME%'
AND trunc(loan.CREATED_DATE) BETWEEN TO_DATE('09-01-2017', 'MM-DD-YYYY')
AND TO_DATE('10-13-2017', 'MM-DD-YYYY')

If I place sysdate at the end of the query, I receive the expected results (1 transaction with created_date of 22-Sep-17). If I trunc(loan.created+date) > sysdate - 11000 and leave everything else the same, I receive the correct expected result. If I remove the trunc from AND trunc(loan.CREATED_DATE) BETWEEN TO_DATE('09-01-2017', 'MM-DD-YYYY') AND TO_DATE('10-13-2017', 'MM-DD-YYYY') to AND loan.CREATED_DATE BETWEEN TO_DATE('09-01-2017', 'MM-DD-YYYY') AND TO_DATE('10-13-2017', 'MM-DD-YYYY'), I receive the expected results. However, I do not understand why those configurations of the same where clause (or slightly different) produce those results.

I understand that when trunc is used on a date field, it essentially rounds up to the beginning of that date. Therefore, I may see a difference in results when comparing created_date field against sysdate due to the timestamps of those fields, but the results I am seeing do not vary by minutes or hours, but years. Somehow, the second date condition is either being ignored or there is an issue with how I am comparing the sysdate and the date comparison.

Not enough information, so I will just leave this here:

Bug 14073795  Wrong results on select statement with TRUNC(date) or ROUND(date)

Bug is fixed in 11.2.0.4.


MySQL Replication: MASTER_LOG_POS never changes I have a Master / Slave Setup in production and from time to time I add or remove a slave and recently I also removed the master and promoted a former slave to new master.

This procedure usually involves dumping my master DB like so:

mysqldump -u root -pPASSWORD --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A  > /data/dump.sql

and then reading the MySQL master log position from the dump like so:

head dump.sql -n80 | grep "MASTER_LOG_POS"

The output is something like

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000036', MASTER_LOG_POS=107;

(Source: http://plusbryan.com/mysql-replication-without-downtime)

Now as I have done this a dozen times I noticed that while the MASTER_LOG_FILE changes, the value for MASTER_LOG_POS is always 107 and never changes even though I have created new dumps from the master every time I add a new slave, and the dumps are weeks apart. This seems odd. I have not noticed any sorts of data corruption but should the value of MASTER_LOG_POS not change with every dump from the master?

No, this is correct.
In your MySQLDump you issue a flush-logs command, which creates a new Binary log.
The initial position in the binary log (after headings etc.) is 107, and always will be (unless you upgrade to a different version - then it may be something else).


SSMS 10.5 alongside 17.7 To set the scene, I maintain 4 local SQL Server 2008 R2 instances.
At some point in the near future, 3 of these will be upgraded to SQL Server 2017 and migrated to azure.

I am currently using SSMS and SSIS 10.5 to manage these servers and develop ETLs.

Can SSMS 10.5 and 17.7 be run side-by-side, and can I do this before we've even upgraded any of the instances to 2017? Is there any benefit of doing so?
I'm considering forking out for ApexSQL but some of the tools aren't compatible with 10.5.

Can SSDT also be installed side-by-side with SSIS 10.5?

Thanks.

You can use the latest version of SSMS with SQL 2008 R2. Im not aware if any backward compatibility problems.

It will also work with SQL 2017 and Azure.

SSDT is a tool used to develop SSAS, SSIS and SSRS Reports. It wont cause any problems installing alongside SSMS.

SSIS changes a far bit in 2012 namely in the use of SSIS catalog. IMO its a great improvement. Depending upon your layout it may be feasible to upgrade SSIS first or independantly.

Note that once you open an old solution in a new version of SSDT or visual studio it will want to upgrade the solution. Once that happens you wont be able to open it again with an old version. Ive been through a similar scenario as you describe. In the end i found it was just easier to take the plunge and upgrade to the current client tools.


mysql 5.6 FEDERATED table and mysql.servers correlation I have no expirence with FEDERATED tables in MySQL. I have database with FEDERATED tables defined and I'm wonder if this tables are used in current env (I have no access to application/developers to ask/check).
The mysql.servers table (https://mariadb.com/kb/en/mysqlservers-table/) is empty.
I wonder if connection info stored there is ephemeral or persitent after first connection to remote server/s.
My question is: if mysql.servers table is empty can I "safely" assume that this tables are not used by apps?

See manual

No data is stored on the local tables.

So if the original datatable has data the federated has too


mlagent: How to export remote task When creating a new remote tasks via Sybase Central for mlagent, it is possible to import task from file.

How to export remote task to a file so it can be imported into another Sybase Central installation (e.g. remote)

The tasks are stored inside project folder of MobiLink-project with mlt-extension. They are XML-files. Therefor there is no special export needed. Just copy the file.


MongoDB insert performance rapidly dropping I'm currently experiencing a rapid decrease of the insert Performance.

I have a rather simple data model

{
    "Id": "ObjectId",
    "SomeProp": "xy",
    "SomeTags": [
        {
            "key": "x",
            "value": "y"
        }
   ]
}

Indices are created on of course the mongodb ("Id"), ("SomeProp") Ascending and ("SomeTags.key", "SomeTags.value") Ascending. The two custom indices also have background:true.
(Also tried with Background:false but got worse results)

I use the new unordered bulk api to perform the inserts, this seemed to be slightly faster than batchinsert.

At the beginning (e.g. the first 100M rows) it'll insert constantly ~20-30k/sec. But then the speed suddenly drops to below 1k/sec.

Is there anything I can do, to keep the speed constant?

Below you can find the stats from MMS.

View Large

View large

The MMS monitoring graphs show that your disk basically can't keep up with the volume of data you are trying to persist to disk.  The flush average is hitting over 100 seconds.  Since MongoDB flushes data to disk every 60 seconds by default, that means that successive queues are queuing up and then impacting everything else.  Basically you need more IO to sustain the write workload you are performing, likely an SSD, for that kind of sustained writing.

You can try throttling things by using different write concerns interspersed to get a more consistent throughput without completely overwhelming the system (check out j:true and a concern greater than 1 if you are running a replica set).


Big table with simple queries, but with a lot of text, is it bad for performance? I am making cache table for website. It will have no problem with indexes (one unique index, simple quires), but it will have a lot of utf text. It will be 10-15gb (2 mln rows).
Caching will help a lot for saving cpu usage, without cache every page has many complex queries.

Is it ok two have such a big table? It will be queried only with "SELECT text WHERE id = $id" several times a second.

MySQL itself can handle primary key selects very efficiently. However cached content does not require transactional engine so it would be much faster to store this kind of data in various key-value storages. Memcached is the first one to consider.


Design for when a company can edit a client information, but client has a private profile First of all, let me apologize for the title, this predicament I'm in is quite impossible to describe in few words.

The application architecture involves a relationship between a company, clients, and its staffs.

1) A client creates an account (Account table with login and password), and creates a profile (Profile table with name, phone number, etc)

2) The client chooses a company for a service. At this point, the client becomes a client of the company, and a record is inserted in a regular many-to-many CompanyClient table.

Now, the company owner must be able to edit details of this client, and this includes profile information, such as phone number and email. However, these changes must not be reflected on the clients account when he logs in, e.g. the profile record of the client cannot be modified. It is as if the client has a private profile, and inside the a company he has another profile.
A client registered as Thomas might have his name changed to Tom by a company admin that finds it easier to recognize Thomas by Tom.

Also, a company may add a client to its client list, a client that has no account or anything.

To solve this, I came up with this design:

Account
 -Id (FK to profile table Id, an account cannot exist without a profile)
 -Login
 -Password

Profile
 -Id
 -Name
 -Email

Person (basically a clone of the client's profile at first)
 -Id
 -Name
 -Email
 -Profile Id (nullable, a client without an account)

CompanyClient
 -Company Id
 -Person Id

Keep in mind that a client may be associated with multiple companies, which means there would be several "profile clones" in the Person table.

Ok, this is half of the problem. Here's the other half:

a company staff may very well have an account or not, just like a client, and a company must also be able to change the staff profile without affecting his account, if any.
A staff has all of the Person table columns, and more.
I thought about making a Staff table with an FK to Person. This would also allow for a Staff to be granted access to the system, which would cause a record to be inserted into Account, and the Profile Id column of his Person table record would be set to the newly inserted account Id.

The last part of the problem is that a company must be able to add additional information about a client, such as rating, observation, etc. Because of this, having the Person table starts to sound wrong.

I'm not sure this is the right way to go about this problem, or if adding all of the Person columsn  to the CompanyClient and CompanyStaff table would be the right way. This feels like a very unusual design, any help is appreciated.

Maybe the best way is to have a Profile, Client, and Staff table, all with replicating information such as name, phone, etc?

A staff has all of the Person table columns, and more.

How much more ? 4-5 column extra ?
I suggest try to keep both table same with more column as additional. along with ClientType Column int.

OR

Client Info table means ,those minimum info is need to become client.
Same thing is true for Staff too .Isn't it ? So Staff info will be maintain in 2 tables.One which is necessary to become client will be in client Table.Rest in Staff table.

Now as per rule Company can change same fields for both Client and Staff.
So may be Staff info is edited from 2 place,but both are 2 diff. thing.

So there will be Profile table for both Staff and Client.

Profile
 -Id
 -Name
 -Email
-- ClientType int not null (1=Client,2=Staff,3=Client who do not have profile)

Staff Table
It contain info of Staff table which cannot be edited by company.but can be edited by Admin

Person (basically a clone of the client's profile at first)
 -Id
 -Name
 -Email
 -Profile Id (not nullable)
-- ClientType (Denormalization)

CompanyClient
 -Company Id
 -Person Id
 --ClientType (Denormalization)

Rating Table
--RatingID
--PersonID
--Observatiion
--Rating

This is not my final design .It all depend upon MIS report and frequency of use.


Mysql insert fixed column value for each row during multi-insert In mysql when I'm doing

# fr-FR.sql
INSERT INTO table (resource_key, resource_value, locale) VALUES
    ("GEN.YES", "Oui", "fr-FR"),
    ("GEN.NO", "Non", "fr-FR"),
    ("GEN.MAYBE", "Peut-être", "fr-FR")
ON DUPLICATE KEY UPDATE `resource_value` = VALUES(`resource_value`);

# en-US.sql
INSERT INTO table (resource_key, resource_value, locale) VALUES
    ("GEN.YES", "Yes", "en-US"),
    ("GEN.NO", "No", "en-US"),
    ("GEN.MAYBE", "Maybe", "en-US")
ON DUPLICATE KEY UPDATE `resource_value` = VALUES(`resource_value`);

Here, 'resource_key' and 'locale' are the primary key. 'locale' is always the same in one file. But differ from file to file (can't set as default value);

This query works. It insert each row and if a label for a given resource_key/locale pair exists, it will update its associated resource_value

Is it possible to insert same value in each row for a given column. There is thousands of lines in different files (added continuously) and always typing the same string in each row is annoying.

I am surely too late for you, but maybe somebody will have similar question in the future.

A way how to accomplish that is by creating temporary table #temptable, and while copying data to actual table, add constant column:

# en-US.sql
INSERT INTO #temptable (resource_key, resource_value) VALUES
    ("GEN.YES", "Yes"),
    ("GEN.NO", "No"),
    ("GEN.MAYBE", "Maybe")
ON DUPLICATE KEY UPDATE `resource_value` = VALUES(`resource_value`);

INSERT INTO table (resource_key, resource_value, locale) VALUES
    (SELECT t.*, "en-US" FROM #temptable AS t);


SQL Server 2016 migration to Azure Trying to migrate SQL Server to Azure, MS Data Migration tool indicates need to delete SSIS. Using advice from Stackflow and others, go into programs and features and SSIS is not listed as a component.

Other advice indicates go into setup and remove components/features will be an option. Using the admin login I do not see that option.

Finally, I ran SQL Server features report and SSIS was not on it. It listed DB Engine services, SQL Server replication, Full text search, SSMS, Adv-SSMS and Data Quality Client.

At a loss on how to proceed.

thanks for the reply. The migration tool generates a report. In it's report, the migration tool listed SSIS as a feature that had to be deleted, it also indicated all maintenance plans had to be deleted. SSIS afaik was not installed and did not show in the features when I checked programs and features. This confused me. I had left the maintenance plans on while running the tool since we needed them. On a hunch I deleted the maintenance plans and SSIS was no longer listed in the report as a problem feature. The maintenance plans were backups, either they used SSIS somehow even with it not installed, or the MS's migration tool was wrong. In any case, with the maintenance plans delete I no longer have the problem.


Can I make PL/SQL functions more strongly typed? I like to use overloading to speed up development, and I want to structure my code to make future changes easier. I have a function that accepts a NUMBER or a VARCHAR2, but I want it to throw a compilation error if I pass in a DATE.
Here's an illustration of the problem I want to avoid:
DECLARE
   PROCEDURE do_something(
      in_ IN VARCHAR2);

   PROCEDURE do_something(
      in_ IN NUMBER);

   mystery_variable_1 VARCHAR2(32767);
   mystery_variable_2 NUMBER;
   mystery_variable_3 DATE;

   PROCEDURE do_something(
      in_ IN VARCHAR2)
   IS
   BEGIN
      dbms_output.put_line('do_something called with VARCHAR2 argument ' || in_);
   END do_something;

   PROCEDURE do_something(
      in_ IN NUMBER)
   IS
   BEGIN
      dbms_output.put_line('do_something called with NUMBER argument ' || in_);
   END do_something;
BEGIN
   mystery_variable_1 := 'Hello, World!';
   mystery_variable_2 := 42;
   mystery_variable_3 := TO_DATE('2063-04-05', 'YYYY-MM-DD');

   do_something(mystery_variable_1);
   do_something(mystery_variable_2);
   do_something(mystery_variable_3); -- I want this to throw a compilation error.
END;
/

Here's the output showing the implicit cast from DATE to VARCHAR2:
do_something called with VARCHAR2 argument Hello, World!
do_something called with NUMBER argument 42
do_something called with VARCHAR2 argument 05-APR-63

I don't want that third line to be allowed.
The best work-around I have so far is to create a honeypot overload that throws an application error. This catches the issue in testing but not at compile time.
DECLARE
   PROCEDURE do_something(
      in_ IN VARCHAR2);

   PROCEDURE do_something(
      in_ IN NUMBER);

   PROCEDURE do_something(
      in_ IN DATE);

   mystery_variable_1 VARCHAR2(32767);
   mystery_variable_2 NUMBER;
   mystery_variable_3 DATE;

   PROCEDURE do_something(
      in_ IN VARCHAR2)
   IS
   BEGIN
      dbms_output.put_line('do_something called with VARCHAR2 argument ' || in_);
   END do_something;

   PROCEDURE do_something(
      in_ IN NUMBER)
   IS
   BEGIN
      dbms_output.put_line('do_something called with NUMBER argument ' || in_);
   END do_something;

   PROCEDURE do_something(
      in_ IN DATE)
   IS
   BEGIN
      RAISE_APPLICATION_ERROR(-20001, 'do_something called with DATE argument ' || TO_CHAR(in_, 'YYYY-MM-DD HH24:MI:SS'));
   END do_something;

BEGIN
   mystery_variable_1 := 'Hello, World!';
   mystery_variable_2 := 42;
   mystery_variable_3 := TO_DATE('2063-04-05', 'YYYY-MM-DD');

   do_something(mystery_variable_1);
   do_something(mystery_variable_2);
   do_something(mystery_variable_3);
END;
/

If I leave this work-around in place for the benefit of the maintenance developer, I get an unsightly PLW-06006 error when my private function is excluded from my package.
Can I trap this at compile time instead?

I think you are fighting against how Oracle works with implicit type conversion but you could suppress that conversion with custom types. Looks ugly but kind of does what you asked:
drop type vtype;
drop type ntype;

create type vtype as object(vvar varchar2(32767));
/

create type ntype as object(nvar NUMBER);
/

DECLARE
   PROCEDURE do_something(
      in_ IN vtype);

   PROCEDURE do_something(
      in_ IN ntype);

   mystery_variable_1 vtype;
   mystery_variable_2 ntype;
   mystery_variable_3 DATE;

   PROCEDURE do_something(
      in_ IN vtype)
   IS
   BEGIN
      dbms_output.put_line('do_something called with vtype argument ' || in_.vvar);
   END do_something;

   PROCEDURE do_something(
      in_ IN ntype)
   IS
   BEGIN
      dbms_output.put_line('do_something called with ntype argument ' || in_.nvar);
   END do_something;
BEGIN
   mystery_variable_1.vvar := 'Hello, World!';
   mystery_variable_2.nvar := 42;
   mystery_variable_3 := TO_DATE('2063-04-05', 'YYYY-MM-DD');

   do_something(mystery_variable_1);
   do_something(mystery_variable_2);
   do_something(mystery_variable_3); -- I want this to throw a compilation error.
END;
/

Output:
 30     do_something(mystery_variable_1);
 31     do_something(mystery_variable_2);
 32     do_something(mystery_variable_3); -- I want this to throw a compilation error.
 33  END;
 34  /
   do_something(mystery_variable_3); -- I want this to throw a compilation error.
   *
ERROR at line 32:
ORA-06550: line 32, column 4:
PLS-00306: wrong number or types of arguments in call to 'DO_SOMETHING'
ORA-06550: line 32, column 4:
PL/SQL: Statement ignored

Bobby


How to get the SQL_ID that is blocking another SQL? Well, I'm personalizing a useful script that I found and I need put in this script what is the sql_id of the SQL that is blocking another SQL.

Here is my script, if you try this, You never will get the sql_id of the blocker session, just the sql_id of the bloqued session.

col BLOCKER for a20;
col OBJ_BLOCKED for a20;
set linesize 3000;
col OWNER_OBJCT for a25;
set pagesize 0 embedded on;
select TO_CHAR(TRUNC(max(l2.ctime)/3600),'FM9900') || ':' ||
    TO_CHAR(TRUNC(MOD(max(l2.ctime),3600)/60),'FM00') || ':' ||
    TO_CHAR(MOD(max(l2.ctime),60),'FM00') AS "Tempo LOCK",
c.username "BLOCKER",c.sid "SID_BLOCKER",c.serial# "SERIAL#_BLOCKER",c.inst_id "ID_inst_BLOCKER",c.sql_id,
b.object_name "OBJ_BLOCKED",b.owner "OWNER_OBJCT",
d.username "SES_BLOCKED" ,d.sid "SID_SES_BLOCKED",d.serial# "SERIAL_SES_BLOCKED",d.inst_id "IDinst_SES_BLOCKED",d.sql_id from gv$locked_object a, dba_objects b, gv$session c, gv$session d,gv$lock l2 where a.object_id=b.object_id and
c.sid=a.session_id and l2.sid=c.sid and c.username is not null and c.sid in (select blocking_session from gv$session) and d.blocking_session is not null
group by c.username,c.sid,c.serial#,c.inst_id,b.object_name,b.object_type,b.owner,d.username,d.sid,d.serial#,d.inst_id,d.sql_id,c.sql_id
order by 1,3,4 asc
/

Can someone help me?

Locks belong to transactions, not SQL statements.

SQL statements do not block other SQL statements on their own. Transactions may block other transactions.

A transaction may consist of multiple SQL statements.

The database can return the current (v$session.sql_id) and previous (v$session.prev_sql_id) SQL statement for a session.

If the blocking session is idle, its sql_id will be null.

If the lock that blocks another transaction was placed by not the current and not the previous SQL, but by one before them, then you are out of luck, you will not find it in such views.

Even if the lock that blocks another transaction was placed by the current or the previous SQL of the blocking session, there is no direct evidence provided about it. You need to examine the SQLs, you need to know what they do, and using that information, decide if really those SQLs placed the lock that blocks another transaction (session).

A simple query like that may provide adequate results for simple cases, but it can not provide a guaranteed answer to what you asked generally.

You can do that with tracing and carefully reading and analyzing the trace files.


MS Access write conflict XMIN at (2^32)/2 -1 I have several clients running access 32 bit with a postgres database backends. A while ago, almost all the access apps started to return "write conflicts" when the end users would make changes to the records. Something intresting we also noticed was the XMIN value for all the records that could not be changed was at (2^32)/2-1 or 2,147,483,647. However, when selected in psql, the values were different. (higher than (2^32)/2-1). I am wondering what recourse I have to fix this issue so the applications will work again.

xmin is internal postgreSQL column determines the transaction ID, When Ms Access doing update for a row it comparing the old values of the row in server with the old values of row in its memory to ensure their are no write conflicts.
psqlODBC offers xmin as long (in Access long has max value of (2^32)/2-1 or 2,147,483,647), so when xmin is the server more than (2^32)/2-1 it will be sent to MS Access as (2^32)/2-1. when you edit the row in MS Access, MS Access sent SQL string like this:
UPDATE table1 SET coln=new_valuen WHERE col1=old_value1 AND col2=old_value2 ... AND xmin= 2147483647

but xmin in the server is not 2,147,483,647 so the transaction failed and MS Access show a write conflict error.
so you have some choices to do:

Don't activate xmin column form psqlODBC driver. Uncheck the highlighted option.

Do update transaction by VBA and pure SQL statements.
Dump your database and remove the postgresql server then reinstall it, restore database, so xmin will be resetted to low numbers.


Error on data node startup in MySQL-cluster Running a 24 core server (with HT enabled) I tried increasing MaxNoOfExecutionThreads to 24 to perform some benchmarks and got the following error:

2013-02-27 11:21:44 [ndbd] INFO     -- Trying to start 12 LQH workers with only 4 log parts, try initial node restart to be able to use more LQH workers.

Here are options from config.ini I find relevant:

NoOfFragmentLogFiles=64

FragmentLogFileSize=128M

It seems that Redo Log file number (not fragment) is always four and they are created in D9-D11 directory on the FS. I haven't been able to find a parameter to change this.

Version running is: 5.5.29-ndb-7.2.10

Any help or experience is welcome.

EDIT - solution:
The following option defines the number of Redo log files - NoOfFragmentLogParts

After setting it to 12, you get D8-D19 for Redo Logs and 24 as MaxNoOfExecutionThreads works fine.

You can try and set

[NDBD DEFAULT]
...

NoOfFragmentLogParts=12
...

http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-ndbd-definition.html#ndbparam-ndbmtd-nooffragmentlogparts

Then restart all nodes with --initial

Best regards

Johan Andersson

Severalnines AB


How can I drop a Memory Optimized Table? Running SQL Server Standard 2016 SP2-CU1.  I am not as concerned with the In memory Filegroup, but I must be able to drop the table objects? Or possibly rename them?

Running:

DROP TABLE [REF].[Work_xxyyzz]

Gives:

  Msg 12332, Level 16, State 111, Line 1
  Database and server triggers on DDL statements CREATE, ALTER and DROP are not supported with memory optimized tables.

There are no user triggers or events on my In Memory Tables.  5 simple tables with primary keys.

There are no dependencies or references to these tables.  They are like temp tables, we name them Work_xxxyyzz tables.  However, they do belong to a database schema.

I am fine to keep the filegroup in place for now.  I want to drop these tables in order to create default objects of the same name.

In addition to the restrictions pointed out in Evan's answer, there are a number of other things that can prevent dropping tables.  Most of them are similar to the restrictions on normal disk-based tables, and you should see them called out in your error message.

For instance, if I have an in-memory table referenced by a natively-compiled (schemabound) stored procedure, I'll get this error if I drop the table:

  Cannot DROP TABLE 'YourTable' because it is being referenced by object 'sp_YourStoredProc'.

The solution in that case is to drop the stored proc first, and then drop the table.

The error message you're getting indicates that there is a server or database level DDL trigger.  You'll need to disable or drop those triggers in order to drop the in-memory table.  You can find any such triggers by running these queries:

USE [master];
GO
SELECT * FROM sys.server_triggers;

USE [YourDatabaseName];
GO
SELECT * FROM sys.triggers;

You should look into what these triggers are doing (auditing, perhaps?).  If it's safe to do so, consider disabling them with the DISABLE TRIGGER statement, then dropping your table, then using ENABLE TRIGGER to turn the triggers back on.

Note that server and database-scoped DDL triggers are different from object level DML triggers (which you mentioned that you checked for and there are none).


delete database and configure database options in DBCA greyed out (disabled) I am using oracle 11g R2 on Linux Redhat 6.3 and  created a lot of databases. At that time there was no problem. But currently I am seeing that in DBCA (Database Configuration Assistant) application some options are disabled as follows:-

1 Create  a Database - ENABLED

2 Configure Database Options - DISABLED

3 Delete a Database  - DISABLED

4 Manage Templates - ENABLED

I don't understand why this happening but in starting all the options were enabled.

EDIT :

oratab file content, inside etc/oratab

**

# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.
# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
Dev:/oracle11gr2/u01/app/oracle/product/11.2.0/db_1:N
Stage:/oracle11gr2/u01/app/oracle/product/11.2.0/db_1:N
Test:/oracle11gr2/u01/app/oracle/product/11.2.0/db_1:N
qa1:/oracle11gr2/u01/app/oracle/product/11.2.0/db_1:N
Dev2:/oracle11gr2/u01/app/oracle/product/11.2.0/db_1:N
Qual:/oracle11gr2/u01/app/oracle/product/11.2.0/db_1:N
fStage:/oracle11gr2/u01/app/oracle/product/11.2.0/db_1:N

**

DBCA relies on /etc/oratab. If that file is empty, or the entry for the database you want to delete is missing, the option(s) will be disabled.


Mysql - show results from 3 tables excluding NULL or EMPTY values for certain fields I have 3 simple tables with polls, users and votes. I'd like to show all polls ordered by clean count of votes (sometimes either poll_id or user_id don't get inserted).

Furthermore I'd like to show a list of users voted, similar to fb (for now my solution is to get that list with new query fetched by ajax call.)

would it be better to create one query to get all results and read them separatelly from array or is it best practice to create separate requests?

My query bellow does get the ordered list but includes the empty or null rows and is very slow for table with >10000 results. any suggestions?

SELECT id,
  title,
  pollid,
  COUNT(pollid) AS clean_count
FROM
(
  SELECT DISTINCT user_id, pollid
  FROM tbl_votes
) AS tmp_tbl
JOIN tbl_polls
  ON tbl_polls.id = tmp_tbl.pollid
WHERE not isnull(tmp_tbl.user_id)
GROUP BY pollid
ORDER BY clean_count DESC

Code to match results of votes with voters:

SELECT DISTINCT fbid, COALESCE(username, 'User not found.')
AS username
FROM tbl_votes JOIN tbl_users
ON tbl_users.fbid = tbl_votes.user_id
WHERE pollid = $band_id AND not isnull(username)

Try this:

SELECT id,
  title,
  pollid,
  COUNT(pollid) AS clean_count,
  group_concat(DISTINCT u.name) AS user
FROM
(
  SELECT DISTINCT user_id, pollid
  FROM tbl_votes
) AS tmp_tbl
JOIN tbl_polls
  ON tbl_polls.id = tmp_tbl.pollid
JOIN tbl_users AS u
ON tmp_tbl.user_id = u.user_id
WHERE not isnull(tmp_tbl.user_id)
GROUP BY pollid
ORDER BY clean_count DESC

This is the sample data that I used:

    CREATE TABLE tbl_polls (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(50)
  );

CREATE TABLE tbl_users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
  );

CREATE TABLE tbl_votes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pollid INT,
  user_id INT,
  INDEX (pollid,user_id),
  FOREIGN KEY (pollid) REFERENCES tbl_polls(id),
  FOREIGN KEY (user_id) REFERENCES tbl_users(user_id)
  );

INSERT INTO tbl_polls (title)
VALUES ('Title 1'),('Title 2'),('Title 3');

INSERT INTO tbl_users (name)
VALUES ('Bob'),('Jack'),('Joe'),('Tom');

INSERT INTO tbl_votes (pollid,user_id)
VALUES (1,2),(1,2),(1,2),(1,3),(2,4),(2,1),(2,4),(2,4),(3,1),(2,1),(1,4);

SQL FIDDLE DEMO

I have used group concat to get the names for your list. Furthermore, to speed up your database have you tried adding index to the foreign keys?

B.T.W. I don't understand your comment that some some votes lack pollid and user_id. If that is the case have a look at this.


Why do database users with no associated login have access to a database? My understanding of SQL security is such that in order to have access to the server, you need a login. To access a database on that server, your login needs to be associated with a user in that database. Over time I have been consolidating security so that developers, QA, etc. are in various roles implemented as active directory groups. Those groups have logins on the server, so individual windows logins are not so prevalent on SQL instances. I grant proper accesses to the databases through these roles.

A common service I provide is to refresh non-production versions of databases from production. I'll do a backup and restore of a production database onto a development server, de-identify the data, rename it, etc.

I have noticed something recently in my environment that is troubling. I was granting access to a key table in production for a development team, when I noticed by using 'fn_my_permissions' that one of the developers has read and write access to the entire production database, rather than being restricted to read-only as I designed. I compared all of this developer's group memberships and found another group he was a member in that was assigned read/write - but only as a database user. There is no associated login  for that group on that SQL server.

It is typical here to copy a 'baseline' production database and repurpose it elsewhere - whether as another production database, or a non-production version. I am starting to find other examples of this anomaly. To sum it up, the problem I see is that when a database is copied to another server, the users in that database that were previously linked to logins on the original SQL instance seem to retain the same level of access on the new SQL instance - even if there is no related login for them on new SQL instance! This strange effect seems limited to users representing windows group logins.

Here is a summary of what I am seeing:
Server1 has a login tied to windows group A, which has been granted read/write to database X.
Database X is then backed up and restored onto Server2, with logins but none related to group A. The database still shows a user for group A, and when I run 'fn_my_permissions' for a user in that group he has read/write to the database.

This particular server is running SQL Enterprise 2012 SP3 with the latest CU. I've seen the same thing on servers running 2008 R2, as well as a current build of SQL 2016.

Why is this happening? And what can I do to ensure that users do not inadvertently circumvent the controls I have put in place? It seems obvious I need to remove all of the unnecessary users after the databases are copied, but why?

It seems obvious I need to remove all of the unnecessary users after
  the databases are copied, but why?

Yes you should remove all the unnecessary users, at least Windows users. And that is why:

For every SQL Server login when creating it, server assings a certain sid(security identifier) that is stored on the server. Every time you map this login to some database, a user with the same sid is created in a database. When you restore this database on another server, unless someone recreates the login with the same sid, it will never reach your database.
Even if the login with the same name already exists on another server, the login's sid is different from user's sid that was restored, so you need to fix this orphan user altering it with corresponding login, to make user's sid equal to corresponding login sid.

The important thing here is that every SQL Server login has only one sid associated with it, and it has access to database only if this sid is also mapped to a database.

Windows logins are different. Server does not create sids for them, it uses Windows access tokens.

When Windows account tries to log on, its sid does not need to be found in sys.server_principals. Instead, any of sids from access token will work as a pass.
Access token contains not only the security identifier (SID) for the user's account but laso SIDs for the groups of which the user is a member.

This means that a situation like yours can be created:

Imagine Windows account acc is a member of 2 Win groups, G1 and G2.

You have 2 servers, S1 and S2 and every group is mapped to only one server:
G1 is a login on S1, G2 is a login only on S2.

You have also a database db1 on S1 where G1 is mapped and has all the permissions on this db1 (let's say G1 is db_owner of db1).

Now you restore db1 on S2.
G1 is still db_owner of db1, but you know that you have no corresponding login on S2, Win group G1 is not mapped to S2, so you think for now you have no db_owners of db1 as the only db_owner was G1 but it cannot reach the server S1.

And now our account acc is logging on server S2.
It can log on to server S2 because it's a member of G2 group. You think that it cannot reach db1 because login G2 is not mapped to db1, but surprise, acc can reach db1 and it is db_owner of db1.

That is because acc is a member of G1. Its token has not only its own SID, it has G1 sid and G2 sid, so G2 sid was used to access a server, and G1 sid was used to access a database.

You can find all the tokens in sys.login_token and sys.database_token, so just impersonate that login (acc):

execute as login = 'dom\acc';
select *
from sys.login_token
where principal_id > 0 -- that is to see only those mapped to server

use db1;
select *
from sys.user_token;


Foreign key constraint on array member? Suppose I have a table containing job roles:

CREATE TABLE roles
(
  "role" character varying(80) NOT NULL,
  CONSTRAINT "role" PRIMARY KEY (role)
);

Suppose I further have a table, users, and each row (a specific user) can have an arbitrary number of job roles:

CREATE TABLE users
(
  username character varying(12) NOT NULL,
  roles character varying(80)[] NOT NULL,
  CONSTRAINT username PRIMARY KEY (username)
);

I should probably make sure that each member of users.roles[] exists in roles.role.  It seems to me that what I want is a foreign key constraint on each member of users.roles[] such that if references roles.role.

This doesn't seem possible with postgres.  Am I looking at this the wrong way?  What is the suggested "right" way to handle this?

Support for array foreign keys was worked on with the goal of getting it into PostgreSQL 9.3, but it didn't make the cut for the release due to performance and reliability problems. It doesn't seem to be being worked on for 9.4.

At this time, you need to stick to the usual relational approach of using a "join table" to model an m:n relationship.

CREATE TABLE user_roles (
   username character varying(12) references users(username),
   "role" character varying(80) references roles("role"),
   PRIMARY KEY(username, "role")
);

I suggest using surrogate keys in this case, too, rather than storing the usernames/role names directly in the join table. The first time you want to rename a user or role you'll be happy you used surrogate keys. Just place a unique constraint on roles."role" and users.username.


While restoring copy of SQL Server 2008 backup file in SQL Server 2008 R2 degraded performance I am trying to upgrade our SQL Server version from SQL Server 2008 to SQL Server 2008 R2. But when I restored a SQL Server 2008 backup file in SQL Server 2008 R2, it's significantly degraded the performance of stored procedures. I took a full back up from old SQL Server 2008 and restored it in new SQL Server 2008 R2. I checked the execution plan of several stored procedures to make sure no indexes are missing, but everything is same.

Can anyone please give me an advice about it if I need to do any further steps to get better performance on it?

Thanks,
Dony

The odds are that on the restore, it is appropriate to update statistics on the db. In many cases, this step alone will help.

If it does not help, it may be that index fragmentation crept up and became an issue now, though my $ is on statstics...


Why am I getting ERROR: duplicate key value violates unique constraint "movies_pkey' So, I'm creating a database for one of my classes using pg admin. I got all the data in no problem but my one issue is this one error that I don't really understand.

ERROR: duplicate key value violates unique contraint "movies_pkey"
DETAIL: key(movie_num)=20 already exists

The above is the error I am getting.

-- DROP'ping tables clear out any existing data
DROP TABLE IF EXISTS movies;
DROP TABLE IF EXISTS actors;

-- CREATE the table, note that id has to be unique, and you must have a name
CREATE TABLE actors(
    id INTEGER PRIMARY KEY,
    name VARCHAR(20) NOT  NULL
);

INSERT INTO actors(id, name) VALUES(
    1,
    'Sean Connery');
INSERT INTO actors(id, name) VALUES(
    2,
    'George Lazenby');
INSERT INTO actors(id, name) VALUES(
    3,
    'Roger Moore');
INSERT INTO actors(id, name) VALUES(
    4,
    'Timothy Dalton');
INSERT INTO actors(id, name) VALUES(
    5,
    'Pierce Brosnan');

INSERT INTO actors(id, name) VALUES(
    6,
    'Daniel Craig');

-- CREATE the table, note movie_num is unique and title and year are required fields, though the actor can be optionally empty
CREATE TABLE movies(
    movie_num INTEGER PRIMARY KEY,
    title VARCHAR(35) NOT NULL,
    actor INTEGER,
    year INTEGER NOT NULL
);

INSERT INTO movies(movie_num, title, actor, year)
VALUES(
    2,
    'From Russia With Love',
    1,
    1963);
INSERT INTO movies(movie_num, title, actor, year)
VALUES(
    3,
    'Goldfinger',
    1,
    1964);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    4,
    'Thunderball',
    1,
    1965);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    5,
    'You Only Live Twice',
    1,
    1967);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    6,
    'On Her Majesty''s Secret Service',
    2,
    1969);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    7,
    'Diamonds Are Forever',
    1,
    1971);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    8,
    'Live and Let Die',
    3,
    1973);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    9,
    'The Man With The Golden Gun',
    3,
    1974);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    10,
    'The Spy Who Loved Me',
    3,
    1977);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    11,
    'Moonraker',
    3,
    1979);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    12,
    'For Your Eyes Only',
    3,
    1981);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    14,
    'A View To A Kill',
    3,
    1985);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    15,
    'The Living Daylights',
    4,
    1987);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    16,
    'License To Kill',
    4,
    1989);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    18,
    'Tomorrow Never Dies',
    5,
    1997);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    20,
    'Die Another Day',
    5,
    2003);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    1,
    'Dr. No',
    1,
    1962);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    13,
    'Octopussy',
    3,
    1983);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    17,
    'Goldeneye',
    5,
    1995);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    19,
    'The World Is Not Enough',
    5,
    1999);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    20,
    'Casino Royale',
    6,
    2006);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    21,
    'Quantum of Solace',
    6,
    2008);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    22,
    'Skyfall',
    6,
    2012);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    23,
    'Spectre',
    6,
    2015);

And this is the database I created.

Any help is much appreciated.

You cannot have duplicate keys on primary key column i.e movie_num=20
 Change Movie Die Another Day with movie_num =20 to 24

   -- DROP'ping tables clear out any existing data
    DROP TABLE IF EXISTS movies;
    DROP TABLE IF EXISTS actors;

    -- CREATE the table, note that id has to be unique, and you must have a name
    CREATE TABLE actors(
        id INTEGER PRIMARY KEY,
        name VARCHAR(20) NOT  NULL
    );

    INSERT INTO actors(id, name) VALUES(
        1,
        'Sean Connery');
    INSERT INTO actors(id, name) VALUES(
        2,
        'George Lazenby');
    INSERT INTO actors(id, name) VALUES(
        3,
        'Roger Moore');
    INSERT INTO actors(id, name) VALUES(
        4,
        'Timothy Dalton');
    INSERT INTO actors(id, name) VALUES(
        5,
        'Pierce Brosnan');

    INSERT INTO actors(id, name) VALUES(
        6,
        'Daniel Craig');

    -- CREATE the table, note movie_num is unique and title and year are required fields, though the actor can be optionally empty
    CREATE TABLE movies(
        movie_num INTEGER PRIMARY KEY,
        title VARCHAR(35) NOT NULL,
        actor INTEGER,
        year INTEGER NOT NULL
    );

    INSERT INTO movies(movie_num, title, actor, year)
    VALUES(
        2,
        'From Russia With Love',
        1,
        1963);
    INSERT INTO movies(movie_num, title, actor, year)
    VALUES(
        3,
        'Goldfinger',
        1,
        1964);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        4,
        'Thunderball',
        1,
        1965);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        5,
        'You Only Live Twice',
        1,
        1967);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        6,
        'On Her Majesty''s Secret Service',
        2,
        1969);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        7,
        'Diamonds Are Forever',
        1,
        1971);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        8,
        'Live and Let Die',
        3,
        1973);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        9,
        'The Man With The Golden Gun',
        3,
        1974);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        10,
        'The Spy Who Loved Me',
        3,
        1977);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        11,
        'Moonraker',
        3,
        1979);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        12,
        'For Your Eyes Only',
        3,
        1981);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        14,
        'A View To A Kill',
        3,
        1985);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        15,
        'The Living Daylights',
        4,
        1987);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        16,
        'License To Kill',
        4,
        1989);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        18,
        'Tomorrow Never Dies',
        5,
        1997);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        24,
        'Die Another Day',
        5,
        2003);  -- changed movie_num from 20 to 24*/
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        1,
        'Dr. No',
        1,
        1962);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        13,
        'Octopussy',
        3,
        1983);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        17,
        'Goldeneye',
        5,
        1995);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        19,
        'The World Is Not Enough',
        5,
        1999);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        20,
        'Casino Royale',
        6,
        2006);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        21,
        'Quantum of Solace',
        6,
        2008);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        22,
        'Skyfall',
        6,
        2012);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        23,
        'Spectre',
        6,
        2015);


Partitioning not working need primary key? We are trying some partitioning as below.

ALTER TABLE tblreceipt PARTITION BY RANGE (MONTH(receiptDate))
(
PARTITION p1 VALUES LESS THAN (TO_DAYS('2012-10-20')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2012-10-21')),
PARTITION p3 VALUES LESS THAN (TO_DAYS('2012-10-22'))
);

but end up with this Error Code: 1503
A PRIMARY KEY must include all columns in the table's partitioning function

I have done the changes now.

ALTER TABLE tblreceipt PARTITION BY RANGE (receiptDate)
(
PARTITION p1 VALUES LESS THAN '2012-10-20',
PARTITION p2 VALUES LESS THAN '2012-10-21',
PARTITION p3 VALUES LESS THAN '2012-10-22'
);

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2012-10-20',
PARTITION p2 VALUES LESS THAN '2012-10-21',
PARTITION p3 VALUES ' at line 3

The problem is that the function (MONTH()) and the LESS THAN values are incompatible.

Apart from the fact that I don't see the value of using TO_DAYS() (what is the advantage there compared to a simple date?), you should use (TO_DAYS(receiptDate)) instead of (MONTH(receiptDate)).

Or, more simply (if you want a day-by-day partitioning), I'd write just

RANGE COLUMNS (receiptDate)
...
LESS THAN ('2012-10-20')

See a very simple working example at SQLFiddle.


Can I change table structure in a transaction and then roll it back if there is an error? I have some ALTER TABLE statements that I am running. Not all of them work (they are the result of running SQL Data Compare) and I want to group them in some transactions and roll back the statements if something goes wrong.

Is this possible, or is it only data that can be rolled back?

Yes, this is possible.

Most DDL statements can be rolled back in SQL Server (There are a few exceptions such as CREATE DATABASE)


Need FLOAT datatype with 2 digits after decimal point when importing from a CSV file I am inporting a CSV file into SQL Server using the import/export wizard. The data in the CSV file looks like this:

...0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,164051.00,...

All these values are dollar amounts.
Since this is packaged finance software (JD Edwards 9.2 to be more precise), I have to live with the data type for all columns being FLOAT and no, I can't change it.

After importing using the wizard, the table has 0 and 164051 respectively in those columns.

When running the pre-packaged JDE finance report, it contains 0 and 1640.51 as the values, i.e. it interprets the last two digits as "cents" and there is nothing I can do about it. Not a problem if the value is 0, but an issue for everything else.

So my only option would be to add two trailing zeroes to the column values after the import. Is there any way this can be done?

If the dollar amount is 16578.98 in the CSV file, it gets imported fine and shows up correctly in the report. The problem is only with even dollar amounts or amounts like 5.80 or 6.10.

Any ideas?

If the data is only used for this JDEdwards report, you could simply convert from dollars (164051, 0, 16578.98) to cents (multiply by 100) (16405100, 0, 1657898).

If the data is used in its correct form properly elsewhere, create a view that converts from dollars to cents, and have the JDE report use the view.

If you ever get fractional cents ($12358.9275), then you'd want to round to remove the fractional part. However, I believe that would be an issue now (it'd be reported as $1235892.75, from what you're saying).


Constraint to one of the primary keys as foreign key Table1: grid_col (col_id,f_id,f_value)
    (col_id,f_id) is primary key.

Table2: grid (grid_id,col_id,text)
    (grid_id) is primary key.

I want to have a constraint for grid that, col_id should be present in grid_col. I can't have foriegn key constraint here.
I can create a function constraint which scans the grid_col while inserting in grid but in that case it increases the chances of having deadlock.
How to add a constriant here?

You could create a TRIGGER on the grid table that will do an outer join on the inserted values and the grid_col table.This should give you NULL values for non-matched records which you can than remove from the grid table.


Is it possible to configure pgbouncer to distribute queries among master and slave? We have master (write-read) and slave (readonly) Postgres 10 databases. Slave is online replication from master.

Now our application (c# webapi) connects to master only directly. We want to execute all SELECT queries on slave to distribute load among our hardware.

Idea is add some connection pooler that will pass all SELECTS on slave, other queries pass to master
If replication falls down, it will detect that automatically ASAP and pass all SELECTS to master till replication online again.

Is pgbouncer ok for that? Some another tool?

pgBouncer cannot do that, and indeed it is a questionable feature. How can you reliably tell that a statement like

SELECT pg_create_physical_replication_slot('aname');

won't modify your database?

There is pgPool which boasts such a feature, you might investigate it.

The better technique is usually for your application to use two database connections: one for reading and one for data modifications. That doesn't put you at the mercy of a tool that tries to guess.


SQLCMD does not print Rows affected in log file I am calling stored procedure using SQLCMD which will insert data into multiple tables. And output of that procedure is captured in log file using -o. But, i can't see Row(s) affected any where in the log file. And same when I am executing procedure I am getting Row(s) affected.

Is there any other way to print Row(s) affected in log file along with print statement. I have tried using SET NOCOUNT OFF as well but no use.

I'm not sure if this is your problem, but check your 'Connections' options for your Sql Server instance.

Using Sql Server Management Studio, right-click your instance and select 'Properties'.  Then select 'Connections'.  Under 'Default connection options', scroll down and make sure that 'no count' is NOT checked.


JDBC Exception JZ0BE: BatchUpdateException: A user with System Administrator (SA) role must reconfigure the system to enable Java I am using Sybase JDBC JConnect3 driver to insert large data in a table using Batch Insert feature of JDBC.
I am preparing a batch of 100 inserts and executing the batch. But it is throwing below error while executing batch query.

  com.sybase.jdbc3.jdbc.SybBatchUpdateException: JZ0BE: BatchUpdateException: Error occurred while executing batch statement: Cannot run this command because Java services are not enabled. A user with System Administrator (SA) role must reconfigure the system to enable Java.
   at com.sybase.jdbc3.jdbc.ErrorMessage.raiseBatchUpdateException(Unknown Source)
         at com.sybase.jdbc3.jdbc.SybStatement.handleBatchException(Unknown Source)
         at com.sybase.jdbc3.jdbc.SybPreparedStatement.executeBatch(Unknown Source)
         at com.loader.LoadTable.loadDataToOracleFirst(LoadTable.java:367)
         at com.loader.LoadTable.fetchDataFromSybaseFirst(LoadTable.java:246)
         at com.loader.LoadTable.main(LoadTable.java:157)

Can anyone tell me excatly what I need to configure to resolve above issue?

Start by trying:

sp_configure "enable java",1

...and restarting the service if necessary. There may be more to it than that, please provide details of your server product, version, and anything else that might be helpful.


What are the common reasons for a Subscription to be deactivated in SQL Server 2005? I am trying to manage database replication in SQL Server 2005.  I have set up my publications, distributors and subscriptions and it seems to work properly at the start.

However, after a period of time, my subscription becomes deactivated, and requires reinitialization.

This is transactional publication, with a non-updatable, pull subscription.  The publication has the "Subscription Expiration" option "Subscriptions never expire, but they can be deactivated until they are reinitialized" selected.  The publishers and subscribers are on the same LAN.

What are the common reasons for a subscription to be deactivated?

For instance, could an insert failure on the subscriber like the following (due to badly handled schema change) cause a subscription to become deactivated?

Cannot insert explicit value for identity column in table 'SOME_TABLE' when
IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number: 544)

If this can cause a subscription to be deactivated, what are some other scenarios to look out for?

Yes that would be the cause.  When the insert fails, the replication will then fail.  Once the replication is in a failed state you've got to fix the problem within a few days before the subscription will expire and you'll need to reinit it.


Database peformance optmization We have a very big table which has around 250 million records. The table is growing bigger as every second many records are inserted. There is no update/delete operation done in this table. The table has structure similar to following sample structure

CREATE TABLE `sample_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `BatchNo` varchar(12) DEFAULT NULL,
  `LotNo` varchar(12) DEFAULT NULL,
  `ModelNumber` varchar(20) DEFAULT NULL,
  `Cost` decimal(4,2) DEFAULT NULL,
  `QualityPercentage` tinyint(3) unsigned DEFAULT NULL,
  `Type` varchar(5) DEFAULT NULL,
  `Vendor` varchar(5) DEFAULT NULL,
  `Send_Time` datetime DEFAULT NULL,
  `InsertTime` int(10) unsigned NOT NULL,
  `VendorRating` tinyint(3) unsigned DEFAULT NULL,
  `ProductType` tinyint(4) DEFAULT NULL,
  `SerialNumber` varchar(4) DEFAULT NULL,
  `ItemID` int(11) DEFAULT NULL,
  `BrandID` int(11) DEFAULT NULL,
  `CategoryID` int(11) DEFAULT NULL,
  `SubTitle` varchar(10) NOT NULL DEFAULT 'gl200',
  `Extras` text,
  PRIMARY KEY (`id`),
  KEY `ModelNumber` (`ModelNumber`),
  KEY `SendTime` (`Send_Time`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

Problem

The performance is slower, specially when we select to fetch records of bigger date range (Send_Time). Like if we try to fetch records from One years before to Now, it takes forever before mysql crashes.

We consulted with DBA and he said, as indexes keeps on changing every time as each minute many many records are inserted, the fetching will be slow, so he suggested -

Don't read and write from/to same database table "sample_table".
Create another database table, say "sample_fetch_table" with same structure as "sample_table"
"sample_table" will only be used to insert records from now onwards.
At night when write frequency is very low, fetch last 24 hours record from "sample_table" and insert them into "sample_fetch_table". Then delete inserted records from "sample_table".
Now use "sample_fetch_table" table to select and display records to webpage.
Archive X years old records to another archive table.

UPDATE

The common query we run is:

SELECT *
FROM sample_table
WHERE (    ModelNumber = "12890"
       AND Send_Time > FROM_UNIXTIME(1556515028) AND Send_Time < FROM_UNIXTIME(1556661540))
       AND (   (    Vendor in('11','12')
                AND ItemID = 31
                AND SubTitle LIKE 'ABC-123'
               )
            OR (    Vendor in('ABTU','RAST','RAMT','ABRT','ABNM')
                AND UPPER(SubTitle) IN('RA200','PR55','XY100','TW20','D1EV','FR091','FR093','FRA12','AB23')
               )
           )
       AND BatchNo != 0
       AND LotNo != 0
 ORDER BY Send_time

Explain of above query

id = 1
select_type =  SIMPLE
table = sample_table
type = range
possible_keys = ModelNumber, SendTime
key = SendTime
key_len = 6
ref = (Null)
rows =  202042
Extra = Using index condition; Using where

We are bit skeptical about this suggestion. Is this optimal solution?? Is there any other better alternatives??

Considering the query and the table size, this looks like a good candidate for partitioning on the Send_Time column.


Why evaluates "IS NULL" in SSMS interactive mode to "TRUE" but the same statement being executed in a SP to "FALSE"? Given

DECLARE @IN__CUST_NO    INT
DECLARE @OUT__USER_ID   INT
SET @IN__CUST_NO = 1000006660

CASE 1: IS NULL evaluates TRUE

Since no CUSTOMER_NUMBER with the value of 1000006660 exists in our dummy USERS-Table, stating a SELECT restricting to tuples of CUSTOMER_NUMBER-Columnvalues of 1000006660 leads to a zero-sized resultset. Hence IS NULL evaluates to TRUE in following statement issued to SSMS interactivly; resulting to PRINT 'null'

SELECT @OUT__USER_ID = u.ID
FROM USERS u
WHERE u.CUSTOMER_NUMBER = @IN__CUST_NO

IF (@OUT__USER_ID IS NULL)
BEGIN
    PRINT 'null'
END
ELSE
BEGIN
    PRINT 'set'
END

CASE 2: IS NULL evaluates FALSE

As above no CUSTOMER_NUMBER with the value of 1000006660 exists. But if one encapsulates the IS NULL part in a stored procedure like below and executes it, IS NULL will result to FALSE! (at least on my configuration; using SQL-Server 2005 and SSMS 2008). Endresult: PRINT 'set'

CREATE PROCEDURE SP_NULL_TEST
    @IN__CUST_NO        INT,
    @OUT__USER_ID       INT OUTPUT
AS
BEGIN
    SELECT @OUT__USER_ID = u.ID
    FROM USERS u
    WHERE u.CUSTOMER_NUMBER = @IN__CUST_NO

    IF (@OUT__USER_ID IS NULL)
    BEGIN
        PRINT 'null'
    END
    ELSE
    BEGIN
        PRINT 'set'
    END
END

EXEC SP_NULL_TEST @IN__CUST_NO, @OUT__USER_ID=@OUT__USER_ID

Please, can someone explain this strange behaviour? Many thanks in advance.

Initialize @OUT__USER_ID with NULL, since it is probably being set to a non-null value earlier in your script somewhere.


Painless way to create a clustered index on a huge table? So we have a customer site that is complaining about some seriously slow performance.  I took one look and it's obvious that the problem is because Somebody Else (grrrr) designed a table holding some 20 million-plus records without a clustered index.

Now I want to create a clustered index on that table - but in my test environment my create index command has been running for an hour and it's still not done.  The customer site is a shop floor that works 24/7, and cannot afford an hour of down time while I create an index.

Is there some less brute-force method of creating the index that will either finish the job quickly, or do it in some smart way that will not totally kill the server's performance while it's busy?

We are using SQL Server Enterprise Edition.

If your sql server is Enterprise+ edition and table has no any BLOB fields - CREATE CLUSTERED INDEX ... WITH(ONLINE=ON)
If not - there is no any way except to create the table with the same schema on side and wisely transfer data to it, including all INSERT/UPDATE/DELETE operations (using trigger, for example), and then accurately drop old table and rename new to the same name as old - cons are: slow, adds extra load to server and storage