Testing a Hive array for IS NULL says not null I have a table containing an array, and I want to check if it is empty or NULL. It appears that I cannot check the NULL directly! Can anyone shed light on why the NULL check isn't working?
create table `test_array_split`
( `campaign` string
, `questions` array<string>
)
stored as orc ;

insert into `test_array_split` (`campaign`) values ('1');

select
  `campaign`
, `questions`
, size(`questions`)
, case when `questions` is null then 'null' else 'not null' end `isnull`
from `test_array_split`;
+-----------+------------+------+-----------+
| campaign  | questions  | _c2  |  isnull   |
+-----------+------------+------+-----------+
| 1         | NULL       | -1   | not null  |
+-----------+------------+------+-----------+

I just investigated the same issue: In my case it was due to Hive CBO (Cost based optimizer) erroneously replacing the IS NULL evaluator by a constant operator during query planning.
You can try set hive.cbo.enable=false; to see if it solves the issue.
The bug about structs in https://issues.apache.org/jira/browse/HIVE-21778 looks very similar. Haven't tested it yet, however the patch (preventing Calcite type nullability optimization to apply) look like it should solve this bug too.


Moving SQL server 2016 DB's from mirroring to HA We have Windows Server 2016 machines with SQL server 2016 standard edition. Currently mirroring is implemented and now are planning to move to HA with 1 listener for each DB. We have multiple DB's

In few DB stored procedure we have call to other DB with hardcode name.

Once we move to HA with 1 listener for each DB, will this new configuration be any issue for me?

Since you are on standard edition - you will be using Basic Always On availability groups for a single database.

  Once we move to HA with 1 listener for each DB, will this new configuration be any issue for me?

The only issue that I see is that you have to failover all the interrelated databases together (since your app has cross db calls e.g. joins, etc).

I don't see any other issues except the limitations highlighted here.


Postgresql on conflict throws error I am trying to insert rows when target table doesn't match with stage.
I used the following code which seems correct but I am getting error as:

  missing FROM-clause entry for table "S"

INSERT INTO app.CUSTOMERS(NAME, ADDRESS)
SELECT NAME, ADDRESS
FROM STAGE.STG_CUSTOMERS S
ON CONFLICT (NAME) do update
SET NAME = S.NAME
AND ADDRESS = S.ADDRESS;

You need to use excluded as the table alias in the do update section, as follows:

INSERT INTO app.CUSTOMERS(NAME, ADDRESS)
SELECT NAME, ADDRESS
FROM STAGE.STG_CUSTOMERS S
ON CONFLICT (NAME) do update
SET NAME = EXCLUDED.NAME
AND ADDRESS = EXCLUDED.ADDRESS;


How to find existing instances "instance root" (MS SQL)? I'm installing a new instance of MS SQL. There is a step asking to specify instance root. The current DBA is off but I want to follow his logic / way of doing things.
The installer lists the existing instances but does not specify the configured paths. A quick google search doesn't reveal anything useful either.
The question is in the title.

Found it by comparing several articles and looking at existing file structure and registry under:   HKLM\software\Microsoft\Microsoft SQL Server\MSSQL10.INSTANCEID\Setup
There are a few "path" and "dir" references, most of them quite long. The common denominator seems to be the SqlProgramDir key, for all the existing instances. In our case the DBA decided to put them all under "D:\SQL\Data\"


Oracle, Is there a way to request a lock with alter table drop partition? We have an Oracle RAC cluster with about 100 tables that continually receive data.  All tables have a timestamp field and the data is partitioned by date.  Stopping the receipt of the data is not an option.

We have a job that runs every few hours, figures out what partitions correspond to old data that can be deleted and runs

alter table SCH.DATA_TABLE                 drop partition SYS_P1026632 ;

The good news is most of the time, we can drop the partitions and delete the required data.
The bad news is occasionally, the alter table happens at the same time as we are receiving new data and we get an error:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

I have been experimenting with checking to see if there are any locks on the table before running the drop partition but looping, waiting until the table is unlocked does not seem like a very good solution. Plus there is no guarantee that I will actually get in before another process gets the lock.

I have considered requesting a lock on the table, something like

lock table SCH.DATA_TABLE in EXCLUSIVE mode;

and when I get the lock, dropping the partition and releasing the lock but that does not seem like a very good solution either.

I was kind of hoping for an

alter table wait for lock

or something but that does not exist (alter table locks but I need it to wait for the lock rather than just giving up right away.)

Is there a good way to go about this? Is my best bet just to lock/alter/release?

Thank you for any input.

Just user the ddl_lock_timout feature/parameter (11g+):

alter session                set ddl_lock_timeout=1000000; -- long long time
alter table SCH.DATA_TABLE   drop partition SYS_P1026632 ;

The value for ddl_lock_timeout means seconds to wait before the ORA-00054 is raised or the command completes (which is desirable...). Te default is 0 (which means no wait).

DDL With the WAIT Option (DDL_LOCK_TIMEOUT)


How can I optimize and speed up a query Below query never completes and runs for over 14 hours:

SELECT
U.Age, U.CreationDate, U.DisplayName,
P.AcceptedAnswerId, P.ClosedDate, p .Title, P.ViewCount,
C.CreationDate,C.Score ,C.UserId
FROM dbo.Users As U
LEFT JOIN dbo.Posts As P on U.id = P.id
LEFT JOIN dbo.Comments As C on U.id =C.id
WHERE
(
U.Location = 'India'
OR U.Location ='United Kingdom'
)
AND U.LastAccessDate>= '04/02/2018'
AND U.LastAccessDate< '06/02/2018'
AND P.LastActivityDate>= '04/02/2018'
AND P.LastActivityDate< '06/02/2018'
AND U.LastAccessDate>= '04/02/2018'
AND U.LastAccessDate< '06/02/2018'
AND U.Age>10
AND U.Age is NOT NULL

How can I improve and speed it up ?

The biggest problem I see is in your joins. You're joining the "id" of Users, to the "id" of Posts and Comments. This basically says, take every record in Users and join it to every record in Posts, then join it to every record in Comments.

Presuming that the UserID exists as a foreign key in Posts and Comments, the code below should work. If not, you'll need to determine the correct foreign keys for the JOIN statements.

SELECT
    U.Age,
    U.CreationDate,
    U.DisplayName,
    P.AcceptedAnswerId,
    P.ClosedDate,
    P.Title,
    P.ViewCount,
    C.CreationDate,
    C.Score,
    C.UserId
FROM
    dbo.Users AS U
    LEFT OUTER JOIN dbo.Posts AS P
        ON U.UserID = P.UserID
    LEFT OUTER JOIN dbo.Comments AS C
        ON U.UserID = C.UserID
WHERE
    U.Location IN ('India','United Kingdom')
    AND U.LastAccessDate>= '04/02/2018'
    AND U.LastAccessDate< '06/02/2018'
    AND P.LastActivityDate>= '04/02/2018'
    AND P.LastActivityDate< '06/02/2018'
    AND U.Age>10

I replaced the location OR statement with an IN.

I removed the duplicated U.LastAccessDate conditions.

I removed the unnecessary U.Age IS NOT NULL condition.

If this query still runs slowly. My next best guess would be a missing index on Users.LastAccessDate and/or Posts.LastActivityDate.


Monitoring CDC (Change Data Capture) performance We currently have in place CDC, DW team is reading data, everyone happy. Then we needed to add couple new columns. Easy peasy following this answer... well, actually we had some hiccups but that's solved. But that was on a small environment just to check that the steps to follow are ok. Now is time to deploy that on a way bigger environment. So, question:

Is there a way to monitor CDC while the 2nd capture instance is running populating data in parallel with the old capture instance? Extended events maybe? Any recommendation on what to look? IO, log growth, storage, memory grants maybe?

We had a similar requirements like yours to have CDC enabled currently for one of ur database around 5 TB in size.

There are various parameters that you might want to consider when enabling CDC for capture job. Ones like maxtrans and maxscans really matter for what you want to extract regarding the changes happening in log file for tables configured for CDC

Please read here for more on parameters I am discussing above. Also it list dmvs to monitor the scan session for logs and latencies.

Also keep a monitor on transaction log file for both the user database involved in cdc along with growth of tempdb growing unusual. Might not be a scenario in your case but just telling you from our experiences.

I am not able to recall but I believe we browsed somewhere for the event that can monitor performance for CDC using xtended events. Apart from this you need to have a normal DBA monitoring of that sql server which we dbas have to do to avoid any last minute surprises.


turn off (synchronized) database I just found a database, and I have no Idea what to do. I can't drop, update, or anything.

The database is in this state:

How can i turn it off? Because, it's already off !

I tried to restore database ...set online.

Your error message is useful to know that the database you are trying to remove is not in mirroring. It is part of Availablity group.

You can use below command to remove the database from Availablity group :

ALTER AVAILABILITY GROUP YourAG REMOVE DATABASE DBthatYouWantToRemove;


How to speed up mysql query using cache I'm using the following query to create a leaderboard of top 10 people in this week.

SELECT
users.id,
concat(users.first_name, ' ', users.last_name) as name,
users.picture_url,
MAX(rounds.speed) as speed,
AVG(rounds.accuracy) as accuracy,
SUM(rounds.score) as score,
users.level
FROM users INNER JOIN rounds ON users.email=rounds.email
WHERE DATE(rounds.dateTime) BETWEEN CURDATE()-INTERVAL 1 WEEK AND CURDATE()
GROUP BY users.id
ORDER BY score DESC, speed DESC
LIMIT 10

Currently, it takes around 6 seconds to run this query. The table 'rounds' contain around 3000 rows. Soon it will be much bigger. So once the user opens the leaderboard it takes more than 6 seconds to load!

Is there any way of caching or improving the query so that it loads faster?

Database: MySQL Backend: PHP5 Framework: Codeigniter

(1) Consider designing the schema so that you join on users.id, not users.email.  That will lead to a better way to formulate the query -- by computing the aggregates before joining to users.

(2) Don't hide indexed columns inside functions; the index cannot be used.  Instead of

WHERE DATE(rounds.dateTime) BETWEEN CURDATE()-INTERVAL 1 WEEK
                                AND CURDATE()

use

WHERE dateTime >= CURDATE() - INTERVAL 1 WEEK
  AND dateTime  < CURDATE()

(3) Composite indexes:

INDEX(email, dateTime)   -- after doing (2)
INDEX(dateTime)  -- after doing both (1) and (2)


Moving the binary location of MySQL for replication Is it possible to move the contents of /var/lib/mysql when setting up replication instead of doing mysqldump > for export and mysql < for insert on the slave? I want this because in our current setup it takes ages.

We have a mixture of MyISAM and InnoDB.

Yes, you have two options.  Both require none of the data changes in any of the files during copying.

Completely shutdown the server you are going to clone.  Copy it over, make relevant .cnf changes (especially server-id), start up the new instance.
If you're using LVMs you can take an LVM snapshot while the server is running and then copy that snapshot over.

If you're doing this to get a slave running, in both cases you'll want to have run show master status before shutting down/taking the snapshot so you'll know what to CHANGE MASTER TO when spinning up the new slave.  I would recommend setting read_only=1, then check the position.  If going the snapshot route you can set read_only=0 so the master can continue to take writes.


MongoDB - Clarification on choosing randomly distributed shard key In one of my readings i have noticed following statement for choosing randomly distributed shard keys but not able to understand why is it said so. Could someone provide me with detailed explanation on this.
"The only downside to randomly distributed shard keys is that MongoDB isn’t efficient at randomly accessing data beyond the size of RAM."
Thank you.

While it's hard to say for certain without full context, I'm assuming its referring to the need to keep the working set in memory.

A randomly distributed shard key would distribute the workload across an entire index, meaning that the entire index would need to be fit in memory to efficiently handle the workload. Performance would deteriorate once the size of this index on a shard grows larger than RAM, as the index on the shard key would need to be page faulting data in and out of memory.

In contrast, a non-random shard key may have a "hot" subset that handles most of the working set. For example, consider a website where only newer "posts" by users are frequently accessed and older "posts" are rarely accessed. While the indexes on "posts" may be larger than available memory, only subsets of the indexes may need to fit in memory, reducing memory pressure and the potential of page faults.


Oracle 11g XE to 18c XE - What is the difference? Is 18c can be used like 11g XE? I have worked on Oracle 11g XE about two months ago. After that I de installed them about a week ago. Now, I am curious to learn a little bit more about databases and I tried to installed Oracle 11g XE since it is free.

When I tried today to install, the site kept rerouting me to install Oracle 18c XE. Not sure why they force us to download "18c XE"?

In Oracle Site it says, they just released it 2 days ago. Is it okay to download and use like 11g XE? What are the differences between them?

(I can't find any solid evidence why I should use 18C??)

Not sure why they force us to download 18c XE?

They're not forcing you to download 18c XE.  They're just making it harder for you to download older versions

  Is it okay to download and use like 11g XE?

Yes.
I suggest you still read the License Agreement for 18c XE.

  What are the differences between them?

Resource Limits have been increased (information can be found in License Agreement section License Rights)

2GB of RAM (up from 1)
12GB of user data (up from 11)
2 CPU Threads (I don't know if this is the same as 2 CPU cores.  11g was limited to 1 CPU core)

New database features

The ability to handle SCD Type 4 data via Flashback Data Archive (11.2.0.4+)
The ability to handle SCD Type 2 data via Temporal Validity (12c+)
MATCH_RECOGNIZE (12c)
other 12c features
other 18c features

"more or less all EE Features" (list from 18c XE Licensing Information)

EE feature Virtual Private Database (VPD)
Enterprise Manager (missing in 11g XE)
Java inside the database (missing in 11g XE)
Partitioning (EE add-on)
Multitenet (EE add-on - XE is limited to 3 PDBs)
Advance Compression (EE add-on)
In-Memory column store (EE add-on)
Advanced Analytics (EE add-on)

  (I can't find any solid evidence why I should use 18C??)

Because 11.2.0.2 (11g XE) is known to be buggy and full of security holes.

anti-upgrade reasons:  Minimum supported Oracle APEX version is 5.1.4


Assuming that the values in the name column are consistent with the parent_id values (and the resulting tree), you can use a triangular join and GROUP_CONCAT():

SELECT
    t.category_id,
    GROUP_CONCAT(anc.name ORDER BY anc.name
                 SEPARATOR ' > ') AS path
FROM tablename AS t                         -- for every row in t
  JOIN tablename AS anc                     -- find its ancestors
    ON t.name LIKE CONCAT(anc.name, '%')
GROUP BY
    t.category_id ;

Test in SQLfiddle.


How much of a security risk are published conceptual schema? I was requesting the conceptual schemas from a government agency's information system for my research. My request has been denied on the grounds of it being a security risk.

I don't really have extensive database experience so I can't verify that claim. Is disclosing your schema really that big of a security risk? I mean, those are pretty abstract and divorced from the hardware and software implementations. An explanation of how an attacker could exploit conceptual schemas would be appreciated. Thanks.

Agreed with gbn (so +1), but I think there are two other possibilities at play:

It is quite possible that their conceptual schema has a lot of overlap with their physical schema.  Knowing table names gives you a decent head start in planning your SQL injection attacks.
It is very likely they don't have their conceptual schema documented.  Organizations that let programmers design their own databases often don't have any rigour in their database design process, going straight to physical implementation without any initial design.  They may not want to admit this, or they may not want to go to the time and trouble of back-creating a conceptual document that never existed.

Edit: OP has commented that the organization being asked for their conceptual schema is a governement agency.  This to my mind adds another likely possibility:

Civil servants aren't known for their love of risk-taking and so a mid-level functionary in a government department is unlikely to stick their neck out and release information just in case it might draw the attention or ire of someone further up the hierarchy.

I do still think that #2 is the most likely.


The join part uses OR that's why running long I'm trying to make it faster The join part uses OR that's why running  long I'm trying to make it faster

SELECT sc.clientid
            ,sc.dictionid
            ,sc.firstname
            ,sc.lastname
            ,sc.DB
            ,sc.DOLS
            ,sc.dlnum
            ,Cons.Description AS ConS
            ,Conc.Description AS ConC
            ,'False' as Assigned
        FROM    [monitor].[uxref] xref
            INNER JOIN [Monitor].[dbo].[seacc] sc
                    ON (
                            xref.regid = sc.[consid]
                            OR xref.regid = sc.[concid]
                        )
            LEFT JOIN [Monitor].[dbo].Reg Cons ON Cons.RegID = sc.ConsId
            LEFT JOIN [Monitor].[dbo].Reg Conc ON Conc.RegID = sc.ConcId
        WHERE xref.UserId =1000

You may want to try a union of:

 SELECT sc.clientid
        ,sc.dictionid
        ,sc.firstname
        ,sc.lastname
        ,sc.DB
        ,sc.DOLS
        ,sc.dlnum
        ,Cons.Description AS ConS
        ,Conc.Description AS ConC
        ,'False' as Assigned
    FROM    [monitor].[uxref] xref
    JOIN [Monitor].[dbo].[seacc] sc
        ON xref.regid = sc.[consid]
    LEFT JOIN [Monitor].[dbo].Reg Cons ON Cons.RegID = sc.ConsId
    LEFT JOIN [Monitor].[dbo].Reg Conc ON Conc.RegID = sc.ConcId
    WHERE xref.UserId =1000

and

 SELECT sc.clientid
        ,sc.dictionid
        ,sc.firstname
        ,sc.lastname
        ,sc.DB
        ,sc.DOLS
        ,sc.dlnum
        ,Cons.Description AS ConS
        ,Conc.Description AS ConC
        ,'False' as Assigned
    FROM    [monitor].[uxref] xref
    JOIN [Monitor].[dbo].[seacc] sc
        ON xref.regid = sc.[concid]
    LEFT JOIN [Monitor].[dbo].Reg Cons ON Cons.RegID = sc.ConsId
    LEFT JOIN [Monitor].[dbo].Reg Conc ON Conc.RegID = sc.ConcId
    WHERE xref.UserId =1000

i.e.

 SELECT sc.clientid
        ,sc.dictionid
        ,sc.firstname
        ,sc.lastname
        ,sc.DB
        ,sc.DOLS
        ,sc.dlnum
        ,Cons.Description AS ConS
        ,Conc.Description AS ConC
        ,'False' as Assigned
    FROM    [monitor].[uxref] xref
    JOIN [Monitor].[dbo].[seacc] sc
        ON xref.regid = sc.[consid]
    LEFT JOIN [Monitor].[dbo].Reg Cons ON Cons.RegID = sc.ConsId
    LEFT JOIN [Monitor].[dbo].Reg Conc ON Conc.RegID = sc.ConcId
    WHERE xref.UserId =1000
 UNION
 SELECT sc.clientid
        ,sc.dictionid
        ,sc.firstname
        ,sc.lastname
        ,sc.DB
        ,sc.DOLS
        ,sc.dlnum
        ,Cons.Description AS ConS
        ,Conc.Description AS ConC
        ,'False' as Assigned
    FROM    [monitor].[uxref] xref
    JOIN [Monitor].[dbo].[seacc] sc
        ON xref.regid = sc.[concid]
    LEFT JOIN [Monitor].[dbo].Reg Cons ON Cons.RegID = sc.ConsId
    LEFT JOIN [Monitor].[dbo].Reg Conc ON Conc.RegID = sc.ConcId
    WHERE xref.UserId =1000


Insert column name as value Consider the following, in PostgreSQL:

drop table test ;
create table test (result1 text, red smallint, green smallint, blue smallint, results2 text) ;
insert into test values ('red',1,2,3) ;

I would like results2 to contain the value blue, indicating that the maximum value for the integer columns occurs in column blue. This can be achieved by running an UPDATE or a function at a later time.

How do I insert the column name as a value? As an example, the row above would be updated such that it contains:

'red', 1, 2, 3, 'blue'

There will not be NULL values for the integer columns and one of the integers will always be the largest.

(See also here.)

You could create a view that looks like something like this:

create view test_view
as
select a,b,c,d,
       case
          when a = greatest(a,b,c,d) then 'a'
          when b = greatest(a,b,c,d) then 'b'
          when c = greatest(a,b,c,d) then 'c'
          when d = greatest(a,b,c,d) then 'd'
       end
from test;

Note that the above will not work correctly if any of the columns contains a null value because greatest() will return null then. If you need to handle that, you need to use e.g.:

when a = greatest(coalesce(a,0),coalesce(b,0),coalesce(c,0),coalesce(d,0)) then 'a'

When two columns have the same highest value this would display the "first" one. So for (2,2,4,4) it would return c


Can we determine FDs if we are given some tuples? I am taking a databases course and i'm learning about functional dependencies. I have searched around and did not find any information on this. My questions is if we are given a table with some tuples, are we able to determine the FDs for this relation instance?

No. The reason why database designers are interested in FDs is because they are a formalisation of Business Rules - rules about the data which accurately reflect the Business Domain to be modelled in the database. The only sound and practical way to identify FDs is by analysing and understanding a set of business requirements.

Suppose you are designing a database for a members-only club and you are given the data for all current members. You notice that for every member Sex = "Male". Can you conclude from the data that the club only admits men? Of course not. The only way to know that is by knowing what the club rules are. You could conclude from the data that the club is not a women-only club, but that's something different. Knowing what dependencies do not apply isn't the same as knowing what dependencies do apply.

You can of course use data to help you test a set of given FDs (by making sure that the FD holds for every row of some sample data) but you first have to know what FDs to try; you shouldn't expect to derive them from data alone.


Create Folder If Non Existent I need to check if a folder exists and if it does not create it.  So if C:\CSV\red\ does not exist create, and so on.  I use this DDL - how can I create folder if not there?

Create Table bonniebell
(
   name varchar(max)
)

Insert Into bonniebell values ('red'), ('blue'), ('green')

Declare @foldername varchar(max), @fulldirname varchar(max)

Declare folder cursor for

  Select name
  from bonniebell
  order by name asc

  Open folder

  fetch next from folder into @foldername

  while @@fetch_status = 0
  Begin

    Set @fulldirname = "C:\CSV\"

    Set @fulldirname = @fulldirname + @folder

  Fetch Next from folder into @foldername

  END

Close folder

Deallocate folder

You can use the extended stored proc "xp_dirtree".  Store the results in a temp table and get the rowcount.  Zero = not exists.

create table #Files(
subdirectory nvarchar(512),
depth int)

insert into #Files
exec xp_dirtree 'c:\windowsX'

select @@ROWCOUNT

drop table #Files


How to get useful error information from mysql command? I'm trying to script the creation of a database with tables. I'm encountering errors but the error messages are not helpful to me because I'm a novice.

I'm finding the mysql tool is providing mostly useless error messages. It is telling me there's an error but it is not telling me what the error is. mysql -v just echos the command, and does not detail the actual error.

My question is, how do I get useful error information from mysql command?

Here is an example of the script:

# Whitelist table
mysql -v -u"${MARIADB_USER}" -p"${MARIADB_PASS}" -e '
    USE '"${MY_DB}"';
    CREATE TABLE IF NOT EXISTS '"${MY_WHITE_TABLE}"'
    ("key" INT PRIMARY KEY NOT NULL AUTO_INCREMENT, "number" VARCHAR(20) CHARACTER SET utf8);
'

Here is an example of the error message:

--------------
CREATE TABLE IF NOT EXISTS whitelist
        ("key" INT PRIMARY KEY NOT NULL AUTO_INCREMENT, "number" VARCHAR(20) CHARACTER SET utf8)
--------------

ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the ma
nual that corresponds to your MariaDB server version for the right syntax to use
 near '"key" INT PRIMARY KEY NOT NULL AUTO_INCREMENT, "number" VARCHAR(20) CHARA
CTER SE' at line 2

I've tried leaving the column name unquoted, single quoting the column name, and double quoting the column name. At this point I believe the problem lies elsewhere, but I don't know where.

Quoting of column names and table names (and some other things) is with backtics:

(`key` INT ...

You can change that with a sql_mode, but MySQL users rarely do that.

In the long run, I think you will abandon using shell scripts to run MySQL commands.


Is there a better way to handle a multi-level ParentId table structure? I work for a publisher, and our products are mainly books and journals. They are most commonly structured as follows:
Book > Chapter

Book Series > Book > Chapter

Book > Volume > Chapter

Book Series > Book > Volume > Chapter

Journal > Volume > Issue > Article

Journal > Volume > Article

We currently have all of these records stored in the same table with Id and ParentId columns. For example, a book with TitleId = 1 that has 3 chapters would have the following rows:
Book: Id = 1, ParentId = 1
Chapter #1: Id = 2, ParentId = 1
Chapter #2: Id = 3, ParentId = 1
Chapter #3: Id = 4, ParentId = 1

All these records, whether they're books, chapters, journals, articles, etc., can have their Id connected to other tables for things like authors, prices, ownership, etc.
The problem this structure creates for us is that the nesting adds tons of overhead in certain situations. For example, if someone tries to access a journal article they purchased, we need to run multiple queries to know if they do, in fact, have access. We have an ownership table containing Id's of owned products, so we not only need to check if the user owns the journal article's Id directly, but we also need to check for ownership of the Id's for the article's parent issue, volume, and journal. (I.e., if the user owns the entire journal, it's implied they own all of the articles within that journal despite not having explicit access.) So our main "ownership" query is rather bulky since we need it to check for ownership at all possible levels of nesting.
There are many other similar situations. For example, if we need to get all the authors associated with a book, we need to run a query for the book directly, then walk down to each chapter and get all the authors for each one, or if the book has volumes, we need to walk down each volume, and get the authors for each volume's chapters.
Another situation is searching, where we need to basically aggregate all these different types to be seen as relatively equal so a search can be performed on them, and the results, whether they're a book, chapter, journal, articles, etc., all need to appear alongside each other in the search results.
What I'm wondering is if there's a way to improve our setup so we can get relational data faster (things like ownership checks, authors, prices, etc.), by either:

Restructuring our main table to get away from the Id/ParentId setup, or,
Building new tables/views off to the side.

I'm interested in any comments/advice/recommendations anyone has about our current setup, or proposing a new setup.

I would always store the intermediate entities, possibly by adding missing ones as dummy entries, so that you can query using always the same query.
E.g., store

Book > Chapter

as

Book Series (dummy) > Book > Volume (dummy) > Chapter

Now, you can query
SELECT *
FROM
   BookSeries bs
   LEFT JOIN Book b      ON bs.BookSeriesID = b.BookSeriesID
   LEFT JOIN Volume v    ON b.BookID = v.BookID
   LEFT JOIN Chapter c   ON v.VolumeID = c.VolumeID

where BookSeries and Volume will yield NULL columns, except for primary and foreign keys.
So, always store starting with BookSeries and end at the desired level. So If you want to store a book with no volume and no chapter, this is fine. The query will yield NULL columns for volumes and chapters (inlcuding for primary and foreign keys). This is ensured by the LEFT JOIN.
Journals have a different structure. Here two approaches are possible:

Store journals as independent hierarchy in other tables.
(partially) unify the two hierarchies and use a Type or Kind field as a discriminator to differentiate between books and journals
BookSeries > BookOrJournal > Volume +--> Issue > Article
                                    |
                                    \--> Chapter


Proving the usefulness of a specific index I'm looking for a way to prove that some recently added indexes are positively impacting a production system.
I've collected some dmv statistics to show that the indexes are indeed being used (frequently) but need some way to prove that they are having a positive enough impact on the select queries, that any negative impact from the added update / delete time is offput.

I'm looking for any insight / advice on how to go about proving that these indexes have a positive impact.

You need to baseline a whole workload and measure before/after. With just the numbers above I have no idea what they mean. The questions I would have include:

There were 124,000 updates but did they take 10 times longer than they used to?
Did they block users?
Is there more activity because people can do more because it's faster, or just a natural increase in number of queries?

Take a backup. Restore on a dev server. Replay a trace with and without the indexes, and compare (note that the absolute results may differ based on hardware/network etc., and you won't get true effects of concurrency, but the relative differences should be helpful). You can compare a variety of things:

time to run the whole workload
perfmon stuff throughout workload (e.g. it was faster, but did it peg CPU? it was slower, but did it use more CPU too?)
snapshots of sys.dm_os_wait_stats before and after each run
snapshots of sys.dm_exec_query_stats/sys.dm_exec_procedure_stats before and after each run

Make sure you run the workload a couple of times first to prime the cache (because your production system will very rarely be operating on a cold cache).

If you haven't taken a trace of a fairly representative workload, then that's what you need to take the time to do. I can't think of any "fast" ways to prove the impact of these indexes on your actual workload.


Do you still use MyISAM or prefer Aria storage engine? If Aria storage engine (previously called Maria) is the "new" MyISAM, which supports transaction and automatic crash recovery:

Why still use MyISAM ?
Should changing storage engine from MyISAM to Aria be a problem? (lose index or something)

It is very interesting this question would come up because a similar question was asked back in January 2011 ( When is the right time to use MariaDB instead of MySQL, and Why? ). Giving thought to that 16-month-old question and how I answered it in April 2011, here are my answers to your immediate questions:

  Why still using MyISAM ?

MyISAM still has features that are unique and useful for small datasets and read slaves in HA setups

Deadlocking is impossible in low-write, heavy-read environments

MySQL MyISAM Locking question
Benefits of table level locking

Converting to Fixed Row Format creates bigger tables but increases query performance in terms of

character string comparison/compression : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
read/write speed : Which is faster, InnoDB or MyISAM?

Full Faith and Confidence

InnoDB has become the de facto standard of Transactional Storage Engines for MySQL. XtraDB is being accepted and used as well among the Percona Server community. Full faith and confidence seems to rest with InnoDB over XtraDB as present. This could easily change over time as more recognize XtraDB's superior features.
The same equally applies to MyISAM vs Aria. Full faith and confidence currently rests with MyISAM. An example of those who do not have Full faith and confidence are Drizzle users (Drizzle have abandoned MyISAM as an Optional Storage Engine and relegated it to temp table use only)

  To changes storage engine from MyISAM to Maria should it be a problem? (lose index or something)

If you mysqldump the data and the schema into two separate files, you can always import data into table with either the MyISAM or Aria storage engines. Just keep around the data only mysqldump to be sure.


Casting fraction number to decimal throws Arithmetic overflow error I have a table called AssignmentMarks that stores Student's assignment marks for different subjects.
This table has a column called Marks varchar(10)  which stores the marks. The marks can be like 1, 2, and also it can be +, - or -+ which represents each sign a specific marks.

Note: + is 1 mark, - is -1 and -+ is 0.5

While getting a student marks from the table I am facing the Arithmetic Overflow error which I don't know what is the cause of this error.

The query is as follow:

SELECT SUM(
   CAST(
      CASE
       WHEN a.Marks IS NULL OR a.Marks = '' THEN 0
       WHEN a.Marks = '+' THEN 1
       WHEN a.Marks = '-' THEN -1
       WHEN a.Marks = '-+' THEN 0.5
       ELSE a.Marks
      END AS DECIMAL(5,2)
   )
)
FROM AssignmentMarks AS a WHERE a.StudentID=10
AND a.SubjectID=1

After Executing the above query I get the following error.

  Msg 8115, Level 16, State 8, Line 7

  Arithmetic overflow error converting varchar to data type numeric.

Any idea what is the main cause of this error?

Sample Data:  SQL Fiddle

From error message,

  Arithmetic overflow error converting varchar to data type numeric.

My first place where I could change is :

 ELSE a.Marks

and change it to

ELSE try_convert(int, a.Marks)

or convert to decimal , if you want

Later edit:
In case expression you have a few THEN with different data types: int, numeric, varchar. Due to data type precedence, when combines different data types, the lower data type is converted to the type with higher precedence
See here information about it.

you can see this , by this example:

SELECT   SQL_VARIANT_PROPERTY(col,'BaseType') AS 'Base Type',
         SQL_VARIANT_PROPERTY(col,'Precision') AS 'Precision',
         SQL_VARIANT_PROPERTY(col,'Scale') AS 'Scale'
FROM
  (select 0 as col) as a

SELECT   SQL_VARIANT_PROPERTY(col,'BaseType') AS 'Base Type',
         SQL_VARIANT_PROPERTY(col,'Precision') AS 'Precision',
         SQL_VARIANT_PROPERTY(col,'Scale') AS 'Scale'
FROM
  (select 1 as col) as a

SELECT   SQL_VARIANT_PROPERTY(col,'BaseType') AS 'Base Type',
         SQL_VARIANT_PROPERTY(col,'Precision') AS 'Precision',
         SQL_VARIANT_PROPERTY(col,'Scale') AS 'Scale'
FROM
  (select -1 as col ) as a

SELECT   SQL_VARIANT_PROPERTY(col,'BaseType') AS 'Base Type',
         SQL_VARIANT_PROPERTY(col,'Precision') AS 'Precision',
         SQL_VARIANT_PROPERTY(col,'Scale') AS 'Scale'
FROM
  (select 0.5 as col) as a

SELECT   SQL_VARIANT_PROPERTY(col,'BaseType') AS 'Base Type',
         SQL_VARIANT_PROPERTY(col,'Precision') AS 'Precision',
         SQL_VARIANT_PROPERTY(col,'Scale') AS 'Scale'
FROM
  (select '-+' as col) as a

Base Type   Precision   Scale
int         10          0
Base Type   Precision   Scale
int         10          0
Base Type   Precision   Scale
int         10           0
Base Type   Precision   Scale
numeric     1           1
Base Type   Precision   Scale
varchar     0           0


Locked my sa user out So I was trying to change some settings in my Microsoft SQL Server Management Studio 2019. I change the login mode from mixed (Windows and SQL authentication) to only Windows and now I am not able to login with my sa account and I don't have another account which has admin rights.

What should I do? Any suggestion will be highly appreciated.

There are two ways to handle this.

One option is to login to the windows machine as a local administrator, start SQL Server in single user mode (-m startup switch) and you will now be able to get into your SQL server as a sysadmin using Windows authentication.

The other option is to quite simply change back to mixed mode by modifying the registry in windows directly (not supported). On my machine, the registry path for a named instance (named "a") is: Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.A\MSSQLServer. And the value for mixed authentication is LoginMode: 2.


32-bit oracle client install hangs Windows 7/x64 . installing oracle 11.2 32-bit client.  Install proceeds, then at 80% hangs on netca.  I have not had any luck finding a solution.   Any suggestions?

You may have some messy registry keys hurting you.  I've filled more than a few swear jars trying to figure out Oracle Client install issues on Windows, so hopefully my pain can be your gain.  I made a couple of blog articles on the matter outlining it all.

I would suggest you remove the existing client first which may require some manual work, then install a new client on the cleaned system.

Hopefully that will help.


Large table random select with some peculiarity I have a table with 800k rows and I need to select 20 random rows that not are in other 2 tables (tickets already purchased).

I already tried a SQL with NOT IN syntax, but it's still (sometimes 1s in development machine).

Actual SQL:

SELECT id, description
FROM ticket
WHERE id NOT IN ( SELECT id
                  FROM cart_item
                  WHERE DATE(added) = CURDATE() )
  AND id NOT IN ( SELECT id
                  FROM valid_ticket )
  AND id BETWEEN 1 AND 200000
ORDER BY RAND() LIMIT 20

I need some faster. Because grabbing all table and select 20 rand rows is very slow. That function is executed is all pages of a website and I'm thinking how to optimize and cache that query.

Thanks all for support

First, ORDER BY RAND() will collect all the rows, tack on RAND(), sort, and then start work.

Actually, it is not that bad, since you have filtering.  But it may not help a lot.

AND id BETWEEN 1 AND 200000

limits the work to 200K instead of 800K.

The other filters involve what might be a terribly inefficient construct:  NOT IN ( SELECT ... ).  It may be turned into a different construct.  Please provide EXPLAIN SELECT  ...

DATE(added) = CURDATE()

Assuming added is DATETIME or TIMESTAMP, and there are not values in the future, then change to

added >= CURDATE()

and have INDEX(added, id).

After I see the EXPLAIN, I may suggest using EXISTS or LEFT JOIN.


A question regarding MySQL master-slave replication I want to set up a one-master-one-slave replication. But I have a question. Suppose the read-load is distributed between the master and the slave. I first write to the master and then read from the slave. At this time the information has not reached the slave. So my read will fail. But I have written it! How is such a problem solved or avoided in practice?

For some use-cases, the (usually) small delay before the data appears on the slave isn't a problem.

For "critical" reads, you could send them to the master only.

Another solution would be to use so-called synchronous replication, though actual synchronous replication doesn't really exist. The technology used in Galera Cluster (used by MariaDB Cluster and Percona XtraDB Cluster) is virtually synchronous. The same is true for MySQL's InnoDB Cluster.


Rule of thumb for tmp_table_size and max_heap_table_size MySQL properties The title pretty much sums up the question itself: Is there a rule of thumb regarding the values for tmp_table_size and max_heap_table_size MySQL properties?

I've had a serious performance degradation which was caused by the fact MySQL used disk space to filesort a JOIN result.

Increasing tmp_table_size and max_heap_table_size to 3G solved the issue but this is more of a trial and error approach.

Is there a more solid way to calculate an appropriate value for these two?

I recommend a simple 1% of memory for each of those.

max_heap_table_size is the limit on a ENGINE=MEMORY table you CREATE.  Not many people use that engine, if you do, set the max size just before doing the create.
LEAST(max_heap_table_size, tmp_table_size) is the cap on how big to let certain implicit temp tables to get.  These are tables used inside SELECTs (etc) to handle GROUP BY, ORDER BY, subqueries, etc.

Since those temp tables can be created by every connection, and possibly multiple temp tables per query, 1% of RAM is a rather "safe" limit.  When MySQL runs out of RAM, the OS swaps things -- Swapping MySQL is terrible for performance.

Also, in setting these too large, you are stealing RAM from other uses (eg, buffer_pool), thereby potentially slowing down all queries.

Other Rules of Thumb

See also SQL_BIG_RESULT.


More Best Practices To Updating Large Tables? I know there are a number of topics on this question, but I'm always seeking more insights.

I have a large table with a billion+ records. The amount of records could be reduced and archived, but the size will still be large. My task is to change a existing data type of a single column where the old value of data is safe to convert into the type.

Here are some of my approaches:

1 - Drop the constraints on the table that impact the targeted column, drop the indexes that also impact the targeted column, add a new column with NULL's at the end of the table, update the new column with the old column values in chunks of 10K, 50K or 100K increments, drop the old column when data has been copied and reapply indexes for that column only.

2 - Copy all data into a new table with the data type change in chunks as before, verify data is completed, drop the old table, rename the new table to the old and apply indexes.

3 - Import all data from another data source like a flat file to a new table with the data type change using BULK INSERT and MERGE SP's, basically like option 2 with having 2 duplicate tables, verify data, drop old to replace with new table and apply indexes.

What would be the fastest and safest option? Are there other options I'm not considering? I've updated 100 million records for other tables really well with option 1. The bigger the table, the harder option 1 becomes due to the time duration of updating.

I have always gone with Option #2, and have done so in such a way that there was minimal impact on the system as it was always assumed that that application would be running continuously, or down for 30 minutes tops. So, I would build the new table and slowly migrate the data into it over the course of a week. I would use a SQL Agent job to insert rows and an AFTER UPDATE, DELETE trigger to keep already migrated rows in sync.

I have detailed this approach in an article on SQL Server Central: Restructure 100 Million Row (or more) Tables in Seconds. SRSLY!.


How do I identify the column(s) responsible for "String or binary data would be truncated." I am generating some queries automagically with code I wrote to SELECT from a remote Pg database, and insert into a local SQL Server database. However, one of them is generating this error:

[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated. (SQL-22001) [state was 22001 now 01000]
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (SQL-01000) at .\insert.pl line 106.

How do I find out what column is generating that error and lacks the length for the input? Is there a way to do this without brute force-guessing all the varchar?

No, it is not logged anywhere. Go vote and state your business case; this is one on the long list of things that should be fixed in SQL Server.

This was requested years ago on Connect (probably first in the SQL Server 2000 or 2005 timeframe), then again on the new feedback system:

binary or string data would be truncated => error message enhancments

And now it has been delivered in the following versions:

SQL Server 2019
SQL Server 2017 CU12
SQL Server 2016 SP2 CU6

In the very first public CTP of SQL Server 2019, it only surfaces under trace flag 460. This sounds kind of secret, but it was published in this Microsoft whitepaper. This will be the default behavior (no trace flag required) going forward, though you will be able to control this via a new database scoped configuration VERBOSE_TRUNCATION_WARNINGS.

Here is an example:

USE tempdb;
GO
CREATE TABLE dbo.x(a char(1));

INSERT dbo.x(a) VALUES('foo');
GO

Result in all supported versions prior to SQL Server 2019:

  Msg 8152, Level 16, State 30, Line 5
  String or binary data would be truncated.
  The statement has been terminated.

Now, on SQL Server 2019 CTPs, with the trace flag enabled:

DBCC TRACEON(460);
GO

INSERT dbo.x(a) VALUES('foo');
GO
DROP TABLE dbo.x;
DBCC TRACEOFF(460);

Result shows the table, the column, and the (truncated, not full) value:

  Msg 2628, Level 16, State 1, Line 11
  String or binary data would be truncated in table 'tempdb.dbo.x', column 'a'. Truncated value: 'f'.
  The statement has been terminated.

Until you can move to a supported version/CU, or move to Azure SQL Database, you can change your "automagic" code to actually pull the max_length from sys.columns, along with the name which you must be getting there anyway, and then applying LEFT(column, max_length) or whatever PG's equivalent is. Or, since that just means you'll silently lose data, go figure out what columns are mismatched and fix the destination columns so they fit all of the data from the source. Given metadata access to both systems, and the fact that you're already writing a query that must automagically match source -> destination columns (otherwise this error would hardly be your biggest problem), you shouldn't have to do any brute-force guessing at all.


How to auto replicate specific columns only from a table in MySQL I want to make one master DB which replicates specific user data into other databases such as:

username
pass (hashed)
email
pin (hashed)
timezone (int)

They are all in one table and running on MySQL 5.5.37 (possible to update to 5.6).

The reason I want to do this is because I have multiple sites using different databases and they all have different logins. Once I get this done I can enable SSO across them and save more than 50 % of the users a big hassle to not signup more that once on the different sites.

Where should I start? Is there any good info to read? I've been looking in the MySQL help files but could not find any info on how to do this for specific rows.

Why not create one separate db for your SSO, which is accessible for each site?
Copying data around will create problems when two sites are editing "the same field".

If you only want to use a copy to set up, you could:

CREATE TABLE newdb.users LIKE otherdb.users;
INSERT newdb.users SELECT * FROM otherdb.users;

If you really want to continuously synchronise tables between databases, you will have to do it with a custom script.
Most important to consider in that case, will be the question of what to do when conflicts arise?
But I would really consider creating one specific db for SSO.


osm2pgsql: select relation member by role I have a standard postgresql OpenStreetMap database that was filled with data via osm2pgsql. There are different tables for different types (points, lines, polygons, relations) that need to get combined for my specific query:
I want to select all relations with the tags boundary=administrative and admin_level=6. The results need to be searched for a member with the role admin_centre.

Here are my steps by now:

Select member-role list:

SELECT unnest(members)
FROM planet_osm_rels
WHERE ARRAY['admin_level','6']<@tags AND ARRAY['boundary','administrative']<@tags;

Result for one example:

    unnest
--------------
 n240045827
 admin_centre
 w185481610
 outer
 w191987461
 outer
 w166286278
 outer
 w166290573
 outer
 w65481875
 outer
 w166278211
 outer
 w166278224
 outer
 w166278225
 outer
 w166278227
 outer
 w185148605
 outer
 [...]

The n stands for nodes, the w for ways, followed by the unique id that can be found within the respective table (planet_osm_nodes or planet_osm_line). In this example, the admin_centre is the only node, so we can find members that are nodes with LIKE:

SELECT name
FROM planet_osm_point
JOIN (
    SELECT ltrim(member, 'n')::bigint AS osm_id
    FROM (
        SELECT unnest(members) AS member
        FROM planet_osm_rels
        WHERE ARRAY['boundary','administrative']<@tags AND ARRAY['admin_level','6']<@tags) u
    WHERE member LIKE 'n%') x
USING(osm_id);

Here my question: It is possible that there are several nodes, but with different roles. How could I manage to get only the node that has the role admin_centre? Here it would be n240045827.

The result from above should be sufficient for my question without going too deep into detail about the database structure. The solution would be to take every nth and nth-1 element as a 2D array in order to have the mapping between member and role.

Here is the named relation with its admin_center just for clarification:
https://www.openstreetmap.org/relation/62383

If we can make the assumption that the unnest() operation will always return a set where a member "id" is listed followed by its role in a single column of text entries, then I've written up a quick solution for you.

I made a quick data set as:

CREATE TABLE mylist (entry text);

INSERT INTO mylist VALUES
('n240045827'),
('admin_centre'),
('w185481610'),
('outer'),
('w191987461'),
('outer'),
('w166286278'),
('outer'),
('w166290573'),
('outer'),
('w65481875'),
('outer'),
('w166278211'),
('outer'),
('w166278224'),
('outer'),
('w166278225'),
('outer'),
('w166278227'),
('outer'),
('w185148605'),
('outer');

Assuming we can return this, then the script

WITH numbered AS(
  SELECT row_number() OVER() AS row, entry
  FROM mylist)
  SELECT a.entry
  FROM numbered AS a JOIN numbered AS b
  ON a.row = b.row-1 AND b.entry = 'admin_centre';

will return only n240045827.

How does it work?  Well, I just took a straightforward approach to assigning order to the returned list by using row_number() OVER().  In this example, I've used a CTE WITH... to add the row after the fact, but in your case you could add it into your query like so:

...
SELECT ltrim(member, 'n')::bigint AS osm_id
    FROM (
    SELECT row_number() OVER() a AS row, unnest(members) AS member
    FROM planet_osm_rels
...

Finally, I simply join this result to itself, comparing one row to the next by its row number, and looking for the 'admin_centre' text entry.

Here's a SQL Fiddle for testing.

For your perusal:

Common Table Expressions

Window Functions

Edit

If you want to return all rows as column pairs, then use the following approach:

WITH numbered AS(
  SELECT row_number() OVER() AS row, entry
  FROM mylist)
  SELECT a.entry, b.entry
  FROM numbered AS a JOIN numbered AS b
  ON a.row = b.row-1 AND a.row%2 = 1;

Also, here's a SQL Fiddle #2 for testing.


Select which has max date or latest date Here are two tables.

SCHOOL_STAFF

SCHOOL_CODE + STAFF_TYPE_NAME + LAST_UPDATE_DATE_TIME + PERSON_ID
=================================================================
ABE           Principal         24-JAN-13               111222
ABE           Principal         09-FEB-12               222111

PERSONS

PERSON_ID + NAME
=================
111222      ABC
222111      XYZ

Here is my oracle query.

SELECT MAX(LAST_UPDATE_DATE_TIME) AS LAST_UPDATE, SCHOOL_CODE, PERSON_ID
FROM SCHOOL_STAFF
WHERE STAFF_TYPE_NAME='Principal'
GROUP BY SCHOOL_CODE, PERSON_ID
ORDER BY SCHOOL_CODE;

which gives this results

LAST_UPDATE SCHOOL_CODE PERSON_ID
===========+===========+=========
24-JAN-13   ABE         111222
09-FEB-12   ABE         222111

I want to select the first one for the school which has latest date.

Thanks.

Your current query is not giving the desired result because you are using a GROUP BY clause on the PERSON_ID column which has a unique value for both entries.  As a result you will return both rows.

There are a few ways that you can solve this.  You can use a subquery to apply the aggregate function to return the max(LAST_UPDATE_DATE_TIME) for each SCHOOL_CODE:

select s1.LAST_UPDATE_DATE_TIME,
  s1.SCHOOL_CODE,
  s1.PERSON_ID
from SCHOOL_STAFF s1
inner join
(
  select max(LAST_UPDATE_DATE_TIME) LAST_UPDATE_DATE_TIME,
    SCHOOL_CODE
  from SCHOOL_STAFF
  group by SCHOOL_CODE
) s2
  on s1.SCHOOL_CODE = s2.SCHOOL_CODE
  and s1.LAST_UPDATE_DATE_TIME = s2.LAST_UPDATE_DATE_TIME;

See SQL Fiddle with Demo

Or you can use use a windowing function to return the rows of data for each school with the most recent LAST_UPDATE_DATE_TIME:

select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME
from
(
  select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME,
    row_number() over(partition by SCHOOL_CODE
                        order by LAST_UPDATE_DATE_TIME desc) seq
  from SCHOOL_STAFF
  where STAFF_TYPE_NAME='Principal'
) d
where seq = 1;

See SQL Fiddle with Demo

This query implements row_number() which assigns a unique number to each row in the partition of SCHOOL_CODE and placed in a descending order based on the LAST_UPDATE_DATE_TIME.

As a side note, the JOIN with aggregate function is not exactly the same as the row_number() version.  If you have two rows with the same event time the JOIN will return both rows, while the row_number() will only return one.  If you want to return both with a windowing function, then consider using the rank() windowing function instead as it will return ties:

select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME
from
(
  select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME,
    rank() over(partition by SCHOOL_CODE
                        order by LAST_UPDATE_DATE_TIME desc) seq
  from SCHOOL_STAFF
  where STAFF_TYPE_NAME='Principal'
) d
where seq = 1;

See Demo


Stored procedure to compare two columns from different tables and make the insert I have two tables:

Project with fields fld_id (integer), fld_allocated_hours (integer), fld_project_id (integer)
Timesheet with columns fld_id (integer), fld_allocated_time (integer), fld_project_id (integer).

And I have this procedure:

CREATE PROCEDURE sp_insert_allocated_time(p_project_id integer, p_allocated_time integer)
  RETURNS void
  AS
BEGIN
INSERT INTO dbo.Timesheet(fld_id, fld_project_id,fld_allocated_time)
from
(
  SELECT p.fld_id, p.fld_allocated_hours t.fld_allocated_time
  FROM dbo.Project p
  INNER JOIN dbo.Timesheet t
  ON p.fld_id=t.fld_id
  where t.fld_project_id = p_project_id
)AS Alias
GROUP BY fld_id, fld_allocated_days, fld_allocated_time
having SUM(fld_allocated_time) < fld_allocated_hours;
END;

All I want to make this procedure to insert the allocated_time value into the table Timesheet using the parameter fld_project_id  if  SUM(fld_allocated_time) from the table Timesheet is smaller or equal to fld_allocated_hours from the table Project, otherwise show an error message and exit.

How can I do this? I think my procedure code is not even close to what I want to do.

Thanks.

CREATE PROCEDURE sp_insert_allocated_time @p_project_id INT, @p_allocated_time INT
AS

BEGIN

DECLARE @sum_alloc_time int, @alloc_hours int, @fld_id int

select @sum_alloc_time = SUM(fld_allocated_time)
from dbo.Timesheet
where fld_project_id = @p_project_id;

select @alloc_hours = p.fld_allocated_hours, @fld_id = p.fld_id
FROM dbo.Project p
INNER JOIN dbo.Timesheet t
ON p.fld_id=t.fld_id
where t.fld_project_id = @p_project_id;

IF @sum_alloc_time <= @alloc_hours
BEGIN
BEGIN TRAN;
INSERT INTO dbo.Timesheet(fld_id, fld_project_id,fld_allocated_time)
VALUES (@fld_id, @p_project_id, @p_allocated_time);
COMMIT TRAN;
END;

END;


When SysServer and Server Property Don't Match Pinal suggests removing and adding the correct name based on the below difference discrepancy to fix this CDC error.  That may be correct for a solution, but would this affect anything else?  I tested an ADO connection to both connection instances and they both work, but I'm curious if there will be anything else negatively impacted by his solution.

The code that shows a difference between the two:

SELECT CONVERT(SYSNAME,SERVERPROPERTY('SERVERNAME'))
SELECT * FROM master.dbo.sysservers

One being fully qualified, the other not.  Again, the ADO connection can hit both, but CDC is negatively impact by this.

His solution is like:

sp_dropserver 'domain\instance'
GO
sp_addserver 'full.domain.dom\instance', 'local'
GO

Would that cause connection issues, outside ADO - since I've confirmed ADO still connects to both instance names and types.

master.dbo.sysservers is a deprecated. You should use sys.servers.

If you change your server name after installing sql server then you have to make the entry in master.sys.servers which can be done by sp_dropserver and sp_addserver


Restore MySQL tables with data folder only? I have many MySQL databases with many tables and I’m trying to figure out if I can restore all of it only using the content of my MySQL data folder.

Or should I just not bother and use the following instead?

mysqldump --all-databases > db_backup.sql

You cannot move a single table or database around via the filesystem.

If you have the entire folder, then there is a pretty good chance of being able to move (or restore) all databases.

ibdata1 must be included
Same version of MySQL.
directory and file permissions need to be maintained
and probably other things.


I cant figure out the error probably with the date field in this mysql stored procedure This is my empDetails table I am trying to create a stored procedure on.

mysql> describe empDetails;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(3)      | NO   | PRI | 0       |       |
| name    | varchar(10) | YES  |     | NULL    |       |
| address | varchar(20) | YES  |     | NULL    |       |
| dob     | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

  There is already a procedure with the same name I am trying to replace

mysql> DELIMITER //
mysql> create or replace procedure getEmpInfo(in n INT(3),out nam varchar(10),ou
t addres varchar(20),out db date)
    -> begin
    -> select name,address,dob into nam,addres,db from empDetails where id=n;
    -> end//
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'proce
dure getEmpInfo(in n INT(3),out nam varchar(10),out addres varchar(20),out ' at
line 1

There seems to be a problem with the date field but I am new to mysql and I can't get this right.

Syntax issue in the CREATE OR REPLACE. I've not written any procs with this notation so for me it rang alarm bells. I checked both with and without and dropping the 'OR REPLACE' allowed the proc to be created. You can 'DROP PROCEDURE' in advance of creating is for the same effect.

Try with;

DELIMITER //
drop procedure if exists getEmpInfo1;
create procedure getEmpInfo1(in n INT(3),out nam varchar(10),out addres varchar(20),out db date)
begin
select name,address,dob into nam,addres,db from empDetails where id=n;
end//


Error: Relation ["table name"] does not exist I am trying to create tables based on relational schema, but I get "Relation does not exist" errors for some tables and could not find a way to get out of this. I will display my schema, code, and actual errors below. I really feel this might have a simple solution, but I just got stuck into it..

CREATE TABLE Driver
(
Id           CHAR(11)      NOT NULL,
FirstName    VARCHAR(15)   NOT NULL,
LastName     VARCHAR(20)   NOT NULL,
Phone        CHAR(12)      CHECK (char_length(Phone) > 8),

PRIMARY KEY  (Id)
);

CREATE TABLE Driving
(
Id           CHAR(11)         REFERENCES Driver ON DELETE CASCADE,
BusId        INT              REFERENCES Bus    ON DELETE CASCADE,

PRIMARY KEY  (Id, BusId)
);

CREATE TABLE Bus
(
BusId    INT           NOT NULL,
Make     VARCHAR(20)   DEFAULT ('Volvo'),
Model    VARCHAR(20),
Year     SMALLINT      NOT NULL    CONSTRAINT ManufacturingYear
                                         CHECK ((Year > 1970) AND
                                                (Year < date_part('year', current_date) + 1)),
 Space   SMALLINT      CHECK (Space > 0),
 Route   SERIAL,

 PRIMARY KEY (BusId),
 FOREIGN KEY (Route) REFERENCES Route ON DELETE SET NULL
 );

CREATE TABLE Route
(
RouteId          SERIAL         NOT NULL,
StartingPoint    SMALLINT       NOT NULL,
EndingPoint      SMALLINT       NOT NULL,

PRIMARY KEY(RouteId),
FOREIGN KEY (StartingPoint) REFERENCES Stop,
FOREIGN KEY (EndingPoint) REFERENCES Stop
);

CREATE TABLE RouteStops
(
Route       SERIAL,
Stop        SMALLINT,

PRIMARY KEY(Route, Stop),
FOREIGN KEY (Route)  REFERENCES Route  ON DELETE CASCADE,
FOREIGN KEY (Stop)   REFERENCES Stop   ON DELETE CASCADE
);

CREATE TABLE Stop
(
StopId    SMALLINT      NOT NULL,
Name      VARCHAR(30)   DEFAULT ('Station'),

PRIMARY KEY(StopId)
);

Errors:

ERROR:  relation "bus" does not exist
ERROR:  relation "route" does not exist
ERROR:  relation "stop" does not exist
ERROR:  relation "route" does not exist

You try to create relations (constraint) before create parent objects, for example:

order #2:

CREATE TABLE Driving
(
Id           CHAR(11)         REFERENCES Driver ON DELETE CASCADE,
BusId        INT              REFERENCES Bus    ON DELETE CASCADE,

PRIMARY KEY  (Id, BusId)
);

but table Bus at this moment not created.

2 possible solutions:

Create tables in order - from parent to child (not always possible)
Create all tables without constraints and add all of them after


Pg_cron crontab log issue We're trying to configure periodic jobs in PostgreSQL. To do this, we have installed on Linux machine, with Postgres 9.6 running, the citusdata pg_cron project.

System information:

OS: Linux pg 4.4.0-72-generic #93-Ubuntu SMP
PG: Postgres 9.6.3 installed from repo 'deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main'
Citusdata pg_cron project https://github.com/citusdata/pg_cron

Following the instructions in the pg_cron repository, we set in postgresql.conf the configuration below:

shared_preload_libraries = 'pg_cron'
cron.database_name = 'our db_name'

Then, on db_name, we created the EXTENSION pg_cron

CREATE EXTENSION pg_cron;

and we scheduled our first Postgres job:

SELECT cron.schedule('45 12 * * *', $$CREATE TABLE testCron AS Select 'Test Cron' as Cron$$);

So, jobid 1 is created and listed in table cron.job.

We expect that at 12:45 the command of the scheduled job will be launched. But nothing happens.

The testCron table is not created and we have no trace in any logs.

We have also defined LOG_FILE in /usr/src/pg_cron/include/pathnames.h to enable logging.

But, after re-compiling the project and restarting the Postgres service, we did not track log for pg_cron.

How can we enable logs for pg_cron to check scheduling result?

As work around, we use this in root's crontab:

su -c "Sql_statement" postgres

but this require full control of the Linux system.

It would be useful to be able to schedule jobs from the db server.

Can someone help us?

Thanks in advance!

To schedule jobs from the db server we'll need to enable trust authentication in pg_hba.conf for the user running the cron job.
We'll also need to either run UPDATE cron.job SET nodename = '' to make pg_cron connect via a local (unix domain) socket or add host all all 127.0.0.1/32 in pg_hba.conf to allow access to the pg_cron background worker via a local TCP connection.

As a basic sanity check to see if logging is enabled, we run SELECT cron.schedule('* * * * *', 'SELECT 1') which will run SELECT 1 at the start of every minute and should show up in the regular postgres log.


What are the reasons to create a key-less, index-less database? TL;DR: why would you create a database with no keys of any kind (not even primary), and no indexes?

I joined a non-profit organization that was 14 months into migration to a custom built front-end for their financial processing.  The organization has about 60 staff.

I have 6 years of software development experience, so they trust me enough to give me access to their database.

Lo and behold, the front-end SQL Server production database has absolutely no keys of any kind and no indexes.  However, the interface actually works well.  The external company that created the system are relatively responsive in creating new features, and the system actually works.

I've been working there for about 6 months.  Most of the problems are relatively minor.  I haven't seen any catastrophic errors due to mistakes on the company's part.  Then again, there are no UAT (user acceptance tests) at all, and I have no idea how QA works at this external company.  I only have contact with a sales/technical liaison.

I highly suspect this company is simply fleecing the non-profit for maintenance fees in the future.  I got so sick of the slow DB that I added in a bunch of multi-column, non-clustered, non-unique indexes based on the most frequently used reports, and increased the DB speed by probably 100 times (I didn't bother to benchmark).

I have never seen this type of setup before, and the solution is so stupidly easy that I'm having difficulty understand why it wasn't done.  I tried asking the sales contact but he just doesn't know what I'm talking about.

Am I correct with my assessment or is there a legit reason to do this?

Clarifications:

"Is the DB relationship in nature?"

I believe so.  They run weekly and monthly DR/CR reports.  It is very much an accounting DB.  Even though there is no primary key setup, they have enforced the uniqueness at the application level.

"How old is the DB"

It is suppose to be a brand new, custom built system, running on SQL Server 2014.  However, I also found out they put the DB in 2008 compatibility mode (compatibility code 100).  I've asked about this but my contact haven't replied to me.

There are a number of possibilities listed in the comments. Several are not "legitimate" reasons not to have indexes or primary keys: ignorance on the part of the application developer, or accidental deletion of all indexes.

In a system where INSERT operations are prioritized over anything else, technically, indexes do slow things down. It may be that slow response for reporting, etc. was considered acceptable, as long as inserts completed as fast as possible.

Aaron Bertrand makes a critical note - a primary key is a constraint. It puts a restriction on the data, preventing duplicate values from being entered.  The same holds true for a unique index.

With this is mind, there's at least one possible reason to avoid implementing those: the application is designed to manage the relation aspects of the data at the application level.

This is more likely if the application was originally developed a long time ago (say the 1980s or 1990s). I worked with a SQL Server DB for about 10 years that was supporting an application originally developed in FoxBase, and it had no foreign keys; all the relationships were maintained via triggers. They'd started using foreign keys for newer tables, but only started changing existing tables a few years ago.

Depending on how the code's written, it's actually possible that you could break the application by adding primary keys or unique indexes. It's unlikely, but not impossible.

Adding regular indexes would only break things if the application really needs to keep inserts as lean as possible. That may have been a limitation at some point, but it may be that it isn't any longer.

Note that this is just one possible scenario. As Aaron recommended, the best option is to communicate with the vendor. Checking to see how much tuning you can do to optimize your organization's workload isn't a bad idea, and respecting the application's creators (whether they deserver it or not :-) ) is a good way to start a relationship.


Enforce known functional dependency in a group by clause Let's consider I have the following tables:

    CREATE TABLE users(
        id serial PRIMARY KEY,
        age integer
    )

    CREATE TABLE products(
        id serial PRIMARY KEY,
        sku character varying(255),
        user_id integer REFERENCES users
    )

So basically a product would belong to a user and a user could have many products.

I want the ability to load users with their products doing the aggregation at the database level (Maybe wrongly but I assume it would be easier and probably more efficient than running some aggregation code in my application layer: Postgres comes with nice aggregation functions, why not using them !)

So I would run something like

SELECT "users".*, json_agg("products".*) as "products"
  FROM "users" LEFT JOIN "products" ON "users"."id" = "products"."user_id"
  GROUP BY "users"."id"

All good, my driver can parse JSON structures and I have nothing to do on the application layer side.

The problem rises if I want to introduce some sort of pagination:

WITH "users" AS (SELECT * FROM "users" ORDER BY "id" LIMIT 20)
SELECT "users".*, json_agg("products".*) as "products"
FROM "users" LEFT JOIN "products" ON "users"."id" = "products"."user_id"
GROUP BY "users"."id"

I have the known error

  ERROR: column "users.age" must appear in the GROUP BY clause or be used in an aggregate function

As the "users"."id" is not considered as the primary key of the temporary table created by my subquery

I could fix it by adding every users column in the group by clause. But I find it troublesome and to be a shame as I am sure "users"."id" will define in a unique way an item of my subquery.

So, I would like to know if there is a way to tell the database engine "users"."id" is some sort of primary key for my subquery ?

If not, do you see a better way ?

EDIT: This question is quite similar (3 years old though)

Thanks

create table users (user_id int, user_name text);

insert into users values
(1, 'user1'),(2, 'user2'),(3, 'user3'),(4, 'user4'),(5, 'user5');

create table products (user_id int, id int, name text, att1 text);

insert into products values
(2, 1, 'prod1', 'att1'),
(2, 2, 'prod2', 'att1'),
(2, 3, 'prod3', 'att1'),
(2, 4, 'prod4', 'att1'),
(3, 5, 'prod5', 'att1'),
(3, 6, 'prod6', 'att1');

IMHO your first query should throw the same error message.

select
    users.*,
    json_agg(products.*) as products
from
    users
left join products on
    users.user_id = products.user_id;

  ERROR:  column "users.user_id" must appear in the GROUP BY clause or be used in an aggregate function
  LINE 2:     users.*,
              ^

But you can move json_agg to a subquery instead of joining both tables, then you can easily limit the number of returned rows.

select
    users.*,
    (select
        json_agg(products.*)
     from
        products
     where
        user_id = users.user_id)  as products
from
    users
limit 3;

+---------+-----------+------------------------------------------------------+
| user_id | user_name | products                                             |
+---------+-----------+------------------------------------------------------+
| 1       | user1     |                                                      |
+---------+-----------+------------------------------------------------------+
| 2       | user2     | [{"user_id":2,"id":1,"name":"prod1","att1":"att1"},  |
|         |           |  {"user_id":2,"id":2,"name":"prod2","att1":"att1"},  |
|         |           |  {"user_id":2,"id":3,"name":"prod3","att1":"att1"},  |
|         |           |  {"user_id":2,"id":4,"name":"prod4","att1":"att1"}]  |
+---------+-----------+------------------------------------------------------+
| 3       | user3     | [{"user_id":3,"id":5,"name":"prod5","att1":"att1"},  |
|         |           |  {"user_id":3,"id":6,"name":"prod6","att1":"att1"}]  |
+---------+-----------+------------------------------------------------------+

db<>fiddle here


Does SQL Server Only Perform Calculations In A SELECT List Once? Take the following example:

SELECT <CalculationA> As ColA,
       <CalculationB> As ColB,
       <CalculationA> + <CalculationB> As ColC
FROM TableA

Would CalculationA and CalculationB, each be calculated twice?
Or would the optimizer be clever enough to calculate them once and use the result twice?

I would like to perform a test to see the result for myself, however, I am not sure how I could check something like this.

My assumption is that it would perform the calculation twice.
In which case, depending upon the calculations involved, might it be better to use a derived table, or nested view? Consider the following:

SELECT TableB.ColA,
       TableB.ColB,
       TableB.ColA + TableB.ColB AS ColC,
FROM(
      SELECT <CalculationA> As ColA,
             <CalculationB> As ColB
      FROM TableA
    ) As TableB

In this case, I would hope that the calculations would only be performed once?

Please can someone confirm or refute my assumptions?
Or instruct me on how to test something like this for myself?

Thanks.

Most of the information you will need is going to be in the execution plan ( and the plan XML).

Take this query:

SELECT COUNT(val) As ColA,
       COUNT(val2) As ColB,
       COUNT(val) +  COUNT(val2) As ColC
FROM dbo.TableA;

The execution plan  (opened with sentryone plan explorer) shows what steps it went through:

With the stream aggregate aggregating the values for EXPR1005 & EXPR1006

If we want to know what these are, we could get the exact info on these expressions from the query plan XML:

<ColumnReference Column="Expr1005" />
<ScalarOperator ScalarString="COUNT([Database].[dbo].[TableA].[val])">
<Aggregate AggType="COUNT_BIG" Distinct="false">

With the first compute scalar computing ColA & ColB:

And the last compute scalar being a simple addition:

This is reading it as the data flows, in theory you should be reading it from left to right if going over the logical execution.

In that case, EXPR1004  is calling the other expressions, EXPR1002 & EXPR1003. In turn these are calling EXPR1005 & EXPR1006.

  Would CalculationA and CalculationB, each be calculated twice? Or
  would the optimizer be clever enough to calculate them once and use
  the result twice?

Previous tests show that in this case ColC is simplified as an addition of the calculations that are defined as ColA & ColB.

As a result, ColA & ColB are only calculated once.

Grouping by 200 distinct values

If we are grouping by 200 distinct values (val3) the same is shown:

SET STATISTICS IO, TIME ON;
SELECT SUM(val) As ColA,
       SUM(val2) As ColB,
       SUM(val) +  SUM(val2) As ColC
FROM dbo.TableA
GROUP BY val3;

Aggregating down to these 200 distinct values in val3

performing the sums on val & val2 and then adding them together for ColC:

Even if we are grouping on all but one non-unique value, the same addition should be seen for the compute scalar.

Adding a function to  ColA & ColB

Even if we change the query to this:

SET STATISTICS IO, TIME ON;
SELECT ABS(SUM(val)) As ColA,
       ABS(SUM(val2)) As ColB,
       SUM(val) +  SUM(val2) As ColC
FROM dbo.TableA

The aggregations are still not going to be calculated twice, we are simply adding the ABS() function to the resultset of the aggregation, wich is one row:

Ofcourse, running SUM(ABS(ColA) & SUM(ABS(ColB)) will make the optimizer unable to use the same expression for calculating ColC.

If you want to go deeper in when this happens I would nudge you towards Query Optimizer Deep Dive - Part 1 (until Part 4) by Paul White.

Anther way to dive deeper into query execution phases is by adding these hints:

OPTION
(
    RECOMPILE,
    QUERYTRACEON 3604,
    QUERYTRACEON 8605
);

This will expose the input tree as created by the optimizer.

The adding of the two previous calculated values to get ColC is then translated to:

AncOp_PrjEl COL: Expr1004

ScaOp_Arithmetic x_aopAdd

    ScaOp_Identifier COL: Expr1002

    ScaOp_Identifier COL: Expr1003

This information is already present in the Input Tree, even before the simplification phase has taken place, showing that the optimizer immediately knows that it does not have to perform the same calculation twice.


I do not perform SUM with conditions in SQL Server I want to make SUM of a colummn according to various conditions. In this example for the column 'fac_saldo_tot' if value is greater than 500 add the column and group them down the column 'cli_razon_soc'

I have made this query but I do not perform the operation it only stores the higher values to 500 and otherwise it puts 0.
 see image (red column)

    SELECT        cli_razon_soc,
   CASE WHEN t .fac_saldo_tot > 500 THEN SUM(t .fac_saldo_tot) ELSE 0 END AS 'Mayor500'
FROM            cdc_vw_BuroCredito_Facturas95 AS t
GROUP BY cli_razon_soc, fac_saldo_tot
ORDER BY cli_razon_soc, 'Mayor500'

I appreciate the suggestions you can give me

This is the result I intend

SELECT        cli_razon_soc,
SUM(CASE WHEN t .fac_saldo_tot > 500 THEN t .fac_saldo_tot ELSE 0 END) AS 'Mayor500'
FROM            cdc_vw_BuroCredito_Facturas95 AS t
GROUP BY cli_razon_soc, fac_saldo_tot
ORDER BY cli_razon_soc, 'Mayor500'

As the CASE operation is being applied to each row, checking the sum doesn't make sense logically. What we need to do for each row is check the value, if it's above 500 then sum it, else treat the value as 0.

The above code should accomplish this.


Adding two more columns to existing script (backup summary) Can anyone help me to add two more columns to the existing code? I am not a core developer. I want backup_size in GBs and name from msdb.dbo.backupset table, where msdb.dbo.backupset.name = **'XXXXXXX'**. I need this because we are using a third party tool for backups. That tool name is reflecting in msdb.dbo.backupset name column, so I want to filter by that name. Please see code below.

SELECT name,
       recovery_model_desc,
       state_desc,
       d AS 'Last Full Backup',
       i AS 'Last Differential Backup',
       l AS 'Last log Backup'
 FROM (SELECT db.name,
              db.state_desc,
              db.recovery_model_desc,
              type,
              backup_finish_date
        FROM   master.sys.databases db
         LEFT OUTER JOIN msdb.dbo.backupset a ON a.database_name = db.name
       ) AS Sourcetable
 PIVOT (MAX(backup_finish_date) FOR type IN ( D, I, L )) AS MostRecentBackup

You should be able to tweak this for your needs

SELECT bs.database_name AS DbName
    ,CAST(bs.backup_size / 1024.0 / 1024 / 1024 AS DECIMAL(10, 2)) AS BackupSize_GB
    ,bs.backup_start_date AS Backup_StartDate
    ,bs.backup_finish_date AS Backup_EndDate
    ,bs.NAME AS BackupName
    ,bmf.physical_device_name AS DeviceName
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = 'DB_Name'
  AND  bs.NAME = 'BackupSetName'
ORDER BY bs.backup_start_date DESC


Oracle: deleting all services associated with a SID Is the situation when an Oracle instance is accessible via its SID, but not a single Service Name, technically feasible?

Is it possible to delete all Service Names associated with a given SID?

I have already set service_names of my XE instance to an empty string:

SQL> show parameter name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      XE
db_unique_name                       string      XE
global_names                         boolean     FALSE
instance_name                        string      xe
lock_name_space                      string
log_file_name_convert                string
service_names                        string

Still, XE and XEXDB show up in the services list:

SQL> select name from sys.v$active_services;

NAME
----------------------------------------------------------------
XEXDB
XE
SYS$BACKGROUND
SYS$USERS

SQL> SELECT name, enabled FROM sys.dba_services;

NAME                                                             ENA
---------------------------------------------------------------- ---
SYS$BACKGROUND                                                   NO
SYS$USERS                                                        NO
XEXDB                                                            NO
XE                                                               NO

and an attempt to delete any of them fails:

SQL> exec dbms_service.delete_service('XE');
BEGIN dbms_service.delete_service('XE'); END;

*
ERROR at line 1:
ORA-44305: service XE is running
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_SERVICE", line 454
ORA-06512: at "SYS.DBMS_SERVICE", line 343
ORA-06512: at line 1

SYS$BACKGROUND and SYS$USERS are built-in internal services, they are not exposed to the listener and clients.

XE is your default service, because an Oracle database by default has a service with its db_unique_name, and registers itself to a listener running on the default port (1521) with the db_unique_name as service name, and instance_name as instance.

The XEXDB service exists, because you have the XML database option configured, and the dispatchers parameter is set.

You do not need to modify service_names manually, the database handles that parameter based on the service configuration.

In my opinion, the use of services should be promoted, not prevented. Using multiple services for a single database has the benefit of being able to manage clients connecting through different services seperately, when troubleshooting (tracing), or prioritizing workload (resource manager).


How to create root user after running mysql_install_db? I'm installing MySQL on ubuntu and I changed the data directory. I created new data files using mysql_install_db command.

So now I need to access my database using root but it won't let me in. I tried starting the database by skipping the grant tables:

mysqld --skip-grant-tables --skip-networking &

Now I'm in, but the mysql.user table doesn't have a single row, which is logical after creating the data files from scratch, or isn't it ??

So I tried:

CREATE USER 'root'@'localhost' IDENTIFIED BY 'mypass';

But it won't run because I'm running with --skip-grant-tables !

So how can I create a root user from that point ?
And how can I stop the started mysql instance I just started with --skip-grant-tables because it seems I always have to killall mysqld to do it !

There are two techniques

TECHNIQUE #1 : Use an init file

INITFILE=/var/lib/mysql/init-file.sql
echo "SET @newpass = PASSWORD('mypass');" > ${INITFILE}
echo "CREATE USER 'root'@'localhost' IDENTIFIED BY @newpass;" >> ${INITFILE}
chown mysql:mysql ${INITFILE}
service mysql stop
service mysql start --init-file=${INITFILE}
rm -f ${INITFILE}

TECHNIQUE #2 : Inject User into mysql.user

With --skip-grant-tables enabled, run this:

REPLACE INTO mysql.user SET
                  Host = 'root',
                  User = 'localhost',
              Password = PASSWORD('whateverpasswordiwant'),
             Select_priv = 'Y',
             Insert_priv = 'Y',
             Update_priv = 'Y',
             Delete_priv = 'Y',
             Create_priv = 'Y',
               Drop_priv = 'Y',
             Reload_priv = 'Y',
           Shutdown_priv = 'Y',
            Process_priv = 'Y',
               File_priv = 'Y',
              Grant_priv = 'Y',
         References_priv = 'Y',
              Index_priv = 'Y',
              Alter_priv = 'Y',
            Show_db_priv = 'Y',
              Super_priv = 'Y',
   Create_tmp_table_priv = 'Y',
        Lock_tables_priv = 'Y',
            Execute_priv = 'Y',
         Repl_slave_priv = 'Y',
        Repl_client_priv = 'Y',
        Create_view_priv = 'Y',
          Show_view_priv = 'Y',
     Create_routine_priv = 'Y',
      Alter_routine_priv = 'Y',
        Create_user_priv = 'Y',
              Event_priv = 'Y',
            Trigger_priv = 'Y',
  Create_tablespace_priv = 'Y',
                ssl_type = '',
              ssl_cipher = '',
             x509_issuer = '',
            x509_subject = '',
           max_questions = 0,
             max_updates = 0,
         max_connections = 0,
    max_user_connections = 0
;

Then, restart mysql as usual.

See my post In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table


Oracle 12c: Password File Questions For one of my assignments I have been attempting to demonstrate password file functionality. However, I have run into some unexpected behaviors. I have tried searching around for answers but can't find any relevant ones; additionally, behavior differs from what the documentation specifies in some scenarios.

I am using 12c and have made sure that the remote_login_passwordfile parameter is set to EXCLUSIVE.

I'm sure I'm probably just missing some base knowledge as the behaviors are consistent; the problems I am having are as follows:

Deleting the password file itself (without replacing it) and then
doing a subsequent lookup of v$pwfile_users removes all of the
sys-privileged users from the CBD, but users from my PDB still
remain in the lookup. Are PDB passwords stored somewhere other than
that file? How are they showing up if the file no longer exists?
The IGNORECASE option for the new file seems to only have an effect on common users rather than PDB users; newly-assigned CDBs cannot login without the correct case, but PDB users can, again making me wonder about some separate password storage.
In both the CDB and the PDB, the ENTRIES option seems to have no effect - despite setting it to 2, I can continually add way more than 2 users in both containers.
I wanted to demonstrate that the password files actually worked by doing some sort of sysoper operation with one of my newly-privileged accounts, such as shutting the database down. I can do this in the PDB but it will not let me do it in the CDB despite the documentation explicitly stating that sysoper users have that privilege. Am I missing some other piece of information that would allow this to happen? The error I get is insufficient privileges (and yes, I have made sure to connect using as sysoper).

Any insights are greatly appreciated - thanks!

Deleting the password file itself (without replacing it) and then doing a subsequent lookup of v$pwfile_users removes all of the sys-privileged users from the CBD, but users from my PDB still remain in the lookup. Are PDB passwords stored somewhere other than that file? How are they showing up if the file no longer exists?

The password of PDB users(local users) as well as common users with administrative privileges are stored in data dictionary in encrypted form.

SQL> select password from sys.user$ where name='JAY';

PASSWORD
--------------------------------------------------------------------------------
4FF80DF8D8DBE02E

SQL> select username, common from dba_users where username='JAY';

USERNAME      COM
---------     ---
JAY           NO

If no password file exit common user is unable to get sysdba privilege.

SQL> create user testuser identified by testuser container=all;

User created.

SQL> grant sysdba to testuser;
grant sysdba to testuser
*
ERROR at line 1:
ORA-01994: GRANT failed: password file missing or disabled

Unable to add common users to the password file as I have removed the password file prior to creating this user.

But assigning SYSDBA to local users is possible!

SQL> alter session set container=orapdb1;

Session altered.

SQL> create user newuser identified by newuser container=current;

User created.

SQL> grant sysdba to newuser;

Grant succeeded.

It confirms that the password for PDB administrative users are stored in data dictionary rather password file.

  The IGNORECASE option for the new file seems to only have an effect on common users rather than PDB users; newly-assigned CDBs cannot login without the correct case, but PDB users can, again making me wonder about some separate password storage.

Same applies here. As PDB administrative privileged users(local users) are not stored in password file IGNORECASE and ENTRIES do not apply to them.

  In both the CDB and the PDB, the ENTRIES option seems to have no effect - despite setting it to 2, I can continually add way more than 2 users in both containers.

It allows us to store the password of the privileged user until the Operating System block is filled(generally up to 4 passwords per block) as per the Oracle Documentation. I have demonstrated this behavior after the excerpt from Oracle Documentation.

  ENTRIES

  This argument specifies the number of entries that you require the password file to accept. This number corresponds to the number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. The actual number of allowable entries can be higher than the number of users, because the ORAPWD utility continues to assign password entries until an operating system block is filled. For example, if your operating system block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four.

I have created password as:

[oracle@ora12c dbs]$ orapwd file=orapworacdb entries=2

Enter password for SYS:

Then I created four test users and tried to grant the SYSDBA privilege but the grant failed for fourth user. Becuase there were already four users with SYSDBA privilege including SYS.

SQL> select * from v$pwfile_users;

USERNAME               SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                TRUE  TRUE  FALSE FALSE FALSE FALSE      0
C##_ABC                TRUE  FALSE FALSE FALSE FALSE FALSE      0
C##_ABC2               TRUE  FALSE FALSE FALSE FALSE FALSE      0
C##_ABC3               TRUE  FALSE FALSE FALSE FALSE FALSE      0

When I tried to grant SYSDBA to fourth user C##_ABC4 I got the following error.

SQL> grant sysdba to c##_abc4 container=all;
grant sysdba to c##_abc4 container=all
*
ERROR at line 1:
ORA-01996: GRANT failed: password file
'/u01/app/oracle/product/12.1.0/db_1/dbs/orapworacdb' is full

  I wanted to demonstrate that the password files actually worked by doing some sort of sysoper operation with one of my newly-privileged accounts, such as shutting the database down. I can do this in the PDB but it will not let me do it in the CDB despite the documentation explicitly stating that sysoper users have that privilege. Am I missing some other piece of information that would allow this to happen? The error I get is insufficient privileges (and yes, I have made sure to connect using as sysoper).

It depends upon how you have granted the SYSOPER role. To shutdown the database instance user should have SYSOPER privilege on all containers.

The following demonstrate the scenario.

I have common user call C##_TEST.

[oracle@ora12c dbs]$ sqlplus sys@oracdb as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:47:05 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select common from dba_users where username='C##_TEST';

COM
---
YES

I have granted SYSOPER to the user for the ORACDB(CDB) only.

SQL> grant sysoper to c##_test container=current;

Grant succeeded.

Then I have connected to container database using C##_TEST user as SYSOPER and tried to shut down the database and got the ORA-01031 error.

[oracle@ora12c dbs]$ sqlplus c##_test/test@oracdb as sysoper

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:49:05 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> shut immediate;
ORA-01031: insufficient privileges
SQL>

Now I again granted the SYSOPER privilege with the option container=all. And this time the user can shut down the database.

[oracle@ora12c dbs]$ sqlplus sys@oracdb as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:49:36 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> grant sysoper to c##_test container=all;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

[oracle@ora12c dbs]$ sqlplus c##_test/test@oracdb as sysoper

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:50:06 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


Mysqlfailover command - No slave is listed in health status I have successfully created replication using GTID_MODE. It works perfectly. Now I need to setup automatic failover feature in it. I have run the following command.

mysqlfailover --master=root:abc@10.24.184.12:3306 --discover-slaves-login=root:abc

I have got the following results. No slave is listed.

MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Tue May

Master Information
------------------
Binary Log File   Position  Binlog_Do_DB  Binlog
mysql-bin.000016  9568

GTID Executed Set
8fe8b710-cd34-11e4-824d-fa163e52e544:1-1143

Replication Health Status
0 Rows Found.
Q-quit R-refresh H-health G-GTID Lists U-UUIDs U

But when I execute the mysqlrplcheck and mysqlrplshow commands, the slave is listed.

Is this normal?

Workaround for this is to give slave details also:

mysqlfailover --master=root:abc@10.24.184.12:3306 --discover-slaves-login=root:abc --slaves=root:abc@<host>, root:abc@<host>

where  is IP or hostname of slaves.


How to determine if an SSRS data driven subscription job has completed I have a report that generates PDF's using a data driven subscription process.

When the job is started, it shows a success status shortly afterwards, however I believe that this is really asynchronous and therefore cannot be used as an indication of the jobs status. Is there a reliable indicator that can be used?

Ideally this would be something that would allow me to automate a check in a stored procedure.

Using the ReportServer database, I have a script that appears to work.

Querying the same table as my data driven query

select count(distinct param)
from MyDataDrivenSourceTable

and compare the result of that to the below

    SELECT count(*)
  FROM [ReportServer].[dbo].[Schedule]
  INNER JOIN [ReportServer].[dbo].ReportSchedule ON ReportSchedule.ScheduleID = Schedule.ScheduleID
  INNER JOIN [ReportServer].[dbo].[ExecutionLogStorage] ON ExecutionLogStorage.ReportID = ReportSchedule.ReportID
  WHERE Schedule.ScheduleID = '********INSERT JOB NAME GUID HERE********'
  AND TimeStart >= schedule.LastRunTime

Then I should be able to compare the results to know when it has completed.

I'm relying on an internal table to SSRS which could change at any time, so I appreciate this method is totally unreliable, but it seems better than polling to see which files have appeared on disk.

This makes some massive assumptions:

The source table is not updated in-between the start of the SSRS subscription and when the check is performed.
The last run time is updated at the start of the execution.
I have no idea what would happen if the job was called again half way through the run.


Find unauthenticated connections to MongoDB I have many services and users that connect to MongoDB and now I need to switch on authentication. I have created some accounts and tried to ensure most users/services are now using accounts when connecting. I'd like to know if there is any way I can find connections to the database that are not using an account before I restart the mongod with --auth and enforce authentication.

Failing anything better here is a script to parse the log files and find unauthenticated users:

#!/usr/bin/env gawk -f

# Script to parse the MongoDB log file to find any connections that was not authenticated.

BEGIN {
    if (ARGV[1] == "") {
        print "[usage]: ./unauth_connections [mongodb.log]"
        exit 1
    }
}

# Build list of all connections and their IPs
/[[]initandlisten[]] connection accepted/ {
    # Prepend "conn" and remove the leading # from the connection
    # ID so the key is consistent with the log authentication line
    # i.e. turns #5 to conn5
    conn_key = "conn" substr($10, 2)
    # Build the list: conn_key => authenticated
    conn_list[conn_key]
    # Build the list for the IP: conn_key => IP
    ip[conn_key] = $9
}

# Find authenticated connections
/[[]conn[0-9]+[]]  authenticate/ {
    # Remove the brackets from the connection ID so key is
    # consistent with the initial connection log line
    # i.e. turn [conn5] to conn5
    gsub(/[][]/, "", $5)
    # Flag that the connection is authenticated
    conn_list[$5] = 1
}

END {
    # Build a list of unauthenticated users
    for (conn in conn_list) {
       if (!conn_list[conn]) {
          anon[conn] = sprintf("Connection %8s from %20s not authenticated", conn, ip[conn])
       }
    }

    # Sort the list and print in order
    n = asort(anon)
    for (i=1; i<=n; i++) {
        print anon[i]
    }
}