why are estimated rows exponentially increasing with each join? summary
We have an Object-Oriented database schema that we query with Hibernate with:

1 parent table (NAL_Actions)
7 child tables (NAL_*)

If I want to query a couple of these "action"-objects we have to join all 1+7 tables together (because we don't know what type of child object we need) which isn't great but because of the query execution plan generated by SQL Server results in very slow queries (index scans with 2,5 million rows).
details
The parent table also has a foreign key (actionGroups) that according to the fullscan statistics results in 1.15 "action"-rows on average.
This query plan - edit, now also on: paste-the-plan is over estimating the number of rows for each subsequent table that is joined which I and statistics know is never more than two rows as all tables are joined on their (common) distinct primary key, which is also the clustered index, which means that the number of estimated rows cannot increase, as can be seen from this definition of one of the Child-tables:
USE [NAL_PRD]
GO

/****** Object:  Table [dbo].[NAL_Unblocks]    Script Date: 6/24/2020 6:04:40 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[NAL_Unblocks](
    [actionId] [bigint] NOT NULL,
    [unblockingAmount] [decimal](5, 2) NOT NULL,
    [productSequenceNumber] [int] NULL,
 CONSTRAINT [PK_NAL_UNBLOCKS] PRIMARY KEY CLUSTERED
(
    [actionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[NAL_Unblocks]  WITH CHECK ADD  CONSTRAINT [fk_unblocks_actions] FOREIGN KEY([actionId])
REFERENCES [dbo].[NAL_Actions] ([id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[NAL_Unblocks] CHECK CONSTRAINT [fk_unblocks_actions]
GO

As SQL Server falsely assumes the number of rows exponentially increases with each joined table it changes the join strategy for the last 3 tables:

from: "clustered index seek + nested loops"
to:   "clustered index scan + merge join"

This means that we need to scan the clustered index of a table of 2,500,000 rows instead of 1 or 2 index seeks
what I've tried so far

hints work, e.g.: WITH(FORCESEEK) for every join, or: OPTION (LOOP JOIN) for the entire query, (but I cannot change the application)
this leads me to suggest plan guides as the next option (for SQL Server 2016 - 13.0.5102.14) but although that works fine with sp_executesql the plan guide does not seem to take immediate effect in the application, although I took the SQL from the Query Store and updated the statistics (but maybe updating statistics was a false assumption so maybe I need to restart the application and/or drop the plan - but how without DBCC FREEPROCCACHE as I am limited to db_owner permissions, so not VIEW/ALTER SERVER STATE permissions? Maybe I should switch the application / the JDBC driver to use sp_executesql with prepareSQL=2 instead of the default (sp_prepare + sp_execute)?.

What I have not yet tried:

upgrading to SQL Server 2017 to try adaptive query processing, should that fix this?

As you're using hibernate this is going to be a bit cumbersome. You can 'guide' the optimizer by telling him how to do the joins.
For example select * from a inner hash join b on a.x = b.x will tell the optimizer to do a hash join. It could also be a merge join or a loop hint.
This might result into writing the sql queries manually. Using hints should also be one of the last things to try. Your tables might change over time and the hints might not apply/be good for the performance at a later stage (mostly when switching from development to production).


How to organize database for translations, performance? I am working on a new project for an international website. The task is to get this available in 16 languages.

On the database structure, I was wondering if there is any performance advantage or disadvantage between these two approaches. What would be the preferred choice and why?

Approach #1

Approach #2

Generally, it's better practice to encapsulate the data as you did in your second approach.

This is because...

  When a fully normalized database structure is extended to allow it to accommodate new types of data, the pre-existing aspects of the database structure can remain largely or entirely unchanged. As a result, applications interacting with the database are minimally affected.

By following this tenant of normalizing, it's far easier to add, update, or remove rows to a table than it is to alter the table by adding/removing columns. Languages may change over time, but the table ideally should stay the same and not need to be in flux to accommodate changes in the data.


select permission was denied on the object 'sysjobs' The select permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.

I have already tried to grant sqlagentoperatorrole and it didn't work. How do I resolve this error?

You use the "SQL Server Agent Fixed Database Roles" in msdb (MSDN link).

And a "how to" for SSMS: "How to: Configure a User to Create and Manage SQL Server Agent Jobs (SQL Server Management Studio)"

Note: you need a user (MSDN) in one of these roles: no need to GRANT rights to sqlagentoperator


Query for Transactions per second? For performance mornitoring, i would like to have a mysql query which gives the calculated output of TPS. I think tps= Com_commit + Com_rollback / second , but I don't know the query to calculate these formula in mysql. Same related question for TPS had been asked but query was not provided there, so why I ask here. Kindly show me how to calculate values from status in MySQL/MariaDB.

It's a bit clunky, but this should give you the tps since restart of MySQL:

SELECT  SUM(IF(variable_name IN ('Com_commit', 'Com_rollback'),
                   variable_value, 0)) /
        SUM(IF(variable_name = 'Uptime',
                   variable_value, 0))
    FROM  information_schema.GLOBAL_STATUS


Trigger to cascade updates to a second table In my PostgreSQL 9.6 database, I am trying to update 2 fields in table2 with a trigger when table1 is modified.

Function:

CREATE OR REPLACE FUNCTION schema.maj_tbl2()
RETURNS trigger AS
$BODY$

begin
UPDATE schema.table2
SET (contributor, code_tranche) = (user, table1.code_tranche) from schema.table1
where NEW.gid = table2.gid;
return NEW;
END;

$BODY$

Trigger:

CREATE TRIGGER trg_maj_table2
BEFORE UPDATE
ON schema.table1
FOR EACH ROW
EXECUTE PROCEDURE schema.maj_tbl2();

The 2 fields in table2 are modified the same way but for the entire table with the sames values. I can't update just the row in table2 concerned by the update in table1. I feel something is wrong with the where condition but can't fix it.

While this trigger function is expensively incorrect and needs to be fixed, it can't explain the problem you describe. There must be some other cause, not evident from your question, yet. Some other broken trigger on table1 or table2?

This trigger updated the same row in table2 once for every row in table1 and the final value of table2.code_tranche was an arbitrary pick from table1.

Remove from schema.table1:

UPDATE schema.table2
SET   (contributor, code_tranche) = (user, NEW.code_tranche) from schema.table1
WHERE  table2.gid = NEW.gid;

And I assume you are aware that user is s synonym for current_user, returning the user name of current execution context. If it's supposed to be a column name, you have to use NEW."user" instead. (But never use reserved words as identifiers to begin with - like jjanes commented.)

Typically, this kind of trigger indicates a problem with your db design. Try to remove redundancy. Storing information in one table should suffice.


Efficient merging (removing duplicates) of arrays I have two tables, left2 and right2. Both tables will be large (1-10M rows).

CREATE TABLE left2(id INTEGER, t1 INTEGER, d INTEGER);
ALTER TABLE left2 ADD PRIMARY KEY (id,t1);

CREATE TABLE right2( t1 INTEGER, d INTEGER, arr INTEGER[] );
ALTER TABLE right2 ADD PRIMARY KEY(t1,d);

I will perform this type of query:

SELECT l.d + r.d,
       UNIQ(SORT((array_agg_mult(r.arr)))
FROM left2 l,
     right2 r
WHERE l.t1 = r.t1
GROUP BY l.d + r.d
ORDER BY l.d + r.d;

Where for aggregation of arrays I use the function:

CREATE AGGREGATE array_agg_mult(anyarray) (
SFUNC=array_cat,
STYPE=anyarray,
INITCOND='{}');

After concatenating the arrays, I use the UNIQ function of the intarray module. Is there a more efficient way of doing this? Is there any index on the arr field to speed up the merging (with removing duplicates)? Can the aggregate function remove duplicates directly? Original arrays may be considered sorted (and they are unique) if that helps.

The SQL Fiddle is here:

Correct results?
First off: correctness. You want to produce an array of unique elements? Your current query does not do that. The function uniq() from the intarray module only promises to:

remove adjacent duplicates

Like instructed in the manual, you would need:
SELECT l.d + r.d, uniq(sort(array_agg_mult(r.arr)))
FROM   ...
Also gives you sorted arrays - assuming you want that, you did not clarify.
I see you have sort() in your fiddle, so this may just be a typo in your question.
Postgres 9.5 or later
Either way,since Postgres 9.5 array_agg() has the capabilities of my array_agg_mult() built-in out of the box, and much faster, too:

Selecting data into a Postgres array
Is there something like a zip() function in PostgreSQL that combines two arrays?

There have also been other performance improvements for array handling.
Query
The main purpose of array_agg_mult() is to aggregate multi-dimensional arrays, but you only produce 1-dimensional arrays anyway. So I would at least try this alternative query:
SELECT l.d + r.d AS d_sum, array_agg(DISTINCT elem) AS result_arr
FROM   left2  l
JOIN   right2 r USING (t1)
     , unnest(r.arr) elem
GROUP  BY 1
ORDER  BY 1;

Which also addresses your question:

Can the aggregate function remove duplicates directly?

Yes, it can, with DISTINCT. But that's not faster than uniq() for integer arrays, which has been optimized for integer arrays, while DISTINCT is generic for all qualifying data types.
Doesn't require the intarray module. However, the result is not necessarily sorted. Postgres uses varying algorithms for DISTINCT. Big sets are typically hashed, which leaves the result unsorted unless you add explicit ORDER BY. If you need sorted arrays, you could add ORDER BY to the aggregate function directly:
array_agg(DISTINCT elem ORDER BY elem)

But that's typically slower than feeding pre-sorted data to array_agg() (one big sort versus many small sorts). So I would sort in a subquery and then aggregate:
SELECT d_sum, uniq(array_agg(elem)) AS result_arr
FROM  (
   SELECT l.d + r.d AS d_sum, elem
   FROM   left2  l
   JOIN   right2 r USING (t1)
        , unnest(r.arr) elem
   ORDER  BY 1, 2
   ) sub
GROUP  BY 1
ORDER  BY 1;

This was the fastest variant in my cursory test on Postgres 9.4.
SQL Fiddle based on the one you provided.
Index
I don't see much potential for any index here. The only option would be:
CREATE INDEX ON right2 (t1, arr);

Only makes sense if you get index-only scans out of this - which will happen if the underlying table right2 is substantially wider than just these two columns and your setup qualifies for index-only scans. Details in the Postgres Wiki.


Error using view to limit data access between databases in SQL Server I am trying to limit user access to data through a view. I have a database full of replicated data that I do not want the users to be able to access, I also have a reporting database in which I will create views on the replicated data that the users can access.

e.g.
In database ReplData, I have a table refData(value int, source char(1)) with data values :
1, A
2, A
3, B
4, B

I want to use a view in a different database to limit users to just those rows with source = 'A'

So, in database Reporting, I have a view

use Reporting
go

create schema SourceA
go

create view SourceA.refData
as
  select * from ReplData.dbo.refData
   where source = 'A'
go

create user Steve for login Steve
go

grant select on SourceA.refData to [Steve]
go

However, when user 'Steve' connects to Reporting and tries to select from the view, he gets an error

Msg 916, Level 14, State 1
The server principal 'Steve' is not able to access the database 'ReplData' under the current security context

What have I missed?

[edit] The problem is not a select permission, it is a security context. If you can identify what I have missed, please do. I have turned cross database chaining on (see comments) but it has not resolved my issue.

[edit 2]
So the issue was the method I was using to test the view, which was to 'execute as user'. When I set up a new user and connected to SSMS using those credentials, then the view worked fine.

A user must have a security context to access the ReplData database.  That can be their own account, an AD group of which they are a member, or the guest user (if the guest user is enabled in the ReplData database).

No permission need be granted on the ReplData.dbo.refData table as long as the ownership chain is unbroken.  Since the refData table is owned by dbo, the implication is the login that owns the ReplData database must be a database user in the Reporting database and that user must own the SourceA schema.  Alternatively, the Reporting could be owned by the same login that owns the ReplData database and the SourceA schema owned by dbo.

You can change the schema or database owner with ALTER AUTHORIZATION, if needed:

ALTER AUTHORIZATION ON SCHEMA::SourceA TO <ReplData-database-owner-user>;
ALTER AUTHORIZATION ON DATABASE::Reporting TO <ReplData-database-owner-login>;

Also, the DB_CHAINING database option must be on in both the ReplData and Reporting databases.

In the case of stored procedures, you can get around the need for a user context by signing the module with a certificate that exists on both databases.  That's not an option with a view, though.


I am not able to use the sqlplus after intalling 11g on redhat 6.7 When I execute:

$ sqlplus / as sysdba
bash: sqlplus: command not found

and the bash profile for oracle user is :

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
#added for oracle
TMP=/tmp; export tmp
ORACLE_HOSTNAME=prashant.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=prod; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=prod;$PATH; export ORACLE_SID
PATH=/usr/sbin: export PATH
PATH=$ORACLE_HOME/bin:PATH; export PATH

This should sort you out. Remember to login & out again once edited, or do a . .bash_profile to source it. Also, make sure it's executable (chmod u+x ~/.bash_profile ).

PATH=$PATH:$HOME/bin
TMP=/tmp
ORACLE_HOSTNAME=prashant.localdomain
ORACLE_UNQNAME=prod
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
ORACLE_SID=prod
PATH=$ORACLE_HOME/bin:$PATH:/usr/sbin:/sbin

export TMP ORACLE_HOSTNAME ORACLE_UNQNAME ORACLE_BASE ORACLE_HOME ORACLE_SID PATH

The most glaring error was that you were nuking PATH by doing PATH=$ORACLE_HOME/bin:PATH; - there was a $ missing.


Help with combining two queries into one I have two tables, s and c.

This query works for the inner join

select s.cname, c.HName
from c inner join s on (c.pid = s.pid AND c.cid = s.cid)

I'm having trouble with a where clause. There's a flag in table c indicating an error, and I want to display only the names where the error flag is set to 'Y', but it also has to be the most recent entry (checks every hour so many many entries).

So far I have this:

select max(date),cid,pid, case WHEN error_flag='Y' THEN 'Y' ELSE 'N' END AS "Still has Error"
from c
group by cid,pid,error_flag

That query does the job, but I need to essentially make a where clause for the first query that achieves the same thing. Any suggestions?

Note: Using SQL server 2016

Assuming your first query has error_flag and date in one of the tables then something like this would probably work:

SELECT ROW_NUMBER() OVER ( ORDER BY s.Date DESC ) AS Rownum ,
       s.cname ,
       c.HName
FROM   c
       INNER JOIN s ON (   c.pid = s.pid
                           AND c.cid = s.cid )
WHERE s.error_Flag = 'Y' AND Rownum = 1;


Redefining a table with a user-defined type using DBMS_REDEFINITION The application which we developed has one schema, USER1, which has tables, types, packages etc.
I have to move some of the user-defined types to a different schema, The reason for doing this is to enable to editions for user1 to suppport online patching of the application.
USER2. USER1 has few tables which have a dependency on these user-defined types. We recreated the types in USER2, but before I drop the types in USER1, we have to remove the table's dependency on them. To do this, I am thinking of using DBMS_REDEFINITION by creating an intermediate table using the user defined type in USER2 (instead of USER1)

I'm using the following to cast the user defined type in USER1 and to the new one created in USER2,

BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE( 'user1', 'department', 'department_in'
         , 'dept_id dept_id, dept_name dept_name
         , cast(emp as user2.employee_t) emp'
         , dbms_redefinition.cons_use_rowid);
END;

But, I'm getting the following error:

  Error : ORA-22907: invalid CAST to a type that is not a nested table or VARRAY

Below are the details:

User1 has the following objects:

CREATE TYPE employee_t AS OBJECT
(employee_id NUMBER
,employee_name VARCHAR2(30)
,salary NUMBER
,dept_id NUMBER);

CREATE TABLE department
(emp employee_t
,mgr varchar2(40)
,dept_id number
,dept_name varchar2(30));

When we try to enable editions for User1, we get, we use the force option table will be come invalid.

  ERROR at line 1:
  ORA-38819: user USER1 owns one or more objects whose type is editionable and
  that have noneditioned dependent objects

ALTER TYPE RESET for the type gives following error, when there are dependent tables.

  ERROR at line 1:
  ORA-22374: cannot reset the version of a type with table dependents

I tried the following,
a. Recreated the type in User2;

CREATE TYPE employee_t AS OBJECT
(employee_id NUMBER
,employee_name VARCHAR2(30)
,salary NUMBER
,dept_id NUMBER);

b. Created a temp table in user1 using the type in user2

CREATE TABLE department_tmp
(emp user2.employee_t
,mgr varchar2(40)
,dept_id number
,dept_name varchar2(30));

c. Migrated the data using the script:

begin
for cr in (select * from department)
loop
dbms_output.put_line(cr.emp.employee_name);
insert into department_tmp values (user2.employee_t(cr.emp.employee_id,cr.emp.employee_name,cr.emp.salary,cr.emp.dept_id),cr.mgr,cr.dept_id,cr.dept_name);
commit;
end loop;
end;
/

d. dropped the type employee_t and table department in user1
e.renamed the table departmemt_tmp to department
f. enabled editions user1, edition was enabled successfully.

Here I have taken a simple case, there are many such tables in our application (some with nest tables also), the above approach will not be easy. Hence I was exploring the possibility of using DBMS_REDEFINITION.

Please let me know if there is any way to achieve this.

alter type xxx noneditionable;
will allow you to have a table (non-editionable) reference your type (editionable by default) and still enable your schema for editions.


MySQL "case as column" question in WHERE clause I'm setting up a new column with this case statement:

SELECT * ,
CASE t2.field_max_occupancy_value
WHEN  'one' THEN 1
WHEN  'two' THEN 2
WHEN  'three' THEN 3
WHEN  'four' THEN 4
WHEN  'five' THEN 5
WHEN  'six' THEN 6
WHEN  'seven' THEN 7
WHEN  'eight' THEN 8
WHEN  'nine' THEN 9
WHEN  'ten' THEN 10
ELSE 0
END AS numfield
FROM field_data_field_rate_dates t1
LEFT JOIN field_data_maximum_occupancy t2 on t2.entity_id = t1.entity_id
WHERE t1.field_rate_dates_value <=  '2015-07-11'

When I run that query - it works just fine - and I see numfield in the output.

However - when I append something to the WHERE clause...

AND numfield >= 3

It tells me that numfield is an unknown column.

Any ideas as to why?  Is there a declaration I need to make somewhere?  Thanks!

Aliases can't be used in WHERE. There are couple workarounds.
1. Repeat CASE in WHERE as oNare suggests
2. Put numfield >3 in HAVING instead of WHERE (works for Mysql only)
3. Rewrite  query to use inline view syntax :

SELECT a.* FROM
(
SELECT * ,
CASE t2.field_max_occupancy_value
WHEN  'one' THEN 1
WHEN  'two' THEN 2
WHEN  'three' THEN 3
WHEN  'four' THEN 4
WHEN  'five' THEN 5
WHEN  'six' THEN 6
WHEN  'seven' THEN 7
WHEN  'eight' THEN 8
WHEN  'nine' THEN 9
WHEN  'ten' THEN 10
ELSE 0
END AS numfield
FROM field_data_field_rate_dates t1
LEFT JOIN field_data_maximum_occupancy t2 on t2.entity_id = t1.entity_id
WHERE t1.field_rate_dates_value <=  '2015-07-11'
)a
WHERE  numfield >= 3


Cannot enter psql command "FATAL: the database system is starting up" I cannot login into postgresql server using  psql command (to create / update / delete table) because some sort of error like below.

the command :

[root@Server-Database pg_log]# su postgres
bash-4.1$ psql
psql: FATAL:  the database system is starting up
bash-4.1$

I am using postgresql 9.2 in CentOS 6.3

here is log file

[root@Server-Database pg_log]# tail -f postgresql-Mon.log
FATAL:  the database system is starting up
FATAL:  the database system is starting up
FATAL:  the database system is starting up
FATAL:  the database system is starting up
....

startup log :

[root@Server-Database data]# tail -f /var/lib/pgsql/9.2/pgstartup.log
could not write to log file: No space left on device
could not write to log file: No space left on device
could not write FATAL:  lock file "postmaster.pid" already exists
HINT:  Is another postmaster (PID 5872) running in data directory "/var/lib/pgsql/9.2/data"?

for information, this server have a crash one day ago, because empty hdd space, this is the space after crash solving :

Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_serverdatabas-lv_root
                      439G  408G  8.5G  98% /

server successfully restarted (look command below), but things still not resolved, I cannot enter psql command

[root@Server-Database data]# /etc/init.d/postgresql-9.2 restart
Stopping postgresql-9.2 service:                           [  OK  ]
Starting postgresql-9.2 service:                           [  OK  ]

There is no more space on the device:

could not write to log file: No space left on device

Check it with df and make some space


How to represent the array of String in Entity Realtionship Diagram? I am creating a database using PostgreSQL. I am using the Entity Relationship (ER) diagram. I have a table named LiCLSituation as shown in the Figure below, where I am using Circumstance as array of string. Is this a correct way of representation or is it possible to represent arrays in the ER diagram?

In a logical datamodel the array should be a separate table. It will have a foreign key referencing the table it is currently in.

In a physical model, from which DDL will be generated, showing it as an array is the right thing to do as this it the desired type for the column.

Neither is the "correct" representation. Each has a different purpose and is right for its own use.

Some modelling tools allow physical and logical representations to coexists in a single model and hold the mappings between them, some do not. If yours doesn't decide at what level you want to model and be consistent.


Using Powershell can you insert a macro into Access? I am using PowerShell to automate an export of an access databse (.accdb) to postgresql. As a step of that I am using a VBA script to convert all the table and column names to lowercase so they play nicer with postgresql queries.

I would like to insert the macro that would perform this task after I copy the database to a local location. This is prevent the cleanup script ever being run on the production database.

My question is, using powershell is it possible to insert  a macro into an access database? I can find documentation on how to do this with excel but as far as I can tell it doesn't translate to access.

Edit: Alternatively a macro that can create another macro with a given input or a macro that will execute code fed to it via an input parameter.

Doing this via a macro seems to be an odd and unnecesary step. In theory you should only need to create the new schema once.

I would create the new schema in postgress and use an ETL tool to map between them.

I would export the access schema and convert the names to lower case using an editor or as part of the export process. This link gives details on how that can be done with vba https://www.techrepublic.com/article/exporting-access-schema-using-xml/

Another option is to import the schema and then use an alter table statement to rename the columns using lower case. It shouldnt be too hard to write something that can be reused.


How to remove these duplicate records in an Oracle Database 12c? I have these two tables:

messages(id primary key, message_date, created_at, ...)
user_messages(user_id, message_id references messages(id))

I have some duplicate rows in messages:

select user_id, message_date, count(*)
from messages inner join user_messages
on messages.id = user_messages_message_id
group by user_id, message_date;

user_id | message_date | count(*)
1       | 2019-01-01   | 2
1       | 2019-02-01   | 3
1       | 2019-03-01   | 2

How can I remove such duplicates, only retaining one of them, for example the one that its created_at (not message_date) is the minimum?

A lesser-known feature of Oracle databases- every row has a [hidden] column, called ROWID.

These meaningless,, character values can be used to isolate duplicates like this and get rid of them.

This query should get you candidates rows to be deleted:

select user_id, message_date, max(ROWID)
from messages m
inner join user_messages um
      on   m.id = um.message_id
group by user_id, message_date
having count( * )  > 1
order by 1, 2 ;

user_id | message_date | rowid
      1 | 2019-01-01   | AB12CD34EF...56
      1 | 2019-02-01   | AB12CD34EF...78
      1 | 2019-03-01   | AB12CD34EF...89

You can then delete rows using the ROWID directly.

delete from user_messages
where rowid = 'AB12CD34EF...56' ;

All that said, do not be tempted to use them for anything else!!
ROWID's are potentially volatile and so can change on you over time.


How to drop all connections to a specific database without stopping the server? I want to drop all connections (sessions) that are currently opened to a specific PostgreSQL database but without restarting the server or disconnecting connections to other databases.

How can I do that?

The query like this should help (assuming the database is named 'db'):

select pg_terminate_backend(pid) from pg_stat_activity where datname='db';

pid used to be called procpid, so if you're using a version of postgres older than 9.2 you could try the following:

select pg_terminate_backend(procpid) from pg_stat_activity where datname='db';

However you have to be a superuser to disconnect other users.

It might also be useful to REVOKE CONNECT ON DATABASE FROM PUBLIC or something similar, and then GRANT it afterward.


ERROR 1031 (HY000): Table storage engine for 'proc' doesn't have this option I am new to MySQL administration, and this week a customer came to me with a problem. They created an application with Wamp in a Windows Server environment.

For some reason when listing the tables in the MySQL Workbench, an error pops up saying could not be fetched.

And in the output window, the following error is thrown:

They also reported that a stored procedure has disappeared, and when trying to compile it again in MySQL, another error is generated:

ERROR 1031 (HY000): Table storage engine for 'proc' doesn't have this option

Some tables were using the MyISAM engine, so I changed it and now all are as INNODB, and the ROW FORMAT parameters are like Do not Use.

Other information that may be important, this base was developed in a version 5.1, and this base with problems is in version 5.7.14.

UPDATE:  I just tried to run mysqldump, to import on another server.
And this error was thrown:

d:\wamp64\bin\mysql\mysql5.7.14\bin>mysqldump.exe -u root --routines  --all-data
bases --force > dump20170912.sql
mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'bejarti'': Table s
torage engine for 'proc' doesn't have this option (1031)
mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'berbigier'': Table
 storage engine for 'proc' doesn't have this option (1031)
mysqldump: Got error: 1031: Table storage engine for 'proc' doesn't have this op
tion when using LOCK TABLES
mysqldump: Couldn't execute 'show create table `proc`': Table storage engine for
 'proc' doesn't have this option (1031)
mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mysql'': Table sto
rage engine for 'proc' doesn't have this option (1031)
mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'new_schema'': Tabl
e storage engine for 'proc' doesn't have this option (1031)

In MySQL 5.6, five(5) InnoDB tables were introduced to the mysql schema. I mentioned and described those tables in my 4-year-old answer to the post InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6
For MySQL 5.7, it went to 19.
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.12    |
+-----------+
1 row in set (0.00 sec)

mysql> select table_name from information_schema.tables
    -> where table_schema='mysql' and engine='InnoDB';
+---------------------------+
| table_name                |
+---------------------------+
| engine_cost               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
+---------------------------+
19 rows in set (0.53 sec)

mysql>

Please note the remaining mysql tables
mysql> select table_name,engine from information_schema.tables
    -> where table_schema='mysql' and engine <> 'InnoDB';
+------------------+--------+
| table_name       | engine |
+------------------+--------+
| columns_priv     | MyISAM |
| db               | MyISAM |
| event            | MyISAM |
| func             | MyISAM |
| general_log      | CSV    |
| ndb_binlog_index | MyISAM |
| proc             | MyISAM |
| procs_priv       | MyISAM |
| proxies_priv     | MyISAM |
| slow_log         | CSV    |
| tables_priv      | MyISAM |
| user             | MyISAM |
+------------------+--------+
12 rows in set (0.00 sec)

mysql>

As shown, mysql.proc (where stored procedures and functions are stored) is supposed to remain as MyISAM. I have mentioned these table counts before (see the subheading "CAVEAT FOR MySQL 5.6/5.7 users" in my answer Will hacking away ibdata1 destroy databases with only MyISAM tables?)
LESSON HERE
You should never convert any table in the mysql schema into any storage engine. Changing them only made matters worse. The solution to this problem may  have been as simple as executing REPAIR TABLE mysql.proc;.


How can I prevent our IT admins with sysadmin to read data stored inside an encrypted database? I built a web application that stores sensitive data into a SQL Enterprise 2014 database with TDE encryption. My key is safe; I know if someone steals the database file from disk the data is protected. However, if you run SQL Management Studio on the database server, you can query and read the database.

I believe this is because the SQL database loads the database with the masterkey, thus decrypting the DB and making queries readable. But at the same time, I cant prevent an IT admin with sysadmin rights to run queries on the data. If I remove their access to a database table, they could easily re-grant themselves the rights they need to read the data.

Is there anything I can do to prevent this from happening? I thought of saving the text itself in an encrypted format but that will affect my queries to run efficiently.

Per the other comments, one way to block them is to NOT have the IT Admins in the sysadmin role.  Sysadmin can override anything pretty much.

If they really need a high level of access and you dont want to out right encrypt your data, you could try a combination of a custom server role and/or database roles.  As long as you are on at least SQL server 2012 (for the server roles piece), this would allow you to grant them a more controlled level of access to the sql instance.

The real question is "Do they need SysAdmin?"  If the answer is yes, you'll have to encrypt the data.


PROPOSED QUERY

SET @dt0 = '2014-09-17';
SET @dt1 = @dt0 - INTERVAL 1 DAY;
SET @dt2 = @dt0 - INTERVAL 2 DAY;
SET @sql = 'SELECT A.action ACTION,';
SET @sql = CONCAT(@sql,'SUM(IF(B.date=@dt2 AND B.action=A.action,1,0)) "',@dt2,'",');
SET @sql = CONCAT(@sql,'SUM(IF(B.date=@dt1 AND B.action=A.action,1,0)) "',@dt1,'",');
SET @sql = CONCAT(@sql,'SUM(IF(B.date=@dt0 AND B.action=A.action,1,0)) "',@dt0,'"');
SET @sql = CONCAT(@sql,'FROM (SELECT DISTINCT action FROM mytable ');
SET @sql = CONCAT(@sql,'WHERE date>=@dt2 AND date<=@dt0) A,mytable B GROUP BY A.action');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SAMPLE DATA

mysql> drop database if exists alkb;
Query OK, 1 row affected (0.01 sec)

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

mysql> use alkb
Database changed
mysql> create table mytable
    -> (
    ->     id int not null auto_increment primary key,
    ->     date date not null,
    ->     action varchar(10)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into mytable (date,action) values
    -> ('2014-09-15','ACT1'),('2014-09-16','ACT1'),
    -> ('2014-09-16','ACT2'),('2014-09-17','ACT1'),
    -> ('2014-09-17','ACT2'),('2014-09-17','ACT3');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytable;
+----+------------+--------+
| id | date       | action |
+----+------------+--------+
|  1 | 2014-09-15 | ACT1   |
|  2 | 2014-09-16 | ACT1   |
|  3 | 2014-09-16 | ACT2   |
|  4 | 2014-09-17 | ACT1   |
|  5 | 2014-09-17 | ACT2   |
|  6 | 2014-09-17 | ACT3   |
+----+------------+--------+
6 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SET @dt0 = '2014-09-17';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @dt1 = @dt0 - INTERVAL 1 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @dt2 = @dt0 - INTERVAL 2 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @sql = 'SELECT A.action ACTION,';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @sql = CONCAT(@sql,'SUM(IF(B.date=@dt2 AND B.action=A.action,1,0)) "',@dt2,'",');
Query OK, 0 rows affected (0.00 sec)

mysql> SET @sql = CONCAT(@sql,'SUM(IF(B.date=@dt1 AND B.action=A.action,1,0)) "',@dt1,'",');
Query OK, 0 rows affected (0.00 sec)

mysql> SET @sql = CONCAT(@sql,'SUM(IF(B.date=@dt0 AND B.action=A.action,1,0)) "',@dt0,'"');
Query OK, 0 rows affected (0.00 sec)

mysql> SET @sql = CONCAT(@sql,'FROM (SELECT DISTINCT action FROM mytable ');
Query OK, 0 rows affected (0.00 sec)

mysql> SET @sql = CONCAT(@sql,'WHERE date>=@dt2 AND date<=@dt0) A,mytable B GROUP BY A.action');
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+--------+------------+------------+------------+
| ACTION | 2014-09-15 | 2014-09-16 | 2014-09-17 |
+--------+------------+------------+------------+
| ACT1   |          1 |          1 |          1 |
| ACT2   |          0 |          1 |          1 |
| ACT3   |          0 |          0 |          1 |
+--------+------------+------------+------------+
3 rows in set (0.00 sec)

All you have to do is supply the last date and assign it to @dt0;

GIVE IT A TRY !!!


Do I need to update Oracle Client? How does Oracle maintain Oracle Client software? I don't find Patch Updates for the Client Software of Oracle.

It looks like the database server updates include client updates.

See this CPU:

http://www.oracle.com/technetwork/security-advisory/cpuapr2017-3236618.html#AppendixDB

Includes a fix to SQL*Plus which is part of the client.

Bobby


Multiple inserts using select row and variable combination with one SQL statement I'm working with SQL 2008 R2 and have a need to find a specific row in a table that has three fields and then insert multiple rows into the same table with each insert using two values from the found row but the third value is a variable that is different on each insert.  Can this be done?  Here is a non-functioning SQL statement that, I hope, shows what I am trying to do:

INSERT INTO Routings_Doors_Options
               (HeaderID, Option2, Option1)
VALUES (Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA1'),
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA2'),
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA3'),
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA4'),
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA6'),
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA7'),
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA8')
SELECT HeaderID, Option2
FROM  Routings_Doors_Options AS Routings_Doors_Options_1
WHERE (Option1 = 'OGGA')

You'll need to create a temporary table with the new variables, then cross apply them to the existing row.

I've set up an MCVE1:

CREATE TABLE dbo.rdo
(
    HeaderID int NOT NULL
    , Option2 varchar(10) NOT NULL
    , Option1 varchar(10) NOT NULL
);

INSERT INTO dbo.rdo (HeaderID, Option2, Option1)
VALUES (1, 'test', 'OGGA')

This code will insert the extra 8 variables into the table:

CREATE TABLE #variables
(
    Option1 varchar(10) NOT NULL
);

INSERT INTO #variables (Option1)
VALUES ('OGGA1')
    , ('OGGA2')
    , ('OGGA3')
    , ('OGGA4')
    , ('OGGA5')
    , ('OGGA6')
    , ('OGGA7')
    , ('OGGA8');

INSERT INTO dbo.rdo (HeaderID, Option2, Option1)
SELECT r.HeaderID
    , r.Option2
    , v.Option1
FROM dbo.rdo r
    CROSS APPLY #variables v
WHERE r.Option1 = 'OGGA';

This shows the table after the 8 rows have been added:

SELECT *
FROM dbo.rdo r

Results:

╔══════════╦═════════╦═════════╗
║ HeaderID ║ Option2 ║ Option1 ║
╠══════════╬═════════╬═════════╣
║        1 ║ test    ║ OGGA    ║
║        1 ║ test    ║ OGGA1   ║
║        1 ║ test    ║ OGGA2   ║
║        1 ║ test    ║ OGGA3   ║
║        1 ║ test    ║ OGGA4   ║
║        1 ║ test    ║ OGGA5   ║
║        1 ║ test    ║ OGGA6   ║
║        1 ║ test    ║ OGGA7   ║
║        1 ║ test    ║ OGGA8   ║
╚══════════╩═════════╩═════════╝

1 - I own the referenced website


Mongo Db server status with wired Tiger I was testing my DB with Mongo 3.0 wiredTiger setup.
After restoring my database with wiredTIger i ran db.serverStatus.
I found following in console output.

"bytes currently in the cache" : 6009288998 (5.5 GB),
"maximum bytes configured" : 7516192768 (7 GB),
"bytes read into cache" : 18380155206 (17 GB),
"bytes written from cache" : 17020204785 (16 GB),

Could you please tell em what this output means.

I have 16 GB RAM in my server.
Database configuration is as follows:

MongoD instance 1
Databases 4
Original Data size with 2.6.4 : DB1- 25 GB, DB2- 19 GB, DB3- 5 GB, DB4- 3 GB
With wiredTiger : DB1- 3 GB, DB2- 1.9 GB, DB3- 0.9 GB, DB4- 0.5 GB

I was looking for updatye performance but it seems that performance seems to be degraded and when run bulk update on DB3 with 23000 update command
I saw this in Mongostat

    insert query update delete getmore command % dirty % used flushes vsize  res qr|qw ar|aw netIn netOut conn     time
        *0    *0     *0     *0       0     2|0     0.6   80.0       0  7.7G 7.4G   0|0   1|1  137b    15k    6 11:21:27
        *0    *0     *0     *0       0     3|0     0.9   79.9       0  7.7G 7.4G   0|0   1|1  195b    15k    6 11:21:28
        *0    *0     *0     *0       0     1|0     1.2   80.0       0  7.7G 7.4G   0|0   1|1   79b    15k    6 11:21:29
        *0    *0     *0     *0       0     1|0     1.4   80.0       0  7.7G 7.4G   0|0   1|1   79b    15k    6 11:21:30
        *0    *0     *0     *0       0     2|0     1.7   79.9       0  7.7G 7.4G   0|0   1|1  133b    15k    6 11:21:31
        *0    *0     *0     *0       0     1|0     2.0   80.0       0  7.7G 7.4G   0|0   1|1   79b    15k    6 11:21:32
        *0    *0     *0     *0       0     3|0     2.2   80.0       0  7.7G 7.4G   0|0   1|1  195b    15k    6 11:21:33
        *0    *0     *0     *0       0     1|0     2.4   80.0       0  7.7G 7.4G   0|0   1|1   79b    15k    6 11:21:34
        *0    *0     *0     *0       0     1|0     2.6   80.0       0  7.7G 7.4G   0|0   1|1   79b    15k    6 11:21:35
        *0    *0     *0     *0       0     2|0     2.9   80.0       0  7.7G 7.4G   0|0   1|1  133b    15k    6 11:21:36
    insert query update delete getmore command % dirty % used flushes vsize  res qr|qw ar|aw netIn netOut conn     time
        *0    *0     *0     *0       0     1|0     3.0   80.0       0  7.7G 7.4G   0|0   1|1   79b    15k    6 11:21:37
        *0    *0     *0     *0       0     3|0     3.1   80.0       0  7.7G 7.4G   0|0   1|1  195b    15k    6 11:21:38
        *0    *0     *0     *0       0     1|0     3.3   80.0       0  7.7G 7.4G   0|0   1|1   79b    15k    6 11:21:39
        *0    *0     *0     *0       0     1|0     3.5   80.0       0  7.7G 7.4G   0|0   1|1   79b    15k    6 11:21:40
        *0    *0     *0     *0       0     2|0     3.7   80.0       0  7.7G 7.4G   0|0   1|1  133b    15k    6 11:21:41

If you see dirty% is getting increased continuously.
Also after completing the updates i left the Db without untouched (no read and write) But still i was getting netIn and netOut showing bytes.

Could you please check the behaviour and explain it more as Mongo DB document section is not showing any thing in detail or these parameters.

Also i am not sure with one point does compression of index also reduces RAM requirement.

"bytes currently in the cache" : 6009288998 (5.5 GB), "maximum bytes

The number of bytes currently in wiredTiger cache.

  configured" : 7516192768 (7 GB),

Maximum size cache is configured (by default half of physical memory on the server).

  "bytes read into cache" : 18380155206 (17 GB),

Cumulative number of bytes that have been read into the cache since startup.

  "bytes written from cache" : 17020204785 (16 GB),

Cumulative number of bytes that have been written out of the cache into files on disk since startup.

  bulk update on DB3 with 23000 update command

You ran a single command with 23K update operations in it, so you were seeing the results you should have expected - you can see in the mongostat output that commands are constantly running and that you always have active writer.

If you actually want high throughput you should use multiple threads to send the operations in parallel to allow use of all cores on the machine.

  see dirty% is getting increased continuously

That shows that in fact the writes are happening - higher and higher percentage of the cache is being written to (dirtied).  When a checkpoint starts running, the dirty percentage will go down signifying that data from the cache is being persisted to disk (but of course, it's also been written already into write-ahead-log aka journal at the time of original write).

  does compression of index also reduces RAM requirement

Yes, indexes are prefix-compressed and the format is the same on disk and in RAM.


Migrate from Windows to Linux I planned to migrate SQL Server 2008 Standard edition to Linux server instance. I have two problems with this instance:

How to activate this instance?
How to run instance in single mode to restore all databases (with master)?

You cannot run/install SQL Server 2008 or any other production version on Linux OS, this is not allowed or possible, you can only install it on Windows server/client OS.

As per their official Blog, Microsoft announced in March 2016 that is planning to launch SQL Server on Linux: Announcing SQL Server on Linux. The SQL Server version is currently called v.next and will be available on Linux. Tentative date for release is Mid 2017.

There is a public preview version that can be installed/tested now but is not production ready yet: Take a closer look at SQL Server on Linux


How to backup full transaction logs for SQL Server 2008 R2 I have a 32GB database with over 7GB logs (DB name: XYZ). I'd like to copy the DB with all of the logs. I thought that I can simply run:

...
ALTER DATABASE XYZ SET RECOVERY FULL;
BACKUP DATABASE XYZ TO DISC = foo.bak;
BACKUP LOG XYZ TO DISC = foo.bak;
...

RESTORE DATABASE N_XYZ FROM DISC = foo.bak WITH NORECOVERY
  MOVE LogicalNameData TO ....\mylocation\n_xyz.mdf
  MOVE LogicalNameLog TO ...\mylocation\n_xyz.ldf

RESTORE DATABASE N_XYZ FROM DISC = foo.bak WITH NORECOVERY
RESTORE LOG N_XYZ FROM DISC = foo.bak WIT RECOVERY

But when I checked the N_XYZ DB size it turned out that data takes 32GB but logs take only 10MB. I searched on the Internet and I found this link. It says that the DB transaction logs will only contain the pages between now and the last backup. Could that be a reason why my logs are so small? Can I force SQL Server to always copy the full logs? If so how?

Edit: Sorry for such a vague question. Below I described the whole story.

I have a CI which tests the newest code base every night. To be more specific the steps are:

Get the newest code from the repository,
Copy the database (the snapshot of production)
Test/Run the whole App

The CI works fine but way slower than regular development environment. After a couple of hours I figured out that the slow down is caused by the second step. In other words: If I run the CI on freshly copied DB it takes 10 hours but if I rerun the CI on the same DB (so no DB is copied) it takes 3 hours. It looks like I'm loosing DB statistics when copying the backup.

So by all of the logs I meant the transaction logs with statistics.

Does the ALTER DATABASE XYZ SET RECOVERY FULL imply that you are normally running in SIMPLE recovery model?

If so, the logs from the SIMPLE mode period are discarded once they are no longer needed.  There is no time machine to go back and recover the logs that were not saved.  So, to interpret your script:

ALTER DATABASE XYZ SET RECOVERY FULL; -- Begin saving logs
BACKUP DATABASE XYZ TO DISC = foo.bak;-- Full backup gives the log a start point
BACKUP LOG XYZ TO DISC = foo.bak; -- The logs that start with the full can now be backed up.

You can get the full logs by keeping the database in the FULL recovery model at all times.

Also the statistics are not stored in the log files but in the database.


Configuring MySQL (my.cnf) for MyISAM and InnoDB I want to convert some (not all) the MyISAM tables to InnoDB.

Current my.cnf
Show engines output
Server has two 6-core Xeon processors (2 threads per core = 24 threads)

What would be the best configuration? (need to add InnoDB settings, because there are no InnoDB settings in my my.cnf).

You mentioned before you are running

MySQL 5.5.29
16 GB RAM

Here are some good settings to start with
innodb_buffer_pool_size=4G
innodb_buffer_pool_instance=2
innodb_read_io_threads=12
innodb_write_io_threads=12
innodb_io_capacity=300
innodb_log_file_size=128M

Don't do the conversion yet !!!
Put there settings in my.cnf, then do the following
service mysql stop
cd /var/lib/mysql
rm -f ibdata1 ib_logfile*
service mysql start

Now, you can do the conversion.
Give it a Try !!!
UPDATE 2013-05-10 10:44 EDT
Here are my past posts on tuning InnoDB for better CPU usage

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance

Here are my past posts on tuning InnoDB

Feb 16, 2011 : How to safely change MySQL innodb variable 'innodb_log_file_size'?
Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?


When I connect to DB, always connects as tempdb I am trying to run a sybase DB query from a jsp session.

I can connect to the DB fine, but when I run my query, by default it always runs against tempdb.

Is there a way I can make a connection to a different DB and not by default connect to tempdb?

The aim is to be able to run a query for any table chosen by the user from a drop down list. I have the dropdown list working and can successfully select any DB. I know this is working as I am storing the DB selected in a variable and can confirm the name selected is always the name I can see in the variable.

It seems the DBA who has created your login has set tempdb as your default database. You may request him/her to do the change at login definition level as per this article. By default, if none of them are mentioned, user connects to master database. You may use below command:

sp_modifylogin login_name, defdb, "your_database"

Kindly note that above command needs to be run at Sybase Database level and only a System Administrator or System Security Officer can execute sp_modifylogin.

As already suggested by Markp, you can issue the command use <database_name> for changing your database. Alternatively you may use database name in the connection string itself.

Below is an example of isql for connecting to "your_database"

isql -S"Server_Name" -U"Login" -D"Your_Database" -P"Password"

You may refer below for isql options:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc30191.1570100/doc/html/san1367605044069.html

Hope above helps.


sp_blitzFirst Does Not Output to DB When Run as SQL Job I'm an accidental DBA here,

I am attempting to create a SQL Agent Job that will capture the data for sp_blitzFirst. My goal is to start using the PowerBI dashboard to help show our pain points to management.

When I run the SQL code below, it outputs the data to my tables, but when I run it as a SQL Job, it does nothing.  The job succeeds without any errors codes.

EXEC sp_BlitzFirst
  @OutputDatabaseName = 'DBATOOLS',
  @OutputSchemaName = 'dbo',
  @OutputTableName = 'BlitzFirst',
  @OutputTableNameFileStats = 'BlitzFirst_FileStats',
  @OutputTableNamePerfmonStats = 'BlitzFirst_PerfmonStats',
  @OutputTableNameWaitStats = 'BlitzFirst_WaitStats',
  @OutputTableNameBlitzCache = 'BlitzCache',
  @OutputTableNameBlitzWho = 'BlitzWho';

Here is the output from the task:

Message
Executed as user: NT SERVICE\SQLSERVERAGENT. Setting up configuration variables [SQLSTATE 01000] (Message 50000)
Now starting diagnostic analysis [SQLSTATE 01000] (Message 50000)
Checking for Read intent databases to exclude [SQLSTATE 01000] (Message 50000)
Capturing first pass of wait stats, perfmon counters, file stats [SQLSTATE 01000] (Message 50000)
Beginning investigatory queries [SQLSTATE 01000] (Message 50000)
Warning: The join order has been enforced because a local join hint is used. [SQLSTATE 01000] (Message 8625)
Warning: The join order has been enforced because a local join hint is used. [SQLSTATE 01000] (Message 8625)
Finished running investigatory queries [SQLSTATE 01000] (Message 50000)  Waiting to match @Seconds parameter [SQLSTATE 01000] (Message 50000)
Capturing second pass of wait stats, perfmon counters, file stats [SQLSTATE 01000] (Message 50000)
Analyzing changes between first and second passes of DMVs [SQLSTATE 01000] (Message 50000)
Analysis finished, outputting results [SQLSTATE 01000] (Message 50000).
The step succeeded.

Any help in the right direction would be appreciated.

Thanks!

Moving the answer from the comments:

When this happens, start by double-checking the contents of your Agent job, and make sure it exactly matches the command you're running manually.

In this case, the text of the Agent job was calling for a different database name - one that didn't exist - so it was skipping the logging portion. Right now, if you call it with a database name that doesn't exist, it doesn't fail hard. It probably should - if you want to file a Github issue for that at http://FirstResponderKit.org, I can add that.


T-SQL - How to filter data from table I'm new in DB world, so if you can help me it would be great.

My problem:

I have filtered some data and got a table with data, but need to filter them again. Picture might help you. For me, it is pretty complicated. (I made this tables / picture just to illustrate my problem, I'm not allowed to post real data because they are business secret)

I have data in Table 1 (HAVE), and need to get data in table 2 (WANT). Need to take data from first table, but result is based on first appearing in table 1 based on name. When I  "find" new "name" (in real life there are some other data, but it is not matter because it is also string), take that row and put it in new table. Then second name, third name ... it depends how many different names are in first table.

If you want to return the minimum id for each name, then there are a few ways that you can get the result.

One way would be to use an aggregate function in a subquery.  The subquery will return the min(id) for each name, you then use this subquery an join it back to your table on those two columns:

select t1.name,
  t1.value,
  t1.id
from yourtable t1
inner join
(
  select min(id) id, name
  from yourtable
  group by name
) t2
  on t1.id = t2.id
  and t1.name = t2.name

See SQL Fiddle with Demo

Another way you can get the result would be using the row_number() windowing function.  This will allow you to create a unique identifier for each row based on the name and order the result by the id, you get the final result by returning only those rows with a sequence value of 1:

select name, value, id
from
(
  select name, value, id,
    row_number() over(partition by name
                      order by id) seq
  from yourtable
) d
where seq = 1;

See SQL Fiddle with Demo.  Both will return a result:

| NAME | VALUE | ID |
|------|-------|----|
|    A |    15 |  1 |
|    B |    12 |  1 |
|    C |    12 |  1 |


Arithmetic overflow on SELECT query I encountered an arithmetic overflow in a simple SELECT statement. Query was as below e.g.

SELECT [SaleValue] FROM Sales

[SaleValue] was of data type decimal(9,0) and not a computed column.

The reason this happened was because somehow the column had a row where this field was storing a value GREATER than specified datatype, e.g. decimal(10,0).

I could only get the select to work when I increased the size of the column. The table in question has two other instances in two other columns and rows.

How was this situation possible? How was an out of range value saved in the column in the first place?

I'm using Microsoft SQL server + this is a base table, not a view.

This can happen in several ways, for example as described in Troubleshooting DBCC error 2570 in SQL Server 2005 and later versions:

Invalid or out-of-range data may have been stored in the SQL Server database in earlier versions for the following reasons:

Invalid data was present in the source while using bulk insert methods, such as the bcp utility.
Invalid data was passed through RPC event calls made to SQL Server.
Other potential causes of physical data corruption left the column value in an invalid state.

That article contains a lot of useful information on the topic. For the basics, see the documentation for DBCC CHECKDB and the DATA_PURITY option in particular.


Last row from a query without Order by I have something like

SELECT var1,var2 FROM table where var3=X AND var4=Y order by var5 desc limit 1;

Running this query takes roughly 5 seconds on my database which is far to slow.
I was wondering if there was any way to retrieve the last row in a way similar to this

SELECT var1,var2 FROM table where var3=X AND var4=Y limit 1;

bcause that is almost instant (.03 seconds) on my database. So, if there was some way to have a query print everything in reverse order that would solve every issue I am having. I have been searching for a long while now and every answer seems to be with using ORDER BY or subqueries which are all >= 5 seconds.

EDIT The tables are not indexed.

Let's say the table is called mydb.mytable
This query
SELECT VAR1,VAR2 FROM mydb.mytable where var3=X AND var4=Y order by var5 desc limit 1;

can dramatically be improved if you index the table.
There is one of two techniques you can try when indexing the table
Technique #1
ALTER TABLE mydb.mytable ADD INDEX search_index (var3,var4,var5);

Within the index, all var5 values are ordered for every var3,var4 tuple. It would simply be a rightmost traversal down the BTREE to get to the last var5. Then, the row is looked up to retrieve var1,var2 from the table.
Technique #2
ALTER TABLE mydb.mytable ADD INDEX search_index (var3,var4,var5,var1,var2);

This has the same searchability as INDEX #1, but with an added bonus: var1,var2 are already in the index, so there is no need to go back to the table for a separate retrieval. Making an index that has every column requested in a query is called a covering index.
I have discussed covering indexes before

Jun 12, 2014 : How to return last used record?
Aug 06, 2013 : In MySQL if column X has unique values what's the difference between UNIQUE index and B-Tree index
Oct 28, 2011 : Using FORCE INDEX
Aug 15, 2011 : How do you increase MySQL performance using current server resources?

Give it a Try !!!


Update function to enter random/unique GUID per row I am new to SQL and am trying to do the following.

i am using SQLite browser to edit the current file I have

I do have a SQLite database with around 1500 rows of data that I would like to create a GUID for.

I created the Col with the name GUID and has a default value of NULL.

My update script looks like:

UPDATE "main"."MyLab" SET guid = (select hex( randomblob(4)) || '-' || hex( randomblob(2))
             || '-' || '4' || substr( hex( randomblob(2)), 2) || '-'
             || substr('AB89', 1 + (abs(random()) % 4) , 1)  ||
             substr(hex(randomblob(2)), 2) || '-' || hex(randomblob(6)) )

This gives the same value to all 1,500 rows. I want a unique value for each row.

Just Add another column to your table and mark the AI checkbox. (AI=Autoincrement). This will make it automatically a PK (Primary Key).

It will automatically populate the column with a different number in each row.

Now if you want to create another type of key, use this GUID field in your update as part of the calculation so that it will variate in each row.

Why the update didn't work?

The reason for why your update didn't work it b/c it sets all values the same since you didn't have a condition to your update.

For example:

Update
    MyTable
set GUID=1
where
   AnotherColumn=1

In your update, you don't have a condition, hence all row values will be updated the same.

For example:

Update MyTable
set GUID=1+1

In other words, Update my table and set the GUID column with the value of 2.

it would be the same as having the following Update

Update MyTable
set GUID=1+1
where
    1=1

The WHERE clause filters which rows it will update.

So in conclusion if you want to create a GUID, you would need a variant in the formula per row.

Update MyTable
set GUID=1+UniqueValuePerRow

Since an update will not help you achieve what you want, you might want to create a while loop to update 1 row at a time or learn about Cursors.

...or simply add an auto increment field like in the image below.

Cheers!


How to get certificate expiration date for certificate in mirroring endpoint in SQL Server 2016 Always On Availability Group? I created a domain-independent 2-node SQL Server 2016 Always On Availability Group using the Microsoft instructions:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/domain-independent-availability-groups?view=sql-server-ver15

One of the steps creates an endpoint using a certificate:

CREATE ENDPOINT DIAG_EP
STATE = STARTED
AS TCP (
    LISTENER_PORT = 5022,
    LISTENER_IP = ALL
      )
FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE InstanceX_Cert,
    ROLE = ALL
      )

The cluster is working and some months later I want to check on the expiration date of this certificate.  So I run this query:

select * from sys.certificates

This query returns the empty set.  This can't be right, I know the mirroring endpoint is using a certificate.  So then I run this query to list all the mirroring endpoints:

select * from sys.database_mirroring_endpoints

And indeed it says connection_auth_desc = CERTIFICATE and certificate_id = XXX in the response.  So it must be using a certificate.  None of the columns in the response actually say which certificate or what the expiration date is.

Does anyone have any guidance on how to track down this certificate used in the mirroring endpoint in the SQL Server Always On Availability Group and check its expiration date?  Also, how can I update this certificate in the future without causing downtime for the cluster?

Any help would be greatly appreciated!

Instance-level certificates are stored in the master database
You will need to set your context to master database, and then check certificates:

use [master]
select * from sys.certificates

Or simply use three-part name in a single command:

select * from [master].sys.certificates


Will a SELECT . . . INTO use short-circuit evaluation for a COALESCE? I'm building a stored proc that can take in two separate values that each can be used to identify a single record. One of these values may be null.

My logic is as follows: if IN_ORDER_ID is not NULL, save it to V_ORDER_ID. If it is NULL, then retrieve ORDER_ID with a query and save that to V_ORDER_ID. V_ORDER_ID is then used in the rest of the procedure.

Here is the code:

SELECT COALESCE(IN_ORDER_ID, order_id) INTO V_ORDER_ID FROM SCHEMA.ORDER WHERE SXC_ORDER_NUMBER = IN_SXC_ORDER_NUMBER;

My Question: Will the coalesce in this query still take advantage of short-circuit evaluation?

Yes, per documentation:

Database SQL Language Reference (Oracle 11g): COALESCE()

  Oracle Database uses short-circuit evaluation. The database evaluates each expr value and determines whether it is NULL, rather than evaluating all of the expr values before determining whether any of them is NULL.

Note (having been burned by this myself): be wary of relying on proof-of-concept to determine order-of-operations in SQL language.  SQL is declarative, and the engine is often given the flexibility to execute portions of a statement in arbitrary orders.  So just because one ad-hoc example shows the order of execution of operations A, B, C to be A-then-B-then-C, it may be the case that in different circumstances, engine will opt for alternate execution order, e.g. B-then-A-then-C.


How to insert values into a junction table? I add values into my two tables using the code below, but I also have a junction table called book_main_cover and I want to add data into this table to make a connection between book_id and metadata_image_id, how can I do it when I add the records for the first time?

With the code below, I can only add individual records without a connection.

    $stmt = $sqli->prepare("INSERT INTO book(book_original_name, book_turkish_name, book_publication_date, book_synopsis) VALUES (?,?,?,?)");
    $stmt->bind_param("ssss", $_POST['book_original_name'], $_POST['book_tr_name'], $_POST['book_year'], $_POST['book_synopsis']);
    $stmt->execute();
    $stmt->close();

    $stmt = $sqli->prepare("INSERT INTO metadata_images(metadata_image_value, type, size) VALUES (?,?,?)");
    $stmt->bind_param("sss", $name, $_FILES['book_cover']['type'], $_FILES['book_cover']['size']);
    $stmt->execute();
    $stmt->close();

I am using MySQL and InnoDB.

My database structure:

Table : book
book_id (PK)
book_original_name
book_tr_name
book_publication_date
book_synopsis

Table : metadata_images
metadata_image_id (PK)
metadata_image_value
type
size

Junction Table : book_main_cover
book_id (FK from book)
book_metadata_id (FK from metadata_images)

If I guessed correct you are using PHP PDO, so what your are looking for is this function. http://us3.php.net/manual/en/pdo.lastinsertid.php. It will return the id off the last insert tuple into the database which you can save in a variable to be used in the insert for the junction table.

$stmt = $sqli->prepare("INSERT INTO book(book_original_name, book_turkish_name, book_publication_date, book_synopsis) VALUES (?,?,?,?)");
$stmt->bind_param("ssss", $_POST['book_original_name'], $_POST['book_tr_name'], $_POST['book_year'], $_POST['book_synopsis']);
$stmt->execute();
$book_id = $stmt->insert_id;
$stmt->close();

$stmt = $sqli->prepare("INSERT INTO metadata_images(metadata_image_value, type, size) VALUES (?,?,?)");
$stmt->bind_param("sss", $name, $_FILES['book_cover']['type'], $_FILES['book_cover']['size']);
$stmt->execute();
$cover_id = $stmt->insert_id;
$stmt->close();

$stmt = $sqli->prepare("INSERT INTO book_main_cover VALUES(?,?)");
$stmt->bind_param($book_id, $cover_id);
$stmt->execute();
$stmt->close();


Can Transactional Replication and triggers be used for near-realtime ETL We want to have a data warehouse synced with our production OLTP database in near realtime. Is it possible to do this with Transactional Replication and triggers?

How we currently ETL
We use sql server hosted on azure. We have a OLTP database for our application and keep a second sql server database as a data warehouse for reporting and modeling. Currently we orchestrate a nightly ETL (ELT really) process with azure data factory. The nightly process syncs reference tables on the data warehouse with the OLAP app database and calls heavy stored procedures to rebuild all the denormalized tables required for reporting and modeling. The nightly process takes a long time.

How we think it could work better in near real time
Proposal is to select the reference tables we want to sync from the OLTP database to the data warehouse and do the one-way sync with sql server's built in transactional replication. Then on the data warehouse we would put triggers for changes (IUD) to the reference tables. Those triggers would insert a modification record into a table on the warehouse which would act as a queue tracking changes to reference table data. We would then build a process to watch that queue. As new modification records came in, the process would see what table was changed and for what primary key and then go update all the denormalized tables that depend on that combination of reference table and primary key. We are unsure if we would write those denormalization procedures as stored procedures on sql server or as a separate .net application. Either way seems like an option.

Anyone with experience in those areas think that could work. Are we crazy?

If you're using a version and edition that supports Change Data Capture or Change Tracking, these mechanisms would be better suited to monitoring for changes to your reference tables because they provide a lightweight solution for identifying only changed rows.

You could implement CT or CDC on your reference tables and poll them frequently for changes. When a change is encountered, you can pull in the changed rows only, update your data warehouse and kick off your process for fixing the denormalised data in your data warehouse.

You may also need to re-evaluate the design of your data warehouse. From your description, these reference tables are possibly slowly changing dimensions and with the correct DWH design, you shouldn't need to update your denormalised data in the warehouse after a change to a dimension, that change should be handled automatically.

There isn't really a complete answer to your problem because there are a lot of unknowns that impact design decisions for both your data warehouse and ETL process. You need to properly define your requirements around performance, data latency (how far behind can the DWH be), historical data (should your fact table records reflect the dimension details at the time of insert or the current state of the dimension), reporting and several other factors which then inform your DWH and ETL design.

After the requirements are defined, you can start looking at technologies and architectures to meet these requirements.


At which frequency run CTXSYS.CTX_DDL.OPTIMIZE_INDEX(...) on a fulltext index? I've been told to run periodically CTXSYS.CTX_DDL.OPTIMIZE_INDEX(...) on some fulltext indexes, with no more information, in particular at which frequency this task should be run.

I guess that it depends on the amount of indexed data, the time after which the index does not "performs well" (maybe after a lot of writes), the time taken by running  CTXSYS.CTX_DDL.OPTIMIZE_INDEX(...) (is it "blocking" DB when running?), etc. so the answer to this question may not be very precise.

However, I would like to know if there are some general rules, principles, recommendations to follow (couldn't find any).

You can try to use the index statistics report by first enabling reporting on the index  (BEGIN ctx_output.enable_query_stats(ix_my_indexname); END;).  Then you can use the procedure ctx_report.index_stats.  While sifting through the CLOB of info in the report, the most useful information to me is the FRAGMENTATION STATISTICS...estimated row fragmentation: 23%

see Oracle documentation here for more thorough examples of the stats report.


MySQL column defined with NOT NULL is allowing nulls I had created a table a while back and started adding data to it. Recently I added a new column (address) to it with NOT NULL as part of the new column. The old rows (pre-addition) are still null, which created a warning as part of the definition. However, new rows with the new column are still being allowed to insert nulls.

Is the new column's pre-addition nulls the source of them being allowed? If so, is there a way to tell MySQL to not allow it even though it was before?

mysql> show create table my_table\G
*************************** 1. row ***************************
       Table: my_table
Create Table: CREATE TABLE `my_table` (
  `entry_id` int(11) NOT NULL auto_increment,
  `address` varchar(512) NOT NULL,
  `follow_up_to` int(11) default NULL,
  PRIMARY KEY  (`entry_id`),
  KEY `follow_up_to` (`follow_up_to`),
  CONSTRAINT `my_table_ibfk_1`
     FOREIGN KEY (`follow_up_to`)
     REFERENCES `my_table` (`entry_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=535 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

What version of mysql is this?

What mode are you running in?

SELECT @@GLOBAL.SQL_MODE, @@SESSION.SQL_MODE;

(This should be run in the context of your application, just in case it is changing it).

MySQL is documented thus: http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html

As of MySQL 5.0.2, if a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows:

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. This is the same as before 5.0.2.

If the column cannot take NULL as the value, MySQL defines the column with no explicit DEFAULT clause. For data entry, if an INSERT or REPLACE statement includes no value for
the column, MySQL handles the column according to the SQL mode in effect at the time:

If strict SQL mode is not enabled, MySQL sets the column to the implicit default value for the column data type.

My own testing fails to duplicate your issue

mysql> CREATE TABLE `my_table` (
    ->   `entry_id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `address` varchar(512) NOT NULL,
    ->   `follow_up_to` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`entry_id`),
    ->   KEY `follow_up_to` (`follow_up_to`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=536 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected, 2 warnings (0.16 sec)

mysql> INSERT INTO my_table VALUES (NULL, NULL, NULL);
ERROR 1048 (23000): Column 'address' cannot be null
mysql> INSERT INTO my_table (follow_up_to) VALUES (NULL);
Query OK, 1 row affected, 1 warning (0.10 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1364 | Field 'address' doesn't have a default value |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM my_table;
+----------+---------+--------------+
| entry_id | address | follow_up_to |
+----------+---------+--------------+
|      537 |         |         NULL |
+----------+---------+--------------+
1 row in set (0.00 sec)

The fact the old data had nulls shouldn't matter. The Alter table should have 'truncated' the nulls into empty strings

mysql> SHOW CREATE TABLE my_table\G
*************************** 1. row ***************************
       Table: my_table
Create Table: CREATE TABLE `my_table` (
  `entry_id` int(11) NOT NULL AUTO_INCREMENT,
  `address` varchar(512) NOT NULL,
  `follow_up_to` int(11) DEFAULT NULL,
  PRIMARY KEY (`entry_id`),
  KEY `follow_up_to` (`follow_up_to`)
) ENGINE=InnoDB AUTO_INCREMENT=536 DEFAULT CHARSET=latin1
1 row in set (0.04 sec)

mysql> ALTER TABLE my_table MODIFY address VARCHAR(512) NULL DEFAULT NULL;
Query OK, 1 row affected (0.76 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO my_table VALUES (NULL, NULL, NULL), (NULL, NULL, NULL);
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM my_table;
+----------+---------+--------------+
| entry_id | address | follow_up_to |
+----------+---------+--------------+
|      535 |         |         NULL |
|      536 | NULL    |         NULL |
|      537 | NULL    |         NULL |
+----------+---------+--------------+
3 rows in set (0.04 sec)

mysql> ALTER TABLE my_table MODIFY address VARCHAR(512) NOT NULL;
Query OK, 3 rows affected, 2 warnings (0.83 sec)
Records: 3  Duplicates: 0  Warnings: 2

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1265 | Data truncated for column 'address' at row 2 |
| Warning | 1265 | Data truncated for column 'address' at row 3 |
+---------+------+----------------------------------------------+
2 rows in set (0.04 sec)

mysql> SELECT * FROM my_table;
+----------+---------+--------------+
| entry_id | address | follow_up_to |
+----------+---------+--------------+
|      535 |         |         NULL |
|      536 |         |         NULL |
|      537 |         |         NULL |
+----------+---------+--------------+
3 rows in set (0.04 sec)

mysql> INSERT INTO my_table VALUES (NULL, NULL, NULL), (NULL, NULL, NULL);
Query OK, 2 rows affected, 2 warnings (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 2

mysql> SELECT * FROM my_table;
+----------+---------+--------------+
| entry_id | address | follow_up_to |
+----------+---------+--------------+
|      535 |         |         NULL |
|      536 |         |         NULL |
|      537 |         |         NULL |
|      538 |         |         NULL |
|      539 |         |         NULL |
+----------+---------+--------------+
5 rows in set (0.05 sec)


Reading SQL Server 2008 transaction logs I have a requirement for tracking some usage on a table that will be getting retired in the next year and feel that I could get the pertinent data (stored procedures and in-line sql being used against it) from the transaction logs. I've see some expensive purchased options out there for reading the logs but I was wondering if anybody knew of any opensource solutions or some sample code of how to parse these logs?

Logs are proprietary and those solutions are expensive because it is not simple and the average person does not know how to do it. Since you're using SQL Server 2008, have you considered some other more accessible options, such as:

SQL Audit
Server-side trace
Change Tracking
Change Data Capture
Taking snapshots of the DMV sys.dm_db_index_usage_stats
Forcing access through a stored procedure and employing custom logging

I wrote about some of these here (before 2008 was released):

https://sqlblog.org/2008/05/06/when-was-my-database-table-last-accessed


Is it possible to move mongodb indexes to another file system? Is it possible to separate MongoDB files location so the indexes are on another file system?

In the example below, I have database with 1.5TB of data + indexes of 330GB.
I have a very fast storage that is not large enough to hold the entire data.
I assume that if I move there the indexes I can get better performance.

Is there a way to do so?

# ls -l /data/mongodb --block-size=G
total 1962G
-rw-------. 1 root root    1G May 27 20:17 collection-0--8049282980716619517.wt
-rw-------. 1 root root    1G May 27 20:17 collection-2--8049282980716619517.wt
-rw-------. 1 root root    1G May 27 20:17 collection-4--8049282980716619517.wt
-rw-------. 1 root root 1627G May 27 20:17 collection-7--8049282980716619517.wt
drwx------. 2 root root    1G May 27 20:14 diagnostic.data
-rw-------. 1 root root    1G May 27 20:17 index-1--8049282980716619517.wt
-rw-------. 1 root root    1G May 27 20:17 index-3--8049282980716619517.wt
-rw-------. 1 root root    1G May 27 20:17 index-5--8049282980716619517.wt
-rw-------. 1 root root    1G May 27 20:17 index-6--8049282980716619517.wt
-rw-------. 1 root root   15G May 27 20:14 index-8--8049282980716619517.wt
-rw-------. 1 root root  320G May 27 20:17 index-9--8049282980716619517.wt
drwx------. 2 root root    1G May 27 13:54 journal
-rw-------. 1 root root    1G May 27 20:17 _mdb_catalog.wt
-rw-------. 1 root root    0G May 27 20:17 mongod.lock
-rw-------. 1 root root    1G May 27 20:17 sizeStorer.wt
-rw-------. 1 root root    1G May 23 12:50 storage.bson
-rw-------. 1 root root    1G May 23 12:50 WiredTiger
-rw-------. 1 root root    1G May 27 20:17 WiredTigerLAS.wt
-rw-------. 1 root root    1G May 23 12:50 WiredTiger.lock
-rw-------. 1 root root    1G May 27 20:17 WiredTiger.turtle
-rw-------. 1 root root    1G May 27 20:17 WiredTiger.wt
#

To be clear, this feature is only available when using the WiredTiger storage engine.

To use, invoke mongod like so:

mongod --dbpath /tmp --storageEngine wiredTiger --wiredTigerDirectoryForIndexes

In your dbpath, you will see a directory called indexes where indexes will be stored.

Also update yaml as

storage:
  engine: wiredTiger
wiredTiger:
  engineConfig:
  directoryForIndexes: true

Kindly note to try this in test environment once.
Please ask if any further help is required.

Source: https://jira.mongodb.org/browse/SERVER-965


Oracle SQL create view privileges This is more of a theoritical question but I need help ASAP. Here it is:

  With the appropriate use of SQL queries, grant as system
  administrators the required access privileges to a database user named
  STD01, so he can create a view of a table named CUSTOMER, which
  belongs to another database user STD00.

Can anyone help me with this?

I know that I have to grant him with the system privilege of CREATE (ANY) VIEW and also give him all the object privileges (SELECT, INSERT, UPDATE & DELETE) on the CUSTOMER table, but I have no idea how can I do that by using SQL...

To grant privilege to create a view:

GRANT CREATE VIEW TO STD01;

To grant the DML privileges:

GRANT SELECT,UPDATE,INSERT,DELETE ON STD00.CUSTOMER TO STD01;

But that's not all of the object privileges. If you did:

GRANT ALL ON STD00.CUSTOMER TO STD01;

you would also give other privileges such as ALTER, INDEX, FLASHBACK, etc.


Should new roles be assigned to SYS Should the user SYS be assigned new roles beside the roles that it has when it was created?

Internal Oracle users (SYS, SYSTEM etc) should never be modified in any way, except for password changes.

As far as roles & grants are concerned, SYS already has unrestricted access to the entire RDBMS due to the nature of the user.


Do Foreign Keys Impose Uniqueness Do FOREIGN KEYs impose the uniqueness of the the referent?
I.e. if I have a foreign key that refers to a UNIQUE constrained column, will the FOREIGN KEY column be unique?
I'm particularly wondering about postgresql.

No, foreign keys are not implicitly unique. And they can't be otherwise it wouldn't be possible to implement a one-to-many relationship, e.g. this:

create table customer
(
   id integer primary key
);
create table orders
(
   id integer primary key,
   customer_id integer not null references customer
);

insert into customer values (1);
insert into orders values (1,1), (2,1), (3,1);

You can however make them unique if you want. This is typically used when modelling a a one-to-one relationship.


How to write database creation scripts when coding software setup? A SQL Server database is part of an software application that clients will install.

When the clients are installing the software, the setup process needs to create an empty database.

Do I need to manually start figuring out which tables needs to be created first, and which foreign key needs to be referenced after X table is created? Or is there a tool that creates these scripts in the correct order using the existing database?

Well, you can always generate a script of the current database using right-click > Tasks > Generate Scripts in Object Explorer (this should create the tables and keys in the right dependency order). Or you could generate such a script with a 3rd party tool like Red Gate SQL Compare1 by comparing the current version of your schema against an empty database. Or you could ship a .bak file (a backup of an empty copy of your database) with your software and the setup could restore it.

1. Probably dated now, but a few years ago I blogged about many alternatives here.


How can I get the user not to be listed twice? I'm using the following query to list best times in a race. This works perfectly well but someone does the best record twice (for example, they finished the race in 15 seconds twice) they being listed twice. I don't want them to be listed again.

SELECT
    *
FROM
    racerecords
WHERE
    (holder_regid, rec_time) IN (
        SELECT
            holder_regid,
            MIN(rec_time)
        FROM
            racerecords
        WHERE
            race_id = 17
        GROUP BY
            holder_regid
    )
AND race_id = 17 ORDER BY rec_time;

Additional Info: holder_regid is the registration ID of the racer and the rec_time is the record time.

So how can I achive this? I tried to use DISTINCT but I got error. Help me with the query.

Try the SQL below. I could only create a few sample records, so maybe I haven't covered everything.

BTW, welcome to the forum. In future, when you are asking questions like this one, it would be great if you could provide DDL in the form of CREATE TABLE MyTable(col1, col1_type, col2...) and some sample DML (INSERT INTO TABLE MyTABLE VALUES(val1, val2...). You can put this up on SQLFiddle for example - or here of course if there's not too much data - you can always provide a downloadable script.

Good questions tend to get better answers - check out this (and links within) to see how to get the best out of this forum - it really is a great resource. As it says, help us to help you!

SELECT
    *
FROM
    racerecords
WHERE
    (holder_regid, rec_time) IN (
        SELECT
            holder_regid,
            MIN(rec_time)
        FROM
            racerecords
        WHERE
            race_id = 17
        GROUP BY
            holder_regid
    )
AND race_id = 17
GROUP BY holder_regid   <<=== added this line
ORDER BY rec_time, holder_regid;


Compare two tables in SQL Server I want to compare two table in SQL server and find difference.

I want Warehouse and Product combination and see the difference. I am expecting to combine and display both table together and write Null if same data is not present in other table.

For Ex:

New Jercy and IPHONE from both table and see what is difference in model and quantity.
New Jercy and IPHONE combination and comapare with San Jose and Samsung and see the difference.
San Jose and Samsung combination and compare with TableB having San Jose and Samsung.

Can anyone help me on this.

Thank you!

Take a look at using the EXCEPT clause.

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-ver15

SELECT WAREHOUSE, PRODUCT, MODEL, QUANTITY FROM Table1
EXCEPT
SELECT WAREHOUSE, PRODUCT, MODEL, QUANTITY FROM Table2


"Role does not exist" error when granting usage on public schema, but role does exit In Postgres, I'm trying to grant USAGE on the public schema to a specific role -

GRANT ALL ON SCHEMA public TO MyRole;

The problem is that this command generates an error -

  ERROR:  role "myrole" does not exist

However, the role does exist.  Listing roles outputs the following table -

   Role name   |                         Attributes                         |    Member of
---------------+------------------------------------------------------------+-----------------
 rds_user      | Create role, Create DB                                    +| {rds_superuser}
               | Password valid until infinity                              |
 MyRole        |                                                            | {}
 rds_superuser | Cannot login                                               | {}
 rdsadmin      | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
               | Password valid until infinity                              |
 rdsrepladmin  | No inheritance, Cannot login, Replication                  | {}

I'm new to Postgres, so I may be missing some subtle command, but as far as I can tell I've done everything I need to.  Can anyone suggest what the problem may be and how I should go about resolving it?

Your problem is that the role you are after has capital letters in its name.  Mixing cases is usually a bad idea in PostgreSQL, because you need to double-quote such names.

So your grant should look like

GRANT ALL ON SCHEMA public TO "MyRole";

I'd suggest renaming that role to "myrole", to avoid such problems later.


Oracle alter column if exists Hello I've messed up a certain db migration. I'm more of a postgres person than oracle, and I googled for a while and haven't find a way to do something like alter if exists.

Basically what I need is to drop db columns if they exist, if they don't exist do nothing.

Example :

ALTER TABLE MY_PROPERTY_LOCK DROP COLUMN PROP

Fails if the PROP doesn't exist.

Edit:

Tried this, among other things :

declare p_count NUMBER;

select count(1) int p_count
from ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'MY_PROPERTY_LOCK'
and COLUMN_NAME = 'PROP';

IF p_count = 1 THEN
    ALTER TABLE MY_PROPERTY_LOCK DROP COLUMN PROP
END IF;

This is the error I get :

begin function pragma procedure subtype type <an identifier>
   <a double-quoted delimited-identifier> current cursor delete
   exists prior
The symbol "begin" was substituted for "SELECT" to continue.
ORA-06550: line 3, column 21:
PLS-00103: Encountered the symbol "P_COUNT" when expecting one of the following:

   , from into bulk
The symbol "," was substituted for "P_COUNT" to continue.
ORA-06550: line 10, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:

   ( begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier>
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

You can use the data dictionary to check whether the column exists before trying to drop it

DECLARE
  l_cnt integer;
BEGIN
  SELECT COUNT(*)
    INTO l_cnt
    FROM dba_tab_columns
   WHERE owner = <<table owner>>
     AND table_name = 'MY_PROPERTY_LOCK'
     AND column_name = 'PROP';

  IF( l_cnt = 1 )
  THEN
    EXECUTE IMMEDIATE 'ALTER TABLE my_property_lock DROP COLUMN prop';
  END IF;
END;

My bias, though, would be to record somewhere what version of the schema is installed and to determine what DDL to execute based on that.  For example, if you have a VERSION table that tells you that the current installed version is 1.4 and you want to drop the column when you move to version 1.5, you can just execute the DROP without worrying that it might not exist.


How to calculate exact month (in days) difference b/w two dates I am trying to get all the rows where the visit date was exact before five months from to date but somehow DateDiff function is including 26th Sept 2013 record which is not right as five month has average 150 to 153 days. I think it should count up to 3rd Sept 2013.

I am using DATEDIFF(MONTH, VisitDate , GETDATE())  > =5

Should i use DATEDIFF( DAY  , VisitDate , GETDATE()) >= 153 instead

The problem with your problem (sorry, couldn't resist) is that you haven't properly defined what you expect a month to be. How many months are there between Dec 27 and Feb 28? How about Dec 28 and Feb 28? Dec 29? What about Dec 15 to Jan 13? Dec 15 to Jan 14?

As others have mentioned, DATEDIFF just measures the number of boundaries that have been passed. The following both return 1:

SELECT
  DATEDIFF(YEAR,'20120101','20131231'),
  DATEDIFF(YEAR,'20121231','20130101');

Even though you and I both know that only one day has passed in the first example, and almost two years have passed in the second.

If you want everything from before September 4th (since today is February 3rd), then:

DECLARE @d DATE = SYSDATETIME;

SELECT <cols> FROM dbo.tablename
  WHERE VisitDate < DATEADD(DAY, 1, DATEADD(MONTH, -5, @d));

-- the above evaluates to < '2013-09-04'


How do I calculate COUNT(*) for a range without needing to use multiple queries in mysql I am using MySQL and I need help using COUNT(*) for a range of values within a table.

I have a table named PERSON (In the live system I expect several hundred thousand records or more).

Name    Result
a       100
b       130.45
c       182.96
d       65.45
e       199
f       245

I need to query the table to find out how many records belong to a given range. For example, how many persons belong to 0-50 range. Range values are dynamic. So the expected result is something like:

Range       Count
0-50            0
51-100          2
101-150         1
151-200         2
201-250         1

Off course I can do:

SELECT COUNT(*) FROM PERSON WHERE RESULT <= 50
SELECT COUNT(*) FROM PERSON WHERE RESULT > 50 AND RESULT <= 100

and so on for all ranges...

However, there must be a better, more efficient way to do this?

using the case you can define any range you want.

select
  case
    when RESULT between 0 and 50 then '0-50'
    when RESULT between 50 and 100 then '51-100'
    when RESULT between 100 and 150 then '101-150'
    when RESULT between 150 and 200 then '151-200'
    when RESULT between 200 and 250 then '201-250'
    else 'OTHERS'
  end as `Range`,
  count(1) as `Count`
from PERSON
group by `Range`;


Cannot drop anonymous user from mysql.user I am trying to DROP the anonymous users from my mysql.users database. However, I have been getting odd behavior. When I enter the command:

DROP User ''@'WOPR';

I was getting a generic error message. So, I rebooted my machine, and tried it again. This time I got the response

Query OK, 0 rows affected.

But when I put in

SELECT User, Host, Password FROM mysql.user WHERE User='';

The return is:

+------+------+----------+
| User | Host | Password |
+------+------+----------+
|      | WOPR |          |
+------+------+----------+

(WOPR is my hostname)

I run the command

DROP User ''@'WOPR';

and get the same result.

I am running a fresh install of MySQL 5.5 on Arch Linux, kernel version 2.6.33.

Does anyone know what can cause this behavior?

DELETE FROM mysql.user WHERE user='' AND host='WOPR';
FLUSH PRIVILEGES;

This should do it for you.

Give it a Try !!!

CAVEAT

MySQL has certain users preinstalled into mysql.user. Also, mysql.db comes with two users that have anonymous access and full privileges to test databases.

Just do this

SELECT * FROM mysql.db \G

and you will see that anyone that connects to test or any database starting with test_ can pretty much do everything in the test database. This is bad since a person with full access to any test database can eat up a disk in matter of minutes.

Example:

use test
CREATE TABLE junk (INT a) ENGINE=MyISAM;
INSERT INTO junk VALUES (1);

OK, big deal. It makes a table with 4 bytes.

Now trying running this SQL statement 30 times:

INSERT INTO junk SELECT * FROM junk;

Hey an instant table with 1,073,741,824 rows (4GB+ file) !!! Imaging having full rights to a test database where you can wreak this kind of havoc on a disk.

My advice to you is to run this to clean out test user access:

DELETE FROM mysql.db WHERE db LIKE 'tes%' AND user='';
FLUSH PRIVILEGES;

For further clarification, see my post MySQL : Why are there "test" entries in mysql.db?

Cheers !!!


Buffer Pool scan took 10 seconds error Using SQL server 2017 (14.0.3381.3) with Always On High Availability environment.
Getting error:"Buffer Pool scan took 10 seconds: database ID 8, command 'CHECKPOINT', operation 'FlushCache', scanned buffers 144321190, total iterated buffers 150079215, wait time 42 ms. See 'https://go.microsoft.com/fwlink/?linkid=2132602' for more information"
This is a new feature that is included in the 2017 CU23, under the VSTS bug number 13741858.
I see the list of all the operations that can trigger a scan but none of them are happening on my machine, is there a way to tell what causes the scan? - OR -  Is there a Trace Flag that can be used to turn  off the log of this error?

I see the list of all the operations that can trigger a scan but none of them are happening on my machine, is there a way to tell what causes the scan?

It's a CHECKPOINT on database ID 8.  It says so right in the message.
Do you have INDIRECT CHECKPOINT enabled?  What does
select database_id , name, target_recovery_time_in_seconds
from sys.databases
where database_id = 8

return?  The default of 60 enables indirect checkpoints.


Will (ROWLOCK,UPDLOCK,READPAST) with ORDER BY non-clustered columns work? I have a SQL code as below

#table creation
CREATE TABLE TAB
([i_task_id] [int] IDENTITY(1,1) NOT NULL,
[i_priority] [tinyint] NULL,
[i_status] [smallint] NULL
CONSTRAINT [PK_TAB] PRIMARY KEY CLUSTERED
(
[i_task_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

#non-clustered index on i_status
IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name = 'IX_status' AND
OBJECT_ID = OBJECT_ID('TAB'))
BEGIN
CREATE NONCLUSTERED INDEX IX_status
ON [dbo].[TAB] (i_status)
END

#values in the table#
INSERT INTO TAB(i_priority,i_status)
VALUES (31,1),(30,1),(31,1),(20,1),(10,1)

#select query#
SELECT TOP 1 i_task_id
FROM TAB with (ROWLOCK,UPDLOCK,READPAST)
WHERE i_status = 1
ORDER BY i_priority desc,i_task_id asc

Locking does not work while executing the above query in 4 different sessions. I get result in one session where as nothing is returned in other 3 sessions.

When i tried to change the ORDER BY condition as below

 ORDER BY i_task_id asc,i_priority desc

then the query worked and returned 4 different results in 4 sessions.

My requirement is if several users try to access the same select query at the same time, there should not be any deadlock situation(i.e each user should be provided the unlocked row).Also,each user should be given different task id in different sessions.

Will the locking with order by non-clustered columns work?

Thanks for your time!

I would opt for marking the row as "in process" (e.g. having a status for the equivalent of "checked out") as opposed to relying solely on row locking:

;WITH x AS
(
  SELECT TOP (1) i_task_id, i_status
    FROM dbo.TAB WITH (ROWLOCK,UPDLOCK,READPAST)
    WHERE i_status = 1
    ORDER BY i_priority DESC,i_task_id ASC
)
UPDATE x SET i_status = 2 -- in process? locked?
  OUTPUT inserted.i_task_id
  WHERE i_status = 1;

No blocking or deadlocking in my tests. The 6th session won't block or deadlock either, it will just return no rows.