Would adding indexes to my foreign keys improve performance on this MySQL query? Consider the following query:

SELECT
  `locations`.`id` AS `location_id`,
  `locations`.`address`,
  `locations`.`lat`,
  `locations`.`lng`,
  `tickets`.`status_id`,
  `customers`.`name`,
  `tickets`.`id` AS `id`,
  `tickets`.`updated_at` AS `updated_at`,
  ( 3959 * acos( cos( radians('39.78222851322262') ) * cos( radians( `lat` ) ) * cos( radians( `lng` ) - radians('-86.16299560000004') ) + sin( radians('39.78222851322262') ) * sin( radians( `lat` ) ) ) ) AS `distance`
FROM `locations`
RIGHT JOIN `tickets`
  ON (`tickets`.`location_id` = `locations`.`id`)
LEFT JOIN `customers`
  ON (`tickets`.`customer_id` = `customers`.`id`)
WHERE `tickets`.`client_id` = '20'
AND
  (
    `customers`.`name` LIKE '%Mahoney%'
    OR `customers`.`email` LIKE '%Mahoney%'
    OR `locations`.`address` LIKE '%Mahoney%'
  )
HAVING `distance` < '5'
ORDER BY `distance`
LIMIT 200;

Using a profiling tool, I got this report:

Speed: 45.569 ms
Query analysis:
· Query: SIMPLE on tickets · Type: ALL · Rows: 160 (Using where; Using temporary; Using filesort)
· Query: SIMPLE on locations · Possible keys: PRIMARY · Key Used: PRIMARY · Type: eq_ref · Rows: 1
· Query: SIMPLE on customers · Possible keys: PRIMARY · Key Used: PRIMARY · Type: eq_ref · Rows: 1 (Using where)

This is a MySQL database. All the tables are InnoDB with utf8_unicode_ci. The primary keys on each table are called id and are int(11) with indexes on them.

Should adding an index on tickets.location_id and/or tickets.customer_id and/or tickets.client_id improve performance of this query at all?
Why or why not?
Are there any other fields I should consider indexing to improve the efficiency of this query?
Should I be explicitly defining my foreign keys in my database schema?

My thinking is that since I'm selecting from locations first, I would want an index on the foreign keys that are being referenced. I read here: indexes, foreign keys and optimization that MySQL requires indexes on all foreign keys. Does explicitly defining the foreign keys in an InnoDB schema improve performance? Sorry for being such a total n00b. Thanks for the help!

Yes, performance may be better if you add those indexes. However, with such a small number of rows, it's quite possible that full table scan is more efficient and optimizer choses not to use any indexes.
After adding indexes your execution plan will be different, to get a rough estimation of how effective the indexes are you can multiply "Rows" column for each line of output of explain
In general, indexes on fields which participate in filtering/join conditions/order/group by improve performance. You also need to take into account selectivity (how many distinct values you have) of the column; if it's too low , the engine will not use it except if it's covering index for a query.
Foreign key is a constraint; the main purpose of any constraint to enforce some restriction (referential integrity in case of FK). Thus, if you care about integrity of you data, you should add foreign constraint.

The fact that Mysql implicitly creates an index on FK column means better read performance, and bit worse insert/update/delete performance (because index itself has to be updated).

Finally,

  My thinking is that since I'm selecting from locations first, ....

is not absolutely correct. Physical processing is not the same as logical; optimizer decides in which order it will process tables involved (as you can see in your output, the engine first accesses tickets table) and what access method to use. You can control it to some extent with hints though...

*Side note. The way your WHERE clause written :

WHERE `tickets`.`client_id` = '20'
  AND
  (
    `customers`.`name` LIKE '%Mahoney%'
    OR `customers`.`email` LIKE '%Mahoney%'
    OR `locations`.`address` LIKE '%Mahoney%'
  )

makes your LEFT JOIN customers behave as INNER JOIN.*

Update
Never mind my side note, I didn't pay attention you have ORs with multiple tables.

I hope it was helpful.


Insert data in current table and update another table MySQL I'm having problem with inserting in purchase table and updating facility table. Let's say, user made a purchase with product_id and product_quantity.

When user hit submit, I want to insert product_id and product_quantity into purchase table. At the same time, updating facility table with product_id and product_quantity that associated with it.

Here is my code

<?php
include 'dbconn.inc.php';
include 'functions.inc.php';

$sql1 = "SELECT * FROM facilities";

$res = $mysqli->query($sql1);
$facilities = array();

while( $row = $res->fetch_array(MYSQLI_ASSOC) ){

$facilities[]['id'] = $facilities_id;
$facilities[]['product_id'] = $facilities_product_id;
$facilities[]['product_current_quantity'] = $product_current_quantity;
}

$id = $mysqli->real_escape_string ($_POST['id']);
$purchase_id = $mysqli->real_escape_string( $_POST['purchase_id'] );
$facility_id = $mysqli->real_escape_string( $_POST['facility_id'] );
$product_quantity = $mysqli->real_escape_string( $_POST['product_quantity'] );

$sql1 = "UPDATE facilities
    SET

`product_current_quantity` = '$product_quantity + $product_current_quantity'

    WHERE $facility_id = $facilities_id AND $id = $facilities_product_id ";

$sql = "INSERT INTO purchases
(
 `purchase_id`,
 `facility_id`,
 `product_quantity`,,
 `product_id`
 )
VALUES
(

 '$purchase_id',
 '$facility_id',
 '$product_quantity',
 '$id'

 )";

$ok = $mysqli->query( $sql );

$ok1 = $mysqli->query( $sql );

if( $ok && $ok1){
redirect( 'new_purchase.php' );
} else {
echo 'Couldn\'t add new purchase';
}

I did some research and I think I need to use triggers. But I never work with triggers before. Any helps would be great. Thank you!

Yes, a trigger could be a good approach, but first, you should look more closely at your code:

$ok = $mysqli->query( $sql );
$ok1 = $mysqli->query( $sql );

It seems fairly clear that the second line should be executing the $sql1 query, not re-executing the $sql query.

Your design also seems less than optimal, for a couple of reasons:

you're storing the same data in two different places in your database... the facilities table contains totals of product quantity, and that same information could be found by summing up the values found in the purchases table; information stored in more than one place is ripe for inconsistencies to creep in.
if changing the above isn't practical, consider also that you're fetching $product_current_quantity from the facilities table, then letting your application add that value to the value in the $product_quantity and then you're stuffing that value back into the facilities table... this practice will also cause problems for you soon, if it hasn't already.

What you should be doing in this case is letting the database update the values in place:

UPDATE facilities
   SET product_current_quantity = product_current_quantity + $product_quantity
 WHERE ...                       # ^^^^^ use the column, not a variable

The big problem here is one of improper handling of concurrency.  Assume two concurrent accesses involving the same facility/product:

Start with product_current_quantity of, for example, 5.  Request #1 will add 3 to the quantity and Request #2 will add 2 to the quantity... and these two requests happen at almost exactly the same time.  The total should now be 5 + 3 + 2 => 10, right? But...

request #1 reads product_current_quantity = 5
request #2 reads product_current_quantity = 5 (#1's update hasn't happened yet)
request #1 adds its local "5" + 3 and writes "8"
request #2 has already read "5" also, so it writes 5 + 2 => "7"
database value is now "7" instead of 10.

Letting the database do the math, and not your application, avoids this, because even if the two updates arrive at exactly the same time, each one will atomically modify the column's value, and the final result will be correct.

Additionally, this seems like a very appropriate place to be using transactions, because everything done inside a single transaction stands (COMMIT) or falls (ROLLBACK) together.

And, yes, a trigger would work, too:

# change the statement delimiter from semicolon to another symbol, such as "$$"
DELIMITER $$
DROP TRIGGER IF EXISTS purchases_ai $$
CREATE TRIGGER purchases_ai AFTER INSERT ON purchases FOR EACH ROW BEGIN

UPDATE facilities
   SET product_current_quantity = product_current_quantity + NEW.product_quantity
 WHERE facility_id = NEW.facility_id
   AND id = NEW.product_id;

END $$
# change the delimiter back
DELIMITER ;

In a trigger, the NEW and OLD aliases refer to the row in the table before and after the query that was just executed.  NEW applies only to INSERT and UPDATE triggers, and OLD applies only to UPDATE AND DELETE triggers.  Triggers can fire either BEFORE or AFTER the affected row is actually changed.  In the example above, this trigger fires AFTER INSERT -- immediately after the insert of the record into purchases.


Simple MySQL query randomly takes forever I'm having an issue on my server with a simple PHP send emails script that runs every minute via a cronjob. The script contains a MySQL query that runs very fast most of the time, but randomly will take "forever".

It feels like a similar problem to this: https://stackoverflow.com/questions/976739/mysql-query-randomly-takes-forever

At seemingly random times while the MySQL query is running the load on my server spikes from an average of 0.5 up to 8 and my entire server slowly grinds to a near halt. The query does eventually complete but sometimes it runs for over 10 minutes -- on a "normal" occasion the query completes in 0.2 seconds.

This is the query:

SELECT * FROM email_messages
WHERE time_created <= "2013-04-22 10:40:00" AND is_sent = 0 AND is_cancelled = 0
LIMIT 6

time_created is (should be) indexed - its purpose is to create a 10 minute delay before sending so emails can be cancelled if necessary.

I've isolated it to this query because my server trucks along nicely without the load average spikes when this cronjob is turned off.

My server is a VPS with 2GB of RAM. The database isn't huge (750mb in total) but the email_messages table being selected from is by far the largest at around 400mb.

Through research, I thought it was a MySQL query cache problem because query cache was turned on. But turning off query cache has not resolved the issue.

I turned off query_cache by setting query_cache_size = 0.

Any ideas at all as to what could be going on here?

[CONCLUSION]

I did an experiment by including a condition in the query using my primary key so that only the last 1000 table entries where being search through.

... AND id > 131000 ...

Running the cronjob with this condition resolved the issue, and further investigation in the mysql console with EXPLAIN revealed that the index on time_created  was not created as I had expected.

I now need to read up on how to use HeidiSQL to properly create indexes because HeidiSQL is telling me there is an index key on time_created but EXPLAIN says otherwise. This may be a trap for young players!

To speed up the execution of your query, you need to create a compound index:

ALTER TABLE email_messages
ADD INDEX idx1(is_sent, is_cancelled, time_created);

More information: Multiple-Column Indexes


Three Node Cluster that Allows For Two Failures We are creating a three-node three-instance cluster with SQL 2008 R2 (not cutting edge, but that is what the application supports) and think it should be able to maintain quorum with two physical nodes down.  What is the best way to configure this?

My manager has been pushing for a witness server that acts like an additional quorum vote on top of a node and disk majority.  That gives us a vote per node, a vote for the quorum disk, but is it possible to setup a witness server that receives a vote?

I thought a witness was for mirroring, which we have no plans to implement on this cluster.  I have been told that adding a fourth node is not a possbility, even if it is only a passive node.

If you want to be able to sustain a failover of two nodes within the failover cluster, then you'll need to ensure that you have five voters for quorum.

You currently have three nodes, and even if you were to add a disk witness, you'd still be at the same point:  whether you have three or four possible quorum votes, you'd still need to have three votes for quorum (you need to have more than half of the votes for quorum).

I've never heard the terminology of a "witness server" before, and I think that person is just trying to give a logical name to simply adding another node to your cluster.

In other words, if you now have a four node cluster with a disk witness, that'll be a total of five voters.  Five voters will allow you to sustain two nodes to fail (provided your disk witness continues to be functioning properly).

With that being said, you also need to answer a few questions for yourself:

Does you really need three separate instances in this cluster?  What is the driving force behind that?
Can one of your possible owning nodes for the FCI be able to sustain all three instances of SQL Server? (think about it, there could be a lot of serious contention there)
Do all of these instances need to be in the same cluster?

And I do want to echo what @AaronBertrand has said above in his comment on your question.  There is no such thing as an active / active [ / n [ / n ... ] ] cluster for SQL Server.  It's extremely misleading to think of it like that, and that terminology has traditionally caused a lot of confusion (not to mention, the basic problem of a wrong title for something).


Performance improvement after table analyze I used a function that contains 3 tables a,b, and c. Table a has an index ind1 on two columns.

I call a stored procedure that contains code and gets data using the above function. I analyzed my session it gives a direct path read event, and produces output in 1 minute.

When I analyze the above 3 tables, the procedure runs faster and generates data within 10 seconds. What is table analyze and what causes this performance difference?

Oracle's optimiser is clever, it makes decisions about how best to do certain things (e.g. joins) based on what the contents of the tables are.  At least, it can do if it knows what the contents of the tables are.

The optimiser uses table statistics to achieve this.  If it knows that there is a particular type of data, or a particular distribution of values, or any number of different things going on in the table, it can change the way it executes the query that's being asked of it.

If there are no statistics, the optimiser has to guess or use defaults, and while the optimiser's defaults may be suitable or not matter that much at small volumes of data, when the volume of data in the table increases or the skew changes, this can make a huge difference to how quickly a query runs.

By analysing the tables, the statistics for the tables are rebuilt, and the optimiser has up-to-date information on what the contents of the table are, and can make more informed decisions.

Have a look here (http://docs.oracle.com/cd/E25054_01/server.1111/e16638/stats.htm) for Oracle's documentation on optimiser statistics.


Is Minimum Canonical cover supposed to be singular? Is Minimum Canonical cover supposed to be singular?

For example when I calculate Fc for:

AC->BD
B->ACD

I get different results if I remove AC->D before B->D and vice versa, so can there be many covers?

In general different results can be produced, depending on the order in which the dependencies are considered for the elimination of extraneous attributes and redundant dependencies. This means that even the normalization algorithms, like the analysis algorithm for BCNF, can produce different results in certain cases.

A terminological note: in general the algorithms used for the normalization start by computing any canonical cover. A minimal canonical cover is a cover which has a number of dependencies less than that of equivalent covers. An optimal canonical cover is a cover that has less symbols of attributes than each other set equivalent to it. Different algorithms exists for the different type of covers, some of them are discussed in Maier, D. The Theory of Relational Databases, Computer Science Press, Rockville, Maryland, 1983.


when a table trigger fires, what user does the trigger execute as I have a table that I am using Insert/Update/Delete triggers to audit. I want to restrict access to the audit table so that only the triggers can write to it. What I am trying to find out is, what user would those triggers count as for inserting records into the audit table.

EDIT: Since phil showed me the documentation in his comment for how it is determined, this changes my question a little.

What is a good method to restrict access to the audit table so that only the triggers can insert records?

There is no need to elevate permissions with EXECUTE AS with ownership chaining. To ensure rows are inserted into the audit table only via the trigger, simply don't grant INSERT permissions on the audit table. As long as all the objects are owned by the same user (e.g. dbo), the ownership chain will be unbroken and permit the trigger to insert into the audit tables without direct permissions.


Are there tips for writing Oracle DDL Triggers for auditing? Or should I just use AUDIT? I've been tinkering with writing a trigger to log my table creation and manipulation for later tracking and review.

The challenge is that my trigger logic is stored in the same schema on which I've put the trigger.  Because of this, whenever I recompile the trigger or the package or whatever, the trigger itself fires. Also, if another component requires a recompile, the trigger fires.

The trigger has been failing and, in fact, locking my database session.  I have started putting guards on the trigger to prevent this and haven't worked it out yet.

Perhaps someone on the site has experience or advice?  Or just tell me to use AUDIT commands instead?

Why do you want to log DDL on your DB? just for auditing?
a trigger is highly not recommended because that if you have an error you can't even disable the trigger.
consider this scenario:

Trigger fails and cashes all DDL operation on the table
You try to disable the trigger
The trigger is invoked (back to step 1)

I suggest you'll take a look at Oracle DB vault and audit vault
http://docs.oracle.com/cd/E14472_01/doc.102/e14459/toc.htm

It got some really nice features for monitoring and controlling administration commands


How to count matching values and print 0 for non-matching value in PostgreSQL? I have two tables as follows:

create table first (f_id int,rank varchar(5), primary key (f_id));
create table second(s_id int,assigned int,f_id int,primary key(s_id),foreign key(f_id) references first(f_id));

Then I have inserted some records in these tables:

insert into first values (1,'A'),(2,'B'),(3,'C');
insert into second values(1,3,1),(2,5,2),(3,7,2),(4,1,2);

Now I want to select all records from these two tables where if first.f_id=second.f_id then it should count the assigned column's value, but if it does not match then it should return print 0.
My desired output would be:

rank         assigned
 A               1
 B               3
 C               0

How can I achieve this?

You need an outer join for this:

select f.rank, count(s.s_id) as assigned
from first f
  left join second s on f.f_id = s.f_id
group by f.rank
order by f.rank;

It's important to count the rows from the outer joined table. A simple count(*) would return the wrong values. count() ignores null values and the rows for which no match exists in the second table will have a null value in s.s_id and thus these rows are counted with 0


How can I copy data from one MySQL server to another based on a SELECT statement (then delete the data from the original)? I have a very large log table from which I want to copy rows, putting them into the same table structure on a new server.  I don't want to copy everything, only old rows, so that the table on the main server stays small-ish.  So I have to SELECT the data I want and only move (and delete) that.

Keep in mind that there is a lot of data, and I don't want to copy it all with a mysqldump if I don't need to, especially if it's going to lock the table while it's being copied.

The best I've come up with is a PHP script, which I will post as a tentative answer, although I'm certain it's not the best option.

You are looking for pt-archiver, part of the Percona Toolkit. The tool can purge rows from a table; write rows from a table to file; purge rows from a table while copying them to another table (possibly another server)...

It does so in chunks, so like 1,000 rows at a time, so that there is no high load on your server.


MongoDB Embeded Index we have a collection in below format:

{
    "_id" : {
        "a" : 8,
        "b" : ObjectId("5b23aa9e24826b82a2553ce7")
    },
    "field1" : "value1",
    "field2" : "value2",
    "field3" : "value3",
    "field4" : "value4",
}

as per my knowledge, MongoDB creates a default index on _id field. But here in this case I want to know how MongoDB will create index on _id; is it on both _id.a and _id.b field or just on _id.b field?
also if I create an additional compound index on (_id.a,field1,field2), will it create an additional overhead? or I need to create and index on only (filed1,filed2) as _id is already indexed field.

Please suggest.
Thanks!

But here in this case I want to know how MongoDB will create index on _id; is it on both _id.a and _id.b field or just on _id.b field?

The index will index the embedded document as a whole; i.e. the entirety of { "a" : 8, "b" : ObjectId(...) }. This is a different index entirely than one that would be built solely on _id.a, _id.b, or a compound index on both fields.

Note that querying on a nested document requires an exact match of the specified document. To illustrate:

> db.embed.insert({ "_id" : { "a" : 1, "b" : 2 } } )
WriteResult({ "nInserted" : 1 })

> db.embed.find( { "_id" : { "b" : 2, "a" : 1 } } )
>

> db.embed.find( { "_id" : { "a" : 1, "b" : 2 } } )
{ "_id" : { "a" : 1, "b" : 2 } }

If a single query will be filtering on _id.a, field1 and field2 (e.g. db.foo.find( { "_id.a" : 8, "field1" : "value1", "field2" : "value2" }) ) then then I would recommend building a compound index on the three fields, i.e. { "_id.a" : 1,  "field1" : 1, "field2" : 1 }.

Update with more info for comment:

The two queries are not functionally equivalent; the 2nd query will only filter on { "_id": {$lt:{"b":5} }, "field1": "value1" }. For example, try the following:

db.foo.insert({ "_id" : { "a" : 1, "b" : 1 } } )
db.foo.insert({ "_id" : { "a" : 2, "b" : 2 } } )
db.foo.insert({ "_id" : { "a" : 2, "b" : 1 } } )
db.foo.insert({ "_id" : { "a" : 1, "b" : 2 } } )

db.foo.find({
  "_id.a":{ "$gt" : 1 },
  "_id.b":{ "$lt": 2}
})

db.foo.find({
  "_id": { "$gt" : { "a": 1 } },
  "_id": { "$lt" : { "b": 2 } }
})

Note that queries that filter on multiple fields have an implicit logical AND as long as the fields being filtered on are different. The second query will effectively only be filtering on "_id": { "$lt" : { "b": 2 } } as there are two filters on _id. The $and filter would be needed for the logical AND to be used as intended in this query.

That being said, these two queries are asking different things. The first query is filtering on the embedded values "_id.a" and "_id.b":

> db.foo.find({
...   "_id.a" : { "$lt" : 2 }
... })
{ "_id" : { "a" : 1, "b" : 1 } }
{ "_id" : { "a" : 1, "b" : 2 } }
>

> db.foo.find({
...   "_id.b" : { "$lt" : 2 }
... })
{ "_id" : { "a" : 1, "b" : 1 } }
{ "_id" : { "a" : 2, "b" : 1 } }

Whereas the second query is asking to return documents where _the entire value of id is greater than / less than the value specified. This can return unexpected results:

> db.foo.find({
...   "_id": { "$lt" : { "a": 2 } }
... })
{ "_id" : { "a" : 1, "b" : 1 } }
{ "_id" : { "a" : 1, "b" : 2 } }
>
> db.foo.find({
...   "_id": { "$lt" : { "b": 2 } }
... })
{ "_id" : { "a" : 1, "b" : 1 } }
{ "_id" : { "a" : 1, "b" : 2 } }
{ "_id" : { "a" : 2, "b" : 1 } }
{ "_id" : { "a" : 2, "b" : 2 } }
> db.foo.find({
...   "_id": { "$gt" : { "b": 1 } }
... })
>


How should I handle measurement errors in a timeseries database? I have a table used to record measurements sampled at regular intervals on different sensors. Each row records the time, the identifier of the quantity being measured, and the value itself.

Now and again measurement errors occur and garbage is being recorded in the value field. How should I deal with these errors:

Delete the offending rows entirely, losing the information that there had been an error;
Keep the row as is, and asking of client code to deal with the errors;
Replace the values with NULL, losing the original erroneous value?

Or is there another option that I have not considered?

I would keep the data, if at any point in the future you want to find out what was wrong with it you still have it.

It can provide you with some information later on about how often measurements fail, where they originate from, what the offset is and things like that.

Deleting the data is unrecoverable, handling them in some way can always be changed later on. For example you could handle them in your client application for now and if you have some issue with that store them physically in a table_cleaned table.

If you deleted them or replaced them with NULL the information is gone for good.


Mirror / replicate only 1 table of a database I need a good way to replicate a single table in SQL Server 2012 between different servers and different databases. I tried replica which works just fine, but there might be a much better / easier way which I don't see right now.

Maybe there is a way to use the task to import data as a job. The data does not need to be synchronized all the time. It is fine that the second database [B] gets updated with the data from database [A] every 5-10 minutes.

Is it possible to setup a mirroring for only one table?

Of course I checked the web which gave me the idea to script the import task.

What else is possible and how?

You can use snapshot replication (it allows you to update the table every 5-10 minutes). If you would like instant changes, go for transactional replication.

Note that you cannot publish a replication on SQL Server Express, you need at least Standard edition.


expdp full export not exporting all schemas On Oracle 12c, fairly new database with APEX installed. I do a

 expdp \"/ as sysdba\"  directory=dp_dir full=y dumpfile=full.dmp logfile=full.log

And I am getting just a few tables from SYS and SYSTEM exported:

. . exported "SYS"."KU$_USER_MAPPING_VIEW"               5.882 KB      24 rows
. . exported "SYS"."AUD$"                                    0 KB       0 rows
. . exported "SYS"."DAM_CLEANUP_EVENTS$"                     0 KB       0 rows
. . exported "SYS"."DAM_CLEANUP_JOBS$"                       0 KB       0 rows
. . exported "SYS"."DAM_CONFIG_PARAM$"                   6.523 KB      14 rows
. . exported "SYS"."TSDP_ASSOCIATION$"                       0 KB       0 rows
. . exported "SYS"."TSDP_CONDITION$"                         0 KB       0 rows
. . exported "SYS"."TSDP_FEATURE_POLICY$"                    0 KB       0 rows
. . exported "SYS"."TSDP_PARAMETER$"                     5.945 KB       1 rows
. . exported "SYS"."TSDP_POLICY$"                        5.914 KB       1 rows
. . exported "SYS"."TSDP_PROTECTION$"                        0 KB       0 rows
. . exported "SYS"."TSDP_SENSITIVE_DATA$"                    0 KB       0 rows
. . exported "SYS"."TSDP_SENSITIVE_TYPE$"                    0 KB       0 rows
. . exported "SYS"."TSDP_SOURCE$"                            0 KB       0 rows
. . exported "SYS"."TSDP_SUBPOL$"                        6.320 KB       1 rows
. . exported "SYSTEM"."REDO_DB"                          25.58 KB       1 rows
. . exported "SYSTEM"."REDO_LOG"                             0 KB       0 rows
. . exported "SYS"."FGA_LOG$FOR_EXPORT"                      0 KB       0 rows
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS"               8.671 KB       4 rows
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS"           10.21 KB      22 rows
. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT"               5.953 KB       2 rows
. . exported "SYS"."DBA_SENSITIVE_DATA"                      0 KB       0 rows
. . exported "SYS"."DBA_TSDP_POLICY_PROTECTION"              0 KB       0 rows
. . exported "SYS"."NACL$_ACE_EXP"                       10.14 KB       3 rows
. . exported "SYS"."NACL$_HOST_EXP"                      6.976 KB       2 rows
. . exported "SYS"."NACL$_WALLET_EXP"                        0 KB       0 rows
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded

Yet I know I have tables and other objects in other schemas:

AtlasProd:SYS@extern01> select owner,
  2  decode(segment_type, 'TABLE', 'TABLE', '-other-') as segment_type,
  3  count(*)
  4  from dba_segments
  5  group by owner, decode(segment_type, 'TABLE', 'TABLE', '-other-')
  6  order by 1, 2;

OWNER                          SEGMENT_TYPE         COUNT(*)
------------------------------ ------------------ ----------
APEX_050000                    -other-                   646
APEX_050000                    TABLE                     155
AUDSYS                         -other-                     6
DBSNMP                         -other-                     4
DBSNMP                         TABLE                       2
GSMADMIN_INTERNAL              -other-                    15
GSMADMIN_INTERNAL              TABLE                       6
OUTLN                          -other-                     6
OUTLN                          TABLE                       3
SYS                            -other-                  1987
SYS                            TABLE                    1059
SYSTEM                         -other-                   321
SYSTEM                         TABLE                     124
XDB                            -other-                   655
XDB                            TABLE                      49

15 rows selected.

I see a workaround at Export pump all schemas except Sytem related schemas but I know I've used full=y before successfully.

This is expected behaviour.

FULL

  A full export does not, by default, export system schemas that contain
  Oracle-managed data and metadata. Examples of system schemas that are
  not exported by default include SYS, ORDSYS, and MDSYS.

12.1.0.1 EXPDP Does Not Export APEX Schema (Doc ID 1912162.1)

  This is the expected behavior in 12.1.0.X version,

  This was added, in part, to avoid problem with common objects in a
  multi-tenant DB, but certainly has been very helpful in eliminating
  errors  report - particularly in full import.  The bit is called
  'ORACLE_MAINTAINED' in [user,all,dba]_objects  dictionary views. Seems
  pretty reasonable that Oracle maintained objects are  not be moved
  around by datapump.


No permissions to the certificate file when trying to restore a DB from a third party I am trying to restore a Db in SQL server 16. It is a developer version installed on windows 10 laptop. We have a backup file from a SaaS instance. We have moved to a new instance of the SaaS and this is our archive from the first instance. This is strictly for archiving purposes. That said, the only was to read this of course is with SQL Server.

Script:

use master ;
 -- drop master key encryption by password ='somepassword';

-- create   master key encryption by password ='somepassword';

create certificate BULLHORN14852_45764 from file
    = 'C:\Program Files\Microsoft SQL Server\MSSQL13.LEVVEL\MSSQLBULLHORN14852_45764.cer'
with private key
  (file='C:\Program Files\Microsoft SQL Server\MSSQL13.LEVVEL\MSSQLBULLHORN14852_45764.pvk',
decryption by password = 'somepassword')

I was able to create the master key encryption no problem. When I run the create certificate command I get the famous

"The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it."

I am connected as the local administrator on windows. I have also granted permissions on the folders and the files.

NETWORK SERVICE account does have permission to the folder and the cert, pvk files.

When I look at the services and what the Logon as is I see the following.

SQL Server (LEVVEL) - logon as NT Service\MSSQL$LEVVEL

SQL Server (MSSQLSERVER) - logon as NT Service\MSSQLSERVER

SQL Server Agent (LEVVEL) - logon as NT Service\SQLAgent$LEVVEL

SQL Server Agent (MSSQLSERVER) - logon as NT Service\SQLSERVERAGENT

The granted permissions on the folder (and files ) are: NETWORK SERVICE and MSSQL$LEVEL. I can't find any user names starting with SQL or MSS or NT in order to add other users to the permissions.

I am not sure what I am missing.

I've been reading that these permissions above are not enough. I haven't administered a SQL DB in 15 years so I need some guidance. Any help would be greatly appreciated!

Rob.

It seems you have two SQL Server instances installed. LEVVEL and a default instance. Which one are we talking about? I.e., which one are you connected to?

Anyhow, both services uses a virtual service account, so privileges assigned to NETWORK SERVICE is irrelevant.

For the default instance, you should assign privileges to NT Service\MSSQLSERVER.

The the LEVVEL, you should assign privileges to NT Service\MSSQL$LEVVEL.

When you assign privileges, you need to type the above account name.


Check value when I have a list of elements separated by commas in a column? I am looking to return a value in a database.
Table named TIPOLOGY is structured like this:

Name   Owner   ID       Office  Type   LIST_OF_VALUES
Jones  Lasi      1       12345   TKT    AUD,PAX,CCF
Jones  Lasi      2       54321   TKT    AGT, FLT
Jones  Lasi      3       45689   TKT    IND,INV,CAS

I need to find the office and I have like information NAME, OWNER, TYPE and a VALUE.

So my basic query is:

SELECT Office FROM typology WHERE NAME=Jones AND OWNER=Lasi AND TYPE=TKT AND "AUD" in LIST.

Value AUD is only on one line, so it can return only one office.

However I don't know how to search for a value that is in a list inside the column LIST_OF_VALUES.

You can use the LIKE clause.

SELECT
    Office
FROM
    typology
WHERE
    NAME='Jones' AND
    OWNER='Lasi' AND
    TYPE='TKT' AND
    LIST_OF_VALUES LIKE '%AUD%'


need to correct this Query/Loop to find and start "new branch" in the output given these tables:

PERSONS
person_id   fname   lname      info
1          LB       Johnson   "info..."
2          Bobby    Baker     (null)
3          Malcolm  Wallace   (null)
4          Billy    Estes     (null)
5          Clifton  Carter    (null)
6          Howard   Hunt      (null)
7          Frank    Sturgis   (null)

ASSOCS
p_id    a_id    p_to_a      a_to_p
1       2      employs     worked for
1       3      knows       knows
3       4      workedwith  worked with
4       6      knows       knows
5       3      brother to  sister-in-law to
6       7      wife of     husband of
7       3      knows       knows
5       1      test        test_

The output (from the Query and Loops below) is this:

Lyndon B Johnson 1
   employs Bobby Baker 2
   knows Malcolm Wallace 3
      worked with Billy Sol Estes 4
         knows E. Howard Hunt 6
            wife of Frank Sturgis 7
               knows Malcolm Wallace 3
Clifton Carter 5 »
   brother-in-law to Malcolm Wallace 3
   test Lyndon B Johnson 1

I can see that it "chainlinks" 1 » 3 » 4, and because of this it's continuing the chain and does not start a new branch where I need it to (denoted here with » »).

(I'd also love it if someone could help me include this "info" field that I've added. I've made several attempts in the Query and PHP - i'll note them in the code at the bottom):

Lyndon B Johnson **
"info..."
**
   employs Bobby Baker
   knows Malcolm Wallace
      worked with Billy Sol Estes
         knows Howard Hunt »
» Howard Hunt
   wife of Frank Sturgis
Clifton Carter
   brother to Malcolm Wallace
      test LB Johnson

This fantastic snippet/query works well - almost. What I need to do is either find a new way to define the associations between 2 persons in my ASSOCS table in such a way that a new branch is defined, or get this query and loop to do so in some fashion. It would probably take both, I'm thinking, but I'm not good with arrays or this depth of php. (I hope php is appropriate here - it seems to me to still be administration of a db.)

There are also my (commented) attempts at including the new "info" field.

$sql = "SELECT a.p_id, a.a_id,
    CONCAT(p1.fname,' ',p1.lname) as name1,
    p_to_a as association,
    CONCAT(p2.fname,' ',p2.lname) as name2,
    /*** i tried p3.info **/
        FROM assocs a
        INNER JOIN persons p1 ON p1.person_id = a.p_id
        INNER JOIN persons p2 ON p2.person_id = a.a_id
        /** INNER JOIN persons p3 ON p3.info = a.p_id **/
    ";

$data = [];
$res = $db->query($sql);
while (list($pid, $aid, $n1, $ass, $n2, /** $nf **/) = $res->fetch_row()) {
    if (!isset($data[$pid])) {
        $data[$pid] =  [ 'name' => $n1, /** 'info' => $nf, **/ 'assocs' => [] ];
    }
    $data[$pid]['assocs'][$aid] = ['name' => $n2, /** 'info' => $nf, **/, 'rel' => $ass];
}

$processed=[];

function listAssociates($pid, &$data, &$processed, $level)
{
    if (!isset($data[$pid])) {
        return;
    }
    if (in_array($pid, $processed)) return; // prevent circular references
    $processed[] = $pid;

    // this is where a branch starts
    if ($level==0) {
     echo "
{$data[$pid]['name']} »
";
    }
        $indent = ($level*25);
        $indent = ($indent+25)."px";
        foreach ($data[$pid]['assocs'] as $aid=>$adata) {
            echo "
{$adata['rel']} {$adata['name']}
\n";
            listAssociates($aid, $data, $processed, $level+1);
        }
   /** I tried using various unset() or reset() here, to no avail **/
}

listAssociates(1, $data, $processed, 0);
listAssociates(6, $data, $processed, 0);
listAssociates(5, $data, $processed, 0);

very grateful for anyone's help. I really have tried hard to do this on my own.

SOLVED (yay!):

I simply put a Start ['s'] toggle, 1/0, in ASSOCS

ASSOCS
p_id    a_id   s   p_to_a      a_to_p
1       2      1   employs     worked for
1       3      0   knows       knows
3       4      0   workedwith  worked with
4       6      0   knows       knows
5       3      1   brother to  sister-in-law to
6       7      1   wife of     husband of
7       3      0   knows       knows
5       1      0   test        test_

and dug it out in my query (a.s AS s):

SELECT a.p_id, a.a_id,
    CONCAT(p1.fname,' ', p1.lname) as name1,
    p_to_a,
    CONCAT(p2.fname,' ', p2.lname) as name2, a.s AS s, a.info
        FROM assocs a
        INNER JOIN persons p1 ON p1.person_id = a.p_id
        INNER JOIN persons p2 ON p2.person_id = a.a_id

stored it and called it in place of the $level switch:

if (!isset($data[$pid])) {
        return;
    }
    if (in_array($pid, $processed)) return; // prevent circular references
    $processed[] = $pid;

    if ($data[$pid]['s']==1) {
     echo "
{$data[$pid]['name']}
";
}

/* was » if ($level==0) { */

right, I can't believe it worked, either.


Give alias / column definition list to ROWS FROM() This obviously doesn't work:
SELECT regexp_matches[1], regexp_matches[1]
FROM ROWS FROM (
  regexp_matches('fooBarBaz', '[[:upper:]]', 'g'),
  regexp_matches('fooBarBaz', '[[:lower:]]', 'g')
);

Error: [42702] column reference "regexp_matches" is ambiguous

How can I give an alias to the function calls?
SELECT u[1], l[1]
FROM ROWS FROM (
  regexp_matches('fooBarBaz', '[[:upper:]]', 'g'),
  regexp_matches('fooBarBaz', '[[:lower:]]', 'g')
) AS (u text[], l text[]);

Error: [42601] ROWS FROM() with multiple functions cannot have a column definition list
Hint: Put a separate column definition list for each function inside ROWS FROM().

SELECT u[1], l[1]
FROM ROWS FROM (
  regexp_matches('fooBarBaz', '[[:upper:]]', 'g') AS (u text[]),
  regexp_matches('fooBarBaz', '[[:lower:]]', 'g') AS (l text[])
);

Error: [42601] a column definition list is only allowed for functions returning "record"

I'm out of ideas. This has to be possible.
Documentation:

Syntax of ROWS FROM() (7.2.1.4. Table Functions)
regexp_matches returns setof text[] (Table 9.9. Other String Functions)

You need a table alias too
SELECT T.u[1], t.l[1]
FROM ROWS FROM (
  regexp_matches('fooBarBaz', '[[:upper:]]', 'g'),
  regexp_matches('fooBarBaz', '[[:lower:]]', 'g')
) AS t (u, l);


Can I use replication/mirroring to have a "Current" database and a "Full History" copy? I have a database that is used for current operations.  The amount of data is starting to get a bit to much for the applications using it.

Most of the data is older and is very rarely needed.

I am wondering if there is a way to configure SQL Server to keep a mirror of the original database, but allow me to occasionally purge out old data (and not have it purge from the copy).

NOTE: My database is currently SQL Server 2008 R2, but we will be upgrading to SQL Server 2012 soon.

Define the data is getting to be a bit much?  I've got clients with 1B+ rows in a table and no performance problems at all.

You could use SQL Server replication to replicate inserts and updates to a remote machine, but basically have it ignore the deletes.

Before I'd start looking at hacking together something like that I'd probably look at index tuning or query rewriting to see what I can do to resolve the performance problems with the queries that I've got.


How do I go from .xlsx to sql Server I'll try my best to explain this.
I've got a spreadsheet (.xlsx) with 220 rows and 15 columns. I need to make it into 3rd normalized form eventually, but how do I get the data from the spreadsheet into a database so I can start changing it? I've tried looking on Google but I can't find anything.

(If this is not where this question please link me to correct direction and I'll close the question)

You can use Excel to save the file as a .CSV (comma-separated-values), and use SQL Server Management Studio to import the file either into an existing table, or potentially a new table.

In SQL Server Management Studio, you select the desired target database, right-click the database name, then click 'Import Data', and go through the wizard step-by-step.


Setup Managed Backup failure I am trying to setup Managed Backup (in Azure SQL) using SQLServer  2014 (Ent 64bit) and  it is failing.

I have the credential created with Identity as the Storage account name and Password set with current Access key, I've tried both keys as well.

When I run through the GUI to Enable managed backup, set the retention period, add the SQL credential (which updates the Storage URL) and I press OK, I get the follow error message back:
Alter failed for

  SmartAdmin 'Microsoft.SqlServer.Management.Smo.ObjectKeyBase'.  (Microsoft.SqlServer.Smo)

  Failed to access the storage URL 'https://[storageaccountname].blob.core.windows.net/' using credential 'MyCredName'.
  Provide a valid SQL Credential for the storage URL. (Microsoft SQL Server, Error: 45207)

I downloaded Azure Storage Explorer from codeplex to test connection and it works, I can browse, upload, delete from my local machine.

I tried this from one of our on prem servers as well and same error.  What am I missing?

Issue identified.  The network admin didn't have the separate Azure domain environments trusted, although he said that he did.  I tried this and failed in our SQL DEV environment/domain in Azure, which isn't trusted.  I then, for sake of troubleshooting and communicating to the powers to be, tried it from our SQL QA environment/domain in Azure and it worked flawlessly!  The network admin compared the two environments and found the issue. Network issue after all.


Should running number be separated column from primary key in database? I have questions that blogged my mind on separating member number and member id.

Suppose that we have a database with member id primary key of Guid type. Then, normally, when we view the profile (on web page), we will display a Profile/100001 instead of Profile/{Guid-id}. So, in this case, we would have another column for member number which is separated from member id.

Now, assume that we have a case where Member ID is int datatype, and the running number of Member No is incremented 1 each time start from 100001, should we separate the member ID (primary key) from member number in this case?

This question is focus on web application database design. Because I got to "know" that exposing primary key to user is not a good practice. So, does that means that we should have an internal ID(primary key) that is used for database and another column with running number that is display to user?

Your Primary Key is a unique key and should be sufficient to find the records that you need.  However, if you need a sequential number for other identifying reasons, then YES you should make it a separate unique value.

I personally would skip the GUID key and just use the Integer, but that is a choice you will need to make.

Getting a sequential set of keys without gaps will require some code.  Are you using Microsoft SQL Server or some other version.  If MS SQL Server, look at:

http://msdn.microsoft.com/en-us/library/ff878058.aspx

This is a discussion of using Sequence Numbers, with some warnings about the limitations.


Login failed for user - Error: 18456, Severity: 14, State: 38 I am trying to run and ADODB command in MS Access 2012 to exec a stored proc in our SQL server 2008R2 database, but I keep getting error 38 and SQL profiler shows the following:

  Error: 18456, Severity: 14, State: 38.
  2015-01-28 12:59:27.34 Logon
  Login failed for user 'NordenDevel'. Reason: Failed to open the explicitly specified database. [CLIENT: 82.71.5.169]

This user (NordenDevel) login works on this database (NordenWeb) in SQL Server Management Studio and in Visual Studio, so the database is working fine and I do not see how it could be a permission issue.

Can anyone suggest what I could do to find out what's wrong?

Many thanks, YAOWSC

As mentioned by Aaron, you're login 'say' X would be pointing to  a different database rather than NordenWeb, when it throws error with state 38:

You need to check the login for any other mapped databases. To find correct error you need to enable the trace when above error is created:

To dig in deep do nslookup 82.71.5.169 from windows to find the host name, just to verify we are on the correct page:

Now to track the correct database the login is throwing error , start the trace with default template using below 2 category of events:

1.Security Audit

2.Errors and Warning

a) From the Security Audit category (which is automatically listed in the default trace),  keep the "Audit Login Failed" event.

b) From the Errors and Warnings category, only two events were chosen. First, the "ErrorLog" event - this would trap every instance the Error Log is accessed. The second and the most important event was the "User Error Message". This is the event that would capture the error returned to the client

For the columns, only five would be fine: TextData, ApplicationName, NTUserName, LoginName and SPID. Again, you may think the DatabaseID or DatabaseName columns would yield the required information

Finally,  choose  filter the NTUserName field  to determine the account name from the Error Log.

After running trace you will see error message saying:

"Cannot open database  requested by the login. The login failed."

From above error message you will get the correct DB name, for which login is throwing error 38.

Try capturing with trace and see if this works!


Load data local infile changing special character i am using load data local infile function to insert data from csv to database. Everything works fine. But the word CLOUTÉ is replacing with CLOUTÃ‰. I have no idea, how to prevent it.

I fixed it by using CHARACTER SET UTF8 option in my query.

$loadmeth = "'".'"'."'";
LOAD DATA LOCAL INFILE   '$current_date/dummycsv.csv'
       INTO TABLE tablename
       CHARACTER SET UTF8
       FIELDS TERMINATED BY ','
        ENCLOSED BY $loadmeth
       LINES TERMINATED BY '\r\n'
       IGNORE 1 LINES;


Finding keywords in SQL script I have a list of 50 columns in a database that are likely to change or be removed within the next few months. I have a task to identify any SQL scripts that reference these tables. Up until now, I have been doing this by eye however, I have come across some huge scripts that will take forever to use this method. I'm also getting a little sick and tired of doing it :)

I would like to be able to search through a script with the 50 table.column names and if there is a hit, I can do further, manual investigation.

Can anyone recommend a product? Freeware would be best as I don't really want to use my own money to pay for the solution.

ApexSQL Search - http://www.apexsql.com/sql_tools_search.aspx

free tools, search database object (for this tasks) - search code for columns name


Execute stored procedure remotely using linked server We have 3 SQL Server 2012 instances, 2 of them we're not db_owner. Some of our stored procedures use DMV and sys views and stored procedures that only work when executed locally.

I'd like to centralize the execution of these (our) stored procedures in our main instance, and from it execute these stored procedures remotely. I tried using SQL Server Agent jobs, but it requires some configuration I'm not comfortable doing, and probably won't have access anyway.

Is there any way to remotely execute a stored procedure, preferably using linked server?

Edit: we can use linked server, openrowset, anything that can be run inside simple SQL. I'd rather not use SSIS, because doing so would separate part of the process from simple SQL and require multiple platforms to run it.

Assuming the procedure lives on the remote server: have you tried using the EXECUTE AT command?

DECLARE @RunStoredProcSQL VARCHAR(1000);
SET @RunStoredProcSQL = 'EXEC [Database].[dbo].[StoredProcName]';
--SELECT @RunStoredProcSQL --Debug
EXEC (@RunStoredProcSQL) AT [LinkedServerName];
Print 'Procedure Executed';

That's what I have used successfully in the past.  I usually surround the declare/exec in a try catch just in case, but it's currently working on my systems every hour.


Managing 100s of similar databases We are migrating to a model where every "customer" lives in its own dedicated schema (database per tenant). This will allow us to later move some accounts to different DB clusters or upgrade specific accounts to newer schemas, for example. However from the get-go we need to automate how databases are managed in bulk or the whole effort will end up in smoke.

Additional info: the split is dictated by regulatory reasons and to scale out.

The number of schemas would be about 1000 (today), hosted in ~10 MySQL instances, so roughly 100 schemas per DB instance. New accounts would grow the system at a rate of 100 schemas-per-DB-instance.

My goal is to apply DevOps practices to database change management. So the schema lives in a git repo. Next, we need to be able to selectively or in bulk upgrade specific accounts to bring them to certain "schema version" that's in git.

Problem is figuring out what changes to apply to what databases, sort of like a diff and make CLI tools but for the database schemas.

I was thinking of creating a file per one of two database objects: a table and a stored procedure. Fortunately these are the only objects I need to track. Each object in the database has a corresponding file in the git. So if I change a definition in the file, I can run a "db-diff" (a tool I'd create) to tell which objects in what databases are outdated.

To bring the DB schema in sync with the git version of the same object would work something like this:

for stored procedures that's easy: DROP/CREATE; I'd utilize the SP's routine_comment field in the information_schema.routines to put there its current git tag. If the version there is outdated, replace it with the SP in the file in git.
for table objects, it is not clear to me how to compare what's in git vs in DB. One major annoyance of MySQL is that the show create.. result doesn't show exactly the definition that you asked for in ALTER/CREATE. For example, if you have a bigint column, you end up with the silly bigint(20). To a diff tool that's a different schema than what we have in the git.

Is this something I'd need to write myself from scratch or anything already on the market that you recommend?

I have a client with that has 11000 databases with each database containing 90-100 databases each.

This causes several problems:

It bloats the INFORMATION_SCHEMA badly. Memory takes a hit.

Apr 22, 2014 : Do Inactive MySQL Databases Consume Memory?
Apr 21, 2014 : Adding new tables -- memory usage increases
Jun 15, 2011 : How is INFORMATION_SCHEMA implemented in MySQL?

If you ever have to backup a database, it takes 3+ hours for Xtrabackup to read the entire instance's table and column info before it actually starts copying data.
Poor performance in one tenant database can hurt performance for the entire instance
Maintenance for one tenant database can hurt availability for the entire instance

Please see my old post : One Big Database vs. Several Smaller Ones. It compares a multitenant database to a multitenant building. Ask any landlord of a multitenant building and you will get the same sob story.

BACK TO REALITY

Managing a building with three tenants is better that managing a complex with 100+ units (NYCHA).

In terms of databases, you should shard the tenants into several independent clusters so that each DB instance holds a decent amount of data without gorging on RAM. Just keep in mind that the more tenant database you have, the more memory will be consumed. (BUYER BEWARE)


constraint view CASE values Below is a sscce to motivate the case.

I keep a table of persons:

CREATE TABLE person (name VARCHAR);
ALTER TABLE person add PRIMARY KEY (name);
INSERT INTO person (name) VALUES ('Bob'), ('Mike');

... and the encounters these persons have had with the law:

CREATE TABLE infraction (person VARCHAR, crimetype VARCHAR);
ALTER TABLE infraction ADD CONSTRAINT infraction_2_person FOREIGN KEY (person) REFERENCES person(name);

INSERT INTO infraction (person, crimetype) VALUES('Bob', 'MISDEMEANOR'), ('Bob', 'MISDEMEANOR'), ('Bob', 'FELONY');

Where crimetype in the table above would normally be a FK into a master table of crime types (omitted here to keep things short).

So Bob has committed two misdemeanors and one felony whereas Mike has a clean record.
The above two tables capture all the information I need.

Now I create a view that produces an overall assessment of that person, depending on the crime types they've committed and how many of them:

CREATE VIEW person_assessment AS
SELECT name, CASE
           WHEN NOT EXISTS(SELECT 1 FROM infraction b WHERE b.person = a.name)                          THEN 'exemplary'
           WHEN     EXISTS(SELECT 1 FROM infraction b WHERE b.person = a.name) AND
                NOT EXISTS(SELECT 1 FROM infraction b WHERE b.person = a.name AND b.crimetype='FELONY') THEN 'relatively law-abiding'
           WHEN     (SELECT COUNT(*) FROM infraction b WHERE b.person = a.name AND b.crimetype='FELONY')<=4 THEN 'felon'
           WHEN     (SELECT COUNT(*) FROM infraction b WHERE b.person = a.name AND b.crimetype='FELONY')>5 THEN 'career criminal'
           ELSE 'unhandled case'
          END AS citizen_type
FROM person a;

The above view produces:

name citizen_type
---- ------------
Bob  felon
Mike exemplary

My question are:

is the SQL code for the view above idiomatic or is there a better way to write it?
is there a way I can somehow constraint the citizen_type that the view produces so that I can make it reference a master table of citizen types with descriptions, etc. I somehow want to make it explicit that the values relatively law-abiding, felon and career criminal come from a controlled set of strings that other tables can cross-reference and I can assign some other properties on them (description for instance).

You could add a column significance to your crimetypes table, with increasing values for the crimes (ie. 1 for INFRACTION,2 for 'MISDEMEANOR', 3 for 'FELONY', etc.)

Then your query could be written as:

CREATE VIEW person_assessment AS
SELECT p.name,
       CASE COALESCE(MAX(c.significance), 0)
           WHEN 0 THEN 'exemplary'
           WHEN 1 THEN 'law-abiding'
           WHEN 2 THEN 'relatively law-abiding'
           WHEN 3 THEN CASE WHEN COUNT(CASE WHEN c.significance >= 3 THEN 1 END) <= 4
                                THEN 'felon'
                                ELSE 'career criminal'
                       END
                  ELSE 'unhandled case'
       END AS citizen_type
FROM person AS p
  LEFT JOIN infraction AS i
    ON i.person = p.name
  LEFT JOIN crimetypes AS c
    ON i.crimetype = p.crimetype
GROUP BY p.name ;

This is more of a way to have the query "tidied up", readable and easy to change in the future.

It may not be as efficient as your query though, depends on the data. If no person in the database has any crime stored, only the first WHEN of your case has to be run. If on the other side, most persons in the database have felonies, several correlated subqueries will have to be run so the above strategy will probably be more efficient as it uses only a join and a GROUP BY.

About question 2, you can always join this view to another table with descriptions.

SELECT
    ....
FROM person_assessment AS pa
  JOIN citizen_types AS ct
    ON ct. citizen_type = pa. citizen_type ;


How do I create a MySQL slave server of a bunch of MyISAM databases on a master I have a MySQL master server and slave became corrupt because of space issues on the master. Binary logs are potentially corrupt and I don't trust them. The databases are using MyISAM tables.

I want to create a new slave, but I can't afford to take the master down or lock the tables for a mysqldump.

Is there a way I can get a known place on the master to seed the slave without creating an outage? If not, what is the way to do this with the briefest outage?

Can you shutdown the master server long enough to copy the data folder to another location?

You don't have to perform a mysqldump. You could just shutdown MySQL on the master server and copy the 'data' folder (folder having your database files) to another location (or server). Next, start both servers with replication disabled (skip-slave-start). Next, issue a RESET MASTER and RESET SLAVE on both servers. Then do a CHANGE MASTER on the slave. Finally, start the slave. If you are using GTIDs, then remove the auto.cnf file on the SLAVE so that it generates a new UUID (auto.conf is usually stored in the data folder).

See this for step-by-step instructions: http://mysql-vsjr.blogspot.com/2015/09/how-to-clone-gtid-mysql-56-server-using.html


Inserting query result to another table hangs on "Copying to temp table on disk" on MySQL I started the process of inserting returned results to another table. The query groups the rows in respect of indexed IDs. This causes 149,000,000 rows to be decreased to 460,000 rows.

The query includes 3 table INNER JOINs, with each table having about 20,000,000 rows.

Further information, the process completes in about 12 seconds for a test file which has 1000 input rows, and returns 703 rows.

I started the query earlier ### we don't know when earlier is ###, but it is still running in the state: "Copying to temp table on disk" after 38000 seconds (10 and a half hours).

I think there is a problem during the insertion process. What am I probably doing wrong here? If it helps, the operating system of the computer is Windows 7, it has 3 GB RAM, an Intel Core2Duo 2.27GHz processor. ### you forgot to tell us details on the hard drive. One partition in, one out, same disk, same partitions, etc ###

Here's my query as it currently reads:

INSERT INTO kdd.contents
            (adid,
             descriptionwords,
             purchasedkeywordwords,
             titlewords)
SELECT t.adid,
       dt.tokensid,
       pkt.tokensid,
       tt.tokensid
FROM   kdd.training t
       INNER JOIN kdd.purchasedkeywordid_tokensid pkt
               ON t.keywordid = pkt.purchasedkeywordid
       INNER JOIN kdd.titleid_tokensid tt
               ON t.titleid = tt.titleid
       INNER JOIN kdd.descriptionid_tokensid dt
               ON t.descriptionid = dt.descriptionid
GROUP  BY adid;

When I see that expression Copying to temp table on disk, I see a cascading problem.
First of all, what would precede Copying to temp table on disk? It would be Copying to temp table.
Why is this the case?
There are two variables that dictate how big a temp table in RAM can be

tmp_table_size
max_heap_table_size

Imagine processing a query where mysqld has tmp_table_size configured at 32M. If the query being processed needs more that 32M to populate a temp table, look at what mysqld must do to the query and the temp table:

Suspend the query's execution
Open a temp table on disk
Migrate the 32M data already in the temp table in RAM over to the new temp table on disk
Continue the query's execution from where it left off, populating the temp table on disk

That's when the message Copying to temp table on disk appears (during step 3). Based on this and the step given, my guess is that you have tmp_table_size set way too high. The bigger the temp table is that is not enough to finish any phase of the query, the longer step 3 must take in the interim.
SUGGESTION #1
I would set tmp_table_size and max_heap_table_size as low as possible. That would force a temp table to go to disk faster.
SUGGESTION #2
If you have the ability to setup a RAM disk in Windows, try mapping tmpdir to that RAM disk.
I once suggested these things in StackOverflow.


Do we really need a file share witness? We have an Availability Group configured. Total number of nodes in the cluster is 3 (odd). All are in same subnet. Also we do have file share witness. Everything is working fine for now.

My question is: do we really need file share witness? And what should the ideal quorum configuration be?

As per my knowledge, it is not needed as we already have an odd number of nodes, so node majority quorum configuration should be fine and is recommended.

SQL Version: Microsoft SQL Server 2016 (SP2) Enterprise Edition
OS Version: Windows Server 2016 Standard

In my opinion , based on version of windows you have and especially using always on configuration i would recommend having a quorum configured. In your case its File share which seems OK or else you have the option of quorum drive witness.

More on this is very neately explained by @Brentozar here

  You don't have a choice as to whether or not to have quorum: Always On
  Availability Groups are built on top of Windows Server Failover
  Clustering, which requires quorum voting to understand who the primary
  is for a given AG at a given time.

  I think what you might be asking is whether or not you need an
  old-school quorum drive (like a Q drive on a SAN). These days, Windows
  Server Failover Clustering (WSFC) can either use a quorum drive
  witness, OR a file share witness.

  Windows Server 2012 R2 includes dynamic witness capabilities: if you
  have an even number of voters (like if 1 of your 3 cluster nodes goes
  down), then Windows will automatically add in your witness in order to
  be a tiebreaker. That's why you want to configure the witness (either
  a disk or a file share) ahead of time - Windows will automatically
  manage its voting rights whenever you have node failures or network
  failures.

Also refer to another part of the answer which should help you here

  In a 3 node cluster you can setup node majority as your quorum mode
  and then you can survive the failure of a single node within the
  cluster . That is in most cases sufficient. If your cluster spans
  multiple sites you might want to fiddle with the quorum-vote weight
  depending on your failover plans


Replication feature in SQL Server 2017 on Linux We have a running production using SQL-Server 2014 in production. It has replication running and partial subscribers in several data centers.
I had a request to evaluate SQL Server 2017 on Linux so it could serve for a new application running on Linux.
The issue is that it should be a subscriber as well and seems that Microsoft doesn't have replication in development yet.
Have someone already handled this issue of replication in any way?
Is Replication supported in SQL Server on Linux?
I opened a connect and perhaps some of you would vote for it.
Thanks in advance

At the moment, replication is not supported on Linux. However, according to the release notes, replication support will be added in a future release.

Release notes for SQL Server 2017 on Linux


DB cloning on daily basis I have a requirement from one of my teams which is database copy (cloning) from production to my test server on daily basis at a particular time.

I know this is a very simple process which every DBA would perform.

Should I use RMAN duplicate for cloning? Should I use Goldengate? Is there any way I can schedule the same without manual intervention?

Server - RHEL
Database - Oracle 12c/11g

This can be done in a few minutes with storage snapshots/cloning. If you can't do that, just go for RMAN duplicate. Both method can be scripted and scheduled to run automatically.


ISNULL on NTEXT causes fatal exception on SQL Server I have a weird error that occurs on a client's SQL Server 2005 box.  We have provided them with a dataview that is populated with a query with a CTE with several joins / subqueries that does some text value interpretation on an NTEXT field that contains an XML blurb

I know that we could have used used other field types and / or methods, but it is what it is for good reason that I hope I would not have to explain here.

The problem is that for some reason, this query creates a fatal exception on the SQL Server.

Herewith a condensed version of the business end of the actual T-SQL that causes the error

declare @@dtDateStart datetime, @@dtDateEnd datetime
set @@dtDateStart = getdate()
Set @@dtDateEnd = dateadd(year, 1, @@dtDateStart)
;with  qItems
    (date_created, SYSTEM_ID, LegalEntity) as
(   select i.date_created,
        i.externalKey as SYSTEM_ID,
        case when i.HasLegalEntity > 0 then
            substring(i.message, charindex('<LegalEntity>',i.message)+13, charindex('</LegalEntity>',i.message)-charindex('<LegalEntity>',i.message)-13)
         else '' end as LegalEntity
    from (select *, charindex('<LegalEntity>', message) as HasLegalEntity from dbo.INT_Error_Queue) as i
    where i.date_created between @@dtDateStart and @@dtDateEnd)
select
    QItems.date_created,
    QItems.SYSTEM_ID,
    Qitems.LegalEntity
from QItems
WHERE
(isnull(Qitems.LegalEntity, '') not in (select key_value from Special_app_Settings where key_name = 'EntityName'))

At first I suspected that it has something to do with the where clause, because the [key_value] on [Special_app_Settings] is of type nvarchar(4000), and [i].[message] on [INT_Error_Queue] is of type [ntext], but I am not sure whether that would cause the error I have.  If I remove the where clause, the error does not occur.

Herewith the dump from the SQL Server Log:

03/13/2013 10:30:24,Server,Unknown,A user request from the session with SPID 101 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
03/13/2013 10:30:24,Server,Unknown,Error: 17310<c/> Severity: 20<c/> State: 1.
03/13/2013 10:30:24,spid101,Unknown,External dump process return code 0x20000001.<nl/>External dump process returned no errors.
...
03/13/2013 10:30:20,spid101,Unknown,* BEGIN STACK DUMP:
03/13/2013 10:30:20,spid101,Unknown,*
03/13/2013 10:30:20,spid101,Unknown,* *******************************************************************************
03/13/2013 10:30:20,spid101,Unknown,SqlDumpExceptionHandler: Process 101 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
03/13/2013 10:30:20,spid101,Unknown,***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\SQLDump0039.txt
03/13/2013 10:30:20,spid101,Unknown,Using 'dbghelp.dll' version '4.0.5'

Does anyone have any idea as to WHY this occurs?

I think you might be a victim to problem I have seen before, introduced with Cumulative update for Service Pack 2. Is the compatibility level of your database 80 (SQL 2000)?

See Microsoft KB 938102 for Hotfix details, but I suggest you upgrade to the latest service pack (or definitely a later cumulative service pack).

Essentially the Query Engine is evaluating your where clause and creating joins using non-ANSI operators such as *=, you then fall foul of this Sql bug. The problem occurs if the following conditions are true:

The query references a view that uses non-ANSI outer join operators,
For example, the view uses the = operator or the = operator.
The column list of the SELECT statement in the view contains a
subquery.
The version of SQL Server 2005 is SQL Server 2005 SP2 or a later
version.
You set the database of SQL Server 2005 to use compatibility level
80.


Why is LIKE more than 4x faster than MATCH...AGAINST on a FULLTEXT index in MySQL? I'm not getting this.
I've got a table with these indexes
PRIMARY     post_id
INDEX       topic_id
FULLTEXT    post_text

Table has (only) 346 000 rows. I am trying to perform 2 queries.
SELECT post_id
FROM phpbb_posts
WHERE topic_id = 144017
AND post_id != 155352
AND MATCH(post_text) AGAINST('http://rapidshare.com/files/5494794/photo.rar')

takes 4.05 seconds while
SELECT post_id
FROM phpbb_posts
WHERE topic_id=144017
AND post_id != 155352
AND post_text LIKE ('%http://rapidshare.com/files/5494794/photo.rar%')

takes 0.027 seconds.
EXPLAIN shows that the only difference is in possible_keys (fulltext has post_text included, LIKE does not)
That's really strange.
What's behind this? What's happening in the background? How can LIKE be so fast when not using index and FULLTEXT so slow when using its index?
UPDATE1:
Actually it now takes about 0.5 seconds, maybe table was locked, but still, when I turn on profiling it's show that FULLTEXT INITIALIZATION took 0.2seconds. What's up?
I can query my table with LIKE 10x a second, with fulltext only 2x
UPDATE2:
Surprise!
mysql> SELECT post_id FROM phpbb_posts WHERE post_id != 2 AND topic_id = 6 AND MATCH(post_text) AGAINST ('rapidshare.com');
Empty set (0.04 sec)

so I'm asking, how is this possible?
Additionally,
SELECT count(*) FROM phpbb_posts WHERE MATCH(post_text) AGAINST ('rapidshare.com')

is really slow. Can be fulltext any broken?
UPDATE3:
What the hell?
SELECT forum_id, post_id, topic_id, post_text  FROM phpbb_posts  WHERE MATCH(post_text) AGAINST ('rapidshare.com') LIMIT 0, 30;

takes 0.27s while
SELECT count(*) FROM phpbb_posts  WHERE MATCH(post_text) AGAINST ('rapidshare.com') LIMIT 0, 30;

takes more than 30 seconds! What is going wrong here?

I think the problem may stem from the presence of the FULLTEXT index itself.

Every time there is a query involving a FULLTEXT index, the MySQL Query Optimizer tends to whack the query into a full table scan. I have seen this over the years. I also wrote an earlier post about this most trifling behavior in FULLTEXT indexes.

You may need to do two things:

refactor the query so that the FULLTEXT index does not throw the MySQL Query Optimizer into a state of confusion
Add an additional index that will properly support the refactored query

REFACTOR THE QUERY

Here is your original query

SELECT post_id
FROM phpbb_posts
WHERE topic_id = 144017
AND post_id != 155352
AND MATCH(post_text) AGAINST('http://rapidshare.com/files/5494794/photo.rar')

You will need to refactor the query like this:

SELECT subqueryA.post_id
FROM
(
    SELECT post_id FROM phpbb_posts
    WHERE topic_id = 144017
    AND post_id != 155352
) subqueryA
INNER JOIN
(
    SELECT post_id FROM phpbb_posts
    WHERE MATCH(post_text) AGAINST('http://rapidshare.com/files/5494794/photo.rar')
) subqueryB
USING (post_id);

CREATE A NEW INDEX

You will need an index to support subqueryA. You already have an index on topic_id. You need to replace it as follows:

ALTER TABLE phpbb_posts ADD INDEX topic_post_ndx (topic_id,post_id);
ALTER TABLE phpbb_posts DROP INDEX topic_id;

Give it a Try !!!

UPDATE 2012-03-19 13:08 EDT

Try this one first

SELECT post_id FROM
(
    SELECT * FROM phpbb_posts
    WHERE topic_id = 144017
    AND post_id != 155352
) A;

If this runs fast and return a small number of rows, then try this nested subquery:

SELECT post_id FROM
(
    SELECT * FROM phpbb_posts
    WHERE topic_id = 144017
    AND post_id != 155352
) A
WHERE MATCH(post_text) AGAINST('http://rapidshare.com/files/5494794/photo.rar');

UPDATE 2012-03-19 13:11 EDT

Compare the running time of this:

SELECT count(*) FROM phpbb_posts  WHERE MATCH(post_text) AGAINST ('rapidshare.com') LIMIT 0, 30;

with this

SELECT count(*) FROM phpbb_posts WHERE 1 = 1;

If there running time is the same, then the MATCH clause is being executed on every row. As I mentioend earlier, using FULLTEXT indexes tends to nullify any benefits attempted  and contributed by the MySQL Query Optimizer.


Looking for a query that eliminates near-repeats I have a database with four rows: county_a, county_b, flow_a_to_b, and flow_b_to_a. Essentially, the data is repeating in other rows, but the values are switched around. Here's an example:

Entry 1: Baltimore County, Baltimore City, 10, 1

Entry 2: Baltimore City, Baltimore County, 1, 10

These entries are technically different but they give me the same information. What kind of query could I write to eliminate the second value while keeping the first?

My sense is that you're doing something wrong and need better tooling like PostGIS and PgRouting just from the problem you're describing but perhaps something like this will work,

SELECT DISTINCT
  greatest(name1, name2),
  least(name1, name2),
  greatest(x1,x2),
  least(x1,x2)
FROM tbl;

If you're using PostGIS then you can use spatial equality,

SELECT max(coalesce(t1.name,t2.name))
FROM table AS t1
JOIN table AS t2
  ON ST_Equals(t1.line,t2.line)
GROUP BY ST_Equals(t1.line,t2.line);

If you're using PgRouting none of this matters because the edges become the same anyway. Edges are bidirectional.


MySql show content of ibdata1 file, block rising exponentially, why is it growing so fast? I have a database with most of tables being stored on the InnoDB engine, however an alert raised the other day showing up that the ibdata1 file was growing exponentially.

For what I searched so far, there is no way I could resize nor shrink this file rather than start fresh again and rebuild the entire database.

However, I've read that some of the cases where this file rises too fast is when there are several transactions that might not be committing fast enough for the database to manage the file but letting it grow.

Is there a way to see what is inside this file in order to know what is taking most of the space?

Besides, is there a way to put a limit on this file so we can control when rises and when not?

InnoDb ruby is a great tool which does exactly what you want.

Troubleshoot

If you don't use innodb_file_per_table this can also be normal data growth so confirm this first.

If you use innodb_file_per_table then it is most likely indeed the undo history. You can confirm that by running:

mysql root@192.168.99.100:(none)> pager grep History
PAGER set to grep History.
Time: 0.000s
mysql root@192.168.99.100:(none)> show engine innodb status\G
History list length 0
Time: 0.001s

You can also check it with innodb ruby:

innodb_space -s ibdata1 undo-history-summary

This command will give you the number of pages used by undo history:

 innodb_space -s ibdata1 undo-history-summary |grep -v Page | awk '{print $1}' | sort | uniq | wc -l

Any of these will only reports current data. Unless you have a consistent high load with many long open transaction where the purge thread is unable to keep up you will mostly have free pages. It may be beneficial to plot this over time so you have an understanding of what your server is doing.

Fix

Shrinking ibdata is not possible. The only thing you can do is to dump and restore your data via mysqldump (or any other equivalent tool of your choice).

Other considerations

Since 5.6.3 it's also possible to separate your undo tablespace from the main ibdata file (https://dev.mysql.com/doc/refman/5.6/en/innodb-undo-tablespace.html). There may be some interesting thing for you.

In 5.7.5 innodb_max_undo_log_size parameter was introduced.


Postgresql get rows based on minimum value Having this table:

warehouse_id
destination_id
days

1
1
2

1
1
3

2
1
3

2
1
4

3
1
5

3
2
5

1
2
2

2
2
3

I'd like to get the warehouse_id and days for each value of warehouse_id, where the row has the minimum value of days, and destination_id matches a specific value, ordered by days.
For example for destination_id = 1

warehouse_id
days

1
2

2
3

3
5

Here is the query which orders by first for warehouse_id and days then gets first record only according to DISTINCT ON clause

select distinct on (warehouse_id)
    warehouse_id,
    days
  from this_table
  where destination_id = 1
  order by warehouse_id, days

warehouse_id | days
-----------: | ---:
           1 |    2
           2 |    3
           3 |    5

db<>fiddle here


Backing up MySQL database I have quite large and rather important DB to backup.

First off, whats best way to do it?

Secondly, easiest way I know is to export it through phpmyadmin, but, i'm worried about options, although I'm quite sure this "Add DROP DATABASE" will not drop actual databases, I'm interested what are these options for? (see pic)

Thanks.

The best (and IMO the only suitable) tool for backuping a MySql database is mysqldump

The PhpMyAdmin options you are referring to are meant only to alter the generated statements in your dump (but will not touch your actual data). You might want to refer to this FAQ at PhpMyAdmin.


Optimize INSERT performance with a UNIQUE constraint on md5(JSONB_COLUMN::text) I have the following table and indices defined:

CREATE TABLE IF NOT EXISTS data (
    id serial,
    job_id bigint NOT NULL,
    payload jsonb NOT NULL,
    tags jsonb NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_data_content on data (md5(payload::text));
CREATE INDEX IF NOT EXISTS idx_data_tags ON data USING GIN (tags jsonb_path_ops);
CREATE INDEX IF NOT EXISTS idx_data_jid ON data USING btree (job_id);

In that table, I store many JSON blobs, which are associated with a job_id (also available in payload->>'jobId') and a time stamp (unix epoch) stored in payload->>'__time'. I want to avoid storing duplicate JSON blobs, so I'm using this method as a UNIQUE constraint. After a few hundred thousand rows in the DB, however, INSERT performance starts to decline, which does not happen without the UNIQUE constraint.

It is highly unlikely but possible to have two different JSON blobs with the same job_id and payload->>'__time'. Is there any way I can make use of that, for example, by defining a UNIQUE constraint that first checks the job_id, then payload->>'__time', and only if both match actually checks the md5(payload::text) index?

(Note, that the keys in my JSON blobs are always in the same order for the same documents.)

If the uniqueness constraint is the only issue (and I'm interested to learn more about why in the discussion above), here's an idea:

remove the uniqueness constraint
when you do reads (selects), do order by id asc limit 1 so that you ignore duplicates
have some sort of parallel process going through the table periodically and removing duplicates


Oracle driver and language specific chars I am developing an Excel / VBA - Application that connects to an Oracle database (v 11.2.0.4) instance. I need to query a customers table and search by name. Customers names will contain german umlaut chars (äöüÄÖÜ and ß). I issue sql queries like "... where c.custname = 'Müller'".

The behaviour I am observing is that on my dev system all works well as well as on several users' systems. On few other systems, the application fails with "no customers found", my own error message if the name lookup mentioned before fails (which should not happen as it works at another system).

Further investigation using the PL/SQL Developer tool revealed that on the affected systems the names for customers are returned without umlaut. "Müller" becomes "Muller". However, querying for "Muller" doesn't return anything - so "Müller" is really converted to "Mu[unprintable char]ller" - I guess.

I am quite familiar with Microsoft SQL Server, Versions, Languages, Drivers... but for Oracle I have no experience at all. Can someone advice where / how to check what driver version is installed on a system, how it is configured (NLS-settings?) and how to make sure all systems are set up the same, in this aspect?

Is there a way to totally clean a system of all oracle related drivers, files and registry settings to get a clear start and have only one, the most recent driver, available?

Further information (to make things worse):

My VBA-macro uses a base class which is in use "forever" in my company. It connects through OLEDB and builds it's connection string using "Provider=msdaora.1" meaning it is using the Microsoft provided Oracle driver intended for Oracle around version 7 (?).
When using PL/SQL Developer to test, it takes a "real" oracle driver installed. For my system, there is a choice from "10.2.0/client_1", a "12.2.0/client" and "12.2.0/client_2"

I am going to try and compare my Microsoft Oracle driver to the non-working systems, just to see what it is.

I will try to upgrade the base class to stop using the obsolete Microsoft driver and use the recent oracle driver. However, manual queries using PL/SQL Developer showed the same basic problem... therefore I am not sure I will solve the issues.

I'd appreciate any idea where to look, what to remove or install or any other idea!

Indeed, the OLEDB provider from Mircrosoft is deprecated for ages and you should not use it anymore. Certainly it can have problems when you work with Unicode.

Use the OLEDB Provider from Oracle. After installing it, it should be sufficient to modify just the connection string from connectString = "Provider=MSDAORA;Data Source=... to connectString = "Provider=OraOLEDB.Oracle;Data Source=....

OLEDB Provider from Oracle always use UTF-16 (see OraOLEDB Provider Specific Features), so german "Umlaute" should be no problem.

Note provider msdaora also uses local Oracle Client installation (like OraOLEDB.Oracle), you cannot use it without any Oracle Client installation.

In order to verify properly what is stored at database level try

select DUMP(c.custname, 1016) FROM ...


PostgreSQL 10 Identity Column gets "null value" when inserting multiple rows with default keyword I recently upgraded from PostgreSQL 9.5 to PostgreSQL 10. One of the nifty features in PostgreSQL 10 is the new identity column type, an alternative to PostgreSQL' serial pseudo-type. Official documentation for identity column can be found one the CREATE TABLE page.

However, when inserting multiple rows into a table with a GENERATED BY DEFAULT AS IDENTITY column and using the keyword DEFAULT to get the next ID value, the default value is coming back as null.

For example, let's say I have a table

CREATE TABLE test (
  id int GENERATED BY DEFAULT AS IDENTITY,
  t text
);
CREATE TABLE

Inserting a single row with the DEFAULT keyword seems to work fine.

INSERT INTO test (id, t) VALUES (DEFAULT, 'a');
INSERT 0 1

Inserting multiple rows does not.

INSERT INTO test (id, t) VALUES (DEFAULT, 'b'), (DEFAULT, 'c');
ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, b).

Inserting multiple rows using an implicit default also works.

INSERT INTO test (t) VALUES ('d'), ('e');
INSERT 0 2

The problem specified above does not appear to be present when using the SERIAL column pseudo-type.

CREATE TABLE test2 (
  id SERIAL,
  t text
);
CREATE TABLE

INSERT INTO test2 (id, t) VALUES (DEFAULT, 'a'), (DEFAULT, 'b');
INSERT 0 2

So my question is: am I missing something? Is the DEFAULT keyword just not expected to work with the new identity column? Or is this a bug?

This is in fact a bug. I verified it. I went to go see if it was filed and it seems it already is. It's not just filed, the commit is there.

You can see their test, exactly like yours

+-- VALUES RTEs
+INSERT INTO itest3 VALUES (DEFAULT, 'a');
+INSERT INTO itest3 VALUES (DEFAULT, 'b'), (DEFAULT, 'c');
+SELECT * FROM itest3;

So just wait, it's there for PostgreSQL 10.2.

Possible work around for PostgreSQL < 10.2

If you absolutely must have this, and using the implicit column isn't acceptable. One easy solution would be to retrieve the sequence with the catalog info function

pg_get_serial_sequence(table_name, column_name)

Which I believe should work, and to set that as a default.

ALTER TABLE ONLY test
  ALTER COLUMN id
  DEFAULT nextval('seqname');


Does index rebuild time depend on the fragmentation level? Is the required time for index rebuild dependent on the level of fragmentation?

Does the rebuild of a 80% fragmented index approximately take 2 minutes if the rebuild of the same index fragmented 40% takes 1 minute?

I am asking for the RUNTIME (for example in seconds) that may be required to perform the required action, not about which action is required in what particular situation. I am aware of basic best practices when index reorg or rebuild / statistic updates should be done.

This question does NOT ask about REORG and the difference between REORG and REBUILD.

The background: Due to setup of different index maintenance jobs (each night, heavier job at the weekends...) I wondered if a daily "light intense" OFFLINE index maintenance job should be better performed on low-middle fragmented indexes to keep the off-times small - or does it not even matter and the rebuild on a 80% fragmented index might take the same off-time as the same operation on the same index 40% fragmented.

I followed the suggestions and tried to find out myself what is going on. My experimental setup: On a test server doing NOTHING else and not being used by anyone or anything else I created a table with a Clustered Index on a uniqueidentifier primary key column with some additional columns and different data types [2 numerics, 9 datetime, and 2 varchar(1000)] and simply added rows. For the test presented I added about 305,000 rows.

Then I used an update command and randomly updated a range of rows filtering on an integer value and changed one of the VarChar Columns with a changing string value to create fragmentation. After that I checked the current avg_fragmentation_in_percent level in sys.dm_db_index_physical_stats. Whenever I created a "new" fragmentation for my benchmark, I added this value including the physical_page_count value to my recordings the following diagram is made of.

Then I Ran: Alter index ... Rebuild with (online=on);
and grabbed the CPU time by using STATISTICS TIME ON into my recordings.

My expectations: I expected to see at least the indication of a kind of linear curve that show a dependency between fragmentation level and cpu time.

This is not the case.
I am not sure if this procedure is really appropriate for a good result. Maybe the number of rows / pages are too low?

However the results indicate that the answer to my original question definitely would be NO.
It looks like the required cpu time SQL Server needs to rebuild the index is neither depending on the fragmentation level nor depending on the Page Count of the underlying index.

The first chart shows the cpu time required to REBUILD the index in comparison to the previous fragmentation level. As you can see the average line is relative constant and there is not at all a relation between fragmentation and required cpu time observable.

To respect the possible influence of the changing number of pages in the index after my updates that could require more or less time to rebuild, I calculated FRAGMENTATION LEVEL * PAGES COUNT and used this value in the second chart that shows the relation of required cpu time vs. fragmentation and page count.

As you can see, this also does not indicate that the required time to rebuild is influenced by the fragmentation even if the number of pages varies.

After making those statements I guess my procedure must be wrong because the cpu time required to rebuild a huge and highly fragmented index then might only be influenced by the number of rows - and I do not really believe in this theory.

So, because I really and definitely want to find this out now, any further comments and recommendations are very welcome.

For everyone interested, I have created a chart showing the index REBUILD duration of about 2500 index rebuilds within couple of weeks in relation to the fragmentation of the index and it's size in pages.

This data is based on 10 SQL Servers, hundreads of tables and on Ola Hallengren's optimizing procedures. The general threshold for rebuilding is set to 5% fragmentation.

I have cut off some of the the largest tables (10 Mi + Pages) in this statistics to make it more readable.

The chart shows the required time (duration) as size of the bubbles. The biggest bubble's values are about 220 seconds.
It shows that the required time to rebuild an index is not really related to the fragmentation. Instead it seems to be more depending on the number of pages the index has. Also it indicates that low-level fragmentation is more time-consuming than higher fragmentaion.

The second chart is just zoomed into the area <= 200 K Pages. It shows the same, it takes longer for larger indexes, not for more fragmentation.


SQL Server weird issue with indexes and query planner We had a bunch of crashes and restorations a few days ago and following those, the SQL Server DB has been acting strangely. We know that there were some issues with the failover clusters due to which we've had to boot the servers again to finally get the database seemingly working.

One of the problems following that is that, we ran a big script that dynamically dropped existing indexes and re-created them with the only difference being that they were now filtered with WHERE column NOT NULL. For some reason however, when I pick the SCRIPT INDEX -> CREATE TO -> NEW QUERY WINDOW from SSMS Object Explorer, it offers the basic index creation script where the index is NOT filtered. When our customer with full permissions does the same, the creation script correctly shows that it's filtered.

Could this be a permission issue (didn't find any such on Google), or is there a possibility that while the script logged the changes correctly, the nodes or the query optimizer or whatever is somehow out of sync?

Similarly, several queries that previously performed fine (and still perform fine in a different DB that's a copy of this one), now show through execution plan that they're behaving differently. One of them has for example the following issues:

By default, execution planner shows that SQL Server uses the wrong index, producing millions of rows in the nested loops as opposed to 2 as it should.
Another index is correct, but produces millions of nested loops where in the copy of this server we only get 3.
When the copy is forced to use the same wrong index as the problem DB in section 1, it still only returns 2 rows in nested loops.

What could possibly be the cause of these issues and how to even begin to diagnose the problem? As said, the databases are copies of each other. The only difference is that the problem DB crashed where the DB was moved to a failover cluster and was then returned to the correct node again. The indexes are not fragmented, the statistics have just been updated, and there seems to be an extreme load from SQL Server's query planner.

I would really appreciate some expert advice on what could be the cause and how I would go about to diagnose the actual problem, thanks.

For some reason however, when I pick the SCRIPT INDEX -> CREATE TO -> NEW QUERY WINDOW from SSMS Object Explorer, it offers the basic index creation script where the index is NOT filtered.

"For some reason" indeed. So weird and strange. You probably have "Script for server version: SQL Server 2005" set in SSMS Tools > Options > SQL Server Object Explorer > Scripting:

2005 didn't support filtered indexes, so it leaves out the WHERE clause on purpose. Make sure that is set to 2008 or better and try again.

For the queries that are performing differently, are you sure the databases are identical? Do they use these indexes, that are filtered on one system and not on the other? Are both databases set to the same compatibility level? Have you updated statistics on the copy? Have you performed any index maintenance? Has the data skewed? Have you tried recompiling the queries with the largest behavioral changes?

(These are all formed as questions but you can interpret them as rhetorical and quasi-suggestions.)


Permanently delete SQL server database - anti recovery I rent a server from a server provider. And one day I'm no longer rent it. I have some important database on the server need to wipe out so no one can see it. Is that safe to just right click on the database and select Delete? Is there any tools or method that able to recover a deleted database?

There are several ways the database could be recovered:

Database Backups. You need to make sure you're removing all database backups as well by shifting them onto on-premises storage and securely deleting on the rented server.
Server Backups. Your provider is possibly providing server-level backups. These would have older copies of the databases on them, so would need to be securely deleted.
Data Recovery on the underlying storage. The deleted files on physical storage may still be recoverable, so you need to ensure that the storage is being securely deleted. This usually involves zeroing the disks multiple times to securely delete the data.

You should check with your provider regarding guarantees for secure data deletion when you exit the service to see if that meets your requirements.


fetch data from two tables I have two tables one for property detail and other for amenities. In amenities i have inserted data one by one means it has three column amenity_id,property_id,amenity. Now I want to get the property which amenities are kitchen and bedroom and Breakfast and internet etc. My created Query is like:

SELECT
    property.*,
    property_type.property_type
FROM property,property_type,apartment_amenity
WHERE property.approval='Y'
    AND property.propertytype = property_type.property_type_id
    AND property.country like '%india%'
    AND (property.city like '%indore%' OR property.state like '%madhya pradesh%')
    AND property.Maximum_Capacity >=1
    AND property.pid = apartment_amenity.property_id
    AND apartment_amenity.amenity_id in (1,2,3)
ORDER by is_online_bookable DESC
LIMIT 0,10;

What you need to do is refactor the query as follows:

Subquery #1 : Collect all the property_id values that have all 3 amenities.

SELECT property_id,COUNT(1) amenity_count
FROM apartment_amenity
WHERE amenity_id IN (1,2,3)
GROUP BY property_id
HAVING COUNT(1) = 3;

Subquery #2 : Collect pid and propertytype values from property table by address

SELECT property_id FROM property
WHERE approval='Y'
AND country like '%india%'
AND (city like '%indore%' OR state like '%madhya pradesh%')
AND Maximum_Capacity >=1;

Subquery #3: Join Subquery #1 and Subquery #2

SELECT B.pid,B.propertytype FROM
(SELECT property_id,COUNT(1) amenity_count FROM apartment_amenity
WHERE amenity_id IN (1,2,3) GROUP BY property_id HAVING COUNT(1) = 3) A
INNER JOIN
(SELECT pid,propertytype FROM property
WHERE approval='Y' AND country like '%india%'
AND (city like '%indore%' OR state like '%madhya pradesh%')
AND Maximum_Capacity >=1) B

Impose ORDER BY and LIMIT 10 on Subquery #3

SELECT B.pid,B.propertytype FROM
(SELECT property_id,COUNT(1) amenity_count FROM apartment_amenity
WHERE amenity_id IN (1,2,3) GROUP BY property_id HAVING COUNT(1) = 3) A
INNER JOIN
(SELECT pid,propertytype FROM property
WHERE approval='Y' AND country like '%india%'
AND (city like '%indore%' OR state like '%madhya pradesh%')
AND Maximum_Capacity >=1) B
ORDER BY is_online_bookable LIMIT 10

Join Subquery #3 to property and property_type tables

SELECT PR.*,PT.property_type
FROM
(
    SELECT B.pid,B.propertytype FROM
    (SELECT property_id,COUNT(1) amenity_count FROM apartment_amenity
    WHERE amenity_id IN (1,2,3) GROUP BY property_id HAVING COUNT(1) = 3) A
    INNER JOIN
    (SELECT pid,propertytype FROM property
    WHERE approval='Y' AND country like '%india%'
    AND (city like '%indore%' OR state like '%madhya pradesh%')
    AND Maximum_Capacity >=1) B
    ORDER BY is_online_bookable LIMIT 10
) K
INNER JOIN property PR      ON K.pid = P.pid
INNER JOIN property_type PT ON K.propertytype = PT.property_type_id;

CAVEAT #1

You have to rebuild Subquery #1 every time you get a new set of amenities and make sure the HAVING COUNT(1) = match the sum of the amenities selected.

CAVEAT #2

Subquery #2 is going to have the worst performance because of

using LIKE
approval='Y' (Cardinality of 2, Y and N values)
Maximum_Capacity >=1 would just be a full scan of all properties

A compound index of (approval,Maximum_Capacity) would be useless

SUGGESTION

Change Subquery #2 from

(SELECT pid,propertytype FROM property
WHERE approval='Y' AND country like '%india%'
AND (city like '%indore%' OR state like '%madhya pradesh%')
AND Maximum_Capacity >=1) B

to

(SELECT pid,propertytype FROM property
WHERE approval='Y' AND country = 'india'
AND (city like 'indore' OR state like 'madhya pradesh')
AND Maximum_Capacity >=1) B

You could then create a compound index on (country,city,state)

You could also create a FULLTEXT index and change Subquery #2 to do FULLTEXT searching

There are other things you could change for Subquery #2. The main point I am making ? Optimize Subquery #2 as much as you can for the best performance.

EPILOGUE

I have suggested refactoring queries before

May 16, 2011 : Fetching a Single Row from Join Table (StackOverflow)
May 21, 2011 : How can I optimize this MySQL query further?
Jun 24, 2011 : What is more efficient, a where clause or a join with million plus row tables?
Dec 04, 2012 : How do I terminate a running SELECT query without restarting MySQL?