Passing in the table into a stored procedure Is it possible to pass in the name of a table into a stored procedure?

For example, suppose you have several views of the same table. They all have the exact same structure.

You want a stored procedure that can be run for any of the views.

Something like:

create procedure myprocedure
@tableName varchar(50) = ''
select blah from @tableName where blah = blah2

When I try to do this, I get

Must declare the table variable @tablename

Any ideas how I can do this?

Instead of creating views, why not make an inline table-valued function - effectively a parameterized view.  You basically get all the benefits of views AND parameters.

Another thing you can do in your TVF is something like this:

SELECT yourtable.*
FROM yourtable
INNER JOIN ranges
    ON yourtable.date BETWEEN ranges.start AND ranges.end
WHERE ranges.name = @range_name

Now your ranges have names (in a table where they can be easily managed without changing the schema) - this also fairly seamlessly handles multiple ranges with the same name, although if ranges overlap you can have duplicates due to the join matching both.

Also, please consider avoiding BETWEEN:

https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common

I strongly suggest that you reconsider your requirements and when asking questions here be sure to include more about your motivations, because when you bring in a question with a preconceived notion of what the solution is, you are likely to get a suboptimal solution.  (like the dynamic SQL solutions given aren't going to easily work for your OR case, but that information about the problem space is buried in a comment from you)

From: http://sqlfiddle.com/#!6/a628a/1

CREATE TABLE LogData (
  Id INT IDENTITY NOT NULL
  ,Dt DATETIME NOT NULL
  ,Data VARCHAR(max) NOT NULL
);

CREATE TABLE Ranges (
  Id INT IDENTITY NOT NULL
  ,Name VARCHAR(50) NOT NULL
  ,DtStart DATETIME NOT NULL
  ,DtEnd DATETIME NOT NULL
);

CREATE TABLE Groups (
  Id INT IDENTITY NOT NULL
  ,Name VARCHAR(50) NOT NULL
);

CREATE TABLE RangeGroups (
  RangeId INT NOT NULL
  ,GroupId INT NOT NULL
);

INSERT INTO LogData (Dt, Data)
VALUES ('2011-11-23 11:00', 'before thanksgiving day')
       ,('2011-11-24 11:00', 'on thanksgiving day')
       ,('2011-12-24 11:00', 'on christmas eve')
       ,('2011-12-25 11:00', 'on christmas day')
       ,('2012-11-21 11:00', 'before thanksgiving day')
       ,('2012-11-22 11:00', 'on thanksgiving day')
       ,('2012-12-24 11:00', 'on christmas eve')
       ,('2012-12-25 11:00', 'on christmas day');

INSERT INTO Ranges (Name, DtStart, DtEnd)
VALUES ('THANKSGIVING2011', '2011-11-24', '2011-11-25')
       ,('CHRISTMASEVE2011', '2011-12-24', '2011-12-25')
       ,('CHRISTMASDAY2011', '2011-12-25', '2011-12-26')
       ,('BOXINGDAY2011', '2011-12-26', '2011-12-27')
       ,('NEWYEARSEVE2011', '2011-12-31', '2012-01-01')
       ,('NEWYEARSDAY2012', '2012-01-01', '2012-01-02')
       ,('THANKSGIVING2012', '2012-11-22', '2012-11-23')
       ,('CHRISTMASEVE2012', '2012-12-24', '2012-12-25')
       ,('CHRISTMASDAY2012', '2012-12-25', '2012-12-26')
       ,('BOXINGDAY2012', '2012-12-26', '2012-12-27')
       ,('NEWYEARSEVE2012', '2012-12-31', '2013-01-01')
       ,('NEWYEARSDAY2013', '2013-01-01', '2013-01-02');

INSERT INTO Groups (Name)
VALUES ('HOLIDAYS2011')
       ,('HOLIDAYS2012')
       ,('ANYCHRISTMAS');

INSERT INTO RangeGroups (RangeId, GroupId)
SELECT Id, (SELECT Id FROM Groups WHERE Name = 'HOLIDAYS2011')
FROM Ranges WHERE Name LIKE '%2011';

INSERT INTO RangeGroups (RangeId, GroupId)
SELECT Id, (SELECT Id FROM Groups WHERE Name = 'HOLIDAYS2012')
FROM Ranges WHERE Name LIKE '%2012';

INSERT INTO RangeGroups (RangeId, GroupId)
SELECT Id, (SELECT Id FROM Groups WHERE Name = 'ANYCHRISTMAS')
FROM Ranges WHERE Name LIKE 'CHRISTMAS%';

CREATE FUNCTION dbo.JustDoIt(@GroupName VARCHAR(50))
RETURNS TABLE
RETURN (
SELECT LogData.*
FROM LogData
INNER JOIN Ranges ON LogData.Dt >= Ranges.DtStart
AND LogData.Dt < Ranges.DtEnd
INNER JOIN RangeGroups
ON RangeGroups.RangeId = Ranges.Id
INNER JOIN Groups
ON Groups.Id = RangeGroups.GroupId
AND Groups.Name = @GroupName
);

SELECT *
FROM dbo.JustDoIt('HOLIDAYS2011');

SELECT *
FROM dbo.JustDoIt('HOLIDAYS2012');

SELECT *
FROM dbo.JustDoIt('ANYCHRISTMAS');

This part:

SELECT LogData.*
FROM LogData
INNER JOIN Ranges ON LogData.Dt >= Ranges.DtStart
AND LogData.Dt < Ranges.DtEnd
INNER JOIN RangeGroups
ON RangeGroups.RangeId = Ranges.Id
INNER JOIN Groups
ON Groups.Id = RangeGroups.GroupId
AND Groups.Name = @GroupName

Means that each log item will be joined to the ranges it fits in (by date), each range will join to the groups they are in, but only that group selected will be used.

This code works in C# + SQL Server 2008

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[test](@table_name varchar(max))
AS
BEGIN
    declare @tablename varchar(max)=@table_name;
    declare @statement varchar(max);

    set @statement = 'Select * from ' + @tablename;

    execute (@statement);
END


Unable to connect to SQL server 2008 R2 via Instance name? I don't understand :

I've installed sql server 2008 R2. the installation process asked me for the desired instance name  , so I wrote: MSSQLSERVER.

after the installation , running Setup Discovery Report does shows me the instance name :

I've also installed all the service packs.

Also , all the services are up :

Also , all the ports are open ( tcp:1433 + udp :1434)

Also , Named pipes are on :

So ,  where is the problem ?

I'm unable  to connect via the instance name :

Also unable   with :

user\MSSQLSERVER where user is the computer name.

Also unable  with : .\MSSQLSERVER

But (weird) :

Those 3 do work :

user ( "user" is my machine name)
(local)
.

Question :

why I can't use my instance name to connect to my sql server ?

Related info :

After connecting via one of the 3 who work  , I run a helpful info :

You selected the default instance name as the name of your instance.

Instance Configuration

"If you specify MSSQLServer for the instance name, a default instance will be created. For SQL Server Express, if you specify SQLExpress for the instance name, a default instance will be created."

To connect to the default instance you don't specify the instance name, only the server name.

Use below query to find exact instace name

DECLARE @GetInstances TABLE
( Value nvarchar(100),
 InstanceNames nvarchar(100),
 Data nvarchar(100))

Insert into @GetInstances
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
  @value_name = 'InstalledInstances'

Select InstanceNames from @GetInstances


User cannot query cross-database, despite having permissions for both databases SQL Server 2017 Enterprise (14.0.3356.20), running on Windows Server 2016.
I have a situation where an application's user needs to join on a table in a separate database in the same instance, but I'm getting permissions errors when I try to run anything cross-database.  Basically, what it boils down to is that this works fine:
use DB2
EXECUTE AS user = 'domain\appUser'
SELECT TOP 1 * FROM dbo.Table
REVERT

But this doesn't:
use DB1
EXECUTE AS user = 'domain\appUser'
SELECT TOP 1 * FROM DB2.dbo.Table
REVERT

I get the following error on the second query:
Msg 916, Level 14, State 1, Line 3
The server principal "domain\appUser" is not able to access the database "DB2" under the current security context.

The user is mapped to both databases.  There are plenty of places elsewhere in our other applications that we have used this same kind of cross-database join, and they work fine.  Logged in as my admin user, this also works fine of course. I admittedly don't know too much about permissions in SQL Server, and this one has me stumped.

This is an artifact of your testing methodology.  You impersonated a user.  A user is a database-level principal.
Instead impersonate the login, or test with the target identity actually connecting to SQL Server.

I think you to enable TrsutWorthy ON for cross database queries. Please go through below blog. It explains pros and cons.
https://www.sqlservercentral.com/blogs/what-is-the-sql-server-trustworthy-database-property


How to create Clustered and non clustered index in Oracle? I dug couple of hours regarding my question and didn't get satisfactory answer. Still I have doubt. I have found the following about Clustered Index:

Data is stored in the order of the clustered index.
Only one clustered index per table.
When a primary key is created a clustered index is automatically created as well.

I got these points, but my questions are:

Does Clustered index exist in Oracle database? since I read in some blogs "Oracle does not have a concept of a clustered index."
If yes, please let me know the sql statement to create a clustered index.
As said above, clustered index automatically gets created when primary key is defined on a column of a table, how can I check the index type if it is created or not ?

Please find my table architecture :

Let me know if anything else is required to get answers for these questions.

Does Clustered index exist in Oracle database? since I read in some blogs

Yes there is.
It is called "index organized table" (IOT) - which in my opinion is the better name as it makes it absolutely clear that the index and the table are the same physical thing (which is the reason why we can have only one clustered index in SQL Server)

If yes, please let me know the SQL statement to create a cluster index.

There is no such thing as create clustered index in Oracle.
To create an index organized table, you use the create table statement with the organization index option.
In Oracle you usually use IOTs for very narrow tables. Very often for tables that only consist of the primary key columns (e.g. m:n mapping tables), e.g.
create table assignment
(
   person_id  integer not null,
   job_id     integer not null,
   primary key (person_id, job_id)
)
organization index;

You can create IOTs with more column, in that case the you need to define the non-pk columns as "included" columns. E.g. if the assignment table should have additional columns, like start and end date that are not part of the primary key:
create table assignment
(
   person_id   integer not null,
   job_id      integer not null,
   start_date  date,
   end_date    date,
   primary key (person_id, job_id)
)
organization index
including start_date
overflow storage (initial 4k);

See the manual for more details and examples: https://docs.oracle.com/database/121/SQLRF/statements_7002.htm#i2153235

Somewhat unrelated, but maybe interesting anyway:
An interesting blog post that questions SQL Server's (and MySQL's) behaviour of using a clustered index as the default when creating a table:
Unreasonable Defaults: Primary Key as Clustering Key
Coming from an Oracle background I wholeheartedly agree with that.

CREATE CLUSTER employees_departments_cluster
(department_id NUMBER(4)) SIZE 512;

CREATE TABLE employees ( department_id number(4), ename varchar2(10), empno int )
CLUSTER employees_departments_cluster (department_id);

CREATE TABLE departments ( department_id number(4), dname varchar2(10), sales int )
CLUSTER employees_departments_cluster (department_id);

CREATE INDEX idx_emp_dept_cluster ON CLUSTER employees_departments_cluster;
insert into departments values (1,'d1',1000);
insert into employees values (1,'emp123',123);
insert into employees values (1,'emp124',124);
insert into employees values (1,'emp125',125);

select INDEX_NAME, INDEX_TYPE, TABLE_NAME, TABLE_TYPE, CLUSTERING_FACTOR from DBA_INDEXES
WHERE INDEX_NAME=UPPER('idx_emp_dept_cluster')
;

INDEX_NAME, INDEX_TYPE, TABLE_NAME, TABLE_TYPE, CLUSTERING_FACTOR
IDX_EMP_DEPT_CLUSTER,CLUSTER,EMPLOYEES_DEPARTMENTS_CLUSTER,CLUSTER,1


How to use SQL*plus from standard user in Windows Vista to run sql query to Oracle10g on my server? i have a bach file it contain:

@echo off
sqlplus x/y@z @run.sql

for execute this code correctly i have to execute this file from administrator user from my client, for some reason i need to run this file from a standard user without administrator privilege and without any password request.

when i research about my problem, i understood sqlplus can't be run without administrator privilege.

How to connect to SQL*plus from standard user in Windows Vista to Oracle10g on my server?

I didn't find how to run sqlplus from standard user but JJ. answer in this question help me to find alternative way.

I just use PsExec to run sqlplus in my server.


Unusual deadlock occuring I have spent a few days trying to debug a deadlock that occurs in production. This is one I didn't understand however.
Below is pseudo code for the stored procedure. The stored procedure generates a deadlock with another instance of itself.

I know that the code is not brilliant, this old code on the system. But I want to understand why this deadlocks. The graph always shows the second selects deadlocking.

The first select performs a table scan, so I believe that places click on all rows in table.

I'd have thought this would queue one stored procedure behind the other. I cannot recreate this after days if trying.

Begin tran

Begin tran

Set isolation level repeatable read

//This select does a table scan, so I believe does X lock on all rows

Select a,b,c
From queuetable with(xlock, rowlock)
Where a = x

Delete from queuetable
Where a = X

Commit tran

Begin tran

//Get oldest row - this is the deadlocking query

Select top 1 a,b,c
From queuetable
Order by b

//Mark row to be processed
Update queuetable
Set c=processing
Where a = y

Commit tran

Commit tran

Try without the first BEGIN TRAN and last COMMIT TRAN.

If you are using a stored procedure, the procedure itself already creates a transaction.

Your transaction locks because of the following script:

//This select does a table scan, so I believe does X lock on all rows
Select a,b,c
From queuetable with(xlock, rowlock)
Where a = x

From what I see in the script, the with(xlock, rowlock) is not needed.

Try with the following script:

SET XACT_ABORT ON
SET ISOLATION LEVEL REPEATABLE READ

-- This select does a table scan, so I believe does X lock on all rows
SELECT a, b, c
  FROM queuetable
 WHERE a = x

DELETE queuetable
 WHERE a = X

-- Get oldest row - this is the deallocking query
SELECT TOP 1 a, b, c
  FROM queuetable
 ORDER BY b

-- Mark row to be processed
UPDATE queuetable
   SET c = processing
 WHERE a = y


Load csv file to mysql using load data local infile I need a simple way to load a csv file from my computer to the database (which is on a remote server).
I'm trying to load the file using a php page that executes this code:

$file = $_FILES['csv']['tmp_name'];
$handle = fopen($file,"r");
$name = $_FILES['csv']['name'];

$import = "LOAD DATA LOCAL INFILE '" . $name .
                "' INTO TABLE temporal_load
                  FIELDS TERMINATED BY ','
                  optionally ENCLOSED BY '\"'
                  LINES TERMINATED BY '\n'
                  IGNORE 1 LINES
                  (num,ticker,company,sector,industry) ";

mysql_query($import) or die(mysql_error());

But when I select a file on the php page and try to load it, shows the error message:

File 'test11-14-15.csv' not found (Errcode: 2)

I already reviewed that the variable mysql.allow_local_infile is ON and the
database connection was made this way:

mysql_connect(HOST,USER,PASSWORD,false,128);

The file is not on the remote server, the file is on my computer, but it should work anyway because I'm using LOCAL.

What am I doing wrong???

Your problem is on using the variable $name on your LOAD DATA LOCAL INFILE statement, you should use the variable $file in your statement..

Your statement should change to;

$import = "LOAD DATA LOCAL INFILE '" . $file. "' INTO TABLE temporal_load FIELDS TERMINATED BY ',' optionally ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (num,ticker,company,sector,industry) ";

mysql_query($import) or die(mysql_error());

You need to use $_FILES['csv']['tmp_name'] instead of $name in your import query.

So your php function might look like like:

$filename = $_FILES['csv']['tmp_name'];

$import = "LOAD DATA LOCAL INFILE '" . $filename . "' INTO TABLE temporal_load FIELDS TERMINATED BY ',' optionally ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES (num,ticker,company,sector,industry) ";

mysql_query($import) or die(mysql_error());


Oracle Database 10g JRE Error I am trying to install Oracle Database 10.2 on OEL5U6(Linux System). I followed all the instructions from HERE.
When I am executing installer this return:

  Preparing to launch Oracle Universal Installer from
  /tmp/OraInstall2013-08-06_12-57-33AM. Please wait ...The Java RunTime
  Environment was not found at
  /tmp/OraInstall2013-08-06_12-57-33AM/jre/bin/java. Hence, the Oracle
  Universal Installer cannot be run. Please visit
  http://www.javasoft.com and install JRE version 1.3.1 or higher and
  try again. : No such file or directory.

But Java is already installed on my system.

Please Do the below written instructions:

Set the class path of latest version of Java in your system  .
set the path as JAVA_HOME in Environment variable too.

Hope this would help you.


In SQL Server, is there a way to determine the values of the parameters passed to an executing stored procedure One way to determine the executing stored procedure is to use "dynamic management" methods, like so:

SELECT
    sqlText.Text, req.*
FROM
    sys.dm_exec_requests req
OUTER APPLY
    sys.dm_exec_sql_text(req.sql_handle) AS sqltext

However, this only displays the text of the stored procedure's create statement. e.g.:

CREATE PROCEDURE IMaProcedure @id int AS SELECT * FROM AllTheThings Where id = @id

Ideally I'd like to see what the parameters were for the running procedure that are causing it to run so long for the particular set of offending parameters.

Is there a way to do that?  (In this question Aaron Bertrand mentions DBCC InputBuffer, but I don't think that's appropriate for this problem.)

This information -- run-time parameter values passed into a Stored Procedure (i.e. RPC call) or parameterized query -- is only available via a SQL Trace (and I assume the equivalent Extended Event in the newer versions of SQL Server). You can see this by running SQL Server Profiler (it comes with SQL Server) and selecting the various "Completed" events, such as: RPC:Completed, SP:Completed, and SQL:BatchCompleted. You also need to select the "TextData" field as the values will be in there.

The difference between my answer and @Kin's answer on this Question is that @Kin's answer (unless I am mistaken, in which case I will remove this) focuses on getting either:

your own query plan (in which case it can have the runtime parameter info in it, but not for other Sessions/SPIDs), or
plans from the DMVs (in which case they should only have the compiled parameter values, which are not runtime values).

My answer focuses on getting the parameter values for other sessions that are currently running. When relying on the DMVs, there is no way to know if the runtime parameter value is the same as the compiled parameter value. And the context of this question is tracking down the runtime value of queries being submitted via other Sessions/SPIDs (and in SQL Server 2005, whereas Extended Events were introduced in SQL Server 2008).

SELECT DB_NAME(req.database_id),
sqltext.TEXT,
req.session_id,
req.status,
req.start_time,
req.command,
req.cpu_time,
req.total_elapsed_time ,   REPLACE(REPLACE(REPLACE(REPLACE(
CONVERT(VARCHAR(MAX), CONVERT(XML, REPLACE( query_plan, 'xmlns="','xmlns1="')).query('//        ParameterList/ColumnReference')),
'<ColumnReference Column="','declare '),
'" ParameterDataType="',' '),
'" ParameterCompiledValue="(',' = '),
')"/>', CONCAT(';', CHAR(10) , CHAR(13))) ParameterList
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
 CROSS  APPLY sys.dm_exec_text_query_plan(plan_handle, statement_start_offset, statement_end_offset) qp
order by req.total_elapsed_time desc


How to set up login/guest and checkout/add to cart I want to create an online store database. I want to give users the ability to 'add to cart' and 'check out' without login.

My idea is that if the user logs in, then I can save the information in tables ex(bag, order), but if the user wants to check out as guest than I can create a guest_unique_id and set up bag that way. Guest bag will be mannually deleted after 10 days.

The only issue I see is that sometimes account_id will be empty. I guess I can check to see if guest_unique_id is NULL then user has an account, if not then it is a guest.
What you guys think about this structure, is there a better way to do this?

Why not have account_id always populated? When anyone who creates a shopping bag, they get an account_id auto assigned, but there's another field on the account table with a boolean is_guest flag set true by default. You could keep things like customer details in a separate table with a 1:1 map to the account table, for customers who do create accounts.

Your challenge then is that you might have repeat customers with different account_ids, but might be worth it depending on how likely customers will create accounts.

what if every user will be guest at start until the user access sign-in. on successful sign-in. Then use your logic of user and guest. This is also an option.


Conversion failed when converting date or time from character string error? i have a problem with this error i don't know why but i try many thing and search about it bout i didn't find any thing to solve this problem ( i want to make a query to calculate the age for the client auto in a column i have the Civil number for him like (286063001795) and i have some wrong civil No in my data ) the query is working good with little of data when i use it with my table gave me that error

UPDATE [dbo].[tabl]
set dob = CONVERT(date, convert(varchar(10), 17000000 + (CIVILIDD / 100000)))

select *, datediff(Month, dob, getdate())/12 as age, (CIVILIDD / 100000),17000000 + (CIVILIDD / 100000)
from [tabl]

  Name_Arabic   nvarchar(255)   Checked
  CIVILIDD    bigint  Checked
  gender  nvarchar(255)   Checked
  NATIONALITY nvarchar(255)   Checked
  dob date    Checked

set dob = TRY_CONVERT(date, convert(varchar(10), 17000000 + (CIVILIDD / 100000)))


'Incorrect datetime value: for column 'last_login' at row 1' i am getting below error:

Last_SQL_Errno: 1292

    Last_SQL_Error: Error 'Incorrect datetime value: '2013-03-10 02:00:00' for column 'last_login' at row 1' on query. Default database: 'platform'. Query: 'INSERT INTO app_player (creation_date, last_login, removed_date, app_id, user_id) VALUES ('2013-03-10 02:00:00', '2013-03-10 02:00:00', null, 400, 77870956)'
1 row in set (0.00 sec)

Table structure:

mysql> show create table app_player\G
*************************** 1. row ***************************
       Table: app_player
Create Table: CREATE TABLE `app_player` (
  `app_id` int(10) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `creation_date` datetime NOT NULL,
  `removed_date` datetime DEFAULT NULL,
  `last_login` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`app_id`,`user_id`),
  KEY `idx_creation_date_app_id` (`creation_date`,`app_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql veriosn is : 5.1.52

mysql> show global variables like '%date%';
+-----------------------------------------+-------------------+
| Variable_name                           | Value             |
+-----------------------------------------+-------------------+
| binlog_direct_non_transactional_updates | OFF               |
| date_format                             | %Y-%m-%d          |
| datetime_format                         | %Y-%m-%d %H:%i:%s |
| innodb_stats_auto_update                | 1                 |
| innodb_stats_update_need_lock           | 1                 |
| log_slave_updates                       | OFF               |
| low_priority_updates                    | OFF               |
| query_cache_wlock_invalidate            | OFF               |
| sql_log_update                          | ON                |
| sql_low_priority_updates                | OFF               |
| sql_safe_updates                        | OFF               |
+-----------------------------------------+-------------------+

Description:
The sql_mode changes behaviour from 5.6 to 5.7.
I notice a create table statement that replicates fine on a 5.6 master breaks on the 5.7 downstream slave.

Last_SQL_Error: Error 'Invalid default value for 'last_change'' on query. Default database: 'db1'. Query: 'CREATE TABLE mytable (
id int(10) unsigned NOT NULL,
field2 tinyint(3) unsigned NOT NULL,
last_change timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
....

sql_mode on the 5.6 master is: sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sql_mode on the 5.7 slave is: sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
(both the config in /etc/my.cnf)

Given the sql_mode change the value and this is being replicated the 5.7 stricter behaviour is being applied on the 5.7 slave by the SQL thread when in fact the 5.6 behaviour should be applied.

How to repeat:
see above.

Suggested fix:
Make the SQL thread aware of the master version being 5.6 and treat the sql_mode as if it were running on a 5.6 server to maintain the same behaviour.

https://bugs.mysql.com/bug.php?id=75439


Why can't I use null values in joins? I have solved the query problem by using ...row_number() over (partition by... this is a more general question on why we cannot use columns with null values in joins. Why can't a null be equal to a null for the sake of a join?

Why can't a null be equal to a null for the sake of a join?

Just tell Oracle to do that:

select *
from one t1
  join two t2 on coalesce(t1.id, -1) = coalesce(t2.id, -1);

(Note that in standard SQL you could use t1.id is not distinct from t2.id to get a null-safe equality operator, but Oracle does not support that)

But this will only work if the replacement value (-1 in the example above) does not actually appear in the table. Finding such a "magic" value for numbers might be possible, but it will be very difficult for character values (especially because Oracle treats an empty string as null as well)

Plus: no index on the id columns will be used (you could define a function based index with the coalesce() expression though).

Another option that works for all types, without magic values:

              on t1.id = t2.id or (t1.id is null and t2.id is null)

But the real question is: does this make sense?

Consider the following sample data:

Table one

id
----
1
2
(null)
(null)

Table two

id
----
1
2
(null)
(null)
(null)

Which of combination of null values should be chosen in the join? My above example will result in something like a cross join for all null values.

T1_ID  | T2_ID
-------+-------
     1 |      1
     2 |      2
(null) | (null)
(null) | (null)
(null) | (null)
(null) | (null)
(null) | (null)
(null) | (null)

A null value in most relational databases is considered UNKNOWN.  It is not to be confused with all HEX zeros.  if something contains null (unknown,) you cannot compare it.

Unknown = Known False
Unknown = Unknown False
Unknown >= Known False
Known >= Unknown False

Which means, anytime you have a null as an operand in a boolean expression, the else part will always be true.

Contrary to the general hatred towards null by the developers, null has its place.  If something is unknown, use null.


Dump Oracle database into a textual SQL script I am thinking on what mysqldump or pg_dump are doing. So, they dump an active database into a textual file containing the SQL queries, which reproduce this whole database. In MySQL and in PostgreSQL, this is the normal database dump format.

Does it exist on Oracle? How can it be done?

It is absolutely not a problem if it is not a standard Oracle thing. I need a solution to the problem. Exporting single tables is not enough, I am asking for the dump of a whole DB.

It is a reduced developer environment of a big project (with multi-million row DB), but still having hundreds of tables and a very complex structure. And, I want to play with it fast. While I am playing with it, I also need to be able to execute tricky modifications on that. On a binary dump I can't do that, on a textual I can.

Does it exist on Oracle?

Similar utility exists in Oracle environment called Data Pump.

  How can it be done?

Use expdp command line tool to perform full database export.

Example:

expdp hr DIRECTORY=dpump_dir2 DUMPFILE=expfull.dmp FULL=YES NOLOGFILE=YES

Then can use impdp to import it in another database.

Example:

impdp hr DUMPFILE=dpump_dir1:expfull.dmp FULL=YES LOGFILE=dpump_dir2:full_imp.log

If you specify DUMPFILE parameter it writes a binary file called dump file. With the SQLFILE parameter(SQLFILE=/my/file/name.sql) you can get all of the SQL DDL that Import would have executed.

More on it depends upon the requirements.

Oracle Documentation explains more than I can here.

Overview of Oracle Data Pump

In some case tools like Flyway or Liquebase can be helpful for you, i think. Seems they can make dump in SQL, XML and maybe something else. This tools aren't very easy to use, but looks much more flexible then SQL Developer. For example if you need to filter data before uploading.


Table with image data base64 per row causes deadlocks/block We have a table,

CREATE TABLE [dbo].[MyTable](
    [MasterKey] [uniqueidentifier] NOT NULL,
    [DetailKey] [varchar](100) NULL,
    [JSON] [nvarchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [ix_MyTable_details] ON [dbo].[MyTable]
(
    [MasterKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

And we have below query which is causing too many deadlocks/blocks.

IF EXISTS(SELECT 1 from  [MyTable](nolock) where  [MasterKey]= @MasterKey AND [DetailKey] =  @DetailKey)
BEGIN

 UPDATE [MyTable]
           SET [JSON] = @JSON
    WHERE  [MasterKey]= @MasterKey AND [DetailKey] =  @DetailKey
END
ELSE
BEGIN
INSERT INTO [MyTable]
           ([MasterKey]
           ,[DetailKey]
           ,[JSON])
     VALUES
           (@MasterKey
           ,@DetailKey
           ,@JSON)
END

Note different user uses MasterKey key at the same time but in peek time we see application becomes unresponsive. When we see the logs we found a lot of block/deadlocks. We also see Sch-S locks in this table.

Will adding MasterKey and DetailKey as primary key can fix this issue or what can we do?

Note sometimes JSON size is huge because it includes images as base64.

In general, you should always change this approach:

IF EXISTS ( /* scan the table with a where clause */ )
    UPDATE ( /* scan the table AGAIN with a where clause */ )
ELSE
    INSERT

To this:

UPDATE ( /* scan the table JUST ONCE with a where clause */ )
IF @@ROWCOUNT = 0
  INSERT

Forget you ever learned the first approach. It's like going to the grocery store to check if they have any eggs, then going back home to get your wallet, and back to the grocery store again to buy your eggs.

Also, yes, a clustered index can help in many scenarios. Is MasterKey really a key (e.g. is it unique)? Or is the combination of MasterKey + DetailKey the candidate key? In the latter case, an index (clustered or not) with both columns as key columns will help reduce any residual searching the query has to do to locate a specific row after it finds the rows with that MasterKey value (which prolongs blocking and can lead to deadlocks depending on other factors).

Actual use case. First, change the indexes:

DROP INDEX [ix_MyTable_details] ON [dbo].[MyTable];

CREATE UNIQUE CLUSTERED INDEX cix_MyTable_details
  ON dbo.MyTable(MasterKey, DetailKey);

Then change the query to:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

UPDATE dbo.[MyTable]
  SET [JSON] = @JSON
  WHERE MasterKey = @MasterKey AND DetailKey = @DetailKey;

IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.[MyTable]
       ([MasterKey]
       ,[DetailKey]
       ,[JSON])
  VALUES
       (@MasterKey
       ,@DetailKey
       ,@JSON);
END

COMMIT TRANSACTION;

Forcing JSON into a TEXT or BLOB column can often cause blocking, especially the bigger images simply due to them being large objects and all the issues associated with that.  The most common are concurrency, run away disk space, and transaction log growth.

Depending on your isolation level on that table, it has to wait for it to be written before it can be read.  If you can pull up the prior written value while the row is updated you can use RCSI or a different isolation level which will help with reads.  Otherwise break up the app a bit so you can do more concurrency.  This can be done by moving the offending data to a different schema, going with a NoSQL engine for your JSON and img files, using FILESTREAM for img files (Though that has some other considerations), and anything else to isolate this.

I've had developers create zip files as a binary file in a blob column to massive redundant JSONs.  They caused blocking, and in several cases isolation levels had to be looked at to fix it.

I really like Aaaron's answer and he's gone through the details, from my experience we had to handle very large JSON or BLOBs like img or zip files stored in a column through isolation levels for numerous reasons, including the keys cant be changed to the sizes were still too large when optimized.


What exactly is overlapping candidate key? Can some one please explain to me in simple terms what is overlapping candidate key ? What is overlapping as the name suggests?

Consider the below relation

R(L,M,N,O,P)
{
    M -> O
    NO -> P
    P -> L
    L -> MN
}

Which of the above functional dependency is bringing in overlapping candidate key in the above relation ?

Lets restrict the discussion to dependencies, I'm not interested in BCNF at the moment

You're right on the money with the possible candidate keys, vikkyhacks. Overlapping candidate keys are composite (consist of more than one attribute) candidate keys with at least one attribute in common. So your overlapping candidate keys are NM and NO (they share N).
Additional explanation of the above, originally left in comments:
All overlapping candidate keys are groups of (e.g. two or more) candidate keys. That means the first criterion is that your relation R must have more than one candidate key (minimal super keys). For any of the candidate keys to be overlapping, each of them (again two or more) must meet a few additional conditions. 1) They both must be composite candidate keys. They must consist of more than one attribute, so a key like A will never be overlapping, but AB might overlap with another key. 2) The composite keys must share an attribute. AB overlaps with AC and BD but not CD or EF.
To summarize: two or more sets of attributes where 1) each set is a candidate key (minimal superkey) for the relation, 2) each set is a a composite key (consists of more than one attribute), and 3) one or more of the attributes of the composite keys overlaps with an attribute of another key in the set. So you can rule out MNOP and NOPL on the basis that they are not minimal superkeys. You can rule out P and L on the basis that they are not composite keys (they consist of one attribute). You're left with two keys, NO and NM, which share the attribute N, so you are finished.
Example
It may also help to have an example you can really wrap your head around. The only time I've ever seen where you will have overlapping candidate keys is when you have 1) two attributes that functionally determine one another (e.g. a one-to-one relationship between A and B where A has one B and B has one A) and 2) these attributes are part of composite candidate keys.
For example, in some system, a Customer has one CreditCard, and a CreditCard belongs to one Customer. In the Rentals table, you uniquely identify a Rental by the EquipmentId, Date and CustomerId. For convenience, you've stored CreditCard on this table as well.
This means that the following FDs hold:
{CustomerId, EquipmentId, Date} -> {CreditCard}
{CustomerId} -> {CreditCard}

But since the association is one-to-one, the following FDs also hold:
{CreditCard} -> {CustomerId}
{CreditCard, EquipmentId, Date} -> {CustomerId}

Since CustomerId and CreditCard can be used interchangeably to uniquely identify your customer.
In the scenario above, you have overlapping candidate keys:
{CreditCard, EquipmentId, Date}
{CustomerId, EquipmentId, Date}

They are overlapping because they are composite keys (they consist of more than one attribute) and because at least one of their attributes is shared (in this case, they share both EquipmentId and Date.
You said you don't care about BCNF at the moment, but to fully bring this home, the scenario above is the reason why you'll occasionally see a table that is in 3NF but not BCNF. The table above is in 3NF, but not BCNF.
3NF allows FDs where 1) the FD is trivial 2) the left side of the FD is a candidate key or 3) the right side of the FD is a key attribute (an attribute used to make any key). Since CreditCard and CustomerId are both key attributes, all FDs either meet 2 or 3.
BCNF is very similar, but it only allows conditions 1 and 2 allowed by 3NF. Since the 3rd condition is not allowed by BCNF, and both CID -> CC and CC -> CID use condition 3, this table is not BCNF, but it is 3NF.
For practical purposes, the case is fairly rare and this information is pedantic. The giveaway that your table has a problem will be the fact that CreditCard/CustomerId pairs are repeated across your table. You also may recognize that the table would not even be in 2NF without this rare condition where the right side of an FD may be a key attribute because CreditCard is a partial dependency on the primary key (it depends on CustomerId but not EquipmentId or Date.

To check if a relation is having an overlapping Candidate key dependency:

Check if there is any dependency where a Complete Candidate key is determining a Part of Candidate key.
Then the OCK dependency holds


Set retention for SQL backup to URL I am using Ola Hallengrens DatabaseBackup script. I am storing my backups in Azure Blob storage using the @url parameter. Everything is working fine, however I am looking for a way to set a retention policy on those backups - currently I assume they live there forever until I manually delete them. I tried using the @CleanupTime parameter but got the following error

The value for the parameter @CleanupTime is not supported

Is there any way I am able to set this?

Edit: SQL being run for the backup

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBATools -Q \
"EXECUTE [dbo].[DatabaseBackup] \
@Databases = 'USER_DATABASES', \
@BackupType = 'LOG', \
@Verify = 'Y',  \
@Compress = 'Y', \
@CheckSum = 'Y', \
@LogToTable = 'Y', \
@ChangeBackupType='Y', \
@url='https://example.blob.core.windows.net/backups', \
Credential='AzureBlobBackups'" -b

In order to clean up files in Azure blob storage requires PowerShell or going through some app like Azure Explorer. So it is likely that Ola's backup script does not support it since there is no native T-SQL command or procedure to access blob storage in that manner.

When you are deleting files you will need to account for any that may still have an active lease. BOL has code to handle dropping the active leases.

I would recommend using XP_Delete_File as your solution.

Here is a link to the scripting and use of it.
Link

Xp_delete_file takes five parameters:

1.File Type = 0 for backup files or 1 for report files.

2.Folder Path = The folder to delete files.  The path must end with a backslash "\".

3.File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.

4.Date = The cutoff date for what files need to be deleted.

5.Subfolder = 0 to ignore subfolders, 1 to delete files in subfolders.

Here is the sample script to loop through and delete backups.

DECLARE @name VARCHAR(50); -- Database name

DECLARE @path VARCHAR(256); -- Path for backup files

DECLARE @fileName VARCHAR(256); -- Filename for backup

DECLARE @fileDate VARCHAR(20); -- Used for file name

DECLARE @DeleteDate DATETIME = DATEADD(wk,-1,GETDATE()); -- Cutoff date

-- Path to backups.

SET @path = 'D:\Backup\';

-- Get date to include in file name.

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112);

-- Dynamically get each database on the server.

DECLARE db_cursor CURSOR FOR

SELECT name

FROM master.sys.databases

WHERE name NOT IN ('master','model','msdb','tempdb');

OPEN db_cursor;

FETCH NEXT FROM db_cursor INTO @name;

-- Loop through the list to backup each database.

WHILE @@FETCH_STATUS = 0

BEGIN

      -- Build the path and file name.

      SET @fileName = @path + @name + '_' + @fileDate + '.BAK';

      -- Backup the database.

      BACKUP DATABASE @name TO DISK = @fileName WITH INIT;

      -- Loop to the next database.

      FETCH NEXT FROM db_cursor INTO @name;

END

-- Purge old backup files from disk.

EXEC master.sys.xp_delete_file 0,@path,'BAK',@DeleteDate,0;

-- Clean up.

CLOSE db_cursor;

DEALLOCATE db_cursor;

GO


Update existing rows with squence number/char or any unique data I added a new column in the Table X

This column "cn" has to be unique and mandatory, but old data don't have any value.

How to update the existing records with sequecely or random unique data?

Thank you.

If you are happy with a number starting from 1 you can use row_number().

update T
set cn = rn
from (
       select cn,
              row_number() over(order by (select 1)) as rn
       from TableX
     ) T

And if all of this still won't work (maybe because it's plain old SQL-92), you can break this down into multiple steps, as suggested by Ziggy Crueltyfree Zeitgeister, here.

CREATE TABLE sorting (sid numeric(10,10), rn int);

INSERT INTO sorting (sid, rn)
SELECT SortID, RecordNumber FROM Beleg
WHERE Year ( Valuta ) = 2016
AND Ursprungskonto = 1210
ORDER BY SortID;

UPDATE Beleg SET SortID = (SELECT rn FROM sorting WHERE sid=Beleg.SortID)
WHERE Year ( Valuta ) = 2016
AND Ursprungskonto = 1210;

DROP TABLE sorting;


Why Does Oracle kill a session at the specific time? Oracle kills sessions remotely open at the specific time. It means if I open a session at local machine, it is ok. In my case, at every 58 minute.

For example, at 13:58, 14:58, 15:58 and so on.

It means even if I open a session at 13:57, it will be closed 1 minute after.

My environment is:

I tested with sqlplus and my python application.
DB is placed in Windows Server 2012.
Firewall is turned off.
Network is connected over local router.

I also checked the Oracle alert.log, but there is nothing special.

Any suggestions?

--------------------Edit--------------------

I get a trace logs after setting TRACE_LEVEL_SERVER on sqlnet.ora.

There seems an error but I don't know what it means.

I run two program, one is my app and the other is seqlplus.

They missed connection at the same time 17:36.

Below are the trace log from each.

[07-4월 -2016 17:36:48:829] ntt2err: soc 4160 error - operation=5, ntresnt[0]=530, ntresnt[1]=53, ntresnt[2]=0
[07-4월 -2016 17:36:48:829] nserror: nsres: id=0, op=68, ns=12570, ns2=12560; nt[0]=530, nt[1]=53, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
[07-4월 -2016 17:36:48:829] nioqrc: recv: packet reader error -> translated to IFCR_EOF
[07-4월 -2016 17:36:48:829] nstimarmed: no timer allocated
[07-4월 -2016 17:36:48:829] nsclose: closing transport
[07-4월 -2016 17:36:48:829] nsclose: global context check-out (from slot 0)
complete

.

[07-4월 -2016 17:36:48:829] ntt2err: soc 2216 error - operation=5, ntresnt[0]=530, ntresnt[1]=53, ntresnt[2]=0
[07-4월 -2016 17:36:48:829] nserror: nsres: id=0, op=68, ns=12570, ns2=12560; nt[0]=530, nt[1]=53, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
[07-4월 -2016 17:36:48:829] nioqrc: recv: packet reader error -> translated to IFCR_EOF
[07-4월 -2016 17:36:48:829] nstimarmed: no timer allocated
[07-4월 -2016 17:36:48:829] nsclose: closing transport
[07-4월 -2016 17:36:48:829] nsclose: global context check-out (from slot 0) complete

If the sessions can no longer be reached, this sounds a lot like Oracle Dead Connection Detection.

From that paper:


The SQL Server Network Interface library could not register the Service Principal Name (SPN) Windows return code: 0x21c7, state: 15. Failure to register a SPN
  might cause integrated authentication to use NTLM instead of Kerberos.
  This is an informational message. Further action is only required if
  Kerberos authentication is required by authentication policies and if
  the SPN has not been manually registered.

I am getting above error whenever I restart my SQL service. I changed it from current service account to local account and was able to connect to SSMS from local machine, but if I am using current service account then this error is coming and in SSMS the error is :

  The target principal name is incorrect. Cannot generate SSPI context.

The password is not changed for this service account.

You specifically get error "0x21c7, state: 15" when an attempt is made to register the SPN which already exists, but possibly under different account.

When you run SQL Service under local system account, an SPN is registered under the Computer Object in AD. You can confirm from below:

setspn -l ComputerName

In above results, If you see any SPNs for MSSQLSvc then they need to be dropped with below commands on AD (You need to have Domain Admin permissions for this) so that you can then register SPN under new Service account:

setspn -d MSSQLSvc/FQDN:XXXXXXXXXX ComputerName

Once the above is done, restart SQL Service under the Service Account adn it should successfully register the SPN under the new Service account and you will be able to confirm by running the below command:

setspn -l DomainName\LoginName

Reference: http://www.b-blog.info/en/changing-service-account-for-ms-sql-server-2012-and-the-following-issues.html

You could also use Microsoft Kerberos Configuration Manager for SQL Server to automatically detect and fix such SPN Issues:

https://blogs.msdn.microsoft.com/farukcelik/2013/05/21/new-tool-microsoft-kerberos-configuration-manager-for-sql-server-is-ready-to-resolve-your-kerberosconnectivity-issues/

I am able to solve the issue with this post.

https://cmatskas.com/fixing-error-cannot-generate-sspi-context-after-changing-sql-service-account/

The recommendation is to grant the domain account that the SQL Server service is using the necessary AD privileges to read and write Service Principal Names.


Is it possible to use SQLite as a client-server database? Are there any techniques or tools to work with SQLite on a medium size/traffic/concurrency DB environment?

SQLite is an embedded database and it is not intended to be used as a client/server DB. If you really want to, you can use SQLitening.

What SQLitening is

  SQLitening is a client/server implementation of the very popular SQLite database.

  SQLitening is a programmer's library in standard Win32 DLL form. It is installed as a standard Windows Service. In addition to client/server mode, the library allows the programmer to also access SQLite databases in local mode. In either mode (local or client/server), the database is extremely fast and robust.
   -- Source: http://www.planetsquires.com/sqlite_client_server.htm

You can use a service similar to dropbox. There self-hosted solutions. However, SQLite3 was not created for a client-server model. You'd be better of with other solutions which were developed ground-up by client-server model.


MySQL Replication: Seconds Behind Master keep growing We have set up a new slave server for our production database. Since than the Seconds_Behind_Master keep growing slowly.

This is the slave output:

           Slave_IO_State: Waiting for master to send event
              Master_Host: notimportant
              Master_User: repl
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.003790
      Read_Master_Log_Pos: 42585179
           Relay_Log_File: mysqld-relay-bin.002798
            Relay_Log_Pos: 32374374
    Relay_Master_Log_File: mysql-bin.003492
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB:
      Replicate_Ignore_DB:
       Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
               Last_Errno: 0
               Last_Error:
             Skip_Counter: 0
      Exec_Master_Log_Pos: 32374215
          Relay_Log_Space: 31350956440
          Until_Condition: None
           Until_Log_File:
            Until_Log_Pos: 0
       Master_SSL_Allowed: No
       Master_SSL_CA_File:
       Master_SSL_CA_Path:
          Master_SSL_Cert:
        Master_SSL_Cipher:
           Master_SSL_Key:
    Seconds_Behind_Master: 1448477
            Last_IO_Errno: 0
            Last_IO_Error:
           Last_SQL_Errno: 0
         Master_Server_Id: 6524
              Master_UUID:
         Master_Info_File: /var/lib/mysql/master.info
                SQL_Delay: 0
      SQL_Remaining_Delay: NULL
  Slave_SQL_Running_State: Reading event from the relay log
       Master_Retry_Count: 86400
              Master_Bind:
  Last_IO_Error_Timestamp:
 Last_SQL_Error_Timestamp:
           Master_SSL_Crl:
       Master_SSL_Crlpath:
       Retrieved_Gtid_Set:
        Executed_Gtid_Set:
            Auto_Position: 0

Show processlist on SLAVE:

|    1 | system user |           | NULL | Connect | 2143813 | Waiting for master to send event | NULL             |
|    2 | system user |           | NULL | Connect | 1448477 | Reading event from the relay log | NULL             |
| 1628 | root        | localhost | NULL | Query   |       0 | init                             | SHOW PROCESSLIST |

On MASTER side:

|  7947837 | repl       | host-vm:59420     | NULL          | Binlog Dump | 2143817 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |

On MASTER the show slave hosts:

+-----------+------+------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+------+------+-----------+
|      3410 |      | 3410 |      6524 |
| 347643210 |      | 3306 |      6524 |
+-----------+------+------+-----------+

Does anyone has an idea what's going on and how can I fix it?

The solution was to add the following to my.cnf:

innodb_flush_log_at_trx_commit = 2

Our server was slow in I/O and the row above reduced the disk usage.

It is not recommended for bank databases, but recommended to all others.

Phrased differently:  =1 flushes at then end of each transaction, thereby making it ACID-compliant.  =2 flushes to disk every second, thereby being much less I/O-intensive.


MyISAM table keeps crashing. What are my options? My MYSQL table is very large (250GB, around 200M rows). Recently I've been putting it through a lot -- parallel processes that do a large number of reads.

It has started locking and crashing with alarming regularity. About once per week. The recovery is obviously time consuming.

Does this seem consistent with overloading, or should I suspect another cause (e.g., hardware failure)?

Next, what are the best steps for resolving it? I think switching to InnoDB will probably take a very long time. I can dial down the processes accessing it, but I'm unclear how low to go to stop being "abusive". Any advice?

In most cases, crashing a MyISAM table is nothing more than throwing mud on the MyISAM file header. It keeps a running count of the number of times the file has been opened. It increments upon opening and decrements upon closing.
You may want to look at the value for open-files-limit. This is an option that you cannot dynamically set with mysqld running. It can be configured at startup. What is interesting is its explanation in the MySQL Documentation:

Changes the number of file descriptors available to mysqld. You should
try increasing the value of this option if mysqld gives you the error
Too many open files. mysqld uses the option value to reserve
descriptors with setrlimit(). If the requested number of file
descriptors cannot be allocated, mysqld writes a warning to the error
log.
mysqld may attempt to allocate more than the requested number of
descriptors (if they are available), using the values of
max_connections and table_open_cache to estimate whether more
descriptors will be needed.

By default, mysqld sets this values based on the OS and how many maximum open file handles mysqld believes the OS will give to it.
You can take a risk and raise that number in /etc/my.cnf and restart mysql. As mentioned in the MySQL Documentation, a warning will be posted.
Talk to your sysadmin to see if your OS can have its file handle limit raised.
Once you can get it raised, restart mysql and see if the open_files_limit was raised as well.

Have you considered sharding your database? It might help with the issue you are experiencing and increase look up time speeds.


Greyed out menu options, broken activity monitor I recently installed SP1 on my 2012 SQL Server. Ever since then SSMS has behaved a little weirdly when I connect from my pc rather than from the local server itself.

I no longer see the green "running" icons on the server and on the agent, and several menu options are greyed out (Start/Stop/Restart on server menu and agent menu).

Also, when I try to access the Activity Monitor, I get the following error:

Error Detail

I found the following article about the Activity Monitor. I followed the steps there (although I am a local admin on the sql server and should have had all of those permissions already) to no avail. The server is running Windows 2008 R2 Datacenter SP1. When I remote desktop to the server itself and use SSMS directly I have none of these issues, and when a domain admin connects to the server through SSMS remotely everything also works for him.

Any ideas?

The behavior you are seeing happens if you aren't recognized as having permissions to manage services on the remote server.  This MSDN article describes some possible solutions on how to grant users rights to manage services.

Does the service account for the agent have local administrator permissions on the server?  Have you stopped and started database services and the SQL Server agent service on the server since encountering this problem?

If you have entered the correct log in info, the issue is most likely linked to the fact that the sqlserver service has been disabled somehow.
You can check its status and re-enable it by going to services.msc.


SQL Server 2005 Cluster Service pack 4 upgrade failure at remote user account I built SQL Server 2005 ENT Cluster on Windows Server 2008 R2. I need to upgrade it to SQL Server 2005 SP4. However, I get this error at the Remote User Account screen

  Enter the remote user account name and password below.
  User name, Password and domain fields.
  Your account information could not be verified.
  Press Ok to return to Authentication mode screen to determine the reason for failure.
  For setup to verify your credentials the services must be startable, connectable, and you must be a SQL Server Administrator

I am using  an domain account that has access and local admin rights to both servers. The SQL Server and agent run as this domain account and sysadmin .

I got connected using the active node name and also using the virtual name and I get the same error. I also made sure no one is connected to the passive node.

This is my exact error message on the link. But none helped.
http://answers.flyppdevportal.com/categories/sqlserver/sqlsetupandupgrade.aspx?ID=89159030-1f3d-4f13-a693-8122b6bb6aae

The link below from discusses "To work around this problem, use the NetBIOS name of the domain instead of the FQDN" but I do not know where I would be using .
http://support.microsoft.com/default.aspx?kbid=932870

Thanks

I faced the same problem with installing sql server 2005 service pack 3 on windows 2008 R2 cluster. On node1, i brought the sql server group online and pause the Node2 in cluster Manager and then ran the set up on node1. Once setup completed, i resume the node2 and failed over the SQL Server group on node2. Now on Node2, i pause Node1 in cluster manager and run the setup on node2. Once setup was completed, i resume Node1 and rebooted both nodes. I verified the version by failing over the sql server on both nodes.


Matching two rows in a single table if two specific keys have different values I have a table called settings in that there are two columns and data like following.

settings           value
-------            ------
depots             12
projectid          156
client             icici bank
clientvisits       2
DBname             icicibank
progress           true
displayvisits      2
visitsskipped      0

Here , the values of rows clientvisits and displayvisits should be same always.
I want a query which display the only these two fields along with values if their values are different. That means if clientvisits=2 and displayvisits=4 then it should throw an error (I mean it should display the output along with values).

I tried  by using self join  like this  but it is not working.

select * from settings TS,settings TS1
where TS.settings=TS1.settings and CONVERT(integer,ts.value)=1

select * from settings TS,settings TS1
where TS.settings=TS1.settings and ts.setting like 'visitstodisplay'=1

Also, I tried to do some thing else but I didn't get.
Please let me know how to do it.

You can do a join on Value <> Value for the settings you are looking for.

select S1.Settings as Settings1,
       S1.Value as Value1,
       S2.Settings as Settings2,
       S2.Value as Value2
from Settings as S1
  inner join Settings as S2
    on S1.Value <> S2.Value and
       S1.Settings = 'clientvisits' and
       S2.Settings = 'displayvisits'

Result:

Settings1            Value1               Settings2            Value2
-------------------- -------------------- -------------------- --------------------
clientvisits         2                    displayvisits        4

select
    TS.clientvisits,
    TS.displayvisits
from settings TS,settings TS1
where
    TS.settings=TS1.settings
    and TS.clientvisits!=TS.displayvisits


Are individual queries faster than joins? Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient?

TL;DR: If my joined query takes longer than running individual queries, is this my fault or is this to be expected?

First of, I am not very database savvy, so it may be just me, but I have noticed that when I have to get information from multiple tables, it is "often" faster to get this information via multiple queries on individual tables (maybe containing a simple inner join) and patch the data together on the client side that to try to write a (complex) joined query where I can get all the data in one query.

I have tried to put one extremely simple example together:

SQL Fiddle

Schema Setup:

CREATE TABLE MASTER
( ID INT NOT NULL
, NAME VARCHAR2(42 CHAR) NOT NULL
, CONSTRAINT PK_MASTER PRIMARY KEY (ID)
);

CREATE TABLE DATA
( ID INT NOT NULL
, MASTER_ID INT NOT NULL
, VALUE NUMBER
, CONSTRAINT PK_DATA PRIMARY KEY (ID)
, CONSTRAINT FK_DATA_MASTER FOREIGN KEY (MASTER_ID) REFERENCES MASTER (ID)
);

INSERT INTO MASTER values (1, 'One');
INSERT INTO MASTER values (2, 'Two');
INSERT INTO MASTER values (3, 'Three');

CREATE SEQUENCE SEQ_DATA_ID;

INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.3);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.5);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.7);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 2, 2.3);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 3, 3.14);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 3, 3.7);

Query A:

select NAME from MASTER
where ID = 1

Results:

| NAME |
--------
|  One |

Query B:

select ID, VALUE from DATA
where MASTER_ID = 1

Results:

| ID | VALUE |
--------------
|  1 |   1.3 |
|  2 |   1.5 |
|  3 |   1.7 |

Query C:

select M.NAME, D.ID, D.VALUE
from MASTER M INNER JOIN DATA D ON M.ID=D.MASTER_ID
where M.ID = 1

Results:

| NAME | ID | VALUE |
---------------------
|  One |  1 |   1.3 |
|  One |  2 |   1.5 |
|  One |  3 |   1.7 |

Of course, I didn't measure any performance with these, but one may observe:

Query A+B returns the same amount of usable information as Query C.
A+B has to return 1+2x3==7 "Data Cells" to the client
C has to return 3x3==9 "Data Cells" to the client, because with the join I naturally include some redundancy in the result set.

Generalizing from this (as far fetched as it is):

A joined query always has to return more data than the individual queries that receive the same amount of information. Since the database has to cobble together the data, for large datasets one can assume that the database has to do more work on a single joined query than on the individual ones, since (at least) it has to return more data to the client.

Would it follow from this, that when I observe that splitting a client side query into multiple queries yield better performance, this is just the way to go, or would it rather mean that I messed up the joined query?

Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient?

In any performance scenario, you have to test and measure the solutions to see which is faster.

That said, it's almost always the case that a joined result set from a properly tuned database will be faster and scale better than returning the source rows to the client and then joining them there. In particular, if the input sets are large and the result set is small -- think about the following query in the context of both strategies: join together two tables that are 5 GB each, with a result set of 100 rows. That's an extreme, but you see my point.

  I have noticed that when I have to get information from multiple tables, it is "often" faster to get this information via multiple queries on individual tables (maybe containing a simple inner join) and patch the data together on the client side that to try to write a (complex) joined query where I can get all the data in one query.

It's highly likely that the database schema or indexes could be improved to better serve the queries you're throwing at it.

  A joined query always has to return more data than the individual queries that receive the same amount of information.

Usually this is not the case. Most of the time even if the input sets are large, the result set will be much smaller than the sum of the inputs.

Depending on the application, very large query result sets being returned to the client are an immediate red flag: what is the client doing with such a large set of data that can't be done closer to the database? Displaying 1,000,000 rows to a user is highly suspect to say the least. Network bandwidth is also a finite resource.

  Since the database has to cobble together the data, for large datasets one can assume that the database has to do more work on a single joined query than on the individual ones, since (at least) it has to return more data to the client.

Not necessarily. If the data is indexed correctly, the join operation is more likely to be done more efficiently at the database without needing to scan a large quantity of data. Moreover, relational database engines are specially optimized at a low level for joining; client stacks are not.

  Would it follow from this, that when I observe that splitting a client side query into multiple queries yield better performance, this is just the way to go, or would it rather mean that I messed up the joined query?

Since you said you're inexperienced when it comes to databases, I would suggest learning more about database design and performance tuning. I'm pretty sure that's where the problem lies here. Inefficiently-written SQL queries are possible, too, but with a simple schema that's less likely to be a problem.

Now, that's not to say there aren't other ways to improve performance. There are scenarios where you might choose to scan a medium-to-large set of data and return it to the client if the intention is to use some sort of caching mechanism. Caching can be great, but it introduces complexity in your design. Caching may not even be appropriate for your application.

One thing that hasn't been mentioned anywhere is maintaining consistency in the data that's returned from the database. If separate queries are used, it's more likely (due to many factors) to have inconsistent data returned, unless a form of snapshot isolation is used for every set of queries.

Multiple queries IS the way to go. If you handle simple scenarios like that - the cost overhead of the query optimizer is a factor. With more data, the network inefficiency of the join (redundant rows) comes in. Only with a lot more data is there efficiency.

At the end, what you experience is something many developers see.  The DBAs always say "no, make a join" but reality is: it IS faster to make multiple simple selects in this case.


How do I tactfully bring up database shortcomings? A little background:

I recently inherited responsibility for support and development of a database application after the original developers left. Looking through the application, the database appears to have several... issues.

Weekly shrink and re-index plan
Database account used for web application access has db_owner role
No foreign keys
All tables use GUID column as primary key
Queries built in C# are not parameterized

How can I bring these issues up with management?

Normally, this should be no trouble, but I have a problem: I have less than six months of SQL experience, whereas the managers and former developers were working on this project for several years.

Here's the line I like to use - it preserves everyone's dignity and lets everyone escape without finger-pointing:

"Yes, that used to be a best practice, and..."

It’s almost easier to explain that line in terms of what it DOESN’T do.  It doesn’t dispute the speaker’s claim, because you don’t want to go down the rathole of arguing about whether or not the point was right at the time.  You could spend the entire day arguing about why USB has the word Universal as part of the name, but that’s beside the point.  You need to get to the right solution as fast as possible, and sometimes that just means ignoring something that’s wrong.

It doesn’t chastise the other guy for not keeping up with the latest tips and tricks.  It’s hard enough to just do our jobs, let alone keep up with blogs and training, and real life interferes.  It lets the other person save face.

It doesn’t divide you against the other guy.  I try to use the word “we” whenever I use this line, because I want to take the other guy along with me on the journey to fix this problem.

I've written more about it in my Consulting Lines series.

Be sure that your manager or team leader understands your point and is supporting you when you bring this up.


SQL Server - Database per company. How to query across databases? We are designing a product that, as per the client's request, must utilize a database-per-company design approach with one shared database holding common data.

We are using SQL Server 2012 for the job.

One of the features is a news feed with an ability to post messages, comments, likes etc.

The news feed also has the ability to post to "community."  These "community" posts should be visible to all the companies.  We will have to store those posts at the common level.

My question is - how would you go about designing this?

I was thinking of having duplicate tables in the common database and each of the company databases and just joining the tables for the company feed that appears in the UI.

This brings up another question; how do we manage user IDs for example?  Each post or comment is associated with a user ID.  Would we have to make user IDs unique for all the databases to do that?

Is there a better way to design this?

I don't know if this will help, and this maybe a little much for your situation, but here goes our solution in use today.

All users are grouped into logical entities under a domain (something.com) umbrella. Our particular scenario required an additional layer of Domain->Company->Group->User break out. Not sure if you ever worked with Active Directory or domain trees, but it follows that logic.

Using the domain model, the server itself is at the top, presumably the client of your services would be the administrator. Each company flows like a branch from the server itself. Then flows into user-defined groups which contain users. Each company in this scenario would have a Domain Admin which can administer the xyz.com, abc.com, etc domains, but can't access any other domain.

Each of the service containers (the databases) use a full trust model with the security database to provide services to the users contained in this security database (Think OpenID for perspective). We use a home brewed C++ compiled module for Apache 2.x to provide an application firewall and session security host.

Each run, the module "asks" the security database to record the page hit, produce a random (64 character string) cookie and a session cookie (two total cookies), and authenticate the session. A new session or the same session is returned based on if the cookies match. The session key and anything else relevant is provided to the end-user UI application code over HTTP headers (so the firewalls could sit in front of Google Apps or AppEngine).

Once the UI has the code, the UI can act on behalf of the user and the service database accepts this token and provides direct permissions to the user based on the token. Our application provides the username as well to match a unique user within the service database to provide extended permissions. Since we also use per-transaction random keys, the state is unable to be cached.

This model supports isolation (UI developers are unable to eavesdrop), allows a central security with a delegation aspect, and the ability to provide centralized services. Such as providing a central forum, trending (statistical compiled) data stored centrally (which maybe restricted to paying clients), or maybe even weblogs as we provide to each of our service databases to make decisions to gauge a fraudulent checkout request.

    SERVERS TABLE Minimum of id (unique key), domain, adminuser
                |
                | -------> OPTIONAL Access Control List
                |
    SERVER USER CONTROL LIST Minimum of id (unique key), serverid, actual url you wish to protect.
                OPTIONAL restrictions on AdminOnly, NoSearchEngines, Restricted (Authentication Required)
                |
                |
                | --------> OPTIONAL ENTITY (Sub-Company) Abstraction
                |
                | --------> OPTIONAL GROUPS
                |
                |
    USERS   Minimum of id (unique key), username, serverid (unless you use the entity container which are already tied to the server)
                |
                |
                |
    PERMISSIONS Minimum of id (unique key), server_ucl_id, groupid/entityid/userid (Depending on preference)

    SESSIONS    Minimum of id (unique key), serverid, userid if authenticated

    Provide the sessionid and either userid or username to tamper-resistant proxy code that will provide it to the end-user UI.

We have a lot of bells and whistles in our app to provide a more robust multi-tenant solution, but this is the basics of what we are doing.

The objective is to totally isolate each company into it's own container. Proxy code of some sort should help. C++ is not required, just need code to sit in front of the web application. Also if you want to provide security in one database and shared services in another database, your shared database, which presumably will also be under your client's control, would be allowed to query directly to the security database to see if the user is logged in based on the tokens provided to it, such as when the user posts.

There are no error messages to humans using this model. If anything, any errors would either be pushed via some type of messaging que, email, or log files. The authentication layer is between the proxy code and the security database. User management is within an application that you create for the end-user companies.

This model also scales well with SQL Azure in the middle if needed as it does not need anything like CLR, FT, or etc. The only true weak point will always be the proxy code for security enthusiast. Physical security, and limited user access should lock that down.

Hope I wasn't to verbose and that this helps!

There are different technologies/styles depending on which database you use (mssql/oracle/others...)

From microsoft you can link databases via "linked servers" ( http://msdn.microsoft.com/en-us/library/aa213778%28v=sql.80%29.aspx ).
Basicly you configure a OLE DB and the SQLserver accesses data via those ole db connections.

In oracle you can use db-Links working best between oracle databases ( http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_5005.htm ), but also capable of connecting other db systems as well.

Selecting data will then be something like:

SELECT * FROM table@DB-linkname

For mysql and most "no cost" systems there are only workarounds to achieve this.


Example of MS Access SQL IIF to T-SQL Given the syntax rules shown below

Access: IF(Condition, ReturnIfTrue, ReturnIfValue)
T-SQL: CASE WHEN Condition THEN ReturnIfTrue ELSE ReturnIfFalse END

What would the below be formatted to T-SQL be?

(((IIf(IsNull(Production!Product_Title) Or Production!Product_Title Like "Process*",
"Not Started", "Started"))="Not Started") And ((Person.Type) Is Not Null))

Seems you don't have to use IIF or CASE

(
    (
    Production.Product_Title IS NULL
    OR Production.Product_Title Like 'Process%'
    )
AND Person.Type IS NOT NULL
)

FYI, Transact-SQL has IIF function starting with 2012.

UPD

If you had to return some value:

SELECT
    CASE WHEN
        Production.Product_Title IS NULL
        OR Production.Product_Title Like 'Process%'
    THEN 'Not Started'
    ELSE 'Started'
    END AS [ProductState]
FROM YourTable


Simple indexed select query takes 350 seconds (?!) even though Filtered 100.00 "Using index condition" My logs show that the query being explained here took 352.19 seconds (and similar times for other similar queries where the contact_id and execute_at were different).

EXPLAIN SELECT
    *
FROM
    `automations`
WHERE
    (`contact_id` = 22638
        AND `job_class_name` = 'App\Jobs\MyJob'
        AND `execute_at` = '2018-12-15 16:43:00')
        AND `automations`.`deleted_at` IS NULL
LIMIT 1

Result of EXPLAIN:

| id | select_type | table       | partitions | type | possible_keys                  | key                            | key_len | ref                     | rows | filtered | Extra                 |
|----|-------------|-------------|------------|------|--------------------------------|--------------------------------|---------|-------------------------|------|----------|-----------------------|
| 1  | SIMPLE      | automations | NULL       | ref  | cId_job_executeAt_delAt_unique | cId_job_executeAt_delAt_unique | 780     | const,const,const,const | 1    | 100.00   | Using index condition |

I am inexperienced at optimizing MySql, but my guess would be that the Explain looks great, right?

Why on earth would queries like this be taking 350+ seconds? How can I diagnose and fix?

P.S. This is related to the E_WARNING: Error while sending STMT_PREPARE packet. PID=* error I frequently see.

CREATE TABLE `automations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `contact_id` int(10) unsigned NOT NULL,
  `job_class_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `execute_at` datetime NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `cId_job_executeAt_delAt_unique` (`contact_id`,`job_class_name`,`execute_at`,`deleted_at`),
  CONSTRAINT `automations_contact_id_foreign` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1519 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

The optimal index for this query would be a composite index with 4 columns, in any order:

INDEX(contact_id, job_class_name, execute_at, deleted_at)

And, depending on the order you pick, you can get rid of one of the existing KEYs.

Watch out for backslashes.  'App\Jobs\MyJob' may be treated as App[LF]obs[CR]yJob or AppJobsMyJob.

352 seconds is really unreasonable.  Perhaps something else was going on, and blocking access to the rows and/or the table? (The 352s was bogus.)

"Using index condition" is not the same as "Using index".  The latter indicates a "covering index", which is not practical for this query and table.

My queries that I thought took 352 seconds really only took 0.352 seconds! ♂

https://laravel.io/forum/03-04-2014-what-time-format-does-dbgetquerylog-return showed me that Laravel's DB::getQueryLog() shows "time" as milliseconds (microseconds multiplied by 1000), not seconds.

What an embarrassing mistake (poor assumption). So I need to edit my 500-point bounty question: https://stackoverflow.com/questions/53469793/e-warning-error-while-sending-stmt-prepare-packet-pid/54374937?noredirect=1#comment95579631_53469793


SQL SERVER 2008 R2 Identity Range The current table I am having issues with has an Publisher Range Size of 10,000, but when I look at the constraint I only see 1000 for the range size at the publisher. I have tried running sp_adjustpublisheridentityrange and changing the Publisher Range Size to 12000 to kick start it, but it still will not change.

I manually changed the identity fields with DBCC CHECKIDENT.  No automated way or configuration to fix it.


Recover data from bak Oracle 9i file Our customer gave us a .bak archive with name file_decrypted_379299.bak which we know was created with Oracle 9i in "hot" style.

We tried to recover the bak file with Oracle 9i RMAN but we don't know how to make reference to this file.

We have not found any specific reference, documentations explains we need specific parameters from how this bak file was created... but customers says "normal" parameters.

We just installed oracle 9i with default database configuration, we don't want database instead we only want SQL tables and data.

Do you know how to ask for these parameters?
How to specify in RMAN the location of this file?

Any tool to recover data without installind the database?

Thanks for your help

Are you sure this is an RMAN backup set? Maybe it is just a dump file made while the database is online. For 9i you only had exp and not the newer expdp available to make dumps so you could try to import it with imp and see what the output is:

imp system/password@abc FULL=Y FILE=file_decrypted_379299.bak


PostgreSQL: options for large table I'm working on a project dealing with genomic data in a Postgres database. There is one table in particular which is causing trouble. It is slowly approaching a billion rows and has three columns: snp_name varchar(32), genotype_id int, local_genotype char(2)

The first two are foreign keys and together form the primary key of the table.

Querying this data is still OK, response-time-wise. Inserting data takes ages, though. We are now at a point where we get new data faster than we can insert it, and we already COPY into a temporary table first and insert from there. Due to the server still running on spinning disks, inserting a genotype with a million rows takes about 5 hours.

Partitioning by genotype_id would be great, since this would result in ~ a thousand tables with a million rows each and a genotype is basically the package we get the data in. Then, querying by snp_name probably won't perform that well anymore, though. Whether we can live without it, has not yet been decided.

Do you have any suggestions for other options we could try? Are there any database systems, that allow indexing across partitions? Any NoSQL solutions that could fit?

EDIT: The table definition:

# \d user_snps
              Table "public.user_snps"
     Column     |         Type          | Modifiers
----------------+-----------------------+-----------
 snp_name       | character varying(32) |
 genotype_id    | integer               |
 local_genotype | character(2)          |
Indexes:
    "user_snps_new_genotype_id" btree (genotype_id)
    "user_snps_new_snp_name" btree (snp_name)

There is only one distinct value of genotype_id per insert, and all values of snp_name are unique per insert.

I'm thinking having two copies of that table, one partitioned by genotype_id, one partitioned by snp_name and adding a few constraints and triggers to keep it consistent might be a good enough solution.


MySql-Workbench: Can't connect to MySQL server on '127.0.0.1' (111) The hard data first:
Local machine: Xubuntu 17.10, with MySql-Workbench 6.3
Remote Machine: Ubuntu 16.04, with MySql-Client 5.7 and MySql-Server 5.7

The connection method is: Standard TCP/IP over SSH

The error:

  Your connection attempt failed for user 'root' from your host to
  server at 127.0.0.1:3306:   Can't connect to MySQL server on
  '127.0.0.1' (111)

Then it gives me a list with things to check.

  1 Check that mysql is running on server 127.0.0.1

check

  2 Check that mysql is running on port 3306

check

  3 Check the root has rights to connect to 127.0.0.1 from your address

I checked the mysql.user table and it says that root has the host: localhost and since MySql-Workbench does an ssh connect to the remote machine and then connects to the MySql-Server, localhost should be fine.
Nonetheless I tried setting the host to % as a wildcard but that didn't change anything either so I switched back to default localhost.
I also checked the my.cnf. That is the entire content of it:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

There is no bind-address or skip-networking directive set.

  4 Make sure you are both providing a password if needed and using the
  correct password for 127.0.0.1 connecting from the host address you're
  connecting from

Password and stuff is all fine. Passwords are set, they are typed correctly when logging in.

When I do, what Workbench is supposed to do

ssh to the remote machine (ssh user@remotemachine)
login to mysql server (mysql -u root -p)

It works flawlessly
I just can't do via MySql-Workbench

Help is much appreciated because I already tried everything I was able to research.

The error message says clearly the MySQL server could not be reached, hence check everything that could block access to it. This is not user related.

Do you have a running network adaptor on the machine you run Workbench on? Does it allow TCP/IP connections?
Can you reach the target machine your MySQL runs on?
Does MySQL run on that target machine?
Can you create an SSH tunnel to the target machine?
Can you reach the MySQL server when you are logged in on the target machine (e.g. by means of an SSH)?

If you can answer all that with yes there should be no problem connecting to the MySQL server from MySQL Workbench. Point 6 can be a bit more complicated, if your MySQL server is not on the same machine as where the SSH endpoint is or if it has networking disabled or if it uses a different port than what you tried.

However, the general idea should be clear now. Try to isolate the actual problem (is it network related or a config problem?). Maybe this video can also help you: https://www.youtube.com/watch?v=DCgRF4KOYIY&t=76s

I am facing the same issue. As weird as it can get I found that I can only connect to the server using the "Topbar -> Database -> Connect to Database..."

Give it a try. This definitely seems a Workbench's bug. My version is 6.3.10


Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server I am having a bit of a linked server problem.

I created a SQL Server linked server and it's been working fine for last 2 years and suddenly not working.

SQL Server

  Microsoft SQL Server 2005 - 9.00.5324.00 (X64)   Aug 24 2012 18:28:47
  Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition
  (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Oracle client version

  Oracle - OraClient11g_home1

Error 7302:

  Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server.

We have enabled Allow in Process in SQL Server for the Oracle Provider.

Has anyone got any ideas on this and what can be done to avoid this happening again?

linked server is Oracle, the first thing that I tried was to check if the Server hosting the SQL Server instance, was able to connect to the listener on the Oracle Database Server using tnsping.

tnsping searched to the path C:\product\11.1.0\db_1\bin initially, this will initially use the used parameter file
C:\product\11.1.0\db_1\network\admin\sqlnet.ora

tnsping successfully connected to the Oracle database from the SQL Server box. Then check the Provider details while creating the Linked Server.

The Linked Server provider was Oracle Provider for OLE DB. This is the driver that gets installed along with the Oracle Client Components.

Error 7302 can be a result of MSDAORA.dll not being registered correctly. In this case it had to be an issue with the dll related to Oracle Provider for OLE DB i.e.OraOleDB.dll. Before checking the dll related issues,to check if something was missing in the registry. The list of Providers in the Linked Server is populated from the Providers key in the registry.

Error 7303 means that the username and password combination is not correct, hence the login failed for the user specified in the Linked Server. When the correct login credentials for the Oracle database were entered, the Linked Server got created successfully!

I was having same error when i was connecting SQL Server with MySQL the issue was due to wrong Username or password in Local Server login to remote server login mapping.

Check if its correct or not !


Stolen Server Memory and Database Cache Memory I have the same behavior in 2 SQL Servers where Stolen Server Memory and Database Cache Memory counters are decreasing (Db Cache) and increasing (Stolen Memory) in a linear way. The amount of memory decreased from Db Cache Memory is almost the same amount of memory increased in Stolen Memory.
Has anyone seeing this behavior before? Is it an expected behavior?

Both server's configuration:

SQL Server 2012 ENTERPRISE with SP3
32GB memory

Thanks for the attention

"stolen" memory?  I like that.  Seriously though, I think the question may be clearer with OS vs SQL memory.  SQL will use the startup minimum set up in the config.  If you didn't set a maximum, then it will continue to steal memory as more work is demanded.

Thus the available total RAM for the OS decreases over time.  Simply set a maximum in the SQL properties, in your case 28GB, so it doesn't steal everything and starve the OS.

This is how SQL behaves normally so it's not that much of a thief.  Hope that helps.


How could you implement a custom sort that groups matching records from other source This is obviously simplified.  I have 2 applications:  Driver and Match.  I'm presenting data to users in this order and asking them to select which of the people in the Match application is truly a match for the person in the Driver application.
Data needs to be ordered as you see below:  Driver rows need to be in alpha order by PersonName.  Below each Driver record, list the Match records that have the same PersonMatchID, in PersonName alpha order.  Some Driver records will have no corresponding Match records.

If you started with the rows below in random order, how would you write a SQL query to return them in this order?  I am dealing with about 3000 rows at most, so I don't have to worry overmuch about optimization.

Source-system    PersonName    PersonPossibleMatchID
-------------    ----------    ---------------------
Driver           AA            77777
  Match          AA            77777
Driver           AB            11111
  Match          AA            11111
Driver           BB            33333
  Match          BA            33333
  Match          BB            33333
Driver           CC            99999
  Match          CB            99999
  Match          CC            99999
  Match          CD            99999
Driver           DD            44444
Driver           EE            22222

I've tried

  SELECT * ORDER BY LEFT(PersonName,1), PersonPossibleMatchId,
  Source-system

but this doesn't return Driver records in strictly alpha order - you get all the A's first, but within that the order on second letter is randomized.  For example, in the above list, I might have Driver AB listed prior to Driver AA.

Added:  I'm using MS SQL (2012)

Here's another couple of possibilities

SELECT [Source-System],
       PersonName,
       PersonPossibleMatchID
FROM   #src
ORDER  BY MAX(CASE
                WHEN [Source-system] = 'Driver'
                  THEN PersonName
              END) OVER (PARTITION BY PersonPossibleMatchId) ASC,
          [Source-System] DESC,
          PersonName ASC

(online demo)

- Or -

WITH DriverPersonNames
     AS (SELECT PersonPossibleMatchID,
                PersonName
         FROM   #src
         WHERE  [Source-System] = 'Driver')
SELECT s.*
FROM   #src s
       JOIN DriverPersonNames d
         ON d.PersonPossibleMatchID = s.PersonPossibleMatchID
ORDER  BY d.PersonName ASC,
          [Source-System] DESC,
          s.PersonName ASC

(online demo)

Both will return the same results assuming that there is exactly one Driver record per PersonPossibleMatchID and PersonPossibleMatchID is not nullable.

(DDL for online demos borrowed from jyao's answer)

Here is a solution (tested on SQL Server 2012)

-- create test table
if object_id('tempdb..#src', 'U') is not null
    drop table #src;
create table #src ([Source-System] varchar(10), PersonName char(2), PersonPossibleMatchID int)
go

-- poupulate tables with random data sequence
insert into #src ([Source-System], PersonName, PersonPossibleMatchID)
values
 ('Driver','EE',  22222)
,('  Match','CC', 99999)
,('  Match','CB', 99999)
,('Driver','AA',  77777)
,('  Match','AA', 11111)
,('Driver','BB',  33333)
,('  Match','BA', 33333)
,('  Match','AA', 77777)
,('Driver','AB',  11111)
,('  Match','BB', 33333)
,('Driver','CC',  99999)
,('  Match','CD', 99999)
,('Driver','DD',  44444)
go

-- check here
select * from #src
go

-- solution here
; with c as
(select top 3000  PersonName, PersonPossibleMatchID
 from #src
 where [Source-System]='Driver' order by PersonName)
select A.[Source-System], A.PersonName, A.PersonPossibleMatchID  from c
cross apply ( select top 3000 * from #src s
              where s.[Source-System]='Driver' and s.PersonName = c.PersonName and s.PersonPossibleMatchID = c.PersonPossibleMatchID
              order by [Source-System] asc,  personname asc
              union all
              select top 3000 * from #src s
              where s.[Source-System]<> 'Driver' and  s.PersonPossibleMatchID = c.PersonPossibleMatchID
              order by [Source-System] asc, personname asc) A([Source-System], PersonName, PersonPossibleMatchID )

Here is the result


Out-of-sync Replication in Sql Server Okay, a little complex:

One local DB table is being replicated to another local DB with same table. Worked for years. User account on replication job is gone, so job was failing. I updated it to a different account, and every 10 minutes, it had no errors then. However, it didn't replicate a new row.
I manually created the new record in the subscriber DB table.
Restarting the server ended up working, but now the replication fails because it has the queue of new records it's trying to add and the identity column value already exists in the destination table.

Options: Should I...

Delete the records I added so that the replication can copy down the new records?

Problem: Not sure if the used IDs will be able to be used with the new records coming over in the replication (because of the auto-numbering identity column), and we need those IDs to remain matched.

Somehow clean out the queue so that those records aren't attempted any more (since I already created them) -- not sure how to do this.

Yes, I searched a lot on this stuff, but I haven't seen this problem described in the same way.

Thanks for any help or answer!

You do not need to drop the subscription.

You're getting primary key violations as the replication agent is trying to insert duplicates.

  Delete the records I added so that the replication can copy down the new records?

Yes, you will need to manually remove the records in order to alleviate the primary key violations.

You need to re-create your publication
Drop the subscription
Sync data from old subscriber database to the publisher - use something like Redgates Data Compare, it will generate the change scripts for you - OR write you own sync script
re-create the subscription


Get a number nth bit value in oracle How can I get nth bit value in oracle database?
I am currently using this query:

WITH a AS
     (SELECT 7 val
        FROM DUAL)
SELECT CASE BITAND (a.val, POWER (2, n-1))
          WHEN 0
             THEN 0
          ELSE 1
       END nth_bit
  FROM a

The shortest expression you can get is:

least(bitand(a, power(2, n-1)), 1)

where you want to find the nth bit of a.

The shortest query with this expression is:

select least(bitand(a, power(2, n-1)), 1) from dual;

From what I've read the above expression also works in PL/SQL, eg:

l_bit_n := least(bitand(a, power(2, n-1)), 1);

with a
as
(select 8 as value,4 as bit_number  from dual)
SELECT CASE WHEN BITAND (a.value, POWER (2, a.bit-1))=POWER (2, a.bit -1)
            THEN 1
          ELSE 0
       END nth_bit
FROM a


Building A SQL table horizontally VS vertically What is a better design principle when designing SQL Server tables.

One can either add columns (ie grow horizontally) or grow it vertically (ie rather adding rows)

I have a situation when there are normally a number of items, say 2- 12 that are generally related to one another.
Just to keep it simple, they would have the same values in 4 of their columns but 3 would be different.

Would it be better to now add the 12 entries in their own row, or would it be better to say add additional columns that would help me combine all the entries into one row, but be able to identify which one was which.

I dont think I have explained the situation well enough to get a specific answer relating to my situation.

But I am asking in general  which is better practice?

The extreme example of the vertical design pattern is called entity-attribute-value. Generally speaking, you don't want to use the EAV design pattern with SQL Server due to challenges with indexing and constant joins.

To find the right middle ground, review Joe Celko's post on normalization in SQL Server Central's Stairways series. Note the sections on 3rd normal form.

The correct way doesn't exist... In dinamic table in SQL DB in my work I have seen equally horizontally and vertically.
The road to follow is common sense applied to the specific context, generally the management software using horizontal tables because easier to manage dynamically at runtime, software for "mission-critical" or "special purpose" use vertical tables, the most elegant and efficient in performance but also more complex searches, and data reconstruction ...