New boolean column in big table OR - bad idea ? - new related 0-1 table I have a big table (200 billion rows) like this :

CREATE TABLE A (
A_Id INT NOT NULL PRIMARY KEY,
A_Attribute1 VARCHAR(100) NOT NULL,
... )

I must add new boolean column NOT NULL with false as a default value, like this :

ALTER TABLE A ADD A_BitAttribute BIT NOT NULL DEFAULT 0

Moreover, in the future, this column will be 0 most of time.

To prevent the cost of adding this column in the A table, I thinked about creating a new related table, like this :

CREATE TABLE ABitAttribute (
A_Id INT NOT NULL PRIMARY KEY,
, CONSTRAINT FK_ABitAttribute_A FOREIGN KEY (A_Id) REFERENCES A (A_Id) ON DELETE CASCADE
)

A row in this table indicates True for A_Id, no line indicates False. The outer join needed to retrieve the data doesn't bother me too much.

Are there any major drawback or theory violation in using this kind of technique ?

edit :

it has to be SQL Server 2005 compatible
the interruption of service has to be minimal (we usually garanty that database has no more than 1 hour)

From a theoretical point of view, booleans are a bit suspicious (not necessarily wrong though). The idea is that we store true propositions, the absence of something is considered false. So, it's definitely nothing wrong with your suggestion from a theoretical viewpoint. Without knowing what your attribute represents it's difficult to be more specific than that.

From a practical point of view, since most of the rows are false, you will save some space by not storing false for almost all rows.

The downside is that you will have to do an outer join, which may affect performance.

EDIT: As pointed out by @eckes in his comment, deletes in A will have to investigate ABitAttribute for presence of the id beeing deleted. The same goes for updates (if the id column being updated). For inserts, there should be no affect


ORA-07445: An unexpected error: core dump We use oracle 10g. We found the following error in a trace file:

   *** 2012-05-07 11:50:56.421
   ksedmp: internal or fatal error
   ORA-07445: An unexpected error: core dump [ACCESS_VIOLATION] [_evaopn2+153] [PC:0x1BBDFA9] [ADDR:0x0] [UNABLE_TO_READ] []
   Current SQL statement for this session:

We took a lot of time to find the cause of this problem but we've been unsuccessful. We were using 9i and it did not have this problem. It only showed up after we recently upgraded to 10g.

What is the problem? How can I resolve it?

It's a bug. See Metalink document 382576.1.

The bug was fixed in Oracle 10.2.0.4, so upgrade to that release (preferably 10.2.0.5).

There are workarounds, but I'm not sure if it's ok to reproduce Metalink documents on this site.


Get least used id within another table I have two tables

Account table

id INT, username TEXT, password TEXT, proxy_id INT, enabled BOOLEAN

Proxy table

id INT, proxy_ip TEXT, proxy_port INT

I would have a loop that goes through accounts one at a time. I want the least used proxy to be assigned to table.proxy_id.

for example if we have 2 proxies in the proxy table and 5 accounts in the Account table

1 10.0.0.1  4000
2 10.0.0.1  4001

Our accounts

1 david    password 2    enabled
2 mark     password 1    enabled
3 jessica  password 1    enabled
4 ashley   password NULL enabled
5 allan    password NULL enabled
6 james    password 2    disabled

My program will loop through all the enabled accounts, it will assign the least used proxy in the total enabled accounts to the account. In the example above David, mark and Jessica already have a proxy set. So the loop will go through Ashley and proxy with id 2 needs to be assigned to Ashley as it is the least used. For Allan proxy 1 or 2 can be assigned since it would be the least used in any case. James should be ignored since his account is not enabled.

I hope I was clear in my question. I think this would need to be done in two queries?

How would these queries look like?

NOTE : This solution is in SQL SERVER

You can do this using while loop.

Here is the query that will do

DECLARE @cnt INT = 1

WHILE @cnt > 0
BEGIN
   update TOP (1) account
  set account.proxy_id = (
      select top 1 proxy.id
      from proxy order by (
        select count(account.id)
        from account
        where account.proxy_id = proxy.id and account.enabled = 1
      )
  ) where account.proxy_id is null
   SET @cnt = (select count(id) from account where proxy_id is null and enabled = 1)
END;

In this query first we count all account for proxy.id in proxy table and select the proxy.id for lowest count.

Then we set that id to user who is enabled and does not have any proxy_id

UPDATE :

In POSTGRESQL you can achieve this by

DO $$DECLARE r int;
BEGIN
r=1;
    while r > 0
    LOOP
        update account set proxy_id = (
            select proxy.id from proxy order by (
                select count(account.id)
                from account
                where account.proxy_id = proxy.id and account.enabled = '1'
            ) limit 1
        ) where id = (
                select id
                from account
                where proxy_id is null and enabled ='1'
                order by id limit 1
        );
        r = (select count(id) from account where proxy_id is null and enabled ='1');
    END LOOP;
END$$;


Can we create multiple users in one login in SQL Server 2008? I have one database where I want 4 users in that particular database, so do I have to create 4 logins, one for each user ?

I want multiple users from one login. I tried to create a second user by using the same login but I am getting error

  The login already has an account under a different user name

If you are using SQL Authentication, then the short answer to your first question (do I have to create 4 logins, one for each user?) is, Yes. You do.

It sounds like you're looking to group individual users under a single entity.

If you are able to use Windows Authentication then you can create an Active Directory Group, let's call it GP-DatabaseUsers. Add the Windows logins as members of that group in AD, and in SQL add the group as a user and attribute the desired credentials to that group. You can still track individual login usage, but you'll be controlling security of a single entity.


Postgres : Multiply length of array to count aggregate result Following command works fine :

select count(*) * 2 from rooms;

But if I am trying to do something like :

select count(*) * array_length(students,1) from rooms;

where students is an array column in the table rooms. Following error is thrown :

ERROR: column "rooms.students" must appear in the GROUP BY clause or be used in an aggregate function
SQL state: 42803
Character: 32

What is the right way to get the desired result?

sum was an obvious choice :

select sum(array_length(students,1)) from rooms;


How to retrieve data from a table with two of the columns in the table with the same value? UserName, UserID, FollowingUserID
'RachelLim', '1003', '1004'
'YunaKim', '1005', '1007'
'AustinLee', '1007', '1005'
'BenChoi', '1010', '1001'
'RebeccaFang', '1009', '1004'
'AliceAng', '1004', '1003'

Basically, i want to find rows in which userid = followinguserid. How do i do it using select?

You can use SELF JOIN to get result

select t1.*
from my_table as t1
join my_table as t2 on t1.userid = t2.followinguserid


How to Export Table sql to Excel file in sql server 2014? I want to export tbl_category to excel file with code. I can export with the wizard but I want to create procedures to create excel file daily.
tbl_category : 2 column contain : [id] is int and [category] is nvarchar(max)

code:

INSERT INTO OPENROWSET

('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\template.xlsx;HDR=YES;IMEX=1','SELECT * FROM [Sheet1$]')
SELECT [id]
      ,[Category]
  FROM [dbo].[TBL_Category]

windows 7 32 bit and 64bit.
sql server2014.
ms office 2010.

error:

  Cannot process the object "SELECT * FROM [Sheet1$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

1

This may be a file permissions/uac issue.

To prove/disprove that, create a new folder and copy (not move) the excel file into it, then give the windows security group "Everyone" Full control on the folder and its files. Update your export query with the new file location and try running it again.

If that works (or if you at least get a different error), then bear in mind that wherever you want to have the export file in the end, the file will need read/write permission assigned for the user account your SQL Server service runs under. (Or the "Everyone" group, but that wouldn't be best security practice.)


Why can hr create an Oracle DB session though not using PWFILE? I have created a database. HR schema has been enabled.

connect / as sysdba is known as operating system authentication.

connect hr/password is not operating system authentication right ? If yes, I guess that authentication is possible only with the oracle password file located at ORACLE_HOME/database for Windows.

I have queried V$PWFILE_USERS but it only showed me sys... Other users (such as hr, system, ...) are not in that view and I guess not in the password file.

Why ? What type of authentication is used by connect hr/password ? Athentication by OS o by Password ?

Only user having sysdba and sysoper privilege are connected to database using password file authentication. Other users such as HR are authenticated using data dictionary.

Password file is required for SYSDBA and SYSOPER privileged users to make remote connection. As I said earlier other users are authenticated using data dictionary, password file is not required for them to make remote connection.

The password is stored in encrypted form user undocumented table called USER$.

SQL> select password from user$ where name='HR';

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


MongoDB remove() function deletes documents but retains indexes I have came across in one of my readings that remove() function will delete the documents without any changes to the existing indexes.

for instance: "db.users.remove() - This will remove all of the documents in the users collection. This doesn’t actually remove the collection, and any indexes created on it will still exist."

I would like to understand why the indexes are retained when the actual referenced data is removed / deleted in that specific collection? is there any specific use case?

Thank you

The db.collection.remove() command is used to remove data from a collection. As such, documents will be removed from the collection and the indexes on the collection will be updated to no longer reference the removed documents.

Remove can be used in such a way to only remove a subset of data. From the docs:

db.products.remove( { qty: { $gt: 20 } } )

For example, you may want to remove a subset of data but retain the existing indexes to continue access the remaining data according to the existing access patterns.

This command contrasts from the db.collection.drop() command, which removes the entire collection along with its indexes.


Can I have more than one db_recovery_file_dest? Can I use more than one db_recovery_file_dest? I have several partitions which could be used, but I think it is not possible to have more than one db_recovery_file_dest.

You can have only one Flash Recovery Area defined by the db_recovery_file_dest. If you do not want to use one directory then you can go for ASM where you can create and use a disk group that can span multiple disks/partitions.

You can also go for LVM if you use Linux. This is simular to ASM. Create a physical group that contains multiple partitions. On that physical group you create a logical volume and mount it to a directory.


Joining two select statements Im using MySql 8.0 and I've been exercising a lot of quries but this one I cant seem to figure it out. I need to join somehow two select statements that I've written and they display the correct results. But I dont know how to join them so I will get the desired result.

SELECT ... FROM (
SELECT users.name,
posts.id,
text_posts.text,
posts.location
posts.createdate

COUNT(*) AS total
FROM posts
INNER JOIN likes
    ON likes.posts_id = posts.id
INNER JOIN users
    ON photos.user_id = users.id
    INNER JOIN text_posts
            ON text_post.posts_id = posts.id
GROUP BY posts.id
ORDER BY total DESC ) as A
    join (select username, user_id, count(user_id)
            as totalfriends FROM follows
    INNER JOIN users ON users.id = follows.user_id
    group by user_id
    order by totalfriends desc )
                as B ON A.posts.id = B.posts.id
                    LIMIT 10;

  The result should be 10 posts but the user needs to have the most amount of friends and that those posts the users created have the most likes, and also display the name of the creator of that post, the content of the post, location, date of creation and the number of likes so basically the two subqueries put together.

You can use the 2nd query as a subquery in this way:

SELECT
    usr1.name,
    posts.id,
    text_posts.text,
    posts.location
    posts.createdate
    COUNT(*) AS total,
    (SELECT
        COUNT(user_id)
    FROM
        follows
    INNER JOIN
        users usr2 ON usr2.id = follows.user_id
    WHERE
        usr2.id = usr1.id
    GROUP BY
        user_id) as totalFriends
FROM
    posts
INNER JOIN
    likes ON likes.posts_id = posts.id
INNER JOIN
    users usr1 ON photos.user_id = usr1.id
INNER JOIN
    text_posts ON text_post.posts_id = posts.id
GROUP BY
    posts.id
ORDER BY
    total DESC


How to insert in a table with only an IDENTITY column? Given a table with only an IDENTITY column, how do you insert a new row? I've tried the following:

INSERT INTO TABLE
(Syntax error)

INSERT INTO TABLE VALUES()
(Syntax error)

INSERT INTO TABLE (Id) VALUES()
(Syntax error)

I am testing something and only need the IDENTITY column. It's not for production.  Otherwise, such a table can be used as a sequence generator, where no other columns are needed.

From the documentation:

  DEFAULT VALUES
  Forces the new row to contain the default values defined for each column.

So:

INSERT dbo.TABLE DEFAULT VALUES;

In addition:

always use the schema prefix
always terminate statements with semi-colons


Is it possible to create a SourceURI Column in SQL? It is possible to create a UUID column in SQL:

create table table_name (
    uuid_column_name uuid,
);

Is it possible to create a SourceURI column in SQL as well? E.g.:

create table table_name (
    source_uri_column_name ?,
);

Note that a question mark has been added as internet research and several attempts were unsuccessful.

Discussion

Perhaps the definition of SourceURI is incorrect in our company? We define  a SourceURI like this:

prefix:UUID:UUID

In which a prefix is a String, e.g. "hello" and a UUID is a UUID, e.g. cd15b4ef-f3c4-49bd-92f4-10a93c041c75.

When I searched for SourceURI I did not find the same definition as for UUID. It seems we have another definition regarding sourceURI in the company or it is not valid at all?

Common practice when modelling compound values like your SourceURI is to split them into the component parts, which have a column each. These are given the types basic to the DBMS i.e. string and UUID.

On retrieval the parts are passed to the application as-is, or concatenated with appropriate punctuation. You may choose to create a calculated column or similar to ensure consistentcy across the application.

Postgres has the composite type construct which may simplify implementation. I have not used it so cannot comment on the implications.


Automatically invoke `SET ROLE` when connecting to PostgreSQL The short version first:

Is it possible to have a new PostgreSQL database connection automatically invoke SET ROLE with a specific role, whether by configurations on the connecting role (using ALTER ROLE), or an option on the end of a connection URI?

Longer, with context:

I'm setting up a web application to use rotating database credentials (thus, there are a variety of roles in play). However, these credentials are also used for modifications to the database (via Rails migrations), and that means tables become owned by a role that isn't meant to exist in the long-term.

I can modify the rotating credentials so they inherit from a parent role (which doesn't have the ability to log in itself), and then via SET ROLE all database modifications are owned by the parent, rather than the short-term child role. This resolves the ownership issue, but it requires every connection to invoke SET ROLE parent - not really feasible.

Hence, I want some way to ensure that every child connection will always operate within the context of the parent role. Is this possible?

This isn't something PostgreSQL can do on its own

You want to do this in your connection pooler

Right after you get a connection from the pool, call SET ROLE

Right after you release a connection to the pool, call RESET ROLE

Not a Ruby guy, so can't help you much with that, but here's how you'd do it in Java:

public class SetRoleJdbcInterceptor extends JdbcInterceptor {

    @Override
    public void reset(ConnectionPool connectionPool, PooledConnection pooledConnection) {

        Authentication authentication = SecurityContextHolder.getContext().getAuthentication();

        if(authentication != null) {
            try {

                /*
                  use OWASP's ESAPI to encode the username to avoid SQL Injection. Can't use parameters with SET ROLE. Need to write PG codec.

                  Or use a whitelist-map approach
                */
                String username = ESAPI.encoder().encodeForSQL(MY_CODEC, authentication.getName());

                Statement statement = pooledConnection.getConnection().createStatement();
                statement.execute("set role \"" + username + "\"");
                statement.close();
            } catch(SQLException exp){
                throw new RuntimeException(exp);
            }
        }
    }

    @Override
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {

        if("close".equals(method.getName())){
            Statement statement = ((Connection)proxy).createStatement();
            statement.execute("reset role");
            statement.close();
        }

        return super.invoke(proxy, method, args);
    }
}


Table Variable and Database Scope I am a junior DBA, and I am still confused by database scope.

Now I am learning about local/global temp table and table variables. I saw an example as following:

declare @T1 as table
(
    col1 int not null
);

insert into @T1(col1) values (10);

exe('select col1 from @T1;');

go

When I run it in SQL Server, I got below message:

  (1 row(s) affected)
  Msg 1087, Level 15, State 2, Line 1
  Must declare the table variable "@T1".

The message means the insert statement was successful while the exec failed. I am confused because:

I don't know why SQL Server considers the @T1 declaration and the insert statement as one block. My colleague told me to regard them as a stored procedure. But I thought a stored procedure should at least follow DECLARE...BEGIN...END format.
There is another example right after this one in book:

declare @T1 as table
(
     col1 int not null
);

insert into @T1(col1) values (10);
go

exec('select col1 from @T1;');
go

And it gave me same error. I don't know what the book is going to show me by using these 2 examples. Or in another word, I don't understand the use of "GO".

Can anyone help? Thanks so much.

Table variables have the scope of the batch or stored procedure they are run in. There is no need to drop the table variable at the end of a batch, however you will not be able to reference the table variable in consecutive batches.

If you execute the SQL as one block, without the exec, then your SELECT will be able to 'see' the table variable and select the data from it.

declare @T1 as table
(
    col1 int not null
);

insert into @T1(col1) values (10);

select col1 from @T1; -- returns 10

go

The exec encapsulates a T-SQL batch, so in your example it is in a completely separate batch from the table declaration and the insert statement and therefore the table variable is not in scope.

Exec does not terminate a batch though, the table variable will still be in scope after the exec is run.

declare @T1 as table
(
    col1 int not null
);

insert into @T1(col1) values (10);

exec('select col1 from @T1;'); -- errors

select col1 from @T1; -- returns 10

go

However, the GO command signals the end of a batch, so placing a GO between the insert and select will also mean you are executing 2 separate batches. You would then also get the error

declare @T1 as table
(
    col1 int not null
);

insert into @T1(col1) values (10);

exec('select col1 from @T1;'); -- errors

GO

select col1 from @T1; -- errors

GO


Msg 8905: DBCC CHECKALLOC Error Messages We recently experienced a database corruption and run integrity checks. DBCC CHECKDB returned a lot of consistency errors and 2 allocation errors.
We have fixed consistency errors by moving data, droping and recreating related objects.

Now we have left 2 allocation errors and we don't know how to resolve them. Can anyone advice us on how to find the cause of these errors?

Below is the code we run;

DBCC CHECKALLOC([DBNAME]) WITH NO_INFOMSGS

The results;

  Msg 8905, Level 16, State 1, Line 1 Extent (1:9612464) in database ID
  22 is marked allocated in the GAM, but no SGAM or IAM has allocated
  it.

  Msg 8905, Level 16, State 1, Line 1 Extent (1:9617504) in database ID
  22 is marked allocated in the GAM, but no SGAM or IAM has allocated
  it.

  CHECKALLOC found 2 allocation errors and 0 consistency errors not
  associated with any single object.

  CHECKALLOC found 2 allocation errors and 0 consistency errors in
  database 'DBNAME'.

  repair_allow_data_loss is the minimum repair level for the errors
  found by DBCC CHECKALLOC (DBNAME, noindex).

I would suggest to take a backup and restore the database to a different server and then run DBCC CHECKDB ...repair_allow_data_loss. This way you will know if there is any data loss or not.

Refer to my answer for more details.

If you have a good backup that you can restore, I would prefer to go with that and export the data out of the corrup database. You can use redgate's data compare (not free, but I have used it and its a lifesaver).

Make sure you do diagnosis of your hardware and update driver firmware to latest version and are on the latest supported service pack for sql server 2008R2.


MongoDB - Store date and optional time as a field I'm developing a web service using MEAN stack + Mongoose, and one of the things I need to implement is storing a date as an object's field. But the thing is, the time should be optional, e.g. the user should be able to save an object filling in only the date without time.

How can I achieve this result? And how can I search on this field later?

You can always insert object with type "date" (type 9), no special codes needed. As you can always insert it as "string" or ISODate.


MySQL thread states when changing an in-memory temporary table to disk-based In High Performance MySQL (3rd edition) they state that:

  You can't tell whether a table was created in memory and then converted to on-disk or just created on-disk to begin with...

What I was wondering though is -

At the point where (the lower of) tmp_table_size or max_heap_table_size is exceeded will a new query (thread_id) be created that will be the same query but with thread state of Copying to tmp table on disk instead of Copying to tmp table?

During some recent debugging we noticed a second query get created right around the 300 second mark but both were of thread state Copying to tmp table. But occasionally we'd see the second query with thread state Copying to tmp table on disk.

The query is the result of a report that only a particular role can request and we instructed those people not to request the report so we are fairly confident that it is not user-initiated.

We also looked at timeouts in MySQL and our application but nothing seems to align with the 300 seconds (there is a MySQL delayed_insert_timeout that defaults to 300 seconds but we changed that value and the second query/thread still was created at 300 seconds).

At the point where (the lower of) tmp_table_size or max_heap_table_size is exceeded will a new query (thread_id) be created that will be the same query but with thread state of Copying to tmp table on disk instead of Copying to tmp table?

This will never happen.

The "same" query can't ever create a new thread id.  Nothing happening in a client thread will create a new thread id.  If it looks like you are seeing that, then it has to be something other than that... something else is connecting and running that query again.

Perhaps the MySQL event scheduler?  Perhaps a timeout at some layer in the application framework?  It seems all but impossible that this is anything other than something (or someone) sending the query again on a new connection.

You should be able to confirm this with the general query log, but it isn't spontaneously MySQL doing this.

Also, going for the quote into its full context, from High Performance MySQL:

  You can't tell whether a table was created in memory and then converted to on-disk or just created on-disk to begin with...

The point is that you can't tell from SHOW STATUS counters whether temporary tables were switched from memory to disk mid-query because of growth in the result-set or initially created as disk-based temporary tables because of some limitation of in-memory tables (like the lack of BLOB support) from looking at the counters.  You can tell when a table is being converted from one to the other because it should say "converting HEAP to MyISAM" and obviously, as you have seen, the processlist does indicate which type of table an implicit temporary table currently is.

But it's inconceivable that you have anything other than another query being sent to the MySQL server from somewhere, if you're seeing what looks like a query forking into or spilling over into another client thread.


How can I determine the last synchronization time for a SQL Server replication subscription? I'm using SQL Server replication to mirror a few tables from an on-premises SQL Server instance to an Azure SQL database. We had an issue where the replication encountered an error, and we incorrectly assumed that the data in the read-only replica was current. I'm trying to setup an alert system that will notify me if the replica database is more than 30 minutes out of sync with the distributor.

In Replication Monitor, I can see the "Last Synchronization", but that timestamp doesn't seem to be in any of the dbo.MS* system tables on Azure SQL. I'm looking to get this information from the subscriber instead of the distributor.

From the subscriber side, you should be able to run EXEC dbo.sp_helppullsubscription to get some level of detail.

As an example:

use [SomeDB]
EXEC dbo.sp_helppullsubscription
    @publisher = N'MYSERVER'
    , @publisher_db = N'SomeDB'
    , @publication = N'SomeDBPublication';

One of the many columns returned will be last updating time, which is in this format:  yyyymmdd hh:mm:ss.mmm.  That column indicates the time the subscription information was updated.

If you look at this query from the subscriber database:

SELECT *
FROM dbo.MSreplication_subscriptions s
WHERE s.publisher = 'MYSERVER'
    AND s.publisher_db = 'SomeDb'
    AND s.publication = 'SomePublication';

The time column contains the time of the last update by the Distribution Agent.

You can setup threshold warnings in Replication Monitor; Microsoft Docs shows how to do that.  Also, this Microsoft Docs page shows how to programmatically monitor various aspects of Replication.

@SqlWorldWide has a good article on monitoring replication on their site.


Understanding `dbcc showcontig` Record Size I am comparing two tables.  They are:

CREATE TABLE Table1(
    Column1 [bigint] NOT NULL,
    Column2 [VARBINARY](max) NULL,
    Column3 [int] NULL,
    Column4 [DATETIME] NULL,
    Column5 [INT] NULL,
    Column6 [DATETIME] NULL,
    [RowVersionId] [timestamp] NOT NULL,
 CONSTRAINT [PK_BagImage] PRIMARY KEY CLUSTERED (Column1 ASC)
)

This is the second table:

CREATE TABLE Table2(
    Column1 [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    Column2 [BIGINT] NULL,
    Column3 [VARCHAR](1) NOT NULL,
    Column4 [int] NOT NULL,
    Column5 [varchar](11) NOT NULL,
    Column6 [varchar](13) NOT NULL,
    Column7 [datetime] NOT NULL,
    Column8 [bigint] NULL,
    Column9 [bit] NOT NULL,
    Column10 [uniqueidentifier] NOT NULL,
    [RowVersionId] [timestamp] NOT NULL,
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED ( Column1 ASC)
)

Neither of them have any non-clustered indexes.  They do each have a foreign key, but the query plans for looking them up are lightening fast! (Clustered Index Seek that takes 00:00:00).

When I copy 55K of rows into the first one it takes 45 minutes.  When I copy 7.7 million rows into the second it takes 7 minutes.

That all seems to make sense to me. (copying images should take longer, right?)

But I wanted to calculate a rough IO for this operation.  So I ran dbcc showcontig on these tables.

Table   MinimumRecordSize  MaximumRecordSize   AverageRecordSize
Table1        75                  123                81.733
Table2        101                 101                101

So here is where I am getting confused.  If the average record size is 20 less for the table with the images in it, then why does it take so very much longer to insert the data?  Especially when I am inserting 7 Million rows more into the second table.

When you're storing data that is bigger than what fits into the SQL Server's 8k page, like varbinary(max) or varchar(max), it's stored in an row overflow data structure. It seems showcontig doesn't show this data at all according to books online:

  DBCC SHOWCONTIG does not display row-overflow storage information and
  other new off-row data types, such as nvarchar(max), varchar(max),
  varbinary(max), and xml.

It seems showcontig is deprecated too.  Its replacement sys.dm_db_index_physical_stats can be setup to show storage of "off row" data.  SAMPLE or DETAIL seems to show this data best.


Windows Server version and SQL Server Is there a blog / webpage / information (I did not find anything) out there, if it has performance benefits, if I use a newer Windows Server version for my SQL Server?

Example: I run SQL 2016 on a Windows Server 2012. Does it make sense to update this server to Windows Server 2016 or the upcoming Windows Server 2019 beside those out-of-support lifecycle topics? Does it make a difference, if I install a new SQL Server 2019 (if it is out) on Windows Server 2016 or Windows Server 2019?

PS: The only point I found to this topic was that - if I want to use NVDIMMs I should use Windows Server 2016 for better support.

There would not be any noticeable performance benefit if you move to higher version of windows server. Normally people move to higher version keeping support in perspective and the host of new features they get in newer version of windows. Refer Compare features in Windows Server versions to see list of new features you get in various versions of Windows Server.


Percona XtraDB Cluster (5.6.28-76.1-56-log) - random OOM events We have a multi-node (each of 64G RAM) cluster. The main (the one we use for mutating data) node randomly just OOMs, this happens both in peak times and in extremely quiet times.

What we've noticed just before the OOM event is usually a bunch of transactions waiting for locks on the main node. MySQL tuner suggests that the max possible usage of mysql is 59G, but when this starts to buildup it exhausts ALL the resources on the box and OOM event happens. There is nothing else running on that box.

The last occurrence of the event was on a quiet period, where there were very small amount of connections (less than 150), with even less actively doing anything.

Can I get any pointers on what may be causing MySQL to go over what is supposed be its maximum in terms of memory?

our my.cnf (sanitised a bit)

[mysqld]
datadir                         = /var/lib/mysql
long_query_time                 = 5
slow_query_log                  = 1
slow_query_log_file             = /var/log/mysql/slow_query.log
log_bin                         = mysql-bin
log_error                       = /var/log/mysql/error.log
tmpdir                          = /var/lib/mysql/.tmpdir
binlog_format                   = ROW
default_storage_engine          = InnoDB

expire_logs_days                = 28
ft_min_word_len                 = 1
ft_stopword_file                = ''
join_buffer_size                = 16M
key_buffer_size                 = 24M
lc-messages-dir                 = /usr/share/mysql
lower_case_table_names          = 0
max_allowed_packet              = 512M
max_connections                 = 400
max_heap_table_size             = 6G
memlock                         = 0
myisam-recover                  = BACKUP
query_cache_limit               = 4M
query_cache_size                = 64M
query_cache_type                = 1
read_buffer_size                = 2M
read_rnd_buffer_size            = 16M
skip-external-locking
skip_name_resolve
sort_buffer_size                = 10M
sysdate_is_now                  = 1
table_open_cache                = 1024
thread_cache_size               = 512
thread_stack                    = 192K
tmp_table_size                  = 6G

explicit_defaults_for_timestamp = 1

innodb_autoinc_lock_mode        = 2
innodb_buffer_pool_instances    = 8
innodb_buffer_pool_size         = 32G
innodb_doublewrite              = 1
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 2
innodb_flush_method             = O_DIRECT
innodb_io_capacity              = 200
innodb_log_buffer_size          = 96M
innodb_log_files_in_group       = 2
innodb_log_file_size            = 1024M
innodb_read_io_threads          = 4
innodb_thread_concurrency       = 0
innodb_locks_unsafe_for_binlog  = 1
innodb_write_io_threads         = 4

innodb_ft_min_token_size        = 0
innodb_stats_on_metadata        = 0

Additional information as requested:

number of threads = 16 per node

uses provisioned 1024 iops (aws) with 1024G storage per node

show global status: https://pastebin.com/kpiWrERR

show global variables: https://pastebin.com/x3uXrvJs

mysqltuner output: https://pastebin.com/7uGHQsGy (we've already been through a few iterations of it)

there are various other system processes the run on that box, but none of them are using more than normal and their memory usage is accounted for, im afraid thats all i can share on that.

Rate Per Second = RPS

Suggestions to consider for your AWS Parameters group

max_connections=300  # from 400 because max_used_connections was 197
innodb_flush_neighbors=2  # from 1 to reduce 95,455 innodb_buffer_pool_pages_dirty faster
innodb_io_capacity=900  # from 200 to use more of your IOPS available
read_buffer_size=262144  # from 2M to reduce handler_read_next RPS of 2,416,022
read_rnd_buffer_size=196608  # from 16M to reduce handler_read_rnd_next RPS of 965,300

to reduce MySQL RAM footprint and reduce CPU busy with performance improvement.

Please view my profile, Network profile for free downloadable Utility Scripts to improve performance and contact info.


How to let two sessions read two different records For example, there is one table T1 with clustered index. I have two sessions and I want to have session one return one record and session two returns another record. Is it possible?

I tried following codes in two sessions but the table scan will block another session.

BEGIN TRANSACTION
SELECT TOP 1 * FROM T1 WITH(UPDLOCK) ORDER BY NEWID()
-- ROLLBACK TRANSACTION

Yes. You can achieve it using READPAST hint.
But you will need to make sure that your query is reading on row level. You need review your indexes and order by. The order by clause should be on an indexed column.

BEGIN TRANSACTION
SELECT TOP 1 * FROM T1 WITH(READPAST, UPDLOCK) ORDER BY IndexedColumn
-- ROLLBACK TRANSACTION

Here the extract from the documentation:

  READPAST

  Specifies that the Database Engine not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped but page-level locks are not skipped. That is, the Database Engine skips past the rows instead of blocking the current transaction until the locks are released.

  For example, assume table T1 contains a single integer column with the values of 1, 2, 3, 4, 5. If transaction A changes the value of 3 to 8 but has not yet committed, a SELECT * FROM T1 (READPAST) yields values 1, 2, 4, 5.

  READPAST is primarily used to reduce locking contention when implementing a work queue that uses a SQL Server table. A queue reader that uses READPAST skips past queue entries locked by other transactions to the next available queue entry, without having to wait until the other transactions release their locks.
  READPAST can be specified for any table referenced in an UPDATE or DELETE statement, and any table referenced in a FROM clause. When specified in an UPDATE statement, READPAST is applied only when reading data to identify which records to update, regardless of where in the statement it is specified.

  READPAST cannot be specified for tables in the INTO clause of an INSERT statement. Update or delete operations that use READPAST may block when reading foreign keys or indexed views, or when modifying secondary indexes.
  READPAST can only be specified in transactions operating at the READ COMMITTED or REPEATABLE READ isolation levels. When specified in transactions operating at the SNAPSHOT isolation level, READPAST must be combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.


How does MySQL coerce types during joins? Why do these two queries behave differently:

mysql> explain select FOOBAAR_hhs.relationship_group from FOOBAAR_hhs left join FOOBAAR on FOOBAAR_hhs.relationship_group = FOOBAAR.relationship_group where new_hh_id = 15387929;

| id | select_type | table       | type  | possible_keys | key       | key_len | ref   | rows     | Extra       |
+----+-------------+-------------+-------+---------------+-----------+---------+-------+----------+-------------+
|  1 | SIMPLE      | FOOBAAR_hhs | ref   | new_hh_id     | new_hh_id | 9       | const |       15 | Using where |
|  1 | SIMPLE      | FOOBAAR     | index | NULL          | BAARn     | 126     | NULL  | 83236197 | Using index |

mysql> explain select FOOBAAR_hhs.relationship_group from FOOBAAR_hhs left join FOOBAAR on cast(FOOBAAR_hhs.relationship_group as char) = FOOBAAR.relationship_group where new_hh_id = 15387929;

| id | select_type | table       | type | possible_keys | key       | key_len | ref   | rows | Extra       |
+----+-------------+-------------+------+---------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | FOOBAAR_hhs | ref  | new_hh_id     | new_hh_id | 9       | const |   15 | Using where |
|  1 | SIMPLE      | FOOBAAR     | ref  | BAARn         | BAARn     | 22      | func  |    3 | Using index |

?

The relationship_group members of both relations are typed VARCHAR(20) NOT NULL, but it is only when an explicit CAST is invoked that MySQL permits use of the available index?

(This is MySQL version 5.1.52-log)

You need to make absolutely sure the character sets of the tables are identical. Keep in mind that a unicode issues could be at play here.

You could redo the query like this:

select FOOBAAR_hhs.relationship_group from FOOBAAR left join FOOBAAR_hhs
on FOOBAAR.relationship_group = FOOBAAR_hhs.relationship_group where new_hh_id = 15387929;

or

select FOOBAAR_hhs.relationship_group from FOOBAAR left join FOOBAAR_hhs
USING (relationship_group) where new_hh_id = 15387929;

Try the explain for these and see if it changes


How would you optimize this 2 milion records table or queries? I have a relatively large InnoDB table that contains domain names, I want to track when they expire, their traffic rank, owner email, and many parameters like that.

Currently I have around 2 million records, I intend to keep it under 5 million.

I wonder if there is anything else I can do to optimize it, currently a query like this takes around 4-8 seconds:

SELECT *
FROM domains
WHERE domain_whois_date > 0
ORDER BY domain_whois_status asc
LIMIT 500

I consider that memory and caching related options I set in my.cnf work decently because if I quickly run the same query again it loads in few ms. But table updates very often and most queries are not cached and take longer (so I understand).

So far my best solution is to create a memory based table, a mirror of domains called domains_cache and run searches in that; but seems to crash often, I think it's because this memory table takes almost 100% of ram.
Is this the right approach ?

Main InnoDB table takes around 500MB, memory table with same data uses around 2GB.
Running on CentOS virtual machine, 2 cores, 4GB ram.

Please create the following index

create index domains_ix_status_date on domains (domain_whois_status,domain_whois_date);

We're traversing through the index where the rows are already ordered by the way we want them (ORDER BY domain_whois_status), we check the condition domain_whois_date > 0 (still within the index structure) and we retrieve only the relevant rows and output them.


Deleting near duplicates based on one varchar field I'm getting around to cleaning a database but I need some help with deleting near duplicates in MySQL database but is only off by one character in a column.  I've searched and found ways to remove true duplicates but each off the solutions I've seen for near duplicates (timestamp, deleting without worry about which record stays) don't seem to apply.

Certain data must remain based on one column.

id  |   plyrID |  year |  pos | teamID
1   | 111548   | 2005  | OF   | 367
2   | 111548   | 2005  | RF   | 367
3   | 111548   | 2006  | RF   | 332
4   | 592743   | 2012  | SS   | 99
5   | 592710   | 2012  | SS   | 111

If there is a near duplicate based on plyrID, year, and teamID and if pos = "OF", I want to keep the record where the pos is either "RF", "LF", or "CF".  The result should look like:

id  |   plyrID |  year |  pos | teamID
2   | 111548   | 2005  | RF   | 367
3   | 111548   | 2006  | RF   | 332
4   | 592743   | 2012  | SS   | 99
5   | 592710   | 2012  | SS   | 111

where the record with pos = "OF" is deleted and the other remains.

Any help would be much appreciated.

So, rephrasing your requirement a little, you want to delete every row whose pos has the value of OF and for which another row in the same table exists with the same plyrID, year and teamID, and with pos of RF, LF or CF.

That sounded almost SQL, you would only need to correct some "grammar":

DELETE FROM
  MyTable
WHERE
  pos = 'OF'
  AND EXISTS
  (
    SELECT
      *
    FROM
      MyTable AS t2
    WHERE
      t2.plyrID = MyTable.plyrID
      AND t2.year = MyTable.year
      AND t2.teamID = MyTable.teamID
      AND t2.pos IN ('RF', 'LF', 'CF')
  )
;

The above is standard SQL but it wouldn't work in MySQL. Needs rewriting with JOIN:

DELETE MyTable
FROM MyTable
  JOIN
      MyTable AS t2
    ON
      t2.plyrID = MyTable.plyrID
      AND t2.year = MyTable.year
      AND t2.teamID = MyTable.teamID
      AND t2.pos IN ('RF', 'LF', 'CF')
WHERE
  MyTable.pos = 'OF'
;


Dropping multiple KEYS of a table Dropping multiple KEYS in a table is a preferred option or one by one is preferred?

“DROP KEY A,DROP KEY B,DROP KEY C"

For more 900 million records in the table.

How does it work internally?

Dropping multiple indexes all in one SQL command is way better. Why ?
SCENARIO #1
ALTER TABLE mytable DROP INDEX ndx1;
ALTER TABLE mytable DROP INDEX ndx2;
ALTER TABLE mytable DROP INDEX ndx3;

This is what would happen

Creates an empty temp table
Removes the index from the empty temp table
Copies the data into the temp table
Swaps temp table with original table
Drops original table

In #SCENARIO #1, this would happen three times.
SCENARIO #2
ALTER TABLE mytable
    DROP INDEX ndx1,
    DROP INDEX ndx2,
    DROP INDEX ndx3
;

Would you believe MySQL used to do this transformation 3 times back in 2006 under MySQL 4.1 ? In other words, SCENARIO #2 would be handled like SCENARIO #1. I actually wrote a post in the  MySQL AB General Discussion Forum requesting this be changed (Why does mysql drop index very very slow in a large table?).
Someone asked in the DBA StackExchange if MySQL still did this. I wrote a post here saying this is no longer the case (Does MySQL still handle indexes in this way?) and that question references my original MySQL AB post.
What MySQL does today is this:

Creates a temp table
Runs the 3 ALTER TABLE commands against the empty temp table
Copies the data into the temp table ONCE
Swaps temp table with original table
Drops original table

Therefore, you can trust that MySQL will do the right thing today.
Your should go with SCENARIO #2.
GIVE IT A TRY !!!
UPDATE 2015-01-02 13:06 EST
You just asked

What will happen to new operations to the table? Where will new records gets stored at the time of copying? Would it take table lock or metadata lock ?

The table will be locked the entire time. Of course, SCENARIO #1 will be locked three times as long as SCENARIO #2. If you cannot take downtime for this, you only have one option : pt-online-schema-change. All your INSERTs, UPDATEs, and DELETEs will be handled properly during the change.
UPDATE 2015-01-03 08:33 EST
@eroomydna just posted a comment.

@rolando, had you considered the effect of dev.mysql.com/doc/refman/5.5/en/innodb-create-index.html and is default on 5.6. Table should not be rebuilt with the above grouped ddl. No need to use pt-osc for this operation.

@eroomydna is right to point this out. The URL he supplied gives insight into this.
For your particular question, @eroomydna says do not need pt-online-schema-change, and he is right in this instance because you are dropping three indexes. Note the Implementation Details of Fast Index Creation, Paragraph 2:

Dropping a secondary index is simple. Only the internal InnoDB system tables and the MySQL data dictionary tables are updated to reflect the fact that the index no longer exists. InnoDB returns the storage used for the index to the tablespace that contained it, so that new indexes or additional table rows can use the space.

You have a 900 million table and probably a lot of index pages for the three indexes. Dropping the index is being done in a logical manner and must invalidate all those pages to make the space reusable. This will also create lots of shared locks allowing reads and blocking writes. So, this operation should be relatively fast. How long it takes I cannot tell you. Since you said you cannot take the downtime, it could seconds, minutes, maybe even hours for a live table with lots of shared locks and blocked writes.
If you have a copy of the table on a DEV or test server, run the DROP KEY A,DROP KEY B,DROP KEY C just so you can see how fast it runs. Then, you can consider whether downtime would be worth it. Nevertheless, If you want the table live and really cannot do the downtime, you should still go with pt-online-schema-change.
As for my answer, it would apply only if

you drop an index and create an index with the same name
drop/add the PRIMARY KEY


Which kind of table design best suitable for normalization in SQL server? I have three different kinds of products i.e., vehicle, gold and property in our application. Each product has almost 30 different kind of its own inventory details. I have designed my table structure as below:-

Header Table:-

 PkId ------ + ProductFkId------ + AssetId ------ + RegNo
  1          | 1                 | A00001         | R0001
  2          | 2                 | A00002         | R0001
  3          | 3                 | A00003         | R0001

Vehicle Inventory Table:-

 PkId ---------------+ HeaderFkId -------- + Make ------- + Model
  1                  | 1                   | Toyota       | Etios Cross Dieselvdsp
  2                  | 2                   | Toyoto       | Etios Dieselgd
  3                  | 3                   | Toyota       | Etios Dieseld-4d Gd

Gold Inventory Table:-

  PkId ------- + HeaderFkId ----- + Carat18 ----- + Carat19 ----- + Carat20 ----- + Carat21 ----- + Carat22 ------ + Carat23 ----- + Carat24
   1           | 1                | 10            | 0             | 100           | 45            | 35             | 4             | 7
   2           | 2                | 1             | 100           | 0             | 50            | 40             | 6             | 0
   3           | 3                | 30            | 40            | 10            | 10            | 56             | 0             | 10

Property Inventory Table:-

  PkId ----- + HeaderFkId ----- + MarketPrice ----- + ReservePrice ----- + PropertyDescription
   1         | 1                | 300000            | 200000             | Flat No. 202 & 202-A
   2         | 2                | 500000            | 400000             | Flat No. 201 & 201-A
   3         | 3                | 200000            | 100000             | Flat No. 200 & 200-A

All together in Single Table:-

PkId --- + ProductFkId--- + AssetId --- + RegNo --- + Make --- + Model --- + Carat18 --- + Carat19 --- + Carat20 --- + MarketPrice --- + ReservePrice --- + PropertyDescription
 1       | 1              | A00001      | R0001     | Toyota   | Etios     | Null        |  Null       | Null        | Null            | Null             | Null
 2       | 2              | A00002      | R0002     | Null     | Null      | 5           |  84         | 56          | Null            | Null             | Null
 3       | 3              | A00003      | R0003     | Null     | Null      | Null        |  Null       | Null        | 46000           | 500000           | Flat

Instead of storing product data in separate tables, I can also store in one table. Tomorrow any new product is coming at that time I need to create new table or adding additional 10 or 20 columns based on product in one table. If I save all product details in one table at that time except particular product other product columns will store null values. Which table design structure is best suitable for normalization?

It can sometimes be tempting to go down the EAV route (or even diabolically tempting), but hic sunt leones! This delightful image encapsulates the difficulties with such an approach - just like in primary school, you can't mix apples and oranges! Take a small amount of time to peruse the links to see where EAV can lead.

If I were you, I would do something like the following.

CREATE TABLE vehicle
(
  vehicle_id INTEGER PRIMARY KEY,
  vechicle_make VARCHAR (50), -- another table?
  vehicle_name VARCHAR (50),
  vehicle_estimated_value INTEGER,
  vehicle_reserve INTEGER
);

CREATE TABLE gold
(
  lot_id INTEGER PRIMARY KEY, -- database generated
  dealer_id INTEGER, -- another table?
  lot_name VARCHAR (30), -- unique/ or unique with dealer
  gold_carat SMALLINT,
  gold_value INTEGER,
  gold_reserve INTEGER
);

CREATE TABLE portfolio
(
  portfolio_id INTEGER, -- database generated
  pf_asset_type INTEGER, -- FOREIGN KEY from asset_type table
  pf_asset_id,           -- FOREIGN KEY from asset (gold) table

  -- make the combo of pf_asset_type and _id UNIQUE

  pf_asset_value INTEGER,
  pf_asset_reserve INTEGER

  --  If I were you, I'd be using a TRIGGER to feed values
  --  from the different asset tables into these two monetary
  --  fields. Not sure of the exact capabilities of SQL Server,
  --  maybe some sort of non-updateable calculated field?
  --  The advantage this has is that you're not having a massive
  --  sum query for all your assets... Test and see what works for you.
);

This schema should work on any server (tested on PostgreSQL), but obviously, you'll have to include Microsoft SQL Server specifics for auto-incrementing keys and syntax for the PK and FK constraints.

Other asset types can be readily added as required!


How to assign GUID to batches of data based on row counts and tunable batch size I need to assign batches of rows a common GUID id (for consumption by an external process).
Below is a simplified setup describing what I'm looking for:
BASE_TABLE represents one of pre-existing tables in this scenario, much simplified obviously.  Values in the "real" table should be considered random; they're not consecutive in real life (even the ID is not really an int)
CREATE TABLE BASE_TABLE (
    ID int
    ,VALUE1 varchar(10)
    ,VALUE2 varchar(255)
)

TARGET_TABLE represents the "output" of the process I'm working on, which is just the input to something else that requires batches of data to have a uniqueIdentifier BATCHID for each batch.  Batches in real life are ~1000 rows each.
CREATE TABLE TARGET_TABLE
(
  ID int
  ,VALUE1 varchar(10)
  ,VALUE2 varchar(255)
  ,BATCHID uniqueIdentifier
)

(Add some dummy data)
DECLARE @DATA1 int
set @DATA1=0
WHILE @DATA1<100
BEGIN
  INSERT INTO BASE_TABLE (ID,VALUE1,VALUE2) VALUES (@DATA1,'v1'+CONVERT(varchar,@DATA1), 'v2'+CONVERT(varchar,@DATA1))
  SET @DATA1=@DATA1+1
END

/**
example DESIRED RESULT, with batch size of 5

ID
VALUE1
VALUE2
BATCHID

0
v10
v20
38B1B4FB-7F1E-44FD-9336-19095C01C629

1
v11
v21
38B1B4FB-7F1E-44FD-9336-19095C01C629

2
v12
v22
38B1B4FB-7F1E-44FD-9336-19095C01C629

3
v13
v23
38B1B4FB-7F1E-44FD-9336-19095C01C629

4
v14
v24
38B1B4FB-7F1E-44FD-9336-19095C01C629

5
v15
v25
41122454-A743-4545-8F0C-D7B461E072AE

6
v16
v26
41122454-A743-4545-8F0C-D7B461E072AE

7
v17
v27
41122454-A743-4545-8F0C-D7B461E072AE

8
v18
v28
41122454-A743-4545-8F0C-D7B461E072AE

9
v19
v29
41122454-A743-4545-8F0C-D7B461E072AE

10
v110
v210
41122454-A743-4545-8F0C-D7B461E072AE

11
v111
v211
FBDE5513-C869-4F2D-AC4D-40CBEF4A2D48

etc
Important result is that for each batch of size N there are N rows with the same BatchID, which must be a GUID.
I'm pretty sure I could do what I want with a cursor, but I want to do something like this if possible:
select
ID,VALUE1,VALUE2,
NEWID() OVER(PARTITION BY ROW_NUMBER() OVER (ORDER BY ID)) AS BatchID
from BASE_TABLE

but it's not valid because NEWID() isn't an aggregate function.

you have to play with row_numbers
 declare @part int = 3

 ;with baseRowNums as
  (
    SELECT ID, VALUE1, VALUE2,
       Row_Number() Over (ORDER BY ID) AS rn
    FROM BASE_TABLE
  )
 ,batchNums as
 (
 SELECT *,
    Sum(CASE WHEN rn % @part = 1 THEN 1 ELSE 0 END)
    Over (ORDER BY ID, rn
          ROWS Unbounded Preceding) AS part
 FROM baseRowNums
 )
 ,GUIDs as
 (select part, MAX(newid()) as GUID
  from batchNums
  group by part
 )
 select ID, VALUE1, VALUE2, GUIDs.GUID
 from batchNums
 join GUIDs on batchNums.part = GUIDs.part

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=7c26d9bab095ce9d8ca1abf1f4c1a898


List of values accepted by a column Table Animal
has following columns:

SR_ID
First_Name
Last_Name
PET

The PET column has a check constraint; hence it only accepts three values DOG, CAT, FISH.
Sample Data
+-------+-------------+-----------+----------+
| SR_ID |  First_Name | Last_Name |   PET    |
+-------+-------------+-----------+----------+
|     1 |  JOHN       |      D.   |   DOG    |
|     2 |  MIKE       |      S.   |   CAT    |
|     3 |  MARIO      |      M.   |   CAT    |
|     4 |  MICHEAL    |      T.   |   CAT    |
+-------+-------------+-----------+----------+

How would the admin check (via a query) which values are accepted for the column PET?
Some Background
By running the following query:
Select PET, count(PET) from Animal group by PET

I get the following result:

+-----+------------+
| PET | count(PET) |
+-----+------------+
| CAT |          3 |
| DOG |          1 |
+-----+------------+

The admin only gets DOG and CAT values. It is obvious that FISH won't be found since it is not in the database. How can we know that PET column accepts the value FISH?
DESCRIBE Animal would just list column names and its datatype - not the values accepted.

You can check the condition of the check constraint from all_constraints or user_constraints view.

SQL> create table animal(
SR_ID number,
First_Name varchar2(20),
Last_Name varchar2(20),
PET varchar2(20) constraint ckpet check (PET in ('DOG', 'CAT', 'FISH'))
);

Table created.

SQL> select constraint_name,search_condition
from all_constraints
where table_name='ANIMAL'
and constraint_type='C';

CONSTRAINT_NAME     SEARCH_CONDITION
---------------     ---------------------------
CKPET                PET in ('DOG', 'CAT', 'FISH')


How to select rows based on other tables - MySql I have tables like this:

CheckTable

(id) (user_id)
[0]  [403]

UserTable

(id)  (username)
[403] [hello]

I want to select only the rows from the UserTable that do not have the user id row in the CheckTable

I could just select all the users, loop through the rows and then run another query checking if the user_id is in the UserTable, but that would be slow.

Is there any MySQL query that could join and select.

Is this what you're looking for?

SELECT Whatever FROM UserTable
WHERE UserTable.username NOT IN
(
  SELECT CheckTable.user_id FROM CheckTable
);

If this is not suitable, please expand. Possibly with table definitions - (SHOW CREATE TABLE My_Table\G) and some sample data - (INSERT INTO My_Table VALUES(....)), and finally, the result you want with the logic you used to obtain it.

With further research, you might also find this interesting - and also this. As a generic response, substituting NOT EXISTS for NOT IN might be the way to go - YMMV.


Proper table design for sparse primary key In my system I have temporary entities that are created based on rules stored in my database, and the entities are not persisted.

Now, I need is to store information about these entities, and because they are created based on rules and are not stored, they have no ID.

I came up with a formula to generate an ID for these temp entities based on the rule that was used to generate them: id = rule id + "-" + entity index in the rule.
This formula generates unique strings of the form 164-3, 123-0, 432-2, etc...

My question is how should I build my table (regarding primary key and clustered index) when my keys have no relation or order?
Keep in mind that I will only (99.9% of the time) query the table using the id mentioned above.

Options I thought about after much reading, but don't have the knowledge to determine which is better:

1) primary key on a varchar column with clustered index.
-According to various sources, this would be bad because of fragmentation and the wideness of the key. Also their format is pretty weird for sorting.

2) primary key on varchar column without clustered index (heap table).
-Also a bad idea according to various sources due to indexing and fragmentation issues.

3) identity int column with clustered index, and a varchar column as primary key with unique index.
-Can't really see the benefit of the surogate key here since it would mainly help with range queries and ordering and I would never query the table based on this key because it would be unknown at all times.

4) 2 columns composite key: rule id + rule index columns.
Now I don't have strings but I have two columns that will be copied to FKs and non clustered indexes. Also I'm not sure what indexes I would use in this case.

Can anybody shine a light here? Any help is appreciated.

--Edit

Here is what I think I'll be using after reading the comments, and the rationale:

clustered identity int Id as PK
non-clustered unique index on (rule id, index)

Rationale:
1 - I will often query the table by rule id only, which is why it is the leftmost index column;
2 - The Id column can be used for subsequent operations after the first query by rule id (the id will be stored client side);
3 - I will never query by index only;
4 - The unique index guarantees insert consistency, though it still slower than a single int;

I'm thinking clustered pk (rule_id, index)

Do that.  This isn't really a hard case.  (rule_id, index) uniquely identifies a row, so that should be your clustered PK, unless there's some compelling reason to use a different design.  and because

  I will often query the table by rule id only

rule_id should be the leading column in the index.  This supports efficient lookup by rule_id and ensures locality for inserting multiple rows for the same rule_id.


Finding Duplicates in LEFT JOIN Here is my table structure:

create table table_a (emp_name varchar(100), dept_name varchar(100));
insert into table_a values ('amar', 'finance');
insert into table_a values ('akbar', 'human_resource');
insert into table_a values ('amar', 'finance');
insert into table_a values ('anthony', 'information_technology');

create table table_b (emp_name varchar(100), dept_name varchar(100));
insert into table_b values ('amar', 'finance');
insert into table_b values ('akbar', 'human_resource');

mysql> select * from table_a as a left join table_b as b on a.emp_name = b.emp_name and a.dept_name = b.dept_name where b.dept_name is null;
+----------+------------------------+----------+-----------+
| emp_name | dept_name              | emp_name | dept_name |
+----------+------------------------+----------+-----------+
| anthony  | information_technology | NULL     | NULL      |
+----------+------------------------+----------+-----------+
1 row in set (0.00 sec)

The left join mentioned above works as expected.

However, what I need is one record for "amar" as well because there are 2 entries for amar in table A and only 1 entry in table B. So, the result should look like this...

+----------+------------------------+----------+-----------+
| emp_name | dept_name              | emp_name | dept_name |
+----------+------------------------+----------+-----------+
| anthony  | information_technology | NULL     | NULL      |
| amar     | finance                | NULL     | NULL      |
+----------+------------------------+----------+-----------+
2 row in set (0.00 sec)

I need this in an environment where count accuracy is more important than data integrity. The table B record number (2) + query results (2) should match with the table A record numbers (4)

This may appear extrememly convoluted by here is the query you need

select * from
(select count(1) dupcount,emp_name,dept_name from table_a group by emp_name,dept_name) as a
left join
(select count(1) dupcount,emp_name,dept_name from table_b group by emp_name,dept_name) as b
on a.emp_name = b.emp_name and a.dept_name = b.dept_name where a.dupcount <> IFNULL(b.dupcount,0);

I loaded you sample data and I got this:

mysql> select * from
    -> (select count(1) dupcount,emp_name,dept_name from table_a group by emp_name,dept_name) as a
    -> left join
    -> (select count(1) dupcount,emp_name,dept_name from table_b group by emp_name,dept_name) as b
    -> on a.emp_name = b.emp_name and a.dept_name = b.dept_name where a.dupcount <> IFNULL(b.dupcount,0);
+----------+----------+------------------------+----------+----------+-----------+
| dupcount | emp_name | dept_name              | dupcount | emp_name | dept_name |
+----------+----------+------------------------+----------+----------+-----------+
|        2 | amar     | finance                |        1 | amar     | finance   |
|        1 | anthony  | information_technology |     NULL | NULL     | NULL      |
+----------+----------+------------------------+----------+----------+-----------+
2 rows in set (0.02 sec)

mysql>

Here is it without the dupcount values

mysql> select a.emp_name,a.dept_name,b.emp_name,b.dept_name from
    -> (select count(1) dupcount,emp_name,dept_name from table_a group by emp_name,dept_name) as a
    -> left join
    -> (select count(1) dupcount,emp_name,dept_name from table_b group by emp_name,dept_name) as b
    -> on a.emp_name = b.emp_name and a.dept_name = b.dept_name where a.dupcount <> IFNULL(b.dupcount,0);
+----------+------------------------+----------+-----------+
| emp_name | dept_name              | emp_name | dept_name |
+----------+------------------------+----------+-----------+
| amar     | finance                | amar     | finance   |
| anthony  | information_technology | NULL     | NULL      |
+----------+------------------------+----------+-----------+
2 rows in set (0.00 sec)

mysql>

Give it a Try !!!


MySQL data directory deleted After a dump import the disks got full and as to try to fix it I just removed the corresponding directory within /var/lib/mysql and now the database doesn't load anymore.

I have some other databases that were running on this server and now none of them works now. All what I want is to load the mysql again to recover the other databases.

I tried to start the database with innodb_force_recovery = 6 and I it didn't work.

May anyone give a clue to solve this problem?

I would like to share how I have fixed this issue.

I just created a fresh mysql dir with mysql_install_db and then copied all other database directory into it and it worked.


What factors prevent and delay TRUNCATE command? I had a quite large table, perhaps a 50M rows and I had to truncate it. Usually truncate is really fast but in this case it took hours to complete. What are the factors that can prevent truncate command behaving normally?

Other queries. A TRUNCATE statement has to wait until all SELECT statements from that table to complete and as well it must wait until all other transactions that issued INSERT, UPDATE, DELETE, MERGE statements on the table being truncated to commit or rollback. Also other DDL transactions (ALTER TABLE) have to commit or rollback before TRUNCATE can proceed.


sqlite database: advanced SQL or redesign I've been programming for several years, but I'm pretty new to SQL(ite).

I'm designing a database for a sort of a bookkeeping program. What I've got so far:

orders
    (o INTEGER PRIMARY KEY,
     customer INTEGER REFERENCES customers(c),
     num_products INTEGER,
     date STRING,
     discount INTEGER,
     comments TEXT,
     status INTEGER)

o_products
    (o INTEGER PRIMARY KEY REFERENCES orders(o),
     p_0 INTEGER,
     p_1 INTEGER,
     p_2 INTEGER, etc.

o_qtys
    (o INTEGER PRIMARY KEY REFERENCES orders(o),
     q_0 INTEGER,
     q_1 INTEGER,
     q_2 INTEGER, etc.

o_prices
    (o INTEGER PRIMARY KEY REFERENCES orders(o),
     p_0 INTEGER,
     p_1 INTEGER,
     p_2 INTEGER, etc.

The customers and products tables are irrelevant.

Now, this design works well for retrieving the products, quantities and prices in an order, but I'm not sure how to do something like figure out total quantity of a product ordered in all orders. I know I could run a lot of queries and sort through the data in my code, but I'm looking for one elegant query, something like SELECT sum(ordered of this product). I could turn the o_products table on its side, to list what orders each product is in, but then it would be hard to retrieve what products are in one specific order. I've done some searching, but I can't seem to find an answer.

Is there a good way to do this, or should I redesign the database? If I should redesign it, how?

I think the tables o_products, o_qtys, o_prices should be combined into a single table, with a one-to-many relationship with the orders table as follows:

order_details
(
    order_id INTEGER,
    order_item_id INTEGER,
    product_id INTEGER,
    price REAL,
    quantity INTEGER,
    PRIMARY KEY (order_id, order_item_id)
    FOREIGN KEY (order_id) REFERENCES Orders(o)
)

When you want to find out the total quantity of a given product, then

SELECT SUM(quantity)
FROM order_details
WHERE product_id = :product_id


Mysql Master-Master Replication Topologies on >2 machines I have 3 Mysql servers that I would like to replicate: two (including the main server) are local and one is remote.  Let's call my main server A, the secondary server B, and a remote, development server C.

As a possible configuration, I started reading about a ring configuration.  Some comments seem to look down on this type of topology.

My question is, given the current state of Mysql 5.1, what types of master-master topologies are advisable and known to be fault-tolerant?

Is a ring really a bad idea, in general?

In my case, A and B are on an intranet that isn't expected to have any network failures so B can theoretically be used as a failover for A.  Most of the time, I am working on C, a remote machine, which sometimes gets network hiccups in reaching A and B.  None of the data is currently critical (ie as long as they eventually get mirrored, it's fine), but I expect A and B to be mirrored tightly - within say a 5 second timeframe on light loads (I think this should be an easy requirement given my preliminary tests).

I currently have B as a Slave to A, but I am looking forward to going Master-Master on A, B, and C.

A ring can be a very good idea under certain conditions

For servers A, B, C

Server A

Master of B
Slave of C

Server B

Master of C
Slave of A

Server C

Master of A
Slave of B

All three servers have 3 databases (db1, db2, and db3)

Here is the only setup that protects the three databases

restrict all reads and writes to db1 on Server A
restrict all reads and writes to db2 on Server B
restrict all reads and writes to db3 on Server C

Replication would do the following in respone

backup database db1 by replicating to db2 and db3
backup database db2 by replicating to db3 and db1
backup database db3 by replicating to db1 and db2

IMHO performing writes (INSERTs,UPDATEs,DELETEs) for db1 to server A,B,C makes all auto_increment values separate and distinct. This would make it brittle to restore data to other boxes.

More to come...

UPDATE

I have another crazy idea

Have you ever heard of a star topology? I posted stuff earlier on this subject

https://serverfault.com/questions/264374/mysql-in-star-topology/264444#264444
https://stackoverflow.com/questions/5273043/mysql-in-star-topology/5781504#5781504

Create a master with two slaves

For servers A, B, C

Server A

Master of B and C
All tables use storage engine BLACKHOLE

Server B

Slave of A
All user tables use storage engine InnoDB

Server C

Slave of A
All user tables use storage engine InnoDB

Benefits

Post all writes (INSERTs, UPDATEs, DELETEs) to serve A
Writes should be fast becasue BLACKHOLE tables map to /dev/null
Replication actual posts real data on Slaves (Server B and Server C)
no actual data will reside in server A, only binary logs
All SELECTs can go between Server B and Server C via a Load Balancer
Slaves would virtually be identical into terms of content

Drawbacks

Once you set this up you cannot freely use ALTER TABLE statements
If replication breaks at Server A, it stops updating the Slaves


Backup & restore to different servers different locations I need to back up hundreds of databases and restore them to 20 different servers, and different data file locations for destination servers.

Are there any tool that allows me to copy one database/all databases easily?

I agree with Kin dbatools you can either use Start-DbaMigration or Copy-DbaDatabase depending on your needs.

Update: Now, you can migrate from one server to many. This applies to both Start-DbaMigration and all of the Copy-Dba* commands, including Copy-DbaDatabase and Copy-DbaLogin - see https://dbatools.io/migration-enhancements/


Shut Down MySQL during InnoDB rollback after crash I am puzzled once again.

We had a server crash last night during some huge queries. Upon restart in the morning MySQL went into recovery mode. I needed to shut down the MySQL service and it is waiting for... I assume the background rollback for uncommitted transactions as the log says but it's been at it on a HOT server for over 9 hours with no end in sight.

Can the process be safely interrupted and resumed in background while service restarts? Or do we gotta wait it out gasp.

Any input or ideas (or even info about what's going on) would be super helpful. In advance, thank you.

We are running MySQL 5.5.

Here's the log output:

140930  9:04:30  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 41217466, file name .\mysql-bin.001584
InnoDB: Starting in background the rollback of uncommitted transactions
140930  9:26:14  InnoDB: Rolling back trx with id B214637, 15850890 rows to undo

InnoDB: Progress in percents: 1140930  9:26:14  InnoDB: Waiting for the background threads to start
140930  9:26:15 InnoDB: 1.1.8 started; log sequence number 1661733560991
140930  9:26:15 [Note] Server hostname (bind-address): '(null)'; port: 3306
140930  9:26:16 [Note]   - '(null)' resolves to '0.0.0.0';
140930  9:26:16 [Note] Server socket created on IP: '0.0.0.0'.
140930  9:26:18 [Note] Event Scheduler: Loaded 0 events
140930  9:26:18 [Note] wampmysqld: ready for connections.
Version: '5.5.24-log'  socket: ''  port: 3306  MySQL Community Server (GPL)
140930 12:19:36 [Note] wampmysqld: Normal shutdown

140930 12:19:36 [Note] Event Scheduler: Purging the queue. 0 events
140930 12:19:38 [Warning] wampmysqld: Forcing close of thread 20  user: 'root'

140930 12:19:38 [Warning] wampmysqld: Forcing close of thread 19  user: 'root'

140930 12:19:38 [Warning] wampmysqld: Forcing close of thread 17  user: 'root'

140930 12:19:38  InnoDB: Starting shutdown...
140930 12:20:44  InnoDB: Waiting for 1 active transactions to finish
140930 12:21:47  InnoDB: Waiting for 200 pages to be flushed
140930 12:21:49  InnoDB: Waiting for 1 active transactions to finish
140930 12:22:55  InnoDB: Waiting for 1 active transactions to finish
140930 12:24:01  InnoDB: Waiting for 1 active transactions to finish
140930 12:25:07  InnoDB: Waiting for 1 active transactions to finish
140930 12:26:12  InnoDB: Waiting for 1 active transactions to finish
 ... for 9 hours

No, rollbacks cannot be done in the background. Both that and the double write checks have to be done before accepting connections again (meaning that if you kill it, you will have to start again on restart). REDO process, however, can and is done in the background.

What you can do is kill mysql (again) and restart it skipping that step:

/etc/init.d/mysqld start --innodb_force_recovery = 3

Then drop the offending table and recover it from the backups, that may be faster than performing a huge rollback.

For the future, try to avoid huge changes on a single transaction precisely to avoid rollbacks, which can be several times more expensive that the actual commit. I can provide you with some tools to help you do this, if they are administrative changes. If the error was (by) a developer, I can only recommend you the option --i-am-a-dummy. :-)


Invalid use of a side-effecting operator 'INSERT' within a function - Multiple Inserts in Function If this is not the right forum please let me know and move it for me.

I've just got a bunch of inserts like this toward the end of my scalar function:

   INSERT INTO [Raptor].[dbo].[UserRole]
           ([RoleId]
           ,[UserId])
     VALUES
           (3
           ,@NewUserID)

    INSERT INTO [Raptor].[dbo].[UserRole]
               ([RoleId]
               ,[UserId])
         VALUES
               (5
               ,@NewUserID)

In T-SQL, you cannot modify any data in a function. There is no straightforward way around it. There are some obscure hacks, but I would not use them. Use a stored procedure.

The hack, quoting from Erland Sommarskog:

CREATE FUNCTION loophole(@i int) RETURNS varchar(20) AS
  BEGIN
     DECLARE @sql varchar(MAX),
             @cmd varchar(4000)
     SELECT @sql = ' UPDATE rsci ' +
                   ' SET b = CASE ' + ltrim(str(@i + 1)) +
                   ' WHEN 1 THEN ''Ett'' WHEN 2 THEN ''Två''' +
                   ' WHEN 3 THEN ''Tre'' WHEN 4 THEN ''Fyra''' +
                   ' WHEN 5 THEN ''Fem'' WHEN 6 THEN ''Sex''' +
                   ' WHEN 7 THEN ''Sju'' WHEN 8 THEN ''Åtta''' +
                   ' WHEN 9 THEN ''Nio'' WHEN 10 THEN ''Tio'' END' +
                   ' WHERE a = ' + ltrim(str(@i + 1))
     SELECT @cmd = 'sqlcmd -S ' + @@servername + ' -d ' + db_name() +
                   ' -Q "' + @sql + '"'
     EXEC master..xp_cmdshell @cmd, 'no_output'
     RETURN (SELECT b FROM rsci WHERE a = @i)
  END


Querying Table for Index Fragmentation - Multiple Different Results for Same Index I am using the following query to check index fragmentation on a table. Each index returns a 3 rows and each one has a different value for "avg_fragmentation_in_percent". The second row seems to be the accurate value when I check the index fragmentation via the Properties menu of the actual index.

I am curious about the other values that are returned because one row says 100% fragmentation and another row says 0% fragmentation. Does anyone have an idea why I am seeing this?

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'wfm');
SET @object_id = OBJECT_ID(N'wfm.dbo.Lab_Employee_Time_Off');

IF @db_id IS NULL
BEGIN;
    PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;

SELECT OBJECT_NAME(ips.OBJECT_ID) AS 'TableName'
 ,i.NAME AS 'IndexName'
 ,ips.index_id
 ,ips.index_type_desc
 ,ips.avg_fragmentation_in_percent
 ,ips.avg_page_space_used_in_percent
 ,ips.page_count
FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL, 'DETAILED') ips
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
 AND (ips.index_id = i.index_id)
ORDER BY i.name, avg_fragmentation_in_percent DESC

END;
GO

There's a row for each level in the index (add ips.index_level to the output and you will see that there is a row for each level).

Your query should probably simply filter out any row with a low page count (say, 1000), which will eliminate all but level 0 in most cases.


Heidi SQL 'Cannot close tab with running query' I have a HeidiSQL query tab which I cannot close, where the execute button is greyed out.

I have searched for this and the recommendations are to show process list and then kill the queries found in it. However when I run show process list all it shows is the process list query itself. Also the status bar in the bottom right is Idle, which according to other answers I've read should mean nothing is running.

Any help would be appreciated.

Oh, dear, another case of a 3rd party software getting in the way.

Get a command window.
Open the "mysql" commandline tool.
SHOW PROCESSLIST;, identify the pid for the naughty query.
kill 1234; (or whatever the pid number is).

(If you were running the original query from "mysql", a simple CTRL-C would terminate it.  The above stuff would not be needed.)


How to select * if sets of data in two columns matches >= 4 times I'm trying to pull information on repeat dispatches and can't seem to come up with a simple solution to pull my data for a report.

I have the following table:

tblMaster15

 ID | Equipment
----------------
  1 |   Sink
----------------
  2 |   Grill

And another table with location info that I have to join on using ID:

tblMaster15_ABData

 ID | Unit | Region
--------------------
  1 | 600  |  6000
--------------------
  2 | 601  |  7000

And I'm trying to produce a list of the ID, Unit, and Equipment but only when the Unit and Equipment are the same more than 3 times in a given time period.

I've tried the following but it seems to be giving me everything, not just where the count is > 3.

select   m.mrid, a.unit, a.Region, m.Equipment
from     MASTER36 m
join     MASTER36_ABDATA a
on       m.mrid = a.mrid
, (select   a.unit, m.Equipment
   from     MASTER36 m
   join     MASTER36_ABDATA a
   on       m.mrid = a.mrid
   group by a.unit, m.Equipment
   having   count(*) > 3
  ) T2
where    a.unit = t2.unit
and      m.Equipment = t2.Equipment
and      mrSUBMITDATE between '2/7/17' and '3/7/17'
and      mrstatus <> 'DELETED'
and      mrstatus <> 'Canceled'
and      m.Equipment  <> 'BLDNG

I did not think you would need status or submit date. The status is closed or open. Submit date in this format: 2017-01-01 00:50:24.000

I am using SQL Server 2012.

You are counting the occurrences of Unit and Equipment across the entire dataset, not specifically within the period, in which some combinations may well occur more than 3 times while others fewer times or never.

Therefore, you need to apply the same date range filter to the derived table to make the results reflect your requirements:

select   m.mrid, a.unit, a.Region, m.Equipment
from     MASTER36 m
join     MASTER36_ABDATA a
on       m.mrid = a.mrid
join     (select   a.unit, m.Equipment
           from     MASTER36 m
           join     MASTER36_ABDATA a
           on       m.mrid = a.mrid
           where    mrSUBMITDATE between '2/7/17' and '3/7/17'
           group by a.unit, m.Equipment
           having   count(*) > 3
         ) t2
on       a.unit = t2.unit
and      m.Equipment = t2.Equipment
where    mrSUBMITDATE between '2/7/17' and '3/7/17'
and      mrstatus <> 'DELETED'   -- these two can be inlined:
and      mrstatus <> 'Canceled'  -- mrstatus not in ('DELETED', 'Canceled')
and      m.Equipment  <> 'BLDNG';

Note: as you can see, I have also rewritten the join to the derived table to use the explicit join syntax, for consistency.

You could also try this alternative which references each table only once and does not repeat any part of the logic:

select   mrid, unit, Region, Equipment
from     (
           select   m.mrid, a.unit, a.Region, m.Equipment, mrStatus,
                    cnt = count(*) over (partition by a.unit, m.Equipment)
           from     MASTER36 m
           join     MASTER36_ABDATA a
           where    mrSUBMITDATE between '2/7/17' and '3/7/17'
           and      m.Equipment  <> 'BLDNG'
         ) as derived
where    cnt > 3
and      mrstatus not in ('DELETED', 'Canceled');

Instead of counting rows with a normal aggregate function, the query uses a window aggregate function. A window aggregate function lets you return aggregate results along with non-aggregate data.

In the above query you can see that part of the logic is applied directly to the joined tables, at the scope where the rows are counted. Other filters are applied at the outer level, to the derived table, so as not to affect the results of count(*), thus matching the logic of the original query.

The filter on count(*) is also applied outside the derived table, because computed columns cannot be filtered on in the same scope where they are defined and window functions in particular cannot be used in a WHERE clause.

As a final note, please consider these points for maintainability and clarity of your queries:

Use a locale-/culture-independent format for your date/time literals. As you are using dates, consider YYYYMMDD.
Use open-ended ranges for dates and/or times.
Always qualify columns with table names or aliases where more than one table is involved.
End your statements with the ; statement terminator.


Change pg_dump directory output to table name instead of object number pg_dump -F d will result in multiple <number>.dat.gz files which represent the object number in the database.
I wish pg_dump create those files in the format <table name>.dat.gz instead so I'll be able to easy distinguish a file/table in a case of table recoery.

There is no built in way to do this.  You would have to change the code to do what you want. Or you could script something to rename the files based on the output of pg_restore -l .

Whatever you come up with wouldn't be very general. For example, what if <table name>.dat.gz is not a legal file name on your file system?


Normalizing a folder schema I've created a schema to allow creation of folders for customers.

Customers

Id
Firstname
Lastname
StreetLine1
StreetLine2
City

CustomerFolders

Id
CustomerId
ParentId (NULL)
Name

Now as you can see, you can create folders with a specified name and which can be arranged like a tree structure.

My problem now is this: CustomerFolder has a relation to Customer (which is a good thing) and to its parent (which alone is also a good thing). However, it would be possible to create a folder related to a customer with ID 1 and for the same folder to have a parent folder that relates to a customer with id 2.

How can I prevent this at database level?

If you want to force every child of every parent to have the same customer then there are two quick solutions that you could use to accomplish this.

The first, and my preference, would be to use a check constraint on the table like so:

-- define a function that performs the logic required
CREATE FUNCTION dbo.fn_CheckCustomersAreEqual(ParentID INT, CustomerID INT)
RETURNS BIT
AS
BEGIN
RETURN CAST(ISNULL(
    SELECT 1
      FROM dbo.CustomerFolders
     WHERE ID = ParentID AND CustomerID = CustomerID
), 0) AS BIT);
END

-- add a check constraint to the table using the function to provide the outcome
ALTER TABLE dbo.CustomerFolders
ADD CONSTRAINT CK_CheckCustomersAreEqual
CHECK (dbo.fn_CheckCustomersAreEqual(ParentID, CustomerID));

The second option would be to add a trigger to the table to enforce the requirements like so:

CREATE TRIGGER CheckCustomersAreEqual ON dbo.Folders
INSTEAD OF INSERT
AS
BEGIN
-- check to see if the number of parents that meet the criteria
-- match the number of records we are inserting
IF (SELECT COUNT(*) FROM INSERTED AS F
                    JOIN dbo.CustomerFolders AS I ON I.ParentID = F.ID
                                      AND I.CustomerID = F.CustomerID) <>
   (SELECT COUNT(*) FROM INSERTED)
BEGIN
-- they don't match so raise an error
RAISERROR('Child customer does not match parent customer', 16, 1);
END
ELSE
BEGIN
-- they match so insert
INSERT dbo.CustomerFolders
SELECT *
  FROM INSERTED;
END
END

You can adjust the error handling and message returned to the client a bit better in the trigger but I personally prefer using the CHECK CONSTRAINT version.

These are just two possibilities that I can come up with, there may be more and you may be able to optimize the function or trigger to perform better. You could also change the design of your database to accommodate this better if you wanted. I just wrote the example above off the top of my head so be aware that they are also untested.

EDIT

I just noticed that the parent can be null (which makes sense) - but the example I gave above does not account for this. Make sure you account for this scenario in your end solution.


NVMe+bcache as huge storage cache for vertica database We are in process of selecting hardware for 3 machines vertica cluster capable to serve 20-30TB of data (15TB compressed). This translates to 8TB of post-RAID storage. With 16 CPU cores, the storage should yield 800MB/s of "large block random I/O" throughput.

Cost of the storage becomes pretty high, once we count in drives, RAID overhead and server chassis necessary to hold all the drives. 10k RPM drives are relatively small (SAS 10k RPM tops at 1.2TB max) and we need many of them to provide the total storage required. SSD are four times more expensive with SATA/SAS becoming the bottleneck.

I have been thinking about "hybrid" solution of using super-fast NVMe storage (2GB/s thoughput) as cache for a much more slower HDD backend (couple of 10TB SATA drives in RAID1). This can be accomplished by using Bcache technology of linux kernel.

The assumptions are:

most reporting queries to vertica cover only "hot" data area of hour/day/week worth of data. Bcache would cache the frequently used data onto NVMe.
as time goes, "hot" data area shifts on the backend disks surface and Bcache automatically adjusts cached blocks to represent it.
write queries (data import) of vertica will be much slower than reads and that would not block any read operations (write-through mode). This is ok, as we do data import in bulk and don't need it to be immediate.

Back of envelope calculations show that cost of such system to be in range of $2000 as opposed to $8000 fully SSD-based solution (per server).

Can this approach work for vertica (or any other analytics database)? Have anyone used the "fast cache in front of slow backend" approach for databases?

Very interesting !

Not sure how this would work around Tuple Mover internal crunching ? Block change all the time.

my take

I guess a UDT to extract data from Vertica and flush it into memcahe or similar would also be a solution


How many fields is too much for a primary key? I am creating a table to store information about the placement of objects in a grid (game map).

For example, I have a 10x10 grid and I want to store, in the database, that cell (5,5) has a blue box, (3,3) has a red chair and so on.

There can be multiple items in any square, but the same item shouldn't be in the same square twice.

is it acceptable to have this structure? (all columns in primary key)

 (room_id (FK), room_object_id (FK), x_cell, y_cell)(PK)

or will it be better to have this

id(PK), room_id (FK), room_object_id (FK), x_cell, y_cell

I think the first way makes more sense, but I'm worried that increasing the integrity checks whenever something is entered might create a lot of unnecessary overhead.

First off, don't worry about performance until you're sure you have a performance problem, especially for a tiny number of rows.

Second, whether you use a unique index that is primary key or a unique index that isn't the primary key to enforce the uniqueness of the combination of your four columns the performance will be the same with respect to that index.

Whether to make that unique index the primary key should depend on what else is going on in your schema.  If you have another table (or tables) that reference this table, then your compound primary key is going to get propagated to other tables as a foreign key.  If that is going to happen, they you have more to think about.

If you don't need to worry about propagating a compound foreign key, then you should probably just make your compound unique index the primary key of your table.  That would be the least effort which achieves your goal of enforcing your business rule.


Netezza Equivalent to T-SQL ROWVERSION or PL/SQL ORA_ROWSCN Is there a Netezza equivalent to the MSSQL ROWVERSION or the Oracle ORA_ROWSCN?  Essentially I'd like to know if a record changed and be confident that it changed.  Using a checksum isn't reliable 100% of the time.

I'm not sure about the ROWVERSION behaviour, but ORA_ROWSCN I believe would still change if you were to update a row with completely identical values. In other words, the row contents would remain the same but its SCN would be different.

The only practically reliable way to detect the actual change in column values would be to calculate the row checksum or hash.

To answer your question though, consider using the createxid special column in Netezza: http://pic.dhe.ibm.com/infocenter/ntz/v7r0m3/topic/com.ibm.nz.dbu.doc/r_dbuser_data_types_ntz_internal.html

And again, a changed createxid value would not necessarily mean that any column values have changed.


Database size limit in Sybase Express edition I'm currently testing out the Sybase 16.0 Developer edition on RHEL. Turns out the limitation on the max. concurrent connections is a real problem for me, so I'd like to switch to the Express edition.

My main concerns are the following:

The documentation mentions a limitation of 5 GB as max. DB size. Does this mean I can have a 20 GB device with 4 x 5 GB databases inside or that an entire server in Express edition won't go past 5 GB total?
Will it be easy to migrate devices created in Developer edition into Express edition?

For anyone coming across this Q&A ... the maximums for the ASE Express Edition were bumped up in early 2017.

Of interest:

max memory: unlimited
max db size: 50GB (this is actually the max size of all db devices combined)
max number of engines: 4

See ASE version comparison matrix for more details.


c# Backup sql server database, error Operating system error 5(Access is denied.) I have been trying to backup an sql server database, and I understand that this error is in regards to drive write protection. I am at the end of my rope with this, as nothing that I have found online has helped with this at all.

I have set up permissions for what should be considered every imaginable user that the server may try to pose as, everyone, administrators, my user, mssqlserver, sqlservermsasuser$xxx$MSSQLSERVER, SQLServer2005SQLBrowserUser, all with permissions set to full control.

I've tried running visual studio as admin, i've tried running the built application as admin, I've changed the "log on as" for the mssqlserver service to "Local System Account", me, and admin.

Yet still am having the same error, Operating System error 5 "Access is denied".
The folder is not read only, and is not in the root of the drive.
Any ideas??

Thanks in advance

You can find the account that your SQL service runs as through the services dialog on the server. That's the account that needs write permission to the directory that you've specified.


How to uninstall pgAdmin4 on Ubuntu 16.04? I successfully installed pgAdmin4 with

pip install pgadmin4-2.0-py2.py3-none-any.whl

But afterward, pip list doesn't show anything related to pgAdmin, so I don't know what argument to pass to pip uninstall <package_name>.

If i do pip uninstall pgadmin4, i get:

  Cannot uninstall requirement pgadmin4, not installed

Should I just delete the virtual environment directory where I installed pgAdmin?

sudo apt autoremove pgadmin4

works for ubuntu version 18.04


What can be best database schema for multiple project and one database? I am working on multiple projects but the back-end will remain the same. I have a different server for auth, data, etc. and one database to store all the details.

If one projects are creating any data then only that project can access that data and others project shouldn't access that.
For example, if users saving some data in the application from one app that only that app can access that.

I am trying to find the Schema for this database. I am using MySQL 5.7 database.

I am not looking for the multitenant approach of storing the data.

I am looking to store the data in JSON datatype.

Thanks for helping me.

You are talking about Multitenancy, or multiple separate entities sharing code, but having its own dataset. While there are many ways to approach this, the most simple way to approach this is by creating multiple databases (schemas), where each project only has access to its own, separate database.

If the amount of data that each user creates is very small, it can make sense to use a single table or set of tables, where each one is prefixed by a user id, or user field, so the applications controls you can only write and read your own rows. That assumes that you ha full control the application.

If the application is on the user's side, you shouldn't solve this on MySQL only- you need a backend application communicating to the user's frontend application, and being between the database and the frontend application for access control (implementing authorization and authentication).


Product of two querysets with a parameter in where clause of second result So I have a query that returns a row of some columns.

SELECT a.id, related_a_id FROM tableA

And I have an aggregated query which'd be using each of the rows.

SELECT tc.c_field1 AS cf1, SUM(td.c_field3) AS cf3 FROM tableA ta
   INNER JOIN tableC tc ON ta.id = tc.a_id
   INNER JOIN tableD td ON tc.d_id = td.id
WHERE ta.id IN (a.id, related_a_id)
GROUP BY cf1

I want to get all the rows for the second query for each pair from the first query with a resulting queryset columns including a_id, related_a_id, cf1, cf3.

Sample data and my attempts are available at this sql-fiddle.

Here is the query that can be used to get desired result

select ta.id,ta.related_a_id,tc.c_field1 as cf1,sum(td.c_field3) as cf3
from tableA ta
inner join tablec tc on tc.a_id in (ta.id,ta.related_a_id)
inner join tabled td on td.id = tc.d_id
group by cf1, ta.id order by ta.id;


Can't alter default privileges I followed the steps below.

I installed PostgreSQL.
I logged in as postgres via (sudo -i -u postgres)
I created new user new_user via shell script createuser --interactive
I granted that user permission to create databases.
I created database: createdb new_db.

Now I want to give new_user privileges be able to perform all default DML operations on tables created by himself (command bellow taken from this question), so I typed:

postgres=# ALTER DEFAULT PRIVILEGES FOR new_user IN SCHEMA
    public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO new_user;

But got an error:

ERROR:  syntax error at or near "new_user"
LINE 1: ALTER DEFAULT PRIVILEGES FOR new_user IN SCHEMA public GRANT...

I have no idea why this command is not working.

A key word is missing. It must be:

ALTER DEFAULT PRIVILEGES FOR ROLE new_user IN SCHEMA public GRANT...

The documentation:

ALTER DEFAULT PRIVILEGES
    [ FOR { ROLE | USER } target_role [, ...] ]

One of both ({ ROLE | USER }) is required.

Note that the particular command does not make any sense to begin with because (per documentation):

  There is no need to grant privileges to the owner of an object
  (usually the user that created it), as the owner has all privileges by default.

If you want that to grant permissions for objects that a different user created, like postgres, then use:

And you probably wouldn't want to restrict that to a particular schema.

ALTER DEFAULT PRIVILEGES FOR ROLE postgres
GRANT ALL ON TABLES TO new_user;

And you typically want to grant privileges on sequences as well

ALTER DEFAULT PRIVILEGES FOR ROLE postgres
GRANT ALL ON SEQUENCES TO new_user;


In Oracle, will certain users only be able to see certain data in the privileges tables? I can view data in the table_privileges view logged in as a particular user, but it seems to be missing or different information when logged in as a different user.

For the privileges views (data created when calling GRANT), will users see the data differently in these views? I seem to recall reading that this was the case, but can't confirm now.

Any specific links would be greatly appreciated.

The table_privileges view is only preserved for compatibility with ancient versions of Oracle.

You should instead be using the user_tab_privs view, all_tab_privs view, or dba_tab_privs view (documentation links for each when you click).