Ms Access SQL problem in this table Customer is actually storing CUSID (i have pleased the caption customer and made it display the name to which the CUSID corresponds to)
now in the CUSnum field i want it to automatically pic the number CUSID of which was entered into the previous field (if he customer doesnt exist it should all to add the number of unregistered customer)

i came up with the following SQL
SELECT [Customer].[CUSContact] FROM Customer WHERE [Sale].[CUSID] = [Customer].[CUSID];

but for some reason it is not automatically catching the CUSID from the sale table bu asking for it to be manually inputted ! please help !

You need to make SQL join Sale to Customer. There are two ways to approach this:

APPROACH #1

In SQL mode, add the Sale table to the FROM clause

SELECT [Customer].[CUSContact] FROM Customer,Sale
WHERE [Sale].[CUSID] = [Customer].[CUSID];

APPROACH #2

Open the QBE (Query-By-Example) Grid and add the Sale table. The graphical representation of the query should draw a line from the CUSID in the Customer table to the CUSID in the Sale table.

Give it a Try !!!

UPDATE 2014-03-10 14:43 EDT

I just looked at the image again. Perhaps the query should be this instead:

SELECT [Customer].[CUSContact] FROM Customer,Sale
WHERE [Sale].[CUSID] = [Customer].[CUSnum];


Order to disable constraint in a SQL Server Database I have a DB that every day delete all the data and load new one at the night. To do this an SP disable all constraint with sp_msforeachtable execution. I created some new objects (Schemas, SP, Tables, etc.) and after this, starts to crash sending this message "The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "[Constraint_Name]". The conflict occurred in database "[DatabaseExample]", table "[Schema].[Table]", column '[Column_X]'"

I decided to disable it manually, but i don't know which is the right order to disabled without using hard coding.

First, do i need to disable Unique Keys or Foreign Keys?, if a FK referenced to another table i should start disabling the reference table?

You need to disable the constraint in the table that references the table mentioned in the error message (I'm sure that's not [Schema].[Table]). You can find this using the constraint name in the error message (which I'm also sure is not Constraint_Name, but I'll use that):

DECLARE @sql nvarchar(max);

SELECT @sql = N'ALTER TABLE '
  + QUOTENAME(OBJECT_SCHEMA_NAME([parent_object_id]))
  + N'.' + QUOTENAME(OBJECT_NAME([parent_object_id]))
  + N' NOCHECK CONSTRAINT ' + QUOTENAME(name) + N';'
FROM sys.foreign_keys WHERE name = N'Constraint_Name';

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Check the print output, if it looks good, uncomment the exec, and run it again.

If you need to do this for multiple tables:

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'
  ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME([parent_object_id]))
  + N'.' + QUOTENAME(OBJECT_NAME([parent_object_id]))
  + N' NOCHECK CONSTRAINT ' + QUOTENAME(name) + N';'
FROM sys.foreign_keys WHERE is_disabled = 0;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

The PRINT output will only be useful up to a certain point, depending on your output settings in SSMS (see a workaround here). If you need to narrow it down and not disable all foreign keys, then you can copy the rows you want to the top pane, disable them, then move on to the next set.

Now, if you do ultimately need these foreign key constraints, that means that the values you insert into the table that had the constraint already exist in the table mentioned in the error message, otherwise what is the point of the constraint? You need to fix your code so that it works correctly. Disabling or dropping the constraint is just masking a more important problem, and is going to allow bad data to get into the table in the meantime.

Disabling the constraint is like doing up your seatbelt behind your back just to shut the car up; dropping it is like ripping out the seatbelt and the speaker.


Can you view the Oracle listener status from OEM? Is it possible to view/monitor the listener status from Oracle Enterprise Manager (OEM)?  If so, how is this done?

Yes, we can view the listener status from OEM (Oracle Enterprise Manager).
I am uploading the two screen shot related to listener in Oracle 11g,OEM(Oracle Enterprise Manager) status.

and the current 'Up Status' of Listener.

Note: I have highlighted some part due to security reason.


How to search 471 strings in a table I want to make a SELECT * FROM of 471 strings in table rows, but is not working.

SELECT *
FROM table
WHERE user_id IN (SELECT id
                  FROM table2
                  WHERE user_id = '(bla)|(bla2)|...|(blaN)')
                    AND field3 IN (SELECT id
                                   FROM table2
                                   WHERE field4 = 'bleh');

I want to know if is correct search many strings in a same query?

Use IN()

WHERE user_id IN ('bla','bla2', 'blaN')


Index file growing endlessly - how to fix it? I need to do some maintenance on a database on our client's site. Both log and index files were huge (Don't know how they took their backups to end this way) anyway I fixed the log file (.ldf) issue, log file is now merely a few megabytes (database is in simple recovery mode)
The problem resides on the index file (.ndf) side. For comparison purposes, the database itself (data side, .mdf) is 265Mb. The index file (.ndf) is 57.3Gb!!

Every time I try to reorganise or rebuild indexes on this database, all available RAM is eaten up for this operation, the server becomes almost unresponsive and IO activity is incredibly high. On our test server, the operation wasn't ended after a full 8 hours activity. I can't do this on their server since this is a 911 emergency center.

I just wish to flush the whole index file data and then rebuild it properly. Is there any way to do this without having to render the server in an almost unusable state during the process and having to put the database offline?

Regards,

Nicholas

You're confusing a few terms, I think.  The ndf file is not an "index file", it is just another database data file to house the data.

It sounds like you have two database data files (the MDF and NDF).  With the size of your MDF being so small, my guess would be that your database has two different filegroups (the PRIMARY filegroup using the MDF, and the "other" filegroup using the NDF file).

You need to do a little discovery here.

What are the filegroups in your database?

select
    name,
    type_desc,
    is_default
from sys.filegroups;

Which database files belong to which filegroups?

select
    file_name = df.name,
    df.physical_name,
    filegroup_name = f.name
from sys.database_files df
inner join sys.filegroups f
on df.data_space_id = f.data_space_id;

And which data space do all of the allocation units live in, and how much are they?

select
    data_space_name = ds.name,
    total_pages = sum(au.total_pages),
    total_pages_mb =
        convert(decimal(11, 2), sum(au.total_pages) * 8.0 / 1024)
from sys.allocation_units au
inner join sys.data_spaces ds
on au.data_space_id = ds.data_space_id
group by ds.name;

My guess is that you have two filegroups, PRIMARY and another one.  The other filegroup probably has the NDF file that you are looking at linked up with it.  And maybe even the "other" filegroup is the default filegroup.  This is actually a prudent approach, so that you keep user objects off of the PRIMARY filegroup.

Why do you think this is a problem?  Maybe you just have 57.3 GB worth of data in this database now.  Don't be thrown off by the differing sizes of the MDF and NDF (unless they are in the same filegroup, in which case update your question with the specifics from my diagnostic queries above).

EDIT

  Every time I try to reorganise or rebuild indexes on this database, all available RAM is eaten up for this operation, the server becomes almost unresponsive and IO activity is incredibly high

You should only be selectively rebuilding/reorganizing your indexes based off of their fragmentation levels.  As for the memory consumption, there are ways to minimize that impact such as limiting by altering the max server memory server configuration so that SQL Server doesn't over consume memory and then battle with the OS.


Restore Postgresql database from table files Is it possible to restore a Postgres database when only having access to the table files under $PGDATA/base/<db_oid>? The system catalogs are empty.

Background

Somebody with sudo executed an rm -Rf under /var/lib. Random files where deleted, but not the ones of the database I am interested in. Postgres would not start, even after I restored several files/directories by hand. So I created a new $PGDATA and copied the database files in, hoping that Postgres would automatically read them. Apparently, Postgres relies a lot on its system catalogs to maintain links between what is on disk and what the user sees.

This doesn't sound promising.  Do you know how you got into this predicament?

What specific system tables are empty?  A good chunk of the system catalogs are located inside $PGDATA/base/<db_oid>

Do you have any recent backups that could be used to splice together whatever it is that you are missing?

Of course the first step it to make a backup of the current state of things, so if you somehow make things even worse you can go back and try again.


What is the word that means a query predicate can use an index I remember reading in a paper a word that means “query predicate can be evaluated using an index” but I can’t recall the word. For example if you had a table with a column called “name” and you had an index on name, then a query that had a WHERE clause involving “name” could be succinctly described by this word as “An X-able query”, where X is the word I don’t remember.

The word you're looking for is "SARGable". From Wikipedia:

In relational databases, a condition (or predicate) in a query is said
to be sargable if the DBMS engine can take advantage of an index to
speed up the execution of the query. The term is derived from a
contraction of Search ARGument ABLE.


Has SELECT an INSERT clause? I know the form
Select * into new_table from old_table

statement. Bur recently I found 2 posts here and here which use it like
SELECT * FROM NhlPlayer
INSERT INTO PlayerBackups

I find nothing in msdn, is that some new syntax or just a fault? I guess the later because
INSERT INTO PlayerBackups
SELECT * FROM NhlPlayer

would work fine.

Both posts are wrong syntax.

I've -1 in the first from SO and left a comment on the second.

Create a table

SELECT * INTO PlayerBackups FROM NhlPlayer

Inserts to an existing table

INSERT PlayerBackups SELECT * FROM PlayerBackups


SQL Server xp_cmdshell: account's access changed - how to avoid restarting the service? A T-SQL script is running an xp_cmdshell call to stop a local service through Powershell's Stop-Service, say, like this:

exec xp_cmdshell 'powershell Stop-Service -InputObject (get-Service -Include *"MyService"*)'

Now, since the current login is sysadmin, it is being ran under SQL Server's service's account, which is a local account without admin rights. In that case the Stop-Service fails due to lacking access.
Then, if I add the SQL Server's account to Administrators group, the only way I can make this change effective is to restart SQL Server's service. Is there any other way to do this without having to restart the sql server?
Thank you!

PS. I know xp_cmdshell is bad for you, let's please not focus on that.

Depending on how secure you want to make it (Not dwelling on it as you've requested), a proxy account could be setup for xp_cmdshell operations so that it does has local admin.
xp_cmdshell_proxy
Since you're interacting with the OS at this point, you could also create scripts to switch execution context to another user either through powershell or through the normal command line.
Obviously this all depends on who has access to SQL Server and Agent, and if it would be an issue. Only you can answer that.


What exactly happens when optimizer_prune_level = 1? This documentation link states that optimizer_prune_level

  Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space.

Can anyone help with the knowledge of what kind of heuristic principles are applied?

Consider this simple schema:

CREATE TABLE `college` (
  `colg_id` int(11) NOT NULL AUTO_INCREMENT,
  `colg_name` varchar(20) DEFAULT NULL,
  `colg_address` varchar(20) DEFAULT NULL,
  `avg_fees` int(11) DEFAULT NULL,
  PRIMARY KEY (`colg_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4

CREATE TABLE `department` (
  `dept_id` int(11) NOT NULL AUTO_INCREMENT,
  `dept_name` varchar(20) DEFAULT NULL,
  `dept_address` varchar(20) DEFAULT NULL,
  `dept_hod` varchar(20) DEFAULT NULL,
  `colg_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`dept_id`),
  KEY `colg_id_fk` (`colg_id`),
  KEY `dept_name` (`dept_name`),
  CONSTRAINT `department_ibfk_1` FOREIGN KEY (`colg_id`) REFERENCES `college` (`colg_id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4

CREATE TABLE `student` (
  `stud_id` int(11) NOT NULL AUTO_INCREMENT,
  `stud_name` varchar(20) DEFAULT NULL,
  `stud_address` varchar(20) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  PRIMARY KEY (`stud_id`),
  KEY `dept_id_fk` (`dept_id`),
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `department` (`dept_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1201 DEFAULT CHARSET=utf8mb4

Relation college contains 10 rows, department 100 rows and student 1100 rows.

For the following query

select * from college c, department d, student s where c.colg_id = d.colg_id and d.dept_id = s.dept_id;

-the optimizer trace is attached: trace.json.

Going off of the trace (with my perception abilities), the optimizer has even tried calculation of cost for joining college with student. A cross product join order plan is also considered amongst others. So is cross product avoidance not part of heuristics? At least for less number of joins? So heuristics change with number of joins? If yes how and any examples?

EAV (Entity Attribute Value) schema may benefit from ..level=1.  This is because of messy WHERE clauses that have lots of essentially the same clauses

WHERE a1.e = 123 AND a1.attr = 'color' AND a1.val = 'red'
  AND a2.e = 123 AND a2.attr = 'shape' AND a2.val = 'round'
  AND a3.e = 123 AND a3.attr = 'size'  AND a3.val = 'big'
  AND a4.e = 123 AND a4.attr = 'style' AND a4.val = 'xyz'

Normally, the Optimizer would try a large number of different orders to look at the table.  In reality, they are equally efficient, setting the parameter to 1 keeps the Optimizer from wasting effort.

In your example, the WHERE clauses should be in ON clauses since they describe how the tables are related.  And your example has no WHERE clause for filtering.

Without filtering, a JOIN of 3 tables with sufficient indexes can be done by starting with any of the tables.  For 3 tables, there would be 6 (3 factorial) orderings.  For 10 tables, it would be millions.  But, again, the Optimizer should not waste its time trying all combos.

If you tack a simple WHERE on for filtering, the Optimizer will quickly gravitate toward that table as being the best to start with, and the "level" won't come into play as much.


SQL Server Backup Proposal Our current system setup sees each server backed up by 3rd party software(Veeam). The backup for our Database server occurs once each night.

Alongside this, there is another 3rd party tool that performs database replication to a backup SQL Server.

As it stands, if a disaster were to occur then we automatically switch over to the replicated backup SQL Server. Then switch back once production has been fixed.

I am not sure if this is enough for DR. All our databases are currently in Simple mode. I feel that they should be in Full recovery mode, and Full/Differential/Log backups should be performed on a regular basis.

Or is the replication and Full backup enough for DR? I am basically trying to put an argument together in favour of Full/Differential/Log backups.

Any suggestions?

Thanks!

Veeam will give you full backups of your SQL Server VM which are not that useful when all you want to do is restore a database to see what a particular value was on a certain date. You don't want to de restoring an entire server to get at a database.

The replication gives a level of High Availability but it wouldn't save you from someone running a stupid update statement that wiped a load of valuable data.

You really need to be making regular full backups (nightly is normal) with log backups during the day depending on your RTO and RPO. If you don't know what your RTO and RPO is then you need to research these terms and sit down with management to make a decision on this. It's this decision that will determine your backup strategy and the recovery mode you run each database in.

There are so many reasons to list why you should be making regular SQL backups and retaining them but here are a few

You need to be retaining backups so you have the ability to look back and see what state your data was on a certain date. This can save your job.

Data corruption can hide in your data for a long time before it becomes an issue. If it does you will want to be able to go back before the point the corruption occurred.

It's your job to make sure your data is safe.


Windows Azure Table Row Key strategy, what makes a useful RowKey? I read somewhere that the row key should be useful - which is why auto numbers are not supported.  However, for all 3 tables I've started saving I can't think of anything better to save in the row key column besides a nice, neat auto number.

So let's discuss a specific case now.  I want to log the clients who access my service and simply track their IP address and the requested service and the date of the request.

No single piece of data is unique, and what point is there in creating a Key that blends multiple pieces of data into a single column?  I can run a string operation to extract the individual pieces when I get the data and append them when I save it.  This seems beyond dumb.  I could also create a guid for each entry, but that seems like a more difficult way to use an auto number.

What concept am I missing about saving my entities to a table that will match the RowKey requirement and be meaningful?

my entity looks like so:

public class AccessLog : TableEntity
{
    public AccessLog() { }

    public AccessLog(string partitionKey, string rowKey)
        : base(partitionKey, rowKey)
    {
        this.RowKey = rowKey;
        this.PartitionKey = partitionKey;
    }
    public string ServiceCalled { get; set; }
    public string UserAccount { get; set; }
    public string IpAddress { get; set; }
}

Edit

There is not a correct tag in the list of tags.  This is for a
Microsoft Azure -> Storage Account -> Table object.

The question is really a general question.  I have gone with using a GUID, but I don't like it.  Timestamp is an idea, but there is already a timestamp column auto generated by the Azure Table mechanics and who knows if 2 people would at any point in time over years have their request processed at the same millisecond.

At the end of the day I think the concept of a row key I found is no more than an ideal which simply doesn't exist in every practical setting nicely.

It's just a requirement of TableStorage that a unique row identifier has to exist. I would probably make the RowKey just a timestamp like DateTime.UtcNow.Ticks.ToString() because you can then sort the records by time. Or you could always use a Guid.NewGuid().ToString() if you're worried about conflicts.


Database design for a car dealers website I am creating a new website for all the car dealers in my region to show their cars on and i need your opinion on the database I have created because I have some doubts.

For instance, is it best to have a  VehicleID as a foreign key in the TechnicalSpecifications table or should it be the other way around, like having a TechSpecID in the Vehicles table? I am a bit confused and every time I think about it I lose my focus and can't figure it out.

I appreciate your input on my work so far and if you have any comments on anything please do share.

I would say that your second suggestion, haivng a TechSpecID in the Vehicles table, makes the most sense. A way of thinking could be, is a Vehicle implementing a TechSpec or is a TechSpec implementing a Vehicle? I.e. what comes first (the Technical Specification) and what is 'using' your Technical Specifications (the vehicles). But, in the end, it's all down to your context and what makes the most sense in your particular situation.

You have a very broad question so it is difficult to answer specifically what you might be looking for.

One thing I'd like to point out, however, is that most people don't prepend tg_ or fwk_ to their table names. You could use schemas if you'd like to differentiate them some...


SQL Server 2005 database log size increasing drastically? My friend works in a company (pretty small in size) that is running SagePro ERP which has a SQL server 2005 database. Now the problem is, the database size (over the period of 7 years) has increased to 9 GB which is nothing really but the log file of the database is increasing drastically day by day and has now reached 124 GB.

He is worried that eventually their server might run out of storage if the log size keeps increasing. Upon googling, I found the "shrinkfile" method.

The question is why is the log file keeps increasing? How do I stop it? is "ShrinkFile" method the best method (I believe its not a permanent solution)?

P.S. They daily take full back-up of database.

Probably either have a long running transaction running (Index maintenance? Big batch delete or update?) or you are in the "default"  recovery mode of Full and have not taken a log backup (or aren't taking them frequently enough).

refer this link, hope it will help you


get datetime difference of a specific time and value of date time column I have a datetime column in my mysql table.
I need to get the difference between a definite time everyday say 12.00 pm and the value of that column.

This was trickier than I expected (been a while since I've used the date and time functions) - so I did the following.

Created a table:

CREATE TABLE times (t timestamp);

Then put in some random data:

INSERT INTO times VALUES ('2016-06-27 01:01:12');
INSERT INTO times VALUES ('2016-06-27 07:34:17');
INSERT INTO times VALUES ('2016-06-27 14:56:03');
INSERT INTO times VALUES ('2016-06-27 23:24:56');

And then ran the best function that I could find:

SELECT TIMESTAMPDIFF(MINUTE, t, '2016-06-27 12:00:00') AS tdiff FROM times;

And obtained the result (correct - in minutes):

tdiff
-----
658
265
-176
-684

You can, of course, divide (or multiply) by whatever factor you choose to convert the outputs to the units of your choice.

Depending on your needs, these might suit you better:

- SELECT TIMEDIFF(t, '2016-06-27 12:00:00') AS tdiff FROM times;

- SELECT UNIX_TIMESTAMP(t) - UNIX_TIMESTAMP('2016-06-27 12:00:00') AS tdiff FROM times;

- SELECT TO_SECONDS(t) - TO_SECONDS('2016-06-27 12:00:00') AS tdiff FROM times;

Following @AndrewBrennan's answer below, I've expanded on it somewhat - i.e. if one wished to run the query over a period of a week/month/quarter - whatever. It will perform the relevant calculation comparing datetimes on a given day in the datetime field with the fixed definite time - i.e. comparing like with like on the particular day in question.

SELECT TIMESTAMPDIFF(MINUTE,
                     t,
                     CONCAT(YEAR(t), '-',
                            MONTH(t), '-',
                            DAY(t),
                            ' 12:00:00'))
AS tdiff
FROM times;


I/O freeze on databases (SQL VSS) Our company is considering a new backup product that is an all in one sort of thing (file system, AD, SQL, etc). The product relies on VSS snapshots to create the full backups for the SQL databases. This causes an I/O freeze on the databases that usually just lasts a second or two, but can take up to 60 seconds (per Microsoft, I believe).

My question is how bad are I/O freezes for databases?  Also, is there any way to determine how long the I/O freeze will be?  I would think it would depend on a number of factors including: the size of the database, activity on it at the time, CPU usage, disk I/O, etc.

I have only been a DBA for less than a year and have not seen this myself, but my coworkers informed me that we have customers who do bulk data loads and in the past even a 1 second I/O freeze on the database will cause their jobs to fail.

We have voiced our concerns to management, but it is likely they will purchase the product.

Thanks for your help!

Yeah, this can be pretty bad! I've seen it go on for more than a minute.

There's no way to forecast it without testing it on your server, then checking the error log. You'll see messages for I/O being frozen, and I/O being thawed, along with timestamps.

There are some showstoppers you might wanna consider, though.

Microsoft recommends not doing more than 35 databases per snapshot.

  We recommend that you create a snapshot backup of fewer than 35 databases at the same time.

And cloud vendors like AWS who use snapshots set a hard limit of 30 databases per instance.

  You can create up to 30 databases on each of your DB instances running Microsoft SQL Server. The Microsoft system databases, such as master and model, don't count toward this limit.

Aside from data being frozen for a long time, you'll want to make sure that your snaps are transactionally consistent (dirty snaps can cause corruption), and that they play nice with your native backups.

For instance, most people will take daily snaps, but still take native log backups to meet RPO. You want to make sure that after you mount a snapshot, you can still restore log backups to get you to a point in time. Otherwise, you're losing all the data in between snapshots.

Of course, if you're in Simple recovery model, or your RPO is pretty wide open, it matters a bit less.


Best practice: Unions or a derived table? I've inherited a medium-sized database with a terrible schema. The sanitized portion in question is like so:

CREATE TABLE `pending` (
  ...
  `invoice` int(11) DEFAULT NULL,
  `lid` int(11) DEFAULT NULL,
  `custacct` varchar(21) DEFAULT NULL,
  UNIQUE KEY `pend_inv` (`invoice`),
  KEY `pend_acct` (`custacct`),
  KEY `pend_pid` (`pid`)
  ...
) ENGINE=InnoDB

CREATE TABLE `done` (
  ...
  `invoice` int(11) DEFAULT NULL,
  `lid` int(11) DEFAULT NULL,
  `custacct` varchar(21) DEFAULT NULL,
  UNIQUE KEY `done_inv` (`invoice`),
  KEY `done_acct` (`custacct`),
  KEY `done_pid` (`pid`)
  ...
) ENGINE=InnoDB

And two similar tables, customers and locations:

CREATE TABLE `customers` (
  `acct` varchar(14) NOT NULL,
  ...
  PRIMARY KEY (`acct`),
  ...
) ENGINE=InnoDB

So, yes, when an invoice is not yet fulfilled, it's in the "pending" table, then when the company's finished with it, it gets moved to the "done" table. So any given invoice will only be on one of the two tables.

Unfortunately, we're still actively using the proprietary software that runs on this schema, so I am powerless to fix it. The "pending" table will usually have around 9000 rows, whereas the "done" table is ~800,000 and counting.

For two years (the full extent of my LAMP/MySQL experience), I have been writing UNIONs to capture information from the customers or locations tables, with the first query joining customers/location/both against "pending" and the second joining customers/locations/both against "done."

I've recently had a nagging feeling that writing two nearly-identical queries UNIONed together is probably newbish and should be done differently. So I finally applied my more recent learning to a problem I had "solved" in my first few weeks of working with MySQL and realized that I can just use a derived table with a UNION inside that subquery to achieve the same results with less typing.

The execution time is near-similar, usually 0.01s for the derived query and slightly less than that for the UNIONed query, but the dervied table query uses far fewer characters and requires less time to type out.

So my question for those with more experience, is using a derived table subquery more sane and maintainable in the long run? It's easier for me, the programmer, so that makes me suspicious that maybe it's not the best practice!

Sample queries below:

select c.whatever,l.whatever from customers c join
  (select d.custacct as acct,d.lid from done d where d.invoice=123456
     union
   select p.custacct,p.lid from pending p where p.invoice=123456
  ) as combi
    on c.acct=combi.acct join locations l on combi.lid=l.lid;

select c.whatever,l.whatever from done d join customers c on d.custacct=c.acct join locations l on d.lid=l.lid where d.invoice=123456
union select c2.whatever,l2.whatever from pending p join customers c2 on p.custacct=c2.acct join locations l2 on p.lid=l2.lid where p.invoice=123456;

In your example query, neither way is particularly better, because the inner queries have their own where clauses, are generating a very small result-set, and are able to do this very efficiently presumably because the invoice column in pending and done are indexed.

If you were doing something more complex, and especially without such highly-selective where clauses in the inter queries, the union would likely perform better... because in the derived table scenario, the derived table is materialized before the joins to the outer tables and indexes on the tables used in the inner query can't be used to handle the join... on the other hand, with UNION, the optimizer could use indexes on pending/done if they were appropriate.

Personally, I'd be inclined to solve this one by writing one or more stored procedures to encapsulate the union queries and return the answer as a result set using an unbounded SELECT.

  MySQL supports a very useful extension that enables the use of regular SELECT statements (that is, without using cursors or local variables) inside a stored procedure. The result set of such a query is simply sent directly to the client.

  http://dev.mysql.com/doc/refman/5.5/en/stored-routines-syntax.html

These are called "unbounded."  So, a stored procedure that takes the invoice id as an input parameter, does the SELECT ... UNION ALL ... SELECT can encapsulate everything and give you a very clean way to ask the database for what you need and get a record-set in return.

CALL get_invoice_customer_location_detail_by_invoice_id(123456); # long, but much more appealing

If you subsequently decide union or derived really was The One True Way after all, then you only have to update that in one place, in the procedure definition.

Free tip: UNION means UNION DISTINCT in non-dinosaur versions of MySQL, while some older versions will interpret it as UNION ALL.  It's probably good practice to explicitly use the one you actually want, so you always get the behavior you expect.  It's also generally good from a performance standpoint to use UNION ALL unless you need UNION DISTINCT because it's one less thing for the optimizer to have to deal with, but this is much more the case with large result sets, where a lot of time could be spent de-duplicating a set of data that is already free of duplicates.


Database Consistency History fails in SQL Server 2017 Express When I run the Database Consistency History report using MSSM I get an error message:

  unable to retrieve data for this section of the report. Following
  error occured  Msg 19050, Level 16, State 5 Trace file name
  '/var/opt/mmsql/log/log_86866.trc\log.trc' is invalid

I re ran DBBCC CHECKDB but it did not help.

When I run

SELECT tracefilename = path FROM sys.traces
WHERE is_default = 1;

I get the tracefilename returned as

/var/opt/mssql/log/log_87148.trc

And if I re-run the Database Consistency History the tracefilename is now

'/var/opt/mmsql/log/log_87148.trc\log.trc' is invalid

This is a limitation in SSMS. This report does not properly support Linux, as the script is performing path manipulation using '\', which is the Windows path seperator.

This is a snippet of code run for that report:

select @curr_tracefilename = path from sys.traces where is_default = 1 ;
set @curr_tracefilename = reverse(@curr_tracefilename);
select @indx  = PATINDEX(''%\%'', @curr_tracefilename) ;
set @curr_tracefilename = reverse(@curr_tracefilename);
set @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + ''\log.trc'' ;

You can post product feedback here.


Can't Login to SQL Server 2008 when running single user mode I'm following the MS documentation here to migrate logins from one instance of SQL Server to another as part of the process described here. This involves putting the server into single user mode (which I've done) and then running scripts etc.

However, after restarting in single-user mode (with only the SqlServer service running) I am unable to log-in using either my Windows account or sa credentials. I get the error:

Failed to connect to <servername>

Login failed for user 'sa'. Reason: Server is in single user mode.
Only one administrator can connect at this time. (Microsoft SSQL Server, Error: 18461)

After restarting the server normally (ie. multi-user mode) all the logins work fine.

Can anyone explain what is going on and/or how to correct this?

This might be because you are attempting to connect using SSMS.  Inside SSMS, Object Explorer will open one connection to the database, while each subsequent query window will open another connection to the database.

What I have done in the past is open SSMS, do not connect to any database via Object Explorer, and open a Dedicated Administrator Connection (DAC) query windows by doing the following:

Click New Query
Type the following into the Server Name dialog box:  ADMIN:ServerName\InstanceName
From here, you can run any scripts needed against your database in Single User Mode.

This requires the SQL Server Browser service to be running.

The real sticking point in all of this is that Object Explorer will eat up that "single user".

Hope this helps,

Matt


Is it a bad practice to keep Master and Slave (Replica) Database on Same Server? I have a database A (master) on server 1. I need to read from database A perform aggregations and write on database B. Since database A has a very high traffic, I cannot read directly from database A without messing up performance.

Is it a bad practice to keep database A (master) on server 2 and create a server 2 with a read-replica of database A and database B? In other words, I would have a master and a slave database on the same server. Is it OK ?

I would say Yes, it is a bad practice to keep master database in same server than replica database. I have good three(3) reasons why, but there are possible workarounds if you cannot acquire another DB Server. Before I discuss those reasons, have a look at a Pictorial Representation of the InnoDB Architecture (from Percona CTO Vadim Tkachenko)

REASON #1 : Disk I/O
Look at InnoDB's system tablespace file ibdata1. It has lots of moving parts

Data Dictionary
Double Write Buffer (to prepare for crash recovery)
Insert Buffer (manages changes to secondary indexes)
Redo Logs (ib_logfile0,ib_logfile1)
Undo Logs
Rollback Segments

You have to create two different datadir locations. If they reside on the same disk, you will have two ibdata1 files being updated on the same disk, which will suffer increased disk I/O and will slow down both MySQL instances.
Suggestion #1: You could accommodate this having the datadir of each MySQL Instance on a different disk using its own disk controller (Make sure your disk controllers have up-to-date kernels).
This is the same concept you would apply if

You use Amazon MySQL RDS, having a Master and Slave in different availability zones
You have VMWare, having Master VM and Slave VM live on different disks

REASON #2 : RAM
Look back at the InnoDB Buffer Pool. You will have two of them. If you set these too large, you could trigger some swapping in the OS.
Suggestion #2: If the Slave instance is just for aggregation (or aggrevation), you could tune the Slave's my.cnf to use a lot less memory (Smaller Buffer Pool, Smaller MyISAM Key Buffer)
Suggestion #3: Increase RAM
REASON #3 : Data Redundancy / High Availability
If mysqld goes down on the Master, you have mysqld for the Slave still running. What if you have a hardware failure and the database is mission critical ? There is nowhere to failover.
Suggestion #4: You should get a cheap server with the same amount of disk and half the memory of of the Master and configure a Slave.
Suggestion #5: If you applied Suggestion #1, you could move the disk holding the Master to another machine. If that disk is fried, you have the disk holding the Slave. Hopefully, the one DB Server did not having a real disaster and and damage both Master and Slave disks.


Data warehouse with foreign key I have data that I extract and from sam.gov and fpds.gov and a few other sources. and I throw the data in a warehouse. The sam data I just throw away at the end of the month and add in the updated data from sam.gov's public extracts.

I am not using a foreign key between the fpds and sam data but there is a relationship between them, and I am able to join them based on a certain key in the data. Should I create a FK relationship between the two tables or just continue without one?

There is a one-to-many relationship from sam -> fpds.

DWs normally go without FKs, since there's normally no DML apart from loading the data into its tables.  You want this to go as fast as possible, while the integrity should be guaranteed by your sources.


Is LocalDB installed independently of SQL Server? I am a Developer.  I thought LocalDB was installed as a component of Visual Studio, which could be used for development purposes.  However, it appears to be installed independently of Visual Studio.

Is it installed as part of SQL Server; SQL Data Tools or both?

LocalDB is an optional feature of SQL Server Express edition.  It may be installed by:

1) Selecting the LocalDB feature during installation of SQL Server Express

2) Selecting the .NET desktop development feature during installation of VS 2017. According to this thread, SSDT does not automatically install LocalDB.


Oracle 11: Change global index to local on huge production table I have big partitioned table with global index. My case is drop old data from archived partitions. I can't simply drop partition because i have global index.

So I have idea to use INVISIBLE local INDEX (calculating for 12-16h) and then drop global  index and make new index visible and rename it to just dropped global index.

But..

SQL> create index I_EMPLOYEE_SALARY_T on EMPLOYEE (SALARY, DEPARTAMENT) local invisible online;
create index I_EMPLOYEE_SALARY_T on EMPLOYEE (SALARY, DEPARTAMENT) local invisible online
                                              *
ERROR at line 1:
ORA-01408: such column list already indexed

Is there any other option to change global index to local using atomic/fast operations to change id on the fly?

Dropping global index and then creating new one is NOT acceptable.

I found solution making creating index quite faster... (on Oracle 11g works fine)

drop index I_EMPLOYEE_SALARY_T;
create index I_EMPLOYEE_SALARY_T on EMPLOYEE (SALARY, DEPARTAMENT)
tablespace IDX_TABLESPACE local unusable;

Then I need to rebuild index online choosing partition:

ALTER INDEX I_EMPLOYEE_SALARY_T REBUILD PARTITION partition_name online;

Altering index is much faster because it using different, faster mechanism and data can be accessed much sooner. For example when data for employees (in this example) is partitioned by timestamp. So newer data are more quickly available.


Data Warehouse attribute that describes its timeframe? I am writing a document about a Data Warehouse system, and would like to know if is there a single word (noun) that describes the DW attribute of how many time it contains?

I mean, if a DW contains 5 years, I want a noun for it, for instance: the XXX of DW is 5 years.

Likewise we call how fresh the data is as its Latency, and how often the data is updated as Periodicity.

I would go with "the date range encompassed by the data warehouse is x."

This eliminates all possible confusion about the intent by deliberately using words that are understandable by non-technical people.


Struggling with complicated date query So I have been tasked to write a query involving dates for a contract management system we have. The database stores the date the contract's primary terms expired, the frequency that the contract auto-renews, and the notice period required to make a change to the contract. Here is a query with some sample data:

WITH EX_DATA AS (
SELECT '28-FEB-2008' EXPIRE_DT, 30 TERM_NOTICE, 'YEARLY' NOTICE_FREQ, 12 NOTICE_MONTHS FROM DUAL
UNION ALL
SELECT '1-MAR-2005', 60, 'YEARLY', 12 FROM DUAL
UNION ALL
SELECT '1-DEC-2007', 30, 'MONTHLY', 1 FROM DUAL
)
SELECT * FROM EX_DATA;

I need to return the following data: the next day the contract expires and the day we have to give them notice. For example, in the first set of data above, the contract expires 2/28/2008, it auto-renews every year, and we have to give them 30 days notice of any change. So, today is 2/8. The next day the contract expires is 2/28/2013. Next month, the date would change to 2/28/2014. That's the first field I need to compute. The second is we have to give them 30 days notice if we want to renegotiate the contract. So, we our deadline for that is 30 days prior to 2/28, or 1/29/2013. Again, after 2/28, that will change to 1/29/2014.

In the last example, since it has a monthly renewal, it expires next on 3/1/2013 (then 4/1, 5/1, so on). With a 30 day notice period, they have to be notified 30 days prior to the next expiration date.

The NOTICE_MONTHS column is just an extra information field that has the month count of the auto-renewal, 12 for yearly and 1 for monthly. This might be helpful in the query.

I've tried several different ways of doing this but have not yet come out with a 100% foolproof result. I was taking the month and day and converting to CHAR then converting that back to DATE which gave me contract expiration for this current month. But when I tried applying other logic, I'd get stuck on an error that resulted from leap year.

Any help would be appreciated.

I'll do it in stages to make it easier to understand.

I put your data into a table called EX_DATA -  added a primary key as I assume you'll have one and it makes things a little easier:

create table ex_data
(
  contract_id number(18),
  expire_dt date,
  term_notice number(18),
  notice_freq varchar(20),
  notice_months number
);

insert into ex_data values(1,TO_DATE('01-MAR-2005','DD-MON-YYYY'), 60, 'YEARLY', 12);

insert into ex_data values(2,TO_DATE('01-DEC-2007','DD-MON-YYYY'), 30, 'MONTHLY', 1);

insert into ex_data values(3,TO_DATE('28-FEB-2008','DD-MON-YYYY') ,30, 'YEARLY', 12);

The next expiry date from the first given date will be:

select add_months(expire_dt, notice_months) as next_expiry
from ex_data;

Then, it follows that the notice period is a bit of easy date arithmetic too:

select add_months(expire_dt, notice_months) as next_expiry,
       add_months(expire_dt, notice_months) - term_notice as notice_date
from ex_data;

Using DUAL as a source, generate expiry dates for lots of years:

select expire_dt as first_expiry_date,
       l as expiry_num,
       add_months(expire_dt,l*notice_months) as all_expiry_dates
from ( select level l from dual connect by level < 100 ) l, ex_data
order by l;

The next expiration date, and the date they should have been contacted for renegotiation:

select first_expiry_date, all_expiry_dates as next_expiry_date, all_expiry_dates - term_notice as renegotiation_date
from
(
select expire_dt as first_expiry_date, l as expiry_period_num, term_notice, add_months(expire_dt, l*notice_months) as all_expiry_dates
from ( select level l from dual connect by level < 100 ) l, ex_data
)
where all_expiry_dates >sysdate
and all_expiry_dates - term_notice < sysdate
;

But, that's not what I think you're asking. You want the next renewal date where the contact period hasn't yet been hit, which can be done as follows:

select * from (
select contract_id, first_expiry_date, all_expiry_dates as next_expiry_date, all_expiry_dates - term_notice as notification_date, ROW_NUMBER( ) OVER (PARTITION BY
contract_id ORDER BY all_expiry_dates) r
from
(
select contract_id, expire_dt as first_expiry_date, l as expiry_period_num, term_notice, add_months(expire_dt, l*notice_months) as all_expiry_dates, notice_months
from ( select level l from dual connect by level < 100 ) l, ex_data
)
where all_expiry_dates - term_notice > sysdate
)
where r=1;

Can probably be done with some simple SYSDATE maths along with the source data, but my brain is a little slow this evening ;)


Which is faster, InnoDB or MyISAM? How can MyISAM be "faster" than InnoDB if

MyISAM needs to do disk reads for the data?
InnoDB uses the buffer pool for indexes and data, and MyISAM just for the index?

The only way MyISAM can be faster that InnoDB would be under this unique circumstance
MyISAM
When read, a MyISAM table's indexes can be read once from the .MYI file and loaded in the MyISAM Key Cache (as sized by key_buffer_size). How can you make a MyISAM table's .MYD faster to read? With this:
ALTER TABLE mytable ROW_FORMAT=Fixed;

I wrote about this in my past posts

Best of MyISAM and InnoDB (Please Read This One First)
What is the performance impact of using CHAR vs VARCHAR on a fixed-size field? (TRADEOFF #2)
Optimized my.cnf for high-end and busy server (Under the heading Replication)
Which DBMS is good for super-fast reads and a simple data structure? (Paragraph 3)

InnoDB
OK, what about InnoDB? Does InnoDB do any disk I/O for queries? Surprisingly, yes it does !! You are probably thinking I am crazy for saying that, but it is absolutely true, even for SELECT queries. At this point, you are probably wondering "How in the world is InnoDB doing disk I/O for queries?"
It all goes back to InnoDB being an ACID-complaint Transactional Storage Engine. In order for InnoDB to be Transactional, it has to support the I in ACID, which is Isolation. The technique for maintaining isolation for transactions is done via MVCC, Multiversion Concurrency Control. In simple terms, InnoDB records what data looks like before transactions attempt to change them. Where does that get recorded? In the system tablespace file, better known as ibdata1. That requires disk I/O.
COMPARISON
Since both InnoDB and MyISAM do disk I/O, what random factors dictate who is faster?

Size of Columns
Column Format
Character Sets
Range of Numeric Values (requiring large enough INTs)
Rows Being Split Across Blocks (Row Chaining)
Data Fragmentation caused by DELETEs and UPDATEs
Size of Primary Key (InnoDB has a Clustered Index, requiring two key lookups)
Size of Index Entries
the list goes on...

Thus, in a heavy-read environment, it is possible for a MyISAM table with a Fixed Row Format to outperform InnoDB reads out of the InnoDB Buffer Pool if there is enough data being written into the undo logs contained within ibdata1 to support the transactional behavior imposed on the InnoDB data.
CONCLUSION
Plan your data types, queries, and storage engine real carefully. Once the data grows, it might become very difficult to move data around. Just ask Facebook...


Function taking a string as table and column name I'm trying to write a function that will create a new column in a participation table based on the presence of a id in survey_name table:

CREATE OR REPLACE FUNCTION survey_participated(_sn text)
  returns void AS
$func$
BEGIN
  EXECUTE format($$ALTER TABLE participation ADD COLUMN %I integer DEFAULT 0$$,_sn);
  EXECUTE format($$UPDATE TABLE participation SET %I = 1 WHERE participation.participant_id = %I.participant_id$$,_sn,_sn);
END
$func$ LANGUAGE plpgsql;

I keep getting the error:

[2017-10-19 12:38:09] [42601] ERROR: syntax error at or near "TABLE"
[2017-10-19 12:38:09] Where: PL/pgSQL function survey_participated(text) line 4 at EXECUTE

Can someone point out my mistake?

The first problem is the syntax for UPDATE doesn't permit an optional TABLE after. It's just not needed. UPDATE only operates on tables; ALTER works on many targets. The second problem is that should be %s (literal/string) and not %I (identifier).

EXECUTE format(
  $$ALTER TABLE participation ADD COLUMN %I integer DEFAULT 0;$$,
  _sn
);
EXECUTE format(
  $$
    UPDATE participation
    SET %I = 1
    WHERE participation.participant_id = quote_literal(%s||participant_id);
  $$,
  _sn,
  _sn
);

That all said, this seems like a horrible idea. You're adding a column to the table, and setting the value of that column to the "colname+id"? How is that useful?


How to deploy SSIS package with XML config, using SQL agent I wonder if anyone can help me with a question.

Context
I've created a database which needs a front end in MS Excel. When the user presses upload button I want it to start an SSIS package which moves the data into some tmp tables then runs a couple of stored procs to move the data into the correct tables. I'm struggling to deploy my package successfully.

Hurdles I've already jumped over

The SSIS package is functional in debug mode.
I've got a SQL Agent job which can run the package on the integration services server file system.
I've managed to create a way of the users starting the job from excel and inheriting SQLAgentOperatorRole through the very convoluted process of creating certificates and a new stored proc, I called it spTriggerJob [with parameter for job name], which then runs EXEC msdb.dbo.sp_start_job @job_name = @strJob with the correct privileges.
I've created and implemented a proxy with permissions to access the network file server.
I've created XML configurations to pass the required variables (e.g. the filepath of the file to be uploaded) and a way of updating them on-the-fly.

Problem 1
When I run the package manually from SSMS I can load the XML configurations successfully. However I haven't been able to do this from the SQL Agent job. If I go into job properties > steps > edit > configurations tab > add the only options I get are C:\ D:\ and a couple of obscure/unhelpful network drives. Ideally I want to point it at a full UNC filepath. Is it necessary to map the drive on the server?

I suspect there is a more optimal way of deploying my SSIS package which circumvents this. Can anyone suggest a better way? I'm fairly new to all this so don't make any assumptions as to my existing knowledge!

Problem 2
When I use my spTriggerJob [name of job], I see a weird message in the job history. It says "The job succeeded. The Job was invoked by user " then it gives the Windows account login of one of the network administrators. However I've been able to verify that the permissions of the proxy (which have nothing to do with Mr Network Admin) are applied correctly by using it to create an entry in a test table and recording the user name.
However if I call msdb.dbo.sp_start_job directly, then the login which appears in the job history message is my own.
This seems wrong! Does anyone know how to resolve this?

Edit: I figured out problem 2. It is because 'spTriggerJob' has to have 'WITH EXECUTE AS OWNER'. Whilst I thought I was owner, in fact it is Mr Network Admin. The confusing bit was that the job was running with the correct privileges inherited from the proxy, which threw me off a bit.

Required solution
I need to reach a point where the package is run on an ad hoc basis at the click of a button, with different configuration settings each time it is run. Surely it can't be that hard!!!

Thanks in advance for your help.

Cheers,
Andy

If you want to store the configuration file and/or the file you need to "pick up" via the SSIS package on a UNC path, SQL Server is going to need permissions to that direct UNC path. This permission is based on your service account being used for SQL Server Agent. If you are in a domain environment, then it is best to have your service run as a domain account and it will allow you to easily provide access to network resources.

If this is a configuration that is meant to change often and I would not be the one to do it necessarily every time, then I might chose to store the configuration in SQL Server. There are a few examples out there online that show how to do this, just one example here.


How can I measure the performance hit of tablespace autoextensions? I'm not a DBA, nor is there a DBA on the project I'm currently working, so we have tablespaces with autoextend on next 6m. I have no idea if this is low, although I believe so.

I want to measure what's going on with the current configuration and, if possible, tune the autoextension of my tablespaces so that a low value does not cause this to happen very frequently.

If you know the original size, it is easy to calculate the number of extends that have occurred.  The metadata for the datafile will include its age.

The overhead for an auto extend is low, but datafile fragmentation may cause problems.

I generally try to extend by 10 to 25 percent of the original size, and aim for as few extend operations as possible.   Try to allocate enough space for at least a year's worth of data.

For development databases, I tend to size smaller, and extend more often.

You can find out how full a tablespace by querying dba_tablespace_usage_metrics. The Orafaq Tablespace page will give you additional information.


One database with two schemas: cross-schema View permissions issue? I have a database on SQL Server 2008 R2 that has two schemas, the default dbo and a new one called Test.

I have a view in the Test schema called View1 that selects from dbo.Table1. Both schemas are owned by dbo.

I have a user called User2 that has SELECT permission to the Test schema.

User2 can login and see View1 but when it tries to select from it this error comes up:

The SELECT permission was denied on the object 'Table1', database 'Database', schema 'dbo'.

I thought since everything was owned by the same owner, ownership chaining would check the permission at entry point and allow the query to execute.

For kicks, I setup a similar example under a SQL Server 2012 Express database and it works fine.

Any ideas why this doesn't work?

Ownership chains work down not up.  If you give the user rights to the view, they can query the view without needing rights to the objects under the view, in this case the table.  It doesn't work the other direction.  The user always needs rights to the object they are attempting to access.  All the objects under that object will come automatically.


Update values in one table from data in another I have an application using SQLite with two tables a master table and a scheduled table.

I would like to update a value in the master table with the value in the scheduled table if I run a query some time after due has occurred. Once the master table is updated, I would like to delete those values from the scheduled table.

Imagine this is the starting state:

Table: master
   id    value
   ---   -----
   1     "Buy"
   2     "Buy"

Table: scheduled
   key   id   value   due
   ----  ---  ------  -----
   1     1    "Sell"  2018-01-01 12:01:00
   2     2    "Sell"  2018-01-01 12:01:00
   3     1    "Buy"   2019-02-02 13:00:00

I'm trying to craft a query where if I run it after 2018-01-01 12:01:00, I would like to see:

Table: master
   id    value
   ---   -----
   1     "Sell"
   2     "Sell"

Table: scheduled
   key   id   value   due
   ----  ---  ------  -----
   3     1    "Buy"   2019-02-02 13:00:00

I'm new the SQLite, the closest I have gotten is the following:

UPDATE master
  SET
    value = (SELECT scheduled.value FROM scheduled WHERE scheduled.id = master.id)
  WHERE
    EXISTS (SELECT * FROM scheduled WHERE scheduled.due >= ?);

Unfortunately this doesn't seem to work, and I'm not even sure where to start on the DELETE.

To answer the first part of the question, I did the following:

Created two tables:

CREATE TABLE master
(
  m_id INTEGER,
  m_value VARCHAR(4)
);

CREATE TABLE scheduled
(
  key INTEGER,
  s_id  INTEGER,
  s_value VARCHAR(4),
  due TIMESTAMP
);

Added some records:

INSERT INTO master VALUES (1, 'Buy');
INSERT INTO master VALUES (2, 'Buy');

INSERT INTO scheduled VALUES (1, 1, 'Sell', '2018-01-01 12:00:00');
INSERT INTO scheduled VALUES (2, 2, 'Sell', '2018-01-01 12:00:01');
INSERT INTO scheduled VALUES (3, 1, 'Buy',  '2019-01-01 12:00:00');

Then I ran the following query:

UPDATE master
SET m_value =
(
  SELECT s_value
  FROM scheduled s
  WHERE s_id = m_id
  AND due <= CURRENT_DATE
);

The result of SELECT * FROM master after the UPDATE:

m_id   m_value
   1      Sell
   2      Sell

You can find all this on the dbfiddle here. Next time you ask a question, you might find it beneficial to set up a  dbfiddle for those who are trying to solve your issue - help us to help you!

BTW, SQLite (unlike other systems) doesn't support UPDATEs with JOINs. I'm not sure what the standard says about using these, but here is your example done using a join.

UPDATE master
SET m_value = s.s_value
FROM  master m
JOIN scheduled s ON m.m_id = s.s_id
WHERE s.due <= NOW();

To answer the second part of the question, do all the work (two separate statements) in one TRANSACTION (documentation here, example here). Unfortunately, dbfiddle doesn't appear to support this.  BUT, we're in luck, this db-fiddle (note hyphen!) does support TRANSACTIONs - take a look at the complete answer to your question here. If my solution doesn't help you, please let me know.


Minimizing database size (Many small tables) Our database is interesting in that we have a large amount of tables that take up a correspondingly large amount of space, but when we do a MySQL dump of said database, it is surprisingly small.

Specifically, the database is about 50GB, and contains about 50k tables.  When dumped, it takes up about 5GB.  (These numbers aren't spot on, but they're close enough for our discussion here).  It's particularly odd to me that the "binary" DB takes up so much more space than the SQL-dumped DB.

The vast majority of the tables (like 49990) are relatively small Wordpress Multisite tables.  These are seldom-used sites with very little content on them.

If it's relevant, we're using innodb_file_per_table.

What is the best way to minimize the size of our database?  Does minimizing the size of the database come at a cost of lower performance?  Ultimately, I'd like to reduce the size of the DB in order to increase performance (e.g. with regard to doing backup/restore operations).

Update: The structure of the tables is essentially just the default Wordpress Multisite layout: http://pastie.org/private/iufzw8z9zlyidqw8b7wggw  Note that I looked into some more accurate numbers, and it appears we have about 9k Multisite instances for a total of nearly 80k tables.  The larger numbers are partially due to our service continuing to grow and add new customers.

"Does minimizing the size of the database come at a cost of lower performance?"

Typically databases are IO limited, unless they are regularly recalculating reporting style queries. (in which case pre-calculated views can be added to again shift cpu to IO.)

Minimizing the size of the database typically minimizes the IO needed against the disk, as a higher % of all data can be in cache.

Compressed storage can also help here; if 1 disk iop can retrieve more rows, then that too can increase performance, without needing to logically re-arrange the data. (many compression schemes are cpu-efficient enough that it is possible to actually see a decrease in cpu usage, due to having to handle less data pages.)

So, in general, minimizing the size of the database does indeed increase performance, but always benchmark as there are many counter-examples to any generic statements like this one (note, the answer by RolandoMySQLDBA lists some downsides of compression concerning memory pressure).

some of the tradeoffs due to data compression are listed at http://dev.mysql.com/doc/refman/5.5/en/innodb-compression-internals.html#innodb-compression-internals-storage


What is the easiest way to install the latest postgis_tiger_geocoder? Ubuntu distributes PostGIS with a core extension called PostGIS TIGER Geocoder, packaged as postgis_tiger_geocoder. Currently, as of PostGIS 2.2.2, it pulls data from TIGER2015. I would like to upgrade this to pull from TIGER2016. I want to use the newest version of the geocoder. How do I go about this?

I want to upgrade just postgis_tiger_geocoder?

Well, they don't make this easy because it's part of core. It's also not fun that this is hard coded into the extension. You can either

Follow the instructions in the official FAQ by extracting from the .zip
Rebuild the entirety of PostGIS which is a monstrously fun task -- though it is totally doable. There is no easy way to build just the extension. The extensions build after the documentation and the rest of PostGIS. Good luck and god speed.
Download PostGIS-Scripts from the PDPG apt archive. Note, the file you want is labeled postgresql-$majorversionpg-postgis-$majorversionpostgis-scripts_$fullversionpostgis with the version of PostgreSQL and PostGIS. Do not download the transition packages that lack the $majorversion portion of the name.

After you download extract the files with dpkg -X justDownloadedArchive.deb ./out
Copy out/*geocoder* to your extension folder (/usr/share/postgresql/*/extension/) where * is some version number.


SQL Server 2005 Percent Log Used Alert fires too fast? I have a SQL Server 2005 database thats roughly half a terabyte.  Third party software that I don't control writes to the database.

I have a Maintenance Plan which backs up the transaction log, and is called by an Agent Job which executes every 3 hours. The Agent job as a second step also compresses the finished trans log backup with gzip.  Sometimes there are sporadic large bursts of updates to the database, and the transaction log starts to fill prematurely.

I created an Alert on the database based on the SQLServer.databases object, Percent Log Used counter, that responds by executing my backup trans log Agent job when percent of log used goes above 75%.

When I enabled the alert and a burst of updates occurred, the Alert fired much more quickly than the job could backup and compress the trans log, about 1 alert every 3 seconds I think.  I think a bunch of job start/exec commands were queued up, and weird things began to happen with the job - it would sit there spinning and never finish.  Job Activity Monitor said the gzip of a trans log was happening, but NO files were being compressed..
I finally had to stop the job and restart Agent.

So it looks like the Alert fires too quickly for the job.  What can I do about this?  Can I somehow set some flag (maybe a file?) while the job is working, and the alert does not call the job when the flag is set?

Am I just missing something in how this should work?

Any help appreciated.
aj

In the properties of the Alert you can set a "Delay between responses". You can set the delay to slightly longer than it takes to do the compression.


Composite Key with a column that auto increments and reset when foreign key value changes I am trying to implement a ticket system, the numbers generated would be sequential.

4000, 4001, 4002, 4003

My idea was to have a table that relates to current raffle, and the ticket_no column is an auto increment column that resets when the foreign key changes.

The composite keys are raf_id and ticket_no since the ticket no should not exist multiple times in same raffle

Is this possible?

|------------|-----------|---------|
| raf_id     | ticket_no | user_id |
|------------|-----------|---------|
| 1          | 4000      | 1       |
| 1          | 4001      | 1       |
| 1          | 4002      | 2       |
| 1          | 4003      | 3       |
| 2          | 4000      | 4       |
| 2          | 4001      | 4       |
| 2          | 4002      | 5       |
| 2          | 4003      | 1       |
|------------|-----------|---------|

Automatic?

Unknown.  Most likely, not. (as in: no out-of-the-box database features)

Manual?

Yes.

You need to keep track of "last number used" in a table. eg

create table raf (
  raf_id        int, -- identity/autoincrement
  last_number   int default 4000 not null,
  constraint raf_pk primary key (raf_id)
);

From there, your process needs to go like this:

grab a lock for RAF_ID
"calculate" the next number.
Update the RAF table.
Use that value in the other table
COMMIT the data
Release the lock.

Depending on your RDBMS, some steps can be combined into a single step.  The HOWTO do these things (eg Best practice for "grab a lock") can also be RDBMS dependent.

Notes

The "grab a lock" step will serialize your code.  This is bad for databases.  This requirement/process will limit how fast you can add new rows.  I suggest you look at alternative methods (eg a VIEW that uses RANK() ).

Can this be implemented in a TRIGGER?  Maybe.


Unable to backup to a UNC path with a gMSA I'm trying to automate backups using Ola Hallengren's backup scripts.

I'm testing this out on a SQL express instance 2008 R2 (I know it's EOL today).  I've setup the jobs and I created a scheduled task to run a batch script with SQLCMD.  The scheduled task is running as a gMSA

Everything seems to be groovy except for some darn reason I can't reach the UNC path I'm trying to backup to.  The error I'm getting is as follows:

Msg 50000, Level 16, State 1, Server servername\instancename, Procedure DatabaseBackup, Line 878
The directory \\servername\sharename does not exist.

This has to have something to do with the gMSA not having proper permission to the share or something but I've explicitly granted this account full access at the share and NTFS levels (out of desperation) and still no dice.  Obviously the share does exist, and I can connect to it just fine using other accounts.

If anyone has any experience running SQL server with gMSA's who could shed some light on this I would greatly appreciate it.

Thanks,

UPDATE - Just for kicks I gave the service account sysadmin priviledges on the SQL server and now it works.  I have no idea why that would fix problems with access to a file share so the error is definitley misleading.  What are the minimum rights required to run a backup for databases?  Initially I gave the service account the "public" server role and "db_backupoperator" on each database.  Looks like I'll need more than that?  The target here is least priviledge.

Thanks

If you check the documentation for Ola Hallengren's solution, you'll see under "Which permissions are needed for the SQL Server Maintenance Solution to work?" the following:

  DatabaseBackup: sysadmin
  DatabaseIntegrityCheck: EXECUTE on dbo.DatabaseIntegrityCheck, VIEW DEFINITION on dbo.CommandExecute, VIEW DEFINITION on dbo.CommandLog, VIEW SERVER STATE, db_owner on all target databases
  IndexOptimize: EXECUTE on dbo.IndexOptimize, VIEW DEFINITION on dbo.CommandExecute, VIEW DEFINITION on dbo.CommandLog, VIEW SERVER STATE, db_owner on all target databases

I believe the reason sysadmin is required is because the solution uses xp_fileexist to check the existence of a directory, and this extended stored procedure requires sysadmin rights.

Normally, the jobs are executed by a SQL Agent which has sysadmin rights by default granted to the per-service sid NT SERVICE\SQLServerAgent (or NT SERVICE\SQLAgent$instance_name for a named instance). If you use a proxy to run the jobs in SQL Agent, that proxy also needs sysadmin, as does the user account running the procedure from any other scheduler, as is your described scenario.


One SQL statement different results by using SQLOLEDB or MSDASQL I'm using VB6 with SQLOLEDB and MSDASQL. By switching the provider I notice that I'm getting different results with one query.

My query checks some server settings and executes one of two BACKUP statements.

When I'm using SQLOLEDB everything works fine and the backup gets created. If I'm using MSDASQL no error occurs but no backup gets created.

It's the same server, the same user and the same statement.

I've used SQL Profiler and it shows that 100% the same command gets executed. Even if I copy the MSDASQL command and execute it in SSMS the backup gets created, but not with VB6.

My statement:

DECLARE @s varchar(1000), @ver int
SET @ver = 10
SELECT @s = case
WHEN
@ver > 9
THEN
'BACKUP DATABASE [DB] TO DISK = ''C:\SQL Backup\Backup.dat'' WITH  COMPRESSION, NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10'
ELSE
'BACKUP DATABASE [DB] TO DISK = ''C:\SQL Backup\Backup.dat'' WITH  NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10;'
END
EXEC (@s)

But when I'm executing:

DECLARE @s varchar(1000), @ver int
SELECT @s = case
WHEN
10 > 9
THEN
'BACKUP DATABASE [DB] TO DISK = ''C:\SQL Backup\Backup.dat'' WITH  COMPRESSION, NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10'
ELSE
'BACKUP DATABASE [DB] TO DISK = ''C:\SQL Backup\Backup.dat'' WITH  NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10;'
END
EXEC (@s)

(without SET @ver = 10) the backup works.

Could someone explain?

OLEDB via ADO has always had the issue where DONE_IN_PROC messages (rows affected) confuse code that is not expecting them.  A workaround without app code changes is to specify SET NOCOUNT ON as the first statement in the batch:

SET NOCOUNT ON;
DECLARE @s varchar(1000), @ver int;
SET @ver = 10;
SELECT @s = case
WHEN
@ver > 9
THEN
'BACKUP DATABASE [DB] TO DISK = ''C:\SQL Backup\Backup.dat'' WITH  COMPRESSION, NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10'
ELSE
'BACKUP DATABASE [DB] TO DISK = ''C:\SQL Backup\Backup.dat'' WITH  NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10;'
END;
EXEC (@s)

EDIT:

A TDS protocol DONE, DONEINPROC, or DONEPROC message is returned to the client upon completion of each T-SQL statement and proc.  These messages can optionally include a row count (indicated by a DONE_COUNT flag).  Higher level APIs like ADO generally ignore these TDS messages except when a row count is included.  But ADO has the nasty habit of returning a result set with no columns or rows in this case.  Not only may this behavior vary by provider/driver, it is further complicated when informational and error messages are generated but not consumed by the client.  This can result in a multi-statement batch that does not run to completion and errors that are not detected.

SET NOCOUNT ON is a convenient way to avoid these issues rather than write code to handle all messages and skip these empty result sets.

I should add that this applies to unmanaged "classic" ADO, not ADO.NET.


Are existing entries immediately included into a newly created index? Suppose I have a table Stuff with a column City and no indices including that column. I populate the table. Then I decide to create an index:

CREATE INDEX [StuffOnCityIndex] ON [dbo].[Stuff](City ASC);

and the database confirms that CREATE INDEX succeeded.

Will the newly created index immediately include all existing entries from the table or will it be slowly built "in background"? In other words, if I think that creating an index will improve performance then will I see that improvement immediately upon CREATE INDEX completion?

The CREATE INDEX is unit of work that follows ACID.

So when it completes, it has completed (or failed) completely ("Atomic" in ACID). No further work is needed to populate or otherwise prepare the index ("Consistent" in ACID)

Whether it improves performance depends on it's suitability for the queries you are running, data distribution etc. That is a whole new area...


Using COPY in psql script does not write to file I have a script with multiple queries I am triying to write to a csv file:

copy (
   select /* very long query */
) to '/tmp/report.csv' with csv header;

And I run the script like this

psql -d mydb -f produce_reports.sql

The script seems to work but nothing is written to /tmp.

I usually use \copy but I am unable to use this in a script. From what I understand using \copy require having all the command on the same line which is not a viable option for me.

Any idea why COPY report success but don`t write anything?

P.S tried runnig with sudo, with psql, and tries granting mydb user superuser.

I usually use \copy but I am unable to use this in a script. From what I understand using \copy require having all the command on the same line which is not a viable option for me.

No all of the commands don't have to be on the same line, but that's not the problem here. COPY is saving the file on the server. You want it on the client, that's what the psql command \COPY does. I assume you're connecting to the remote server which is where the file is. Likely you could just scp or rsync it back to the client or the like, but that's a bad idea. You're better off doing it with just a client.

There are numerous ways to put the \copy on a different line. For instance, take this test.psql

CREATE TEMP TABLE t AS VALUES (1,2), (2,3);
\COPY t TO /tmp/1
CREATE TEMP TABLE b AS VALUES (1,2), (2,3);
\COPY t TO /tmp/2

You can run it with psql -d test -f test.psql

COPY is a PostgreSQL server-command that is made to look like SQL.
\COPY is a PostgreSQL client (psql)-command that is made to look like COPY


Assign a value to a field of a composite type (a row in an array of rows)? I am currently writing a function that works with an array of rows. I need to write a value to a particular column of a particular row in the array. The following code should encapsulate the problem I'm having.

drop type if exists test_type;

create type test_type as (
  some_int int
);

create or replace function test()
returns void as
$$
declare
x test_type[];
y test_type;
begin
  x[1] = row(1);
  y = row(1);

  /* DOESN'T WORK */
  /* ERROR: syntax error at or near "." */
  /* x[1].some_int = 2; */

  /* HOWEVER, THIS WORKS */
  y = x[1];
  y.some_int = 2;
  x[1] = y;

  raise notice '%', x[1].some_int;
end;
$$
language plpgsql;

As you can see, referencing x[1].some_int is perfectly legal in most contexts but not for assignment. The workaround accomplishes the goal; however, it involves two additional copies via the assignment operator.  Is there any explanation for why this is an error or how to work around it and accomplish this task without creating extra row copies?

Yes, this feature is missing in the arsenal of array functions. It's rather exotic, and one would typically operate with a (temporary) table instead of an array of rows.

That said, there is a way with a single assignment, using the hstore #= operator:

CREATE OR REPLACE FUNCTION f_test()
  RETURNS int LANGUAGE plpgsql AS
$func$
DECLARE
   x test_type[] := '{(1)}';
BEGIN
   x[1] := x[1] #= hstore '"some_int"=>"2"'; -- !!!
   RETURN x[1].some_int;
END
$func$;

SELECT f_test();

| f_test |
| -----: |
|      2 |

db<>fiddle here

The additional module hstore must be installed. In-depth explanation:

How to set value of composite variable field using dynamic SQL


Mysql time shifed replication We got a system with a Mysql database and a replication on a slave database.
We got huge uploads, which will do major changes to the master database. We want to keep the slave database the old version before the changes for a certain while.

Is it possible to set up the replication update to a certain time shift?

As EJP say you can work manually using the stop/start slave, so you can stop replication using:

mysql> STOP SLAVE;

than resume when your checks are finished using:

mysql> START SLAVE;

Alternatively you can manage the delay of a replica using the MySQL Delayed Replication, note that this feature is available only on 5.6.x + MySQL version.

I think that a good idea where to start (but there are many configuration for your scenario) is to have 2 node attached in replica, one standard replica and one delayed replica:

Master
`--- Standard Replica
`--- Delayed Replica

In this configuration you have all the advantages in case of a master crash(promoting the standard replication) and all the advantages for checking your "past" :) and/or a point in time recovery


Does the table constraint CHECK gets executed on deletions? I guess not but if someone has an idea perhaps could give me a hand.

I'm trying to restrict the deletion of a row of a table under some circumstances that requires to query against other tables.

An example

Each worker (table worker) references the physical person (table person).
The check aims to allows the existence of workers that will work with his actual_hapiness_level (from table worker) >= base_hapiness_level (from table person), so all the underlying persons are happy.

There is an important exception, is you hire a worker that acts a clown, everybody is happy and therefore the former restrictions doesn't apply. Try to follow the example running one statement at the time, reading the comments.

And the end you will get inconsesty from a "business" point of view.

create table person (
    id serial primary key,
    base_hapiness_level int,
    name text
);
create table worker (
    id serial primary key,
    person_id int,
    actual_hapiness_level int,
    acts_as_clown bool default false,
    CONSTRAINT person_id_fkey FOREIGN KEY (person_id)
          REFERENCES person (id) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE OR REPLACE FUNCTION is_worker_happy_according_to_his_personal_values (bigint,bigint) RETURNS boolean AS $BODY$
DECLARE
    BEGIN
    RAISE NOTICE 'Performing things_are_okey(person_id= %,base_hapiness_level= %)', $1, $2;
    RETURN ($2 >= (select p.base_hapiness_level from person p where p.id = $1));
    END;
$BODY$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION is_there_a_clown_in_there_to_handle_the_unhappiness () RETURNS boolean AS $BODY$
DECLARE
    BEGIN
    RAISE NOTICE 'Performing is_there_a_clown_in_there_to_handle_the_unhappiness';
    RETURN EXISTS (select * from worker where acts_as_clown is true);
    END;
$BODY$ LANGUAGE plpgsql IMMUTABLE;

ALTER TABLE worker ADD CONSTRAINT person_can_work check (
    is_worker_happy_according_to_his_personal_values(person_id,actual_hapiness_level) OR
    (NOT is_worker_happy_according_to_his_personal_values(person_id,actual_hapiness_level) AND is_there_a_clown_in_there_to_handle_the_unhappiness())
);

-- create 3 persons
insert into person (base_hapiness_level,name) values (50,'robert');
insert into person (base_hapiness_level,name) values (10,'cris');
insert into person (base_hapiness_level,name) values (0,'the_clown');

-- insertion 1 is okey, as there is no clow required to the hapiness level of robert
insert into worker (actual_hapiness_level,person_id) values (50,(select id from person where name = 'robert'));

-- insertion 2 goes wrong, as there is no clown to handle the unhapiness of crish
insert into worker (actual_hapiness_level,person_id) values (1,(select id from person where name = 'cris'));

-- let's hire a clown.....
insert into worker (actual_hapiness_level,person_id,acts_as_clown) values (100,(select id from person where name = 'the_clown'),true);

-- try to insert cris again (with the clown is alright)
insert into worker (actual_hapiness_level,person_id) values (1,(select id from person where name = 'cris'));
-- and works ;)

-- but if we kick the clown, now there is no one to handle the unhapinness what a mess!! what about the check :(!!!
delete from worker where person_id = (select id from person where name = 'the_clown')

As you can realize, the check is not beign performing on the deletion of the clown and that is a problem in this example domain model

Have you tried ON DELETE RESTRICT ? You can combine this constraint with a function attached to the foreign tables CREATE TRIGGER ON DELETE

CREATE TABLE my_table (
  foreign_key_id uuid REFERENCES my_foreign_table ON DELETE RESTRICT
);

CREATE OR REPLACE FUNCTION foreign_object_delete()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM my_table
    WHERE foreign_key_id IS NULL
    AND foreign_key_id = OLD.foreign_key_id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER foreign_row_trigger BEFORE DELETE ON my_foreign_table
    FOR EACH ROW EXECUTE PROCEDURE foreign_object_delete();

Update:
Check a condition in several tables before deleting the record

CREATE OR REPLACE FUNCTION foreign_object_delete()
RETURNS TRIGGER AS $$
BEGIN
   IF SELECT exists(SELECT 1 FROM table_x WHERE column_x = 'SAMPLE') THEN
      DELETE FROM my_table
      WHERE foreign_key_id IS NULL
      AND foreign_key_id = OLD.foreign_key_id;
   END IF;
      RETURN OLD;
END;
$$ LANGUAGE plpgsql;


Large (>22 trillion items) geospatial dataset with rapid (<1s) read query performance I'm in the process of designing a new system for a large geospatial data set that will require rapid read query performance. Therefore I want to see if anyone thinks it is possible or has experience/advice about suitable DBMSs, data structure, or alternative methods to achieve the required performance in the following situation:

Data will be continuously produced from processed satellite radar data, which will have global coverage. Based on the satellite resolution and land coverage of the globe, I estimate the full data set to produce values at 75 billion discrete locations on the globe. Over the life span of a single satellite, the output will produce up to 300 values at each of these locations (so a total data set of >22 trillion values). This is for one satellite, and there is already a second in orbit, with another two planned in the new few years. So there will be a lot of data! A single data item is very simple and will only consist of (longitude, latitude, value), but due to the number of items I estimate a single satellite to produce up to 100TB.

The written data should never need updating, as it will only grow as new satellite acquisitions are processed. Write performance is not important, but read performance is crucial. The goal of this project is to be able to visualize the data through a simple interface such as a layer over google maps, where each point has a colored value based on its average, gradient, or some function over time. (demo at end of post).

From these requirements, the database needs to be scalable and we are likely to look towards cloud solutions. The system needs to be able to deal with geospatial queries such as "points near (lat,lon)" and "points within (box)", and have read performance of < 1s for locating a single point, and polygons which contain up to 50,000 points (although up to 200,000 points would be preferable).

So far I have a test data set of ~750 million data items at 111 million locations. I've trialed a postgres/postGIS instance, which worked OK, but without the possibility of sharding I don't this this will be able to cope as the data grows.I have also trialed a mongoDB instance, which again appears to OK so far, and with sharding it might be sufficient to scale with the data volume. I've recently learnt a little about elasticsearch, so any comments on this would be helpful as it is new to me.

Here is a quick animation of what we want to achieve with the full data set:

This gif (from my postgres trial) is serving (6x3) pre-computed raster tiles, each containing ~200,000 points and taking ~17s to generate each. By clicking a point the graph is made by pulling all historic values at the nearest location in < 1s.

Apologies for the long post, all comments/advice are welcome.

How up-do-date do your read queries need to be?

You could partition the database by time if the map just needs to show the most recent measurement.  This would reduce your query load for the map.

For the history of a given point, you could hold a second store by x and y showing the history.  This could be done with a nightly refresh/update as the the historical data won't change.

Then you could pre-compute averages at more coarse resolutions for integrating with maps at different zoom levels.  This would reduce the number of points to retrieve for large map areas (zoom out).  Finer resolutions would be used for more zoomed in maps which were querying smaller areas.  If you really need to speed this up you could compute tiles as blobs and interpret them in your application.

Because these would involve some re-computing of aggregate information there would be some latency in query results.  Depending on how much latency was acceptable you could use this sort of approach to optimise your reads.

OK, so your points need to be computed averages over time.  With this computation I guess your actual queries come down quite a lot from 22 trillion items as the raster values can be pre-calculated for querying.


Cannot use phpmyadmin in XAMPP I have installed XAMPP 5.5.30 for Linux(64 bit), which consists of phpmyadmin version 4.5.1. For the first time when I went to phpmyadmin, saw a warning at the bottom saying

  The phpMyAdmin configuration storage is not completely configured,
  some extended features have been deactivated. Find out why.
        Or alternately go to 'Operations' tab of any database to set it up there.

I didn't give much attention to that. I tried creating a database. At that time I got another error,

  Table phpmyadmin.pma__tracking doesn't exist in engine

I tried to create the database with the same name again and this time the error was

  Can't create database,database exists

So I refreshed the list of databases and my new database was there and when I clicked on it a "Loading..." loader appeared and it remained there forever. So I tried opening the database in a new tab and this time I got,

  1932 - Table 'phpmyadmin.pma__column_info' doesn't exist in engine

After some research I came to know that all these tables (that did not exist) are a part of the phpmyadmin database. So I clicked on it and I got

  Table phpmyadmin.pma__tracking doesn't exist in engine

I am stuck now. As the phpmyadmin database cannot be selected I cannot add or edit any tables in it. Somebody please suggest a way to fix this issue.

This appears to be a problem with a recent XAMPP release, my suggestion is to roll back to a previous XAMPP version.

That being said, the phpMyAdmin way of fixing it would be:

Comment out the pma__tracking line in config.inc.php. You may also need to comment out the pma__column_info line as well. This will allow you to start phpMyAdmin without the constant error messages.
Start phpMyAdmin normally
Run the included sql/create_tables.sql (making modifications if needed to match the XAMPP naming scheme)
Remove the comments in config.inc.php

Of course, I can't say whether that will also work for XAMPP.


Modifying a result set in a Stored Procedure, then returning that modified set I am currently in the process of designing my database for a mobile/web application. When I look at competing apps I see a massive flaw in how they handle user locations. Due to the nature of the application this could cause privacy and security issues for the users.

I need to store all user's locations in my database, but I want to reduce the possibility of an outside source gaining access to this data if the user does not want it shared.

After researching possible ways of doing this, I believe it is in my best interest to return values through stored procedures, limit db user access etc.

What I want to achieve is returning the results of a stored procedure. After the results have been modified. In this case, the procedure will run through the results. Check a value on the record and omit the relevant data if required.

From what I have read and understood the best way for me to achieve this is through creating a temporary table. Run a Select Query on my actual table into a cursor. Loop through the results, check the relevant value in the record, the insert into the temp table accordingly.

E.g.

Begin Procedure
Check if temp table already exists, drop if necessary
Create Temp Table, based on schema of the "permanent" table.
Run SELECT * FROM PERMTABLE WHERE Location within bounds ORDER BY Location ASC INTO CURSOR
LOOP THROUGH CURSOR
IF 'Show_Loc' IS FALSE
INSERT INTO TEMPTABLE WITHOUT LOCATION
ELSE
INSERT AS NORMAL

SELECT * FROM TEMPTABLE
DROP TEMPTABLE
END

Is there a better way of achieving this, or am I on the right track?

Also I am concerned that there will be a large volume of requests to the same procedure. Will there be any issues if the procedure was called multiple times at the exact same time in regards to temporary tables? Do I need to generate random names, or do MySQL/SQL Server handle this automatically?

Is there a better way of achieving this, or am I on the right track?

Yes there is and no, I don't think you are on the right track. There is almost no reason to use a cursor in this situation.

If ShowLocation is a value on each record in the table then this is the kind of situation where a CASE statement comes in handy.

SQL Server code would be similar to the following

CREATE PROCEDURE dbo.GetLocationData
AS
SELECT
  <all_other_fields_here>,
  CASE
     WHEN ShowLocation = 1 THEN Location
     ELSE NULL
END AS Location
FROM PERMTABLE
WHERE Location within bounds -- not entirely sure how you're doing this
ORDER BY Location;
GO


SQL Reporting Services - T2Embed.dll missing When generating a report in SSRS, I receive the following error:

There was an error generating your report. Please refer to item 673778174106859 for additional diagnostic information. There was an error during the render phase of the report generation. There was an error generating the document Network Statistics There was an error generating the document Network Statistics An error occurred during rendering of the report. --->

Microsoft.ReportingServices.ReportProcessing.UnhandledReportRenderingException: An error occurred during rendering of the report. --->

Microsoft.ReportingServices.OnDemandReportRendering.ReportRenderingException: An error occurred during rendering of the report. --->

System.DllNotFoundException: Unable to load DLL 'T2Embed': The specified module could not be found. (Exception from HRESULT: 0x8007007E) An error occurred during rendering of the report. --->

Microsoft.ReportingServices.ReportProcessing.UnhandledReportRenderingException: An error occurred during rendering of the report. --->

Microsoft.ReportingServices.OnDemandReportRendering.ReportRenderingException: An error occurred during rendering of the report. --->

System.DllNotFoundException: Unable to load DLL 'T2Embed': The specified module could not be found. (Exception from HRESULT: 0x8007007E)

Running a repair install of the specific SQL and RS instance did not help.  Any suggestions?

SQL Server Reporting Services specifically is not supported on Windows Server Core 2008 R2 SP1 and higher.

You've probably stumbled upon one of the reasons why it's not supported. (I.e., it flat out doesn't work.)

Besides hacking around, which may or may not actually fix the issue, I think your only recourse is to install SSRS on a full version of Windows Server, and try again.


Is there a difference between text_pattern_ops and COLLATE "C"? If I have a text column name with a collation, for example tr-TR, and I run a query
SELECT * FROM t WHERE name LIKE 'a%'

then this will do a seq scan.
If I now create an index
CREATE INDEX ON t(name text_pattern_ops)

the above query will become a bitmap scan. But there is another technique that will achieve the same result:
CREATE INDEX ON t(name COLLATE "C")

Are those approaches completely equivalent or are there differences?

The manual:

Note that you should also create an index with the default operator
class if you want queries involving ordinary <, <=, >, or >=
comparisons to use an index. Such queries cannot use the
xxx_pattern_ops operator classes. (Ordinary equality comparisons can
use these operator classes, however.) It is possible to create
multiple indexes on the same column with different operator classes.
If you do use the C locale, you do not need the xxx_pattern_ops
operator classes, because an index with the default operator class is
usable for pattern-matching queries in the C locale.

So, the index with COLLATE "C" does everything a text_pattern_ops would do,  plus more which the latter cannot. Like support this query:
SELECT * FROM t
WHERE name >= 'z' COLLATE "C";

COLLATE "C" must be specified explicitly in the query to make the COLLATE "C" index applicable (unless you operate with "C" collation to begin with, but then why the index?)
The above query requires the feature of "per-column collation support" (as does your index). The feature was added with Postgres 9.1. The operator class text_pattern_ops is much older and made a lot of sense before Postgres 9.1.
Since Postgres 9.1 the COLLATE "C" variant is superior as it is more versatile. The only reason I could think of would be to intentionally not support additional use cases for some odd reason. The manual might actually add a deprecation notice for the operator classes xxx_pattern_ops.


AlwaysOn Geo Dispersed Automatic Failover I'm looking to implement a "not quite" hub and spoke type architecture using AlwaysOn and wondered how the cluster quorum might play into this scenario if a remote site can't connect to the hub where we setup a file share witness or the other nodes in the underlying cluster. If the remote location was the primary for the AG and had a local secondary and also a remote secondary (Corp), would the AG automatically failover to the remote secondary even though there may not be an issue inside the remote location?

For example: The hub we will call corporate and the remote locations we will call stores, all on the same AD domain. I put all twelve stores (two servers each, a primary and a local secondary) and the corporate server (will be a central secondary to each of the remote primary servers) into the cluster together (not using Failover Clustering, just the backbone of AlwaysOn) and setup a file share witness. Now let's say that store 1 looses WAN connectivity back to corporate for a few hours. Since neither of the servers (primary or local secondary) can connect to any other store (node) and the file share witness can't be reached, will the Availability Group automatically failover if I have it set for Automatic Failover and Database Level Health Detection? Or will it understand that it has gotten separated from the cluster and simply do nothing?

I realize this is a bit of a complex use case when it comes to AlwaysOn, but thought I'd see if anyone had knowledge of how this scenario might work.

There is a max of 8 secondary replicas for AlwaysOn in SQL 2016, I do not believe there is a change for that in 2017

  Always On Availability Groups on 2016 supports up to eight secondary replicas including one primary replica and two synchronous-commit secondary replicas (no Enhancement on this compared to SQL 2014). Also use SQL Server 2016 Always On Availability Groups to provide high availability for SQL Server databases hosted in Azure, etc. Source

In any case adding a local secondary would not add value from a SQL failover scenario.  Only the instance with votes for quorum can be primary, of these there can only be three potential primaries

  Another enhancement is an increase in the number of Automatic Failover partners which means you can have two more failover partners apart from the primary. In a three node scenario, if one node fails still we can have high availability with other two nodes. Source

Automatic failover requires synchronous-commit, Spreading synchronous-commit over geographic distance can have an impact on performance.

If you have a 3 node cluster (file share and other quorum considerations excepted ...), any node that can not talk to the at least one of the other two will be down. If all three loose connection, there will be no auto failover (all three will be down). If any two can talk, one of those two will be primary.

Any of the replicas that can communicate with the current primary can have asynchronous copies that are current.   So if corporate goes down, one of your two stores will be primary, and you could have several stores with asynchronous read only copies.

Given your scenario you probably want the primary at corporate, and your two synchronous-commit automatic failover partners at the stores that provide the fastest and most dependable connections to corporate.


Locking/transactions in MySQL Stored Procedure Multiple upstream Kapacitor servers are sending notifications to a load-balanced django app. If all the upstream servers are working correctly, the app will always receive duplicates of these notifications (since all the upstream notifiers should send the same notifications; it's just for redundancy). I want to be able to filter out these duplicates. However, since the python app is load-balanced, the only place we can check for these duplicates is in the database.

This means I'm using this stored procedure to control application logic, just inserting the data with the hash into the database and ignoring duplicates is not an option (the application might do something like send someone an SMS message based on the contents of the alert, so we definitely don't want dupes)

To do this, I'm hashing the messages, then invoking a stored procedure in the database that checks if a message received in the last 10 seconds had the same hash. I want to be 99% sure the stored procedure is safe against race conditions.

Here's some SQL code that seems to work:

DROP PROCEDURE IF EXISTS openduty_check_duplicate;
CREATE PROCEDURE openduty_check_duplicate(IN new_hash CHAR(40))
  BEGIN
    DECLARE now TIMESTAMP;
    DECLARE is_duplicate INT;
    SELECT CURRENT_TIMESTAMP INTO now;
    DO GET_LOCK('openduty_check_duplicate', 10);
      START TRANSACTION;
        DELETE FROM openduty_dedup WHERE DATE_SUB(now, INTERVAL 10 SECOND) > triggered;
        SELECT COUNT(*) FROM openduty_dedup WHERE request_hash = new_hash INTO is_duplicate;
        IF is_duplicate = 0 THEN
          INSERT INTO openduty_dedup (request_hash, triggered) VALUES (new_hash, now);
        END IF;
      COMMIT;
    DO RELEASE_LOCK('openduty_check_duplicate');
    SELECT is_duplicate;
  END;

Will this work as intended? DO I need to change the transaction isolation level? Is there a better way to do this in MySQL?

Note I'm using MySQL 5.6 in one environment and Amazon RDS in another environment, so it should be as compatible as possible.

Do these with autocommit=1, with PRIMARY KEY(request_hash):

DELETE FROM openduty_dedup
    WHERE triggered < NOW() - INTERVAL 10 SECOND
      AND request_hash = $hash;
INSERT IGNORE INTO openduty_dedup
    (request_hash, triggered)
    VALUES ($new_hash, NOW());

If the INSERT fails, either there is an entry in the last 10 seconds, or someone else snuck in just now.  If you need to know about success, check Rows_affected(), which is connection-specific and does not need to be in a transaction.

No need for START TRANSACTION.  No need to pre-compute "now".  No need for GET_LOCK().  The only flaw is the hard-coded 10 second window.