Why do we still need VIP when using SCAN? When we are using Single Client Access Name (SCAN), the clients connect to the database through the SCAN name in DNS or GNS like in the example below:

stackdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.stack.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stackdb)
    )
  )

but why do we still need to specify a Virtual IP address (VIP) when configuring the clusterware? where is the VIP  used?

I know that before 11.2 we used the VIP like this

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL=tcp)(HOST=rac1-vip.localdomain)(PORT=1521))
      (ADDRESS=(PROTOCOL=tcp)(HOST=rac2-vip.localdomain)(PORT=1521)))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

Clients connect to the scan listener(s) listening on the scan vip(s). The scan listener forwards the request to the appropriate local listener listening on the node vip. So the node vip is still used for connecting.

The listener must run on the node you end up connecting to spawn your server process. The scan listener running on rac1 can not spawn a process on node rac2. You need a local listener that listens on a local address. That local address, even if it is just virtual and not fixed, is the node vip.

Overview of Virtual IP Addresses

  A typical connect attempt from a database client to an Oracle RAC
  database instance can be summarized, as follows:

  The database client connects to SCAN (which includes a SCAN VIP on a public network), providing the SCAN listener with a valid service
  name.
  The SCAN listener then determines which database instance hosts this service and routes the client to the local or node listener on
  the respective node.
  The node listener, listening on a node VIP and a given port, retrieves the connection request and connects the client to the an
  instance on the local node.

The original point of having the vip was to provide the ability of quickly returning a response in case of a node failure.

If rac1 goes down and a connection attempt is initiated using its address, the client must wait for a tcp timeout before getting any response. If the connection attempt is made using a virtual address, even if the vip is running on rac2 and it does not serve requests, the client gets an immediate response and can go and try another address listed in its connection string, thus making the overall time to connect lower.

Overview of Virtual IP Addresses

  If a node fails, then the VIP address fails over to another node on
  which the VIP address can accept TCP connections, but it does not
  accept connections to the Oracle database. Clients that attempt to
  connect to a VIP address not residing on its home node receive a rapid
  connection refused error instead of waiting for TCP connect timeout
  messages.

Also some old clients are unable to use SCAN.


SQL Full-text Filter Daemon Launcher with Startup set to Manual is Running after Bootstrap I have on my Windows 10 PC an instance of SQL Server 2017 Developer edition
All the services Startups are set to Manual
Today I started the PC and found in the SQL Server Configuration Manager that the SQL Full-text Filter Daemon Launcher was Running
I'd expect for it to be Stopped, since the startup mode is set to Manual.
Why? Is this a normal behavior I'm unaware of?

I found out the answer by myself.
I share it in case it might be useful for someone else
The default Shutdown setting for Windows 10 is "Fast Startup", that means that part of the running system processes are Hibernated instead than shut-down. This way at startup they are loaded in memory in the state they where when the PC was shut-down.
So long to the "turn it off and on again" to be working as expected

"Fast startup" can be disabled in the settings


Float datatype with 2 digits after decimal point In SQL server float datatype does not display trailing zeros after decimal point

declare @num as float
set @num=5.20
select @num

will dispaly only 5.2
But i want it to display as 5.20

After a little research i found that it can be solved by using

select CAST(@num AS numeric(10,2))

But this is not the solution i am expecting. Is there any method like setting datatype or changing datatype so that i can achieve the same?(It will be better if any variants are there in float which can do the same)

If it is possible then i need not change the SQL statement and include cast etc. So please help me on this.

This can be achieved with the decimal datatype.

See below for an example:

declare @num as float;
set @num=5.20;

select convert(decimal(10, 2), @num);

The output here will be 5.20.

After re-reading your question:

  But this is not the solution i am expecting. Is there any method like setting datatype or changing datatype so that i can achieve the same?

Is there a reason why you are specifically using float?  Oftentimes people tend to default to that datatype when decimal is more than sufficient.


Choose record based on varying condition I have a table that a primary key that can have one to two different status' associated with it. It looks like this:

Key Status
123 Book
123 Change
122 Book
121 Book
121 Change

If there is a record that has status of 'Change' then I only want to select that record and not the record with the same key that has a status of 'Book'.  If the key only has a status of 'Book'  then I always want to choose that record.  It is not possible to have a status of 'Change' without having a status of 'Book' first.

This is what I would like to see returned:

Key Status
123 Change
122 Book
121 Change

Any help?

SELECT Key, MAX(Status) FROM Table
GROUP BY Key

Nice and simple - works because Change is after Book when ordered by the MAX aggregate function.


How to increase the maximum number of control files? ORA-00208 number of control file names exceeds limit of num

  Cause:    An attempt was made to use more control files than Oracle
  supports. The limit is given in the message.

  num = 8.

I need to create 11 control files.

It is clearly documented that the parameter can not have more than 8 values.

CONTROL_FILES

  Range of values:  1 to 8 filenames


Connect to a Windows Authentication SQL server in SQL Server Authentication mode? Is it possible to connect to a SQL server in SQL Server Authentication if the server has been installed with Windows Authentication?

No. You'd need to be in "mixed mode" which is both SQL and Windows Authentication


Why can't I see my table (PostgreSQL) when I use \dt(+) inside psql? I have created the table donor in the schema reference as per:

CREATE TABLE reference.donor (
    donor_code smallint PRIMARY KEY,
    donor_name character varying NOT NULL,
    donor_type smallint REFERENCES reference.donor_type (type_id),
    alpha_2_code char(2) REFERENCES reference.iso_3166_1 (alpha_2_code)
);

I have populated the table as per:

INSERT INTO reference.donor (donor_code, donor_name, donor_type, alpha_2_code)
SELECT donor_code, donor_name, donor_type, alpha_2_code
FROM reference.donor_template;

When I run:

\dt+ reference.*

inside psql I see the reference.donor table:

                          List of relations
  Schema   |      Name      | Type  |  Owner   | Size  | Description
-----------+----------------+-------+----------+-------+-------------
 reference | donor          | table | postgres | 16 kB |
 reference | donor_template | table | postgres | 16 kB |
 reference | donor_type     | table | postgres | 16 kB |
 reference | iso_3166_1     | table | postgres | 48 kB |
(4 rows)

But when I run \dt+ donor* (or \dt(+)) I don't see the reference.donor table:

                          List of relations
  Schema   |      Name      | Type  |  Owner   | Size  | Description
-----------+----------------+-------+----------+-------+-------------
 oecd_cl   | donor          | table | postgres | 16 kB |
 reference | donor_template | table | postgres | 16 kB |
 reference | donor_type     | table | postgres | 16 kB |
(3 rows)

Why can I only see the reference.donor table if I run \dt+ reference.* or \dt+ *.donor?
I was expecting \dt (or \dt+) to display it, but it does not.

My search_path includes the schema reference & the user postgres has all permissions on the schema reference and all tables in the schema as per:

GRANT ALL ON ALL TABLES IN SCHEMA reference TO postgres;

Just to clarify, I have two donor tables, but they are in two different schemas i.e., oecd.donor & reference.donor. (I can see oecd.donor without any problems when I use \dt(+) inside psql).

The documentation on psql explains:

  Whenever the pattern parameter is omitted completely, the \d commands
  display all objects that are visible in the current schema search path
  — this is equivalent to using * as the pattern. (An object is said to
  be visible if its containing schema is in the search path and no
  object of the same kind and name appears earlier in the search path.
  This is equivalent to the statement that the object can be referenced
  by name without explicit schema qualification.) To see all objects in
  the database regardless of visibility, use *.* as the pattern.

Bold emphasis mine.
Obviously, you have oecd_cl before reference in your search path. Use this for your purpose:

\dt *.donor*

And you'll get:

                          List of relations
  Schema   |      Name      | Type  |  Owner   | Size  | Description
-----------+----------------+-------+----------+-------+-------------
 oecd_cl   | donor          | table | postgres | 16 kB |
 reference | donor          | table | postgres | 16 kB |
 reference | donor_template | table | postgres | 16 kB |
 reference | donor_type     | table | postgres | 16 kB |
(4 rows)


Is it possible to restore a database from 10.50.4033 in 10.00.1600 I did one project for my last year exam I want to restore a database.

Is it possible to restore a database from v10.50.4033 to 10.00.1600.

If yes, how is this possible.

Those look like MS SQL Server version numbers, not Oracle ones, 10.50.<patch_level> and 10.00.<patch_level> being 2008r2 and 2008 respectively.

2008 and 2008r2 are considered separate versions (much like 2008r2 and 2012) in most considerations including this one and backups are not backwardly compatible between versions, so no you can not directly restore a backup from 10.50.* on 10.00.* (though you can the other way around).

You will need to recreate the structure and then transfer the data.

If you have your structure in proper source control then remaking it should be easy, if not you can use the generate scripts function within SSMS and run the result on the older instance.

For the data there are numerous options and which will be suitable depends on may things, most importantly being can you connect to both database at once (in which case the data export/import in SSMS may be sufficient) and better still can they see each other over the network (in which case transferring using a linked server setup may be much more efficient). Listing every option available and their pros & cons would be time consuming (there are many sources out there that already do that), but if you update your question with further detail we can give more specific help.


Force create foreign key even if reference does not exist I have two tables foo and bar, bar has column a that references foo.id as a foreign key. When creating table bar before foo, the error column "a" referenced in foreign key constraint does not exist is given. This is obvious and intended. However, the creation of these two tables are handled by two different entities that of which I cannot control. It's guaranteed that no data will be added to either table until both are successfully created, but the creation itself may happen out-of-order. Is there a way to force the creation of bar even though foo does not exist?

As a quick fix. you can add a semaphore that prevents the creation of bar before foo exists. Pseudocode:

WHILE true:
    n = (select count(1)
         from information_schema.tables
         where table_name = 'foo')
    if n == 1:
        break
    sleep 1

create table bar ...

In the long run, consider how you should handle dependencies between different parts of the system, that are created independently of each other


Sybase ASE lock table to disallow users insert values I need to disallow users to insert values in some table, for example for a 5 mins. Seems I should use at isolation clause but can't figure out how to use it. Thanks

What operation(s) are you trying to perform on the table? Some DML? Maintenance (eg, reorg, (re)indexing, adding/changing RI)? Modifying DDL?

If just doing DML, consider:

begin tran
lock table <table_name> in exclusive mode
--perform DML--
commit (rollback?) tran

'lock table/exclusive mode' (attempts to obtain an exclusive table-level lock), once completed (may have to wait for other locks to be released before successfully obtaining the exclusive lock), means only your current session has access to the table (minus anyone using dirty reads).

Since the 'lock table' command is transactional in nature you need to wrap it inside a transaction hence the 'begin/commit tran'. [If not wrapped in the 'begin/commit tran' then the command would still obtain the exclusive table lock but then immediately release it.]

If you're looking to perform maintenance and/or DDL operations ... there's no easy way to limit access short of denying/revoking access to the table, database or dataserver.


SQL minimum permissions to manage any job I am looking for a way to grant minimum rights to a login to manage ANY SQL Server Agent job.  The SQLAgentOperatorRole allows to manage own jobs and disable/enable any job, however I need to allow the user to edit any job on the instance.  Is there a built-in msdb role that I am not seeing.  Obviously I dont want to grant the user SA rights on the instance and would like to avoid granting owner rights to MSDB.

Any advice is appreciated.

Unfortunately, the SQLAgentOperatorRole msdb role is the most privileged role for "managing" only jobs on a SQL server, and it only gives the users the ability to disable/enable jobs/schedules not owned by the user.

The only supported way for giving full access to all jobs AND manage them using SQL Server Management Studio is to add the users to the sysadmin server role.

The reason for this is the way the sp_update_job* procedures are written, they are actually checking role membership for access.  Excerpt from sp_update_job is below.  Note the comments and the explicit check for @x_owner_sid <> SUSER_SID() along with NOT being in sysadmin.

  -- Check permissions beyond what's checked by the sysjobs_view
  -- SQLAgentReader and SQLAgentOperator roles that can see all jobs
  -- cannot modify jobs they do not own
  IF ( (@x_owner_sid <> SUSER_SID())                  -- does not own the job
      AND (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)   -- is not sysadmin
      AND (@enable_only_used <> 1 OR ISNULL(IS_MEMBER(N'SQLAgentOperatorRole'), 0) <> 1))
  BEGIN
   RAISERROR(14525, -1, -1);
   RETURN(1) -- Failure
  END

  -- Check job category, only sysadmin can modify mutli-server jobs
  IF (EXISTS (SELECT * FROM msdb.dbo.syscategories WHERE (category_class = 1) -- Job
                                                     AND (category_type = 2) -- Multi-Server
                                                     AND (category_id = @x_category_id)
                                                     AND (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1))) -- is not sysadmin
  BEGIN
     RAISERROR(14396, -1, -1);
     RETURN(1) -- Failure
  END

One alternative/workaround if you can live without SSMS management, is to utilize certificates for access to the underlying sp_update_job* procs, and then create wrapper procedures (which is why you cannot use SSMS still).  For a full example, see Uwe Ricken db Berater GmbH response in this MSDN post.


Can I be automatically notified of prolonged blocking in SQL server? About once a week I have to resolve a blocking chain on a SQL Server 2005 database, caused by a long-lived read lock from an Access 2003 front-end. The lock is taken out whenever a user opens a certain form and is released once the user has finished scrolling through the form or closes it. Since many of our users open this form as reference, these locks stay around for a while. Any update to the table causes the blocking, and suddenly nobody can select from this table since they're all waiting on the first lock. This is quite a problem for us, since lots of apps rely on this data. I understand that this locking behavior is part of how Access works with linked tables.

I've been solving the problem from Activity Monitor, by killing whichever SELECT process is the Head Blocker whenever I find out about it. This is a problem not only because it takes me time to do it manually, but also because it's reactive. By the time I hear about it, it's already been a problem for a lot of people.

I'd like to know if there is an automatic way to check for these long-lasting blocking chains, and either be emailed or have the problem resolved automatically. The logic seems straightforward enough ("if any process matching this SELECT query has been blocking for longer than a minute, notify me/kill it") but I don't know how to implement this with SQL Server.

For what it's worth, I think the proper solution is to fix or rewrite the app. However, due to departmental politics, this is not an option for the next few months, so I'm looking for a stopgap.

Have you considered using snapshot isolation? Enabling read_committed_snapshot in the database will cause all reads (selects) to be lock free:

alter database [...] set read_committed_snapshot on;

No application changes. Some semantics change under snapshot and your application may react weirdly, but that is the exception not the norm. The vast majority of applications don't notice any difference, they just get a free performance boost.

Anyway, I though to answer also the original question: how to detect (and possibly kill) a long running query. Actually, the engine already does that for you. There is an event raised when a threshold is passed: Blocked Process Report Event Class. The threshold is configured via the blocked process threshold Option. Any trace event can be turned into an Event Notification and event notifications can activate procedures. Connect the dots and you have on-demand activated code that runs when the engine detects a query that has crossed an execution time threshold. No polling, no monitoring. Note though that the notification is asynchronous, by the time you process it the query may had completed so that has to be taken into account.

Here is an example:

use msdb;
go

create queue blocked_process_report_queue;
go

create service blocked_process_report_service
    on queue blocked_process_report_queue
    ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

create event notification blocked_process_report_notification
    on server
    for BLOCKED_PROCESS_REPORT
    to service N'blocked_process_report_service',
          N'current database';
go

sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
sp_configure 'blocked process threshold', 20 ;
GO
RECONFIGURE ;

Now in a new query set up a WAITFOR expecting a notification:

use msdb;
waitfor(
   receive cast(message_body as xml), *
   from  blocked_process_report_queue);

And go ahead and cause some blockage. I used a process that created a table and didn't commit, and from another query windows I tried to select from the table. In 20 seconds (my configured threshold above) I got the blocking report:

<blocked-process-report>
  <blocked-process>
    <process id="process1b5a24ca8" ...>
      <executionStack>
        <frame line="1" stmtstart="-1"... />
      </executionStack>
      <inputbuf>
          select * from t   </inputbuf>
    </process>
  </blocked-process>
  <blocking-process>
    <process status="sleeping" spid="51" ...>
      <executionStack />
      <inputbuf>
         begin transaction
         create table t (a int)   </inputbuf>
    </process>
  </blocking-process>
</blocked-process-report>

I'll leave the task of wrapping this up into an automated process as an exercise to the reader. And yes, the queue/service/activated procedure must be in [msdb].


SELECT DISTINCT on one column, while returning the other columns? I have a query which uses three lookup tables to get all the information I need.  I need to have DISTINCT values for one column, however I also need the rest of the data associated with it.

My SQL code:

SELECT acss_lookup.ID AS acss_lookupID,
   acss_lookup.product_lookupID AS acssproduct_lookupID,
   acss_lookup.region_lookupID AS acssregion_lookupID,
   acss_lookup.document_lookupID AS acssdocument_lookupID,
   product.ID AS product_ID,
   product.parent_productID AS productparent_product_ID,
   product.label AS product_label,
   product.displayheading AS product_displayheading,
   product.displayorder AS product_displayorder,
   product.display AS product_display,
   product.ignorenewupdate AS product_ignorenewupdate,
   product.directlink AS product_directlink,
   product.directlinkURL AS product_directlinkURL,
   product.shortdescription AS product_shortdescription,
   product.logo AS product_logo,
   product.thumbnail AS product_thumbnail,
   product.content AS product_content,
   product.pdf AS product_pdf,
   product.language_lookupID AS product_language_lookupID,
   document.ID AS document_ID,
   document.shortdescription AS document_shortdescription,
   document.language_lookupID AS document_language_lookupID,
   document.document_note AS document_document_note,
   document.displayheading AS document_displayheading
FROM acss_lookup
     INNER JOIN product ON (acss_lookup.product_lookupID = product.ID)
     INNER JOIN document ON (acss_lookup.document_lookupID = document.ID)
ORDER BY product_displayheading ASC;

I want to get all the products from this query but I only want to get them once because I'm populating a drop down menu for a search application.  I want the user to be able to select from the products that are in that table (that's why I only need them once).

Is this too complicated?  Should I use a more simplified approach?

One more approach not mentioned yet is to use window functions, for instance row_number:

   SELECT * FROM
   (
   SELECT acss_lookup.ID AS acss_lookupID,
   ROW_NUMBER() OVER
   (PARTITION BY your_distinct_column ORDER BY any_column_you_think_is_appropriate)
   as num,
   acss_lookup.product_lookupID AS acssproduct_lookupID,
   acss_lookup.region_lookupID AS acssregion_lookupID,
   acss_lookup.document_lookupID AS acssdocument_lookupID,
   product.ID AS product_ID,
   product.parent_productID AS productparent_product_ID,
   product.label AS product_label,
   product.displayheading AS product_displayheading,
   product.displayorder AS product_displayorder,
   product.display AS product_display,
   product.ignorenewupdate AS product_ignorenewupdate,
   product.directlink AS product_directlink,
   product.directlinkURL AS product_directlinkURL,
   product.shortdescription AS product_shortdescription,
   product.logo AS product_logo,
   product.thumbnail AS product_thumbnail,
   product.content AS product_content,
   product.pdf AS product_pdf,
   product.language_lookupID AS product_language_lookupID,
   document.ID AS document_ID,
   document.shortdescription AS document_shortdescription,
   document.language_lookupID AS document_language_lookupID,
   document.document_note AS document_document_note,
   document.displayheading AS document_displayheading
   FROM acss_lookup
     INNER JOIN product ON (acss_lookup.product_lookupID = product.ID)
     INNER JOIN document ON (acss_lookup.document_lookupID = document.ID)
   )a
   WHERE a.num = 1
   ORDER BY product_displayheading ASC;


PostgreSQL claims to support 170 of 179 SQL:2016 features, but it also lists 309 features of SQL:2016 that it doesn't support. Why the discrepancy? As of September 2020, PostgreSQL claims to support most of the SQL:2016 standard, but the numbers in don't match up at all. From PostgreSQL's documentation:

As of the version 13 release in September 2020, PostgreSQL conforms to at least 170 of the 179 mandatory features for SQL:2016 Core conformance.
(emphasis mine)

So 9 features are unsupported. But elsewhere, PostgreSQL lists all the features of SQL:2016 that it doesn't support, and there's 309 features listed. This is more unsupported features than the first page claimed even exist in SQL:2016. Granted, some of the unsupported features are irrelevant, like "Embedded Ada" or "Module Language COBOL", but either way, there's way more than 9 unsupported features listed here. And I don't think that SQL:2016 changed so much (or even at all) between 2020 and 2021, so that can't be the reason for the mismatch.
I thought that individual unsupported features might be grouped together into 9 "features" somehow, e.g. all features involving support for dead languages are counted as one feature, and all XML-related features are counted as one feature, etc., but after skimming through the list of 309 unsupported features, I don't see any clear way to do this.
So, why the discrepancy? Is it because of individual features being grouped together, or is it something else?

Features in the SQL standard are split between mandatory features and optional features.
As stated here

Out of 177 mandatory features required for full Core conformance, PostgreSQL conforms to at least 170. In addition, there is a long list of supported optional features

(emphasis mine)
So the discrepancy you are seeing, stems from the difference between mandatory and optional features.


MySQL - How to check for a value in all columns I am curious, is there a good way to search all columns for a given value? For my purposes, it doesn't need to be at all fast, it's just a 1-off kinda thing, and I don't really want to have to type out every field name. That's precisely what I'll be doing for now, but I think surely there's a better way.

I would like to turn this:

SELECT * FROM table WHERE col1 = 'val' OR col2 = 'val' OR col3 = 'val';

into this:

SELECT * FROM table WHERE * = 'val'

...or, even better (though I seriously doubt it...)

SELECT * FROM table WHERE * like '%val%'

I found this, which seems kinda-not-really close, but I'm not finding anything closer:

SELECT whatever WHERE col1,col2 IN ((val1, val2), (val1, val2), ...)

Difference being, that searches a selection of columns for the specified values, whereas I'm trying to search ALL columns for a single value.

It's not important though, like I said more than anything I'm just curious

SQL doesn't provide a good way of doing this because it's probably not a good table design to have N columns that might have values in the same domain. This is potentially a case of "repeating groups" like if you have columns phone1, phone2, phone3, ... phoneN.

If you have a number of columns that have the same logical domain of values, it could be a case where it's a multi-valued attribute (like the phones example). The standard way of storing multi-valued attributes is as multiple rows in another table, with a reference to the row they belong to in the primary table.

If you do that, then you only have to search for the specific value in one column of the dependent table.

SELECT t.* FROM mytable AS t
JOIN phones AS p ON t.primaryKey = p.refKey
WHERE p.phone = ?


Are these indexes redundant? I have two indexes on a table that will contain roughly 10-20 million records at a given moment.

Index 1

CREATE NONCLUSTERED INDEX [IX_NC_AccountNumber_UTC] ON [dbo].[LogWindow]
(
    [AccountNumber] ASC,
    [UTC] ASC
)
INCLUDE (   [Site],
    [Application],
    [Version],
    [Machine],
    [Context],
    [Severity],
    [Message],
    [Details]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Index 2

CREATE NONCLUSTERED INDEX [IX_NC_AccountNumber_UTC_Site_Context_Machine] ON [dbo].[LogWindow]
(
    [AccountNumber] ASC,
    [UTC] ASC,
    [Site] ASC,
    [Context] ASC,
    [Machine] ASC
)
INCLUDE (   [Application],
    [Version],
    [Severity],
    [Message],
    [Details],
    [Type]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

The reason why I am currently using two indexes is because I am issuing two types of queries.  The first query only filters records based upon AccountNumber and UTC.  The second query filters records based upon those same columns, plus the additional Site, Context and Machine columns.

The second index is very similar to the first; it's just covering less columns because now they are part of the key indexed columns.

Does it make sense for me to have the first index when the second one is so similar with additional key columns?  Will SQL Server determine there is a benefit to using the first index over the second?

They are redundant. Second index should be usable by any query that would be using the first one.


Failing to insert into table getting error: ORA-00600: internal error code, arguments: [ktssinseg2] I am facing problems inserting records into my oracle database 12C R2. I have no Oracle Support. All this time I was able to do inserts into my database, but all of a sudden it is now failing. Below is the query and error:

SQL> INSERT INTO MV_IN_SMS_TOTAL SELECT * FROM mv_in_sms_total@newdwhlink.com WHERE START_DATE = '20191101';
INSERT INTO MV_IN_SMS_TOTAL SELECT * FROM mv_in_sms_total@newdwhlink.com WHERE START_DATE = '20191101'
                                   *
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktssinseg2], [60], [7],
[289598865], [], [], [], [], [], [], [], []

You could try to copy the table into a new object:

CREATE TABLE newtab AS SELECT * FROM MV_IN_SMS_TOTAL

Try to do your inserts into the new table. If that solves the problem and the problem is isolated to that specific object (table), and you use that as a workaround.

RENAME TABLE MV_IN_SMS_TOTAL TO MV_IN_SMS_TOTAL_OLD;
CREATE TABLE MV_IN_SMS_TOTAL AS SELECT * FROM MV_IN_SMS_TOTAL_OLD;

Note that all auxiliary objects must be created. Indexes, constraints, triggers, you name it - and privileges.

Regards,

Daniel


Why and for what possible reasons 'sa' gets locked? Last night all of our systems failed and connections to the database kept dropping. We watched the log today and found out that this was the message:

  Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it.  (Microsoft SQL Server, Error: 18486)

Of course it can be solved in a minute of Googling around, via some tricks and scripts.

However, the more we search the less we find out about the reason behind this. Why it has happened in the first place at all? What possible reasons caused it, and might cause it again? Was it because of an attack? We have no clue, and any help is appreciated.

Update: in this post it's been argued that a policy check can make this happen. We haven't set a policy so far, and we do not even know where are the policies.

Update2: We realized that some of the threads in some applications kept working, even though other threads and other applications were encountering sa is locked message. Could it be that connection pooling doesn't require re-authentication? How is that possible?

When you look at the General tab in the Login Properties of the sa account, you will notice different settings. One of them being Enforce password policy.

I discussed this setting in my answer to the question How to lock a sql login after N unsuccessful login attempts.

Possible reasons for change in lockout behaviour

If you previously didn't have an Active Directory policy for locking out accounts, then  ...

... the user's inability to use the correct password had no adverse effect on the sa account (No locking of sa account)
... the hacker was able to enter multiple passwords for the sa SQL Login until he had access. (No locking, but hacker in system)
... it didn't matter if any script, program, user, system used a wrong password for the sa account.

If you changed the Active Directory policy recently to lock accounts after n wrong password attempts or if you reduced the number of wrong password attempt to cause a lockout, then you might be encountering a locked sa account due to ...

... the user using the sa account is unable to enter the correct password after n attempts, which results in a lock out.
... the hacker is unable to gain access again, which results in a lockout.
... somebody is just having fun locking out the sa account.

Recommended actions

Change the password for the sa account to ensure nobody gains access to the server via this account.
Ensure that the option Failed logins only is enabled at server level in the in the Server properties - xyz | Security tab | Login auditing section . (Better: If you have enough space for your ERRORLOG and are cycling the ERRORLOG at a regular interval, set it to Both failed and successful logins.)

Check the SQL Server ERRORLOG file for login issues and track down who is behind the IP address in the error message.

2017-07-21 09:58:12.48 Logon       Error: 18456, Severity: 14, State: 8.
2017-07-21 09:58:12.48 Logon       Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: ]

this example was from the same host, hence no IP address


Get parent/child or parent/siblings depending on the ID passed This is similar to my table:

| id | parent_id | title        |
|----|-----------|--------------|
| 61 | NULL      | title of 61  |
| 62 | 61        | another title|
| 68 | 61        | more text    |
| 75 | 61        | testing 123  |

Let's say I select ID 61. I want this output (all children of parent):

| id | title        |
|----|--------------|
| 62 | another title|
| 68 | more text    |
| 75 | testing 123  |

so, i use the following mysql:

SELECT a2.id, a2.title
FROM article a
JOIN article a2 ON a2.parent_id = a.id
WHERE a.id = 61

...so far so good...

Let's say I select ID 75, I want this output (parent and siblings):

| id | title        |
|----|--------------|
| 61 | title of 61  |
| 62 | another title|
| 68 | more text    |

so, i use this query:

SELECT a2.id, a2.title
FROM article a
JOIN article a2 ON COALESCE(a2.parent_id, a2.id) = a.parent_id
WHERE a.id = 75 AND a2.id != 75

Is there a way to create a combination of those 2 queries so whatever ID i pass, it will output correctly...

NOTE:
English is not my first language, please feel free to ask questions if its not clear

Your first query can be simplified to:

SELECT a2.id, a2.title
FROM article a2
WHERE a2.parentid = 61

To combine this with your second query you can use union:

SELECT a2.id, a2.title
FROM article a
JOIN article a2
    ON COALESCE(a2.parent_id, a2.id) = a.parent_id
WHERE a.id = 61 AND a2.id <> 61

UNION

SELECT a2.id, a2.title
FROM article a2
WHERE a2.parentid = 61;


Tool to synchronize PostgreSQL DDL and data databases We have several developers who each have a local database for testing. We often make DDL and/or data changes to the master database that we need to replicate to each development machine. The framework we use allows us to use the concept of migrations, which cover 95% of the use cases. However, in some cases we are in need of a simple refresh.

The procedure now is to dump the schema on the master, copy it to each developer's local machine, drop the schema from their database, and reload it from the dump. This is slow, and we would like to be able to compare the two databases more directly and only transfer/execute what has changed. Most tools that I've found (such as SLONY) don't handle DDL changes because they rely on triggers. I found this product: http://www.datanamic.com/schemadiff-for-postgresql/index.html, but it seems to be built for a Windows GUI only.

Does anyone have any suggestions?

Here are some tools that have been around for a while, though I have not used any of them:

https://github.com/jmafc/Pyrseas
http://www.andromeda-project.org/
https://labs.omniti.com/labs/pgtreats/wiki/getddl

I have a feeling there is at least one more floating around, but a quick search didn't trigger any other links for me.


update variable from multiple tables in one row I have two databases, and I need to UPDATE variable 'birthday' from one row to another checking 'name' (from db1 to db2).

The problem is, that 'name' variable in second db is located in another table (table2).
It is possible to do this without changing DB structure using 'id' variable from db2 to synchronizing?

UPDATE `db1`.`table1`, `db2`.`table2` SET `db2`.`table1`.`birthday` = `db1`.`table1`.`birthday` WHERE `db2`.`table1`.`name` ...

Scheme:

 db1-> table1 -> name,birthday
 db2-> table1 -> name,id
       table2 -> birthday,id

If your db2.table1 and db2.table2 tables are joined by some key field and the name field in both databases is unique, you can use the following query to update your data:

UPDATE `db1`.`table1` t SET
t.`birthday` =
(SELECT t2.`birthday` FROM `db2`.`table1` t1 JOIN
`db2`.`table2` t2 ON (t1.`key` = t2.`key`)
WHERE t.`name` = t1.`name`)

This should work if you need to update db2 -> db1. If you need to update db1 -> db2, the query can be changed as follows:

UPDATE `db2`.`table2` t2 SET t2.`birthday` =
(SELECT t.`birthday` FROM `db1`.`table1` t  WHERE t.`name` =
(SELECT t1.`name` FROM `db2`.`table1` t1 WHERE t1.`key` = t2.`key`))


False positive error message in MS Access (concurrent users) I have discovered a strange apparent bug in MS Access and would like to know if others know it already and hopefully how to get around it.

Trying to count records based on known column categories.

One particular set of records can't be saved or output because of apparently one or more records in that range. They appear to have some corrupt character which is generating a false error message:

"Another user is currently using this record" or words to that effect.

Is there some obscure character (combination) that can cause Access to lock a record? It is physically impossible that anyone else actually has access to the DB.

There could conceivably be all kinds of strange characters in there because the data come from a wide variety of sources.

I think I have narrowed it down to a personal name field but this is not a final verdict...

"Another user is currently using this record" is a misleading error message Access loves to throw out - many times it really indicates you have the table open in another window.


How to sync logins and jobs when using an availability group? So I am configuring availability group which will sync the databases from primary replica to the secondary replicas.
How to sync the jobs and logins so that when there is failover to secondary then the logins and jobs can continue working on the secondary?

I have to credit David Browne for this one (I learned from one of his recent answers), but one thing you can do is setup a Linked Server between the your Primary and Secondary Replicas. Then you can create a SQL Agent Job on the Primary Replica that generates the scripts for the Logins and other Jobs and executes them on the Secondary via the Linked Server. You can even have the Job synchronize itself to the other servers as well. It of course should first check if the objects you're synchronizing already exist and take the appropriate action when they do.
You might find this article Synchronize logins between Availability replicas in SQL Server Always On Availability Groups helpful, especially the Use stored procedure sp_help_revlogin and sp_hexadecimal (which references this Microsoft BOL) section which discusses a Microsoft stored procedure you can utilize to transfer Logins between servers as well. You probably can simplify the job above by having it leverage this procedure.
As far as synchronizing the other Jobs, you might be able to do so with the meta-data stored in the the msdb Jobs tables, e.g. sysjobs, or this StackOverflow answer seems to have found a way via T-SQL. You might also find the Microsoft article Sync Logins and Jobs helpful as well.
Finally, depending on how many Secondary Replicas you are planning to setup, or if you have an affinity to SSIS, you might find it cleaner and more manageable to create an SSIS Package that does all of this (which is also discussed in the first article above) as opposed to using Linked Servers.
Either way, this is a moderate difficulty task to setup, but definitely accomplishable once you start diving into it.


Unable to run job - Subquery returned more than 1 value I'm trying to run the following job:

IF (SELECT ars.role_desc
    FROM sys.dm_hadr_availability_replica_states ars
    INNER JOIN sys.availability_groups ag
    ON ars.group_id = ag.group_id
    AND ars.is_local = 1) <> 'PRIMARY'
BEGIN
   --We're on the secondary node, throw an error
   THROW 50001, 'Unable to execute job on secondary node',1
END

But end up getting the error:

  Subquery returned more than 1 value. This is not permitted when the
  subquery follows =, !=, <, <= , >, >= or when the subquery is used as
  an expression. [SQLSTATE 21000] (Error 512).  The step failed.

I have this working on another sql server but on this specific I get this error message and can't really figure out why. I'm new to SQL so would appreciate some help. The two servers don't differ in sql version.

Here's the result from the working SQL job:

  Message Executed as user: VT\sqladmin-internaltes. Unable to execute
  job on secondary node [SQLSTATE 42000] (Error 50001).  The step
  failed.

You must have more than one availability group in the instance as mentioned in the comment. I suggest you use this function. Details here.

If sys.fn_hadr_is_primary_replica ( @dbname ) <> 1
BEGIN
-- If this is not the primary replica, exit (probably without error).
END
-- If this is the primary replica, continue to do the backup.

You can run this query to check how many Availability group you have.

SELECT count(*) AS [NumOfAG]
FROM sys.availability_groups


Why mongod.service is killed by my system? I am using MongoDB 4.4 version on ubuntu 20.4, using 2 mongo instances on the same server. I encountered this problem:
Sep 03 10:45:25 mlnode systemd[1]: Stopping MongoDB Database Server...
Sep 03 10:45:25 mlnode systemd[1]: mongod.service: Succeeded.
Sep 03 10:45:25 mlnode systemd[1]: Stopped MongoDB Database Server.
Sep 03 10:45:25 mlnode systemd[1]: Started MongoDB Database Server.
Sep 03 10:55:46 mlnode systemd[1]: Stopping MongoDB Database Server...
Sep 03 10:55:46 mlnode systemd[1]: mongod.service: Succeeded.
Sep 03 10:55:46 mlnode systemd[1]: Stopped MongoDB Database Server.
Sep 03 10:55:46 mlnode systemd[1]: Started MongoDB Database Server.
Sep 03 11:05:05 mlnode systemd[1]: Stopping MongoDB Database Server...
Sep 03 11:05:05 mlnode systemd[1]: mongod.service: Succeeded.
Sep 03 11:05:05 mlnode systemd[1]: Stopped MongoDB Database Server.
Sep 03 11:05:05 mlnode systemd[1]: Started MongoDB Database Server.
Sep 03 11:13:20 mlnode systemd[1]: /lib/systemd/system/mongod.service:11: PIDFile= references a path below legacy directory /var/run/, updating /var/run/mongodb/mongod.pid → /run/mongodb/mongod.pid; please update the unit file accordingly.
Sep 03 11:25:47 mlnode systemd[1]: /lib/systemd/system/mongod.service:11: PIDFile= references a path below legacy directory /var/run/, updating /var/run/mongodb/mongod.pid → /run/mongodb/mongod.pid; please update the unit file accordingly.
Sep 03 11:56:47 mlnode systemd[1]: mongod.service: Main process exited, code=killed, status=6/ABRT
Sep 03 11:56:47 mlnode systemd[1]: mongod.service: Failed with result 'signal'

Wondering why the system is killing mongod.service, I don't have any clue. Then I checked my mongo logs:
{"t":{"$date":"2020-09-03T11:56:46.379+05:30"},"s":"F",  "c":"-",        "id":23083,   "ctx":"conn10155","msg":"Invariant failure","attr":{"expr":"ret","error":"UnknownError: -31803: WT_NOTFOUND: item not found","file":"src/mongo/db/storage/wiredtiger/wiredtiger_record_store.cpp","line":1598}}
{"t":{"$date":"2020-09-03T11:56:46.379+05:30"},"s":"F",  "c":"-",        "id":23084,   "ctx":"conn10155","msg":"\n\n***aborting after invariant() failure\n\n"}
{"t":{"$date":"2020-09-03T11:56:46.379+05:30"},"s":"F",  "c":"CONTROL",  "id":4757800, "ctx":"conn10155","msg":"Writing fatal message","attr":{"message":"Got signal: 6 (Aborted).\n"}}

Systemd is changing the PIDFile path at startup to /run/mongodb/mongod.pid.  However, the mongod doesn't consult the unit file, it uses its own config, usually /etc/mongod.conf.
Update both the unit file and the mongod.conf to use "/run/mongodb/mongod.pid" as the PIDFile.
Since mongod forks, systemd is expecting to see the pidfile created so that it knows the process has started.  That would use signal 15, it isn't the main problem, though.
Signal 6 occurs when the mongod itself calls abort.
The error calls out line 1598, which is:
Status WiredTigerRecordStore::updateRecord(OperationContext* opCtx,
                                           const RecordId& id,
                                           const char* data,
                                           int len) {
    dassert(opCtx->lockState()->isWriteLocked());
    invariant(opCtx->lockState()->inAWriteUnitOfWork() || opCtx->lockState()->isNoop());

    WiredTigerCursor curwrap(_uri, _tableId, true, opCtx);
    curwrap.assertInActiveTxn();
    WT_CURSOR* c = curwrap.get();
    invariant(c);
    setKey(c, id);
    int ret = wiredTigerPrepareConflictRetry(opCtx, [&] { return c->search(c); });
    invariantWTOK(ret);  <<--- Line 1598

The WiredTiger docs provide an description of WT_NOTFOUND:

#define WT_NOTFOUND
Item not found.
This error indicates an operation did not find a value to return. This includes cursor search and other operations where no record matched the cursor's search key such as WT_CURSOR::update or WT_CURSOR::remove.

The provided 3 log lines unfortunately don't give us any real clues as to what wasn't found.  It could have been a missing or corrupt data file, a damaged entry in the journal, or a truncated record.
What I suspect happened is the PIDFile mismatch led to systemd causing an unclean shutdown that left one or more files corrupt.
To fix this:

fix the PIDFile configuration in both the unit file and the mongod.conf so this doesn't happen again
make a backup of the dbpath
remove all of the files in the dbpath
attempt to start mongodb

If it is able to startup with the corrected PIDFile settings and a clean data directory, you can decide from there whether to restore the backup and attempt to repair the corrupt files, or just start over with an empty db.


Main Purpose of FOREIGN KEY When I did my first interview, I faced this confusing question.
By using join we can join the tables, but what is the main purpose of FOREIGN KEY in mysql?

Please suggest me the best answer.

The primary purpose of FOREIGN KEYs is to maintain Referential Integrity (RI). Check out any of the standard textbooks (Connolly, Elmasri, Garcia-Molina or Silberschatz amongst others).

This fundamental concept endures despite users' and programmers' best efforts to produce systems which render the concept null and void - by that I mean that they frequently try and subvert these (reasonable) requirements of a decent database system. See here for further details on that matter.

As explained in the Wiki, RI is

  a property of data which, when satisfied, requires every value of one
  attribute (column) of a relation (table) to exist as a value of
  another attribute (column) in a different (or the same) relation
  (table).

To give a simple example, that means that if you have a purchase order system with an order_header table with fields as follows:

CREATE TABLE order_header
(
  order_id INTEGER,
  client_id INTEGER,
  order_date DATE,
  <...other fields depending on your individual requirements...>
);

You might then have a client table like this

CREATE TABLE client
(
  client_id INTEGER,
  client_name VARCHAR(50),
  client_address VARCHAR(50),
  <.... other fields depending on your requirements...>
);

You would then add a CONSTRAINT to your order_header table with something like this:

ALTER TABLE order_header
    ADD CONSTRAINT fk_header_client
    FOREIGN KEY (client_id)
    REFERENCES client (client_id);
-- the exact syntax may differ per RDBMS.

This is telling the RDBMS that on the client_id column of the order_header table, there is now a constraint such that any client_id in order_header must already exist in the client table.

The referenced FOREIGN KEY columns(s) on the parent table need to have a UNIQUE constraint (or be the PRIMARY KEY).

Otherwise, it wouldn't make much sense - if there were 2 clients with the same client_id, how would the system be able to tell them apart - what address do you send the order to?

Note also that with many systems, you can create the CONSTRAINT in the CREATE TABLE statement.

Now, your system will be unable to create a purchase order for a client which does not exist. This doesn't mean that the wrong client can't be entered into the system, but it does help to ensure that the system is internally consistent.

Many optimizers can also take advantage of some optimizations when there are FOREIGN KEY constraints on the joined tables and produce better (more efficient) and simpler query plans.

Finally, a further advantage of using FOREIGN KEYs is that some systems also can (depending on your RDBMS) automatically create INDEXes on the FOREIGN KEY columns.


Oracle: Output not shown even if SERVEROUTPUT variable set to ON I was wondering why the output is not being shown even if I set the SERVEROUPUT variable to ON.

I have the following annonymous block code:

DECLARE
    v_rec_count number:=0;
BEGIN
    for rec in (SELECT T_A.ROWID, T_A.OPEN_COUNT, T_B.OPEN_COUNT as NEW_OPEN_COUNT
            FROM T_A
            INNER JOIN T_B ON
                T_A.KEYA = T_B.KEYA
            AND T_A.KEYB = T_B.KEYB
            AND T_A.OPEN_COUNT <> T_B.OPEN_COUNT)
    loop
        v_rec_count := v_rec_count + 1;

        update T_A t1
        set t1.OPEN_COUNT = rec.NEW_OPEN_COUNT,
            t1.UPD_USER_ID = 'batch',
            t1.UPD_DATE = SYSDATE
        where t1.rowid = rec.rowid;
    end loop;

    dbms_output.put_line ('T_A UPDATE_TOTAL: ' || v_rec_count);
END;
/

When I execute the following code, I would like to see the output of dbms_output.put_line to the sql prompt and eventually to the log file when I run the sql file via sqlplus. The output that the block does is just the "PL/SQL Procedure has successfully run".

I already set SET SERVEROUPUT ON before the block but it does not work. Any ideas?

As you can see from the output below, a test anonymous PL/SQL block works in SQL Plus as expected:

[oracle@node1 ~]$ sqlplus phil/phil

SQL*Plus: Release 11.2.0.2.0 Production on Mon Oct 1 21:55:35 2012

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set serveroutput on
SQL> DECLARE
  2      v_rec_count number;
  3  BEGIN
  4      select count(*) into v_rec_count from dual;
  5      dbms_output.put_line ('count: ' || v_rec_count);
  6  END;
  7  /
count: 1

PL/SQL procedure successfully completed.

SQL>

I'd guess that you're not using SQL Plus to test & are testing in another tool (such as SQL Developer) and it's not set DBMS_OUTPUT.enable() or you haven't configured the tool to correctly display the output from the RDBMS.

Or, it could be the fact that SET SERVEROUPUT ON is a typo :)


Problem with foreign keys I do

ALTER TABLE bdt.auth
    ADD CONSTRAINT `depositor no.`
      FOREIGN KEY ( `depositor no.` )
         REFERENCES bdt.depositors( `depositor no.` )
            ON DELETE CASCADE
            ON UPDATE CASCADE

I get

  Can't create table 'bdt.#sql-881_140' (errno: 121)

Fields are identical
There is no defined constraints:

SELECT
   constraint_name,
   table_name
FROM
   information_schema.table_constraints
WHERE
   constraint_type = 'FOREIGN KEY'
AND table_schema = DATABASE()
ORDER BY
   constraint_name;

Result

  Empty set (0.01 sec)

The error is that you're trying to use a constraint that is already identified.

SELECT
   constraint_name,
   table_name
FROM
   information_schema.table_constraints
WHERE
   constraint_type = 'FOREIGN KEY'
AND table_schema = DATABASE()
ORDER BY
   constraint_name;

Should give you the constraints you've already established. Also, you can use SHOW ENGINE INNODB STATUS or SHOW INNODB STATUS after you get the error to get the logs and more information.


Executing delayed inserts with MySQL event scheduler A table that I'm working on, has two triggers; one is after insert and the other is before delete. These triggers carry out calculations and copy over some rows to a backup table. This backup table is to be used in the application for showing data, so data can be delayed, but this table should be available in the db for JOINs etc.

Now if a large quantity of insert's/delete's are carried out on the primary table, the triggers slow the entire application down. I've thought about creating a sql-queue table, and using the triggers to dump any SQL query that needs to run, in the backup table.

Then, I'd create an MySQL event that checks this sql-queue table every 'x' seconds for queries to execute. At least in this manner, the user using the application is not blocked waiting for the triggers to execute and insert data into the backup table.

Is this approach recommended?

I think you need to start over in the processing of your data.  I don't think TRIGGERs are an efficient way to achieve the goals.

Here's a thought:

Put information to be INSERTED/DELETED into a couple of tables.  Periodically, batch process the entire tables to do the calculations and do the inserts and deletes.  Batch processing is a lot faster than one-row-at-a-time.

Furthermore, I would set up pairs of such tables.  Various clients would be inserting into one set, while another job is doing the processing described above.  When finished, the processed tables would be cleared, then swapped with the tables being inserted into.

This discusses the details for doing the "inserts", together with associated extra work ('normalization', in the blog).  Deletes would work similarly.


MySQL performance impact of increasing innodb_buffer_pool_size I have two databases one of size 170 MB and second one is of 15 MB, both having INNODB tables and innodb_buffer_pool_size is set to 8 MB.

My queries are responding well now i.e under mili seconds.
Is there any impact on performance if I set innodb_buffer_pool_size more than the default?

If you want to size the innodb_buffer_pool_size bigger please run this:

SELECT CEILING(SUM(data_length+index_length)/POWER(1024,2)) RIBPS
FROM information_schema.tables WHERE engine='InnoDB';

This will give you the maximum amount of InnoDB Buffer Pool needed for your current data set.

Given the amount of data and index pages for your dataset, having 8MB Buffer Pool will just cause data and index pages accessed to rotate out and load new pages as needed. Setting it with the value based on that RIBPS query (RIBPS stands for Recommended InnoDB Buffer Pool Size), all of InnoDB will be accessed from RAM. Having the Buffer Pool too big would just wastes RAM.

Let's say that you ran this query and got the following:

mysql> SELECT CEILING(SUM(data_length+index_length)/POWER(1024,2)) RIBPS
    -> FROM information_schema.tables WHERE engine='InnoDB';
+-------+
| RIBPS |
+-------+
|  1493 |
+-------+

Take that number and place it in /etc/my.cnf

[mysqld]
innodb_buffer_pool_size=1493M

and restart mysql.

I have discussed this before

Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?
Jan 11, 2012 : MySQL said "InnoDB: Initializing buffer pool, size = 128.0M", does it mean it is using 128MB RAM?
Jun 13, 2012 : What to set innodb_buffer_pool and why..?
Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?

Give it a Try !!!


What happens when an automatically generated UUID primary key collides in Postgres? If I have a PostgreSQL table with a column defined as follows, where gen_random_uuid() is from extension pgcrypto.

id UUID PRIMARY KEY DEFAULT gen_random_uuid()

Upon each insert where an UUID is not specified, a new UUID (v4) is automatically generated to be set as the primary key. But seeing as UUIDv4 are generated at random, it could (at an astronomically low chance) collide with an existing row's UUID. What happens in this scenario? Does the insert return with a duplicate key error, or is generation repeated until it finds a UUID that does not collide?

No, it does not retry and thus throws an duplicate key error.

You can use the following code to test it yourself:

CREATE OR REPLACE FUNCTION random_uuid() RETURNS uuid AS $$
        BEGIN
            IF random() > 0.5 THEN
                RETURN '35d4b5e2-8fb5-4b89-9874-62b572c8fa2b'::uuid;
            ELSE
                RETURN gen_random_uuid();
            END IF;
        END;
$$ LANGUAGE plpgsql;

CREATE TABLE test (id uuid default random_uuid() Primary key, date timestamp);

INSERT INTO test (date) VALUES (now());
INSERT INTO test (date) VALUES (now());
INSERT INTO test (date) VALUES (now());

If you want to avoid collisions you might want to use a v1 (time based) uuid.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v1();

But this depends on your usecase (do not use that for crypto/security).


Disaster recovery/Server Migration for databases that are permanently in Standby I've got an old 2005 server where 8/12 of the databases are in Standby/Read-Only. This server has 81 jobs, many of them with unique id's for names. One of these jobs is called Replicate, and it calls a .cmd file every 30 minutes. The .cmd calls a .pl that looks like some sort of home-brewed log shipping.

I'd like to bump the databases out of standby so I can back them up properly, but I'm worried it will break everything.

To make matters worse, while the server is a VM, there is (according to the VM admins) a problem with the drives where it sits, making it impossible to back up. They are worried that the server could die at any time, so we have to do something.

The databases are relatively small - I may be able to get in and out in the 30 minutes between the Replicate job. There's no test environment, though, so I can't find out easily.

We do have vendor support, but they don't seem to be very good. Am I better off just handing them the keys?

Question: How would you go about backing up this server without knocking it down in the process?

I think there are at least the following methods here for your consideration.

BCP (Bulk Copy) out each table and then script out the database and then you can recreate a new db using these data and scripts.
You can temporarily take the db offline (by detaching the db) and then copy the MDF file and/or LDF file. You can use the file copy to reattach it to another db.
If you want doing online, you can simply create an SSIS package to dump the data (of course, your destination db should be created the same way as the source db, which you can script it out)


Best way to import and/or upgrade Oracle database I have an Oracle database, version 11.2.0.3.6. I need to export that database and import it in a 11.2.0.3.15 home on another server. What's preferred?

1) Just export/import the old database on a newly created instance running 11.2.0.3.15?

2) Upgrade the old database to 11.2.0.3.15, and then do the export/import to the new server?

3) Any other suggestion?

Thanks in advance.

That is possible.
Not really needed. You can just simply transfer the database to the new server, and run the PSU postinstall script there, that takes like 1 minute.
Depends on some factors:

platform of the old and new systems
size of the database + hardware environment (network/disk transfer speed and CPU power)
downtime allowed
Standard or Enterprise edition
archivelog mode enabled
have backups
need the current state of the database on the new server, or yesterday evening is good enough
can use licensed extra products

Some other methods:

RMAN restore from backup (and run the PSU postinstall script)
RMAN duplicate from active database or backup (and run the PSU postinstall script)
RMAN transportable tablespace/database
configure Streams replication and switch to new database
configure GoldenGate replication and switch to new database
configure the new host as a Data Guard standby then perform a role transition (and run the PSU postinstall script)

There is no best method. It depends on your actual task/environment.


Postgres SQL: INSERT from DELETE I have a table with id,user_id,timestamp and I'm trying to INSERT into the table, the user_id from a DELETE from table where id=some_number and timestamp. So what I'm trying to achieve looks like:
INSERT INTO table (user_id,timestamp)
VALUES (
    (DELETE FROM table WHERE id=some_number returning user_id),
    current_timestamp+'1 day'
)
RETURNING *;

I am getting a syntax error doing this... Can someone help me here plz...?

You can use a data modifying common table expression:
with deleted as (
  DELETE FROM some_table
  WHERE id = 42
  returning user_id
)
INSERT INTO other_table(user_id, "timestamp")
select user_id, timestamp '2020-01-04 21:32:34'
from deleted;


Optimal query for comparing table column vs. summary column What is the most efficient way to stack a field on one table with a summary field summarizing child data in another table?

For example, I have an account table and a task table. Below is the query I'm currently executing to compare a stamped value on the account table to actual counts from the task table.

SELECT
    account.sfid,
    account.lastactivitydate,
    account.sales_activities_last_30_days__c AS expectedcount,
    (
        SELECT COUNT(*)
        FROM salesforce.task
        WHERE task.accountid = account.sfid AND
              task.activitydate >= CURRENT_DATE - INTERVAL '60 days'
    ) AS actualcount
FROM salesforce.account
;

This is a naive attempt to get the data I want, and I'd appreciate any suggestions on optimizing this query to get results like what's shown in the table below.

sfid lastactivitydate expectedcount actualcount
---- ---------------- ------------- -----------
abc  2019-11-30       3             3
def  2019-11-27       5             4

Test

SELECT account.sfid,
       account.lastactivitydate,
       account.sales_activities_last_30_days__c AS expectedcount,
       COALESCE(subquery.cnt, 0) AS actualcount
FROM salesforce.account
LEFT JOIN ( SELECT task.accountid, COUNT(*) cnt
            FROM salesforce.task
            WHERE task.activitydate >= CURRENT_DATE - INTERVAL '60 days'
            GROUP BY task.accountid
          ) AS subquery ON subquery.accountid = account.sfid;

or

SELECT account.sfid,
       account.lastactivitydate,
       account.sales_activities_last_30_days__c AS expectedcount,
       COUNT(task.accountid) AS actualcount
FROM salesforce.account
LEFT JOIN salesforce.task
    ON task.accountid = account.sfid
   AND task.activitydate >= CURRENT_DATE - INTERVAL '60 days'
GROUP BY account.sfid,
         account.lastactivitydate,
         account.sales_activities_last_30_days__c;


How can I inform myself that an automatic SQL Server process has worked? I have a Windows batch file which extracts some data from a remote system and then imports it into an instance of MS SQL Server 2014. The batch file runs SQLCMD and uses SQL files to bulk insert from the text files into a staging table, tidy the fields up and move the data to a production database.

Having finished the development of this, I would like to test the stability of the process by easily knowing when it has run successfully.

I could check the batch file error log, but it is part of a larger process which means it takes a while to find the SQL Server part.
I could send an email to myself when the process has finished, but this does not tell me if it worked properly. By this, I mean if the INSERT INTO statement from the staging table to the production table did not produce any errors.

Does anyone have any suggestions? My gut feeling is that I need to include something in the SQL statements I use to import the data. Thanks!

count lines/records in your textfile
import
count records after import
if count1=count2 then continue
tidy up fields  (update blablabla set changefield='new' where myfield='badinfo')
test tidy up :  select count(*) from blablabla where myfield='badinfo' and changefield<>'new'
if testTidyUp-count = 0 then cleanup = correct ... continue
count records after tidy up
export to production
count records in production
if count3=count4 then ... let mysql know all is 'ok'

All the else ... mail that something is wrong


T-SQL MINUS operator These are the tables I have created and inserted values in it:

CREATE TABLE Customer
(Customer_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 Customer_Name VARCHAR(30) NOT NULL
)

CREATE TABLE DVD
(DVD_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 DVD_Name VARCHAR(30)
)

CREATE TABLE DVD_Purchase
(DVD_Purchase_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 DVD_No INTEGER NOT NULL,
 Customer_No INTEGER NOT NULL
 )

INSERT INTO Customer (Customer_Name)
VALUES('Daman')
INSERT INTO Customer (Customer_Name)
VALUES('Saif')
INSERT INTO Customer (Customer_Name)
VALUES('Gurung')
INSERT INTO Customer (Customer_Name)
VALUES('Upendra')
INSERT INTO Customer (Customer_Name)
VALUES('Ornob')

INSERT INTO DVD (DVD_Name)
VALUES('Bleach')
INSERT INTO DVD (DVD_Name)
VALUES('Gintama')
INSERT INTO DVD (DVD_Name)
VALUES('Tokyo Ghoul')
INSERT INTO DVD (DVD_Name)
VALUES('Death Note')
INSERT INTO DVD (DVD_Name)
VALUES('Rurouni Kenshin')

INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,1)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,2)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (3,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,4)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,4)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (5,1)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (5,2)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (5,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (5,4)

In order to display customer names who did not purchase any DVD by using MINUS operator, this is what I have tried:

SELECT Customer_Name
FROM Customer
WHERE Customer_No
IN
(SELECT Customer_No FROM Customer
 MINUS
 SELECT Customer_No FROM DVD_Purchase
)

And I'm getting the following error message:

  Msg 156, Level 15, State 1, Line 123 Incorrect syntax near the keyword
  'SELECT'. Msg 102, Level 15, State 1, Line 124 Incorrect syntax near
  ')'.

As shown in the error message, Incorrect syntax near ')' which I'm unable to figure out.

Other than that, can someone provide me the correct query?

MINUS is a name for the "set difference" operator that is used only by Oracle.

SQL Server uses the (standard) name, EXCEPT, for this operator. Replacing the MINUS with EXCEPT will solve the issue:

SELECT Customer_Name
FROM Customer
WHERE Customer_No
  IN
  (SELECT Customer_No FROM Customer
   EXCEPT
   SELECT Customer_No FROM DVD_Purchase
  ) ;

By the way, since the external query and the subquery reference the same table+column (Customer . Customer_No) you could use NOT IN without EXCEPT. Assuming that the columns involved (Customer_No) are not nullable, this is equivalent:

SELECT Customer_Name
FROM Customer
WHERE Customer_No
  NOT IN
  (SELECT Customer_No FROM DVD_Purchase
  ) ;


Probable update error to mysql 5.7.21 I have a VM with Ubuntu 16.04.4 LTS. I use it for wordpress sites, so it haves mysql, nginx and php. I migrated from Azure to Vultr. For doing so I backed up all the databases in Azure and restored them into my VM at Vultr.

That was a couple of months ago. Everything was working fine until today, when I wanted to add a new wordpress site. As usual I started by configuring the database for mysql, so I tried to loging to mysql and my issues started. A problem with the socket appeared, and then I ran mysqld_safe --skip-grant-tables & to be able to access mysql.

I was able to access it and to fix my issue I ran update user set plugin="mysql_native_password"; and exited mysql. After struggling a while some other sockets errors appeared.

Reading on StackOverflow an upgrade was recommended, therefore I updated my database and now this is my current issue. I can login with mysql -u root -p. But, when I enter use mysql; I get: ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Then, when I run ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxxxxxx'; i get ERROR 1805 (HY000): Column count of mysql.user is wrong. Expected 45, found 46. The table is probably corrupted

The password column doesn't exists in the user table, so instead of the previous statement I used update mysql.user set authentication_string=password('xxxxxxx') where user='root'; and I got ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. so it seams to me that I'm in a dead end.

This is my current error when I run mysql_upgrade --force -uroot -p...mysql_upgrade: Got error: 1862: Your password has expired. To log in you must change it using a client that supports expired passwords. while connecting to the MySQL server
Upgrade process encountered error and will not continue.

I appreciate any help you can give me.

UPDATE

Looking at the user.frm files I noticed that the column that the is_role column is the extra column in my user table. I tried to edit the user.frm file but I wasn't able to make it work.

You must be operating in some restricted mode

The MySQL Documentation for ALTER USER says the following:

  Password expiration for an account affects the corresponding row of the mysql.user system table: The server sets the password_expired column to 'Y'.

  A client session operates in restricted mode if the account password has been expired. In restricted mode, operations performed within the session result in an error until the user establishes a new account password:

mysql> SELECT 1;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

mysql> SET PASSWORD = PASSWORD('new_password');
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

SUGGESTION : Try the aforementioned code

mysql> SET PASSWORD = PASSWORD('new_password');

If this does not works due to old_passwords, do the hash algorithm manually

mysql> SET PASSWORD = CONCAT('*',UPPER(SHA1(UNHEX(SHA1('new_password')))));

I learned about this hash algorithm from PalominoDB (last bought by Pythian).


How to partition a very large table with limited db space? I have a very big table. I want to partition it, but I cannot.

Database size: 1TB, Free space 200GB

Table:

Size: 165 columns (row lengh 4216 KB, no LOBs), 500 million rows, 600GB of data.
Possible partitioning: a day per partition
Number of rows per day/parition: 2 million

In order to partition it, I need to create a clustered index. But creating parition requires free space of the same size as the table, and I do not have extra 600GB.

Are there any options how I partition this table?

EDIT 1:

I have tried copying data into separate table.

However when I try to DELETE (or INSERT) 1 day of data into another table, I get an error, that tranasaction log is full and my transaction is being rolled back. My transaction log is approx 20 GB and I cannot make it any bigger.

You'll need to create a new table with the same schema, but as a partitioned object.  Optionally you can compress the table to save even more space.  As you are only putting on average one row per page I'm not sure how much space savings you will see.  I'd recommend putting a few thousand rows into the new table then compressing to see if the space savings is worth the CPU overhead.

As for how to move this much data without eating up all your drive space and without bloating your transaction log, that'll need to be done in a loop moving small amounts of data per run.  You'll want to do some data analysis to see how large of a window you can process, but I'll assume that based on the data volumes you need to move the rows one minute at a time.

DECLARE @processFrom as datetime
SELECT @processFrom = min(YourDateColumn)
FROM YourTable

DECLARE TABLE @Rows (...)

WHILE EXISTS (SELECT * FROM YourTable)
BEGIN
     DELETE TOP (10000) FROM YourTable
     OUTPUT DELETED.* INTO @Rows
     WHERE YourDateColumn = @processFrom

     INSERT INTO NewTable
     (...)
     SELECT ...
     FROM @Rows

     DELETE FROM @Rows

     IF @@ROWCOUNT = 0
              SET @processFrom = dateadd(dd, 1, @processFrom)
END

When everything is done and you've verified that all the data is in the new table drop the old table and rename the new table so that it has the old tables name.  This way nothing breaks.  You'll want to script out the permissions on the old table (if there are any) so that you can apply them to the new table.

If there are tables with foreign keys to this table you'll need to drop them before this will work.


Can an encrypted MySQL restart without the keyring file on the box? I am running Mysql 5.7 with a few tables in the DB encrypted, if I move the keyring file off of the box to secure it, can I still restart the DB without issue?

Yes, mysql will start, but you will receive an error if you try to access the encrypted table.


Short circuit in JOIN I've been searching everywhere but I have not been able to find anything about it, seems like everyone is concerned only with WHERE and CASE. (and I haven't a SQL Server at hand reach to verify it by myself)

So, simple question: is SQL Server short circuiting JOIN based on the JOIN condition? If I write:

TableA LEFT OUTER JOIN TableB ON 1=0 AND TableA.ID = TableB.A_ID

will SQL Server skip the join at all?

Edit

Ok, someone correctly pointed out that this is solved at compile time, so I'd better expand on the question.

The above was just intended as an example, but it turned out to be a bad one. What I wanted to know is if SQL Server shorts circuit a JOIN when that condition happens at runtime, as a conseguence of a @parameter passed to a stored procedure.

I'm asking 'cause I'm sure to have used something like that back in the years, but I wanted to be 100% sure before talking about it with some colleagues. So, will SQL Server short circuit this, then:

TableA LEFT OUTER JOIN TableB ON @parameter IS NOT NULL AND TableA.ID = TableB.A_ID

if it's inside an SP and it's called with @parameter set to NULL? Will it make any difference if the store procedure is WITH RECOMPILE?

DB<>fiddle and other online-services allow you to test different DBMS without having to install them locally. As shown in:

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=7692d5abba854bdc57d90c95335014c0

There is no access to table B.

You may find the following article by Lukas Eder of interest, he compares algebraic optimizations for different DBMS:

https://blog.jooq.org/2017/09/28/10-cool-sql-optimisations-that-do-not-depend-on-the-cost-model/


DB2 equivalent for MySQL's DROP .. IF EXISTS Is there an equivalent in DB2 for MySQL's (or PostgreSQL's):

DROP TABLE IF EXISTS sometable;

No.

You'll need an IF clause to check for the existence of the table and then a separate statement to actually drop it if it does exist.


User Defined Function (UDF) in master database I have a function which is used in more than a dozen databases. I want this function to be accessible in all the databases by writing it only once, so I want to write the function in master database so that my function can be accessed easily.

Is there any problem in writing the user defined function in master db?

When surfing the Internet some suggested converting the function to a system function. (Using “EXEC sp_ms_marksystemobject  'fn_db_name' ”).

Is there a necessity to convert the function created in master db to system function? If yes why?

I agree with Jonathan - as long as the function isn't supposed to return local data based on the calling database context, put the function in a utility database (this is also where I put things like numbers and calendar tables, splitting functions, etc):

USE UtilityDB;
GO
CREATE FUNCTION dbo.whatever() RETURNS ...
GO

Now, in every database that needs to access the function, just create a synonym:

CREATE SYNONYM dbo.whatever() FOR UtilityDB.dbo.whatever();

This way each database can still reference the function using a simple, 2-part name - and they don't have to know or care where it actually exists. And you only have to maintain a single copy of the function.

(And in fact, you can put the synonym in the model database, so that it is created in new databases automatically.)

The reason I dislike putting user objects in master - unless they really do need to be globally available and contextual to the calling database, like your own customized version of sp_spaceused or sp_helpindex - is that people aren't looking for user objects in master, and they're less discoverable there. They also make it harder to migrate your user databases elsewhere, because you need to remember the user stuff you put in master, too. If you are absolutely dead-set against creating or using a utility database for this, I think msdb is a more practical choice for a central location.


Creating a Min date with Session Date also included in the table Code: MSSQL / Bigquery

SELECT EmployeeId, SessionLoginLocal, cast(MIN(SessionLoginLocal) as date)MinSession
FROM database.data_source.user_session_table
Group by EmployeeId, SessionLoginLocal
Order by EmployeeId

Current output:

 Row | EmployeeId |  SessionLoginLocal  | MinSession
-----+------------+---------------------+------------
   1 |        354 | 2017-12-12T07:29:36 | 2017-12-12
   2 |        354 | 2017-12-11T16:58:21 | 2017-12-11
   3 |        354 | 2017-12-12T10:59:34 | 2017-12-12
   4 |        354 | 2017-12-11T18:32:59 | 2017-12-11
   5 |        354 | 2017-12-11T18:33:34 | 2017-12-11
   6 |        354 | 2017-12-11T17:16:58 | 2017-12-11
   7 |        354 | 2017-12-12T10:56:29 | 2017-12-12
   8 |        354 | 2017-12-11T19:37:54 | 2017-12-11
   9 |        354 | 2017-12-12T10:57:52 | 2017-12-12

Desired Output

 Row | EmployeeId |  SessionLoginLocal  | MinSession
-----+------------+---------------------+------------
   1 |        354 | 2017-12-12T07:29:36 | NULL
   2 |        354 | 2017-12-11T16:58:21 | 2017-12-11
   3 |        354 | 2017-12-12T10:59:34 | NULL
   4 |        354 | 2017-12-11T18:32:59 | NULL
   5 |        354 | 2017-12-11T18:33:34 | NULL
   6 |        354 | 2017-12-11T17:16:58 | NULL
   7 |        354 | 2017-12-12T10:56:29 | NULL
   8 |        354 | 2017-12-11T19:37:54 | NULL
   9 |        354 | 2017-12-12T10:57:52 | NULL

The goal of this view is to create a stacked column graph with the date along the X axis, and the number of sessions on the Y.
Currently the number of sessions by distinct employee per day are displayed, now I am trying to include a second column with the minimum session date, basically highlighting when they had their first session. How do i create the column with the Min(SessionTime) to only include the first session and create nulls for their other sessions?

When you post questions like this, it's really helpful if you post your sample data as inserts, so anyone can hop in and immediately query it.

CREATE TABLE #whatever
(
    id INT,
    employeeid INT,
    sessionloginlocal DATETIME
);

INSERT #whatever ( id, employeeid, sessionloginlocal )
SELECT *
FROM (
VALUES
(1,   354, '2017-12-12 07:29:36'),
(2,   354, '2017-12-11 16:58:21'),
(3,   354, '2017-12-12 10:59:34'),
(4,   354, '2017-12-11 18:32:59'),
(5,   354, '2017-12-11 18:33:34'),
(6,   354, '2017-12-11 17:16:58'),
(7,   354, '2017-12-12 10:56:29'),
(8,   354, '2017-12-11 19:37:54'),
(9,   354, '2017-12-12 10:57:52')
) AS x (id, employeeid, sessionloginlocal)

This code uses a case expression and MIN as a windowing function to find the min login time per user. When they match, we display it as a date. When they don't we display NULL.

SELECT *,
       CASE WHEN w.sessionloginlocal =
               MIN(w.sessionloginlocal) OVER ( PARTITION BY w.employeeid )
               THEN CONVERT(DATE, w.sessionloginlocal)
               ELSE NULL
       END AS min_sessionlocal
FROM   #whatever AS w;


SQL Server 2008 R2 - Upgrade Default DB Instance (Express) to Developer Edition I have just installed SQL Server 2008 R2 Developer edition (created a new instance for it: DEVEDITION). I previously had and the default instance (MSSQLSERVER) which was running SQL Server 2008 R2 Express.

I have ~20 DB's that are running on the default instance: MSSQLSERVER.

If this is possible, I would like to convert/upgrade the MSSQLSERVER instance so that I can use all the bells and whistles of the Developer edition on it. i.e. Profiler, SQL Agent etc.

I have all DB's backed up and as it is a development environment I can uninstall all versions of SQL if needs be.

Thanks in advance.

Here's the previous question I asked regarding the installation of Developer Edition over Express.

I don't think it is possible to upgrade SQL Server Express to another edition.  You should be able to use all the features of SQL Server you mentioned (agent and profiler) with the new, named instance (DEVEDITION) along side of Express Edition.

I don't think its possible to rename a SQL Server instance name, at least not with a bunch of headache.

What I would do is:

Detach the dbs connected to your SQL Server Express instance and uninstall SQL Server Express.  Then re-install the dev edition to the default instance and reattach the dbs.


Which error codes are safe for `slave_skip_errors` with MySQL Innodb Does anyone know which error codes are safe to ignore during replication, meaning the slave will remain consistent with the master?

For example

[mysqld]
slave-skip-errors=1007,1008,1050,1051,1054,1060,1061,1068,1094,1146

The example that I found omitted is 1213 (Deadlock found when trying to get lock; try restarting transaction, Error_code: 1213; handler error HA_ERR_LOCK_DEADLOCK), which seems safe to me?

I've found little advice on the matter so seems like a good one for stackoverflow...

To get the ball rolling, let us start with the manual

option_mysqld_slave-skip-errors

  MySQL 5.7 supports an additional shorthand value ddl_exist_errors,
  which is equivalent to the error code list
  1007,1008,1050,1051,1054,1060,1061,1068,1094,1146.

Which includes:

Error: 1007 SQLSTATE: HY000 (ER_DB_CREATE_EXISTS)

  Message: Can't create database '%s'; database exists
  An attempt to create a database failed because the database already exists.
  Drop the database first if you really want to replace an existing database, or add an IF NOT EXISTS clause to the CREATE DATABASE statement if to retain an existing database without having the statement produce an error.

Error: 1008 SQLSTATE: HY000 (ER_DB_DROP_EXISTS)

  Message: Can't drop database '%s'; database doesn't exist

Error: 1050 SQLSTATE: 42S01 (ER_TABLE_EXISTS_ERROR)

  Message: Table '%s' already exists

Error: 1051 SQLSTATE: 42S02 (ER_BAD_TABLE_ERROR)

  Message: Unknown table '%s'

Error: 1054 SQLSTATE: 42S22 (ER_BAD_FIELD_ERROR)

  Message: Unknown column '%s' in '%s'

Error: 1060 SQLSTATE: 42S21 (ER_DUP_FIELDNAME)

  Message: Duplicate column name '%s'

Error: 1061 SQLSTATE: 42000 (ER_DUP_KEYNAME)

  Message: Duplicate key name '%s'

Error: 1068 SQLSTATE: 42000 (ER_MULTIPLE_PRI_KEY)

  Message: Multiple primary key defined

Error: 1094 SQLSTATE: HY000 (ER_NO_SUCH_THREAD)

  Message: Unknown thread id: %lu

Error: 1146 SQLSTATE: 42S02 (ER_NO_SUCH_TABLE)

  Message: Table '%s.%s' doesn't exist

But in support of Dave's comment:

  Normally, replication stops when an error occurs on the slave, which
  gives you the opportunity to resolve the inconsistency in the data
  manually. This option causes the slave SQL thread to continue
  replication when a statement returns any of the errors listed in the
  option value.

  Do not use this option unless you fully understand why you are getting
  errors. If there are no bugs in your replication setup and client
  programs, and no bugs in MySQL itself, an error that stops replication
  should never occur. Indiscriminate use of this option results in
  slaves becoming hopelessly out of synchrony with the master, with you
  having no idea why this has occurred.


Backup SQL Server database on UNC path using SA We need to backup some database to an unc path, the problem it's that it's a SQL Server instance that has to execute with SQL Server autentication (sa user).

How can we configure the plan task in SQL Server to make it able to have access to the UNC folder? I attach the state of our SQL Server Services in SQL Server Configuration Manager.

Thanks in advance and regards,

Pedro Sánchez.

You can map a local drive letter to your UNC folder using xp_cmdshell like:
exec xp_cmdshell 'net use X: \MyServer\MySharedFolder\'

Then you can issue your backup statement to use the X: drive as if it was local.

You may have to run sp_configure to enable xp_cmdshell.


MySQL 5.1: How to rollback partitioned table I have partitioned a table on the basis of one flag column have value either 0 or 1.
Now, I want to roll back these tables.
How can I do this?

ALTER TABLE .... REMOVE PARTITIONING

http://dev.mysql.com/doc/refman/5.1/en/alter-table-partition-operations.html

Only available from version 5.1.8 onwards.


PosgreSQL: very slow select on a single table with no joins I'm trying to help a colleague who's having some problems with a PostgreSQL query.

We've narrowed it down to a join to one table making the query painfully slow and, upon investigation, we found that even a simple select * request on the "data" table takes up to 2 minutes to return the results.

The table contains 420,000 records and 13 columns, none of which are BLOBs or anything massive (they're all text).

Running an EXPLAIN ANALYSE VERBOSE massively underestimates the time taken to return the results

i.e. for SELECT * FROM DATA

"Seq Scan on data  (cost=0.00..18284.35 rows=389735 width=27)
(actual time=0.135..594.929 rows=426934 loops=1)"
"  Output: id,..."
"Total runtime: 1069.643 ms"

Whereas running the query without EXPLAIN gives

Total query runtime: 86598 ms.
426934 rows retrieved.

I'm sorry if I'm breaking any conventions in asking the question but I don't really know Postgres at all so if anyone could suggest what kind of underlying issues we should be looking for to help resolve this issue, it would be gratefully received.

Edit: as requested, the table definition

CREATE TABLE data
(
  id text NOT NULL,
  methodid text NOT NULL,
  replicateid text,
  t_id text NOT NULL,
  t_name text NOT NULL,
  a_id text NOT NULL,
  originalname text,
  qualifier text,
  frequency text,
  frequencyunits text NOT NULL,
  determiner text,
  sensitive text,
  versionkey text,
  CONSTRAINT data_pkey PRIMARY KEY (id),
  CONSTRAINT sample FOREIGN KEY (sid)
      REFERENCES sample (sid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

CREATE INDEX eventid_idx
  ON data
  USING btree
  (id);

Running an EXPLAIN ANALYSE VERBOSE massively underestimates the time
  taken to return the results

There's a misunderstanding here, because EXPLAIN ANALYZE does not estimate, it runs the query for real and reports the actual time taken by each steps, as opposed to EXPLAIN without ANALYZE that just reports the estimates without running the query.

Consider this line from your EXPLAIN ANALYZE output:

  (actual time=0.135..594.929 rows=426934 loops=1)

These 595 milliseconds are not an estimate, it's actual time. Besides, when postgres shows estimates, they're expressed in units of "cost", not in units of time.

When it's finished, EXPLAIN ANALYZE sends back the result of the analyze to the client, and discards the rows of the actual result. That differs from a real SELECT which has to send back the rows to the client.

For this reason, the big difference between both operations in your case could/should be accounted by the slowness in receiving the results, either because the network is slow, or because the client is slow, or both. Maybe the client is swapping like crazy if the resultset is too big to fit in the available RAM. I'd look at the vmstat 1 and iftop outputs (or even strace) on server and client during these 2 minutes to check what's doing what.


Multi-tenant database using SQL Server 2008? I am designing a database using a shared schema containing the "security views" to which all logged tenants will be directed.

I read the msdn article on Multi-tenant Data Architecture. I understand the concept to a degree - unclear how to get the tenant view filter to work.

So, I have a tenant table containing a TenantID, and TenantName. I suspect i would need a mapping table to associate the tenantID with roles set up in SQL Server to which user domain logins will be mapped to?

I have the client authenticate using a DSN-less connection.

Please any guidance and examples on this "Mapping" table, and how this relates to the SUSER_SID() described in the article to make this all work will be greatly appreciated.

Would i need to create database / application roles identical to those indicated in the Mapping table??

Thanks

To be clear.  When talking about a DSN-less connection what you are really saying is that you are using a connection string that uses the native OLE DB driver to talk to SQL as opposed to ODBC.  Just want to clarify.

Usually with a tenant data model the really critical thing to solidify is the process that authenticates users and issues them a connection to the tenant database.  Most tenant database models don't use windows authentication as they are hosted or an SaaS product.  Your question makes it sound like you are using domain accounts however.

If you were assigning sql logins to each tenant or each windows login was viewed as a unique tenant then the easiest thing to do would be to define the TenantID and the sql login used in the connection to be the same value.  Then you wouldn't have to worry about a mapping table.  Why?  Because SUSER_SID() (the login id) IS the TenantID.  You could also use SUSER_SNAME() if you wanted.

CREATE VIEW TenantEmployees AS
SELECT * FROM Employees WHERE TenantID = SUSER_SID()

If you are not using sql logins or each tennant is associated with more than one login (such as with windows authentication) or business rules preclude you from using the login as the TenantID then yes.  The next best thing would be a mapping table.  Something like this for example that stores logins and the tenantID they are associated with.

UserKey   |  Tenant       |  TenantID  |  LoginName    |  LoginID
------------------------------------------------------------------------
1         |  Bills Bolts  |  111       |  billbolt     |  0x010050
2         |  Sams Screws  |  111       |  samscrew     |  0x010010
3         |  Nicky Nuts   |  222       |  nickynut     |  0x010000

In this case the view would look like this:

CREATE VIEW TenantEmployees AS
SELECT Employees.*
FROM Employees E INNER JOIN TenantMapTable T
    ON E.TenantID = T.TenantID
WHERE T.LoginName = SUSER_SNAME()  /* or T.LoginID = SUSER_SID() if preferred */

Another critical step however is that rights to the actual table are revoked.  Only grant access to the table through the view - or this was all for nothing.

Don't forget to remove access to anything but the views from the tenant user login.

Last - keep in mind that you can query a view just like a table.  So nothing is stopping you from filtering it further based on the needs of your application via procs or your ORM or where-ever.

select [firstname] from tenantemployees where [dob]='12/25/1980'


How to retrieve closest value based on look-up table? I'm attempting to create a query that will find the closest value from one table and return its ID into the resulting table.

Below is an example that should describe the situation better.

Sample Data

These two tables will exist in the SQL database.

Main Table

+----+-------------+
| ID | Measurement |
+----+-------------+
|  1 | 0.24        |
|  2 | 0.5         |
|  3 | 0.14        |
|  4 | 0.68        |
+----+-------------+

Look-up Table

+----+---------------+
| ID | Nominal Value |
+----+---------------+
|  1 | 0.1           |
|  2 | 0.2           |
|  3 | 0.3           |
|  4 | 0.4           |
|  5 | 0.5           |
|  6 | 0.6           |
|  7 | 0.7           |
|  8 | 0.8           |
|  9 | 0.9           |
+----+---------------+

Goal

This will be the result of a query. Measurements should not be on the border (0.25 for example).

+----+-------------+-----------+
| ID | Measurement | Lookup ID |
+----+-------------+-----------+
|  1 | 0.24        |         2 |
|  2 | 0.5         |         5 |
|  3 | 0.14        |         1 |
|  4 | 0.68        |         7 |
+----+-------------+-----------+

Is there a query that would be able to return this kind of result?

A couple of queries tested and optimized for Postgres 9.3. All return the same, all are basically standard SQL, but no RDBMS supports the standard completely.
In particular, the first one uses a LATERAL JOIN, which is missing in Oracle or MySQL. Test which performs best.
All of them use index-only scans on the lookup table in Postgres. Obviously, lookup.nominal_value needs to be indexed. I suggest to make it UNIQUE because it seems like the column should be unique, and because that also creates the all-important index automatically.
LATERAL JOIN
SELECT m.id, m.measurement, l.nominal_value
FROM   measurement m
JOIN LATERAL (
   (
   SELECT nominal_value - m.measurement AS diff, nominal_value
   FROM   lookup
   WHERE  nominal_value >= m.measurement
   ORDER  BY nominal_value
   LIMIT  1
   )
   UNION  ALL
   (
   SELECT m.measurement - nominal_value, nominal_value
   FROM   lookup
   WHERE  nominal_value <= m.measurement
   ORDER  by nominal_value DESC
   LIMIT  1
   )
   ORDER  BY 1  -- NULLS LAST is default
   LIMIT  1
   ) l ON TRUE;

All parentheses required for UNION. Related answer:
Postgres 9.2 select multiple specific rows in one query
Correlated subqueries in a subquery
SELECT id, measurement
      ,CASE WHEN hi - measurement > measurement - lo
         THEN lo
         ELSE COALESCE(hi, lo)  -- cover all possible NULL values
       END AS nominal_value
FROM (
   SELECT id, measurement
         ,(SELECT nominal_value
           FROM   lookup
           WHERE  nominal_value >= m.measurement
           ORDER  BY nominal_value
           LIMIT  1) AS hi
         ,(SELECT nominal_value
           FROM   lookup
           WHERE  nominal_value <= m.measurement
           ORDER  by nominal_value DESC
           LIMIT  1) AS lo   -- cover possible NULL values
   FROM   measurement m
   ) sub;

Correlated subqueries in a CTE
WITH cte AS (
   SELECT id, measurement
         ,(SELECT nominal_value
           FROM   lookup
           WHERE  nominal_value >= m.measurement
           ORDER  BY nominal_value
           LIMIT  1) AS hi
         ,(SELECT nominal_value
           FROM   lookup
           WHERE  nominal_value <= m.measurement
           ORDER  by nominal_value DESC
           LIMIT  1) AS lo
   FROM   measurement m
   )
SELECT id, measurement
      ,CASE WHEN hi - measurement > measurement - lo
         THEN lo
         ELSE COALESCE(hi, lo)  -- cover all possible NULL values
       END AS nominal_value
FROM cte;

Nested correlated subqueries
SELECT id, measurement
      ,(SELECT nominal_value FROM (
         (
         SELECT nominal_value - m.measurement, nominal_value
         FROM   lookup
         WHERE  nominal_value >= m.measurement
         ORDER  BY nominal_value
         LIMIT  1
         )
         UNION  ALL
         (
         SELECT m.measurement - nominal_value, nominal_value
         FROM   lookup
         WHERE  nominal_value <= m.measurement
         ORDER  by nominal_value DESC
         LIMIT  1
         )
         ORDER  BY 1
         LIMIT  1
         ) sub
         ) AS nominal_value
FROM   measurement m;

SQL Fiddle.


How to migrate SQL Server 2014 to SQL Server 2019 on Linux using AG I would like to migrate SQL Server 2014 on Windows to SQL Server 2019 on Linux using while running AG.

Is there an option to Add the SQL Server 2019 to the same AG and once the time comes gracefully fail over to the SQL Server 2019 version?

I currently have 3 nodes on the AG using Asynchronous mode (primary and two standby nodes).

In addition what is the suggested clustering method that I should be using in Linux?
Is there something like WSFC or can I use other Linux clustering method?

And finally I am using EC2 at AWS.

You cannot add a Linux system to a Windows cluster, so you won't be able to add any Linux systems into a SQL Server 2014 availability group. You also cannot do the migration with distributed availability groups because SQL Server 2014 did not have support for distributed availability groups.  From Distributed availability groups:

  Because the distributed availability groups feature did not exist in SQL Server 2012 or 2014, availability groups that were created with these versions cannot participate in distributed availability groups.

In order to be able to do a migration using availability groups, you would have to get the SQL Server 2014 systems upgraded to at least SQL Server 2017 where you can create an availability group with a cluster type of "none," which will allow you to add a replica to a Linux SQL Server.  See Always On Availability Groups on Linux -> Interoperability with Windows-based availability groups and replicas.

Always On Availability Groups on Linux states that you can use Pacemaker, or no cluster for Linux availability groups.


How to get query execution time and query result at the same time? Is there any SQL query syntax that returns query execution time and query result at the same time?

I agree with @DTest, how are your running your query? If you log directly into a MySQL terminal style and hand-run queries,  it will give you a time result. Also, if you use Oracles MySQL database tool (I believe it is called "MySQL Workbench"), you can connect to your database server directly and the GUI will also give you time results for the query if you hand-run it.