How can I determine which tables are causing the database to grow over time? I have a database in SQL Server 2008 that is growing all the time, and I'd like to know which tables have caused the most growth over the past 6 months.

SQL Server doesn't track this information. You'd need some kind of monitoring (whether it be a tool or home-grown) that keeps snapshots of table space over time, going forward. This can be simple or complex:

Viewing the report @Zane highlights in his answer, and saving the file every so often;
Taking your own periodic snapshots of views like sys.dm_db_partition_stats;
Looking at restored copies of older backups, as suggested by @Ali - however who knows how far your backups go, and if disk space is an issue, you might not have the luxury of restoring a bunch of them for side-by-side comparisons anyway.
Using an external tool like PowerShell to automate it, like the article @Kin points out;
Using a full-blown monitoring tool which tracks space, fragmentation, etc. over time (Disclaimer: I work for SQL Sentry).

Short of having already implemented one or more of these methods, or someone inventing a time machine, there's not really any good way to figure out how big a table was 6 months ago, or two weeks ago, or 20 minutes ago.


SQL Server Bulk Migration I have production database with a size of 1TB in SQL Server 2008. I can't close the database for backup/restore process, because it needs so long time for perform this operation.

How to migrate this database to another server which has SQL Server 2016?

I am thinking of creating a database on the new server and then bulk inserting the existing data. But what about logins, roles, permissions? How to figure it out this problem ?

Thanks,

You dont need to close the database for backup, just setup log-shipping with two servers (legacy and target), once you decide to migrate (cut-over) to target server just take log backup from legacy server and restore on target server "with recovery".


Use GROUP BY and return more than 2 columns I am trying to find the latest unit_status, and the timestamp of that status (cdts), for every unid I have in a table.

This is what I have come up with so far:

with cte as
(
    select *
    from un_hi
    where ag_id = 'bfd' and cdts > '201708' and
    unit_status != 'uc'
)
select distinct(unid), max(cdts2) Last_GPS_Report_Date
from cte
where unid in ('C06','C07','D01','D03','D04','D06')
group by unid
order by unid

It correctly returns every unid and the time of their latest status (max(cdts)), but I want to see what the unit_status was.

I have tried taking this query and joining it to the UN_HI table (which is where this data is coming from), but I couldn't get that to work. Any other suggestions?

You can use ROW_NUMBER:

with cte as
(
    select  *,
            RN = ROW_NUMBER() OVER(PARTITION BY unid ORDER BY cdts2 DESC)
    from un_hi
    where ag_id = 'bfd'
    and cdts > '201708'
    and unit_status != 'uc'
)
select *
from cte
where RN = 1;


Using NULL values for foreign key with normalization I am creating a table as follows:

Building (PK: buildingID)
Area (PK: areaID, FK: buildingID)
Room (PK: roomID, FK: buildingID, FK: areaID)

All rooms are in a building. Some rooms can be in an area of a building. Rooms that are not in an area of a building have the FK: areaID set to NULL.

For example, areas of a building include: blue zone, green zone, red zone. Rooms can be in one of those areas, but may not be in an area and just part of the building itself.

Is this properly normalized? I feel that when the FK: areaID is set to a value then it is not normalized as the buildingID can be determined from the areaID.

If it is not normalized, how do I go about normalizing it so that the FK: areaID can still use NULL values?

Many designers use a NULL  in a foreign key when there is an optional relationship, and this particular row is not participating.
There's nothing terribly wrong with this.  When a join is done on this FK and the corresponding PK,  the rows with NULL will just drop out.
Other designers avoid this like the plague.


Is Circular replication recommended among 4 MySQL servers We have 4 MySQL servers and our main motto is that data should replicate across all the 4 servers if one is getting updated and the same changes has to go to all other
3 servers.

And below are the few other requirements

1) Changes need to be on all 4 servers.

2) one should be active at anytime which should be on Read/Write and rest of 3 to be on read-only mode.

3) If switch/Failover happens the new master should only be read/write and other servers has to be on read only

We want to achieve the above explained funtionality through the Circular replication mechanisum? Is it possible?

If not possible then what would be the recommended HA setup solution to meet above said requirement.

Appriciate any help or suggessions on this.

I do not know why trying to fit circular replication in such a setup- a circular replication has multiple single point of failures (one per server, as if any server fails, the "circle" is broken) plus with non-gtid replication solving the failover is not easy at all (what is the binary coordinates equivalent to the binary coordinates of the next server?). While there are some very specific and contained cases where it could make sense, I would say yours is not one of them.

What you described, 1 read/write server and 3 read-only ones is the paradigmatic usage of traditional replication where the master is r/w and it has 3 slaves depending on it. So, a tree-like topology, not a circle.

Of course, replication, up to the most recent versions has its issues (traditionally being asynchronous, no possibility of checking for drift, no paralelization, no automatic resolution of conflicts), so in some cases you may want to check alternative options, like Galera or Tungsten replication. However, many of the shortcomings have been solved in the most recent versions with semisync replication, GTID, parallel apply of logs, and in the upcoming 5.7, multi-master replication.

My recommendation would be to stick with standard replication with one master and 3 slaves, if possible using 5.6 GTID, as it makes the failover easier.

However, replication does not provide HA by itself. There are different approaches, depending on the technology used, here you have some suggestions:

MySQL failover is a small first party utility that can monitor replication and help you change the topology of your setup. If you want a more all-in-one solution, check MySQL Fabric
MHA allows you to check and change the topology of your setup, maybe combined with virtual IPs to guarantee the service
HAProxy allows you to setup a couple of virtual read/write and read-only services that are backed by several MySQL servers, so it can transparently check for failure and switch the active server, or load balance between several servers

Here you have a presentation with some thoughts about different methods of doing HA with MySQL.


How do I use SQL Server's "Hekaton"? The SQL Server 2017 version on Linux comes packaged with a utility called mtlogreader.exe that is absent from the Windows distribution,

MtLog Reader - Dump Hekaton MtLog files

Usage: \\VBOXSVR\mssql\lib\sqlservr\Content\binn\mtlogreader.exe <file> [options]

Options:

        -verbose <int>  Verbosity (0-4) Default 1. (short form: -v)
        -buffio <bool>  Buffer stdout (true/false) Default true (short form: -b)
        -keySecret1 <string>    The first secret key blob used for crypto keys (short form: -k1)
        -keySecret2 <string>    The Second secret key blob used for crypto keys (short form: -k2)
        -instructionFile        Treat file as instructionfile. (short form: -i)
        -targetruntimeinseconds <int>   The target run time in seconds (only valid with -i). (short form: -t)
        -skipchksum     Skip checksum validation. (short form: -sc)
        -maxbsn <uint64>        Max BSN to dump. Default 0 (dump whole file). (short form: -m)

<file> has a different meaning depending on whether -instructionFile is specified
if -instructionFile is specified, <file> is a file containing checkpoint file
information extracted at dump time, containing the root file name followed by a
list of container directories (one per line).
 the -t parameter is an indication of how long to run the tool
when used with -i it is used to limit the processing of checkpoint files

I can't find much about Hekaton though. I've found some errors here and I see it has a pretty old wiki page where can I find documentation on the Hekaton subsystem but how to use it? Has Hekaton been rolled into the "Analysis Services"?

Hekaton was the internal Microsoft project that references the In-Memory OLTP feature, starting with SQL 2014. Some people still use the word Hekaton generically, to describe the In-Memory feature in any version of SQL server (2014, 2016, 2017).

In-Memory OLTP is a feature that stands alone - it is not part of any other feature, i.e. Analysis Services.

There is tons of info out there on the web about In-Memory OLTP, but I would caution you to avoid anything that references SQL 2014 (as you have above), because that was essentially v1.0, and much has changed since then.

First - have you proven through a valid POC that your workload is likely to benefit from using In-Memory OLTP?

I have blogged extensively about In-Memory OLTP, but you might be better off starting with the documentation, here:

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/in-memory-oltp-in-memory-optimization


Update DATE field when another field in the same table changes value I hava a table, say:

 - id (autoinc pk)
 - name (varchar)
 - description (varchar)
 - yearofarrival (int) <- (should contain either 78, or 1998, that's why it is not a date field)
 - date_updated <- auto-update field

What I need is to set a trigger (I guess this is how it should be done) that, after update, if the field "yearofarrival" has been changed, it should update the "date updated" with the current date.

This should not happen with any other field change.

A Trigger can't update another row of the same table as the row, the trigger was called from.

The typical way to do that, is to create a stored procedure, that inserts into/Updates the target table, then updates the other row(s), all in a transaction.

Another option for you might be the ON UPDATE constraint that you can add to your table
- this option comes only from 5.5 up.

See example here

mysql> create table your_table (date_updated DATETIME ON UPDATE CURRENT_TIMESTAMP, yearofarrival int  );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into your_table values (now() - INTERVAL 1 DAY, 100);
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from your_table;
+---------------------+---------------+
| date_updated        | yearofarrival |
+---------------------+---------------+
| 2014-03-06 12:00:59 |           100 |
+---------------------+---------------+
1 row in set (0.00 sec)

mysql> update  your_table set yearofarrival= 1998;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from your_table;
+---------------------+---------------+
| date_updated        | yearofarrival |
+---------------------+---------------+
| 2014-03-07 12:01:16 |          1998 |
+---------------------+---------------+
1 row in set (0.00 sec)

mysql>

Should i explain what i have posted here ?

By adding this constraint to your column , every time you alter your yearofarrival  column the date_updated  will be automaticly updated with the CURRENT_TIMESTAMP value!


SSIS package Job Fails with Unexpected Termination I scheduled a SSIS package in SQL Server 2012 Agent which fails sometime with "Unexpected Termination" without any error message.

Any Idea why it happens?

This problem occurs with SQL Server 2012 SP1.

I went through these KB articles

https://support.microsoft.com/en-us/kb/2837964
and
https://support.microsoft.com/en-us/kb/2993859

and installed the suggested hot-fixes that resolved the error.


Using Standard Edition for a VLDB In order to save licensing costs I'm evaluating whether I can get away with Standard Edition for a VLDB. I'll outline the scenario:

Here, "VLDB" means 5TB.
The size is expected to be stable over time.
Almost all data would be in a single table.
The query and DML pattern is a key-value type of scenario. There are only single-row accesses by primary key.

I wonder what I would need Enterprise Edition for. I would not derive any benefits from partitioning because I don't want to switch partitions or place them on different storage. Data compression could save us maybe 50% but we use cheap storage. The saving would be insignificant compared to the expensive license.

For HA I don't see why I couldn't go with the deprecated but still working Mirroring. That's not ideal but Enterprise Edition is a high price to pay for unclear benefit.

Still, it makes me nervous to use Standard Edition here because it is not what's generally recommended and it seems vaguely risky. Looking for some advice on this situation. Thanks!

Here is my two cents in the form of an answer...

Kin pointed out the CPU and memory limitations of standard edition (16 cores/4 sockets and 128GB - though see Buffer Pool Extension), which I completely agree with being the largest obstacle. If you're server is going to be under those requirements then it's a moot point.

Aaron pointed out that if you use SQL 2016 you'll get a less flexible form of AGs. Honestly, to me, it doesn't sound like this will work for you. Cheap storage would be the death of it and if you're considering standard edition I highly doubt this database is that important. Not saying all important databases need to be enterprise edition - but the fact that you're in cheap storage and looking at SE says it all.

Additionally you're not looking at any enterprise level features which could help you. Range partitioning the key/value pairs, compression, column store indexes on the KVPs, TDE/AE, etc.

So, it seems, at the end of the day you're looking for someone to talk you INTO using enterprise edition. I don't know what the usage patterns of the database are, the load, or what it supports for the business... so unless it requires more than the CPU/Memory of standard edition I see no need with the information provided to go with enterprise edition.

The last thought is if this will be virtualized - if so, depending on licensing (enterprise of the host[s]) that's where it may make sense. This wasn't included in the discussion so I'm just throwing it out there.

See also: Features Supported by the Editions of SQL Server 2014


Find out number of active locks on a table I was wondering if there is a way to find out the number of active locks held on a table in SQL Server?

Here is a start. Remember that locks can go parallel so you may see the same object being locked on multiple resource_lock_partition values.

USE yourdatabase;
GO

SELECT * FROM sys.dm_tran_locks
  WHERE resource_database_id = DB_ID()
  AND resource_associated_entity_id = OBJECT_ID(N'dbo.yourtablename');

Please look at the documentation for sys.dm_tran_locks


Extract mdf & ldf files from backup file? Is there a way to extract SQL Server files (.mdf & .ldf) from a backup file (.bak) to a physical location, without restore database?

I need a restore query or script to restore files from bak file only, no attaching to server.
Please don't speak about detaching query after restore.

ApexSQL has recently released a tool that can do this. It's ApexSQL Restore. I haven't tested it fully yet, but you have to connect to a SQL Server instance to do this. It attaches database backup file and creates MDF and LDF files for the database.


MySQL - Deleting old data; Select with index taking time I am a bit confused about 2 queries.

First query I am running on a 50m rows table by index on a timestamp row with a limit, I would expect the query to be immediate regardless of the size of the table, since I run on an index with small limit. Am I wrong?

SELECT 1
FROM indicators.fileso fo
WHERE fo.lastSeen < NOW() - INTERVAL 2 MONTH
limit 100;

Create table:

CREATE TABLE `fileso` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hostId` int(11) unsigned NOT NULL COMMENT 'code',
  `sha256` binary(32) NOT NULL COMMENT 'meir',
  `fileName` varchar(150) DEFAULT NULL COMMENT 'meir',
  `fullPath` varchar(350) DEFAULT NULL COMMENT 'meir',
  `lastSeen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `uniqueness` binary(32) NOT NULL DEFAULT '0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniqueness` (`uniqueness`),
  KEY `lastSeen` (`lastSeen`)
) ENGINE=InnoDB AUTO_INCREMENT=1842469487 DEFAULT CHARSET=utf8

Explain:

Second query when I select by timestamp row with index, with order by desc limit 1 its immediate, but when I do order by asc limit 1 it takes alot of time, dont understand that.

Immediate query:

SELECT 1
FROM indicators.fileso fo
ORDER BY fo.lastSeen DESC
LIMIT 1;

Takes time:

SELECT 1
FROM indicators.fileso fo
ORDER BY fo.lastSeen ASC
LIMIT 1;

(This does not address your question directly; rather it addresses your goal of deleting old records.)

Plan A

You are talking about DELETEing "old" records.  Assuming you are removing "all" the old records, then PARTITIONing is an excellent way to do that.  PARTITION BY RANGE(TO_DAYS) by weeks.  Use DROP PARTITION (instantaneous and non-invasive) to get rid of the oldest week and REORGANIZE PARTITION to add a new partition.  Details.  It will take some downtime to initially partition your 50-million-row table.

Plan B

Alternatively, you can continually walk through the table, using the PRIMARY KEY for keeping track of where you are.  Find the 1000th row hence & delete any 'old' records.  Repeat.  Details.  And it does not need an index on lastseen; removing it could be an efficiency bonus.

Since the table is clustered on the PK(id), walking through the table is very efficient -- very little I/O for each step.  (For unknown reasons, your original solution was more I/O-bound than it should have been.)


What is the meaning of DOP in the context of SQL Server? What is the meaning of DOP in the context of sql server?

DOP in SQL Server stands for Degrees of Parallelism.

Per the MSDN article here, if SQL Server has multiple processors available, and the query meets certain thresholds, SQL Server will try and split up the query over multiple processors to increase its performance.

In earlier versions of SQL Server, I have seen parallelism actually make queries run slower; that is, the time to spend figuring out the parallelism takes longer than the actual query would take without it.

In queries, you can specify the maximum number of processors to use by using the OPTION (MAXDOP X) query hint, where X is the maximum number of processors available.  You can turn off parallelism by using OPTION (MAXDOP 1).

There is also a known issue with parallelism and SCOPE_IDENTITY() in SQL Server 2005/2008.


Generic script for stored procedure rollback We're trying to switch to feature-driven development in my organization. We're planning on using a new branch on our source control for each feature and merge when development is complete. I think it will work great for application code since the rollback is to remove the merge check-in. For our db schema work might be difficult though.

Is there a way of retrieve the current version of a stored procedure, by code, at the time a new version is being deployed in order to create a rollback script automatically?

You can capture the event data with EVENTDATA() function and use a ddl trigger to persist the information into a version table, which can be used as rollback script, if needed. That way you will have a rollback script with timestamp stored in the database, no matter what happens within your version control. My sample implementation code is given below.

-- create version table to store procedure versioned text with timestamp
create table sp_verison(
event_type nvarchar(24),
obj_name nvarchar(256),
obj_type nvarchar(24),
sql_text nvarchar(MAX),
principal nvarchar(50),
log_date datetime);

go

--create trigger to capture proc ddl events
create trigger trg_sp_version
on database
for create_procedure, alter_procedure, drop_procedure
as
insert into sp_verison
(event_type, obj_name, obj_type, sql_text, principal, log_date)
values(
eventdata().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(24)'),
eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(256)'),
eventdata().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(24)'),
eventdata().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
eventdata().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(50)'),
current_timestamp);

go


Add new columns on existing or auxiliary table? I have a database with the following table:

stores
- ID
- name
- phone number
- email address

After adding around 4000 stores to the database we figured that we will need to allow store owners to create a more complete profile. This means we will have the following optional fields:

address, zip code, description  and website URL

My question: should I add these new columns on the existing table or create a new one called store_details, with a foreign key to main table?

I am using MySQL, and each store can only have 1 of those fields.

Consider that only 30% of store owners are going to use them by creating a complete profile.

I am mostly worried with performance when the database grows to say 500,000 entries. For example, when fetching all stores to perform a keyword search. One could argue that by using an axillary table I could save some memory both on disk and on RAM when manipulating the data, but not not sure if this is significant or not.

You haven't specified a database platform, but my experience with MS SQL server is that you should put those fields in the same table, unless you need them separate for other reasons (like if a single store can have multiple addresses).

Any performance concerns you have can likely be resolved with proper indexing, based on how you believe the table will be queried.


Plpgsql seems to be deleting and inserting instead of updating - Why? I am using postgreSQL 7.4.

I have a large table , call it table_a:

key1 INT NOT NULL,
key2 INT NOT NULL,
data INT NOT NULL,
itstamp INT NOT NULL DEFAULT (date_part('EPOCH'::text, (timeofday())::timestamp without time zone))::INTEGER

and a table that summaries the last update time for key1, call it table_b:

key1        INT NOT NULL,
max_itstamp INT NOT NULL

I created a trigger function in plpgsql to update or insert rows in table_b as necessary:

CREATE OR REPLACE FUNCTION table_b_update() RETURNS TRIGGER AS '
 DECLARE
  l_key1 INT;
  l_itstamp INT;
 BEGIN
  l_key1 := new.key1;
  l_itstamp := new.itstamp;
  PERFORM TRUE FROM table_b WHERE key1=l_key1;
  IF NOT FOUND THEN
   INSERT INTO table_b(key1, max_itstamp) values (l_key1, l_itstamp);
  ELSE
   UPDATE table_b SET max_itstamp=l_itstamp WHERE key1=l_key1;
  END IF;
  RETURN NULL;
 END'
LANGUAGE plpgsql IMMUTABLE;

and then I attached a trigger to table_a:

CREATE TRIGGER table_a_trigger1 AFTER INSERT OR UPDATE ON table_a FOR EACH ROW
EXECUTE PROCEDURE table_b_upate();

Now, the time to insert new data into table_a grows incrementally.  The file footprint of table_b grows steadily.

I have used RAISE NOTICE commands in the function to confirm that the If statement causes an UPDATE and not an INSERT after the first call per key.

Since the table_a insert time grows for each INSERT, I tried a VACUUM FULL on table_b.  The table_a insert time was reduced considerably.  The file size for table_b was reduced considerably.  After the VACUUM FULL the table_a insert time started to grow again.  I don't want to do a VACUUM FULL after every INSERT into table_a though.

Is it possible that the UPDATE is actually doing a DELETE and INSERT in table_b?

PostgreSQL doesn't really do updates, it does the equivalent to delete and insert in terms of how it does them.  It leaves the old version of the row in place and creates a new version that all future transactions will see.  Eventually vacuum reclaims the old row space for reuse.  A word of advice, update immediately to a supported release. 7.4 is VERY old, is no longer supported, and has known data eating bugs that will never be fixed.  I'd recommend going right to 9.0, and researching the issues with removed implicit casts which will be the biggest problem.  We had to fix three queries in out app when we went to 8.3 the first version to remove implicit casts.


Extract MySQL information from a compressed tar.gz file I have a back up-of a MySQL database which is contained in a .tar.gz file - I need to restore it.

I've never had to do this before. The only way I can access the database is through phpmyadmin. There is an 'import' feature in phpmyadmin, but this doesn't accept files in the tar.gz format, only sql.

Is there any way I can convert from .tar.gz to sql? I know I can upload from an sql file without a problem.

I don't have access to any 'back-end' stuff on the web-server, so I'm limited with what I can do locally on my PC.

Any help is much appreciated.

Actually it seems a backup by copy files (as tables are myisam tables) not a mysqldump. So you just need to untar this compressed file by below command on linux-

$ untar -zxvf your_backup.tar.gz .

Note: Please run this command from the directory where your backup file exist and it will provide you normal backup also in same directory.

Now you can simply copy these files (*.frm, *.MYI, *.MYD) to your database directory in which Database you want to restore them and it should work.

Note: if there is any permission related issue then use below command to make sure mysql can use these files/tables.

$ chown -R mysql:mysql /path_till_your_db_directory/*


How to log or see all MySQL errors? I have a website, which collects input from the user. When his data cannot be inserted into a database, I simply display "Sorry..." I am hosting my website on LAMP. I am new to MySQL and would like to know how to log all the errors. For example, I want to log/see why exactly user was not able to submit data properly: maybe he violated integrity constraints etc. Is it possible to do?

In PHP, consider using

error_log($str);

It will write to the web server's log file.

$str should be constructed from diagnostics provided by MySQL or php.  You might consider prefixing the string with some keyword to make the log file more easily searchable.

Alas, this means identifying all places in your code that might have "errors" or "warnings" and adding code to grab the relevant message.  But it does put all the messages into a central location for later study.


Oracle 11g R2 on CentOS 6.3, and Net Configuration Assistant This is my first time installing Oracle 11g R2 on Linux. I followed all the instructions in the Oracle documentation, but in the installation process, the Net Configuration Manager is failed. I continued the process, and after it finished, I couldn't connect to sqlplus.

tnsnames.ora

# tnsnames.ora Network Configuration File: /Oracle/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.localhost)
    )

listener.ora

# listener.ora Network Configuration File: /Oracle/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /Oracle/u01/app/oracle

Output of lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 29-MAR-2013 02:35:48

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused

I used ./netca to reconfigure the listener, but the program told me that the port is in use. I deleted it and added a new one, but the same warning that port is in use. When I kill the process that uses this port (1521). and reconfigure the listener, the same message appears that the port is used.

When I run lsnrctl status, I get:

  Services Summary...
  Service "orcl.localhost" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
  Service "orclXDB.localhost" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...

But when I use sqlplus and enter user-name SYSTEM and its password, I get:

  ERROR: ORA-12541: TNS:no listener

Community Wiki answer containing the solution originally added to the question by the author.

Finally I found the solution: I didn't change permission to be owned by the oracle user and oinstall group for the database dir in which the Oracle software is. I think there are some scripts or programs related to listener and the oracle user could not run it.


SQL Assignment query I have 2 databases - one with customers' data and the second one with discount codes. I would like to assign code to each customer. The code can be assigned only once, so I would like to mark the code as 'used' once it is assigned. The codes should be assigned only to those customers that don't have a code assigned. Unused code can be assigned to any customer.
CUSTOMER_DATABASE:

CUSTOMER ID
CUSTOMER CODE

1
6f73js

2

...
...

DATA_DISCOUNT_CODES:

ID
CODE
STATUS

1
6f73js
USED

2
kdw45s

...
...
...

Could you please help me write SQL query for this, using MySQL 8.0 syntax?

This is a bit tricky as you have to generate ROW_NUMBERs for both tables to join them.
IF you want only to include certain customers you need further criteria than CUSTOMER CODE IS NULL.
And a word of advise, don't use spaces in your column names, you need to encapsulate them always with backticks, that makes much more work, than having column names that lack spaces.

CREATE TABLE CUSTOMER_DATABASE (
  `CUSTOMER ID` INTEGER,
  `CUSTOMER CODE` VARCHAR(6)
);

INSERT INTO CUSTOMER_DATABASE
  (`CUSTOMER ID`, `CUSTOMER CODE`)
VALUES
  ('1', '6f73js'),
  ('2', NULL),

  ('3', NULL);

CREATE TABLE DATA_DISCOUNT_CODES (
  `ID` INTEGER,
  `CODE` VARCHAR(6),
  `STATUS` VARCHAR(4)
);

INSERT INTO DATA_DISCOUNT_CODES
  (`ID`, `CODE`, `STATUS`)
VALUES
  ('1', '6f73js', 'USED'),
  ('2', 'kdw45s', NULL),
  ('3', 'and43d', NULL);

UPDATE CUSTOMER_DATABASE T1
INNER JOIN (SELECT `CUSTOMER ID`
, ROW_NUMBER() OVER ()  rown
FROM CUSTOMER_DATABASE WHERE `CUSTOMER CODE` IS NULL) T2 ON T1.`CUSTOMER ID` = T2.`CUSTOMER ID`
INNER JOIN (SELECT `CODE`
, ROW_NUMBER() OVER ()  rown
FROM DATA_DISCOUNT_CODES
WHERE `STATUS` IS NULL) T3 ON T2.rown = T3.rown
INNER JOIN DATA_DISCOUNT_CODES T4 ON T3.`CODE` =  T4.`CODE`
SET T1.`CUSTOMER CODE` = T4.`CODE`,
      T4.`STATUS` = 'USED'

✓

SELECT * FROM CUSTOMER_DATABASE;

CUSTOMER ID | CUSTOMER CODE
----------: | :------------
          1 | 6f73js
          2 | kdw45s
          3 | and43d

SELECT * FROM DATA_DISCOUNT_CODES

ID | CODE   | STATUS
-: | :----- | :-----
 1 | 6f73js | USED
 2 | kdw45s | USED
 3 | and43d | USED

db<>fiddle here


Error when running job using SSIS package I have  SQL Server 2008R2 installed on my Windows server 2008.
I created a package using the Import and Export Wizard from file system.
After that I created a job (SQL Agent Job ) that used the SSIS package I created in the first step.

After I run this job I would receive the following error:

  Date,Source,Severity,Step ID,Server,Job Name,Step
  Name,Notifications,Message,Duration,Sql Severity,Sql Message
  ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
  10/10/2017 17:38:12,update job,Error,0,DBServer\ABC,update job,(Job
  outcome),,The job failed.  The Job was invoked by User
  mydomain\myuser.  The last step to run was step 1 (update step
  1).,00:00:10,0,0,,,,0 10/10/2017 17:38:12,update
  job,Error,1,DBServer\ABC,update job,update job,,Executed as user:
  mydomain\myuser$. Microsoft (R) SQL Server Execute Package Utility
  Version 10.0.6000.29 for 32-bit  Copyright (C) Microsoft Corp
  1984-2005. All rights reserved.    Started:  5:38:12 PM  Error:
  2017-10-10 17:38:17.80     Code: 0xC00291EC     Source: Drop table(s)
  SQL Task 1 Execute SQL Task     Description: Failed to acquire
  connection "DestinationConnectionAdoNET". Connection may not be
  configured correctly or you may not have the right permissions on this
  connection.  End Error  Error: 2017-10-10 17:38:17.81     Code:
  0xC00291EC     Source: Preparation SQL Task 1 Execute SQL Task
  Description: Failed to acquire connection
  "DestinationConnectionAdoNET". Connection may not be configured
  correctly or you may not have the right permissions on this
  connection.  End Error  DTExec: The package execution returned
  DTSER_FAILURE (1).  Started:  5:38:12 PM  Finished: 5:38:17 PM
  Elapsed:  5.578 seconds.  The package execution failed.  The step
  failed.,00:00:10,0,0,,,,0

I did some research and I found that the SSIS service was not installed.
I installed SSIS and I can connect to it using SQL Server Management Studio.
After that I created new job using same file but still I received same error as before.
Please advise how to fix this problem

So the SSIS service has been installed, but is it actually running?  Also, make sure that the domain and username are the same as those you used to setup the service.  You need to check the permissions settings for the database and server you are trying to configure.


If you are using SQL Server 2005 onward you can use the ROW_NUMBER() function:

WITH cte_GetLatest
AS
(
  SELECT ID, [Date], Alloc, Prod, Water, Tub, Cas,
         ROW_NUMBER() OVER (PARTITION BY ID,
                                         Alloc,
                                         Prod,
                                         Water,
                                         Tub,
                                         Cas
                            ORDER BY Date) as rownumber
  FROM Example

)
SELECT *
FROM cte_GetLatest
WHERE rownumber = 1

Lothar


Configure Mysql client timeout? Is there a way to configure the timeout for inactive clients with mysql?

Example:
A MySQL DB has a limit of 100 client connections but some clients are not using the connection any more. I assume MySQL will eventually drop the connection but is this configurable?

There are a number of ...timeout... parameters that can be configured for a MySQL instance. Here are a few selected parameters (Ver. 8.x) for your convenience:

Reference: 5.1.7 Server System Variables (MySQL | Documentation)

interactive_timeout

  The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also wait_timeout.

wait_timeout

  The number of seconds the server waits for activity on a noninteractive connection before closing it.

  On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout.

Answering Your Question

  Is there a way to configure the timeout for inactive clients with mysql?

It depends. Globally you can define a timeout with the wait_timeout parameter of your MySQL instance. If you are using the specific option CLIENT_INTERACTIVE of the mysql_real_connect() function, then you can use the interactive_timeout parameter.


Join results and sum of likes in a different table I have two databases, articles and article-likes.
I need the query to select everything from the article and add a row which is the sum of likes (we have -1 for dislike and +1 for like) from the table article-likes by searching for their ID.

Articles table:

 Article-Likes table:

For this instance, the article with ID Lorem-Ipsum should have 2 likes. On the other hand, Sit-Amet should have 1 like (1 + -1).

You can use a subquery to sum all likes of each article.

SELECT author,
       name,
       text,
       id,
       (SELECT SUM(value)
        FROM Articles-Likes
        WHERE articles_id = Articles.ID) as Likes
from   Articles


Single value from a list of comma separated values when used in inner join in SQL Server Can anyone please help me with a query to return a single value from a list of comma separated values when used in inner join. I can paste the example query here.

select *
from Test.dbo.Test1
   INNER JOIN  @TestTable T ON T.Name = ????

The table given is Test.dbo.Test1 .I had inserted the values to a table variable @TestTable and doing an inner join. The table is:-

Id  Name
1   John,Mili,Anil,Abhi
2   John,Abhi
3   John
4   Mili,Anil,Abhi
5   Anil
6   John,Mili,Anil,Abhi

I need a query in '???' space where I can select a single value from comma separated values in 'name' field and compare with the other.

mdbo and @ are hints it's MS Sql Server. Use a splitter table valued function of choice, Jeff Moden's one http://www.sqlservercentral.com/articles/Tally+Table/72993/ performs very good. So

select *
from Test.dbo.Test1 a
   INNER JOIN  @TestTable T on T.Name in (select item from [DelimitedSplit8K](a.Name, ','))

But may I say the best way to solve the problem is to have no such a problem. Any chance Test.dbo.Test1 could be refactored to have a separate row for every item from Name column? Then take this opportunity.

it's possible to use plain like, but note it prevents optimizer from using any index. In the above version at least index on T.Name can be used.

select *
    from Test.dbo.Test1 a
       INNER JOIN  @TestTable T on ','+a.Name+',' like '%,'+T.name+',%'


User creation and rights to import a dump I'm having problems creating an instance of Oracle 10.2.0.4 and importing a dump from other environment. I'm using Oracle under AIX.

I used a ksh script and everything goes right except when creating the users.

After that I create the default user, used to connect to the database and other working modules. The problem comes when trying to import the dump.

export ORACLE_SID=HISTO;
sqlplus / as sysdba
CREATE USER ANC IDENTIFIED BY "xxxxxxx";
grant connect, create session, imp_full_database to ANC;
commit;

The error when trying to import de dump is the following.
Command used to load the dump: nohup impdp ANC/xxxxxx DIRECTORY=MIG11G  DUMPFILE=dump_histo.expdp LOGFILE=impdp_29102013.log schemas=HISTO &

Output from the nohup.out file:

Import: Release 10.2.0.4.0 - 64bit Production on Lundi, 23 Décembre, 2013 18:51:22

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "ANC.SYS_IMPORT_SCHEMA_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 871
ORA-01950: no privileges on tablespace 'TOOLS'

My question: Is it normal haven't got system user to load a dump file ?
Why the load of the dump file fails ?

The error you receive means that the user ANC does not have any quota on the TOOLS tablespace, as FreshGrinchOfSO says.
From your code, it seems that your default permanent tablespace is TOOLS (and not USERS as usual).

SELECT * FROM database_properties WHERE property_name LIKE 'DEFAULT%TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                 Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE   USERS                Name of default permanent tablespace

Otherwise, you could create the user ANC just like this way:

CREATE USER anc IDENTIFIED BY anc
       DEFAULT TABLESPACE tools;

To verify the quota of ANC on TOOLS use this query:

SELECT tablespace_name, username, bytes, max_bytes FROM dba_ts_quotas;

You will obtain "no rows selected".
To assign a quota to ANC on TOOLS you can proceed as follows:

expdp histo/passwd estimate_only=y NOLOGFILE=y
..
Total estimation using BLOCKS method: 1.256 KB
..
ALTER USER anc QUOTA 5m on tools; -- a x3, safety margin :-)

Then perform the import:

impdp anc/anc directory=MIG11G dumpfile=dump_histo.expdp schemas=histo

If the job seems to be suspended, check for errors in the alertlog file. For example:

statement in resumable session 'ANC.SYS_IMPORT_SCHEMA_01' was suspended due to
    ORA-01536: space quota exceeded for tablespace 'TOOLS'

Then, it is necessary to increase the quota, than the job will resume automatically.

statement in resumable session 'ANC.SYS_IMPORT_SCHEMA_01' was resumed

To see all current jobs:

sqlplus / as sysdba
SQL> SELECT job_name, state FROM dba_datapump_jobs;


What is the performance difference between IN (42) and id = 42 in MySQL? Are there any performance differences between

SELECT *
FROM table
WHERE id IN (42);

and

SELECT *
FROM table
WHERE id = 42;

?

The question is about just supplying a single value. The natural assumption for me is that the optimizer will treat them as equal and perform the same optimization when fetching the data. But is that correct?

Whether the queries are equivalent or not is up to the MySQL Query Optimizer. Why ?
Back n Mar ,13 2013 I wrote an answer to this post: Is there an execution difference between a JOIN condition and a WHERE condition?
In that post I described exactly how JOINs are executed. The following is taken from my post which quotes from page 172 of Understanding MySQL Internals:

Determine which keys can be used to retrieve the records from tables, and choose the best one for each table.
For each table, decide whether a table scan is better that reading on a key. If there are  a lot of records that match the key value, the advantages of the key are reduced and the table scan becomes faster.
Determine the order in which tables should be joined when more than one table is present in the query.
Rewrite the WHERE clauses to eliminate dead code, reducing the unnecessary computations and changing the constraints wherever possible to the open the way for using keys.
Eliminate unused tables from the join.
Determine whether keys can be used for ORDER BY and GROUP BY.
Attempt to simplify subqueries, as well as determine to what extent their results can be cached.
Merge views (expand the view reference as a macro)

On that same page, it says the following:

In MySQL optimizer terminology, every query is a set of joins. The term join is used here more broadly than in SQL commands. A query on only one table is a degenerate join. While we normally do not think of reading records from one table as a join, the same structures and algorithms used with conventional joins work perfectly to resolve the query with only one table.

From the aforementioned information, JOIN behavior will execute the same say regardless of a query having multiple tables or event just one table.
YOUR ORIGINAL QUESTION
Under the hood, MySQL will evaluate the two queries the same way. If you want better query performance, you have to take the bull by the horns. You should do all you can to the table so that MySQL join behavior goes as smooth as possible.

Add the needed indexes
Increase session level buffers (sort_buffer_size, join_buffer_size)
Take advantage of Storage Engine mechanisms for tuning data and indexes
Refactor the query

If you look at dimitar's answer, now it spells out a case where MySQL's join behavior is put to the test. Instead of betting on two horses you own (your queries) to see who runs better, invest time into getting a faster horse if such a horse exists.
From ditimar's post, you have these

SELECT * FROM table WHERE id IN  (42,43,44,45);
SELECT * FROM table WHERE id = 42 or id = 43 or id = 44 or id = 45;

Here is yet another one I suggest for the sake of example
SELECT A.* FROM table A INNER JOIN
(SELECT 42 id UNION SELECT 43 UNION SELECT 44 UNION SELECT 45) B
USING (id);

and another
SELECT * FROM table WHERE id = 42
UNION
SELECT * FROM table WHERE id = 43
UNION
SELECT * FROM table WHERE id = 44
UNION
SELECT * FROM table WHERE id = 45;

I can make up other possibilities, but the main idea here is to try to write good queries the first time. When your amount of data grows, your best queries may suffer due to key distribution and stale index stats which may require optimizing tables or even rewriting queries to suit bigger data.


How to create lots of SQL-Developer Connections quickly? I wonder if there is a way to create lots of SQL-Developer connections in "one shot" ?

  Eg: By modifying a config file or password file.

SQL Developer stores its connection information in (on Windows):

%HOMEPATH%\Appdata\Roaming\SQL Developer\system18.1.0.095.1630\o.jdeveloper.db.connection\connections.xml

The path obviously changes within different versions because of the version number.

I am not aware of any built-in solution for adding entries except the GUI, but it is not that difficult to copy an existing entry and modify the values. Passwords are stored in a hashed form, copying an existing hash value works as well.


pg_restore ERROR: "Relation does not exist" and creating new database I have made a backup of my specific tables that I want to restore into a new database using:

call pg_dump -Fc -h server -d database -U user -p password -v -f dump.sql -t public.table1 -t public.table2

And I have no problems.

I then want to restore the dump by creating a new database with pg_restore using:

call pg_restore --clean --create -d temp -h server -p password -U user dump.sql

This gives me a "database temp does not exist" error. This should have created the database and restored it as far as I understand.

I However then create the "temp" database manually and run:

call pg_restore --clean --create -d temp -h server -p password -U user dump.sql

This follows through, but does not create the tables and gives me an error "relation table1" and "relation table2" does not exist and only creates the corresponding id_sewuences for the two tables.

What I actually want is to not have to manually create the new database and that all tables in the backup is restored into a brand new database via pg_restore using:

call pg_restore --clean --create -d temp -h server -p password -U user dump.sql

as I understand it.

Please Help, very frustrating

When --create and -d are used together, the argument to -d is not the name of the database to create, it's the name of an existing database to connect to run the CREATE DATABASE statement, because it's impossible to create a database if you're not already connect to another database.

This is documented as:

  When this option is used, the database named with -d is used only to
  issue the initial DROP DATABASE and CREATE DATABASE commands. All data
  is restored into the database name that appears in the archive.

This is why pg_restore errors out with database temp does not exist

You should add a createdb step in your restore procedure since you want to create a specific database name anyway, not one that comes from the backup.

For the second problem, it's a completely different question, hard to guess why it happens without complete error messages.

Also note that -p is not followed by a password but by a port number.


what could be the fastest way to delete all data from a partition of a partitioned table? I have a partitioned table that in real life has 80 million rows.

for testing purposes I have created and partitioned this table here.

when I run the following query:

select * from countries
where visit >= '20110101'
  and visit <= '20111231'

as you can see in the query plan here and on the picture below, it uses partition elimination so I know I am doing something right.

I am aware that partition generally is not meant to speed up my queries, it is a management feature, however, it can speed up queries on large tables.

I will start by stating what I don't want.
I don't want to remove any partition from my table.

what I want?

I want to delete all data from a partition in the quickest possible way:

can something be quicker than this?
without considering deleting in batches

BEGIN TRANSACTION T1

DELETE
FROM dbo.countries WITH (TABLOCKX)
WHERE visit >= '20110101'
  AND visit <= '20111231'

--COMMIT TRANSACTION T1

query plan is here

SQL Server 2014 unfortunately doesn't support TRUNCATE on a partition. Either drop and recreate it or switch it out.

See longer discussion here.

SQL Server 2016 does support truncating partitions. If you're on that version, that's definitely your fastest option.


Postgresql function to create table I want to create a function in order to create a table with a specific structure pasing part of the name of the table as an argument so the name of the table is t_ . Similar to this:

CREATE OR REPLACE FUNCTION create_table_type1(t_name VARCHAR(30)) RETURNS VOID AS $$
BEGIN
    EXECUTE "CREATE TABLE IF NOT EXISTS t_"|| t_name ||"
    (
    id SERIAL,
    customerid INT,
    daterecorded DATE,
            value DOUBLE PRECISION,
    PRIMARY KEY (id)
    )"
END
$$ LANGUAGE plpgsql

Then call it like:

SELECT create_table_type1('one');

Is it possible?

Answer is yes. :)
CREATE OR REPLACE FUNCTION create_table_type1(t_name varchar(30))
  RETURNS VOID
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('
      CREATE TABLE IF NOT EXISTS %I (
       id serial PRIMARY KEY,
       customerid int,
       daterecorded date,
       value double precision
      )', 't_' || t_name);
END
$func$;

I am using format() with %I to sanitize the table name and avoid SQL injection. Requires PostgreSQL 9.1 or above.
Be sure to use single quotes ('') for data. Double quotes ("") are for identifiers in SQL.


track down which application / procedure / function modified the value of a column in a specific table in our enterprise application, value in one column of a table is being modified at client side. This happen usually once in a week. i have verified in all stored procedures and other modules, value in that column is just inserted first time (when some request is generated through web app) but never updated again. so it seems quite strange. now i have created a temp table and a trigger to track the record when that column is updated, but my problem is i don't know how to track the stored Procedure or any other part of db / or application which is modifying that value.

I have used HOST_NAME(), APP_NAME(), SUSER_NAME() in my trigger. these functions just return the machine IP, and db name "sa", and ".Net SQL client" things, but i need to know the actual SP / Trigger / function name which modify the value.
i cant use CONTEXT_INFO() since i don't know the actual issue point.

Any help from you experts, will be highly appreciated.

This might get you going along the right track. You pass the sql_handle of the existing connection to the table valued function sys.dm_exec_sql_text, and it returns the SQL text being executed.  If you run this example you will see your own code, as @@SPID is your own process ID.

Source Article

SELECT t.text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE session_id = @@spid

Other system resources that may help you on your quest:

select * from sys.dm_exec_connections where session_id = @@spid
select * from sys.dm_exec_sessions where session_id = @@spid
select * from sys.dm_exec_requests where session_id = @@spid


Does the SQL Spec require a GROUP BY in EXISTS () Microsoft currently permits this syntax.

SELECT *
FROM ( VALUES (1) ) AS g(x)
WHERE EXISTS (
  SELECT *
  FROM ( VALUES (1),(1) )
    AS t(x)
  WHERE g.x = t.x
  HAVING count(*) > 1
);

Notice that there is no GROUP BY in the EXISTS clause, is that valid ANSI SQL. Or is it merely exposing an implementation detail.

For reference, this same syntax isn't permitted in PostgreSQL.

  ERROR:  column "t.x" must appear in the GROUP BY clause or be used in an aggregate function

But this syntax is permitted..

SELECT *
FROM ( VALUES (1) ) AS g(x)
WHERE EXISTS (
  SELECT 1  -- This changed from the first query
  FROM ( VALUES (1),(1) )
    AS t(x)
  WHERE g.x = t.x
  HAVING count(*) > 1
);

And this syntax is permitted.

SELECT *
FROM ( VALUES (1) ) AS g(x)
WHERE EXISTS (
  SELECT *
  FROM ( VALUES (1),(1) )
    AS t(x)
  WHERE g.x = t.x
  GROUP BY t.x  -- This changed from the first query
  HAVING count(*) > 1
);

Question arises from a conversation with @ErikE in chat

I found it in the SQL 2011 spec...

  If the <select list> “*” is simply contained in a <table subquery> that is immediately contained in an <exists predicate>, then the <select list> is equivalent to a <value expression> that is an arbitrary <literal>.

This confirms that by * not being equivalent to an arbitrary literal in this context that it is in fact PostgreSQL breaking the spec.

Keep in mind this is a distinct problem from

SELECT *
FROM ( VALUES (1),(2),(3) ) AS t(x)
HAVING count(*) > 1

Which both databases reject.

PostgreSQL,

  ERROR:  column "t.x" must appear in the GROUP BY clause or be used in an aggregate function

SQL Server,

  Column 't.x' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Why this bug persists in PostgreSQL

Thanks goes to RhodiumToad on irc.freenode.net/#PostgreSQL for his help trouble shooting this. He also points out the difficulty in resolving this situation

  20:33 < RhodiumToad> the one problem is that in pg you can do  exists(select func() from ...   where the func() is an SRF that might return 0 rows

An SRF is a set returning function.

In PostgreSQL, we can do for instance use an SRF to generate a series from 1-10 (generate_series is in core)

SELECT * FROM generate_series(1,10);

And, we likewise can put it right here.

SELECT generate_series(1,10);

Two of them together give us a cross-join (cartesian product)

SELECT generate_series(1,10), generate_series(1,2);

But, if either of those return 0-rows you get nothing.. Effectually the same as this

SELECT * FROM ( VALUES (1) ) AS t(x)
CROSS JOIN ( SELECT 1 LIMIT 0 ) AS g;

And, that's the problem with optimizing this out entirely. You can have an SRF in a select-list inside of an EXIST statement that returns 0-rows, and forces the EXISTS to evaluate to false.


Frequent Delete & Insert, impact on performance In my application, we have very few updates. Most of the data gets deleted and inserted.

My question is, how this will impact the performance?

We are using SQL Server 2012.

Table statistics (will these get outdated faster?)
Fragmentation

Please post your experiences.

Thanks in advance!!

Using a DELETE + INSERT pattern instead of also involving UPDATEs can be highly problematic. IMO, it's an anti-pattern. In 99.9% of cases, it's better to use UPDATES when possible.

Usually the DELETE + INSERT pattern is implemented such that the subset of rows is blindly deleted in the target, which can create a tremendous amount of unnecessary write activity because the data is always re-written even if nothing changed.

Not only does this put load on the I/O subsystem, but it can also kill data access concurrency unless snapshot isolation is used (which will multiply the wasted disk write workload). If the default isolation level is used and the data is concurrently read, this pattern can produce deadlocks very readily and in large quantity.

Having said that, one needs to implement UPDATEs carefully because they still have the potential to rewrite rows that haven't changed. Thankfully that's solved easily with a bit of extra code. All in all, it's well worth the effort to use UPDATEs despite the slight added code complexity.

Depending on what you're doing, it may be easier and more reliable to send the entire desired state as a whole to the database using something like a table-valued parameter and sorting things out there instead of on the client side.


Hide column if any value is null or zero via proc sql or view Given either of the two following datasets

Kağıt Cinsi        Sarı      Yeşil      Mavi

Kalın                4         0          5
İnce               null        0          3

or

Kağıt Cinsi        Sarı      Yeşil      Mavi

Kalın                4         0          5
İnce                 0         0          3

I want to filter via either a stored procedure or a custom view to hide any column which contains a row with either a 'null' or 'zero' value.

These are the results: I would expect from either of the above datasets

Kağıt Cinsi       Mavi

Kalın               5
İnce                3

I'm not sure what a query for this looks like, but I want to return it from the database like this.

You can't hide a column dynamically unless you use dynamic SQL. This quickly gets complicated: what if the zeros are in different columns on different rows? What should the output be

If the zeros/nulls are in the same column, then you can "hide" a column by not selecting it.

SELECT
   Kağıt Cinsi, Sarı, Mavi
FROM
   MyTable
WHERE
   (Yeşil IS NULL OR Yeşil = 0)

You may not need the filter here: I added it because the question was unclear


Consistent backup of large DB --single-transaction I need to mysqldump a large DB (330 GB) in order to set up a slave for replication.

Does --single-transaction allow me to dump the entire DB without shutting mysql down and keeping it down for the duration?
If I put a read lock on, can I get my bin information but then can release the lock and dump using --single-transaction so there is minimal downtime to users?

If you want the binlog file and position, just include --master-data.

According to the MySQL Documentation on mysqldump's --master-data option

  Use this option to dump a master replication server to produce a dump
  file that can be used to set up another server as a slave of the
  master. It causes the dump output to include a CHANGE MASTER TO
  statement that indicates the binary log coordinates (file name and
  position) of the dumped server. These are the master server
  coordinates from which the slave should start replicating after you
  load the dump file into the slave.

  If the option value is 2, the CHANGE MASTER TO statement is written as
  an SQL comment, and thus is informative only; it has no effect when
  the dump file is reloaded. If the option value is 1, the statement is
  not written as a comment and takes effect when the dump file is
  reloaded. If no option value is specified, the default value is 1.

  This option requires the RELOAD privilege and the binary log must be
  enabled.

  The --master-data option automatically turns off --lock-tables. It
  also turns on --lock-all-tables, unless --single-transaction also is
  specified, in which case, a global read lock is acquired only for a
  short time at the beginning of the dump (see the description for
  --single-transaction). In all cases, any action on logs happens at the exact moment of the dump.

  It is also possible to set up a slave by dumping an existing slave of
  the master. In MySQL 5.5.3 and later, you can create such a dump using
  the --dump-slave option, which overrides --master-data and causes it
  to be ignored if both options are used.

Here is a sample script

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS="--single-transaction --master-data=2 --all-databases"
mysqldump --all-databases  | gzip > dumpfile.sql.gz

You said the the data is 330GB, so the gzip may be 30-40GB. No worries. If you run

less dumpfile.sql.gz

The CHANGE MASTER TO command with the log file and log position will be located on line 23.

Please keep one thing in mind: You cannot run any DDL against InnoDB tables during the mysqldump or else the tables being dumped after that DDL will no longer be point-in-time copies.

BTW there will be no downtime. Just make sure the binary log in the dump is still in existence on the Master when you load the Slave and setup replication.


Syntax error in PL SQL i'm trying to do a command that gather stats of tables that has more than 10 million rows. Right now only one table has, but i want the command to execute the gathering to more tables as soon as they reach 10 million rows.

Right now I have this command, but i can't find out what is wrong that is preventing it of running ok. I have ~ok~ sql skills, but almost zero experience with Oracle PL SQL.

Can someone point me a direction? Tks in advance.

DECLARE
 CURSOR t1 is
     select table_name from dba_tables where owner='SAPIENS_TESTE' and num_rows > 10000000;
BEGIN
 OPEN t1
 FOR t IN t1 LOOP
    EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SAPIENS_TESTE', TABNAME => '||t.table_name||', partname => NULL, granularity => 'GLOBAL', CASCADE => TRUE, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
 END LOOP;
END

DECLARE
 CURSOR t1 is
     select table_name from dba_tables where owner='SAPIENS_TESTE' and num_rows > 10000000;
BEGIN
 FOR t IN t1 LOOP
    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SAPIENS_TESTE', TABNAME => t.table_name, partname => NULL, granularity => 'GLOBAL', CASCADE => TRUE, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
 END LOOP;
END;
/


Delete Only Log Backup History in SQL Server Is it possible to run something like dbo.sp_delete_backuphistory to only delete transaction log backups without rolling your own scripts? Using Ola's maintenance scripts, we do 5 minute log backups on all our critical databases and nightly differential and weekly full backups. This means we get about 8500 transaction log backups in a month, but only 30ish differentials and 4-5 full backups.

We keep the full and differentials for much longer than we do log backups, so we'd like to keep the backup history for those, but we'd like to reduce the size of our backup tables (particularly backupset) as it's slowing down our monitoring tool.

There isn't any builtin code to just delete the transaction log backup history. You'd have to roll your own to achieve that.

But I would recommend that you just dump the data that you want to keep in a separate table and purge msdb backup history normally with sp_delete_backuphistory.


Recommended replication strategy for fast global access to MySQL DB The app i'm working on has users from all over the world and due to the nature of the app, i need to minimize all delays. My application servers are located in different data centers across the world and load balancing is in place. The load of the DB isn't that high, so that's not an issue, the issue is simply that i need to minimize delays across long distances somehow.

Currently, there's a single DB server and it's located in Europe and the issue is when my app server in South-East Asia requests data from the DB in Europe, the complete duration for the query easily increases tenfold (from 50ms to 500ms).

My initial idea is to add another DB server in Asia, what kind of replication should i set in that case? Or is this even a good way to go?

Any guidelines and suggestions for this scenario are welcome.

If you only want read access, then Master-Slave will work nicely.  Anything written to the Master will incur a replication delay getting to Asia, but after that, clients in Asia can get the data with very little delay from the Asia slave.

For fast write access everywhere in the world, Master-Master might be viable.  The long delay should not be a problem since the traffic is low.  There are many potential problems with M-M, mostly having to do with primary/unique keys, but perhaps you can resolve that.

NDB Cluster is another possibility.  However, many things are different with it, and the setup is more complex.  It relies on "eventual consistency" wherein you provide rules for what to do when conflicting data is written to different Masters.

In your current setup...

If the operations you perform involve multiple queries (say, a few selects, then an update), there is a simple speed-up.  Combine the queries into a Stored Procedure and perform one CALL.  This cuts the trans-oceanic hops down from "multiple" to "one".  I suspect the ping time from Asia to Europe is about 200ms?  That is how much additional latency for each SQL statement.


Parenthese use in a SET statement I am trying to compute the number of distinct pairs sender_id, send_to_id and assign it to a variable @UniqueRequests. When I try the statmement below, I get an error saying the syntax near ) is incorrect.

DECLARE @UniqueRequests int;
SET @UniqueRequests = (SELECT COUNT(*) FROM (SELECT DISTINCT sender_id, send_to_id FROM friend_request));

So my questions would be

What is the correct statement?
It seems that we must use parentheses around a SELECT when assigninig a variable. That is to say, we need to write SET @MyVariable = (SELECT some_expression); Why is that so?
Is there any documentation page on the use of parentheses in SQL Server in general?

Thank you for your help!

Actually this error is a bit confusing and that's the reason you are asking these questions.

Correct statement would be:

DECLARE @UniqueRequests int;
SET @UniqueRequests = (SELECT COUNT(*) FROM (SELECT DISTINCT sender_id, send_to_id FROM friend_request) friends);

I did testing on some other table with same concept and you may see the result as below:

Answer to these questions are as below:

  What is the correct statement?

DECLARE @UniqueRequests int;
SET @UniqueRequests = (SELECT COUNT(*) FROM (SELECT DISTINCT sender_id, send_to_id FROM friend_request) friends);

  It seems that we must use parentheses around a SELECT when assigninig a variable. That is to say, we need to write SET
  @MyVariable = (SELECT some_expression); Why is that so?

This is because you are setting the value, and you can't assign keyword and other strings to an int, rather you want to assign the value which means query has to run first and that result you want to assign to this particular integer variable.

Alternatively if you wish to only set the value, you can use below:

DECLARE @UniqueRequests int;
select @UniqueRequests = COUNT(*) FROM CommandLog
print @UniqueRequests

  Is there any documentation page on the use of parentheses in SQL Server in general?

You may refer this from Micrososoft, this thread and this link.

Hope this helps.


Using a While function but running out of days I have a DB which includes a datetime column (timestamp).
A new record is written every hour, so 24 records a day.

I want my procedure to look for conditions where SO2_7PCT > 44 and NOX_LB_Validity = 'Valid'.

I want to find the most recent 30 days in which any hour meets these conditions. On days where the conditions are met by at least one hour, I want to pull all 24 hours of that day into a temp table. If 0 hours meet the conditions, skip the day.

Here is the code I wrote. The problem I have with this code is if there aren't 30 days which meet the conditions, it doesn't stop, it keeps going day after day, earlier and earlier, forever. The earliest day I have in my DB is 10/1/2017 but it blows right past that and keeps looking, even though there are no more rows to look at!

What should I do to make it stop when it runs out of rows to look at?

Here's the code:

-- daycount is how many valid days it found.
-- daystart and dayend are the 24 hour period to look at the hours.
-- isonline counts how many valid and online hours that day.
-- daycounter counts how many valid days are in the roll.

declare @daycount int, @daystart datetime, @dayend datetime, @isonline int, @daycounter int

set @daycounter = 0
set @daycount = 30
set @dayend = @EndDate
--set @dayend = dateadd(DD,1,@dayend)
set @daystart = dateadd(DD,-1,@dayend)
print '@daystart'
print @daystart
print '@dayend'
print @dayend

While @daycount > 0 -- if we haven't found 30 useable days then:
Begin
  set @isonline = (select count(So2_7PCT)
                     from Auto_SixtyMinuteEmissionRecords
                    where SO2_7PCT > '44'
                      and NOX_LB_Validity = 'Valid'
                      and type = @Type
                      and timestamp >= @daystart and TimeStamp < @dayend)

  While @isonline > 0 -- Found a good day, need to pull the 24 hours into temp table, increment daycounter, decrement daycount.
  Begin
    insert into @temptable
    select @ from Auto_SixtyMinuteEmissionRecords
     where timestamp >= @daystart
       and TimeStamp < @dayend)

    set @isonline = 0
    set @daycount = @daycount - 1
    set @daycounter = @daycounter + 1
  END

  --Set start and end to -1 day to look at previous day.
  Set @daystart = dateadd(dd,-1,@daystart)
  set @dayend = dateadd(dd,-1,@dayend)
END;

As a general rule, in SQL, you want to accomplish things with set-based operations rather than iterative processes.

So, you want the 30 most recent days where your search criteria were true for at least one entry.

I'd try this:

SELECT TOP (30) DISTINCT CAST(timestamp as DATE) targetDate
  FROM Auto_SixtyMinuteEmissionRecords
 WHERE SO2_7PCT > '44'
   AND NOX_LB_Validity = 'Valid'
   AND type = @Type
 ORDER BY targetDate DESC
;

This will locate all the records that have columnA = 1, take the unique dates from those rows' timestamps, and give you the 30 most recent.

If there aren't 30 days where this is true, you just won't get back thirty rows in the result set.

Of course, this assumes that using WHILE is not a requirement.

You could then use the above query as a derived table, and join it to your INSERT statement, as follows:

INSERT INTO #temptable
SELECT auto.*
  FROM Auto_SixtyMinuteEmissionRecords auto
         INNER JOIN (SELECT TOP (30) DISTINCT CAST(timestamp as DATE) startDay, DATEADD(day, 1, CAST(timestamp as DATE)) as endDay
                       FROM Auto_SixtyMinuteEmissionRecords
                      WHERE SO2_7PCT > '44'
                        AND NOX_LB_Validity = 'Valid'
                        AND type = @Type
                      ORDER BY targetDate DESC
                    ) dates ON (    auto.timestamp >= dates.startDay
                                AND auto.timestamp < dates.endDay)
;

This should put (up to) 30 days' worth of hourly rows into your temp table, in one statement.

NOTE: Code above is untested.


Identity column as clustered index bad idea? Its just been a few months of programming in SQL Server for me so my knowledge is not good in many regards. In an already existing project at work I came across many tables with large composite primary keys with clustered index. From what I have gathered, a large column/composite column with clustered index hits performance very hard, and at times the logical solution is an identity column. But at the same time I have come across many people flaming the over-usage of identity columns.

But I have never came across an example where identity column is a bad idea.

Recently we have standardized that every table should have an identity column as the clustered index - whether we use it as PK or not, as we require it for some export purposes.

So I would like some examples, in real life scenarios, where using an identity column as a clustered index is a bad idea.

Though at times it makes our life easy I have never encountered a scenario where it will be considered bad.

PS: I think my question is a bit naive but it is bugging me so much so I had to ask about it.

I usually use an identity column as clustered primary key. However in some (rare?) cases this is not ideal because of the LastPageInsertLatchContention. This happens if a table is heavely filled with data. Because of the identity key all this INSERT's wants to write the last page of the table (index). So this page can be locked and the performance may be better with another solution.

See

http://dangerousdba.blogspot.ch/2011/10/bit-reversion.html
http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/monotonically-increasing-clustered-index-keys-can-cause-latch-contention.aspx
http://www.sqlpassion.at/archive/2014/04/15/an-ever-increasing-clustered-key-value-doesnt-scale/

for details.


Replicate DB2 table to SQL Server 2008 I need to replicate data from a DB2 database into a SQL Server Database (along the lines of about 80 tables). I can create an SSIS package, to just flat copy the data from one to another pretty easy, but the amount of records is a couple million, so doing it once takes a while, but for the initial setup its fine. However, I need to do "almost" real-time updates, so this method won't be viable. What would be the best method, and how, to do just add change and delete replication from DB2 to SQL Server?

You could create a hidden timestamp column in DB2 on the replicated tables. When you run the replication job you can pull just the data that has changed since the last replication.  This would lower the quantity of data sent.

For instance, run the SSIS package every minute pulling just the last minutes worth of data.

Or, instead of a timestamp field, you could use an auto incrementing int and keep a small table on the replicated instance that records the last int value replicated. The replication process would grab any new records, and update the small table's value to reflect the last value replicated.


Should I use the Infobright database or is there a better aggregation solution? We are a MySQL front-end shop and have a system currently logging event data in a highly structured format. Think apache traffic log.

We need to be able to aggregate counts of this data (cube) vis a vi ad-hoc queries. We are currently sending the data to CouchDB. This is very fast, once a view has mapped completely, but our database size is only 31GB and CouchDB is taking FOREVER to map a new view ( 2+hours ). Retrieving JSON formatted data has worked well for us. But we dread creating new view documents. I'm thinking all Map-Reduce systems may have this problem.

We are evaluating Infobright as they claim to provide superior aggregation performance for ad-hoc queries and are a MySQL fork.

We have evaluated Mondrian and it's not going to work for us.

MySQL/InnoDB is too slow.

We will be around 500GB of log data by the end of the year.

Is Infobright the right solution or should we be also evaluating something else?

Almost always (in my experience), the following is the solution to performance with large, log-like, data and semi ad hoc queries.

Characteristics of the data and the application:

continually arriving data
no updates to 'old' data
optionally purging old data (if so use PARTITION BY RANGE(TO_DAYS()))
queries tend to have a date range in the WHERE clause

Solution:  Build and maintain "summary tables".

pick a timeframe (usually day or hour)
after midnight (or top of hour), summarize yesterday's data from the raw (Fact) table into the Summary table.
PK of the Summary table usually includes some dimension(s) and the rounded-down date/hour.
rest of fields include aggregates like COUNT(), SUM(), etc, but not AVG().
AVG is calculated as SUM(sum_foo)/SUM(ct)
"reports" hit a summary table, not the fact table.
1-10 summary tables usually suffice for a given application
Querying the summary table usually gives you 10x performance improvement; I have seen 1000x in rare cases.

I say "semi ad hoc" because you will find out that users don't really query on everything.  Even if they do, you can have summary table(s) that at least help.

I mentioned the straight-forward way of completely summarizing "yesterday".  There are many other flavors.  One is "as needed", with the aid of INSERT ... ON DUPLICATE KEY UPDATE.


Using Aerospike without SSD Is there a drawback for using Aerospike when used in non-SSD server?

Not at all! Aerospike is a great in-memory key-value store, typically as fast as Redis, but that also scales super easy.

You can check the different configurations possible on Aerospike here. So if you can fit all your data in memory, Aerospike will work as well or better then any other in-memory database. The SSD optimization is useful for larger data sets that would be too expensive to fully store in memory and Aerospike is also optimized for that use case.


What does the Union sign in EER diagrams mean? I know disjoint and overlapping, but what does the union like sign mean in an enhanced entity-relationship (EER) diagram...? For example:

In other words, what does this diagram mean?

It means that the publication can be of type chapter, article, or book. That makes publication different from overlap and disjoint.

When it comes to disjoint or overlap of entities, the topic becomes about a super class and a subclass.

This, however, is more like a kind of category.

For example, if an organization has an owner, and if the owner can be a person, a company, or a bank, then it is a union of entities, if they are separate "tables".


Reduce query time for higher offset in sql server Currently, I have table base_voter with data around 100M of dummy data. I have stored procedure as follows:

CREATE Procedure [dbo].[spTestingBaseVoter]
   @SortColumn    NVARCHAR(128) = N'name_voter',
   @SortDirection VARCHAR(4)    = 'asc',
   @offset INT,
   @limit INT
As
Begin
  SET NOCOUNT ON;

  -- reject any invalid sort directions:
  IF LOWER(@SortDirection) NOT IN ('asc','desc')
  BEGIN
    RAISERROR('Invalid parameter for @SortDirection: %s', 11, 1, @SortDirection);
    RETURN -1;
  END

  -- reject any unexpected column names:
  IF LOWER(@SortColumn) NOT IN (N'name_voter', N'home_street_address_1', N'home_address_city')
  BEGIN
    RAISERROR('Invalid parameter for @SortColumn: %s', 11, 1, @SortColumn);
    RETURN -1;
  END

  --SET @SortColumn = QUOTENAME(@SortColumn);

  DECLARE @sql NVARCHAR(MAX);

  SET @sql = N'SELECT id, name_voter, home_street_address_1, home_address_city
             FROM dbo.base_voter
    WITH(NOLOCK)
    WHERE deleted_at IS NULL'

  SET @sql = @sql + N' ORDER BY ' + @SortColumn + ' ' + @SortDirection +
    ' OFFSET @OF ROWS
    FETCH NEXT @LIM ROWS ONLY ';

  EXEC sp_executesql @sql,
     N'@OF int,@LIM int',
     @OF=@offset, @LIM=@limit
End

To make query faster, I have also created index as :

CREATE NONCLUSTERED INDEX FIBaseVoterWithDeletedAt
  ON dbo.base_voter (name_voter asc,home_street_address_1, home_address_city)
  WHERE deleted_at IS NULL ;

By creating this non-clustered index I have reduced the query time drastically. However, it does not applied same for the query with higher offset.

For example with:

Execute spTestingBaseVoter name_voter,asc,9999950,50

Is there something I am doing wrong, which is causing this performance issue? Or, it is more compulsory to create another index in descending order.

Let, me know if there is any better way to tackle this situation, which might decrease query time drastically.

Update:

Estimated Execution Plan

Answer based on comments originally left by Dan Guzman:

OFFSET isn't magic; time will be progressively slower with higher offsets. Also, you should have a separate index for each column to be ordered but SQL Server can read each either forward or backward so you don't need additional permutations for descending order.

Instead of row-number pagination, you could use key pagination instead, passing the last retrieved values of the ordered column and primary key. The query could then specify `SELECT TOP(n) WHERE '. That would allow users to scroll forward (and backwards with similar logic). If you must do row number pagination, do that in a caching layer.

See Optimising Server-Side Paging - Part I and T-SQL Querying: TOP and OFFSET-FETCH (sample chapter) by Itzik Ben-Gan.

An anchor filter is required to avoid a progressively larger scan, which is costly to return a page far into the result with a large paginated result like yours. I've used that method with multi-billion row tables with sub-second response time, albeit that was back in the SQL 2000 days and I don't think users ever scrolled to the end.


viewing the population? (sql server full text search) I'm thinking that when we are working on full text search, we might want to view how sql server FT engine breaks the data and indexes it. For my case the column being indexed are simple nvarchar strings with spaces (no punctuation).

I'd want to be able to take the "tokens" out. Any idea if this is possible?

I think you need to be having a look at the Full Text DMVs here: http://msdn.microsoft.com/en-us/library/ms174971%28v=sql.105%29.aspx

Specifiably I think you want something like:

SELECT * FROM sys.dm_fts_index_keywords(db_id('AdventureWorks2012'), object_id('HumanResources.JobCandidate'))
GO

or

SELECT * FROM sys.dm_fts_index_keywords_by_document(db_id('AdventureWorks2012'), object_id('HumanResources.JobCandidate'))
GO


Postgresql AutoFailover I am having Java Application with Two nods in cluster however I have Postgresql 1 Master Database server and 3 nodes in Hot-Standby Mode.
Problem: Since DB is a single point of failure, every time patching the servers I need to bring down the entire Application which increases my Application downtime.
Looking for a Solution to implement Postgres DB Autofailover to Stand By node.
My Environment: Ubuntu 18 OS with Postgres v10
Questions:

Postgres 10 Supports Auto Failover?
is it possible to implement AutoFailover of DB using Postgres native tools?
I am planning to configure my application to point to single LB IP which is configured to connect to an active DB server out of 4 Postgres DB serves( Out of 4 one is master). Any recommendation on this is welcome.
How to configure Auto failover for Postgresql DB with minimal failover time?

Np, PostgreSQL doesn't have any built-in facilities for automatic failover.
You have to use third-party software like Patroni.
Client failover can be managed by using a connection string that looks like
host=host1,host2 port=5432,5432 target_session_attrs=read-write


can't execute opatch even I'm in the oinstall group with -rwx--x--- I'm in $ORACLE_HOME/OPatch directory and
I type opatch.  It responds:

bash: opatch: command not found

if I do ./opatch:

/bin/sh: ./opatch: Permission denied

ls -l opatch shows -rwx--x--- so for group the user which is oinstall, it should have the execute right.

id -g does show me in the oinstall group.

So why I can't execute opatch in this case?

First, let me state that I think that running anything as the non-oracle user on your filesystem may result in significant database issues! (I'll explain why in a minute)

Aside from the pathing issue, which you solved, I think that Colin is correct. You (or your admin) does need to give read on the binary:

[root@db01 ~]# useradd captjackvane
[root@db01 ~]# groups captjackvane
captjackvane : captjackvane
[root@db01 ~]# usermod -a -G oinstall captjackvane
[root@db01 ~]# groups captjackvane
captjackvane : captjackvane oinstall

[captjackvane@db01 OPatch]$ ls -ltrh opatch
-rwx--x--- 1 oracle oinstall 31K Mar 26  2013 opatch
[captjackvane@db01 OPatch]$ ./opatch lsinventory
/bin/sh: ./opatch: Permission denied

oracle@db01:(psu1) ~]$ chmod 750 ./opatch
oracle@db01:(psu1) ~]$ ls -ltrh opatch
-rwxr-x--- 1 oracle oinstall 31K Mar 26  2013 opatch

[captjackvane@db01 OPatch]$ ./opatch lsinventory
chmod: changing permissions of `/u01/oracle/app/product/db_1/.patch_storage': Operation not permitted
The chmod command failed for ./u01/oracle/app/product/db_1/.patch_storage.. It is possible that you have no permission to access it. OPatch cannot proceed!
OPatch returns with error code = 1

As you can see, I can now "execute" the binary, but other permissions issues remain. So here's where I get to my first point:

Running things, especially anything that stops/starts a database and/or the cluster as someone other than the Oracle user can lead to serious issues. I remember in my career-youth I accidentally started a development database as my physical user, instead of Oracle. Which meant that many (but not all) of the processes used by the database had my name on them (and by extension, my permissions) instead of Oracle.

Your best bet here is to use the Oracle user, or ask your admin to complete whatever the task is for you.


History of cluster failover Is it possible to get a history of when a cluster failed over and which node became the active node?

Is it possible to get a history of when a cluster failed over and which node became the active node?

That depends on how your define "history". There will be, in the cluster log, system log, and potentially sprinkled in other logs depending on a few things, the events that show a failover has occurred. For example, in the event logs you might search for event 1069 which would tell you that a resource failed in the cluster.

However, logs don't go back until the beginning of time. So if it's extremely recent history you're after then you might be ok. If you're looking at since inception, the answer is, nope you can't if you weren't already capturing this data.

Extended note: It also depends on how you define failover, as per manual or automatic. Additionally if you have the SQL Server engine errorlog, you can parse it to find when the node name is not the same after startup (FCI failover) or the line items for role synchronization changes (AG).


How do I go about easy table input without creating an application? I am converting all my applications from MS Access to PostgreSQL and I have a few personal-use databases. These MS databases have text fields as the primary keys, so I can type the name of something in a reference table rather than typing an arbitrary ID number.

I want to create the databases in postgresql properly (with ID numbers as primary key) - but don't want to have to look up the ID all the time when manually entering info into the database.

Is there a way I can use an INSERT statement (or something similar) and use a name field, rather than the auto ID?

Example of a record table and reference table:

Record Table:

CREATE TABLE testreport.records
(
  recordid serial NOT NULL,
  userid integer,
  CONSTRAINT records_pkey PRIMARY KEY (recordid),
  CONSTRAINT records_r_user_fkey FOREIGN KEY (userid)
      REFERENCES testreport.r_user (userid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

Reference Table:

CREATE TABLE testreport.r_user
(
  userid serial NOT NULL,
  username text,
  CONSTRAINT r_user_pkey PRIMARY KEY (userid)
)

I want to be able to insert data by typing in the username, rather than having to type in the userid. Is there a way I can do this?

Note: All fields in username are unique

Thanks

If I am not missing anything... Assuming you always specify the user name in such a way as to exclude possibility of multiple matches (if e.g. you decided to look the user up by a mask using LIKE rather than by a complete name), you could try inserting data into the record table using an INSERT...SELECT statement like below:

INSERT INTO testreport.records (userid)
SELECT userid
FROM testreport.r_user
WHERE username = 'specific user name';
/* or:
WHERE username LIKE 'some pattern'
- just make sure it matches the single row you meant to reference
*/


Unable to use cyrillic characters in mysql docker console I'm running a MySQL docker container (latest - 8.0) with the custom configuration file containing:

======================================
[client]

default-character-set=utf8mb4

[mysqld]
collation_server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character_set_server = utf8mb4

[mysql]
default-character-set=utf8mb4
======================================

Executing inside MySQL console:

show variables like "%coll%";

produces this:

+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_0900_ai_ci |
| collation_database            | utf8mb4_unicode_ci |
| collation_server              | utf8mb4_unicode_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+

Executing inside MySQL console:

show variables like "%char%";

produces this:

+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8                           |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+

BUT when I go the MySQL console with:

docker exec -it mysql1 mysql -uroot -p*********

I can't use cyrillic (russian) characters. When I switch to Russian locale and try to type anything, the console just ignores keypresses, nothing is displayed on the screen. But when I switch to English locale, everything starts working again.

What can I do to solve this problem?

PS: I AM able to interact with the database via DataGrip and enter Russian characters for table names, fields and records. I'm just completely unable to use Russian characters when I'm inside the MySQL console inside the docker container.

PPS: MySQL is running from an official Docker image on MacOS Mojave and I'm using iTerm 3.2.6. Character encoding in iTerm is set to "Unicode (UTF-8)"

As it turns out this was indeed a Docker problem rather than MySQL.
Vanilla MySQL image is built on Debian 9 and by default it lacks UTF-8 support. So I had to follow these steps:

Login into Docker container with docker exec -it mysql1 bash and run apt-get update
Then run apt-get install vim apt-get install locales
Run dpkg-reconfigure locales and select 376 for ru_RU.UTF-8
Open ~/.bashrc and add

export LANG=ru_RU.utf8
export LANGUAGE=ru_RU.utf8
export LC_ALL=ru_RU.utf8

Exit the shell and restart the container

Now I can use Russian characters inside the MySQL docker console.
PS: I guess a cleaner and more "Dockeresque" solution would be to run these commands from a Dockerfile. If anybody could provide an answer with a dockerfile example, I'd be happy to accept it.


Is there any function like checksum in Oracle I need to calculate the checksum of some columns. The built-in function checksum is used to calculate the checksum in Microsoft SQL Server.

Is there any built-in function checksum in Oracle?

You can use Oracle's STANDARD_HASH function.

  Oracle's STANDARD_HASH function "computes a hash value for a given
  expression" (see the documentation here). Use the checksums in the WHERE
  clause of the UPDATE (in the MERGE statement).

You might do something like this:

-- 2 tables
create table table1 as
select 1 id, 1 a1, 1 b1, 1 c1, 1 d1, 1 e1, 1 f1 from dual;

create table table2 as
select 2 id, 2 a2, 2 b2, 2 c2, 2 d2, 2 e2, 2 f2 from dual;

and then calculate a SHA256 checksum:

SELECT
  STANDARD_HASH ( T.id || T.a1 || T.b1 || T.c1 || T.d1 || T.e1 || T.f1, 'SHA256' )
AS my_hash_1
FROM table1 T ;

MY_HASH_1 0x2558A34D4D20964CA1D272AB26CCCE9511D880579593CD4C9E01AB91ED00F325

and

SELECT
  STANDARD_HASH ( T.id || T.a2 || T.b2 || T.c2 || T.d2 || T.e2 || T.f2, 'SHA256' )
AS my_hash_2
FROM table2 T ;

MY_HASH_2
0xCC2E018AA6EB9612CCD027BBDCDC9B8C8D351789F14CAE4D688A876C18938235

This will tell you if your records are the same or different (well, to a very high probablity! :-) ).

With STANDARD_HASH, you get to choose your hashing algorithm. You might want to be careful about your choice - MD5 is much less processor intensive than SHA256. If security is not a major concern, you might want to use that instead? See the fidddle here for more.

See here for the excellent question, and here (same thread) for the excellent answer, both of which I (shamelessly!) lifted to answer this question. I felt that the thread and answer were both good enough to merit a place on dba.stackexchange!


Postgres PITR: Recovery does not seem to fetch from Archive Dir Make and give permission to database dir

cd /home/admin-12/Desktop/

mkdir test2
sudo chown postgres:postgres test2

mkdir test2_wal
sudo chown postgres:postgres test2_wal

touch test2.log
sudo chown postgres:postgres test2.log

Init Database

/usr/lib/postgresql/11/bin/initdb -D /home/admin-12/Desktop/test2

Edit postgres conf

nano /home/admin-12/Desktop/test2/postgresql.conf

archive_mode = on
archive_command = 'cp %p /home/admin-12/Desktop/test2_wal/%f'

/usr/lib/postgresql/11/bin/pg_ctl -D /home/admin-12/Desktop/test2 -l /home/admin-12/Desktop/test2.log start

Dummy Data

create table testPITR1 as select * from pg_class, pg_description;
select * from current_timestamp;

Backup

SELECT pg_start_backup('label', false, false);
tar -cvzf test2.tar test2
SELECT * FROM pg_stop_backup(false, true);

More Dummy data

create table testPITR2, testPITR3, testPITR4

Kill existing db

/usr/lib/postgresql/11/bin/pg_ctl -D /home/admin-12/Desktop/test2 -l /home/admin-12/Desktop/test2.log stop
mv test2 test2.bad
tar -xvzf test2.tar
sudo chown -R postgres:postgres test2

Recovery conf

nano /home/admin-12/Desktop/test2/recovery.conf

restore_command = 'cp /home/admin-12/Desktop/test2_wal/%f %p'
recovery_target_time = '2019-08-21 16:14:11.175781+05:30'

WITHOUT THIS STEP I CANNOT RECOVER Table 2,3,..

cp test2.bad/pg_wal/0* test2/pg_wal/

Any tips?

IF i do above steps in Postgres Docker (using volumes), then I get PANIC:  could not locate a valid checkpoint record, but this somehow is limited to docker

The problem is that WAL files only get archived when they are full (or at the end of a PostgreSQL backup).

So it is likely that there was no WAL switch since you finished the backup, and the WAL archiver probably died before it could archive the final WAL file with the additional actions in it (check the original server's log file).

You can use the archive_timeout parameter to mitigate the problem, or you can use pg_receivewal to archive WAL in real time, so you don't lose transactions.

The error from your docker container indicates that the restore_command didn't work (check the log file).


Relying on expected data I'm relatively new to Database Design and I wanted to know the disadvantages to a particular decision I have made in the design of a database for a project. I don't think it will have an effect on the answer, but I am using SQL Server 2008 R2.

Project:

We have things called Bids which contain Materials. Materials can be grouped so I plan on having a MaterialGroup table, However, Materials don't have to be apart of a group (they can be unassigned if you will). So, instead of making a link-table or making the foreign key nullable I decided to make the first group in the database called unassigned and instead of allowing Materials not be grouped I plan to place them in this group if not given a group.

Question: Are there any other disadvantages to expecting the first row of the table MaterialGroup to be unassigned (other than the case of someone failing to insert it there)? From both a practical and theoretical standpoint should I switch to a link-table (or another alternative)?

Having a group that is not a group may lead to reporting issues down the road.  For instance, if you want to know the average number of Materials per Group, the results could easily be skewed by the overly large number of unassigned group members.

I'd lean towards having a cross reference table that links Groups to Materials.  So a table named MaterialsGroupsXRef that contains GroupID and MaterialID; any Material that is a member of a Group has a row in that table.

Any Materials that are not a member of a Group would not show up in the MaterialsGroupsXRef table.  This allows you to easily identify Materials that do not have a group.  Likewise, you could easily identify Groups with no Materials.


Efficient partial DISTINCT ON I would like to get the most efficient query to give me all the rows that are distinct on one field (ID) but are identical on another field (validity).

Let's set a concrete example: you have a table of meds, each having an ID, some values and a validity date. Of course, in the ideal world each single ID is an individual meds; unfortunately, in reality this is not the case as you might have two variants of the same medicine, with the same ID, some different values in the middle of the table and a validity date again the same. In this real-world example, you want to retrieve all the lines in which the medicine is "still valid" (for whatever definition of still, out of the scope of my question).

Consider the following table:

# ID # VALUE # VALIDITY
# 1  # foo   # 2018/01/10
# 2  # bar   # 2018/01/03
# 2  # flo   # 2018/01/10
# 2  # duv   # 2018/01/10
# 3  # pas   # 2018/01/10
# 4  # cip   # 2018/01/08
# 4  # mao   # 2018/01/10

I'd like the query to return the following lines:

# ID # VALUE # VALIDITY
# 1  # foo   # 2018/01/10
# 2  # flo   # 2018/01/10
# 2  # duv   # 2018/01/10
# 3  # pas   # 2018/01/10
# 4  # mao   # 2018/01/10

notice that (2, bar) and (4, cip) are both "outdated" by (2, flo; 2, duv; 4, cip) respectively.

You can imagine that this is a rather trivial exercise that you can solve using an INNER JOIN:

SELECT * FROM TABLE T
INNER JOIN (SELECT ID, MAX(VALIDITY) FROM T GROUP BY ID) AS TT
ON T.ID = TT.ID AND T.VALIDITY = TT.VALIDITY

However, I noticed that this query becomes quite expensive (essentially there are two full scans, plus an additional group by.
I am not sure whether there is a less expensive way to do this (I tried with DISTINCT ON but that will kill the two entries with ID = 2).
Possibly, recommend also indexes that will work on improve this use case.

Thank you so much!

The same result als ypercube, but I find a simple max windowing function easier to understand than dense ranks and such.

select mm.id, mm.value, mm.validity from (
   select m.*, max(m.validity) over (partition by m.id) maxval
      from meds m ) mm
   where mm.validity = mm.maxval;

see http://sqlfiddle.com/#!17/e478c/4 for the results.