Database Mirroring - Will forcing the service only lose non-committed data? I have a database mirroring session in high safety mode between Server1 (Primary) and Server2 (Secondary)

According to this, synchronous data mirroring means

  every transaction committed on the principal database is also
  committed on the mirror server, guaranteeing protection of the data.
  This is achieved by waiting to commit a transaction on the principal
  database, until the principal server receives a message from the
  mirror server stating that it has hardened the transaction's log to
  disk. Note the wait for this message increases the latency of the
  transaction

With this in mind, if I lose Server1 completely (and irrecoverably) and force service onto the secondary using

ALTER DATABASE [MyDatabase] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Would I only lose the transactions that were in-flight on Server1?

Correct, once the Primary loses connectivity with the Secondary (closes), all in-flight transactions will be rolled-back on the Primary and never make it to the secondary.  The committed transactions are guaranteed to be written to disk on the mirror server in that mode.

Here is an excellent article with more detail on this subject -
https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-130-in-flight-transactions-continue-after-a-failover/


postgresql: there is no unique constraint matching given keys for referenced table I don't know how to resolve my situation:

create Table VIPUsers(
  id int primary key,
  parent int references Parent(id)
);

create Table Parent(
  id int not null,
  childType int not null,/*indicates ChildA or ChildB*/
  unique (id,childType)
);
create index on Parent(id);

create Table ChildA(
  parent int references Parent(id),
  propertyA int
);

create Table ChildB(
  parent int references Parent(id),
  propertyB date
);

There is M to N relationship between tables VIPUsers and ChildA, ChildB.
Table Parent is a junction table containing information whether VIPUsers uses instances of ChildA and/or instances of ChildB

I need to add rows:

insert into Parent (id,subtype) values (1,1)
insert into Parent (id,subtype) values (1,2)

This gave me an error 'duplicate primary key' so I deleted the primary key and I'd use an index instead, just as it is shown above.

But now I can't create tables VIPUsers, ChildA, ChildB as I get the error

'there is no unique constraint matching given keys for referenced table ...'

I know it is because VIPUsers would not reference a unique row. But that's what I need. I need a join where for each parent I get one or more childTypes. For example:

  VIPUsers ParentId   childType
    1         1          1
    1         1          2

If I make Parent.id a primary key, there will be only one childType for each user. If I remove primary key, DBMS starts complaining about referencing a non unique column.

My goal is to then cross join the above example table with ChildA or ChildB on ParentId to get table like this:

 VIPUsers  ParentId   childType  ChildA.propertyA  ChildB.propertyB
    1         1          1             50                 null
    1         1          2            null              2/2/2017

And based on the childType I'm able to tell what kind of information this row represents.

I don't like putting childType into each Child table because it would allow for nonsense values like ChildA being of type ChildB and vice versa.

What are the usual ways of going about this?

After some clarification (in comments and chat), it seems that:

ChildA and ChildB are subtypes.
a VIPUser has a many-to-many relationship with all Child entities ("VIPUser "uses" Child").
there is a many-to-many relationship between VIPUser and ChildType (stored in Parent), essentially what types of Child a `VIPuser can use ("VIPUser "can use" a child of ChildType").

Then the relationships between entities can be shown in the diagram
(I renamed Parent to VIPCanUse):

               ChildType
VIPUser        /      \
      \       /        \
       \     /          \
      VIPCanUse          \
           \           Child
            \         /    |
             \       /     ------------
              \     /        |        |
               \   /       ChildA   ChildB
              VipUses


mysql UNIQUE KEY multiple columns with one column as NULL I understand the way UNIQUE KEY works in Mysql 5.5+, and understand that NULL allows duplicate entries to be created.

In following schema and I would finally want that family_relation should have only a unique (father, mother, kid) relationship with optionally kid (as kid_id can be null). In the INSERT INTO ON DUPLICATE KEY at the end, you will see that two family_relation entries are created for same father and mother due to kid_id=null. How can I prevent this and enforce mysql to have only one relationship?

DROP TABLE IF EXISTS family_relation;
DROP TABLE IF EXISTS person;

CREATE TABLE person (
    person_id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
) DEFAULT CHARSET=utf8 ENGINE=InnoDB;

CREATE TABLE family_relation (
    pk INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    father_id INTEGER UNSIGNED NOT NULL,
    FOREIGN KEY (father_id) REFERENCES person(person_id) ON DELETE CASCADE,
    mother_id INTEGER UNSIGNED NOT NULL,
    FOREIGN KEY (mother_id) REFERENCES person(person_id) ON DELETE CASCADE,
    kid_id INTEGER UNSIGNED COMMENT 'family may or may not have kids',
    FOREIGN KEY (kid_id) REFERENCES person(person_id) ON DELETE CASCADE,
    UNIQUE KEY one_father_mother_with_possible_kids (father_id, mother_id, kid_id)
) DEFAULT CHARSET=utf8 ENGINE=InnoDB;

INSERT INTO person (name) VALUES ('john the father');
INSERT INTO person (name) VALUES ('ashley the mother');
INSERT INTO person (name) VALUES ('dennis the kid');

INSERT INTO family_relation (father_id, mother_id) VALUES((SELECT person_id from person where name like '%john%'), (SELECT person_id from person where name like '%ashley%'))
ON DUPLICATE KEY UPDATE father_id=VALUES(father_id), mother_id=VALUES(mother_id);

-- This creates a duplicate entry of family_relation due to kid_id = NULL. HOW DO I PREVENT THIS?
INSERT INTO family_relation (father_id, mother_id) VALUES((SELECT person_id from person where name like '%john%'), (SELECT person_id from person where name like '%ashley%'))
ON DUPLICATE KEY UPDATE father_id=VALUES(father_id), mother_id=VALUES(mother_id);
SELECT * from family_relation;

/* Result is as
"pk" "father_id" "mother_id" "kid_id"
"1" "1" "2" \N
"2" "1" "2" \N
*/

here is http://sqlfiddle.com/#!2/834d6/1

Possibilities, you can:

use the BDB-Storage engine in MySQL prior 5.1. which treats nulls as non-distinct to each other. Later versions don't support BDB
introduce a special value for "no kid_id", so you won't have nulls
add a not-null pseudo column, which is filled by triggers and has to be in the constraint instead of the original kid_id
use a before-insert trigger instead of the constraint, which checks if there is a row containing all the 'unqiue' fields (use <=> for also compare nulls) and changes the PK of the to-be-inserted-row to the one of the existing one so it results in a "Duplicate key" error.


forcing Oracle to use hash join for a subquery I have a query that looks like

SELECT *
FROM table0
WHERE id IN (SELECT id FROM table1 JOIN table2)

Oracle is choosing to join table0 with the result of (table1 x table2) using nested loops and takes hours.  I'm trying to figure out whether I can hint it to use HASH instead, but don't understand which hint and where to use.  I tried sticking HASH_SJ and HASH_AJ in various places and it didn't help...

I'd try using a WITH clause in combination with the MATERIALIZE hint to force materialization of the subquery first. Something like this:

WITH x as (select /*+ MATERIALIZE */
           from [your subquery join])
SELECT *
FROM table0, x
WHERE table0.id =x.id


SQL Server Agent cannot log on to "MyServer" I'm struggling to get our SQL Server Agent to run a maintenance plan, which used to work in the past. (This is on SQL Server 2008 R2.)

The original problem was that the SQL Server Agent would not start, because it tried to connect to, let's call it "MyServer," which resulted in what appeared to be a network name resolution error. We then changed the alias from "MyServer" to "." (SSMS > SQL Server Agent > right-click Properties > Connection > Alias local host server). This fixed it and we can now start the agent.

The second and current problem is that when I execute a maintenance plan, then the log for the SQL Agent (not the log for the maintenance plan) shows a similar error, that it cannot log on to the server "MyServer." (See below for the exact error.) I updated the maintenance plan's connection to use the IP directly (in a desperate attempt), and I checked the job's user password, but it did not change anything. I think it fails before it gets to this point.

Why does it still want to use "MyServer?" Is there another place where I should set the alias, or clear a wrong alias?

Side Notes:

This is a test server, so there is not much control over who changed what, which is probably how things broke in the first place.

Current error: [382] Logon to server 'MyServer' failed (ConnLogJobHistory)

Error for the original problem (now fixed): [000] Unable to connect to server 'MyServer'; SQLServerAgent cannot start

I already tried a server rename from "MyServer" to "MyServer.abc.def" (where "abc.def" is the DNS suffix), but the error message still shows a failure to log on to "MyServer" (without the suffix). The plan's data source is ".", so no server name mix-up there. I think the problem occurs before this point.

Here are some thoughts to help investigate.

Have you tried just removing the alias local host information?

What is the value for HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\VERSION.INSTANCE_NAME\SQLServerAgent,

What are MyServer's SQL Server Logs saying?

Did you make sure you don't have any aliases in your hosts file.

C:\Windows\System32\drivers\etc\hosts

# localhost name resolution is handled within DNS itself.
#   127.0.0.1       localhost
#   ::1             localhost

Any named aliases in SQL Server Configuration Manager?


How MySql Join two tables with Checkboxes results stored as "string" data format? Suppose I have 2 tables obtained from a web form submission, with some checkboxes results stored as "string" format as describerd here in "hobby" column https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=aff4e50085be40930e223b00a1ac81f3,
How could I join this two tables and obtain the following result?

sub_id
datimkt

1
Basket,Gas,SUV

2
Basket,Cabrio,Football,hybrid

I've tried to execute the following query, but it only takes the first result from the checkbox result field.
SELECT GROUP_CONCAT(DISTINCT submission_id)sub_id,
GROUP_CONCAT(DISTINCT c1.option_name)datimkt
FROM form_results d
JOIN field_options c1 ON FIND_IN_SET(c1.option_value, d.datimkt)
GROUP BY d.datimkt;
SELECT*FROM result2;

You need to group by the submission_id:
like:
SELECT submission_id sub_id,
GROUP_CONCAT(DISTINCT c1.option_name)datimkt
FROM form_results d
JOIN field_options c1 ON FIND_IN_SET(c1.option_value, d.datimkt)
GROUP BY sub_id;


Only allow access to a table via a function, not directly via queries I have a table t and some functions which access t. For example:

create function list_t() returns setof t as $$
    select * from t;
$$ language sql stable;

Is it possible to define role permissions to only allow the execution of list_t(), while not allowing plain SELECT, UPDATE, DELETE and INSERT queries on t?

I tried the following:

grant execute on all functions in schema public to my_user;
revoke all on t from my_user;

While this indeed disallows SELECT * FROM t, it also disallows SELECT list_t().

You need to create the function as a user that has (select) access to the underlying table. Then define the function with the option security defined which means when the function is called, it operates with the privileges of the function owner, not the caller:

create function list_t() returns setof t
  security definer
as $$
    select * from t;
$$ language sql stable;

Another way of doing this is to create a view, then grant SELECT privileges to the view (not the table):

create view v_t
as
select *
from t;

grant select on v_t to my_user;

Note that a set returning function should be used in the FROM clause like a table:

select *
from list_t();


How to generate current Oracle timestamp in perl? I use DBI. And I want to make some like

INSERT INTO ... VALUES (the_generated_timestamp, ...);

What is the command? How could it be created, the perl ``ocaltimescalar value has a very complex, for human eyes optimized format (f.e.Mon Apr 28 15:58:51 2014). My goal were some like as the unixgettimeofday()` does (giving back the seconds since 1970.1.1), converted by some format string, which I can let eat by the Oracle.

But any simpler solution were also okay, if it exists.

It need to be generated in perl. INSERT ... (current_timestamp, ...) isn't okay.

Something like this will do:

my ($second, $minute, $hour, $day, $month, $year, $weekday, $dayofyear, $dst) = localtime();

my $oracledatestring = sprintf "TO_DATE('%04d/%02d/%02d %02d:%02d:%02d', 'yyyymmdd HH24:MI:SS')", $year+1900, $month+1, $day, $hour, $minute, $second;

.. giving something like:

TO_DATE('2014/04/28 16:34:34', 'yyyymmdd HH24:MI:SS')

... which can then be inserted directly into an Oracle DATE column.

All a bit of a long way round, to be honest. Use SYSDATE in your INSERT statements instead.


Goldengate warning on table with primary key Installed Goldengate Version 18.1.0.0.0 for Oracle 11gR2. Table has primary key and yet goldengate complains that there is no key found,see below

    GGSCI (server01.localdomain as gguser@UPGR) 3> add trandata scott.books

    2019-01-23 22:44:42  WARNING OGG-06439  No unique key is defined for table BOOKS. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

    2019-01-23 22:44:42  INFO    OGG-15130  No key found for table SCOTT.BOOKS.  All viable columns will be logged.

    2019-01-23 22:44:42  INFO    OGG-15135  TRANDATA for instantiation CSN has been added on table SCOTT.BOOKS`

    GGSCI (server01.localdomain as gguser@UPGR) 10> info trandata scott.books

    Logging of supplemental redo log data is enabled for table SCOTT.BOOKS.
Columns supplementally logged for table SCOTT.BOOKS: "AUTHOR", "ID", "LANGUAGE_ID", "TITLE", "YEAR_PUBLISHED".

Prepared CSN for table SCOTT.BOOKS: 978016

This is DDL of scott.books from Oracle SQL Developer

CREATE TABLE "SCOTT"."BOOKS"
   ("ID" NUMBER(*,0),
    "TITLE" VARCHAR2(26 BYTE),
    "AUTHOR" VARCHAR2(23 BYTE),
    "YEAR_PUBLISHED" NUMBER(*,0),
    "LANGUAGE_ID" NUMBER(*,0),
     CONSTRAINT "PK_ID" PRIMARY KEY ("ID")
  )

Can someone point me in right direction.
Thanks

From dba_constraints

SQL> SELECT
  2      constraint_name,
  3      constraint_type,
  4      status,
  5      validated
  6  FROM
  7      dba_constraints
  8  WHERE
  9      owner = 'SCOTT'
 10      AND table_name = 'BOOKS';
CONSTRAINT_NAME                C STATUS   VALIDATED
------------------------------ - -------- -------------
PK_ID                          P ENABLED  VALIDATED

This happens when the PK exists, but it is not enabled or not validated. Make sure the PK is enabled and validated, then try adding trandata again.


Is possible to mysql change some engine tables automatically? I have 3 WordPress blog hosted on cPanel using MySQL. All 3 of them were working fine until yesterday. Now, the support says that “some tables changed from 'MyISAM' to InnoDB”.

In the logs I get:

[mydb.wp_usermeta] Error: Unknown storage engine 'InnoDB'
[mydb.wp_usermeta] error: Corrupt

And the same for other tables.

I don't know why those tables changed the engine. Is it possible for MySQL to have changed it automatically for some reason?

SHOW VARIABLES LIKE `%engine%`;

Of interest are default_storage_engine, which was MyISAM in older versions, but is now InnoDB.  This comes into play when you do CREATE TABLE without including ENGINE=....

Also of interest is enforce_storage_engine.  This strengthens the previous setting by not letting you create a table of Engine other than the default.

When changing (ALTER TABLE .. ENGINE=..) a table from one engine to another, there are some number of potential pitfalls.  I cover MyISAM --> InnoDB in here.

Did you recently upgrade MySQL?  If so, any number of problems can occur if the upgrade failed to run mysql_upgrade.

What version of MySQL are you running?

Please provide the recent contents of mysqld.err.

Please provide ls -ld /tmp

Are you using RoundCube?  The bug seems to be related to their installation.


Can partitioning help speed up inserts on a very large table? Some background:

SQL 2012 Enterprise
OLTP table currently at 1B records
records increase 5-10M per day
SAN lun with flash storage
two indexes (one clustered identity int)

What we have noticed is a gradual slowing of INSERTs over time, which will eventually become an issue to the business if left unchanged.

The question:
We are planning to transition to use partitioning on this table soon, primarily for space management, but the main question here is whether partitioning can help improve the performance of INSERTs. (We know about performance increases for SELECTs already, please do not comment on that side of things if possible.)

I have done a fair amount of searching forums, etc, but have not found anything specific to INSERTs and partitioning. It seems reasonable to me that this would help maintain speed over time, given that we will partition on "date inserted" and that the INSERTs would always be directed to the most recent partition, but looking for confirmation.

Thanks all.

UPDATE:
To clarify: a staging table (for switching in) is not an option due to the OLTP nature and needing to read the data out immediately after inserting, both of which cannot be delayed awaiting a switch in.

Short answer: yes it can help, because it's theoretically instantaneous. You would insert your data into a staging table with the same definition as your main partitioned table, and then switch it into the partitioned table, which is a metadata operation (schema lock).

Long answer: it might make performance suffer over the long run to have such a large table, and unless you move to later versions of SQL Server, statistics updates are quite difficult to manage.

If this is a read-heavy table, I'd consider using view partitioning instead. Each month (for example) would get its own table, with check constraints on a date column to help the query optimizer know where the data is physically stored, and a view over the top of that:

SELECT col1, col2, col3, col4 FROM period201501
UNION ALL
SELECT col1, col2, col3, col4 FROM period201502
...
SELECT col1, col2, col3, col4 FROM period201608

(or whatever). Then your metadata operation is the update of the view, as opposed to the switching in of the partitioned table.


Handling of Topic Drill Downs in Databases I have a requirement wherein I have to set up a drill down feature.

For example:
There is a tab in a shopping website "Men". Now if we click on Men we will get more topics like "Clothing", "Accessories" etc. When we click on "Clothing" we again get "Tees", "Polos", "Shirts", "Jeans". Again if we click on "Tees" we get further options of "Plain", "Graphic" etc.

So a specialization kind of thing comes into picture

  Men --> Clothing --> Tees --> Graphics

I am not able to figure out an efficient way how to handle these types of feature in a database. Though this is present everywhere and is quite common, but still figuring out a way to implement this is getting tougher.

Can someone help me with some pointers so that I can do some research in the correct path.

It looks like you're wanting to store a tree structure. This is a bit awkward with databases, but it's definitely possible. Here's a link which explains how: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ The "Nested Set Model" part is what you're looking for.

Applying that model to your scenario, you would get a table that looks something like this:

And a conceptual model for the first 3 levels that looks something like this (please excuse my MS Paint drawing skills):
Obviously you would have more nodes, but it's the basic idea.


Sql Server Reporting I have a report parameter which depends on a query from the Dataset. The query needs one parameter and sql server reporting has no parameter drop down

version - Microsoft SQL Server 2016 Report Builder

Dataset parameters are not defined there.

To define the "value" of the parameter, you have to edit the dataset itself. That's where you will specify what value you want your query to run with; you can use a static value, or a report parameter.

If you use a report parameter as the value for your dataset, the dataset will not execute until the report parameter value is defined by the user. This will make the report behave like, "the values of parameter 2 only become available after selecting parameter 1".

Hope it helps.


Accessing old version of referenced table in trigger function - Postgresql In my database I have a table named "layer" and a table named "grouplayer". The table "layer" is referencing the table "grouplayer" by a foreign key constraint with Cascade DELETE.
I have added a trigger function to the table "layer" which is getting the value of another column of the table "grouplayer" by a SELECT query.  The function is working as intended when I delete a row of the table "layer". However if I delete a row of the table "grouplayer", the function doesn't work as the result of the SELECT query is NULL.

CREATE OR REPLACE FUNCTION some_func() RETURNS TRIGGER AS $$
DECLARE
    pro TEXT;
BEGIN
    SELECT project into pro from grouplayer WHERE id = old.grouplayer_id;
    -- Query = NULL. How can I access project from grouplayer before deleting?

    RETURN NEW;
END
$$  LANGUAGE plpgsql;

I can't access the grouplayer even though I have included the keyword Before in the Create Trigger Statement:

CREATE TRIGGER some_trig BEFORE DELETE ON layer FOR EACH ROW EXECUTE PROCEDURE some_func ()

I didn't look at the code, but obviously the row is deleted from grouplayer before the BEFORE trigger on layer is executed.

Perhaps you can perform the task in a BEFORE trigger on layer.


Grouping in relational algebra with more than one grouping attribute So I am having a bit of trouble understanding the Grouping operator, Ɣ.
If had a the relation Sales(employeeId, date, salePrice) and I wanted to list the largest salePrice for each employee per day, how would I group both by employeeId and date?

For example would it be
ƔemployeeId,date,MAX(salePrice)->largestSale(Sales)
or
ƔemployeeId,date; MAX(salePrice)->largestSale(Sales)
or something along those lines?

The usual notation is to write separately the grouping attributes from the aggregation functions, writing the attributes on the left of the γ symbol and the aggregation functions on the right, so your query should be something like this:

employeeId, date γ MAX(salePrice)→ largetSale(Sales)

This means:

make a group for each different combination of employeeId and date in the Sales relation, and
for each group produce a tuple with employeeId, date, maximum sale price of the group.


Entity-Relationship Diagrams: Identifying which way a relationship flows I am having trouble grasping the "relationship" concept of an entity-relationship diagram. For example, you have Classroom and Furniture as your entities. If you were to put a relationship diamond with the word Contains and a line between the two, how would one be able to tell which contains which?

Obviously we know that Furniture cannot contain a Classroom, however, when it comes to applications and data models there are often foreign concepts and terms meaning we have no prior knowledge of the relationship or possibilities.

Without actually having words to describe the diagram, which would defeat the purpose of the ERD (as it should speak for itself), how would one determine visually which way the relationship was flowing?

Thanks in advance.

FYI; in case it is relevant - the line touching Classroom has a "one and only one" symbol, the line touching Furniture has a "zero through many" symbol.

The flow of ERD relationships go in both directions. Cardinality expresses the rules governing the relationships between entities.  The 'one and only one' of classroom provides you with a verbal logic of - 'A classroom must contain..'.  The 'zero to many' furniture cardinality provides 'zero to many pieces of furniture.'

Don't think of the furniture physically being in the classroom, just that a connection exists between the two.


How to control access security in database Our database's security became a problem recently. Here is what we have:

Several basic roles -

read_only (select, view definition)
read_write (insert/update/deleted)
executor (execute SP/function)
ddl_admin (alter/create table/view/SP/function/trigger/almost any thing, it's a risk role and we are going to discard it)
several ssis roles for ssis jobs
etc

Those roles are in every database and in all environments (DEV/QA/STG/PROD).

We also have a bunch of user groups such as tech_leads, QA, developers (based on different projects), reporting, BA and so on.

We grant those users different roles on different environments. Usually people have more privileges on DEV/QA than STG/PROD. We almost only give regular user read only in production databases.

However, there were many occasions that users come to ask specific permissions on different servers. For example, the tech_leads may ask for server control on DEV/QA. Or one developer team ask for read_write permission on STG temporarily for trouble shooting.

After a long time, we got a massive database security.

Now our DBAs trying to think of some better ways to re-regulate the users and roles. We got advised that creating some new roles such tech_leads role, QA_readonly role, QA_leads role, developer role, and granting those roles to relevant groups.

The interesting thing here is, in same environment, say QA environment, the same role will have same permissions across all databases. But the role on PROD environment will be different than QA, meaning has much less permissions on PROD. Every time we modify the role in one database, we have to modify the role on all databases in that environment (we will have scripts for this of course).

I can't tell if the advise is good for long term yet. But another opinion would be keeping most of our basic roles, splitting some of the existing roles and create more tiny roles. For example, removing ddl_admin, and add a role to create SP/trigger/function, a role to create table/view, a role to alter, and a role to create synonym. This way, all of our roles will be static across all databases, and all environments.

I want to know how you guys control your database security? And can you give us any suggestions on what I talked above? Thanks very much.

A common practice in my experience has been that if a user needs elevated access to a higher environment, you restore a copy to the level which they have access to. This ensures that you can keep similar security across most environments and mitigates privacy and security concerns as well as the creation of new issues.

For example, if tech_leads need to troubleshoot an issue in STG, restore the staging level database into a dev environment (in parallel or overwriting the previous version). That way they can safely test fixes and then let that code be promoted through change management upwards through the environments.

There will still probably be one-off occasions that arise, but that can be handled manually as they are ideally few and far between. Making all those extra roles sounds like more of a nightmare to keep track of and might create new issues.


Data Lake : Data Catalog and Data Schema definitions I'm trying to understand the definition of data catalog and data schema. After several articles I read, I'm getting even more confused!

I realized when the article is talking about data lake, the term will be used to describe data catalog as containing metadata information within data lake. As obvious as the name, it is a catalog of all information within data lake it self.

Now, as far as I know, data catalog is database. Whereas data schema is described as containing metadata information within a database. Which means data catalog in data lake is equal to data schema in database.
Both seems to have the same purpose such as data discovery.

EXCEPT the terms are used in different level. One for data lake and the other one is database.

My questions are :

Isn't the terms used inconsistent and confusing? What is the right term I should be using for each level?
If these two are different, how are they different? Please enlighten me..

This has a regular structure ("schema"):

id  name   color  cost
1   dress  green    40
2   camera red     200
3   car    black 34000

Note the rigid "rows" and "columns".  The columns have specific datatypes" (string, number, etc).  Etc.

The following has some structure, but is also very open-ended and "self-defining".

{name: "dress"; size: "large"},
{name: "camera"; pixels: "12M"; zoom: "4x"},
{name: "car"; style: "sedan"},
{name: "family"; members: ["father", "mother", "children"]}

The former example is the key design of all RDBMSs.  It is the primary focus of MySQL, Postgres, etc.  It is designed and implemented for speed and scaling.  The "catalog" and "schema" define the structure separate from the data.

The latter example is less structured, less performant, but more flexible.  Perhaps this is the "data lake" you are asking about.

I suggest you focus on RDBMS and don't confuse yourself with "other" ideas.


How to correct a primary key field in MS SQL 2014 I have a table that uses serial numbers as the primary key in a database. We had a bad import of some serial numbers, where a single apostrophe was included at the beginning of the field.

I attempted to remove it using the statement

update [xxx].[dbo].[xxx] set [xxx].[dbo].[xxx].[keynumber] = replace([xxx].[dbo].[xxx].[keyNumber], '''','')

When I did, I got the following error:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'xxx$PrimaryKey'. Cannot insert duplicate key in object 'dbo.xxx'. The duplicate key value is (123456789).

Is there a way that I can update this field and remove the single apostrophe without violating the constraint?

We would need the table definition for a definite answer. It looks like that when you remove the single apostrophe from the serial number, that that value is then the same as an already existing value. Hence the error message.

It looks like the import file was corrected and imported again, resulting in multiple values containing, for example, values similar to 12345 and '12345.

Solution

I would try and search for the near identical primary keys first and see if you can delete them.

I created a dummy scenario with db<>fiddle to show you how it would work.

Create Table

create table xxx (keynumber nvarchar(10) primary key clustered, comment nvarchar(20))

Insert Pseudo Data

insert into xxx (keynumber,comment)
values
('12345','First Comment'),
('''12345','Second Comment'),
('12346','Third Comment'),
('''12346','Fourth Comment'),
('123457','Fith Comment')

SELECT Psuedo Duplicates

SELECT x1.keynumber, x2.keynumber
FROM [xxx].[dbo].[xxx] as x1
     JOIN [xxx].[dbo].[xxx] as x2
     ON '''' + x1.keynumber = x2.keynumber

Results

keynumber | keynumber
:-------- | :--------
12345     | '12345
12346     | '12346

You can then either deleted the duplicates or rename them (see Alternative)

Once you have cleaned up the duplicates, you will be able to run your UPDATE.... Before that you have some manual labour to do.

Alternative / Renaming

Instead of just removing the apostrophe, add an additional value to the keynumber values that have an apostrophe. E.g. add CHK to the keynumber

UPDATE [xxx].[dbo].[xxx]
SET [xxx].[dbo].[xxx].[keynumber] = replace([xxx].[dbo].[xxx].[keyNumber], '''','CHK')

This way you will be able to see which values have to be checked.

Observations

If the column keynumber would have been an int, then it would have been impossible to enter '12345 into the table.


Does MAXDOP affect the number of tempdb data files I should create? From what I have read, best practices for configuring tempdb includes creating multiple data files.  The rule of thumb as I understand it is if you should create the same number of data files as you have logical processors up to a max of 8.  Then if you have tempdb contention add an additional file until either the contention goes away or you have added an additional file per 4 additional logical processors.  So if you have 16 logical processors then you would go up to a max of 10 files.

What I'm curious about is if MAXDOP affects this at all?  So if for example I have 12 logical processors.  I have set my MAXDOP to 6 because they are broken into 2 NUMA nodes.  Does this mean I should stick with 6 data files for tempdb?  Or would I still have the 8 or 9 that is recommended for the total number of processors?

I wouldn't add single additional files, I would at least go up by 2 or 4 each time. Not many operations will work better at cores = 5 than cores = 4. I don't think you'll get much improvement when, faced with contention, you go from 4 files to 5. I would probably go up in groups of 4 (except when you have 6-core processors, for example, and want to test limiting SQL Server to exactly 6 cores), but by 16 if you still have contention I would have to wonder if this is really getting anywhere near solving the problem - especially because your best bang for the buck is going to be placing each of those files on its own set of spindles; highly unlikely you have this many completely independent disks.

As for the MAXDOP effect, I don't think you should think about MAXDOP that way. Unless you use affinity and prevent SQL Server from even seeing the other 6 cores, this doesn't mean that all queries will use the same 6 cores. So in theory, one query could use CPUs 1-6, and the other could use 7-12, and if contention could be relieved by letting each CPU access its own file (and it actually worked out perfectly), then certain concurrent ops could benefit from 12.

Another very easy way to solve tempdb contention problems is to throw a couple of SSD drives in the box (format one to 80%, fill it with a single pre-sized tempdb file, and use the other drive as standby - SSD lifespans can vary, but keeping a chunk unallocated can help extend that life quite a bit). This is even supported in a cluster as of SQL Server 2012. Takes a lot of thinking and effort out of trying to squeeze performance out of tempdb when slow storage is always going to be a bottleneck no matter how many files you try to configure.

Some background reading that might be useful, especially if you're stuck with old-fashioned spinny disks for tempdb:

http://www.sqlskills.com/blogs/paul/tempdb-configuration-survey-results-and-advice/
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/
http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/
http://www.simple-talk.com/sql/database-administration/optimizing-tempdb-configuration-with-sql-server-2012-extended-events/
http://www.sqlservercentral.com/blogs/robert_davis/2010/03/05/Breaking-Down-TempDB-Contention/


UNION ALL vs CASE - SQL query optimization I need to retrieve the count of both approved and unapproved data from my_table.
And I have tried both queries and succeeded. Which query is optimized and why. Please give suggestion

DDL Query
CREATE TABLE my_table( id int(11) UNSIGNED NOT NULL,
 approved TINYINT(1) NOT NULL,
 invoice_no varchar(9) NOT NULL,
 deleted_at timestamp NULL DEFAULT NULL)

First Query
SELECT
    COUNT(CASE WHEN approved=1 THEN id END) as approved,
    COUNT(CASE WHEN approved=0 and deleted_at is null THEN id END) as unapproved
    FROM my_table

Second Query
 SELECT COUNT(id)
 FROM my_table
 WHERE approved=1
 UNION ALL
 SELECT COUNT(id)
 FROM my_table
 WHERE approved=0 and deleted_at is null

I have run command explain extended and attached the screenshot

Plan A -- no useful indexes:

Do the CASE approach.

Reason:  It is a single pass over the data.

PS:  SUM(approved = 1) is more concise, possibly faster.

Plan B -- useful indexes:

Do the UNION ALL approach if you have this composite index:

INDEX(approved, deleted_at)

and don't do COUNT(id), instead do COUNT(*).

That can be done in range scans on that one index.  The index is smaller than the table and each count only needs to scan part of the index.

Furthermore, UNION ALL is better optimized in 5.7 / 10.1.  So upgrade if you are not there yet.


How to use parent table column inside subquery JOIN condition? I am writing a SQL Query for pulling records from multiple tables for sending the update or added new values email notifications to the user.

In below SQL Query I am getting all records which are updated in respective tables, But I want to add one conditional check/JOIN in each LEFT JOIN satatements
for AssociatedRecordID Column of NotificationQueue Table so that I will only pull only matched records from each select tables to increase the performance of query execution.

For example:

LEFT JOIN Note nt WITH(NOLOCK) ON op.OpportunityID = nt.RecordID AND nt.NoteID = nq.AssociatedRecordID

Here I want to use nq.AssociatedRecordID for Join AND nt.NoteID = nq.AssociatedRecordID

Query:

SELECT ops.* FROM NotificationQueue nq
    INNER JOIN (
        SELECT op.OpportunityID, op.Name AS [OpportunityName], ua.Email AS OwnerEmail, nt.NoteID, nt.Note,
            tks.TaskID, tks.ActivityType, ua.FirstName, ua.LastName, FORMAT(getdate(), 'M/dd/yyyy, hh:mm:ss tt') AS [datetime],
            lead.LeadID, lead.LeadName
        FROM Opportunity op WITH(NOLOCK)
        INNER JOIN UserAccount ua WITH(NOLOCK) on op.owner = ua.UserID
        LEFT JOIN Note nt WITH(NOLOCK) ON op.OpportunityID = nt.RecordID AND nt.EntityID = 4 -- OPPORTUNITY
        LEFT JOIN (
            SELECT t.TaskID, t.TaskTypeID, t.OpportunityID, tt.Description AS [ActivityType] FROM Task t WITH(NOLOCK)
            INNER JOIN TaskType tt ON t.TaskTypeID = tt.TaskTypeID
        ) tks ON op.OpportunityID = tks.OpportunityID
        LEFT JOIN (
            SELECT l.LeadID, l.Topic AS [LeadName], lo.AssociatedOpportunityId FROM LeadOpportunity lo WITH(NOLOCK)
            INNER JOIN Lead l ON lo.ParentLeadId = l.LeadID
            WHERE l.Active = 1
        ) lead ON op.OpportunityID = lead.AssociatedOpportunityId

        WHERE op.Active = 1
    ) ops ON nq.RecordID = ops.OpportunityID
WHERE nq.Processed = 'N'

Output:

I am getting expected records from above query but I want to use AssociatedRecordID column of NotificationQueue Table in all LEFT JOIN statements for better performance,
Any suggestions How should I achieve same would be appreciated.

You may want to use EXISTS operator with Sub-query, but that serve in different scenarios, in your case moving NotificationQueue table down to the sub-query can make you to do join that your looking for, following is the example of same:

SELECT  op.OpportunityID,
        op.Name AS [OpportunityName],
        ua.Email AS OwnerEmail,
        nt.NoteID, nt.Note,
        tks.TaskID,
        tks.ActivityType,
        ua.FirstName,
        ua.LastName,
        FORMAT(getdate(), 'M/dd/yyyy, hh:mm:ss tt') AS [datetime],
        lead.LeadID, lead.LeadName
FROM NotificationQueue nq
    INNER JOIN  Opportunity op WITH (NOLOCK) ON nq.RecordID = op.OpportunityID
    INNER JOIN  UserAccount ua WITH (NOLOCK) on op.owner = ua.UserID
    LEFT JOIN   Note nt WITH (NOLOCK) ON op.OpportunityID = nt.RecordID AND nt.EntityID = 4 -- OPPORTUNITY
    LEFT JOIN   (
                SELECT  t.TaskID, t.TaskTypeID,
                        t.OpportunityID,
                        tt.Description AS [ActivityType]
                FROM    Task t WITH(NOLOCK)
                        INNER JOIN TaskType tt ON t.TaskTypeID = tt.TaskTypeID
                ) tks ON op.OpportunityID = tks.OpportunityID

    LEFT JOIN   (
                SELECT  l.LeadID,
                        l.Topic AS [LeadName],
                        lo.AssociatedOpportunityId
                FROM    LeadOpportunity lo WITH(NOLOCK)
                        INNER JOIN Lead l ON lo.ParentLeadId = l.LeadID
                WHERE l.Active = 1
                ) lead ON op.OpportunityID = lead.AssociatedOpportunityId

WHERE op.Active = 1 and nq.Processed = 'N'

As per comment, following is the example of last LEFT JOIN:

    OUTER APPLY (
                SELECT  l.LeadID,
                        l.Topic AS [LeadName],
                        lo.AssociatedOpportunityId
                FROM    LeadOpportunity lo WITH(NOLOCK)
                        INNER JOIN Lead l ON lo.ParentLeadId = l.LeadID
                WHERE l.Active = 1 AND nq.RecordID = lo.AssociatedOpportunityId
                ) lead --ON op.OpportunityID = lead.AssociatedOpportunityId


Determine percentage from count() without cast issues I'm trying to run the following query to provide the % of rows in my patients table that have a value the refinst column. I keep getting a result of 0.

select (count (refinst) / (select count(*) from patients) * 100) as "Formula"
from patients;

The table has 15556 rows, and select count(refinst) from patients tells me that 1446 of those have a value in the refinst column. The response I'd like to get from the query would be 30.62 (1446/15556*100=30.62XXXXX, rounded to two decimals).

I'm pretty sure it has something to do with the data type of the count results (integers I'm assuming). If I divide an integer by an integer and the result is less than 0 it is truncated to 0 correct? If that's the case, can someone show me how to cast the results of the counts as a number with 2 decimal places so that the result will be rounded to 2 decimal places as well?

I'm sure there's a better way to write this code than multiple count statements. I am looking for a more processor-efficient way to write this query in particular.

SELECT (count(refinst) * 100)::numeric / NULLIF(count(*), 0) AS refinst_pct
    -- count(refinst) * 100.0 / NULLIF(count(*), 0) AS refinst_pct  -- simpler
FROM   patients;

Do not use a subselect. Both aggregates can be derived from the same query. Cheaper.

Also, this is not a case for window functions, since you want to compute a single result, and not one result per row.

Cast to any numeric type that supports fractional digits, like @a_horse already explained.
Since you want to round() to two fractional digits I suggest numeric (which is the same as decimal in Postgres).
It's enough to cast one value involved in a calculation, preferably the first. Postgres automatically settles for the type that does not lose information.
Or, simpler yet: since we multiply anyway, use a numeric constant that's coerced to numeric automatically because of the decimal point (100.0).

It's generally a good idea to multiply before you divide. This typically minimizes rounding errors and is cheaper.
In this case, the first multiplication (count(refinst) * 100) can be computed with cheap and exact integer arithmetic. Only then we cast to numeric and divide by the next integer (which we do not cast additionally).

NULLIF(count(*), 0) prevents division by zero (raising an exception). We get NULL as (unknown) percentage if there are no rows at all.

Rounded to two fractional digits:
SELECT round((count(refinst) * 100)::numeric / NULLIF(count(*), 0), 2) AS refinst_pct
FROM   patients;


Why am I able to query for more than 1024 columns but I can not make a view of the same query? My boss is requesting a view with greater than 1024 columns where in he will only select a few columns he wants when he queries the view so that the actual returned columns are less than 1024. Why am I able to query for greater than 1024 columns but I can not save this very same query as a view?

I am using SQL Server 2012 Enterprise

From CREATE VIEW (Transact-SQL):

  A view can have a maximum of 1,024 columns.

From Maximum Capacity Specifications for SQL Server:

Each table can have a maximum of 1,024 columns (30,000 for wide tables).

So, the simple answer is that the (somewhat arbitrary) limits are different.


"Too many connections" when running MySQLDump MySQL 5.5.x on Windows, 4GB RAM.

I have a Wordpress Multi-Site install.  The WPMU db contains over 6k tables (I know, horrendous... WPMU is not my favorite app right now)

I am running mysqldump early mornings, and from my monitoring tools, I am seeing that my other db sites on the server, along with the WPMU site is going down for about 10-15 min during the time when the WPMU db is being dumped.  The error is "Too many connections"

MySQL is currently set to 100 connections.

What is my best path forward to mitigate the downtime? Up the connections, and throw more RAM at the box?

If you convert all your tables to InnoDB, then you can perform your mysqldumps without disturbing any of DB Connections.

Here is a script to mass convert all MyISAM tables into InnoDB.

mysql -uroot -p -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" > /root/ConvertMyISAMToInnoDB.sql

Just execute /root/ConvertMyISAMToInnoDB.sql during a full maintenance. This needs to be done only once. Once all your tables are InnoDB, you could probably mysqldump your databases in parallel. I wrote an earlier post back in April 2011 on multiple ways to perform mysqldumps. You need option 2. You will also need to add the --single-transaction option to each mysqldump so that all the mysqldumps are launched from the same point-in-time.

However, you could still experience performance problems anyway because each table that is being mysqldump'd will push old data out of the InnoDB Buffer Pool to push in table data for each table being mysqldump'd.

Operations must slow down because queries being executed must share the InnoDB Buffer Pool with the mysqldumps that are thrashing it.

As for upping the max_connections you can do this without restarting mysql.

Please add this to my.ini

[mysqld]
max_connections=1000

You do not have to restart mysql. Just run this command in the mysql client:

mysql> SET GLOBAL max_connections = 1000;

You may need more RAM on the box to accommodate additional DB Connections as well as a Decent Sized InnoDB Buffer Pool.


Move Commonly-Repeated Fields into a new Table I'm still at the design stage for a new system, and I've noticed there are several places where different types of object share similar fields.

In this case, we have customers, offices, warehouses etc. that have address fields like Address1, Address2, ... Postcode. So, I was strongly considering making a new Addresses table with all these fields in, and an ID, and linking to that with foreign keys for all of the different types that contain addresses. The same thing could be done for groups of telephone/fax numbers too.

To illustrate this point, here's the current situation in an abstract form...

Customers        Offices
- CustomerID     - OfficeID
- FirstName      - OfficeName
- Surname        - Telephone
- Address1       - Address1
- Address2       - Address2
- Address3       - Address3
- Address4       - Address4
- Postcode       - Postcode

...and after the change...

Customers        Offices         Addresses
- CustomerID     - OfficeID      - AddressID
- FirstName      - OfficeName    - Address1
- Surname        - Telephone     - Address3
- AddressID      - AddressID     - Address3
                                 - Address4
                                 - Postcode

The objective here isn't really normalization (I'm not intending to eliminate duplicate addresses here, even though it may be possible) but to help keep similar subtypes of data consistent. It also means that in the model code, I can treat all addresses in the same with with one class, whatever they belong to. I hope that the overhead in joining these tables wouldn't be that high, especially since I don't always need the addresses for all use cases.

Your opinions/experiences please!

Yes, move it.  The extra join overhead will be minimal (if the right indexes are in place!) and you will get a performance improvement for all the other queries which don't use addresses because you will get more rows per page in Customers and Offices.

Down the road, any master data management (MDM) or data clean-up will be easier with the values all in one place.

Sticky points to look out for are common definitions of column semantics (is Address4 the city, say, for absolutely every possible address?).  This could be important for reporting, for example, or summarising sales by region.  Uniqueness could be difficult to define for any natural keys you may choose to create on Addresses.


Always start Oracle DB in Idle State Is there a way to setup your Oracle db to always be brought up in an idle state so that you can then 'startup' when you want to use it. I'm running Oracle 11g R2 on Windows with lean resources.

Starting and Stopping the Database with Oracle Administration Assistant for Windows

  To start or stop the database:

  From the Start menu, select All Programs, then Oracle - HOME_NAME,
  then Configuration and Migration Tools, and then Administrative
  Assistant for Windows.

  In the console window, expand the Oracle Administration Assistant for
  Windows tree structure.

  Under Databases, right-click the name of the database, and from the
  menu, select from the following options:

  Connect Database

  Start Service

  Disconnect Database

  Stop Service

  Startup/Shutdown Options

  Process Information

At Startup/Shutdown Options, you can set whether the instance should start when the Windows service belonging to that database instance starts.

Another method is to use the command line, disabling the automatic startup of the instance of database ORCL:

oradim -edit -sid ORCL -STARTMODE manual


NULL value returned when concatenating a string in SQL while loop! I'm trying concatenate a string in a while loop, but it only returns NULL. Is there something I'm missing?

declare @o int = 10000000/1000000;
declare @i int = 0;
declare @s nvarchar(200);

while @i < @o
  begin
    set @s += 'hello' + cast(@i as nchar(4));
    set @i += 1;
  end

select @s;

If you are on SQL Server 2012+ you can use CONCAT. This treats NULL as an empty string.

declare @o int = 10000000/1000000;
declare @i int = 0;
declare @s nvarchar(200);

while @i < @o
  begin
    set @s = CONCAT(@s, 'hello', cast(@i as nchar(4)));
    set @i += 1;
  end

select @s;


What is the default column name given to a TABLE() type with a single column CREATE OR REPLACE TYPE MISSING_KEYS AS TABLE OF VARCHAR2(256);

SELECT [¿what goes here?] as missing_key
FROM TABLE (MISSING_KEYS('A','B','C','D'))

What is the default column name that should be in [¿what goes here?] in the above example?

column_value Pseudocolumn

  When you refer to an XMLTable construct without the COLUMNS clause, or
  when you use the TABLE function to refer to a scalar nested table
  type, the database returns a virtual table with a single column. This
  name of this pseudocolumn is COLUMN_VALUE.

Example:

CREATE OR REPLACE TYPE MISSING_KEYS AS TABLE OF VARCHAR2(256);

SELECT column_value AS missing_key
FROM TABLE (MISSING_KEYS('A','B','C','D'))


Migrating a Mariadb/wordpress site with only .frm files My server crashed. My /home /www and lib/mysql were on a different hard disk so I have all the data. The original server was Debian 9 with Mariadb and an apache webserver. I planned to move to mysql so I have a debian 10 server with mysql. I also have another debian 9 server with Mariadb so that I can use sqldump to move my databases. All my databases moved over fine, except one that was a wordpress database. That directory only has .frm files in it, there are no .MYD or .MYI files. I tried copying them to /var/lib/mysql of the Deb9 server, changed the permissions and ownership. However, mysql dump gives the following error message

mysqldump: Got error: 1932: "Table 'dbname.tablename' doesn't exist in engine" when using LOCK enter code here

I tried copying them as is to the MYSQL server too, the server did not see the tables. In the MariaDB server command line interface it shows the tables. However when I try to view the contents of any table it says

Error 1932 (42S02): Table 'dbname.tablename' doesn't exist in engine

I get the same error when I try repair table.

Any ideas on how I can get this moved?

I have resolved my issue. Instead of copying only the database folder, I copied the entire /var/lib/mysql folder to the mariadb install. Then I did sqldump on each database and moved them to the mysql server. I'm not sure why the copy of the database folder didn't work but I got all my data back.


Oracle Newbie Question - How can one run selects from different tables but show output on separate rows E.g. Table 1 Japan Customers
     Table 2 USA Sales People
     Table 3 HK Engineers

There are selects from the 3 tables each. However, can I run all 3 selects within a single/overall select statement and output 1 result set, so one can see
each row labeled based on the table name? all these tables don't have similar columns but share a common id column

Col1        Col2       Col3...
Japan
USA
HK

Is possible to merge/add the result of two or more different queries using:

UNION ALL

But count and names of columns must match on each query.

Of course you can add null or fixed values as needed.

SELECT COL1 AS ID, COL2 AS NAME, NULL AS INFO
FROM   TABLE1
UNION ALL
SELECT F1 AS ID, F2 AS NAME, INFO
FROM.  TABLE2
UNION ALL
SELECT 0 AS ID, NAME, INFO
FROM   TABLE3


Do triggers compile each time? We are troubleshooting a server that has high CPU utilization. After finding that the queries weren't really causing it, we started looking into compilations.

Performance Monitor is showing less than 50 Compilations/sec and less than 15 Recompilations/sec.

After running an XE session looking for compilations, we are seeing thousands of compilations per second.

This system is using triggers to audit changes. Most of the compilations are due to triggers. The triggers reference sys.dm_tran_active_transactions.

Our first thought was that maybe referencing a DMV in a trigger would cause it to compile each time, or maybe just this specific DMV would cause it. So I started testing that theory. It does compile each time, but I hadn't checked if a trigger compiles each time it is triggered when it doesn't reference the DMV and instead hardcodes a value. It was still compiling each time it got triggered. Dropping the trigger stops the compiles.

We are using sqlserver.query_pre_execution_showplan in an XE session to track the compilations. Why is there a discrepancy between that and the PerfMon counter?
Is it normal that you get a compilation event each time a trigger runs?

Repro script:

CREATE TABLE t1 (transaction_id int, Column2 varchar(100));
CREATE TABLE t2 (Column1 varchar(max), Column2 varchar(100));
GO

CREATE TRIGGER t2_ins
ON t2
AFTER INSERT
AS

INSERT INTO t1
SELECT (SELECT TOP 1 transaction_id FROM sys.dm_tran_active_transactions), Column2
FROM inserted;
GO

--Both of these show compilation events
INSERT INTO t2 VALUES ('row1', 'value1');
INSERT INTO t2 VALUES ('row2', 'value2');
GO

ALTER TRIGGER t2_ins
ON t2
AFTER INSERT
AS

INSERT INTO t1
SELECT 1000, Column2
FROM inserted;
GO

--Both of these show compilation events
INSERT INTO t2 VALUES ('row3', 'value3');
INSERT INTO t2 VALUES ('row4', 'value4');

DROP TRIGGER t2_ins;

--These do not show compilation events
INSERT INTO t2 VALUES ('row5', 'value5');
INSERT INTO t2 VALUES ('row6', 'value6');

DROP TABLE t1, t2;

The XE event being used is leading you incorrectly to think the trigger is actually compiling every execution.  There are two extended events query_pre_execution_showplan and query_post_compilation_showplan that have similar descriptions, but differ by one important word:

query_pre_execution_showplan

  Occurs after a SQL statement is compiled. This event returns an XML
  representation of the estimated query plan that is generated when the
  query is optimized. Using this event can have a significant
  performance overhead so it should only be used when troubleshooting or
  monitoring specific problems for brief periods of time.

query_post_compilation_showplan

  Occurs after a SQL statement is compiled. This event returns an XML
  representation of the estimated query plan that is generated when the
  query is compiled. Using this event can have a significant
  performance overhead so it should only be used when troubleshooting or
  monitoring specific problems for brief periods of time.

The events aren't exactly the same in description and occur at different times from further testing using your repro.  Using a much larger event session definition, it is easy to see where compilations actually are happening.

Here you can see the first compilation happening for the insert statements as prepared plans being auto-parameterized in the green box.  The trigger is compiled in the red box and the plan is inserted into the cache as shown by the sp_cache_insert event.  Then in the orange box the trigger execution gets a cache hit and reuses the trigger plan for the second INSERT statement in the batch, so it's not compiling every execution of the INSERT command and the plan does get reused as you can see with the sp_cache_hit event for the trigger.

If we run the two INSERT statements individually again after the first execution the trigger doesn't compile again as shown in the events below:

Here the first statement encounters a cache hit for the prepared auto-parameterized version of the statement in cache but a miss for the adhoc batch that was submitted.  The trigger gets a cache hit and doesn't compile again as shown in the red block of events.  The green block of events repeats this behavior for the second INSERT statement run as a separate batch. However, in every case you still see the query_pre_execution_showplan event firing which I can only attribute to the difference in being optimized vs compiled in the event description, but the trigger isn't compiling for every execution as shown by these series of events.


How to set a specific directory location for individual MySQL database? Is it possible to set each database in MySQL to use a separate datadir?

I'm running a userdir development (sandbox) server and would like to put the MySQL data files for the database(s) for that user in their /home/<user>/mysql directory.

Linux Ubuntu Server 10.4
MySQL Server version: 5.1.41
Storage Engine type: InnoDB

How would you do this?

You would have play games with symlinks.

WARNING : The following only works with innodb_file_per_table enabled

For example, suppose your datadir was /opt/mysql/data. Each database woudl reside under that folder. If you have three databases (db1, db2, db3), then the folders are:

/opt/mysql/data/db1
/opt/mysql/data/db2
/opt/mysql/data/db3

Before doing anything run this query:

mysql> select table_schema,table_name from information_schema.tables
where table_schema in ('db1','db2','db3');

Let's start by creating temp databases

mysql> create database tmpdb1;
mysql> create database tmpdb2;
mysql> create database tmpdb3;

Next, let's move every table you have db1 to tmpdb1, db2 to tmpdb2, db3 to tmpdb3

mysql -uroot -AN -e"SELECT CONCAT('ALTER TABLE ',db,'.',tb,' RENAME tmp',db,'.',tb,';') FROM information_schema.tables WHERE table_schema in ('db1','db2','db3')" > /root/MoveTables1.sql
mysql -uroot < /root/MoveTables1.sql

OK, we moved every .ibd file sideways to tmp databases

Go into mysql and make sure all the tables have been moved. Databases db1-db3 should be empty

mysql> use db1
mysql> show tables;
mysql> use db2
mysql> show tables;
mysql> use db3
mysql> show tables;
mysql> use tmpdb1
mysql> show tables;
mysql> use tmpdb2
mysql> show tables;
mysql> use tmpdb3
mysql> show tables;

Next, drop the original databases

mysql> drop database db1;
mysql> drop database db2;
mysql> drop database db3;

Go into the OS and create symlinks

ln -s /home/user2/mysql /opt/mysql/data/db1
ln -s /home/user2/mysql /opt/mysql/data/db2
ln -s /home/user3/mysql /opt/mysql/data/db3
chown -R mysql:mysql /opt/mysql/data/db1
chown -R mysql:mysql /opt/mysql/data/db2
chown -R mysql:mysql /opt/mysql/data/db3

Go into mysql and make sure the databases are visible

mysql> show databases;

Next, move the tables into the symlinked databases

mysql -uroot -AN -e"SELECT CONCAT('ALTER TABLE ',db,'.',tb,' RENAME ',SUBSTR(db,4),'.',tb,';') FROM information_schema.tables WHERE table_schema in ('tmpdb1','tmpdb2','tmpdb3')" > /root/MoveTables2.sql
mysql -uroot < /root/MoveTables2.sql

Finally, run the first query you started with

mysql> select table_schema,table_name from information_schema.tables
where table_schema in ('db1','db2','db3');

If the same display comes up, you did it successfully.

UPDATE

@AaronBrown was kind enough to remind me that spreading .ibd files does not buy you anything except for spread-out files. No performance gains whatsoever. Thanks Aaron.

https://dba.stackexchange.com/a/4425/877
http://www.mysqlperformanceblog.com/2010/12/25/spreading-ibd-files-across-multiple-disks-the-optimization-that-isnt/


What happens when there is no available physical memory left for SQL Server? While googling I found some conflicting information.

Some sites state that when there is no physical memory left for data, then SQL Server moves the already existing data into TEMPDB (see: SQL Server: Demystifying TempDb and recommendations).

But other sites state that, when there is not enough physical memory left, the operating system can use the PAGE FILE and move data from the physical memory to it (see Page File for SQL Server).

I wonder where SQL Server writes data when it runs out of physical memory? To tempdb or to the OS Page file? Or maybe both of them?

when there is no physical memory left for data, then SQL Server moves the already existing data into TEMPDB

The article you linked to is misleading at best, and incorrect in some places.  I think the author was attempting to over-simplify some complicated things, and in doing so went a little too far.

SQL Server doesn't move data from memory (the buffer pool) into tempdb in that way.  It uses a "least recently used" caching strategy (in general), so if there is memory pressure, and new data needs to be pulled into memory, SQL Server will kick out the LRU data from the buffer pool to accommodate the new data.  This behavior is often monitored by a perfmon counter called "Page Life Expectancy" (PLE):

  The definition of PLE is the expected time, in seconds, that a data file page read into the buffer pool (the in-memory cache of data files pages) will remain in memory before being pushed out of memory to make room for a different data file page. Another way to think of PLE is an instantaneous measure of the pressure on the buffer pool to make free space for pages being read from disk. For both of these definitions, a higher number is better.

During query execution, SQL Server can use tempdb for certain operations.  This is usually done if estimates are bad, but low available memory can influence this behavior.

Some of the operations that can "spill" to tempdb in this way are hashing rows (for joins or aggregates, etc), sorting rows in memory, and buffering rows during parallel query execution.

User queries can also explicitly use tempdb (with global or local temporary tables), and implicitly use tempdb (with snapshot or read committed snapshot isolation levels).

Neither of these situations really seem to fit the statement you quoted.

  when there is not enough physical memory left, operating system can use the PAGE FILE and move data from the physical memory to it

This can definitely happen, and is outside of SQL Server's control for the most part.  There is a knob you can turn to try to prevent some types of OS-level paging, namely turning on "Lock Pages in Memory" (LPIM):

  This Windows policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk.

So what can we prevent from being paged to disk?

Prior to SQL Server 2012, pages that were allocated through a component called the "Single Page Allocator" were locked in memory (could not be paged).  This included the buffer pool (database pages), procedure cache, and some other areas of memory.

See Fun with Locked Pages, AWE, Task Manager, and the Working Set… for details, especially the section "4. Now I know that SQL Server on x64 can use “Locked Pages”, what exactly is locked?"  Additional related reading can be found here: Great SQL Server Debates: Lock Pages in Memory

In SQL Server 2012 and later, there is no "Single Page Allocator" (the single and multi-page allocators were merged, per An In-depth look at memory – SQL Server 2012/2014).  The details of what, exactly, can and cannot be paged is not documented in detail anywhere that I've seen.  You can use a query like this to see what is locked:

select osn.node_id, osn.memory_node_id, osn.node_state_desc, omn.locked_page_allocations_kb
from sys.dm_os_memory_nodes omn
inner join sys.dm_os_nodes osn on (omn.memory_node_id = osn.memory_node_id)
where osn.node_state_desc <> 'ONLINE DAC'

Per the same MS Support article, you could also use DBCC MEMORYSTATUS to see how much memory is "locked."

As a side note, you can see evidence of SQL Server's working set being paged by the OS in the error log.  There will be messages that look like this:

  2019-09-02 10:19:27.29 spid11s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 329 seconds. Working set (KB): 68780, committed (KB): 244052, memory utilization: 28%.


Large number of VLF and shrinking the log If there are lot number of VLF then it will effect the performance of the queries. I saw that solution is to shrink the log file. But shrinking the file is also bad. So how to take control of whole thing? What is the optimal number of VLF in a database?

"But shrinking the file is also bad"

Shrinking a data file: usually bad, mostly due to the havoc it wreaks on the allocation layout and results in increased fragmentation. Why you should not shrink your data files.

Shrinking a log file: provided the log has to shrink (it did grow beyond reason), shrinking it is mostly harmless. It should not be abused though as excessive shrinking will just trigger new growth when the file needs to expand for legitimate reasons. See How to shrink the SQL Server log.

  If there are lot number of VLF then it will effect the performance of the queries

Strictly speaking it will affect the performance of recovery, not the performance of queries. Queries will largely ignore the log (except queries that query the log, of course). Even writes (DML) will not suffer too much from a large number of VLFs. Is recovery that is impacted, really really really seriously, as to make it imperative to keep the number of VLFs in check.


SQL 2008 R2 Repair: Setup failed to install Windows PowerShell. Error code is -2145124329 When I try to Repair my SQL 2008 R2, I get the following error. How can I solve this?

TITLE: Microsoft SQL Server 2008 R2 Setup

The following error has occurred:

Setup failed to install Windows PowerShell. Error code is -2145124329

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.50.1600.1&EvtType=0x82728477%25400x237CA5E1%25401215%25400

BUTTONS:

OK

Well, that error code is:

  SUS_E_NOT_APPLICABLE -- install is not needed because no updates are
  applicable

(source)

This happens when you're trying to install a patch for an application and the patch no longer applies to the application.  For example, if the application is version 10 and the patch fixes version 9, the patch will fail with that error.

One possible fix is to uninstall and reinstall SQL Server.  Also, you could check to see if you have any patches or fixes installed already.  I know that Service Pack 1 is already out for SQL Server.  If you have that installed, it could be causing this error (trying to add an old patch to a newer version of PowerShell).


Is it good to create new indexes for better performance - SQL Server Hi I have a SQL Stored Proc that used to take 45 seconds to execute.

Then I run the Execution Plan under SQL and saw some high cost steps:

Then I right click that and select:

And finally I create the indexes that the Execution Plan recommend me.

Now the Query takes 2 seconds that is acceptable.

Is that right what I did? Does it come with extra cost?

  Any advice is well appreciated

I always take careful consideration when creating indexes on production systems, always try to create in DEV first. There are several reasons, but the top three for me are. First, there is a cost associated to indexes, essentially more overhead, so depending on your table the cost can be quite high. Second, I am never always privy to all the functionality that could be accessing that table/dataset, so I have seen negative results in other parts of an application due to adding indexes for other reasons. Third, if this is a data warehouse or a table that experiences frequent writes/loads, then you may actually negatively affect performance (unless you drop and add the indexes before and after).


Cross Join with Filter? i need to make Sp to distribute students to their sections the procedure take 2 string parameters StuID and SecID

in case I've send '1,2,3,4,5' as StuID and 'a,b' as SecID i'm using spliting function which well return tables

Tb1 |   Tb2
1   |    a
2   |    b
3   |
4   |
5   |

how can i get the following result

1 a
2 b
3 a
4 b
5 a
....

I've tried to do it via cross join but it did not show the result i want

select US.vItem as UserID,SE.vItem as Section
from split(@pUserID,',') us
cross join split(@pSectionID,',') se

you can do it by following

CREATE TABLE #Temp(id int)

insert into #Temp values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)

CREATE TABLE #Temp2(val varchar(2))

insert into #Temp2 values('a'),('b'),('c')

--Get temp table using split funtion

DECLARE @var int
SELECT @var = COUNT(0)FROM #Temp2

;WITH
    CTE AS (SELECT ROW_NUMBER()OVER(ORDER BY ID)AS SNO,* FROM #Temp),
    CTE2 AS (SELECT ROW_NUMBER()OVER(ORDER BY val)AS SNO,* FROM #Temp2)
SELECT * FROM Cte c JOIN CTE2 d ON d.SNO= CASE WHEN c.Sno%@var = 0 THEN @var ELSE c.SNO%@var END
ORDER BY c.SNO
;

DROP TABLE #Temp
DROP TABLE #Temp2


Need correct mysqlfrm command path to restore a database from .ibd and .frm files I'm attempting to restore a database made up of only .ibd and .frm files.
Why I'm doing this: An uninstall of my WAMP installation left only .ibd and .frm files, and was using MySQL 5.6.12. A reinstall of WAMP has MySQL 5.6.17. The WAMP people said if I restore the database in the 5.6.12 then I'll be able to choose the 5.6.12 setup from the Aestan tray menu.

Using this post Recover MySQL database from data folder without ibdata1 from ibd files by Ronaldo, I was able to learn of a routine using mysqlfrm to DISCARD TABLESPACE and then IMPORT TABLESPACE. While that post mentioned creating a database to start, I was informed by someone else here that the ibdata1 file from the 5.6.17 installation could be used in the 5.6.12 as part of the recovery. No database has been set up in the 5.6.17 installation. So I have a "generic" ibdata1 file, I assume.

My database is located at C:\wamp\bin\mysql\mysql5.6.12\data\wordpress.

I've read in this post How can extract the table schema from just the .frm file? where I assume the user put mysqlfrm.exe in the database folder as evident by this command example: C:\MySQL_5.6.10\data\mysql>mysqlfrm --server=root:rootpassword@localhost mysql:user.frm --port=3307 In the example, they're trying to read frm files. My goal is to to establish the right location to start running Ronaldo's instructions.

I put mysqlfrm.exe in the wordpress folder and I tried this command: C:\wamp\bin\mysql\mysql5.6.12\data\wordpress>mysqlfrm --server=root:rootpassword@localhost --port=3307 but I got a Fatal Python error: cannot get zip importer instance, and Windows gave me a message about the program stopping running.

Could someone share where I should put mysqlfrm.exe, and what my command should be to get to the point where I can run mysqlfrm to run Ronaldo's routines as listed in the first link above?

You cannot just move the .exe around like that and expect it to work. All libraries and dll's (in your case I suspect _bz2.pyd) have to be in the same directory structure.

You can execute commands from other locations by specifying the entire path or from any directory which is in your PATH environment variable.

You could either execute

C:\wamp\bin\mysql\mysql5.6.12\data\wordpress>c:\path\to\mysqlfrm --server=root:rootpassword@localhost --port=3307

or change your PATH


Scipt User roles and permissions I am having issues with the user accounts in SQL Server v 11.0.6020. I need to run a script that will allow for the copy of the principal user account: 'TrainID1' and all of the user rights and permissions so that when I get the other users logged in, they can access the DB's and run the applications that connect to the SQL DB's.
So I have the user: 'TrainID1', and that user connects to the SQL DB: 'TrainID_01' DB, so I need to have TrainID2 to 42 user accounts allowed in SQL Security for all of the other Data Bases like: TrainID_02, TrainID_03 and so on...

I recommend you have a look at the procedure sp_dbpermissions by Kenneth Fisher.

I have been using it and it allows you to list all the permissions of a particular user in a database, and gives you all the scripts to copy all those permissions and apply them on a different database.

In case you want to apply them on a different server as well, that is fine too, but don't forget to create the server login first.


Sybase Database Migration from 11.5 to 15 I have to migrate the Sybase Database from 11.5 version to 15.
I would like to know few things mentioned below -
i) What are the neccessary steps I need to follow in this migration.
ii) What are the changes required for table, Stored Proc, etc., for this change to work as per the current execution?
iii) Any bottle necks if exist for this transiton

Note :- We have a Unix server that executes these procs with the help of Perl Script?

Thanks
Utkarsh

Sybase ASE does not support a direct upgrade from version 11.5 to version 15, so you will have to export your database from the ASE 11.5, and import it to ASE 15.

From the $SYBASE/$SYBASE_ASE/scripts/ directory in your ASE 15 installation, run the installupgrade file into your ASE 11.5 installation, to install some utility stored procedures to assist with the migration.

isql -Usa -Ppassword -SOLD_SERVER_NAME -iinstallupgrade

Now login to each database, and run the sp_checkreswords command to make sure none of your DDL is using words reserved for the system.  If reserved words are found, you will need to alter the code, and remove/change the reserved words.

Use the ddlgen utility to export your full database structure (Tables, indexes, stored procedures, defaults, user defined datatypes, etc.)

At this time, you may want to review the code generated by ddlgen, to make sure it makes sense in your new environment.  You may want to make changes to device locations, device names and device sizes.  For example, ASE versions prior to 12.5 had a device size max of 2 Gb, so if your database was 10 Gb, you were forced to have 5 devices.  ASE 15 will allow device sizes of up to 1 Tb (on a server with a 2k page size), so you would only need one device to store the entire database.

You may also want to seperate the index and trigger creation scripts from the rest of the DDL, to allow you to run those independantly.  This comes in handy when you import data into the new server.

Once you are satisfied with the scripts, you can run them against the new server, and it will create all of your database structure on the new server.

At this point it's a matter of transitioning the database, using the bcp utility, you will need to export your data on a Table by Table basis.  Depending on the size of your databases, and the number of tables in each database, this can take a while.  I would recommend trying the -n (native) version of bcp instead of the -c (character) if it's possible.

To import the data into your new database, you will also want to use the bcp utility, but you will likely want to make sure that your databases do not have triggers or indexes as this will enable the 'unlogged' version of bcp to run, allowing it to run faster and not fill the transaction log as it runs.  If you can not run the unlogged bcp then you will want to make sure you have the appropriate database options ('truncate log on checkpoint', 'abort tran on log full') and set the batch size for your import to a reasonable value.

At this point, you can create in indexes and triggers (if they haven't been created up to this point), and start testing the server.

I would also recommend reviewing Sybase's Migration Guide, as it will likely go more in depth, and cover things I may have forgotten/skipped over.  A lot of the details will depend on the migration environement, so planning and preparation are key.


Running ratio of unique counts to total counts Any ideas on how to code a running ratio of counts (unique and total) for ordered observations (rows) in t-SQL (SQL Server 2017 Community Edition or higher)?

Given a data set, which is necessarily ordered (Ex. a time series indexed/ordered by date):

CREATE TABLE #x (dt DATE, Name VARCHAR(MAX));
INSERT INTO #x VALUES
('2012-01-01', 'a'),('2012-01-02', 'b'),('2012-01-03', 'c'),
('2012-01-04', 'a'),('2012-01-05', 'b'),('2012-01-06', 'd'),
('2012-01-07', 'a'),('2012-01-08', 'b'),('2012-01-09', 'e'),('2012-01-10', 'e');

So, given columns Dt and Names, I need the output Ratio below. For clarity, I also included output columns UCnt that counts unique names to date (i.e. a restricted running count) and TCnt counts all names to date (i.e. a simple running count). The output Ratio field is a just a running proportion of the two.

Dt          Name    UCnt    TCnt    Ratio
2012-01-01  a       1       1       1.000
2012-01-02  b       2       2       1.000
2012-01-03  c       3       3       1.000
2012-01-04  a       3       4       0.750
2012-01-05  b       3       5       0.600
2012-01-06  d       4       6       0.666
2012-01-07  a       4       7       0.571
2012-01-08  b       4       8       0.500
2012-01-09  e       5       9       0.555
2012-01-10  e       5       10      0.500

A combination of OUTER APPLY and ROW_NUMBER seems to do the trick:

WITH CTE AS
(
    SELECT  *,
            Tot_Cnt = ROW_NUMBER() OVER(ORDER BY dt)
    FROM #x
)
SELECT  A.dt,
        A.[Name],
        B.Unq_Cnt,
        A.Tot_Cnt,
        Ratio_of_Cnts = CONVERT(NUMERIC(10,4),B.Unq_Cnt)/A.Tot_Cnt
FROM CTE A
OUTER APPLY (SELECT Unq_Cnt = COUNT(DISTINCT [Name])
             FROM CTE
             WHERE dt <= A.dt) B
;

Here is a demo of this.


How to add constraint on a varchar column to have at least one non numeric character I am trying to implement some constraints on tables so that there is no tampering of the data from what is expected.

One of the constraints is on a varchar column. It should at least contain one non-numeric character.

How can I implement this constraint in SQL Server, and if I implement it, What will be the effect on the performance if any?

A check constraint with a LIKE expression could be used to ensure at least one non-numeric character exists:

CHECK (YourColumn LIKE '%[^0-9]%')

The 0-9 range there might not be exactly right for you, depending on the collation of the varchar column. For example, using the common Latin1_General_CI_AS collation, the following additional characters would be considered numeric by the 0-9 test:

²
³
¹
¼
½
¾

If that is a concern, expand the range explicitly:

CHECK (YourColumn LIKE '%[^0123456789]%')

I would expect negligible performance impact in either case.


MySQL binary logs - skip a table Is there a way to skip a specific table in the binary logs?

I've got binary logs configured as my incremental backup strategy between nightly backups. But we have one huge reference table that we're going to update nightly from a batch process (70GB table). This table is only read, and it's updated nightly using mysqlimport.

During the update process for this reference-only table the binary logs are causing a pretty serious delay (especially when I do a full 70GB update). It's got to do 70GB twice basically. yuck.

The reference table is currently using MyISAM (I chocked InnoDB trying to load it, so trying out MyISAM).

There is no mysql option to ignore a table in binary log.

If you could move the table to a separate database you might use binlog-ignore-db option, but this has its gotchas. You should understand how MySQL evaluates replication rules.

If you can write a wrapper script to load the data instead of mysqlimport (the manpage says that it's a command line interface to LOAD DATA INFILE), and the user you are importing the data with can have the SUPER privilege you might use sql_log_bin session variable like:

    SET @@session.sql_log_bin=0;
    LOAD DATA INFILE 'file_name'
      -- other LOAD DATA INFILE options ;
    SET @@session.sql_log_bin=1;

This will disable binary logging for the current session, load the data and enable binary logging again.


Cannot find aria_s3_copy utility in mariadb 10.5.5 I am not able to find the Aria utility - aria_s3_copy under bin. I use mariadb official docker image and I do find aria_chk but not aria_s3_copy.
Can you tell me where I can find it?

aria_s3_copy is in the mariadb-plugin-s3 package which isn't installed by the mariadb docker library container image.
Extending the docker container and installing this plugin package is possible.


How to return a result set from oracle procedure ? How can i return a result set from a stored procedure in oracle ?
suppose i want to return a table from a simple select statement such as this :

Select * from tblTest

I tried this but this seems plain wrong!

CREATE OR REPLACE
PROCEDURE ProcSelectEveryThing(cursor_ OUT TYPES.REF_CURSOR)
AS

BEGIN

OPEN cursor_ FOR
      SELECT * FROM "tblTest";
END;

what is wrong?

You don't need a return statement, because it is using OPEN and FOR... It is the same like RETURN...

The error is in the name of the table, you put it in "" double quotes.  Remove that, use simply the name of the table, and use the type  SYS_REFCURSOR like this:

CREATE OR REPLACE
PROCEDURE ProcSelectEveryThing(cursor_ OUT SYS_REFCURSOR)
AS

BEGIN

OPEN cursor_ FOR
      SELECT * FROM tblTest;
END;


Trace and analyze logs on SQL Server I am using an SQL Server 2012 on windows datacenter 2012

I have encountered a situation where i think some virus/sql injection had heart my database,

Some data on my table had changed to weird link's and it was 100% not coming from my system,

I wish to know what is the best way to track the origin of this change,
Is there a way to look in the transactional logs and checek which ip/machine had changed a table ? and what were the changes ?

Thanks.

EDIT :

Maybe is there some software like fiddler\wireshark that can trace the sql queries that are being sent to a server ?

SQL Injection is hard to track from SQL Server side. If you want to find out who change what, then some sort of tracing should be running on your server.

If no tracing (custom one implemented by your company DBA or sysadmin) is running, then you can check

windows event logs
sql server error logs
Default Trace for objects modified.
Successful and Failed logins (if you have enabled on sql server side).

Above all are few areas to look into, but it will be difficult for you to corelate and find a pattern.

Best is to scan your web-server IIS error logs using tools like LogParser.

Additional References :

SQL Injection: Defense in Depth
How to Secure Microsoft SQL Server


Grant permissions to allow user to check if session is on read replica or not Azure SQL Managed Instances offer access to read replicas from the availability group (in specific tiers at least).

I am writing a test script for our application to connect to read/write and read only connections to prove everything is functioning as it should.

I know we can use the databasepropertyex SP to identify the current database access level, which will be enough.

However, the application user is obviously limited in permissions - so I want to grant the lowest possible permissions to the application user that still allows that user to identify the connectivity type.

I am really struggling to identify what permissions I need to grant to the app user that will allow them to see the output of this command - currently it returns 'NULL' regardless of rights I have granted.

I have obviously missed something somewhere!

It appears that the issue was not permissions at all.

Thanks to this thread https://lists.ibiblio.org/pipermail/freetds/2010q2/025616.html I was pointed in the correct direction - our connection library was not able to handle the return type for the procedure gracefully, as it couldn't map the datatype.

SELECT CAST(DATABASEPROPERTYEX(DB_NAME(), 'Updateability') AS VARCHAR(40));

Returns the expected output.

For reference, to confirm the permissions that worked for us;

SQL User

Server Roles - public
Database Roles - public, db_datareader, db_datawriter


Error passing variable table type to a stored procedure I'm having a noob problem passing a data table type to a stored procedure.

Here is the implementation :

DECLARE @T TVARIABLE

    INSERT INTO  @T   VALUES ( '#var1','1')
    INSERT INTO  @T   VALUES ( '#var1','1')
    INSERT INTO  @T   VALUES ( '#var2','2')
    INSERT INTO  @T   VALUES ( '#var3','3')
    INSERT INTO  @T   VALUES ( '#var4','4')
    INSERT INTO  @T   VALUES ( '#var5','5')
    INSERT INTO  @T   VALUES ( '#var6','6')
    INSERT INTO  @T   VALUES ( '#var7','7')
    SELECT * FROM @T
    EXEC SP_NUEVO_VAR   @T

Here is the type I have created:

CREATE TYPE  [dbo].[TVARIABLE] AS TABLE
      (
         variable varchar(25),
         valor    varchar(25)
      )

Here is the stored procedure:

 CREATE PROCEDURE SP_NUEVO_VAR
    @T_Variable TVARIABLE READONLY
AS
BEGIN
BEGIN TRAN SAV_VAR
BEGIN TRY
    DECLARE @Output_T TVARIABLE
    DECLARE @variable       as varchar(50),
            @valor          as varchar(50),
            @folio_terminal as int,
            @new_var        as int,
            @sp             as varchar(50),
            @LOG            AS VARCHAR(Max)

    DECLARE VARIABLES CURSOR FOR SELECT * FROM  @T_Variable;
     SET @sp='SP_NUEVO_VAR'
     EXEC SP_LOGDB 'START',@sp

    SELECT @folio_terminal = folio_terminal FROM terminal
    --WHERE no_serie=@no_serie

    SET @LOG='@folio_terminal:['+CAST(@folio_terminal AS VARCHAR(10))+']'
     EXEC SP_LOGDB 'START',@sp

    OPEN VARIABLES
    FETCH NEXT FROM VARIABLES INTO @variable,@valor
      WHILE @@FETCH_STATUS = 0
       BEGIN
              SELECT @new_var=NEXT VALUE FOR dbo.seq_variable;
              INSERT INTO dbo.variables (id_var,   folio_terminal,  variable,   valor)
              VALUES                    (@new_var, @folio_terminal, @variable, @valor)
              FETCH NEXT FROM VARIABLES INTO  @variable,@valor
       END
       COMMIT TRAN SAV_VAR
END TRY
BEGIN CATCH
    ROLLBACK TRAN SAV_VAR
END CATCH
END
COMMIT
GO

When I run the implmentation I get this error:

  Mens 2766, Nivel 16, Estado 4, Procedimiento SP_NUEVO_VAR, Línea 12
  La definición del tipo de datos definido por el usuario 'TVARIABLE' ha cambiado.

The error in English is that the type of data defined by the user 'TVARIABLE' has changed.

When I use this stored procedure from C#, the stored procedure works FINE, but running the procedure in T-SQL is not working.

Sorry, I haven't noticed about something:

I use a script to erase all the database, to start all from zero, this script is useful to test my stored procedures, so in the stored procdure
SP_NUEVO_BD (in the code below), I drop the stored procedure SP_NUEVO_VAR, I drop the type TVariable, then I create the type, then I create the stored procedure.

Something is happening that I cannot run the full script, but when I run it individually it works:

Here is the script below:

USE foxtrot
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
-- Nueva Base de datos
SET NOCOUNT ON
EXEC SP_NUEVO_DB

-- Script de ejemplo, variables de ejemplo

DECLARE @no_serie as varchar (20),
        @folio_terminal as int,
        @err_cod as int,
        @folio_solicitud as int
        SET @no_serie='282-716-200'

-- Tabla de ejemplos de nuevas variables

-- Nuevo Registro de terminal ùnica

EXEC SP_NUEVO_TERMINAL   @no_serie, @folio_terminal OUT, @err_cod OUT

PRINT 'No de serie:['   +@no_serie+']' PRINT 'Folio terminal:['+CAST(@folio_terminal as varchar(20))+ ']' PRINT '@err_cod:['+CAST(@err_cod as varchar(20))+']'

-- Nuevo Registro de terminal, con sistema operativo, modelo

EXEC SP_NUEVO_REGISTRO_TERMINAL 'T00001','282-716-200','QTG5001','EOS123','VX610', @err_cod OUTPUT

PRINT 'err_cod:['+CAST(@err_cod as varchar(20))+']'

-- Script de nuevas variables

DECLARE @T TVARIABLE
INSERT INTO  @T   VALUES ( '#var1','1')
INSERT INTO  @T   VALUES ( '#var2','2')
INSERT INTO  @T   VALUES ( '#var3','3')
INSERT INTO  @T   VALUES ( '#var4','4')
INSERT INTO  @T   VALUES ( '#var5','5')
INSERT INTO  @T   VALUES ( '#var6','6')
INSERT INTO  @T   VALUES ( '#var7','7')
--SELECT * FROM @T
EXEC SP_NUEVO_VAR   @T_Variable=@T

Yu can see that the implmentation I refer in the first post is at the end of the script, when I executed indivudually it works fine, but when I run the complete script it doesn´t, considering I make all the process of deleting the stored procedure and the type, then I recreate it.


ORA-7445 Error on 12c I am installing an Oracle 12.1.0.1 Enterprise Edition on RHEL 6.4
I set it up with ASMM and HugePages  (64G of RAM)

There may be a correlation between the errors and signing in to em express (not 100% certain yet).  I mention this because of the following blog post with a similar error: http://surachartopun.com/2013/06/learn-something-wtih-12c-and-enjoy-bug.html

I have opened an SR already but wanted to see if anyone had thoughts on this.

Fri Aug 30 14:53:22 2013
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x5] [PC:0xB71F161, qervwRowProcedure()+113] [flags: 0x0, count: 1]
Errors in file /home/oracle/diag/rdbms/prdtdr1m/prdtdr1m/trace/prdtdr1m_s000_5987.trc  (incident=33811):
ORA-07445: exception encountered: core dump [qervwRowProcedure()+113] [SIGSEGV] [ADDR:0x5] [PC:0xB71F161] [Address not mapped to object] []
Incident details in: /home/oracle/diag/rdbms/prdtdr1m/prdtdr1m/incident/incdir_33811/prdtdr1m_s000_5987_i33811.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Aug 30 14:53:24 2013
Dumping diagnostic data in directory=[cdmp_20130830145324], requested by (instance=1, osid=5987 (S000)), summary=[incident=33811].
Fri Aug 30 14:53:33 2013
Sweep [inc][33811]: completed
Sweep [inc2][33811]: completed

Oracle Support instructed me to apply patch: 16527374


Assign different RAM usage (buffer pool size) per db in mysql I have quite some very large mysql databases (dev, production, staging, etc.) on a single server. Total size of indexes of all my databases is 64Gb.
However, the InnoDB Buffer Pool is only 16Go, meaning that often my indexes need to be loaded from disk, which destroys performance of my requests.
Index size of my production database is 16Go. Is there somehow I can tell mysql to keep my production db indexes in RAM ? E.g., by assigning buffer pool sizes per db.
Any other recommended strategy ?

I have written a few posts on loading index pages into the InnoDB Buffer Pool

Feb 04, 2012 : MySQL warm procedure
Nov 21, 2011 : Cache MySQL database in memory

Here is an example of a SELECT that will generate all possible SELECTs you would want to run to read InnoDB Index Pages:
SELECT DISTINCT
    CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
    ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
    FROM
    (
        SELECT
            engine,table_schema db,table_name tb,
            index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
        FROM
        (
            SELECT
                B.engine,A.table_schema,A.table_name,
                A.index_name,A.column_name,A.seq_in_index
            FROM
                information_schema.statistics A INNER JOIN
                (
                    SELECT engine,table_schema,table_name
                    FROM information_schema.tables WHERE
                    engine='InnoDB'
                ) B USING (table_schema,table_name)
            WHERE B.table_schema NOT IN ('information_schema','mysql')
            ORDER BY table_schema,table_name,index_name,seq_in_index
        ) A
        GROUP BY table_schema,table_name,index_name
    ) AA
ORDER BY db,tb
;

The main idea with these posts is this: Run SELECT queries whose columns are indexed. This will force index pages into the InnoDB Buffer Pool. Unfortunately, when doing SELECTs against non-indexed columns will push out old index pages out of the InnoDB Buffer Pool. There is no perfect solution for this but to get as many InnoDB index pages as possible.
You may want to limit the tables in the WHERE clause to a specific list of tables.
UPDATE 2021-02-02 10:13 EST
I have some additional advice: If the InnoDB tables don't change all that often, you may want to take a snapshot of the InnoDB Buffer Pool. This is on by default in MySQL 8.0.
For those using MySQL 5.7 and back, please do the following:
STEP 01 : Configure MySQL to Dump the InnoDB Buffer Pool on Shutdown and Load the InnoDB Buffer Pool on Startup
Add these two lines to my.cnf under the [mysqld] group header
[mysqld]
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup  = 1

STEP 02 : Configure Running Instance to Dump the Buffer on Shutdown
SET GLOBAL innodb_buffer_pool_dump_at_shutdown = 1;

STEP 03 : Dump the Buffer Pool Now
SET GLOBAL innodb_buffer_pool_dump_now = 1;

That's it. The dump of the Buffer Pool is just a binary file with a list of tablespace IDs. The size of such a file is usually in the 100K-120K range and dumps in seconds.


Trigram search gets much slower as search string gets longer In a Postgres 9.1 database, I have a table table1 with ~1.5M rows and a column label (simplified names for the sake of this question).

There is a functional trigram-index on lower(unaccent(label)) (unaccent() has been made immutable to allow its use in the index).

The following query is quite fast:

SELECT count(*) FROM table1
WHERE (lower(unaccent(label)) like lower(unaccent('%someword%')));
 count
-------
     1
(1 row)

Time: 394,295 ms

But the following query is slower:

SELECT count(*) FROM table1
WHERE (lower(unaccent(label)) like lower(unaccent('%someword and some more%')));
 count
-------
     1
(1 row)

Time: 1405,749 ms

And adding more words is even slower, even though the search is stricter.

I tried a simple trick to run a subquery for the first word and then a query with the full search string, but (sadly) the query planner saw through my machinations:

EXPLAIN ANALYZE
SELECT * FROM (
   SELECT id, title, label from table1
   WHERE lower(unaccent(label)) like lower(unaccent('%someword%'))
   ) t1
WHERE lower(unaccent(label)) like lower(unaccent('%someword and some more%'));

Bitmap Heap Scan on table1  (cost=16216.01..16220.04 rows=1 width=212) (actual time=1824.017..1824.019 rows=1 loops=1)
  Recheck Cond: ((lower(unaccent((label)::text)) ~~ '%someword%'::text) AND (lower(unaccent((label)::text)) ~~ '%someword and some more%'::text))
  ->  Bitmap Index Scan on table1_label_hun_gin_trgm  (cost=0.00..16216.01 rows=1 width=0) (actual time=1823.900..1823.900 rows=1 loops=1)
        Index Cond: ((lower(unaccent((label)::text)) ~~ '%someword%'::text) AND (lower(unaccent((label)::text)) ~~ '%someword and some more%'::text))
Total runtime: 1824.064 ms

My ultimate problem is that the search string comes from a web interface which may send quite long strings and thus be quite slow and may also constitute a DOS vector.

So my questions are:

How to speed up the query?
Is there a way to break it into subqueries so that it is faster?
Maybe a later version of Postgres is better? (I tried 9.4 and it does not seem faster: still the same effect. Maybe a later version?)
Maybe a different indexing strategy is needed?

In PostgreSQL 9.6 there will be a new version of pg_trgm, 1.2, which will be much better about this.  With a little effort, you can also get this new version to work under PostgreSQL 9.4 (you have to apply the patch, and compile the extension module yourself and install it).

What the oldest version does is search for each trigram in the query and take the union of them, and then apply a filter. What the new version will do is pick the rarest trigram in the query and search for just that one, and then filter on the rest later.

The machinery to do this does not exist in 9.1.  In 9.4 that machinery was added, but pg_trgm wasn't adapted to make use of it at that time.

You would still have a potential DOS issue, as the malicious person can craft a query which has only common trigrams.  like '%and%', or even '%a%'

If you can't upgrade to pg_trgm 1.2, then another way to trick the planner would be:

WHERE (lower(unaccent(label)) like lower(unaccent('%someword%')))
AND   (lower(unaccent(label||'')) like
      lower(unaccent('%someword and some more%')));

By concatenating the empty string to label, you trick the planner into thinking it can't use the index on that part of the where clause.  So it uses the index on just the %someword%, and applies a filter to just those rows.

Also, if you are always searching for entire words, you could use a function to tokenize the string into an array of words, and use a regular built-in GIN index (not pg_trgm) on that array-returning function.