Mysqlbinlog statement reading I am trying to perform a point in time on a specific database using a snapshot and mysql's transaction logs. I am using the following to pull statements from the time of the snapshot to the time of recovery:

mysqlbinlog --database=**database** --start-datetime="$start_datetime" --stop-datetime="$stop_datetime" list_of_binlog_file_names

The resulting statements that mysqlbinlog produces include INSERT/UPDATE/DELETE statements for another database that has the form:

INSERT INTO **database**_reporting.tablename VALUES (data);

So functionally I'm getting statements from 2 different databases, one is our production database, the other is our reporting database, differentiated by nomenclature by the '_reporting'. It would appear to me that our application is inserting to the secondary database while still using the primary one and the binlogs associate both statements with the primary database.

Am I correct that mysqlbinlog is going to read out statements for both databases as long as they are executed after a USE primary_database;? Is there a way to separate out the ones for the reporting database using mysqlbinlog? I have searched quite a bit- I may be barking up the wrong tree here. Let me know if I can clarify the question.

Since the --database option does not screen out queries that explicitly name the database with the table, you should redirect that to grep and locate primary_database and INSERTs, UPDATEs, DELETEs:

mysqlbinlog --database=**database** --start-datetime="$start_datetime" --stop-datetime="$stop_datetime" list_of_binlog_file_names | grep "primary_database\." | grep "[IUD][NPE][SDL][EA][RT][TE]"

This may not be a perfect solution. Perhaps a good perl or python script and nail this one.


How to select nodes where all children is satisfied? I have a tree structure of light bulbs. I want to turn on all the light bulbs starting from the leafs of the tree. A light bulb cannot be turned on unless all its immediate children are turned on.

The relation between the nodes in the tree structure is represented by table A:

Table A:

  node_id integer
  child_node_id integer

Table B represents the nodes in the tree:

Table B:

  id integer
  state boolean

The state of table B represents the states true = on and false = off.

Question:
I would like to select all the light bulbs which are turned off AND has all immediate children turned on.

This is probably very simple, but I can't seem to get my head around it.

I would first rethink the design. You only need one table:

CREATE TABLE c (
  id integer PRIMARY KEY
 ,parent_id integer REFERENCES c(id)
 ,state boolean
);

With the layout as presented in the question, the query could be:

SELECT DISTINCT a.*
FROM   a
JOIN   b ON a.node_id = b.id
LEFT   JOIN a ca ON ca.child_node_id = a.node_id
LEFT   JOIN b cb ON cb.id = ca.node_id AND cb.state = FALSE
WHERE  b.state = FALSE
AND    cb.id IS NULL

This includes nodes that are turned off and have no children at all.
To exclude nodes without children, replace the first LEFT JOIN with a plain JOIN.

Or, may be faster:

SELECT a.*
FROM   a
JOIN   b ON a.node_id = b.id
WHERE  NOT EXISTS (
   SELECT 1
   FROM   a ca
   JOIN   b cb ON cb.id = ca.node_id
   WHERE  ca.child_node_id = a.node_id
   AND    cb.state = FALSE
   )


DESCRIPTION_LIST in tnsnames.ora How does this work? If I specify an address list, I know I can specify a load balance or failover option to switch the connection to the other address.

But in case of various descriptors (DESCRIPTION {...}) what's happening? Is it a random choice from the connector?

Which Database Version do you have?

From Oracle 11g onward you should be using the SCAN listener. The SCAN listener knows where the database is located in the RAC and how much load is on that node.

Further more you should define services for long ops and short ops for better load balancing.


Why do I get "Truncated incorrect date value" when using str_to_date()? I have data in one of my tables as "Sun Dec 14 22:49:13 2014 -0800" and need to load it into another table but skipping the time filed i.e. HH:MM:SS.

I used:

insert into final_data
select Book_Name, Author_Name, Author_email,
str_to_date(substring_index(date_string,' ',4),'%a %b %d %Y'),
Total_Sold
from data;

  ERROR 1292 (22007): Truncated incorrect date value: 'Sun Dec 14 22:49:13'

I can use str_to_date(substring_index(date_string,' ',5),'%a %b %d %T %Y') but that will add HH:MM:SS in the column as well.
Is there a way to load the data skipping HH:MM:SS ?
i.e instead of 2014-12-14 22:49:13 it diplays 2014-12-14

you have to pass your datetime column into date() function like this :
create table test (
  id int AUTO_INCREMENT,
  datetime_string datetime,
  date_date date,
  PRIMARY KEY (id)
);

insert into test(datetime_string) values('2014-12-14 22:49:13');
update test set date_date = date(datetime_string);

http://sqlfiddle.com/#!9/cc200b/1


Defining constraints in `CREATE TABLE` statements Recently I have been using a Database Abstraction Layer built by a Python web-framework called web2py (click for their DAL syntax). They include the option to include your constraints within the CREATE TABLE statement.
Whilst taking Stanford's "Introduction to Databases" MOOC, the SQL Standard was mentioned as supporting any query within the CREATE TABLE statement as constraints (essentially replacing a major use-case for triggers).
What is best practice?
Below is a simple example of including constraints in CREATE TABLE statements; rather than through ALERT TABLE and/or CREATE TRIGGER statements:
CREATE TABLE Place (
    address VARCHAR2(40),
    CONSTRAINT place_pk
        PRIMARY KEY (address)
);

CREATE TABLE Company (
    c_name VARCHAR2(40),
    CONSTRAINT company_pk
        PRIMARY KEY (c_name)
);

CREATE TABLE Employee (
    e_name VARCHAR2(40),
    tax_no NUMBER,
    salary NUMBER(19,4),
    sex CHAR,
    birthdate DATE,
    address VARCHAR2(40),
    CONSTRAINT employee_pk
        PRIMARY KEY (tax_no),
    CONSTRAINT address_fk
        FOREIGN KEY (address) REFERENCES Place(address),
    CHECK (address IS NOT NULL)
);

CREATE TABLE CompanyEmployee (
    employee_id NUMBER,
    company_id VARCHAR2(40),
    CONSTRAINT unique_employee_id
        UNIQUE(employee_id),
    CONSTRAINT employee_id_fk
        FOREIGN KEY (employee_id) REFERENCES Employee(tax_no),
    CONSTRAINT company_id_fk
        FOREIGN KEY (company_id) REFERENCES Company(c_name),
    CONSTRAINT company_employees_pk
        PRIMARY KEY (employee_id, company_id)
);

BTW: You'll note that I'm using CAPS for keywords, upper CamelCase for table names and lower under_score for attribute and trigger names. Is this good practice? - Feel free to critique my indentation and whitespace usage styles also :)

I personally think it's a matter of taste.

The scripts where the constraints are defined through an ALTER statement are a bit more flexible, as you don't need to care about the order of creation (first create all tables, then all PKs, then all FKs).

The scripts with embedded constraints are more "self-contained" however. You don't need to look for other places in the script to find the constraint definitions.

Using camel-case doesn't make any difference as everything will be converted to uppercase anyway (because you did not use " to quote your names - which is a good thing).

Regarding indention and whitespace: that again is completely personal taste. Do it the way you think you can still understand this in 6 months. The most important thing is to be consistent (and maybe document your style somewhere in your project documentation, so that new members can understand and use the same formatting)


Query to extract all SQL database roles with INSERT and/or UPDATE privileges I've taken over the support for a SQL 2008 database and there are a multitude of custom roles (25+) associated within. I'm trying to figure out which of the custom roles have the ability to perform privileged INSERT and/or UPDATE actions within the database.

Can anyone help me write a query to run against the database which would return a listing of all roles with INSERT and UPDATE privileges?

Any help would be much appreciated, Thank you!

Here is a starting point.  The first query will give you a list of roles with either INSERT or UPDATE permission.  From there you will need to look at the permissions being at the DATABASE, SCHEMA, or OBJECT level and decide if they cover the objects you are interested in.  Check the state_desc column to make sure it is GRANT (it probably will be) as DENYs are obviously the opposite of what you want.

The second query will tell you any role that is a member of the db_datawriter role.  These roles will also have INSERT and UPDATE permissions.

SELECT dprin.name, dperm.*
FROM sys.database_permissions dperm
JOIN sys.database_principals dprin
    ON dperm.grantor_principal_id = dprin.principal_id
WHERE permission_name IN ('INSERT','UPDATE')
  AND dprin.type = 'R'

SELECT drole.name as role_name, dprin.name as role_member
FROM sys.database_role_members rmem
JOIN sys.database_principals drole
    ON drole.principal_id = rmem.role_principal_id
JOIN sys.database_principals dprin
    ON dprin.principal_id = rmem.member_principal_id
WHERE drole.name = 'db_datawriter'
  AND dprin.type = 'R'


SQL Clustering Preferred Owners I noticed that in the sql failover clustering under the possible owners none of the nodes has been checked, I have a two node cluster does that mean in the event of failover it will failover to any of the active nodes. Whats is the difference if both of the nodes are checked vs if both of the nodes are unchecked as possible owners.

I noticed that in the sql failover clustering under the possible owners none of the nodes has been checked, I have a two node cluster does that mean in the event of failover it will failover to any of the active nodes.

You only have two nodes. If there is a failover... there is only one node to fail to, so having preferred owners set is... kind of a wash. It doesn't get you anything, to be quite honest.

  Whats is the difference if both of the nodes are checked vs if both of the nodes are unchecked as possible owners.

In a two node cluster there is no difference. There is only one other node to go to so it doesn't make much sense.

Now, assume you had an X node cluster where X is > 2 and you're still using SQL FCI's (Note: AGs work differently, don't check or uncheck these values).

Now, if you choose "Move service to best possible..." it'll go to a preferred first, additionally if there were a fail over it'd move to a preferred first as well. Since it's all about preference and not about actual possible owners, having a preference set is unlikely going to really change anything, in my honest opinion.

Some will argue about fail back, which I don't like to enable and requires there to be a cluster event for failback to occur anyway... so it's a wash.


Will 'sleeping' connections open files other than the socket in MySQL? Recently, in our MySQL replication setup, the replication slave crashed due to 'too many open files' error. We brought it backup, but trying to figure out what caused the unexpected hike in the number of open files. We habitually see too many connections in 'sleep' status. Would these 'sleeping' connections open files other than the sockets they keep open?

MySQL version is 5.6.20. OS is RHEL 6.4

Edit: Our MySQL is started with a huge number of 'open-files' limit and the OS has already allowed mysql user to open that many number of files. I am doing an RCA and would like to know whether the sleeping connections would open files other than the network socket they open.

SHOW VARIABLES LIKE '%open%';

Probably one of those is too low.

At a shell prompt, do

ulimit -n

That will tell you what the OS is letting you have.  In some recent OS builds, it was the ridiculously low value of 1024.  If so, increase it to, say, 16000.  Then restart mysqld and see if the SHOW increased the values.  (Some default values are computed.)  If it did not, then go into my.cnf to increase some things.

"Sleep" processes may be hanging onto open files, but they won't be getting more.

Do you have a large max_connections?

Do you have lots of tables?  Lots of PARTITIONs?  Each PARTITION uses 1-3 files.  (This is one of several reasons for not breaking a table into more than, say, 50 partitions.)

Oh.  You are using MyISAM.  It needs 3 files per table.  Switch to InnoDB, with innodb_file_per_table = 1 -- that way you need only 1 file per table.


Select over 2 select queries I have two tables:
work:

id  w_date        other stuff
1   2017-05-10
1   2017-05-08
3   2017-05-17

home:

id  h_date       other stuff
1   2017-05-01
4   2017-05-06
1   2017-05-14

Now I need a query that I get all dates where id is 1.

Something like:

Select *
from (
    select w_date as date
    from work where id=1
),
  (select h_date as date from home where id=1)

Result should be:

id  date
1   2017-05-01
1   2017-05-08
1   2017-05-10
1   2017-05-14

Any ideas?

This is a simple UNION query:

SELECT id, date
FROM work
WHERE id = 1
UNION ALL
SELECT id, date
FROM home
WHERE id = 1
ORDER BY date;

I've used the UNION ALL syntax which return all rows from both source tables.  Leaving the ALL keyword out ensures rows that are duplicated in both tables are only returned a single time.

I've added an ORDER BY clause at the end of the query to return the list in date ascending order, since your sample data seems to indicate that.

See the Wikipedia entry for further details.


Cannot drop column : needed in a foreign key constraint I have a table with two foreign key constraints as below:

mysql> SHOW CREATE TABLE `user`;

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `region_id` int(11) unsigned DEFAULT NULL,
  `town_id` int(11) unsigned DEFAULT NULL,
  `fullname` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `username` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `password` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `active` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `FK_G38T6P7EKUXYWH1` (`region_id`),
  KEY `FK_J8VWK0ZN7FD2QX4` (`town_id`),
  CONSTRAINT `FK_G38T6P7EKUXYWH1` FOREIGN KEY (`region_id`) REFERENCES `region` (`id`) ON UPDATE NO ACTION,
  CONSTRAINT `FK_J8VWK0ZN7FD2QX4` FOREIGN KEY (`town_id`) REFERENCES `town` (`id`) ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I can't drop a foreign key column although I disable FOREIGN_KEY_CHECKS.

mysql> ALTER TABLE `user` DROP COLUMN `region_id`;
1553 - Cannot drop index 'FK_G38T6P7EKUXYWH1': needed in a foreign key constraint

mysql> SHOW VARIABLES LIKE 'FOREIGN_KEY%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+
1 row in set

mysql> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected

mysql> SHOW VARIABLES LIKE 'FOREIGN_KEY%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | OFF   |
+--------------------+-------+
1 row in set

mysql> ALTER TABLE `user` DROP COLUMN `region_id`;
1828 - Cannot drop column 'region_id': needed in a foreign key constraint 'FK_G38T6P7EKUXYWH1'

Having a look at MySql docs I've found a warning about foreign_key_keys:

  Warning
  With foreign_key_checks=0, dropping an index required by a foreign key constraint places the table in an inconsistent state and causes the foreign key check that occurs at table load to fail. To avoid this problem, remove the foreign key constraint before dropping the index (Bug #70260).

IMHO you should drop FOREIGN KEY before DROP the COLUMN.

ALTER TABLE `user` DROP FOREIGN KEY `FK_G38T6P7EKUXYWH1`;
ALTER TABLE `user` DROP COLUMN `region_id`;

I've set up a rextester example, check it here.


Function Performance Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company.

One of the things I would like to do is move some of the application logic into stored procedures, so I'm here asking for DOs and DON'Ts (best practices) on using functions in PostgreSQL (9.0), specifically regarding performance pitfalls.

Strictly speaking, the term "stored procedures" points to SQL procedures in Postgres, introduced with Postgres 11. Related:

When to use stored procedure / user-defined function?

There are also functions, doing almost but not quite the same, and those have been there from the beginning.

Functions with LANGUAGE sql are basically just batch files with plain SQL commands in a function wrapper (and therefore atomic, always run inside a single transaction) accepting parameters. All statements in an SQL function are planned at once, which is subtly different from executing one statement after the other and may affect the order in which locks are taken.

For anything more, the most mature language is PL/pgSQL (LANGUAGE plpgsql). It works well and has been improved with every release over the last decade, but it serves best as glue for SQL commands. It is not meant for heavy computations (other than with SQL commands).

PL/pgSQL functions execute queries like prepared statements. Re-using cached query plans cuts off some planning overhead and makes them a bit faster than equivalent SQL statements, which may be a noticeable effect depending on circumstances. It may also have side effects like in this related question:

PL/pgSQL issues when function used twice (caching problem ?)

This carries the advantages and disadvantages of prepared statements - as discussed in manual. For queries on tables with irregular data distribution and varying parameters dynamic SQL with EXECUTE may perform better when the gain from an optimized execution plan for the given parameter(s) outweighs the cost of re-planning.

Since Postgres 9.2 generic execution plans are still cached for the session but, quoting the manual:

  This occurs immediately for prepared statements with no parameters;
  otherwise it occurs only after five or more executions produce plans
  whose estimated cost average (including planning overhead) is more
  expensive than the generic plan cost estimate.

We get best of both worlds most of the time (less some added overhead) without (ab)using EXECUTE. Details in What's new in PostgreSQL 9.2 of the PostgreSQL Wiki.

Postgres 12 introduces the additional server variable plan_cache_mode to force generic or custom plans. For special cases, use with care.

You can win big with server side functions that prevent additional round-trips to the database server from your application. Have the server execute as much as possible at once and only return a well defined result.

Avoid nesting of complex functions, especially table functions (RETURNING SETOF record or TABLE (...)). Functions are black boxes posing as optimization barriers to the query planner. They are optimized separately, not in the context of the outer query, which makes planning simpler, but may result in less than perfect plans. Also, cost and result size of functions cannot be predicted reliably.

The exception to this rule are simple SQL functions (LANGUAGE sql), which can be "inlined" - if some preconditions are met. Read more about how the query planner works in this presentation by Neil Conway (advanced stuff).

In PostgreSQL a function always automatically runs inside a single transaction. All of it succeeds or nothing. If an exception occurs, everything is rolled back. But there is error handling ...

That's also why functions are not exactly "stored procedures" (even though that term is used sometimes, misleadingly). Some commands like VACUUM, CREATE INDEX CONCURRENTLY or CREATE DATABASE cannot run inside a transaction block, so they are not allowed in functions. (Neither in SQL procedures, yet, as of Postgres 11. That might be added later.)

I have written thousands of plpgsql functions over the years.


Alias for multiple instances on a SQL Server I have 2 SQL named instances on a SQL Server, trying to add aliases for both so that application teams can use the alias names in their connection strings.

There are 2 IP Addresses assigned for the server. 10.10.1.1, 10.10.1.2

I've assigned 10.10.1.1 to ServerName\Instance1 and 10.10.1.2 to ServerName\Instance2.
Ports for the instances are 1433 and 1435 respectively

I have created 2 aliases from the SQL Server Configuration Manager:
Test1, Test2

A DNS entry has also been created with IP Address specified for each alias.

Connections work only with Test1. I or the application team is unable to connect to Test2 instance.

How do I fix this?

It's a little tricky with multiple IP addresses and different ports when configuring SQL Server. The most important bit, is that the whole configuration has to be done with the SQL Server Configuration Manager.

Open up the SQL Server Configuration Manager with Runas Administrator...
Navigate through the SQL Server Network Configuration branch and you should find two sub-branches for ...
a. Protocols for NAMED_INSTANCE_1
b. Protocols for NAMED_INSTANCE_2

Repeat the following steps for each instance:

Click on the Protocols for NAMED_INSTANCE_n branch and in the right pane right-click on the TCP/IP Protocol Name entry.
Open up the properties.
In the Protocol register verify the following:
a. Enabled = Yes
b. Listen All = No
Switch to the IP Addresses register
You should find sections in the form of IPn for each of your IP addresses 10.10.1.1 and 10.10.1.2. This is the tricky bit: You are already in an instance and want to configure only one IP address for this instance.
In the IPn section corresponding to the IP address for this instance check the following:
a. Active = Yes
b. Enabled = Yes
c. IP Address = 10.1.0.n
d. TCP Dynamic Ports = leave empty
e. TCP Port = 1433
Scroll down to the IPAll section
Verify the following:
a. TCP Dynamic Ports = leave empty
b. TCP Port = leave empty
In all other IPn sections ensure the following:
a. Enabled = Yes
b. Active = No (unless you want to communicate with the instance via the IP configuration settings in the section).

Repeat from step 3. for each instance.

You have to restart the instance and then it will only pick up on the IP address and port configured.

We have SQL Servers with up to 10 instances configured this way, but we leave the port at 1433 so that each instance will pick up on the default port. This allows for easier firewall configuration.

Good luck.


Login failed for user 'domain\username'. (Microsoft SQL Server, Error: 18456) I am facing this problem when connecting to local system db, but when I connect any server it connects.

Login failed for user 'domain\username'. (Microsoft SQL Server, Error: 18456)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

Scenario - I was connecting my local SQL server by using SQL authentication.
                 I got an error code: 18456.
After digging a bit i found the solution -
SQL server error: 18456 overview - This error occurs when your authentication request is been successfully accepted by SQL server named, but due to some reason SQL server is not able to grant the access to connect.

Reason - This error reveals that SQL server authentication is not enabled in local server.

To enable SQL server authentication, Follow the below steps

Step 1 - Go to SQL server properties by Right click -> Properties.

Step 2 - Enable SQL server authentication mode.

Step 3 - Restart the SQL server.

I hope it will help, Check here for more detail


SQL Agent Job step using Powershell can not find Network Share I have a SQL Job step running powershell that cannot find a network drive in one of its steps For brevity, I am calling this server Production, the network share is D:\ on Production and the Job is running on Production. The powershell Job step with issues in running under the SQL Server Agent Service Account.

The first Job step creates an input file on a network share. The second Job step queues an email based on information in the first steps output file. This job is run on server we will call Production. The network share is based the server were the Job is run specifically what we will call here the D:\ drive. When writing the Job step if I use SELECT-STRING .. -Path "\\network share.." my job fails it cannot find the Path, but if I use SELECT-STRING .. -Path "D:.. it runs successfully!

I kept the Job tokens in the code below in case the option for creating a script comes up. I have a solution here, but there is a possiblity I will need to use this script on a different server that does not a have a D:\ and I want to use the network share name for maintainability.

First Job step creates file(run as CmdExec):

SQLCmd -S <server> -U <me> -P <Passw0rd> -d <DB> -w 1000 -m-1 -b -i "\\<network share>\Results\20120117_02 Err Message Monitor.sql" -o "\\<network share>\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt"

Second Job step run as Powershell(specific problem line listed below)

$sel = SELECT-STRING "Nothing to do" -path "\\<network share>\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt"
if ($sel -eq $null) {
    Invoke-Sqlcmd -Query "
SET QUOTED_IDENTIFIER ON;
EXEC DBATools.dbo.s_DBAEmailINS
    @ToList = 'My@email.com',
    @Subject = '(Prod) Message Monitor',
    @AttachmentList = '\\<network share>\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt';"
}
else {
    Invoke-Sqlcmd -Query "
SET QUOTED_IDENTIFIER ON;
EXEC DBATools.dbo.s_DBAEmailINS
    @ToList = 'Me@email.com',
    @Subject = 'Nothing to do (Prod) Message Monitor',
    @AttachmentList = '\\<network share\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt';"
}

Problem is here

$sel = SELECT-STRING "Nothing to do" -path "\\<network share>\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt"

This works:

$sel = SELECT-STRING "Nothing to do" -path "D:\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt"
if ($sel -eq $null) {
    Invoke-Sqlcmd -Query "
SET QUOTED_IDENTIFIER ON;
EXEC DBATools.dbo.s_DBAEmailINS
    @ToList = 'My@email.com',
    @Subject = '(Prod) Message Monitor',
    @AttachmentList = '\\<network share>\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt';"
}
else {
    Invoke-Sqlcmd -Query "
SET QUOTED_IDENTIFIER ON;
EXEC DBATools.dbo.s_DBAEmailINS
    @ToList = 'Me@email.com',
    @Subject = 'Nothing to do (Prod) Message Monitor',
    @AttachmentList = '\\<network share\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt';"
}

There are no Proxy accounts.

How can I get my -Path to access a network share in this Job?

I cannot explain why this is the answer but it is:

$sel = SELECT-STRING "Nothing to do" -path "D:\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt"

Changed to:

New-PSDrive -Name "P" -PSProvider FileSystem -Root "\\<network share\Results"
$sel = SELECT-STRING "Nothing to do" -path "P:\Ad-hoc\Results\20120117_02\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt"

Runs successful! There were NO permission changes but I guess powershell needs a [temporary with PS-Drive] local path to run. Again ZERO permission changes and if I were to replace the "P:\" with the "\\network share\Results" it would fail.

This is what I needed. The network share name will not be changing but a drive letter on the server can change so this is maintainable for me and reusable in my other Jobs.


Always On shared network location To use the wizard from SSMS, we had to provide full access to "everyone" to the shared network location.

Is there a way to avoid this and just limit the access to a specific user?

Already tried the service account but that seems not to be working.

The message received from the wizard is:

\\Hostname\sync' is not a valid shared network location.
(Microsoft.sqlserver.Management.hadr Tasks)

Well it took a while but finaly found it myself. Besides the service account it also requires full access for the user of SSMS. Sounds obvious probably and indeed I thought about it earlier but since the account was already in the list of administrators and this group already had full access to the shared volume I thought it would have been enough. So what was required was just adding the SSMS user/group and service account to the advanced sharing of the SYNC folder.


logical reads vs. scan count I'm joining a small table (1,000 rows) against a large table (8M rows) in SQL Server 2008. The join uses a nonclustered covering index on the large table, and the join can produce three possible query plans. I'm trying to figure out which plan is better, but also I want to generalize this knowledge so next time I can better know what heuristics to use when looking at SQL I/O statistics.

Plan #1 is a loop join and emits statistics for the large table like this:

Scan count 2582, logical reads 35686, physical reads 1041, read-ahead reads 23052

Plan #2 is a merge join and emits statistics like this:

Scan count 1, logical reads 59034, physical reads 49, read-ahead reads 59004

Plan #3 is a hash join and emits statistics like this:

Scan count 3, logical reads 59011, physical reads 5, read-ahead reads 59010

The covering index is ordered by (ID, Date). The query returns data for about 50% of the IDs and, for each ID, returns a contiguous chunk of the most recent 3 months of data, which is usually about 1/4 or the rows for each ID.  The query returns about 1/8 of the total rows in the index.  In other words, the query is sparse but consistently so.

My assumption is that plan #1 is awful for this workload, because moving the disk head around 2,500 times (or even 1,041 times) is far more expensive than a sequential disk scan.  I also assume that #3 and #2 have similar, sequential (and therefore more efficient) I/O patterns.

But is there a case where plan #1 is really best, where "best" means less impact on the I/O subsystem and less impact on other queries running concurrently?

Or does it really depend on many variables like the kind of disk subsystem I have, index fragmentation, etc. If "it depends" are there any rules of thumb to approach the problem?

Here is the killer deal: it January it was costing $12k to buy 864*GB* of RAM. You can get a lot of bang for the buck by simply increasing the RAM of your server up to the point that you will never hit a physical read (after warm up, of course).

Other than that is really hard to give an black or white opinion about either of those data points you present. Sure plan #1 had most physical reads, but are you positive that all tests were done on similarly warmed up cache? Could it be that #1 warmed up the cache for #2, what is your test methodology to ensure all cases are considered on level ground? Even so, if you shell out $500 and double the RAM, would it matter any more? #1 does have the least logical reads...

But then #2 is probably benefit from a high DOP (that one scan can be parallel). Is the wall-clock time of #2 better that #1 after you added sufficient RAM?

How many of these plans run in parallel? Are there tens of queries requesting concurrently a significant memory grant for the hash of #3 and thus creating contention for the RESOURCE_SEMAPHORE? Is the #2 doing a sort and also requesting a memory grant? Will #1 work better since it requires no grant (at least from the info posted...)?

Is really really relative and the question you ask is more like finding one solution for a complex system of equations... there simply could be more that one solutions.

One things is sure: 8M rows should fit in RAM with plenty room to spare. Those physical reads are begging for some memory banks.


Connect to MySQL (WAMP) via SSH Hi I installed the latest WAMP on Windows 7. I would like connect via SSH to MySQL database and create dump.

As SSH client I use PUTTY.

I tried using the root account and also other accounts, but I get this error:

  server unexpectedly closed network connection

Then I tried using another SSH client Private Shell http://www.privateshell.com/

I get this error:

  other side does not support SSH protocol on this port

For connection I use these settings:

IP: localhost
PORT: 3306

MySQL version is 5.5.16 I am not sure if support SSH connection.

Is it needed to allow SSH in phpAdmin or what is the root of problem?

Is there a SSH server on your machine?? If not you may install copssh - OpenSSH for Windows or similar and then try to connect.


Find if column exist in certain tables I want to find out all tables having a column let's say test. How to obtain this? I need DBName, TableName, testcolumnexist(Yes/No), TableCreateDate and ProductName details.

The tricky part is tablenames. I have tables with IDs appended at the end. Below is the entire scenario:

bColumn to find = test
Tables = Logs#, Details#, Data#

Where # represents IDs coming from different table master. i.e. select id from master where id< 5).

So my tables are like Log1, Details1, Data1 for ID = 1, Log2, Details2, Data2 for ID = 2 and so on

Output: DBName, TableName, testcolumnexist(Yes/No), TableCreateDate, ProductName

Note that ProductName column exist in master table.

This will give you the Schema and Table names along with YES or NO and Creation date:

SELECT s.name As [Schema]
    , t.name as [Table]
    , CASE WHEN c.name IS NULL THEN 'NO' ELSE 'YES' END as 'Exists'
    , t.create_date
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
LEFT JOIN sys.columns c ON c.object_id = t.object_id AND c.name = 'test'

How the master table works is unclear but it seems that adding such a WHERE clause should work:

WHERE t.name IN (
    SELECT tn.name + CAST(mn.id as varchar(10)) as name
    FROM (values('Logs'), ('Details'), ('Data')) as tn(name)
    CROSS JOIN [master] mn
)

This will mix the list of name with the list of ids from master. Names can be added to the list or it could be replaced by a Names table.

By the way, master is not a proper name for a table since it is also a system database name.

This query adds productname from sample @master table for id < 5:

declare @master table (id int, productname varchar(10))
insert into @master(id, productname) values(1, 'xxx'), (2, 'yyy'), (6, 'yyy')

SELECT s.name As [Schema]
    , t.name as [Table]
    , CASE WHEN c.name IS NULL THEN 'NO' ELSE 'YES' END as 'Exists'
    , t.create_date
    , m.productname
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN (
    SELECT name = tn.name + CAST(mn.id as varchar(10))
        , mn.productname
    FROM (values('Logs'), ('Details'), ('Data')) as tn(name)
    CROSS JOIN @master mn
    WHERE mn.id < 5
) m ON m.name = t.name
LEFT JOIN sys.columns c ON c.object_id = t.object_id AND c.name = 'test'


High number of Checkpoint pages/sec and memory pressure Recently I read a blog post on mssqltips.com about memory bottlenecks on SQL Server. In this article I read following:

  The following performance counters on SQL Server: Buffer Manager object can also indicate memory pressure:

  High number of Checkpoint pages/sec
  High number of Lazy writes/sec
  High number of Page reads/sec
  Low Buffer cache hit ratio
  Low Page Life Expectancy

What caught my attention was that a high number of 'checkpoints pages/sec' can indicate memory pressure.

My understanding was that a checkpoint writes 'dirty' pages to disk. This can be triggered in different ways.

automatic (to maintain recovery interval)
indirect (to maintain target recovery time)
manual
internal

So high number of checkpoints indicates a very busy system (in case of automatic and indirect checkpoints).
Because a checkpoint never removes a page from the buffer cache I don't quite understand how high number of checkpoints/sec can indicate memory pressure. If there is memory pressure I would except to see a high number of 'lazy writes/sec'. The lazy writer removes 'cold pages' from memory, to make place for new pages.

How does a high number of checkpoint pages/sec indicate memory pressure?

How does a high number of checkpoint pages/sec indicate memory pressure?

It does not directly indicate memory pressure. The value itself is cumulative and does not give much insight into memory pressure.

In the blog mentioned by you I would not rely on following 2 counters for memory pressure.

Buffer cache hit ratio(BCHR)
Checkpoint pages/sec

For BCHR the reason is already explained by Jonathan Kehayias in This Blog. It would give you incorrect interpretation if you rely on it because of SQl Server feature of Read Head.

The work of checkpoint in SQL Server is to decrease the overall recovery time of SQL Server. More frequent checkpoint means less amount of transaction log record needed to roll forward and hence shorter recovery. So I would not equate high checkpoint pages/sec to memory bottleneck. It would need further analysis or simply on a very busy system it can be ignored. It is just the SQL Server trying hard to make sure when database goes through crash recovery it comes online in minimum time letting you meed the RTO.

If I would want to look at memory pressure I would fire following perfmon counters

SQLServer:Buffer Manager object Page reads/sec
SQLServer:Buffer Manager object Free Pages
SQLServer:Buffer Manager object Page Life Expectancy
SQLServer:Buffer Manager object Free List Stalls/sec
Memory Manager: Total Server Memory (KB)
Memory Manager: Target Server Memory (KB)
Memory Manager:  Memory Grants Pending
Memory Manager: Free Memory (KB)


Transaction Table in Personal Finance App I'm a noob designing a Personal Finance App to teach myself Rails. This is just for learning purposes, but I actually intend to use it to replace my current system in Google Spreadsheets. I want to apply best practice.

I created a transactions table with the following columns:

user_id: integer
type: string (income or expense)
date: date
account_group_id: integer
account_id: integer
amount: integer (my currency does not need decimals)
comment: string

Now, the way is this designed, all users' transactions would be recorded on the same table. Is this a good idea?

Or should I create a different transactions table for each user?

Does it matter if I do it either way?

My first advice would be to check out a few Open Source projects on the same topic.

Adapt and adopt the concepts you like - pick and choose ideas. No point reinventing the wheel! :-) I appreciate that you want to learn, but translating code from [PHP | Python | Perl | Whatever] could also be a beneficial learning experience! And you might even gain tips and tricks from more experienced programmers!


How to COUNT number of rows with LIMIT? I want to catch X rows, thus, I set LIMIT X; but how can I simultaneously count the total number of rows too?

Currently, I use two separate queries to do so as

SELECT COUNT(*) FROM col WHERE CLAUSE
SELECT * FROM col WHERE CLAUSE LIMIT X

Is there a way to do this in one query?

EDIT: The output should be the col cells and the number of rows. In fact, after selecting the col cells, it should walk over the table to count only.

I know that it is not possible to merge these two queries, as the first return 1 row, but the second X rows. I am curious if there is a function in mysql to return the number of rows in the presence of LIMIT.

It sounds like you want to run the LIMIT, but still know how many rows would have been returned without the LIMIT.  Check out FOUND ROWS


Could I control which protocol will ODBC uses to connect MSSQL on "integrated windows authentication" I have a Windows machine that should connect to an MSSQL instance with an ODBC driver using "windows integrated authentication".
Now according to the specifications of the login message the server is using SSPI for that. And if I understood the sspi documentation correctly, then SSPI could be use by Kerberos, NTLM or some other authentication protocol.
Now let's say that I want to enforce ODBC to user Kerberos, is it possible to do that by configuring either one of the following components?

SQL Server
Active Directory
ODBC driver

(Hope that my question makes sense? I am really new to the world of Windows authentication and protocols and really confused by all the docs..)

SQL Server will always prefer Kerberos over NTLM, however, NTLM is used as a fallback by SQL Server to ensure connectivity. You cannot disable NTLM authentication at the SQL level, this can only be done at the domain level, however, if you have Kebreros authentication properly configured then SQL Server will use that over NTLM.
See this article for information on properly configuring Kerberos in SQL Server:
https://www.sqlservercentral.com/articles/configuring-kerberos-authentication


Communication between two database servers I have 2 databases running on different virtual servers. I have some database processes running on one node. These processes connect to Database Gateway (PG4MQ) and process the messages.

I need to start the process manually. I need to write a script that will monitor the node on which the processes are running and if the node is down or not running then these process will automatically start on the other node.

Can someone tell me is this feasible or not, if feasible then how can this be achieved?

Databases installed on RHEL 5.8. OEM, Grid, DBCA and Oracle Net services are not available.

Because both of them are virtual servers (you didn't mention the technology used (ESX/KVM...), you can try this:

Create a small virtual drive that can be mounted on both servers (Linux ? Windows ? - you didn't tell)
Create a resource checking script (ex. http://howtodba.com/linux-cluster-resource-workers-locking-mechanism/)
Run the resource checking script, from each box, each 5 minutes (odd minutes on box 1, even minutes on box2).

** The more info you provide, the better solutions you'll get.


&& vs ST_Intersects performance I am trying to figure out points reside inside an rectangular area (envelope). I am having a little bit difficulty understanding performance implications of using && operator compared to ST_Intersect.

However I think I explained my own question while trying to formulate my question. I submit it anyway in case it would be useful to somebody.

The manual for && says (ironically the page name is geometry_overlaps.html):

  && — Returns TRUE if A's 2D bounding box intersects B's 2D boundingbox.`

The manual for ST_Intersects says:

  Returns TRUE if the Geometries/Geography "spatially intersect in 2D" - (share any portion of space) and FALSE if they don't (they are Disjoint). For geography -- tolerance is 0.00001 meters (so any points that close are considered to intersect)

ST_Intersects will return true in such cases. For my objective && operator does almost same thing since I am using a rectangular bounding box in either case. I am wondering if && is the fastest operator for my purpose? It feels like && has to do less checks so it must be much more efficient.

Here is an example which is direct copy from &&, ST_Intersects adaptation:

SELECT
        t1.id AS t1,
        t2.id AS t2,
        t1.ln && t2.ln AS "&&",
        ST_Intersects(t1.ln,t2.ln)
FROM ( VALUES
        (1, 'LINESTRING(0 0, 3 3)'::geometry),
        (2, 'LINESTRING(0 1, 0 5)'::geometry)
) AS t1(id,ln)
CROSS JOIN (VALUES
        (3, 'LINESTRING(1 2, 4 6)'::geometry)
) AS t2(id,ln);

 t1 | t2 | && | st_intersects
----+----+----+---------------
  1 |  3 | t  | f
  2 |  3 | f  | f
(2 rows)

Below is a simple graph of how these lines should look like. I just added plotted this to see how bounding box works exactly for lines. For my purposes, the points are always inside the box (while this line example does not really reflect what I am trying to do but good for illustration)

Question 1 is if && is significantly faster than ST_Intersects when using with ST_MakeEnvelope (a rectangular boundary), when finding points inside a rectangular bounding box.

Question 2 is Also am I understanding correctly that when checking points inside a rectangular boundary && does exactly same thing as ST_Intersects?

Background, functionality and performance

&& opperator

&& is bounding-box overlaps. All operators call functions in PostgreSQL: you can see this \doS+ && in this case && literally calls the PostGIS function geometry_overlaps. The only catch here is that && will make use of an index, from the docs

  In general, you will want to use the "intersects operator" (&&) which tests whether the bounding boxes of features intersect. The reason the && operator is useful is because if a spatial index is available to speed up the test, the && operator will make use of this. This can make queries much much faster.

You can see in the definition of geometry_overlaps that it calls an internal C function gserialized_overlaps_2d.  The function gserialized_overlaps_2d uses 4 comparisons to determine whether or not there is an overlap in the bounding box. That's
not usually all that useful except for adding selectivity, so you don't normally want it.

That means this isn't a performance question, && just doesn't do much. However what && does do can make use of a GIST index.

ST_Intersects

ST_Intersects from the docs,

  This function call will automatically include a bounding box comparison that will make use of any indexes that are available on the geometries.

The reason why is simple, only the bounding box uses the index. That means it'll do a && AND someting else. And you can see that with \dfS+ st_intersects

SELECT $1 && $2 AND _ST_Intersects($1,$2);

So the extra bit it does is call either geos_intersects or sfcgal_intersects intersects depending on your chosen back end. in the best case, that you get geos_intersects, you can see what that does here.

In essence, it is telling you if any point intersects without making any assumptions (other than floating point math).

Mixed SRIDs

As a last note, it maybe be worth noting that these two operations handle mixed SRIDs differnetly.

Your questions

  Question 1 is if && is significantly faster than ST_Intersects when using with ST_MakeEnvelope (a rectangular boundary), when finding points inside a rectangular bounding box.

Yes, it's faster -- significantly. It does less. It doesn't find "points" inside a rectangular bounding box unless one side is a simple point. Other than that, it finds bounding-box overlaps which are subject to false positives if all points reside outside of the bounding box.

  Question 2 is Also am I understanding correctly that when checking points inside a rectangular boundary && does exactly same thing as ST_Intersects?

No. It should be clear why now.


getting one day timestamp in mysql Please consider the following stored procedure I have:

ORIGINAL Stored Procedure:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE PROCEDURE `myProc`()
BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION,SQLWARNING

BEGIN
  -- ERROR
ROLLBACK;

END;

START TRANSACTION;

INSERT INTO mydb.mytable(some values)

SELECT (some values)

FROM   mydb.mytable

WHERE m_dt BETWEEN  CURDATE() - INTERVAL 60 DAY AND CURDATE() - INTERVAL 60;

DELETE FROM   mydb.mytable

WHERE m_dt BETWEEN  CURDATE() - INTERVAL 60 DAY AND CURDATE() - INTERVAL 60;

COMMIT;

END

Since m_dt datatype is datetime, I want to modify my BETWEEN condition. I want to add 00:00:00  before the AND and 23:59:59 after the AND condition. For example something like the following:

WHERE m_dt BETWEEN  CURDATE()  - INTERVAL 60 DAY 00:00:00 AND CURDATE() - INTERVAL 60 23:59:59;

By any chance it is possible in MySQL?

Try this

I believe the first part already evaluates to 00:00:00. So I just added the seconds in.

WHERE m_dt BETWEEN  (CURDATE() - INTERVAL 60 DAY) AND
  (DATE_ADD((CURDATE() - INTERVAL 60 DAY),INTERVAL 86399 SECOND))


DB2 removing white spaces in field values In a table I have a column called column_xml defined as CLOB(1000000). The fields in the column contain XML formatted information. My problem is that there are lots of white spaces in these fields that I need to get remove if there are more than 3 whitespaces in a row.

This is what the output looks like now

column_xml
--------------------------------------------------------------------------------
<values>
 <comment>test test test                               </comment>
 <rights></rights>
 <title>TEST MAN                                            </title>
</values>
--------------------------------------------------------------------------------

How can I remove all the white spaces before </comment> and </title> so it looks like this?

column_xml
--------------------------------------------------------------------------------
<values>
 <comment>test test test</comment>
 <rights></rights>
 <title>TEST MAN</title>
</values>
--------------------------------------------------------------------------------

If you define your XML document in a XML column, you can control how the whitespaces are stored.

For XML parsing, you can take a look a this page: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.xml.doc/doc/c0023533.html

To define the XML Schema, and how the whitespaces should be treated: http://www.w3schools.com/schema/schema_facets.asp

However, you are using a LOB and you are not exploiting the hybrid motor of DB2.


find the beginning and ending values of sequential runs This article does the inverse of what I need:  Find which numbers in [1, 161] are not in the result set?

Given the sample data they used:

mysql> CREATE TABLE wp_blogs
    -> (
    ->   blog_id INTEGER
    -> );

mysql> insert into wp_blogs values(1);
mysql> insert into wp_blogs values(2);
mysql> insert into wp_blogs values(4);
mysql> insert into wp_blogs values(6);
mysql> insert into wp_blogs values(7);
mysql> insert into wp_blogs values(8);
mysql> insert into wp_blogs values(10);

this query answers the question, "What are the gaps?":

mysql> SELECT a.blog_id+1 AS start, MIN(b.blog_id) - 1 AS end
    ->     FROM wp_blogs AS a, wp_blogs AS b
    ->     WHERE a.blog_id < b.blog_id
    ->     GROUP BY a.blog_id
    ->     HAVING start < MIN(b.blog_id);

+-------+------+
| start | end  |
+-------+------+
|     3 |    3 |
|     5 |    5 |
|     9 |    9 |
+-------+------+

What I want to know is what are the islands?
I would like to see output like this:

+-------+------+
| start | end  |
+-------+------+
|     1 |    2 |
|     4 |    4 |
|     6 |    8 |
|    10 |   10 |
+-------+------+

but I can't quite figure out the query to do so.  I am using MySQL 5.6.

Anyone have a solution?

To keep all the state information with the current row to avoid having conditions you can so something like:

SELECT start, max(end) FROM (
    SELECT
        CASE WHEN blog_id = @prev + 1 THEN @start:=@start ELSE @start:=blog_id END AS start,
        blog_id AS end,
        @prev:=blog_id
    FROM (SELECT @prev:=NULL, @start:=NULL) AS vars
    JOIN wp_blogs
) a
GROUP BY start;

+---------+------------+
|   start |   max(end) |
|---------+------------|
|       1 |          2 |
|       4 |          4 |
|       6 |          8 |
|      10 |         10 |
+---------+------------+
4 rows in set
Time: 0.003s


Time elapsed Calculation I need to calculated time elapsed for each user to perform his part in a transaction.

The sequence is: 0 -1 - B2 or 0-B3
The table looks like the following:

+--------+--------+-------------------+------------------+-----------+
| id     | Trtype |Internal Status    | Timestamp        | User Name |
+--------+--------+-------------------+------------------+-----------+
|2072499 | BSE100 | 0                 | 03/06/2014 13:33 | User1     |
|2072499 | BSE100 | 1                 | 04/06/2014 08:53 | User2     |
|2072499 | BSE100 |B2                 | 03/06/2014 13:33 | User3     |
|2072605 | BSE100 | 0                 | 03/06/2014 13:33 | User1     |
|2072605 | BSE100 |B3                 | 03/06/2014 13:33 | User3     |
.....................

I am using the following sql query to generate the table above:

select h.id, h.trtype, T.DESCRIPTION, hist.internal_status,
 ts.description status_Desc, h.val_dte, h.account_dte,
 hist.signature_timestamp, usr.login_name as user_name
  from k$transaction_header h, k$transtypes t, k$transaction_history hist, k$usr usr, K$TRANSACTION_STATUS ts
 where h.trtype = t.trtype
 and hist.trans_id = h.id
 and usr.id=hist.signature_usr
 and ts.id=hist.internal_status
 AND H.ACCOUNT_DTE = TO_DATE(&DATE_YYYYMMDD, 'yyyymmdd')
 order by h.id, hist.internal_status

Do you want to calculate it by hand, or is it enough to let the oracle db to do the calculation?

  Question:  I want to see the response time for my SQL statement and see the run time.  What tools does Oracle provide to see how long a query runs?

  Answer:  For seeing the elapsed time for an individual query, you can see individual query response time in SQL*Plus with the “set timing on” command.

  SQL> set timing on;
  SQL> select stuff from mytab;

  Elapsed: 00:00:02.82

(directly from http://www.dba-oracle.com/t_measure_sql_response_time.htm) may be of some interest in that case.


multiple ids table for accounting schema I am trying to make a small accounting db and I have hit a dead end, my search bared no results as of how to make this idea work.

In the picture below you can see that I am trying to link multiple tables to one table CreditDebit in this case, so I can use CreditDebit's ID (CD_ID) in the BondDetails table as the Creditor_CD_ID and Debtor_CD_ID

what I am trying to achieve is to have correct structure so that I could create a query that would tell me which table the creditor or debtor belong to based on their id and show their name (box_name, emp_name, customer_name)

NOTE: CreditDebit table would have only one of the three ids (box_name, emp_name, customer_name)

If you are concerned about multiple nulls in your CreditDebit, you may consider change the table to the following

create table CreditDebit (CD_ID int primary key, ref_id int, ref_entity varchar(20));

Here ref_id can refer to box_id/emp_id/customer_id depending on ref_entity column, which can have values like 'box', 'employee', 'customer'.
The disadvantage of this design is there is no way to create the foreign key relationship between CreditDebit` and the other three tables. However, you can implement the data integrity in the application layer (if you do not want to use trigger).


Import Oracle schema data without losing modifications of stored procedures I have this scenario:

One huge (thousands of tables), complex, Oracle 8 production database,
One huge (thousands of tables), complex, Oracle 9 development database,  (same structure as production)
In the development database, some stored procedures and packages have been modified and new ones have been added.
The development database has new tables in some schemas
Neither Oracle 8 exp nor Oracle 9 imp has ROWS ONLY option

We usually do as follows because it's the only way to get data updated properly because a import with ignore=yes would only insert new data, but wouldn't update pre-existing rows with the same PK but different values in non-PK columns:

Delete one schema, then create the user again to have an empty schema
Import from user to user to the empty schema

The problem is:

How to update the development database with fresh data from a production export without having to delete the schema first, since there're new stored procedures/packages as well as modified ones in the development database ?
The comparing process to re-create only modified or new stored procedures after deleting the schema, for getting them back from a backup, would be too error prone.
There are thousands (literally) of tables so we don't want to program a stored procedure to refresh the data in certain order etc. That would take months to write and test.

What would be an import-based solution to this ?

EDIT: I failed to mention that prod is Solaris and dev is RedHat.

I see two solutions. I did not test them but I hope they are userful.

In contrast to table mode export both user mode and tablespace mode export take care of the dependencies between tables so that the tables are imported in the right order. Both  methods import the production table stuctures to dev. Tablespace mode export/import in ORacle 8i or 9i  can be done only beteen database systems on the same operating system. On can handle this restriction by importing the data using user mode import into a intermediate schema or database on the same operating system as the target sytem.

Method 1: Using TablespaceMode export/import:
  1) Put the tablespace(s) of prod in read only mode
  2) TablespaceMode-Export the tablespace(s) and datafile copy from prod
3) Put the tablespaces of prod in read/write mode again
4) Drop the Tablespaces in dev (leaves procedures, views from dev)
5) TablespaceMode-Import the tablespaces in Target (includes tables, triggers , indexes, constraints of prod)
6) Put the tablespace(s) of dev in read/write mode

Maybe there are some problems you have to copy with changes in table structure, missing grants or that sequences have to be adjusted

Method 2: replace procedures by an export
1) UserMod-Export of the prod schema
2) UserMod-Export of the dev schema
    CONSTRAINTS=N
    GRANTS=N
    STATITICS=N
    TRIGGERS=N
    ROWS=N
3) drop the dev schema
4) import the prod schema (to dev)
5) import the dev-schema (to dev)
  IGNORE=Y
  CONSTRAINTS=N*
  GRANTS=N*
  INDEXES=N
  ROWS=N*

the second import schould replace the prod objects (procedures, views) by the dev objects but leave the tables and
table related objects.
Parameters with * are alredady set during the export. Maybe one must not set them during import.
If you set IGNORE=N then you will get a lot of error messages but maybe there are some other advantages.
It will happen hat you import tables from dev that are not in prod. Changes to the table structure must be handled mynually.

I would prefere a method that extracts the procedures from the dev schema before it is replaced by the prod schema. Tools like Toad from Quest (as mentioned by @r.m in a comment) that can compare schemas an create scripts to implement the differences may help.

1) UserMode-Export from prod
2) Extract the procedures from dev into sql-scripts
3) drop dev schema
4) UserMode-Import to dev
5) drop procedures in dev
6) run the scripts created by the extraction process
7) compile all procedures

The following method leaves the dev structures and only imports the data from production. You will get errors during import of data if the column of a table has been renamed or removed in dev. Adding columns with constraints in dev tables or altering contraints in dev may also make the import of data fail for this tables. If some tables aren`t imported because of this errors dependent tables that were not changed in dev can also have problems when the data is imported. So a lot of complicated problems can arise. The basic idea is:

*) remove the data from the dev tables
 *) disable contraints, triggers and indexes on the dev tables
 *) import the data only from the prod tables to the dev tables
 *) enable the constraints, triggers and indexes on the dev tables
 *) import the data to the new dev tables and enable their constaints, triggers and indexes

1)user mode export the dev schema without triggers
  ROWS=NO
  TRIGGERS=NO
  FILE=dev1.dmp
2) user mode export the dev schema again
  ROWS=NO
  TRIGGERS=NO
  FILE=dev1.dmp
3) table mode export of dev tables not found on prod
   TABLES=list_of_tables_in_right_order
   TRIGGERS=Y
   CONSTRAINTS=Y
   INDEXES=YES
   GRANTS=Y
   ROWS=Y
   STATISTICS=NO
   dev3.dmp
4) drop the schema on dev
5) create the empty schema on dev again
6) import the empty dev schema without triggers, constraints and indexes
   ROWS=NO
   CONSTRAINTS=N
   INDEXES=NO
   FILE=dev1.dmp
7) import the indexes in a script
   INDEXES=YES
   ÌNDEXFILE=index.sql
   FILE=dev1.dmp
8) user mode export the prod schema
   CONSTRAINTS=NO
   GRANTS=NO
TRIGGERS=NO
INDEXES=NO
FILE=prod1.dmp
9) table import the prod table data
TABLES=*
   IGNORE=Y
   CONSTRAINTS=N
   INDEXES=N
   GRANTS=N
   FILE=prod1.dmp
10) create the indexes using index.sql with sqlplus
11) import table constraints and triggers
   TABLES=*
   CONSTRAINT=Y
   TRIGGERS=Y
   ROWS=N
   FILE=dev2.dmp
12) import the dev tables you need
   TABLES=list_of_tables_in_right_order
   IGNORE=Y
   CONSTRAINT=Y
   GRANTS=Y
   INDEXES=Y


Oracle 11g hash join vs nested loops question I'm using Oracle 11g and working on a legacy system. I have two tables, A and B. A has ~40M rows and B ~4M rows. A has a foreign key on B (B is the parent of A).

A query does essentially this:

select count(A.id), sum(A.something1), avg(B.something2), ....
from A inner join B on A.id_b = B.id
where A.filter_column in (...)
group by A.filter_column

condition on filter_column is such that 5-10% of rows from table A are selected. Join picks up 20-25% of rows from B. The end result is about 100 rows that contain aggregated data from selected rows from tables A and B.

The query initially had a hint in it, forcing Oracle to do a nested loops join of A to B. By removing the hint, execution plan changes from nested loops to a hash join. I would expect that to run faster, however, to my great surprise, it actually runs slower! Trying to find out why, I collected statistics on execution using one of Tom Kyte's scripts ("runstats") and found out that the nested loops version of the query did 0 (!) physical reads while the hash join version did a lot of them. I ran the test many times, changing the order of the execution of the two queries so that the nested loops version runs first and hash join version runs second and vice versa, but the results were always about the same. Furthermore, the hash join plan produces less logical I/O but is still slower.

Does anybody have an explanation for that?

Thank you!

Try to execute the query with gather_plan_statistics hint. Then use dbms_xplan to display exec plan. You will see E-rows(estimated) and A-rows(Actual). This is where I would start, to check whether the optimizer is wrong in it's assumptions or not.

Also check v$sql_plan of the running query and check whether TEMP space is really used or not. Especially on older Oracle versions hash joins were slower, because hash table was too big, it did not fit into PGA and had to be stored on disk - thus generating too mush disk I/O.


How can I force Teradata to create a table in memory? Teradata has an "intelligent" in-memory engine that dynamically loads some data into memory based on usage.

How can I force it to create or store a table in memory all the time?

The answer to your question and other information about Teradata Intelligent Memory (TIM) can be found in the Orange Book about Teradata Intelligent Memory. Also in the appendixes are some SQL samples to query Teradata to verify what the temperature of a specific table is.

Please be advised that there is a general advice to NOT load data directly as "very hot", but to "hot" and let TVS then handle the temperature characteristics. Otherwise cooling down could be a problem.

To directly answer your question, there are 3 options:

1) Wait and access the table until TVM sees that the data is "very hot". If normal query characteristics access the table often there is a high chance the table will get loaded in memory and stay there.

2) Use a queryband to force it to "very hot"

SET QUERY_BAND='TVSTEMPERATURE_PRIMARY=VERYHOT;' FOR SESSION;

However you need to have access to the macro DBC.VHCTRL(). Otherwise TD will silently set your data to "hot".

3) Use FERRET:

> force "ADW_DB.INVOICE" P TEMPERATURE=VERYHOT
force "ADW_DB.INVOICE" P TEMPERATURE=VERYHOT
FORCE command changed the temperature of table ADW_DB.INVOICE
to VERY-HOT.
The temperature of 638 cylinders have been changed to VERY-HOT.

Please also note that a DBA is not amused if you suddenly load a huge table in TIM in production, forcing other data to not reside in TIM. There is a reason why the queryband is restricted...


How to unmount oracle database? According to the oracle docs, during shutdown, 3 steps are performed:

Close the database.
Unmount the database.
Shut down the instance.

Is it possible to make only first 2 steps?

Yes, it's possible.

SQL> alter database close;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database dismount;

Database altered.

SQL> select open_mode from v$database;
select open_mode from v$database
                      *
ERROR at line 1:
ORA-01507: database not mounted


What's your experience to deal with the offboarding account? I am thinking about doing the cleanup after the employee offboarding. Such as check across the multiple SQL instances and remove the offboarding account and the user on the database level.
Since it's across multiple SQL instances, I am considering using the dbatools PowerShell module. But not sure if it's a good way.
Anyone have a good experience to share with me?
Many thanks,

Central Management Server (or Local Server Groups) is another option for multi server administration.
https://docs.microsoft.com/en-us/sql/relational-databases/administer-multiple-servers-using-central-management-servers?view=sql-server-ver15


Best practices to maximize portability in SQL Server 2016 When it comes to developing the prototype of a solution, often the technologies has not been decided yet and might not be the same that will be used in the finished product.

In this scenarios I tend to use Microsoft SQL Server writing the queries as standard as possible to simplify the eventual migration to another server.

Is there a way or some known practice to enforce the use of standard SQL over T-SQL dialect directly in SQL Server or via SQL Server Management Studio (SSMS)?

User Aaron Bertrand made some comments that align well with my thoughts on your question.  This is more of a frame challenge than an answer to your specific question, but I think it's valuable to consider in this context.

  Portability is a nice textbook goal, but it rarely happens in practice.

If you have to change platforms at some point, there will be changes needed to the application, the database, and probably many other things.  If you can be somewhat "platform agnostic" without too much effort, that's fine.  But it's really a bad business decision to use that as a design goal.

There are many places online where people discuss the downsides or programming this way, here's one of them that I find pretty compelling:

Database Abstraction Layers Must Die!

  The Portability Fallacy

  The author uses an argument I hear all the time: If you use a good abstraction layer, it'll be easy to move from $this_database to $other_database down the road.

  That's bullshit. It's never easy.

  In any non-trivial database backed application, nobody thinks of switching databases as an easy matter. Thinking that "the conversion will be painless" is a fantasy.

  Good engineers try to select the best tools for the job and then do everything they can to take advantage of their tool's unique and most powerful features. In the database world, that means specific hints, indexing, data types, and even table structure decisions. If you truly limit yourself to the subset of features that is common across all major RDBMSes, you're doing yourself and your clients a huge disservice.

  That's no different from saying "I'm doing to limit myself to the subset of PHP that's the same in Perl and C, because I might want to switch languages one day and 'painlessly' port my code."

  That just doesn't happen.

  The cost of switching databases after an application is developed and deployed is quite high. You have possible schema and index changes, syntax changes, optimization and tuning work to re-do, hints to adjust or remove, and so on. Changing mysql_foo() to oracle_foo() is really the least of your problems. You're gonna touch most, if not all, of your SQL--or you'll at least need to verify it.

  That doesn't sound "painless" to me.


Can we reuse a .dmp file(expdp) to perform import using data pump utility I'm an accidental DBA. We use Oracle 11g database. I had to do a database refresh in the test environment. I created a .dmp file using expdp command and performed an import using impdp command a week ago. I had to make some schema changes in test and then the department decided to revert to how it was before we made changes to the schema. The department wants me to perform impdp using the old .dmp file that I created a week ago. Can I re-use this file to do the import? Can the .dmp files be reused as many times as we can?

A DMP file is just a copy of the whole database contents. During the Import, it does not get modified, so you can use that file to "jump back" to the time when that dump file was created as often as you want.


What is Data Virtualization? I've just been asked whether our company should consider Data Virtualization for our test environments.  The benefits are given as:

Screening of sensitive data
Fast data refreshes in our test environments
Potential benefits for DR and BI scenarios

However I've only found marketing info; nothing technical.  From what I can figure out there are 2 approaches:

A service layer over a production database which abstracts you from the data model (presumably resulting in a different data model presented by that new layer).
A tool to automate the restore and subsequent manipulation of data which can be used by non-technical users and is faster than using database backups and SQL scripts.

Without seeing any technical information this smells of snake oil to me; but I want to understand it rather than dismissing out of hand.

Keywords:
[data-as-a-service]
[data-virtualisation]
[data-virtualization]
[delphix]
[denodo]

Data virtualization is the provision of an abstraction layer so the data consumer does not have to know the physical location or format of the original data. You may have a PostGres DB, a MySQL DB, a SQL Server DB, a whole batch of Parquet/ORC files and the person writing the query is completely unaware of that physicality. As far as they are concerned they are hitting a connection to Presto (or whatever data virtualization solution you'd chosen use).

Technologies such as Apache Presto allow a central point against which to run SQL queries but Presto itself is configured to know where and what the source data is, the end user does not need to know.  Presto is an open-source tool that has had a lot of input from Teradata, particularly with regard to JDBC connectivity, security and LDAP authentication.  It also has commercial support from StarBurst.  Starburst has recently announced a cost based query optimizer for Presto.

AWS have faith in Presto because they have based AWS Athena on it.  The beauty of it is that data does not have to reside in a relational database.  It can be file based as well.

In terms of screening sensitive data you can choose who has access to what but it isn't a data masking or obfuscation tool.

It isn't snake oil but neither is it a silver bullet.  There is obviously a hit on the source systems and you have to understand what that hit is.  The key benefit is that you don't have to shift data all over the place and have a huge plethora of technology to support that data movement.


MySQL create index in 1B rows table We're trying to add an index to one of our tables in production database.

CREATE TABLE `abcd_s3_metadata` (
  `rowId` bigint(20) NOT NULL AUTO_INCREMENT,
  `sId` varchar(50) NOT NULL,
  `ruid` varchar(200) NOT NULL,
  `s3Pointer` varchar(250) NOT NULL,
  `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `latest` tinyint(1) NOT NULL DEFAULT '1',
  `version` int(11) NOT NULL,
  PRIMARY KEY (`rowId`),
  UNIQUE KEY `ruid_UNIQUE` (`ruid`)
)

The table has ~ 1B rows.
We created a copy of the live table and I tried to add an index on updateTime field.
With the following statement:

ALTER TABLE `innodb`.`ruids_s3_metadata`
ADD INDEX `updateTime_idx` (`updateTime` ASC);

The ALTER statement ran for 3 hours resulted in extra read 1500 IOPS consumed all free InnoDB memory. And eventually caused the DB to choke and become unusable.

What can we do better? Are there other options?

Thanks

Alex

You should look at using a 3rd party schema change tool to avoid causing issues. You can start with pt-online-schema-change from the Percona Toolkit and if that doesn't meet your needs there's a schema change tool written by github named Ghost.

https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html

https://github.com/github/gh-ost


Mariadb galera cluster master node started and reporting 2 nodes but second node service says activating mariadb I managed to start the master node mariadb galera cluster, then I started the second node and I check on the first master node;
$ sudo mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
Enter password:
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+

but when I checked the second node it says:
 Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
   Active: activating (start) since Sat 2020-11-28 19:08:24 GMT; 11min ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 7659 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUC
  Process: 7660 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SU
  Process: 7662 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bi
 Main PID: 7829 (mysqld)
   Status: "WSREP state transfer ongoing, current seqno: -1 waited 690.000000 secs"
    Tasks: 12 (limit: 881)
   CGroup: /system.slice/mariadb.service
           ├─ 7829 /usr/sbin/mysqld --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1
           ├─ 7837 sh -c wsrep_sst_rsync --role 'joiner' --address '192.168.5.132' --datadir '/var/lib/mysql/' --pa
           ├─ 7838 /bin/bash -ue /usr//bin/wsrep_sst_rsync --role joiner --address 192.168.5.132 --datadir /var/lib
           ├─ 7890 rsync --daemon --no-detach --port 4444 --config /var/lib/mysql//rsync_sst.conf --address 192.168
           └─10407 sleep 1

Nov 28 19:08:24 raspberrypi systemd[1]: Starting MariaDB 10.3.25 database server...
Nov 28 19:08:27 raspberrypi sh[7662]: WSREP: Recovered position 00000000-0000-0000-0000-000000000000:-1
Nov 28 19:08:28 raspberrypi mysqld[7829]: 2020-11-28 19:08:28 0 [Note] /usr/sbin/mysqld (mysqld 10.3.25-MariaDB-0+d
Nov 28 19:08:28 raspberrypi mysqld[7829]: 2020-11-28 19:08:28 0 [Warning] Could not increase number of max_open_fil
Nov 28 19:08:31 raspberrypi rsyncd[7890]: rsyncd version 3.1.3 starting, listening on port 4444

what does it mean ?
port 4444 is open on both the nodes.

I had the same issue with 2 nodes configured. mariadb-10-1-galera-cluster-error.
When using Galera Cluster, it’s recommended that the cluster have at least three nodes. In a two-node cluster, when there is a conflicting pair of transactions, it could lead to a split-brain situation. Also, when one node fails, the remaining node will become non-operational. (Two-Node Clusters)


Error when tring to get lots of data When I run the report for a few days then its good. but if I try to retrive data for more then a year then I get this error message.

I tried to run DBCC CHECKDB as it says in the error, but after 10 min running a error came up that it can't continue.

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:952663; actual 2668:-1594740640).
It occurred during a read of page (1:952663) in database ID 5 at offset 0x000001d12ae000 in file 'F:\Data\PointOfSale.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

It looks like the issue is due to data corruption on page 952663 on F:\Data\PointOfSale.mdf.

The error is not due to large data being returned. One of the pages in your result set is corrupt. You can still circumvent the corrupt pages and get back a large result set.

You can do what @jl01 suggests or you can also try a page level restore for page 952663.


is there a term for databases which are not system databases? Is there a term for all databases in SQL Server that are not system databases (such as master, model, tempdb)?

"User Databases".

The same "System" vs "User" distinction is also made for other objects such as tables.

For example

  Members of the db_datareader fixed database role can read all data
  from all user tables.

msdb and the distribution database (if you use replication) are also system databases that you do not mention by the way.


Using wm_concat to concatenate rows, but in an order determined by another column Let's say I have 3 columns: p_id, description, order_by.  I am trying to do the following:

I would like to concatenate the description for all like p_id values.  So we are talking a group by p_id sort of thing.  But then, I want the description to be concatenated in the order of the order_by column (which is an integer).  So my ideal query (not-working) would look like

select p_id, wm_concat(description)
from my_table
where p_id = 12345
group by p_id
order by order_by asc

How do I concatenate rows in this fashion?

So the query (which would work if under 4000 bytes) is

select p_id, listagg(description, '\n') WITHIN GROUP (ORDER BY order_by)
from my_table
where p_id = 12345
group by p_id


Operator "~<~" uses varchar_pattern_ops index while normal ORDER BY clause doesn't? Let's say I have a table with  1 000 000  records.
Structure of table is:
create table individual (
  id serial primary key,
  surname varchar(128),
  "name" varchar(128),
  patronymic varchar(128),
  birth_dt date
)

I create a composite index.
INDEX 1
create index on individual using btree (upper(surname) varchar_pattern_ops
  , upper("name") varchar_pattern_ops, patronymic varchar_pattern_ops, birth_dt);

Docs state that varchar_pattern_ops should be applied when using LIKE or pattern match in query. Conclusion: this index will not be used in query below, even it gets only 10 row from 1 000 000.
QUERY 1
select * from individual
order by upper(surname), upper("name"), upper(patronymic), birth_dt limit 10;

and even more, docs recommends to create an index without varchar_pattern_ops as well.
INDEX 2
create index on individual using btree
(upper(surname), upper("name"), upper(patronimyc), birth_dt);

Then a query using LIMIT will use this index.
I found a cheat to force Postgres to use first index on Postgres users forum. It is operator ~<~.
QUERY 2
select * from individual
order by upper(surname) using ~<~
       , upper("name") using ~<~
       , upper(patronymic) using ~<~
       , birth_dt limit 100;

In this case INDEX 1 will be used even if INDEX 2 doesn't exists. I tried to investigate to discover why it happens, but failed.
There are some system tables like pg_operator, which (I think) link operator ~<~ to some functions that most probably uses LIKE or regular expressions.
I ran QUERY 1 and QUERY 2 a few times and compared result manually. It looks like operator ~<~ gives correct result, but I didn't risk anything and just create a normal index anyway.
I am still interested how the Postgres planner decides which index to use index where it meets the operator ~<~ in QUERY 1.

This revolves around COLLATION rules (which are part of your locale). Per documentation:

  The C and POSIX collations both specify "traditional C" behavior,
  in which only the ASCII letters "A" through "Z" are treated as
  letters, and sorting is done strictly by character code byte values.

Bold emphasis mine. These internal operators:

~<~, ~<=~, ~>=~, ~>~

are members of the operator classes text_pattern_ops, varchar_pattern_ops and bpchar_pattern_ops which ignore current collation rules and default to "traditional C" behavior. Per documentation:

  The difference from the default operator classes is that the values
  are compared strictly character by character rather than according to
  the locale-specific collation rules.

That's why they can use your first index, while their locale-aware equivalents

<, <=, >=, >

would need the second (using the default operator class).

ORDER BY defaults to the current collation rules. Unless you happen to work with the "C" collation the second set of operators is used for sorting, which can only use indexes with a matching collation (default btree indexes).

Notably, pattern-matching generally relies on "traditional C" behavior, that's why a left anchored LIKE match can use a _patern_ops index with a non-C locale, but not a default index. Compare (last chapter):

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

In Postgres 9.1+ you can force a selected sort order with COLLATE "locale_name" (the locale has to be available in your system!). So if you run your query with COLLATE "C" (which is a much better option than the evil hack using ~<~) , it can use the _pattern_ops index:

SELECT * FROM individual
ORDER  BY upper(surname) COLLATE "C"
        , upper("name") COLLATE "C"
        , upper(patronymic) COLLATE "C"
        , birth_dt
LIMIT  100;

Results are different. Try with non-ASCII letters like ö,  é, etc. Those typically sort depending on your current collation rules, while letters a-z typically sort the same in all locales.


How to mysqldump to a subset of rows in specific table during a complete backup I have a database (myDB) that holds some tables. During my backup process I have one of those tables that should be filtered. So I want to backup only a subset of rows inside table1, when I backup the entire database.

I am using mysqldump like this:

mysqldump -uUSER -pPASSWORD --routines --add-drop-database   --databases myDB --tables table1 -w "id<3"> myDB_BK.sql

The subset rows is correct for table1 but I dont have the entire database. Only the specified table1.

I've tried all combination with no success.

Any Idea in how to accomplish this task?

Dumping subsets of table data can only be done backing up individual tables.

I once answered a post entitled Is it possible to mysqldump a subset of a database required to reproduce a query?. In that post, I used mysqldump strictly against the tables in question.

The only thing you can do is to produce two dumps

mysqldump of the database without table1 (means you have to name all other tables (See my old post How do you mysqldump specific table(s)?)
mysqldump only table1 using -w or --where
concatenate the second dump to the first dump

Give it a Try !!!


Can we prevent table from update directly but allow update using trigger from another table? So, let's say I have 3 tables:

stocks table, it have columns: id, product_id, stock
sell table, it have columns: id, product_id, qty
buy table, it have columns: id, product_id, qty

What I want to do:

Prevent update stocks.stock value directly.
But allow update stocks.stock value by trigger from buy or sell table.

Is this possible? If it's possible, how? If its impossible, why?

create trigger has a definer = {user} option for this.

You need to ensure that this defined user has update access to the stocks.stock however the other users only have select table access.

GRANT can control access at a table level.


Allocate space in Cassandra We run a Cassandra cluster with 2 DC, 3 nodes each (2 RF). The state of the cluster is quite bad (never repaired) and the disks were getting full so we added additional nodes which after the bootstrap procedure joined successfully the cluster.
According to the Cassandra documentation is supposed to run cleanup after adding a new node, but in our case:

Since we never run repair (and now it too late to run since disk usage in old nodes is > 90 %) Is it safe for the data to run:

nodetool cleanup

in each node in order to free space?

Cheers,

Jbrl

Simply put, nodetool cleanup will remove data from a node that it is no longer responsible for.  This can happen when adding new nodes to a cluster, as they assume token ranges once held by other nodes.  Once the data is streamed to the new node(s), it isn't automatically removed from the pre-existing nodes.  This is intentional, in that if a new node fails to bootstrap no data is lost.

Running a cleanup will indeed remove potentially orphaned replicas.  Normally, it is a good idea to ensure that repairs have been successfully completing prior to running nodetool cleanup.

In this case, as repair has never been run, I can't imagine that the application users really care about data consistency at this point.  But one thing to try, might be to run cleanup on one node to get some space back, followed by repair on the same node.  Work your way through the cluster running repair/cleanup (in that order) on each.  That should minimize the risk of data loss, while allowing for restoration of some semblance of consistency.


SQL Server job to use external Powershell I have a SQL Server job which runs some Powershell code, but although when running the Powershell script in a PS editor on the machine it runs successfully, when running the SQL Agent Job with the same code it fails.

And the reason it fails is because it doesn't manage to add a few snap-ins, like the one below or include some assemblies.

Add-PSSnapin SqlServerCmdletSnapin100

This leads me to believe that SQL Server has a internal version of Powershell installed with which it runs the Powershell jobs. And that specific internal version of Powershell is older and does not have the required assemblies / snap-ins and thus fails.

Now, is there any way I can use the Powershell version installed on my machine and not the one that's built into SQL Server?

I've looked over "Proxies" in SQL Server but I don't think they're what I'm looking for.

Any help is appreciated!

As requested, here is an example of the process.  The step should look like this, though

Job Step Command
Set the step to be a cmdexec job and then run powershell through that.  It lets you use the latest version of powershell as well as configure the powershell environment for that user (if you want).  The step command will look like the one below, complete with arguments for the example.  Remember to put quotes around strings that contain spaces.

Powershell.exe -noprofile -executionpolicy bypass -file "C:\Scripts\DailyRestoreScript.ps1" -Arguments -SourceSQLInstance 'localhost\jdf2016' -SourceDatabaseName 'AdventureWorks' -TargetSQLInstance 'localhost\jdf2016' -TargetDatabaseName 'AdventureWorks2'

Complete Job

USE [msdb]
GO

/****** Object:  Job [DailyRestore_ADWorks]    Script Date: 2/16/2017 9:00:51 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2/16/2017 9:00:51 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DailyRestore_ADWorks',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'Daily restore AD Works from backup',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name='XXXXX\JFite', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Restore ADWorks]    Script Date: 2/16/2017 9:00:51 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Restore ADWorks',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'CmdExec',
        @command=N'Powershell.exe -noprofile -executionpolicy bypass -file "C:\Scripts\DailyRestoreScript.ps1" -Arguments -SourceSQLInstance ''localhost\jdf2016'' -SourceDatabaseName ''AdventureWorks'' -TargetSQLInstance ''localhost\jdf2016'' -TargetDatabaseName ''AdventureWorks2''',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO


Fulltext search using multiple indexes I have a table with several columns, say a, b, c, d that should be searchable. The problem arises when I need to search a, b, c separately from d (and vise-versa). AFAIK, there's no way to achieve this using one composite fulltext index on all columns, so I create two separate indexes like this:

CREATE FULLTEXT INDEX idx1 ON content (a, b, c);
CREATE FULLTEXT INDEX idx2 ON content (d);

Now I can search the first and second one successfully. For both of them, I would  use the following command:

SELECT * FROM content
WHERE MATCH(a, b, c) AGAINST ('keyword')
AND MATCH(d) AGAINST ('keyword');

explain tells me this:

+----+-------------+---------+----------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type     | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+----------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | content | fulltext | idx1,idx2     | idx1 | 0       |      |    1 | Using where |
+----+-------------+---------+----------+---------------+------+---------+------+------+-------------+

Great! So it's using two indexes, but returns only rows where keyword is present in both inclusive and I need either one, so I change AND to OR and now explain says:

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | content | ALL  | NULL          | NULL | NULL    | NULL |  128 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

What? Suddenly, it's doing a full table scan. Why is this happening? What would be the best way to avoid this?

Unfortunately, this is how MySQL Query Optimizer treats FULLTEXT indexes. When a MATCH clause is the only clause in the WHERE, the index will be used. When used in conjunction with AND, the index may easily get overlooked.

I wrote about this behavior before in Mysql fulltext search my.cnf optimization

SUGGESTION : Rewrite the query as the union of two FULLTEXT searches

SELECT * FROM content
WHERE MATCH(a, b, c) AGAINST ('keyword')
UNION
SELECT * FROM content
WHERE MATCH(d) AGAINST ('keyword');

GIVE IT A TRY !!!


MySQL unique constraint across two columns I have a table defining relationships

Src smallint(5) unsigned NOT NULL,
Dst smallint(5) unsigned NOT NULL,
other fields

I need to add a constraint that says if a given values is present in one of the columns, then.
1) Cannot be duplicated in the same column.
2) Cannot be duplicated in the other column either.

This is invalid

src    dst
1      354
666    1

Since the value 1 is present in the first row, it cannot be present in the second row.

How can I define this type of constraint?

I'm doing a lightweight check at application lever. But I want the DB to ensure it.

Update: Currently I have 7 different types of relationships, and one table per relationship type.

Update 2: Originally this was only one table containing all the relationships, now I'm exploding it

# variante
Create TABLE `productsRelationships3` (
  `relSrc` smallint(5) unsigned NOT NULL,
  `relDst` smallint(5) unsigned NOT NULL,
  PRIMARY KEY `src-dst-3` (relSrc, relDst),
  UNIQUE  `src-3` (relSrc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# this is the import
INSERT INTO productsRelationships3 SELECT relSrc, relDst FROM  productsRelationships WHERE relType=3;
DELETE FROM productsRelationships WHERE relType=3;

#this is the retrieval. The dummy rows are there because I do a UNION
#SELECT relSrc, relDst, 3 as relType, relTypeDesc, 0 as fracQty, 28281 as source FROM productsRelationships3 LEFT JOIN productsRelationshipsDesc on 3=relTypeID WHERE relDst=28281 OR relSrc=28281;

# fraccion
#relType is from the old 1-table schema. It's going to be deleted
Create TABLE `productsRelationships6` (
  `relSrc` smallint(5) unsigned NOT NULL,
  `relType` tinyint(2) unsigned NOT NULL DEFAULT 6,
  `fracQty` int(2) unsigned NOT NULL,
  `relDst` smallint(5) unsigned NOT NULL,
  PRIMARY KEY `src-dst-6` (relSrc, relDst),
  UNIQUE  `src-6` (relSrc),
  UNIQUE  `dst-6` (relDst),
  CONSTRAINT `fk_type_desc_6` FOREIGN KEY (`relType`) REFERENCES `productsrelationshipsdesc` (`relTypeID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#import
INSERT INTO productsRelationships6 SELECT relSrc, relType, fracQty, relDst FROM  productsRelationships WHERE relType=6;

The other tables are basically the same as productsRelationships3

I have modified ypercube's solution a little bit, so that both source and destination are not null, as is guaranteed by the original design. My CHECK constraints are commented out, as they apparently will not work in MySQL. I kept them as comments because they document my intent, and they will work on other RDBMS.

CREATE TABLE PointType
( PointTypeID tinyint unsigned NOT NULL,
  TypeDescription CHAR(20) NOT NULL,
  PRIMARY KEY (PointTypeID),
  UNIQUE (TypeDescription)
) ;

INSERT INTO PointType
  (PointTypeID, TypeDescription)
VALUES
  (1, 'Source'),
  (2, 'Destination') ;

CREATE TABLE PointUsageQuota
( RouteID int unsigned NOT NULL,
  PointTypeID tinyint unsigned NOT NULL,
  PointID smallint(5) unsigned NOT NULL,
  PRIMARY KEY (PointID),     -- and this is what all the fuss is about
  UNIQUE (RouteID , PointID, PointTypeID), -- target for the foreign keys
  FOREIGN KEY (PointTypeID)
    REFERENCES PointType (PointTypeID)
) ;

CREATE TABLE Route
( RouteID int unsigned NOT NULL,
  SourcePointID smallint(5) unsigned NOT NULL,
  SourceTypeID tinyint unsigned NOT NULL,
      -- CHECK(SourceTypeID = 1),
  FOREIGN KEY (RouteID , SourcePointID, SourceTypeID)
    REFERENCES PointUsageQuota (RouteID , PointID, PointTypeID) ,
  DestinationPointID smallint(5) unsigned NOT NULL,
  DestinationTypeID tinyint unsigned NOT NULL,
      -- CHECK(DestinationTypeID = 2),
  FOREIGN KEY (RouteID , DestinationPointID, DestinationTypeID)
    REFERENCES PointUsageQuota (RouteID , PointID, PointTypeID) ,
  --  other fields
  PRIMARY KEY (RouteID)
) ;

Testing:

insert into PointUsageQuota
values(1,1,666),(1,2,354);

INSERT INTO Route VALUES (1, 666, 1, 354, 2);
-- this fails:
INSERT INTO Route VALUES (2, 666, 1, 354, 2);
-- this fails too:
INSERT INTO Route VALUES (2, 354, 1, 666, 2);


How do I manage two versions of a stored procedure at the same time? Here is the situation: There are some stored procedures (3 - 5 no's) already in production. We made some changes for a fix. It also involved changes to the code that is using these stored procedures.
I am looking for a strategy that can help us achieve this:
Let the old code in production use the old version of the stored procedures. So when I deploy the newer version of the stored procedures, and the old code still running in production (no down time) it should use the old version of the stored procedure so the application does not break.
Once I deploy the new code, it should start using the newer version of the stored procedures.

This is typical use case for edition-based redefinition. You initially create an edition and a service that the clients use for connecting, to that speficic edition:

SQL> alter user bp enable editions for procedure;

User altered.

SQL> conn bp/bp
Connected.

SQL> create edition e1;

Edition created.

SQL> alter session set edition=e1;

Session altered.

SQL> create or replace procedure p1 as
  2  begin
  3    dbms_output.put_line('Edition e1.');
  4  end;
  5  /

Procedure created.

SQL> exec dbms_service.create_service('myservice', 'myservice', edition=>'e1');

PL/SQL procedure successfully completed.

SQL> exec dbms_service.start_service('myservice');

PL/SQL procedure successfully completed.

This is what you get if you execute the procedure:

SQL> conn bp/bp@localhost:1521/myservice
Connected.
SQL> set serveroutput on
SQL> exec p1
Edition e1.

PL/SQL procedure successfully completed.

This session remains alive and continues its work. Now you want to deploy a new version of the procedure. In another session:

SQL> conn bp/bp@localhost:1521/myservice
Connected.
SQL> create edition e2;

Edition created.

SQL> alter session set edition=e2;

Session altered.

SQL> create or replace procedure p1 as
  2  begin
  3    dbms_output.put_line('Edition e2.');
  4  end;
  5  /

Procedure created.

Then modify the service to use the new edition:

SQL> exec dbms_service.modify_service('myservice', edition=>'e2', modify_edition=>true);

PL/SQL procedure successfully completed.

New sessions connecting through this service will use the new version of the package:

SQL> conn bp/bp@localhost:1521/myservice
Connected.
SQL> set serveroutput on
SQL> exec p1
Edition e2.

PL/SQL procedure successfully completed.

While back in the original session:

SQL> exec p1
Edition e1.

PL/SQL procedure successfully completed.

If the old session ends then simply reconnects, using the same service it used for connecting earlier, it will start using the new procedure as well:

SQL> conn bp/bp@localhost:1521/myservice
Connected.
SQL> set serveroutput on
SQL> exec p1
Edition e2.

PL/SQL procedure successfully completed.

More about this: Using Edition-Based Redefinition