Maximizing Availability on a Table When Changing Data Type I'm looking for some advice on the best way to change the data type on a column from an INT to a BIGINT on a table with 220 million rows, as it has reached the upper limit of the INT data type.

The database is on SQL Azure DB, and there is a clustered index with a primary key constraint, a foreign key constraint, and a non-clustered index. The table the foreign key is from also has 3 non-clustered indexes which include the key.

What is the fastest/most efficient way to achieve this? Ideally in such a way as to minimize the length of time the table will be inaccessible.

I am planning to test by dropping the constraints and indexes, making the change, and recreating them, as well as moving the data into a new table and creating the appropriate constraints and indexes there, but I wanted to find out if anyone could offer some advice on this first. I've seen a few ideas online such as moving data in batches, but I'm not sure what will work best.  The table itself is only about 12GB, but I tested the whole operation on a local copy of the database and it generated 150GB of log.

You didn't say whether this column was part of any of the FKEY or indexes. Assuming it is not, the simplest way to achieve this is to add a new bigint column and update the data there, and then prepare a script to apply the changes to other affected objects. Potentially, the changes could be done in one transaction and have near-zero downtime.


pg_dump from 9.2 to 8.4 I'm trying to restore a db dump from PostgreSQL 9.2 to PostgrSQL 8.4
(from separate machines, both are running Windows 7).

I followed the instructions in this answer:
Use pg_restore to restore from a newer version of PostgreSQL.

However,the screen keeps writing

invalid command \N  invalid command \N
invalid command \N  invalid command \N
invalid command \N  invalid command \N

How can I resolve this issue?

First my recommendation is to start with text dumps and expect you may need to manually edit them.  There are a large number of differences between 9.2 and 8.4 and these are especially important if you have used extensions in 9.2.

There are however a few things you can do to make the import less troublesome but slower:

Don't use -F to select a format.  Work with plain text dumps since you can edit them if you need to.
Don't use -j.  Keep things simple.
Use --inserts, so INSERT statements are used instead of copy.

Be prepared to edit the dump if needed.  Be comfortable with text editors used for managing large files (vim or emacs, both of which have Windows versions).


How to handle lock timeout error in MySQL? I am getting below listed error many times in my application log.

2015-05-19 10:27:57,842 ERROR [Timer-3] [org.hibernate.util.JDBCExceptionReporter] Lock wait timeout exceeded; try restarting transactionCouldn't retrieve InnoDB stat
us due to underlying exception:

BEGIN NESTED EXCEPTION **
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException
MESSAGE: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

STACKTRACE:

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)

I know this error can be happen when one thread has locked particular row of table, and second thread is waiting for it and this waiting time exceed certain amount of time.

But how can I optimize MySQL Configuration or my code to overcome this situation?

I have already read this article about isolation level, and I have already set isolation level to READ COMMITTED.

Is there any expert advice/solution rather than I have mentioned?

EDIT:
I have also increased lock_timeout_period.

This is not a MySQL configuration problem, it is an application problem.

You apparently have one connection hanging onto something for more than 50 seconds (the default for lock_wait_timeout).  I consider even 5 seconds to be much too long for an interactive system to hang onto something.

Find the other piece of code and rethink its logic.


Return output of Oracle query How to change below query to see results of my_sql (now I see only query, not it results) in SQL Developer?

DECLARE
    my_sql VARCHAR(1000);
BEGIN
    FOR t IN (SELECT  t.table_name, t.owner FROM  all_tables t where owner = 'O' )
    LOOP
        my_sql := 'select max(DATE) from ' || t.owner || '.' || t.table_name ;
        dbms_output.put_line('executing: ' || my_sql);
        EXECUTE IMMEDIATE my_sql;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('ERROR!! -- '  || SQLCODE || '-- ' || sqlerrm || ' --' );
END;

I want to get one value (in this case - max) from all tables (all tables have these same column).

The reason you are not seeing any results is that the FOR loop is not returning any rows as there is no owner called 'O'.
To select all the table for a user who is not the logged in user you could use;

FOR t IN (SELECT  t.table_name, t.owner FROM  all_tables t where owner <> USER)

However you will get a failure almost immediately as MAX(DATE) suggests that every table has a column called DATE. Which is not true.

It looks like you probably want a list of all the tables for a specific list of users (not SYS or SYSTEM or any other 'system' account) with the latest date that a row was added or created or something else involving a date.
To do that EVERY table for the schema must have the column you want to check.

DECLARE
my_sql VARCHAR(1000);
BEGIN
    FOR t IN (SELECT  t.table_name, t.owner FROM  all_tables t where owner IN ('SCHEMA1', 'SCHEMA2','SCHEMA3')
    LOOP
        my_sql := 'SELECT MAX(update_date) FROM ' || t.owner || '.' || t.table_name ;
        dbms_output.put_line('executing: ' || my_sql);
        EXECUTE IMMEDIATE my_sql;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('ERROR!! -- '  || SQLCODE || '-- ' || sqlerrm || ' --' );
END;

SCHEMA1 / 2 / 3 are the users you know have the tables you are interested in and it assumes that each table has a'update_date' column.


How do I output the "most" in MySql? I have a list of employees for our application.  One of the fields is job title.  I use the 4000 job titles to roll people into 1 of 50 "job roles".  We then give people access to our apps via job roles.

On this list we have their supervisor email associated too.  Their supervisor can assign them something on our apps but needs a job role.  Note that supervisors have a "generic manager" job title which rolls into a generic manager "job role".

So based on

employeeid
supervisorid
jobrole

How can I parse through my list get all of the employees under a supervisor and then assign the supervisor a "suprole" base on what jobrole most of their employees have?

Example is:

Ted, Dan, Fred, Susan, and Bill report to Robert.

Their job roles are:
Ted - dishwasher
Dan - cook
Fred - server
Susan - cook
Bill - cook

I would expect Robert's "suprole" to be cook.

update employee as a
      set a.suprole=(
                    select b.jobroll from employee as b
                           where b.supervisorid=a.employeeid
                           group by b.jobroll
                           order by count(b.jobroll) desc
                           limit 1
                     )
       where a.jobroll='generic manager';


Creating an insert trigger on views My table definitions look like this:

I've created a view as:

users_questions(qid,username,question)

I want to create an instead of trigger on the view which puts the data
going to be inserted on the view to the table question.

I've tried:

CREATE OR REPLACE TRIGGER TRIGGER1
  INSTEAD OF INSERT ON USERS_QUESTIONS
  FOR EACH ROW AS
BEGIN
  INSERT INTO questions(qid,question)
    VALUES (:NEW.qid,:NEW.question);
END TRIGGER1;

It says something like:

  Error report - ORA-04079: invalid trigger specification
  04079. 00000 -  "invalid trigger specification"
  *Cause:    The create TRIGGER statement is invalid.
  *Action:   Check the statement for correct syntax.

Tried this also.

CREATE OR REPLACE TRIGGER TRIGGER1
INSTEAD OF INSERT ON USERS_QUESTIONS
BEGIN
  INSERT INTO questions(qid,question)
      SELECT qid,question FROM inserted;
END;

Error:

  Error(4,3): PL/SQL: SQL Statement ignored Error(5,32): PL/SQL:
  ORA-00942: table or view does not exist

I am using:

Oracle OracleXE112
sqldeveloper-18.2.0.183.1748

The error “Table or view does not exists” (at your second try) is caused by the SELECT qid,question FROM inserted; - As far as i can see, there’s no inserted table in your question. Is this table/view yours? Or it’s another user’s?

Hope this helps.


Best Design for this "Sweepstakes" Project? Hello there fellow developers,

Merry Christmas first aff all, and i hope you will have a happy new year soon. :)

I was having more or less a problem with an idea that came to my mind.
I am currently making a Project. We are going to call this project for this threads sake "Sweepstakes Project". So in this "Sweepstakes Project" we got the following database tables:

users | id (INT[11] Auto Increment) | username (VARCHAR[30]) | real_name (VARCHAR[100])
sweepstakes | id (INT[11] Auto Increment) | creator_id (INT[11]) | title (VARCHAR[100])
sweepstakes_items .... DESIGN NOT IMPORTANT ....
sweepstakes_entries .... DESIGN NOT IMPORTANT ....
user_stats | user_id (INT[11]) UNIQUE | sweepstakes_entered (MEDIUM_INT[11]) | sweepstakes_won(SMALL_INT[11]) | sweepstakes_created (SMALL_INT[11])

The table NO. 5 is imaginary. It doesn't yet exist, but is the part of my question.

So here is my question: Which of the following 2 systems would you prefer, or do you have a third and better one?

We want when a user reach some specific statistics to give him a badge [Badge table already exists and is fully functional, not needed to include it here].

System 1. Each time a user creates/wins/enters a sweepstake to count all his previous sweepstakes created/won/entered and if he reaches the amount required to give a badge, then award him the badge.

System 2. Each time a user creates/wins/enters a sweepstake to add (++) that created/won/entered sweepstake in the user_stats table. Then check if the user has reached the amount required to give him the badge, and then reward him the badge.

System 1 PROS: * 1 Less query than System 2, * Less tables

System 1 CONS: * Can't keep backups since it will could break the badge system(When the database is backed up, the sweepstakes count will be wrong since it counts only the rows that are in the current table, not the ones that are also backed-up).

System 2 PROS: * Can keep backups -> Can make the DB faster by removing all old unwanted sweepstakes stats.

System 2 CONS: * 1 Extra query, * 1 Extra table, * Will require some extra time for each status update.

What system would you prefer? Do you have an alternative more effiecient way?

Kind regards,
Vasilis Dimitriadis

(Please keep in mind that the database design may not be the best that exists and i would enjoyed if i recieved some feedback on how to make it better too)

Assuming my comment about Archiving vs. Backup is accurate, go with System 2.

You don't mention it in your post, but System 1 would require some sort of count operation on each insert into the sweepstakes_entries and sweepstakes tables.  Initially the performance associated with the count will not be a problem.  However, as your tables grow the BadgeEarned? query will get slower and slower.

What's worse, your best approach to reduce that burden will be to archive data about closed sweepstakes into a less active area in your database, but doing that will be difficult which you point out in System 1 cons.

So best to store the user_stats somehow and update them as necessary.


Correct pg_hba.conf to allow script to run locally with a specific db-user I've got a working postgreSQL database with following pg_hba.conf:

local   all             postgres                                peer
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
hostssl all             dbadmin         my.ip.at.home/32       md5

Which, to my understanding is correct, and only allows local connections and from my home-ip via SSL. Both via a md5 password.

I now want a local bash script to run some things on the databases (backups). For this I created a new user backup_user; as the script needs to run autonomous my thoughts were to create a line for this user to trust from the the lo:

host   all           backup_user     127.0.0.1/32    trust

But when I login via SSH and run this command I have to type the password....

psql -U backup_user -p 5454

What is the best way to let the script do its thing?
If it matters, I want this script to sun: Automated Backup

Since you want the backup user to connect locally from the database server. I assume the backup is running on a dedicated backup account on the OS.
Using pg_hba.conf you can specify like:
# TYPE  DATABASE USER   ADDRESS METHOD
local   all      backup_user    peer map=local_peers_backup_user

(method peer (with local/peer an address is not needed))
combine this with a pg_ident.conf like:
# MAPNAME                SYSTEM-USERNAME   PG-USERNAME
local_peers_backup_user  postgres          backup_user
local_peers_backup_user  OSBKPUSER         backup_user

This allows the os user postgres and OSBKUSER to connect to all databases in the cluster as the postgres database user named backup_user, using peer authentication. This works using sockets, on linux.
connected as your OSBKPUSER running psql suffices to get connected as backup_user, assuming that you already prepared the environment variables for the OSBKPUSER and OSBKPUSER has only 1 map. Otherwise run psql -U backup_user.
This connect method is secure, if you trust that the access to your dbserver is secured.


SQL Server 2014 AOAG Issue Have a current AOAG setup with 1 database.  Trying to add a 2nd database.  It's in FULL recovery mode and I performed a FULL backup.  When I try to add the database, it says I need a FULL backup and the check box is "grayed" out so I cannot select it.

I have not found anything on Google with information on this so hoping someone who is a "guru" on AOAG can point me in the correct direction.

Please see this which consists of all the things you need to do before you add a DB to existing AOAG :

https://msdn.microsoft.com/en-us/library/ff878487.aspx


Script not emailing from Maintenance Plan/SSIS I have created the following script to send emails from my plan but it isn't sending out. It works correctly in a query window and under an agent job. This is added to the plan as a 'Execute T-SQL Statement Task'

MSSQL 2008 R2

USE msdb

DECLARE @EmailBody VARCHAR(MAX)
DECLARE @EmailProfile VARCHAR(100) = 'SQL_Alert'
DECLARE @Email VARCHAR(100) = 'my email is here'
DECLARE @JobName VARCHAR(200) = 'job name is here'
DECLARE @Subject VARCHAR(200) = @JobName + ' Status: '

;WITH CTE
AS
(
SELECT distinct top 1 sj.name AS [Agent Job Name],
CASE WHEN sjh.run_status=0 THEN 'Failed'
                 WHEN sjh.run_status=1 THEN 'Succeeded'
                 WHEN sjh.run_status=2 THEN 'Retry'
                 WHEN sjh.run_status=3 THEN 'Cancelled'
           ELSE 'Unknown'
      END [Job Outcome],
sja.run_requested_date,
CONVERT(VARCHAR(12), sja.stop_execution_date-sja.start_execution_date, 114) Duration
FROM    msdb.dbo.sysjobactivity sja
INNER   JOIN msdb.dbo.sysjobs sj
ON  sja.job_id = sj.job_id
INNER   JOIN msdb.dbo.sysjobhistory sjh
ON  sjh.job_id = sj.job_id
WHERE   sja.run_requested_date IS NOT NULL
AND sj.name = @JobName
AND sjh.step_id = 0
)

SELECT @EmailBody =
'Agent Name: '
 + CHAR(9)
 + (SELECT [Agent Job Name] FROM CTE)
 + CHAR(13)
 + 'Job Outcome: '
 + CHAR(9)
 + (SELECT [Job Outcome] FROM CTE)
 + CHAR (13)
 + 'Ran At: '
 + CHAR(9)
 + CONVERT(VARCHAR(100), (SELECT run_requested_date FROM CTE), 25)
 + CHAR (13)
 + 'Duration: '
 + CHAR(9)
 + (SELECT [Duration] FROM CTE)

IF (@EmailBody IS NOT NULL)
BEGIN
SET @Subject = @Subject +
CASE WHEN @EmailBody LIKE '%Failed%' THEN 'Failed'
     WHEN @EmailBody LIKE '%Succeeded%'  THEN 'Succeeded'
     WHEN @EmailBody LIKE '%Retry%' THEN 'Retry'
     WHEN @EmailBody LIKE '%Cancelled%' THEN 'Cancelled'
     ELSE 'Unknown'
     END;

EXEC sp_send_dbmail
@profile_name = @EmailProfile,
@recipients = @Email,
@subject = @Subject,
@Body = @EmailBody
END

Results from history

  Executing query "USE msdb    DECLARE @EmailBody VARCHAR(MAX)  DECLA...".: 100% complete  End Progress  DTExec: The package execution returned DTSER_SUCCESS (0).  Started:  11:38:55 AM  Finished: 11:38:58 AM  Elapsed:  2.995 seconds.  The package executed successfully.  The step succeeded.

Currently, your code will not send any email if any fields used to create the subject or body are NULL.

I'd add a case to deal with that such as:

IF (@EmailBody IS NOT NULL)
BEGIN
    SET @Subject = @Subject +
    CASE WHEN @EmailBody LIKE '%Failed%' THEN 'Failed'
         WHEN @EmailBody LIKE '%Succeeded%'  THEN 'Succeeded'
         WHEN @EmailBody LIKE '%Retry%' THEN 'Retry'
         WHEN @EmailBody LIKE '%Cancelled%' THEN 'Cancelled'
         ELSE 'Unknown'
         END;

    EXEC sp_send_dbmail
    @profile_name = @EmailProfile,
    @recipients = @Email,
    @subject = @Subject,
    @Body = @EmailBody
END
ELSE /* catch any issues with NULL for @EmailBody */
BEGIN
    SET @EmailBody = 'nothing to report';
    SET @Subject = 'alert!';
    EXEC sp_send_dbmail
    @profile_name = @EmailProfile,
    @recipients = @Email,
    @subject = @Subject,
    @Body = @EmailBody
END


Where to start learning to become a successful Oracle DBA I am IT Graduate with good knowledge on SQL and C. I have done most of the SQL programming on Microsoft SQL Server in my academics. But i want to become Oracle DBA. Could you please suggest me a structural way to to start learning.  And also please suggest me what background work is needed before digging into it.
Thanks

Start with the Oracle Concepts Guide (pdf). It will give you a good overview of everything related to Oracle databases and will give you a framework in which to place everything else you learn about Oracle.  While you will by no means be a DBA after reading the guide, this is where you should start.


Optimisation postgres windows query I need some guidance with this query being too slow.

SELECT DISTINCT ON (id)
  id,
  views - lead(views)
  OVER (PARTITION BY id
    ORDER BY update_date DESC) vdiff
FROM videoupdate;

With 10 million+ rows it takes ~30 seconds. I have created a multicolumn index that reduced the original time from 1 minute. I want to see difference between views for each row partitioned by id. Some thoughts I had:

After table update create TABLE AS with the query and select from it.
Move old data to backup and shrink table.
Look up data warehouse?
Change database schema?

Following @a_horse_with_no_name's suggestion again, because he's really smart though super, super-resilient to using the Post Your Answer functionality.

SELECT DISTINCT ON(id),
  id,
  views - lead(views) OVER (PARTITION BY id ORDER BY update_date DESC) AS vdiff
FROM (
  SELECT id,
    views,
    update_desc,
    row_number() OVER (PARTITION BY id ORDER BY update_date DESC) AS rn
  FROM videoupdate
) AS t
WHERE rn <=2
ORDER BY id, update_desc DESC;


Finding count of null values in 500 columns of a single table I have 500 columns in a table in SQL Server. I want to find the count of null values in each column. Is there a way to do it using a cursor and dynamic SQL?

An alternative that does not use a cursor:

-- Set the target table details here
DECLARE
    @Schema sysname = N'Production',
    @Table sysname = N'Product',
    @SQL nvarchar(max) = N'SELECT ';

DECLARE
    @QName sysname = QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table);

SET @SQL +=
(
    SELECT
        NCHAR(13) + NCHAR(9) +
        QUOTENAME(C.name + N'_NullCount') + ' = ' +
        N'COUNT_BIG(*) - COUNT_BIG(' +
        QUOTENAME(C.name) + N'),'
    FROM sys.columns AS C
    WHERE
        C.[object_id] = OBJECT_ID(@QName)
        AND C.is_nullable = CONVERT(bit, 1)
        AND C.is_filestream = CONVERT(bit, 0)
        AND C.is_column_set = CONVERT(bit, 0)
    ORDER BY
        C.column_id
    FOR XML PATH (''), TYPE
).value('.[1]/text()[1]', 'nvarchar(max)');

SET @SQL =
    STUFF(@SQL, LEN(@SQL), 1,  NCHAR(13) + N'FROM ' + @QName + N';');

PRINT @SQL;

EXECUTE (@SQL);

The SQL generated on the AdventureWorks example given is:

SELECT
    [Color_NullCount] = COUNT_BIG(*) - COUNT_BIG([Color]),
    [Size_NullCount] = COUNT_BIG(*) - COUNT_BIG([Size]),
    [SizeUnitMeasureCode_NullCount] = COUNT_BIG(*) - COUNT_BIG([SizeUnitMeasureCode]),
    [WeightUnitMeasureCode_NullCount] = COUNT_BIG(*) - COUNT_BIG([WeightUnitMeasureCode]),
    [Weight_NullCount] = COUNT_BIG(*) - COUNT_BIG([Weight]),
    [ProductLine_NullCount] = COUNT_BIG(*) - COUNT_BIG([ProductLine]),
    [Class_NullCount] = COUNT_BIG(*) - COUNT_BIG([Class]),
    [Style_NullCount] = COUNT_BIG(*) - COUNT_BIG([Style]),
    [ProductSubcategoryID_NullCount] = COUNT_BIG(*) - COUNT_BIG([ProductSubcategoryID]),
    [ProductModelID_NullCount] = COUNT_BIG(*) - COUNT_BIG([ProductModelID]),
    [SellEndDate_NullCount] = COUNT_BIG(*) - COUNT_BIG([SellEndDate]),
    [DiscontinuedDate_NullCount] = COUNT_BIG(*) - COUNT_BIG([DiscontinuedDate])
FROM [Production].[Product];

Execution plan:

Results (sample):


Is there a way to only allow SQL Server 2014 syntax/functions to be used in SQL Server 2019? We're developing against SQL Server 2019 currently, but we have a need to deploy the same set of SQL scripts to a SQL Server 2014 database.

For example, in SQL Server 2019 a built-in function called TRIM exists but this is not supported in SQL Server 2014. To make sure we're only using syntaxes supported by both databases, I'm wondering if there's a solution to just allow 2014 syntaxes in 2019.

I thought altering compatibility mode of the database would have this effect, but this does not have the intended effect.

I'm wondering if there's a solution to just allow 2014 syntaxes in 2019.

No. If you need your users to steer clear of things that won't work in versions newer than 2014, then they should develop on 2014.

At the very least, you could let them continue developing on 2019, but automate scripting their objects to a 2014 instance. You can't do this with backup/restore or detach/attach, but there are plenty of ways to transfer objects/data to an older version. You'd also have to run all of the code, because some problems won't crop up until runtime.

Compatibility level is not the answer here, except for very rare exceptions (like STRING_SPLIT). Compatibility level is not used for allowing / disallowing syntax, in modern versions at least. See this and this. In older versions, yes, a lot of syntax was controlled this way, but that’s not how it works anymore.


DevExpress formats columns differently if they're all numbers I have a table with a 'Code' column, defined as varchar.  However, if you have only numbers in some of the records, for this field, and you search on these records in our application, the results in grid (DevExpress) is formatted as numbers.

The moment you have records in the result that contains a-z, then the formatting does not happen.  So I'm trying to see if i can fix this from the database side.  I have created a view on the table, and changed the column in question to:

:
Code + CHAR(144) AS Code
:

This solves the formatting of the field in the grid.  The field will now always been seen as a string, even if the results only contains numbers.

The problem is however, if I now explicitly search on this value in our application's front-end, eg Code = 40E, I get no results, due to the special character (which is not visible).  So I have only partly solved the issue.  I have tried to add just a trailing space to the field at first, but that did not work.

Any ideas how this can be solved from the database, if possible at all?

I only have access to the SQL objects, hence I was trying to fix this through SQL.  Is there any way to address this in a database view, but also ensuring that there are no funny characters appended to the field value which affects the searching?

Force the GridView column format from the code behind:

using DevExpress.Utils;
// ...
GridColumn myCol = new GridColumn() { Caption = "TheColumn", Visible = true, FieldName = "ColumnName" };
colPayment.DisplayFormat.FormatType = FormatType.String; //Char, Varchar, or whatever option DE gives you to format the display
gridView1.Columns.Add(myCol)

If you're gonna add it from the code behind, if not access the Column and set the format.

gridView1.Columns["TheColumn"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.String;

It is not an SQL issue, the data is stored in the format that is supposed to be stored, the frontend is the one supposed to handle the display format in this case. If you create a view the same deal will happen because the grid will still try to guess the column type. Talk to the dev.


What is the purpose of setting serveroutput in oracle? Could someone let me know what is the purpose of using set serveroutput on/off in oracle?

I ran the below query to see what it is doing. However I need to know much about serveroutput. Kindly help me

 set serveroutput on;
     select * from tab;
    select * from tab;
     select * from tab;
     set serveroutput off;

set serveroutput on executed in SQL Plus executes behind the scenes:

dbms_output.enable(buffer_size => NULL);

set serveroutput off executes:

dbms_output.disable;


Check and Retrieve those results where a cell(row) contains atleast one value from many values in a column Let me give you an example :

I have a table (name = posts) which contains following rows:
   id    |    title   | .... |  names
    1    |      .     |   .  |
    .    |      .     |   .  |
    4    |     ABC    |   .  |  joe  , nash  , brad
    5    |     XYZ    |   .  |  jeff , michael ,gerard, pique
    6    |     JKQ    |   .  |  archeo , michael ,neymar, schultz

Another table (name = followed) containing rows:
   id    |  names
    1    |  jeff
    2    |  michael
    3    |  cheryl
    4    |  zohan

Given : Two tables
To fetch : Titles from posts table where names.posts contains atleast one name from names.followed in followed table
Result I should get in the above example : XYZ , JKQ
query form : mysql , PDO

I dont know if its possible .The reason I am storing values in cells with names concatenated with commas is to reduce redundancy. I have basic query knowledge only .If its possible it will be great if some one can answer me a query/queries to get asked result . If not possible just give me an alternative method with a different table structure and an explanation why its the best alternative solution .
Big Thanks to all who can help me in any way possible

If I were doing this in SQL Server*, I would use:

USE tempdb;

CREATE TABLE Posts
(
    PostID INT NOT NULL IDENTITY(1,1)
    , PostName VARCHAR(255)
    /*  whatever other fields relate to POSTS */
    , CONSTRAINT PK_Posts PRIMARY KEY CLUSTERED (PostID)
);
CREATE TABLE People
(
    PeopleID INT NOT NULL IDENTITY(1,1)
    , PersonName VARCHAR(255)
    /* whatever other fields relate to PEOPLE */
    , CONSTRAINT PK_People PRIMARY KEY CLUSTERED (PeopleID)
);
CREATE TABLE PostsPeopleXRef
(
    PostID INT NOT NULL
    , PeopleID INT NOT NULL
    , CONSTRAINT FK_PostsPeopleXRef_PostID
        FOREIGN KEY (PostID) REFERENCES Posts(PostID)
    , CONSTRAINT FK_PostsPeopleXRef_PeopleID
        FOREIGN KEY (PeopleID) REFERENCES People(PeopleID)
);

INSERT INTO Posts (PostName) VALUES ('Post Test 1');
INSERT INTO People (PersonName) VALUES ('Jack');
INSERT INTO People (PersonName) VALUES ('Jill');
INSERT INTO People (PersonName) VALUES ('John');
INSERT INTO PostsPeopleXRef (PostID, PeopleID) VALUES (1, 1);
INSERT INTO PostsPeopleXRef (PostID, PeopleID) VALUES (1, 2);
INSERT INTO PostsPeopleXRef (PostID, PeopleID) VALUES (1, 3);

SELECT *
FROM dbo.Posts;

SELECT *
FROM dbo.People;

SELECT P.PostName, E.PersonName
FROM dbo.PostsPeopleXRef X
    INNER JOIN dbo.Posts P ON X.PostID = P.PostID
    INNER JOIN dbo.People E ON X.PeopleID = E.PeopleID
ORDER BY P.PostName
    , E.PersonName;

This results in the following output:

*
: for MySQL, you'll need some changes, like replacing IDENTITY(1,1) with AUTO_INCREMENT, removing the CLUSTERED keyword, adding the ENGINE = InnoDB at the CREATE TABLE statements, etc.


check format of every value of column in sql server I have a table_1 that has column 'dob' which has dates in nvarchar(max). Now I want to check every date whether it is in datetime format or not. The dates which are in datetime format should be copied to table_2 that has column which has dates in datetime datatype format.

Any help how can I do this?

You can use TRY_PARSE for this. This function will attempt to parse a supplied NVARCHAR value as a given data type, and if it fails it returns NULL.

For example, you can try the following:

select TRY_PARSE('11-23-09' AS DATETIME2)

and it returns

2009-11-23 00:00:00.0000000

But this

select TRY_PARSE('abc' AS DATETIME2)

returns NULL because it cannot parse 'abc' as a datetime value. There are other options, such as using TRY_CONVERT or TRY_CAST, however, TRY_PARSE as the advantage of allowing you to use a culture setting to parse values for a specific region/language.

For example, TRY_PARSE('11-23-09' AS DATETIME2) will parse if the default region is en-US on your SQL Server, but TRY_PARSE('23-11-09' AS DATETIME2) will fail as the NVARCHAR value is not using the US date format.

Specifying the region in the TRY_PARSE function - TRY_PARSE('23-11-09' AS DATETIME2 USING 'en-AU') - means that the string value will PARSE correctly.

You can see this behaviour in this db<>fiddle.

For your specific requirement, use TRY_PARSE in the WHERE clause to identify rows in the first table that can be successfully converted to a datetime value for inserting into your second table.


Huge Size (still) with Differential Backup I am new to differential backup so please bear with me.

I have a database which the full backup size is ~55GB.
I perform ETL nightly on this database. The ETL process involves UPDATE, DELETE, and INDEX DISABLE/REBUILD operations.
1st differential backup produced 48GB backup file!

Now, I know that this is most likely (if not 100%) caused by the index rebuilds (and they are on big tables too!).
As index rebuilding is a pretty common technique used during ETL process, what option(s) do I have to minimise the size of differential backup on a data warehouse database?

That sounds perfectly expected. If you are updating most of your data and rebuilding all of your indexes: all the affected extents need to be in the differential backup, there is simply no way around that because it must contain every extent that has changed since the last full or differential backup.

If you are not already doing so add the COMPRESSION option to your backups, which can result in considerable savings (and speed up the backup process too by reducing IO).

Also if the size really is a massive issue, this might be one of the few occasions to question if a backup is needed particularly regularly. If you are regenerating all of the data in your warehouse database every day from other sources then you might not need to worry as much about backing it up as often as you do the source data, assuming of course that the source databases have good backup taking and testing processes in place, because you can reconstruct that data from the other sources (or backups thereof) anyway if disaster strikes. Of course this reconstruction process could be significantly slower than restoring a backup, so consult you users and your service restore time SLAs before changing the warehouse's backup plan.

Possible Remediation:

Two possible options to reduce the amount of data that you need to transfer spring to mind:

After each update, make a copy of the data without any non-clustered indexes (backup, restore locally, drop all non-clustered indexes, backup again and transfer this version) and recreate the indexes after restoring at the other end. This avoids transferring all the index data.
Make sure the daily changes are as minimal as possible by only INSERTing new rows and UPDATEing those that need it (leaving identical rows untouched), not rebuilding any indexes (this will mean not disabling them so may slow down the ETL process).
Depending on the complexity of your ETL process refactoring it this way may be a massive job. One way around this may be to add an extra stage to the end of the process: keep a second copy of the warehouse, rebuild the first using the normal process, then update the second using one MERGE statement per table to INSERT/UPDATE/DELETE as needed being very careful to leave any unchanged rows alone. Then take your differential backups from this DB. Keep minimal indexes in the primary instance of the copy, just keep the primary keys and clustered indexes (which you will want for efficiency of the MERGE operations) and rebuild the rest after restore.
This reduces the amount of no-op changes, so will hopefully markedly reduce the size of each differential, and reduce space in the backups devoted to index data.

Both these methods try to reduce the backup size that you are transferring at the expense of adding a fair chunk of extra processing at the receiving end (rebuilding indexes after restore) and sending end (maintaining the second copy at that location) so you are trading one bottleneck for another pair which may or may not be practical.

Depending on your data sources and the complexity of the ETL process you may have better luck with building the warehouse DB at both locations each time, as Chris suggested in the comments.


Group by every N records but remove incomplete group that has records less than N I need to query a table to fetch data in groups of some size say N=5 but if the size is less than 5 then the last group should be ignored
i.e every 5 records should be grouped

==> i.e first 5 records form first group
==> next 5 records form second group
==> next 5 records form third group

....
What I have tried
I have tried the below query. It works but it fetches all the data.
My Requirement
I need to strip off the last group if it does not have 5 records there.
in below query groupSize is the size of group
WITH T AS (
  SELECT RANK() OVER (ORDER BY dateReceived ) Rank,
    p.data
  FROM parameterData p where parameterID=@parameterID
        and isActive=1
)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS GroupID, (Rank - 1) / @groupSize GroupSize, AVG(data) average, MIN(data) min,MAX(data) max,MAX(data)-MIN(data) range
FROM T
GROUP BY ((Rank - 1) / @groupSize);

WITH T AS
(
  SELECT
    RANK() OVER (ORDER BY dateReceived ) Rank,
    p.data
  FROM parameterData p where parameterID=@parameterID
    and dateReceived between @fromDate and @toDate
    and recipe = @recipe
    and isActive=1
)
SELECT
  ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS GroupID,
  (Rank - 1) / @groupSize as GroupSize,
  AVG(data) as average,
  MIN(data) as min,
  MAX(data) as max,
  MAX(data)-MIN(data) as range,
  count(*) as cnt
FROM T
GROUP BY ((Rank - 1) / @groupSize)
HAVING count(*) = 5;


SQL Server always running I have a personal computer where I have different instances of SQL server running (developer edition) with both integration services and analysis services (one in tabular and one in multidimensional). I use it for practice and to improve my skills. The start mode in Configuration Manager is "Automatic". So I have two instances each with both SSIS and SSAS. When I am not using SQL Server will these services use a lot of resources on my computer by simply running in the background?
Thanks

As usual in these cases, "It depends".
Depends, as a minumum, on:

How performant is your PC in general (especially: how much RAM, how many CPUs, are your disks SSD, etc).
How often do you practice with SQL Server.

Personally, I have a situation like yours and I don't feel any kind of slowness. Starting/stopping services can be boring when you are in a hurry, but if there are situations in which you need "all the power" of your PC, you can safely stop or pause SQL Server related services: it is not a DB server, then you know when you can stop it.
An idea could be to make a script to stop/start all SQL related services.


Stuck in "query end" status every 5 minutes I'm sick of seeing data in the slow query log, so I decided to check out what the fuss was all about.

An UPDATE query that is running up to tens of times per second is sometimes really slow. Here is the query:

UPDATE `user` SET
    `last_active` = NOW(),
    `last_ip` = '<ip>'
WHERE `name` = '<name>'
LIMIT 1

The table:

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) NOT NULL,
  `last_ip` varchar(50) NOT NULL DEFAULT 'none',
  `last_active` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `last_active` (`last_active`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=34003;

Usually the query takes ~0.2 ms, but every 5 minutes (accurately every 5 minutes, 10:00:12, 10:05:12, 10:10:12...) it takes over 250 ms, with all time spent with the status "query end".

Any idea what may be causing this? Why would one query run slow every 5 minutes? What is happening every 5 minutes that is affecting "query end"? No other processes are running.

MySQL version 5.5.44

All uses of that table will run faster (some will run a lot faster) if you jettison id and make name the PRIMARY KEY.

Your UPDATE is currently looking in 2 BTrees, plus updating 2 BTrees.  The PK change would change those to 1 and 2.  More importantly it would simply the row locking.

How big is the table?  How much RAM do you have?  What is the value of innodb_buffer_pool_size?  innodb_io_read_threads?  What the heck, let's see SHOW VARIABLES LIKE 'innodb%';


WAL files are not copied I'm using postgres 9.3 on Ubuntu 14.04 and I'm currently setting up a WAL backup. These are my changes to the postgresql.conf:
[as described here: http://www.postgresql.org/docs/9.3/static/continuous-archiving.html ]

# - Settings -
wal_level = archive                     # minimal, archive, or hot_standby
                                        # (change requires restart)
# - Archiving -
archive_mode = on               # allows archiving to be done
archive_command = 'cp %p /home/userName/Documents/WALS'

I did a 'sudo service postgresql restart' after changing the config and there are wal files generated:

root@control:/var/lib/postgresql/9.3/main/pg_xlog# ls -hlrt
total 49M
-rw------- 1 postgres postgres  16M Dez  2 14:24 000000010000000000000002
-rw------- 1 postgres postgres  16M Dez  2 14:24 000000010000000000000003
drwx------ 2 postgres postgres 4,0K Dez  2 14:24 archive_status
-rw------- 1 postgres postgres  16M Dez  2 14:25 000000010000000000000004

root@control:/var/lib/postgresql/9.3/main/pg_xlog# ls archive_status/
000000010000000000000002.ready  000000010000000000000003.ready

I assume that the archive_status means that WAL segments 2 and 3 are ready to be archived. But I don't find anything in the Documents/WALS (which exists).

Could someone give me a hint?

The wal files are archived when they are no longer needed in the pg_xlog directory (usually when there are 16 segments and another one is needed - the one that is rolled over and reused is first archived).  You can force the system to archive some files to verify manually if you want - to do this run the following:

select pg_start_backup('testing_archiving');
select pg_stop_backup();

Once pg_stop_backup() is finished, you should see a few files in the archive directory.

Postgres uses the archive status folder to record "notes" about archiving attempts and so on with regard to the WAL files.


What is the proper way to backup MySQL database with rsnapshot? I want to backup my website that contains both user uploaded images and data stored in MySQL database such that they are always consistent with each other in any particular point of time. While searching for solutions, I found this application rsnapshot which might be appropriate for the task. Having gone through a few blogs on the internet, I realize that this is actually done with a backup_script using mysqldump method:

backup_script   ssh root@example.com "mysqldump -A > /var/db/dump/mysql.sql"    unused2
backup  root@example.com:/var/db/dump/  example.com/

What I am not sure is whether there is a necessity to flush all tables with read lock before performing the dump in the script to ensure consistency of the database. If so, how should this be incorporated? If no, why is it not necessary?

By default, your usage of mysqldump will lock each table as it dumps it and there will not be any consistency among tables.  You will also not get any triggers, stored procedures, stored functions, or events backed up, and if any of your view definitions are invalid (referencing nonexistent tables or columns that you may have dropped or renamed since the view was defined), the dump process will terminate when that error is encountered.

If all of your tables are InnoDB, you can get a consistent snapshot by adding this:

--single-transaction

This option disables the default --lock-tables which locks each table as it is dumped, then unlocks it when done dumping it, and instead issues the following statements to the server at the beginning of the dump (which you can observe by enabling the general query log):

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;

Of course, this only helps with InnoDB.  If your tables are MyISAM or any other non-transactional engine, you only have one option:

--lock-all-tables

...which locks all of the tables for the entire dump, since that's the only way to ensure consistency without transactions to assist.

To back up your stored procedures, events, and triggers, add:

--routines --events --triggers

To prevent mysqldump from failing when there's an invalid view definition

--force

To capture your binlog position and store it in the backup file, add:

--master-data=2

This option doesn't work if your machine isn't a slave.

Your question was about backing up the database "with rsnapshot" although to put a fine point on it, you're proposing backing up the database with mysqldump and then backing up the dumpfile with rsnapshot... It's hard to tell from a brief look at their site whether that's going to be a good choice, because rsnapshot appears to have file-level granularity.  It doesn't appear to store diffs, but instead stores full files for "each" file that changes between backup cycles:

  "The amount of space required is roughly the size of one full backup, plus a copy of each additional file that is changed."
  -- http://www.rsnapshot.org/rsnapshot.html

I welcome correction if I'm wrong on this point.   In this case, you only have 1 file, your dump file from mysqldump, which will of course change every time.

Whatever you do, don't entertain the thought of trying to back up the actual files (.frm, .ibd, .MYD, etc.) underlying your tables in MySQL.  That does not work with the server running even though under rare conditions you might get a backup that seems to be intact.  It isn't.

Update:

There's an important distinction between what mylvmbackup is doing and "backing up the actual files."  To use mylvmbackup, you have to be using LVM, which can be visualized as a layer of abstraction between your filesystem and your hard drive.  With LVM, you have the ability to freeze the file system and make a point-in-time snapshot of the entire filesystem.

It looks like mylvmbackup is doing a FLUSH TABLES followed by a FLUSH TABLES WITH READ LOCK prior to looking up the replication coordinates (if specified in configuration), which is the same process mysqldump does when --single-transaction and --master-data are both used.  This stablizes MySQL but it does not fully quiesce InnoDB, so when a backup like this is restored, InnoDB will think it crashed and will do crash recovery... but it should be a clean recovery.

If your web site's data files were in the same filesystem, then an LVM snapshot would contain everything consistent to a single point in time, but whether having your other web site files in the same filesystem as the database is good practice (if, indeed running your web server on the same server is good practice) is another discussion.

Whatever the approach, it's vitally important in any backup strategy to periodically verify that your backups can actually be restored (on a different machine).  The truly safest method would be to be sure innodb_fast_shutdown = 0 and actually shut down MySQL but that's probably not practical.

Of course, if you want with something like rsnapshot, you could always snapshot the web site files, then snapshot the database, then snapshot the web site files again.  If nothing changes in the site files while the database is being backed up, rsnapshot will use very little space for that second run.  If things do change, it should be fairly trivial to understand the differences if you needed to recover.


Query to filter a table based on a comparison applied to the same table? Case & Problem:

Consider a corporate building with some rooms and departments that need authorization to enter.
Employees have to scan their badges to be authorized.
If the employee is inside (the badge was already scanned), the system warns her/him accordingly and does not open the door.
And the supervisor should be able to see and/or report all employees who are in a particular room at any given time.

Checking whether the employee is inside or not was somewhat trivial with the help of a couple of StackOverflow/StackExchange answers.

My problem is at the reporting stage.
The question is how can I filter the table to show those who are currently inside the given point, in a performant way.

Current Situation & What I've Done So Far:

My backend code is looping over all the records and check if they're inside, one by one.
Yet, I suspect that this is not ideal (I might be wrong, though) performance-wise and/or for modularity/responsibility concerns; in the future, I'll need to allow other client applications to connect to the database without having them write their own code/logic to check or report the inside status.

Sample Table:

+-----+----------+---------------+-----------+-----------------+
|  id | code     | point         | action    | timestamp       |
+-----+----------+---------------+-----------+-----------------+
|   1 | 11111111 | Department A  | pass_in   | 1561369024008   |
|   2 | 11111111 | Department A  | pass_out  | 1561369024007   |
|   3 | 11111111 | Department A  | pass_in   | 1561369024006   |
|   5 | 22222222 | Department B  | pass_in   | 1561369024005   |
|   6 | 22222222 | Department A  | pass_out  | 1561369024004   |
|   7 | 22222222 | Department A  | pass_in   | 1561369024003   |
+-----+----------+---------------+-----------+-----------------+

Sample Code:

var point = 'Department A';
var recordsInPoint = helper
                        .fetchAllRecords()
                        .filter( record => helper.isInside( record, point ) );

Query to check if inside:

SELECT
    ( count_compare.count_in > count_compare.count_out ) AS is_inside
FROM
(
    SELECT
        (
        SELECT
            COUNT(`id`)
        FROM
            scanner_log
        WHERE
            `code`='11111111' AND
            `point`='Department A' AND
            `action`='pass_in'
        )  AS count_in,
        (
        SELECT
            COUNT(`id`)
        FROM
            scanner_log
        WHERE
            `code`='11111111' AND
            `point`='Department A' AND
            `action`='pass_out'
        ) AS count_out
) AS `count_compare`;

Questions:
• How can I move the reporting logic from backend to database?
• Would it be really faster and less resource-hungry if handled by db server?

Ideas & Options:

• Create a stored procedure and carry the backend logic to db as is by looping over all records.
I don't see much benefit on this, to be honest. Same loop method, just performed by db server. Also, there's a major drawback as I don't have just one table for logs, there are separate tables for each building or company. And I can't pass the table name as a parameter, as far as I know mySQL / MariaDB doesn't support this. I have to use PREPARE QUERY and EXECUTE QUERY commands which sounds a little bit overkill to me.

• Create another nested temporary table and join them somehow to filter but I just couldn't get my head around it, really, nothing.

Thanks for all your help and insights.

In a situation like this one, I recommend having two tables:

History (like what you have).  This is a list (a la audit trail) of everything that has happened (INs and OUTs).
Current -- The latest place where each person is.

History has an ever-growing number of rows, and is probably not relevant for the Question at hand.

Current contains one row per person.  A simple SELECT with a WHERE clause should pick out all the persons that are "IN" (or "OUT").

"separate tables for each building or company" -- No.  This is the third time today I have advised against having multiple identical schemas.

On the other hand, Current and History may have the same columns.  This is an exception to the previous paragraph.

Do not split by department or building.


(How) can I return the most specific row on a join? I'm trying to work out a query that can parse through a table of prices (and exceptions to those prices) to yield the applicable price for a product, ideally using a join. I'm on PostgreSQL. For example, consider a teddy bear store. The default teddy bear price is $20. If, however, the teddy bear is at least 12 inches tall, we want to charge $30. If the teddy bear is made of suede, we charge $40, and if the teddy bear is both at least 12 inches tall and made of suede, we charge $60 (because the larger bear also requires more of the expensive fabric). ETA: We now also have a grass bear special because we just hired a teddy bear maker who was previously a florist. Given the perishable nature of the bear, it costs $15 if it's at least 24 inches tall.

Here's what the prices table I have looks like (sorry about the formatting):

EDITED: I added the grass bear to better reflect my inability to make any assumptions about the data. I simply need to pick the price that best corresponds to each product without knowledge of how different features affect the price, positively or negatively.

  price | min_ht | fabric
---------------------------
| 20    | NULL   | NULL   |
---------------------------
| 30    | 12     | NULL   |
---------------------------
| 40    | NULL   | suede  |
---------------------------
| 60    | 12     | suede  |
---------------------------
| 15    | 24     | grass  | <-- our floral special
---------------------------

which I want to join with a products table like

  id    | height | fabric
---------------------------
| 1     | 8      | cotton |
---------------------------
| 2     | 6      | poly   |
---------------------------
| 3     | 4      | suede  |
---------------------------
| 4     | 18     | cotton |
---------------------------
| 5     | 12     | suede  |
---------------------------
| 6     | 30     | grass  |
---------------------------

to yield

  id    | height | fabric | price
-----------------------------------
| 1     | 8      | cotton | 20    |
-----------------------------------
| 2     | 6      | poly   | 20    |
-----------------------------------
| 3     | 4      | suede  | 40    |
-----------------------------------
| 4     | 18     | cotton | 30    |
-----------------------------------
| 5     | 12     | suede  | 60    |
-----------------------------------
| 6     | 30     | grass  | 15    |
-----------------------------------

Any ideas? I saw a similar question, though it is unfortunately both unanswered and regarding MySQL.

This makes some assumptions but it will do:

SELECT
    prd.id, prd.height, prd.fabric,
    MAX(pri.price) AS price
FROM
    products AS prd
  JOIN
    prices AS pri
      ON  (prd.height >= pri.min_ht OR pri.min_ht IS NULL)
      AND (prd.fabric =  pri.fabric OR pri.fabric IS NULL)
GROUP BY
    prd.id ;

The above assumes that the more details a pricing has, the higher the price is. Since that is not the case, a different, more general approach:

SELECT
    prd.id, prd.height, prd.fabric,
    pri.price
FROM
    products AS prd
  , LATERAL
    ( SELECT p.price
      FROM prices AS p
      WHERE (prd.height > p.min_ht OR p.min_ht IS NULL)
        AND (prd.fabric = p.fabric OR p.fabric IS NULL)
      ORDER BY
          ((p.min_ht IS NOT NULL)::int + (p.fabric IS NOT NULL)::int) DESC
      LIMIT 1
    ) AS pri ;

Tested at SQL-Fiddle


Oracle SQL Developer equivalent of "SELECT TOP xxx ROWS" in SQL Server In SQL Server, we have the functionality of "SELECT TOP xxx ROWS" when we right click a table:

This will generate a SELECT statement with all the columns in selected table written in the new SQL statement. Is there any similar functionality in Oracle SQL Developer?

There are two parts to this.

RDBMS side
Client side (SQL*Developer)

Client Side

In SQL*Developer, you can drag+drop the table from the Connection to the worksheet in order to get a SELECT statement.  A blog on it is here.

If you use the Query Builder, you can drag+drop multiple tables and SQL*Developer will create the JOIN syntax based on defined FK.
ThatJeffSmith-Oracle goes over the speed improvements for 18.2 here.

In both cases, the Top n rows clause is not part of the resulting SQL statement.

RDBMS

Oracle database introduced a TOP n ROW clause in 12c.

Example:  https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1


innodb_page_size is not present in [mysqld] section in my.ini file in MySQL Server 8.0? I have to edit the innodb_page_size to 32K but I cannot find innodb_page_size in [mysqld] section in my.ini file. I am using MySQL Server 8.0.13. Where can I find it?

If it is not explicitly set it defaults to 16k. You would set it to a new value in the [mysqld] section but it cannot be changed once the instance has been created.


Is this correct normalization for my tables? I think I'm confusing myself and making this too complicated. At this point, I need outside advice.
I have a Laravel 7 application where a user will enter orders. Each order has one customer. Each order has one contact. Each customer has many contacts, but contacts only belong to one customer.
When the user is entering the order and chooses a customer, another select field populates with contacts associated with that customer.
Here is my structure:
orders table:
id
customer_id
contact_id
Customers table:
id
name
Contacts table:
id
customer_id
Is this good or do I need to change the structure? I'm having trouble setting up the relationships in Laravel.

I'm not familiar enough with Laravel 7 to understand how well it implements alternate keys/composite keys, but this is basically what you've described:

Notes:

The alternate keys are crucial as an Id in and of itself does not provide uniqueness as required by the Relational Model.  If I can spam "Customer 1, Dwayne Johnson" a million times into Contact then no key exists and there is a normalization error.
The composite key (CustomerId, ContactId) is required to ensure CustomerId is migrated properly to Order
IE is inversion entry, or non-unique index.  This allows orders to be quickly searched by customer.
ContactId itself is not unique.  If you are using a ORM that requires an Id column on everything, you would need to make the relationship between Customer and Contact non-identifying.  In doing so you will remove the ability to retrieve a customer's orders without joining to Contact first.  Under no circumstances should you create a relation between Customer and Order as the Contact selected must belong to the Customer in question and this would introduce a normalization error.

Revised model based on comment:


Maintain stable data in a database I am doing Performance evaluation of my application. The size of database is around 20GB. I am using jMeter as a performance evaluation tool. Here each performance test (ie., Each jMeter run) there is some number of records are inserted. Here is my testing sequence.

Run jMeter and check the time taken to complete an action
Finetune the code for performance.
Re-Run the same scenario and check the timing.

For example.
 Before run :

Id      Name
1       XXXX
2       YYYY
3       ZZZZ

After Step 1, My table data look likes

Id      Name
1       XXXX
2       YYYY
3       ZZZZ
4       AAAA
5       BBBB
6       CCCC
..etc

Now I wants to revert my table like the first ie.,

Id      Name
1       XXXX
2       YYYY
3       ZZZZ

Here what my problem is the DB size is growing for each test run.

Is there any way to maintain some benchmark (if the DB size is 20GB means, It must be maintain the size as 20GB till end of my test). Any one please guide me how to achieve my requirement.

I don't really have the possibility to try jMeter now, so I don't know how following the below advice would affect your tests.

Normally benchmarking is done so that after the tests the original database state is restored.  This is achieved by rolling back all changes.

In the case of jMeter you have to set AutoCommit to false in the JDBC configuration, then issue a ROLLBACK as the very last command in your test script.


Linked Server: An error occurred during decryption We're running a SQL Server instance on AWS RDS. I believe this is a 2017 instance. We also have a couple of Linux EC2 instances running SQL Server (not sure the version, but I assume 2017)

Our RDS instance has linked servers set up to both of the Linux instances. What I've found is that after creating the linked servers, I can query them and they work fine.

This is a QA environment and, to save money, we take all the servers down at night. Then they are started on demand. The problem I'm seeing is that once the servers come back up, I get the following error when trying to query the linked servers:

  System.Data.SqlClient.SqlException (0x80131904): An error occurred during decryption.

My best guess is that when the RDS instance comes back up, it appears to be different hardware and I'm guessing there is a decryption key based on that hardware?

I've tested a couple things to eliminate possibilities:

I tested leaving the Linux instances up and just stop/start (not restarted) the RDS instance. This seemed to reproduce the problem. I haven't yet tried the reverse (leave RDS running and stop/start a Linux instance).
I tried dropping and recreating the linked server when I get this error. This seems to fix the problem.

Theoretically, I guess I could use this sp_procoption procedure I've been reading about to run a query at server startup that would drop and recreate all the linked servers. But that seems like the wrong way to fix this.

We could leave our servers running 24x7, though our budget is pretty lean so we'd rather not. And besides, if we had some other reason to stop/start our RDS instance, we still have this problem.

Is there any way to make this work other than just running a drop/create script on the linked servers at server startup?

Update: July 2020
As of July 2020, we now replicate the service master key after a host replacement. Previously, during a host replacement linked servers with passwords would get the error “An error occurred during decryption”. This is now resolved.
https://aws.amazon.com/blogs/database/implement-linked-servers-with-amazon-rds-for-microsoft-sql-server/

If a host replacement happens, RDS SQL Server does not restore the service master key. This means that linked servers with passwords will get the error “An error occurred during decryption” when you try to use the linked server. Unfortunately, there is no current notification of a host replacement. The closest you can come is to monitor reboots and failovers via SNS events. You can read more about subscribing to those events on Using Amazon RDS Event Notification. When a reboot or failover happens, you need to manually re-add the linked server login passwords. This is a temporary workaround until a future update to RDS SQL Server addresses this by restoring the service master key after a host replacement.


Easy way to change Column ID (primary key) of a table to Auto increment I started to use SQL Server recently and I still don't know the best way to do some things.

I created all the tables with a column ID as the primary key. Now when I try to insert values I get the following error:

  "Cannot insert the value NULL into column 'id', table 'Project.dbo.Table'; column does not allow nulls. INSERT fails. The statement has been terminated."

What is a good, simple solution to this problem?

My preferred method for a non-empty table is to use the ALTER TABLE ... SWITCH method which is a meta-data-only operation.

This is a simple test-bed setup, consisting of the initial table, named dbo.SwitchTest1, and the target table containing an IDENTITY, named dbo.SwitchTest2:

USE tempdb;
IF OBJECT_ID('dbo.SwitchTest1') IS NOT NULL
DROP TABLE dbo.SwitchTest1;
IF OBJECT_ID('dbo.SwitchTest2') IS NOT NULL
DROP TABLE dbo.SwitchTest2;
GO
CREATE TABLE dbo.SwitchTest1
(
    ID INT NOT NULL
        CONSTRAINT PK_SwitchTest1
        PRIMARY KEY CLUSTERED
    , SomeVal VARCHAR(50) NOT NULL
);
CREATE TABLE dbo.SwitchTest2
(
    ID INT NOT NULL
        CONSTRAINT PK_SwitchTest2
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SomeVal VARCHAR(50) NOT NULL
);

INSERT INTO dbo.SwitchTest1(ID, SomeVal)
VALUES (1, 'This is a test');

Here, we use the ALTER TABLE ... SWITCH method to replace the data in dbo.SwitchTest2 table with data from dbo.SwitchTest1, while maintaining the definition of dbo.SwitchTest2 including the IDENTITY column.  Although not strictly necessary, you probably want to rename any constraints included in the definition of dbo.SwitchTest2 after the fact to match what they used to be called in dbo.SwitchTest1.  I've done that below for the primary key.

BEGIN TRANSACTION
BEGIN TRY
    ALTER TABLE dbo.SwitchTest1 SWITCH TO dbo.SwitchTest2;
    DROP TABLE dbo.SwitchTest1;
    EXEC sp_rename @objname = 'dbo.SwitchTest2'
        ,@newname = 'SwitchTest1', @objtype = 'object';
    EXEC sp_rename @objname = 'PK_SwitchTest2'
        , @newname = 'PK_SwitchTest1', @objtype = 'object';
    EXEC sp_recompile @objname = 'dbo.SwitchTest1';
    DBCC CHECKIDENT('dbo.SwitchTest1');
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH

In the code above, I've also added a call to sp_recompile - this causes stored procedures, triggers, and user-defined functions to be recompiled the next time that they are run. It does this by dropping the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure or trigger is run.  This is A Good Thing™ since the object_id for the table dbo.SwitchTest1 table has changed.  Also, I'm running DBCC CHECKIDENT('<tablename>'); to cause the IDENTITY() column to be updated with the next valid value to be used by an INSERT operation.

Here, we're inserting a value into the SomeVal column without specifying a value for the ID column, which is now an IDENTITY column:

INSERT INTO dbo.SwitchTest1 (SomeVal)
VALUES ('This is another test');

And the results:

SELECT *
FROM dbo.SwitchTest1;


SQL Server 2012 how to create new schema from existing database selecting only subset of database objects? I'm looking for a way to create new schema from existing database selecting only subset of database objects. I.e. I want to have some "database representation" which only includes subset of tables explicitly specified. What is the easiest way to create schema which includes/has access not to all objects in the database. Is it possible at all? I'm trying to do this in MS SQL Server 2012 R2.

You can create a new schema and expose the objects you need with a synonym:

USE master;
GO

IF DB_ID('testDatabase') IS NOT NULL
BEGIN
    ALTER DATABASE testDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE testDatabase
END
GO

CREATE DATABASE testDatabase
GO

USE testDatabase;
GO

-- test table in schema dbo
CREATE TABLE dbo.test (
    i int primary key clustered
)
GO

INSERT INTO dbo.test VALUES (1),(2),(3)
GO

-- new schema for exposed objects
CREATE SCHEMA schema1
GO

-- synonym for dbo.test
CREATE SYNONYM schema1.test FOR dbo.test
GO

-- new user with no permissions on the dbo schema
CREATE USER testUser WITHOUT LOGIN
GO

-- grant permissions on the new schema only
GRANT SELECT ON schema::schema1 TO testUser
GO

-- change security context to testUser
EXECUTE AS USER = 'testUser'
GO

SELECT * FROM schema1.test; -- works

SELECT * FROM dbo.test; -- fails

-- revert to the original security context
REVERT
GO


Adding Where Clause Skews Data This is the result set that I get returned using my query:

empname itemssold
JJ      14

But this is my desired result set:

empname itemssold
AA      0
BB      0
CC      0
DD      0
JJ      14
RR      0

It seems to pulls all employees until I add in the where clause to my query which makes me wonder if I am adding it in the wrong place, or am I setting up my query incorrectly?

Below is DDL & My query I use:

Create Table #ShowAll
(
  empname varchar(1000)
  ,empid varchar(100)
)

Create Table #Data
(
  empid varchar(100)
  ,itemssold int
  ,beenverified varchar(100)
)

Insert Into #ShowAll Values
('JJ', 'J1'),
('AA', 'A1'),
('BB', 'B1'),
('CC', 'C1'),
('DD', 'D1'),
('RR', 'R1')

Insert Into #Data Values
('J1','14', 'Yes'),
('A1', '12','No'),
('B1', '13', 'No')

Select sa.empname
    , Da.itemssold
FROM #ShowAll sa
LEFT JOIN #Data da
    ON sa.empid = da.empid
WHERE da.beenverified = 'Yes'

You must move the test on beenverified  from the WHERE clause to LEFT ... ON ...:

SELECT sa.empname
    , coalesce(Da.itemssold, 0)
FROM #ShowAll sa
LEFT JOIN #Data da
    ON sa.empid = da.empid
    AND da.beenverified = 'Yes';

With a LEFT JOIN, when there is no match on empid between the two tables, this query (minus the WHERE clause) returns NULL for beenverified (and other columns from #Data):

empname itemssold   beenverified
JJ      14          Yes
AA      12          No
BB      13          No
CC      NULL        NULL
DD      NULL        NULL
RR      NULL        NULL

If beenverified is tested in the WHERE clause, it does not return rows where the condition is not met:

beenverified IS NULL
beenverified <> 'Yes' (i.e. = 'No')

Only row JJ is then valid. With the test in the WHERE clause, this query behaves like an INNER JOIN ....

Coalesce is then added in order to replace NULL by 0.


Remove particular table from being replicated in a Multi-Master replication I have followed all the steps in this link to set-up MySQL Master-Master replication for two database servers.

The master-master replication works fine.

Here is my my.cnf file, which contains some additional configuration than that present in the above link for auto-increment:

server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 10
auto-increment-offset = 1

log_bin = "/var/lib/mysql/mysql-bin.log"
binlog_do_db = my_employee_database

log-slave-updates

expire_logs_days = 20

binlog_format = mixed

Similarly this is set-up in the other node with some changes.

my_employee_database has 4 tables:

my_employee
my_company
my_address
my_audit.

Question

Is there a way I can ignore or stop or remove the table my_audit from being replicated among the two server nodes and have the replication run normally for the other 3 tables as it is?

Environment

Database: MySQL 5.6.25
OS: CentOS 6.5 64 bit

Yes you can, simply add replicate-ignore-table in your my.cnf . please refer following link for mysql documentation :

https://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_replicate-ignore-table


How to do a cumulative count for certain values I have a scenario to do cumulative counts in certain way. So I decided to create an experiment for that before I run the query in the actual environment.

I have created a table as:

create table ttable (id numeric(10,0), txt text, dtd timestamp, cnt numeric)

And inserted the values as:

insert into ttable values (1,'Pen','2019-01-01',10),
  (2,'Pencil','2019-01-01',10),
  (3,'Pen','2019-01-02',20),
  (4,'Eraser','2019-01-02',1),
  (5,'Eraser','2019-01-03',1),
  (6,'Other1','2019-01-03',10),
  (7,'Other1','2019-01-04',10);

Now, I do have data as below:

 id  txt      dtd                   cnt
 --  ------   -------------------   ---
 1   Pen      2019-01-01 00:00:00   10
 2   Pencil   2019-01-01 00:00:00   10
 3   Pen      2019-01-02 00:00:00   20
 4   Eraser   2019-01-02 00:00:00   1
 5   Eraser   2019-01-03 00:00:00   1
 6   Other1   2019-01-03 00:00:00   10
 7   Other1   2019-01-04 00:00:00   10

I want the output as below:

txt      dtd                    cnt
------   -------------------    ---
Eraser   2019-01-01 00:00:00    0
Pen      2019-01-01 00:00:00    10
Pencil   2019-01-01 00:00:00    10

Eraser   2019-01-02 00:00:00    1
Pen      2019-01-02 00:00:00    30
Pencil   2019-01-02 00:00:00    10

Eraser   2019-01-03 00:00:00    2
Pen      2019-01-03 00:00:00    30
Pencil   2019-01-03 00:00:00    10
Other1   2019-01-03 00:00:00    10

Eraser   2019-01-03 00:00:00    2
Pen      2019-01-03 00:00:00    20
Pencil   2019-01-03 00:00:00    10
Other1   2019-01-04 00:00:00    20

(I have added empty lines in above output for clear presentation).

I want to consider only first three (Eraser, Pen, Pencil) should be repeated for each date, rest can be added if there is any entry for the date.

I have found a query from Stackoverflow for date range generation and modified based on my needs:

select dt::date from generate_series('2019-01-01', '2019-01-05', '1 day'::interval) dt

I could use above query to join with my main table data. However, I couldn't achieve what I want:

with b1 as (
select id, txt, dtd, sum(cnt) over (partition by txt order by dtd) cnt from ttable
group by 1,2,3, cnt order by 3
),
b2 as
(select dt::date from generate_series('2019-01-01', '2019-01-05', '1 day'::interval) dt ),
b3 as
(select 'Pen' obj union select 'Pencil' obj union select 'Eraser' obj)
select b2.dt, b1.*, b3.* from b2 left join b1 on b1.dtd = b2.dt left join b3 on b1.txt = b3.obj

What am I missing here?

I think that you want something like this (see fiddle here):

WITH item AS
(
  SELECT txt FROM ttable WHERE txt IN ('Eraser', 'Pen', 'Pencil')

  -- You have specified these three items as somehow "special", so that's where
  -- this bit comes from.

),
item_date AS
(
  SELECT DISTINCT i.txt, t.dtd FROM item i, ttable t  -- CROSS JOIN special items
  ORDER BY dtd, txt                                   -- and dates
)
SELECT id.dtd, id.txt, COALESCE(t.cnt, 0) AS icnt -- COALESCE to get 0 for NULLs
FROM item_date id
LEFT JOIN ttable t
  ON id.txt = t.txt
  AND id.dtd = t.dtd
UNION                             -- the UNION here is to reunite non-special items
SELECT t.dtd, t.txt, t.cnt        -- i.e. Other1 with the special ones.
FROM ttable t
WHERE t.txt NOT IN (SELECT txt FROM item)
ORDER BY dtd, txt

Result (dates separated for clarity as in your question):

                dtd txt   icnt
-----------------------------
2019-01-01 00:00:00 Eraser   0
2019-01-01 00:00:00 Pen     10
2019-01-01 00:00:00 Pencil  10

2019-01-02 00:00:00 Eraser   1
2019-01-02 00:00:00 Pen     20
2019-01-02 00:00:00 Pencil   0  -- << your INSERT has no Pencils for 2019-01-02

2019-01-03 00:00:00 Eraser   1
2019-01-03 00:00:00 Other1  10
2019-01-03 00:00:00 Pen      0
2019-01-03 00:00:00 Pencil   0

2019-01-04 00:00:00 Eraser   0
2019-01-04 00:00:00 Other1  10
2019-01-04 00:00:00 Pen      0
2019-01-04 00:00:00 Pencil   0


Is NOLOCK always bad? I am a Report Developer who wants to make my queries as efficient as possible.  I used to work with a DBA who told me - I believe because I was always dealing with reports on a Production Server - to use NOLOCK in every single query.

Now, I work with a DBA who has banned NOLOCK under any circumstance - even when a report of mine (due to a considerable lack of indexes on a couple of tables) is stopping replication and system updates.  In my opinion, in this case, a NOLOCK would be a good thing.

Since most of my SQL training has come various DBAs with very different opinions, I wanted to ask this to a wide variety of DBAs.

It isn't always bad.

Of course it allows you to read uncommitted values (that may be rolled back and hence never logically existed) as well as allowing phenomena such as reading values multiple times or not at all.

The only isolation levels that guarantee that you won't encounter any such anomalies are serializable/snapshot. Under repeatable read values can be missed if a row is moved (due to a key update) before the scan reaches this row, under read committed values can be read twice if a key update causes a previously read row to move forward.

These issues are more likely to arise under nolock however because, by default, at this isolation level it will use an allocation ordered scan when it estimates there is more than 64 pages to be read. As well as the category of issues that arise when rows move between pages due to index key updates these allocation ordered scans are also vulnerable to issues with page splits (where rows can be missed if the newly allocated page is earlier in the file than the point already scanned or read twice if an already scanned page is split to a later page in the file).

At least for simple (single table) queries it is possible to discourage the use of these scans and get a key ordered scan at nolock by simply adding an ORDER BY index_key to the query so that the Ordered property of the IndexScan is true.

But if your reporting application doesn't need absolutely precise figures and can tolerate the greater probability of such inconsistencies it might be acceptable.

But certainly you should not be chucking it on all queries in the hope that is a magic "turbo" button. As well as the greater probability of encountering anomalous results at that isolation level or no results at all ("Could not continue scan with NOLOCK due to data movement" error) there are even cases where the performance with nolock can be much worse.


Calling stored procedure with select and update statement I have a requirement to return a batch of records and then mark the batch complete. I was thinking to acheive this with a select and the update. Something like

create procedure testproc
as

select x,y,z from Table

update Table set retreived = 'Y'

go

The question I have, is what happens if the procedure executes, returns the select but the connection breaks before the batch is retreived. Does the batch still get updated?

Otherwise can anyone think of a better way to acheive this with stored procs?

You can do this in a single statement using the OUTPUT clause:

UPDATE dbo.Table
SET Retrieved = 'Y'
OUTPUT inserted.x, inserted.y, inserted.z;

If you don't really mean to update the entire table every time:

UPDATE dbo.Table
SET Retrieved = 'Y'
OUTPUT inserted.x, inserted.y, inserted.z
WHERE <...where clause...>;


Can I change MSSQL interpretation of date order system wide? I use Laravel with PHP from https://laravel.com/
MSSQL is a driver and works perfectly - with th exception of dates.
For some reason the project has decided that the follwoing is a universal date format

Y-m-d H:i:s
2021-01-21 12:00:00

However this does not seem to be universal as MSSQL interprets this as a US based date eg. The date above is invalid as 21 is considered the month not the day.
I will try to get them to allow date to be set to a custom format but this may take a while.
My MSSQL user is set to Language:British English which solves most of my issues, but not all.
Is it possible to in some way tell MSSQL to interpret a date like this

2021-01-22 12:00:00

as

YYYY-MM-DD HH:II:SS

instead of

YYYY-DD-MM HH:II:SS

which is what it is doing?
Can this be done on a system wide basis?

This is caused by the user being set to "British English". I've always considered it a bug (I imagine someone was told that UK dates had month & day inverted from the US norms, and they incorrectly assumed that applied to ISO8601-a-like dates also). This has been the case since at least SQL7 IIRC. It is irritating because in every other context YYYY-MM-DD is unambiguous.
If you explicitly convert string to dates using CONVERT() you can specify the string format that is being used using its optional third parameter. This may not help if the issue is with date type parameters being sent in SQL strings (instead of prepared queries with typed parameters) as the conversion in that case is out of your hands.
If you only ever provide dates in a year-first format then just avoid the British English user setting. This may not help if your application sometimes passes in non-US year-last dates.


multi valued attribute as primary key I am designing a database system for my current project and got this situation.
I am having a functional dependency as

  D -> E

Here D is multi valued attribute. D can be(i,j,k) or (i,j) or any other combination. And Unique combination of D will define unique value of E. So i am interested in knowing what is the best way of achieving this requirement.

Just to be clear, you can have multi-value primary keys. But PRIMARY KEY means all values in the composite key are NOT NULL.

CREATE TABLE d ( i int, j int, k int, PRIMARY KEY (i,j,k) );

INSERT INTO d (i,j,k) VALUES (1,2,null);
ERROR:  null value in column "k" violates not-null constraint
DETAIL:  Failing row contains (1, 2, null).

So, you must be unique on (i,j,k). That makes sense. This can provide a linear map to E in your example

  Here D is multi valued attribute. D can be(i,j,k) or (i,j) or any other combination.

That's where you've lost me. That doesn't make sense. If they're all NOT NULL the only combination that matters is (i,j,k)

If you want them such that reordering is also not allowed, for instance. Such that these are rejected..

INSERT INTO d (i,j,k) VALUES (1,2,3), (3,2,1);
INSERT INTO d (i,j,k) VALUES (1,2,3), (2,1,3);

We can do that too using an EXCLUDE constraint and sort with an array.

CREATE TABLE d (
  i int,
  j int,
  k int,
  PRIMARY KEY (i,j,k),
  EXCLUDE (sort(ARRAY[i,j,k]) WITH =)
);

Now we get our error.

INSERT INTO d (i,j,k) VALUES (1,2,3), (3,2,1);
ERROR:  conflicting key value violates exclusion constraint "d_sort_excl"
DETAIL:  Key (sort(ARRAY[i, j, k]))=({1,2,3}) conflicts with existing key (sort(ARRAY[i, j, k]))=({1,2,3}).


Repartition Primary Key Index online in Oracle I have an 24x7 OLTP (11gR2) application that endures heavy inserts on particular tables, traffic has increased to the point where we are getting heavy index contention.  We use sequences for the primary key on this particular table and in order to reduce the index contention I want to repartition the primary key index to have 8 hash partitions (currently the index and table are not partitioned).  Can I rebuild the index converting it to partitioned in an online fashion, i.e. not stopping DML on the table?

After much thought, I think I've managed it. Only drawback is that you'll need double the space of the underlying table to do so...

Don't think there's any other way to do it as you can't redefine PKs without dropping and recreating (correct me if I'm wrong).

PHIL@PHILL11G2 > alter table bigtable add constraint bigtable_pk primary key (object_id);

Table altered.

PHIL@PHILL11G2 > commit;

Commit complete.

PHIL@PHILL11G2 > create table bigtable_interim as ( select * from bigtable where 1=0 );

Table created.

PHIL@PHILL11G2 > EXEC dbms_redefinition.can_redef_table('PHIL', 'BIGTABLE');

PL/SQL procedure successfully completed.

PHIL@PHILL11G2 > exec DBMS_REDEFINITION.start_redef_table(uname =>     'PHIL',orig_table => 'BIGTABLE',int_table => 'BIGTABLE_INTERIM');

PL/SQL procedure successfully completed.

PHIL@PHILL11G2 >
PHIL@PHILL11G2 > create unique index bigtable_int_hash on bigtable_interim (object_id) global partition by hash (object_id) (partition p1 tablespace users, partition p2 tablespace users) online;

Index created.

PHIL@PHILL11G2 > exec dbms_redefinition.finish_redef_table(uname => 'PHIL',orig_table => 'BIGTABLE',int_table => 'BIGTABLE_INTERIM');

PL/SQL procedure successfully completed.

PHIL@PHILL11G2 > select count(*) from bigtable_interim;

  COUNT(*)
----------
   2300640

PHIL@PHILL11G2 > select count(*) from bigtable;

  COUNT(*)
----------
   2300640

PHIL@PHILL11G2 >
PHIL@PHILL11G2 > alter table bigtable add constraint bigtable_hashed_pk primary key (object_id) ;

Table altered.

PHIL@PHILL11G2 > -- that reused the hashed index i created

Feel free to correct any mistakes I've made :)

Phil


How do I use SSMS to compare Database 1 Table 1 against Database 2 Table 1? I have two databases - Database1 and Database2.

Both databases contain a table that has similar structure, exemplified as follows:

===========================================================================================
| ID | Name | PhoneNoFormat | DialingCountryCode | InternationalDialingCode | InternetTLD |
===========================================================================================
|    |      |               |                    |                          |             |
===========================================================================================

However, due to some reason, (a) one of the tables in one of the databases has data that is not exactly the same as (b) that contained in the other table in the another database.

So, how can I compare Database1.Table1 against Database2.Table1?

I tried using the following query, but nothing happened so was wondering if I have to rewrite it:

SELECT MIN(TableName) as TableName,
       ID,
       Name,
       PhoneNoFormat,
       DialingCountryCode,
       InternationalDialingCode,
       InternetTLD

FROM

(

  SELECT  'Table A' as TableName,
          A.ID,
          A.Name,
          A.PhoneNoFormat,
          A.DialingCountryCode,
          A.InternationalDialingCode,
          A.InternetTLD

  FROM [D:\DATABASE1.MDF].[dbo].[Table1] AS A

  UNION ALL

  SELECT 'Table B' as TableName,
         B.ID, B.Name,
         B.PhoneNoFormat,
         B.DialingCountryCode,
         B.InternationalDialingCode,
         B.InternetTLD

  FROM [D:\DATABASE2.MDF].[dbo].[Table1] AS B

) tmp

GROUP BY ID, Name, PhoneNoFormat, DialingCountryCode, InternationalDialingCode, InternetTLD

HAVING COUNT(*) = 1

ORDER BY ID

The name of your database can't be [D:\DATABASE1.MDF], that may be the path to the physical data file. So the complete table name (3 part name as in db.schema.table) is [DATABASE1].[dbo].[Table1].

If it's a one time task, use the Red Gate SQL Data Compare. It's a great tool and it offers a trial version.

If you have Visual Studio Professional you should also have Data Comparison Tool inside it. It's another great tool to compare data and schema.

If you don't have access to any other tools to compare the data in two tables, I would use tablediff.exe, which is included in the SQL Server installation. You'll find it in the SQL Server program path. Details here, on MSDN.

If you want to use SQL, than you can use the function binary_checksum (or its relative, checksum) to generate a hash and compare over two different versions of the same row, in case you can compare row by row. You can also use the functions for each column, not only for the complete row (off course, going row by row, based on a key). See an example here.


Disabling protocols using SQL Server Configuration Manager or ENDPOINTs Besides SQL Server Configuration Manager, it's possible to disable access to specific SQL Server protocols by disabling the default ENDPOINTs, but what's the difference between the two options?

Are there security concerns regarding disabling one and not the other? (Should I disable both?)

If you disable the endpoint another endpoint of that connection type could be created.  If you disable the protocol in the config manager no endpoints for that connection type will ever work.

There's no security issue as normal authentication is required for all connection types.


Why does max_used_connections status not get refreshed automatically once it reaches max_connections, even though the Threads_connected falls down I always wonder, why does the max_used_connections value not come down, once it reaches the peak value of max_connections?

I always flush the status and bring down this value to avoid DB connection refused errors and warning messages in the error log:

=====
121112 14:04:36 [Warning] Too many connections
121112 14:04:36 [Warning] Too many connections
121112 14:04:36 [Warning] Too many connections
121112 14:04:36 [Warning] Too many connections
121112 14:04:36 [Warning] Too many connections
121112 14:04:36 [Warning] Too many connections
121112 14:04:36 [Warning] Too many connections
=======

mysql> select version();show variables like "%max_connections%";show global status
         like "%Max_used%";show status like "%thread%";
            +--------------+
            | version()    |
            +--------------+
            | 5.1.52-2-log |
            +--------------+
            1 row in set (0.00 sec)

            +-----------------+-------+
            | Variable_name   | Value |
            +-----------------+-------+
            | max_connections | 150   |
            +-----------------+-------+
             1 row in set (0.00 sec)

            +----------------------+-------+
            | Variable_name        | Value |
            +----------------------+-------+
            | Max_used_connections | 151   |
            +----------------------+-------+
            1 row in set (0.00 sec)

            +----------------------------+-------+
            | Variable_name              | Value |
            +----------------------------+-------+
            | Com_show_thread_statistics | 0     |
            | Delayed_insert_threads     | 0     |
            | Slow_launch_threads        | 0     |
            | Threads_cached             | 89    |
            | Threads_connected          | 2     |
            | Threads_created            | 1344  |
            | Threads_running            | 2     |
            +----------------------------+-------+
            7 rows in set (0.00 sec)

mysql> show variables like "%thread_cache%";
            +-------------------+-------+
            | Variable_name     | Value |
            +-------------------+-------+
            | thread_cache_size | 90    |
            +-------------------+-------+
            1 row in set (0.00 sec)

Here is the Aborted Connections Status:

mysql> show global status like "%Aborted%";
            +------------------+----------+
            | Variable_name    | Value    |
            +------------------+----------+
            | Aborted_clients  | 119      |
            | Aborted_connects | 43855304 |
            +------------------+----------+
            2 rows in set (0.00 sec)

I observed in some scenarios that the max_used_connections value comes down dynamically, once the threads_connected falls down.
Is this only an indication variable to increase the max_connections? Posting this query for better understanding. Any detailed explanations please?

I read somewhere thread_cache_size should be greater than max_connections, what will be the impact if it is less ?

Max_used_connections

  The maximum number of connections that have been in use simultaneously since the server started.

It doesn't come down because it isn't supposed to come down.

Max_used_connections is a status variable, and is defined as "the maximum number of connections that have been in use simultaneously since the server started."

It turns out, you can also reset the value to the current number of connections using FLUSH STATUS.

But that isn't a "partial solution" to anything because it doesn't change anything.  The only thing you are accomplishing with FLUSH STATUS is sort-of baselining the value of an information-only status variable: resetting the value you see, down to the current value of the status variable Threads_connected -- the current number of connections.

This action doesn't change anything about the server's behavior.  If you're seeing a change in behavior after doing that, it's coincidental.

mysql> SHOW STATUS LIKE 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 225   |
+-------------------+-------+
1 row in set (0.00 sec)

The above would be a more useful value for you to watch, while troubleshooting.  It shows the current count.

But addressing your problem, you either have an application that does not properly release its connections -- which needs to be fixed elsewhere, not in MySQL -- or you may be right -- you need to increase the value of the max_connections global variable -- after you consider what's written in C.5.2.7 Too many connections.  That page also explains why your output above shows a Max_used_connections of 151 compared to max_connections of 150.

Setting thread_cache_size to a value larger than max_connections seems like tremendously unhelpful advice... the cache can't possibly grow larger than max_connections and even a cache anywhere close to that size could only make sense if you have a tremendous amount of churn on your threads... which, in a well-behaved application, won't be the case.


Get Aggregate Data From CTE I have a CTE statement that returns line item information for me.  This is great, but I need to manipulate this in order to only get aggregate counts by Employee where the criteria is met.  For example data when this query is executed this is returned

Employee saleid   SaleAmt  SaleDate
Emp A    abc123   1100  2017-01-03
Emp A    def456   1200  2017-01-03
Emp B    ghi789   1500  2017-01-09

Now all that I want returned is

Employee  Count
Emp A 2
Emp B 1

How should this query be modified in order to only return aggregate data?
**Note I am using the RTRIM() and the LTRIM() functions as sometimes a user will input extra whitespace before or after an employee name (cleanup is going to take place soon :)--)

WITH DollaAmt AS
(
    Select
    cm.empName employee
    ,jb.saleID saleID
    ,CAST(COALESCE(jp.saleamt,0) As DOUBLE PRECISION) DollaAmt
    ,CAST(jp.saleDate As Date) saleDate
    FROM salePieces jp
    Inner Join sale jb
    on jp.saleID = jb.saleID
    Inner join employee cm
    on cm.esID = jb.esID
    WHERE jp.saleDate BETWEEN '2017-01-01' AND '2017-03-31'
    AND cm.empStatus IN ('A', 'E', 'L')
    OR RTRIM(LTRIM(cm.empName)) = 'Trainee'
),
employees As
(
    select
    cm.empName As Employee
    ,cm.empStatus As employeeStatus
    FROM employee cm
    ORDER BY Employee ASC
)
Select
DISTINCT(RTRIM(LTRIM(Employee))) As "employeeName"
,ia.saleID
,"TotalDollaAmt"
,ia.saleDate
FROM employees cm
JOIN (
        Select
        RTRIM(LTRIM(employee)) As employee
        ,SUM(DollaAmt) As "TotalDollaAmt"
        ,saleDate
        ,saleID
        FROM DollaAmt
        GROUP BY RTRIM(LTRIM(employee)),saleDate,saleID
) As ia
ON RTRIM(LTRIM(ia.employee)) = RTRIM(LTRIM(cm.Employee))
WHERE CAST(saleDate As Date) BETWEEN '2017-01-01' AND '2017-01-31'
AND "TotalDollaAmt" >= '1000.00'

It sounds like you just need to change your outer query to also GROUP BY the employee name:

...
SELECT
(RTRIM(LTRIM(Employee))) As "employeeName"
,COUNT(1) AS "Count"
FROM employees cm
JOIN (
        Select
        RTRIM(LTRIM(employee)) As employee
        ,SUM(DollaAmt) As "TotalDollaAmt"
        ,saleDate
        ,saleID
        FROM DollaAmt
        GROUP BY RTRIM(LTRIM(employee)),saleDate,saleID
) As ia
ON RTRIM(LTRIM(ia.employee)) = RTRIM(LTRIM(cm.Employee))
WHERE CAST(saleDate As Date) BETWEEN '2017-01-01' AND '2017-01-31'
AND "TotalDollaAmt" >= '1000.00'
GROUP BY (RTRIM(LTRIM(Employee)))


Why is “Access this computer from the network” permission required for proxy credential? https://docs.microsoft.com/en-us/sql/ssms/agent/create-a-sql-server-agent-proxy?view=sql-server-ver15#Restrictions

SQL Server Agent proxies use credentials to store information about
Windows user accounts. The user specified in the credential must have
"Access this computer from the network" permission
(SeNetworkLogonRight) on the computer on which SQL Server is running.

I have logged on to the SQL server computer. Connected to SSMS using the SA user. Created new credentials with the username as domainame\user1. Then configured proxy to use this credential.
What is the purpose of giving this credential user domainname\user1 "access this computer from the network"? The sql server/proxy is on the same computer - so why is there mention of network?

Because if the account doesn't have the "Access this computer from the network" privilege your job step will fail with

Message Unable to start execution of step 1 (reason: Error
authenticating proxy xxxx\xxx, system error: Logon failure: the user
has not been granted the requested logon type at this computer.
('Access this computer from network')).  The step failed.

Ok, followup question, why is that?  I don't know, but either SQL Agent is performing the impersonation using a call to LogonUser with a dwLogonType that requires this privilege, or SQL Agent is checking the privilege directly and generating this error message, instead of just trying to perform the job step and reporting a failure.
In both cases, this suggests a security feature in SQL Agent.
IE if you don't have the privilege to connect remotely to this computer, but you do have the ability to connect to to SQL Server and create a job, then you could bypass the bar on remote authentication when SQL Agent logs you on locally to run your job step.


Fabric - Operation CREATE USER failed for 'fabric_server'@'%'' I am finding the use of fabric to be tottalyh unstable.  Here is what I do and the below is the result:

mysqlfabric group add druid 192.34.59.181:3306
mysqlfabric group add druid 192.34.56.66:3306
mysqlfabric group promote druid

Why is fabric so difficult?  the fabric users were created on all servers.

                                uuid is_alive    status is_not_running is_not_configured io_not_running sql_not_running io_error                                                                                sql_error
------------------------------------ -------- --------- -------------- ----------------- -------------- --------------- -------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
c5c0345b-c101-11e5-a6eb-04019dd77301        1   PRIMARY              0                 0              0               0    False                                                                                False
fb6b2f9d-c0bf-11e5-ac4f-04019da86e01        1 SECONDARY              0                 0              0               1    False Error 'Operation CREATE USER failed for 'fabric_server'@'%'' on query. Default database: ''. Query: 'CREATE USER 'fabric_server'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*0C21C48C1D8B7C42A8155CC993649AB7478D6102''

issue
-----

root@fabric-forex-do-development-ny-druid-20160122035236:~# mysqlfabric group lookup_servers druid
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid            address    status       mode weight
------------------------------------ ------------------ --------- ---------- ------
c5c0345b-c101-11e5-a6eb-04019dd77301  192.34.56.66:3306   PRIMARY READ_WRITE    1.0
fb6b2f9d-c0bf-11e5-ac4f-04019da86e01 192.34.59.181:3306 SECONDARY  READ_ONLY    1.0

You can try to execute on your secondary:

mysql> drop user 'fabric_server'@'%';

mysql> stop slave;

mysql> start slave;

Worked for me.

Best Rgds,
Jeferson Santana
DBA


What tables are affected by Change Data Capture We're looking at using Change Data Capture in SQL 2012. We're trying to work out if there is an easy way to find out what tables have been changed by a given lsn. Is there an inbuilt function, or would we need to query each cdc table to see if the lsn is in there?

UPDATE to explain a bit more about what we're trying to do:

We're trying to build a process that looks at cdc data for all the tables in the database (think 100's). This process will run on a schedule and will know the last lsn that it processed. When it runs we want to do something with each of the cdc tables that have had a change since the last recorded lsn.

We could check the fn_cdc_get_all_changes_dbo_tablename(@lastlsn, @currentlsn, 'all'), but the problem with that is that we would have to run it 100's of times (once for each table). Is there a central place we can check?

First you have to enable CDC at database level.
USE database_name
GO
EXEC sys.sp_cdc_enable_db
GO

Then you have to enable CDC at table level
USE database_name
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'table1',
@role_name     = NULL

Below query will tell you what tables have cdc enabled
SELECT [name], is_tracked_by_cdc
FROM sys.tables

update:

What I'm trying to find is for a given Lsn, which cdc tables have a record

I guess this is not relevant, as when enabling a CDC, you will have

__$start_lsn
__$end_lsn
__$seqval
__$operation
__$update_mask

The __$start_lsn and __$end_lsn will correspond to the table itself that you have enabled CDC ON.
I dont see a need where you have to do a table search based on LSN to find out the table name - unless I am not comprehending your question.
You can even look up in the system tables

cdc.captured_columns
cdc.change_tables
cdc.ddl_history
cdc.index_columns
cdc.lsn_time_mapping => Returns one row for each transaction having rows in a change table. This table is used to map between log sequence number (LSN) commit values and the time the transaction committed.
dbo.systranschemas

Read up : An Introduction to SQL Server 2008 Change Data Capture by Brad McGehee and Introduction to Change Data Capture (CDC) in SQL Server 2008 by Pinal Dave.


Equivalent to BIT_OR function in SQLite? I have code that runs against a MySQL database but the unit tests for the code use SQLite.
The issue is one code method uses a SQL query which aggregates using the BIT_OR function of MySQL. Is there an equivalent or a way to replicate its functionality in SQLite?

SQLite supports using the pipe symbol as a bitwise-OR operator, as in:
SELECT col1 | col2
FROM sometable;

So, if col1 contains 1 and col2 contains 2, the result would be 3.
Here's a SQLFiddle.uk demo showing how that works under SQLite 3.27.