Execute UNION and after LEFT JOIN in MySQL I have 3 tables

cdc_vw_Marbetes_Catalogo_Exis
tblMarbetesAdicionales
tblMarbetesCongeladas

First I make a UNION of the tables cdc_vw_Marbetes_Catalogo_Exis with tblMarbetesAdicionales.

(SELECT CLAVE_ART, DESCRIPCION_ART, EXISTENCIA_ART, MARCA_ART, exi_num_alm
        FROM cdc_vw_Marbetes_Catalogo_Exis
        WHERE EXISTENCIA_ART > 0 AND exi_num_alm = 25)
    UNION
(SELECT clave, descripcion, existencia, marca, almacen
        FROM tblMarbetesAdicionales
        WHERE almacen = 25)

Result of the union: Column(CLAVE)

2457-84735-001 (cdc_vw_Marbetes_Catalogo_Exis)
107067860P     (cdc_vw_Marbetes_Catalogo_Exis)
BS-113A        (cdc_vw_Marbetes_Catalogo_Exis)
6200           (tblMarbetesAdicionales)
6300           (tblMarbetesAdicionales)

In table tblMarbetesCongeladas have:

BS-113A
CS-23523
CS-120000
CV206ETCS

But from the result of that union I want to make a LEFT JOIN to get the records that are not in tblMarbetesCongeladas.

I tried like that but it does not work

(SELECT CLAVE_ART, DESCRIPCION_ART, EXISTENCIA_ART, MARCA_ART, exi_num_alm
        FROM cdc_vw_Marbetes_Catalogo_Exis
        WHERE EXISTENCIA_ART > 0 AND exi_num_alm = 25)
    UNION
(SELECT clave, descripcion, existencia, marca, almacen
        FROM tblMarbetesAdicionales
        WHERE almacen = 25)
    LEFT JOIN tblmarbetescongeladas ON cdc_vw_Marbetes_Catalogo_Exis.CLAVE_ART = tblmarbetescongeladas.clave WHERE cdc_vw_Marbetes_Catalogo_Exis.CLAVE_ART IS NULL

the result that I expect is

2457-84735-001
107067860P
6200
6300

I appreciate the suggestions

You are on the right way but just messed up with the syntax.

SELECT t1.*
  FROM ( SELECT CLAVE_ART
              , DESCRIPCION_ART
              , EXISTENCIA_ART
              , MARCA_ART
              , exi_num_alm
           FROM cdc_vw_Marbetes_Catalogo_Exis
          WHERE EXISTENCIA_ART > 0
            AND exi_num_alm = 25

          UNION

         SELECT clave
              , descripcion
              , existencia
              , marca
              , almacen
           FROM tblMarbetesAdicionales
          WHERE almacen = 25
       ) AS t1
  LEFT JOIN tblmarbetescongeladas AS t2 ON t1.CLAVE_ART = t2.clave
 WHERE t2.clave IS NULL
;


How to get count for different columns on same table Table #01 Status:

StatusID    Status
-----------------------
 1          Opened
 2          Closed
 3          ReOpened
 4          Pending

Table #02 Claims:

ClaimID     CompanyName StatusID
--------------------------------------
1               ABC     1
2               ABC     1
3               ABC     2
4               ABC     4
5               XYZ     1
6               XYZ     1

Expected Result:

CompanyName TotalOpenClaims TotalClosedClaims TotalReOpenedClaims TotalPendingClaims
--------------------------------------------------------------------------------
ABC                 2           1                      0               1
XYZ                 2           0                      0               0

How do I need to write the query so that I could get the result as expected?

It's easiest with SUM() and a CASE statement:

select CompanyName,
sum(case when StatusID=1 then 1 else 0 end) as TotalOpenClaims,
sum(case when StatusID=2 then 1 else 0 end) as TotalClosedClaims,
sum(case when StatusID=3 then 1 else 0 end) as TotalReOpenedClaims,
sum(case when StatusID=4 then 1 else 0 end) as TotalPendingClaims
from Claims
group by CompanyName;


How to rollback changes made to the database by a specific user? In mssql, is it possible to rollback changes made by a specific user to the database?
For example if that user ran an update query and did not write begin transaction before, is it possible to revert changes made by his query WITHOUT restoring the entire Database (I want to preserve changes made by other users)?

It would be difficult to do that using Native tools or you will have to look for third party tools.

Natively:

Default trace

Can be used to track Object Altered, Object Created and Object Deleted along with other stuff. Refer to the link below.

Refer to : The default trace in SQL Server - the power of performance and security auditing

Note this is very limited as it will just give you info related to who did what e.g. Who dropped the table, etc.

fn_dblog function ==> [undocumented]

Note: You need to transaction log backups available or the transaction should not have been cleared from the active portion of the log

Excellent reading at : Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN and
Joining sys.dm_tran_database_transactions to fn_dblog Results

Third Party tools - the best one out is ApexSQL Log

As a side note:

  I want to preserve changes made by other users

Depending on your version of sql server and your business requirements, you can implement

Change Data Capture
Change Tracking
Run SQL Server Audit
Server-Side Tracing and Collection - with appropriate events selected.


Are race conditions possible in SQL? I would like to understand what the SQL standard says about parallel queries and race conditions. If there is no standard, then I would be interested in the approach adopted by popular databases.

I understand that 2+ read-only queries can run in parallel, but can a read/write or write/write pair run in parallel as well?

For example, suppose a simple aggregation such as

SELECT AVG(col1) FROM tbl;

And while that is running, I will issue an

INSERT INTO tbl (col1) VALUES (3);

Is the INSERT executed while the SELECT is running or will the INSERT be blocked until execution finishes?

In other words, are race-conditions possible in SQL?

Additionally, I would appreciate a discussion on how row, page and table locks are used in this context.

Thanks

According to the SQL standard a single SQL query sees a consistent state of the table(s) it operates on. So it must not see any changes while it's running. But not all DBMS products completely comply with that (notably those that allow dirty reads). For example Postgres or Oracle will not block the INSERT while the SELECT is running (or vice versa). But different DBMS do behave differently in that situation depending on their configuration. - a-horse-with-no-name


Determining Whether Query Load is Too High for PostgreSQL Server I just started experiencing some write-heavy load on a PostgreSQL 11.2 system running inside a Docker container on an Ubuntu 18.04 server. PostgreSQL is receiving about 1-4k INSERT queries/second.

How can one determine whether this amount of load is too high for the system? Are there some metrics to monitor, like one that shows the number of queries being queued up at any one time?

System Specs

Intel® Atom™ C2750 (8-Core, 2.4GHz)
8GB DDR3
240 GB SSD

Output of htop

2 node.js app concurrently sending queries to PostgreSQL. Both app and database are running on the same server.

Output of iotop

Output of iostat -c 5 999

Output of vmstat 1

pgAdmin

This is not a horrible load for modern server hardware.

On a write heavy DB its going to be dependent on the Hard-disk layer, triggers, functions, indexes and constraints that have to be processed/checked.

An easy way to get to the information you want is to install pgAdmin,  and use the dashboard to see what is going on. Number of connections, transactions per second, tuples in and out

From there need to study system load using,  some kind of System Monitor,  i like glances,  this will get the information on the CPU , Network and HD loads,

If there is significant wait times on the CPU or HD,  can adjust the log settings in postgresql.conf to record which queries take longer than X time


Automatically set timestamps for "create" and "modified"? I have currently following SQL table:

CREATE TABLE IF NOT EXISTS `xyz` (
  `id` bigint(20) NOT NULL,
  `guid` char(36) NOT NULL,
  `ts_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ts_modified` timestamp NULL DEFAULT NULL,
  ................
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT AUTO_INCREMENT=1 ;

ts_created is the timestamp when the entry was created. The default value is CURRENT_TIMESTAMP.

ts_modified is the timestamp when the entry was updated.

In phpMyAdmin, I could choose ON UPDATE CURRENT_TIMESTAMP for ts_modified. This would be exactly what I want.

But the error message is following:

  #1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

The error message is clear to me, but it is not clear to me

1) why this constraint is existing, since the "default" value (on create) and the "on update" value are two different things.

2) Is there anything I could do, or do I have to add ts_modified = NOW() at every UPDATE statement?

From here:

This limitation, which was only due to historical, code legacy reasons, has been lifted in recent versions of MySQL:

Changes in MySQL 5.6.5 (2012-04-10, Milestone 8)

  Previously, at most one TIMESTAMP column per table could be
  automatically initialized or updated to the current date and time.
  This restriction has been lifted. Any TIMESTAMP column definition can
  have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE
  CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used
  with DATETIME column definitions. For more information, see Automatic
  Initialization and Updating for TIMESTAMP and DATETIME.

What can you do?

You can add a BEFORE UPDATE trigger and set the ts_modified to NOW() like this:

USE `test`;

DELIMITER $$

DROP TRIGGER IF EXISTS test.xyz_BEFORE_UPDATE$$
USE `test`$$
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`xyz_BEFORE_UPDATE` BEFORE UPDATE ON `xyz` FOR EACH ROW
BEGIN

SET NEW.ts_modified=NOW();

END
$$
DELIMITER ;


write csv file only after SQL table update, insert, delete Objective: Write csv file of updated data from sql table only after SQL table update, insert, delete.

I've read from the community that I should not do triggers to execute python.  I should not run triggers to write to file from within sql.  Also, my IT keeps the environment very very restricted and I can't use XP_cmdshell, or sqlcmd.

I have python in windows. This must be a very common task.  Yet, surprisingly, it's hardly mentioned.  Perhaps I'm using the wrong search terms.  Please no expensive software suggestion.  I can't ask the IT to buy it.

How can I execute python only after the sql table updates?

Check out Query Notifications. You could leverage this functionality and a SQL Server agent job to monitor for data changes and execute your Python script on change.

Basically, you create a query notification for the data in question and create a stored procedure that will receive the notification, execute the Python script then return to wait for the next notification.

Schedule a SQL Agent job to run on startup and start this procedure and it will run continuously (unless it is stopped or fails, but you should be monitoring for these events in SQL Server anyway) and execute your Python script whenever data changes.

It's not the same as a trigger, as in you still must poll the queue for notifications of data changes, but the Service Broker technology it is built on is very lightweight compared to polling the tables constantly or adding triggers to your tables.


SQL Server restore database file access error I am trying to restore a database from a file on a remote machine. The script I am using is as follows:

RESTORE FILELISTONLY
   FROM disk = '\\server\path\to\file.dmp'
RESTORE DATABASE MyDB
   FROM disk = '\\server\path\to\file.dmp'
   WITH REPLACE,
   MOVE 'MyDB_Data' TO 'D:\path\to\file.mdf',
   MOVE 'MyDB_Log' TO 'D:\path\to\log.ldf',
   stats = 20
GO

I am using this script because the SQL Server Management wizard appear to dislike the use of a .dmp file instead of a .bak file, though my team has used .dmp files in the past without issue. However, whilst the RESTORE FILELISTONLY command appears to complete successfully, the RESTORE DATABASE command fails with the following error.

Msg 3203, Level 16, State 1, Line 3
Read on "\\server\path\to\file.dmp" failed: 38(failed to retrieve text for this error. Reason: 15105)
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

This seems unusual, as it appears to be an error with the .dmp file, but the previous RESTORE command did not complain at all. On top of this, the database in question is now stuck in a neverending "Restoring..." state which I cannot seem to get it out of. I tried RESTORE DATABASE MyDB WITH RECOVERY but that gave the same error, tried adding RECOVERY to the original script but just got the same error.

Any idea what's going on? I am new to DBA, myself, so I have limited experience to draw upon.

Double check that the paths you are trying to restore to exist on the D drive. SQL Server will not create the directory path if it doesn't exist.

If you haven't already, run the restore command with the verify only option MSDN.

If that is successful here are a few additional things you can try to isolate why it is failing

If you have the space on the SQL Server that created the backup, try to restore the backup there under a different file path and different database name (again make sure the temporary restore path(s) exist.)  This will confirm if the backup is valid and can be read by SQL Server.  Be very careful here to change the database name to something different and to give the file names a different name to not cause a problem with the current database.  (Example: change MyDB to Restored_MyDB, MyDB.mdf to Restored_MyDB.mdf and MyDB.ldf to Restored_MyDB.ldf in the restore statement.)
Confirm the account that you are using to do the restore,if it's not the same, has the same permissions that the account that did the backup does.
Copy the backup locally to the machine you wish to restore it to and restore from the local drives.
Change the file extension from dmp to bak.


Series of questions about ER diagram and database? I have newly learned about ER diagrams and how powerful they are.
I am trying to make a very basic ER diagram for hospital and there are few questions and doubts in my mind.
This is the ER diagram that I've made:

I want tests to be stored in hospital database as well as in the patient's report file(all the tests of a specific patient should be stored in his/her report file).
The ternary relationship test should signify that:

A patient and a doctor maintains only one report
A doctor makes a report for specific patient
A patient and his report can have tests from N no of doctors.

Q1- But I am confused how to achive this using ternary relation?
Q2- What is the difference between 3 binary relationship and 1 ternary relationship(and also which of them carrys more information)?
Q3- Can a relationship can have it's own prime(key) attribute?
Q4- If we have concept of surrogate key then why does weak entity exists in first place?
Q5- How to conclude which entity should be made weak and whichshould be not?
Q6- Any good source to learn EER ,advanced topics of database designing(relational algebra,etc..) and about ternary relationships(I have searched youtube and google but none of them was satisfactory)?

Q1:  Why do you need a ternary relationship?  Test is a relationship between a patient and a doctor.  What is the third entity you need to specify in order to pin down a specific instance of a test.  I assume that a doctor can order more than one test for a given patient.
Q2:  They carry different information.  One of them is appropriate in any given situation, and the other does not carry the needed information.
Q3:  In the Chen ER model,  relationships are generally not given a primary key attribute.  Instances of the relationship may be identified by identifying each of the participants in the instance.
Q4: There is more than one way to skin a cat.
Q5: Make an entity weak if it can have no existence outside the context of the related strong entity.  Invoice detail line is a weak entity.  There will never be an invoice detail line without a corresponding invoice.
Q6:  There are hundreds of good textbooks to learn more about database design.  The ones by CJ Date are classics.  There a re a few good sources on EER,  but most good sources on ER will  incorporate EER concepts.  Here is a link to ternary relationships, but it repeats stuff you already know.  https://www.tutorialspoint.com/N-ary-Relationship-in-Database#:~:text=N-ary%20Relationship%20in%20Database%201%20Unary%20Relationship.%20When,a%20binary%20relationship.%20...%203%20Ternary%20Relationship.%20  the primary value to you is the terminology of N-ary relationships.  This should help you google.
You didn't ask this,  but you need to know it anyway.  There is a difference between analyzing the subject matter  (hospitals),  and designing a database to hold the relevant data.  People who do not understand the difference often end up producing the right solution to the wrong problem.


Are there any SQL Server or MySQL versions that allow MULTIPLE NULL for a UNIQUE Constraint? I wanted to know whether there are any versions that allow multiple NULL on UNIQUE.
Also, single NULL for UNIQUE.
Please specify the versions.

All versions of MySQL and derivatives (MariaDB, Drizzle, Galera, etc.) ignore  nulls in UNIQUE constraints. This allows for any number of rows that have nulls in the columns of a unique constraint. Note that this is the behaviour defined in the SQL standard.

On the opposite, all SQL Server versions, at least from 2000 till now, restrict to only one NULL in unique constraints. I think there was a Connect request to have this changed but since it would break backwards compatibility (in lots of existing databases that have been using the feature as it is for more than 20 years), I highly doubt this will ever be changed.

If you want to have the standard behaviour (multiple nulls) in SQL Server, you can use a filtered index in versions 2008+. Example:

CREATE UNIQUE INDEX Nulls_ignored_UQ
  ON tableX
    (columnA)
  WHERE (columnA IS NOT NULL) ;

One disadvantage is that you can't use this column as a target for a FOREIGN KEY constraint (only PRIMARY KEY and UNIQUE constraints can be used for that, not unique indexes.)

On the opposite issue, if you want to allow only a single null in MySQL, you can use a computed column in recent versions (MariaDB 5.2+, MySQL 5.7+) and then add a second UNIQUE constraint:

ALTER TABLE tableX
  ADD columnA_single_null int AS (CASE WHEN columnA IS NULL THEN 1 END),
  ADD CONSTRAINT Single_null_UQ
    UNIQUE (columnA_single_null) ;

Test in dbfiddle.uk


How to find the date a login password was last changed in SQL Server 2008? I'm porting stored procedures from Sybase 15 to SQL Server 2008.

In Sybase this statement calcs an expiration date by adding a number of days to syslogins.pwdate (Date the password was last changed) and works fine:

SELECT @l_pwd_date = dateadd( day, @l_pwd_max_expire, pwdate)
FROM master.dbo.syslogins
WHERE name = @v_user

...but throws this error when trying to compile it in SQL Server 2008:

  Msg 207, Level 16, State 1, Line 21 Invalid column name 'pwdate'.

syslogins in SQL Server 2008 no longer includes that column pwdate.

Does anyone know where could I find equivalent column in SQL Server 2008 or a workaround to find the date a login password was last changed?

Use the PasswordLastSetTime option of the LOGINPROPERTY.

SELECT LOGINPROPERTY('YourLoginName', 'PasswordLastSetTime');


Specified key was too long creating primary key Simple question: what is wrong here?
Using utf8 (CHARSET=utf8mb4) makes the internal fields larger.
What is the maximum field size for UTF8 keys?
We need to guess?
create table repos(repo varchar (200), PRIMARY KEY (repo));
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

How hard is for mariadb to give a better error message?
btw: The answer, by trial and error, is 191 * 4 + 4 = 767.
mariadb Ver 15.1 Distrib 10.1.47-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

767 Limit in InnoDB Indexes lists 5 work arounds for the 767 problem.
But, these days, the preferred solution is to upgrade:
This problem exists before the limit was raised in 5.7.7 (MariaDB 10.2.2?).


Restore mysql from database snapshot I have a folder containing the snapshot taken from a database.

They are all in the format organisation_product_tablename.sql.

How can I restore them back into the database?

EDIT 1:

I managed to restore all the files, but one with:

ls -1 *.sql | awk '{ print "source",$0 }' | mysql --batch -u root -p databaseName

The only one that is giving problems fails with an this error:

ERROR 1449 (HY000) at line 346 in file: 'org_sugar_routines.sql': The user specified as a definer ('root'@'%') does not exist.

This is the Head of the file:

USE `organisation_product`;

-- MySQL dump 10.13  Distrib 5.6.13, for Win32 (x86)
--
-- Host: 10.10.0.10    Database: organisation_product
-- ------------------------------------------------------
-- Server version     5.1.60-community

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Temporary table structure for view `vw_contact_insurance`
--

When I checked that line in the file I found that in the file marked as error there is this:

/*!50013 DEFINER=`root`@`%` SQL SECURITY DEFINER */

When I run this query

mysql> SELECT USER(),CURRENT_USER();

I obtain this:

+----------------+----------------+
| USER()         | CURRENT_USER() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.02 sec)

Isn't this the root user? I have it as the admin user in the DB. So, what's the problem here?

I see you restored the tables. Your issue seems to be the owner of either a view or a stored procedure

You need to replace all occurrences of

`root`@`%`

in the file with

`root`@`localhost`

I would not recommend creating the user root@'%' as that would allow root to have remote access.

Since root@'%' is the owner of the routines, I will create root@'%' like root@localhost

Please run the following:

CREATE TABLE mysql.user_root LIKE mysql.user;
INSERT INTO mysql.user_root
SELECT * FROM mysql.user WHERE user='root' and host='localhost';
UPDATE mysql.user_root SET host='%';
REPLACE INTO mysql.user SELECT * FROM mysql.user_root;
DROP TABLE mysql.user_root;
FLUSH PRIVILEGES;


MongoDB 2.0.9 IndexStats I am trying to get the index usage statistics in a mongoDB 2.0.9.

I found a similar question in https://stackoverflow.com/questions/44813787/how-to-check-index-usage-in-mongodb but this only applies to versions 3.2 and up.

Any assistance will be very appreciated.

The $indexstats command was added for the MongoDB 3.2 server release in Dec 2015. Without server support for measuring or logging index use, I'm not aware of any obvious workaround short of modifying the 2.0.x server code and building a custom server.

MongoDB 2.0 is a relatively ancient server version: it was first released in Sept 2011 and reached End-of-Life in March 2013. I'd strongly recommend planning to upgrade to a modern & supported version of MongoDB (currently 3.4+, although 3.4 will reach EOL in Jan 2020). There are numerous essential bug fixes, stability, and security improvements since 2.0.


Mysql checksum for master-slave replication returning different values, despite being identical I've set up a master slave replication in mysql and to make sure that I know if the data starts getting out of sync I created a script that:

Locks both databases on both servers.
Prints the checksums of all the tables to a text file.
Copy's the slave checksum text file to master.
Compares the both files to see if anything is different.

But on some tables I got different checksums. So I downloaded both those tables and created another script to find whats different on them. And Nothing is different! They're identical.

I only get the different checksums on tables that have float variables, so I suspect it has something to do with that.

I do have slightly different versions of mysql installed, could this be causing it?

+-------------------------+-----------------------------+
| Variable_name           | Value                       |
+-------------------------+-----------------------------+
| innodb_version          | 5.5.34                      |
| protocol_version        | 10                          |
| slave_type_conversions  |                             |
| version                 | 5.5.34-0ubuntu0.13.04.1-log |
| version_comment         | (Ubuntu)                    |
| version_compile_machine | x86_64                      |
| version_compile_os      | debian-linux-gnu            |
+-------------------------+-----------------------------+

+-------------------------+-----------------------------+
| Variable_name           | Value                       |
+-------------------------+-----------------------------+
| innodb_version          | 5.5.37                      |
| protocol_version        | 10                          |
| slave_type_conversions  |                             |
| version                 | 5.5.37-0ubuntu0.14.04.1-log |
| version_comment         | (Ubuntu)                    |
| version_compile_machine | x86_64                      |
| version_compile_os      | debian-linux-gnu            |
+-------------------------+-----------------------------+

Anyone of you know what could be causing this?

There is better way to check whether the master and it's slaves are in sync.

Run pt-table-checksum on the master.

Then if it shows some tables are different use this script to see where exactly the difference is. http://bazaar.launchpad.net/~akuzminsky/+junk/stats-scripts/view/head:/compare_tables.sh

./compare_tables.sh master_ip slave_ip database table


MongoDB multi update consistency I have a collection similar to the below, of say 5,000,000 records.

{ "_id" : ObjectId("56d18f95d2961d1ddcb7244f"),
  "rec_id" : 0,
  "status" : "open",
  "updated by" : "A"
}

User B is trying to mark all status to "closed", with below command:

db.prod.update({},{'$set': {'status':'closed', 'updated by':'B'}}, false, true)

Let's say user B is able to update closed status to 100,000 records and the connection is lost, or some problem occurs, and the remaining docs are not updated. So the data is inconsistent, is there any way in which we can fix this kind of problem?

I need to mark all status to closed or nothing to closed.

The documentation states

  When a single write operation modifies multiple documents, the modification of each document is atomic, but the operation as a whole is not atomic

Multi-row rollback is one of those things which are foregone in the NoSQL world, sadly.

That same link mentions the $isolated operator. This may be of some help, depending on the use case and topology.

Also documented is the so-called "two phase commit" pattern. This may require application changes, however, so may not be practical.

Five million rows isn't a silly-big number. Would it be possible to arrange an application outage until the writes succeed, with continuous retries? Maybe run the write application on the same hardware as Mongo so the network is no longer an issue.


Grid Control Standby database status: Creation status unknown After using Grid Control to add standby database, Data Guard status of standby database is showing "Creation status unknown". However, dataguard status on both primary and standby databases are OK. Database is Oracle 11.2.0.3. The Management agent version is 12.1.0.1.
I read on the forum that I need to apply patch #9244383. However, when I apply patch, It requires agent version 10.2.0.5.

What should I do here?

There's a MOSC note, and while it's specific to versions 10.2.0.1 to 10.2.0.5, maybe it worths testing.

The note states that when creating Standby using grid, there were some issues and the remaining part of its creation was done manually.

The recommendation is to remove the Standby DB from the Grid in order to clear the errors, and then rediscover the Standby DB.

For complete reference: Doc ID 1455165.1


Attach/detach vs. backup/restore I need to transfer the database (as a whole) to another server, to make a duplicate database to setup another test environment.

I have two choices:

Make a full backup at source server/restore at destination server;
Detach at source server/attach at destination server.

What are the pros and cons of the two solutions according to my requirements?

I am using SQL Server 2008 Enterprise.

Backup/restore should normally be your method of choice. It will be quicker in most situations.

You can use it consistently, also for production to test too.

See this related question as well, where the backup/restore vs detach/attach is mentioned:

SQL Server Migration restore backup vs copy data and log files

Make sure you add the WITH COPY_ONLY option to the backup so it does not break the existing maintenance plan backup chain.


Practical application of EXISTS vs. LEFT JOIN to find addresses for delivery Our end users have many ways of shipping product to our customers. They can use the customer address in one table ([customer]), a specific delivery address in another ([dropship]), or yet another delivery address in another table (for customers with multiple locations) ([delivery]). These all have roughly the same structure, with the same column names for the same data points (e.g. [address] is the address in all tables).

Currently, our logic for finding the delivery address of an order is as follows:

SELECT
    Address = COALESCE(r.address, d.address, c.address)
FROM [order] o
JOIN customer c
    ON o.customerid = c.customerid
LEFT JOIN delivery d
    ON o.customerid = d.customerid
    AND o.delivaddressid = d.delivaddress
LEFT JOIN dropship r
    ON o.orderid = r.orderid;

Based on conversations and not a few blog posts, EXISTS seems to be preferred.

However, this is a new paradigm to me and I'm trying to wrap my brain around it. Do I just replace the LEFT JOINs with

WHERE EXISTS
    (select 1 from delivery d
        where o.customerid = d.customerid
        and o.delivaddressid = d.delivaddress)

This won't allow me to use the data from [delivery] unless I toss it in the FROM clause and create a cartesian product, right?

While you can use EXISTS to determine whether, say, a dropship address is present, you can't use it to tell what the actual address is. EXISTS doesn't return any data. For that, you'll need the join.

A better design might be well-served here, for example a single Addresses table with a type. But as I understand it your model is currently not flexible.


Group by + Order by Behavior Explanation? Suppose I have:

Product(_id,Name)

Provider(_id,Name)

Provides(ProductId, ProviderId, Price)

If I want to obtain the providers who sell the most expensive products, and whose name starts with A.

Could you tell me how the following snippet runs? It seems to be
returning the results I ask for.
Does group by get a random record from the table ?
Is Order by working first and then Group by gets the first record?

SELECT provider.Name, piece.Name, provides.Price
FROM provider,piece,provides
WHERE provider._id = provides.ProviderId
AND piece._id = provides.PieceId
AND provider.Name LIKE 'A%'
GROUP BY piece.Name
ORDER BY provides.Price DESC

Edit1: I still haven't gotten answered if the way this query is made, my question (bold text) is answered. I'm ALSO asking for what happens once/behind the scenes Group by is applied. See I'm trying to order based on price. I'm not asking about the basic idea, but what's going on behind.
I got answered "No." So what happens behind the Group by and over who Order by works over?

Edit2: Seems the question was a bit vague: The original question, for clarification, meant:
For every product, I want the provider who sells it the most expensive.

So can you guys suggest solutions with standard SQL?
What was on my mind, along with the results giving me the right things was:

So, does anyone know why group by doesn't internally keep the records "grouped" and then order by sorts them? and shows instead an arbitrary record? (meaning for this problem the solution I had in mind is not trustworthy/predictable).

The order of execution for SQL query is

FROM & JOIN -determine table & filter rows
WHERE -more filters on the rows
GROUP BY -Combine rows in group
HAVING - filters on group
ORDER BY - arrange the remaining rows

  Does group by get a random record from the table ?

Answer is No.

  Is Order by working first and then Group by gets the first record?

Answer is No.

How the query execution happen will tell us the reason,
In the query that you have added, FROM provider,piece,provides is executed first, then WHERE provider._id = provides.ProviderId
AND piece._id = provides.PieceId
AND provider.Name LIKE 'A%'
after that GROUP BY followed by ORDER BY clause.

EDIT:

Check Sqlite select for more details.


replica set in MongoDB using docker, primary has error and stops being primary when another member is added to the set I have two docker containers running a mongo instance each, they were initialized like this:

docker run --name mongodb-shard-1-node-1 -d -v mongodb-shard-1-node-1:/data/db -p 27031:27017 mongo --replSet rs0 --smallfiles --oplogSize 128

when i do docker inspect mongodb-shard-1-node-1 it shows the ip 172.17.0.2

docker run --name mongodb-shard-1-node-2 -d -v mongodb-shard-1-node-2:/data/db -p 27020:27017 mongo --replSet rs0 --smallfiles --oplogSize 128

when i do docker inspect mongodb-shard-1-node-2 it shows the ip 172.17.0.4

So i proceed to access mongodb-shard-1-node-1 by using docker exec -it mongodb-shard-1-node-1 mongo and i initialize it as the primary member like this:

rs.initiate()
{
    "info2" : "no configuration specified. Using a default configuration for the set",
    "me" : "ee3c41ef76b2:27017",
    "ok" : 1
}

Then I proceed to add the mongodb-shard-1-node-2 to this member for it to work as a secondary member, at first it looks like it worked:

rs0:PRIMARY> rs.add("172.17.0.4:27017")
{ "ok" : 1 }
rs0:PRIMARY> rs.status()
{
    "set" : "rs0",
    "date" : ISODate("2016-05-20T01:04:02.095Z"),
    "myState" : 1,
    "term" : NumberLong(1),
    "members" : [ervalMillis" : NumberLong(2000),
            {       "_id" : 0,
                    "name" : "ee3c41ef76b2:27017",
                    "state" : 1,,
                    "uptime" : 27,PRIMARY",
                    "optime""ts" : Timestamp(1463706237, 1),
                    },      "t" : NumberLong(1)
                    "infoMessage" : "could not find member to sync from",
                    "electionDate" : ISODate("2016-05-20T01:03:43Z"),
                    "self" : truen" : 2,
            {,
                    "name" : "172.17.0.4:27017",
                    "state" : 0,,
                    "uptime" : 4,"STARTUP",
                    "optime""ts" : Timestamp(0, 0),
                    },      "t" : NumberLong(-1)
                    "lastHeartbeat" : ISODate("2016-05-20T01:04:01.187Z"),
                    "pingMs" : NumberLong(0),Date("1970-01-01T00:00:00Z"),
            }       "configVersion" : -2
    "ok" : 1
}

but right away it fails for some reason and i have no idea why, here's what i get:

rs0:PRIMARY> rs.status()
2016-05-20T01:04:18.007+0000 E QUERY    [thread1] Error: error doing query:
failed: network error while attempting to run command 'replSetGetStatus' on host '127.0.0.1:27017'  :
DB.prototype.runCommand@src/mongo/shell/db.js:135:1
DB.prototype.adminCommand@src/mongo/shell/db.js:153:16
rs.status@src/mongo/shell/utils.js:1090:12
@(shell):1:1

2016-05-20T01:04:18.012+0000 I NETWORK  [thread1] trying reconnect to 127.0.0.1:27017 (127.0.0.1) failed
2016-05-20T01:04:18.018+0000 I NETWORK  [thread1] reconnect 127.0.0.1:27017 (127.0.0.1) ok
rs0:SECONDARY

What is wrong? How do I fix it?

Edit: just to clarify, i had already tried the connections between the containers by doing what this part of the documentation says at: Test Connections Between all Members

Your primary is trying to auto-configure itself as ee3c41ef76b2:27017 and that then resolves to the loopback (127.0.0.1) which is then not responding on the container as it expects.  Depending on what the second container does to resolve ee3c41ef76b2, and especially it it does not resolve to 172.17.0.2, it will probably not be able to talk to the primary either.

Assuming you are correct about the connectivity (and you have verified that the instances are listening on the IP and not just the loopback) then you need to override the automatic detection and be explicit when you are calling rs.initiate(), something like this:

rs.initiate(
   {
      _id: "rs0",
      version: 1,
      members: [
         { _id: 0, host : "172.17.0.2:27017" },
         { _id: 1, host : "172.17.0.4:27017" }
      ]
   }
)


MySQL: Lock wait timeout exceeded I have a developer that has been trying to alter a large table (~60M rows). Via LiquidBase/JDBC they're running

ALTER TABLE foo DROP FOREIGN KEY fk_foo_1;

Today while it was running I checked in on it periodically; everything looked normal, the query was running, in state "copying to tmp table", I could see the temp table on disk getting larger and larger (still plenty of free space). I could see other processes blocked waiting for this table to be unlocked. Finally after about 4.5 hours, they got the "lock wait timeout exceeded; try restarting transaction" error. This is actually the 2nd time they've tried, and it seems to fail right about when I would expect it to complete.

innodb_lock_wait_timeout is set to the default 50, I can't imagine it would run for so long to be affected by this. No errors logged, no deadlocks or other weirdness seen in 'show engine innodb status'. Can anyone help me with other ideas? I'm fairly stumped on this one.

thanks

This is definitely a head-scratcher, but here's one possibility, especially if you're running MySQL 5.1 or earlier.  MySQL Bug #37346 addresses an issue where there are locks at two different layers that are (at least partially) unaware of each other's existence... an uncommitted transaction is holding locks inside the storage engine (invisible to MySQL's core), while the ALTER TABLE is holding a different kind of lock, outside the storage engine (invisible to InnoDB).

In the bug report, when the ALTER TABLE is almost done, it bumps into the inner locks and ultimately times out with the message you see.

This is referenced as "fixed" in the release notes for 5.5.3 but I didn't find it mentioned in 5.1.x, which suggests to me that it might still exist there.  It seems like a long shot but it did seem worth mentioning.

Until I found the above, my initial thoughts had to do with the table that is referenced by fk_foo_1... I was going to speculate that InnoDB might be trying something unnecessary related to validation of the foreign key constraint (unnecessary, since the constraint is about to disappear)... or that an uncommitted update or delete on the referenced table might be setting locks on the table you're trying to alter... an update or delete "over there," needing to be validated or trying to cascade "over here," while the ALTER TABLE is underway, and not waiting as it should for the lock held by the ALTER TABLE.


How to Remove Scripts Date Comments from Powershell Generate Database Script? I am generating Database Generate scripts with Powershell.
What is the option in Powershell to remove Script dates comment lines?

Automated Script-generation with Powershell and SMO

Object:  Table [dbo].[customer]    Script Date: 9/6/2018 4:40:33 PM

$Filepath='E:\MyScriptsDirectory' # local directory to save build-scripts to
$DataSource='MyServer' # server name and instance
$Database='MyDatabase'# the database to copy from
# set "Option Explicit" to catch subtle errors
set-psdebug -strict
$ErrorActionPreference = "stop" # you can opt to stagger on, bleeding, if an error occurs
# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$ms='Microsoft.SqlServer'
$v = [System.Reflection.Assembly]::LoadWithPartialName( "$ms.SMO")
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
[System.Reflection.Assembly]::LoadWithPartialName("$ms.SMOExtended") | out-null
   }
$My="$ms.Management.Smo" #
$s = new-object ("$My.Server") $DataSource
if ($s.Version -eq  $null ){Throw "Can't find the instance $Datasource"}
$db= $s.Databases[$Database]
if ($db.name -ne $Database){Throw "Can't find the database '$Database' in $Datasource"};
$transfer = new-object ("$My.Transfer") $db
$CreationScriptOptions = new-object ("$My.ScriptingOptions")
$CreationScriptOptions.ExtendedProperties= $true # yes, we want these
$CreationScriptOptions.DRIAll= $true # and all the constraints
$CreationScriptOptions.Indexes= $true # Yup, these would be nice
$CreationScriptOptions.Triggers= $true # This should be included when scripting a database
$CreationScriptOptions.ScriptBatchTerminator = $true # this only goes to the file
$CreationScriptOptions.IncludeHeaders = $true; # of course
$CreationScriptOptions.ToFileOnly = $true #no need of string output as well
$CreationScriptOptions.IncludeIfNotExists = $true # not necessary but it means the script can be more versatile
$CreationScriptOptions.Filename =  "$($FilePath)\$($Database)_Build.sql";
$transfer = new-object ("$My.Transfer") $s.Databases[$Database]

$transfer.options=$CreationScriptOptions # tell the transfer object of our preferences
$transfer.ScriptTransfer()
"All done"

From the SMO documentation for ScriptingOptions:

IncludeHeaders Gets or sets a Boolean property value that specifies whether the generated script is prefixed with a header that contains information which includes the date and time of generation.

So you should be able change this line of your PowerShell script:

$CreationScriptOptions.IncludeHeaders = $true; # of course

to

$CreationScriptOptions.IncludeHeaders = $false; # of course not


Database Doesn't Contain Current Data After Restoring Last Full Backup from Production I restored my last full backup from production database to reporting database server, but last restored database on my reporting server doesn't contain current tables, procedures, functions etc. What can cause this situation? How can I solve it?

This is a common issue when you back up to the same file over and over again. Without WITH INIT, the new backup gets appended to the file, it doesn't overwrite it. So in this example:

BACKUP DATABASE model TO DISK = 'c:\temp\model.bak' WITH COMPRESSION;
GO
USE model;
GO
CREATE TABLE dbo.newtable(id int);
GO
BACKUP DATABASE model TO DISK = 'c:\temp\model.bak' WITH COMPRESSION;

We can see from RESTORE HEADERONLY that there are two backups:

RESTORE HEADERONLY FROM DISK = 'c:\temp\model.bak';

Partial results:

Position        BackupFinishDate
-------------   -----------------------
1               2019-09-03 14:01:08.000
2               2019-09-03 14:01:11.000

Guess which one has dbo.newtable? Guess which one is used as the default when you just say RESTORE ... FROM DISK ...;?

If you're going to continue using this method to back up your databases, you'll need to determine the Position to pass to RESTORE ... WITH FILE = <position> so that you get the latest backup. A better practice IMHO is to always use BACKUP ... WITH INIT and append timestamps to the file name. This makes it easier both to ensure you're using the latest backup and also to remove older backups.


SSIS Data Import from CSV to SQL Table Failing I have been trying to import the data from CSV file to SQL Table. I have 7 columns which are:

Refresh Date - Db_Date
Report Date - DB_Date
Report Period - Four byte singled Int
Participated - Four byte singled Int
Organized - Four byte singled Int
Peert-To-Peer - Four byte singled Int

I am using Flat File Source --> Data Conversion (to convert the data in my csv to match the data type according to the above table) --> OLE DB Destination.

I am getting following errors:

[Data Conversion [2]] Error: Data conversion failed while converting column ""???Report Refresh Date"" (37) to column "Copy of "???Report Refresh Date"" (11).  The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
[Data Conversion [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Data Conversion.Outputs[Data Conversion Output].Columns[Copy of "???Report Refresh Date"]" failed because error code 0xC020907F occurred, and the error row disposition on "Data Conversion.Outputs[Data Conversion Output].Columns[Copy of "???Report Refresh Date"]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Data Conversion" (2) failed with error code 0xC0209029 while processing input "Data Conversion Input" (3). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

I am also attaching the screenshot of how my CSV file looks like.

Any help will be helpful as I have a ton of data which needs to be load to SQL tables from CSV files.

The key here is a short snippet from the first error message:
Data conversion failed while converting column ""???Report Refresh Date"" (37) to column "Copy of "???Report Refresh Date"" (11).

This sort of error is always a pain to track down, but in short, it's saying that for some reason it's encountering a value that it's unable to convert to a date.

One trick you can use to try and figure out where the problem is located is to look at the number of rows the package is able to process before erroring out. You can usually see this by entering into the data flow task in question. If it's able to process 15 rows before erroring, you know that the problem row is somewhere around row 16. From there, you can see if anything is in a weird format, or if any values are out-of-bounds (think 2/30/2020).


CASE statement with IS NULL and NOT NULL Is there any better way to write the lines below in SQL Server 2005?

CASE
WHEN (ID IS NULL)
   THEN 'YES'
WHEN (ID IS NOT NULL)
   THEN 'NO'
END AS ID_Value,

Did you try:

CASE WHEN (ID IS NULL) THEN 'YES' ELSE 'NO' END AS ID_Value,

I only have access to 2008 right now, but I'd hope that this syntax would still work in 2005 (seems like something that would be part of the original definition of CASE).


What are the alternatives for an ORDER BY clause in a View? This question just had to be in this site :)

ORDER BY is forbidden to use in a view, as I understood because of the possibility for multiple order by's when using this view.

I know that there are ways of bypassing this limitation, for example TOP 99.999999 PERCENT, but I would like to know what is the best practice, not how to hack it.

So, if I want to create views in my database for personal use, meaning I want to connect to the database and just see the data fixed and sorted, how should I do it if I can't order a view?

Currently in my SQL Server DB I have views with the TOP hack, and I'm using them a lot, but it feels wrong.

Only the outermost ORDER BY will guarantee order

Any intermediate or internal ORDER BY is ignored.This includes ORDER BY in a view
There is no implied order in any table
There is no implied order from any index (clustered or not) on that table

Links

"Sorting Rows with ORDER BY" (MSDN)

  ORDER BY guarantees a sorted result only for the outermost SELECT statement of a query. For example, consider the following view definition: (and example follow that matches this question)


Pooler (pgbouncer) + MasterDB + Slaves During a spike, my connections go from 30 to over 140, as you can see below:

I'm willing to set-up a pooler like pgbouncer, but have some questions as my scenario is not that easy:

  1 Load Balancer (nginx)

  2 web servers (www01 and www02)

  5 DB servers (master01, slave01 (read-only), slave02 (hot_standby),
  slave03 (hot_standby), slave04 (hot_standby)

  Just ONE Database. Size: 2TB

  max_connections: 200

  I'm using PostgreSQL 9.2

Question:

Can pgbouncer handle with my master and slave01, as I'm using slave01 as read-only server? Also.. I've got 2 www servers... Do I have to have pgbouncer in both of them?

Yes you can define in pgbouncer your 2 servers (or more) masetr and slave and connect to each of them.
There is different kind of topology you can install pgbouncer on each www, as is the connection will be the fastest (www process can use unix socket to connect), on install a pgbouncer on each server (master and slave) or install it on a new host to be single.
PErsonnaly I recommend another topology, install pgbouncer everywhere, on each www (to increase the connection), and on each db server, it will help you when you'll want to do migrations for example. PgBouncer support PAUSE/RESUME on connection, as is you can reconfigure your conenction on move traffic to master to a new host when you'll upgrade verison for example.


General table name for Person Inf I was in a meeting with my boss and technical support leader and they asked me for a database model to a very simple application. That application requires: employees information, some location data, and etc, etc, etc.

One of their database table was:

employee

  id
  name
  lastname
  username
  password

That table suppose to save all employers' info but since I was available to make any changes as I wanted I did as follow:

   Person
      id
      lastname
      ocupation// saved as int. Ex: 1 =employee, 2 =contacts, 3 = bank name, etc, etc

   User
      id
      person_id
      username
      password

My approach was very simple and extensible since now my table person was able to handle all person information despite whether if it is employee or a dentist or what ever. But my boss and a work-mate said that my approach was a bad idea and not correct and too complicated it(which is not), but what if sometimes later the costumer wants to add a contact-list? Now i have to create another table to store contacts info and repeat the columns name,  lastname, etc, etc.

I had implemented this before and it worked perfectly.

The question is: Is my approach so bad? Is there any way to make it better?

Well, having occupation as part of Person isn't very well normalized.  One person can have multiple occupations, so that should go in a different table or set of tables, depending on how normalized your schema is.

I am confused - how is a "bank name" an occupation?  Or even related to a person?

I would note that while the theory of a Person table is sound, in practice it can get fairly interesting; for example, perhaps you start with U.S. SSNs.  Then you get some Canadians with TINs (also U.S. 9 digit federally issued identifiers).  And companies with TINs.  Then you get some people with Mexican CURP values (18 character alphanumerics).  Now you have a person with a CURP and a TIN both.  Now you have a person with a Chinese identification number as well.

Let's not even get into truly international addresses :).

You may want to consider normalizing further, but definitely work out how a single human being with multiple roles (employee, vendor, customer) and multiple locations (different addresses or cities or regions or countries) and multiple ID's (TIN, SSN, etc.) is going to work.

Likewise with organizations - perhaps your food service vendor is also a customer, and then comes to work for you part-time.

EDITED TO ADD: I would also note that the "password" field horrifies me.  Please read How to securely hash passwords? and replace the password field with fields for the plaintext random per-person salt, the PBKDF2/Bcrypt/Scrypt password hash, and ideally the # of iterations (so it's easy to increase).


MySQL - replicate some tables between transaction and report database I have two different database (on two different hosts). One database is transactional and another database is reporting. Let us say, it stores information about departments and their employees.

Transactional Database has one department table which stores employee data of all departments. This table has foreign and primary keys. This table has no partitions. Disk size is above 250 gb.

Reporting Database has one department table which is partitioned by department id. This table has no primary or foreign keys. Otherwise, structurally this table is same as transactional one.

Is there any way to replicate the department table between transactional and reporting.

You can replicate Transaction to Reporting without any issues are it's "independent" but Reporting to Transactional may cause issues depending on your foreign-key settings.

You can easily replicate single table with the help of replication filters (in your case replicate-do-table or replicate-wild-do-table).

There are plenty of blogs for replication setup instructions, say this one from Percona.


Can Microsoft SQL Server be installed on Ubuntu 18.04 LTS? Trying to install SQL Server on Ubuntu 18.04, I'm getting

# sudo apt-get install -y mssql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:

The following packages have unmet dependencies:
 mssql-server : Depends: libcurl3 but it is not going to be installed
                Depends: openssl (<= 1.1.0)
E: Unable to correct problems, you have held broken packages.

Is it possible to install SQL Server on Ubuntu 18.04 and is it supported?

Supported

No, Microsoft only supports 16.04 LTS. The version that's two years old.

Installation

In order to install SQL Server on Ubuntu whether new or old, I suggest installing it as a chroot. To do that first install debchroot, and then use that utility to set up a chroot.

sudo apt install debchroot
sudo debootstrap --arch=amd64 xenial /opt/mschroot http://archive.ubuntu.com/ubuntu/

You'll have to --bind mount your host's /proc

sudo mount --bind /proc /opt/mschroot/proc

Now jump into your chroot

sudo chroot /opt/mschroot

And set out to configure it,

apt update

## Add the Universe Repository (needed https://dba.stackexchange.com/q/212868/2639)
apt install wget software-properties-common apt-transport-https
sudo add-apt-repository "deb http://archive.ubuntu.com/ubuntu $(lsb_release -sc) universe"
apt update

## Add Microsoft Repository
wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)"
apt update

## Configuration complete begin install
apt install -y mssql-server

Then configure it, (use option 2 for Developer)

/opt/mssql/bin/mssql-conf setup

You'll have to pick a password, note for reasons unexplained The password must be at least 8 characters long and contain characters from three of the following four sets: Uppercase letters, Lowercase letters, Base 10 digits, and Symbols.. I suggest everyone use MICROSOFTsucksH4RD, as it satisfies that. Lastly, don't forget to disable the spyware.

/opt/mssql/bin/mssql-conf set telemetry.customerfeedback false


How Can I generate a data dictionary which contains the data types of a Json file. The data dictionary should be generated from a CSV file of sample data.

You can not get all the types from a csv or json file.

Lets check the following example:

asd;3534;0674;slfo

What are the types? The third column with the value 0674 contains a string or an integer, decimal, float or what? Has that leading 0 any meaning? If so, it should be a string/character data.

The case is similar with the JSON files: Dates are stored as strings in those files (for example), you can not determine what its type. There should be values in a sample data which seems to be valid dates, but what is those values are some kind of category identifiers or generated identifiers (2014-01-24 could be for example 'marketing campaign' (id = 01) in the year 2014 for the partner with id of 24).

Conclusion: you can get a prediction from sample data, but you have to determine the real type manually (or at least revise the predictions). You have to 'add' the business meaning to the determination process -> you have to know what does that data mean to determine the data type required to store that data.

Also please note, that the RFC of the CSV files is not determines the "string identifier" character. It could or could not wrap the string with '"' or any other characters.


Inserting date range arrays daterange[] I'm trying to insert the value of an attribute of a tuple, which would be an array of date ranges, i.e. a daterange[], in a previous tuple accepted the value:
{"[2013-05-08,2018-03-22)"}

But now I would like to insert this array, with more than one range:
{"[19-03-2018,29-12-2020)","[21-06-2018,29-12-2020)", "[17-07-2018,29-12-2020)",
        "[18-04-2018,30-07-2018]", "[01-08-2019,19-11-2019]"}

I have tried all possible solutions,putting the outer quotes
 '{"[19-03-2018,29-12-2020)","[21-06-2018,29-12-2020)", "[17-07-2018,29-12-2020)",
        "[18-04-2018,30-07-2018]", "[01-08-2019,19-11-2019]"}'

Putting the internal single quotes instead of the double ones, but nothing works.
Gives me the error:

argument formats can't be mixed

This would be legal syntax - using ISO format for dates (YYYY-MM-DD), which is always a good idea as it does not depend on the current setting of datestyle. Your example only works with datestyle = 'DMY'.
 '{"[2018-03-19,2020-12-29)","[2018-06-21,2020-12-29)"}'

You can always ask Postgres to show you:
test=# SELECT ARRAY ['[2018-03-19,2020-12-29)'::daterange ,'[2018-06-21,2020-12-29)'];
                         array
-------------------------------------------------------
 {"[2018-03-19,2020-12-29)","[2018-06-21,2020-12-29)"}
(1 row)

The error msg you report does not seem to be generated by Postgres.

argument formats can't be mixed

Are you, by any chance, using an outdated version of pgAdmin III? See:

pgAdmin argument formats can't be mixed


how often to run Tablediff I wonder if it is safe to run the Tablediff tool on a database fairly often?
Let's say every 15 or 30 minutes. Also, the Data in the DB is around 100 GB.

tablediff is not designed to be used as a real-time synchronization tool (it seems this is your goal). If you really need real-time sync, there's database mirroring. The primary server sends its transaction logs to the secondary, which then replays the transaction logs - so the databases are synchronized
Check out MSDN for more info Database Mirroring (SQL Server)


How to make MySQL join one-to-many with lowest value? I have following database structure

Domain has many URL which has many positions which belongs to phrase which belongs to user which belongs to subscription.

In subscriptions table I have period field and I need to fetch all domains with lowest period value from subscriptions. To accomplish this challenge I've created following query

 select
        domains.domain,
        domains.id,
        subscriptions.period

        from
            domains
        inner join urls ON urls.domain_id = domains.id
        inner join positions ON positions.url_id = urls.id
        inner join phrases ON phrases.id = positions.phrase_id
        inner join users ON users.id = phrases.user_id
        inner join subscriptions ON subscriptions.id = users.subscription_id

Which return me results similar to

"domain": "http://www.example.com",
"id":     60,
"period": 7

"domain": "http://www.example.com",
"id":     60,
"period": 30

"domain": "http://www.other-example.com",
"id":     61,
"period": 7

In the results one of the domains occurs more than on time, because two different user with varied subscription have the same domain.

How can I filter results and get only lowest period value for given domain?

"domain": "http://www.example.com",
"id":     60,
"period": 7

"domain": "http://www.other-example.com",
"id":     61,
"period": 7

As suggested @ypercube it can be done by grouping by domains.id, domains.domain and selecting min(subscriptions.period). Complete query:

 select
        domains.domain,
        domains.id,
        MIN(subscriptions.period) as period

        from
            domains
        inner join urls ON urls.domain_id = domains.id
        inner join positions ON positions.url_id = urls.id
        inner join phrases ON phrases.id = positions.phrase_id
        inner join users ON users.id = phrases.user_id
        inner join subscriptions ON subscriptions.id = users.subscription_id

        GROUP BY domains.id

SQL Fiddle: http://www.sqlfiddle.com/#!2/438c1/2


Divide a PostgreSQL database into schemas My Postgres database will be focused on multidisciplinary organizations. The project axis is: a person could belongs to diverses disciplines (ex: arts, sports) for the same organization, and this organization needs an unique central Web app.

My first intent was modeling only one database but the size was enormous and the relations so complex and redundant, then my second and current approach is as follows:

People_Schema containing general tables like tbl_person, tbl_city, tbl_ocupation, etc.
Arts_Schema containing tables like tbl_art_center, tbl_enrollment(art_center_id FK, person_id FK), etc.
Sports_Schema containing tables like tbl_sport, tbl_team, tbl_team_member(team_id FK, person_id FK), etc.
And among others schemas in future growing.

The question is: Does this last approach is a good idea? or Does will be a hell for join queries between schemas or others painful implications that I have not seen?, considering multi-tenant expectatives, RESTfull APIs, DreamFactory Backend as a Service and Amazon EC2 Server.

Edit:
My approach was inspired by this statement taken from official Postgres docs: If the projects or users are interrelated and should be able to use each other's resources, they should be put in the same database but possibly into separate schemas.

schemas is used for organizational structure and namespacing. By namespacing I mean that you can have similarly named table in few places. And another feature of using schema is additional access control for that schema. You will have joins depending on your data model. You are using schemas implicitly already. There is a "search path" defined in PostgreSQL database.

So every time you type create table X, you use default schema.
And for queries you should use table aliases so you might add only schema name to every table but if you use alias your queries should be the same with or without schema.

I don't have experience with above feature (multitenant db and amazon and ...)


signal_wait_time_ms > wait_time_ms for SOS_SCHEDULER_YIELD in sys.dm_os_wait_stats Question regarding SOS_SCHEDULER_YIELD when using sys.dm_os_wait_stats:
Should signal_wait_time_ms not be exactly the same as wait_time_ms for the SOS_SCHEDULER_YIELD wait type?
For other wait types signal wait is when a worker thread is waiting in the Runnable Queue on the scheduler. Therefore I conclude that signal wait should be the same as total wait time (= wait_time_ms) for SOS_SCHEDULER_YIELD when looking at sys.dm_os_wait_stats.
So my problem is that the two columns mentioned should have the same value in this DMV for this particular wait type.
More specifically, I am looking at a case where signal_wait_time_ms is larger than wait_time_ms, and I would like an explanation for that difference.
The example I am looking at is from the book "Pro SQL Server 2019 wait Statistics" by Van de Laar. In a screenshot on page 120, signal wait is higher for SOS_SCHEDULER_YIELD.

From the documentation for sys.dm_os_wait_stats (emphasis mine):

Column name
Data type
Description

...
...
...

wait_time_ms
bigint
Total wait time for this wait type in milliseconds. This time is inclusive of signal_wait_time_ms.

If you see signal_wait_time_ms higher than wait_time_ms, then per the current documentation, that is a bug.  You should report it to Microsoft if it's causing you problems so they can investigate where things got off track.  It could be rounding errors in the underlying data that lead to a slight difference (you didn't mention how far off the two values were).
You said this:

the example I am looking at is from the book "Pro SQL Server 2019 wait Statistics" by Van de Laar. In a screenshot on page 120 signal wait is higher for SOS_SCHEDULER_YIELD.

Since the example is from a screenshot in a book, it's quite possible that editorial processes resulted in invalid information making it into the final published result.  In that case, I wouldn't worry about it too much.  You could contact the author to see if it's possible or likely that something happened like that (maybe they were splicing screenshots from multiple tests together for simplicity).


In Postgres, how to select by a column of type UUID I have a DB table that someone else created, its PK is a single column of type 'uuid'. I'm struggling to perform a simple select by that column. Examples I have tried with no success:

select from site where id = "9d4da323-4c20-360f-bd9b-ec54feec54f0"

select from site where id = UUID('9d4da323-4c20-360f-bd9b-ec54feec54f0')

select from site where id::text = '9d4da323-4c20-360f-bd9b-ec54feec54f0'

what's the right syntax?

A colleague enlightened me: it was the double quotes around the string literal. The right syntax is:

select from site where id = '9d4da323-4c20-360f-bd9b-ec54feec54f0'

I'm answering my own question, hoping that this helps someone else in the future.


Do I Alter user quota in USERS tablespace? I'm new to Oracle so please bear with me.

After installing Oracle XE 11.2, I created user SALES and granted create session and create table to the user.

Once I logged in with SALES I tried to create a table and I get the error ORA-01950: no privileges on tablespace 'SYSTEM'.

A related question in DBA StackExchange says that I should alter database default tablespace users;. and then assign a higher quota to user, like so: alter user SALES quota 50m on system;

I've already changed the default tablespace to users.

My question is: shouldn't I alter user SALES quota 50m on users; instead of tablespace system?

Yes, users should not write to the system or sysaux tablespaces.  If the user is expected to have data or objects and will be copied to development instances they should have their own tablespace as it makes moving them easier.

Change your file path to what you have and execute this as a privileged user

create tablespace sales_tbs datafile '/home/oracle/databases/ora11/sales.dbf'
 size 10M autoextend on maxsize 200M extent management
 local uniform size 64K;
alter user SALES quota 50m on sales_tbs;

and to make sure all future objects go to the dedicated tablespace for sales

 alter user sales default tablespace sales_tbs temporary tablespace temp;


SELECT person_id, MAX(answer_1), MAX(answer_2), MAX(answer_3)
FROM (<your_sub_query>) subquery_alias
GROUP BY person_id

would do the job.


Oracle 11G Clob column with where clause I am using a query to get data from a CLOB column. I need to add a WHERE clause to it to only get the rows where Error = 0 from the CLOB column.

The query I am using atm is:

select lt.T_ID, lt.SUCCESSFUL_TIMESTAMP,
       clob_xml_element_text(lm.XML_DATA, 'year-of-manufacture') "year",
       clob_xml_element_text(lm.XML_DATA, 'make') "make",
       clob_xml_element_text(lm.XML_DATA, 'model') "model",
       clob_xml_element_text(lm2.XML_DATA, 'mt') "odometer"
from tabletest2 lt
join tabletest lm on lt.T_ID = lm.T_ID
LEFT JOIN tabletest lm2 on lm.T_ID = lm2.T_ID and lm2.TYPE_ID = -129
where lm.TYPE_ID = -29
order by lt.SUCCESSFUL_TIMESTAMP ASC;

Output:

T_ID    SUCCESSFUL_TIMESTAMP    year    make    model odometer
167951  01/01/18 00:00:08.541000000     2009    Kia Sportage 2.0 Auto   82396
167979  01/01/18 00:02:42.613000000     2009    Mazda   2 Sport 5 DR 1.5 4AT    98589
174570  01/01/18 00:03:33.738000000     2015    Mercedes-Benz   CLA-Class CLA 250 2.0P/7AT/SL   null
198700  01/01/18 00:03:35.599000000     2008    Mitsubishi  Galant  null

Expected output

Remove the two rows where the odometer is null.

I tried to use:

(existsNode(lm2.XML_DATA,'/mt[Error]') = 0)

It works but when running the query with no limit I get the error:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

UPDATE:

Updating this question with the tables DDL.

CREATE TABLE tabletest
   (
  "TYPE_ID" NUMBER(10,0) NOT NULL ENABLE,
  "T_ID" NUMBER(10,0) NOT NULL ENABLE,
  "XML_DATA" CLOB,
  CONSTRAINT tabletest_pk PRIMARY KEY (T_ID)
   );

   CREATE TABLE tabletest2
    (
   "T_ID" NUMBER(10,0) NOT NULL ENABLE,
   "SUCCESSFUL_TIMESTAMP" TIMESTAMP(3) NOT NULL ENABLE,
   CONSTRAINT tabletest2_t_id
    FOREIGN KEY (T_ID)
    REFERENCES tabletest(T_ID)
    );

    INSERT INTO tabletest ("TYPE_ID", "T_ID", "XML_DATA") VALUES
    (
      '-29',
      1,
      '<?xml version="1.0" encoding="UTF-8"?>
    <l-ana version="2.9">
       <vehicle>
          <Error>0</Error>
          <year-of-manufacture>1999</year-of-manufacture>
          <make>Toyota</make>
          <model>Hilux</model>
          <mt>235998</mt>
       </vehicle>
    </l-ana>'
    );

    INSERT INTO tabletest ("TYPE_ID", "T_ID", "XML_DATA") VALUES
    (
      '-29',
      2,
      '<?xml version="1.0" encoding="UTF-8"?>
    <l-ana version="2.9">
       <vehicle>
          <Error>10</Error>
          <year-of-manufacture>1989</year-of-manufacture>
          <make>Toyota</make>
          <model>Hilux</model>
          <mt>535998</mt>
       </vehicle>
    </l-ana>'
    );

The disare output is to only get records where Error = 0.

Prologue

You are not having problems with a "CLOB column with a WHERE clause".
You are having problems processing XML Documents stored in a CLOB field.

This is your third question regarding this task.

I recommend you Read The Fine Manual before you advance on your problem:  Oracle XML Developer's Guide

existsNode

existsNode is deprecated.  Don't use it.

Instead, use:

WHERE XMLExists( '/mt[Error="0"]' passing xml_data)

PL/SQL Error

I suspect this might be due to a conversion error.

You should start investigating this error by fixing your Schema such that you are storing numbers as NUMBER, dates as DATE, and XML Documents as XMLType.  Even if this isn't the case, your Schema would be better for it.

My other thought is: your XML Document has a string but you are attempting to extract a number.


At my wits end trying to create my very 1st MySQL stored procedure I am trying to create a stored procedure in MySQL 5.5.8. using connector 8.0.13. Once this procedure is debugged, it will have to be embedded into a c#.net application, to be created on the fly every time the program is set up. For now, I am doing it manually.
For someone like me, with MS SQL, Oracle, and DB2 background, this is challenging. I honestly read MySQL dev docs and googled to get a hang of MySQL SQL syntax. What I gather is that:

I have to change delimiter to something like //
I should use if not exists just in case
Parameters do not start with @, and they have to be parenthesized
I end the block with the temporary delimiter // and restore it to ; afterwards

This is what I arrived at:
delimiter //
create procedure if not exists `logging`.`logEntry`(
   in hostName varchar(512)
  ,in entryDateTime datetime
  ,in entryText varchar(1024)
  ,out return_value int
)
begin
  insert into `logging`.`log` (hostName, entryDateTime, entryText) select hostName, entryDateTime, entryText;
  set return_value = last_insert_id();
  select return_value;
end//
delimiter ;

When I run the above code in SquirrelSQL, the server throws the following error:

Error: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'if not exists loging.logEntry`(  in hostName varchar(512) ,in
entryDat' at line 1 SQLState:  42000 ErrorCode: 1064

Here is what I tried:

Ran without DELIMITER
Removed if not exists
Removed backquotes around entity names
Inserted values() instead of select even though the standalone insert works

I know that this is something totally obvious for a seasoned MySQL developer, but it escapes someone like me with a different background. Am I using any functionality that does not exist in 5.5.8 or are there bugs in my code?

When I gave up on creating the stored procedure through SquirrelSQL and installed MySQL GUI Tools, then I was able to create the procedure with the exact same source, by using Catalogs, selecting my schema, switching to the Stored procedures tab, and clicking Create Stored Proc button at the very bottom.


Handling read conflict in cassandra? Say  we have three nodes(simple replica no sharding) i.e. N1,N2 and N3 with replication factor as 3.

As read and write request can go to any node, Say Request  write request(to update the same record at same time) comes and  goes
node N1.
Read request comes to read the same document and  goes to node N2. I believe it will return stale data as write is not replicated to N2 yet ?
Consider the default configuration where i have not tuned the read and write concerns.
I believe problem in step2 can be resolved with  either of below options

a. In synch replication, Good for strong consistency but bad for performance

b. Tuning read and write consistency as explained at read and write consistency

c. With the help of sharding(if required), where shard key will ensure all read and writes goes to particular node for particular shard key ?

Is my understanding in point 2 and 3 correct?

Mostly yes, but with small correction. You can get the stale data if they aren't replicated yet to another node (replication happens almost at the same time, but still could be delayed because of garbage collection, etc.).  You can get strong consistency by tuning consistency levels...

The comment is about point 3c - Cassandra's partitions are replicated to all nodes that were selected as replicas for particular partition - the driver or coordinator node can come to any of them, depending on multiple factors.  There is no such thing as an additional sharding key that will route to specific node - all replica nodes are equal.


How to join three table using inner join in postgres sql I am trying to get the data from the three table using inner join but its sows me empty result.

select comp.gps_time as complaint_register_date,comp.accept_date as complaint_accept_date,comp.name as citizen_name,comp.road_id,dept.dept_name,circle.circle_name,division.div_name
from complaints as comp
   INNER JOIN department as dept ON dept.dept_code=comp.dept_code
   INNER JOIN circle as circle on circle.circle_code=comp.circle_code
   INNER JOIN division as division on division.div_code=comp.div_code
where comp.templateid != null

  complaint table

Complaint_Id    Roadid  Dept_code   Div_code    Circle_code Name    Templateid  Accept_date             Gps_time
162            167889       3         1             4        Xyz       3        2019-02-25 09:40:39        2019-02-23 09:40:39
168            167880       2         4             6        abc     null       2019-02-25 09:40:39       2019-02-23 09:40:39

  Department table

Dept_code   Dept_name
3           Abcdept
2           Zyzdept
1           syzdept

  Circle table

Circle_code Circle_name
4           Xcircle
6           ycircle

  Division Table

Division_code   Division_name
1                Ydiv
2                ndiv
4                zdiv

You can't test for null using = or <> (or !=)

To test for non-null values you need to use IS NOT NULL:

where comp.templateid is not null


Why are global temporary tables truncated when the connection is closed? I'm a relatively new Oracle database developer coming from SQL Server.

I noticed that global temporary tables (created with ON COMMIT PRESERVE ROWS) lose their contents when the session times out, but the tables themselves still exist and have to be manually dropped before rerunning whatever script I have that creates them.

In SQL Server, a temporary table (which isn't global in any sense) is dropped and completely gone when the session is closed.

What is the logical or technical reason for Oracle keeping temporary tables around - but truncating them - when the session is closed?

(If there is a database or server option to avoid this behavior, it is irrelevant to my use-case since I'm only a developer and will be creating scripts that are run on an outside database.)

When connection is closed, the transaction is rolled back, that's why data is not preserved.
The rule of thumb for Oracle is "never use DDLs for business logic", you should never do any DDL during normal business run.

If you drop a TABLE (persistent or temporary) "all" the PL/SQL code gets invalidated and has to be re-compiled again.

GTT are called "global" because their definition are visible to all sessions.

UPDATE: GTTs are used sporadically in Oracle. Usually when you use a temporary table in MS SQL, you use a cursor in Oracle. IMHO in most cases you can avoid using them. GTTs are used for example as staging storage for some ETL loaders, so their stucture is predefined/fixed.


How to fix SQL Server instances with different builds on same server? I have a Windows 2008 box and there are 5 instances of SQL Server 2008 Standard installed.

After that, SQL Server service pack patched.

Weird thing is: the builds for those instance are different. For example,

One instance build No is 10.0.5512.0
Another instance build No is 10.0.5500.0

Confused. When applied SP on the server, this patch should be for all instances on this server.

How could this happen? How to fix it?

Sounds like someone applied a security update, but only selected one of the instances to apply the update (or it was installed prior to the instance at 5500 was installed).

To fix it, you can run the update again (5512 is from a security update I blogged about here). It should give you the option of which instances to apply; check all that you can. The ones that are already at that patch level (or higher!) will be greyed out.

In fact, I recommend you apply the most recent cumulative update, Cumulative Update #15.


Select distinct id with max date according to department I have a table such as following

RevNo  RevContent       PIC      Created
-----  ---------------  -------  ----------
00     Testing Purpose  Smith    2008-01-11
01     Testing Purpose  Windsor  2008-02-01
02     Test             Thorn    2008-01-05
02     Testing          Baker    2008-03-01
03     Testing only     Sykes    2008-01-20

I want the output like below, it display the latest date for each rev no. and other details

RevNo  RevContent       PIC      Created
-----  ---------------  -------  ----------
00     Testing Purpose  Windsor  2008-02-01
01     Testing          Baker    2008-03-01
02     Testing only     Sykes    2008-01-20

But when I run the sql, it display the value with the latest date only like below, I want it display the latest date for each rev no. and it based on department(session).

RevNo  RevContent  PIC    Created
-----  ----------  -----  ----------
01     Testing     Baker  2008-03-01

What I have tried:

SELECT CCSMASTERLISTREVNO, CCSREVCONTENT, CCSPREPAREDREV, CCSREVEFFECTIVEDATE
FROM CCS2_TBL_MASTERLIST a
WHERE CCSEQUIPMENTDPMT = :DPMT
AND CCSREVEFFECTIVEDATE = (SELECT MAX(CCSREVEFFECTIVEDATE) FROM CCS2_TBL_MASTERLIST GROUP BY CCSMASTERLISTREVNO HAVING CCSMASTERLISTREVNO =a.CCSMASTERLISTREVNO)
ORDER BY CCSMASTERLISTREVNO DESC;

12c+

Oracle 12c introduces the MATCH_RECOGNIZE syntax.

It may appear cumbersome at first, but I find that it becomes drastically easier to enhance and maintain as you add information that isn't from last row only.

Code:

with data(revno,revcontent,PIC,created) as (
select '00',     'Testing Purpose',  'Smith',    date '2008-01-11' from dual union all
select '01',     'Testing Purpose',  'Windsor',  date '2008-02-01' from dual union all
select '02',     'Test',             'Thorn',    date '2008-01-05' from dual union all
select '02',     'Testing',          'Baker',    date '2008-03-01' from dual union all
select '03',     'Testing only',     'Sykes',    date '2008-01-20' from dual
)
select *
from data
  MATCH_RECOGNIZE (
    partition by revno
    order by created
    MEASURES
        last(anyrow.revcontent) as revcontent,
        last(anyrow.pic) as pic,
        last(anyrow.created) as created,
        first(anyrow.revcontent) as first_revcontent,
        first(anyrow.pic) as first_pic,
        first(anyrow.created) as first_created
    one row per match
    PATTERN (ANYROW*)
    define
      ANYROW AS 1=1
  )

Table Results

REVNO REVCONTENT      PIC     CREATED   FIRST_REVCONTENT FIRST_PIC FIRST_CREATED
----- --------------- ------- --------- ---------------- --------- -------------
00    Testing Purpose Smith   11-JAN-08 Testing Purpose  Smith     11-JAN-08
01    Testing Purpose Windsor 01-FEB-08 Testing Purpose  Windsor   01-FEB-08
02    Testing         Baker   01-MAR-08 Test             Thorn     05-JAN-08
03    Testing only    Sykes   20-JAN-08 Testing only     Sykes     20-JAN-08


How can I allow an Oracle database to be accessed remotely? I have an Oracle database installed on one machine, and I am trying to accessing the same from another machine.

I tried via Oracle SQL developer eclipse, and even via JDBC program, butnothing works. I am not sure if it is firewall issue (I have ruled out this because both the machines are in same domain) or something to do with Oracle database setting. I know in MySQL you need to grant permission to connect to database remotely. Do we need to set something similar on oracle SID also?

The error message I get:

  Io Exception: The Network Adapter could not establish the connection

Oracle is an enterprise database server so you shouldnt need any strange setup to get it working, just the correct configuration on the DB server or client will be enough.

When you get connection errors, i suggest first ensuring that the client machine can ping the database server where  is the name or ip address - try both as it's possible the name may not be configured in the hosts file of the client machine. (Note that ping may be disabled due to security but if you get a reply back, you know the connection is good.A non reply may not mean the network is missing but you can try to ssh or telnet to the dbhost from the client machine as an alternative)

 unix> ping <dbhost>

Once you have confirmed the client machine can see the db server, you can check the configuration on the DB server.

On the DB server, if you connect with the following, you are connecting directly to the database without using TNS.

 unix> sqlplus user/pass

If you connect instead with

 unix> sqlplus user/pass@ORACLE_SID

where ORACLE_SID is the identifier of the database, you will be using TNS, the same as your client machine.  If this doesnt work locally, then it's also not going to work on the remote machine.

Check the listener is running

 unix> lsnrctl status

and if it's not, start it as the oracle user

 unix> lsnrctl start

and try again to connect using user/pass@ORACLE_SID

If this connects OK, then check your JDBC configuration to ensure you have specified the connection string correctly.

see http://javaeesupportpatterns.blogspot.de/2011/08/network-adapter-could-not-establish.html for assistance with the correct jdbc connection string.


Error remote connection from Oracle 11g I am trying to connect to my Oracle Database 11g from another computer connected in the same network.

In the connection data I put the address of the server machine, but it does not allow me the connection. From the server machine if it allows me to connect, but not from another computer:

Error de E/S. The network adapter could not establish the connection

  Machine 1 - Oracle Server: Local access connection: Yes

  Machine 2 - Client Remote connection: Failed

Both machines are in the same network segment:
192.168.x.machine1
192.168.x.machine2

Listerner
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = LAPTOP-BPS592UE)(PORT = 1521)(QUEUESIZE = 32))
    )
  )

Status LISTENER:

C:\..\..\11.2.0\server\network\ADMIN>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 22-JUN-2018 11:30:17

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
Start Date                22-JUN-2018 11:04:36
Uptime                    0 days 0 hr. 25 min. 40 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   C:\..\..\..\product\11.2.0\server\network\admin\listener.ora
Listener Log File         C:\..\..\..\diag\tnslsnr\LAPTOP-BPS592UE\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LAPTOP-BPS592UE)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LAPTOP-BPS592UE)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully

Hi the solution to problem:

Add Windows Firewall Exception to:

  Oracle_home\bin\oracle.exe - Oracle Database executable

  Oracle_home\bin\tnslsnr.exe - Oracle Listener


Tables for Product Components AND Component Combinations I have a basic understanding of relational databases and would like to assign a number of attributes to an assortment of products. Products examined consistently have four components: model, fabric, color and size; each of which has a corresponding vendor-supplied identifier (ie. 'SSSS.MMMM.CCCC.ZZZZ' = a single product at the size level).

The most streamlined solution would seem to be having a product table that is at the size level, then a table for model, fabric, color and size to assign relevant attributes under each. The problem I am facing is that I would like to assign certain attributes to, say, a given fabric/color combination. Should I then also have tables of each fabric/color combination in this case?

I have laid out roughly what I am referring to above with the below table diagram:

My personal recommendation would be to have a separate table four each of the for attributes listing the different options. This is purely to make it easy to validate data entry.

Then a product for each combination that is required - store transactions against this.

Then a product line table that has several products of different sizes/colors whatever.

Reason for this approach is that a colour/size combination represents a product that should be tracked distinctly from any other size/color of the same thing. A product line might be 'army cargo trousers', and could come in lots of sizes and colours.


SQL is producing CROSS JOIN but I want INNER JOIN (records being multiplied) SELECT *
  FROM        GRNHISTORY GR
  inner Join Inventorytransferlog itl on GR.ReferenceEntryID=itl.ReferenceEntryID

Both tables GRNHISTORY and Inventorytransferlog have their own ID column and unique ids. But we did not need it nor are we using it.
ReferenceEntryID is a foreign key from another table. Both tables have 10 records. Now when I run this query it shows me 100 records which I seem not to be able to understand. It should be showing 10 records as per my understanding. Please correct me where I am wrong.

The answer becomes obvious when valid values are inserted for the PRIMARY KEYs of the two tables you are joining and you look at the results:

An example:

The two tables:

CREATE TABLE fred (int_1 INTEGER, ref1 INTEGER);   -- equivalent of GRNHISTORY
CREATE TABLE bill (int_2 INTEGER, ref2 INTEGER);   -- of Inventorytransferlog

The data:

INSERT INTO fred VALUES (1, 55), (2, 56), (3, 57);
INSERT INTO bill VALUES (21, 55), (22, 56), (23, 57);
       -- NOTE: only 1 matching value of the JOINing refrerence field per table!
       -- and NOTE: different PRIMARY KEYs for the two tables!

The (your) SQL:

SELECT * FROM fred f
INNER JOIN bill b ON f.ref1 = b.ref2

The result (and fiddle):

int1 ref1 int2 ref2
   1   55   21   55
   2   56   22   56
   3   57   23   57

But, if the data is now changed as follows:

INSERT INTO fred VALUES (1, 55), (2, 55), (3, 55);
INSERT INTO bill VALUES (21, 55), (22, 55), (23, 55);

       -- NOTE: 55 - ALL the values of JOINing reference columns in each record match!

Note that the joining field now has the same value in both tables for all records, then run the same SQL as above. Result (and fiddle):

  int1  ref1 int1 ref2
    1   55  21  55
    2   55  21  55
    3   55  21  55
    1   55  22  55
    2   55  22  55
    3   55  22  55
    1   55  23  55
    2   55  23  55
    3   55  23  55

And the result is a 9 (i.e. 3 x 3) records (effective) CROSS JOIN because your data makes it so! Because you've asked the SQL to JOIN on very common values in BOTH tables so therefore you can expect a lot of records in the result!

You can check this out by doing a

SELECT COUNT(DISTINCT(ref1)) AS distinct_ref FROM fred;

answer = 1, (and the same result for bill with ref2).

I'm willing to bet that in your tables where you are getting a CROSS JOIN (or something very similar), that the SQL above using the ReferenceEntryID will get you very low counts (which also match in both tables) leading to many matching records in your SELECT.

There's the explanation for your apparent multiplication or CROSS JOIN - you have too many matching records in both tables - et voilà!


mysql join member roles i need to get the members roles for specific users id

example return for the user id 4 the members roles
e.g id 4 peter father des brother

INSERT INTO `testdb`.`users` (`id`, `name`,`year`,`rank`) VALUES (4,"jef", 2006,1);
INSERT INTO `testdb`.`members` (`id`, `first_name`,`last_name`,`gender`) VALUES (1,"peter","zex",1);
INSERT INTO `testdb`.`members` (`id`, `first_name`,`last_name`,`gender`) VALUES (2,"des","zex",1);
INSERT INTO `testdb`.`member_roles` (`member_id`, `user_id`,`role`) VALUES (1,4,"father");
INSERT INTO `testdb`.`member_roles` (`member_id`, `user_id`,`role`) VALUES (2,4,"brother");

i need to get the members roles for specific users id

SELECT mem.`first_name`, mr.`role`
FROM member_roles mr
JOIN members mem
ON mr.`member_id`=mem.`id`
WHERE mr.`user_id` = 4

If you want the User info as well:

SELECT users.`name` AS "User Name", mem.`first_name` AS "Member First Name", mr.`role` AS "Member Role"
FROM member_roles mr
JOIN members mem
ON mr.`member_id`=mem.`id`
JOIN users
ON mr.`user_id` = users.`id`
WHERE mr.`user_id` = 4


delimited values in a column value I have a table which have a * to * relationship with another table. Lets say the first table is T1 and the second table is T2. So I need to keep the T2 Pks linked with single row in T1. I really like to go with 1NF here and add new table to keep the relationship. The number of rows linked from T2 is around 10 at maximum. The PK of T2 is a bigint.  So doing this will require me to either join which will be not really good because of dplicates that will come from T1. Second is to do a seperate query from application to get the T2 PKs. Now if I go with option 2 every thing is nice but I require an additional query. Then I though why I can't keep the T2 PKs in a T1 column delimited. This will avoid a query that is require just to find the T2 ids. Well this is feasible since there never will be a requirement to query T1 values by T2 PKs.  Any other good solution for this? Or should still stick with 1NF to solve this.

If you're designing an OLTP database, you should really design it in third normal form initially.  If, once you've done that, you find that you can make measurable performance improvements by denormalizing something and there is no other way to get those improvements, it makes sense to denormalize selectively.  It does not make sense, however, to denormalize to first normal form proactively.

I don't really understand why you expect that adding a mapping table would be problematic.  Yes, you'll need to join to it.  But that's what a relational database was built to do-- it joins tables very very efficiently.  Plus, you won't have to constantly parse the delimited string to figure out the T2 keys.  And you will be able to create a foreign key that ensures that the T2 keys in the mapping table are valid and that you don't have keys in your delimited string that don't map to a valid row in T2.  I don't really see the downside.

If you have a separate mapping table and you want to return a single row of data to the client with the T1 data and the T2 keys (which seems awfully suspicious-- the T2 keys are only useful if you are going to subsequently use them to query T2 in which case you almost always want to just let the database join to T2 in the initial query) you can always pivot the data in the mapping table to return a delimited string of T2 keys.  I certainly wouldn't recommend that but it can be done.


Un-Install OS Patch (KBs) in Physical PRODUCTION Servers - Consequences We have our PRODUCTION Database and Application Servers which are Physical Servers .

Recently , the October month OS Patches (specific KBs) are little problematic and reported by Microsoft too that they are going to fix in November patches .

https://blogs.msdn.microsoft.com/dataaccesstechnologies/2017/10/18/unexpected-error-from-external-database-driver-1-microsoft-jet-database-engine-after-applying-october-security-updates/

The document says :

Uninstall the security patch (Not recommended):
Registry change (Not recommended): [Request you to take a backup of the registry key before making any changes]
Please guide me on :

Do I need to expect any issue while Uninstalling patches (specific KBs).

Since my servers being PRODUCTION and Physical in nature , what are the consequences I might face while rollback of patches .

In this case is it recommended to Rollback patches ?

I am preparing one PowerShell code for this process .
Kindly help .

As the MSDN page you link to states that a fix has already been released, I would install that rather than removing the previous update as the fix is likely to be intended to go over the top (though is likely to be made such that it can replace the older update too) and it will only be one operation rather than two potentially requiring two reboots.

In either case the main consequence of adding or removing patches is the potential need to reboot (or if not a full reboot, restart affected services) which may involve downtime depending on your arrangement (a cluster might be able to avoid downtime). The extra consequence of removing patches (without installing a replacement) is that the changes within them are gone, which if it is a roll-up patch could mean unrelated security updates that you need to mitigate by other means until the patch can be re-applied.

The best approach to take is to test in a production-like environment before making changes to production proper.


SQL Server Agent Server Login Account What SQL Server login does the SQL server agent use to log in to the database instance?

I am running the SQL server agent as the built in local system account.

I have created a SQL agent job which has one step. That step inserts the value of SYSTEM_USER into a table.

When i Run the job and then select from the table when the job is complete, I can see the value of SYSTEM_USER was DOMAIN\SERVERNAME$ so it seems that is what the agent is logging into the instance as.

So why can the agent log in as DOMAIN\SERVERNAME$ when it is not an SQL Login?

What privileges does this account have?

The SQL Agent uses whatever is assigned as the login on the Windows Service.
To access Windows services and see, click the start menu and type 'Services'.

Locate the SQL Agent and view the properties.
If the account is the Local System, then it is actually using the credentials of the machine.  You confirmed that already when you saw that the DOMAIN\SERVERNAME$ was returned.

(That is the account for the machine that it's hosted on.  All PC's have a local system account and it gets added when SQL is installed.  I believe the system account inherits sysadmin privileges which is why you probably don't want to leave it as the default. NT AUTHORITY\SYSTEM I believe is how it gets added to the logins in SSMS.)

If you want to assign different permissions which is a good idea, you can use a different login, usually a service account or master service account.

Alternatively create a credential in SQL server assigned to an account that has permissions.  Then create a proxy in the SQL Agent and you can use that to run the job.

According to MS best practices is to always assign the login credentials for the service accounts using SQL Configuration Manager.  While I've never run into a problem doing it other ways, if MS says so, it's probably correct.