MySQL Error Reading Communication Packets In MySQL error logs, I see these quite few a warnings like these:

120611 16:12:30 [Warning] Aborted connection 2619503 to db: 'db_name' user: 'user_name' host: 'webapp_hostname' (Got an error reading communication packets)

Haven't noticed any loss of data per se, so I am wondering what this warning means, or what causes it, and if how one might address the issue causing these. This is on RHEL 6.1 and MySQL Enterprise 5.5.

One of the silent killers of MySQL Connections is the MySQL Packet.

First, let's figure out what a MySQL Packet is.

According to the page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining MySQL Packets:

  MySQL network communication code was
  written under the assumption that
  queries are always reasonably short,
  and therefore can be sent to and
  processed by the server in one chunk,
  which is called a packet in MySQL
  terminology. The server allocates the
  memory for a temporary buffer to store
  the packet, and it requests enough to
  fit it entirely. This architecture
  requires a precaution to avoid having
  the server run out of memory---a cap
  on the size of the packet, which this
  option accomplishes.

  The code of interest in relation to
  this option is found in
  sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay
  particular attention to
  net_realloc().

  This variable also limits the length
  of a result of many string functons.
  See sql/field.cc and
  sql/intem_strfunc.cc for details.

Knowing this about MySQL Packets allows a Developer/DBA to size them up to accommodate multiple BLOBs inside one packet even if they are obnoxiously large. Definitely, a packet too small will cause problems for open connections in this respect.

According to the MySQL Documentation

You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server's max_allowed_packet variable, which has a default value of 1MB. You may also need to increase the maximum packet size on the client end. More information on setting the packet size is given in Section C.5.2.10, “Packet too large”.
An INSERT or REPLACE statement that inserts a great many rows can also cause these sorts of errors. Either one of these statements sends a single request to the server irrespective of the number of rows to be inserted; thus, you can often avoid the error by reducing the number of rows sent per INSERT or REPLACE.

RECOMMENDATION

Try raising the max_allowed_packet to a much larger number, since the default is 1M. I would suggest about 10 times the largest TEXT or BLOB field you have in your current dataset.

To set the max_allowed_packet to 256M, you can add it to /etc/my.cnf or my.ini

[mysqld]
max_allowed_packet=256M

to cover future restarts of mysqld. To install the value now on the server, please run this:

SET GLOBAL max_allowed_packet = 1024 * 1024 * 256;

Give it a Try !!!

This my.ini line temporary solved my problem:
log_error_verbosity=1

Reference this link


Choice- or ids-list- field representation I wonder what is the best way to represent a user setting that could either take a value from a set (such as 'High', 'Medium' or 'Low') or be a list of IDs from another (or the same!) table (Many to Many relation).

I would like to know what is commonly used and what are the drawbacks/assets of each solution (in terms of performances, integrity and sustainability).

To some extent any solution is going to depend to a great deal on exactly what you are doing and what RDBMS you are using.  In general though using a text field to store a comma separated list of integers is a bad idea.

The starting point solution is always id-based reference.  This is a good place to start because it obeys proper relational rules, and it allows you to handle things like multiple values by breaking these off into new relations.  In fact I would say it is always best to start here.

Alternatives however, work well in some cases and with some RDBMS's.  ENUM fields (or alternatively text domains with a check to keep them within some values) are helpful if you never need to drop options.  If you do, then you have to determine what you want to do about this before you drop them.  Secondly array types can be useful in certain cases too (and they don't necessarily break 1NF if the array as a whole is an atomic value of a domain, for example if we represent an IPv4 address as an array of 4 8-bit integers). Array-based approaches depend highly on what you are doing and db-level support, but on PostgreSQL I would choose an array where cardinality matters (i.e. an array is a tuple instead of a set), and where the semantics are entirely self-contained.

The "XOR-paired columns" solution

This solution consists in two columns that work together:

A nullable character varying "ENUM" column that can value "High", "Medium" or "Low".
A virtual column that would actually be represented by a third table that makes the relation between the user table and the foreign table. Just as would do a classic Many-to-Many relation.

If the former is worth "High", "Medium" or "Low", the application would just take this value and the latter would just be ignored. On the other hand, if it's NULL, the M2M relation would be used.


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

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

Natively:

Default trace

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

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

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

fn_dblog function ==> [undocumented]

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

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

Third Party tools - the best one out is ApexSQL Log

As a side note:

  I want to preserve changes made by other users

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

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

No. You dont even know which changes were his unless you recorded it wih triggers etc. - for sql that would be useless information, outside what the logging is supposed to handle.


Have DB users share tables I want to have 2 users of the DB (user_app and user_migration), one for executing migrations (which will have more privileges, like creating/dropping tables + basic usages) and one that the overlaying application will use to interact with the DB (which will have select, insert, update, delete privileges only).

The problem right now, that I'm facing, is that when I create a table from user_migration, the other user doesn't have the permissions to access that table.

This is the SQL code I've got for creating the users:

-- create the role
DO
$body$
BEGIN
  IF NOT EXISTS(
      SELECT *
      FROM pg_catalog.pg_roles
      WHERE rolname = 'app_users')
  THEN

    CREATE ROLE "app_users";
  END IF;
END
$body$;

GRANT CONNECT ON DATABASE :dbname TO "app_users";
GRANT USAGE ON SCHEMA "public" TO "app_users";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "app_users";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO "app_users";

-- create the users and grant them role privileges
DO
$body$
BEGIN
  IF NOT EXISTS(
      SELECT *
      FROM pg_catalog.pg_user
      WHERE usename = 'user_app')
  THEN

    CREATE USER "user_app" WITH PASSWORD 'somepswd';
    GRANT app_users TO user_app;
  END IF;

  IF NOT EXISTS(
      SELECT *
      FROM pg_catalog.pg_user
      WHERE usename = 'user_migration')
  THEN

    CREATE USER "user_migration" WITH PASSWORD 'somepswd';
    GRANT app_users TO user_migration;
  END IF;
END
$body$;

-- here, assign additional privileges to user_migration to create/delete entities

How can I set up the roles/users in order to achieve the described scenario above?

I suspect that you didn't run this script as user_migration, but as a different user.

Then the ALTER DEFAULT PRIVILEGES statements won't have the desired effect.

You should write:

ALTER DEFAULT PRIVILEGES FOR ROLE user_migration IN SCHEMA public
   GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "app_users";
ALTER DEFAULT PRIVILEGES FOR ROLE user_migration IN SCHEMA public
   GRANT USAGE, SELECT ON SEQUENCES TO "app_users";

Without the FOR ROLE user_migration, it will only affect tables created by the user that executed the statement.

You need to grant the second user access to the first user's tables, or grant the relevant access to PUBLIC. Two users can't see each other's objects by default, it must be expressly permitted.

Read this:

https://www.postgresql.org/docs/9.0/static/sql-grant.html


Select from other table if exist hi I want to union 2 tables but I am not sure if the table is exits
this is my query:

select  a from table
union
select  case when exists(select a from table1) then (select a from  table1)
else (select a from table2) end;

got this

  ERROR: ERROR 4840:  Subquery used as an expression returned more than one row

I need to use limit 1 in a subquery but it's not the result that I wanted.

do you have other suggestions?
thanks

table, for example, I just created them in my local env for testing:

table 1

CREATE TABLE public.table1
(
    a int,
    b varchar(80),
    c int
);

CREATE TABLE public.table
    a int,
    b varchar(80),
    c int
);

In principle this won't work, as you can't check if table exists and then join it in a query.
The error is unrelated. You are selecting a value in a subselect and it returns multiple rows, as the table exists.
What you can do is:

Create temp table
Check if table1 exists using V_Catalog.table and if specific column existence is important, v_catalog.column
If it exists, select data from table1 into temp table
Use the temp table in the union instead of table1

I have not knowledge working with Vertica but you can try using If exists like in SQL Server

If exist (Select 1 FROM sys.objects Where type = 'U' and name in ('YourTableName1', 'YourTableName2'))
Begin
 Select * From Public.Table1
 Union
 Select * From Public.Table2
End


Memory Optimized Tables - can they really be so difficult to maintain? I'm investigating the benefits of upgrading from MS SQL 2012 to 2014.  One of the big selling points of SQL 2014 is the memory optimized tables, which apparently make queries super-fast.

I've found that there are a few limitations on memory optimized tables, such as:

No (max) sized fields
Maximum ~1KB per row
No timestamp fields
No computed columns
No UNIQUE constraints

These all qualify as nuisances, but if I really want to work around them in order to gain the performance benefits, I can make a plan.

The real kicker is the fact that you can't run an ALTER TABLE statement, and you have to go through this rigmarole every time you so much as add a field to the INCLUDE list of an index.  Moreover, it appears that you have to shut users out of the system in order to make any schema changes to MO tables on the live DB.

I find this totally outrageous, to the extent that I actually cannot believe that Microsoft could have invested so much development capital into this feature, and left it so impractical to maintain.  This leads me to the conclusion that I must have gotten the wrong end of the stick; I must have misunderstood something about memory-optimized tables that has led me to believe that it is far more difficult to maintain them than it actually is.

So, what have I misunderstood?  Have you used MO tables?  Is there some kind of secret switch or process that makes them practical to use and maintain?

No, in-memory really is this unpolished.  If you are familiar with Agile you will know the concept of "minimal shippable product"; in-memory is that.  I get the feeling that MS needed a response to SAP's Hana and its ilk.  This is what they could get debugged in the timeframe for a 2014 release.

As with anything else in-memory has costs and benefits associated with it.  The major benefit is the throughput that can be achieved.  One of the costs is the overhead for change management, as you mentioned.  This doesn't make it a useless product, in my opinion, it just reduces the number of cases where it will provide net benefit.  Just as columnstore indexes are now updatable and indexes can be filtered I have no doubt that the functionality of in-memory will improve over coming releases.

SQL Server 2016 is now generally available. Just as I supposed, In-Memory OLTP has received a number of enhancements. Most of the changes implement functionality that traditional tables have enjoyed for some time. My guess is that future features will be released at the same time for both in-memory and traditional tables. Temporal tables is a case-in-point. New in this version it is supported by both In-Memory and disk-based tables.

you can use In-Memory OLTP in Operational Servers without any major problem. we used this technology in a Banking and Payment company,

In general we can use memory-optimized tables when workload is too high.
by using In-Memory OLTP you can reach better performance to 30X !
Microsoft correct most of this limitations In SQL Server 2016 & 2017 .
memory-optimized tables has a completely different architecture compared with Disk-Based tables.

memory-optimized tables are two types. durable tables and nondurable tables.
Durable and Non-Durable Tables maintain table data reside in memory. Furthemore durable tables persists data on Disks for Recovery Data and Schema.
most of operational scenario we should use durable tables because data lost is critical here. in some scenario for example ETL loading and Caching we can use nondurable tables.

you can use this ebooks and learn how to use this technology:

Kalen Delaney:
https://www.red-gate.com/library/sql-server-internals-in-memory-oltp

Dmitri Korotkevitch:
https://www.apress.com/gp/book/9781484227718


Pros and Cons of SQL Server back up strategies and their appropriate usage scenarios From what I can tell, there are three possible ways of backing up your SQL Server database

Full backup
Differential backup
Log Shipping

What are the pros and cons of each strategy and in what situations should they be employed?

Log shipping is not a backup scenario. It's a semi high availabiliy scenario.
For backups there's full, differential and transaction log backups.
They should all be used together. Your SLA dictates how you use them.
Most typical scenarios are full backup say at midnight, diff backup at noon and transaction log backups every 30 or 15 minutes.

And remember: you don't have a valid backup until you restore from it to test if it's ok.

The best way is to use all three backups types altogether. Of course, you can ignore differential backup of transaction log backup. Everything depends on your database, how fast it grows, how frequently you make changes in your database and other. Before to choose your backup plan, consider how much data are you willing to loose? How much time are you ready to spend to recover your database?

For example, If your database growth fast you can use the following SQL Server backup strategy: full backup - once a day, differential backups - every two hours and transaction log backups -  every 20 minutes. In this case, if the failure occurs, you will lose not more than 19 minutes of your work. Another example, if your database growth slowly you can perform a full backup once a day, differential backup every six hours, and every hour make transaction log backup.

One more tip - to be sure that your database in safe, from time to time restore your backups on a test server.


How to turn JSON array into Postgres array? I have a column data of type json that holds JSON documents like this:

{
    "name": "foo",
    "tags": ["foo", "bar"]
}

I would like to turn the nested tags array into a concatenated string ('foo, bar'). That would be easily possible with the array_to_string() function in theory. However, this function does not accept json input. So I wonder how to turn this JSON array into a Postgres array (type text[])?

Postgres 9.4 or newer

Obviously inspired by this post, Postgres 9.4 added the missing function(s):
Thanks to Laurence Rowe for the patch and Andrew Dunstan for committing!

json_array_elements_text(json)
jsonb_array_elements_text(jsonb)

To unnest the JSON array. Then use array_agg() or an ARRAY constructor to build a Postgres array from it. Or string_agg() to build a text string.

Aggregate unnested elements per row in a LATERAL or correlated subquery. Then original order is preserved and we don't need ORDER BY, GROUP BY or even a unique key in the outer query. See:

How to apply ORDER BY and LIMIT in combination with an aggregate function?

Replace 'json' with 'jsonb' for jsonb in all following SQL code.

SELECT t.tbl_id, d.list
FROM   tbl t
CROSS  JOIN LATERAL (
   SELECT string_agg(d.elem::text, ', ') AS list
   FROM   json_array_elements_text(t.data->'tags') AS d(elem)
   ) d;

Short syntax:

SELECT t.tbl_id, d.list
FROM   tbl t, LATERAL (
   SELECT string_agg(value::text, ', ') AS list
   FROM   json_array_elements_text(t.data->'tags')  -- col name default: "value"
   ) d;

Related:

What is the difference between LATERAL and a subquery in PostgreSQL?

ARRAY constructor in correlated subquery:

SELECT tbl_id, ARRAY(SELECT json_array_elements_text(t.data->'tags')) AS txt_arr
FROM   tbl t;

Related:

How to apply ORDER BY and LIMIT in combination with an aggregate function?

Subtle difference: null elements are preserved in actual arrays. This is not possible in the above queries producing a text string, which cannot contain null values. The true representation is an array.

Function wrapper

For repeated use, to make this even simpler, encapsulate the logic in a function:

CREATE OR REPLACE FUNCTION json_arr2text_arr(_js json)
  RETURNS text[] LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT ARRAY(SELECT json_array_elements_text(_js))';

Make it an SQL function, so it can be inlined in bigger queries.
Make it IMMUTABLE (because it is) to avoid repeated evaluation in bigger queries and allow it in index expressions.
Make it PARALLEL SAFE (in Postgres 9.6 or later!) to not stand in the way of parallelism. See:

When to mark functions as PARALLEL RESTRICTED vs PARALLEL SAFE?

Call:

SELECT tbl_id, json_arr2text_arr(data->'tags')
FROM   tbl;

db<>fiddle here

Postgres 9.3 or older

Use the function json_array_elements(). But we get double quoted strings from it.

Alternative query with aggregation in the outer query. CROSS JOIN removes rows with missing or empty arrays. May also be useful for processing elements. We need a unique key to aggregate:

SELECT t.tbl_id, string_agg(d.elem::text, ', ') AS list
FROM   tbl t
CROSS  JOIN LATERAL json_array_elements(t.data->'tags') AS d(elem)
GROUP  BY t.tbl_id;

ARRAY constructor, still with quoted strings:

SELECT tbl_id, ARRAY(SELECT json_array_elements(t.data->'tags')) AS quoted_txt_arr
FROM   tbl t;

Note that null is converted to the text value "null", unlike above. Incorrect, strictly speaking, and potentially ambiguous.

Poor man's unquoting with trim():

SELECT t.tbl_id, string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.data->'tags') d(elem)
GROUP  BY 1;

Retrieve a single row from tbl:

SELECT string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.data->'tags') d(elem)
WHERE  t.tbl_id = 1;

Strings form correlated subquery:

SELECT tbl_id, (SELECT string_agg(trim(value::text, '"'), ', ')
                FROM   json_array_elements(t.data->'tags')) AS list
FROM   tbl t;

ARRAY constructor:

SELECT tbl_id, ARRAY(SELECT trim(value::text, '"')
                     FROM   json_array_elements(t.data->'tags')) AS txt_arr
FROM   tbl t;

Original (outdated) SQL Fiddle.
db<>fiddle here.

Related:

Need to select a JSON array element dynamically from a postgresql table

Notes (outdated since pg 9.4)

We would need a json_array_elements_text(json), the twin of json_array_elements(json) to return proper text values from a JSON array. But that seems to be missing from the provided arsenal of JSON functions. Or some other function to extract a text value from a scalar JSON value. I seem to be missing that one, too.
So I improvised with trim(), but that will fail for non-trivial cases ...

These few functions, taken from answers to this question, are what I'm using and they're working great

CREATE OR REPLACE FUNCTION json_array_casttext(json) RETURNS text[] AS $f$
    SELECT array_agg(x) || ARRAY[]::text[] FROM json_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION jsonb_array_casttext(jsonb) RETURNS text[] AS $f$
    SELECT array_agg(x) || ARRAY[]::text[] FROM jsonb_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION json_array_castint(json) RETURNS int[] AS $f$
    SELECT array_agg(x)::int[] || ARRAY[]::int[] FROM json_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION jsonb_array_castint(jsonb) RETURNS int[] AS $f$
    SELECT array_agg(x)::int[] || ARRAY[]::int[] FROM jsonb_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

In each of them, by concatenating with an empty array, they handle a case that had me racking my brain for a bit, in that if you try and cast an empty array from json/jsonb without it you'll get nothing returned, instead of an empty array ({}) as you would expect. I'm certain there's some optimization for them, but they're left as is for simplicity in explaining the concept.


Secure MySQL-Server completely (disable ability to reset root-password) is there a way to secure a mysql-server so no one can reset a password via --init-file-startup?

there should only be a:

"root" user (password not known to anyone else than person a)
"system" user (no password, full access to one database restricted to
localhost-connection)
"reader" user (password, read only access to the "one" database and
no host-restrction)

and absolutely no way to bypass this setting.

i did not find a way till now - but maybe there is a hidden one.

thank you!

As indicated there is no option.

The person that has the ability to trigger the mysqld with --init-file is either root or the mysql user who can overwrite all of the mysqldatadir including all permission tables of the mysql. That is why there isn't even a considered option to achieve this.

you could use mysql_config_editor, this command creates a hidden file with all your connection parameters placed in this file and you dont need anymore put this parameters in console client


Count rows where value differs I have the following dataset in SQL Server:

Name     Pattern
-------  -------
Peter    DRF
Peter    DRF
Peter    BBB
Peter    DRF
Peter    CCC
Peter    CCC
Sue      DRF
Sue      CCC
Sue      DRF
Sue      VVV
Sue      SSS

I need to count the number of times a pattern changes per Name (not only distinct changes). So my end result will be:

Peter 3
Sue 4

In other words, only counting the distinct changes. Peter changed from DRF to BBB to CCC and Sue changed from DRF to CCC to DRF to VVV to SSS.

Is this possible without using a cursor?

I have a start date and end date per row, but no unique id. Patterns gets logged by the system per person per day and sometimes multiple times per day per person.

Try this:

Declare @T table (name varchar(10), Pattern varchar(10))
insert into @t (name, Pattern) values
('Peter', 'DRF'),
('Peter', 'DRF'),
('Peter', 'BBB'),
('Peter', 'DRF'),
('Peter', 'CCC'),
('Peter', 'CCC'),
('Sue', 'DRF'),
('Sue', 'CCC'),
('Sue', 'DRF'),
('Sue', 'VVV'),
('Sue', 'SSS')

SELECT NAME
    ,count(DISTINCT pattern) AS DistinctChanges
FROM @t
GROUP BY NAME

| name  | DistinctChanges |
|-------|----------------|
| Peter | 3              |
| Sue   | 4              |

Note: There is a difference between distinct values and the number of times a value changed assuming some kind of timeline.

For example:
AAA, BBB, AAA, BBB, AAA, BBB, AAA would be 2 distinct changes, but 5 total times the value changed assuming some sort of order.

Which variation are you looking for?

First you need row_number/serial number.

;With CTE as
(
select *
,row_number()over(order by name)rn
 from @t
)

select t.name
,sum(case when t1.rn=t.rn +1 and t.pattern<>t1.pattern then 1 else 0 end)Result
from cte t
cross apply(select t1.rn,t1.pattern from cte t1
where t.name=t1.name
)t1
group by t.name

if output is ok across all data then it will perform ok.


Why does SQL Server return some rows while still executing the query, and sometimes not? There are queries where when we hit "execute", it shows some rows and it keeps growing, but the query is not over yet. Yet sometimes, it waits until the end of the query.

Why does this happen? Is there a way to control this?

The answer, as usual (alright, most of the time), lies in the execution plan.

There are certain operators that require all rows to arrive at them before they can start processing those rows, and passing them downstream, for example:

Hash Join (on building the hash table)
Hash Match
Sort (Except Hash Flow Distinct)

They're either called blocking, or stop and go operators because of this, and they're often chosen when the optimizer thinks it'll have to process a whole lot of data to find your data.

There are other operators that are able to begin streaming, or passing any found rows along immediately

Nested Loops
Index supported Merge Joins
Stream Aggregates

When queries start returning data immediately, but don't finish immediately, it's usually a sign that the the optimizer chose a plan to locate and return some rows quickly using operators that have a lower start up cost.

This can happen because of row goals introduced either by you, or by the optimizer.

It can also happen if a bad plan is chosen for some reason (lack of SARGability, parameter sniffing, insufficient statistics, etc.), but that takes more digging to figure out.

For more information, check out Rob Farley's blog here

And Paul White's series on row goals here, here, here, and here.

It should also be noted that, if you're talking about SSMS, rows only appear once an entire buffer has been filled, not just willy-nilly.

To add to sp_BlitzErik's answer, take the example using a NOT IN () with a sub select.  In order to determine whether an item is in the result of the nested query, it's (generally) necessary to retrieve the entire result.

So one way easy way I've found to improve performance of such queries is to rewrite them as an LEFT OUTER JOIN with where condition for RIGHT side is null (you could flip it around, of course but who uses RIGHT OUTER JOINS?).  This allows for the results to start to return right away.


Failed to drop column - value too large I'm trying to remove column from DB and got a strange error:

  Error starting at line 147 in command:
      alter table config drop column dte
      Error report:
      SQL Error: ORA-12899: value too large for column "OWNER"."CONFIG"."DTE" (actual: 3, maximum: 1)
      12899. 00000 -  "value too large for column %s (actual: %s, maximum: %s)"

  *Cause: An attempt was made to insert or update a column with a value
                 which is too wide for the width of the destination column.
                 The name of the column is given, along with the actual width
                 of the value, and the maximum allowed width of the column.
                 Note that widths are reported in characters if character length
                 semantics are in effect for the column, otherwise widths are
                 reported in bytes.

  *Action:   Examine the SQL statement for correctness.  Check source
                 and destination column data types.
                 Either make the destination column wider, or use a subset
                 of the source column (i.e. use substring).

I don't have triggers on this table. Could anyone advice on it?
Thanks!

UPD: we were able to delete it using
alter table ... set unused ...
and
alter table drop unused columns
after it. But still have no idea why simple drop doesn't work.

You can do it by increasing column size, then drop .
(Because drop column statement try to validate the data, then drop )
like ..

SQL> ALTER TABLE OWNER.CONFIG
     MODIFY(DTE VARCHAR2(500 BYTE));

SQL> ALTER TABLE OWNER.CONFIG DROP COLUMN DTE ;

example:-

SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> ALTER TABLE ORCL.FORM DROP COLUMN CURRENT_STEP;
ALTER TABLE ORCL.FORM DROP COLUMN CURRENT_STEP
*

ERROR at line 1:
ORA-12899: value too large for column "ORCL"."FORM"."CURRENT_STEP" (actual:
6, maximum: 2)

SQL> ALTER TABLE ORCL.FORM
  2  MODIFY(CURRENT_STEP VARCHAR2(500 BYTE));

Table altered.

SQL> ALTER TABLE ORCL.FORM DROP COLUMN CURRENT_STEP;

Table altered.


how to write conditional where clause query Hi I want write the mysql query conditionally. I am basically using the Wordpress, please have a look at my same tables.

tbl1

id      name

1   A
2   B
3   C
4   D
5   E

tbl2

id    post_id   key             value

1   1   m_type          1
2   1   m_date          2013-11-22
3   2   m_type          2
4   2   m_date          2013-11-20
5   2   m_routine       2
6   3   m_type          2
7   3   m_date          2013-11-10
8   3   m_routine       null
9   3   m_custom_rountine   5

I have the maintenance date in tbl2. So I want to get the all the post id filtered by following types.

Maintenance

Due today
Week due.
Year due.
over due.

tbl2 has the due date m_date. But My problem when m_type = 1. I need to add the where clause like WHERE CURDATE() = m_date else m_type =2 means m_date will be the starting date of maintenance and I need to increment the date using m_routine or m_custom_routine.

For example,
m_type = 2
m_date = 2013-11-22
m_routine = 2

Then I want check

CURDATE() = 2013-11-22 OR 2013-11-24 OR 2013-11-26 etc.

I was thinking to use CASE .. WHEN... THEN in WHERE clause. But I am not getting it properly.

Desired output

CURDATE() = 2013-11-22

Output post_id = 1,2 (2-routine incremented date from 2013-11-20)

CURDATE() = 2013-11-15

Output post_id = 3  (custom_routine incremented date from 2013-11-10)
Hope I explained well enough. Please someone help me.

SUDO code

if(m_type == 1){
    m_date = CURDATE();
}
else if(m_type == 2){
    increment_date = m_date increment m_rountine (OR) m_date increment m_custom_rountine
    CURDATE() = increment_date
}

Edit:-

I created the sql fiddle for your ref

It can be done using some multiple joins from table1 into table2 and then use those joins  combined with case/calculation logic, or using sub-selects.
But it seems rather complex to want to do that in a single query/single join - that I'd recommend finding an alternative way to do it. It's something which is a good candidate to solve in application logic.

However, I think the route you want to go is something like this:

SELECT
  T1.*,
  CASE
    WHEN T2.value = '1' THEN 'CalculateStuffBasedOnT3OrT4'
    WHEN T2.value = '2' THEN
        CASE
          WHEN t3.value IS NULL THEN 'CalculateOtherStuffBasedOnT3OrT4'
          WHEN T3.value = '2' THEN 'CalculateMoreStuffBasedOnT3OrT4'
          ELSE 'SomethingElse'
        END
     ELSE 'Something'
    END
FROM tbl1 T1
LEFT JOIN tbl2 T2 ON T1.id = T2.id AND T2.key = 'm_type'
LEFT JOIN tbl2 T3 ON T1.id = T3.id AND T3.key = 'm_routine'
LEFT JOIN tbl2 T4 ON T1.id = T4.id AND T4.key = 'm_date'

Such a query can then be used as foundation in a subquery to select the posts you want.

But this will quickly become quite complex and unmaintainable.
So I'd advice you to rethink the strategy and go with designing the query dynamically in you php code, or rethink the data structure, or utilize multiple queries to solve it.

Please refer below links, you will get idea.

http://use-the-index-luke.com/sql/where-clause/obfuscation/smart-logic

https://stackoverflow.com/questions/12264342/conditional-where-clause-any-way-to-write-except-dynamic-query

https://stackoverflow.com/questions/11338775/conditionally-use-where-clause-in-sql

http://www.codeproject.com/Questions/489046/Howplustoplusapplyplusconditionalplusstatementplus


Deterministic function for getting today's date I am trying to create an indexed view using the following code (so that I can publish it to replication it as a table):

CREATE VIEW lc.vw_dates
WITH SCHEMABINDING
AS

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), number) AS SettingDate
FROM lc.numbers
WHERE number<8

GO

CREATE UNIQUE CLUSTERED INDEX
idx_LCDates ON lc.vw_dates(SettingDate)

lc.numbers is simply a table with 1 column (number) which is incremented by row 1-100.

However, I keep getting the error:

  Column 'SettingDate' in view 'lc.vw_dates' cannot be used in an index or statistics or as a partition key because it is non-deterministic.

I realize that GETDATE() is non-deterministic. But, is there a way to make this work without drawing a calendar table?

You cannot use the function GETDATE() as the basis for a persisted view since the output of the function changes moment-by-moment.  This what SQL Server means in the error "because it is non-deterministic."  Function results MUST be predictably the same every time they are called in order for SQL Server to persist the results.

Luckily, there is an easy way for you to persist this data once-per-day using a scheduled job.  Perhaps using SQL Server Agent, or Windows Job Scheduler, etc.

Here I'm creating the numbers table, and the table to hold the next 7 days:

CREATE TABLE dbo.Numbers
(
    Number INT NOT NULL
        CONSTRAINT PK_Numbers
        PRIMARY KEY CLUSTERED
);

;WITH cte AS
(
    SELECT TOP(100) Number = ROW_NUMBER() OVER (ORDER BY t1.num, t2.num)
    FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) t1(num)
        , (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) t2(num)
    ORDER BY t1.num * t2.num
)
INSERT INTO dbo.Numbers (Number)
SELECT cte.Number
FROM cte;

CREATE TABLE dbo.Next7Days
(
    SettingDate DATETIME NOT NULL
        CONSTRAINT PK_Next8Days
        PRIMARY KEY CLUSTERED
);

Schedule this to happen once per day:

TRUNCATE TABLE dbo.Next7Days;

INSERT INTO dbo.Next7Days(SettingDate)
SELECT SettingDate = DATEADD(DAY, n.Number, DATEDIFF(DAY, 0, GETDATE()))
FROM dbo.Numbers n
WHERE n.Number < 8;

Since the dates of the next 7 days only changes once per day, this solution should work well.

The dbo.Next7Days table contains the following after I ran the above code:

This is hacky, but you can add a non-deterministic column to the table, rather than calling the non-deterministic function i.e. RAND(), GETDATE() from inside of the UDF. First in the column definition give it a default value, i.e. DEFAULT(GETDATE()).  Then use  this new column to feed the udf to compute the calculated column.

IF OBJECT_ID('dbo.sometest','U') is not null drop table dbo.sometest
GO
IF OBJECT_ID (N'dbo.udf_Next_Run', N'FN') IS NOT NULL  DROP FUNCTION dbo.udf_Next_Run
GO

CREATE FUNCTION dbo.[udf_Next_Run]
(
    @seconds int,
    @seed DATETIME --new column
)
RETURNS varchar(20)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @NextRun varchar(20);

    SET @NextRun =  ( Select TOP 1 TRY_CONVERT(datetime, DATEADD(SECOND, @seconds, @seed) , 112) ) ;

    RETURN @NextRun;

END

GO

CREATE TABLE dbo.sometest ( id int identity(1,1), numb_sec int, SEED DATETIME DEFAULT( GETDATE() ) , Next_Run_Date AS dbo.udf_Next_Run(numb_sec, SEED) PERSISTED )
GO
--external Python function reading a cron expression and getting # of seconds till next run

INSERT INTO dbo.sometest(numb_sec)VALUES(2020),(180)
select * from sometest


Cannot Copy Database I am working with SQL server 2012. I want to copy a database along with all the data. I just want to create a dev DB.

I know SQL Server 2012 has a Copy Database... wizard. But when I run that wizard, I get an error on the last step:

On error, it tells me that error log in inside a text file on the shown path. When I go to that path, there is no such file there.

I tried backup and then restore with a different name as well. But it gave similar results. I got error while restoring. But I couldn't find the error log file.

Can anyone please help me with this?

These are the steps of the SQL Agent Job:  detach the database, copy MDF and LDF files to the destination, then attach both source and destination to their respective instances.

It is possible that there was something preventing the detach.
You cannot detach a database that is replicated/published.
A database snapshot exists on the database.
It is being mirrored.
It is suspect.
It is a system database.

There could have been an issue with permissions copying the MDF and LDF files (not your login, but the SQLAgent service account login).

There could have been an issue with the attach.
A database created by a more recent version of SQL Server cannot be attached in earlier versions.

For the non-existent log file:  Ensure write permissions for the account that runs SQLAgent service on the folder where the log of the error would be written.

I had to do it the other way. I created an empty DB and used import wizard to import all the data and the schemas. This worked without any error. But I still don't know why the Copy Wizard didn't work.


Slow proc Used to take a few seconds now takes well over a minute I'm at a new place and working on several issues. One item that was passed to me was this proc that used to take a few seconds is now taking well over a minute or timing out.

The data hasn't changed too drastically.

The filesReceived table has 902,596 records.
The fileuploads table has 629,862 records.

We run SQL2016.
Can you see if there is anything I can do to help speed this back up?

The plan:
https://www.brentozar.com/pastetheplan/?id=BJc4DsywZ

Easy answer for this. Updating statistics takes care of this slowness.


ORA-00338: log # of thread # is more recent than control file We are facing the following error on our production database:

  ORA-00338: log # of thread # is more recent than control file

The database crashed and there was no recovery action. What could I do to resolve the problem?

It could be that you are opening using the wrong control file[s]. This is easily caused when using for example a parameter file in ASM - the correct one - and one in the default location $ORACLE_HOME/dbs/ that still has the old references in place after a migration to ASM. In that case search in ASM to the current control files.

In such a setup, normally the parameter is found by using srvctl config database -d YOUR_DB

During every opening attempt the log# is increased .... so after many attempts, the error can go away and the problem can still exist ....

Error Cause:

The control file change sequence number in the log file is greater than the number in the control file. This implies that the wrong control file is being used. Note that repeatedly causing this error can make it stop happening without correcting the real problem. Every attempt to open the database will advance the control file change sequence number until it is great enough.

ACTION:
Use the current control file or do backup control file recovery to make the control file current. Be sure to follow all restrictions on doing a backup control file recovery.


MySQL migration physical server VMware virtual server How to migrate physical MySQL server to VMware virtual server with all db and user privileges

You would almost certainly be better off doing a fresh install, taking a backup using xtrabackup, and restoring the config files and the backup into the install inside the VM. That way you could even do it with only seconds of downtime by using replication up to the moment you switch over.

You test everything local with an installation of vmware.
Install the vm including the mysql server. then stop the server in the vm and the original.
And simply copy the data files.
You need all files including ini ...
change the ini/cnf file to the new location and start the vm mysql again or the local first.
Check the error log of the new server if something is a wrong.


Select any variant of

    INSERT INTO c (c, m)
    SELECT c.id, m.id
    FROM c, m
    WHERE c.type = 1
      AND m.type = 1

    INSERT INTO c (c, m)
    SELECT c.id, m.id
    FROM c
    JOIN m ON m.type = 1
    WHERE c.type = 1

    INSERT INTO c (c, m)
    SELECT c.id, m.id
    FROM c
    JOIN m ON c.type = 1
    WHERE m.type = 1

    INSERT INTO c (c, m)
    SELECT c.id, m.id
    FROM c
    JOIN m ON m.type = 1
          AND c.type = 1

    INSERT INTO c (c, m)
    SELECT c.id, m.id
    FROM c
    JOIN m /* ON 1 = 1 */
    WHERE c.type = 1
      AND m.type = 1

I am not sure if this also works in PostgreSQL (since you added the tag) but the following is the MySQL way of doing it.

This looks to me like a merge operation on the two tables after replacing a string. Since it involves removing the prefixes "C" and "M" we can use the SQL REPLACE([Column], [Needle], [Replacement]) method.

SELECT
    REPLACE(`C`.`Name`, 'C', '') AS C
FROM C
WHERE `C`.`Type` = 1

However, there is no merge operation in SQL, so we cannot use that approach. This would leave us to resort to using JOIN. Since this is a simple merge which no key matching is needed, we can simply add a dummy key to the table.

SELECT
    REPLACE(`C`.`Name`, 'C', '') AS C,
    'dummy' AS `tkey`
FROM C
WHERE `C`.`Type` = 1

We do that on Table M as well.

SELECT
    REPLACE(`M`.`Name`, 'M', '') AS M,
    'dummy' AS `tkey`
FROM M
WHERE `M`.`Type` = 1

Since these are derived tables, we have to give them an alias.

(
    SELECT
        REPLACE(`C`.`Name`, 'C', '') AS C,
        'dummy' AS `tkey`
    FROM C
    WHERE `C`.`Type` = 1
) AS NewC

(
    SELECT
    REPLACE(`M`.`Name`, 'M', '') AS M,
    'dummy' AS `tkey`
    FROM M
    WHERE `M`.`Type` = 1
) AS NewM

By performing a LEFT JOIN on the two tables, you get the C and M columns as desired.

SELECT
    NewC.`C`,
    NewM.`M`
FROM (
    SELECT
        REPLACE(`C`.`Name`, 'C', '') AS C,
        'dummy' AS `tkey`
    FROM C
    WHERE `C`.`Type` = 1
) AS NewC
LEFT JOIN
    (
        SELECT
        REPLACE(`M`.`Name`, 'M', '') AS M,
        'dummy' AS `tkey`
        FROM M
        WHERE `M`.`Type` = 1
    ) AS NewM
ON `NewM`.`tkey` = `NewC`.`tkey`
ORDER BY `NewC`.`C` ASC, `NewM`.`M` ASC

And implementing the autoincrement column from Source #3, we first wrap the above query into a subquery (if you would like to keep the order as in your demo):

(SELECT
    NewC.`C`,
    NewM.`M`
FROM (
    SELECT
        REPLACE(`C`.`Name`, 'C', '') AS C,
        'dummy' AS `tkey`
    FROM C
    WHERE `C`.`Type` = 1
) AS NewC
LEFT JOIN
    (
        SELECT
        REPLACE(`M`.`Name`, 'M', '') AS M,
        'dummy' AS `tkey`
        FROM M
        WHERE `M`.`Type` = 1
    ) AS NewM
ON `NewM`.`tkey` = `NewC`.`tkey`
ORDER BY `NewC`.`C` ASC, `NewM`.`M` ASC) AS final

And we use a CROSS JOIN on a variable which would increment by 1:

SELECT
    (@cnt := @cnt + 1) AS id,
    final.C,
    final.M
FROM
    (SELECT
        NewC.`C`,
        NewM.`M`
    FROM (
        SELECT
            REPLACE(`C`.`Name`, 'C', '') AS C,
            'dummy' AS `tkey`
        FROM C
        WHERE `C`.`Type` = 1
    ) AS NewC
LEFT JOIN
    (
        SELECT
        REPLACE(`M`.`Name`, 'M', '') AS M,
        'dummy' AS `tkey`
        FROM M
        WHERE `M`.`Type` = 1
    ) AS NewM
ON `NewM`.`tkey` = `NewC`.`tkey`
ORDER BY `NewC`.`C` ASC, `NewM`.`M` ASC) AS final
CROSS JOIN (SELECT @cnt := 0) AS dummy

And we are done.

Sources:

https://www.w3schools.com/sql/func_sqlserver_replace.asp
https://stackoverflow.com/questions/42663074/is-merge-statement-available-in-mysql
https://stackoverflow.com/questions/15930514/mysql-auto-increment-temporary-column-in-select-statement


Special characters in Oracle utl_http request When calling a web service using utl_http, the calls fail when the SOAP envelope contains characters like ¿, ± or º.

IS
     http_req  utl_http.req;
     http_resp utl_http.resp;
     request_env varchar2(32767);
     response_env varchar2(32767);
BEGIN

    request_env:='
<soap:Envelope
     ...
</soap:Envelope>';

    http_req := utl_http.begin_request('http://ws.domain.com/Project.asmx', 'POST', utl_http.HTTP_VERSION_1_1);
    utl_http.set_header(http_req, 'Content-Type', 'text/xml; charset=utf-8');
    utl_http.set_header(http_req, 'Content-Length', length(request_env));
    utl_http.set_header(http_req, 'SOAPAction', '"http://domain.com/WS/ProjectAction"');
    utl_http.write_text(http_req, request_env);

I thought that charset=utf-8 in Content-Type should take care of that, but it still bombs and the characters above have to be replaced to their ASCII multi-characters equivalents beforehand.

This is an NLS report for this database:

NLS_CALENDAR    GREGORIAN
NLS_CHARACTERSET    WE8ISO8859P1
NLS_COMP    BINARY
NLS_CURRENCY    $
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE   AMERICAN
NLS_DUAL_CURRENCY   $
NLS_ISO_CURRENCY    AMERICA
NLS_LANGUAGE    AMERICAN
NLS_LENGTH_SEMANTICS    BYTE
NLS_NCHAR_CHARACTERSET  AL16UTF16
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS  .,
NLS_SORT    BINARY
NLS_TERRITORY   AMERICA
NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT  HH.MI.SSXFF AM TZR

I am kind of consulting the in-house developers on this issue and before I will try to convince them to try redeclare request_env as NVARCHAR2, wanted to get the community opinion on whether that should be the fix they should attempt.

The problem is length or lengthb functions treat these special characters as 1 byte but write_text function (correctly) considers these as 2 bytes. Hence a mal-formed XML is sent to the server.

To resolve this error please use the following instead of the length / lengthb function.

lv_clob_length_bytes := utl_raw.length(utl_raw.convert(utl_raw.cast_to_raw(lv_request_body),'american_america.al32utf8','ENGLISH_UNITED KINGDOM.WE8ISO8859P15'));

The above casts the XML to raw and then converts it from our characterset to UTF8 before finding its length.

Change write_text to write_raw like this:

UTL_HTTP.WRITE_RAW (r    => http_req,
                data => UTL_RAW.CAST_TO_RAW(request_env));


Database backup: NT AUTHORITY\\SYSTEM is not able to access the database When trying to automatically backup a MsSQL database on a Windows 2012 Server, the following exception is thrown, and hence the backup is not taken:

  Description: Error encountered during backup. Error: [Microsoft.SqlServer.Management.Common.ExecutionFailureException:The server principal "NT AUTHORITY\SYSTEM" is not able to access the database "TestDB" under the current security context. BACKUP DATABASE is terminating abnormally. An exception occurred while executing a Transact-SQL statement or batch.]

We had 2 instance and NB MSSQL Job was failing and we just added (NT AUTHORITY\SYSTEM) under sysadmin in security roles and restarted the Netbackup services. NB SQL Jobs Started working.

Apparently, for MsSQL 2012 and above, the BUILTIN\administrators and Local System (NT AUTHORITY\SYSTEM) are not automatically provisioned in the sysadmin server role. So simply add the sysadmin role to NT AUTHORITY\SYSTEM and everything should work.

This can be done in the Object Explorer by expanding:

  Server -> Security -> Server Roles -> sysadmin


"SELECT *" why is it an antipattern On multiple questions here and on stack overflow i saw people saying in the comments and answers that select * from table is almost always an antipattern without any explaination why. Althrough I can sort of deduce why it is an antipattern. I may be looking over a detail that someone else with better understanding of the issue noticed.

So here is my question, why people say that select * is an antipattern.

The two reasons that I find the most compelling not to use SELECT * in SQL Server are

Memory Grants
Index usage

Memory Grants

When queries need to Sort, Hash, or go Parallel, they ask for memory for those operations. The size of the memory grant is based on the size of the data, both row and column wise.

String data especially has an impact on this, since the optimizer guesses half of the defined length as the 'fullness' of the column. So for a VARCHAR 100, it's 50 bytes * the number of rows.

Using Stack Overflow as an example, if I run these queries against the Users table:

SELECT TOP 1000 DisplayName
FROM dbo.Users AS u
ORDER BY u.Reputation;

SELECT TOP 1000 DisplayName, u.Location
FROM dbo.Users AS u
ORDER BY u.Reputation;

DisplayName is NVARCHAR 40, and Location is NVARCHAR 100.

Without an index on Reputation, SQL Server needs to sort the data on its own.

But the memory it nearly doubles.

DisplayName:

DisplayName, Location:

This gets much worse with SELECT *, asking for 8.2 GB of memory:

It does this to cope with the larger amount of data it needs to pass through the Sort operator, including the AboutMe column, which has a MAX length.

Index Usage

If I have this index on the Users table:

CREATE NONCLUSTERED INDEX ix_Users
    ON dbo.Users
(
    CreationDate ASC,
    Reputation ASC,
    Id ASC
 );

And I have this query, with a WHERE clause that matches the index, but doesn't cover/include all the columns the query is selecting...

SELECT u.*,
       p.Id AS [PostId]
FROM   dbo.Users AS u
JOIN   dbo.Posts AS p
ON p.OwnerUserId = u.Id
WHERE  u.CreationDate > '20171001'
       AND u.Reputation > 100
       AND p.PostTypeId = 1
ORDER BY u.Id

The optimizer may choose not to use the narrow index with a key lookup, in favor of just scanning the clustered index.

You would either have to create a very wide index, or experiment with rewrites to get the narrow index chosen, even though using the narrow index results in a much faster query.

CX:

 SQL Server Execution Times:
   CPU time = 6374 ms,  elapsed time = 4165 ms.

NC:

 SQL Server Execution Times:
   CPU time = 1623 ms,  elapsed time = 875 ms.

Two main reasons:

Firstly, your database has to go and work out which columns are available on the table[s] that you're querying.  That's an additional overhead and, in [older versions of] older DBMSs, that could be a [very] significant performance bottleneck if there was lots of contention around the System Catalog tables holding this information.

Secondly, it assumes that you know everything about these tables and that you always will.  Database are inherently shared resources and will, over time, have many people working on them, changing the data structures to keep up with changing requirements.  Today, you could create a table with two columns (say, "username" and "password_hash") and use that as the core of your Application's login screen.  Everything works well for ages ...   until someone [else] comes along and shoehorns into your tidy, little table a couple of dozen extra, "blob" fields, containing gigantic data or photos or whatever.  Suddenly, your login screen slows to a crawl because the query is pulling back all ("*") those extra, gigantic, network traffic-bloating fields in which it has no interest at all!

Also, you can write code that depends on the order in which fields are returned (you shouldn't, but you can).  This is most common in reporting circles, particularly if data is "handed off" to a User's spreadsheet program of choice.  When the table structure changes, fields can "move around" and your (their?) data "suddenly" starts arriving in the "wrong" order.

Always ask for the columns that you need, in the order in which you need them.

And, just for completeness, remember the "order by " clause - rows in [Relational] tables have no intrinsic order.


Left outer join ruins query in MySQL I have two tables. One of them is big, long with more than 500 columns (most of those are tinyint) and 360000 rows. It contains info about photos. Second one is small with just three int columns (surrogate primary key and two foreign keys) and one decimal. It has just 45000 rows and contains info about what people are on those photos. It's so small it doesn't even have indexes. What I have trouble with is selecting photos within clause that have no people marked on them.
Query with clause within just first big table takes acceptable few seconds. Then I make something like that

SELECT photos.* FROM photos
 LEFT OUTER JOIN
 photos_people ON photos.photo_id = photos_people.photo_id
WHERE photos_people.photo_people_id is NULL AND ... LIMIT 150;

Now it suddenly takes literally minutes. What am I doing wrong? Is there a way to speed up query?

Add an index on the column photo_id  on both the tables- photos and photo_people.


Backup and restore database - occured system error 5 In my work I made backup of database and I receive file .bak. Now I would like restore this database in my machine. But I get the following error:

  Cannot open backup device 'C:\Program Files...\MSSQL10_50.SQLEXPRESS...\file.bak'.
  Operating
  system error 5(failed to retrieve text for this error. Reason: 15105).
  RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server,
  Error: 3201)

Of course I searched the Internet, for example:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/805661f1-cf23-4230-ae31-d9cc16505359/cant-restore-backup-from-2005-to-2008r2?forum=sqlexpress

But all users have permissions to all folders so I'm really confused. What is the problem?

Sounds like a permissions error is being masked. You have to remember that the user that needs access to the folder where the backup resides (and where you restore the backup, if using WITH MOVE explicitly) is the SQL Server service account, not regular users.

If the folder listed in the error message does not represent the same instance where you're trying to restore, it's unlikely the service has access to the folder. Try moving the .bak file to the current instance's data or log folder, where SQL Server will certainly have adequate permissions.

I has this error trying to restore a .bak file to a new server when I had forgotten to specify a name for the new database it was going to create


What is table bloating in databases? Can someone explain what bloating means in database terms? For example what does it mean to say an index is bloated. I tried searching for it but there is no explanation of what bloating is, only what it causes or what it is caused by.

Due to how PostgreSQL handles transactions and concurrency, MVCC - Multi-Version Concurrency Control, you can get bloat. In PostgreSQL, when you do an UPDATE or DELETE, the row isn't actually physically deleted. For a DELETE, it simply marks the row as unavailable for future transactions, and for UPDATE, under the hood it's a combined INSERT then DELETE, where the previous version of the row is marked unavailable.

While the data is marked unavailable, it is still there, and the space cannot be used. To then mark the space as available for use by the database, a vacuum process needs to come along behind the operations, and mark that space available for the database to use. It isn't returned to the operating system, however. That only happens when there are no active rows in an entire page, which can be uncommon in some workloads. This can be a good thing for some workloads, because you can just simply update the space on the individual pages inside of the data files, without needing to add additional data files.

Problems come about with bloat when there are are excessively large numbers of dead tuples versus live tuples. Walking along and checking all of the visibility flags takes time, and having more data files for a relation results in additional unnecessary IO load. Bloat is especially noticeable on indexes, which can also have many dead tuples, sometimes many more than the table. Bloat can slow index lookups and scans, which will show up in slowly increasing query times and changing query plans.

You can restore space by using pg_reorg, pg_repack, CLUSTER, or VACUUM FULL. This will go through and reorganize the files, moving tuples and reorganizing to make sure that there are no dead tuples, which will eliminate the bloat.

Bloat can also be efficiently managed by adjusting VACUUM settings per table, which marks dead tuple space available for reuse by subsequent queries.

You can use queries on the PostgreSQL Wiki related to Show Database Bloat and Index Bloat to determine how much bloat you have, and from there, do a bit of performance analysis to see if you have problems with the amount of bloat you have on your tables.

This probably refers to common issues with indexes where either two many columns are added to the index or there are overlapping indexes on a table.  That is, multiple indexes with the same set of columns in them (making one of them unnecessary).  I would review all indexes on tables looking for overlapping columns, looking for indexes which are just subsets of other indexes and remove them IF you can determine that they aren't being used.

On top of that, as data in tables are updated, the indexes can become fragmented which causes them to be larger than necessary.  I'm not familiar with postgres, but I suspect there are methods to defragment the indexes (by rebuilding the indexes) that will reduce their size on disk.


How do you mysqldump specific table(s)? How can I dump a specific table or set of tables without including the rest of the db tables?

If you are dumping tables t1, t2, and t3 from mydb
mysqldump -u... -p... mydb t1 t2 t3 > mydb_tables.sql

If you have a ton of tables in mydb  and you want to dump everything except t1, t2, and t3, do this:
DBTODUMP=mydb
SQL="SET group_concat_max_len = 10240;"
SQL="${SQL} SELECT GROUP_CONCAT(table_name separator ' ')"
SQL="${SQL} FROM information_schema.tables WHERE table_schema='${DBTODUMP}'"
SQL="${SQL} AND table_name NOT IN ('t1','t2','t3')"
TBLIST=`mysql -u... -p... -AN -e"${SQL}"`
mysqldump -u... -p... ${DBTODUMP} ${TBLIST} > mydb_tables.sql

Give it a Try !!!
UPDATE 2014-03-06 10:15 EST
@RoryDonohue pointed out to me that the GROUP_CONCAT function needs to have its max length extended. I added the session variable group_concat_max_len to my answer with a length max of 10K. Thanks, @RoryDonohue.

You can do it simply using below command:

mysqldump -uusername -ppassword dbname \
  --ignore-table=schema.tablename1    \
  --ignore-table=schema.tablename2    \
  --ignore-table=schema.tablename3 > mysqldump.sql


Splitting a large SQL Server MDF file I have a large (1.2 terabyte) SQL Server database that I need to migrate to a new server. Most of the database lives on a single, 1.25 TB data file, and a little bit sits on a much-more-manageable 550 GB file (which is practically empty).

Now, the tricky bit: the server to which I'm migrating only has 3 700 GB volumes, meaning I need to somehow dissect this goliath into three equal chunks. Most advice I've found involves creating 3 target files and running DBCC SHRINKFILE EMPTYFILE on my main file to empty it into the targets, but that'd take ages with a database this large.

Is there a recommended method for splitting a database this large? I'm considering using the Sql Server Integration Services Data Export feature to dump the data into a clone database with the proper file structure, but I'm curious as to whether there's a better way.

DBCC SHRINKFILE -- will be single-threaded – which will contribute to the long run-time.

Also, Sql Server Integration Services Data Export will be slower due to massive database size (1 TB) !

Instead, you should look for BCP OUT (in binary format) and BULK INSERT in the database.

BCP can read the SQL Server native format from text files. This is a very fast option that requires minimal parsing of the text file input.
The BULK INSERT command is the in-process method for bringing data from a text file into SQL Server. Because it runs in process with Sqlservr.exe, it is a very fast way to load data files into SQL Server.
Additionally, enable Trace Flag 610 to minimally log inserts into Indexed tables along with BATCHSIZE and ROWS_PER_BATCH parameters.
You can even disable Lock Escalation which on sql server 2005 generally occurs at 5000 locks allocated. This can be disabled on sql server 2005 by setting a batchsize to a number lower than this or enableing Trace flag 1211 (to completely disable lock esclation). In sql server 2008, you can do it using alter table  <table_name> (LOCK_ESCLATION = DISABLE)
BCP has a -a switch to allow to specify packet size or if using SSIS it can be done in properties of the connection manager --> PacketSize = 32767
To avoid  PFS contention, consider using -E startup parameter.

Also at hardware level, you can look into

Use fast NIC and switches.
Have the latest certified drivers for your NIC.
Enable full duplex.
Enable support for Jumbo frames.
Use TCP Chimney Offload.
Use Receive Side Scaling (RSS).

Check the BCP & BULK INSERT script that I provided at here

Note: Your hardware including disk partitioning, layout, number of CPU's, NUMA configuration, etc will also play a big role in performance when you load your data.

Excellent reading:

Bulk Inserting 1 Terabyte within 10 minutes
We Loaded 1TB in 30 Minutes with SSIS, and So Can You
Load 1TB in less than 1 hour
The Data Loading Performance Guide

"…but I'm curious as to whether there's a better way."

Yes - use hardware or software RAID to create one RAID0 (or maybe RAID5, but write performance might suffer) array across the three 700 GB disks.

Better still (as the commenter below says), add more disks and create a RAID array with fault tolerance.

SQL Server databases are more manageable with fewer files, not more files.

Is it possible to physically remove the hard disks from the old server and install them in the new server? If so then restoring the database into the new instance of SQL Server is just a matter of attaching the data files.

Edit: The poster below makes an excellent comment about fault tolerance. A RAID0 array has no tolerance for errors. Then again, neither does splitting the database across three individual disks.

I am OK with SQL Server data files being on RAID arrays with no tolerance, if and only if (1) there is a great DR solution, and (2) the log file is on an array with fault tolerance, and (3) the database is in Full recovery Model (so the tail of the log can be backed up).


Group and order output into a single row I want to display each person's contact info and their commissions in a single row as opposed to multiple rows.

Commission Table

ID          COMMISSION  DATE
N12545233    774         4/1/15
N12545233    361         5/1/14
N12545233    685         6/1/15
N14521746   3384        10/1/14
N14521746   1188        11/1/14
N14521746   1256        12/1/14
N15981070   1188         1/1/15
N15981070    616         2/1/15
N15981070    936         3/1/15

Sales_Staff Table

ID          LASTNAME    FIRSTNAME   STREET          CITY        STATE   ZIP
N12545233   Jones       Mark        123 Main St     Brooklyn    NY      11377
N14521746   Greene      Phil        345 Broadway    Queens      NY      11237
N15981070   Stahl       Barbara     567 Lexington   New York    NY      10018

Desired Output

ID         LASTNAME FIRSTNAME   STREET        CITY      STATE   ZIP     COMMISSION  DATE      COMMISSION    DATE           COMMISSION   DATE
N12545233  Jones    Mark        123 Main St   Brooklyn  NY      11377   774          4/1/15    361           5/1/14          685         6/1/15
N14521746  Greene   Phil        345 Broadway  Queens    NY      11237   3384        10/1/14   1188          11/1/14         1256        12/1/14
N15981070  Stahl    Barbara     567 Lexington New York  NY      10018   1188         1/1/15    616           2/1/15          936         3/1/15

If you're running on MySQL:

SELECT
    c.ID,
    ss.LASTNAME,
    ss.FIRSTNAME,
    ss.STREET,
    ss.CITY,
    ss.STATE,
    ss.ZIP,
    GROUP_CONCAT(c.COMMISSION ORDER BY c.DATE SEPARATOR '\n') AS COMMISSION,
    GROUP_CONCAT(c.DATE ORDER BY c.DATE SEPARATOR '\n') AS DATE
FROM db1.Sales_Staff AS ss
JOIN db1.Commission AS c ON (c.ID=ss.ID)
ORDER BY ss.LASTNAME ASC,ss.FIRSTNAME ASC;

In Oracle

select a.*, b.commission, b.date
from sales_staff a, comission b
where a.id = b.id;


Query that always worked now hangs indefinitely many years ago I coded a stored procedure containing the following SQL (for a client's backend website) and it has always worked fine for them right up until a few days ago when it started to fail with a timeout message. When I run the SP directly in SSMS it just doesn't terminate, I left it running for 90 minutes and it neither timed out nor completed. In the past it would have run fine in just a few seconds.

During the query the CPU maxes out, but RAM usage stays reasonable.

We have moved to a faster server with more RAM and the latest version of SQPEXPRESS (10.50.4000.0) but this has not fixed the problem. (BTW i'm aware of the single-core limitation of SQLEXPRESS, but that's what we are having to use)

The database tables involved are pretty large, but have only been very slowly increasing in size over the years and the code has always worked pretty quickly right up until recently.

Unfortunately the project was so long ago that I have very little idea about what is going on in the code even though I created it myself!

My hunch is that the query involves an exponential increase in CPU cycles as the table sizes increase, and that we've now hit a critical point in size.

My knowledge of SQL Server is patchy, especially after several years of non-involvement. Would I be right in thinking that this can be tackled by adding indexes? If so how should I go about it?

Or perhaps the script needs to be recoded using a different approach?

Any help greatly appreciated!

select DISTINCT
Pprin.product_id as printerID,
dbo.deduceMFRfromSubCatName(subcat.category) as MFRdeduced,
RTRIM(ISNULL(ECAT.category,'NOCAT')) + '///' + RTRIM(ISNULL(SubCat.category, 'NOSUBCAT')) + '///' AS catPath,
ISNULL(PO.forceName,Pprin.model_name) as approvedPrinterName,
max(M.dateCreated) as newestProductCreated,
max(M.lastUpdated) as mostRecentProductEdit,
max(M.lastIceCatFetch) as latestProductSheetFetch,
isnull(PO.dateForced, '1-Dec-2012') as nameApprovalDate

from xtra_icecat_printers1_rel_carts2 R
inner join xtra_icecat_allproducts Pcart on R.prodID2 = Pcart.product_id
inner join xtra_itemmaster I on dbo.dataSKU(I.SKUlite) = Pcart.SKUlite
inner join xtra_SKUmaster M on dbo.dataSKU(M.SKUlite) = Pcart.SKUlite
inner join xtra_icecat_allproducts Pprin on R.prodid1 = Pprin.product_id
inner join xtra_icecat_productdata Dprin on R.prodid1 = Dprin.product_id
left join Xtra_IceCat_Cats CA on CA.catID=Pprin.category_id
left join Xtra_IceCat_PrinterNameOverride PO on R.prodid1 = PO.printerID
left join category ECAT on ECAT.CatID=I.CatID
left join subcategory Subcat on Subcat.SubcatID=I.subCatID
where copyOfPrinterID=Dprin.product_id and isnull(PO.forceName,'OFF')<>'OFF'
group by Pprin.product_id, forceName, ECAT.category, subcat.category, Pprin.model_name, PO.dateForced
order by Pprin.product_id

dbo.dataSKU function (from comments):

ALTER FUNCTION [dbo].[dataSKU] (@SKUlite as varchar(25))
RETURNS varchar(25)
AS
BEGIN
  IF @SKUlite LIKE '%COMP'
    SET @SKUlite = LEFT(@SKUlite, LEN(@SKUlite)-4)
  RETURN @SKUlite
END;

Here's what I'd generally do. I apologize if the indexes are duplicative to what you already have. I started looking at the output you posted, but there was  lot in there that I didn't have time to weed through.

Architecture:

Replace WHERE CLAUSE function dbo.dataSKU with a computed column in xtra_itemmaster and xtra_SKUmaster. I don't mean use the function in a computed column, I mean pull out the code you showed me and put that in one.

You can do that with a case expression like this (not copy and paste-able)

ALTER TABLE dbo.Table ADD [Computed SKU Column Name Here] AS CASE WHEN SKUlite LIKE '%COMP' THEN left(SKUlite, len(SKUlite)-4) ELSE SKUlite END /*PERSISTED if possible*/

That will allow you to index the computed expression, which should make the joins more efficient.

Query:

After adding indexes, play with removing the distinct. You're already grouping by a bunch of columns, so I'm not sure what else you're getting out of this.

Pull out the order by Pprin.product_id unless you really need it.

Replace dbo.deduceMFRfromSubCatName with inline table valued function. I can't do this rewrite, even if you show me the code.

Indexes I'd like:

xtra_icecat_printers1_rel_carts2
 - (prodid1, prodID2)

xtra_icecat_allproducts
 - (product_id, SKUlite)

xtra_itemmaster
 - ([Computed SKU column], CatID, subCatID)

xtra_SKUmaster
 - ([Computed SKU column], dateCreated, lastUpdated, lastIceCatFetch)

xtra_icecat_allproducts
 - (product_id, category_id) INCLUDE (model_name)

xtra_icecat_productdata
 - (product_id)

Xtra_IceCat_Cats
 - (catID)

Xtra_IceCat_PrinterNameOverride
 - (printerID) INCLUDE (forceName, dateForced)

category
 - (CatID)

subcategory
(SubcatID) INCLUDE (category)

And, as discussed in the comments, replace the ISNULL in the WHERE clause as well.

Hope this helps!

Few considerations:

(1) Update statistics in your tables.

(2) Consider tuning the indexes in each table.

(3) This query includes many JOINs which will slowdown performance. Consider creating fact table to speed up the data collection.


Remove both Special Characters and String Characters from output SQL Server I am looking to remove some string characters in an output that I have so that it only shows numbers rather then letters and numbers. My Query is:

Select DISTINCT t.processed 'Title',
(Select max(reconst) from isbn where bib# = t.bib#) 'ISBN',
t.bib# 'Bib#',
(select max(collection) from item where bib#=t.bib#) 'Collection',
convert ( varchar(3), (Select count (*) from item i where i.bib# = t.bib#)) 'Total Items',
(Select count (*) from item i where i.bib# = t.bib# and item_status not in ('o','l','cr','m')) 'Eligible Items',
(Select count(*) from request r where r.bib# = t.bib#) 'Requests'
from title t
inner join isbn
on t.bib#=isbn.bib#
inner join item
on t.bib#=item.bib#
where t.bib# in (Select bib# from request)
and convert( int, (Select count(*) from item i where i.bib# = t.bib#)) <
convert( int, (Select count(*) from request r where r.bib# = t.bib#))
order by Requests desc

The output from the Query is:

The desired output is in the ISBN column which would would look like 9781501132957, which would be removing the letters C and also the words such as hardcover, HRD1, acid-free paper, softcover,PAP, DVD, CD, pbk and special characters such as (), :, ., -

Thank you in advance

Try this code that uses a combination of SUBSTRING, PATINDEX, LEN, and REVERSE, to account for the possibility of 10-13 digit ISBN variability to return the exact ISBN with no trailing extras:

declare @myvar varchar(50)='C9781501132957 : (hardcover)'
SELECT substring(@myvar,2,LEN(@myvar)-   PATINDEX('%[0-9]%',reverse(@myvar)))

...or perhaps differentiating between other numbers in the string from ones that are at least 10 digits in size, like maybe a date of 1987 included in the description (if this is even needed):

declare @myvar varchar(50)='C9781501132957 : (hardcover) 1987'
SELECT substring(@myvar,2,LEN(@myvar)-   PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',reverse(@myvar)))

i.e.

Select DISTINCT t.processed 'Title',
substring((Select max(reconst) from isbn where bib# = t.bib#),2,LEN(@myvar)-   PATINDEX('%[0-9]%',reverse(@myvar))) 'ISBN',    t.bib# 'Bib#',
(select max(collection) from item where bib#=t.bib#) 'Collection',
convert ( varchar(3), (Select count (*) from item i where i.bib# = t.bib#)) 'Total Items',
(Select count (*) from item i where i.bib# = t.bib# and item_status not in ('o','l','cr','m')) 'Eligible Items',
(Select count(*) from request r where r.bib# = t.bib#) 'Requests'
from title t
inner join isbn
on t.bib#=isbn.bib#
inner join item
on t.bib#=item.bib#
where t.bib# in (Select bib# from request)
and convert( int, (Select count(*) from item i where i.bib# = t.bib#)) <
convert( int, (Select count(*) from request r where r.bib# = t.bib#))
order by Requests desc

Use this simple function

Create Function [dbo].[fn_rm_special_char](@ver VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^0-9]%'
    While PatIndex(@KeepValues, @ver) > 0
        Set @ver = Stuff(@ver, PatIndex(@KeepValues, @ver), 1, '')

    Return @ver
End

Thanks


Seemingly equivalent queries take vastly different execution times I am encountering an issue where two different ways of executing the same query are taking very different execution times. My guess is that in one way or another, the slow query is unable to utilize my index.
I have a large table of data points (Data)

TABLE(assetId INT, dataItemId INT,timestamp DATETIME(2),value REAL)

There is a full table unique clustered index here. assetId is the owner of the datapoint, dataItemId is other metadata for the datapoint, including a label. So there is a table DataItems

TABLE(dataItemId INT, labelId INT, unitId INT)

And finally a table of Labels (there is another table just like Labels for Units). I have FK constraints on the DataItems/Labels/Units tables.

TABLE(labelId INT, label VARCHAR(128))

I would like to make a query for data across a time range asking for all datapoints with a certain label for a particular asset. Something like:

--QUERY #1
SELECT Data.timestamp,Labels.label,Data.value
FROM Data
JOIN DataItems on DataItems.dataItemId = Data.dataItemId
JOIN Labels on DataItems.labelId = Labels.labelId
WHERE Data.assetId=134
AND Label.label IN ('Eng_RPM','Eng_Load','Eng_OilTemp')
AND Data.timestamp between '2016-09-27T12:00:00Z' AND '2016-09-27T14:00:00Z'

However, this query (returns 21000 rows) takes about 38 seconds to execute. So I tried an alternate query, where I use dataItemId instead of Labels:

--QUERY #2
SELECT Data.timestamp,Labels.label,Data.value
FROM Data
JOIN DataItems on DataItems.dataItemId = Data.dataItemId
JOIN Labels on DataItems.labelId = Labels.labelId
WHERE Data.assetId=134
AND Data.dataItemId IN (
   SELECT dataItemId from DataItems
   JOIN Labels ON DataItems.labelId=Labels.labelId
   WHERE label in ('ENG1_Eng_Load','ENG2_Eng_Load','ENG3_Eng_Load')
   )
AND Data.timestamp between '2016-09-27T12:00:00Z' AND '2016-09-27T14:00:00Z'

I didn't expect this to work any better, and it didn't. However, I then took the output of the nested select in the WHERE clause, and used it as a constant:

--QUERY #3
SELECT Data.timestamp,Labels.label,Data.value
FROM Data
JOIN DataItems on DataItems.dataItemId = Data.dataItemId
JOIN Labels on DataItems.labelId = Labels.labelId
WHERE Data.assetId=134
AND Data.dataItemId in (618,654,690)
AND Data.timestamp between '2016-09-27T12:00:00Z' AND '2016-09-27T14:00:00Z'

This query runs like a dream with <1s query times. I don't understand why though - it seems to me that the nested query for finding the dataItemId from a label should execute first, and I verified that it is a trivial query. From there, why is there a difference between the way #2 and #3 execute, and why does it take so much longer? I am new to databases in general, and to SQL server/ Management studio, so my apologies if I have overlooked some wonderful feature for analyzing this problem.

Edit:

Indices/PKs are as follows:

 --Data:
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-20160725-145924] ON      [dbo].[Data]
 (
[assetId] ASC,
[dataItemId] ASC,
[timestamp] ASC,
[value] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

--DataItems
ALTER TABLE [dbo].[DataItems] ADD PRIMARY KEY CLUSTERED
(
[dataItemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

--Labels
ALTER TABLE [dbo].[Labels] ADD PRIMARY KEY CLUSTERED
(
[labelId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

I have viewed the execution paths and I did find that the slow queries are not using the Clustered index on Data - they are using a different Non-clustered index that I did not intent to keep (on assetId,timestamp)

SELECT Data.timestamp, Labels.label, Data.value
FROM Data
JOIN DataItems
      on DataItems.dataItemId = Data.dataItemId
     and Data.assetId = 134
     AND Data.timestamp between '2016-09-27T12:00:00Z' AND '2016-09-27T14:00:00Z'
JOIN Labels
      on DataItems.labelId = Labels.labelId
     AND Label.label IN ('Eng_RPM','Eng_Load','Eng_OilTemp')

maybe add a non-clustered index on Label.label


"Untrusted Domain" error when connecting to SQL Server localhost and specifying port I have two SQL Server 2016 instances running on localhost, and I can connect to both with no issues (localhost and localhost\test) using windows authentication. However, if I specify the port explicitly in SSMS ("localhost,1433" or "localhost,1435", respectively), I receive the following error:

  Login failed. The login is from an untrusted domain and cannot be used
  with Windows authentication. (Microsoft SQL Server, Error: 18452)

I'm connecting to a local instance (it's not a domain PC, but it shouldn't matter) and I'm a local admin, so there are no permissions issues - when I connect without the port specified, I connect without any issues at all.

I've seen this error when connecting across domains or in a workgroup, but that's not what's happening here as I can connect if I remove the port number.

So, we just had this problem, adding details in case it helps someone else and they don't spend time going down the rabbithole of Kerberos debugging like we did.

I don't have all the details because our next level tier fixed it, but apparently in our case since we had a cross-forest trust authentication, the issue was that we needed to grant the user's group "Allowed to Authenticate" permission on the server object they were trying to connect to, after that it worked fine.

we had a win2k16 server running Sql2017 in domain1, and users running win7 in domain2 with trusts setup between the two, this worked fine.

But when the users migrated to win10, they received the above error. Fix for us was granting 'allowed to authenticate' to the domain user group in domain2 on the server in domain1, then win10 worked fine.

If anyone knows why upgrading client OS makes any difference please let us know!

If you have named instances and don't want to declare your ports then you need to ensure that SQL Server Browser is running

By default this runs through port UDP 1434

Also try below steps if you are connecting using the port number

create manually a SPN for the SQL service (we use managed service accounts)
add a localhost 127.0.0.1 entry in the host file
check that the TCP and Named Pipes protocols are enabled
check if firewalls are disabled on your local machine from where are you connecting, do you have all the AD User auth ports opened.


Can CLR execution continue after 'THROW' is encountered in T-SQL? I am calling a T-SQL stored procedure from a CLR stored procedure. and the THROW statement in T-SQL stops the execution of CLR without going into the catch block. Is there something I can change in my code to cause the CLR procedure to execution the catch block?

T-SQL:

CREATE TABLE t1 (ID INT PRIMARY KEY clustered);
CREATE TABLE t2 (ID INT CONSTRAINT fk_1 FOREIGN KEY REFERENCES t1(ID))
GO

CREATE PROC dbo.TestThrow
AS
BEGIN
BEGIN TRY
    INSERT INTO t2 VALUES (1)
END TRY
BEGIN CATCH
    THROW
END CATCH
END
GO

CLR:

    public static void TestThrow()
    {
        String query = "EXEC DBO.TESTTHROW";
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            try
            {
                using (SqlCommand command = new SqlCommand(query, connection))
                {

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        SqlContext.Pipe.Send("no error");
                    }
                }
            }
            catch (SqlException e)
            {
                SqlContext.Pipe.Send("sqlexception");
                SqlContext.Pipe.Send(e.Message);
            }
            catch (Exception e)
            {
                SqlContext.Pipe.Send("exception");
                SqlContext.Pipe.Send(e.Message);
            }
        }
    }

It appears that this behavior is specific to connections using "Context Connection = true;". I have tried to get around this by writing out the try-catch-finally structure instead of using the using macro, but that had no effect.

A Microsoft Connect bug was filed almost 3 months ago regarding this behavior. In that Connect bug it was speculated that THROW raises a ThreadAbortException that cannot be reset via the Thread.ResetAbort method. I tried explicitly catching this exception, and even calling Thread.ResetAbort when catching the generic Exception, but to no avail. So I am not sure if a ThreadAbortException is really being called, but regardless, the current process ends immediately. And, it even displays the error as a T-SQL error and not a .NET Framework error, which is odd.

The reporter of that Connect bug tested on SQL Server 2014, and I tested on SQL Server 2012. I cannot say for certain if this behavior still exists in SQL Server 2016, but I strongly suspect that it does given that there doesn't seem to be much effort (if any) put into fixing and/or improving SQL Server's CLR Integration (i.e. SQLCLR). So for now, and likely the foreseeable future, there are three possible work-arounds that have worked for me:

Replace THROW with RAISERROR(); RETURN; for procs that might be called by SQLCLR objects. The only downsides I can think of are:

Can't set a custom ERROR_NUMBER on-the-fly
Can't re-throw to send the original, system-defined ERROR_NUMBER to the caller

Wrap your query in a T-SQL TRY / CATCH that uses RAISERROR:

  String _Query = @"
BEGIN TRY
  EXEC dbo.TestThrowSql @CauseError;
END TRY
BEGIN CATCH
  DECLARE @ErrorMessage NVARCHAR(4000);
  SET @ErrorMessage = ERROR_MESSAGE();
  RAISERROR(@ErrorMessage, 16, 1);
END CATCH;
";

The benefit here is that you can continue to use THROW and it will work as expected when called by non-SQLCLR app code, by SQLCLR app code that is not using the Context Connection, by other Stored Procedures, in SQL Agent jobs, etc. That and you don't have to go back and edit any existing Stored Procedures :-).
Switch to using a regular / external connection string. The downsides here are:

The Context Connection is much faster.
The Context Connection can be done in a SAFE Assembly.
The Context Connection has access to session-based items (i.e. CONTEXT_INFO, local temporary tables, etc.)

SET XACT_ABORT ON makes THROW behavior same as RAISERROR. See here - https://stackoverflow.com/questions/17781196/throw-t-sql-exception-from-clr


Mongo DB Replica set - not able to read also We have deployed mongodb replica set:

mongod --port 27017   --dbpath /opt/eltropy/mongodb/data/rs0-1 --replSet rs0 --smallfiles --oplogSize 128

mongod --port 27018   --dbpath /opt/eltropy/mongodb/data/rs0-1 --replSet rs0 --smallfiles --oplogSize 128

mongod --port 27019   --dbpath /opt/eltropy/mongodb/data/rs0-1 --replSet rs0 --smallfiles --oplogSize 128

What I understand from mongdb documentation; even if your two nodes goes down, we still should be able to read from remaining working nodes (Single working node in this case). However, write is not possible.

But even reads are not working on the available node. I also try to add one arbiter but still no luck. Remaining node still stay in secondary stage.

I am using mongodb 3.0.

The reason

The answer is quite simple: when two voting nodes of three are down, the remaining one reverts to secondary, to which – by definition – one can't write. The default read preference is to read from and write to the primary of a replica set only. Hence, when only a secondary remains in the replica set, you can not read.

tl;dr

You have to change your read preference at least to primaryPreferred, allowing reads from secondaries.

MongoDB nodes will vote of who can be next Primary, with a majority of votes.

3 nodes : at least 2 have to choose a certain node. When 2 of the 3 can't vote, the the remaining one, has 1 vote out of 3 => no majority
3 nodes + arbiter : now you have 2 votes out of 4, what is still no majority.

Solutions:

You could have a script or manually startup an arbiter for each node that is down.
You can reconfigure the replica set with rs.reconfig() to remove the nodes that are down.
(You could alter the number of votes in the rs.conf, but mongo strongly advises only to change the number of votes for a node in rare cases.)

(MongoDB has some free university/classes where they explain with exercises how all those situations can be handled.)


Insert the same time, it is possible to happen? Good evening.

I have a doubt about inclusion in the database. A common example is a user account where the user names must be unique.

In the following logic:

User enters data into a form.
Data are collected via $ _POST (or other method)
Before saving the data is checked if the user name is already registered
If not, save the record, if it is, it informs the user that the username that already exists.

Example:

<?php
$username = $_POST['username'];
$query = "SELECT * FROM `user_tbl` WHERE `username` = '{$username}'";
$result = mysql_query($query);
if ( mysql_num_rows ( $result ) > 1 ) {
    /* Username already exists */
    echo 'Username already exists';
} else {
    /* Username doesn't exist */
    /* a certain time is elapsed after checking */
    /* .. insert query */
}

My question is, in a system with high volume of requests, it is possible that between the time to check whether the user name already exists and the inclusion (if the user name does not exist), another user can do the same action in same time?

Theoretically you could end up with duplicate usernames.
In a scenario of high incoming requests, a race condition could occur.

If you want to avoid this, just put an UNIQUE index on your column.
You could create a UNIQUE index on username by using this query:

CREATE UNIQUE INDEX IDX_USERNAME ON `user_tbl` (`username`);

Prior to creating this unique index, please verify that there are no duplicate entries in your table, as the index creation would fail.

When you have an unique index on this field, it will be impossible to end up with duplicate usernames.
When a username is inserted, the table is locked during the insertion, this guarantees that a race condition would not occur.

You could create a UNIQUE index on (username).


Oracle - Automate Export/Unload of Data Oracle SQL Developer has an option to export the contents of a query result to various formats (CSV/fixed width/Excel/XML).  Is there any way to automate it?

If not, what free tools are available that will let me automate exports into the same formats that SQL Developer is capable of exporting to?

There are many ways to export data from Oracle and automate the functionality. Be sure to understand exactly what the data export is being used for, though. If it is for interop between systems, then export in a format your receiving system can understand. If it is for backup purposes, go for the exp/expdp (data pump) method because a database backup needs to store much more than simply data. (Better yet, just use RMAN. But I know many DBAs who also like to do full db exports on a regular basis as well.)

You can use a number of tools to accomplish this, TOAD being the one that springs to mind. It has a powerful data export tool that supports scheduling. There is a free version available, but I am uncertain if it has the scheduling functionality. Worth a try, though.

Alternatively, use the tools already at your disposal: SQL*PLUS, PL/SQL, and cron (or the Windows Scheduler if you run Windows). For a good example of how to write a PL/SQL routine that exports a table to a CSV file, see http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:88212348059 . If you use a procedure, you can schedule it from Oracle's own job scheduler.

You can do similar things purely with SQL*PLUS as well (without getting into writing a procedure), but it isn't quite as flexible, but for what you want, it might be just what you need. SQL*PLUS is also easily scriptable so that you can then call it whenever you want via cron/Windows Scheduler.

SQL*PLUS works really well on its own to create good fixed-width reports, but it is possible to do HTML and CSV as well. XML will probably require a procedure of some sort, but I'm not an export here, so SQL*PLUS may not be perfect here (it will output to HTML, though, so that might be good enough). If exporting to Excel, remember that the current versions use XML as their file format, which makes things easy (in one way) and painful in other ways (like needing to know beforehand how many rows you're going to have in the output file).

Regardless, with a little bit of work and the combination of two or three tools, you should be able to export your data in any format you wish on any schedule you desire.

Hope that helps.

/** **/
DECLARE
   lfFilelog UTL_FILE.FILE_TYPE;

   lspath varchar2(50) := 'c:\';
   lsfile varchar2(50) := 'file';
BEGIN
   lfFilelog := UTL_FILE.FOPEN(lspath,lsfile||'.txt','w');

   FOR r IN (SELECT FROM table) LOOP
      UTL_FILE.PUT_LINE(lfFilelog ,r.row);
   END LOOP;

   UTL_FILE.FCLOSE_ALL;
EXCEPTION
   WHEN UTL_FILE.INVALID_OPERATION THEN
      UTL_FILE.PUT_LINE(lfFilelog,SQLERRM);
      UTL_FILE.PUT_LINE(lfFilelog,' Invalid File open Operation');
      UTL_FILE.FCLOSE_ALL;
      RAISE_APPLICATION_ERROR(-20051,'Invalid File open Operation');

   WHEN UTL_FILE.INVALID_FILEHANDLE THEN
      UTL_FILE.PUT_LINE(lfFilelog,SQLERRM);
      UTL_FILE.PUT_LINE(lfFilelog,' Invalid File Name');
      UTL_FILE.FCLOSE_ALL;
      RAISE_APPLICATION_ERROR(-20052,'Invalid File Name');

   WHEN UTL_FILE.READ_ERROR THEN
      UTL_FILE.PUT_LINE(lfFilelog,SQLERRM);
      UTL_FILE.PUT_LINE(lfFilelog,' Read Error');
      UTL_FILE.FCLOSE_ALL;
      RAISE_APPLICATION_ERROR(-20053,'Read Error');

   WHEN OTHERS THEN
      UTL_FILE.PUT_LINE(lfFilelog,SQLERRM);
      UTL_FILE.FCLOSE_ALL;
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      ROLLBACK;
END;


Do I have to retype information every time I make a mistake in SQLPlus If I make a mistake in SQLPlus while inputting an 'insert into' command, is there a way I can bring this information back up in order to edit it?

Several ways.

You can set the EDITOR environment variable before running SQL*Plus (Assuming Unix), to allow use of an external text editor (vi, by way of example):

export EDITOR=vi

Then type ed in SQL*Plus to edit the previous query in the vi editor. Of course, you may prefer nano, pico, emacs, vim etc etc.

If you don't wish to set an environment variable, you can add define _editor=vi to your $ORACLE_HOME/sqlplus/admin/glogin.sql file instead.

My favourite way of achieving SQL*Plus edit heaven (in Unix) is by using rlwrap. This adds readline support to SQL*Plus and allows you to easily edit within SQL*Plus itself.

Install rlwrap from your distributions package repository (or download & compile from the website here), then add the following to your shells startup script (.bashrc/.bash_profile or similar):

alias sqlplus="rlwrap -i sqlplus"

For all commandline application emacs can be used as an interface. You can open the shell mode and run the command line tool. Emacs will send the command you input to the command line tool and read and the display the output. Because you type your input to emacs you can manipulate your input using the emacs commands. Using this mode emacs can be used with the following oracle tools like*

rman
lsnrctl
adrci
dgmgrl

There is a special sql-oracle mode that especially supports sqlplus. I worked with it long time ago. Today I did not find it in the documentation. I searched the web and but I found only the following link. Maybe you can find more when searching the emacs documents. There are other  sql modes for emacs. This page contains a reference to the link found by @Jack Douglas too.

A lot of people don't like emacs because they think that it is a complex tool.


What is the best practice for selecting from two tables with multiple relations? I have two tables with a structure like below:

and am selecting the data with below query:

SELECT
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = DN) DN,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = PN) PN,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = Winder) Winder,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = CouplingType) CouplingType,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = Type) Type,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = ILayer) ILayer,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = OLayer) OLayer
FROM Product_Coupling_Serial

So,what is the best practice for the best performance of selects like that (fastest way)?

Create a covering index on Id column and Include Name on the table Management_EnumerableItem.

CREATE INDEX IDX_Name on Management_EnumerableItem (Id) Include (Name) with (fillfactor=90);

and write it like this.

SELECT A.NAME,
B.NAME,
C.NAME,
D.NAME,
E.NAME,
F.NAME,
G.NAME,

FROM Product_Coupling_Serial P
LEFT OUTER JOIN Management_EnumerableItem A
ON A.ID = P.DN
LEFT OUTER JOIN Management_EnumerableItem B
ON B.ID = P.PN
LEFT OUTER JOIN Management_EnumerableItem C
ON C.ID = P.Winder
LEFT OUTER JOIN Management_EnumerableItem D
ON D.ID = P.CouplingType
LEFT OUTER JOIN Management_EnumerableItem E
ON E.ID = P.[Type]
LEFT OUTER JOIN Management_EnumerableItem F
ON F.ID = P.[ILayer]
LEFT OUTER JOIN Management_EnumerableItem G
ON G.ID = P.[OLayer]

Thanks

You should create separate indexes on Product_Coupling_Serial for each column you use on your joins (DN, PN, Winder).Then both the subquery and the left joins will be faster.

You should consider the index size of the table and insert speed.

I don't think SQL server will use a covering index in this scenario because each column is queried separately.