Are database backups specific to the OS on which they are created? I am trying to get a better understanding of the behaviors of database backups for Sybase (15.X) and Oracle (11.x) as it relates to the underlying OS. Is the backup file OS specific or can it be backed up on one OS and restored on a separate OS without issue?

If we wanted to support a customer on Oracle (Linux) and wanted to be able to restore their database for support purposes but only had Oracle on AIX is this possible?

If it is feasible does it depend on the settings used to generate the backup?

I would like to know for both Sybase and Oracle versions mentioned above.

Thank you for your help.

In Oracle, you can restore onto an OS/CPU with the same endianness in most cases. This can be done with RMAN, the standard Oracle backup and restore tool, but it might be worth it to  investigate options such as DataPump or a Linux VM on AIX (but I don't know how that would deal with endianness). x86 is little-endian and POWER is big-endian, so this would not necessarily be straightforward.


Login in server suddenly lost mapping to their Users in databases Mine is Always ON availability environment. I have 3 SQL 2014 servers(Primary and 2 replicas). Since one year all the logins working FINE in the servers. Some how suddenly, I am getting errors from the Application server stating that the PIDS were not able to access the Database. The PIDS are successfully logging in the server but not able to access database. I checked the mappings of the login in the server. I seen no database was checked. I dont know how it lost its mapping to user in the database.

When I checked for the user in the database, I can see that user exist in the database and has all the required privileges assigned to that user too..

NOte: I tried to restart the server that which fix the problem for 2 hours and again we ran back to the same issue. I droped all those users(who lost mapping from Login) and recreated them with all the SIDs synced in all three servers. This fixed my problem for now. but I want to know the root cause of the issue.

Question 1: How my user lost their mappings with login suddenly that which are working absolutely fine since 1 YEAR.

Question 2: How a simple server restart fix the issue for a couple of hours and again why the problem came back.

Since an Availability Group is just a container of databases, you should treat users and logins the same way you would for a mirrored database, log shipping, etc. And that is that the logins on a secondary server must be created with the same explicit SID as the logins on the primary server. Otherwise the link between the server-level login and the database-level user will be broken upon failover - which I suspect is what happened here (I'm not going to try to retrace all of your steps and figure out how many times you failed over and back - let's focus on preventing this from happening again).

You say that you re-synced with the SIDs, but I don't know what that generic phrase means. You should include the code that demonstrates what you did, because if it isn't doing what you think it's doing, we may be able to tell you why, but we can't do that with just hand-waving.

The script I think you should be using is the one from Robert Davis, which Mike Walsh describes here (I'm not going to copy the code here):

Transferring Logins to a Database Mirror

There is also an SSMS add-in written by Jonathan Kehayias that helps automate synchronization of things like logins and jobs across all the nodes involved with an availability group:

Synchronize Availability Group Logins and Jobs
SQL Server Management Studio Availability Group Add-in

However, this add-in only works in SSMS 2012/2014.

Length of time is irrelevant here. You had a year of uptime. Yay! You could failover tomorrow and be right back in the same boat.


PL/SQL for Updating 1 Column in Table A Where PK = a row in table B Using an oracle DB, 11g r2, I need to write a pl/sql which updates one column in table A where the PK exists in table B, with commits ever ~50,00 rows.

3200000 rows are involved in the update, and table A has ~70 million rows total.

What is the most efficient way to do this?

Table A (has ~70 million rows, and ~100 columns. Only one needs to be updated):

CREATE TABLE ORD
   (
    ORDER_KEY NUMBER NOT NULL,

    /* ~95 other columns */

    ORDER_TYPE" NUMBER(10,0) /* col needing update */
   )

Order key has an index.

Table B (contains the id's of the rows from table a which need the update):

CREATE TABLE GR_ORD (ORDER_KEY NUMBER NOT NULL)

Most efficient way to do the update is most likely not with PL/SQL, but with single SQL statement, for example:

update ord
set order_type=<new value>
where order_key in (
  select order_key
  from gr_ord
);

or with merge:

merge into ord
using gr_ord
on (ord.order_key = gr_ord.order_key)
when matched then
  update set order_type=<new value>;

You should be able to do ~3M update in a single transaction. I have run updates against table with 100+ columns at rate of 1M updates in 15 seconds (single thread in average server).


ONLINE vs OFFLINE index rebuilds I am looking for an opinion, which I know is usually frowned upon, so my apologies in advance.

I'm currently in an environment where every instance is SQL Server enterprise edition.
The maintenance tasks are being performed by a custom plan. This plan by default does an offline rebuild during the night.

There are no nightly maintenance windows, and as the environment is multinational the "nightly" part of the maintenance doesn't always mean the lowest activity point (however it does in 95% of the cases). And there are a lot of nightly jobs that are currently running slow due partly to aggressive indexing maintenance.
(A reporting query that got stuck waiting for the index rebuild of a 300GB table with customer data led me to this route)

Can I assume that the following will have mostly positive impact:

Add a minimum fragmentation value of 10% for reorganize (currently done every night regardless of %)
Changing minimum rebuild requirements to 30% (currently at 5%)
Changing default behavior to ONLINE
Changing minimum page count to 1000 (currently at 50)

Mainly what I'm asking here is, am I correct in assuming the current maintenance schedule is too aggressive. And that the above 4 changes will have a net positive effect.

I'm mostly worried about the reorganize everything every night for everything... That seems excessive.

Hard for us to know if it will be a net positive. These are pretty much no-brainers:

setting all operations to ONLINE, because you can
ignoring tables with < 1000 pages

You should still test those, though.

But we have no idea what impact defrag/rebuild has on your read workload, and whether holding off until some % is better, gains you nothing, or maybe even makes some read queries worse.

Other people have already perfected this type of maintenance and taken you out of the guessing game. Get Ola Hallengren's scripts or our tool, Fragmentation Manager. You can make changes like you're suggesting by simply changing parameter values. And don't forget that these people have already worked out lots of bugs you haven't come across yet and thought of things you haven't thought of yet. And when new operations are offered online in newer versions, or new options are added to different maintenance features, these tools and scripts will be updated for you. What you've done is affectionately known as re-inventing the wheel:

Re-Blog : The cost of reinventing the wheel

Refusing to move to a more automated solution because of investments in the past is kind of like keeping a terrible housekeeper or gardener because you paid them last year. Sometimes you just need to cut the cord.


Unable to connect with root because of skip-name-resolve option We had our MySQL server on an old server. We decided to move all of the databases to our new server.

Old MySQL Server engine 5.5.31
New MySQL Server engine 5.5.38

We created one general dump file without logs from all of the Databases in the old server and we restored the .sql file in the newly created MySQL Server on the new server.
On the new server when we put skip-name-resolve option in the my.ini  everything is fine but we can't login to MySQL server with root account.
We tested the issue on old server and we didn't have any issue with skip-name-resolve.
We removed skip-name-resolve option and we tried to login to MySQL server.
after login when we tried to list the available users. Like previous MySQL server here after restoring the dump we have two root users when we execute:
select user,host from mysql.user;

For the first one % is defined as host and for the second one LOCALHOST is defined as host.
All of the users are defined with % as their host. We want to use skip-name-resolve and on another hand resolve our issue with root account.

The issue may be that you have 2 entries for root, and that root@% doesn't have the same password as the root@localhost. Changing options may change the order MySQL uses each entries.


Return distinct rows based on matching data in a column in another table in PosgreSQL I have a table (e_v2) with some time-series data. I want to fetch all rows where the data in the "POINT_KEY" column appears in the "KEY" column in another table (etraining_v1). The code below (I'm unsure whether it's correct) did return some rows, but there are many duplicates. I would like to exclude the duplicates.

SELECT * FROM e_v2
WHERE EXISTS (SELECT FROM etraining_v1 WHERE "POINT_KEY" = etraining_v1.key)
ORDER BY
"POINT_KEY",
"INTERVAL_READING"

Each row of e_v2 will be returned at most once by your query.  Any duplicate values you are getting are duplicates that already exist in e_v2.  You can remove them by adding a "DISTINCT" before your select-list.

SELECT DISTINCT * FROM e_v2
WHERE EXISTS (SELECT 1 FROM etraining_v1 WHERE "POINT_KEY" = etraining_v1.key)
ORDER BY
"POINT_KEY",
"INTERVAL_READING"

Or you an clean up e_v2 so that it doesn't contain duplicates.


MySQL replicate-rewrite-db ALTER TABLE not rewritten I have a production database called "master" that I want to replicate on another server under another name (call it "slave").

All worked fine until an ALTER TABLE statement was performed on the master server. Since then, the replication hung up and I get this error in the slave log:

[ERROR] Slave SQL: Error 'Table 'master.students' doesn't exist' on query. Default database: ''. Query: 'ALTER TABLE `master`.`students`

Well of course it does not exists since I told MySQL to rename "master" to "slave"! But it seems like MySQL decided not to rename the database in ALTER TABLE statements.

Here is an excerpt from my slave config.

server-id   = 2
relay-log   = /var/log/mysql/mysql-relay-bin.log
log_bin     = /var/log/mysql/mysql-bin.log
binlog_do_db   = slave
replicate-rewrite-db="master->slave"

It turns out I was using MySQL Workbench and it does not output a "USE" statement in its DDL update scripts, also rewriting does not occur if you do not provide a use statement.
So adding USE master; at the beginning of the script fixed it. Thanks!


Redshift: do with clauses /CMTs ALWAYS create temporary tables? I like to use "with clauses" as filters: it makes my queries more "functional" in the linearish functional programming style x= a.b().c().filter(condition).sum() sense (obviously, SQL is one giant functional language, it's just a bit obtuse).

If I am careful about avoiding groupings, distincts or orderings, and only use a CMT in one following query/CMT, will Redshift dispense with the temporary table, and just treat it like a filter? Monitoring seems to suggest this to be the case.

For instance, will this query create two temp tables, or will the planmer say: "hey! no need to store any data…"?

with FilteredList as (
          select col1, col2 from Table
          where col1 is not Null
     ),
     MoreFiltering as (
           select col1, col2 from FilteredList
           where col2 is not Nill
      )
select col1+col2 as Summo
from MoreFiltering;

If you want to know what is going to do the planner. I would suggest to do
EXPLAIN

I would say your planner will execute both CMT


Non-RAC single instance with ASM startup query How to start a non-rac single instance with ASM file system. As in the case of RAC after reboot of server, the ASM and CRS resources startup automatically.. but with non-RAC but with ASM storage, what is the right way to start the database?

Is there anything specific that should be given attention to start the database?

Start the ASM instance and mount all the diskgroups and then start the database?

Correct me/add if i'm wrong.

Server- RHEL Database - 12c

Check if autostart for Oracle Restart is enabled:

$ cat /etc/oracle/scls_scr/$HOSTNAME/oracle/ohasdstr
enable

If it is not enabled, then enable it:

crsctl enable has

Check if ASM autostart is enabled:

crsctl stat res ora.asm

If ASM is not registered in GI, add it with:

srvctl add asm ...

Check if used diskgroups are registered:

srvctl status diskgroup -g DATA
crsctl stat res ora.DATA.dg

If they are not registered, add them with:

srvctl add diskgroup ...

Check if the database is registered and autostart is enabled:

srvctl config database -d ORCL

If it is not, register and enable:

srvctl add database ...
srvctl enable database ...

Finally make sure you define the used ASM diskgroups as dependencies:

srvctl modify database -d ORCL -diskgroup "DATA,FRA"

You can also check the listener:

srvctl config listener

If it does not exist, you can add with:

srvctl add listener ...

You do not need to start or stop anything manually with sqlplus, lsnrctl. Oracle Restart takes care of that based on the defined start/stop options and dependencies.


MySQL query takes too long in codeigniter I've made this query

SELECT id,name,iupac_name,inchi_key,molecular_weight,molecular_formula,exact_molecular_mass
    FROM compound
    WHERE name LIKE '%quer%'
    OR iupac_name LIKE '%quer%'
    OR inchi_key LIKE '%quer%'
    OR inchi_key IN( SELECT inchi_key FROM compound_synonyms WHERE name LIKE '%quer%')

that took 0.3563 seconds to show result through phpmyadmin in my local machine and took 0.1117 seconds in digital ocean vps.

But the same query took 24+ minutes in another vps.

Does anyone can help me to find out the problem?

Several things make the query slow:

Leading wildcard in LIKE.  (See below)
OR -- One workaround is with UNION.
IN ( SELECT ... ) -- This one is version-dependent; what version is each machine running?

The first two can be alleviated by using a FULLTEXT(name, iupac_name, inchi_key) and MATCH(name, iupac_name, inchi_key) AGAINST('quer*' IN BOOLEAN MODE).


Combine addresses missing Unit/Suite # I have a list of addresses which need to be updated in SQL Server. How can I go about adding the Suite # to the addresses which are missing it?

Address
1 Burton Hills Blvd, Suite 250
1 Burton Hills Blvd
1 Burton Hills Blvd
1 Burton Hills Blvd
1 Burton Hills Blvd
1 Burton Hills Blvd
1 Ford Pl
1 Ford Pl, Suite 5B
1 Kish Dr
1 Park Center Dr, Suite 11
1 Park Center Dr

This is not a clean problem.  The following should work assuming that there will not be multiple street addresses with different suite numbers.  Also the data will have to be formatted with the comma and space before the Suite.  I added a duplicate record (1 Burton Hills...) for testing the group by logic.

Create Table Addresses (Address Varchar(8000))

Insert Addresses (Address) Values ('1 Burton Hills Blvd, Suite 250')
Insert Addresses (Address) Values ('1 Burton Hills Blvd, Suite 250')
Insert Addresses (Address) Values ('1 Burton Hills Blvd')
Insert Addresses (Address) Values ('1 Burton Hills Blvd')
Insert Addresses (Address) Values ('1 Burton Hills Blvd')
Insert Addresses (Address) Values ('1 Burton Hills Blvd')
Insert Addresses (Address) Values ('1 Burton Hills Blvd')
Insert Addresses (Address) Values ('1 Ford Pl')
Insert Addresses (Address) Values ('1 Ford Pl, Suite 5B')
Insert Addresses (Address) Values ('1 Kish Dr')
Insert Addresses (Address) Values ('1 Park Center Dr, Suite 11')
Insert Addresses (Address) Values ('1 Park Center Dr')

;
With cteWithSuite As
    (
    Select Substring(Address, 1, CharIndex(', Suite', Address)-1) AddressRoot, Address
        From Addresses
        Where CharIndex(', Suite', Address) > 0
        Group By Substring(Address, 1, CharIndex(', Suite', Address)-1), Address
    )
Update B Set B.Address = A.Address
    From cteWithSuite A
    Join (Select Address
            From Addresses
            Where CharIndex(', Suite', Address) = 0
        ) B On B.Address = A.AddressRoot


Mysql deterministic function, scope? The documentation for MySQL isn't explicit on this point, but I'm wondering whether the scope for a function with respect to DETERMINISTIC is database global, or by some other scope?

Consider this definition:

CREATE
FUNCTION test()
RETURNS VARCHAR(255) DETERMINISTIC
RETURN USER();

This function is determinstic for all calls in the same connection, as user() will always return the same value. However, does DETERMINISTIC mean that "computed" values are shared across connections?

Defensively I would treat this function as NOT DETERMINISTIC but the question came up and I'm curious about the answer.

Formally, since the function USER() is not deterministic according to that term's definition:

  A routine is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise.

your function that simply returns its result is also not deterministic.

However, the manual further states that

  Assessment of the nature of a routine is based on the “honesty” of the creator

What it means is that you define the scope where it is deterministic (or not). Considering that this property is used for query optimization, and that, as you say, within a single session USER() will always return the same result, you can treat it, and the functions that call it, deterministic within the scope of a single query.

This changes, however, if the query cache is enabled, as the cache is shared among sessions and it is possible that sessions other than yours might get results from the cache that would be invalid for them.


change file table file stream data folder faces error The container can not be dropped I want to change file database file stream folder from Drive D to Drive F. I successfully transferred data to new folder but when I want to Remove old file stream folder I face this error:
"The container can not be dropped because changes exist that require a log backup. Take a log backup and then retry the ALTER DATABASE operation."
When I want to get transaction log, It encounter this error: you should enable filestream on this instance, while filestream is enabled.
This my code:
ALTER DATABASE MyDatabase
REMOVE FILE MyDatabase_Files;
GO

Thanks for any helps.

As the message suggests, you need to backup the transaction log in order to move on to the next step. You can do this inside SSMS with a couple of clicks or, if you prefer writing SQL, you can do this:
BACKUP LOG MyDatabase
   TO MyDatabase_TXN_log1;
GO

Once this is completed the requisite ALTER DATABASE statements can be run before restarting the SQL Server instance


When would one want to use Oracle transparent data encryption Oracle TDE features comes with various limitations such as not being able to encrypt columns which are used in foreign key constraints. And probably is available only with enterprise edition which costs upwards of 100 thousand dollars.

Why would one want to pay and use TDE instead of simply using file system encryption with the OS? This is free and byepasses various limitations regarding FKs etc.

Am I missing some advantages that it provides?

Transparent data encryption guarantees the data is always encrypted at rest, implying even the backups are encrypted, no matter where they are physically stored, be it tape, disk, a DVD, etc.

Encrypting data at rest is a great way to control who sees the data, even if someone misplaced an entire copy of the database or a steals a database backup.


MYSQL ORDER BY Performance We have a query like this:

SELECT
 PROJECT.PROJECTID, REQUEST.REQUESTID,
 SAMPLE.SAMPLEID,SAMPLE.OTHERSAMPLEID,
 ILLUMINASEQPROTOCOL2.SAMPLEID
FROM
 ILLUMINASEQPROTOCOL2,
 PROJECT,
 SAMPLE,
 REQUEST,
 ANCESTORS A1,
 ANCESTORS A2,
 ANCESTORS A3
WHERE (
 ILLUMINASEQPROTOCOL2.RECORDID = A1.RECORDID AND
 A1.ANCESTORID = SAMPLE.RECORDID AND
 SAMPLE.RECORDID = A2.RECORDID AND
 A2.ANCESTORID = REQUEST.RECORDID AND
 REQUEST.RECORDID = A3.RECORDID AND
 A3.ANCESTORID = PROJECT.RECORDID)
AND  (
 (((ILLUMINASEQPROTOCOL2.RecordId > 0) AND
  ((SAMPLE.EXEMPLARSAMPLETYPE LIKE 'DNA') AND
  (REQUEST.RecordId > 0)))
 AND  (PROJECT.RecordId > 0))
)
ORDER BY SAMPLE.RECORDID LIMIT 0,501;

The RecordId column on all the tables is indexed as is AncestorId.  No other columns are indexed.  Here is what makes no sense.  When i have the query as above, it takes 130 seconds to run on our machines here.  The ORDER BY is what takes all the time since if I remove it the query runs instantly.  But what is weirder is if I change to the ORDER BY to use a non-indexed column like SAMPLE.SAMPLEID, it also comes back instantly.

So when I run this wanting to ORDER BY an indexed field it is MUCH slower than ordering by a non-indexed field.  Any help greatly appreciated.

What sometimes works in this situation is

SELECT * FROM
(
SELECT
 PROJECT.PROJECTID, REQUEST.REQUESTID,
 SAMPLE.SAMPLEID,SAMPLE.OTHERSAMPLEID,
 ILLUMINASEQPROTOCOL2.SAMPLEID
FROM
 ILLUMINASEQPROTOCOL2,
 PROJECT,
 SAMPLE,
 REQUEST,
 ANCESTORS A1,
 ANCESTORS A2,
 ANCESTORS A3
WHERE (
 ILLUMINASEQPROTOCOL2.RECORDID = A1.RECORDID AND
 A1.ANCESTORID = SAMPLE.RECORDID AND
 SAMPLE.RECORDID = A2.RECORDID AND
 A2.ANCESTORID = REQUEST.RECORDID AND
 REQUEST.RECORDID = A3.RECORDID AND
 A3.ANCESTORID = PROJECT.RECORDID)
AND  (
 (((ILLUMINASEQPROTOCOL2.RecordId > 0) AND
  ((SAMPLE.EXEMPLARSAMPLETYPE LIKE 'DNA') AND
  (REQUEST.RecordId > 0)))
 AND  (PROJECT.RecordId > 0))
) AS mytable
) ORDER BY SAMPLE.RECORDID LIMIT 0,501;

i.e. transform your original query into a subquery within the outer SELECT. Then, put your ORDER BY and LIMIT as subclauses of your outer SELECT.

This should eliminate the bottleneck with the ORDER BY and LIMIT. The reason your query slows down with the ORDER BY is that the query has to retrieve all records and then SORT them - without the ORDER BY, the system can just spit out the first 500 records.

It is puzzling that you do have indexes on the relevant fields. If what I have suggested doesn't work, you could post the table structures and the query execution plan.


Generating a sequence of values How can I change 2,2,2,1,2 into 201,202,203,101,204 in MySQL?

I have a table with data in the format of:

+---+---+---+
| U | C | R |
+---+---+---+
| 1 | 2 | 1 |
| 1 | 2 | 2 |
| 1 | 2 | 3 |
| 1 | 1 | 4 |
| 1 | 2 | 5 |
+---+---+---+

I am looking for a query to turn it into:

+---+-----+---+
| 1 | 201 | 1 |
| 1 | 202 | 2 |
| 1 | 203 | 3 |
| 1 | 101 | 4 |
| 1 | 204 | 5 |
+---+-----+---+

I have a query that is sort of what I am looking for:

SELECT U , C , group_concat( R ORDER BY R )
FROM table
GROUP BY U, C

But this is what I get back:

U-C-R
1-1-1, 2, 3, 5
1-2-4

With MyISAM, you can get an AUTO_INCREMENT that starts over with each new value.

CREATE TABLE foo (
    C INT UNSIGNED NOT NULL,
    N INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(C,N)
  ) ENGINE=MyISAM;
INSERT INTO foo (C)
    SELECT C FROM your_table;

Then...

SELECT 100*C + N FROM foo

gives you the desired numbers.  (If you need U and R, add them to foo, etc.)

If this is a 1-time task, then give that a try.  If you need the numbering to be on-going, then it gets messier.


Introduce or not introduce SSIS to an ETL project Given is an ETL process that works fine using traditional SQL.

The process is well optimized making use of ALTER TABLE permissions and performing partition switching, index creation, partition truncation and statistics updates.

Furhtermore a Scheduler is available that is up to the job.

What benefits would be gained from introducing SSIS to the project? Also would I get rid of needing ALTER TABLE permissions because SSIS interfaces at a lower layer to the database?

If additionally given that the project is small involving only one really large fact table and creating a data warehouse accessed only via SQL (no Analysis Services), does this affect the answer to the question?

The main strengths of SSIS as I see it are 1) the ability to do things across servers which aren't otherwise linked (eg via linked servers) and 2) the ability to do things in parallel (eg multiple Execute SQL tasks operating concurrently).

If you are using traditional SQL then you probably don't need 1), but you might need 2).  Your ETL must be running in serial, all one after the other.  If you look across your whole ETL process, are there any tasks that could run in parallel thus potentially saving you time?

SSIS also has several other abilities, eg the Script Component and Script Task allow you access to sophisticated .net scripting without having to use CLR in your SQL Server.  The Fuzzy Grouping Task allows you to group similar terms for comparison, way better than Soundex!  There are also third-party add-ins such as CozyRoc which has a vastly improved Excel connector and a partitioning task.

Regarding the permissions, SSIS jobs running under SQL Agent normally run under the context of the SQL Agent account, although you can change this using proxies.

Regarding the scale of your project, using SSIS would give you the ability to scale out in the future, eg if you suddenly have to process 10 facts instead of 1, you could do this in parallel whereas your current process would have to do them one after the other, in potentially the same time.  Maybe your scheduler handles some parallelism but I can't imagine it would have the level of control (eg conditional flow, redirect error output) that SSIS gives you.

HTH


How to check with SQL whether a column is an identity column for an Access database? Knowing how to check whether a column is an identity column for Microsoft SQL Server, I currently still found no solution for doing the same with SQL for a Microsoft Access database.

My question:

How can I check with SQL whether a column is an identity column for a Microsoft Access database?

(Since I want to do it in my code, an ADO.NET solution would be helpful, too, although it seems, that this is not possible)

There is a solution with DAO:

Dim db As DAO.Database, tbl As DAO.TableDef
Dim f As DAO.Field
Set db = CurrentDb()

For Each tbl In db.TableDefs
    If tbl.Name = "Table1" Then
        For Each f In tbl.Fields
            If f.Attributes = 17 And f.Type = 4 Then    '(17 = dbAutoIncrField (16) + dbFixedField(1),  4 = dbLong)
                MsgBox ("Table1 idenity column: " & f.Name)
            End If
        Next f
    End If
Next tbl

This checks, if there is a column with Type = 4 [dbLong] and an Attributes value of 17 (16 [dbAutoIncrField] + 1 [dbFixedField]).

This worked fine for me so far.


creating a database scheme for storing information about an item, which option is better? I have a database which stores information about various items. In particular, for each item I need to store the name, type and color. Each type can have a various amount of different colors. For example, I need to be able to ask 'what color can an item of type A have?'

I'm struggling to see how I should best structure my database. Each item has a color and a type, but I also need to capture the fact that each type has a unique range of colors available.

currently I have these ideas:

FIRST OPTION

items            (ID(PK), name, types_and_colors_ID (FK))
types and colors (ID(PK), type_name (FK), color(FK))
types            (ID(PK), type_name)
colors           (ID(PK), color_name)

SECOND OPTION

items            (ID(PK), name, type(FK), color(FK))
types and colors (type (PK FK), color (PK FK))
types            (ID(PK), type_name)
colors           (ID(PK), color_name)

THIRD OPTION

items            (ID(PK), name, type_color_id(FK))
types            (ID(PK), type_name))
type_colors      (type_color_id, color_name (PK), type_id (PK))

With the first version the schema design itself forbids having invalid type-color pairs, so I would choose that one.  With the second version you will need an extra mechanism (a trigger for example) to maintain valid state of the items.

From a querying point of view, it means one more JOIN on quite simple tables, I would not expect that causing performance or maintainability problems.

EDIT

Well, I finally realized what could Emmad mean on 'The first answer is not OK, because type information appears in 2 tables' - I silently considered type_name in your line

  types and colors (ID(PK), type_name (FK), color(FK))

a typo, and a foreign key on types.ID instead of name.  With this modification my suggestion is quite the same as Emmad's second diagram.


Accessing MySql Data on local machine I have never worked with MySql.

I have some old/crude software running on my machine. It holds a lot of data that I would like access to since the UI is garbage. After searching around the file directories I found what appears to be a MySql database.

C:\Users\Me\AppData\Roaming\Program Name\mdb\bin

Inside of this directory is a my.ini file along with several others. Also several folders including data.

Inside C:\Users\Me\AppData\Roaming\Program Name\mdb\bin\data are ib_logfile0, ibdata1, and other folders/files including frm.

In Sql Server I'd attach it to SSMS, but I can't seem to do that here. I tried copying all the files to the local MySQL ProgramData folder then opening MySQL workbench. But I never see any tables in the Schema section.

My question is: Is there a way for me to access these tables? I have installed MySql and attempted to connect to this database with no luck.  Is there a way to access usernames or can I just use a root account?

Thanks in advance.

Look in your my.ini file where the variable "datadir" points.
Install another mysql (same version or sup better) and copy everything from "datadir" to the new "datadir" in the new installation, start the mysql service. Look in the log maybe you have to run mysql_upgrade script or face another issue, then connect to the database via the workbench.


Cannot create SSRS subscription for a report using SSAS as datasource My questions is : how should I config SSRS subscription a report, which consumes SSAS datasource.
I already googled and lookup stackoverflow a lot and still couldn't make it work.

Problems:
I developed and deployed a SSRS report to the server, and next I need to make subscription for that report to send emails.

And the report is using SSAS as datasource.
I learnt that SSAS required to use windows credentials and I already setup
the datasource .

However, when I attempted to click [New subscription], it alerts:

I have blogged about this exact issue. The workaround/solution involves expression-based connection strings which can be a bit painful. But I have prototyped a tool to automate the setup of those connection strings after deployment:

Using EffectiveUserName To Impersonate in SSAS


Access 2010 ODBC Connection to SQL Server 2005 I'm trying to code an ODBC connection to SQL Server in Access 2010 and hoping someone can help me, as I've lost the plot.

This is my code, it simply accesses one SQL Server database/table and insert into another when a button is clicked:

sql-casecodes is an ODBC table link

DoCmd.RunSQL "insert into [sql-casecodes] (CaseCode,CaseName) values select casecode, name FROM [ODBC;Driver=SQL Server;Server=SQL1;Database=Clients;Trusted_Connection=Yes].clients_case"

When the code is run, Access gives me a run-time error of

  '3134' syntax error in INSERT INTO statement.

But the code looks ok?

I don't want to be using system ODBC links as I want to learn how to code it in

The INSERT command comes in two flavors (this is for SQL Server but there's a good chance it'll be the same for Access, too):

(1) either you have all your values available, as literals or SQL Server variables - in that case, you can use the INSERT .. VALUES() approach:

INSERT INTO dbo.YourTable(Col1, Col2, ...., ColN)
VALUES(Value1, Value2, @Variable3, @Variable4, ...., ValueN)

Note: I would recommend to always explicitly specify the list of column to insert data into - that way, you won't have any nasty surprises if suddenly your table has an extra column, or if your tables has an IDENTITY or computed column. Yes - it's a tiny bit more work - once - but then you have your INSERT statement as solid as it can be and you won't have to constantly fiddle around with it if your table changes.

(2) if you don't have all your values as literals and/or variables, but instead you want to rely on another table, multiple tables, or views, to provide the values, then you can use the INSERT ... SELECT ... approach:

INSERT INTO dbo.YourTable(Col1, Col2, ...., ColN)
   SELECT
       SourceColumn1, SourceColumn2, @Variable3, @Variable4, ...., SourceColumnN
   FROM
       dbo.YourProvidingTableOrView

Here, you must define exactly as many items in the SELECT as your INSERT expects - and those can be columns from the table(s) (or view(s)), or those can be literals or variables. Again: explicitly provide the list of columns to insert into - see above.

You can use one or the other - but you cannot mix the two - you cannot use VALUES(...) and then have a SELECT query in the middle of your list of values - pick one of the two - stick with it.

For more details and further in-depth coverage, see the official MSDN SQL Server Books Online documentation on INSERT - a great resource for all questions related to SQL Server!


Is there any difference between a predicate using a literal value or a parameter value when that value will always be the same? I have a query that always filters on a status, is there a performance benefit of one of these ways over the other?

(This is in the context of an ad-hoc query. The datatype of UserStatus is int)

...
AND UserStatus = 1
...

or

DECLARE @userStatus int = 1
...
AND UserStatus = @userStatus
...

(p.s. please don't talk about how params and literals are different when the values are unknown/changing, that's a different topic)

Alright assuming that you are talking about a local variable running from a query in SSMS since it hasn't been specified otherwise. Even if you use the same value for the
AND UserStatus = @userStatus that you would use in the literal AND UserStatus = 1 you will see a difference in your execution plan due to how the cardinality estimate is generated.

When you use a literal value SQL Server will go out to the histogram for that table and see where that value of fits within the range key. The estimate gathered on that will result in one of two scenarios.

HISTOGRAM DIRECT HIT Essentially this means there is a RANGE_HI_KEY(Upper column value for any step in the histogram) value for that specific literal value in your query and therefore the estimate will match the number of EQ_ROWS(# of rows whose value equals the RANGE_HI_KEY) in the histogram. This means your estimate will be the number of rows that match that value based on the last time you updated statistics.

HISTOGRAM INTRA-STEP HIT This is when the value exists in a range between two RANGE_HI_KEY values. When your literal value is between that range it is calculated by the RANGE_ROWS(number of rows between two histogram steps), DINSTINCT_RANGE(the number of distinct values within that histogram step), and AVG_RANGE_ROWS (RANGE_ROWS/DISTINCT_RANGE_ROWS) and that will give you your estimate.

However when you run with a local variable you will no longer go to the Histogram for these values since the @Variable isn't known at runtime.

For more information on this topic I recommend reading this white paper by Joe Sack.

DENSITY VECTOR When there is no specific value to go with then SQL server instead uses a Density to be able to determine the estimated number of rows returned for that predicate. Density being 1/ the number of distinct values within that column. So then your cardinality estimation will be Density * the number of rows in the table.

So long story short no. Even if you run the same value over and over with a local variable you will not get the same results for reasons further explained in the link Eric Provided from Kendra Little.


Optimising a query to get rows where x is between pairs of values from another table PostgreSQL 9.4.5

In my database there are 2 tables, vcfentries and exons.

I want to query for rows in vcfentries where pos is between a list of pairs of positions, exonstart and exonend in the table exons.

Result of the query:
select * from exons where exons.genename = 'NM_001301824'

Total query runtime: 11 ms.
8 rows retrieved.

This is the equivalent of pulling up the results for a single exon without any joins across tables:

Select pos,chrom,alt,ref from vcfentries
where chrom = '1'
and pos > 33546679
and pos < 33547159

Total query runtime: 11 ms.
89 rows retrieved.

Currently these are the indexes I have on the tables

Querying for a specific exon is efficient:

select vcfentries.pos,vcfentries.chrom,vcfentries.alt,vcfentries.ref,exons.exonnumber from exons , vcfentries
where vcfentries.pos BETWEEN  exons.exonstart
and exons.exonend
and exons.genename = 'NM_001301824'
and exonnumber = 6
and vcfentries.chrom = exons.chrom

Total query runtime: 72 ms.
137 rows retrieved.

"Nested Loop  (cost=438468.31..2324377.64 rows=809587 width=24)"
"  ->  Index Only Scan using exonspkey on exons  (cost=0.42..8.44 rows=1 width=26)"
"        Index Cond: ((genename = 'NM_001301824'::text) AND (exonnumber = 8))"
"  ->  Bitmap Heap Scan on vcfentries  (cost=438467.89..2317823.87 rows=654532 width=16)"
"        Recheck Cond: ((pos >= exons.exonstart) AND (pos <= exons.exonend) AND (chrom = exons.chrom))"
"        ->  Bitmap Index Scan on vcfentries_pos_chrom_idx  (cost=0.00..438304.26 rows=654532 width=0)"
"              Index Cond: ((pos >= exons.exonstart) AND (pos <= exons.exonend) AND (chrom = exons.chrom))"

The performance goes down the toilet when querying for them all. Suddenly it jumps into the minutes range:

select * from exons , vcfentries
where vcfentries.pos BETWEEN  exons.exonstart
and exons.exonend
and exons.genename = 'NM_001037501'
and vcfentries.chrom = exons.chrom

Total query runtime: 325389 ms.
2331 rows retrieved.

"Hash Join  (cost=58.73..11528494.14 rows=11334216 width=24)"
"  Output: vcfentries.pos, vcfentries.chrom, vcfentries.alt, vcfentries.ref, exons.exonnumber"
"  Hash Cond: (vcfentries.chrom = exons.chrom)"
"  Join Filter: ((vcfentries.pos >= exons.exonstart) AND (vcfentries.pos <= exons.exonend))"
"  ->  Seq Scan on coeus.vcfentries  (cost=0.00..7170736.76 rows=141378976 width=16)"
"        Output: vcfentries.pos, vcfentries.chrom, vcfentries.alt, vcfentries.ref, vcfentries.analysisid, vcfentries.filter, vcfentries.info_ac, vcfentries.info_af, vcfentries.info_an, vcfentries.info_baseqranksum, vcfentries.info_clippingranksum, vcfentrie (...)"
"  ->  Hash  (cost=58.56..58.56 rows=14 width=26)"
"        Output: exons.exonnumber, exons.exonstart, exons.exonend, exons.chrom"
"        ->  Bitmap Heap Scan on coeus.exons  (cost=4.53..58.56 rows=14 width=26)"
"              Output: exons.exonnumber, exons.exonstart, exons.exonend, exons.chrom"
"              Recheck Cond: (exons.genename = 'NM_001301824'::text)"
"              ->  Bitmap Index Scan on exons_genename_idx  (cost=0.00..4.53 rows=14 width=0)"
"                    Index Cond: (exons.genename = 'NM_001301824'::text)"

The exons table is modest in size with only about 400K rows.

The vcfentries table is large with a few hundred million rows but is acceptably fast to query with indexes.

I'm having trouble optimizing this query. When I run explain on my attempts to use explicit joins I get the same execution plan.

Any ideas why it's creating such a bad execution plan and any suggested fixes or better queries?

I'd suggest using a CTE in this instance. Something that looks like this:

with exons (genename, exonnumber, crom, exonstart, exonend, padding, direction) as
    (select genename, exonnumber, crom, exonstart, exonend, padding, direction
     from exons
     where enename = 'NM_001037501')
select *
from vcfentries vcf
join exons exo
using (chrom)
where vcf.pos between exo.exonstart and exo.exonend;


What's the difference between Linked Server solution and SSIS solution? When I want to move data between two databases (e.g source: Oracle, destination: SQL Server), I think I have two options: Linked Server and SQL Server Integration Services. But is there any benefit using Linked Server? Is there any use of Linked Server if I have SSIS in my hand?

Linked Servers allow you to connect from SQL Server on an adhoc basis to another datasource, be it SQL Server, Oracle, or something else. Adhoc is the key word, so occasional use is fine. You'll see a lot of negative comments online about performance, hopefully Microsoft will fix in the next SQL Server after Denali.

SSIS is a more robust way of moving and transforming data, with very good exception handling. Getting some data out of Oracle should be straightforward (try the import/export wizard), but SSIS is generally considered to have a steep learning curve. On the other hand, it will make you a more valuable database professional.

It's worth mentioning replication - whilst not trivial, it's a great way of getting data around the enterprise.


Is MySQL Clustering a viable option for a DB expecting 5TB of data in 5 years? We plan to implement MySQL Clustering.The database should support 5 years of data and expected data/year is 1TB.
When we created a test enviornment with the DB size as 200GB, NDB Cluster took more than 2 hours to restart.

Is MySQL Clustering a viable option for an application that expects 5TB of data in 5 years, if yes, could you please guide on the suggested architecture and configuration for such a setup.

currently the maximum data set size is around 3TB, if you are using all in-memory data.
You can increase that if you use disk-based tables

MySQL Cluster also has very flexible replication to external storage engines, ie to InnoDB, so many users manage their "hot" data in cluster, then replicate "colder / aged" data to innodb.

There is a blog describing how to configure this here:
http://johanandersson.blogspot.co.uk/2009/05/ha-mysql-write-scaling-using-cluster-to.html


resizing mongodb replica-set on aws I have a replica-set which consists of 3 amazon ec2 nodes : primary, secondary and an arbiter
the data volume size is ~400GB and currently it is 90% full. i need to resize the volumes to a more appropriate size.
I'm looking for the best solution to do it with minimum downtime/no-downtime and minimum resource overhead - meaning use the least amount of CPU/IO/network needed for the resize process. time is not a big issue as i believe it wouldn't take more than 24 hours.

Here are the logical steps i have come up with so far:

create, attach & mount new volume for secondary
stop mongod process on secondary
copy data from old volume to new volume
restart mongod process on secondary with new dbpath directing to the new volume  OR  replace mounting points for old and new volumes, and then restart mongod process
ensure secondary is finished syncing the remaining data

===============================================================

create, attach & mount new volume for primary
stepdown primary
ensure all processes bounced correctly to the new primary
repeat 2-5 (on the current secondary)
[optional] stepdown primary - return to original setup

my questions:

a) when copying files from one volume to another, assuming the bigger one is also faster (more PIOPS), is it efficient to use parallelism ? as in copy a maximum of ~10 files at a time?

b) some would say if i already put the effort for copying the data then i should simply wipe the data and let it resync anew - however i'm worried about the network and read impacts on the "living" primary which may degrade performance for my applications. is that a right assumption?

c) how can i measure/verify that the oplog retention is sufficient for resyncing the database after copying the files?

d) is there a better idea/solution ?

mongo version - 3.0.10
storage engine - mmapv1
os - amazon linux
journaling is on

thanks

a) when copying files from one volume to another, assuming the bigger one is also faster (more PIOPS), is it efficient to use parallelism ? as in copy a maximum of ~10 files at a time?

Limiting factors for your transfer are likely to be the speed of reading from your original storage as well as the network bandwidth between old and new storage paths. Starting parallel copies is unlikely to speed up the process, and could potentially slow down the overall transfer if your copy process requires reading multiple files which are randomly located on slow origin storage (eg. non-SSD).

Some EBS volumes (notably those restored from snapshots) may also benefit from initialization or prewarming to achieve maximum performance after being attached. See: Initializing Amazon EBS Volumes in the EC2 User Guide.

  b) some would say if i already put the effort for copying the data then i should simply wipe the data and let it resync anew - however i'm worried about the network and read impacts on the "living" primary which may degrade performance for my applications. is that a right assumption?

With MMAPv1 there are some possible benefits to copying the data files vs a resync:

You could potentially compress the data files before transferring, which may save transfer time if the files compress significantly.
The new data volume could be usable faster, as data files and indexes will already be built.

Some possible benefits of resyncing are:

If you have excessive fragmentation or storage usage, the actual data to transfer may be less than the file size on disk.
This saves some overhead of transferring index data but has the expense of rebuilding indexes on the destination node.
You could change the storage engine to WiredTiger, which includes on-disk compression. If your main concern is disk space this might alleviate some of your resource pressure. Since this is a more significant change than simply increasing storage space, you definitely want to test against your application in a staging/UAT environment.

  c) how can i measure/verify that the oplog retention is sufficient for resyncing the database after copying the files?

You could estimate how long the file transfer will take and ensure the oplog is a reasonable multiple of the worst case file transfer time. I strongly recommend avoiding any approach which leaves you without a viable secondary while you are copying/syncing data; racing against the oplog duration is risky if something goes amiss in the copy/sync process and it takes much longer than you planned for.

  d) is there a better idea/solution ?

You currently have a Primary/Secondary/Arbiter configuration. Instead of compromising replication by stopping your only secondary in order to copy the files, I recommend adding a new secondary with increased storage and dropping the arbiter (since it won't be needed if you have an odd number of voting nodes).

Once your new secondary completes initial sync you can then upgrade and resync the other secondary, and finally step down the primary. At this stage you could either drop the former primary and add an arbiter to return to your Primary/Secondary/Arbiter config, or consider adding another secondary to the replica set so you have a more robust Primary/Secondary/Secondary deployment.

For a critical production environment I would encourage you to use three data-bearing replica set members instead of two plus an arbiter. A main consideration when using a three node configuration with an arbiter is that if one of your data-bearing nodes is unavailable, you no longer have replication or data redundancy.


Log shipping - Bulk logged recovery mode doesn't log bulk operations, yet it is allowed for Log Shipping config? Why? Log shipping is one of methods for high availability/disaster recovery. It works by transferring the transaction logs from primary to the secondary.

Log shipping needs that the database should be either in full recovery mode or bulk logged recovery mode.

My questions is: Since the Bulk logged recovery mode doesn't log the bulk operations, then why is it allowed in log shipping? Doesn't this mean it violates the principle of high availability/disaster recovery because the secondary database will be missing all the data that was added into primary via bulk operations (as these are not written to the transaction log)?

"Bulk logged recovery mode doesn't log the bulk operations" - it's a wrong message.
Instead of writing information about adding each row to the log file it writes full pages which were affected by a bulk logged operation to the log backup.

You could read more:
Bulk Changed Map (BCM): The Minimally Logged Advantage


MySQL High Availability for InnoDB I am looking for MySQL High Availability with huge transactions. I prefer to go with InnoDB Engine.
I prefer to use MySQL Master - Slave Replication. However, this option provides us Manual fail over of Application from Master to Slave.
QUESTIONS

What could be the maximum difference time to have Sync between Master and Slave ?
Is there any option of having High Availability other than this approach ?

While Percona XtraDB Cluster is great for Multimaster Replication and InnoDB, it is not good for every type of workload. Bill Karwin states MySQL's Async Replication could be better due to workload : What are the drawbacks of using Galera Cluster instead of Master/Slave Replication?

If you are still looking for synchronous replication, you could look into using DRBD with UCARP or Linux Heartbeat for DB VIP and Failover Management.

Under that setup, the Master (aka DRBD Primary) has a data volume mounted on DRBD, MySQL running, and a ucarp mechanism for automatic failover.

The Slave (aka DRBD Secondary) does not have the data volume mount. MySQL is not running. DRBD is doing everything in terms of having a Block Device synchronized with the DRBD Primary.

DRBD is best used when having failover set up withing a single data center. DRBD should never be setup to with DRBD Primary and Secondary in two different data center due to synchronization latency. I have discussed this before in these posts

Mar 29, 2011 : MySQL high availability, failover and replication with Latency
Mar 26, 2012 : 3 Databases, Best Replication Setup?
Aug 20, 2012 : Best solution for cross-datacenter MySQL master-slave replication

I recommended this for doing PostgreSQL with DRBD : PostgreSQL Failover - What tools should I use?

The drawback in using MySQL/DRBD for High Availability is that fact there is only one instance of MySQL running. The benefit i shaving a full copy of the data on disk. Even a hard failover will have InnoDB crash recover bring back uncommitted data that is written in the double write buffer (inside ibdata1).

If you want it all, use DRBD Clusters inside a data center but have DRBD Primary in one data data center using MySQL Replication in Circular Fashion with a DRBD Primary in another data center.


Driver properties for db2jcc in Squirrell and MON_GET_CONNECTION table function I'm trying to set driver properties like:

clientAccountingInformation
clientApplicationInformation
clientProgramId
clientProgramName

in Squirrell 3.6 (http://squirrel-sql.sourceforge.net/). See https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_r0052607.html for a more detailed description of these properties.

I was under the impression that it should be possible to inspect these values with for example:

select APPLICATION_HANDLE, APPLICATION_NAME, APPLICATION_ID
     , CLIENT_WRKSTNNAME, CLIENT_ACCTNG, CLIENT_USERID
     , CLIENT_APPLNAME
from table(MON_GET_CONNECTION(cast(NULL as bigint), -2))

but no matter what values I use, I get null back from the query.

The db cfg regarding metrics are:

Request metrics                       (MON_REQ_METRICS) = BASE
Activity metrics                      (MON_ACT_METRICS) = BASE
Object metrics                        (MON_OBJ_METRICS) = BASE
Unit of work events                      (MON_UOW_DATA) = BASE
Lock timeout events                   (MON_LOCKTIMEOUT) = HISTORY
Deadlock events                          (MON_DEADLOCK) = HISTORY
Lock wait events                         (MON_LOCKWAIT) = HISTORY

Thoughts anyone, what am I missing?

It appears to be a bug in the way Squirrel handles additional parameters. Data Studio acts as expected with both db2jcc and db2jcc4 drivers. I have filed a bug to:

https://sourceforge.net/p/squirrel-sql/bugs/1201/


how does multiple column index work in details if we have a table T1 with columns A,B,C,D,E and an index ( A,B,C) built for it

if we a SQL query joining on columns A,B or A,B,C or A, this index can still be used, but if the query is joining on B or C or B,C  the index is totally useless

I know indexes are often implemented BTree , I want to know how is the implementation detail related with this ?

This is not necessarily the case. Oracle, for example, has an access path known as an "index skip scan". See http://docs.oracle.com/cd/B10501_01/server.920/a96533/optimops.htm#51553

Basically, if there are few distinct values in column A, and the query restricts on column B (and optionally, column C), the executor will substitute each of the distinct values of column A in turn and probe the index for the supplied value of column B (and optionally, column C).

Postgresql can do something similar, but it is still labelled as an "index scan", see Working of indexes in PostgreSQL


In-memory and hard-drive hybrid I'm going to start my Master of Science degree work.
My goal is to implement (and test in various scenarios) hybrid database environment: my application will communicate with two databases. One will be in-memory database and other will be standard (on hard disk) database. I want to research if this hybrid can be faster than standard database only (in certain situations).

Have you ever heard or read of someone, who tried something like this? Because if there are works like that I would like compare my results to them.

Edit:
I'm on beginning of my work, so exact topic is not cleared yet (I have something about 18 months to write it).
My main idea is to compare standard database (eg MySQL) vs hybrid (eg MySQL+VoltDB) with the same set of data, where on hybrid some tables (or maybe some columns from some tables) will be in in-memory database.
Mainly I want to compare time performance, but maybe other aspects (like administration issues of that hybrid) as well.

Microsoft just announced Hekaton which allows you to have a mix of in-memory tables and traditional on-disk tables in the same database, and seemlessly integrate the two technologies.  It will ship in the next major release.  They annouced it at the 2012 SQLPass Summit during the day 1 keynote (viewable online).


Why is this filtered index not only not used but errors when I try to force it I'm trying to tune a rather annoying query where a number of the tables have something that looks like:

date = '9999-12-31 23:59:59.9999999'

At least a few of the tables are in the 300 mil row range and when I filter this I end up in the 2 mil range.  It seemed reasonable to attempt a filtered index.

CREATE TABLE test (col1 int PRIMARY KEY, col2 int, col3 varchar(50), col4 datetime2(7), col5 int);
CREATE INDEX ix_filtered ON test(col2,col3) INCLUDE (col4)
       WHERE col4 = '9999-12-31 23:59:59.9999999' ;
GO

SELECT col2,col3 FROM test
WHERE col4 = '9999-12-31 23:59:59.9999999';
GO

However when I checked the query plan it wouldn't use the index.  It just did a clustered index scan and a key lookup.  Obviously without any data this makes sense but even with all of my data it did the same thing.  The reason I haven't bothered supplying any data is this second problem that occurs regardless.

When I tried to force the index to see what the query plan would look like:

SELECT col2,col3 FROM test
WITH (index (ix_filtered))
WHERE col4 = '9999-12-31 23:59:59.9999999'

I get this error:

  Msg 8622, Level 16, State 1, Line 52 Query processor could not produce
  a query plan because of the hints defined in this query. Resubmit the
  query without specifying any hints and without using SET FORCEPLAN.

I'm trying to figure out why I'm getting the error with the query hint.  My guess is that the answer to this will also tell me why the filtered index isn't being used at all.

We found that this error occurred because forced parameterization was on at the database level. For other readers  who want to reproduce it, use Kenneth's sample code in a database with this setting on:

create database filterme;
GO

alter database filterme set parameterization forced;
GO

For parameterized queries, SQL Server doesn't want to cache an execution plan with a filtered index that might not work for other parameter values.

In later versions of SQL Server, the query plan for the "non-hinted" plan would (hopefully) show an "unmatched indexes" warning to alert you better to this. That just didn't exist in SQL Server 2008R2. (Note: I just tested on SQL 2016 and am not seeing the unmatched indexes warning, need to look into more why it isn't showing up with this sample code.)

To get the query to work without changing the database setting, you can use an "Option recompile" hint in queries. That works because it instructs SQL Server not to cache the plan for re-use.


DBCC CHECKDB on Always On databases Do we really need to run DBCC checkdb on a database configured with Always On synchronous updates?

I believe the automatic page repair mechanism should identify the corruption and repair it automatically.

Yes you really need to run checkdb on ALL the secondary replicas. If you read MS document on Automatic Page repair it says

Automatic page repair cannot repair the following control page types:
File header page (page ID 0).
Page 9 (the database boot page).
Allocation pages: Global Allocation Map (GAM) pages, Shared Global Allocation Map (SGAM) pages, and Page Free Space (PFS) pages.

So you can see not all types of repairs are fixed by automatic page repair. What if you encounter corruption not coming under error fixed by automatic repair.
If you are using availability groups on WSFC without FCI the storage would be local storage so you need to separately run checkdb and analyze storage for each replica.


SQL 2008 Column level encryption We are looking to do Column encryption on some of our DBs.
So creating the keys and encrypting is all good.
My boss is wanting to use Vault by Hashicorp to store the keys.

So I am wondering if anyone knows how you go about exporting the keys from SQL 2008 and then store them in Vault?

There are five types of keys in the heirarchy of any SQL Server column encryption. They are service master key, database master key, asymmetric key, certificate and symmetric key. SQL Server contains commands to back up the service master key, database master key and the certificate with the private key to separate password protected files. If you want to have something to preserve an asymmetric key, you can create one from a strong named key file (.snk) that already contains the key pair and save that. For the symmetric key, you will need to create it from a command, specify the key_source and identity_value and save those two values in your Vault. IF you create symmetric and asymmetric keys without saving the inputs, then you will not be able to export them. If you want to learn more about another method of securing the keys, I suggest reading about Extensible Key Management systems, which is a SQL Server enterprise edition feature. An EKM is a separate piece of hardware designed to hold the keys and perform the encryption and decryption functions.


Chaining SQL Server Agent Jobs I have several work packages for each I have setup up a job in sql server agent. Sometimes I should run all together. What is the best way to run them all in a given sequence? I'm a bit surprised that sql server agent is not able to include jobs as job steps to be executed. I did try

SQL Agent is not considered to be "enterprise class" as a job scheduler. It lacks a lot of functionality that you end up having to build yourself. One such example would be dependencies. As a result you are forced to put most of this logic into SSIS packages. Not necessarily a bad thing...just a pain to build and manage yourself.

So, that's my answer, build out an SSIS package and use some t-sql commands to call SQL Agent jobs as desired. Make sure you have your requirements and scenarios clearly defined. You may not want to have multiple jobs hitting the same tables at the same time, for example.

HTH


Refactoring a very large database table and having join over millions of records I have a fairly big postgresql jobs table of (more than 60 attributes) and one critical query contains joins with this table of million records. and I cannot reduce its size(paritioning by range which is introduced recently in postgresql 10). Only 10 fields are required for this critical query(All other 50 fields when joining are a burden). is there any way that I can only join with smaller table(with 10 fields only, Because I think pg loads all the fields when applying join) when running this critical query but in all of the remaining app, I would be able to fully use this table.
E.g

jobs table fields critical for query are: cost, id, user_id, params, location
and the fields which are not required in this query are quite a lot.

Note: If I create two different tables then I had to update a lot of code everywhere

Don't  "select *", then the other 50 fields won't be a burden. Better optimize the critical query.


PostGIS error building with `make` in ./extensions: make[1]: *** No rule to make target Whenever I run make in extensions, I'm getting error.

make
for DIR in postgis  postgis_tiger_geocoder  postgis_topology address_standardizer; do \
    echo "---- Making all in ${DIR}"; \
    make -C "${DIR}" all || exit 1; \
done
---- Making all in postgis
make[1]: Entering directory '/home/ecarroll/code/postgis/extensions/postgis'
cp ../../doc/postgis_comments.sql sql_bits/postgis_comments.sql
make[1]: *** No rule to make target '../../raster/rt_pg/rtpostgis_for_extension.sql', needed by 'sql_bits/rtpostgis.sql'.  Stop.
make[1]: Leaving directory '/home/ecarroll/code/postgis/extensions/postgis'
Makefile:26: recipe for target 'all' failed
make: *** [all] Error 1

You must first run,

make;

in root, then you can run make in ./extensions


Replication not updating all tables Master db MySQL db Server 2012
Slave db MySQL Win7 XAMPP
DB size 500MB
Table count 42

I have setup the replication successfully however it stopped last week and my slave was showing the error Slave_SQL_Running No. I realised that it was looking at an incorrect log file (00004 whereas it should have been 00006).

I have since sorted this by;

At the MASTER;

SHOW MASTER STATUS;
Copied the values of MASTER_LOG_FILE and MASTER_LOG_POS.

At the SLAVE;

STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98; (<- example values)
START SLAVE;
SHOW SLAVE STATUS \G;

On my master I tested the replication by editing the table members - I edited one of the row values (from 85 to 86 - this successfully replicated in my slave). However I notice that on my master members table there are 70652 members but on my slave there are only 70056.

What could be the problem? Replication seems to be working but totals aren't. New members are added to the members table each day, but the aren't being added to my slave members table.

The results of my slave status table (from phpmyadmin) are;

Slave_IO_State  Waiting for master to send event
Master_Host xxx.xxx.xxx.xxx
Master_User repl
Master_Port 3306
Connect_Retry   60
Master_Log_File mysql-bin.000006
Read_Master_Log_Pos 787956776
Relay_Log_File  mysql-relay-bin.000004
Relay_Log_Pos   624412
Relay_Master_Log_File   mysql-bin.000006
Slave_IO_Running    Yes
Slave_SQL_Running   Yes
Replicate_Do_DB
Replicate_Ignore_DB
Replicate_Do_Table
Replicate_Ignore_Table
Replicate_Wild_Do_Table
Replicate_Wild_Ignore_Table
Last_Errno  0
Last_Error
Skip_Counter    0
Exec_Master_Log_Pos 787956776
Relay_Log_Space 788197
Until_Condition None
Until_Log_File
Until_Log_Pos   0
Master_SSL_Allowed  No
Master_SSL_CA_File
Master_SSL_CA_Path
Master_SSL_Cert
Master_SSL_Cipher
Master_SSL_Key
Seconds_Behind_Master   0

Is there something else that I could check or test?

In the end I had to reset the slave completely and set up replication again. Not sure why but this seems to be a common problem/solution as I can see from other articles online.


Only allow one checked row in a Column in SQL Server Background:

I've only just begun programming in SQL... barely begun even. But I have been thrown into the deep-end left to find a way and learn to program SQL.

Question:

Is is possible to have a boolean column in a table that only allows one checked row.
So even if a user wanted to update the table with another checked row they wouldn't be able to? Suggestions and Ideas and use layman terms please.

Attempted illustration:

ID        Col_1        Col_2       Boolean_Col
----------------------------------------------
1         Data       More Data         0
2         Data       More Data        -1
3         Data       More Data         0

No

While strictly speaking it can be achieved, it is always a bad idea. In general thinking at restricting properties of sets is a bad road to travel because set property enforcement usually require inspecting the entire set to validate the constraint. In other words enforcing your 'single row check' requires to scan every other row, on every change. This is not only slow, more importantly is the 100% guaranteed recipe for deadlocks.

It would be much better if you post your actual problem rather than ask how to implement the solution you choose.


efficient way to query for count totals I have a table that has two columns:

user_id int(11)
friend_id int(11)

In order to get the number of friends a user has has, I'd do something like:

SELECT COUNT(user_id) AS num_friends FROM friends WHERE friend_id = some_id

What I want to do is get the number of friends every single user in the table has and cache the results to another table so I don't have to run an expensive query like the one above. Basically a 'rollup'.

What's the best way to query for this data to get the number of friends that every friend_id has? I was thinking something like this, but it seems very wrong, especially if you have a large amount of distinct friend_id instances in the table:

SELECT COUNT(*) num_friends, friend_id FROM friends WHERE friend_id IN ( SELECT DISTINCT(friend_id) FROM FRIENDS) GROUP BY friend_id

SELECT friend_id, COUNT(*)
    FROM num_friends
    GROUP BY friend_id;

This will assist in making it fast:

INDEX(friend_id)

With that index, you will find out that the original query is so fast that you don't need to cache the results.


Why do function inputs need an extra set on parens in this Postgres function? This works. However, it fails with a syntax error if the "extra" parenthesis in the second function are omitted. Why?

create function epochSeconds(timestamp) returns int as $$
  select extract(epoch from $1)::int;
$$ language sql;

create function tableRowEpochSeconds(integer) returns int as $$
  select epochSeconds((select max(baz.created) from baz where baz.id = $1));
$$ language sql;

Does not work:

<same other code>
select epochSeconds(select max(baz.created) from baz where baz.id = $1);
<same other code>

You need the extra parentheses because your function epochseconds() is defined to accept a single value, a select returns a set of rows, the parentheses indicated that the select only returns a single row and column (a scalar value).

But there is no need for that, just pass the max value directly to the function:

create function tablerowepochseconds(integer) returns int as $$
  select epochseconds(max(baz.created))
  from baz
  where baz.id = $1;
$$ language sql;


Help using SQL Server Database Experimentation Assistant for performance testing I am following the guide for capturing a trace in SQL Server Database Experimentation Assistant here: https://docs.microsoft.com/en-us/sql/dea/database-experimentation-assistant-capture-trace?view=sql-server-2017

I am using DEA version 2.6. The article above states that I should be able to choose the capture trace file size for rolling over files. However, in version 2.6 I do not have the option to set trace file size.

As a result, I end up with dozens of 500 MB files at the end of the capture. Then I am expected to sit there and replay each trace file, one by one. Is there any easier way of doing this, or of forcing the trace file to grow larger so I end up with less rollover files?

My new capture screen:

MS doc capture screen:

I end up with dozens of 500 MB files at the end of the capture.

Yes I agree the documentation is wrong and you cannot configure file size. You can only configure duration. 500 MB is by design and recommended here and here. 200 MB or 512 MB is the ideal trace rollover size to use to capture production or performance traces. Second link give you few reasons why this is the recommended size. One of them is:

If you use our "published" versions of production and performance
trace definition files and the performance trace definition filters
out the traces for one database of interest, analysis shows that the
Performance Trace size is about 15 times larger than the Production
Trace size.

  Then I am expected to sit there and replay each trace file, one by
  one. Is there any easier way of doing this, or of forcing the trace
  file to grow larger so I end up with less rollover files?

You do not need to process each file individually. In Full path to source File instead of individual file, give the folder name where your raw trace files are located. Tool will create a new sub-folder named PreProcessOutput and put all Replay Markup Language (RML) files there.

On a side note hopefully you configured your Distributed Replay successfully.


How to restart server without rebooting system Local server
PostgreSQL\9.6
PgAdmin 4 v3.2

A least once an hour pgadmin gives me an out of memory error followed by "Not connected to the server or the connection to the server has been closed.". The only way I know to recover is to reboot my system.
I know I may have other issues, like why am I running out of memory and why am I losing the connection but for right now I just want to know if there another way of restarting the server afresh without rebooting?
If so, please explain in simple newbie terms :)

On CentOS server, you can use these below commands:

PostgreSQL 9.6:

/bin/systemctl restart postgresql-9.6.service

PostgreSQL 10.x

/bin/systemctl restart postgresql-10.service

PostgreSQL 11.x

/bin/systemctl restart postgresql-11.service


SQLite query rows nearest to hour My question is similar to the one asked here. I'm new to SQL and I'm creating a weather app that stores part of the data locally in a db, so I'm writing the query using the sqlitedatabase java class for the android app.

I need to select a temperature row for each hour interval offset by 30 minutes (so 1:30-2:30) by taking the most recent to the hour reading. Here's what I've tried so far using a database of lat/lons that I have. I used avg() here because it may be easier to get a picture of the weather around that hour by averaging temperature reading within that interval. I've been using SQLite Studio to get a hang of writing queries in SQL:

select avg(longitude), time_stamp, strftime('%H',time_stamp)
from events
group by strftime('%H', time_stamp) + round((time_stamp - 1800)/3600)

And my result:

-81.4919071241071   2015-11-11 11:21:53 11
-48.8079752772727   2015-11-11 12:59:31 12
-54.104869527907    2015-11-11 13:44:16 13
-88.0822953625      2015-11-11 14:37:38 14

It looks okay BUT there's data it's not including from 11:30-12:30, even though it's definitely present in the database. The first row is fine, the second row should go with 13, 3rd row with hour 14, and 4th row with hour 15.

Sample data with columns: row, event_id, type, machine_id, longitude, lat, time_stamp (datetime), other_machine, synched

42  1   2   -89.4813131 40.7400818  2015-11-11 11:27:30 3   0
43  9   1   -89.4813908 40.7401891  2015-11-11 11:21:53 -1  0
44  8   1             0          0  2015-11-11 11:27:52 -1  0
45  0   2   -89.4812174 40.7401841  2015-11-11 11:27:55 3   0
46  4   2   -89.4813087 40.7401857  2015-11-11 11:29:10 -1  0
47  5   2   -89.4813087 40.7401857  2015-11-11 11:29:13 -1  0
48  4   2   -89.4813087 40.7401857  2015-11-11 11:29:13 -1  0
49  5   2   -89.4812153 40.7401323  2015-11-11 11:30:02 -1  0
50  4   2   -89.4812153 40.7401323  2015-11-11 11:30:02 -1  0
51  2   2   -89.4812291 40.7400435  2015-11-11 11:30:20 3   0
52  5   2   -89.4812718 40.7401197  2015-11-11 11:30:46 -1  0
53  4   2   -89.4812718 40.7401197  2015-11-11 11:30:46 -1  0

This sample data shows where half of the points would group with hour 11 and the ones after 11:30 would go with 12.

time_stamp - 1800 subtracts two numbers. If the value in the time_stamp column is not a number, it is converted.

From what you've shown, converting the values in the time_stamp column (which begin with the characters 2015) to a number results in the value 2015; subtracting 1800 from that results in 215. This is probably not what you want.

To group by hours offset by half an hour, just add half an hour to the timestamp, then drop the minutes/seconds:

GROUP BY strftime('%H', time_stamp, '+30 minutes')


SQL 2005 table data type "text" to nvarchar I have this column Remark which is data type text. The longest length is 153.

So is it safe to say that if I change this column from text to nvarchar(200), I won't lose any data?

SELECT LEN(LTRIM(RTRIM( CAST( Remark AS NVARCHAR(MAX) )))) FROM myTable
ORDER BY LEN(LTRIM(RTRIM( CAST( Remark AS NVARCHAR(MAX) )))) DESC

If you want to know what data is there, then:

Don't use LTRIM or RTRIM
Use DATALENGTH() (for NTEXT add / 2 to the end of that) instead of LEN

Now, converting from either TEXT or NTEXT to NVARCHAR won't result in data loss if the length is the same or larger than what was calculated assuming what is mentioned above.

HOWEVER, the real issue isn't what data is there, it's what does the app allow for. If the app allows for more than 200 characters, then 200 isn't a safe choice. Make sure that all sources that can insert or update data in this column enforce a limit of 200 characters. If even one does not, then you need to work with the devs to make sure any sources of input are updated to reflect a limit of 200, or whatever you agree on (just because nobody has yet used even 200 characters doesn't necessarily imply that there isn't a valid case for going above that limit, if the app allows for it).

I just had the thought that there could be a slight issue with maximum row size since the TEXT, NTEXT, and IMAGE datatypes, by default, are a 16 byte pointer in the row with the actual data off row. Since you only get approximately 8060 bytes per row, if you were less than 400 bytes from that limit, then changing TEXT (requiring 16 bytes) to NVARCHAR(200) (requiring 400 bytes) could potentially result in an error. However, I just tried it and doesn't error; it allows for going above the 8060 byte limit (keep in mind that the max row size is still approx. 8060, but any extra data will be placed on ROW_OVERFLOW pages).

USE [tempdb];

-- DROP TABLE dbo.AlterColumnTest;
CREATE TABLE dbo.AlterColumnTest
(
  [ID] INT NOT NULL PRIMARY KEY,
  [EnglishSource] VARCHAR(50) COLLATE Latin1_General_CI_AS,
  [EnglishText] TEXT COLLATE Latin1_General_CI_AS,
  [HebrewSource] VARCHAR(50) COLLATE Hebrew_CI_AS,
  [HebrewText] TEXT COLLATE Hebrew_CI_AS,
  [Spacer] CHAR(7500) NOT NULL DEFAULT ('')
);

DECLARE @TestString VARBINARY(100);
SET @TestString = 0x4120746573743A20C0C620CBE0E2E8EC20F0F4F92021;
SELECT CONVERT(VARCHAR(50), @TestString); -- A test: ÀÆ Ëàâèì ðôù !

INSERT INTO dbo.AlterColumnTest ([ID], [EnglishSource], [HebrewSource])
VALUES (1, @TestString, @TestString);

SELECT * FROM dbo.AlterColumnTest;

UPDATE dbo.AlterColumnTest
SET    [EnglishText] = [EnglishSource],
       [HebrewText] = [HebrewSource];

SELECT * FROM dbo.AlterColumnTest;

ALTER TABLE dbo.AlterColumnTest
  ALTER COLUMN [EnglishText] NVARCHAR(200) COLLATE Latin1_General_CI_AS;

ALTER TABLE dbo.AlterColumnTest
  ALTER COLUMN [HebrewText] NVARCHAR(200) COLLATE Hebrew_CI_AS;

SELECT * FROM dbo.AlterColumnTest;

SELECT *
FROM   sys.columns sc
WHERE  sc.[object_id] = OBJECT_ID(N'dbo.AlterColumnTest');

Values for @TestString taken from:

Code Page 1252 Windows Latin 1 (ANSI)
Code Page 1255 Windows Hebrew


Partial vacuum and whole table vacuum From postgresql document, I know  vacuum can skip pages that are already marked in  visibility map, so vacuum progress only vacuum pages which  requires vacuum,and we call this partival vacuum; Sometimes  all pages of a table happen to require vacuuming to remove dead row version, we call this whole table vacuum ( not vacuum full ),  My question is: How can we see if a vacuum is a partial vacuum or whole table vacuum, the following is the output of vacuum verbose commond, can we judge from this ?

--vacuum verbose
mydb=> vacuum verbose test_vacuum2;
INFO:  vacuuming "mydb.test_vacuum2"
INFO:  "test_vacuum2": found 0 removable, 774 nonremovable row versions in 5 out of 5 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 226 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

I don't think that you can tell from the "verbose" listing whether any pages were skipped because they were known to contain only visible tuples.

I think that it will only vacuum pages flagged as "all visible" if it decides to freeze tuples in the table, either because of approaching transaction wraparound or because you explicitly ran VACUUM with the FREEZE option.  (If you use the vacuumdb command-line executable, this is the -F option.)

In our shop we supplement the autovacuum activity with a scheduled VACUUM FREEZE ANALYZE on the database during non-peak hours.  That minimizes the overhead of autovacuum during peak hours, and slightly improves query performance, because visibility checks are simpler.


How to make a distibutable .bak file? I am trying to restore 2 databases. One is adventureworks database downloaded from msdn(its .bak file), and another one is created by me from one databases from my old server. Both have .bak extension, are in the same folder where the new instance where they should be restored, has all the access privileges. Now while restoring adventureworks database everything goes alright. While trying to restore the other .bak file made by me I get an access error, it wants access to its original server's DATA folder. Here is the error

Now, I want to know how to create .bak backup in order to restore it to any other server without this error.

Thanks for your time!

SQL Server won't automatically restore a database to the folder where the .BAK is located. This would be a disaster waiting to happen (say, that drive is out of space, or is encrypted or compressed, or SQL Server only has read access). Instead, it will try to place the files using the same configuration they had on the system they were backed up from. As Tibor alluded to in a comment below, you certainly could standardize all of your users' installs to have SQL Server installed to the same data folders as the source system, or at least all have the same data folder available to write to.

Barring that, you need to use RESTORE ... WITH MOVE in order to place the data and log files in the right folder for this instance. First verify what that is:

EXEC master.sys.sp_helpfile;

That will give you two rows with paths like:

filename
------------------------
C:\some\path\master.mdf
C:\some\path\mastlog.ldf

Now run the following from the backup file:

RESTORE FILELISTONLY FROM DISK = 'C:\path\file.bak';

This will give you a row for each file in the backup, corresponding to each data/log file you'll need to restore (and move). For example:

LogicalName
-----------
AdventureWorks2016_Data
AdventureWorks2017_Log

Take the C:\some\path\ part from the first query, and the logical names from the second query, into a statement like this:

RESTORE DATABASE AdventureWorks
  FROM DISK = 'C:\path\file.bak'
  WITH REPLACE, RECOVERY,
    MOVE 'AdventureWorks2016_Data' TO 'C:\some\path\aw.mdf',
    MOVE 'AdventureWorks2016_Log'  TO 'C:\some\path\aw.ldf';

There are likely complications when your backup includes things like filestream or In-Memory OLTP.

But if your goal is to make a "distributable" backup that will do all that work for the end user, I don't think a traditional SQL Server backup is what you are looking for. Maybe you should look into database projects or providing scripts instead of backups.


MYSQL - Operand should contain 1 column(s) - Nested Case statement I have written the below query with nested case statements. The syntax checks out but when I run it I receive error code 1241: Operand should contain only 1 column(s). I have attempted to remove the parenthesis from the SELECT statements but it does not help and indicates there is a syntax error.

This query is intended to select a data subset based on current time. The first case selects data from today's first shift. The second nested case determines if the current time the night portion of second shift (Between now and midnight 2nd shift) or the early morning portion of second shift between  midnight and 4:30. If it is after midnight then date from today and today-1 is selected. Any ideas?

SELECT CASE WHEN current_time() BETWEEN CAST('6:00:00' AS TIME) AND CAST('16:30:00' AS TIME)
    THEN
        (SELECT infeedweight, outfeedweight, yield, time_stamp, num_checks_passed, num_checks_failed,TIME(time_stamp)
            FROM workers_totals
            WHERE time_stamp >= curdate())
    ELSE
        CASE WHEN (current_time() BETWEEN CAST('16:30:00' AS TIME) AND CAST('23:59:59' AS TIME))
            THEN
                 (SELECT infeedweight, outfeedweight, yield, time_stamp, num_checks_passed, num_checks_failed,TIME(time_stamp)
                 FROM workers_totals
                 WHERE time_stamp >= CAST(CONCAT(curdate(),' 16:30:00') AS DATETIME)  and CAST(CONCAT(curdate(),' 23:59:59') AS DATETIME))
            ELSE
                 (SELECT infeedweight, outfeedweight, yield, time_stamp, num_checks_passed, num_checks_failed,TIME(time_stamp)
                 FROM workers_totals
                 WHERE (time_stamp >= CAST(CONCAT(curdate(),' 00:00:00') AS DATETIME)  and CAST(CONCAT(curdate(),' 04:59:59') AS DATETIME))
                 OR (time_stamp >= CAST(CONCAT(curdate()-1,' 16:30:00') AS DATETIME)  and CAST(CONCAT(curdate()-1,' 23:59:59') AS DATETIME)))
            END
    END

CASE in MySQL can either be a statement, or an expression.

Since your CASE statement is in a SELECT list, it's being treated as an expression. As an expression, it should return a single value. You're trying to return multiple columns (and, presumably multiple rows).

Presumably, you want to use CASE as a statement, without the SELECT in front, so that just one of your three SELECT statements is executed:

CASE WHEN current_time() BETWEEN CAST('6:00:00' AS TIME) AND CAST('16:30:00' AS TIME)
     THEN
          SELECT infeedweight, outfeedweight, yield, time_stamp, num_checks_passed, num_checks_failed,TIME(time_stamp)
            FROM workers_totals
           WHERE time_stamp >= curdate()
     WHEN (current_time() BETWEEN CAST('16:30:00' AS TIME) AND CAST('23:59:59' AS TIME)
        THEN
             SELECT infeedweight, outfeedweight, yield, time_stamp, num_checks_passed, num_checks_failed,TIME(time_stamp)
               FROM workers_totals
              WHERE time_stamp BETWEEN CAST(CONCAT(curdate(),' 16:30:00') AS DATETIME) and CAST(CONCAT(curdate(),' 23:59:59') AS DATETIME)
     ELSE
             SELECT infeedweight, outfeedweight, yield, time_stamp, num_checks_passed, num_checks_failed,TIME(time_stamp)
               FROM workers_totals
              WHERE (time_stamp BETWEEN CAST(CONCAT(curdate(),' 00:00:00') AS DATETIME) and CAST(CONCAT(curdate(),' 04:59:59') AS DATETIME))
                 OR (time_stamp BETWEEN CAST(CONCAT(curdate()-1,' 16:30:00') AS DATETIME) and CAST(CONCAT(curdate()-1,' 23:59:59') AS DATETIME))
END CASE

Note that the CASE statement is terminated with END CASE instead of just END.

Also, I've condensed your code to use one CASE statement instead of two nested ones (you can have multiple WHEN branches with CASE), and changed your timestamp comparisons to check if the value is BETWEEN the two dates you supply, instead of timestamp >= dateX AND dateY.

You should double-check your time_stamp boundaries - for times between midnight and 6AM, you're cutting off anything with a timestamp later than today at 5AM.

Finally, note that this can only be used in a stored program.

I believe you could do this as a single SELECT statement, without needing a CASE at all:

SELECT infeedweight, outfeedweight, yield, time_stamp, num_checks_passed, num_checks_failed,TIME(time_stamp)
  FROM workers_totals
 WHERE (    current_time() < CAST('6:00:00' AS TIME)
        AND time_stamp >= CAST(CONCAT(curdate()-1,' 16:30:00') AS DATETIME)
        AND time_stamp <=CAST(CONCAT(curdate(),' 04:59:59') AS DATETIME)
       )
    OR (    current_time() >= CAST('6:00:00' AS TIME)
        AND current_time() < CAST('16:30:00' AS TIME)
        AND time_stamp >= curdate()
       )
    OR (    current_time() >= CAST('16:30:00' AS TIME)
        AND time_stamp >= CAST(CONCAT(curdate(),' 16:30:00')
        AND time_stamp <= CAST(CONCAT(curdate(),' 23:59:59') AS DATETIME)
       )
;

I've tweaked your current_time() checks, to try to make sure that all times are covered, and fall uniquely into one range or another. You'll want to validate that this works as intended.

NOTE: All code is untested.


how wsrep_local_index is defined or could be changed? I have a cluster of 3 nodes using Galera with MariaDB, every time I want to backup I check the node with the highest wsrep_local_index and then do:

SET GLOBAL wsrep_desync = ON;

Take the backup, once finish I do:

SET GLOBAL wsrep_desync = OFF;

But I notice that the wsrep_local_index value changes during this process, also if I reboot a node, the wsrep_local_index values are reorganized, therefore would like to understand more about how this value is defined in the first place and what are the cases that make it change, from the docs I only found:

wsrep_local_index

This node index in the cluster (base 0).

The reason for taking the backup on a cluster with the highest wsrep_local_index is that the cluster is behind a proxy that only serves traffic from the node with index 0.

The wsrep_local_index does not change due to desync mode. You most likely restart the node, this process does change the local index.

Here is the log of the process to show that it does not change:

MariaDB [(none)]> show global status like 'wsrep_local_index';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| wsrep_local_index | 0     |
+-------------------+-------+
1 row in set (0.002 sec)

MariaDB [(none)]> set global wsrep_desync=1;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> show global status like 'wsrep_local_index';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| wsrep_local_index | 0     |
+-------------------+-------+
1 row in set (0.002 sec)

MariaDB [(none)]> set global wsrep_desync=0;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> show global status like 'wsrep_local_index';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| wsrep_local_index | 0     |
+-------------------+-------+
1 row in set (0.002 sec)


Mongodb restrict access on collections level I'm building a backend system using MongoDB. Now I want to restrict access on certain numbers of database collections to this system. For example I have users, admin_users, pageviews and I want this system to be able to read and write only admin_users and pageviews collections but no read or write access on users colection. Is there any ways to do so?

They have added collection-access ACLs, see here: http://docs.mongodb.org/manual/core/collection-level-access-control/


Is there a way to analyze a MySQL query and determine whether it's read-only? I have a main database and a replica that is automatically kept in sync with the main one. It would be nice, for each query I make, to perform all data- and database-altering queries on the main database, and all read-only queries on the replica. I'm envisioning some kind of query analyzer, and then based on its output, using the correct database, but maybe there's a different way to do that that I'm not aware of.

I think it will be fairly complicated to do both completely automatic as well as efficient. Imagine a transaction like:
BEGIN TRANSACTION
SELECT ... FROM T1 WHERE ...
UPDATE T2 SET ... = ...
SELECT ... FROM T3 WHERE ...
END TRANSACTION

Would you direct statement 1 and 3 to your replica whereas statement 2 would be asked against your master?
If not, you need to analyze at the transaction level. That, in turn, can be really complicated due to branching in the code.
A viable option would be to do read-only reporting against your replica


Prod environment uses a different SSIS version to Dev when running a legacy SSIS package I have a dev environment with SQL Server 2008 R2 and SSIS (10.0) installed.

I have a number of SSIS packages built with SQL Server 2008 R2 BIDS and these have been deployed to a Prod server running SQL Server 2017 and SSIS (14.0). The legacy SSIS packages run fine on Prod's SQL Server 2017.

I recently installed SQL Server 2017 & SSIS onto my Dev environment. The plan being to upgrade the SSIS packages in the future. Therefore in my dev environment, I have SQL Server 2008 R2 with SSIS 10.0 and also SQL Server 2017 with SSIS 14.0. Everything seems to co-exist fine.

The reason for my question is that in my dev environment, when I run the 2008 R2 SSIS packages from anywhere (BIDS / SQL Server 2008 R2 / SQL Server 2017) it requires the 'SQL Server Integration Services 10.0' Windows service to be running. If I run the same package on the Prod server which only has SQL Server 2017 and SSIS 14.0 installed it requires the 'SQL Server Integration Services 14.0' Windows service to be running.

Why in Dev is it falling back to use the older SSIS 10.0 Windows service, even when launched from SQL Server 2017? The package is deployed in an identical way onto the Dev SQL Server 2017 and prod SQL Server 2017, yet the Dev install uses SSIS 10.0 and Prod uses 14.0. Is this to be expected? I don't have a huge issue with this, but with Dev using a different SSIS runtime to Prod, I am not guaranteeing a similar SSIS environment for testing.

Is this behaviour to be expected?

Many thanks in advance.

The solution ended up being nothing to do with running different versions of the SSIS Windows service, but how SSIS 14.0 in SQL Server 2017 handles legacy SSIS packages which use configuration files. I use configuration files to store database credentials.

In SQL Server 2008 R2 when you deploy a SSIS package, it also asks you where you want to store the configuration file and the deployed package references that configuration file in the file location you specify.

In SQL Server 2017 when you deploy a legacy SSIS package, it grabs the contents of the configuration file and stores it within the deployed project. The configuration parameters are then configurable via the SSMS SSIS GUI.

Here's the gotcha, in SQL Server 2017, if the configuration file exists in the original file location when the SSIS deployment package was built, it uses the contents of that configuration file in preference over any configuration defined in the SSIS package node of SQL Server 2017. If the configuration file is removed/renamed from the original file location (when the SSIS deployment package was built), it then falls back to using the configuration defined via the GUI in the SSIS package node of SQL Server 2017.

This doesn't make a whole heap of sense, but this is definitely what is happening.

Hopefully this solution might help someone somewhere!


How to clear space after deleteing rows I am maxed out on a DB in SQL Express 10GB.

I deleted rows of approx 2.5G in size. In report I can see number of rows is much lower. But "Data" is still the  same. I am happy about Reserved remaining 10G, but I am unable to insert new rows.

I read that shrinking is not necessary if table will grow again, BUT how do I tell SQL Server that it now has space to use again?

Your problem is you were deleting from a heap, i.e. your table has no clustered index, so the space was not released, you could solve this in follow ways as described here: DELETE (Transact-SQL)

  When rows are deleted from a heap the Database Engine may use row or
  page locking for the operation. As a result, the pages made empty by
  the delete operation remain allocated to the heap. When empty pages
  are not deallocated, the associated space cannot be reused by other
  objects in the database. To delete rows in a heap and deallocate
  pages, use one of the following methods.

Specify the TABLOCK hint in

  the DELETE statement. Using the TABLOCK hint causes the delete
  operation to take an exclusive lock on the table instead of a row or
  page lock. This allows the pages to be deallocated. For more
  information about the TABLOCK hint, see Table Hints (Transact-SQL).

Use TRUNCATE TABLE if all rows are to be deleted from the table.

Create a clustered index on the heap before deleting the rows. You
can

  drop the clustered index after the rows are deleted. This method is
  more time consuming than the previous methods and uses more temporary
  resources.

  Note

  Empty pages can be removed from a heap at any time by using the ALTER
  TABLE  REBUILD statement.

So what you can do now is rebuild the table into another database as you have no space in current database. And then create a clustered index on it to avoid the same situation in future


SQL: How do you create a table inside of a function For some reason, whenever I attempt to create tables and drop them within an SQL function, I'm met with a syntax error. When I do them outside it's always fine but inside the function it becomes a problem.
I've truncated my code significantly but this is similar to what I have:
DECLARE

CREATE TABLE inputCategories(
      Category varchar(255)
   );

DROP TABLE inputCategories;

return 0
END ;
$$ LANGUAGE plpgsql ;

I am required to do all table operations within this function that will be run several times, any help is greatly appreciated, thanks in advance!

there is semi colon missing after RETURN 0 ... and also no BEGIN ...
... so a slight improvement, with no syntax error, would be :
CREATE FUNCTION _name_ ()
RETURNS
integer
AS
$$
BEGIN

CREATE TABLE inputCategories(
      Category varchar(255)
   );

DROP TABLE inputCategories;

RETURN 0 ;

END ;
$$ LANGUAGE plpgsql
   VOLATILE ;

(( VOLATILE indicates to postgres that the function could change the database !! It is the default for a function definition, so there is no need to write it, although it helps to put it in for clarity. VOLATILE also means that function 'can do anything'1, for example the returned result from the function could be different after each call (but not in this simple case) and so a VOLATILE function will be evaluated at each point in a query where it is called. In this case the function is VOLATILE because it has side effects in the database ... it creates and then drops a table. ))
postgres docs : Function Volatility Categories
Another way to define that function would be as an SQL language function :
CREATE FUNCTION _name_ ()
RETURNS
integer
AS
$$

CREATE TABLE inputCategories(
      Category varchar(255)
   );

DROP TABLE inputCategories;

SELECT 0;

$$
LANGUAGE SQL
VOLATILE ;

SQL language functions and PLPGSQL language functions are treated differently, and so depending on the nature of your real function, there may be an advantage to either way. If it is a more complex function requiring dynamically generated SQL, for example, then PLPGSQL is the way. Or some other supported langage like PL/Perl or PL/Python.
There is a discussion of the differences between PLPGSQL and SQL language functions (as well as a little about more recently implemented stored procedures) here :
dba.stackexchange.com : Function Performance


As a MySQL DBA, I sadly admit that MySQL can be rather cavalier in its SQL processing. One of the most infamous feats of this is its GROUP BY behavior.
As example, Aaron Bertrand answered the post Why do we use Group by 1 and Group by 1,2,3 in SQL query? where he described MySQL's GROUP BY as cowboy who-knows-what-will-happen grouping. I just had to agree.
SUGGESTION
Rewrite the GROUP BY using code
select code,min(price) as total
from product group by code

Do three things

Make the query a subquery
Use price as alias instead on total
Join it back to the product table on code and price

Here is the proposed query
select b.* from
(select code,min(price) as price from product group by code) a
inner join product b using (code,price);

or
select b.* from
(select code,min(price) as price from product group by code) a
inner join product b ON a.code=b.code AND a.price=b.price;

Checkout the SQL Fiddle for this
GIVE IT A TRY !!!
UPDATE 2017-01-06 16:17 EST
If there exists more than 1 row with the same minimum price for a given code, you have take the query, make it a subquery, join it to retrieve the minimum id for each (code,price) and join that back to product by id:
select bb.* from
(select a.code,a.price,min(b.id) id from
(select code,min(price) as price from product group by code) a
inner join product b using (code,price)
group by a.code,a.price) aa
inner join product bb using (id);

Checkout the SQL Fiddle for that