Choosing database for large set sum and difference I am creating an application with one server node and multiple clients.
Each client holds a set of strings, that will be stored in their own local database.
Once a day, all clients will send all their data to the server. The server will calculate the sum of all clients' sets, and then calculate the difference between this total set and each client's.
Small example:
Client1 has {1, 2, 3}
Client2 has {1, 2}
Client3 has {1, 3}
Client4 has {}
They send it to the server, that calculates Client1 + Client2 + Client3 + Client4 = {1, 2, 3} and server persists this result in it's own database.
Then server calculates difference.
Client1 lacks {}
Client2 lacks {3}
Client3 lacks {2}
Client4 lacks {1, 2, 3}
As those set of strings are going to be quite big, I would like to choose a database (SQL or NoSQL), that will be able to optimize those two operations.
Application is written in Spring Boot and will run on Ubuntu. Database needs to be open source. I also need full-text-search. In future there may be multiple server nodes, and they should be at least "eventually consistent".

In maths terminology the summed set calculated at the server is called the union. The comparison between the server's set and each client's is called the difference or relative complement.
It is simple enough to write these so any combination of programming language and persistence layer would suffice. However, relational databases are build on set theory and have built-in functionality for precisely these operations, so it makes sense to use one. I have a copy of SQL Server to hand so I'll use it's syntax. Postgres will support this schema and algorithm. MySQL has work-arounds.
Define the upload table thus
create table dbo.Upload
(
  ClientNumber  int          not NULL,  -- change the type to match your needs
  StringValue   varchar(99)  not NULL,  -- whatever type and length is appropriate
);

I'm treating this as a staging table that will be emptied at the end of each day. If it must retain history add the appropriate key columns. I will define a table to hold the server-calculated superset, though this could be a view or function or equivalent.
create table dbo.Superset
(
  StringValue  varchar(99)  not NULL
);

And a list of all clients so I can process ones that send no rows.
create table dbo.Client
(
  ClientNumber  int  not NULL
);
insert dbo.Client(ClientNumber) values (1), (2), (3), (4);

The end-of-day process runs and uploads the clients' data into the staging table
insert dbo.Upload(ClientNumber, StringValue)
values (1, '1'), (1, '2'), (1, '3');

insert dbo.Upload(ClientNumber, StringValue)
values (2, '1'), (2, '2');

insert dbo.Upload(ClientNumber, StringValue)
values (3, '1'), (3, '3');

-- Client 4 sends no rows.

Since I put all the clients' rows in a single table there is no need to union them. We just need to retrieve the unique values
insert dbo.Superset(StringValue)
select distinct
  StringValue
from dbo.Upload;

select * from dbo.Superset;  -- just to check

StringValue
-----------
1
2
3

In SQL the difference operation is performed using the EXCEPT keyword.
select
  c.ClientNumber,
  v.StringValue
from dbo.Client as c  -- read from the reference table so even clients which sent no values are reported.
outer apply           -- executes the sub-query once per outer row i.e. per Client
(
  select              -- read the superset
    ss.StringValue
  from dbo.Superset as ss

  except              -- remove those rows..

  select
    StringValue
  from dbo.Upload as u
  where u.ClientNumber = c.ClientNumber  -- .. which were sent by the current row in the outer query (Client).
) as v
order by
  ClientNumber,
  StringValue;

ClientNumber StringValue
------------ -----------
1            NULL         -- NULL since client 1 sent everyting

2            3

3            2

4            1            -- Client4 was absent from Upload but is still reported
4            2
4            3

If the correlated sub-query is too tricky executing the sub-query in a cursor would do the same job.


why computed columns slowing down performance all the sudden? Users were able to run reports before 10 am.
After that same reports became very slow, sometimes users just didn't have patience to wait.
After some troubleshooting I found the column that was causing the delay. It was computed column that uses function in order to bring the result.

Approximately at the same time I got another complain about slow running report, that was always working fine. After some troubleshooting I found the columns that was causing a delay:

where (Amount - PTD) <> 0

And again, the Amount column is computed column.

So my question is: why all of the sudden computed columns that were always part of the reports started to slow down the performance significantly?
What could really happen approx after 10 am?
And what is the disadvantage if I make those columns persisted?

Thank you

--FUNCTION that bring the column:

ALTER FUNCTION [dbo].[CalcInvoiceAmtPTD]
(@SInvNum INT, @entityGuid uniqueIdentifier) RETURNS MONEY
AS
BEGIN
    DECLARE @Amt MONEY

    declare @toplevel uniqueidentifier
    set @toplevel = (select dbo.gettoplevelentity(@entityguid))

    declare @t table
    (
        Guid uniqueidentifier
    )

    insert into @t select * from dbo.getlinkedentities(@toplevel) where guid is not null
    declare @tbl table (amount money, glacctid int)

    select @amt = isNull(sum(amount), 0) from tblfin_journalpostings jp
        inner join tblfin_journal j on j.transactnum = jp.transactnum
            and (voiderfor is null and voidedby is null)and j.transdescid <> 'I'
        inner join tblfin_glaccounttypes glt on glt.glacctid = jp.glacctid and glt.accounttype = 'p'
        inner join @t t on t.guid = jp.entityguid
    where invoicenum = @SInvNum

    RETURN ISNULL(@Amt  , 0)
END

I'm going to throw this out there, since no one else has mentioned it.

Having a scalar valued function in a computed column is an awful idea.

Forces ALL queries against the table to run serially (even if you don't select the computed column)
Forces maintenance (Index, CHECKDB) to run serially, even if you're
on Enterprise
Executes row by row
If used in the WHERE clause, can cause issues with cardinality
estimates

Here are a few blog posts I've written about similar subjects.

Still Serial After All These Years

Scalar Functions In Computed Columns

Scalar Functions In Check Constraints


Exporting database Oracle 7.3 on Windows NT 3.51 How to take dump files on Oracle7.3 running on Windows NT 3.51?

Should you stand in the Orant/bin folder and run one of the programs there like>

exp73 SYSTEM/password@[dbinstance] FULL=Y LOG=EXPORT.LOG

Did not work

These services:

Ok here's what I did:

I was in the ORANT\BIN folder.

I had to figure out what the SID was for the DB. Did that via an Oracle client which I tried to connect remotely to Oracle on the server.

set oracle_sid=a

exp73.exe System/manager file=myexp13xxxx.dmp full=y log=mylog130527.log


Invalid Use of Group Function Using Join SELECT
    a.aircraftid 'Aircraft ID',
    COUNT(s.serviceid)
FROM
    aircraft a
JOIN service s ON
    a.aircraftid = s.serviceid
JOIN(
    SELECT
        (
            COUNT(ser.serviceid) / COUNT(DISTINCT ser.aircraftid)
        ) AS Aver
    FROM
        service ser
) AS se
ON
    COUNT(s.serviceid) > se.Aver
GROUP BY
    a.aircraftid

I have been trying to figure out why I am getting the Invalid Use Of Group Function.

What I am trying to do is show the aircraft ID and how many services it has had if the number of services is above the average number of services.

The problem is that I use group by to count the number of services per aircraft. I tried to have the average in the where clause, but I also got an error using the following code:

SELECT
    a.aircraftid,
    COUNT(s.serviceid)
FROM
    aircraft a,
    service s
WHERE
    COUNT(s.serviceid) >(
        COUNT(s.serviceid) / COUNT(DISTINCT s.aircraftid)
    )
GROUP BY
    a.aircraftid

Try:

SELECT  a.aircraftid 'Aircraft ID',
        COUNT(s.serviceid)
FROM    aircraft a
JOIN    service s
    ON  a.aircraftid = s.serviceid
GROUP BY    a.aircraftid
HAVING COUNT(s.serviceid) > (   SELECT  COUNT(serviceid) / COUNT(DISTINCT aircraftid)
                                FROM    service )


Force Sql server to use index seek in merge join I am testing Merge join in sql server. I have an INNER JOIN and force the optimizer to do MERGE JOIN:

ID in Personal Table is a Primary key
ID in Abteilung Table is a primary key
There is no other Indexes in these tables

 select * from  [dbo].[Personal] as P inner join [dbo].[Abteilung] as A
 on  P.[ID]= A.[Personal_ID]   OPTION (MERGE JOIN)

then Optimizer uses this plan to run my query:

I would like to force optimizer to use index seek instead of Table Scan

What I did to achieve this aim:

Defining a grouped index in the Table Abteilung on columns ID, Personal_ID. If I run the query, the execution plan is changes like this:

here I have Clustered index scan, but not clustered index seek

Using FORCESEEK hint. If I am using this hint, an error occures by running the query:

  Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any
  hints and without using SET FORCEPLAN.

If I add a where clause in the query, then the query would be change to:

  select * from  [dbo].[Personal] as P inner join [dbo].[Abteilung] as A
  on P.[ID]= A.[Personal_ID] where A.[Personal_ID]=2
  OPTION (MERGE    JOIN)

in this situation I have only Clustered index scan.

My question:

How do I change my query to have Clustered Index Seek in the bottom line?

To get a clustered index seek, you'd need a clustered index that supports your filter (e.g. leading key would have to be Personal_ID, not ID).

You can't force a seek if there's no index with a leading column of Personal_ID that supports the filter.

This does not mean you should change the existing clustered index, unless this is the only query you ever run against the table.

And while you could create a non-clustered index with Personal_ID as the key column, a seek on that index might not be what you want anyway - since you're using SELECT * (are you really sure you need all columns from both tables?), it will need to go fetch the rest of the columns from the clustered index anyway, and if there are more than some number of rows returned, at some point a seek (well, what would amount to a range scan disguised as a seek) + lookups will be more expensive than a regular scan.

Why do you think you need a seek here? How many rows does the query return, how wide are they, and how long does it take? Is this just educational, or are you under the assumption that a seek will always perform better than a scan? (It won't, btw.)

Some useful reading:

Bad habits to kick : using SELECT * / omitting the column list
Assumption #2 : A seek is better than a scan, always


Queries that ran, and the number of times they ran I want to profile a running web application in dev on a mysql 5.5 database.  The web application uses hibernate and I am able to see that a rather large number of queries are run against the database.

I'm able to see long running queries with logging, but that is not a concern as they are all running fairly quickly.

What I would like to know is, what queries are being run, and how many times they are run.

for the query monitoring you have two solutions from MySQL level:

solution 1:

create log file for the query by editing my.ini or my.cnf

slow_query_log_file = "C:/slowquery.log"
log = log_file_name

solution 2:

Create log tables:

Slow queries:

CREATE TABLE `slow_log` (
   `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
                          ON UPDATE CURRENT_TIMESTAMP,
   `user_host` mediumtext NOT NULL,
   `query_time` time NOT NULL,
   `lock_time` time NOT NULL,
   `rows_sent` int(11) NOT NULL,
   `rows_examined` int(11) NOT NULL,
   `db` varchar(512) NOT NULL,
   `last_insert_id` int(11) NOT NULL,
   `insert_id` int(11) NOT NULL,
   `server_id` int(10) unsigned NOT NULL,
   `sql_text` mediumtext NOT NULL,
   `thread_id` bigint(21) unsigned NOT NULL
  ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'

General queries

CREATE TABLE `general_log` (
   `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
                          ON UPDATE CURRENT_TIMESTAMP,
   `user_host` mediumtext NOT NULL,
   `thread_id` bigint(21) unsigned NOT NULL,
   `server_id` int(10) unsigned NOT NULL,
   `command_type` varchar(64) NOT NULL,
   `argument` mediumtext NOT NULL
  ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'

  SET global general_log = 1;
SET global log_output = 'table';

To show the log use the following command:

select * from mysql.general_log;

if you want the slow query:

select * from mysql.slow_log;

To count the number of query executing:

show session status like "Queries";


mysql rds read replica replication error I use amazon rds and have a cross region replica. While I was doing some schema changes my read replica gave me this error and replication stopped.

  Message : Read Replica Replication Error -  SQLError:  1146, reason:
  Error Table phppoint_koreannoona.phppos_items doesn't exist on query.
  Default database: DB_NAME;. Query: ALTER TABLE phppos_items DROP
  COLUMN aaatex_qb_item_name

What would cause this? The table is in the master. My migration DID remove that field but entire table was missing in slave.

I had to rebuild slave.

What would cause this?

Let's go into facts, then we will later have to go into speculation:

Table phppoint_koreannoona.phppos_items doesn't exist on query

You executed an ALTER TABLE query on a table that didn't exist. Your table existed on the master at the time the alter was ran (otherwise, it wouldn't have gone through the binlog and tried to be executed on the replica), but didn't exist on the replica. This makes replication break because it cannot continue in a consistent way.

What can make a table exist on the master and not on the replica? Several options:

The table was never there. In order to create a replica, RDS does a snapshot of the master and transfer it away. Depending on when or how it is done, the replica can be non-consistent (for example, I would be skeptical of a snapshot done at the same time than a running schema change). Maybe it was a memory only table and it wasn't properly transferred to disk? Maybe it was in a particular engine that has issues with transactionality (MyISAM).
The table was there, but it was deleted somehow.
Something else happened that caused the replicas to go out of sync. The exact reason will depend highly on the environement, configuration and the application. Some examples include:

A crash of the replica in a non-crash safe configuration
Replication coordinates were changed inappropriately or other maintenance introduced a mistake on replication
Unsafe write statements or configuration is used, which leads to inconsistencies
Filtering is used, leading to data inconsistencies
Queries were run on the master with replication/binlog disabled

How to fix that?

The safest way to fix inconsistencies on a replica is to recreate it from scratch. You can copy a single table and synchronize it relatively easily if it has a low amount of writes, but that will not prevent you from having other data drift on other tables.

How to prevent that from happening again?

The most important part is to have checks in places to detect issues early. Tools such as pt-table-checksum or simple dumps and compares may allow you to detect differences earlier. It is impossible, with the information given, to say exactly what caused that, as mysql configuration will be safe or not depending on the specific setup. Some general advice:

Try using ROW based replication, it is usually more resilient against bad configuration/queries
Check for "unsafe statements for binlog" warnings on the logs
Check the logs for replication changes due to improper maintenance
Make sure replicas are really in read only and they cannot be written or manipulated out of band
Use innodb on all your tables, as well as crash-resilient configuration for your replicas


How to handle Schedules when moving Jobs to another server? We are migrating our SQL Server 2008 R2 to another physical server. For this, I will script out all jobs and run it on the target server. My question is about handling the schedules:
When a job is scripted out, there is a parameter called @schedule_uid of type uniqueidentifier for the step sp_add_jobschedule. Should I include or remove this parameter from the script? Is this value specific to the original server and therefore should not be included? If so, how can I create the matching one on the target server?
Thank you.

From my experience scripting out jobs and running on another servers works fine - including schedulers.
You might want to check job owner login to appear on destination server.


pgbouncer and ssl connection I have three Servers, two of whom are running postgres 9.5.3 and the third runs pgbouncer 1.7.2.

i can access one of the two postgresql instance through pgbouncer and it all works perfect. the other postgresql Servers, lets call it P1 is not under my control and i have to connect using ssl so it got and therefor the following line was added to this Servers pga_hba.conf

hostssl    all       all             ###.###.###.###/32               md5

I can connect to P1 from pgbouncer server with psql with no Problem.
But when i connect via pgbouncer to P1 i get this error message

psql: ERROR:  no pg_hba.conf entry for host "XXX.XXX.XXX.XXX", user "myuser", database "mydb", SSL off

here is my pgbouncer.ini

[databases]
my_database = host=xxx.xxx.xxx.xxxx port=5432 dbname=mydb user=myuser password=mypassword client_encoding='UTF8'

[pgbouncer]
listen_addr = *
listen_port = 5432
auth_type = md5

auth_file = /etc/pgbouncer/userlist.txt
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid

admin_users = rms_admin

pool_mode = session

server_reset_query = DISCARD ALL
server_check_query = select 1

default_pool_size = 40

stats_period=300

log_pooler_errors = 1

i've tried add sslmode=require on the connection string to my_database in combination with ignore_startup_parameters but that led to the following error in the pgbouncer log .

ERROR skipping database my_database because of unknown parameter in connstring: sslmode

how can i make pgbouncer build up an ssl Connection?

TLS between pgbouncer and server is not enabled through the connect string, but with server_tls_sslmode, which is disabled by default.

It should be set to at least prefer, and also some of the other server_tls_* parameters might be needed to, depending on the TLS configuration at the other end.

See https://pgbouncer.github.io/config.html#tls-settings for all the details.


Force exact match from postgres ts_query I've constructed a column of stem words called journal_tsv from the column journal_name. I've then created a GIN index on journal_tsv. Here's and example of the type of query I'm running:

SELECT DISTINCT(journal_name) FROM my_table WHERE journal_tsv @@ to_tsquery('SCIENTOMETRICS');

This returns a list of 15 journal names

                            journal_name
--------------------------------------------------------------------------------------------------------------------------------------------
 ISSI 2005: Proceedings of the 10th International Conference of the International Society for Scientometrics and Informetrics, Vols 1 and 2
 SCIENTOMETRICS
 PROCEEDINGS OF ISSI 2009 - 12TH INTERNATIONAL CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, VOL 1
 EVALUATION OF RESEARCH BY SCIENTOMETRIC INDICATORS
 8TH INTERNATIONAL CONFERENCE ON SCIENTOMETRICS AND INFORMETRICS, VOLS 1 AND 2 - ISSI-2001, PROCEEDINGS
 ISSI 2005: PROCEEDINGS OF THE 10TH INTERNATIONAL CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, VOLS 1 AND 2
 JOURNAL OF SCIENTOMETRIC RESEARCH
 PROCEEDINGS OF ISSI 2009 - 12TH INTERNATIONAL CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, VOL 2
 PROCEEDINGS OF ISSI 2011: THE 13TH CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, VOLS 1 AND 2
 PROCEEDINGS OF ISSI 2015 ISTANBUL: 15TH INTERNATIONAL SOCIETY OF SCIENTOMETRICS AND INFORMETRICS CONFERENCE
 COLLNET JOURNAL OF SCIENTOMETRICS AND INFORMATION MANAGEMENT
 SEVENTH CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, PROCEEDINGS - 1999
 Proceedings of ISSI 2007: 11th International Conference of the International Society for Scientometrics and Informetrics, Vols I and II
 PROCEEDINGS OF ISSI 2007: 11TH INTERNATIONAL CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, VOLS I AND II
 14TH INTERNATIONAL SOCIETY OF SCIENTOMETRICS AND INFORMETRICS CONFERENCE (ISSI)
(15 rows)

In most circumstances this works really well but what I can't figure out is how to restrict the results to exact matches? Most of the time fuzzy searches are exactly what I need but how can I use the same type of query to only return exact matches?

I'm trying to get the results I would get from this query:

SELECT DISTINCT(journal_name) FROM my_table WHERE journal_name = 'SCIENTOMETRICS';

But with the performance gains from using tsvector.

                                version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)

As filiprem says, you can combine the match and the equality by an AND.  It will use the FTS index to get candidates and then just filter out the ones that it needs to for the equality

But you can also build a regular index on 'journal_name' and then just write the query naturally. The 2nd index will take some maintenance overhead, but less than the index your already have does.  If you do this type of query a lot, you should probably add the extra index.


Copy database schema from SQL Server 2008 to SQL Server 2005 Possible Duplicate:
  Downgrading from SQL Server 2008 to 2005

Is it possible to copy the database schema from SQL Server 2008 to SQL Server 2005,

I don't need data from the tables, just the empty database structure with triggers, stored procedures, ...

If you aren't using any SQL2008+ only features (e.g. compression) you will be able to script out the objects you require and execute in SQL2005. If you're not sure if any SQL2008+ features are in use, trial and error (script and execute, see what happens) will soon tell you where the problems are.

This is very different to restoring a backup from 2008 to 2005, which is not possible.


Oracle 11gR2 archive log destinations I want to add a second archive log destination to an Oracle 11gR2 RAC database with ASM, the idea is that I will then have some redundancy if the primary storage should fail.

Archive log is already enabled, and logs are currently archived in the Fast Recovery Area. However, as far as I can see, none of the LOG_ARCHIVE_DEST_n init parameters have been configured.

Is there now an unset default that specifies the Fast Recovery Area as a log destination?

If that is the case, I assume I must now configure two destinations. One entry to continue writing archive to that default destination, and one entry for the additional backup destination. If so, how do I specify the existing default location in the fast recovery area?

Will this work, is there another preferred way?

alter system set log_archive_dest_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST';
alter system set log_archive_dest_2 = 'LOCATION=/s01/archive/TESTDB';
alter system set log_archive_dest_state_1 = enable;
alter system set log_archive_dest_state_2 = enable;
alter system set log_archive_min_succeed_dest = 1;

Naturally, the /s01 filesystem is available on all (both) cluster nodes.

The 11.2 manual says you should also check plain LOG_ARCHIVE_DEST. Although it comes from Standard Edition, it is still valid in case none of numbered Enterprise Edition destinations LOG_ARCHIVE_DEST_n are defined. I haven't tested that personally.

In case all are unset, the another 11.2 manual says

  If you configure a Fast Recovery Area (by setting the
  DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE parameters) and
  do not specify any local archive destinations, the database
  automatically selects the Fast Recovery Area as a local archive
  destination and sets LOG_ARCHIVE_DEST_1 to USE_DB_RECOVERY_FILE_DEST.

I vaguely remember I've seen somewhere that 11.1 database in such situation defaults to USE_DB_RECOVERY_FILE_DEST but via LOG_ARCHIVE_DEST_10.

In all, your configuration and syntax is correct.

alter system set log_archive_min_succeed_dest = 1;

Normally all destinations are optional. But your setting is ok; it could somewhat decrease potential data loss (loss of redo log not yet archived).

Further two items to take care of:

a copy of redo logs on a second disk
a copy of control file on a second disk


#1044 - Access denied for user '#username'@'10.%' to database '#database' I'm trying to connect my webpage to mySQL with ipage hosting. When I copy the snippet:

<?php
$link = mysql_connect('username.ipagemysql.com', 'username for mySQL', 'password for mySQL');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_select_db(name of database);
?>

from ipage and link the .php-file to my website in an attempt to establish a test connection I get an error message:

"Could not connect: Access denied for user '#username'@'boscustweb3102.eigbox.net' (using password: YES)"

When I use mySQLadmin on the ipage site and enter the query:

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'IP-of-DBServer';

I get another error message:

"#1044 - Access denied for user '#username'@'10.%' to database '#database'"

I am currently using Dreamweaver and trying to establish a form of validation login with only 1 user to be able to create events for a calendar and an input form to create news for my website.

1st edit:

Here's some extra information:

When I try this query:

SELECT USER() RequestedUserLogin,CURRENT_USER() AllowedUserLogin;

I get the results:

RequestedUserLogin: #username@myadmin01.int.bizland.net
AllowedUserLogin: #username@10.%

By searching for ipage mySQL IP addresses by using the query:

SHOW VARIABLES WHERE Variable_name = 'hostname'

I got this:

Hostname: boscustsqlm56.eigbox.net

To find the appropriate IP adresses I used the website service http://www.hcidata.info/host2ip.cgi:

boscustsqlm56.eigbox.net - 38.113.1.135myadmin01.int.bizland.net - Cannot be resolved to an IP address

have you tried using a password for your user also, when granting privileges?

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'IP-of-DBServer' identified by 'password for mySQL';


Converting a date to a datetime in MySQL I'm supposed to add time to a date field. We use MySQL with python3, alembic and sqlalchemy. Now I've written a conversion program. It is my first program of such a kind. I'm not certain how it will work, how I can also migrate the data to the new field or if that is a different scenario.

I'm not sure about ? downgrade , what should it do if it is downgrading? In the examples I've looked at examples where nearly all do an ADD TABLE and therefore at the downgrade those function just delete what they created. But what about an ALTER tatement, how can I roll it back in the downgrade function if downgrading (which probably not will happen).

Is there anything else that I should think about?

def upgrade(**args):
    db_update_context = mnet_get_required_argument("db_update_context", **args)

    if not mnet_column_exists(table_name, column_name, **args):
        sql = textwrap.dedent("""\
                ALTER TABLE {} MODIFY COLUMN `{}` datetime;
                  """.format(table_name, column_name))
        mnet_execute(db_update_context, sql)

        sql = textwrap.dedent("""\
                ALTER TABLE {} MODIFY COLUMN `{}` datetime;
                  """.format(table_name, column_name2))
        mnet_execute(db_update_context, sql)

def downgrade(**args): # TODO: What is supposed to happen here
    columns_to_drop = [column_name]
    for column_to_drop in columns_to_drop:
        medinet_drop_column(table_name, column_to_drop, **args)


Strange query plan when using OR in JOIN clause - Constant scan for every row in table I'm trying to produce a sample query plan to show why UNIONing two result-sets can be better than using OR in a JOIN clause. A query plan I've written has me stumped. I'm using the StackOverflow database with a nonclustered index on Users.Reputation.

The query is

CREATE NONCLUSTERED INDEX IX_NC_REPUTATION ON dbo.USERS(Reputation)
SELECT DISTINCT Users.Id
FROM dbo.Users
INNER JOIN dbo.Posts
    ON Users.Id = Posts.OwnerUserId
    OR Users.Id = Posts.LastEditorUserId
WHERE Users.Reputation = 5

The query plan is at https://www.brentozar.com/pastetheplan/?id=BkpZU1MZE, query duration for me is 4:37 min, 26612 rows returned.

I haven't seen this style of constant-scan being created from an existing table before - I'm unfamiliar with why there's a constant scan ran for every single row, when a constant scan is usually used for a single row inputted by the user for example SELECT GETDATE(). Why is it used here? I would really appreciate some guidance in reading this query plan.

If I split that OR out into a UNION, it produces a standard plan running in 12 seconds with the same 26612 rows returned.

SELECT Users.Id
FROM dbo.Users
    INNER JOIN dbo.Posts
       ON Users.Id = Posts.OwnerUserId
WHERE Users.Reputation = 5
UNION
SELECT Users.Id
FROM dbo.Users
    INNER JOIN dbo.Posts
       ON  Users.Id = Posts.LastEditorUserId
WHERE Users.Reputation = 5

I interpret this plan as doing this:

Get all 41782500 rows from Posts (the actual number of rows matches the CI scan on Posts)
For each 41782500 rows in Posts:

Produce scalars:
Expr1005: OwnerUserId
Expr1006: OwnerUserId
Expr1004: The static value 62
Expr1008: LastEditorUserId
Expr1009: LastEditorUserId
Expr1007: The static value 62

In the concatenate:

Exp1010: If Expr1005 (OwnerUserId) is not null, use that else use Expr1008 (LastEditorUserID)
Expr1011: If Expr1006 (OwnerUserId) is not null, use that, else use Expr1009 (LastEditorUserId)
Expr1012: If Expr1004 (62) is null use that, else use Expr1007 (62)

In the Compute scalar: I don't know what an ampersand does.

Expr1013: 4 [and?] 62 (Expr1012) = 4 and OwnerUserId IS NULL (NULL = Expr1010)
Expr1014: 4 [and?] 62 (Expr1012)
Expr1015: 16 and 62 (Expr1012)

In the Order By sort by:

Expr1013 Desc
Expr1014 Asc
Expr1010 Asc
Expr1015 Desc

In the Merge Interval it removed Expr1013 and Expr1015 (these are inputs but not outputs)
In the Index seek below the nested loops join it is using Expr1010 and Expr1011 as seek predicates, but I don't understand how it has access to these when it hasn't done the nested loop join from IX_NC_REPUTATION to the subtree containing Expr1010 and Expr1011.
The Nested Loops join returns only the Users.IDs that have a match in the earlier subtree. Because of predicate pushdown, all rows returned from the index seek on IX_NC_REPUTATION are returned.
The last Nested Loops join: For each Posts record, output Users.Id where a match is found in the below dataset.

The plan is similar to the one I go into in more detail here.

The Posts table is scanned.

For each row it extracts the OwnerUserId and LastEditorUserId. This is in a similar manner to the way UNPIVOT works. You see a single constant scan operator in the plan for the below creating the two output rows for each input row.

SELECT *
FROM dbo.Posts
UNPIVOT (X FOR U IN (OwnerUserId,LastEditorUserId)) Unpvt

In this case the plan is a bit more complex as the semantics for or are that if both column values are the same only one row should be emitted from the join on Users (not two)

These are then put through the merge interval so that in the event the values are the same the range is collapsed down and only one seek is executed against Users - otherwise two seeks are executed against it.

The value 62 is a flag meaning that the seek should be an equality seek.

Regarding

  I don't understand how it has access to these when it hasn't done the
  nested loop join from IX_NC_REPUTATION to the subtree containing
  Expr1010 and Expr1011

These are defined in the yellow highlighted concatenation operator. This is on the outer side of the yellow highlighted nested loops. So this runs before the yellow highlighted seek on the inside of that nested loops.

A rewrite that gives a similar plan (though with the merge interval replaced by a merge union) is below in case this helps.

SELECT DISTINCT D2.UserId
FROM   dbo.Posts p
       CROSS APPLY (SELECT Users.Id AS UserId
                    FROM   (SELECT p.OwnerUserId
                            UNION /*collapse duplicate to single row*/
                            SELECT p.LastEditorUserId) D1(UserId)
                           JOIN Users
                             ON Users.Id = D1.UserId) D2
OPTION (FORCE ORDER)

Dependant on what indexes are available on the Posts table a variant of this query may be more efficient than your proposed UNION ALL solution. (the copy of the database I have has no useful index for this and the proposed solution does two full scans of Posts. The below does it in one scan)

WITH Unpivoted AS
(
SELECT UserId
FROM dbo.Posts
UNPIVOT (UserId FOR U IN (OwnerUserId,LastEditorUserId)) Unpivoted
)
SELECT DISTINCT Users.Id
FROM dbo.Users INNER HASH JOIN Unpivoted
       ON  Users.Id = Unpivoted.UserId
WHERE Users.Reputation = 5


show detail processlist like at mariadb on maxscale on mariadb when i need check what are query is running or sleep on mariadb instance.
i can see with SHOW PROCESSLIST or select * information_schema.processlist.
But after mariadb join and client connect to maxscale i can't see processlist like SHOW PROCESSLIST or select * information_schema.processlist
any advice ?
maxscale configuration
[maxscale]
threads=auto
max_auth_errors_until_block=0
admin_host=192.168.101.107
admin_port=8989
admin_enabled=1

Edit.
I already update paramater at maxscale configuration
[maxscale]
threads=auto
max_auth_errors_until_block=0
admin_host=192.168.101.107
admin_port=8989
admin_enabled=1
retain_last_statements=20
dump_last_statements=on_error

query already show from command
maxctrl show sessions

but there is query from last execution on each session. and i can't see the different like sleep or still running when i run command SHOW PROCESSLIST on mariadb.

Only users with PROCESS privilege can see processes other than their own.
From the SHOW PROCESSLIST Statement (MySQL Documentation):

SHOW PROCESSLIST shows which threads are running. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field.

And from Privileges Provided by MySQL (MySQL Documentation):

Enables display of information about the threads executing within the server (that is, information about the statements being executed by sessions). The privilege enables use of SHOW PROCESSLIST or mysqladmin processlist to see threads belonging to other accounts; you can always see your own threads. The PROCESS privilege also enables use of SHOW ENGINE.


Unique, Non-Clustered Index and Unique Clustered Index on Same Column I've inherited a project and I'm seeing something pretty interesting that I haven't come across in the past.  Looking for guidance.

The Primary Key on a table is a Unique, Non-Clustered Index and there is also a Unique Clustered Index on same column.

Is there any situation where you'd want this, or should I make alterations so that the primary key is a unique, clustered index?

Thanks in advance.

In SQL Server the PK index will, by default, be a clustered index, containing all of the data rows at the leaf level of the index.

Queries of the form

SELECT COUNT(*) FROM T

or

SELECT ... FROM T ORDER BY ID OFFSET 100 ROWS FETCH NEXT 10 ROWS ONLY

Would normally use the clustered index.  But scanning the whole clustered index to count the rows, or to fetch a page of rows from deep in the sort order is expensive.

So it's a separate non-clustered index on the same key is sometimes created to support these queries.


Completely remove additional data file from SQL Server 2012 DB Hope someone can help me on this one.

I have created by mistake an additional data file in the incorrect file group, I need to either remove the file completely or move the file to the default file group. Looks like changing files from one file group to another is not possible?

The data file is empty so I tried removing the file using alter DB remove file, when I run DBCC showfilestats, the file is gone, but when I check SYS.master_files, I can see still the entry of the file I want removed.
Is there a way to remove it completely, even deleting it physically from the server as I need to recover the space?

Kind regards,
Jose.

Looks like changing files from one file group to another is not
  possible?

Correct, you would need to create a new file on the correct filegroup and recreate all your objects on the correct filegroup (if any existed) to move them, as you cannot move a file between filegroups.

  The data file is empty so I tried removing the file using alter DB
  remove file, when I run DBCC showfilestats, the file is gone, but when
  I check SYS.master_files, I can see still the entry of the file I want
  removed. Is there a way to remove it completely, even deleting it
  physically from the server as I need to recover the space?

I've seen this before in SQL Server 2012. If you query sys.master_files you will see the drop_lsn field has an LSN value in it for this file. This indicates the LSN at which this file was dropped. You need to perform a log backup to clear this value, then it will disappear from the DMVs.


How can I change three hundred procedures at once? I need to change about 300 procedures and packages in the database, due to a migration that will accomplish this weekend. We will do a migration from one server to Exadata.

However the database has been developed in a very sloppy way. The bank carries out a number of text files written directly to disk, but nobody uses directories. In Exadata, the path to writing the files will be different due to the use of DBFS, for that I must change all calls via UTL_FILE.

Let me give an example: Currently the code is this:

file: utl_file.fopen = ('/file/folder/documents', filename, 'W');

What I want to do:
Create a directory

create or replace directory directory_name as '/file/folder/documents';

Change the 300 procedures for:

file: = utl_file.fopen ('directory_name', filename, 'W');

During migration only change the directory:

create or replace directory directory_name as '/dbfs/documents';

The real question: Is there a way I make a search and replace changing all 300 procedures in the database at once?  I mean, there's a way to change all '/file/folder/documents' to directory_name?

If you script the procedures out to a file, the search/replace can be trivially dome with a sed script along the lines of

s/\/file\/folder\/documents/directory_name/g

(note not tested, just off the top of my head, but you can fiddle with it).

Then you can re-load the stored procedures.  Note that if you're frigging with the code base you should really test what you're doing rather than doing a blind search/replace on production code.  What could possibly go wrong?


Why does my SQL Server show more than half a million active tasks? select task_state,count(*)
from sys.dm_os_tasks
group by task_state

I ran the above statement on a SQL Server instance, and found it had about 633,000 records.

task_state
-------------- -----------
RUNNABLE       2
RUNNING        32
DONE           633115
SUSPENDED      99

How can I Close/Kill the useless tasks?

The MDW data collector have about 4000 page allocate in tempdb per time.

And this cause the IO pressure when server in busy time.

This is production server, We do not want to restart the service.
And the version number is 11.0.3000.

The Max Worker Count is :1216
CPU Count:48
Hyperthread ratio:12
MaxDop: 8

Scheduler_Id is 0 - 47 and the amount of rows are average.
and other columns are null.

This is a bug in SQL Server 2012. Details of a hotfix are at

FIX: A memory leak occurs when you enable AlwaysOn Availability Groups or SQL Server failover cluster in Microsoft SQL Server 2012


SQL Server logshipping: Extend the LS_Alert window We have a logshipping setup, where the LS_Restore job on the secondary database is switched off every night for the datawarehouse to grab its data. The restore cycle is set to 15 minutes and if LS_Restore would kick in, the datawarehouse connection would get severed - the interface therefore switches off LS_Restore. When the datawarehouse interface is done, the LS_Restore job is turned back on.
However, this window can be longer than one hour (by just ten minutes or there-abouts), and this then triggers the LS_Alert job to raise errors.
How can we extend the range on LS_Alert to, say, 1,5 hours?

update the relevant row and column (backup_threshold (in minutes)) in the log shipping config table
https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/log-shipping-monitor-primary-transact-sql?view=sql-server-2017


Understanding The Transaction Log I used to work with MySQL/PhpMyAdmin for web application and portable SQLCE for light local program. I just started with SQL Server Express 2016 and SSMS and just cannot understand Transaction Log. I have read various posts including MSDN: The Transaction Log and A beginner’s guide to SQL Server transaction logs, but I still cannot conceptually understanding even one single need for Transaction Log (the *.ldf file) and my lack of understanding leads me to think the log file is just a complete waste of disk space.

In MSDN it states the transaction log supports the following operations:

  Individual transaction recovery.

So it logs all my query and data? Then what is the point of having the
*.mdf data file in the first place? Alternatively, if I back up my data and database schema, I can always recreate my database and insert back my data, and I have no need for my *.ldf

  Recovery of all incomplete transactions when SQL Server is started.

“Incomplete Transaction" - does that mean if there is a power outage before a process is completed, the log file will resume/repair the transaction automatically when the power is back on and server is running?

  Rolling a restored database, file, filegroup, or page forward to the
  point of failure.

"Rolling"... does that mean there are subversions of database within the log file like a backup machine?

  Supporting transactional replication.

I found no explanation. Does it mean I can repeatedly insert duplicate rows of the same data? It make no sense.

  Supporting high availability and disaster recovery solutions: Always
  On Availability Groups, database mirroring, and log shipping.

So..if my *.mdf database file got corrupted, the log file will mirror and recreate the whole thing uncorrupted?

At last, the log file seems contain everything there is in a *.mdf data file plus a good number of functions wrapping it. I thought either the *.mdf file is not needed, or alternatively if my schema and data is regularly backup, then there is no need for log file. But I am sure my understanding is incorrect.

I'm going to attempt to show you why you have a vested interest in the Sql Server Transaction log.

Bear with me as I lay out a few assumptions...

For simplicity sake, I'm assuming FULL RECOVERY model.
Because you're following BEST PRACTICES, you've separated the MDF/NDF files onto different drives than where your LDF file is stored.  So, you have your MDF on Drive-E and your LDF on Drive-F. You'll see why this is important later.
Your company wants to minimize data loss in the event of a disaster,
so you're actually backing up your transaction logs every minute.
For disaster recovery purposes, you have some sort of HA/DR configured.  Let's just go with simple transactional replication.

Here's a simple scenario using two different transactions:

--Start transaction for Salary
BEGIN TRANSACTION

UPDATE Payroll SET YourSalary = YourSalary + 5000.00

COMMIT
--Congrats, you just got a $5,000 raise!

--Start a transaction for BankCheckingAccount
BEGIN TRANSACTION

--Subtract $500.00 from your bank checking account
UPDATE YourBankCheckingAccount SET AccountBalance = AccountBalance - 500.00

--No COMMIT yet.

When those statements executed, Sql Server read data into memory (if it wasn't already there), updated the data pages in memory and wrote that information into the transaction log indicating the update.  It tracks the BEFORE and AFTER of the data modification in the transaction log.  The data pages only have the 'final' result.  However, at this point, the modified data pages are not 'necessarily' written to disk for performance reasons.  Imagine how slow things would be if you had to wait on disk for each data 'page' modification to be written.  Data modifications are always written to the log before a 'completion' response is returned to the user.  That's the 'D' part of ACID.  At this point, the data page in memory is flagged as IsDirty meaning that one or more modifications have been made to it since it was read into memory and those changes have not been written to disk.  These 'dirty' pages are written to disk during a CHECKPOINT.

Now, back up your database (without committing that transaction).  The back up process initiates the CHECKPOINT process and the 'dirty' pages are written to disk.  Keep in mind that you have NOT committed this transaction and are free to undo (rollback) it at any time.  Your updated information is in that backup.  That backup also has enough information (from the log) to identify committed and uncommitted transactions at the time the backup was taken.  If you restored that backup to another database, I'm sure you'd like your $500.00 back, right?  During a restore to the other database, Sql Server would put the data page (with your -$500.00 account balance) back to disk.  However, after the data pages have been restored, Sql Server goes through 'crash-recovery' and uses the transaction log to identify transactions that need to be undone (due to uncommitted transactions like yours) and also transactions that need to be 'redone' (due to data being modified and committed after the backup started, but are not reflected in the actual data pages because the updates occurred AFTER the CHECKPOINT.)

Now, on to another scenario still using our transactions.  Your Salary was updated (and committed) 5 minutes ago.  Your E-drive, which hosts the MDF file suffers a disk failure and Sql Server goes down due to not being able to access it.  Your F-Drive (which hosts the LDF file) is intact.  Again, because you're following BEST PRACTICES, you've been taking FULL (and possibly DIFFERENTIAL) backups. Your last FULL backup was 1 hour ago, but that was BEFORE your Salary increase transaction took place.  The information about your Salary increase is in the transaction log.  At this point, you should attempt to do a 'tail-of-the-log' backup of the transaction log to try and salvage any transactions that occurred between the last log backup and the failure.   When you restore your backup from 1 hour ago and subsequent log restores, Sql Server looks through the transaction log for transactions that need to be undone (not committed) or redone (committed).  Your salary increase needs to be 'redone' because it has been committed but it's not in your backup.  Your transaction log (and backups) saves the day.

The Sql Server transaction log is a 'circular' file.  Let's say your transaction log was created with a maximum size of 1gb.  You have complete control over how you define your transaction log space.  As transactions occur, information about those transactions is written to the transaction log.  Eventually, you'll run out of that 1GB space allocated for your log.  When you backup your transaction log (remember, you're doing that every minute), Sql Server is able to 'reuse (circular)' transaction log space allocated as long as transactions logged in that space have committed.  Transaction log space allocated to transactions that are are still 'in-flight' (not committed) OR that have not been completely replicated (you're using transactional replication) cannot be release during a transaction log backup because the log needs to be used to 'rollback' the update should the user decide to 'rollback' the transaction or the system goes down.  In transactional replication (or other HA/DR scenarios like mirroring, etc.), those transactions need to be completely replicated before the log space can be reused.  Actually, in our scenario, our uncommitted bank account transaction will prevent the log from being reused and you'll eventually run out of log space even though you're taking log backups every minute.  So make sure transactions are being committed or rolled back in a timely fashion.

Speaking of 'Rolling a restored database, file, filegroup, or page forward to the point of failure.'

Your company wants to minimize data loss and you're taking transaction log backups each minute.  Remember, only the log knows the BEFORE and AFTER information for a transaction.  The only thing in the MDF/NDF files is the data as it is NOW and that's only if a CHECKPOINT has occurred since the update.  Now assume your entire server dies.  You have FULL backups AND you have transaction log backups to the minute just before the server died.  Knowing that this possibility could happen, you knew to store your backups on a backup server that was isolated from THIS server.  Your $5000 raise is in those backups AND you want your $500.00 back into your checking account.  Those changes are NOT in your FULL backup because it was taken over an hour ago.  When you get around to being able to restore your backups, you restore your FULL (and maybe a DIFFERENTIAL) and all of the transaction logs you have since the FULL/DIFF (just up to a minute before disaster).  Sql Server then goes through crash-recovery.  It makes sure your $500.00 back account transaction is 'undone' because it was uncommitted and then makes sure your $5000 raise is 'redone' because it was committed.

As far as replication, Sql Server uses the transaction log to replicate these changes to remote servers, not the data pages.  Also, the transaction log space cannot be 'reused' until the transactions participating in replication or mirroring have been 'replayed' on the target server.

To summarize - the transaction log is your BEST friend.

It supports 'Individual transaction recovery' and 'Recovery of all incomplete transactions when SQL Server is started' during crash-recovery to undo uncommitted transactions and redo committed transactions because there is no guarantee these data page modifications were actually written to disk before disaster struck.
Similarly, it supports 'Rolling a restored database, file, filegroup, or page forward to the point of failure' again using the restoration of transaction logs and crash-recovery.
It supports 'transactional replication' and 'high availability and disaster recovery solutions: Always On Availability Groups, database mirroring, and log shipping', by sending the transaction logs to the remote server for 're-play'

My attempt to explain the usefulness of the transaction log is minimal at best.  Refer to Paul Randal's excellent post about how all of this stuff works.


SQL Server Backup and Restore Incrementally I am investigating a reporting solution for a production database.

We currently have a production db which backs up fully at 01:00 each day. This backup is then copied using a Robocopy job to a separate SQL db which restores it.

We then report on the second database in order to keep some extensive queries away from production.

I am hoping someone can help me with my thoughts - is it possible instead of having our current setup - to copy only updated or newly added rows across incrementally (note not deleted - I need these to stay in the second database)?

Thanks in advance.

From the way I see it, you have two pretty good options here:

Replication
Log Shipping

If you setup transactional replication you could have your data modifications replicated to a subscriber database (or databases).

With log shipping it essentially would be transferring transaction log backups (note, you'd need to have your database in the full recovery model to take transaction log backups).  Then you could have your secondary database in standby mode, allowing for read-only traffic.

The way I would choose between these two options is the level in which you want reporting and data movement to your secondary server.  Are you reporting on the entire database?  In that case, I'd opt for log shipping.  But if you're primary reporting on a single table or just a handful of tables and you don't necessarily want the entire database's footprint living on the secondary server, then replication would be a better option.

EDIT

As per your comments below, it looks like you don't want to transmit DELETE operations to your reporting server.  In this case, Log Shipping wouldn't meet the requirements because all logged operations would be on the reporting server, including your DELETEs.

What you can do with replication is set it up so that it only replicates INSERT and UPDATE operations.

See the below screenshot.  Basically when you create your publication, you can set many properties for each article.  One of the options is what action to take for each operation:  INSERT, UPDATE, and DELETE.  All you need to do is specify to not replicate DELETE operations:


Replication compatibility between minor MySQL versions I have a dedicated server running MySQL 5.7, installed using MySQL's Yum repository.

Yum always installs the latest version, which is MySQL 5.7.11 at the time of this writing.

I want to replicate this server to Amazon RDS, to have an offsite, real-time backup.

The problem is, RDS is always at least one minor version behind. To illustrate this, they just released MySQL 5.7 on RDS yesterday, yet they did not pick the latest version, and only offer MySQL 5.7.10.

So I would be replicating from a newer master to an older slave.

Is this acceptable, as long as we're talking minor versions?

The only page I could find on the MySQL website is this one:
https://dev.mysql.com/doc/refman/5.0/en/replication-compatibility.html

It basically says that you can only replicate from an older master to a newer slave, but they are talking major releases, i.e. 5.6 to 5.7.

This does not give me a clue to whether it is OK to replicate to a slave running an older minor version, e.g. from a 5.6.11 master to a 5.6.10 slave.

Get the .rpm for 5.7.10 and install that! Or else test the bejesus out of your proposed setup! From here: the manual is self-contradictory:

Sentence 1:

  MySQL supports replication from one release series to the next higher
  release series. For example, you can replicate from a master running
  MySQL 5.5 to a slave running MySQL 5.6, from a master running MySQL
  5.6 to a slave running MySQL 5.7, and so on.

Sentence 2: (this **is** the biggie)

  However, you may encounter difficulties when replicating from an older
  master to a newer slave if the master uses statements or relies on
  behavior no longer supported in the version of MySQL used on the
  slave.

Sentence 3:

  The use of more than two MySQL Server versions is not supported in
  replication setups involving multiple masters, regardless of the
  number of master or slave MySQL servers. This restriction applies not
  only to release series, but to version numbers within the same release
  series as well. For example, if you are using a chained or circular
  replication setup, you cannot use MySQL 5.7.1, MySQL 5.7.2, and MySQL
  5.7.4 concurrently, although you could use any two of these releases together.

Check out my first idea - I firmly believe that it's (by far) your best option - no compatibility worries (that's the great thing about Open Source software).


Incremental backup in mariaDB mysql I am using 5.5.41-MariaDB and ENGINE is MyISAM on CentOS 7. Please help me for Incremental backup command of mariaDB mysql on CentOS 7.

You might check out Percona's Xtrabackup solution and see if it will work with MariaDB as all seem to be drop-in replacements.

See: https://www.percona.com/doc/percona-xtrabackup/2.1/xtrabackup_bin/incremental_backups.html

Some articles imply this would work in MariaDB. I only use Percona so cannot confirm unfortunately: https://mariadb.com/kb/en/mariadb/backup-restore-and-import-xtrabackup/


Oracle 10g NVL() not working? I need to create a pivot table of all customers and all products and the price that the customer gets charged but also falling back to a standard customer when the customer has no price set up (the customer 999999 is the standard customer that has a standard price for all products).

I wrote this query;

Select KUST_ADR.KU_NAME As Customer,
  GLAS_DATEN_BASIS.GL_BEZ As Product,
  NVL(OS_PRZU.ZUM2, (Select OS_PRZU.ZUM2 From OS_PRZU
  Where OS_PRZU.IDNR = GLAS_DATEN_BASIS.IDNR And OS_PRZU.KUNR = 999999)) As
  Price
From GLAS_DATEN_BASIS
  Inner Join OS_PRZU On GLAS_DATEN_BASIS.IDNR = OS_PRZU.IDNR
  Inner Join KUST_ADR On KUST_ADR.KU_NR = OS_PRZU.KUNR
Where KUST_ADR.KU_ADR_ART = 0

but still lots of the fields are empty, so NVL() is not working? What am I doing wrong?

Apparently this sub-query does not always return a value:

(Select OS_PRZU.ZUM2 From OS_PRZU
Where OS_PRZU.IDNR = GLAS_DATEN_BASIS.IDNR And OS_PRZU.KUNR = 999999)

meaning that the default customer probably does not have a price for some of the products.

Replace the sub-query with a hard-coded value in order to verify that NVL itself works as expected.


How can I unload Oracle database tables into a csv files? I need to retrieve certain data from an Oracle 11g release 1 database and provide it as one or more CSV files. The data resides in multiple tables and/or views. All this should work via the command line.
What would be the best approach to this?

Arun,

now that sqlcl is available fromOracle SQL Developer 4.1 EA2 (4.1.0.18.37)
you can use it a lot like the old and famous sqlplus.
sqlcl has an output format setting for csv

set sqlformat csv
spool x.csv
select * from yourtable[s];
spool off

for more info about sqlcl checkout Kris' blog

before sqlcl was around
easiest for this was to use APEX and export the report to csv.
In plain old sqlplus you can do this by using

set lines 9999 -- the appropriate size
set head off  -- no header lines
set colsep ';' --column separator to ;
set pages 0 -- no pages
set feed off
select your data;
spool to_tofile
/
spool off

This works best if the results are to be written on an application server or client. If they have to be written on the database server, utl_file might be a better option.

Ronald.


Reference a schema from public table column I have a multi-tenant Postgresql database with each schema belonging one of our customers (tenants). Each tenant has a table called Messages. So CustomerX.Messages contains all of the messages that are sent to CustomerX.

All of the messages come in through a generic endpoint and are processed a bit to determine which customer should receive the message. So the message pipeline looks like this from a high level:

RAW MESSAGE (public.Messages)
    |
    |
[PROCESSING]
    |
    |
DECRYPTED MESSAGE (some_tenant.Messages)

I would like to put the "pre-processed" messages into a public table containing the exact, unmodified input to the system. After going through a round of processing, I'd like to drop the final message into the customer's CustomerX.Messages table.

My Question

What is the best way for me to model this? I need to ensure the following:

There is exactly one message in "some" schema for each message in the public table.
There is exactly one message in the public schema for each message in a customer schema.
Given some message (in either the public or customer schema), I can find the other one.

You can store everything in public.messages, add column that describes the actual schema and make views in every schema, that are selecting only messages that needs to be visible in particular schema.
Depending on you needs you can create rules on views for data modification, partition that big table, add row level security.


Calculate Count Distinct without grouped columns in Oracle I am trying to extract a few records and I ran into the "not a single group" error. This error can be resolved by removing the (count(distinct dc.DUCT_FID)) as NO_OF_WAYS column expression. However, I really need this column in my extraction.

SELECT TELEM.EXCH_CODE,
       TELEM.CENTRAL_OFFICE,
       TELEM.IPID as SEGMENT_IPID,
       TELEM.FEATURE_STATE AS SEGMENT_STATUS,
       DCON.G3E_FID AS DUCT_FID,
       DELEM.FEATURE_STATE AS DUCT_STATUS,
       COND.FR_MH,
       COND.TO_MH,
       TI.TRENCH_LENGTH,
       (count(distinct dc.DUCT_FID)) as NO_OF_WAYS,
       TELEM.YEAR_PLACED AS YEAR_BUILD,
       ADDR.STREET_NAME AS LOCATION,
       ADDR.POSTAL_CODE,
       TELEM.OWNERSHIP
FROM SGTEL10.B$GC_NETELEM_0404 TELEM
JOIN SGTEL10.B$GC_COND_0404 COND ON COND.G3E_FID = TELEM.G3E_FID
JOIN SGTEL10.TRENCH_INFO TI ON TI.PIPE_FID = COND.G3E_FID
LEFT JOIN SGTEL10.B$GC_CONTAIN_0404 FCON ON FCON.G3E_OWNERFID = TELEM.G3E_FID
LEFT JOIN SGTEL10.B$GC_CONTAIN_0404 DCON ON DCON.G3E_OWNERFID = FCON.G3E_OWNERFID
LEFT JOIN SGTEL10.B$GC_DUCT_0404 DUCT ON DUCT.G3E_FID = DCON.G3E_FID
LEFT JOIN SGTEL10.DUCT_COUNT DC ON DC.DUCT_FID = DUCT.G3E_FID
LEFT JOIN SGTEL10.B$GC_NETELEM_0404 DELEM ON DELEM.G3E_FID = DUCT.G3E_FID
LEFT JOIN SGTEL10.B$GC_ADDRESS_0404 ADDR ON ADDR.G3E_FID = COND.G3E_FID
WHERE TELEM.IPID IN (4001702,
                     4005753,
                     4005757,
                     4005785
                    );

What should I do?

You use COUNT(DISTINCT ..) expression without GROUP BY clause. If you need this value for each group (if there are duplicates in your output while removing COUNT() expression) you must add GROUP BY for all other fields in output list. Or you can use the window variant of COUNT() function which is scalar. - Akina


SELECT * FROM INFORMATION SCHEMA on V5R3M0 I have access to a client's V7R1 AS/400 and I can run queries like SELECT NAME FROM INFORMATION_SCEMA.TABLES and

SELECT
    COLUMN_NAME,
    DATA_TYPE,
    TYPE_NAME,
    COLUMN_SIZE,
    COLUMN_TEXT
FROM "SYSIBM"."SQLCOLUMNS"
WHERE
    TABLE_SCHEM = 'MYLIB'
    AND TABLE_NAME = 'SOMEPF'

Just fine on that. I am QSECOFR on that system.

However, on a free greenscreen account on PUB1.DE running V5R3M0, I cannot run that command from either STRSQL or from the db2 command inside of QSH. I tried the alternative format of INFORMATION_SCHEMA/TABLES I've seen around as well. I get the following errors from STRSQL:

> SELECT name FROM INFORMATION_SCHEMA/TABLES
  TABLES in QSYS2 type *FILE not found.
> SELECT name FROM INFORMATION_SCHEMA.TABLES
  Qualified object name TABLES not valid.

Is this a permissions problem, a syntax difference between V5 and V7 or something else?

You are experiencing the difference between "system naming convention" and "SQL naming convention" modes.  Originally, system naming mode required a slash character / as the delimiter when qualifying with a schema name, while SQL naming mode used a period . delimiter.  An update to v7.1 now allows either delimiter when using system naming.  V5R3 is no longer supported, nor updated.  On a green screen session you can specify STRSQL NAMING(*SYS).  Or specify system naming convention on your JDBC/ODBC connection string settings.

There are other functional differences between the two modes.  See here and here. Chief among them is that system naming mode allows use of the library list, as long as you don't specify a CURRENT SCHEMA.  But you can specify a current library, with the CHGCURLIB command in CL, or in SQL with CALL QCMDEXC('CHGCURLIB MYLIB',15) (where 15 is the length of this sample command)

DB2 for i catalog views may be found in QSYS2.

SELECT COLNO
      ,SYSTEM_COLUMN_NAME
      ,DATA_TYPE
      ,coalesce(PRECISION,LENGTH)        as len
      ,smallint(SCALE)                   as dp
      ,STORAGE                           as bytes
      ,varchar(COLUMN_TEXT,50)           as fldtext
      ,varchar(substr(LABEL, 1,20),20)   as hdg1
      ,varchar(substr(LABEL,21,20),20)   as hdg2
      ,varchar(substr(LABEL,41,20),20)   as hdg2
      ,CCSID
      ,IS_NULLABLE
FROM QSYS2/SYSCOLUMNS
WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB'
  AND SYSTEM_TABLE_NAME   = 'SOMEPF'

Most IBM i shops used 10-character names (operating system rules) and typically did not use 128-character names allowed by DB2, so generally SYSTEM_COLUMN_NAME, SYSTEM_TABLE_NAME, and SYSTEM_SCHEMA_NAME will be sufficient.

The V5R3 SQL Reference  is available as a PDF, as Dave Jones mentioned.


Does CommVault use native SQL Server backup COM objects or the VSS? We are being told by a parent company that we will be transferring our backups to COMM Vault and are trying to gather some information regarding how the product works as far as whether it is using snapshots or the native SQL Server backup objects.  Can anyone tell me a little more about how COMM Vault backups work underneath?  A couple of us here have gone through the documentation and it appears that snapshots are not the only choice but I thought I would post the question here as well.  Thank you in advance.

I used CommVault at a previous job. Version 9.4+ did both native and VSS snaps.


How to create date ranges of events coming from an API where open state represents the date range and close state the gaps between the ranges? I would like to store date/time ranges in PostgreSQL of some arbitrary events that have a state of open or closed and
a date/time of when the state changed.

Events coming from an API will have the following data for a single event:

Request 1:
{
  id: 1,
  state: 'open',
  date: '2020-02-17T10:00:00Z'
}

Request 2:
{
  id: 1,
  state: 'close',
  date: '2020-02-17T10:10:00Z'
}

Request 3:
{
  id: 1,
  state: 'open',
  date: '2020-02-17T11:00:00Z'
}

The requests can come in any order, so future dates can come before past dates or states are not always
open -> close -> open -> close. For example the API could send the open states for the same event one after the other.

I was thinking of using the tstzrange for saving this data in the database in the following form:

CREATE TABLE events (
  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  event_id int,
  validity tstzrange
);

The open states are captured in the validity column and close states are the gaps between the validity columns.
For example if a single event has the following states and date/times (using only times for simplicity) in this order:

state          date/time
close          20:30
open           18:00
close          16:00
open           15:00
open           20:00
close          19:30

The validity rows should look like:

id          event_id           validity
1           1                  [15:00, 16:00)
2           1                  [18:00, 19:30)
3           1                  [20:00, 20:30)

Event 1 has the state open between 15:00 - 16:00, state close between 16:00 - 18:00, state open between 18:00 - 19:30 and so on.

To illustrate this visually:

My problem is that the events are not coming in order, so I don't know how to manipulate the individual validity
columns to insert / update these rows.

I figured it out how to insert/update this kind of data into PostgreSQL date ranges from an algorithmic perspective
that this may be helpful to you too.

As this data is a time series, ultimately when we know the full dataset, there can't be any overlaps between the open,
close states of a single event over time.

Keeping the algorithm flexible enough, for when we don't know the full range of events and open/close states there are a
couple of rules that needs to be taken into consideration:

If there's a validity column containing the event's date and:

if the event state is open then:

1.1. if the containing validity has a negative infinity = update the lower bound of the containing validity to the event's date.

1.2. if there is another validity row strictly before the containing validity, meaning that the containing validity lower bound equals to another validity upper bound = update the lower bound of the containing validity to the event's date.

1.3. if the above points are false = split the containing validity into two separate rows:

1.3.1. update the containing validity lower bound to the event's date.
1.3.2. insert a new row where the lower bound is the containing validity's original lower bound and the upper bound is the event's date.

if the event state is close then (reverse of the open state rules):

2.1. if the containing validity has a positive infinity = update the upper bound of the containing validity to the event's date.

2.2. if there is another validity row strictly after the containing validity, meaning that the containing validity upper bound equals to another validity lower bound = update the upper bound of the containing validity to the event's date.

2.3. if the above points are false = split the containing validity into two separate rows:

2.3.1.  update the containing validity upper bound to the event's date.

2.3.2. insert a new row where the upper bound is the containing validity's original upper bound and the lower is the event's date.

If there's no validity range containing the event's date then:

if the event state is open then insert a new validity row with the lower bound set to the event's date and the upper bound set to positive infinity.
if the event state is close then insert a new validity row with the upper bound set to the event's date and the lower bound set to negative infinity.

These rules gives you the best possible picture of the event states where the open state are captured in the validity
columns itself and the close state is the gaps between the validity rows.

Once there are more and more data coming in, either from historical data or current ones, the validity columns will
give you a clearer picture of the event states over time.


Separate login for user / administrators using mongodb I have 2 separate groups of users on my website. One is user and the other one is admin.  Currently I put them in 2 different collections user and admin and login respectively.  My questions is:

Mongodb generates _id automatically for both user and the admin collection.  Since they're in 2 different collections chances are one day you created one user and one admin with the same _id right?  So it seems the only way is to check all documents in both dbs and make sure your newly created user (or admin) does not have repeated _id right?  Is there something I can do natively in mongodb to make this happen without checking?
I can also put all users and admins in one collection.  This way I don't have a problem but I am not sure if I should do this.

p.s: because I have a table keeping track of user / admin balances so I certainly don't want the _id to repeat
p.s2: the db is taking longer and longer to query.  Also my ORM creates user instances and generated _id automatically for me.  I am not sure if I should just generate an _id and create it this way...

Any comments are greatly appreciated.  Thanks!!

You should not have two collections, that will give you headache.

Have a user collection which has admin and regular users.  You can then query users as a whole or look for a given type only. So if you are looking for user id x you don't have two search two collections.

As for the question on duplicate ids with 2 collections, it simply depends how the ids are generated.  You say your ORM does it... so you will need to check the doc on how it does it.


PL/SQL Developer - empty object list I'm using PL/SQL Developer v9, brio intelligence 6.6 and on another computer SQL Developer v7. On version 7 everything is ok. My table objects are 'expanding' shows all avaiable databases.

In version 9 object does not develop - as if it were empty. When I use BRIO all databases are avaiable...

So there is some problem with PL/SQL Developer but I can't figure it out ;\

I fixed it. A little ridiculous but it was enough to change the 'current user' to 'all users' tab objects


Recursive self joins I have a comments table, which can be simplified down to this:

comments
=======
id
user_id
text
parent_id

where parent_id is nullable, but might be a key for its parent comment.

Now, how can I select all descendants of a specific comment?
The comments might be several levels down...

Hierarchical queries, as those recursive queries are known, are now supported in MySQL 8.

Old Answer

Alternatively, you can find a dynamic (and thus, potentially dangerous) trick here: https://stackoverflow.com/questions/8104187/mysql-hierarchical-queries

You can also find a discussion on how to store hierarchical data with other models than with an Adjacency List (i.e. the Parent column) here: https://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/

Good luck!


When you use a replica to backup from so you don't have to turn off the master, is this considered High Availablity? I read somewhere that MySQL can have a master / slave setup in which the master database can continue to run live and in production while the slave database can be turned off to snatch a backup.  And then when the backup is complete, the slave database can be turned back on, and all the changes made in the master will be replicated over.

Is this considered high availability?  Or does this also require that if the master fails, the slave take over? Or does that require something like vMotion from VMware?

High Availability (HA) is a very generic term that can be applied to a whole range of different scenarios.

There is no “one size fits all” approach to delivering High Availability (HA). Unique application attributes, business requirements, operational capabilities and legacy infrastructure can all influence HA technology selection. And technology is only one element in delivering HA: people and processes are just as critical as the technology itself.

At it's most basic it simply means ensuring your entire system (of which the database is a part) is available to meet your required SLA, whether that be 99% availability, or 99.999% availability, or somehing else. The above link gives the official definition from MySQL.

Your use of a Slave database is one of many good reasons for using a MySQL slave. And at it's most basic level, if your master fails, the slave could be manually promoted to be the master. Then you would set up the Master as the new slave once it had been recovered.

The below slide presentation gives a good overview of the main MySQL HA offerings.

MySQL HA Solutions


Oracle 11g install on Debian Wheezy does not start I successfully installed Oracle 11g R2 on my laptop for evaluation, by mixing various sources of documentation. I saw the Oracle daemons after installation.
But I have a few problems :

After a reboot, the instance does not start, and does not even create
a log file. (only the TNSLSNR starts).
The oracle user's ORACLE_SID gets a default value (AL32UTF8) where
the value I set in the /etc/profile.d/oracle.sh initialisation script
is INFO (created during install). Where does this come from?
Where can I start investigating?

Thanks for your help.

Note : Only one line not commented out in the /etc/oratab file :

  INFO:/u01/app/oracle/product/11.2.0/dbhome_1:N

Best regards,

Fred

Edit your /etc/oratab file to read:

INFO:/u01/app/oracle/product/11.2.0/dbhome_1:Y

I guess it's just one of those things you need to know to look for!


MySQL Partitions with Partition limit I have a partitioned table on the basis of the Primary key having 1.5 Billion rows in it. But when I check the .ibd files only 80 ibd show in GB's whereas 120ibd shows 1MB file. I would like to know how does the distribution work when I limit Partition with PARTITIONS 200. My structure is as below. Given that id & checksum both are unique all the time.

CREATE TABLE `mapping` (
  `id` varchar(50) NOT NULL DEFAULT '',
  `sha_checksum` varchar(50) NOT NULL DEFAULT '',
  `path_id` varchar(90) DEFAULT NULL,
  `file_id` varchar(35) DEFAULT NULL,
  `server_id` CHAR(6) DEFAULT NULL,
  PRIMARY KEY (`id`,`sha_checksum`)
) ENGINE=InnoDB
/*!50100 PARTITION BY KEY (id,sha_checksum)
PARTITIONS 200 */

Please help me to understand where I am wrong.

Rather than trying to explain why most of the partitions seem empty, let me argue against that flavor of PARTITIONing.

To put it bluntly, PK & BY KEY(id, sha1) gains no performance, nor any other benefit that I can imagine.

Note that to get "partition pruning", you have to specify both the id and the sha_checksum.  Performance will be essentially the same as having a non-partitioned table with the same PRIMARY KEY.

Since the records are randomly scattered around, I can think of no use for "transportable tablespaces".

What version of MySQL are you using?

What character set are you using?  (I'm thinking of space considerations, especially for the CHAR column.)

Please describe id and the application; I can help you devise a better PARTITION scheme, or continue to argue against it.

Another thing to note:  More than about 50 partitions slows things down for a variety of reasons, one of which is that all partitions are opened when an INSERT occurs.  (Very new versions have 'fixed' this failure to 'prune'.)


Entity-relationship diagrams and application program functionalities It seems that the core functionalities of an application program I'm working on are nothing but associative entities. Hence one-to-many relations sort of produce "metadata" that will only feed (one way or the other) the associative entities for our application functionalities.

Now we have an entity-relationship diagram (ERD) that has lots of one-to-many (more that 10 tables) and just one associative entity. What does it say about that model or the the application?

Is it improvable, i.e., can the application circumvent more functionalities if the ERD is improved to add more associative entities?

Does having very few associative entities means that the application will not be rich in functionalities?

Additional considerations

What I'm wondering is: if the project scope statements lead to an ERD with only one many-to-many relation and a dozen of one-to-many, then would that mean that the project does not solve many problems (functionalities) except only digitizing lots of data?

I think that with less many-to-many, they will just mirror at first (unless we create join queries for other purposes...).

Or simply put: does a high number of many-to-many associations means that the software will be richer in functionalities than with less many-to-many (do not include join queries in the thoughts for this one)?

You touch several subjects, therefore it is important to establish their scopes and the links that exist among them.
Firstly, if the specific business domain you are working with implies (a) multiple one-to-many associations along with just one associative entity type —or many-to-many [M:N] association—, and (b) you are representing said characteristics with the demanded precision at both the conceptual and logical levels of abstraction of the database of interest then (c) there is nothing special about such a situation, you are doing just fine.
Conceptual schema: capturing the informational requirements of a business
A properly delimited conceptual schema —I am not sure, but it seems that that is what you mean by “project scope statements”— implies to the identification and definition of the relevant business rules that describe the interconnections (1) between entity types, (2) between entity types and their own properties and (3) between the properties themselves, which may come about in quite different cardinality ratios —neither only one-to-many [1:N], nor exclusively many-to-many [M:N]— and may or may not be represented in an entity-relationship diagram (ERD for brevity).
Regarding the initial title of the question, an associative entity type —which is different from an entity, i.e., an instance or occurrence of an entity type— can only be missing if the database modeler failed to (i) identify it and consequently (ii) did not reflect it in the pertinent conceptual schema and perhaps in an ERD.
ERDs: graphical representations of conceptual schemas
An ERD depicting the associations and the corresponding cardinality ratios of a certain scenario is not supposed to solve “the problem of digitizing lots data”, but serves the purpose of capturing and exposing graphically the conceptual definitions of the business in question. This sort of diagram is a communication instrument that can be really powerful when used appropriately.
If you want to see examples of the delineation of the conceptual schemas of databases with associations of very distinct cardinality ratios (and the subsequent logical-level representations), visit, e.g., my answers to the questions entitled

“Modeling a database for a video game production business context with multiple many-to-many relationships”, and
“Developing a database for a funds transfers business where (a) people and organizations can (b) send and receive money”.

Levels of abstraction: application program components, ERD symbols and relational/SQL instruments
Since you mention “tables”, I assume that you have the intention of building a relational database, and said type of database must be independent from the application programs (apps, for brevity) that share access to it.
Naturally, the number of conceptual and logical elements of a database has repercussions on the number of kinds of items that the apps have to (a) receive from an end-user terminal or another system, (b) apply some computational processing and (c) send to the database.  But the quantity of apps components (e.g., object-oriented classes) does not necessarily have to mirror (1) the quantity of conceptual-level entity types of a database nor (2) the quantity of base tables of the corresponding logical layout (e.g., the fields of a single object-oriented class may encompass the columns of two or more base or derived tables).
On the other hand, an ERD is not composed of tables but of graphical constructs portraying entity types and relationships (I prefer the words associations, connections or links, for reasons I will detail below). Said entity types and relationships are, in turn, usually represented by base tables (i.e., base relations), and enforced via constraints declared on columns with the respective data types or domains if possible (i.e., relation attributes), if they are managed by virtue of a relational database running on a relational database management system —which is the typical case, but the conceptual-level elements could be handled by, e.g., an obsolete network or hierarchical database—.
In this manner, it is very important to distinguish between (i) a conceptual-level relationship and (ii) a logical-level relation which is the mathematical construct —proposed by Dr. E. F. Codd in his relational model—
utilized for administering data in a relational database. In this regard, you may find of help this series of posts.
It is as well opportune to differentiate between base and derivable data structures; the former commonly represented by base tables (i.e., those declared by dint of CREATE TABLE … ( … ); statements), and the latter by derived tables (i.e., those expressed by way of SELECT operations that, e.g., “combine” columns FROM distinct base tables or other derived tables via JOINs, sometimes fixed as VIEWs), when operating on a relational database. Since the app components have to do directly with the way in which the information of interest is displayed to end-users, they belong in the external level of abstraction when it comes to the ANSI/SPARC architecture, hence they should have a direct correspondence with the aforementioned views.
Application programs and data management: separation of concerns
In turn, the functionalities of the apps involve carrying out processes (“behaviour” using object-oriented programming terminology) and should be analyzed and defined by techniques (e.g., algorithm development) that are distinct from the ones used in the creation of a database, because database modeling and app design are two different (although related) disciplines.
The structural and functional richness of a whole computerized information system (i.e., a database and one or more apps sharing it) depends entirely on the skills possessed by the designer(s) with respect to the successful (i.e., accurate) representation of the informational (regarding the database) and processing or computational (concerning the apps) requirements. This is in agreement with the software development principle known as separation of concerns.


Is there a way to use a Case statement to read from one field and change another field? I have a simple MS SQL database created from an import from a CSV file. I need to check a field to see if a word is in that field, and if it is, change the contents of another field. Like this:

When [Category] contains the word 'Server' change [Form_Factor] to 'Server'.

I'm not a MS SQL person, and I have stumped my Database administrator for some reason.

I don't see why you'd even need a case statement for this. Assuming your table is called myTable in the dbo schema (you didn't specify, but it isn't particularly important) and we're talking about updating the field in the same row.

UPDATE dbo.MyTable
SET Form_Factor = 'Server'
WHERE Category LIKE '%Server%'

Msdn link: Update with Where

I don't see how any database professional can be stumped by this though.. unless the problem is much more complex than you are letting on.


How to get all data before and after 10 days of interval? SELECT * FROM events  WHERE `date`
    BETWEEN NOW() AND DATE_ADD
    (NOW(), INTERVAL 10 DAY) order by date

I am using the above code to extract the events data of ten days of interval. Now I want to Extract out all the data before this 10 days of interval and after 10 days of interval. How can I do that in a perfect way?

Please help. Advance thanks for your time

It is probably best to issue two queries.

SELECT * FROM events
    WHERE `date` >= NOW() - INTERVAL 10 DAY
      AND `date`  < NOW();

SELECT * FROM events
    WHERE `date` >= NOW()
      AND `date`  < NOW() + INTERVAL 10 DAY;

You could get both at once, but it could be inconvenient if you need to separate them:

SELECT * FROM events
    WHERE `date` >= NOW() - INTERVAL 10 DAY
      AND `date`  < NOW() + INTERVAL 10 DAY;

If you want to base on midnight instead of the current second, use CURDATE() instead of NOW().


skip columns while using LOAD command mysql I have a csv file having 5 columns and i want to load only one out of that, is there a way i can do it. I want to use LOAD statement of MySQL 5.5
Any pointers are welcomed, help would be much appreciated

From the documentation:

  You can also discard an input value by assigning it to a user variable
  and not assigning the variable to a table column:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @dummy, column2, @dummy, column3);


Update table with values from another table runs really slow I have a PostgreSQL table (tab_A) with about 32 million records, and I have a second PostgreSQL table (tab_B) with about 4000 records. tab_B contains 3 fields whose values I intend to pass on to tab_A (field1, field2, field3).

tab_A

32 million records
Unique identifier (id)
BTREE index

tab_B

4000 records
Unique identifier (id)
BTREE index

I am trying to do the job with the following query (previous to do this I have already created the 3 new fields in tab_A to host the values):

UPDATE tab_A
SET field1 = t2.field1, field2 = t2.field2, field3 = t2.field3
FROM tab_A t1 JOIN tab_B t2
ON t1.uprn = t2.uprn;

This query runs on and on for more than 5 hours and I eventually need to stop it because it doesn't seem to me it should be taking that long (my understanding is that if both tables contain an index this should be pretty fast).

Any ideas on whether I am missing something here? Perhaps it's normal it takes that long taking into account tab_A contains 32 million records? Any other approach to run this more efficiently?

Don't repeat the target table in the FROM clause:

UPDATE tab_A
   SET field1 = t2.field1, field2 = t2.field2, field3 = t2.field3
FROM tab_B t2
WHERE tab_A.uprn = t2.uprn;

Quote from the manual

  Note that the target table must not appear in the from_list, unless you intend a self-join

(emphasis mine)


How to replay xlogs from the beginning of time? (postgresql) Let's say I'm keeping all the xlog's for a cluster instance since the time of cluster initialization (ie, initdb). Let's call that time T0. All xlog's are retained/archived through T1 and continuing through time T2, at which point a backup is made. But now, I need to recover to time T1. I know how to do PIT to a time after T2, but I'm not sure how to do it to time T1. Do I re-initialize the database and replay the logs? Will that actually work, or will I have issues with timelines?

I was able to perform a complete recovery from T0 to T1 using these steps. We do it with a completely newly initialized database. We need the original database and all its xlogs. The original database is in $OLD_PGDATA and the new one is in $PGDATA. Make sure you get these straight before you proceed.

Steps

Set $PGDATA and create its directory and new database therein

[postgres]$ export PGDATA=/var/lib/pgsql.$$
[postgres]$ mkdir -p $PGDATA
[postgres]$ initdb

Modify the control file using a little trick I gleaned from stackexchange and other sites:

[postgres]$ cut -c 1-8 $OLD_PGDATA/global/pg_control | tr -d '\n' |
 dd of=$PGDATA/global/pg_control count=1 skip=0 conv=notrunc
[postgres]$ pg_resetxlog -f $PGDATA

You should see something like the following warning and output, which can be ignored:

     pg_resetxlog: pg_control exists but has invalid CRC; proceed with caution
     Transaction log reset

Create recovery.conf file and start the database.

 [postgres]$ cat >recovery.conf << END
 restore_command='cp "$OLD_PGDATA/pg_xlog/%f" "%p"'
 END
 [postgres]$ pg_ctl start

Note: the space between << and END is very important.

According to pg_resetxlog manual, afterwards, do a full dump, initdb, restore to be sure the DB is in a consistent state.

Note: if you are running the OLD_PGDATA instance on the same host, you'll just add -o "-p 5433" to use a port #5433 instead of the default port.

It took less than 15 minutes to recover 1700+ or 28 GB of xlog files.


Backup and restore FileTable files I'm preparing to use the FileTable feature in SQL Server 2012, but I'm unclear about how backups work with this feature:

Does a full database backup include FileTable files?
How would I perform the restore of a single file, for example?

Does a full database backup include FileTable files?

Yes it does include the filetable data. From BOL :

  When you use SQL Server to back up a FileTable, the FILESTREAM data is backed up with the structured data in the database. If you do not want to back up FILESTREAM data with relational data, you can use a partial backup to exclude FILESTREAM filegroups.

-

  How would I perform the restore of a single file, for example?

You can use the same restore concept and do a point-in-time restore with STOPAT parameter.

For detailed example refer to (Page 319): Backup and Restore for FILESTREAM Databases.


SQL server Memory components I am trying to understand different memory components in sql,so far my research lead me understand below ,If SO DBAs can confirm my understanding/add something if i am missing,i will be really gratefull

Total components which use memory in SQL can be found through below query,were each  distinct clerk means a component.

select * from sys.dm_os_memory_clerks

SQL dynamically calculates buffer pool memory +memory used for storing plans using max memory settings.This is the part which confuses me .so i am taking one scenario to explain in detail

Test case:
Suppose my SQL is having  64 GB ram and i have capped max server setting to 58 GB,then below are the events that occur

Buffer Pool plus query plans  will be able to use maximum of 58 GB ram
Remaining 6 GB of RAM will be used to for OS and other SQL components

So my questions are like below

Since sql server calculates number of locks during startup,will the memory used by locks fall into 6 GB category ?
Suppose i have executed a query which requires more  memory ,will this memory be allocated from 6 Gb or from buffer pool (58 Gb ) memory or will this be spilled to disk ?
There are different memory clerks all shown above in screenshot,will they fall in 6 GB category ?
In 64 Bit systems,does Sql still calculate memtoleave option or with the exception of few memory clerks ,everything falls under
58 GB category ?
Further largest consumer of these clerks would be buffer pool and
query plans. If other clerks face memory pressure or need more
memory,memory allocation will come through buffer pool clerk in
terms of stolen pages if they pages flushed to disk to accomodate
this request.

Below are total references i had

1.SQL Server Max Memory not limiting use of RAM

--links in question too

2.http://blogs.msdn.com/b/sqljourney/archive/2012/12/17/an-in-depth-look-at-sql-server-memory-part-1.aspx
3.Slavos msdn blog

To start with you have SQL Server 2012 and memory architecture changed significantly in this version there is only one memory allocator (yes there is VAS allocator as well but I still would say there is only one 'Any Page' allocator) and this does Almost All memory allocations. Here is what BOL has to say. Please read this definition clearly

  Max server memory controls SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and CLR memory (basically any “clerk” as found in dm_os_memory_clerks). Memory for thread stacks, Memory heaps, linked server providers other than SQL Server, or any memory allocated by a “non SQL Server” DLL is not controlled by max server memory.

For your question

  Total components which use memory in SQL can be found through below query,were each distinct clerk means a component.

From above definition please note that not all memory allocations are tracked by clerks. But for practical purposes you can safely assume sys.dm_os_memory_clerks is place to look for memory breakup as used by various components.Every component that allocates a significant amount of memory must create its own memory clerk and allocate all its memory by using the clerk interfaces. Frequently, components create their corresponding clerks at the time SQL Server is started.

Please note clerks only track memory allocated to various components they never do allocation of SQL Server memory.

  Buffer Pool plus query plans will be able to use maximum of 58 GB ram

Not only buffer pool and query plans but almost all components which require memory will now be catered from max server memory setting. Specially SQLCLR, memory to this is now allocated from max server memory setting previously in SQL Server 2008 R2 and before memory was allocated from outside max server memory setting.

  Since sql server calculates number of locks during startup,will the memory used by locks fall into 6 GB category ?

No memory will be allocated by Max server memory setting(will fall under 58G category) not from outside this limit. I am not using word buffer pool as from SQL Server 2012 buffer pool is only consumer not allocator of memory so its better to use max server memory

  Suppose i have executed a query which requires more memory ,will this memory be allocated from 6 Gb or from buffer pool (58 Gb ) memory or will this be spilled to disk ?

Well it depends on what components require memory. Please read the definition on top if resource that requires memory is Thread stack, memory heap, Third Party linked server provider the memory would be allocated from outside max server memory limit otherwise from inside. When query requires more memory Lazywriter would start ageing out unused pages and internal threads would ask various memory consumers to free some memory. This would create new space in buffer pool and SQL Server can bring would bring new pages into the memory.

  There are different memory clerks all shown above in screenshot,will they fall in 6 GB category ?

You have not added screenshot but when you query sys.dm_os_memory_clerks whatever clerks information come they all track memory allocated to various components and would fall under max server memory limit category (58 G limit category).

  In 64 Bit systems,does Sql still calculate memtoleave option or with the exception of few memory clerks ,everything falls under 58 GB category ?

NO THERE IS NO MEMORYTOLEAVE IN 64 BIT. Please read This Blogs.msdn article for more details.

  Further largest consumer of these clerks would be buffer pool and query plans. If other clerks face memory pressure or need more memory,memory allocation will come through buffer pool clerk in terms of stolen pages if they pages flushed to disk to accomodate this request.

No it is not exactly like that. Yes in SQL Server 2012 as well largest consumer of memory is buffer pool. Stolen memory is memory utilized by SQL Server to do miscellaneous tasks NOT REALTED to SQL Server database. These other Miscellaneous task might include sorting, has joins etc. So in case of memory pressure it might or might not take stolen memory depending on memory pressure.