Why does a CTE cause a full table scan? UPDATE

I've posted a newer more focused question How to use index for simple time range join?
I'll keep this for reference.

ORIGINAL

I have a large (~100 million rows) timeseries table t_16 where the primary key is a field abs_date_time of type timestamp.

I want to join this table to a small (<1000 rows) ranges table, which only has one field range of type tsrange.

In the production example, range is not really a table, but a CTE (i.e. a query in the WITH clause.
I managed to reproduce the issue with a "real" table, like so:

For my tests I create a new ranges table and insert only one range.

create table ranges
(
    time_range tsrange
);

In this case the query is very slow: it makes a full table scan of the timeseries table:

EXPLAIN
SELECT t_16.*
FROM t_16, ranges
WHERE (abs_date_time >= LOWER(ranges.time_range)
    AND abs_date_time <  UPPER(ranges.time_range));

Explain output - note the Seq Scan on t_16:

Nested Loop  (cost=0.00..2645938802.76 rows=13056002418 width=80)
  Join Filter: ((t_16.abs_date_time >= lower(ranges.time_range)) AND (t_16.abs_date_time < upper(ranges.time_range)))
  ->  Seq Scan on t_16  (cost=0.00..2098286.16 rows=86400016 width=80)
  ->  Materialize  (cost=0.00..30.40 rows=1360 width=32)
        ->  Seq Scan on ranges  (cost=0.00..23.60 rows=1360 width=32)

When I now analyse the ranges table and run the query again, the index is used and the query is fast:

analyse ranges;

EXPLAIN
SELECT t_16.*
FROM t_16, ranges
WHERE (abs_date_time >= LOWER(ranges.time_range)
    AND abs_date_time <  UPPER(ranges.time_range));

Explain result: note that t_16_pkey is used:

Nested Loop  (cost=127355.89..3147692.66 rows=9600002 width=80)
  ->  Seq Scan on ranges  (cost=0.00..1.01 rows=1 width=22)
  ->  Bitmap Heap Scan on t_16  (cost=127355.89..3051691.63 rows=9600002 width=80)
        Recheck Cond: ((abs_date_time >= lower(ranges.time_range)) AND (abs_date_time < upper(ranges.time_range)))
        ->  Bitmap Index Scan on t_16_pkey  (cost=0.00..124955.89 rows=9600002 width=0)
              Index Cond: ((abs_date_time >= lower(ranges.time_range)) AND (abs_date_time < upper(ranges.time_range)))

Questions:

why is a full table scan used (before the analyse)? Since the join is only on the primary key field, I hoped that this was always used.
Is there a way that I can rewrite the query, so that the index is used, even without analyse (which I cannot use in a CTE)?

Additional Info:

Here is a version that actually uses a CTE:

EXPLAIN
WITH cte AS (
    select * from ranges
)
SELECT t_16.*
FROM t_16, cte
WHERE (abs_date_time >= LOWER(cte.time_range)
    AND abs_date_time <  UPPER(cte.time_range));

This also does not use the index (before analyse), but makes a full-table scan ont the time-series-table.
Explain output:

Nested Loop  (cost=23.60..3527218976.16 rows=13056002418 width=80)
  Join Filter: ((t_16.abs_date_time >= lower(cte.time_range)) AND (t_16.abs_date_time < upper(cte.time_range)))
  CTE cte
    ->  Seq Scan on ranges  (cost=0.00..23.60 rows=1360 width=32)
  ->  Seq Scan on t_16  (cost=0.00..2098286.16 rows=86400016 width=80)
  ->  CTE Scan on cte  (cost=0.00..27.20 rows=1360 width=32)

You are making unwarranted assumptions here.  You don't need an ANALYZE for a CTE, a CTE will use the stats of the base table(s), not its own stats.

However, before version 12, CTEs are optimization "fences". The CTE will always run to completion almost as if it were an entirely separate query.


Confirm that my.cnf file has loaded OK Does anyone know if there is a way to confirm if any my.cnf file is being read by MySQL?

I only have the one my.cnf on the machine (and there are no my.ini files) which I have in /etc/my.cnf.

When I run ./mysqld --help --versbose, it doesn't give me the variable I have set in the file, so my assumption was it wasn't loading it or that it was loading a different file.

I tested this by setting my.cnf to 777 and running the command again, this time I get a warning saying the file won't be loaded because it is world writeable.

Is this enough to imply the my.cnf has been loaded and perhaps there is an error in it, causing the variables not to be set or is there some other way of knowing for sure what (if any) my.cnf file is loaded?

If there's only one my.cnf file, and it's  /etc/my.cnf, then that's the one it loads. From the manual:

On Unix, Linux and Mac OS X, MySQL programs read startup options from the following files, in the specified order (top items are used first).
/etc/my.cnf   Global options
SYSCONFDIR/my.cnf Global options
$MYSQL_HOME/my.cnf    Server-specific options
defaults-extra-file The file specified with --defaults-extra-file=path, if any
~/.my.cnf User-specific options (in your home directory)

So, if you check the other directories and there are no my.cnf files, you can be certain it's loading from /etc/my.cnf.
If you use MYSQLAdministrator, supposedly you can get it from Service Control->Configuration File->Config filename,  as per this post.


How and what do you backup for a simple Oracle DB? Background:

I am in the following situation: We have a JIRA instance running on a dedicated server machine (Windows Server). It uses Oracle XE to store all the data. The JIRA instance and a  Oracle XE install for the data are both running on this one server box. JIRA does support doing cyclic export to XML, and we export this "XML dump" to a network share twice a day. But due to performance implications, we would like to replace this no-brainer solution with a (hopefully) no-brainer database backup.

Question:

Now, this question is not about the technical details, I can look them up easily enough. This question is about cutting through the blabla and hopefully getting to the point :-)

So I have this very simple one-user database on a single server and single harddisk, and want to do a backup to a network share:

What do I need to back up so that the database is recoverable if the machine breaks.

Is an "export" enough, do I need some setting files, etc?
Essentially, what does constitute a "full" backup, so that the database + instance can be reconstructed by a fairly inexperienced Oracle user, even if the instance saw some undocumented tweaking.

How do I backup, as in:

Which tools should I use to do the backup.
Should I try to get a live/continuous backup, or just do periodic "exports".
Should the backup be "initiated" by the DB or by an external script?

So basically, I have a simple setup. The database was simple to install. How do I backup this so that it'll be simple to maintain and simple to restore?

Hope this is not too generic or noobish for this site.

Use RMAN to back up to the network share. An export is NOT a true backup as you cannot perform a point-in-time recovery with it. XE comes with backup scripts that you can just schedule with AT or similar, the documentation says:

  To schedule automatic backups, use any operating system or third party
  task scheduling software to run the supplied backup script for your
  platform

If you are running the database in archivelog mode, then the archived redo logs are your "continuous export", so you would ideally want to have those on seperate disks from the DBFs, and hopefully mirrored. RMAN will back those up too. As you say you have a single disk, well, this is a judgement call you will need to make - I would say if the data is valuable enough to back up, it is valuable enough to run on server-grade hardware.


Copying Partitioned table data to another table on different SQL Server We have a partition table on Production environment with 2338117620 rows and 206 partitions. Now we have to copy only last 2 year data in Test environment.

But the problem is that , Import Export utility does not copy partition tables the same way in target database even if we have created the same partition reference on the target database.

So, now we have only one option left is that of Bulk-copy. But not sure if
bulk copy supports exporting partition data to a target partitioned table.
Do let me know your thoughts on it.

BCP works fine.

First, you create the target table.
Then export data with the select and filter you need.
Import data with BCP will fit the correct partitions.

I did this before. I was on SQL 2014, but it will be fine on 2012.


Can I prevent SQL 2008 Server Management Studio's intellisense from autocompleting with the longest word? In SQL 2008 Server Management Studio: is there a way to keep intellisense from always picking the longest word to autocomplete?  For example, I could be trying to type the word client and it'll autocomplete with clientdetails.

I find it easier to just disable IntelliSense, the more I use it, the less I like it. Although before I tried it, I loved it :)

Just better to learn to hand-code things correctly. Mostly IntelliSense intefere's with that


MongoDB Update specific entries in an array {
    "_id" : "abcd",
    "arrayItems" : [
        {
            "name" : "Peter",
            "age" : 23,
            "profession" : "Student"
        },
        {
            "name" : "Paul",
            "age" : 80
            "profession" : "Student"
        },
        {
            "name" : "Mary",
            "age" : 45
            "profession" : "CEO"
        }
    ]
}

Is it possible to update in a single step?
Peter-> "age" : 27 and Paul -> "age" : 23

In single steps I could do something like this

db.students.update({'_id':'abcd', 'arrayItems.name': 'Peter'}, {$set:{'arrayItems.$.age': 27}});

The short answer here is no, at least if you want to match and not just set specific elements (rather than looking to match multiple elements), something like this:

> db.students.findOne()
{
    "_id" : "abcd",
    "arrayItems" : [
        {
            "name" : "Peter",
            "age" : 23,
            "profession" : "Student"
        },
        {
            "name" : "Paul",
            "age" : 80,
            "profession" : "Student"
        },
        {
            "name" : "Mary",
            "age" : 45,
            "profession" : "CEO"
        }
    ]
}
> db.students.update(
    { "_id" : "abcd"},
    { $set: {"arrayItems.0.age": 27, "arrayItems.1.age" : 23 }}
  );
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.students.findOne()
{
    "_id" : "abcd",
    "arrayItems" : [
        {
            "name" : "Peter",
            "age" : 27,
            "profession" : "Student"
        },
        {
            "name" : "Paul",
            "age" : 23,
            "profession" : "Student"
        },
        {
            "name" : "Mary",
            "age" : 45,
            "profession" : "CEO"
        }
    ]
}

Unfortunately, unlike this simple test, with real usage there is no guarantee that the array you are updating has the structure you think it does, even if you fetch it immediately beforehand there is always the chance that someone updates between the read and your update.

In order for this to work you need SERVER-1243 to be implemented, and that is not currently planned for an imminent release. Here is the current statement on that lack of capability from MongoDB (as of Jun 2016) from that ticket:

  MongoDB appreciates the challenge of maintaining applications
  utilizing schemas with large arrays, especially with respect to
  updating many or all array elements, and we very much recognize the
  interest from the community around this ticket.

  Unfortunately, to implement such a major feature there are serious
  requirements:

  a specification for new language features (like update modifiers or    expressions), since we cannot break existing uses
  should be included with support to match all array elements, as well was those matching a query
  requests for support to update the last element(s) could be considered.
  must support all existing update modifiers (correctly, and in a non-backwards-breaking way) $rename, $set, $unset, $pull, $push,
  $bit...
  must work efficiently with all arrays, including those having thousands of elements
  cannot change current update semantics or behaviors which existing applications and deployments depend on (non-backwards-breaking).

  In summary, adding this as a new feature, or improvement, is not
  trivial. It will require resources which are currently working on
  other projects, and in short is a matter of prioritization over other
  parts of the whole server.

As you can infer from the age of the ticket and the lengthy discussion, this has come up many times and there are plenty of workarounds in the ticket and elsewhere but (as yet) there is no built-in method to achieve this.


Get top SQL Queries that aren't stored procs/functions/triggers Afternoon,

I need way to get the query stats for queries that aren't procedures/functions/triggers.

sys.dm_exec_query_stats only appears to contain these, i need to gather similar stats that are in this dmv but for just normal queries submitted by ssms/EF/Web etc.

This is what i used and it returns no results. (not the most efficient but just a quick proof of concept)

SELECT txt.text
   last_execution_time,
   execution_count,
   total_elapsed_time,
   last_elapsed_time,
   min_elapsed_time,
   max_elapsed_time
   FROM sys.dm_exec_query_stats
   CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS txt
   WHERE
   txt.text NOT LIKE '%CREATE PROC%'
   AND txt.text NOT LIKE '%CREATE FUNC%'
   AND txt.text NOT LIKE '%CREATE TRIGG%'
   AND txt.dbid = DB_ID('$(DB)')

I know there are several queries submitted regularly from client apps that don't use stored procs and take a long time to execute. I would have thought SQL keeps a record of these somewhere.

There's an easy (and free!) way to do that using sp_BlitzCache (full disclosure, I contribute to this OSS).

You can run it with the @QueryFilter option (which can also get only stored procs, and only functions (but functions are only 2016+).

EXEC master.dbo.sp_BlitzCache @QueryFilter = 'statement'

That will give you your top 10 statements by overall CPU. If you want to order by other metrics, you can use the @SortOrder parameter to go by reads, duration, executions, and more.

Hope this helps!


Find tables and views that are not in use at all? I did not find any similar question here, so please direct me if I'm wrong.

I have a large database (inherited from former employee) and I need to clean up that database.

How do I find all tables and views which are not used at all in database?

I found a query at SQL Server script to know when the table accessed last time (CALSQL | SQL Server Community Blog):

with cte_recent as
(
select SCHEMA_NAME(B.schema_id) +'.'+object_name(b.object_id) as tbl_name,
(select MAX(last_user_dt) from (values (last_user_seek),(last_user_scan),(last_user_lookup)) as all_val(last_user_dt)) as access_datetime FROM sys.dm_db_index_usage_stats a
right outer join sys.tables b on a.object_id =  b.object_id
)
select tbl_name,max(access_datetime) as recent_datetime  from cte_recent
group by tbl_name
order by recent_datetime desc , 1

I have used a process from this article by spaghettidba (Gianluca Sartori):

Tracking Table Usage and Identifying Unused Objects

To summarize very briefly:

  Search your codebase
  Use the index usage stats DMV
  Use Extended Events to monitor object access, streaming the events with Powershell

Gianluca provides many code examples in his article.

There is still a lot of risk

After collecting data for 3 months. I have renamed unused table to TBD and then finally removed after 6 - 8 months. Good luck.


ERROR: invalid XML content, when Select unnest(xpath(... on a text column I have a text column named big_xml in my table which has the following value:

<?xml version=1.0 encoding=UTF-8?>
<dpid:DpidDatabase xmlns:dpid=http://ddex.net/xml/dpid/11 xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance xsi:schemaLocation=http://ddex.net/xml/dpid/11 http://ddex.net/xml/dpid/11/dpid.xsd>
 <DpidOwner SequenceNumber=1>
  <DPID>PADPIDA2006111001O</DPID>
  <CompanyName>234AG</CompanyName>
  <Address>Riedtlistrasse 23, Zürich, 8006, CH</Address>
 </DpidOwner>
 <DpidOwner SequenceNumber=2>
  <DPID>PADPIDA2007011501Q</DPID>
  <CompanyName>OpenIMP</CompanyName>
  <Address>8-10 Rhoda Street, London, E2 7EF, UK</Address>
 </DpidOwner>
 <DpidOwner SequenceNumber=3>
  <DPID>PADPIDA2007040501K</DPID>
  <CompanyName>The Harry Fox Agency</CompanyName>
  <Address>711 Third Avenue, 8th Floor, New York, 10017, USA</Address>
 </DpidOwner>
</dpid:DpidDatabase>

Execute query:

SELECT
  unnest((xpath('/dpid:DpidDatabase/DpidOwner',  table1.big_xml::xml,ARRAY[ARRAY['dpid', 'http://ddex.net/xml/dpid/11']]))) AS SequenceNumber
  ,unnest((xpath('/dpid:DpidDatabase/DpidOwner/DPID',  table1.big_xml::xml,ARRAY[ARRAY['dpid', 'http://ddex.net/xml/dpid/11']]))) AS DPID
  ,unnest((xpath('/dpid:DpidDatabase/DpidOwner/CompanyName',  table1.big_xml::xml,ARRAY[ARRAY['dpid', 'http://ddex.net/xml/dpid/11']]))) AS CompanyName
  ,unnest((xpath('/dpid:DpidDatabase/DpidOwner/Address',  table1.big_xml::xml,ARRAY[ARRAY['dpid', 'http://ddex.net/xml/dpid/11']]))) AS Address
FROM table1_20190828

Error displayed:

ERROR: invalid XML content
 Detail: line 1: XML declaration allowed only at the start of the document
<?xml version=1.0 encoding=UTF-8?>
        ^
1 statement failed.

Basically double-quote all attribute values.

You can read about it in this tutorial.

I used an on-line xml validator and this is the result:

  The value following "version" in the XML declaration must be a quoted string.

Change:

<?xml version=1.0 encoding=UTF-8?>

by

<?xml version="1.0" encoding="UTF-8"?>

  Open quote is expected for attribute "xmlns:dpid" associated with an element type "dpid:DpidDatabase".

Change:

<dpid:DpidDatabase xmlns:dpid=http://ddex.net/xml/dpid/11 xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance xsi:schemaLocation=http://ddex.net/xml/dpid/11 http://ddex.net/xml/dpid/11/dpid.xsd>

by

<dpid:DpidDatabase xmlns:dpid="http://ddex.net/xml/dpid/11" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://ddex.net/xml/dpid/11 http://ddex.net/xml/dpid/11/dpid.xsd">

  Open quote is expected for attribute "SequenceNumber" associated with an element type "DpidOwner".

Change all occurrences of:

<DpidOwner SequenceNumber=XX>

by double quoting SequenceNumber values

<DpidOwner SequenceNumber="1">

You can check the result db<>fiddle here

sequencenumber                                                                                | dpid                            | companyname                                     | address
:----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :------------------------------ | :---------------------------------------------- | :---------------------------------------------------------------------------------------------------------------------------------
<DpidOwner SequenceNumber="1">
  <DPID>PADPIDA2006111001O</DPID>
  <CompanyName>234AG</CompanyName>
  <Address>Riedtlistrasse 23, ZÃƒ????????Ãƒ???????Ãƒ??????Ãƒ?????Ãƒ????Ãƒ???Ãƒ??Ãƒ?Ã‚Â¼rich, 8006, CH</Address>
 </DpidOwner> | <DPID>PADPIDA2006111001O</DPID> | <CompanyName>234AG</CompanyName>                | <Address>Riedtlistrasse 23, ZÃƒ????????Ãƒ???????Ãƒ??????Ãƒ?????Ãƒ????Ãƒ???Ãƒ??Ãƒ?Ã‚Â¼rich, 8006, CH</Address>
<DpidOwner SequenceNumber="2">
  <DPID>PADPIDA2007011501Q</DPID>
  <CompanyName>OpenIMP</CompanyName>
  <Address>8-10 Rhoda Street, London, E2 7EF, UK</Address>
 </DpidOwner>                                                                         | <DPID>PADPIDA2007011501Q</DPID> | <CompanyName>OpenIMP</CompanyName>              | <Address>8-10 Rhoda Street, London, E2 7EF, UK</Address>
<DpidOwner SequenceNumber="3">
  <DPID>PADPIDA2007040501K</DPID>
  <CompanyName>The Harry Fox Agency</CompanyName>
  <Address>711 Third Avenue, 8th Floor, New York, 10017, USA</Address>
 </DpidOwner>                                                | <DPID>PADPIDA2007040501K</DPID> | <CompanyName>The Harry Fox Agency</CompanyName> | <Address>711 Third Avenue, 8th Floor, New York, 10017, USA</Address>


Exclude certain results from in condition in sql I have two tables
Table 1
Id data1  data1
1  'ff'   'ffe'
2  'r'    '3'
3  'df'   'er'

Table 2
Id   WFID  data1   data2
1    12    'd'     'e'
1    13    '3'     '4f'
1    15    'e'     'dd'
2    12    'f'     'ee'
3    17    'd'     'f'

Now what I need is to join the two tables and get the data when only WFID is 12 or 17 for the record and 12 or 17 only. In this case Id number 1 has 12 but it also has 12 and 15 so it's excluded. Id number 2 and 3 are included because they have just 12 or 17.
I used the below query but it is also giving me data with id 1. Can someone please tell me what I can use to fix this.
select * from Table1
where Exists (select distinct ID from Table2 where
    Table1.ID = Table2.ID and
    WFID in (12,17))

Thanks

One way to get the results you're after is to "reverse" both the EXISTS and IN operators - it's a little confusing to read, though.  I also got rid of the distinct, since it's not really needed in the subquery.
SELECT *
FROM #Table1
WHERE NOT EXISTS
(
    SELECT ID
    FROM #Table2
    WHERE
        #Table1.ID = #Table2.ID
        AND WFID NOT IN (12,17)
);

Here's the setup for the query I'm using locally:
CREATE TABLE #Table1
(
    Id int NOT NULL,
    data1 varchar(2) NOT NULL,
    data2 varchar(3) NOT NULL
);
INSERT INTO #Table1
    (Id, data1, data2)
VALUES
    (1,  'ff',   'ffe'),
    (2,  'r' ,   '3'  ),
    (3,  'df',   'er' );

CREATE TABLE #Table2
(
    Id int NOT NULL,
    WFID int NOT NULL,
    data1 varchar(2) NOT NULL,
    data2 varchar(3) NOT NULL
);
INSERT INTO #Table2
    (Id, WFID, data1, data2)
VALUES
    (1,    12,    'd',     'e' ),
    (1,    13,    '3',     '4f'),
    (1,    15,    'e',     'dd'),
    (2,    12,    'f',     'ee'),
    (3,    17,    'd',     'f' );


Prevent explicit insert into serial columns I want to prevent explicit inserts into serial columns. I have come up to the following trigger:

drop table test_table;

create table test_table(
id bigserial primary key,
foobar text
);

create or replace function serial_id_check() returns trigger as
$$
begin
    if new.id != currval(TG_TABLE_NAME||'_id_seq') then
        raise exception 'Explicit insert into serial id, currval = %, tried to insert = %', currval(TG_TABLE_NAME||'_id_seq'), new.id;
    end if;
    return new;
end;
$$ language plpgsql;

create trigger test_table_serial_id_check
before insert on test_table
for each row
execute procedure serial_id_check();

Maybe there is a better approach? Maybe this approach is broken and this cannot be achieved at all?

P.S. I also think about not granting rights for the insert and update, but only for pgplsql procedure for inserting/updating - but this approach is not possible for me right now.

In Postgres 10 or later, consider an IDENTITY column instead. Like:
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

See:

What are Identity Columns?
Auto increment table column

Original answer for older versions:
Updateable View
In Postgres 9.4, views are automatically updatable per column. I.e., as long as basic conditions are met, columns are automatically updatable for plain references to underlying columns. Expressions are not. We can exploit this feature:
CREATE TABLE test_table(
  id serial PRIMARY KEY
, foobar text
);

CREATE VIEW test_view AS
SELECT id * 1 AS id  -- id unchanged but not updatable!
     , foobar
FROM   test_table;

By multiplying id with 1, the value is unchanged but the column is not automatically updatable any more.

REVOKE INSERT / UPDATE privileges on test_table from your users.
Make another role holding those privileges own the view.
GRANT INSERT / UPDATE on test_view to your users.

Now they can do everything, but they cannot manually set or alter the value in id. It's your choice how to handle DELETE.
This simple and quick solution works out of the box in Postgres 9.4. Automatically updatable views were introduced with Postgres 9.3, but all columns need to be updatable in that version for the feature to work.
In Postgres 9.3 or older, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.
While writing rules / triggers manually, you don't need the id * 1 trick. You might want to use this even in Postgres 9.4+ to fine-tune functionality. Example:
CREATE VIEW test_view1 AS TABLE test_table;

CREATE OR REPLACE RULE ins_up AS
 ON INSERT TO test_view1 DO INSTEAD
INSERT INTO test_table (foobar) VALUES (NEW.foobar);

Now, INSERT on the view is possible, but not yet UPDATE or DELETE. Write more rules if you want that.
SQL Fiddle  (Postgres 9.6)
Important difference: while the automatically updatable view rejects attempts to INSERT / UPDATE values in id with an exception, the demonstrated RULE simply ignores values for id and proceeds without exception.
Simple trigger
Alternatively, you could simply overwrite the serial column with the next value from its sequence unconditionally:
CREATE OR REPLACE FUNCTION force_serial_id()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW.id := nextval(pg_get_serial_sequence(quote_ident(TG_TABLE_NAME), 'id'));
   RETURN NEW;
END
$func$;

That's simpler and cheaper and less error-prone than trying to be smart about it. quote_ident() to safely escape otherwise illegal names (also defends against SQL injection).
Like @dezso commented, this burns two numbers per row in normal operation with a serial column because a default is fetched before the trigger function kicks in. Typically, gaps in a sequence should not be a problem (to be expected anyway), but you can avoid the side effect by removing the DEFAULT from the column. Then you rely on the trigger exclusively.
You could fine-tune the UPDATE case with a separate trigger and a condition on the trigger itself WHEN (OLD.id <> NEW.id). Syntax example:

How to prevent a PostgreSQL trigger from being fired by another trigger?

Note the use of pg_get_serial_sequence(), which won't break like your original for non-basic identifiers. Think of "MyTable" or a non-default sequence name. Still assuming the column name id which I personally never use since it's not descriptive.


Delete with join and condition (3 tables) - MySQL Need some assistance on MySQL delete using join with condition (3 tables).

snote (child table)
--------
snote_id
bnote_id

bnote (parent table of snote)
------
bnote_id
note_id

note (parent table of bnote)
----
note_id

Want to delete all snote records with bnote_id=10 and note_id=5

Able to find the answer in another post... Sorry for raising this as duplicate

DELETE `stn` FROM `snote` AS `stn`
LEFT JOIN `bnote` AS `ban` ON `ban`.`bnote_id`=`stn`.`bnote_id`
WHERE `ban`.`note_id`='5' AND `stn`.`bnote_id` = '10'


Choosing a PK: VARCHAR(2) or SMALLINT(2)? Today we were discussing the following:

Brazil has 27 states and each one has its own abbreviation (just like USA).
So we've got RJ for Rio de Janeiro, SP for São Paulo, MG for Minas Gerais and so on.

One of our programmers proposed that we should use those abbreviations (RJ, SP, MG, etc.) as PK on the States table that we are planning to add to a new project.

Extrapolating the use of our database, I countered his argument saying that if we were to - someday - expand our services to other countries, we would have a problem with repeated abbreviations, for example: in the USA there is SC for South Carolina and on Brazil we've got SC for Santa Catarina; the same happens for MT, PA and MA. Counting on this we've agreed that there should be a ID column as PK IDENTITY.

Now, supposing that we do not expand our services to other countries and stay only in Brazil I started considering the idea of using a VARCHAR(2) column as PK. In this scenario it doesn't sound like a totally bad idea.
Is it? Why? In which cases this could be applied? Should memory be considered in order to choose from one to another?

In most cases I agree with @Aaron's approach, but this just happens to be one of the few instances of there being a truly natural key to use: ISO codes (ISO 3166 specifically). For those who are unfamiliar with ISO, they are, in their own words (from the main http://www.iso.org/ page):

  We're ISO, the International Organization for Standardization. We develop and publish International Standards.

ISO 3166-1 describes the country codes. While there are a few choices of codes to use (2 character, 3 character, and numeric), the 2 character codes are the recommended choice and the most widely used (including for most country-based top-level domain names).

ISO 3166-2 describes each country's subdivisions (e.g. states). This standard is divided into country-based sections (e.g. ISO 3166-2:BR for Brazil) and the codes are 1, 2, or 3 alphanumeric characters.

So, you could do something like:

CREATE TABLE dbo.Country
(
  CountryCode CHAR(2) NOT NULL
                      COLLATE Latin1_General_100_BIN2
                      PRIMARY KEY,
  CountryName VARCHAR(50) NOT NULL
);

CREATE TABLE dbo.CountrySubdvision
(
  CountrySubdvisionCode VARCHAR(3) NOT NULL
                                   COLLATE Latin1_General_100_BIN2,
  CountryCode CHAR(2) NOT NULL
                      COLLATE Latin1_General_100_BIN2
                      CONSTRAINT [FK_CountrySubdvision_Country]
                                 FOREIGN KEY
                                 REFERENCES dbo.Country(CountryCode)
                                 ON UPDATE CASCADE,
  CountrySubdvisionName VARCHAR(50) NOT NULL,
  --  LocalizedSubdvisionName NVARCHAR(50) NOT NULL, -- ??
  CONSTRAINT [PK_CountrySubdvision]
             PRIMARY KEY (CountryCode, CountrySubdvisionCode)
);

The idea here is that you would place the CountryCode and CountrySubdvisionCode fields in any tables that needed the "state" value, and FK back to dbo.CountrySubdvision on (CountryCode, CountrySubdvisionCode).

While this method, using 3 - 5 bytes depending on the Country / Subdvision, is not as compact as using a 2-byte SMALLINT, it does have the advantage of placing human-readable / meaningful values in those related tables. This could easily reduce some number of JOINs (when you only need the 2-character code anyway) and can (at least slightly) reduce time spent debugging certain problems.

Please note that I specified a _BIN2 (i.e. binary) collation for both of the "Code" fields to assist performance. Even with an extra couple of bytes, that should be nearly as fast as comparing two SMALLINT values. The only downside there is that you need to be consistent in adding codes in all upper-case and people need to remember that they need to use all upper-case when filtering on these codes.

Please also note that I added the ON UPDATE CASCADE clause to the FK on dbo.CountrySubdvision to handle the off-chance that a CountryCode is changed by ISO (extremely unlikely). Along those same lines, the FKs created on tables where the CountryCode and CountrySubdvisionCode fields have been placed will also need to be set to ON UPDATE CASCADE. This will propagate changes made to CountryCode in dbo.Country to the tables that have both of the fields. This also will propagate any changes made to the CountrySubdvisionCode to those related tables. This is also highly unlikely to happen (especially for where most of our systems would be dealing with) though more likely than any changes being made to the CountryCode.

UPDATE

  Now, supposing that we do not expand our services to other countries and stay only in Brazil I started considering the idea of using a VARCHAR(2) column as PK. In this scenario it doesn't sound like a totally bad idea.

Just to have this stated, if using this method (i.e. ISO codes) to denote "States" / "Provinces" and possibly Countries, then you do technically have the ability to start out handling only "States" for a single country. In this configuration you would simply remove the dbo.Country table and the CountryCode field (and associated FK) from dbo.CountrySubdvision. Then you would only have CountrySubdvisionCode to place in any related tables.

Now, if you later find that you need to expand the system to handle other countries, you can, at that time, do the following steps (none of which change any existing CountrySubdvisionCode values in any of the related tables):

Create the dbo.Country table
Populate the dbo.Country table
Drop any FKs from related tables that reference dbo.CountrySubdvision
Drop the PK on dbo.CountrySubdvision
Add the CountryCode field to dbo.CountrySubdvision, making it NOT NULL and with a DEFAULT of the CountryCode for the only country that you have been using so far (this DEFAULT can be removed at the end of this process, if you like)
Recreate the PK on dbo.CountrySubdvision to be on (CountryCode, CountrySubdvisionCode)
Create the FK on dbo.CountrySubdvision for the CountryCode field to reference the dbo.Country table
Add the CountryCode field to all of the related tables that already have the CountrySubdvisionCode field, making it NOT NULL and with a DEFAULT of the CountryCode for the only country that you have been using so far (this DEFAULT can be removed at the end of this process, if you like)
Recreate the FKs on those related tables for both fields to reference the dbo.CountrySubdvision table
Remove the Default Constraints on the CountryCode fields from dbo.CountrySubdvision and the related tables


Unsure what this relationship is called Essentially, I have googled around but I am unsure what would this type of relationship be called. Table 3 contains a field [Labelled as STRING], that has multiple 1 to many relationships to Table 2 [Labelled as STRING and STRING2, STRING connects to both]. However, Table 1 has a 1 to many relationship with Table 2 through STRING2.
I am thinking that Table 3's STRING is erroneously linked to Table 2's STRING2, but I am unsure if this is intended, and whether if these types of relationships do exist. I am thinking that STRING could be a composite of STRING and STRING2 in table 2, but I am sure that breaks a few db rules (e.g.atomicity), does anyone have any pointers I should read up on?

What you're describing looks like a many-to-many relationship between table1 and table3 through table2.
Direct many-to-many relations between two tables are illegal and require an intermediate table.
Typically, many-to-many relations are usually verbs in natural language with T1 and T3 being nouns, and T2 being a verb - e.g. Customer (T1) Buys (T2) Books (T3). T2 should have both the PK of T1 and T3 as FKs in its own table to ensure the data matches across the three tables.
The design you have is problematic as strings are not unique, and therefore you can end up joining MJones in T1 to MJones in T3 with no assurance that the two individuals are the same person.
This type of error is acceptable in say a marketing database where some errors may be non-critical, and typically this is a good use in a non-relational database e.g. Big Data. However, in a database where accuracy is paramount e.g. a database recording medicines administered to patients this type of error would have disastrous consequences.
If you use a unique key in your design you will always ensure data consistency - strings are potentially problematic.


Sql server sleeping state is increasing using ADO.NET? We have one live ASP.Net application which is used by around 1000 concurrent users. In this application we have used connection pooling. There are 2 connection string defined in web.config with different name but we are calling same DB.

<add key="connection1" value="server=1.1.1.1;USER ID=**;pwd=**;database=test;Max Pool Size=500;Min Pool Size=50;Connection Timeout = 300; Pooling='true'" />

<add key="connection2" value="server=1.1.1.1;USER ID=**;pwd=**;database=test;Max Pool Size=500;Min Pool Size=50;Connection Timeout = 300; Pooling='true'"/>

In my application we are opening an closing the connection every where like below

try{
    if (connection.State == ConnectionState.Closed)
        {
            connection.Open();
        }
}
catch(Exception ex)
{
}
finally{connection.close()}

Now issue is when I am checking Database using sp_who2, I am seeing around 1000 or more than that sleeping session and for each record program name is ".Net sql server Provider". I gone through MSDN and found that if we are using connection pooling then connection keep opened till 4-8 minutes. So I am bit confused now, if I am using only 2 connection string with connection pool then why we are seeing so many sleeping connection, I am thinking it should be only 2? Can someone please help me in this situation and suggest what is correct? And how can we minimize sleeping sessions.
Also not sure whether it's an application pool(IIS server) error or application error?
Please help.

Thanks,

So I am bit confused now, if I am using only 2 connection string with
  connection pool then why we are seeing so many sleeping connection, I
  am thinking it should be only 2?

Min Pool Size=50 specifies number of connections established when the pool is created and will remain in the pool until the application pool is unloaded (including an IIS restart). With 2 distinct connection strings and security context, you will have at least 100 connections at all times and these will show as sleeping on SQL Server when not in use. The purpose of Min Pool is to ensure at least that number of connections are always available to the application and avoid the overhead of physically closing and opening connections that will be needed but at the costs mentioned below.

The Max Pool Size=500 specification allows up to 500 connections for each pool so you can have up to 1000 total connections if needed. The connections in excess of the Min Pool Size are acquired only when an unused connection is not available in the pool. The high number of connections you see with sp_who2 may be as a result of user activity, long-running queries, because connections are not closed/disposed in a timely manner, or pooled connections are still associated with a transaction context.

  And how can we minimize sleeping sessions. Also not sure whether it's
  an application pool(IIS server) error or application error? Please
  help.

Not sure what you mean by IIS error but a large number of sleeping connections that were not due to query activity may indicate an application issue. All of the examples in the ADO.NET connection pool documentation employ a using block to ensure connections are properly closed and disposed without the need for app code to explicitly close the connection. I suggest you follow that practice with consistently throughout the application with both connections and transaction objects.

The sleeping connection overhead on the SQL server is additional memory and client side connection pool management. The query below will show how much memory is consumed by all SQL Server connections:

SELECT SUM(cntr_value) AS ConnectionMemoryKB
FROM sys.dm_os_performance_counters
WHERE counter_name = N'Connection Memory (KB)';

The ADO.NET connection pool documentation


How to restart mysql slave node? i have 8 slave servers and i need to restart one of them but in my.cnf file master-host, master-user etc. variables dont exists. So if i restart server as indicated here
replication will start again?

The replication configuration (which master to sync from, which relay log & which change it's up to) isn't stored in the my.cnf, it's stored in the master.info / relay.info files, so you should be OK to follow that process.


ORA-12518: TNS:listener could not hand off client connection ORACLE XE 11.2 x64 I'm starting to become very very frustrated by Oracle.
So, I have Oracle XE 11.2 64 bit and ODAC121012_x64 installed.

I can start listener; I can do tnsping xe; but when I try to connect as via sqlplus as system I'm getting:

ERROR:
ORA-12518: TNS:listener could not hand off client connection

Here are my config files:

tnsnames.ora:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC =
                           (SID_NAME = XE)
       (ORACLE_HOME =  C:\oraclexe\app\oracle\product\11.2.0\server)
     )

    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

sqlnet.ora:

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)

Help is very much needed and will be even more appreciated!

To understand the problem and try to fix it you need to check related logs:
 listener.log, alert log, trace file, etc

possible causes and solutions:

listener problem:

Make sure there is no error message and listener is up and running by execute the following command:

lsnrclt status

if listener is down check the following it may help you troubleshoot this issue:

could be network error: check the even log for any network error and fix them.
service stopped or it has failure when trying to start:  start--->run--->services.msc the search for oracle listener service  should be like OracleInstancename  try to start it, if it failed to start it will throw an exception inside listener error and windows error number. please provide that error

listener workaround:

add the following parameter to your listener:

DIRECT_HANDOFF_TTC_LISTENER=OFF

database problem:

1.database server memory were enough for new connection

database connection parameters such as: dedicated server, please checked processes parameter or query from v$resource_limit where resource_name=’processes’; checked max_utilization must less than Limit_value or shared server, verify large_pool_size or check dispatcher were enough for all connection.


Compile TSQL stored procedures into assembly I am trying to protect my business logic and I don't want to use "With Encryption" as it is not enough and can be broken.

I don't want to re-write the procedures again in CLR language.

Is there an easy way to compile the TSQL programming into a DLL that can be used by SQL server, just like the same way it uses the CLR assemblies?

No, there is no way to do this. And there is not much purpose in doing so anyway as you can easily decompile Assemblies with tools such as ILSpy.

The two ways to protect your intellectual property are:

Proper license agreement. Consult with a lawyer to see what is best for you. There are many options and laws vary by state / region / country / etc so advice you get online might not be correct for you.
Innovation. Even if you protect the code from being seen, it can still be reversed engineered by observation / testing. Keep evolving and improving.
Yes, this is now more than 2, but this one isn't exactly protecting the intellectual property, BUT... Support: provide great support and that will help in some cases. Provide good, thorough documentation and fix things as quickly as possible when someone identifies a bug. This is harder to duplicate than code ;-).


How do I generate a time series in PostgreSQL? If you're looking to generate a date series, see this question

Let's say I want to generate a series for every 5 minutes for 24 hours. How do I do that in PostgreSQL?

PostgreSQL can generate_series() from a timestamp, but not from time.

Is it better to pick an arbitrary timestamp, or is there another way to generate the series?

To optimize:

SELECT x::time
FROM   generate_series(timestamp '2000-01-01 00:00'
                     , timestamp '2000-01-02 00:00'
                     , interval  '5 min') t(x);

The date is irrelevant, so use arbitrary timestamp constants. The cast to time is very cheap.
This includes lower and upper bound, so we get '00:00' twice. Use '2000-01-01 23:59' as upper bound to get it once only.

Related:

Generating time series between two dates in PostgreSQL


How to rollback after wrong update statement in sql server 2008R2? select * from aa

update aa set City='chennai',LastName='vinoth';

ID  FirstName  LastName  City
29  Abcrdrr    vinoth    chennai
1   John       vinoth    chennai
2   Joe        vinoth    chennai
35  raja       vinoth    chennai
38  Johsdfgn   vinoth    chennai

I have wrongly updated Lastname, City columns in all the rows. Now I want to rollback to the old table rows.

Using SQL-Server 2008R2.

Unless you have some kind of history table & trigger in place, to retain old values at every change, or you made a copy of the table before you ran the update, you will need to use the last backup that was taken before that update. Restore it (as some temp database) and extract the data.
You do have a backup available, right?

BTW, next time you're doing an update, I suggest you set it up like this:

SELECT *
-- UPDATE t SET Column1 = x, Column2 = y
FROM MyTable AS t
WHERE ...

Run the SELECT first to see exactly which rows will be updated. If needed, adjust the WHERE clause to get the rows you want to target. Only then mark the sentence from the UPDATE till the end and execute it. Much safer then running a "blind" update, as you did, forgetting the WHERE clause (I assume that was the problem).
Even safer - do it on some test database first :).


Reset my root password, now I can't see my main database I was getting access denied for root@localhost. To resolve this, I stopped the mysql services, ran mysqld --install and mysqld --initialized, added skip-tables in the my.ini file and was able to log back in with the root user got the password to reset. Cleared the skipped-tables from the my.ini file. Flush the privileges, granted the root all access. Put in the command "show databases;" but it only shows these databases and its missing one.

information_schema
mysql
performance_schema
sys

I looked at the my.ini file to see where the datadir was, looked at the path and I see the folder and the ibd file where the database resides, but it still does not show in MySQL. Any help on this would be greatly appreciated.

MySQL --initialize installs a clean data directory. It seems like that's what you got.

There may be a previous idb file there but its not connected to the database.


How to store one-byte integer in PostgreSQL? In PostgreSQL documentation, it is said that integer data types can be stored in either two-, four- or eight-byte space. One of the columns of a table in my database contains a one-byte integer value and I want it to be stored in a one-byte data type.

Is there an extension or a way to use one-byte integer data type in PostgreSQL?
How many bytes is NUMERIC(1,0)?

No, there is no 1-byte integer in the standard distribution of Postgres. All built-in numeric types of standard Postgres occupy 2 or more bytes.
Extension pguint
But yes, there is the extension pguint, maintained by Peter Eisentraut, one of the Postgres core developers. It's not part of the standard distribution:
In addition to various unsigned integer types, it also provides the 1-byte integer you are looking for:
int1 (signed 8-bit integer)
uint1 (unsigned 8-bit integer)
uint2 (unsigned 16-bit integer)
uint4 (unsigned 32-bit integer)
uint8 (unsigned 64-bit integer)

Be sure to read the chapter "Discussion" at the linked site, explaining possible complications. You need to exercise care with type casts and numeric literals when introducing more integer types ...
Workaround
A possible, simple workaround would be to encode 1-byte integer values as "char" (with double-quotes!), an "internal" simplistic 1-character type, which actually uses a single byte of storage, byte values of a signed 1-byte integer, the upper half represented as ASCII characters.
You can encode values in the range of -128 to 127. Demo:
SELECT i
     , i::"char"
     , i::"char"::int
FROM   generate_series(-128,127) i;

There are several characters not meant for display. So encode before you store and decode before you display ...
Remember: "char" is an "internal" type intended for simple and cheap enumeration. Not officially designed for what we are doing here, and not portable to other RDBMS. There are no guarantees by the Postgres project. But since "char" is used all over the system catalogs, the type is not going to change.
Initially, I had assumed unsigned 1-byte integer range (0 to 255) and used text as stepping stone. But that only works for numbers 1 - 127. Instead, use signed integer range (-128 to 127) and cast between "char" and integer directly.


"Diamond Shaped Relationship" What's the formal term? Consider the following "Diamond-Shaped" entity relationship diagram:

I'm trying to find some formal readings on why this is a bad design - but Googling "Diamond Shaped Table Relationship" only turns up the ERD symbol.

What is this relationship called? Why is it a bad table design (if it is)?

You'd have to add the crows feet (or whatever notation you prefer) for us to properly understand the diagram. I choose to interpret it as directed top-to-bottom.

    top table
     |     |
     ^     ^
   Left   Right
      |    |
      ^    ^
      Bottom

Fig: Diamond pattern

I do not recall ever seeing this pattern explicitly named. "Diamond" seems as good as any.

As such I don't believe it is a circular reference. For that you'd have to be able to follow the "many" back to the origin:

  First ---< Second
    v           |
    +-- Third >-+

Fig: Circular reference

I question the premise that the diamond pattern is a bad design. For certain applications it may be entirely appropriate. Perhaps it's just not appropriate for yours.

As a toy example consider a small multi-tenant app to track hot-desking. We have entity types Company, Employee, Desk and DeskAllocation. A Company hires many Employees. The Company provisions many desks. Each employee can sit at a different desk each day. Naturally an employee may only sit at a desk provisioned by their employer.

We can identify the company by it's business registration number or a made-up number we supply. It doesn't matter. We'll call this CompanyId.

Companies create employee IDs for their hires. Since every company has an employee who wants to be hire #1 we have to have a composite key of (CompanyId, EmployeeId).

Each company has its own labeling scheme for desks but most of them use a simple numerical sequence. So the PK of Desk is the composite (CompanyId, DeskId).

So what's the PK of DeskAllocation? It will be the PK of Employee, that of Desk and a date column. Those first two are themselves composite, each containing CompanyId. That may seem redundant, or even an error, but is integral to the enforcement of the "sit at your employer's desk" rule. That can be implemented as a check constraint.

The pseudo code for DeskAllocation will be

table DeskAllocation
(
  DeskCompanyId,
  DeskId,
  EmployeeCompanyId,
  EmployeeId,
  AllocationDate

  primary key
  (
    DeskCompanyId,
    DeskId,
    EmployeeCompanyId,
    EmployeeId,
    AllocationDate
  )

  foreign key FK_Desk
  (DeskCompanyId, DeskId) references Desk(CompanyId, DeskId),

  foreign key FK_Employee
  (EmployeeCompanyId, EmployeeId) references Employee(CompanyId, EmployeeId)

  constraint CHECK DeskCompanyId = EmployeeCompanyId
)

This is may seem artificial but I have implemented exactly this pattern in an application for automotive pricing.


SQL Server 2008 user and group permissions behavior weirdness I have a strange situation:

An AD user account has a login to a SQL Server 2008 database without any roles assigned to that user.

That same database has a login for an AD group with datareader/datawrites roles.

The AD user is a member of the AD group.

It looks like if ONLY the user-permissions are used. He is not able to do anything with the database.

This doesn't make sense to me at all.

I would presume that the sum of all roles (from user and group) would be in effect.

As I'm not really a SQL Server guy I am at a loss to explain this. Can someone clarify how user and group derived permissions interact ?

P.S. The user has been a member of that AD group for years. This is not a case of a recent addition to an AD-group not being synchronized in AD.

I recreated your situation. I created the following items:

Database TEST
SQL Login DOMAIN\USER
SQL Login DOMAIN\GROUP
Database User USER (linked to SQL Login DOMAIN\USER)
Database User GROUP (linked to SQL Login DOMAIN\GROUP)
Assigned Database User USER no database roles
Assinged Database User GROUP the database roles db_datareader and db_datawriter

I then queried the TEST database with the Windows login DOMAIN\USER. Because the DOMAIN\USER is also memmber of DOMAIN\GROUP the user is allowed to SELECT from all tables in the TEST database.

You are writing that the DOMAIN\USER is unable to select any data. Correct?

Do you have any orphaned users on the database level? Follow these steps to check.

Open SQL Server Query Window
Change database to YOUR database
Run the command exec sp_change_users_login 'Report'
Check the output. You might find that one of your database users is not properly linked to a SQL Server Login.

To fix:

In the same query window for YOUR database execute the following command

exec sp_change_users_login 'Update_one', '<DATABASE USER>', '<SQL LOGIN>'

E.g. if your database is YOUR_DB, the SQL Login (SQL Server level) is YOUR_SQL_LOGIN and the database user you are relinking is YOUR_DB_USR, then the command would be:

USE YOUR_DB
GO
exec sp_change_users_login 'Update_one', 'YOUR_DB_USR', 'YOUR_SQL_LOGIN'

If everything is ok and you don't have any orphaned users, then try checking the permissions of the database user in question.

Does that user have an explicit DENY set somewhere?
Is the account disabled at the SQL Server Login level?
Do you have any error messages?

I hope this gives you some ideas on what to check.


performance of Replace vs If exists update else insert vs update if row_count()=0 insert Structure of my table is:

Id           int(11)        NO     PRI      auto_increment
Mobile       varchar(10)    NO
LoanAmount   int(11)        NO

I want to UPDATE if existing Mobile else INSERT new entry.

What have I tried?

Using REPLACE as there is no foreign key constrain
IF EXISTS (SELECT 1 FROM table WHERE Mobile='SomeValue')
UPDATE ...
WHERE Mobile=v_mobile
ELSE
INSERT INTO table ...
UPDATE table SET (...) WHERE Mobile='SomeValue'
IF ROW_COUNT()=0
INSERT INTO table.person (...) VALUES (...)

Which would be best in terms of performance or future maintenance?

Usually the answer is simply "which one sends more requests to the server".

IODKU - 1
REPLACE - 1
EXISTS - 2 (SELECT, plus either UPDATE or INSERT)
ROW_COUNT - 1 or 2 (ROW_COUNT does not count)

But we have a tie with that metric.  So, let's dissect the winners:

IODKU - Find the row, decide what do, then do it.
REPLACE - DELETE all rows that match on any UNIQUE (or PRIMARY) KEY, then INSERT.

It feels like IODKU will be less work.


Can't connect to a MySQL from the command line (works from MySQL Workbench) I am connected through a local socket/pipe and the username password is root/roots. I can login and view the 4 schemas I have created. However, when I use the mysql command at the command line and login to localhost then the only schemas visible are test and information_schema.  What am I doing wrong and how to I login correctly?

As 'ypercube' commented, log in from both connections to tell which user is connecting. You should see differences when you run SELECT USER(),CURRENT_USER(); and SHOW GRANTS;

You are logging in but you will need to GRANT permissions (from your root account) to your @'localhost' account to gain access to your four schemas. (ref. http://dev.mysql.com/doc/refman/5.1/en/grant.html)

If this is not being used for testing, it would be adviseable to secure/drop anonymous accounts that are typically set-up with MySql if you don't intend to use them. (ref. http://dev.mysql.com/doc/refman/5.5/en/default-privileges.html)


Can't get databases or its objects with powershell I have Powershell installed both in my server and development pc. In my dev machine apparently have a most recent version of PowerShell installed or some its components of SQL Server 'sqlps'. My current problem is that I can't list databases as I do in my dev machine like this:

PS SQLSERVER:\> ls SQLSERVER:\\SQL\server\instance\Databases\

And I get the following:

Get-ChildItem : No se encuentra la ruta de acceso 'SQLSERVER:\SQL\serverr2\serverr2\Databases' porque no existe.
En línea: 1 Carácter: 4
+ dir <<<<  SQLSERVER:\SQL\server\instance\Databases
    + CategoryInfo          : ObjectNotFound: (SQLSERVER:\SQL\server\instance\Databases:String) [Get-ChildItem], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetChildItemCommand

Obviously I have no way to get Child-Items like the Stored Procedures, Views, etc.

However, I'm able to list the installed instances, and also workaround this issue by listing databases with this:

DIR SQLSERVER:\\SQL\serverr2 | SELECT Databases -ExpandProperty Databases | SELECT Name,Owner,Urn

But still need to get another objects like stored procedures, views, functions,etc.

This is the info provided by the Get-Host cmdlet

Name             : ConsoleHost
Version          : 2.0
InstanceId       : c1976472-19c0-439e-a4f6-debe59a18616
UI               : System.Management.Automation.Internal.Host.InternalHostUserInterface
CurrentCulture   : es-MX
CurrentUICulture : es-ES
PrivateData      : Microsoft.PowerShell.ConsoleHost+ConsoleColorProxy
IsRunspacePushed : False
Runspace         : System.Management.Automation.Runspaces.LocalRunspace

and the @@version of SQL Server is Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)

Edit: According to $PSVersionTable.PSVersion in my server I have installed PowerShell 2.0. Also I found that I'm unable to Import-Module sqlps but for some reason I'm capable to dir as I already described (with the current problems obviously).

I found the answer in serverfault:

https://serverfault.com/questions/355014/sql-server-powershell-cannot-find-path-error

I have to run a script to properly load the Snapins in PowerShell:

#
# Add the SQL Server provider.
#

$ErrorActionPreference = "Stop"

$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
    throw "SQL Server Provider is not installed."
}
else
{
    $item = Get-ItemProperty $sqlpsreg
    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}

#
# Set mandatory variables for the SQL Server rovider
#
Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

#
# Load the snapins, type data, format data
#
Push-Location
cd $sqlpsPath
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Update-TypeData -PrependPath SQLProvider.Types.ps1xml
update-FormatData -prependpath SQLProvider.Format.ps1xml
Pop-Location

Once loaded I can list every object in that database instance


Underutilized CPU on SQL server I recently upgraded my SQL Server 2014 hardware. The CPU profile is looking weird. I have 24 CPUs, all CPUs are VISIBLE ONLINE, MAXDOP is 0. The SQL CPU usage is peaking at 60% and not going beyond that although the queries are highly CPU intensive and mostly in running/runnable state. I have checked the session log dumps that the Total Request Time is only slightly above the CPU time so have ruled out io waits, etc. Also checked the OS CPU usage is tracking only slightly higher than the SQL CPU usage. Wondering why CPU is not fully utilized. Previous hardware had 16 CPUs and used to saturate at ~95%

A few things:

Check licensing, depending on edition and how you are licensed it might be ignoring some of the extra cores.

It might be that your load can't efficiently use more cores. Using multiple cores for any statement doesn't necessarily make it faster, in fact for many it can make them slower due to the work to split the data and recombine the output, so the processing engine might simply not be seeing an advantage in using more cores right now. If your general workload is that it is running ~16 processes that it thinks won't benefit from multiple threads then it is only going to be using ~16 cores worth of CPU power.

You might be hitting an IO bottleneck that was previously masked by the CPU bottleneck.

Each of those cores may be more powerful than each of the ones in the previous chip(s), which will further reduce the likely peak % CPU use if you are hitting IO bottlenecks.

If your workload involves a lot of table/index scanning on objects that are of significant size compared to the CPU's cache (which most objects in a large DB will be) but not compared to the machine's RAM, then you might be hitting a memory bandwidth bottleneck instead of an IO bottleneck.

Finally, though perhaps most importantly: Is anything actually being too slow? It might be that your workload simply does not need more hardware power at this time.


MySQL replication - replicate to multiple DBs on slave? I've got a single table in a master DB that I would like to replicate to multiple databases on one slave server. Like this:

masterDB.tableA -> slaveDB1.tableA
masterDB.tableA -> slaveDB2.tableA

slaveDB1 and slaveDB2 are on the same slave server. Is this possible?

I can get slaveDB1's replication working no problem, but it's as it it's ignoring the command in my.cnf for slaveDB2:

replicate-rewrite-db="masterDB->slaveDB1"
replicate-rewrite-db="masterDB->slaveDB2"

replicate-wild-do-table=slaveDB1.tableA%
replicate-wild-do-table=slaveDB2.tableA%

Am I missing something, or can this just not be done with MySQL replication?

The first obvious question is why would you want to do this?  It's probably a bad idea and completely unnecessary, since you can join across databases:

SELECT * FROM db1.table1 JOIN db2.table2 ON db1.table1_id = db2.table2_id WHERE ...

Next, in order to understand why this doesn't work, you first need to understand the basics of MySQL replication.  In summary:

On the master, a statement is executed
On the master, that statement is written to the binary log
On the slave, the SLAVE IO_THREAD connects to the master, reads the binary log from a known position, and writes the queries to the local relay log
On the slave, the SLAVE SQL_THREAD reads the relay log and executes the statements.

You seem to be expecting that by having multiple rewrite statement in place for the same database, that the SQL thread will execute the relay log statements twice.  This is not the case.  From the documentation, replicate-rewrite-db simply changes the value of USE db as it is being written to the relay log, so one or the other of your statements is being evaluated, but not both.

To accomplish what you are trying to do, there are a couple options that come to mind

Run multiple instances of MySQL on the same host on different ports.  This can be done with mysqld_multi.  This is very easy and can fit in with your rewrite rules.
Use the BLACKHOLE storage engine with AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers on the master.  This is very ugly, but could work for certain use cases

The way #2 would look is like this:

On the master server, create the second database and a copy of the table that uses the BLACKHOLE storage engine:

mysql> create database db1;
Query OK, 1 row affected (0.03 sec)

mysql> create database db2;
Query OK, 1 row affected (0.00 sec)

master> use db1;
Database changed
mysql> create table foo ( id int auto_increment primary key, name varchar(32) not null ) engine=innodb;
Query OK, 0 rows affected (0.16 sec)

mysql> use db2;
Database changed
master> create table foo ( id int auto_increment primary key, name varchar(32) not null ) engine=BLACKHOLE;
Query OK, 0 rows affected (0.05 sec)

master> use db1;
Database changed
master> CREATE TRIGGER insert_into_blackhole AFTER INSERT ON foo FOR EACH ROW INSERT INTO db2.foo SELECT * FROM db1.foo WHERE id = NEW.id;

master> CREATE TRIGGER update_into_blackhole AFTER UPDATE ON foo FOR EACH ROW UPDATE db2.foo2 SET id = NEW.id, name = NEW.name WHERE id = OLD.id;

master> CREATE TRIGGER delete_from_blackhole AFTER DELETE ON foo FOR EACH ROW DELETE FROM db2.foo2 WHERE id = OLD.id;

On the slave, create the tables as their regular storage engine.

INSERT/UPDATE/DELETE operations into master://db1.foo will get written to the binary log for db2.foo and then get replayed on the slave, where it will exist in both places.

There are a lot of caveats with this, of which here are only a few.  I recommend extensive testing.

ONLY INSERT/UPDATE/DELETE operations will get reproduced in both places.
ALTER TABLE operations will not get reproduced, so changes will have to be made to both the BLACKHOLE and the main table manually.
TRUNCATE TABLE operations will not get reproduced.
It will be very difficult to detect out-of-sync data.
Bulk operations will block until the trigger has completed.  BLACKHOLE is quite fast because it's essentially /dev/null, but in large enough batches simply generating the statements may take a long time.  If you perform a bulk INSERT/UPDATE/DELETE of 1000 rows, that will generate 1000 individual statements to execute against the BLACKHOLE table.  You can improve on this by setting up a relay slave.
Because of the conversion from bulk updates to individual row updates, the slave may have a tendency to lag behind.

Using a relay slave:
Your replication topology would look like this:

master -> relay -> slave

In the relay slave topology, you'd end up with an unaltered master and instead, the BLACKHOLE configuration would be on the relay slave.  Your regular slave that contained the copies would be a slave of the relay.  This would prevent any slowdown of the writes on the master.  Note, due to the way triggers are applied on slaves, the relay slave topology will only work with STATEMENT based replication.

CAVEAT: I'm certain I've missed some critical detail, so test this extensively.

My advice: Don't do it.  I can't think of any good reasons why you would need to have the same exact data replicated in two places on the same server.


Installing SQL Server 2017, I get E: Unable to locate package mssql-server I'm using Ubuntu 17.10, but after adding the Microsoft repo for 17.10

sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -sr)/prod.list)"

and trying to install SQL Server, I get

# apt-get install mssql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
E: Unable to locate package mssql-server

Normally, a distro packages things in a specific format, so for instance adding a repository like

deb [arch=amd64] https://packages.microsoft.com/ubuntu/17.10/prod zesty main

Will tell apt to download a package list and look for the packages here,

https://packages.microsoft.com/ubuntu/17.10/prod/pool/

However, with Microsoft that's not the case. They have a repository for 17.10 (as they should) which has all their packages for Ubuntu, but they have another repository specifically for SQL Server. To make matters more confusing, that SQL Server repository is in the tree of 16.04.

So they have both (pay attention to the /pool/s)

https://packages.microsoft.com/ubuntu/16.04/mssql-server/pool/main/m/
https://packages.microsoft.com/ubuntu/16.04/prod/pool/main/m/

You need the 16.04/mssql-server repository. You can add that with

sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)"

You may think because you have the 17.10/prod and you have SQL Server installed that you don't need the 16.04/prod repository. That's incorrect, you'll need that too if you want the SQL Client sqlcmd provided by mssql-tools

sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list)"

I did file a doc bug upstream with a description of the confusion so maybe they'll fix it someday

If you find your system in an inconsistent state for any reason, you can always remove SQL Server and start over.


SQL Server Command Line Install - How to change the edition? I am creating a script to install SQL Server via the command line.
I cannot see a way to choose the edition I want to install. The GUI on my install media offers Evaluation, Developer or Express:
(When I choose Evaluation, I can enter the product code)

The ConfigurationFile.ini generated by the installer does not seem to have any sort of reference to the edition that is being installed. Once I run the installer, it installs standard edition (verified by PRINT @@version)
How can I specify the edition I wish to install?

I found some information about your question.
There is a parameter when you set up on the command line which is /PID.
Check out the document.

Specifies the product key for the edition of SQL Server. If this
parameter is not specified, Evaluation is used.

C:\SQLMedia\SQLServer2019> setup.exe /Q
/IACCEPTSQLSERVERLICENSETERMS /ACTION="install"
/PID="AAAAA-BBBBB-CCCCC-DDDDD-EEEEE" /FEATURES=SQL,AS,IS,Tools
/INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="MyDomain\MyAccount"

You can use this parameter with the product key.
Evaluation: 00000-00000-00000-00000-00000
Express: 11111-00000-00000-00000-00000
Developer: 22222-00000-00000-00000-00000
Excerpt from the blog of Pinal Dave.


Different datatypes different execution plans bitmap scan vs index scan POSTGRESQL I am currently working on a small presentation showing when postgresql will switch from index scan to bitmap scan to sequence scan.
First example (hint all index scan…):
Loosely based on a similar example of Schönig in Mastering Postgres by Packt.
create table t_test (id serial, name text);

-- add data
insert into t_test (name) select 'hans' from generate_series(1, 5000000);
insert into t_test (name) select 'paul' from generate_series(1, 5000000);

CREATE INDEX idx_x ON t_test(id);
analyze t_test;

--Index Scan using idx_x on t_test  (cost=0.43..1431.45 rows=42058 width=9)
explain SELECT * FROM t_test WHERE id = 42353;

-- Index Scan using idx_x on t_test  (cost=0.43..1431.45 rows=42058 width=9)
explain SELECT * FROM t_test WHERE id < 42353;

-- Index Scan using idx_x on t_test  (cost=0.43..21.77 rows=419 width=9)
explain SELECT * FROM t_test WHERE id < 423;

So this example is not very good in showing when postgres will switch from index scan to bitmap scan.
explain SELECT * FROM t_test WHERE id < 6002353;
At around 6 mio retrieved lines pg will switch do seq scan.
Second example:
drop table if exists t_test;

create table t_test (id numeric, name text);

-- add data
insert into t_test (name, id) select 'hans', random() * 10000 from generate_series(1, 5000000);
insert into t_test (name, id) select 'paul', random() * 10000 from generate_series(1, 5000000);

CREATE INDEX idx_x ON t_test(id);
analyze t_test;

-- Index Scan using idx_x on t_test  (cost=0.43..8.45 rows=1 width=16)
explain SELECT * FROM t_test WHERE id = 42353;

-- Seq Scan on t_test  (cost=0.00..179078.41 rows=9999873 width=16)
explain SELECT * FROM t_test WHERE id < 42353;

-- Bitmap Heap Scan on t_test  (cost=9955.65..69408.57 rows=429834 width=16)
explain SELECT * FROM t_test WHERE id < 423;

So this example is exactly what I wanted. But why the difference?
The example is almost identical. But I had to change the id.

You’re asking why the two queries with the WHERE id < 423 clause have different query plans correct?
Well the type of scan used is dependent on the actual characteristics of the table. Your first table uses a serial so it increments by 1 and there are only 423 rows to get so an index scan is most efficient. In your second example there you use a numeric and multiply that by a random so there are potentially quite a few rows in that filter and PG decides an index scan will not be most efficient.
TL;DR even though these queries and table properties look similar, your tables’ data is actually quite different.


How do I find a list of customers that have not placed an order in the past 1 year? I'm having trouble figuring out how to write this query:

We have a sales table but the sales table only represents customers that have purchased.

We have a customers table that represents all of the customers that we have.

I have tried:

select
    c.customer_id
from
    customers c
left join
    orders o
    on c.customer_id=o.customer_id
    and c.created_at >= now()-interval '1 year'
group by
    c.customer_id
having
    count(distinct o.order_id)=0

But for a table with many millions of rows, it is very slow even with index.

I'm assuming it's because of the left join.

What can I do?

SELECT c.customer_id
    FROM customers AS c
    WHERE NOT EXISTS ( SELECT 1 FROM orders
                      WHERE customer_id = c.customer_id
                        AND sale_date > NOW() - INTERVAL 1 YEAR
                     )


Unnest ids from arrays based on priority with no duplication Here is the sample schema for my database:
Priority  | Productive | UnProductive | Neutral |
-------------------------------------------------
  High    |   [1, 2]   |      []      |  [4, 5] |
  Medium  |   [3, 4]   |    [5, 7]    |   [2]   |
  Low     |     [1]    |    [2, 6]    |    []   |

NOTE: These numbers are actually UUIDs in my database and no numbers will be repeated in the same row.
I want to unnest the category from productive, unproductive and neutral such that:

Not any from 1 to 7 numbers are repeated and
If the number is already captured by High Priority it will not be captured by Medium and same goes for Low.

Expected output:
Productive: [1, 2, 3]  # no 4 because it have been captured by neutral High priority
UnProductive: [7, 6]  # no 5 and 2 because it have been captured before
Neutral: [4, 5]  # no 2 because it is caputed before

I've solved this relationaly by unnesting and assigning the weights as suggested by @Akina.
with weighted as (
  select
    case priority
      when 'High' then 6
      when 'Medium' then 3
      when 'Low' then 0
    end as priority,
    productive,
    unproductive,
    neutral
  from
    data
), unnested as (
  select
    unnest(productive) as id, priority, 2 as category
  from
    weighted
  union all
  select
    unnest(unproductive) as id, priority, 1 as category
  from
    weighted
  union all
  select
    unnest(neutral) as id, priority, 0 as category
  from
    weighted
)
select
  case category
    when 0 then 'Neutral'
    when 1 then 'UnProductive'
    when 2 then 'Productive'
  end,
  array_agg(id)
from unnested x
where not exists (
  select null
  from unnested
  where id = x.id
  and priority + category > x.priority + x.category
)
group by category, 1
order by category desc;

See https://dbfiddle.uk/?rdbms=postgres_13&fiddle=8a54e1f8018c49146faba3e446f7b694
It can probably be done more efficiently.


PostgreSQL - PUBLICATION tries to copy the data which already exists in another database? I have database and table collection_city on one server. It has 21 rows.
I have database and table collection_city on second server. It also has 21 rows.

They both have this row:

tinker=# table collection_city;
 id |     name      |    alias     | postal_code | region_id
----+---------------+--------------+-------------+-----------
  2 | Obrenovac     | obrenovac    |             |         1

id column is the primary key.

I created Publication on first server:

CREATE PUBLICATION tinkerpub FOR ALL TABLES;

tinker=# \dRp[+]
                      Publication tinkerpub
  Owner   | All tables | Inserts | Updates | Deletes | Truncates
----------+------------+---------+---------+---------+-----------
 postgres | t          | t       | t       | t       | t
(1 row)

I created Subscription on second server:

CREATE SUBSCRIPTION tinkersub CONNECTION 'dbname=tinker host=192.168.150.5 user=postgres password=test port=5432' PUBLICATION tinkerpub;

But I see errors like this (it is trying to copy the row with id 2 AGAIN???):

2019-06-25 15:51:37.178 CEST [2270] ERROR:  duplicate key value violates unique constraint "collection_city_pkey"
2019-06-25 15:51:37.178 CEST [2270] DETAIL:  Key (id)=(2) already exists.
2019-06-25 15:51:37.178 CEST [2270] CONTEXT:  COPY collection_city, line 1
2019-06-25 15:51:37.181 CEST [21905] LOG:  background worker "logical replication worker" (PID 2270) exited with exit code 1

Why system is trying to copy the data which already exists with exactly the same data?
I see errors for all tables.
How we can resolve this? Does the second table should be totally empty before the subscription process? But it does not have any sense...why I should not have both tables populated and then to start the process of replication at one moment?

By default, CREATE SUBSCRIPTION will first copy all the existing data from the source tables. The idea is that you usually start with an empty destination database created with something like pg_restore -s.

If you don't want to have the initial data copy, create the subscription like this:

CREATE SUBSCRIPTION ...
WITH (copy_data = false);

See the documentation for details.


Is this the right and efficient way to deal with MySQL query? The table I have is

 id,  b_name,    s_name,      from_time,    to_time
  1   Bus1       Stop1        START         0800
  2   Bus1       Stop2        0810          0811
  3   Bus1       Stop3        0820          0821
  4   Bus1       Stop4        0830          0831
  5   Bus1       Stop5        0840          END
  6   Bus2       Stop1        START         0810
  7   Bus2       Stop2        0820          0821
  8   Bus2       Stop3        0830          0831
  9   Bus2       Stop4        0840          0841
 10   Bus2       Stop5        0850          END
 11   Bus3       Stop1        START         0820
 12   Bus3       Stop2        0830          0831
 13   Bus3       Stop3        0840          0841
 14   Bus3       Stop4        0850          0851
 15   Bus3       Stop5        0900          END

I want to display results when searched from stopX to stopY without/within times (from and to times, with these being optional). This is what I did

SELECT     st1.id,
           st1.b_name,
           st1.s_name,
           st1.t_time,
           st2.id,
           st2.b_name,
           st2.s_name,
           st2.f_time
FROM table st1
INNER JOIN table st2
ON         (st1.b_name = st2.b_name)
WHERE      st1.`s_name` = 'Stop2'
AND        st2.`s_name` = 'Stop4'
AND        st1.to_time < st2.from_time
AND        st1.to_time > '0500'
AND        st2.from_time < '0845'

Is this an efficient way to put min load?

Now how do I:

Make the time fields optional?
Stop searching from and to same s_name (like Stop3 to Stop3)?

And as the b_name and s_name fields are being repeated should I put them into a separate table as each bus may have 10-30 stops and a few hundred trips (which may increase)?

If you have two questions -- one needing times, one not needing them, then you should pick between two different queries.  That will be a lot simpler and cleaner.

For performance, have

INDEX(s_name, to_time)
INDEX(b_name, s_name, from_time)

(These two indexes should be pretty good; other indexes may work equally well.)


Error with Oracle trigger, invalid identifier I have the following DML statement that is working perfectly, i have tried to make it into a trigger but it isn't working.

DML statement

UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL =
   NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.IN_FLIGHT_ID), 0) * NVL(R.IN_FLIGHT_SEATS_NO,0) +
   NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.OUT_FLIGHT_ID), 0)* NVL(R.OUT_FLIGHT_SEATS_NO,0) +
   NVL((SELECT AC.ACC_PRICEPN FROM ACCOMMODATION AC WHERE AC.ACC_ID = R.ACC_ID), 0);

Trigger statement

CREATE OR REPLACE TRIGGER HR_SUBTOTAL
BEFORE INSERT OR UPDATE ON HOLIDAY_RESERVATION
FOR EACH ROW
BEGIN
   SELECT
      NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = :NEW.IN_FLIGHT_ID), 0) * NVL(R.IN_FLIGHT_SEATS_NO,0) +
      NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = :NEW.OUT_FLIGHT_ID), 0) * NVL(R.OUT_FLIGHT_SEATS_NO,0) +
      NVL((SELECT AC.ACC_PRICEPN FROM ACCOMMODATION AC WHERE AC.ACC_ID = :NEW.ACC_ID), 0)
      INTO :NEW.SUBTOTAL
   FROM DUAL;
END;

Errors for TRIGGER HR_SUBTOTAL:

LINE/COL ERROR
-------- ----------------------------------------------------------------
2/4      PL/SQL: SQL Statement ignored
4/92     PL/SQL: ORA-00904: "R"."OUT_FLIGHT_SEATS_NO": invalid identifier

The error has to be in the * multiplying bit.

HOLIDAY_RESERVATION, which is aliased as R in your UPDATE statement , is not in the FROM clause in the SELECT query.


Postgresql log rotation In postgresql, I'm using the following configuration to maintain my weekly logs:

log_truncate_on_rotation = on
log_rotation_age = 1d
log_filename = 'postgresql-%a.log'
log_rotation_size = 0  #just rotate daily

This creates a log file with a name like 'postgresql-Mon.log' and when rotation occurs, it overrides the old file with the same name. This way I'm able to maintain a weekly log.

I now want to move this configuration to keeping the log for just one day, instead of 7 days. How do you suggest I tweak the config given above to achieve this? Do I only need to change the log_filename to postgresql.log? Please advise. Thanks in advance.

Yes, as (like you described) rotation replaces the file with the same name, you can find whichever name you like, without any daily changing bits.


How do you correct a corrupted index in Oracle? I have this ORA error in our logs:

Caused by: java.sql.BatchUpdateException: ORA-01578: ORACLE data block corrupted (file # 8, block # 22921)
ORA-01110: data file 8: '/data/app/oracle/oradata/MYSRVR/datafile/o1_mf_mysrvr_88m82mdj.dbf'

I tried running this in sqlplus:

select segment_name,segment_type,tablespace_name, owner from sys.dba_extents where file_id=8 and 22921 between block_id and block_id + blocks -1;

output is:

SEGMENT_NAME             SEGMENT_TYPE   TABLESPACE_NAME     OWNER
-----------------------    ------------     ----------------    ---------------
PK_ZXCATSET_CATID      INDEX            MYSRVR_IDX_TB    MYSRVR

Now I am stuck on which index is that actual segment name. (I think :/ )
I tried doing this to rebuild that index:

SQL> alter index PK_ZXCATSET_CATID rebuild tablespace MYSRVR_IDX_TB;

*
ERROR at line 1:
ORA-01418: specified index does not exist

Even though if I do "select index_name from dba_indexes", that index name shows up.
What am I doing wrong?  This is a 10.2 Oracle server.

Thanks in advance!

There is a command line utility dbv which can scan a datafile a will report and mark corrupted blocks. If you want for "fix" it you can either use RMAN block recover option - if you run Ent. Edition and if you have RMAN backup.

Otherwise search for document named "How to format corrupted block" on Metalink. This describes a situation when a table block is corrupted but it is applicable for indexes too.
First of all you mast mark the block as corrupted.

PS: the is also an SQL comment "ALTER TABLE  VALIDATE STRUCTURE CASCADE", this will also generate a report, but will not fix anything.


Update MySQL user table of active/inactive members based on array I have a MySQL table of unique users. I have an 'active' boolean column (0 or 1) to denote whether the user is currently subscribed to the site. The primary table will hold the 'active' boolean column, and it might contain as many as ~30,000 IDs (active and not). I plan to populate that table via an array of active users.

Right now, I'm dumping all of the active IDs into a temporary table (T2) from the active-user array. From that table, I'm populating the primary table (T1) with any IDs that don't already exist.

INSERT INTO T1 (id_hashed, active_boolean, history, date)
SELECT id_hashed, '1', '6-29-18 on :: history', '$time'
FROM T2 t2
WHERE NOT EXISTS(SELECT 1 from T1 t1 where t2.id_hashed = t1.id_hashed) ";

Using two further queries, I'm also updating the 'active' boolean column in the primary table based on the temporary table of active users.

UPDATE T1
SET active_boolean = 0, history = concat('" . date("n-j-Y",time()) . " off :: ', history)
WHERE active_boolean = 1 AND id_hashed
NOT IN (SELECT id_hashed from T2 )";

UPDATE T1
SET active_boolean = 1, history = concat('" . date("n-j-Y",time()) . " on :: ', history)
WHERE active_boolean = 0 AND id_hashed
IN (SELECT id_hashed from T2 )";

I'm trying to make sure this is scalable, so I'm populating the temporary table (T2) with ~6,000 random IDs and the primary table (T1) with ~12,000 random IDs. Updates to the primary table are taking ~90 seconds with that much data. I'm a bit of a DB noob, so I'm sure there's a more effective way to do this. Thanks for any suggestions.

ETA: Explain Plan for insert into T1

id | 1
select_type | PRIMARY
table | t1
type | ALL
possible_keys |
key |
key_len |
ref |
rows | 3003
Extra | Using where; Using temporary
id | 2
select_type | DEPENDENT SUBQUERY
table | t2
type | ALL
possible_keys |
key |
key_len |
ref |
rows | 3003
Extra | Using where

Don't use EXISTS or IN ( SELECT ... ).

Use a multi-table UPDATE.  Twice.

Once with a JOIN to set the active rows.
Once with a LEFT JOIN ... IS NULL to set the inactive rows.

Or...

Once to set all users to inactive.
Once with a JOIN to set the active rows.


What database name should I use to connect Tomcat with my database I have a dump file in Oracle and I should use Oracle 12c.

I installed it, created a database and imported the dump into a tablespace,
but I can't connect it with my application on Tomcat.

I'm lost between all the names in Oracle 12c. What username should I use to configure the Tomcat server.xml?

In Oracle 12c there are 4 key name-related concepts you need to know:

SID, or instance ID.
Database name / database unique name
Container / Pluggable database names
Service names

The instance ID is an ID that uniquely identifies one database instance running on one server.  On a Linux server, this is normally specified in the $ORACLE_SID variable, but also combined with $ORACLE_HOME to uniquely identify the binaries and data files that make up that database.

Database name is defined at database creation time (and can also be changed later with the nid tool).  This is embedded within the data files for this database.  The DB name can be the same as, or different to the instance ID.  It's possible to have multiple databases of the same name running on one host.  Database unique name can be the same or different to database name in a standalone setup, but in a data guard setup, each database must have a different unique name.

CDBs/PDBs. Oracle 12c introduced the concept of Pluggable DataBases.  With these, you have one "master" database (the container) into which you can plug/unplug one or more PDBs.  This matters because you can't use the SID any more to connect to a pluggable DB - there may be many PDBs open within that same SID.

Service names are a little more ephemeral in nature.  They're a construct which can be used to separate, monitor, control and report on database traffic.  They can be dynamically created or destroyed.  To connect to a PDB you must use (one of) the database's service(s).  They're useful because no matter what the database instance is called, the available services can remain the same.

An example of why services can be useful.  In dev/test/prod environments, if we used SIDs to connect to a database, we'd need to have either all our database instances named the same, or we'd have to change configuration files between environments.  Because we use service names, we can name the instance what we like but ensure the same services are running in all 3 environments.

By default when you create an instance, you end up with an service the same name as the SID + domain.

e.g.:

SID = orcl
domain = oracle.com
default service name = orcl.oracle.com

So, to get back to your question, you will almost certainly need to know the service name.  This can normally be found (on a Linux instance, anyhow) by running (as the oracle user, or equivalent in your environment):

lsnrctl status

e.g.

Service "devcdb.xxxx.national" has 1 instance(s).
  Instance "devcdb", status READY, has 1 handler(s) for this service...
Service "devpdb1.xxxx.national" has 1 instance(s).
  Instance "devcdb", status READY, has 1 handler(s) for this service...


What is the best way to split a string into integers and units? I've got a table of medication prices, each with their own units.
I am trying to split the strength into four columns: Numerator_integer, Numerator_uom, denominator_integer and denominator_uom.
I'm able to split the fraction into a numerator and denominator with a little game of 'find the slash' like so:
2GM/50ML becomes 2GM and 50ML
            if object_id('tempdb..#example') is not null begin drop table #example end

    create table #example (cost decimal(10,4), size decimal(10,4), strength varchar(255))

    insert into #example (cost, size, strength)
    values (918.060,    210.000,    '0.05MG/ML'),
            (808.480,       50.000, '1GM/50ML'),
            (1619.190,   50.000,    '2GM/50ML'),
            (1941.960,    1.000,    '300MG'),
            (30005.560,    40.000,  '5MG/ML')

    ;with costs2 as (select cost, size, strength,
    case when strength like '%/%' then replace(left(strength, charindex('/', strength)),'/','')  else strength end as numerator,
    case when strength like '%/%' then right(strength, len(strength) - charindex('/', strength)) else '1' end as denominator
    from #example),

    costs as (select *, null as numerator_uom, null as denominator_uom from costs2)

    select * from costs

However I can't think of an elegant way to loop through the numerator and split out the numeric half from the varchar half.
Question: how would you split out the numeric and varchar halves of a string?

We can use APPLY to calculate the position of the various things we need, we use each APPLY in series like variables in procedural code.
We assume that the number is the start of the string, followed by arbitrary letters, a slash, another number, then more letters.
Note: no tally function needed here:

select
    cost,
    size,
    strength,
    v4.*

from example e

cross apply ( values (
    charindex('/', e.strength)
) ) as v1 (slash)

cross apply ( values (
    iif(slash = 0, e.strength, left(e.strength, slash - 1)),
    substring(e.strength, nullif(slash, 0) + 1, len(e.strength)) -- len means the rest of string
) ) as v2 (numer, denumer)

cross apply ( values (
    patindex('%[^0-9.]%', numer),  -- first non-number
    patindex('%[^0-9.]%', denumer)
) ) as v3 (numerUnit, denumerUnit)

cross apply ( values (
    try_cast(iif(numerUnit = 0, numer, left(numer, numerUnit - 1)) as real),
    iif(numerUnit = 0, null, substring(numer, numerUnit, len(numer))),

    try_cast(iif(denumerUnit = 0, denumer, left(denumer, denumerUnit - 1)) as real),
    iif(denumerUnit = 0, null, substring(denumer, denumerUnit, len(denumer)))
) ) as v4 (numerator, numeratorUnit, denumerator, denumeratorUnit)


AlwaysOn - Availability Group Listener in a multi-subnet cluster I'm running into some difficulty getting the AG Listener created for a 3 node cluster where node 3 is on a separate subnet.

I'm encountering an error that states

Create failed for Availability Group Listener 'AO-AGL'
None of the IP addresses configured for the availability group listener can be hosted by the server
'NODE3'. Either configure a public cluster network on which one of the specified IP addresses can
be hosted, or add another listener IP address which can be hosted on a public cluster network for
this server. (Microsoft SQL Server, Error: 19456)

How do I handle this situation.  This blog post (http://mohdsohail.wordpress.com/2013/02/14/adding-seconday-replica/) is the only thing I could find for reference and it isn't very clear on the solution.  He states adding a second IP address.  However, what exactly does this look like in DNS?  Do I have one DNS hostname with two IP addresses?

To flesh out the accepted answer a little bit for anyone looking for code examples and order of operations, I had to:

First create the Availability Group on the primary node
Add a static subnet IP to the AG listener on the primary node.
Connect to the secondary node and JOIN it to the Availability Group
Connect BACK to the secondary node and add the second static subnet IP address (which belongs to the subnet of the secondary node) to the AG listener.

Here is a partial sample of the code showing the steps above (obviously with dummy IPs and node names):

:Connect PrimaryAGInstName

USE [master]

GO

CREATE AVAILABILITY GROUP [PrimaryAGInstName]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE [DBName]
REPLICA ON N'PrimaryAGInstName' WITH (ENDPOINT_URL = N'TCP://PrimaryAGInstName.Domain.Info:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)),
    N'SecondaryAGInstName' WITH (ENDPOINT_URL = N'TCP://SecondaryAGInstName.Domain.Info:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));

GO

:Connect PrimaryAGInstName

USE [master]

GO

ALTER AVAILABILITY GROUP [PrimaryAGInstName]
ADD LISTENER N'AG_Listener_Name' (
WITH IP
((N'12.345.67.8', N'255.255.248.0')
)
, PORT=1919);

GO

:Connect SecondaryAGInstName

USE [master]

GO

ALTER AVAILABILITY GROUP [PrimaryAGInstName] JOIN;

GO

:Connect PrimaryAGInstName

USE [master]

GO

ALTER AVAILABILITY GROUP [PrimaryAGInstName]
ADD LISTENER N'AG_Listener_Name' (
WITH IP
((N'12.567.89.0', N'255.255.248.0')
)
, PORT=1919);
GO


Create food database I'm working on a project for a database that contains, in the best way, the nutritional values of foods. They are divided according to type of food, eg Breakfast, Lunch, Dinner. For portions of the food, for example ml, g, etc. oz. That have the name of each food and all of its nutritional values.

Like this.

Food
==========
ID
NAME

NUTRITIONAL_FACTS
==========
ID
NUTRITIONAL_FACTS_FOOD_ID
KCAL
FAT
SUGAR
PROTEINS

PORTIONS
==========
ID
PORTION
FOOD_ID

TYPE_FOOD
=========
ID
TYPE_FOOD
FOOD_ID

Could someone tell me which solution would be most appropriate for the design of this database?

Some thoughts on the design of this database:

For portions, is there only ever one per food? If so it belongs in the food table.
Similarly, with the nutritional facts, only per food?  If so, probably just in the food table.  If there are multiple portions, it would seem like the nutritional facts are based on the portion.
And for types, only one per food, or can a food be listed as both a lunch and dinner food?  If you have multiple portions, you might have a lunch portion and a dinner portion, and as such the type would be as part of the portions.

So, if only one portion, one nutritional fact, and one type per table, I'd just have it all in the one Food table, potentially with indexes on fields the user may search on.

(edited after more information)

Based on there being multiple portions per food, and the nutritional facts being dependent on the portion, it would make sense to put the nutritional facts in the portion table.  It would seem like your Food and Type tables can remain as you originally posted, so the Portions table looks something like this:

PORTIONS
==========
ID
PORTION
FOOD_ID
KCAL
FAT
SUGAR
PROTEINS


Remind customers of an event 7 days before the event based on a date Scratching my head on something for sometime now and I can't seem to resolve it through MySQL. I have a table that has records of events. I want to remind customers of an event every month, 7 days before it's due date. I have 3 tables called customers, events and event_reminders.

To describe what these tables do:

Customers table - a list of customers
Events table - when the event was created and the day the reminder is set for
Event Reminders table - A list of date/time for when the reminder was sent out

My thought process is that based on the "reminder_day" field ( 1st, 2nd, 3rd, 30th) you create a custom date, passing in the field called "reminder_day". You then do an interval of -7 days and check if this exists in the event_reminders table for every month. My SQL statement so far is below:

SELECT c.customer_name, c.customer_email, REPLACE(e.reminder_day, RIGHT(e.reminder_day,2), '') as due_day
FROM customers c JOIN events e ON (c.customer_id = e.customer_id)
WHERE NOT EXISTS
(
 SELECT 1 FROM event_reminders r
 WHERE MONTH(r.reminder_date) != MONTH(CURRENT_DATE())
)

The query above is checking the event_reminders table, to check if the current month record exists for an event of a customer but I need it to do a check on the due_day alias with an interval of -7 days.

I hope some genius could help me and it is clearly defined on my end. Much appreciated!

A database is a place to store data.  An application is where that data is manipulated.

MySQL cannot "send email" or otherwise reach outside of itself.

So...  I would have a daily 'cron' job in the OS open the "events" table, read all the rows, compute who needs a reminder, send out the emails, and remove any expired rows from the table.

If you have only thousands of entries in the table, this is quite manageable; if you have millions, then there needs to be some way for the SELECT to filter out most of the table, thereby lightening the load on the app.

To accommodate February, you might want to say 'last' instead of '30th'.  That can be computed as "go forward 1 month, then back up 1 day".  Or, in MySQL, date + INTERVAL 1 MONTH - INTERVAL 1 DAY.

Your definition of Event Reminders table is confusing -- is it a list of future reminders to issue?  Or a list of past reminders sent out.  Pick one.  You seem to need to remember that you have already sent out a reminder; so I prefer that.  Pre-computing the 'future' is not worth the effort (as I already pointed out).