Can't drop UNDO TABLESPACE We have Oracle 11gR1 RAC two instances on two different servers, new undo tablespace UNDOTBS20140508 created 30G, I want to remove the old one UNDOTBSX02, I did the following:

CREATE UNDO TABLESPACE UNDOTBS20140508  DATAFILE '+DATA/....../UNDOTBS20140508' SIZE 20G;
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS20140508;
DROP TABLESPACE UNDOTBSX02 INCLUDING CONTENTS AND DATAFILES;

When trying to drop the UNDOTBSX02 it show the following error:

Error starting at line : 13 in command -
DROP TABLESPACE UNDOTBSX02 INCLUDING CONTENTS AND DATAFILES
Error report -
SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 57
ORA-30013: undo tablespace 'UNDOTBSX02' is currently in use
00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
           can be corrected, do so; otherwise contact Oracle Support.

I checked UNDOTBSX02 for any pending transaction and it is empty.
So how to drop it?

Note : I am not Oracle DBA, but I have to do this task.

I tried the above suggestions, but did not work, such as select SQL did not get results and I still cannot drop the undo table space.  The fundamental issue is that the old undo table space is still registered in Oracle as its undo, therefore we need to change it to the new undo table space.

Here are what I did and it worked:

create a new UNDO table space:

CREATE SMALLFILE UNDO TABLESPACE "UNDO"
DATAFILE '+DATA/t2/datafile/undo_01.dbf' SIZE 10G
REUSE AUTOEXTEND ON NEXT 500M MAXSIZE 10G;

Switch the new UNDO to the "official" undo table space:

alter system set undo_tablespace='UNDO' scope=both;

drop the old undo table space:

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

hope this helps.


SQL update set column = @ parameter doeas anyone knows how to update a table where column = @parameter WHERE SELECT

Basically I have a table with the following values. Table name is UserAttribute

ID|Name       |Properties|
17|Description|Student   |
17|Contact    |JohnDoe   |
18|Description|Worker    |
18|Contact    |MaryJane  |

I can update the fields with the following query:

UPDATE UserAttribute
SET Properties = 'Charlie'
WHERE Name = 'Contact' AND ID = 17

For the results I need I would need a query something like:

UPDATE UserAttribute
SET Properties = @parameter
WHERE Name = Contact AND ID = (SELECT HouseNo,ID FROM Table2 t1
JOIN UserAttribute t2 ON t1.ID =t2.UserID
WHERE HouseNo = @HouseNo)

SQL doesnt allows the execution of such a query. Any suggestion on how to write the select statement in the WHERE clause?

Thank you!

EDIT 1:
Maybe the question is not the clearest, but its a bit hard to formulate a question when the problem I have is also hard to understand. Therefore I will try to write some kind of pseudo-code, hoping that it will make it a bit more clear.

UPDATE table
SET Column = 'value'
WHERE Column2 = value AND column 3 =(SELECT column from other tables)

You're close, but when you introduce two tables, you have to be consistent with the alias.  So
at the declaration of the update statement, you'd want to start with:

UPDATE ua1
SET properties = 'Charlie'

Then create a from clause just as you would with a select statement, declaring your aliases:

FROM UserAttribute ua1
    INNER JOIN UserAttribute ua2
        ON ua2.UserID = ua1.id
WHERE ua1.name = 'Contact';


how do I prevent my postgres connection from timing out? (python, psycopg2) very quick one from me: I have a python script that runs continuously, keeping an eye on a postgres database. I've set up a listening channel and everytime a relevant change is made, the script gets a notification and does something accordingly.
the problem I'm having is that sometimes there are hours between notifications and my connection simply "drops" after a while. that means even though a change is taking place, the connection is not active anymore, hence python won't get a notification and everything technically stops.
is there a parameters that can be used to prevent that from happening? when I was using php/mysql, there was a persistent connection option. does postgres have something similar? this is what I currently use to connect:
try:
    conn = psycopg2.connect('postgresql://username:password@localhost:5432')
    conn.set_session(autocommit = True)
    cursor = conn.cursor(cursor_factory = psycopg2.extras.RealDictCursor)
    print('Database connection OK\n')
except:
    print('Database connection error\n')
    sys.exit()

PostgreSQL doesn't drop idle connections. It must be something else, probably a misconfigured firewall.
To defend against that, set the parameter tcp_keepalives_idle on the server to something less than the default 2 hours.
There is also a corresponding parameter keepalives_idle on the client side that you could set when you connect with psycopg2.
Both should do the trick.


How do you see which database on a Server uses most resources I have a database server with a couple of databases on it. How can I see where any resource pressure may come from?

I would like to get a table of:

Database Name
Batch Requests per second
CPU Time
Logical Read
Logical Writes

Only some resources can be tracked to databases. IO and buffer pool usage can clearly be tracked to a database:

IO usage: sys.dm_io_virtual_file_stats
Buffer pool usage: sys.dm_os_buffer_descriptors

The query execution statistics (like logical reads) can be attributed to queries, but cannot be associated (correctly) with a database because queries can (and often do) span databases. sys.dm_exec_query_stats is the main entry point for these stats.


Inserting in a table after crossing the allocated tablespace space What will happen when I try to insert so many number of rows beyond the allocated tablespace size.
Say in Oracle DB, the initial tablespace size is set to 500MB and when it reached the limit and we try to create more records into it, should it fail or it will acquire more space from the hard disk?

Oracle will throw an error and rollback the query.

  ORA-01653: unable to extend table by in tablespace


Mirroring the Distributor Has anyone had success with mirroring the distribution database? We have a dedicated server as a Distributor. It handles all our push replication from prod to reporting. We'd like to build an identical server, close by, in case the distributor crashes. Has anyone had success building something like this?

Sadly mirroring of the distributor isn't supported.  I'm not sure if it would work as the server names wouldn't be correct when the agents would log in, not to mention you'd have to manually setup all the agents on the backup box.  Currently your only HA option for the distributor is Windows Clustering.


Beginner SQL: Unexpected results from GROUP BY I have a sidewalk_deficiency table:

create table sidewalk_deficiency (
    def_id number(10),
    insp_id number(10),
    def_length number(10,2));
insert into sidewalk_deficiency values (10,1,.5);
insert into sidewalk_deficiency values (11,1,1);
insert into sidewalk_deficiency values (12,1,1.5);
insert into sidewalk_deficiency values (13,2,2);
insert into sidewalk_deficiency values (14,2,2.5);
commit;

    DEF_ID    INSP_ID  DEF_LENGTH
---------- ----------  ----------
        10          1          .5
        11          1           1
        12          1         1.5
        13          2           2
        14          2         2.5

I can create a query that summarizes the sidewalk inspections by tallying up the deficiency lengths, per inspection:

select
    insp_id,
    sum(def_length) as def_length
from
    sidewalk_deficiency
group by
    insp_id

   INSP_ID DEF_LENGTH
---------- ----------
         1          3
         2        4.5

This works as I had expected. Two rows are output; one for each insp_id.

I understand that the above query does, in fact, provide the desired output. However, I don't really understand why it works the way it does.

Admittedly, when I write queries like this, it makes more sense to me to include  fields such as def_length in both the aggregate function and the group by clause. To me, I know I want to collapse the def_length into a sum, so it seems to me that I should group by it.

select
    insp_id,
    sum(def_length) as def_length
from
    sidewalk_deficiency
group by
    insp_id,
    def_length       --<<--Problem

   INSP_ID DEF_LENGTH
---------- ----------
         2        2.5
         1         .5
         1          1
         2          2
         1        1.5

Of course, as we can see, my thought process is flawed.  When I include the def_length field in the group by, it produces more records than I had expected.

Forgive my ignorance, but why does the second query produce more results than the first? I would have thought that including the field in both the aggregate function and the group by would have grouped the rows as I had planned.

This answer is a bit unlike the others, but I understood your question to be more related to "what is the logic behind adding/removing columns to/from the SELECT and GROUP BY".  If it's out of place, please let me know and I'll delete.

It's not really a quirk, it's just a basic rule of aggregation.
If I'm counting people that live at my house (3 total), I count people that live at my address.  So I can say

SELECT Address
      ,COUNT(PersonID) AS PeopleCount
FROM Household
GROUP BY Address

That will give me the number of people at my address.

If I try to do it by name:

SELECT Address
       Name
      ,COUNT(PersonID) AS PeopleCount
FROM Household
GROUP BY Address
        ,Name

Then it will give me a "1" value in People count as there is only 1 person per address per name in my household.

If I had 2 people with the same name living at my house, then the "PeopleCount" value for those two people would be "2" until I found something else to add to make the records unique.

So the reason you add items to the SELECT/GROUP BY is to perform aggregates based on UNIQUE records.  The more columns you add to the SELECT statement, the more unique the records are.


What is the default order of records for a SELECT statement in MySQL? Suppose you have the following table and data:

create table t (
    k int,
    v int,
    index k(k)
    ) engine=memory;

insert into t (k, v)
values (10, 1),
       (10, 2),
       (10, 3);

When issuing select * from t where k = 10 with no order by clause, how does MySQL sort the records by default?

Reposting my answer to a similar question regarding SQL Server:

  In the SQL world, order is not an inherent property of a set of data.
  Thus, you get no guarantees from your RDBMS that your data will come
  back in a certain order -- or even in a consistent order -- unless you
  query your data with an ORDER BY clause.

So, to answer your question:

MySQL sorts the records however it wants without any guarantee of consistency.
If you intend to rely on this order for anything, you must specify your desired order using ORDER BY. To do anything else is to set yourself up for unwelcome surprises.

This is a property of all SQL, not just MySQL. The relevant text in the SQL-92 spec is:

  If an <order by clause> is not specified, then the ordering of the rows of Q is implementation-dependent.

There are similar bits of text in the spec for cursors.


Mysql Query Take Longer To Prcoess On My Dedicated Server Than My Local Server One of my MySQL queries takes a very long time to process on my live server compared with my development server. My live server (Windows dedicated server) is more powerful than my local (development) server. Both databases, tables, engine, columns, indexes all are same/identical on both servers.

On my development server (local) the query takes only milliseconds (less than a second) where on my dedicated server it takes 4-5 seconds.

On my dedicated Server I noticed in Task Manger that msqld is running by a command line which defined my.ini file:

And this is the my.ini file contents on that location:

Is there anything in my.ini that I can change to boost the speed?

Here are the outputs from explain as requested:

Dedicated server

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  PSCThana    ALL ThanaID,UpazillaID  NULL    NULL    NULL    509 Using where
1   SIMPLE  PSC15ALL    ref STD_ROLL,THANA  THANA   18  siamit.PSCThana.ThanaID 2648    Using where

Local Server

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  PSCThana    ALL ThanaID,UpazillaID  NULL    NULL    NULL    509 Using where
1   SIMPLE  PSC15ALL    ref STD_ROLL,THANA  THANA   18  siamit.PSCThana.ThanaID 2837    Using where

There are other things that can come into play when dealing with a production vs a development environment such as fragmentation/load/locks/amount of data/etc. I would first start by comparing the plans in mySQL using EXPLAIN. This will give you more information as to what the problem is.


Should I put followers_no & following_no & product_no & last_login and etc in the user table? Won't updates cause fragmentation on the table? I have a table for users in a social networking site. I want it to be efficient from ground up. If I put mentioned fields on the table then I have to update user's table on each changes. Should I have another table for fields like these that change frequently? Should I be concerned about fragmentation?

Is there any better approach that I'm unaware of?

It most certainly will cause fragmentation, but you must compare it with normalization.

FRAGMENTATION

Fragmentation is introduced in a heavy-write environment.

DELETEs automatically create empty space by at least the size of the row at the time of deletion.
UPDATEs can also cause fragmentation, mostly notably on variable-length data.

Further Links on InnoDB Fragmentation and how to Eliminate it from InnoDB data and system tablespace

http://www.bluegecko.net/mysql/innodb-tablespace-fragmentation-find-it-and-fix-it/
How do you remove fragmentation from InnoDB tables? (April 11, 2012)
Howto: Clean a mysql InnoDB storage engine? (My StackOverflow Post From Oct 29, 2010)

NORMALIZATION

Any columns you have in a user table that are immutable (i.e., will never experience changes) should act as the main table. Things such as

Address Change
Spelling Correction
Last Name Change (in case a lady get married)

will produce very little fragmentation since changes of those kinds are rare.

Any information that logs frequent changes to user information should go into a userinfo table. This will separate fragmentation issues from the immutable user data. You can easily defrag an InnoDB userinfo table with one of the following

ALTER TABLE userinfo ENGINE=InnoDB;
OPTIMIZE TABLE userinfo;

CONCLUSION

You should split up the user data into user and userinfo tables. A simple INNER JOIN will combine them as needed.


Inserting result of stored procedure into a table with additional column I've found hundreds of articles about how to insert output of stored procedure into a table but nobody goes a step further and explains how to insert additional data as column.

I'm looping through a table and execute store procedure which I insert details into tempTable however I need to add additional column with an ID from second table. I could change stored procedure to return that additional ID however I need to run it on 20+ databases on different servers which would be time consuming.

DECLARE @c_id_view varchar(255)
DECLARE @c_id int

DECLARE cur CURSOR FOR SELECT c_id FROM #temp where ins != 'Health'
OPEN cur

FETCH NEXT FROM cur INTO @c_id_view

WHILE @@FETCH_STATUS = 0 BEGIN
set @c_id = (select c_id from claim where c_id_view = @c_id_view)
insert into #tempbedsNONIL (c_id_view,room_Type,ward_name,common_bed_name,bed_name,no_of_bed,no_of_days) values (@c_id_view, usp_patient_transfer_retrieve_forPDF @c_id)  -- call your sp here
FETCH NEXT FROM cur INTO @c_id_view

END

CLOSE cur
DEALLOCATE cur

I know that to insert SP results into a table I just need to do INSERT INTO #TEMP EXEC sp_test but in my case I need to enter extra value. This might be a stupid question but I can't figure out how to achieve it.

A possible option is to use the technique from this post.

Basically, you declare a table variable to hold the results of the stored procedure and then use an additional insert statement to add the extra column.

In the following example, 3 columns are returned from the sp_readerrorlog stored procedure and the additional insert adds servername.

Just make sure you truncate the table variable before each invocation of the stored procedure within your loop.

DECLARE @readerrorlog_results TABLE (
  LogDate date,
  ProcessInfo varchar(max),
  Text varchar(max)
)

INSERT INTO @readerrorlog_results
      (LogDate,ProcessInfo,Text)
EXEC sp_readerrorlog 0, 1, 'Login failed'

INSERT INTO FailedLogins
      (LogDate,  ServerName,ProcessInfo,Text)
SELECT LogDate,@@SERVERNAME,ProcessInfo,Text FROM @readerrorlog_results


Using LIKE with apostrophes I have been running a server side trace in order to find out how a specific stored procedure is called within my database.

I am running the following query in order to get data from the trace file generated:

select TextData,
db_name(databaseid)
from fn_trace_gettable( 'R:\Profiler\udpProductTaxRateGet4.trc', default) fn
join sys.trace_events te
 on fn.EventClass = te.trace_event_id
where --
1=1
--and EventClass = 42
and textdata like '%udpProductBulletPointSelectByTier1NoteTypeCode%'
--and db_name(databaseid) = 'US16AUTPProduct'
order by starttime desc

But as you can see on the picture below, there are too many rows like this one:

declare @p5 int  set @p5=86400 exec dbo.udpProductBulletPointSelectByTier1NoteTypeCode @Tier1=N'AR763',@LanguageID=2,@SeasonItemID=N'16AUT',@ListNoteTypeCode=N'',@CacheDuration=@p5 output  select @p5

more specifically the procedure was called with an empty parameter value for @ListNoveTypeCode

  @ListNoteTypeCode=N''

How can I filter out all those rows where @ListNoteTypeCode=N''??

The single quote character has no special significance to LIKE

You need to double them up to escape them inside any string literal.

So the two consecutive single quotes in the string you are trying not to match become four as below.

And textdata not like '%@ListNoteTypeCode=N''''%'


MySQL InnoDB Reset Existing Auto-Increment Records Using MySQL Workbench, I have a MySQL InnoDB with a few hundred records.

The records table has the following format (id is auto_increment and unique);

+------+-------+
| id   | name  |
+------+-------+
| 2500 | jo    |
+------+-------+
| 2511 | jim   |
+------+-------+
| 2512 | sarah |
+------+-------+
| 2513 | jane  |
+------+-------+

Notice my first record begins at 2500. Presumably this is because I have dropped and re-imported the table data numerous times.

Is there any way I can 'reset' the id field so that the records begin at 1 and not 2500?

I want the table to look like this:

+----+-------+
| id | name  |
+----+-------+
| 1  | jo    |
+----+-------+
| 2  | jim   |
+----+-------+
| 3  | sarah |
+----+-------+
| 4  | jane  |
+----+-------+

Any advice is appreciated.

TRUNCATE TABLE tbl;

If that does not work,

ALTER TABLE tbl AUTO_INCREMENT=1;

But you should not depend on the actual values of AUTO_INCREMENT.  There are many things that can make it non-reproducible.


What are the real life uses of role based authentication control in a database? I am new to database management. I was going through the mongodb docs for Role Based Authentication Control. I have understood how it works, but I have a few questions.

How is it applied in real life scenarios? For example let's say I am running a blogging web app. How would I use Role Based Authentication Control to improve my database management?

You can group several privileges into one Role. It is simpler to grant roles rather than dealing with a bunch of privileges.

In a multi-tier architecture this might be no so important. You may have a user admin, backup, http - that's it.

However, when your database is used by many (human) users distributed over several departments with different responsibilities then Roles make your life much easier.

You create (or use built-in) roles like backup, clusterAdmin, developer, reporter, powerUser, etc. and grant such roles to users. You would have serious security flaws if every user would get full admin privileges on entire database.


Why does SQL Server start REDO phase from minLSN instead of last CHECKPOINT? while reading an article from BOL, I stuck on the picture which explains the recovery process of database (without ADR):

Shouldn't Phase 2:Redo start from Last CHECKPOINT as till that point everything is already in data file? Why does it starts from minLSN?

The Database Checkpoints doc says that

  For performance reasons, the Database Engine performs modifications to
  database pages in memory-in the buffer cache-and does not write these
  pages to disk after every change. Rather, the Database Engine
  periodically issues a checkpoint on each database. A checkpoint writes
  the current in-memory modified pages (known as dirty pages) and
  transaction log information from memory to disk and, also records the
  information in the transaction log.

So the CHECKPOINT is not directly related to transactions, instead it's related to dirty pages which might not belong to the same transaction and that explains why the recovery process of a database must consider the MinLSN instead of the last CHECKPOINT as you suggested.

If you take a look at the structure of the Transaction Log Physical Architecture, you'll see that a checkpoint can happen in a VLF that is in front of the oldest active VLF as the following picture ilustrates:

With that in mind, think that a really long transaction (T1) started using Virtual Log 3 and only completed by the end of Virtual Log 4. During the execution of T1 another transaction happened (T2), but this one started and completed after the CHECKPOINT. Now imagine a crash happened. If the recovery process would consider as starting point the CHECKPOINT, it would be able to redo T2, but not to grant a consistent state to the recovered database for the crash of the database happened after the CHECKPOINT but before T1 had completed, leaving half a transaction registered when it should be rolled back.

You can also see that example on the picture in your question where the Oldest uncommitted Tx started before the Last Checkpoint.


MySQL having variable in view I have a query that I want to save as a view because it will be used on a different system that we don't have access to. The query basically gets running balances on debit and credit columns from a table.

The query works fine,here is the query

SELECT id,deb_id,trans_date,trans_ref,dr,cr,
    @bal := @bal + (dr -cr) AS `Balance`
FROM debtor_transactions a , (SELECT @bal := 0) var
ORDER BY a.id ASC

But I cannot create a view from the above query, I keep getting this error.

Error Code: 1351
View's SELECT contains a variable or parameter

How can I get around this, or is there a way I can rewrite my query without the variable and still achieve the same result?


SQL Server:Buffer Manager Page reads-write/sec has exceeded the threshold Can anyone please explain me below situation we have and please suggest me solution for how to deal with Memory issues in SQL Server 2008R2 Version.

Installed RAM 128 GB

FYI... I have assigned close to 122 GB for SQL Server.

Still please clarify what this means :

SQL Server:Buffer Manager Page reads-write/sec has exceeded the threshold

I see PLE more than 300 which is good ?
Please clarify other Performance counters if the value is good or needs to be addressed.

SQL Server:Buffer Manager Page reads-write/sec has exceeded the threshold

This does not really indicates a problem. What it means is SQL Server has issues more number of physical read and writes than the threshold you have set. Now next question is how often do you get the alerts, if this is often you might need to find out whether you are facing memory crunch and what queries running are causing this.

  I see PLE more than 300 which is good ?

300 mark should be completely ignored unless you have system with 4 GB memory. The base PLE should be calculated like
(DataCacheSizeInGB/4GB *300). This should be your benchmark PLE anything much lower than that should bother you. Moreover if you have NUMA system you have to track PLE for each NUMA node

  Please clarify other Performance counters if the value is good or needs to be addressed.

The counters should not be taken for a moment collect it for period of 8 hours or so and please run it when load on system is on peak. You can use Performance data collector set


Bypassing Password Verify Function I am considering making a recommendation to enforce a password verify function that will not allow users to change their own passwords.  My understanding of the PASSWORD_VERIFY_FUNCTION in Oracle is that the function only restricts the password criteria when the user changes his or her own password.  It would not restrict a privileged user from changing someone else's password or a third party IAM software from changing the password, but I cannot find that expressly documented.  Am I correct?

The PASSWORD_VERIFY_FUNCTION is used, even when a privileged user changes another user's password, see below.

SQL> @?/rdbms/admin/utlpwdmg.sql
SQL> create user u1 identified by Oracle123#;

User created.

SQL> select profile from dba_users where username = 'U1';

PROFILE
------------------------------
DEFAULT

SQL> select limit from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_VERIFY_FUNCTION';

LIMIT
----------------------------------------
VERIFY_FUNCTION_11G

SQL> show user
USER is "SYS"

SQL> alter user u1 identified by 1;
alter user u1 identified by 1
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20001: Password length less than 8


Does encapsulating a statement in a transaction include the triggers it fires? I have an INSERT that will fire a TRIGGER which will UPDATE an attribute on another TABLE. I need to make sure that if the INSERT is successful and the UPDATE inside the TRIGGER isn't then the whole thing should be rolled back.

Does encapsulating the INSERT in a transaction guarantee this?

If not, how can I do this?

The effects of a trigger 'belong' to the statement that triggered it, so everything will be rolled back together (with or without explicit transactions):

> create table a(x int);
> create table b(y int check(y > 0));
> insert into b values(1);
> create function t() returns trigger as $$
  begin
    update b set y = -1;
    return null;
  end;
  $$ language plpgsql;
> create trigger at after insert on a execute procedure t();
> insert into a values(0);
ERROR:  new row for relation "b" violates check constraint "b_y_check"
DETAIL:  Failing row contains (-1).
CONTEXT:  SQL statement "update b set y = -1"
PL/pgSQL function t() line 1 at SQL statement
> select * from a;
 x
---
(0 rows)


MySQL timeout on INSERT lasts almost 60 seconds I have this table:

CREATE TABLE `ClusterDescription` (
  `ClusterDescriptionId` int(11) NOT NULL AUTO_INCREMENT,
  `ClusterId` int(11) NOT NULL,
  `RightBarDateTime` datetime NOT NULL,
  `InstrumentId` int(11) NOT NULL,
  `RowCreated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ClusterDescriptionId`),
  UNIQUE KEY `ClusterInstrumentId` (`ClusterId`,`InstrumentId`),
  KEY `InstrumentId` (`InstrumentId`) USING BTREE,
  KEY `OrderDesc` (`RightBarDateTime` DESC,`ClusterId` DESC)
) ENGINE=InnoDB AUTO_INCREMENT=3055989 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

The table has more than 3m rows.

I receive regularly this error:

  SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when
  trying to get lock; try restarting transaction

for this command:

INSERT INTO ClusterDescription (ClusterId, RightBarDateTime, InstrumentId)
 SELECT IFNULL(MAX(ClusterId) + 1, 1), :rightBarDateTime, :instrumentId
 FROM ClusterDescription WHERE InstrumentId = :instrumentId

There can be up to 50 above INSERT at almost the same time.
:instrumentId is never the same in any of the parallel commands.
Checking the process list I see lot of these commands waiting to be executed with a Time value > 30 seconds.
I set innodb_lock_wait_timeout to 360 seconds which improved the situation. Before I received lot of the above mentioned error and this error:

  SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try
  restarting transaction

My questions:

What is wrong with this table definition?
Why do need the parallel INSERT commands more than 50 seconds to be executed?
Why do I receive a dead lock error when only 1 table is affected?

Running on MySQL 8.0.13

I need the InnoDB engine because of the descending index.

Both of these improve the efficiency of the SELECT MAX... in the INSERT..SELECT:

Plan A

UNIQUE KEY `ClusterInstrumentId` (`ClusterId`,`InstrumentId`),
KEY `InstrumentId` (`InstrumentId`, ClusterId) USING BTREE,

This assumes you need to look up things by ClusterId.

Plan B

UNIQUE (`InstrumentId`, ClusterId)

This covers the Uniqueness constraint


Problem replicating IDENTITY columns from Sybase ASE 15.5 to Sybase ASE 15.5 I am trying to perform replication from Sybase ASE 15.5 to Sybase ASE 15.5 for a list of given tables via replication definitions and subscriptions defined in Replication Server(REP) Version 15.7.1. I found out that there is a table with one of the columns defined as IDENTITY. After creating the replication definition and subscription for that table, I am getting the below error messages in replication server:
Message from server: Message: 584, State 1, Severity 16 -- 'Explicit value specified for identity field in table 'customers' when 'SET IDENTITY_INSERT' is OFF'.
I have tried to run SET IDENTITY_INSERT customers ON on both of my primary and replicate database, but no luck.
Appreciate if you could help. Thanks!

Assuming your primary/replicate tables were create like such:

create table mytable
(id     int           identity
,name   varchar(100)
,dob    date
... snip ...

And assuming you want to replicate identity values from the primary table to the replicate table, you need to tell SRS that the id column is an identity column, and the way you do this is by designating the id column as an identity in the repdef, like such:

create replication definition mytable_repdef
with primary at ...
with all tables named mytable
(id     identity             <<<=== use 'identity' instead of the actual datatype
,name   varchar(100)
,dob    datetime
... snip ...

By designating the id column as an identity column in the repdef, SRS should now enclose all INSERTs (sent to the replicate database) in a set identity_insert on/off wrapper, and UPDATEs should be wrapped in a set identity_update on/off wrapper.


Repair going very slow We have a 32 node cluster with an average of approximately 150GB of data on each node. When running a full repair (not doing primary range) it is taking approximately 26 hours to complete. This seems very slow to complete.. any ideas on why this type of repair is going so slow? Are there any settings I may tweak to make it go faster?

Marshall

How to make repairs go faster:

Use the -pr flag.  This repairs only the primary range, which prevents the unnecessary repairs of the additional ranges.  If you run your repairs on all nodes (and you should) then you'll repair everything anyway, but without repairing everything an extra time for each replica.
Focus repairs only on the tables/keyspaces that need it.  Some tables may be static enough that you don't really need to repair them.  For instance, if you don't regularly add new users, you probably don't have to repair system_auth.  Nodetool repair allows you to specify keyspaces and tables to run a repair on, and you should definitely use it.
Avoid streaming repairs from different physical data centers.  This was a big "win" for us.  Nodetool repair allows you to specify a list of hosts to include in the repairs.  If you have multiple data centers, you should limit your node to only repair itself from nodes which are "local" to it.
Focus repairs only on specific token ranges.  You probably won't always have to do this, but sometimes you only have a window that allows you repair for so long.  To programmatically repair say only one third or quarter of your data, you can further focus your repair tasks by specifying certain token ranges.  I don't typically like to do this one, because repairing all data for a table can stretch out for a few days.  But if you have only a small window to run a repair, this might help.

tl;dr :

Don't repair the same data multiple times.
Repair only where you need to.
Stream repairs only from specific, local nodes.
Focus your repairs as much as you can.


MySQL get previous row with inner join In my query, I need to get the previous row with the current row and everything works fine here.

SELECT *,
       (SELECT calendar_id
       FROM   calendar AS sub
       WHERE  sub.calendar_id < calendar.calendar_id
       ORDER  BY sub.calendar_id DESC
       LIMIT  1) AS previous
FROM  calendar

But now i need to join more two tables i using inner join for what.

SELECT *,
       (SELECT calendar_id
       FROM   calendar AS sub
       WHERE  sub.calendar_id < calendar.calendar_id
       ORDER  BY sub.calendar_id DESC
       LIMIT  1) AS previous
FROM calendar
     INNER JOIN relationships
             ON calendar.relation_id = relationships.relation_id
     INNER JOIN customers
             ON customers.customer_id = relationships.customer_id
WHERE  relationships.user_id = '$user_id'
       AND Date_format(calendar.date, '%m-%Y') = '$date'
ORDER  BY calendar.date ASC

Here is my final query with inner joins, but that part about previous row now result is just mixed. How i can keep my previous row result good and have that data from other tables with inner join?

Use that query as a 'derived table' to finish the task:

SELECT ...
    FROM ( ... your query ... ) AS x
    JOIN calendar AS prev_row  ON prev_row.calendar_id = x.previous
    ...

(If you are using MySQL 8.0, there is a "windowing" way to get the previous id.)


Getting an aggregate (here: min) for a group of values Example table:

ID   | value A | value B
----------------------
1    |    abc  |   3
1    |    def  |   5
1    |    ghi  |   1
2    |    cba  |   9
2    |    fed  |   4

I want the rows from within any 'ID-group' that has it's minimum within that group in value B.

Wanted result:

ID | value A | value B
----------------------
1  |    ghi  |   1
2  |    fed  |   4

Anything I tried with group by always fails because of that 'value B'-column and its varying values because I do not want to / cannot aggregate them.

Postgres has distinct on that does just what you are looking for:

select distinct on (id) id, value_a, value_b
from t
order by id, value_b;

SQLFiddle here

You haven't specified what you would like to see when the are multiple values of value_b for an id though.


Why does SQL Server Management Studio hang (Not Responding) when connecting to Amazon RDS? When I use SSMS to connect to a database hosted on Amazon RDS, the IDE hangs (as in goes to "Not Responding) after login. I enter SQL Authentication credentials and click Connect and the login window closes as it would for successful authentication. From there I get the "wait" cursor spinning indefinitely and the window status eventually changes to "Not Responding." The only thing I can do from there is force the application to close.

I have checked logs through Event Viewer and there are no entries for the 5 minutes before or after the error.
I can connect to local SQL Server databases with no errors or problems
I have been able to connect to this database within the past month through SSMS using the same credentials
I can connect to this database now using Visual Studio's IDE using the same credentials
SSMS version is 11.0.3128.0

Sounds like the connection is successful but SSMS is having trouble loading metadata to populate Object Explorer. I would try opening SSMS without connecting to this server, open an empty query window, disable IntelliSense, and then change that query window's connection.

Also your copy of SSMS is quite old, I would strongly consider applying SP3 + CU1 to your workstation, or moving up to 2014 SP1 or even the new monthly previews. I don't know what build RDS is on, but you should always strive to have SSMS >= the highest version of server you have to manage.


Unexpected HY001: Memory allocation error after MySQL upgrade Earlier today I upgraded a db.t3.large MySQL instance on AWS from v8.0.17 to 8.0.20.  I've seen a handful of cases where I'm seeing the error SQLSTATE[HY001]: Memory allocation error: 1038 Out of sort memory, consider increasing server sort buffer size.  I don't expect such an error because the config or instance type didn't change.  An example of where I'm seeing this is on the following query:
SELECT * FROM `messages`
WHERE
    EXISTS (
        SELECT * FROM `updates`
        WHERE
            `messages`.`update_id` = `updates`.`id`
            AND `game_id` = 18
    )
    AND `messages`.`deleted_at` IS NULL
ORDER BY `id` DESC
LIMIT 6

In this query, all columns used in the order and where clauses are either foreign or primary keys with messages.deleted_at being the only exception.
In the meantime I'm going to adjust this setting in my prod environment to increase the size of the sort_buffer_size, but this change is not recommend according to a lot of stuff I'm reading so I hope it'll temporarily resolve the issue.

And you have large JSON or Geometry columns?  (Always provide SHOW CREATE TABLE when asking a question.)
Either increase or decrease sort_buffer_size and innodb_sort_buffer_size.
https://bugs.mysql.com/bug.php?id=103465
and
https://bugs.mysql.com/bug.php?id=103225


How to set space usage alert for database using SQL Server 2012 with Amazon RDS as managed service? I have a database whose initial size is set to 1000 GB.
Is there a way where i can set an alert when the max space is used and database size is nearing to 1000 GB, for eg when it reaches maybe 995 or 994 GB , so that we get to know about it and take actions for increasing the disk space before hand. without sudden downtime.

We are using SQL Server 2012 with Amazon RDS as managed service.

Thanks in advance.

You could create a SQL Server Agent Job which will be run daily and check the free space by using a query like below:

USE [YourDatabaseName]

/* Size in MB */
DECLARE @Info TABLE (
    [Size]          decimal(12,2),
    [SpaceUsed]     decimal(12,2),
    [SpaceFree]     decimal(12,2)
)

insert @Info ([Size], [SpaceUsed], [SpaceFree])
SELECT
    [Size] = ISNULL(CONVERT(decimal(12,2),round(f.size/128.000,2)),0)
    ,[SpaceUsed] = ISNULL(CONVERT(decimal(12,2),round(fileproperty(f.name,'SpaceUsed')/128.000,2)),0)
    ,[SpaceFree] = ISNULL(CONVERT(decimal(12,2),round((f.size-fileproperty(f.name,'SpaceUsed'))/128.000,2)),0)
    --,GETDATE() as DT
FROM sys.database_files f
    CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.file_id) vs
WHERE f.name = 'YourFileLogicalName'

/* Free space less than  5GB */
If (SELECT TOP 1 [SpaceFree] FROM @Info) < 5120 BEGIN
    /*
        Anything is suitable for you:
        - send email
        - raiserror
        - write log
        .......

    */
    EXECUTE dbo.KickDBAToAwakeHim
END

Maybe, it has sense to create some table and write the values there every day to have information about how the space is changed.


"no join predicate" warning with outer join I have a database schema that looks something like this:

create table t_order (orderid int, customerid int)
create unique clustered index ix_order on t_order(orderid)
create index ix_order_1 on t_order(customerid)

create table t_product (productid int)
create unique clustered index ix_product on t_product(productid)

create table t_orderItem (itemid int, orderid int, productid int)
create unique clustered index ix_orderItem on t_orderItem(itemid)
create index ix_orderItem_1 on t_orderItem(orderId, productId)
create index ix_orderItem_2 on t_orderItem(productId, orderId)

(i.e. the orderItem table is a many-to-many between orders and products). Now I have an SQL statement like this:

SELECT
    t_product.productid,
    t_order.orderid,
    t_orderItem.itemid
FROM
    t_product
    JOIN t_order ON t_order.customerid = 101
    LEFT OUTER JOIN t_orderItem ON t_product.productid = t_orderItem.productid AND t_order.orderid=t_orderItem.orderid

when there are a lot of rows in the database (and with statistics updated), I get the "no join predicate" warning on the left outer join, and the query takes a long time to execute.

Unfortunately it's not code I wrote myself and I don't really understand what that outer join is trying to do. I quite expect the SQL is "wrong" (i.e. doesn't do what the author intended) but I can't fix it unless I can get a handle on what it's actually doing at the moment.

I have seen other examples on the net of using AND inside the ON clause, but not involving other tables that aren't part of the join.

Can someone explain what the effect of this join is and why it causes a Cartesian product to be generated.

Not a complete answer but the joins in the FROM clause are equivalent to a CROSS JOIN and then a LEFT JOIN to the 3rd table. This rewrite may help you understand better what the query is doing:

FROM
        t_product
    CROSS JOIN
        (SELECT * FROM t_order WHERE customerid = 101) AS t_order

    LEFT OUTER JOIN
        t_orderItem
    ON  t_product . productid = t_orderItem . productid
    AND t_order   . orderid   = t_orderItem . orderid

It will basically return all combinations of all the orders of a specific customer and all products (and whether each product was an item of each order).


Improving slow queries. Slow query log attached Below is the Slow Query Log when my .NET application was running. Please let me know how to improve the performance of the queries:

TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument
# Time: 110805 13:25:39
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.015625  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1373
use stockist;
SET timestamp=1312530939;
SELECT SUM(GROSSAMOUNT) FROM BILLDETAILS WHERE MONTH(BILLDATE) = 8 AND YEAR(BILLDATE) = 2011;
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.000000  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1373
SET timestamp=1312530939;
SELECT COUNT(BILLNO) FROM BILLDETAILS WHERE MONTH(BILLDATE) = 8 AND YEAR(BILLDATE) = 2011;
# Time: 110805 13:30:32
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.078125  Lock_time: 0.046875 Rows_sent: 1  Rows_examined: 1374
SET timestamp=1312531232;
SELECT A.BILLNO AS BILL_NO, DATE_FORMAT(A.BILLDATE,'%d/%m/%Y') AS BILL_DATE, B.PARTYNAME AS PARTY_NAME, A.NETAMOUNT AS NET_AMOUNT FROM BILLDETAILS A, PARTYMASTER B WHERE A.PARTYID = B.PARTYID AND MONTH(A.BILLDATE) = 8 AND YEAR(A.BILLDATE) = 2011 ORDER BY A.BILLNO, A.BILLDATE;
# Time: 110805 13:30:44
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.296875  Lock_time: 0.031250 Rows_sent: 407  Rows_examined: 19552
SET timestamp=1312531244;
select a.itemcode as Item_Code, a.itemname as Item_Name, a.stockinhand as Stock_In_Hand, b.mrp As MRP, round((a.stockinhand * b.mrp),2) As Value, date_format(b.invoicedate,'%d/%m/%Y') As Stock_Date from itemmaster a, stockentry b where a.itemid = b.itemid and invoicedate = (select max(invoicedate) from stockentry where itemid = b.itemid) group by a.itemname order by a.itemname;
# Time: 110805 13:30:55
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.000000  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1312531255;
SELECT * FROM REMARKSETTINGS;
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.000000  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1312531255;
SELECT * FROM BILLINGSETTINGS;
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.015625  Lock_time: 0.015625 Rows_sent: 1  Rows_examined: 1373
SET timestamp=1312531255;
SELECT MAX(BILLNO) FROM BILLDETAILS WHERE YEAR(BILLDATE) = 2011;
# Time: 110805 13:30:58
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.031250  Lock_time: 0.000000 Rows_sent: 184  Rows_examined: 3266
SET timestamp=1312531258;
SELECT B.RECORDID, A.ITEMCODE, A.ITEMNAME, A.STOCKINHAND, B.SALEPRICE FROM ITEMMASTER A, STOCKENTRY B WHERE A.ITEMID = B.ITEMID AND RECORDID = (SELECT MAX(RECORDID) FROM STOCKENTRY WHERE ITEMID = A.ITEMID) AND A.STOCKINHAND > 0 AND B.SALEPRICE > 0 AND B.INVOICEDATE IS NOT NULL ORDER BY A.ITEMNAME, B.INVOICEDATE;
# Time: 110805 13:31:21
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.031250  Lock_time: 0.000000 Rows_sent: 184  Rows_examined: 2898
SET timestamp=1312531281;
SELECT B.RECORDID, A.ITEMCODE, A.ITEMNAME, A.STOCKINHAND, B.SALEPRICE FROM ITEMMASTER A, STOCKENTRY B WHERE A.ITEMID = B.ITEMID AND RECORDID = (SELECT MAX(RECORDID) FROM STOCKENTRY WHERE ITEMID = A.ITEMID) AND A.STOCKINHAND > 0 AND B.SALEPRICE > 0 AND B.INVOICEDATE IS NOT NULL AND A.ITEMNAME LIKE '%';
# Time: 110805 13:36:31
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.250000  Lock_time: 0.000000 Rows_sent: 407  Rows_examined: 20367
SET timestamp=1312531591;
select c.categoryname, a.itemcode as Item_Code, a.itemname as Item_Name, a.stockinhand as Stock_In_Hand, b.mrp As MRP, round((a.stockinhand * b.mrp),2) As Value, date_format(b.invoicedate,'%d/%m/%Y') As Stock_Date, D.TradeName, D.OwnerAddress, D.OwnerTinNo from itemmaster a, stockentry b, categorymaster c, OwnerDetails D where a.itemid = b.itemid and a.categoryid = c.categoryid and invoicedate = (select max(invoicedate) from stockentry where itemid = b.itemid) group by a.categoryid, a.itemname order by a.itemname;

One area that stands out is the way you are accessing the BillDetails table. All queries appear to be using MONTH and YEAR operators on BillDate. For example:

SELECT SUM(GROSSAMOUNT) FROM BILLDETAILS WHERE MONTH(BILLDATE) = 8 AND YEAR(BILLDATE) = 2011;

This approach requires accessing every record in BillDetails. A better approach would be to index BillDate and query as follows:

SELECT SUM(GROSSAMOUNT) FROM BILLDETAILS WHERE BILLDATE BETWEEN '2011-08-01' AND '2011-08-31';

Looks like you don't have much data in there at the moment (1373 rows) so the difference in the above will be minimal at the moment, more significant when the volume grows.


Database design for actions on some entity I am making a todo list kind of a service. One todo list will have several items , that can be done/deleted/snoozed etc. Users can also add a new item to the list , delete an item etc.

I want to know what the best practice for this kind of a table should be.
say I have 3 actions that can be performed like add/delete/done.
I was thinking of storing all the actions that can be performed on that list individually. But this brings the problem of getting the final state of the list.
How should I store activity performed on some entity as well as be able to get the final state of it.

It is possible to get all the actions ever performed on the list, and then process them , to get to the final result, but this sounds too processor intensive.
Is there a better way , or a best practice that I may follow ?

You want to track changes to the items at the item level so your item table would have a key that represents the 'item_id' and something else that distinguishes each state change for that item - you could use a timestamp or a counter or a flag. Let's generically call it 'version attribute'.

  Table Design

[LIST]

    LIST_ID
    LIST_NAME
    -- other attributes

[ITEM]

    ITEM_ID
    ITEM_NAME
    LIST_ID -- FK to LIST.LIST_ID
    ACTION_CODE
    -- version attribute (read below)
    -- other attributes

Your LIST to ITEMS relationship would be a one-to-many relationship.

  Sample Data [version attribute omitted]

LIST
1    List-1

ITEM
11   Item-1    1     A -- say added
12   Item-2    1     A
13   Item-3    1     A
11   Item-1    1     D -- say deleted
13   Item-3    1     C -- say changed

Now there are 2 ways you could track the latest state.

1) Maintain the order of timestamps or counters (if you choose this method) and pick the latest for each item of the list. [Not my preferred way]

2) Add a flag column to your items list say CURR_VER_FLAG and for a given item always pick the record that has CURR_VER_FLAG = 'Y'.

This flag is set to 'Y' (or true if boolean) on the addition of a new
item.
When there are any changes to that item you do

update ITEM set CURR_VER_FLAG = 'N' where ITEM_ID = ? and LIST_ID = ? and CURR_VER_FLAG = 'Y'

and then insert the new record with CURR_VER_FLAG = 'Y'

Your ITEM table would now be

ITEM
    11   Item-1    1     A   **N**
    12   Item-2    1     A   **Y**
    13   Item-3    1     A   **N**
    11   Item-1    1     D   **Y**
    13   Item-3    1     C   **Y**-- say changed

I haven't shown it but it should be clear that item_id can no longer be a primary key of this table. You would have to add another distinguishing column probably a surrogate key.

Now you can get

all actions performed on a list
state changes for any item of a list
final state of a list

Note
I haven't considered performance here but with right indexes that shouldn't be an issue.


Max_packet_allowed setting for Windows 7 We have windows xp machine with older version of mysql. So when we need to import huge data we just set the max_allowed_packet=1500M in the C:\Program Files\MySQL\MySQL Server 5.1 . Now we loaded the latest mysql 5.6.12. We are trying to import the same date and we find there are few my.cnf file on is in the program file and another in programme data both change yet we cant import the same data. Where else to change even after the change we started the service by going into the admistrative tool.

The maximum size for a MySQL Packet is 1G according to the MySQL Documentation. See my post Changed max_allowed_packet and still receiving 'Packet Too Large' error  in case you are dealing with big BLOBs.

If you are importing a large database from Windows. see my past posts

Jul 24, 2013 : Moving MySql from windows server to linux (StackOverflow)
Apr 17, 2011 : How can I optimize a mysqldump of a large database?


On Amazon RDS, what consumes storage space beyond the database files? I recently right-sized the data and log files for an RDS database, leaving just a few GB of unallocated space.  Shortly thereafter, something allocated, then deallocated, 1 GB of the margin of error.  What might have caused this?

All of the databases - including tempdb - are either tiny (MB) or at exactly the size they've been set; they add up to 197 GB, leaving 3 GB free on a 200 GB instance.  And yet:

Clearly, if this happens again in a slightly greater magnitude, there will be a problem.  For now, I can simply shrink one of the database files to give a larger margin, but I'd like to understand what actually happened.

AWS support came back with a comprehensive answer.  In this case, it was transaction log backups, being written to the SQL disk before offloading to S3.  There are a few more things which can consume space, mostly temporarily.  As their tech outlined:

  As a best Practice, I would recommend to keep at around 5% free space for similar processes (Log Backups, Error Log Files, Trace Files, etc), however this may vary based on high transactional volume or bulk data upload process , which may need high disk storage temporarily.


SQL Server 2005 Huge DB issue My DB files are growing very fast ( designer is not me and just a weekly report makes 7 GB growth ). And finally, I don't have enough space in the disk. And number of un-reported weeks counting up.

I have some options to perform but need your recommendations :

Shrink the database ( does shrinking gives the space to OS ? )
Detach and attach DB from an external disk or network storage. ( Is network storage supported by SQL Server 2005 ? )
Change RAID config from 1 to 5 to double the disk size. ( this is safest option but also dangerous of RAID config change. )

Thanks for these great and helpful answers regarding to my question. I have checked the log file and 99% of space is unused. So, I shrunk it. Since the DB is just used when we need some reports ( on time request, not always need to access to db ) I think performance won't be an issue for me.

Shrinking the database will allow for you to give back some space to the O/S, but that space comes at a performance cost as the shrink activity fragments the database. You can fix this by rebuilding your indexes, but that usually causes the space to be reclaimed. And the ides that a shrink would be a solution is false, because you are not addressing the growth issue itself, you are merely trying to put a bandage on a sucking chest wound.

Placing your files on disks that can be expanded easily is your best bet. External storage works well (or a SAN, if possible). You cannot use a UNC path for your database files, but you could use DFS to mount a drive and point to a network location and everything would work fine.

HTH


What could cause a text comparison to fail? DB Version: PostgreSQL 8.2.15 (Greenplum Database 4.3.4.1 build 2) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Feb 10 2015 14:15:10

I'm having a problem in one of our environments where the tables in one schema seem to behave incorrectly when doing simple queries, character columns are not comparing to literal strings or to other columns. Joins within the schema to other identical columns work, but joins to tables outside of the schema do not. Here's a simple example with literal strings.

I do this:

SELECT *
FROM carriers
WHERE carrier_code = 'FR'

Then I copy and paste the value from a character(32) column and do this:

SELECT *
FROM carriers
WHERE carrier_hash = '11aedd0e432747c2bcd97b82808d24a0'

This returns nothing. That's a straight copy-and-paste from that actual column as returned by the first query. If I do this, however:

SELECT *
FROM carriers
WHERE carrier_hash like '11aedd0e432747c2bcd97b82808d24a0'

That returns the same record. This also works:

SELECT *
FROM carriers
WHERE substring(carrier_hash,1,32) = '11aedd0e432747c2bcd97b82808d24a0'

Why might = and like behave differently with a simple string and no _ or % characters? This has been working for months. The only thing that's changed recently is we've backed up, cleared out, and restored the database. Could something have become messed up during the restore?

We have other tables in other schemas that have the same column, and those work just fine.

Our DBA recently restored this environment from a backup. Don't know if that is relevant.

This is occurring because something has changed in the way our data is being distributed. Some of our tables are not distributed in the same way as others - the records are on different nodes than the distribution algorithm says that they should be. So, when I run a query that looks for a specific value, the master sends that query only to the node that should hold that key value, and it doesn't find it. The same happens with a join - the segment node assumes that since both tables are distributed on the same join key, that the join can be performed locally. One of the tables is incorrectly distributed, so the join fails.

I don't yet know what has caused this, I will post an update if I find out. Any suggestions what it might be?

I have removed the postgresql tag as this problem turns out to be greenplum-specific.

Ok here's what happened. Our DBA was restoring from a backup, but it failed because the files in the backup didn't match the expected names. The restore wanted a file called something_0_2_something and _0_3_ and _0_4_ but the backup contained _0_22_ and _0_23_ and _0_24_ instead. So he raised a call with Pivotal, but because that was taking a while to get an answer, he just renamed them all subtracting 20 from the number and carried on with the restore. Seems like this has ended up with data being restored to the wrong nodes. We're still waiting for an answer from Pivotal about what went wrong with the file names and how we can fix it.

Further information: apparently the backup had backed up the mirror segments, not the primary segments, hence the different file names. We are working around it by taking down the primaries, restoring to the mirrors, bringing the primaries back up, and re-balancing. We don't know why it happened, the primary and mirror status looked fine. Pivotal have been looking at the system on webex and they say they've never seen this happen before so there's an achievement.


Open encrypted trigger of table in database (MS SQL Server Management Studio) Microsoft SQL Server Question for Trigger in database-tables.
Is there a possibility to view Code behind a encrypted trigger of a database table?
I only see a key inside the Icon and rightclick "Change" is Grey.

Need some help please

Is there a possibility to view Code behind a encrypted trigger of a database table?

Yes, but since it's most likely against the TOS, I'll leave it as that and tell you if you really really want to get at the code to search the internet.

I hate to leave the answer that short, but that's the whole point of encrypting it. If this is your code and you did this, note that you'll want to save the original text in a managed repository or other source code system. If it's not yours, you may be violating a TOS, EULA, or other laws depending on the situation and opening yourself up to liabilities.


Why max_allowed_packet is larger in mysqldump than mysqld in my.cnf? If you look at the sample config such as /usr/share/mysql/my-large.cnf, you will find..

[mysqld]
max_allowed_packet = 1M

[mysqldump]
max_allowed_packet = 16M

What is the point?

Because when you export a dump using mysqldump with max_allowed_packet that might not able import successfully to the current mysql? E.g. https://stackoverflow.com/questions/93128/mysql-error-1153-got-a-packet-bigger-than-max-allowed-packet-bytes

The max_allowed_packet variable limits the size of a single result set. In the [mysqld] section, any normal connection can only get 1M worth of data in a single query.
In mysqldump you typically produce "extended INSERT" queries, where you list multiple rows within the same INSERT command. It's better, then, to have this variable set high.

The above is a brief explanation. Now, as for your question "What is the point?" -- there is no clear answer. The my-XXXXX.cnf set of files are long since outdated, and have their issues, including recommendations which are commonly not agreed upon (like the amount of memory you should allocate to the innodb_buffer_pool_size). So take it as a general starting point. In particular, these settings relate to systems with very low memory available (like 128M RAM), so the numbers don't really apply to today's hardware.

One could argue that you won't necessarily import the dump into same server, so possible to another server with higher setting for max_allowed_packet.

As a side note, I usually set max_allowed_packet to very high values, like 32M or 64M. As opposed to other variables like sort_buffer_size, with max_allowed_packet you only pay for what you need. So a connection will not allocate the full 64M if it doesn't have to.

A 1M limit means you could have some row with some TEXT or BLOB, possibly larger than 1M, and in such case, you would actually be unable to read that row.


SQL Query: Column Name as Value I have a Redshift table that looks like the following:

+----------------+---------------+------+
| FieldA         | field1        |field2|
+----------------+---------------+------+
| ABC            | 123           |   78 |
| DEF            | 456           |   90 |
+----------------+---------------+------+

I want to run a query to produce the following output. I believe I have to use a

UNION ALL

to get the fields I want split up into different rows, but I'm not sure how to get the column names (field1, field2) as values within a column.

+----------------+---------------+------+
| FieldA         | value         |field |
+----------------+---------------+------+
| ABC            | 123           |field1|
| DEF            | 456           |field1|
| ABC            | 78            |field2|
| DEF            | 90            |field2|
+----------------+---------------+------+

(I don't care about output order at the moment)

You are right about UNION or UNION ALL. Due to the field column, the legs in the UNION will be disjoint:

SELECT FieldA, field1 as value, 'field1' as field
FROM T
UNION ALL
SELECT FieldA, field2 as value, 'field2' as field
FROM T

I don't think MySQL as of latest version yet supports LATERAL, but I may be mistaken so in case it does, it will be slightly more efficient to do something like:

SELECT x.FieldA, x.value, x.field
FROM T
LATERAL JOIN (
    values (T.FieldA, field1, 'field1')
         , (T.FieldA, field2, 'field2')
) x (FieldA, value, field);

You may have to do:

LATERAL JOIN (
    SELECT T.FieldA, field1 as value, 'field1' as field
    UNION ALL
    SELECT T.FieldA, field2 as value, 'field2' as field
) x;


How to index two tables for JOINed query optimisation I'm using PostgreSQL 9.5 on High Sierra.

Over the two tables:

request_logs - ~ 26K rows

response_logs - ~ 9K rows

I've the following query (with JOIN):

SELECT
  req.uuid,
  res.status,
  req.method,
  req.requesturi,
  req.accessed,
  req.payload reqpayload,

  res.payload respayload,
  COUNT(*) OVER() AS total_rows
FROM
  request_logs req
  LEFT OUTER JOIN response_logs res ON req.uuid = res.uuid
WHERE
   req.accountid = 2 AND
   req.requesturi not ilike '/v1/sessions%' AND
   req.accessed BETWEEN “2018-01-01 15:04:05 +0000” and “2019-01-02 15:04:05+0000” AND
   res.status IS NOT NULL AND
   req.applicationid = 1
ORDER BY
   accessed DESC LIMIT 1000

As I'm trying to optimise the query, I've experimented with different indexes:
Here's a list of what I tried:

Configuration 1:
 1. request_log.uuid (pkey, unique)
 2. response_log.uuid (pkey, unique, foreign key)

Response time avg. : 260 ms

Configuration 2:
 1. request_log.uuid (pkey, unique)
 2. request_log.applicationid
 3. response_log.uuid (pkey, unique, foreign key)

Response time avg. : 230 ms

Configuration 3:
 1. request_log.uuid (pkey, unique)
 2. request_log.applicationid
 3. request_log.accessed (timestampz)
 4. response_log.uuid (pkey, unique, foreign key)

Response time avg. : 230 ms

Configuration 4:
 1. request_log.uuid (pkey, unique)
 2. request_log.applicationid
 3. request_log.accessed (timestampz)
 4. request_log.accountid
 5. response_log.uuid (pkey, unique, foreign key)

Response time avg. : 230 ms

Configuration 5:
 1. request_log.uuid (pkey, unique)
 2. request_log.applicationid, request_log.accessed (combined)
 3. response_log.uuid (pkey, unique, foreign key)

Response time avg. : 240 ms

As visible from the result, indexing by applicationid (an int8) did help a little, while indexing by the timestampz accessed didn't help at all.
Maybe the bad performance is due to the JOIN?
Altogether, it seems quite slow and I try not to think what will happen when these tables contain millions of record (10M+).

What would be a better way to index these tables to make this query run faster?

EDIT:

Here is EXPLAIN ANALYZE for the last configuration:

Limit  (cost=3489.80..3490.69 rows=356 width=823) (actual time=241.152..241.345 rows=1000 loops=1)
  ->  Sort  (cost=3489.80..3490.69 rows=356 width=823) (actual time=241.150..241.288 rows=1000 loops=1)
        Sort Key: req.accessed DESC
        Sort Method: top-N heapsort  Memory: 2064kB
        ->  WindowAgg  (cost=1829.41..3474.71 rows=356 width=823) (actual time=230.040..237.993 rows=3951 loops=1)
              ->  Hash Join  (cost=1829.41..3470.26 rows=356 width=823) (actual time=8.622..17.974 rows=3951 loops=1)
                    Hash Cond: (res.uuid = req.uuid)
                    ->  Seq Scan on response_logs res  (cost=0.00..1604.21 rows=8821 width=758) (actual time=0.006..4.527 rows=9124 loops=1)
                          Filter: (status IS NOT NULL)
                    ->  Hash  (cost=1816.39..1816.39 rows=1042 width=102) (actual time=8.243..8.243 rows=4046 loops=1)
                          Buckets: 4096 (originally 2048)  Batches: 1 (originally 1)  Memory Usage: 1122kB
                          ->  Bitmap Heap Scan on request_logs req  (cost=105.85..1816.39 rows=1042 width=102) (actual time=0.581..6.449 rows=4046 loops=1)
Execution time: 242.154 ms

For starters, get your LEFT JOIN right and try to get rid of total rows (as discussed in comments):
Query
Updated to your comment:

I only want to returns rows from request_logs that have a parallel entry in response_logs with non nil status:

SELECT q.uuid
     , s.status
     , q.method
     , q.requesturi
     , q.accessed
     , q.payload reqpayload
     , s.payload respayload
--   , COUNT(*) OVER() AS total_rows  -- see below
FROM   request_logs q
JOIN   response_logs s USING (uuid)   -- not LEFT JOIN
WHERE  q.accountid = 2
AND    q.requesturi NOT ILIKE '/v1/sessions%'
AND    q.accessed BETWEEN '2018-01-01 15:04:05 +0' AND '2019-01-02 15:04:05+0'
AND    q.applicationid = 1
AND    s.status IS NOT NULL           -- see below
ORDER  BY q.accessed DESC
LIMIT  1001;                          -- see below

Your LEFT [OUTER] JOIN burns down to a plain [INNER] JOIN. Related:

Query with LEFT JOIN not returning rows for count of 0

COUNT(*) OVER() AS total_rows is expensive for counts substantially bigger than LIMIT (and you are expecting "10m+ rows"). Maybe it's good enough to use LIMIT 1001, only use the first 1000 rows, check the row count and if it's 1001 then you know there are "more than 1000 matching rows". Related:

Best way to get result count before LIMIT was applied

Indexes
If (like you commented)

the only 'moving parts' in this query are the dates (from and to) that are compared with accessed

... and more than a few rows are excluded with the other predicates
on applicationid, requesturi and accountid, then a partial index should help read performance (a lot):
CREATE INDEX ON request_logs (uuid, accessed DESC)
WHERE  accountid = 2
AND    requesturi NOT ILIKE '/v1/sessions%'
AND    applicationid = 1

And if a substantial percentage of rows is eliminated by status IS NOT NULL, also:
CREATE INDEX ON response_logs (uuid)
WHERE  status IS NOT NULL;

It may pay to append the columns status and payload as index columns if you can get index-only scans out of it. Some preconditions apply.


Slow Select over View My simple SELECT statement on a view in Postgres 9.3.1 is pretty slow. The receipts table has 20 million entries and the other one has around 17k.

But I still think it should be faster than ~16 seconds?

Table receipts:

     Column      |            Type           | Modifiers | Storage     | Stats target  | Description
-----------------+---------------------------+-----------+-------------+---------------+--------------
 profitcenter    | character varying(5)      | not null  | extended    |               |
 receiptnumber   | bigint                    | not null  | plain       |               |
 receiptposition | character varying(6)      | not null  | extended    |               |
 date            | date                      | not null  | plain       |               |
 customerid      | integer                   |           | plain       |               |
 vkorg           | character varying(4)      |           | extended    |               |
 artikelnummer   | integer                   |           | plain       |               |
 price           | numeric(18,2)             |           | main        |               |
Indexes receiptnumber, date and customerid as B-Tree.

Table cc_add_entry2:

    Column     |          Type          | Modifiers | Storage     | Stats target  | Description
---------------+------------------------+-----------+-------------+---------------+--------------
 receiptnumber | bigint                 | not null  | plain       |               |
 customerid    | bigint                 |           | plain       |               |
 user          | character varying(255) |           | extended    |               |
 note          | text                   |           | extended    |               |
 date          | date                   |           | plain       |               |
Indexes receiptnumber, customerid as B-Tree.

The view is simple:

 SELECT s.receiptnumber,
    s.receiptposition,
    s.date,
    COALESCE(b.customerid, (s.customerid)::bigint) AS customerid,
    s.price
   FROM (dashboard.receipts s
     LEFT JOIN dashboard.cc_add_entry2 b ON ((s.receiptnumber = b.receiptnumber)));

EXPLAIN ANALYZE over a simple query:

explain analyze select * from test2 where customerid = 520001215;

 Hash Left Join  (cost=582.40..1086787.72 rows=100185 width=37) (actual time=2124.048..15937.744 rows=52 loops=1)
   Hash Cond: (s.receiptnumber = b.receiptnumber)
   Filter: (COALESCE(b.customerid, (s.customerid)::bigint) = 520001215)
   Rows Removed by Filter: 20036943
   ->  Seq Scan on receipts s  (cost=0.00..599969.96 rows=20036996 width=29) (actual time=0.904..7187.563 rows=20036995 loops=1)
   ->  Hash  (cost=360.51..360.51 rows=17751 width=16) (actual time=5.850..5.850 rows=17751 loops=1)
         Buckets: 2048  Batches: 1  Memory Usage: 694kB
         ->  Seq Scan on cc_add_entry2 b  (cost=0.00..360.51 rows=17751 width=16) (actual time=0.005..2.537 rows=17751 l
oops=1)
 Total runtime: 15937.962 ms

I am pretty sure the reason is that customerid in the view is the result of:

COALESCE(b.customerid, s.customerid::bigint)

After LEFT JOIN, cast & COALESCE, the predicate cannot be pushed down, so both tables have to be read in full, before the filter customerid = 520001215 can be applied. The fact that customerid can be NULL in both tables is not helping either. Can at least one be set to NOT NULL?

Extremely inefficient.

Run this query, and compare performance:

SELECT r.receiptnumber
    ,  r.receiptposition
    ,  r.date
    ,  c.customerid
    ,  r.price
FROM   dashboard.cc_add_entry2 c
JOIN   dashboard.receipts r USING (receiptnumber)
WHERE  c.customerid = 520001215;

UNION ALL
SELECT r.receiptnumber
    ,  r.receiptposition
    ,  r.date
    ,  r.customerid::bigint
    ,  r.price
FROM   dashboard.receipts r
LEFT   JOIN dashboard.cc_add_entry2 c USING (receiptnumber)
WHERE  r.customerid = 520001215
AND    c.customerid IS NULL;

Should be much faster. If you can't wrap it into a view, either use this query instead or write a SQL or plpgsql function that takes customerid as parameter.

Also these multicolumn indices would help some more:

CREATE INDEX foo1 ON dashboard.cc_add_entry2 (customerid, receiptnumber);
CREATE INDEX foo2 ON dashboard.cc_add_entry2 (receiptnumber)
WHERE customerid IS NULL;
CREATE INDEX foo3 ON dashboard.receipts (customerid, receiptnumber);


Will Oracle ignore my index? Suppose I have a table (TABLE_1) with some fields, including ID (number), NAME (varchar2) and DATE_TIME (date). There was a need to create a index on those 3 fields to optimize queries like:

select * from TABLE_1 where ID = [SOME_VALUE] and NAME = [SOME_VALUE] and DATE_TIME is not null;

Because DATE_TIME can have a multitude of different datetime values, I decided to create the index as such:

CREATE INDEX IDX_TABLE_1 ON TABLE_1 (ID,NAME,TRUNC(DATE_TIME));

My question is: will Oracle use this index on the select query or will it ignore it and perform a full table scan on TABLE_1?

EDIT1: I forgot to mention that the ID field is a foreign key.

Depends on the data distribution. I can easily construct examples for both cases (index vs table scan).

Just think about the case when the columns are unique or nearly unique (index), or when all the rows are the same (table scan).

Technically your index can be used for the above query.

Given the general nature of a column named ID, I would say the index will be chosen, but it is not guaraanteed.

The predicate DATE_TIME is not null will be processed at the table level, not the index level, even if you make sure that the NULL rows are indexed as well:

create index index_1 on table_1(id, name, trunc(date_time), 1);

PLAN_TABLE_OUTPUT
-------------------------------------------------------
Plan hash value: 852675278

-------------------------------------------------------
| Id  | Operation                           | Name    |
-------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TABLE_1 |
|*  2 |   INDEX RANGE SCAN                  | INDEX_1 |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DATE_TIME" IS NOT NULL)
   2 - access("ID"=1 AND "NAME"='Oracle')


Cannot dump database in Oracle on Docker Oracle is working fine on Docker, I can connect from sqlDeveloper with no problem, but I cannot dump the database logging into Docker in the bash command line.

Runnung:

expdp CONFIG0001-TEST/xxxxxxx DIRECTORY=backups DUMPFILE=dumpfile.dmp
      LOGFILE=config_export.log SCHEMAS=CONFIG0001-TEST

I get:

UDE-12547: operation generated ORACLE error 12547
ORA-12547: TNS:lost contact

Troubleshooting the ORA-12547 error on this page I see that the most likely option is that Oracle doesn't have permission:

  Possible solutions to to the ORA-12547 error include:

  Bad kernel parameters settings (e.g. "ulimit" set too small)
  Incorrect permissions on the ORACLE executable ($ORACLE_HOME/bin/oracle.exe)
  $ORACLE_HOME/rdbms/lib/config.o is 0 bytes
  Oracle binaries have not been linked correctly

What user needs to have access to $ORACLE_HOME/bin/oracle.exe ?

I was able to replicate the error.First login without username parameter into docker container(you'll logged in as root user)

C:\>docker exec -it xedbd bash
bash-4.2# expdp system/oracle  dumpfile=test01.dmp directory=DATA_PUMP_DIR logfile=test.log

Export: Release 18.0.0.0.0 - Production on Mon Feb 17 21:22:19 2020
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

UDE-12547: operation generated ORACLE error 12547
ORA-12547: TNS:lost contact

I'll add user parameter and remember the schema is in pdb you need to add connect identifier in expdp

    C:\>docker exec -it -u oracle xedbd bash
    [oracle@89feb4161c93 /]$ expdp system/oracle@xepdb1 dumpfile=test01.dmp directory=DATA_PUMP_DIR logfile=test.log schemas=test

    Export: Release 18.0.0.0.0 - Production on Mon Feb 17 21:24:12 2020
    Version 18.4.0.0.0

    Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

    Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
    Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@xepdb1 dumpfile=test01.dmp directory=DATA_PUMP_DIR logfile=test.log schemas=test
    Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
    Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
    Processing object type SCHEMA_EXPORT/USER
    Processing object type SCHEMA_EXPORT/ROLE_GRANT
    Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
    Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
    Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
    Processing object type SCHEMA_EXPORT/TABLE/TABLE
    . . exported "TEST"."T1"                                 9.978 MB   72834 rows
    Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
    ******************************************************************************
    Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
    /opt/oracle/admin/XE/dpdump/9ECC9107E3AE0AADE053020011AC6840/test01.dmp
    Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Feb 17 21:24:53
   2020 elapsed 0 00:00:39


How to delete a Backup from a backup set in SQL Server 2008 R2 The title pretty much sums it up. We are running out of space on our backup disks, and need to remove old backups out of the set. I can't seem to find any information on this.

I would change your backup strategy to write to individual files for each database backup.  You could then have a maintenance plan step that examines your backup location and removes *.bak files older the X days.


MySQL overhead of calling a Stored Procedure Within Java, we access MySQL (v5.5.40) via JDBC -- a prepare statement, setting parameters then calling the stored procedure. It appears that every execute() has the overhead of 4 meta-calls (per MySQL query log) preceding the actual SP call:

SELECT name, type, comment FROM mysql.proc WHERE name like...
SHOW FUNCTION STATUS LIKE 'sp_one'
SHOW PROCEDURE STATUS LIKE 'sp_one'
SHOW CREATE PROCEDURE `db-name`.`sp_one`
CALL sp_one()

Is this the expected behavior on the server side? It just doesn't seem very efficient.

Here is the example of how we use the API:

  conn = DriverManager.getConnection(..);

  stmt = conn.prepareCall("{call sp_one(?,?)}");

  stmt.setString(1, "a");
  stmt.setString(2, "b");

  stmt.execute();

Please note that the following is true:

CallableStatement.getMetadata() is not used
Parameters are accessed by index, not by name

Thanks!

Percona wrote up a nice article MySQL Prepared Statements explaining pros and cons

  So there are good reasons to use prepared statements:

  Save on query parsing
  Save on data conversion and copying
  Avoid SQL Injection
  Save memory on handling blobs

  There are also drawbacks and chewats of using prepared statements:

  Query cache does not work
  Extra server round trip required if statement used only once
  Not all statements can be prepared. So you can’t use prepared API exclusively you’ll need to fall back to normal API for some statements
  Newer and sometimes buggy code. I had a lot of problems with PHP prepared statements. It is getting better but still it is less mature than standard API
  You can’t use placeholders in place of all identifiers. For example you can’t use them for table name. In certain version it even does not work for LIMIT boundaries
  Inconvenient list handling. Unlike in for example PEAR emulated
  prepared statements there is no nice way to pass list of values to IN
  Harder tracing. Logs were now fixed to include full statement text not
  only “Execute” but in SHOW INNODB STATUS you would still see
  statements without actual values – quite inconvenient for analyses

Since what you are experiencing is the expected behavior, you should not use Prepare Statements. You are making many calls to execute a single command. If you simply ran this

stmt = conn.createStatement();
rs = stmt.executeQuery("call sp_one('a','b')");

the complete parsing of the SQL, preparing for execution, and the parameters already being passed in through the SQL statement are all done on the server side with one call.

Please read that Percona blog for more details.


How I would I break a total count query into intervals? For example, if I did a normal count query like:

select status, count(*)
from customers
group by status

and get the result

status   count
-----------------
open     1200
closed   1002

but I want the count to be split into intervals of 500 so I would get a result returned like this

status   count
----------------
open     500
open     500
open     200
closed   500
closed   500
closed   2

You could potentially do this using ROW_NUMBER.

I'd just advise to steer away from this whole thing, as it's not particularly useful....

If you were to do this, make sure you replace the <some_id> with the Customer ID or similar...

SELECT status, count(*)
FROM
(
  SELECT
    ROW_NUMBER() OVER(PARTITION BY status ORDER BY <some_id> ASC) rnum,
    status
  FROM customers
) weirdness
GROUP BY status, (rnum-1)/500;


Recent patches for vulnerability attacks for SQL Server 2014 SP2 version Keeping in mind the recent vulnerability attacks patches like Spectre and Meltdown , I would like to patch my SQL Server 2014 SP2 (12.0.5000)

1.Description of the security update for SQL Server 2014 SP2 CU10: January 16, 2018 - SP2 CU10   KB4057117  12.0.5571.0
2.Description of the security update for SQL Server 2014 SP2 GDR: January 16, 2018 - SP2 GDR   KB4057120  12.0.5214.6

Can anyone help me for below questions:

Recently we patched our Windows 2012 R2 Standard servers . Is OS Patching different from SQL Sever patching or these patches are already included during monthly OS patching installed ?
Since we have both CU10 and GDR , should I apply both ?
Which one should I need to first apply GDR or CU 10 ?

You need both SQL and Windows patches, as well as CPU microcode updates, to be fully** protected.

GDR is meant to be security-only patches, whereas Cumulative Updates are bug fixes as well as security patches. Once you go from the GDR path on to the CU path, you're stuck there, i.e. you can't go back to GDR only.

(Confusingly, this latest Meltdown / Spectre update shows up on certain SQL Server versions as a GDR and CU update.)

If you are already on a CU, then stick with CU update.

** Note that Microsoft is rolling back the Meltdown / Spectre updates on Windows Update because there are some issues with certain CPUs, so it's hard to tell whether you were one of the lucky ones to get them while they were up. See the warning below.

Use this link to check if you're fully patched, using a PowerShell script from Microsoft.

The link includes the following statement:

  Customers must take the following actions to help protect against the
  vulnerabilities:

  Verify that you are running a supported antivirus application before
  you install operating system or firmware updates. Contact the
  antivirus software vendor for compatibility information.
  Apply all
  available Windows operating system updates, including the January 2018
  Windows security updates.
  Apply the applicable firmware update that is
  provided by the device manufacturer.

And this warning:

  Customers who only install the Windows January 2018 security updates will not receive the benefit of all known protections against the vulnerabilities. In addition to installing the January security updates, a processor microcode, or firmware, update is required. This should be available through your device manufacturer.


Large SQL Server advice I'm dealing with a quite large SQL Server database and encountering a few problems.

The database is quite new and I've been importing data into the tables, and it's making the whole server go quite slow. The database is now 15GB and in 1 table for example it has just 4 columns, but 122 million rows and takes up 3.5GB. Another table has about 35 columns with 3 million rows and takes up about 1.5GB.

Whenever I open one of the big tables, or start using it with my MVC3 application, it nearly cripples the server; the the memory usage hits max and it starts running really slowly. (Server spec: Pentium dual core E6300 2.8GHz, 2GB RAM, Windows 2008, SQL Server 2005, one disk).

This is really the first dealings I've had with this amount of data so have been adding indexes to try and speed things up but once the memory hits the max level, I've had to restart the server in order for it to work at normal speed.

Anyway, I don't feel that 15GB is that big - surely 2GB RAM could handle it? I've not even finished adding the first round of data so it's going to get considerably bigger.

Does anyone have any advice for how it could be set up better and be more efficient. All PK's and FK's have been set up and I've added indexes to the large tables to see if that helps any but it's eventually started going slowly again. BTW, in the database properties, it says 'Space Available - 1596.96MB' How do you increase that figure?

2GB of RAM is barely acceptable to run Windows, let alone SQL Server on top.

Obviously your 15GB of data doesn't fit into 2GB of RAM. So, any and every query you run that requires rows that aren't in RAM will require reads from disk.

From there, you can do the math on how much data you're requesting versus how fast your disks can deliver it. Given that the server is so woefully underspecified for memory, one can assume you've got just a single spinner delivering IOPS at miserably slow rate, perhaps 50MB/sec for a sequential read but as low as 1MB/sec with several queries fighting for their share.

Have 5 users each run a query that needs 1GB of data that isn't currently in RAM and you'll be waiting 15+ minutes for the data to crunch its way through the system.

RAM is comically cheap, especially compared to the cost of you wasting your time trying to optimise 15GB into 2GB. Fill the server, or buy a new one.

Also: the Pentium E6300 seems to be a desktop-class processor. You want a computer designed to be a server. Just adding a couple of disks and more RAM may not be satisfactory.


SELECT with where between not working as expected I'm trying to use this query, to filter between the names C89 to C110.
SELECT id FROM servers WHERE server_name BETWEEN 'C89' AND 'C110'

But this doesn't work. If I change c89 to c90, it at least selects the two servers. It's like it's only using the first 2 numbers in the calculation.
Can anyone help me ? I'm using mariadb 10.5 I'm looking for a solution that doesn't need to add a lot of words into the query. To be as lean as possible for readability

A shorter answer:
... WHERE MID(server, 2) BETWEEN 89 AND 110

Or, if you need to limit to the "C" set of servers:
... WHERE MID(server, 2) BETWEEN 89 AND 110
      AND server LIKE "C%"

Or (to avoid trouble with "24H1"),
... WHERE IF(server LIKE "C%",
             MID(server, 2) BETWEEN 89 AND 110,
             FALSE)

Oh, now I see another 'bad' server name (INT1).  Face it, you need to put some of the burden on the DBA.  He must realize that the practical way is to say
... WHERE server BETWEEN 'C89'  AND 'C99'
       OR server BETWEEN 'C100' AND 'C110'

That is, break up the filtering into as many ranges as needed so that string comparisons will work, and do not think numeric.


PostgreSQL use NEW in query for INSTEAD OF trigger I'm having trouble getting an INSTEAD OF trigger to work correctly, and I think I've misunderstood how to use NEW. Consider the following simplified scenario:

CREATE TABLE Product (
  product_id SERIAL PRIMARY KEY,
  product_name VARCHAR
);
CREATE TABLE Purchase (
  purchase_id SERIAL PRIMARY KEY,
  product_id INT REFERENCES Product,
  when_bought DATE
);

CREATE VIEW PurchaseView AS
SELECT purchase_id, product_name, when_bought
FROM Purchase LEFT JOIN Product USING (product_id);

I'd like to be able to create INSTEAD OF triggers to allow me to insert directly into PurchaseView, e.g.:

INSERT INTO Product(product_name) VALUES ('foo');
INSERT INTO PurchaseView(product_name, when_bought) VALUES ('foo', NOW());

What I had in mind was something along the lines of:

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$BODY$
BEGIN
  INSERT INTO Purchase(product_id, when_bought)
  SELECT product_id, when_bought
  FROM NEW
  LEFT JOIN Product USING (product_name)
  RETURNING * INTO NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER insert_productview_trig
  INSTEAD OF INSERT
  ON PurchaseView
  FOR EACH ROW
  EXECUTE PROCEDURE insert_purchaseview_func();

However the above trigger function gives errors (relation "new" does not exist) when executed. I know I can write queries that explicitly use attributes from NEW in the WHERE and SELECT clauses, but sometimes it would be convenient to be able to include NEW in a join. Is there a way to do this?

Current (unsatisfactory) solution

The closest I can get to what I want is

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$BODY$
DECLARE
tmp RECORD;
BEGIN
    WITH input (product_name, when_bought) as (
       values (NEW.product_name, NEW.when_bought)
    )
    INSERT INTO Purchase(product_id, when_bought)
    SELECT product_id, when_bought
    FROM input
    LEFT JOIN Product USING (product_name)
    RETURNING * INTO tmp;
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

This is a little unsatisfactory for several reasons:

I need to explicitly write all the attributes of NEW in CTE WITH query, which for large views (especially those whose attributes are automatically determined with SELECT *) gets unwieldy;
The returned result doesn't have the SERIAL type product_id updated, so you don't get the expected result for:

INSERT INTO PurchaseView(product_name, when_bought)
VALUES ('foo', NOW())
RETURNING *;

NEW is a record, not a table. Basics:

Use NEW in FROM clause in Postgres trigger?

Slightly modified setup

CREATE TABLE product (
  product_id serial PRIMARY KEY,
  product_name text UNIQUE NOT NULL  -- must be UNIQUE
);

CREATE TABLE purchase (
  purchase_id serial PRIMARY KEY,
  product_id  int REFERENCES product,
  when_bought date
);

CREATE VIEW purchaseview AS
SELECT pu.purchase_id, pr.product_name, pu.when_bought
FROM   purchase     pu
LEFT   JOIN product pr USING (product_id);

INSERT INTO product(product_name) VALUES ('foo');

product_name has to be UNIQUE, or the lookup on this column could find multiple rows, which would lead to all kinds of confusion.

1. Simple solution

For your simple example, only looking up the single column product_id, a lowly correlated subquery is simplest and fastest:

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO purchase(product_id, when_bought)
   SELECT (SELECT product_id FROM product WHERE product_name = NEW.product_name), NEW.when_bought
   RETURNING purchase_id
   INTO   NEW.purchase_id;  -- generated serial ID for RETURNING - if needed

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER insert_productview_trig
INSTEAD OF INSERT ON purchaseview
FOR EACH ROW EXECUTE PROCEDURE insert_purchaseview_func();

No additional variables. No CTE (would only add cost and noise). Columns from NEW are spelled out once only (your point 1).

The appended RETURNING purchase_id INTO NEW.purchase_id takes care of your point 2: Now, the returned row includes the newly generated purchase_id.

If the product is not found (NEW.product_name does not exist in table product), the purchase is still inserted and product_id is NULL. This may or may not be desirable.

2.

To skip the row instead (and possibly raise a WARNING / EXCEPTION):

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO purchase AS pu
            (product_id,     when_bought)
   SELECT pr.product_id, NEW.when_bought
   FROM   product pr
   WHERE  pr.product_name = NEW.product_name
   RETURNING pu.purchase_id
   INTO   NEW.purchase_id;  -- generated serial ID for RETURNING - if needed

   IF NOT FOUND THEN  -- insert was canceled for missing product
      RAISE WARNING 'product_name % not found! Skipping INSERT.', quote_literal(NEW.product_name);
   END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

This piggybacks NEW columns to SELECT .. FROM product. If the product is found, everything proceeds normally. If not, no row is returned from the SELECT and no INSERT happens. The special PL/pgSQL variable FOUND is only true if the last SQL query processed at least one row.

Could be EXCEPTION instead of WARNING to raise an error and roll back the transaction. But I'd rather declare purchase.product_id NOT NULL and insert unconditionally (query 1 or similar), to the same effect: raises an exception if product_id is NULL. Simpler, cheaper.

3. For multiple lookups

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO purchase AS pu
            (product_id,   when_bought)     -- more columns?
   SELECT pr.product_id, i.when_bought      -- more columns?
   FROM  (SELECT NEW.*) i                   -- see below
   LEFT   JOIN product  pr USING (product_name)
-- LEFT   JOIN tbl2     t2 USING (t2_name)  -- more lookups?
   RETURNING pu.purchase_id                 -- more columns?
   INTO   NEW.purchase_id;                  -- more columns?

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

The LEFT JOINs make the INSERT unconditional again. Use JOIN instead to skip if one is not found.

FROM  (SELECT NEW.*) i transforms the record NEW into a derived table with a single row, which can be used like any table in the FROM clause - what you were looking for, initially.

db<>fiddle here


COUNT for instances with no value I have a query that looks for and counts orders that have been written in a medical record that have a specific display group SID. I have 8 medical centers and I want a count for all of them even if it's zero. My query return counts fine when there are instances, but it will not list a facility with a zero count. I tried the LEFT JOIN as mentioned in other threads.

SELECT DISTINCT *
INTO   #T
FROM   (SELECT    p. [PatientSID]
                  ,p.[TestPatientFlag]
                  ,co.[CPRSOrderSID]
                  ,co.[DisplayGroupSID]
                  ,co.[Sta3n]
                  ,[EnteredDateTime]
        FROM      [CPRSOrder].[CPRSOrder] co
        LEFT JOIN [SPatient].[SPatient] p
        ON        co.[PatientSID] = p.[PatientSID]
        WHERE     [TestPatientFlag] IS NULL
        AND       [EnteredDateTime] >'2018-01-01'
        ) AS X

SELECT [Sta3n]
       ,COUNT([DisplayGroupSID]) AS '# Of RTC Requests'
FROM   #T
WHERE [DisplayGroupSID] IN (1200003559
                            ,1200003561
                            ,1200003558
                            ,1200003557
                            ,1200003560
                            ,1200003562
                            ,1200003556
                            ,1200003519)
GROUP BY [Sta3n]

Here's the output (It's missing 4 sites who have no instance of the type of order I am looking for:

Sta3n   # Of RTC Requests
-----   -----------------
679     755
544     974
521     2
534     54

You need to use the LEFT JOIN to connect a dataset with the list of values you want to see with another dataset with the values you want a count of.

If you make your final query something like:

SELECT
       ctr.[Sta3n]
      ,COUNT(cnt.[DisplayGroupSID]) AS '# Of RTC Requests'
  FROM (SELECT DISTINCT [Sta3n] FROM [CPRSOrder]) ctr
         LEFT JOIN #T cnt ON (ctr.[Sta3n] = cnt.[Sta3n])
 WHERE cnt.[DisplayGroupSID] IN
      ( 1200003559
       ,1200003561
       ,1200003558
       ,1200003557
       ,1200003560
       ,1200003562
       ,1200003556
       ,1200003519)
 GROUP BY ctr.[Sta3n]
;

(Alias ctr is short for "medical CenTeR"; cnt for "values to CouNT").

You should get something more like what you are looking for.

Note that it may make more sense to select the list of distinct [Sta3n] values from some other location - you presumably know more about your system than I do.

Similarly, the temp table you're using may not be necessary, and you might choose to store the distinct [Sta3n] values in their own temp table.

The key is, have the list of values you want to see even if your count is 0 in the table on the left side of the join, and the table with the data to be counted on the right side.

NOTE: Assumes your DBMS allows sub-queries as derived tables, and table name aliases. I think that's pretty generic, but you don't specify your DBMS.


What would a "properly designed" schema for roles and permissions look like? I'm creating an application that has a similar roles and permissions system to Discord.

Basically

Users are members of groups
Groups have roles
Roles have permissions
And users can be associated with one role at a time.

I'm not sure how to design a system like this. Originally, I was thinking I'd have a roles table, and for each permission there would be a boolean column like: can_change_nickname.

That doesn't seem right though...

If you have any recommended reading, or can offer an answer with details on why it's done that way, I'd really appreciate that.

A USER is a member of 1 or more GROUPS
A GROUP can have 1 or more ROLES
A ROLE can have 1 or more PERMISSIONS
A USER can only get PERMISSION by being assigned a ROLE with those PERMISSIONS.
Every GROUP will share common permissions based on what features and content types the application has made available
Different ROLES can share the same PERMISSIONS.

So it looks like I'll need a table for both ROLES and PERMISSIONS. Now I just need to find what the PERMISSIONS table would look like.

Let's start with GROUPS. In general, I use plural for table names unless there is a collective noun that works. You may have a different preference.

CREATE TABLE GROUPS
( GROUP_ID ... NOT NULL PRIMARY KEY
, ... ) ;

Since a user can be a member of several groups (and I assume that a group can contain more than 1 user), we need a n-n relationship. This is usually implemented via a junction table:

CREATE TABLE USERS
( USER_ID ... NOT NULL PRIMARY KEY
, ... ) ;

CREATE TABLE USER_GROUPS
( USER_ID ... NOT NULL
      REFERENCES USERS (USER_ID)
, GROUP_ID ... NOT NULL
      REFERENCES GROUPS (GROUP_ID)
,   PRIMARY KEY (USER_ID, GROUP_ID)
);

Next we have PERMISSIONS:

CREATE TABLE PERMISSIONS
( PERMISSION_ID ... NOT NULL PRIMARY KEY
, ...
);

Since a permission can exists for several roles, we once again use a n-n relatyionship table:

CREATE TABLE ROLES
( ROLE_ID ... NOT NULL PRIMARY KEY
, ...);

CREATE TABLE ROLE_PERMISSIONS
( ROLE_ID ... NOT NULL
      REFERENCES ROLES (ROLE_ID)
, PERMISSION_ID ... NOT NULL
      REFERENCES PERMISSIONS (PERMISSION_ID)
,   PRIMARY KEY (ROLE_ID, PERMISSION_ID)
);

Finally we can describe the relationship between groups and roles. If I got it right that is once again an n-n relationship:

CREATE TABLE GROUP_ROLES
( ROLE_ID ... NOT NULL
      REFERENCES ROLES (ROLE_ID)
, GROUP_ID ... NOT NULL
      REFERENCES GROUPS (GROUP_ID)
,   PRIMARY KEY (ROLE_ID, GROUP_ID)
);

This is of course just a sketch. I invented attribute names blindly, if there is an attribute name that exists in reality, use that..