Repair database on Replica in sharded set up I am using mongo sharding in my DB. The scenario I am facing is that I have deleted some of my collections in my DB. But as to reclaim the space I need to run repair option which i can not run using Mongos in production.
So my plan is to run Repair on each replica server one by one by switching primary to replica and vice versa so that production systems is working.
Here I have two confusions:

Is it a right approach to repair DB
I have a big Data base like 1 TB of Data so repair option might take a day or two in this case my issue is every daya my application creates sharded DB bucket for that day so while running repair on one replica server metadata in config will get updated will it be a error in this case.
While deleting some old databases from my cluster I stopped the balancer and restart it after delete but still DB is not completely deleted it is showing me 2 or 3 GB memory space used. I check the remaining DB and it shows no collection in it.

In my opinion if your fragmentation is not over 15-20% its not worth doing that (except if you are running out of disk space). What i would do is:

1) Add an arbiter to each replica set (optional)

2) Shutdown one of the secondaries and delete the data directory

3) Start the secondary and let it do the initial sync which removes fragmentation

(at this point you can evaluate if the gain on disk worth continue)

4) When Secondary catch up do the same for the next Secondary

5) When all Secondaries done, step-down Primary and do the same on the x-Primary

6) Remove the arbiter

Important: Your oplog size must be able to keep all operations during the initial sync. Else you will need to resize it (Same applies if you choose repair option)

For your second question replica set member don't hold any metadata. If the majority of Replica set is always available you will not face any issues.


Normalise for a very small database? I am designing a very small database (c. 250 rows). I have a number of datapoints for each record, which under normalization rules, ought to be in separate tables.

For instance:

Percentages of a number of different categories (which should add up to 100)
Multiple sources of data (for instance a record may come from up to 4 sources of data - the reference within these data sources should be recorded
Columns with very different units for instance I want to store residential units and Gross Floor area delivered (different units).

All these fields are optional, none are mandatory.

I am considering not normalizing in case 1, because I want a data integrity check that all the relevant columns in each database row add up to 100%.

For the other two cases, it seems that it is a lot of work to normalise for not a lot of gain. For 3, I would have to design 2 additional tables, one to store the data:

| ID(prim key) | project(FK)          | type(int) | value(float) |
|--------------|----------------------|-----------|--------------|
| 1            |  1                   | 1         | 200.3        |

and another to store the column metadata:

| ID(prim key) | typeID(FK)  | Description |
|--------------|-------------|-------------|
| 1            | 1           |    GFA(m2)  |

rather than just do this in the main table:

| RecordID(prim key) | GFA(m2) | Residential units | .... |
|--------------------|---------|-------------------|------|
| 1                  | 200     | 25                | .... |

With such small data, my main concern is updating things if the schema changes, but I can't think of a reason that will be harder with the de-normalised data.#

Can anyone give me a good reason to normalise for cases 2 and 3?

Thanks

For a small database with just a few rows, you might say that the design is whatever you can maintain and sustain. However, a few things to think about include:

Remember that the first implementation is not the future implementation.  Planning ahead just a little can save headaches in the future.
If the database should scale up in the future you might find yourself refactoring tables and code.
Since in Table3 the columns are named GFA(m2), Residential units, et cetera for whatever you are measuring you, you are committing to changing that table repeatedly as new measures are added.

By the way, I note that your values(float) of 200.3 becomes 200 in the final table. (Maybe just a typo.)  But be sure to keep the data types of the columns defined to fit the data you are storing.

And, yes, if I was delivering this to someone I would normalize it further.


Timestamp : from UNIX to MySQL On one side, I have a MySQL DB where there is table that has a timestamp field. According to MySQL doc, this MySQL TIMESTAMP is similar to DATETIME (with different bounds). As far as I know, the required form is : "Y-m-d H:i:s".

An extract from the DB :

date_heure TIMESTAMP NOT NULL

On the other side, I have a mobile app which sends data to the DB. The time is very important, so among this data (sent using JSON) there is a timestamp field. This timestamp is UNIX-like, in milliseconds.

As you guessed, my purpose is to insert the received timestamp in the database.

To do so, on server-side, I wrote the following PHP code :

$timestamp = $json_decode['t']; //get the UNIX timestamp from the JSON data
$sec_timestamp = $timestamp/1000; //in order to have seconds, not milliseconds
$mysql_timestamp = date("Y-m-d H:i:s", $sec_timestamp); //converting UNIX timestamp into MySQL timestamp.

However, in the DB, the timestamp field still has the following form, for every row : 0000-00-00 00:00:00

There is no problem on the app side, the UNIX timestamp (in milliseconds) is properly sent via JSON.

Hence, what can I do to insert this into the DB ?

There is a built-in function FROM_UNIXTIME() in MySQL, use it:

INSERT INTO yourtable (date_heure, ...) values (FROM_UNIXTIME(1470762668), ...)


How to select from VALUES in mysql Is there some alternative in MYSQL for Postgres VALUES syntax:

select *
from (values('1'), ('2')) as f;

MySQL (as well as Postgres) allows you to select from nothing, so you can do this:

select *
from (
  select 1 as some_value
  union all
  select 2
  -- etc.
) t

Since you intend to join this set to a table, you'll need to provide column aliases that you can reference later at least in the first inner select.


How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM? I'm running a MySQL 5.5 server on my workstation for scientific data analysis and wonder how to configure MySQL in order to get the most out of it performance-wise. The types of query that I typically run involve joins of 10-20 tables and can run for quite long, one to several minutes being no exception at all. Only very few users access the database at the same time (5 being the maximum).
I moved the server from a Lenovo Thinkpad T61 with a 2.2 GHz Dual Core and 4 GB of RAM to the following brand-new machine with hand-selected components:

Intel i7 3770, 4x 3.4 GHz (running @ 4x3.7 GHz)
Z77 chipset
16 GB of DDR3 1600 RAM
Windows 7 Prof 64-bit
Windows and MySQL server run on a Intel 520 series SSD drive.

First tests (running the same query on both machines) showed a definitive improvement in speed for the new one, but the queries still take a lot of time and I had expected more of a boost. The queries in question are fairly well optimized, i.e. all tables have proper key that are also being used as of "explain extended".

Now to my current MySQL settings:
First I should mention that I moved from MyISAM to Innodb long time ago.

Some of my my.ini tweaks (i.e. departures from default settings):

# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
#tmp_table_size=35M
tmp_table_size=4000M
max_heap_table_size=4000M

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system.  Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
#innodb_buffer_pool_size=96M
innodb_buffer_pool_size=800M

general-log
expire_logs_days = 60
general_log_file = "F:/my_query_mysql.log"
log-output = TABLE
optimizer_search_depth = 0 #meant to cure the "statistics state" bug in some queries

I'd like to know whether somebody would suggest changes to the above numbers or even further settings that I do not know of.

I'd appreciate any helpful remark.

Steve

EDIT: I have two queries involving joins across 10-20 tables and ran them on my Lenovo notebook and the new PC. Query #1 took 3m36s on the new machine vs 9m11s on the laptop; Query #2 took 22.5s on the workstation vs 48.5s on the laptop. So the execution speed was improved by roughly the factor 2-2.5.
On the workstation, not even 50% of the RAM was used. The average CPU load across the four cores (as reported by Windows Task Manager) was only about 13%. The load on a per-core basis (as reported by Core Temp) was about 25-40% for ONE core, while it was <=10% for the others, indicating that MySQL does not make use of multiple cores for a single query.

Since you are running MySQL 5.5, you may want to consider configuring InnoDB to access multiple cores

Here are the settings you should be using

innodb_thread_concurrency sets the upper bound on number of concurrent threads that InnoDB can hold open. Best round number to set for this is (2 X Number of CPUs) + Number of Disks. UPDATE : As I learned firsthand from the Percona NYC Conference, you should set this to 0 in order to alert InnoDB Storage Engine to find the best number of threads for the environment it is running in.

innodb_concurrency_tickets sets the number of threads that can bypass concurrency checking with impunity. After that limit is reached, thread concurrency checking becomes the norm again.

innodb_commit_concurrency sets the number of concurrent transactions that can be committed. Since the default is 0, not setting this allows any number of transactions to commit simultaneously.

innodb_thread_sleep_delay sets the number of milliseconds an InnoDB thread can be dormant before reentering the InnoDB queue. Default is 10000 (10 sec).

innodb_read_io_threads and innodb_write_io_threads (both since MySQL 5.1.38) allocate the specified number of threads for reads and writes. Default is 4 and maximum is 64.

innodb_replication_delay imposes thread delay on a slave is innodb_thread_concurrency is reached.

Here are my past posts on MySQL 5.5 and activating multiple cores for InnoDB

Jun 01, 2012 : I've got 16GB of ram, how should I configure MySQL Server?
May 07, 2012 : MySQL Server Performance
Apr 26, 2012 : Is the CPU performance relevant for a database server?
Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian
Oct 07, 2011 : Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?


SQL Server Deferred Stored Procedure Execution I have a stored procedure (e.g. SP_DoSomething) with 2 parameters. Inside the code of this SP there's another SP execution (e.g. SP_DoSomethingElse) with an enormous list of parameters like this:

CREATE PROCEDURE SP_DoSomethingElse
(
    @param1 int,
    ...
    @paramN int
)
AS
BEGIN
    EXEC SP_DoSomethingElse @param1, ... @paramN
END

What I want to achieve is to defer the execution of SP_DoSomethingElse for later execution.

Is there a way to save somewhere (and somehow) the SP execution context (state), including all the parameters and their values?

I prefer not to concatenate as varchar the whole exec.

I found this similar question but is related to replication:

Publishing stored proc execution in transactional replication

If SP_DoSomething is called and there is already a pending SP_DoSomethingElse, no special handling is needed because the calls are not related, and they'll just go into a queue.

This is on SQL Server 2014, but with backward compatibility if possible, no matter if not.

How often is this stored procedure called?

One way to do this would be to use a table to track the times/parameters you want to execute. Create a table with a datetime to execute the stored procedure at, and an active flag.

Every time SP_DoSomething is executed, it inserts values into this table. Run a scheduled job every few minutes to check the table for Datetime values less than GETDATE(), run the stored procedure and change the active flags.

To store the parameters, you would need columns in your table, one for each parameter that you'll be using. Instead of executing SP_DoSomethingElse, place those values into a table.


Nested AD group access not working when nested more than 2 levels deep? For the setup of the security of the databases we have created a number of nested Active Directory groups:

Group
Members
Group type

RGP-EA_PROD-RW
RGP-SparxEA-RW
Domain Local Security

RGP-SparxEA-RW
BGP-FunctionalAnalyst
Domain Local Security

BGP-FunctionalAnalyst
User1
Global Security

On my database I have added the group RGP-EA_PROD-RW as a user login and given it db_datareader and db_datawriter roles.
When User1 tries to login he gets an Access Denied error.
User1 is a member of BGP-FunctionalAnalyst who is a member of RGP-SparxEA-RW, who is a member of RGP-EA_PROD-RW. So User1 is ultimately a member of RGP-EA_PROD-RW.

The SQL Server Logs record two events when the login fails:
Date        3-5-2021 08:56:17 Log       SQL Server (Current - 15-4-2021 05:21:00)
Source      Logon
Message Error: 18456, Severity: 14, State: 38.

and
Date        3-5-2021 08:56:17 Log       SQL Server (Current - 15-4-2021 05:21:00)
Source      Logon
Message Login failed for user 'MYDOMAIN\User1'. Reason: Failed to open the explicitly specified database 'AE_PROD'. [CLIENT: 100.74.0.4]

The database is online, and the AD group has been setup to have access to it. It works just fine with the other groups, just not with the group nested at the third level.
I did a little bit of experimenting and found that when setting up logins:

Login User1 directly works
Login BGP-FunctionalAnalyst works
Login RGP-SparxEA-RW works
Login RGP-EA_PROD-RW does not work

I'm not sure what is causing this problem:

Is there a maximum level of nesting supported by SQL Server?
Is there a certain property I should look for in AD? (both Domain Local groups look similar to me)
Something else?

This is a completely new Active Directory with only a handful of users and groups.
We are trying to apply the AGDLP guideline in a single AD domain.

RGxxx groups are Resource Groups, defined as Local Security Groups. They are used to assign access rights to resources, and they only have other groups as members, no users.
BGxxx groups are Business Groups defined as Global Security Groups. They have only users as members and are not used to assign access rights.

Sorry I don't get it. You write:

On my database I have added the group RGP-EA_PROD-RW as a user login and given it db_datareader and db_datawriter roles

Okay. So that means RGP-SparxEA-RW ought to be able to login and do stuff.
User1 is not a member of RGP-EA_PROD-RW so why do you expect for User1 to be able to login?

User1 is a member of BGP-FunctionalAnalyst who is a member of RGP-SparxEA-RW, who is a member of RGP-EA_PROD-RW. So yeah, User1 is ultimately a member of RGP-EA_PROD-RW.

MYDOMAIN\User1 suggests when you login you the username needs to be prefixed with a real domain.  There is a weirdness that the groups that work are all Domain Local Security Groups.  Maybe the Global Security Group only exists in Azure - not on the local domain controllers. Next step is to query group membership with VBscript or Powershell and confirm whether the group membership is what you think it is.
Standard practice is to make local groups inherit from global groups. In your case it's the other way around.


How to retrieve the value of deleted key in jsonb? Using the minus operator of jsonb, we can delete the required key, which is super cool, but what if we need to retrieve the value of deleted key?

Thus, if the key was not found, a null is returned or so. Otherwise the value is returned.

Is this possible?

The below function won't work on keys that are inside arrays.  If you want something like that, you'll first have to define the behaviour for such JSON structures.

The main idea is to create a table returning function, which both returns the remaining jsonb and the popped key-value pair.  The rest is finding and reconstructing the key-value pair in the source structure.

CREATE OR REPLACE FUNCTION jsonb_pop(source jsonb, key text)
    RETURNS TABLE (result jsonb, popped jsonb)
    LANGUAGE sql AS
$$
WITH elem(popped) AS (
    SELECT jsonb_strip_nulls(jsonb_build_object(key, source -> key))
)
SELECT source - key, NULLIF(popped, '{}'::jsonb) FROM elem;
$$;


SQL Server 2016 with high tempdb usage and low RAM usage I've got a small instance of SQL Server Standard that I'm running HammerDB TPC-H (1 GB) benchmarks on. It's got some configuration issue though where it's writing everything into tempdb on disk, and only tempdb which seems to be bottlnecking me as my physical storage is pretty slow:

The database by itself is about 3.5 GB, and the server has 16 GB RAM.

My understanding is that tempdb is only used when the server is under memory pressure, but it never even hits 30% usage. Physical_memory_in_use_kb is never much more than 3 GB, though it steadily grows over time. The VMware host is not having any obvious trouble either.

sp_blitz results warn about high CPU usage (~75-85%) and not much else.

This is an almost default installation of SQL Server Standard in a cluster - Is this behavior of writing to tempdb normal in the case of these longer-running benchmark queries? If not, where can I start troubleshooting?

In response to Comments:

Max memory is set to 14336 GB in Server Properties > Memory
Relevant hammerdb code with tcph queries: https://pastebin.com/AwJ000Qp
Example planfile (query #18 above): https://www.brentozar.com/pastetheplan/?id=B1CkC9kE4

My understanding is that tempdb is only used when the server is under memory pressure

That's not exactly true.  There are lots of things that can cause tempdb usage:

the most obvious would be explicitly writing data to local or global temporary tables (#tempTableName or ##tempTableName).  I imagine this is not your problem though
version store activity (for instance because of queries using snapshot isolation or RCSI)
there are a few execution plan operators that can "spill" to tempdb

It's likely that your problem is spills.  For some good general information, and links to specific troubleshooting steps, check out this answer from Paul White to the question What frequency of hash/sort spills into tempdb is concerning?

You could also run sp_BlitzCache @SortOrder = 'spills' (from the First Responder Kit) to find some of the most problematic queries if they are still in your plan cache.

As far as dealing with the spills, you could post separate questions about the bad queries and probably get help on resolving whatever is causing the spills (bad cardinality estimates, small memory grants, etc).

Memory pressure can lead to low memory grants, which can lead to tempdb spills - which is why I said your initial assertion about memory pressure is not exactly true - because it is true sometimes!

The execution plan you provided is an estimated execution plan, so it doesn't indicate if any operators spilled to tempdb.  But it does have multiple memory-consuming operators (sorts in particular), so it has the potential to generate tempdb activity.


Block SSRS Developers from embedding SQL Statements in Report Is there any way I can automatically block SSRS reports with embedded SQL from accessing my Databases?

I want to force a standard practice of using Stored Procedures, so the DBAs can take back control.

I’d like to avoid implementing a Policing solution where daily jobs interrogate the SSRS Reports for SQL Statements.

I want to block users from having Read Permissions on the databases, so here’s my plan:

All Data Sources use an SSRS service account that has read
permissions on designated databases.
The SSRS service account will be given execute permissions on a per
SP basis (so I can audit every SP that is created to support an SSRS
report).
The DBA will be in control of the SP deployment and permissions
management.
Access to the SSRS rdl file is managed via AD, this is the single
area where I manage end user permissions. (the user can only see data
via an SSRS report)

My problem is that a “Rogue” Developer can modify a report to contain a SQL statement and use the SSRS service account to  read data.

If I can’t Block this behaviour then the very busy DBA will have to take ownership of deploying SSRS reports to live.

create a database schema (lets say "reporting"), and add only the stored procedures you want the developer to have access to that schema, grant the allowed users access only to that schema.


Selecting latest row for user grouped by day help Hoping someone can help me here as I can't quite wrap my head around the best way to do this.

Im trying to SUM() some numbers from a JSON block, which I have working just fine, how ever there could be multiple records per day per user, and I only want to SUM the numbers of the latest record per day per user.

So, essentially using the record that matches MAX(created_at) per user, per day.

Some sample data

id  | user_id  | scan_id  | data           | created_at
1   | 1        | 100      | {"score": 40}  | 2015-11-06 22:15:27
2   | 1        | 101      | {"score": 50}  | 2015-11-06 22:18:27
3   | 3        | 102      | {"score": 20}  | 2015-11-06 22:15:27
4   | 3        | 103      | {"score": 70}  | 2015-11-06 22:12:27
5   | 5        | 104      | {"score": 40}  | 2015-11-06 22:15:27
6   | 6        | 105      | {"score": 10}  | 2015-12-06 22:15:27

In the above data,  I want to SUM the values from data->'score', but you can see the first 4 rows are from two users.  I only want to use the LATEST record in the SUM, so that would be record id's 2 and 3, but not 1 and 4 (as they were older than the other records)

Record 6, would fall under its own day as its on a different date.

So, this query works without getting the latest record,  I would like to know how to alter it to only use the latest record per user per day.

SELECT
 SUM((DATA ->> 'score')::integer) AS score,
 count(*) as count,
 created_at::date
FROM
 scores
GROUP BY created_at::date

One way (not necessarily the most efficient) is to use the Postgres special DISTINCT ON modifier in a subquery, and then sum:

SELECT
    SUM(score) AS score,
    COUNT(*) AS count,
    created_at_date
FROM
  ( SELECT DISTINCT ON (user_id, created_at::date)
        created_at::date AS created_at_date,
        (DATA ->> 'score')::integer AS score
    FROM
        scores
    ORDER BY
        user_id, created_at::date, created_at DESC
  ) AS g
GROUP BY
    created_at_date ;

Tested at SQLfiddle.


Is it "OK" to have a table that only contain foreign keys? By "OK", I mean "it doesn't have any bad impact with the overall database design".

For example, will it cause insertion problems?
Or will it be a problem in the future when I add field in the table?

For example, I have a simple database with three tables:

Customer(CustomerID, CustomerName)
Product(ProductID, ProductName)
Order(OrderID, CustomerID, ProductID)

It is clear that the Order table is only a mapping between the two tables (Customer and Product). The OrderID in table Order has no use, it is there only to identify each row.

How about if I don't want to identify each row because for example we have the rule:

  A user can't order the same product twice.

Of course, it is far from reality, but it is only an example.

The bottom line question is, in the example given above, is it OK to remove the OrderID in the table Order and setting both ProductID and CustomerID from table Order as a composite primary key? In this case, the Order table only contains foreign keys and are set as a composite primary key.

As a beginner, I have searched a lot about database design concepts and found some terms like "primary key", "composite primary key", "database normalization", etc., but I don't know what will happen in practice so I decided to ask here. I am just worried about its future implications because I don't know if there is a particular circumstance that it will be a disadvantage.

There is no problem having a table that consists of foreign keys only.  In your particular example it would be OK to remove the OrderID. Relational theory states that each table should have a candidate key so, ideally, the business rule you mention would be in place to make the foreign key only table a "good" table.  In practice, DBMS software will not care and will allow a table without a unique constraint.

There will not, generally, be any problem with inserts as long as you observe the uniqueness business rules you decide for your system. Conversely, without uniqueness, you may end up with duplicate rows from a SELECT which the application would have to deal with.

If you add further columns in the future there will be no problems due to the existing columns all being foreign keys.

This situation is very common when there is a many-to-many relationship between entity types. This is implemented as an intersection table containing the foreign keys.


How to grant permissions to allow anything inside a SQL Server schema? I have a schema called training. I have a new role called training_modify. Here are the desired permissions for users placed in that role:

Select, update, insert, and delete permissions on any object in that schema
Create any object in that schema
Drop any object in that schema
No permissions on any other object outside that schema (except for those granted through the public role
No create/drop/alter permissions for the schema itself
The role and individual users should not require membership in db_ddladmin, db_datareader, db_datawriter, or any other default security roles

In simple terms, I want the users in the role to be able to do anything they want within the schema without affecting/seeing anything outside the schema or the schema itself. What is the least privilege to grant this type of access?

My approach so far:

Create training schema with dbo as the owner
Create training_modify role with dbo as the owner
Grant all permissions on the schema to the role
Grant create objects to the role
Add a user to the role

Here is the code for the above steps:

CREATE SCHEMA training AUTHORIZATION dbo;

CREATE ROLE training_modify AUTHORIZATION dbo;

GRANT ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT,
          UPDATE, VIEW DEFINITION ON SCHEMA::training TO training_modify;

GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO training_modify;

EXEC sp_addrolemember 'training_modify', 'example_user';

Is this the right approach? Does it have any unintended consequences? I am most worried about how ownership chaining may affect this approach negatively with dbo being the owner of the schema and the role (as well as other schemas/objects in the DB), and the alter permission being granted on the schema.

Also:

Should dbo still own the role? or should TestOwner own both the role and the schema?
Is it accurate that if I took away the CREATE PROCEDURE and CREATE FUNCTION permissions, this would prevent any DML on objects outside the specific schema? I don't think I care if they can create procedures, although I would like them to be able to execute procedures/functions (which I think would be covered by the EXECUTE permission on the schema). If this is accurate - are there any pros/cons between this approach and changing the owner of the schema?

Is this the right approach? Does it have any unintended consequences?
  I am most worried about how ownership chaining may affect this
  approach negatively with dbo being the owner of the schema and the
  role (as well as other schemas/objects in the DB), and the alter
  permission being granted on the schema.

The ALTER permission in the training schema will only grant ALTER permissions to objects in that schema, as stated by Solomon Rutzky in the comments, ownership chaining doesn't apply for DDL, only DML/Execute.

Ownership chaining provides a mechanism for providing limited to access to objects that a user does not have explicit permissions on by chaining the ownership of one object to another. For example, if you have a stored procedure in the training schema that queries a table in the dbo schema and both the procedure and table are owned by dbo then users who have permission to execute the procedure will be able to get the query results from the table they otherwise do not have access to.

This doesn't provide any direct access to the table, the only access to the table is via the stored procedure. This is a method for granting controlled access to an object without needing to expose the underlying object. See this link for more info.

In your specific scenario, to allow users to perform DML/Execute on objects in another schema, you would need a stored procedure/function/view that is owned by dbo and executes the DML/Execute statements against other schemas also owned by dbo. Without this, your users will be prevented from running the commands against schemas/objects outside of training.


How to catch and handle only specific Oracle exceptions? From this and this i guess, that there is no predefined Named System Exceptions for ORA-00955.

How can I rewrite the following to catch only the error ORA-00955?

begin
      EXECUTE IMMEDIATE 'CREATE SEQUENCE S_TEST START WITH 1 INCREMENT BY 1';
exception when OTHERS then
    Null;
end;

BTW Is there any syntax to catch errors by just providing the error-codes?

You have two options:

Refer to the exception directly by number:

BEGIN
    EXECUTE IMMEDIATE 'CREATE SEQUENCE S_TEST START WITH 1 INCREMENT BY 1';
EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -955 THEN
        NULL; -- suppresses ORA-00955 exception
      ELSE
         RAISE;
      END IF;
END;

Other option is to use EXCEPTION_INIT Pragma directive to bind a known Oracle error number to user defined exception;

DECLARE
   name_in_use exception; --declare a user defined exception
   pragma exception_init( name_in_use, -955 ); --bind the error code to the above
BEGIN
    EXECUTE IMMEDIATE 'CREATE SEQUENCE S_TEST START WITH 1 INCREMENT BY 1';
EXCEPTION
    when name_in_use then
       null; --suppress ORA-00955 exception
END;

  BTW Is there any syntax to catch errors by just providing the error-codes?

Yes, I've demonstrated it in the first example

Further reading for variations on this:

Oracle Reference Documentation on Handling PL/SQL Exceptions


Getting proper output of my SELECT clause. SQLite I am coding a bus connection search engine. My goal is to see the time when the bus is departing, while the stop_from and stop_to names are provided.

I have created the STOP table

CREATE TABLE STOP (
ID INTEGER PRIMARY KEY,
STOP_NAME TEXT NOT NULL,
ROUTE_ID INTEGER NOT NULL
);

and the TIMETABLE table

CREATE TABLE TIMETABLE (
ID INTEGER PRIMARY KEY,
TIME TEXT NOT NULL,
STOP_ID INTEGER NOT NULL,
FOREIGN KEY (STOP_ID) REFERENCES STOP (ID)
);

The route starts in Lidzbark Warmiński and leads via Smolajny and Dobre Miasto to Olsztyn. There are four connections per day. And I have also inserted all the possible routes from Lidzbark Warmiński to Olsztyn

INSERT INTO STOP VALUES
(1,'Lidzbark Warmiński', 1),
(2, 'Smolajny', 1),
(3, 'Dobre Miasto', 1),
(4, 'Olsztyn', 1),
(5, 'Lidzbark Warmiński', 2),
(6, 'Smolajny', 2),
(7, 'Dobre Miasto', 2),
(8, 'Lidzbark Warmiński', 3),
(9, 'Smolajny', 3),
(10, 'Smolajny', 4),
(11, 'Dobre Miasto', 4),
(12, 'Smolajny', 5),
(13, 'Olsztyn', 5),
(14, 'Dobre Miasto', 6),
(15, 'Olsztyn', 6),
(16, 'Smolajny', 7),
(17, 'Dobre Miasto', 7),
(18, 'Olsztyn', 7);

and the timetables regarding them

INSERT INTO TIMETABLE VALUES
--towards Olsztyn
(1, '08:00', 1),
(2, '12:00', 1),
(3, '15:00', 1),
(4, '19:00', 1),
(5, '08:00', 2),
(6, '12:00', 2),
(7, '15:00', 2),
(8, '19:00', 2),
(9, '08:00', 3),
(10, '12:00', 3),
(11, '15:00', 3),
(12, '19:00', 3),
(13, '08:20', 4),
(14, '12:20', 4),
(15, '15:20', 4),
(16, '19:20', 4),
(17, '08:20', 5),
(18, '12:20', 5),
(19, '15:20', 5),
(20, '19:20', 5),
(21, '08:30', 6),
(22, '12:30', 6),
(23, '15:30', 6),
(24, '19:30', 6),
(25, '08:20', 7),
(26, '12:20', 7),
(27, '15:20', 7),
(28, '19:20', 7);

I am developing under python 3.8 and this is a function with my SELECT clause

def fetch_hours(cursor, stop_from_name, stop_to_name):
    cursor.execute(
        '''SELECT T.TIME FROM TIMETABLE T
             JOIN STOP S1 ON T.STOP_ID = S1.ID
             JOIN STOP S2 ON S2.ROUTE_ID = S1.ROUTE_ID
             WHERE S1.STOP_NAME = ?
             AND S2.STOP_NAME = ? ORDER BY T.TIME ASC''',
        (stop_from_name, stop_to_name))
    return cursor.fetchall()

However, I have encountered some problems. For a wide range of bus stops this code doesn't work well. Providing stop_from Smolajny and stop_to Dobre Miasto, the result is

Departures: 08:00, 08:30, 12:00, 12:30, 15:00, 15:30, 19:00, 19:30

While I would like to see only four hours. For some connections it doesn't work at all and for some it is OK.

The desired effect is to see four departure hours for any stop_from and stop_to on this route (from Lidzbark Warmiński to Olsztyn). The reverse route is now commented, and if it would be helpful, the rest of my code can be achieved here: https://github.com/wwmarkositrawe/autobusy-backend

While looking for help,
Wishing you best regards,
marasecki

SELECT * FROM ... gives this output:

ID  TIME   STOP_ID  ID  STOP_NAME  ROUTE_ID  ID  STOP_NAME     ROUTE_ID
--  -----  -------  --  ---------  --------  --  ------------  --------
5   08:00  2        2   Smolajny   1         3   Dobre Miasto  1
21  08:30  6        6   Smolajny   2         7   Dobre Miasto  2
6   12:00  2        2   Smolajny   1         3   Dobre Miasto  1
22  12:30  6        6   Smolajny   2         7   Dobre Miasto  2
7   15:00  2        2   Smolajny   1         3   Dobre Miasto  1
23  15:30  6        6   Smolajny   2         7   Dobre Miasto  2
8   19:00  2        2   Smolajny   1         3   Dobre Miasto  1
24  19:30  6        6   Smolajny   2         7   Dobre Miasto  2

Both route 1 and route 2 have stops at Smolajny and Dobre Miasto, and the first stops (2 and 6) have four times each. This output is correct.


Why the MySQL query for where clause is very slow? I was running a query, the rows of the table are about 10 millions:

select * from zhihu.zhihu_answer_meta  where voteup_count>100000 limit 5;

However, it took more than 20 mins to finish.

I also tried other condition and found that it ran fast (in a second) with small number in where clause:

select * from zhihu.zhihu_answer_meta  where voteup_count>10 limit 5;
select * from zhihu.zhihu_answer_meta  where voteup_count>100 limit 5;

the DDL is as below:

CREATE TABLE `zhihu_answer_meta` (
  `answer_id` bigint(20) NOT NULL,
  `author_name` varchar(500) DEFAULT NULL,
  `reward_member_count` int(11) DEFAULT NULL,
  `answer_created` datetime DEFAULT NULL,
  `voteup_count` int(11) DEFAULT NULL,
  `is_advertiser` tinyint(1) DEFAULT NULL,
  `author_url_token` varchar(500) DEFAULT NULL,
  `comment_count` int(11) DEFAULT NULL,
  `is_org` tinyint(1) DEFAULT NULL,
  `question_type` varchar(100) DEFAULT NULL,
  `question_created` datetime DEFAULT NULL,
  `author_type` varchar(100) DEFAULT NULL,
  `insert_time` datetime DEFAULT NULL,
  `badge_num` int(11) DEFAULT NULL,
  `answer_updated` datetime DEFAULT NULL,
  `gender` tinyint(4) DEFAULT NULL,
  `reward_total_money` int(11) DEFAULT NULL,
  `author_id` varchar(300) DEFAULT NULL,
  `can_comment` tinyint(1) DEFAULT NULL,
  `question_id` bigint(20) DEFAULT NULL,
  UNIQUE KEY `answer_index` (`answer_id`,`insert_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

So why is it slow and is there any way to optimize?

Thanks for any advice.

You will need to define an index on the voteup_count column for the query to be performant. Without this index, this query will be scanning all the rows until it reaches the first value of the Range condition.

Assuming that the data tree is in such a way that rows with low voteup_count values come first (basically, in ascending order). In that scenario, MySQL does not need to scan a lot of rows, and stops as soon as the LIMIT is achieved.

To make the query fast in all scenarios, define the following index:

ALTER TABLE zhihu.zhihu_answer_meta ADD INDEX voteup_count_idx (voteup_count) ;

Also, note that LIMIT wihout ORDER BY is non-deterministic, as data is basically stored as an unordered set, so you can get random set of rows.

Also, another optimization tip is to avoid using SELECT *, and instead specify the column name(s) explicitly. Do read: Why is SELECT * considered harmful?


Can I prevent deadlock using `WITH(NOLOCK)`? I have a table which I'm only Insert, Delete and Select (no Updates)

on Insert, Sometimes it used a session lock (WITH (TABLOCKX))

I have a program using ADO which Select from the table using the RecordSet::Open command to open a simple sequential query.

like:

Select * from t1 where id >= @from_id And >= @to_id`

Or:

Select top(10) * from t1 where id >= @id Order by id

The id column is an Identity column.

When I'm open the query, sometimes I'm getting a deadlock. (the table is locked by the Insert query and the id column's index is locked by the Select)

I'm trying to prevent the deadlocks.

Should I open the select queries WITH(NOLOCK)? may it prevent the deadlocks?

A better idea would be to enable snapshot isolation: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

This will enable each transaction to have its own snapshot of data and will not lock, or block other transactions.


Is there an alternative to "Create or replace procedure" in MySQL? Is there an version of "create or replace procedure" for MySQL? I can't seem to do this or script the dropping of the procedure if exists before recompiling without getting an error message that the stored procedure exists.

DELIMITER $$

-- would love to be able to drop procedure if exists db.sp_tmp_90days;
-- or use "create or replace"

create procedure db.sp_tmp_90days()

BEGIN
drop table db.tmp_90days;

create table db.tmp_90days (
    user_name varchar(128),
    first_name varchar(50),
    last_name varchar(50),
    system varchar(10),
    last_login datetime
);

alter table db.tmp_90days add index idx_user_name(user_name);
alter table db.tmp_90days add index idx_system(system);
alter table db.tmp_90days add index idx_last_login(last_login);

insert into db.tmp_90days (user_name, first_name, last_name, system, last_login)
    SELECT
        [...]
END $$

This is the syntax to drop it if it exists

DROP PROCEDURE IF EXISTS db.sp_tmp_90days;


Parameter Sniffing and Multiple Plans in Cache We have a multi-tenanted database. FirmID is the partition key and we have lots of different firms.
I am running into a parameter sniffing issue and I am having a heck of a time getting around it.
I would rather not use any [Options] on the query.
My latest thought was to change the name of the parameter I am using for the firm. In the snippet below you will see that instead of using @FirmID I called it @Firm611 where 611 is the actual firm of the ID. This will give me a unique query for every firm.
select
    c.ID [_cid],
    c.Name [Name]
from vwClaims c with(nolock)
where c.FirmID=@Firm611
and (c.Name is not null and c.Name!='')

select
    c.ID [_cid],
    c.Name [Name]
from vwClaims c with(nolock)
where c.FirmID=@Firm625
and (c.Name is not null and c.Name!='')

After running Brent Ozar's sp_BlitzCache, I found that it is just compiling down to the same query and causing duplicate cache entries:

My question is am I reading that result right? Even though I am changing the parameter name, is it really still using the same plan and parameter sniffing?

There are a few different questions in here.
Q: If two identical queries use two different parameter names, what does SQL Server do?
SQL Server will build a separate execution plan for each of the queries. SQL Server considers even the slightest change of the text - even as simple as a space - to be a different query.
Each variation will get compiled and cached completely independently of the other.
Q: Will these two queries get different plans or the same plan?
SQL Server will examine the contents of the parameter and build an execution plan based on that. @Firm611's plan will be compiled based on the selectivity of @Firm611, and @Firm625's plan will be compiled based on the selectivity of @Firm625.
If the two parameters happen to have the same selectivity, both compiled plans may end up with the exact same shape, same index usage, same memory grants, etc. However, they will still be compiled independently and cached separately.
Q: What does the warning mean in sp_BlitzCache?
The warning indicates that you have two "different" queries that aren't really different - they're getting plans that are shaped the same. That means you're wasting time building multiple versions of them because:

You're wasting CPU resources repeatedly compiling the same plan
You're wasting memory caching different plans (that are really the same plan)

Q: Would OPTION (RECOMPILE) fix this?
No, because then all of them would get recompiled every time they run, leading to higher CPU usage. I only recommend recompile hints when a query runs once per minute or less - if it runs more often than that, I start worrying about the CPU overhead due to the frequent compilations of all the queries where you're using that trick.
I use a baseline of once per minute because that's only this query - you have to keep in mind the total workload on the server. If you slap recompile hints on every query, you can end up with 100% CPU utilization - even if each individual query is only run 1x per second, that's a lot of compiles when you consider every query in total.


How to Get the Second Sequence of Characters using Regular Expressions I have a field with an address in it (ex. 68 TIDAL BREEZE DR) and this regular expression gets everything before the last sequence (ex. 68 TIDAL BREEZE):

substring (address from '(.*) ')

My question is, how do I modify this expression to get everything after the first sequence (ex. 68) and everything before the last sequence (ex. DR) like so: TIDAL BREEZE?

I'm using PostgreSQL 9.5.

postgres=# select substring('68 TIDAL BREEZE DR' from '\s+(.*)\s');
  substring
--------------
 TIDAL BREEZE
(1 row)

Lazy match the first whitespace character to chop the first bit off.


Recover mysql using data folder One of our MySQL servers died but somehow we were able to recover all the files from it. This server was a slave at one time however we stopped it being a slave so we could use it for housekeeping tasks and reports, etc.

Can someone tell me how to recover with this?

I now have these files and a backup of my.cnf

    ll /root/home/recovered/mysql/
    -rw-r----- 1 root root          117 Jun  1 09:30 rabney-relay-bin.000001
    -rw-r----- 1 root root           26 Jun  1 09:30 rabney-relay-bin.index
    -rw-r----- 1 root root 113055367168 Jun  1 10:42 ibdata1
    -rw-r----- 1 root root   1073741824 Jun  1 09:33 ib_logfile0
    -rw-r----- 1 root root   1073741824 Jun  1 09:33 ib_logfile1
    drwx------ 2 root root         4096 Jun  1 09:30 mysql
    drwx------ 2 root root        16384 Jun  1 09:30 production_copy
    -rw-r----- 1 root root   3253518648 Jun  1 09:32 slowquery.log
    drwx------ 2 root root         4096 Jun  1 09:30 test

I've been reading articles on Google regarding this but not sure how to do it yet.

STEP 01

Go see if MySQL is running on the Server. Run this

service mysql status

If the status says SUCCESS! MySQL running (some number) then shutdown mysql

service mysql stop

STEP 02

Run ls -l /etc/my.cnf. If the file exists, rename it with mv /etc/my.cnf /etc/my.cnf.backup

Then, copy your my.cnf into /etc

STEP 03

See if you have a previous mysql setup

ls -l /var/lib/mysql

If you do, do this

mv /var/lib/mysql /var/lib/mysql.old
mkdir /var/lib/mysql

Then, copy your /root/home/recovered/mysql into /var/lib/mysql

STEP 04

chown -R mysql:mysql /var/lib/mysql

STEP 05

service mysql start

GIVE IT A TRY !!!


SQL Server DR, mirroring or virtualise and mirror I have a client with a small business server 2008 Premium installation.  This obviously includes SQL Server 2008 standard on a separate 2008 server.  On the SQL we run several line of business applications.

The client has got the the size where if we were to have a failure it would cripple the business.  We have ample backups protecting the data, but it would obviously take time to get a new server up and running etc.

What we want is a stand by server in a remote office, but I need to know the best way of replicating the SQL server.  I see there are different ways, snap shot or transactional etc, but I haven't found a decent article describing the pros and cons of them.

I dont want to put much more stress onto the SQL server as it is quite busy with both reads and updates.  I don't want the primary server waiting on the secondary server to update before it commits a write.

Also the table structure and views change fairly regularly as new features/updates are added to the LOB applications.

I'm wondering if anyone has any opinion on what method may help.

Also wondering if we would be better off virtualising the server and mirroring this, which I believe is quite good with the new hyper V.

edit (OP, once you have associated your accounts, please incorporate this further information to make a single, cohesive question.)

sorry for the delay.

I suppose in terms of data loss, i suppose an hour wouldnt be the end of the world.

Hardware, wouldnt be identical as its currently on an hp ml350 g6

i was talking about hyper-v 2012s replication to a completely seperate server.

I guess i am concerned about disk io as one of the lob dbs is pretty hefty and slow, although we got the developers to re-write some of the worst bit to make it better.

I guess my biggest concern is if when writing data it has to wait for both the local and remote server to update.  Does log shipping need do this?

The remote server will be on remote ADSL connection, and i would like to avoid having to replicate it twice.

Also the SQL server is part of Small business server 2008 premium, but was brought as OEM, are we allowed to either move the second windows server license onto another machine (the main server will remain on the origional hw) or can we move just the SQL onto a new windows 2012 box?  as this would mean we can move the sql onto better hw, and use the old one as the DR box.

I would implement Log Shipping, which is just a process of taking transaction log backups at one site, and restoring them at another site. This operates at the database level, and it will inherently handle all the schema changes because backups operate at a level above that.

There's really no other sane solution on Standard Edition because the requirement of having an asynchronous process severely limits the available options. (If you're just getting started in this area, don't even think about any kind of SQL Server replication.)

Note also that as far as I can tell (and I could be wrong about this), Windows Server 2012 storage virtualization (aka Storage Spaces) is meant as an enhanced RAID controller given a set of local disks; it's not for replicating virtual machines across data centers. I believe to do that requires specific storage hardware that supports it, and I don't think you're looking to go down that path right now.

  I dont want to put much more stress onto the SQL server as it is quite busy with both reads and updates.

Quantify this. What kind of options do you have to upgrade the hardware (I'm assuming it's several years old at this point)? Is the bottleneck CPU or I/O?

Regardless, if you're legitimately maxing out the available resources right now, virtualizing is a bad idea, as it adds inherent overhead to the system. Virtualization is great, and it may be appropriate here for other reasons, but definitely not if you're concerned about performance on your current physical hardware.

  I don't want the primary server waiting on the secondary server to update before it commits a write.

I'm assuming this is because there's a slow link between the sites? If that's the case, and you want to protect against hardware failure, consider implementing a synchronous site-local mirror (on a separate physical box using Database Mirroring) for high availability, and then an asynchronous remote site using Log Shipping for disaster recovery.

Any kind of mirroring or replication is going to add some latency or load to the primary server. How much depends on the hardware and the workload. This is the tradeoff you have to make to protect against failures. It isn't completely free, but with proper hardware and design, it can be minimized to the point that it's irrelevant.


Calculating the median value in a MySQL table with CodeIgniter This is my model file in codeigniter. I need to calculate the median for this variable: budget_breakdown.breakdown_grants. How can I do this calculation?

function reports_details1() {
        $this->db->select('budget_breakdown.breakdown_grants');
    //survey_respondent_info.state,survey_respondent_info.survey_id,budgets.budget_id,
        $this->db->from('survey_respondent_info');
        $this->db->join('budgets',
                        'budgets.survey_id=survey_respondent_info.survey_id' , 'left');
        $this->db->join('budget_breakdown',
                            'budgets.budget_id=budget_breakdown.budget_id' , 'left');
        $this->db->where('budgets.budget_option_id', 2);
        $query1 = $this->db->get();
        $result = $query1->result();
        return $result;
    }

Not being familiar with codeigniter, I am doing this first in SQL, and then make a guess on how to translate it back into codeigniter:

I translated your query to SQL first"

SELECT budget_breakdown.breakdown_grants
FROM survey_respondent_info
LEFT JOIN budgets ON budgets.survey_id=survey_respondent_info.survey_id
LEFT JOIN budget_breakdown ON budgets.budget_id=budget_breakdown.budget_id
WHERE budgets.budget_option_id  = 2;

Now, I alter the query to find the midpoint:

SELECT CEIL(COUNT(*)/2
INTO @midpoint
FROM survey_respondent_info
LEFT JOIN budgets ON budgets.survey_id=survey_respondent_info.survey_id
LEFT JOIN budget_breakdown ON budgets.budget_id=budget_breakdown.budget_id
WHERE budgets.budget_option_id  = 2;

Finally, I use the @midpoint variable to get the median:

PREPARE STMT FROM
   'SELECT max(budget_breakdown.breakdown_grants) FROM (
    SELECT budget_breakdown.breakdown_grants
    FROM survey_respondent_info
    LEFT JOIN budgets ON budgets.survey_id=survey_respondent_info.survey_id
    LEFT JOIN budget_breakdown ON budgets.budget_id=budget_breakdown.budget_id
    WHERE budgets.budget_option_id  = 2
    ORDER BY budget_breakdown.breakdown_grants LIMIT ?) x';

EXECUTE STMT USING @midpoint;

I went through the PREPARE and EXECUTE, because LIMIT doesn't work directly with a variable, this is a workaround.

Now put it back into codeigniter:

function reports_median() {

    $this->db->select('CEIL(COUNT(*)/2');
    $this->db->from('survey_respondent_info');
    $this->db->join('budgets',
                    'budgets.survey_id=survey_respondent_info.survey_id' , 'left');
    $this->db->join('budget_breakdown',
                        'budgets.budget_id=budget_breakdown.budget_id' , 'left');
    $this->db->where('budgets.budget_option_id', 2);
    $query1 = $this->db->get();
    $midpoint = $query1->result();
    $midpoint = $midpoint/2;

    $this->db->select('max(budget_breakdown.breakdown_grants');
    $this->db->from('survey_respondent_info');
    $this->db->join('budgets',
                    'budgets.survey_id=survey_respondent_info.survey_id' , 'left');
    $this->db->join('budget_breakdown',
                        'budgets.budget_id=budget_breakdown.budget_id' , 'left');
    $this->db->where('budgets.budget_option_id', 2);
    $this->db->order_by('budget_breakdown.breakdown_grants', 'asc');
    $this->db->limit($midpoint);
    $query2 = $this->db->get();
    $median = $query2->result();

}

As I said, I am not familiar with codeigniter and can't test that part. It is a good guess.


AlwaysEncrypted Conversion Fails I'm trying to encrypt a column with AlwaysEncrypted and I get the following error.

  Could not deploy package.

  Warning SQL72012: The object [AlwaysEncryptedDemo] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

  Warning SQL72012: The object [AlwaysEncryptedDemo_log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

  Warning SQL72015: The table [dbo].[tmp_ms_xx_t1] is being dropped, data loss could occur.

  Error SQL72014: .Net SqlClient Data Provider: Msg 50000, Level 16, State 127, Line 6 Rows were detected. The schema update is terminating because data loss might occur.

  Error SQL72045: Script execution error.

The executed script:

IF EXISTS (SELECT TOP 1 1
           FROM   [dbo].[tmp_ms_xx_t1])
    RAISERROR (N'Rows were detected. The schema update is terminating' +
          ' because data loss might occur.', 16, 127)
        WITH NOWAIT;

This happens both in SSMS and using the generated PowerShell. The table schema is simply one column, nvarchar(10). We're only testing this out.

Can someone help me get past this error please?

So, this actually wasn't a bug. There is (was) a trigger on the table that was changing data. The changing data is what caused the conversion to fail. Disabling the trigger did not help, it had to be dropped.


Is ELT appropriate for life critcal applications such as Medical Systems when converting to a new application? My small experience with ELT and reading makes it look like it is mostly for development.  Because the data is not cleaned up like in ETL that makes me think the new application will need to adapt its queries to the old data structure alongside the new application's new data structure.  And, then, eventually ETL takes place somewhere down the road, in the new database, overtime until you only have your new application, new schema, etc., the old data schema isn't used anymore.  It may still be in some tables somewhere, but unused, archive only.

For something like a hospital, ELT doesn't feel right.  I would think the hospital wants things cleaned up before the conversion.

Is ELT for going to a new application in a super critical life or death system?

Edit:  This article was suggested, http://www.jamesserra.com/archive/2012/01/difference-between-etl-and-elt/

After reading this helpful article, one of the ELT cases for preferring ELT is:

  The source database and the target database are the same

What does this mean?  The same database engine or the same schema?  New applications that replace 20-year-old systems don't usually have the same schema, so I do not understand.

ELT is fine for a critical system. Your problem is predicated on this faulty assumption:

"Because the data is not cleaned up like in ETL..."

You can do most any cleanup with ELT as you do with ETL. You just generally do it in a staging table on the target system instead of in-flight between the source and target.

Edit:
You asked why do they mention:

  The source database and the target database are the same

I think that means "ELT is easier to do when the source and target database engines are the same, because you frequently have them talk directly".

For example, ELT over Oracle DB links (Oracle to Oracle) or SQL Server Linked Servers (SQL Server to SQL Server) is much easier than setting up heterogeneous links with something like GoldenGate.

Tools like Presto can make this easier though, as you'll see people do:

insert into sqlserver.table
select * from oracledb.table


Dynamic Partition Elimination when using "OR " or "IN" Say I have a query like this:

SET @ContainerId = 5

SELECT  *
FROM    Container.Container_Partitioned
WHERE   containerId = @ContainerId

When I run this, I get a query plan with Actual Partition Count equal to 1.  (Good)

But if I run this:

SET @ContainerId1 = 5
SET @ContainerId2 = 30000000

SELECT  *
FROM    Container.Container_Partitioned
WHERE   containerId = @ContainerId1 OR ContainerId = @ContainerId2

I get an Actual Partition Count equal to all my partitions. (Bad)

I have two questions:

Why can the optimizer figure it out with one parameter, but not with two?
I guess it is the "OR" operator.  But why?
How can I get it to correctly see that only two partitions are need without using  OPTION(RECOMPILE) or something similar?
I can recompile each time if I have to, but I would rather not for obvious reasons.

Below a couple of techniques that should coerce partition elimination while providing the same functionality as the IN or OR methods.  These both use a JOIN instead, one with a row constructor and the other a table variable.  The table variable (which could be passed as a table-valued parameter) allows a variable number of container values without changing the query itself.

SELECT  *
FROM    Container.Container_Partitioned
JOIN ( VALUES ( @ContainerId1), ( @ContainerId2) ) AS containers ( ContainerId )
    ON containers.ContainerId = Container_Partitioned.ContainerId;
GO

DECLARE @Containers TABLE(ContainerId int PRIMARY KEY);
INSERT INTO @Containers VALUES(5);
INSERT INTO @Containers VALUES(30000000);

SELECT  *
FROM    Container.Container_Partitioned
JOIN @Containers AS containers ON Containers.ContainerId = Container_Partitioned.ContainerId;
GO

As to why, SQL Server generates a plan that can be reused regardless of the values that may be specified.  With an index on ContainerId, I get a seek with a partition range as the first seek predicate and the supplied container values as the second.  Remember that the partition number is treated much like the first column of an index an index in SQL 2008 and later.  The partition range seek must allow for all partitions in order to be able to return any possible container id values with that plan.

Note that even though the unneeded partitions are touched, the number of logical reads for each is just the number of index levels since no rows will be found for those.  Not ideal, especially if you have a lot of partitions, but not too expensive since those pages will likely remain memory resident if the query is executed often enough.

Interestingly, it seems SQL Server can eliminate partitions better in this case when the partitioning column is not the first column of an index.  Without an index on ContainerId, the plan I get scans with a nested loop operator using a RangePartitionNew function to only access the partition with the specified container ids.  One would think SQL Server could generate a similar plan for the index seek.  There seems to be room for improvement but keep in mind that partitioning adds another layer of optimizer on top of the already complex job to optimizer has to do.


Quorum configuration warning for cross subnet AAG failover We have setup a 3 node AAG in our staging environment as a POC. The 3rd node has been configured in a different subnet and also as an asychronous replica. The Windows Server version is 2012 R2 and the SQL Server version is 2014 SP2. After I changed the availability mode to synchronous commit and tried to perform a manual failover across subnets, this is what I get. I had removed the node weight of the 3rd node. Am I  missing something here? Please advice.

I had removed the node weight of the 3rd node. Am I missing something here?

You're receiving the informational message because you've removed the possible vote (node weight of 0) of the replica. Since it won't ever have a vote, it also won't ever be able to stand on its own should any issues arise with the cluster, since this effectively removed the dynamic quorum aspect of clustering when this node owns anything.

This truly may be what you want, for example as a DR node whose availability you don't want impacting the production nodes. In that case, as long as you understand the situations you're protecting against and meeting those then I don't see a problem setting it like this (which is effectively that you know better than the cluster).


MongoDB 3.2.10 Index rebuilding did not complete: 11000 E11000 duplicate key error collection MongoDB 3.2.10

Getting the following error when adding a replica set as WiredTiger. This is sharded cluster ( 3 shards nodes ).

2 Shards replica sets created using wiredTiger storage so only 1 shard is having a problem when creating the replica set.

2016-11-11T00:51:55.005+0000 I -     [initandlisten]   Index Build: 39291000/39372845 99%
2016-11-11T00:51:58.032+0000 I -     [initandlisten]   Index Build: 39345000/39372845 99%
2016-11-11T00:52:04.696+0000 E INDEX [initandlisten] Index rebuilding did not complete: 11000 E11000 duplicate key error collection: XYZ.ALBUM index: _id_                                                                         dup key: { : 59000012923579 }
2016-11-11T00:52:04.696+0000 I INDEX [initandlisten] note: restart the server with --noIndexBuildRetry to skip index rebuilds
2016-11-11T00:52:04.696+0000 I -     [initandlisten] Fatal Assertion 26100
2016-11-11T00:52:04.696+0000 I -     [initandlisten]

I did not have issue when I was using mmapv1 replica set but now I have dropped old replica set.

How to fix this issue?

I have deleted all data from dbpath and again restarted replica set sync and it looks it is going fine now. I was trying to restart a failed replica set sync and it looks like MongoDB was not able to handle it, once I cleaned up failed replica set data and restart mongo and looks like it is passed point where it was failing before.

Basically,I have removed host from relica set configuration, cleanup dbpath, restarted mongo and again added host in replica set configuration.


How do MariaDB and MySQL differ in character set handling? In MySQL/MariaDB, I set the connection character set to UTF-8:

SET NAMES 'utf8mb4';
SET CHARACTER SET 'utf8mb4';

There are some tables with a latin1_swedish_ci collation and latin1 encoded data in them. SELECTing from them gives me correct UTF-8 data.

When I once incorporated SET NAMES, SET CHARACTER SET in my program startup code, I assumed that I could give UTF-8 encoded data in INSERT strings to the MySQL server and it would convert it according to the table/column collation. That assumption came from me testing locally with MariaDB, where it worked. Then on the production site with MySQL: The UTF-8 data would not be converted to latin1 but saved as UTF-8 in Latin1 column. To fix that, I always encode values explicitly to latin1 when INSERTing into latin1 table.

Now, I revisited some code that did not SET NAMES, SET CHARACTER SET. I changed it to SET it. Now the MariaDB/MySQL difference strikes again. What is the difference between MariaDB and MySQL character set handling?

MySQL and MariaDB handle character sets the same.  However...  Different versions of MySQL have different defaults.  Ditto for MariaDB.

SET NAMES declares what encoding is in the client.  This is independent of the CHARACTER SET on a column or table.  (There are, of course, characters that are not representable in latin1, so there can be conversion problems.)  As you say, the conversion occurs on INSERT or SELECT.

To further pursue this, please tell us what client you are using, what connection parameters were used, any SETs performed, and what happened.

For diagnosing what is stored, use SELECT col, HEX(col)....  More details.


Pass variable as an input argument to a function inside another function in PostgreSQL I am new to PostgresQL and having some trouble in feeding a function with a variable from an outer function. (maybe it is not possible...)
Inside a function I am trying to assign a value from calling another function like this:
left_bipolar_n:=public.convert_to_decimal(left_bipolar);
When I call the function, If I use something like '122' as an argument instead of "left_bipolar" it works just fine but just as it is I get:
Error: invalid input syntax for type numeric: "  "
I should say that Function convert_to_decimal expects text
Complete code:
-- FUNCTION: public.AmateurExercise(numeric)
--drop FUNCTION public."AmateurExercise"(numeric);

CREATE OR REPLACE FUNCTION public."AmateurExercise"(
    n numeric)
    RETURNS numeric
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
declare
bipolar text;
tamanho integer;
left_bipolar text;
right_bipolar text;
left_bipolar_n numeric;
right_bipolar_n numeric;

begin
bipolar:=public.convert_to_bipolar8(n);
tamanho=length(bipolar);
if tamanho/2 =floor (tamanho/2) then
begin
left_bipolar=left(bipolar,tamanho/2);
right_bipolar=right(bipolar, tamanho/2);
end;
else
left_bipolar=left(bipolar,(tamanho-1)/2);
right_bipolar=right(bipolar, ((tamanho-1)/2)+1);
end if;

left_bipolar_n:=public.convert_to_decimal(left_bipolar);
return left_bipolar_n;

end;
$BODY$;

ALTER FUNCTION public."AmateurExercise"(numeric)
    OWNER TO postgres;

answer from a jjanes comment:
"The error indicates that for some reason left_bipolar contains a space just before the call. I guess convert_to_bipolar8 must put the space there. But we can't see the code for it, not what input you called the outer function with"


SSIS Package Memory Issue We have a package that performs data extraction from multiple external systems.  It has been running with no issues for a long time.  A few days ago, it failed with the following error:

The system reports 9 percent memory load. There are 1099463696384 bytes of physical memory with 991419998208 bytes free. There are 2147352576 bytes of virtual memory with 174698496 bytes free. The paging file has 1153150787584 bytes with 1033803776000 bytes free.

I have read a lot of suggestions on the internet regarding setting max server memory but as it has been running for all this time with the configuration, I am not sure why that would be a factor now.  I did change it to a lower number but set it too low and SQL Server would start up.  Had to go thru single user mode to reset it back.  The server has 1TB RAM.

I also saw something about setting the buffers but I am unable to find where to set that.

The package runs fine within Visual Studio but fails when run thru SQL Server Agent.  Also, although it is a 64bit server, everything has been configured to run as 32bit.

Disclaimer: I am programmer and not a DBA so I am just a novice trying to figure this out cold so all suggestions are welcome but please keep that in mind when submitting your responses. thanks!

There may be several reasons:

Check if the Page file for the system is configured properly, you might get this error if there is no page file or if page file is very small
Change the lookup cache mode from Full to Partial or no cache mode.
Migrate to 64-Bit SSIS if you are in 32-BIT. Remember you have to migrate all the drivers and providers used in connections. If you are in 64 BIT system and getting above errors while executing packages from BIDS then check if you have set package to run in 64bit run time ( Run64bitruntime = True in  Integration services project properties page).
Divide the package in to multiple child packages (Execute package task –> ExecuteOutOfProcess =True)
Tweak DefaultBufferMaxRows and DefaultBufferMaxSize size.

I hope that it helps.


Informix - Getting all descendants of a parent I have a table with two columns, Parent and Child. Need to get the list of all descendants associated with the parent records.

Source Table:

+----+-----------+
| Parent | Child |
+----+-----------+
|  a     |     b |
|  b     |     c |
|  c     |     d |
|  d     |     e |
|  e     |     f |
|  f     |     x |
+----+-----------+

Expected Result:

+----+-----------+
| Parent | Child |
+----+-----------+
|  a     |     b |  // As b is the child of a, all the descendants of b
|  a     |     c |  // are also descendants of a.
|  a     |     d |
|  a     |     e |
|  a     |     f |
|  a     |     x |
|  b     |     c |  // As c is the child of b, all the descendants of c
|  b     |     d |  // are also descendants of b.
|  b     |     e |
|  b     |     f |
|  b     |     x |
|  c     |     d |
|  c     |     e |
|  c     |     f |
|  c     |     x |
|  d     |     e |
|  d     |     f |
|  d     |     x |
|  e     |     f |
|  e     |     x |
|  f     |     x |
+----+-----------+

Informix 11.50 doesn't have recursive CTEs (common table expressions). CONNECT BY can help about recursion levels, paths etc., but I can not get expected result using CONNECT BY. Any idea?

Using the CONNECT BY syntax (similar to Oracle's) which allows recursive queries:

select connect_by_root parent as ancestor,
       child
from x                            -- table name
connect by prior child = parent
order siblings by parent ;

Tested in rextester.com


EAV, multiple left join query optimisation Any assitance would be greatly welcomed. Pardon my ignorance however I am wondering if anyone else has had to encounter bad EAV performance issues. I have a particular query which takes a long time. What is the best way to increase the speed on this?

 SELECT * FROM
(
    SELECT f.*, p.`timestamp`
    FROM `tObjectValues` ov
    INNER JOIN `tObjectProperties` op ON ov.`propertyId` = op.`id`
    INNER JOIN `tObjects` o ON op.`objectId` = o.`id`
    INNER JOIN `files` f ON SUBSTRING(ov.`value`, CHAR_LENGTH('cache://content//')) = f.`filename`
    LEFT JOIN `publishFiles` pf ON f.`id` = pf.`fileId`
    LEFT JOIN `publishes` p ON p.`id` = pf.`publishId` AND o.`appId` = p.`appId` AND p.`type` = 'l'
    WHERE o.`status` = 1 AND op.`status` = 1 AND ov.`status` = 1 AND ov.`value` LIKE 'cache://content/%' AND o.`appId` = 2
    ORDER BY p.`timestamp` DESC
    LIMIT 0, 10000000
) s
GROUP BY s.`id`

I have run the explain function to delve further into the issue however i was wondering if anyone who has had similar problem could help in the optimisation.

Please explain the order by - limit - group by.  It sounds kludgy and possibly illegal in future versions.
Store the tail of the filename so you can avoid SUBSTRING (etc) in critical places like WHERE clauses.
Show us SHOW CREATE TABLE.


Find if and when a Query was run? I am trying to get to the root of a problem and need to see if and when a specific Delete Query was run on my MS SQL Server.
The command would have come from my .NET website.
My Server is Windows 2012 R2 running SQL 2014 Express.
The query would have run at some point in the last 3 weeks or so.

In a database where SQL Server Audit was not previously configured you could try to Read SQL Server Transaction Log Backups to Find When Transactions Occurred with the use of the undocumented function fn_dump_dblog.
For future situations where you need to audit a database I advise you to Create a server audit and database audit specification.

Unfortunately, based on your comment

all I have is whatever is built-in by default

I'd say you have no means to find the info you seek and the reason is that, by default, a new database you create on a SQL Server Express uses the Simple Recovery Model, which doesn't allow taking log backups. Therefore, the function I mentioned will not help.


How to investegate MySQL crash on windows? I have MySQL 5.6.19 running on Windows Server 2008 R2.

I have 3 servers A, B, C
Server A is a stand alone server. This server had data that can't be placed on SERVER B (ie. users information.)

Server B is another stand alone server which has data that can't be saved on server A. Server B is a master of server C.
Server C is another stand alone reporting server which is a SLAVE of server B.

After using adding FEDERATED table to server B. I re-sync server C "the the slave" to Server B "it's master."

But now server C will not start unless I add --skip-slave-start option.

In addition, whenever I issue START SLAVE; command on server C, this server stops working immediately (crashed.)

Something is going on with the replication. I must say that the replication was working before I added the FEDERATED table with no issues. (IMPORTANT NOTE: I did not issue any create table engine=FEDERATED while the replication is running. I first created all the FEDERATED tables on the master (SERVER B) and then I created a server backup and restored it into the slave (SERVER C).

In an effort to figure out what is causing this crash. I have added core-file to the file my.ini to get MySQL to generate core.pid file, but nothing was created.

I tried to add core-file-size=1GB but this caused the not to start at all due to unknown variable! (perhaps it is not recognized by Windows.)

What can I do to investigate this silent crash on Windows 2008 R2?

Thank you

I figured out a solution.

I run MySQL via the command line

I disabled this line log-error=.... from the my.ini file.

I stopped MySQL service.
Then I opened a command line and run this

mysqld.exe --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" --console

This showed me that I run into a bug with MySQL


Disabling MariaDB cluster multi master mode Is it possible to configure replication with one master node and 2 slaves in MariaDB like replication works in mongodb? I noticed that the default is multi master mode. Thanks in advance.

Yes, of course. Standard replication configuration is master and single or multiple slaves. If you use MariaDB Galera cluster you can point your applications to a single node and the other two serves as slaves.


Deadlock during Slony uninstall and concurrent access of DB I am using slony1-2.0.4.rc2 and postgresql-8.4.0-0. I faced a deadlock in the slony uninstall process.

I was trying to uninstall node from a cluster, as per slony's documents this requires access exclusive locks on all replicated tables. When the slony process was uninstalling the cluster, at the same time the application inserted a row in one of the replicated tables which I think resulted in a dead lock:

  DETAIL:  Process 22593 waits for AccessExclusiveLock on relation 16392 of database 16388; blocked by process 11808.
  Process 11808 waits for AccessShareLock on relation 18366 of database 16388; blocked by
  process 22593.

Can any body confirm that what I am thinking is correct and suggest a solution for this.

I suspect you are going to have to disable application access during the uninstall process.  The basic problem is that Slony uses large numbers of triggers to maintain the binary logs for shipping.  These triggers are going to be hard to delete while there is concurrent access.


Postgres database level encryption I want to have the whole database to be encrypted, instead of encrypting the data before storing into database.

Is it possible to encrypt the whole postgres database and what best method to use?

Thanks :)

PostgreSQL doesn't currently support DB-level encryption. (Correct as of 9.4, at least).

You can use an encrypted file system, though the performance impact can be pretty serious for write-heavy systems, especially those doing random I/O. Your options depend on the operating system - Microsoft BitLocker, Linux's dm-crypt/LUKS/cryptsetup, OS X's FileVault 2, etc.

Alternately, look at PgCrypto for field-level encryption, but beware of key-disclosure issues in logging and pg_stat_activity.

See also:

Secure postgresql database encryption
How to use aes-encryption in PostgreSQL?
https://stackoverflow.com/q/12614523/398670
https://stackoverflow.com/q/21095026/398670
https://stackoverflow.com/q/8054503/398670


Best way to capture historical changes between levels in a dimensional hierarchy What is the cleanest way to capture changes between levels in a dimensional hierarchy?

I have the dimensional hierarchy [Area] > [Region] > [Location], where Area is the parent to Region and Region is the parent to Location. Location is lowest level of the hierarchy, and is associated with the fact table.

The business rules are: Locations are able to change the Region they are assigned to based on business need. Furthermore, Regions are able to change the Area they are assigned to.

We want to capture those changes so that when we do historical analysis, we can compare business metrics before and after a Location changed its Region, and a before and after a Region changed its Area.

What is the best way to do this?

The four ways that I have come up with all seem to have major drawbacks:

Using a bridge table between hierarchy levels requires that you use time bounds (effective start/end dates) to correctly identify which parent a child belongs to as you walk up the hierarchy. That makes queries prone to error, especially with hierarchies with many levels.
Versioning a child record whenever its relationship to its parent changes (e.g., versioning Region A when it moves from Area A to Area B) and then cascading that change all the way down the hierarchy (e.g., creating new versions of Locations tied to Region A so that they are tied to the new Region A).
Updating the child record's reference to the parent record, making you use time bounds to reassemble the correct history (prone to error).
Always use time bounds when querying up the hierarchy to make sure Location A is associated with the correct Region (likewise, Region A is associated with the correct Area) for the moment of the fact being analyzed.

None of these seems clean to me.

Is there a better way?

Thanks a lot

If you want to capture historical changes on the three tables you need change hierarchies tables (i.e. [Area], [Region]) into sub-dimensions of the dimension table (i.e. [Location]) by adding an effective_date and expire_date (in the three tables )and you can track changes in dimension and sub-dimension attributes in order to report historical data using Slowly Changing Dimensions (SCD) techniques.


SQL Server : How to disable trigger for an update only for your current session? I am working on SQL Server 2008 R2.

I have a table benefit which has a AFTER INSERT, UPDATE trigger named tiu_benefit.

I want to write an UPDATE statement for this table to update 1 row but I dont want its trigger to fire. I know I can disable trigger before UPDATE and then enable the trigger after UPDATE :

DISABLE TRIGGER tiu_benefit ON benefit;
GO
UPDATE benefit SET editor = 'srh' where benefit_id = 9876
GO
ENABLE TRIGGER tiu_benefit ON benefit;
GO

But this disable and enabling trigger will affect all users logged in currently. So there is a possibility that another user run an UPDATE/INSERT while the trigger is disabled by my script which is not good. Thats why I only want to disable and enabling trigger for my current session. Is it possible? If yes please tell how.

Thanks

To solve your problem, we have to take a programmatic approach to the problem. There are two routes you can go here. The reason for needing these approaches is because you cannot disable a trigger for a particular statement, it can only be disabled for the entirety of the table.

Option 1: Context_Info()

Samuel Vanga on MS SQL Tips had a great example:

USE AdventureWorks;
GO
-- creating the table in AdventureWorks database
IF OBJECT_ID('dbo.Table1') IS NOT NULL
DROP TABLE dbo.Table1
GO
CREATE TABLE dbo.Table1(ID INT)
GO
-- Creating a trigger
CREATE TRIGGER TR_Test ON dbo.Table1 FOR INSERT,UPDATE,DELETE
AS
DECLARE @Cinfo VARBINARY(128)
SELECT @Cinfo = Context_Info()
IF @Cinfo = 0x55555
RETURN
PRINT 'Trigger Executed'
-- Actual code goes here
-- For simplicity, I did not include any code
GO

Now when Samuel does not want want the trigger to execute, they use this:

SET Context_Info 0x55555
INSERT dbo.Table1 VALUES(100)

Context_Info uses the following system views to grab information regarding the current session:

sys.dm_exec_requests
sys.dm_exec_sessions
sys.sysprocesses

The ideology here is that the binary string you are setting is exposed only to the current session, so when the trigger executes during your session, it will see the scope and variable setting of the Context_info function and it will jump to the escape portion of the trigger instead.

Option 2: Temp Table

Itzik Ben-Gan has a great solution in his book "Inside Microsoft SQL Server 2008 T-SQL Programming: T-SQL Programming" which is also in his later book T-SQL Querying. The primary problem with this over the context_info function is the minor TempDB overhead.

To spoil the surprise but not ruin the plot of the books (I felt they are worth purchasing and reading), you will alter your trigger.

Your trigger should perform a check for a temporary table. If the temporary table exists, the trigger should know to end and not perform the actions.

In the update statement you want to perform, create the temporary table first. It will be seen in the same transaction as the trigger and it will cause the trigger to ignore your statement.

Example of trigger:

CREATE TRIGGER TRIGGERNAME ON TABLENAME for INSERT AS

IF OBJECT_ID('tempdb..#FAKETEMPTABLE') IS NOT NULL RETURN;
GO

Example of beginning statement when you don't want the trigger to run:

CREATE TABLE #FAKETEMPTABLE(col1 SMALLINT);

Putting it altogether for your example:

ALTER TRIGGER tiu_benefit ON benefit FOR
...
AS
...
IF OBJECT_ID('tempdb..#FAKETEMPTABLE') IS NOT NULL RETURN;
--... rest of code here
GO

CREATE TABLE #FAKETEMPTABLE(col1 SMALLINT);
UPDATE benefit SET editor = 'srh' where benefit_id = 9876;
GO


phpMyAdmin how to run an output query? run a batch? In phpMyAdmin the code below just outputs a list of sql statements for each table:

SET @DATABASE_NAME = 'my_db';
SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

How would I actually execute this to change all the tables? I've seen people say "batch" but I'm not sure if that is what I'm looking for.

It is actually easiest to simply copy&paste the SQL statements.  Don't bother with trying to find and "eval".

It is even easier in the mysql commandline tool.

Here are my tips on gotchas that sometimes occur in the conversion from MyISAM to InnoDB:  http://mysql.rjweb.org/doc.php/myisam2innodb


Can anyone describe the step by step process of recovery , backup and restore in sql server Can anyone  describe the step by step process of recovery via backup and restore in SQL Server 2008.

If possible please include some examples.

Follow the links:

1.Backup Under the Full Recovery Model

http://msdn.microsoft.com/en-us/library/ms190217%28v=SQL.100%29.aspx

2.BACKUP and RESTORE Commands

http://msdn.microsoft.com/en-us/library/ff848768

3.Back Up and Restore of SQL Server Databases

http://msdn.microsoft.com/en-us/library/ms187048%28v=sql.100%29.aspx

Hope this will help.


Equivalent to PostgreSQL's Dollar-quoted String Constants for SQL Server Is there an equivalent of PostgreSQL's Dollar-quoted String Constants, on SQL Server?

I would like to enter HTML string literals that would potentially contain single or double quotes in them.

Example:

UPDATE table_name
SET    column_name = $$
  Here's a string that contains "double quotes".
$$
WHERE  condition = true

Edit:
The reason I'm asking this is because I would like to update some rather big HTML "web parts" that are stored in the database (yuck!). Because at work we use that kind of CMS (which I won't give you the name). So I don't want to have to escape my single quotes every time. That's why I'm asking about that kind of feature. If I understand correctly, QUOTENAME would still require me to double my single quotes.

This is a PostgreSQL extension of the spec. There is nothing like it in SQL Server. The idea is that you can quote everything that doesn't have the $$[token]$$. If the optional token is absent it looks like $$. Thus the name, Dollar-quoted String Constants

  Dollar quoting is not part of the SQL standard, but it is often a more convenient way to write complicated string literals than the standard-compliant single quote syntax. It is particularly useful when representing string constants inside other constants, as is often needed in procedural function definitions. With single-quote syntax, each backslash in the above example would have to be written as four backslashes, which would be reduced to two backslashes in parsing the original string constant, and then to one when the inner string constant is re-parsed during function execution.

So you can pass something like this,

$$ SELECT foo FROM "mySchema"."myTable" WHERE bar='foo''bar'; $$

or, even this.

$OUTER$ SELECT foo FROM "mySchema"."myTable" WHERE bar=$$foo'bar$$; $OUTER$

And we do that frequently with modules like tablefunc

As a workaround, in SQL Server, you just have to be escape the single quotes and double escape the single-quotes in the single quotes. This is the standard method of literal-quoting.

' SELECT foo FROM "mySchema"."myTable" WHERE bar=''foo''''bar''; '

The '''' is what we're trying to avoid here. That's fugly.


How to reference column of DAX variable? I have a DAX variable that contains a table. How can I reference a particular column in that variable?

For example, in the below command, the EVALUATE returns an error. But it works if I replace table1 with FactInternetSales (which is the name of the table which contains that column)

define var table1=FactResellerSales
EVALUATE ROW("a",COUNTBLANK(table1[SalesAmount]))

In DAX you can't use a variable name as with tables that are defined in the model.
What you can do is to use the original table names using the table variable as a filter.
For instance like in the following code
DEFINE
    VAR tv1 =
        FILTER( Tab1, Tab1[F1] = "A" )
EVALUATE
ROW( "a", CALCULATE( COUNTBLANK( Tab1[Value] ), tv1 ) )


Running a speculative transaction in SQL Server I have a long-running transaction that I know is always going to be rolled back.

Is there a way to run this transaction so that it doesn't acquire any locks in the database? The transaction inserts and updates rows, but these changes will never persist in the database and in general should not be visible to anybody outside the transaction.

Here is a more specific example.

Connection 1:

BEGIN TRAN
UPDATE foobar SET something = 1;
COMMIT

Connection 2:

BEGIN TRAN
UPDATE foobar SET something = 2;
ROLLBACK

I want to prevent connection 2 from blocking connection 1. I can add SQL statements in the transaction, but I cannot easily change the queries.

No, there isn't a built-in way to run a "speculative transaction" in SQL Server.

If multiple transactions make incompatible changes, there will be blocking. You would need separate copies of the data to avoid this.


Is there a difference between running the source command from the mysql command line client and '<' from outside it? if so, when should I use each? I can run the following commands:

C:\myfolder>mysql -uuser -ppassword

Then from within the client do:

mysql>source C:\path\to\file.sql

Or I can do:

C:\myfolder>mysql -uuser -ppassword  < C:\path\to\file.sql

Is there a difference between these two commands? if so, what is it and when should I use each?

They are the same, but you can use only one depending in which environment you are.

for example when you are in DOS/ linux prompt you can only run mysql.exe

in Mysql command  mode you only can use source.

so there is no need to change the environments to import the sql file.


sql or hql query to find all parents of a particular record I need a sql or hql query to find all parents of a particular record, that should work in oracle as well as mysql, for eg if I have the id: 201, the parents would be: 188, 187, 1.
any help would be greatly appreciated, thanks

On Oct 24, 2011, I wrote some Stored Functions to perform Ancestor and Descendant traversals (Find highest level of a hierarchical field: with vs without CTEs ). From my post, you would use GetAncestry. From your data,  the code in GetAncestry will traverse until it sees -1 as a parent id. No changes in the algorithm are needed except to change parent_id to parent.

For the sake of example, let's suppose the table is called mydb.mytable

GetAncestry Code

USE mydb
DELIMITER $$
DROP FUNCTION IF EXISTS `GetAncestry` $$
CREATE FUNCTION `GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
    DECLARE rv VARCHAR(1024);
    DECLARE cm CHAR(1);
    DECLARE ch INT;

    SET rv = '';
    SET cm = '';
    SET ch = GivenID;
    WHILE ch > 0 DO
        SELECT IFNULL(parent,-1) INTO ch FROM
        (SELECT parent FROM mytable WHERE id = ch) A;
        IF ch > 0 THEN
            SET rv = CONCAT(rv,cm,ch);
            SET cm = ',';
        END IF;
    END WHILE;
    RETURN rv;
END $$
DELIMITER ;

Once you create this stored procedure, you would call the function as follows:

USE mydb
SELECT GetAncestry(201);

Let's test that out

Sample Data Based On Your Question

drop database if exists mydb;
create database mydb;
use mydb
create table mytable
(id int not null,
name varchar(20),
parent int default -1,
primary key (id));
insert into mytable values
(1,'ROOT',-1),(162,'ROOT',1),(163,'ROOT',1),(164,'ROOT',1),
(165,'ROOT',1),(166,'ROOT',1),(167,'ROOT',1),(168,'ROOT',1),
(169,'ROOT',1),(170,'ROOT',1),(171,'ROOT',1),(172,'ROOT',1),
(173,'ROOT',1),(174,'ROOT',1),(175,'ROOT',1),(176,'ROOT',1),
(177,'ROOT',1),(178,'ROOT',1),(179,'ROOT',1),(180,'ROOT',1),
(181,'ROOT',1),(182,'ROOT',1),(183,'ROOT',1),(184,'ROOT',1),
(185,'ROOT',184),(186,'ROOT',1),(187,'ROOT',1),(188,'ROOT',187),
(201,'ROOT',188),(202,'ROOT',201),(203,'ROOT',202);

Load Sample Data

mysql> drop database if exists mydb;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create database mydb;
Query OK, 1 row affected (0.00 sec)

mysql> use mydb
Database changed
mysql> create table mytable
    -> (id int not null,
    -> name varchar(20),
    -> parent int default -1,
    -> primary key (id));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into mytable values
    -> (1,'ROOT',-1),(162,'ROOT',1),(163,'ROOT',1),(164,'ROOT',1),
    -> (165,'ROOT',1),(166,'ROOT',1),(167,'ROOT',1),(168,'ROOT',1),
    -> (169,'ROOT',1),(170,'ROOT',1),(171,'ROOT',1),(172,'ROOT',1),
    -> (173,'ROOT',1),(174,'ROOT',1),(175,'ROOT',1),(176,'ROOT',1),
    -> (177,'ROOT',1),(178,'ROOT',1),(179,'ROOT',1),(180,'ROOT',1),
    -> (181,'ROOT',1),(182,'ROOT',1),(183,'ROOT',1),(184,'ROOT',1),
    -> (185,'ROOT',184),(186,'ROOT',1),(187,'ROOT',1),(188,'ROOT',187),
    -> (201,'ROOT',188),(202,'ROOT',201),(203,'ROOT',202);
Query OK, 31 rows affected (0.01 sec)
Records: 31  Duplicates: 0  Warnings: 0

mysql>

Create the Stored Function

mysql> USE mydb
Database changed
mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS `GetAncestry` $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE FUNCTION `GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
    -> DETERMINISTIC
    -> BEGIN
    ->     DECLARE rv VARCHAR(1024);
    ->     DECLARE cm CHAR(1);
    ->     DECLARE ch INT;
    ->
    ->     SET rv = '';
    ->     SET cm = '';
    ->     SET ch = GivenID;
    ->     WHILE ch > 0 DO
    ->         SELECT IFNULL(parent,-1) INTO ch FROM
    ->         (SELECT parent FROM mytable WHERE id = ch) A;
    ->         IF ch > 0 THEN
    ->             SET rv = CONCAT(rv,cm,ch);
    ->             SET cm = ',';
    ->         END IF;
    ->     END WHILE;
    ->     RETURN rv;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

Call GetAncestry

mysql> SELECT GetAncestry(201);
+------------------+
| GetAncestry(201) |
+------------------+
| 188,187,1        |
+------------------+
1 row in set (0.00 sec)

mysql>

Only one thing left to do...

GIVE IT A TRY !!!

I have discussed using GetAncestry in the DBA StackExchange many times

Please note that this is a MySQL-specific answer. Please read PL/SQL Documentation if you wish to implement the algorithm in a PL/SQL block or a Stored Procedure.


How to cast a value to boolean I need to order a query by the value of a field. If it is empty or not

I need to cast the value of the column order_by to a boolean and the column type is varchar

All rows where order_by is empty or null should be ordered last in the query

SELECT *
FROM table
ORDER BY CAST(order_by, 'boolean')

Here is a concise way using IFNULL() function

SELECT *
FROM TABLE
ORDER BY IFNULL(order_by,'')='';

or adding the IF() function

SELECT *
FROM TABLE
ORDER BY IF(IFNULL(order_by,'')='',1,0);

UPDATE 2017-03-22 14:43 EDT

ypercubeᵀᴹ has one more suggestion:

SELECT *
FROM TABLE
ORDER BY (order_by > '') DESC;


INFORMATION_SCHEMA  Selection Error I'm trying to select data from information_schema but I'm getting the following error. How can I fix this?

mysql> SELECT * FROM information_schema.tables ;
ERROR 1018 (HY000): Can't read dir of '.' (errno: 13)

I find that error particularly disturbing because MySQL introduced INFORMATION_SCHEMA with the introduction of MySQL 5.0. @gbn's answer shows that this bug

goes back to MySQL 5.0.16
was reported on ANY operating having this
was based on the error happening in the IBM AIX 5.3 ML2 Operating System
was closed 2006-01-26

MySQL's definition of a database is simply a subfolder under datadir.

Now, here is why I find the error particularly disturbing: The INFORMATION_SCHEMA database is not supposed to be a manifested folder under datadir.

For example

datadir is /var/lib/mysql
You have two databases: db1 and db2

Go to the OS and run the following:

cd /var/lib/mysql
ls -l

You will see several folders:

mysql
db1
db2
test (since MySQL default installs a test database)
. (current dir)
.. (parent dir)

In mysql, when you do SHOW DATABASES;, you should not see . and ... The source code would have made sure of that. Now, where is INFORMATION_SCHEMA? Guess what? All tables in the INFORMATION_SCHEMA are temp tables and use the memory storage engine. Also note that you do not a see a folder called INFORMATION_SCHEMA. Now, check out the definition of INFORMATON_SCHEMA.TABLES:

mysql> use information_schema
Database changed
mysql> show create table tables\G
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.05 sec)

I lay full blame for this error on the source code because just as mysql bypasses . and .. as special case folders that are not to be considered databases, INFORMATION_SCHEMA is supposed to be considered a s special case folder as well, a special case folder that does not have a manifested file under the datadir.

I would highly recommend just upgrading MySQL to the latest version because, as @gbn found, there was a bug report but it is marked as closed. This could only happen

if you are still running a very old version of mysql
this situation was not properly handled in the source code for the Ubuntu OS

@gbn gets a +1 from me for finding the bug report which helped me look a little deeper


increment row value does not work properly with where condition I have created sql fiddle as below
it is working fine if i remove where condition(WHERE rg.groupid= 1)

output without where condition

| ID | RANK | MYORDER | PREVIOUSRANK |
|----|------|---------|--------------|
|  3 |    7 |       1 |            7 |
|  2 |    3 |       2 |            3 |
|  4 |    3 |       2 |            3 |
|  1 |    2 |       3 |            2 |

i want my result order by(ORDER BY mro.rank DESC) but it does not return desired result

SQL Fiddle

CREATE TABLE rankGroup
    (
     id int auto_increment primary key,
     groupid int
    );

INSERT INTO rankGroup
(groupid)
VALUES
(1),(2),
(1),(1);

CREATE TABLE myRankorder
    (
     id int auto_increment primary key,
    groupid int,
     rank int
    );

INSERT INTO myRankorder
(groupid, rank)
VALUES
(1,2),(2,3),
(3,7),(4,3);

Query 1:

SELECT mro.id, mro.rank,
         IF (@prevrank = mro.rank, @rownum, @rownum := @rownum + 1) AS `myorder`,
         @prevrank := mro.rank AS `previousRank`
    FROM `myRankorder` mro
    INNER JOIN `rankGroup` rg
        ON rg.`id` = mro.`groupid`
    cross JOIN
        (SELECT @rownum := 0, @prevrank := 0) const
    WHERE rg.`groupid` = 1
    ORDER BY mro.rank DESC

Results:

| ID | RANK | MYORDER | PREVIOUSRANK |
|----|------|---------|--------------|
|  3 |    7 |       2 |            7 |
|  4 |    3 |       3 |            3 |
|  1 |    2 |       1 |            2 |

Desired Result

| ID | RANK | MYORDER | PREVIOUSRANK |
|----|------|---------|--------------|
|  3 |    7 |       1 |            7 |
|  4 |    3 |       2 |            3 |
|  1 |    2 |       3 |            2 |

Try it this way

SELECT id, rank,
       @rownum   := IF(rank = @prevrank, @rownum, @rownum + 1) myorder,
       @prevrank := rank previousRank
  FROM
(
  SELECT mro.id, mro.rank
    FROM myRankorder mro JOIN rankGroup rg
      ON rg.id = mro.groupid
   WHERE rg.groupid = 1
   ORDER BY mro.rank DESC
) q CROSS JOIN
(
  SELECT @rownum := 0, @prevrank := NULL
) const

Output:

| ID | RANK | MYORDER | PREVIOUSRANK |
|----|------|---------|--------------|
|  3 |    7 |       1 |            7 |
|  4 |    3 |       2 |            3 |
|  1 |    2 |       3 |            2 |

Here is SQLFiddle demo